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 |
...
...
注: 这是本人的学习笔记及练习, 如果有错误的地方望指出一起讨论, 谢谢!
来源: http://www.jianshu.com/p/4e93e53204f8