MySQL学习笔记(十二)__连接查询(一)

绝地灬酷狼 2021-11-02 13:44 279阅读 0赞

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

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

分类：  
按年代分类：  
           sql192 标准：仅支持内连接  
           sql199 标准；支持内连接+外连接(左外、右外)+交叉连接

按功能分类：  
           内连接：  
                      等值连接  
                      非等值连接  
                      自连接  
           外连接：  
                      左外连接  
                      右外连接  
                      全外连接  
           交叉连接

一、sql192 标准  
内连接  
1、等值连接  
a.多表等值连接的结果为多表的交集部分  
b.n表连接，至少需要n-1个连接条件  
c.多表的顺序没有要求  
d.一般需要为表取别名  
e.可以搭配前面所有的子句使用，比如排序、分组、筛选  
1  
2  
3  
4  
5  
e.g.：查询员工名和对应的部门名

SELECT  
last\_name,  
department\_name  
FROM  
employees,  
departments  
WHERE  
employees.department\_id = departments.department\_id;  
1  
2  
3  
4  
5  
6  
7  
8  
2、为表起别名  
a.提高语句的简洁度  
b.区分多个重名的字段  
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定  
1  
2  
3  
e.g.：查询员工名、工种号、工种名

SELECT  
last\_name,  
e.job\_id,  
j.job\_title  
FROM  
employees AS e,  
jobs AS j  
WHERE  
e.job\_id = j.job\_id;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
3、两个表的顺序可以调换  
e.g.：查询员工名、工种号、工种名

SELECT  
last\_name,  
e.job\_id,  
j.job\_title  
FROM  
jobs AS j,  
employees AS e  
WHERE  
e.job\_id = j.job\_id;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
4、可以加筛选  
e.g.：查询有奖金的员工名、部门名

SELECT  
last\_name,  
department\_name,  
commission\_pct  
FROM  
employees AS e,  
departments AS d  
WHERE  
e.department\_id = d.department\_id  
AND commission\_pct IS NOT NULL;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
e.g.：查询城市名中第二个字符为o的部门名和城市名

SELECT  
city,  
department\_name  
FROM  
locations l,  
departments d  
WHERE  
l.location\_id = d.location\_id  
AND city LIKE '\_o%’;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
5、可以加分组  
e.g.：查询每个城市的部门个数

SELECT  
COUNT( \* ) 个数,  
city  
FROM  
departments d,  
locations l  
WHERE  
d.location\_id = l.location\_id  
GROUP BY  
city;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
e.g.：查询有奖金的每个部门名和部门的领导编号和该部门的最低工资

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,  
manager\_id;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
6、可以加排序  
e.g.：查询每个工种的工种名和员工个数，并按员工个数降序

SELECT  
job\_title,  
COUNT( \* )  
FROM  
jobs j,  
employees e  
WHERE  
e.job\_id = j.job\_id  
GROUP BY  
job\_title  
ORDER BY  
COUNT( \* ) DESC;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
7、三表连接  
e.g.：查询员工名、部门名、所在城市

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;  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
非等值连接  
与等值连接类似

自连接  
e.g.：查询员工名与上级名字

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;  
\---------------------

转载于:https://www.cnblogs.com/hyhy904/p/10971063.html