mysql多张表外连接 mysql多表内连接查询

文章目录

• 连接查询的分类
• Sql92

• 等值连接查询
• 非等值连接查询
• 自连接查询

连接查询的分类

1. 按照年代进行分类

• Sql92标准：仅仅支持内连接
• Sql99标准（推荐）：支持内连接和外连接（左外和右外）以及交叉连接

1. 按照功能进行分类

• 内连接：等值连接、非等值连接、自连接
• 外连接：左外连接、右外连接、全外连接
• 交叉连接

Sql92

Sql92标准：仅仅支持内连接

等值连接查询

/**案例1：查询女神名和对应的男神名**/
SELECT name,boyName
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;
/**案例2：查询员工名对应的部门名**/
SELECT last_name,department_name
FROM employees,departments
WHERE employees.'department_id' = departments.'department_id';
/**案例3：查询员工名、工种号和工种名**/
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.'job_id' = jobs.'job_id';

注意：在操作过程中可以选择为表其别名，好处如下：

1. 提高语句的简简洁度
2. 区分多个重名的字段

/**查询员工名、工种号和工种名**/
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE d.'job_id'= j.'job_id'

1. 添加筛选的等值查询

/**查询有奖金的员工名、部门名**/
SELECT last_name,department_name
FROM employeess e,departments d
WHERE e.'department_id' = d.'department_id' 
AND e.'commission_pct' IS NOT NULL;
/**查询城市名中第二个字符为o的城市名和部门名**/
SELECT city, department_name
FROM department d,location l
WHERE d.'location_id' = l.'location_id'
AND city LIKE '_O%';

2. 添加分组的等值查询

/**查询每个城市的部门个数**/
SELECT COUNT(*) 部门个数,city
FROM location l,department t
WHERE l.'location_id' = t.'location_id'
GROUP BY city
/**查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资**/
SELECT department_name,d.manager_id,MIN(salary)
FROM department d, employees e
WHERE d.'department_id' = e.'department_id'
AND commission_pct IS NOT NULL
GROUP BY department_name;

3.添加排序的等值查询

/**查询每个工种的工种名和员工的个数，并且按照员工的个数降序排序**/
SELECT job_title,COUNT(*) 员工个数
FROM job j,employees e
WHERE j.'job_id' = e.'job_id'
GROUP BY job_title
ORDER BY 员工个数 DESC;

4.三表连接的等值查询

/**查询员工名、部门名和所在的城市**/
SELECT employee_name,department_name,city
FROM employees e, departement d,location l 
WHERE e.'department_id' = d.'department_id'
AND d.'location_id' = l.'location_id';

总结：

1. 多表链接查询的结果为多个表的交集部分
2. n表链接，至少需要n-1个连接条件
3. 多表的顺序没有要求
4. 一般情况下需要为表起别名
5. 可以搭配排序、分组、筛选来进行使用

非等值连接查询

表：job_grade

grade_level	lowest_sal	highest_sal
A	1000	2999
B	3000	5999
C	6000	9999
D	10000	12000

/**查询员工的工资和工资级别**/
SELECT salary,grade_level
FROM employees e, job_grade jg
WHERE salary BETWEEN lowest_sal AND highest_sal
/**查询员工的工资和工资级别为A的工资**/
SELECT salary,grade_level
FROM employees e, job_grade jg
WHERE salary BETWEEN lowest_sal AND highest_sal
AND grade_level = 'A';

自连接查询

表employees结构如下：

employee_id	last_name	manager_id
100	K_ing	(NULL)
101	Kochhar	100
102	De Haan	100
103	Hunold	102
104	Ernst	103

/**查询员工名和上级名称**/
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e, employees m
WHERE e.'employee_id' = 'm.manager_id';