SQL-DQL(8)199标准连接

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 |
...
...

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！