SQL-DQL(8)199标准连接
2018-08-30 本文已影响62人
小白201808
1. sql199标准【推荐】:因为几乎所有功能都支持，可读性高。
功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接（mysql不支持）
交叉连接
2.语法：
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
2-1.连接类型分类：
1.内连接（∆）：inner
2.外连接
左外（∆）：left【outer】
右外（∆）：right【outer】
全外：full【outer】（mysql不支持）
3.交叉连接：cross
2-2.连接条件
分类：等值～，非等值～，自连接～。
3.来吧，练习靶场...
3-1 内连接（inner 可以省略）
特点： 1.筛选条件放在where的后面，连接条件放在on后面，提高分离性，增加可读性。
2.inner 可以省略
3.可添加筛选，分组，排序，多表连接
4.inner join连接和sql192语法中的等值连接效果是一样的，都是查询多表的交集。
3-1-1 等值连接
案例1:查询员工名，部门名。
#inner 可以省略。
mysql> select last_name ,department_name from employees e inner join departments d on e.department_id = d.department_id;
+-------------+-----------------+
| last_name | department_name |
+-------------+-----------------+
| Whalen | Adm |
| Hartstein | Mar |
| Fay | Mar |
| Raphaely | Pur |
| Khoo | Pur |
| Baida | Pur |
| Tobias | Pur |
...
...
案例2: 查询名字中包含e的员工名和工种名（添加筛选条件）
mysql> select last_name 员工名,job_title 工种名 from employees e inner join jobs j on e.job_id = j.job_id where last_name like '%e%';
+-------------+---------------------------------+
| 员工名 | 工种名 |
+-------------+---------------------------------+
| De Haan | Administration Vice President |
| Ernst | Programmer |
| Lorentz | Programmer |
| Greenberg | Finance Manager |
| Faviet | Accountant |
| Chen | Accountant |
| Raphaely | Purchasing Manager |
| Colmenares | Purchasing Clerk |
| Weiss | Stock Manager |
...
...
案例三：查询部门个数>3的城市名和部门个数（添加分组和筛选条件。
mysql> select city ,count(*) from locations l join departments d on l.location_id = d.location_id group by city having count(*)>3;
+---------+----------+
| city | count(*) |
+---------+----------+
| Seattle | 21 |
+---------+----------+
1 row in set (0.11 sec)
案例四：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序。（添加分组和筛选条件和排序）
mysql> select department_name ,count(*) from departments d join employees e on d.department_id = e.department_id group by department_name having count(*)>3;
+-----------------+----------+
| department_name | count(*) |
+-----------------+----------+
| Pur | 6 |
| Shi | 45 |
| IT | 5 |
| Sal | 34 |
| Fin | 6 |
+-----------------+----------+
5 rows in set (0.00 sec)
案例五：查询员工名，部门名，工种名，并按部门降序排序。（多表连接）
mysql> #查询员工名，部门名，工种名，并按部门降序排序。
mysql> select last_name , department_name,job_title from employees e join departments d on d.department_id = e.department_id join jobs j on j.job_id = e.job_id order by department_name desc;
+-------------+-----------------+---------------------------------+
| last_name | department_name | job_title |
+-------------+-----------------+---------------------------------+
| Taylor | Shi | Shipping Clerk |
| Chung | Shi | Shipping Clerk |
| Walsh | Shi | Shipping Clerk |
| Bissot | Shi | Stock Clerk |
| Ladwig | Shi | Stock Clerk |
| Weiss | Shi | Stock Manager |
| Fleaur | Shi | Shipping Clerk |
| Dilly | Shi | Shipping Clerk |
3-1-2 非等值连接
案例一：查询员工的工资级别
mysql> select salary ,grade_level from employees e join job_grades g on salary between lowest_sal and highest_sal ;
+----------+-------------+
| salary | grade_level |
+----------+-------------+
| 24000.00 | E |
| 17000.00 | E |
| 17000.00 | E |
| 9000.00 | C |
| 6000.00 | C |
| 4800.00 | B |
| 4800.00 | B |
| 4200.00 | B |
| 12000.00 | D |
| 9000.00 | C |
...
案例二：查询工资级别的个数大于20 的个数，并按工资的级别的降序排序。
...
mysql> select count(*) ,grade_level from employees e join job_grades g on salary between lowest_sal and highest_sal group by grade_level having count(*) >20 order by grade_level desc ;
+----------+-------------+
| count(*) | grade_level |
+----------+-------------+
| 38 | C |
| 26 | B |
| 24 | A |
+----------+-------------+
3 rows in set (0.00 sec)
3-1-3 自连接
案例一：查询员工的名字和他上司的名字
mysql> select e.last_name e ,m.last_name m from employees e join employees m on e.manager_id = m.employee_id;
+-------------+-----------+
| e | m |
+-------------+-----------+
| Kochhar | K_ing |
| De Haan | K_ing |
| Hunold | De Haan |
| Ernst | Hunold |
| Austin | Hunold |
| Pataballa | Hunold |
| Lorentz | Hunold |
| Greenberg | Kochhar |
| Faviet | Greenberg |
| Chen | Greenberg |
| Sciarra | Greenberg |
| Urman | Greenberg |
| Popp | Greenberg |
...
...
3-2 外连接（在这只学习左外和右外，因为mysql不支持全外）
1.应用场景：用于查询一个表中有，另一个表没有的记录。
2特点：
（1）外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则就显示匹配
的值，否则显示null。（外连接的查询结果
= 内连接结果+主表有而从表没有的记录）
(2) 左外连接，left join 左边是主表
右外连接，right join 左边是主表
(3) 左外和右外交换两表的顺序可以实现同样的效果。
(4)全外连接 = 内连接的结果+ 表1有而表2 没有+表2有而表1没有。
3-2-1 左/右外连接
案例一：查询男朋友 不在男神表的女神名。（鸡冻吧！！！）
#1. 可以先查一下所有女神的男盘友是否都在表中
mysql> select gf.name girlname, boyname from beauty gf left join boys bf on boyfriend_id =bf.id;
+------------+-----------+
| girlname | boyname |
+------------+-----------+
| 柳岩 | NULL |
| 苍老师 | NULL |
| Angelababy | 黄晓明 |
| 关晓彤 | 鹿晗 |
| 周冬雨 | NULL |
| 周芷若 | 张无忌 |
| 岳灵珊 | NULL |
| 小昭 | 张无忌 |
| 双儿 | NULL |
| 王语嫣 | 段誉 |
| 夏雪 | NULL |
| 赵敏 | 张无忌 |
| 王菲 | 谢霆锋 |
| 张柏芝 | 谢霆锋 |
+------------+-----------+
14 rows in set (0.00 sec)
#2. 匹配结果为NULl的说明男朋友资料还不在我们的表中
于是添加条件就可以查询出男朋友不在男神表的女神名
mysql> select gf.name girlname, boyname from beauty gf left join boys bf on boyfriend_id =bf.id where boyname is null;
+-----------+---------+
| girlname | boyname |
+-----------+---------+
| 柳岩 | NULL |
| 苍老师 | NULL |
| 周冬雨 | NULL |
| 岳灵珊 | NULL |
| 双儿 | NULL |
| 夏雪 | NULL |
+-----------+---------+
6 rows in set (0.00 sec)
#这里推荐用下面的方法吧，毕竟ID，才是我们唯一标志。
mysql> select gf.name girlname, boyname, bf.id bfId from beauty gf left join boys bf on boyfriend_id =bf.id;
+------------+-----------+------+
| girlname | boyname | bfId |
+------------+-----------+------+
| 柳岩 | NULL | NULL |
| 苍老师 | NULL | NULL |
| Angelababy | 黄晓明 | 3 |
| 关晓彤 | 鹿晗 | 2 |
| 周冬雨 | NULL | NULL |
| 周芷若 | 张无忌 | 1 |
| 岳灵珊 | NULL | NULL |
| 小昭 | 张无忌 | 1 |
| 双儿 | NULL | NULL |
| 王语嫣 | 段誉 | 4 |
| 夏雪 | NULL | NULL |
| 赵敏 | 张无忌 | 1 |
| 王菲 | 谢霆锋 | 5 |
| 张柏芝 | 谢霆锋 | 5 |
+------------+-----------+------+
14 rows in set (0.00 sec)
mysql> select gf.name girlname, boyname, bf.id bfId from beauty gf left join boys bf on boyfriend_id =bf.id where bf.id is null;
+-----------+---------+------+
| girlname | boyname | bfId |
+-----------+---------+------+
| 柳岩 | NULL | NULL |
| 苍老师 | NULL | NULL |
| 周冬雨 | NULL | NULL |
| 岳灵珊 | NULL | NULL |
| 双儿 | NULL | NULL |
| 夏雪 | NULL | NULL |
+-----------+---------+------+
案例二：查询编号>3的女神的男盆友信息，如果有则列出详细信息，如果没有就用null填充。
#用左连接实现
mysql> select gf.name, gf.id, boyname from beauty gf left join boys bf on bf.id = gf.id where gf.id>3;
+-----------+----+-----------+
| name | id | boyname |
+-----------+----+-----------+
| 关晓彤 | 4 | 段誉 |
| 周冬雨 | 5 | 谢霆锋 |
| 周芷若 | 6 | NULL |
| 岳灵珊 | 7 | NULL |
| 小昭 | 8 | NULL |
| 双儿 | 9 | NULL |
| 王语嫣 | 10 | NULL |
| 夏雪 | 11 | NULL |
| 赵敏 | 12 | NULL |
| 王菲 | 13 | NULL |
| 张柏芝 | 14 | NULL |
+-----------+----+-----------+
11 rows in set (0.35 sec)
#用右连接实现
mysql> select gf.name, gf.id, boyname from boys bf right join beauty gf on bf.id = gf.id where gf.id>3;
+-----------+----+-----------+
| name | id | boyname |
+-----------+----+-----------+
| 关晓彤 | 4 | 段誉 |
| 周冬雨 | 5 | 谢霆锋 |
| 周芷若 | 6 | NULL |
| 岳灵珊 | 7 | NULL |
| 小昭 | 8 | NULL |
| 双儿 | 9 | NULL |
| 王语嫣 | 10 | NULL |
| 夏雪 | 11 | NULL |
| 赵敏 | 12 | NULL |
| 王菲 | 13 | NULL |
| 张柏芝 | 14 | NULL |
+-----------+----+-----------+
11 rows in set (0.00 sec)
案例三：查询一下哪个城市没有部门
mysql> use myEmployees;
mysql> select city ,d.* from locations l left join departments d on l.location_id = d.location_id where d.location_id is null;
+-----------------+---------------+-----------------+------------+-------------+
| city | department_id | department_name | manager_id | location_id |
+-----------------+---------------+-----------------+------------+-------------+
| Roma | NULL | NULL | NULL | NULL |
| Venice | NULL | NULL | NULL | NULL |
| Tokyo | NULL | NULL | NULL | NULL |
| Hiroshima | NULL | NULL | NULL | NULL |
| South Brunswick | NULL | NULL | NULL | NULL |
| Whitehorse | NULL | NULL | NULL | NULL |
| Beijing | NULL | NULL | NULL | NULL |
| Bombay | NULL | NULL | NULL | NULL |
| Sydney | NULL | NULL | NULL | NULL |
| Singapore | NULL | NULL | NULL | NULL |
| Stretford | NULL | NULL | NULL | NULL |
| Sao Paulo | NULL | NULL | NULL | NULL |
| Geneva | NULL | NULL | NULL | NULL |
| Bern | NULL | NULL | NULL | NULL |
| Utrecht | NULL | NULL | NULL | NULL |
| Mexico City | NULL | NULL | NULL | NULL |
+-----------------+---------------+-----------------+------------+-------------+
16 rows in set (0.00 sec)
3-3 交叉连接(cross join)
实现了笛卡尔乘积的效果。
mysql> select bf.*,gf.* from beauty gf cross join boys bf;
+----+-----------+--------+----+------------+------+---------------------+-------------+-------+--------------+
| id | boyName | userCP | id | name | sex | borndate | phone | photo | boyfriend_id |
+----+-----------+--------+----+------------+------+---------------------+-------------+-------+--------------+
| 1 | 张无忌 | 100 | 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 2 | 鹿晗 | 800 | 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 3 | 黄晓明 | 50 | 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 4 | 段誉 | 300 | 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 5 | 谢霆锋 | 1000 | 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 1 | 张无忌 | 100 | 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 2 | 鹿晗 | 800 | 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 3 | 黄晓明 | 50 | 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 4 | 段誉 | 300 | 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 5 | 谢霆锋 | 1000 | 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 1 | 张无忌 | 100 | 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
| 2 | 鹿晗 | 800 | 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
| 3 | 黄晓明 | 50 | 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
| 4 | 段誉 | 300 | 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
| 5 | 谢霆锋 | 1000 | 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
...
...
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
