MySQL学习笔记（八）——连接查询

MySQL学习笔记（八）——连接查询

含义

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类

按年代分类

sql92标准：仅仅支持内连接（mysql中）
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接（mysql中）

按功能分类

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

一、sql92标准

1.等值连接

特点：

1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

案例1：查询女神名和对应的男神名

SELECT NAME,boyname 
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=department.department_id;

（1）为表其别名

好处：
1.提高语句的简洁度
2.区分多个重名的字段
注意：
如果为表起了别名，则查询的字段就不能使用原来的表名去限定
案例：查询员工名、工种号、工种名

SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id=j.job_id;

（2）两个表的顺序是否可以调换

可以调换

（3）可以加筛选

案例1：查询有奖金的员工名、部门名

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,location l
WHERE d.location_id=l.location_id
AND city LIKE '_o%';

（4）可以加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

（5）可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

（6）可以实现三表连接

案例：查询员工名、部门名和所在城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

2.非等值连接

案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grade g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

3.自连接

案例：查询员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

二、sql99语法

语法

SELECT 查询列表
FROM 表1 别名【连接类型】
JOIN 表2 别名 
ON 连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】
【ORDER BY 排序列表】

分类

内连接：inner
外连接：左外：left [outer]
　　　　右外：right [outer]
　　　　全外：full [outer]
交叉连接：cross

1.内连接

语法

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件

分类

等值连接
非等值连接
自连接

特点

1.添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

（1）等值连接

案例1：查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;

案例2：查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';

案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）
1.查询每个城市的部门个数
2.在1结果上筛选满足条件的

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN location l
ON d.location_id=l.location_id
GROUP BY city 
HAVING COUNT(*)>3;

案例4：查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序（添加排序）
1.查询每个部门的员工个数

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name;

2.在1的结果上筛选员工个数>3的记录，并排序

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name;
HAVING COUNT(*)>3;
ORDER BY COUNT(*) DESC;

案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id=j.job_id
ORDER BY department_name DESC;

（2）非等值连接

查询员工的工资级别

SELECT salary,grade_level
FROM employees e
【INNER】 JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

查询每个工资级别大于2的个数，并且按工资级别降序

SELECT COUNT(*),grade_level
FROM employees e
【INNER】 JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
GROUP BY grade_level
HAVING COUNT(*)>2
ORDER BY grade_level DESC;

（3）自连接

查询姓名中包含k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;
WHERE e.last_name LIKE '%k%';

2.外连接

应用场景：

用于查询一个表中有，另一个表中没有的记录

特点：

1.外连接的查询结果为主表中的所有记录
　如果从表中有和它匹配的，则显示匹配的值
　如果从表中没有和它匹配的，则显示null
　外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接，left join左边的是主表
　右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
引入：查询男朋友不在男神表的女神名
左外连接

SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

右外连接

SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id=bo.id;

案例1：查询哪个部门没有员工
左外连接：

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

右外连接：

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

3.全外连接（MySQL中不支持）

SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

4.交叉连接

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和sql99的比较

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高