多表连接查询：
1
2
笛卡尔乘积，如果连接条件省略或无效则会出现
解决办法：添加上连接条件。
传统模式下的连接：等值连接—非等值连接：
1
2
3
4
1：等值连接的结果 = 多个表的交集。
2：n表连接，至少需要n-1个连接条件。
3：多个表不分主次，没有顺序要求。
4：一般为表起别名，提高阅读性和性能。
sql99语法：通过join关键字实现连接。

支持：等值连接、非等值连接、内连接、外连接、交叉连接

语法 (好处：语句上，连接条件和筛选条件实现了分离，简洁明了)：

1
2
3
4
5
6
7
8
select 字段···
from 表1
[ inner | left outer | right outer | cross ] join 表2 on 连接条件
[ inner | left outer | right outer | cross ] join 表3 on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选条件
order by 排序的字段或表达式
自连接(起别名)：
1
2
3
4
5
6
7
8
9
10
11
12
例：查询员工和直接上级的名称
sql99：

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

sql92：

SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
例：等值连接
1：查询员工名、部门名
SELECT last_name,department_name
FROM department d
JOIN employees e
ON e.department_id = d.dempartment_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的城市名和部门个数(添加分组+筛选)
①：查询每个城市的部门个数
②：在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*)>3;

4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
①：查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY departments_name
②：在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数，department_name
FROM empioyees 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 departmens d ON e.department_id = d.deparment_id
INNER JOIN jobs j ON e.job_id = j.job_id	//第三个表要与前两个表有联系，否则会出错，顺序可以变。
ORDER BY department_name DESC;

例：非等值连接
1：查询员工的工资级别
SELECT salary,grade_lever
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

2：查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_leval
HAVING COUNT(*)>20
ORDER BY grade_leval DESC;

例：自连接
查询员工的名字、上级的名字
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%';
外连接：

应用场景：用于查询一个表中有，另一个表中没有的记录

特点：

1：外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的，则显示匹配的值

如果从表中没有和它匹配的，则显示null

外连接查询结果=内连接结果+主表中有而从表没有的记录

2：左外连接：left join左边的是主表

  右外连接：right join右边的是主表

3：左外和右外交换两个表的顺序，可以实现同样的效果

4：全外连接=内连接的结果+表1中右但表2中没有的+表2中有但表1中没有的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
例：查询男友不在男神表的女神名
左外连接：
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.boyfriend_id = bo.id
WHERE b.id IS NULL;

查询哪个部门没有员工
左外连接：
SEKECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.enployee_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;

全外连接：（MySQL中不支持全外连接）
USE girls;
SELECT b.*,bo.*
FROM beayty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

MySQL中可以使用UNION达到全外连接的效果：
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
故实现全外连接可以使用：
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

交叉连接：
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
联合查询：

union 联合，合并，将多条查询语句的结果合并成为一个结果

语法：

1
2
3
4
5
查询语句1
uinon
查询语句2
unoin
...

应用场景：

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

1：要求多条查询语句的查询列数是一致的。

2：要求多条查询语句的查询的每一列的类型和顺序最好一致。

3：union关键字默认去重，如果使用union all 可以包含重复项。

1
2
3
4
5
6
7
8
9
10
11
例1：查询部门编号>90或邮箱包含a的员工的信息。
SEKECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
联合查询：
SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id>90;

2：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id_name,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_id WHERE tGender='male';

2021-03-27