1. 含义：连接查询又称多表查询，当查询的字段来自多个表时，就会用到连接查询。
2.笛卡尔乘积现象：表1 有M行，表2 有N行，那么结果就有M*N行。
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
3. 分类：
（1）按年代分类：
sql192标准：等值～，非等值～，自连接～，也支持一部分外连接（用于oracle ,sqlserver)
sql199标准【推荐】:所有功能几乎全部支持。
（2）按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接（mysql不支持）
交叉连接
4. SQL192标准的学习
4-1. 等值连接
语法：
select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key（连接条件）
and【筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：（1）一般为表起别名
（2）多表顺序可以调换
（3）n表连接至少需要(n-1)个连接条件
（4）多表等值连接的结果为多表的交集部分
（5）可以搭配前面介绍的所有子句的使用，比如排序，分组，筛选。
注意：（1）为表起了别名，则查询字段就不能使用原来的表名去限定了.
原因：当你为表起别名后，因为执行顺序起从 【from 子句 】开始，从此在视图里表名就以
别名的形式存在了，以后再喊原始表名就不搭理你了，（我是🐟，我只有当前记忆），从此以后
的操作必须喊别名了。
√ 4-1. 等值连接练习
√ 4-1-1. 简单的等值连接练习
#Girls 数据库里有三张表。
mysql> show tables;
+-----------------+
| Tables_in_girls |
+-----------------+
| admin |
| beauty |
| boys |
+-----------------+
3 rows in set (0.00 sec)
**嘻嘻，让你们看一下表，了解一下内容以及结构，嗯都是你们的男神和女神，😄。
mysql> select * from beauty;
+----+------------+------+---------------------+-------------+-------+--------------+
| id | name | sex | borndate | phone | photo | boyfriend_id |
+----+------------+------+---------------------+-------------+-------+--------------+
| 1 | 柳岩 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 8 |
| 2 | 苍老师 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 3 | Angelababy | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 3 |
| 4 | 关晓彤 | 女 | 1993-02-03 00:00:00 | 18209876579 | NULL | 2 |
| 5 | 周冬雨 | 女 | 1992-02-03 00:00:00 | 18209179577 | NULL | 9 |
| 6 | 周芷若 | 女 | 1988-02-03 00:00:00 | 18209876577 | NULL | 1 |
| 7 | 岳灵珊 | 女 | 1987-12-30 00:00:00 | 18219876577 | NULL | 9 |
| 8 | 小昭 | 女 | 1989-02-03 00:00:00 | 18209876567 | NULL | 1 |
| 9 | 双儿 | 女 | 1993-02-03 00:00:00 | 18209876579 | NULL | 9 |
| 10 | 王语嫣 | 女 | 1992-02-03 00:00:00 | 18209179577 | NULL | 4 |
| 11 | 夏雪 | 女 | 1993-02-03 00:00:00 | 18209876579 | NULL | 9 |
| 12 | 赵敏 | 女 | 1992-02-03 00:00:00 | 18209179577 | NULL | 1 |
| 13 | 王菲 | 女 | 1969-08-08 00:00:00 | 18202318320 | NULL | 5 |
| 14 | 张柏芝 | 女 | 1980-05-24 00:00:00 | 15920424850 | NULL | 5 |
+----+------------+------+---------------------+-------------+-------+--------------+
mysql> select * from boys;
+----+-----------+--------+
| id | boyName | userCP |
+----+-----------+--------+
| 1 | 张无忌 | 100 |
| 2 | 鹿晗 | 800 |
| 3 | 黄晓明 | 50 |
| 4 | 段誉 | 300 |
| 5 | 谢霆锋 | 1000 |
+----+-----------+--------+
5 rows in set (0.00 sec)
mysql> #1.查询女神名和对应的男神名
mysql> select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id;
+------------+-----------+
| name | boyName |
+------------+-----------+
| Angelababy | 黄晓明 |
| 关晓彤 | 鹿晗 |
| 周芷若 | 张无忌 |
| 小昭 | 张无忌 |
| 王语嫣 | 段誉 |
| 赵敏 | 张无忌 |
| 王菲 | 谢霆锋 |
| 张柏芝 | 谢霆锋 |
+------------+-----------+
8 rows in set (0.01 sec)
mysql> #2. 查询员工名和对应的部门名
mysql> select last_name, department_name from employees , departments where employees.department_id = departments.department_id;
+-------------+-----------------+
| last_name | department_name |
+-------------+-----------------+
| Whalen | Adm |
| Hartstein | Mar |
| Fay | Mar |
| Raphaely | Pur |
| Himuro | Pur |
| Colmenares | Pur |
| Mavris | Hum |
| Weiss | Shi |
| OConnell | Shi |
| Grant | Shi |
| Hunold | IT |
| Ernst | IT |
| Austin | IT |
| Pataballa | IT |
| Lorentz | IT |
| Baer | Pub |
| Russell | Sal |
| Partners | Sal |
| Errazuriz | Sal |
...
...
√ 4-1-2 起别名的等值查询(一般都会起别名）
#起别名，为了提高语句的简洁度。
#这题必须为表起别名，因为避免字段重名造成查询迷糊定位不了的错误。
mysql> #查询员工名，工种号，工种名。
mysql> select e.last_name,j.job_id, j.job_title from employees e,jobs j where e.job_id = j.job_id;
+-------------+------------+---------------------------------+
| last_name | job_id | job_title |
+-------------+------------+---------------------------------+
| Gietz | AC_ACCOUNT | Public Accountant |
| Higgins | AC_MGR | Accounting Manager |
| Whalen | AD_ASST | Administration Assistant |
| K_ing | AD_PRES | President |
| Kochhar | AD_VP | Administration Vice President |
| De Haan | AD_VP | Administration Vice President |
| Faviet | FI_ACCOUNT | Accountant |
| Chen | FI_ACCOUNT | Accountant |
| Sciarra | FI_ACCOUNT | Accountant |
#注意：当你为表起别名后，因为执行顺序起从 【from 子句 】开始，从此在视图里表名就以
别名的形式存在了，以后再喊原始表名就不搭理你了，（我是🐟，我只有当前记忆），从此以后
的操作必须喊别名了。
√ 4-1-3 添加筛选条件的等值查询[where ...and 筛选条件]
mysql> #3.查询有奖金的员工名部门名
mysql> select e.last_name , d.department_name from employees e, departments d where e.department_id = d.department_id and commission_pct is not null;
+------------+-----------------+
| last_name | department_name |
+------------+-----------------+
| Russell | Sal |
| Partners | Sal |
| Errazuriz | Sal |
| Cambrault | Sal |
| Zlotkey | Sal |
| Tucker | Sal |
| Bernstein | Sal |
| Hall | Sal |
| Olsen | Sal |
| Cambrault | Sal |
| Tuvault | Sal |
| K_ing | Sal |
| Sully | Sal |
| McEwen | Sal |
| Smith | Sal |
| Doran | Sal |
| Sewall | Sal |
| Vishney | Sal |
| Greene | Sal |
| Marvins | Sal |
| Lee | Sal |
| Ande | Sal |
| Banda | Sal |
| Ozer | Sal |
| Bloom | Sal |
| Fox | Sal |
| Smith | Sal |
| Bates | Sal |
| Kumar | Sal |
| Abel | Sal |
| Hutton | Sal |
| Taylor | Sal |
| Livingston | Sal |
| Johnson | Sal |
+------------+-----------------+
34 rows in set (0.19 sec)
mysql> #4.查询城市名中第二字符为o的城市名,部门名.
mysql> select department_name,city from departments d,locations l where d.location_id = l.location_id and city like '%o%';
+-----------------+---------------------+
| department_name | city |
+-----------------+---------------------+
| IT | Southlake |
| Shi | South San Francisco |
| Mar | Toronto |
| Hum | London |
| Sal | Oxford |
+-----------------+---------------------+
5 rows in set (0.00 sec)
√ 4-1-4 添加分组的等值查询。
mysql> #5.查询每个城市的部门个数
mysql> select count(*) 部门个数,city from departments d , locations l where l.location_id = d.location_id group by l.city;
+--------------+---------------------+
| 部门个数 | city |
+--------------+---------------------+
| 1 | Southlake |
| 1 | South San Francisco |
| 21 | Seattle |
| 1 | Toronto |
| 1 | London |
| 1 | Oxford |
| 1 | Munich |
+--------------+---------------------+
7 rows in set (0.00 sec)
/*有小可爱问：你不是起别名了吗？为啥 「select count(*) 部门个数,city 」中的city 没加别名限定
呀？？？因为这并没有犯重名的毛病，我们的数据库可以自己分辨出来，所以加不加别名限定都可以啦～，
当然你不可以用原始表名限定啦～*/
mysql> #6. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资。
mysql> 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, d.manager_id;
+-----------------+------------+-------------+
| department_name | manager_id | min(salary) |
+-----------------+------------+-------------+
| Sal | 145 | 6100.00 |
+-----------------+------------+-------------+
1 row in set (0.00 sec)
#好惨哎，我以为有奖金的部门有好几个呢，还以为自己查错，然后亲测 发现真的只有一个部门💔(代码为证）
mysql> select distinct(department_name) from employees e, departments d where e.department_id = d.department_id and commission_pct is not null;
+-----------------+
| department_name |
+-----------------+
| Sal |
+-----------------+
1 row in set (0.00 sec)
√. 4-1-5 可以添加排序的等值查询
mysql> #7.查询每个工种的工种名和员工的个数，并且按员工个数降序排序
mysql> select job_title ,count(*) from jobs j,employees e where j.job_id = e.job_id group by j.job_id order by count(*) desc;
+---------------------------------+----------+
| job_title | count(*) |
+---------------------------------+----------+
| Sales Representative | 30 |
| Shipping Clerk | 20 |
| Stock Clerk | 20 |
| Accountant | 5 |
| Sales Manager | 5 |
| Programmer | 5 |
| Stock Manager | 5 |
| Purchasing Clerk | 5 |
| Administration Vice President | 2 |
| Purchasing Manager | 1 |
| Finance Manager | 1 |
| Human Resources Representative | 1 |
| Public Accountant | 1 |
| Accounting Manager | 1 |
| Marketing Manager | 1 |
| Administration Assistant | 1 |
| Marketing Representative | 1 |
| President | 1 |
| Public Relations Representative | 1 |
+---------------------------------+----------+
19 rows in set (0.00 sec)
√. 4-1-6 可以实现多表连接的等值查询。
mysql>#8. 查询员工名，部门名，和所在含有‘e’的城市,按部门的名称排序
mysql> 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 and city like '%e%' order by department_name asc;
+------------+-----------------+-----------+
| last_name | department_name | city |
+------------+-----------------+-----------+
| Higgins | Acc | Seattle |
| Gietz | Acc | Seattle |
| Whalen | Adm | Seattle |
| Kochhar | Exe | Seattle |
| De Haan | Exe | Seattle |
| K_ing | Exe | Seattle |
| Greenberg | Fin | Seattle |
| Faviet | Fin | Seattle |
| Chen | Fin | Seattle |
| Sciarra | Fin | Seattle |
| Urman | Fin | Seattle |
| Popp | Fin | Seattle |
| Hunold | IT | Southlake |
| Ernst | IT | Southlake |
| Austin | IT | Southlake |
| Pataballa | IT | Southlake |
| Lorentz | IT | Southlake |
| Raphaely | Pur | Seattle |
| Khoo | Pur | Seattle |
| Baida | Pur | Seattle |
| Tobias | Pur | Seattle |
| Himuro | Pur | Seattle |
| Colmenares | Pur | Seattle |
+------------+-----------------+-----------+
23 rows in set (0.00 sec)
4-2. 非等值连接
语法：
select 查询列表
from 表1 别名，表2 别名
where 非等值连接条件
and【筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
4-2-1 非等值连接练习
#查询工资级别为‘A’的员工工资和工资级别。
mysql> select salary ,grade_level from job_grades g, employees e where salary between g.lowest_sal and g.highest_sal and grade_level ='A';
+---------+-------------+
| salary | grade_level |
+---------+-------------+
| 2900.00 | A |
| 2800.00 | A |
| 2600.00 | A |
| 2500.00 | A |
| 2700.00 | A |
| 2400.00 | A |
| 2200.00 | A |
| 2800.00 | A |
| 2500.00 | A |
| 2100.00 | A |
| 2900.00 | A |
| 2400.00 | A |
| 2200.00 | A |
| 2700.00 | A |
| 2500.00 | A |
| 2600.00 | A |
| 2500.00 | A |
| 2500.00 | A |
| 2800.00 | A |
| 2900.00 | A |
| 2500.00 | A |
| 2800.00 | A |
| 2600.00 | A |
| 2600.00 | A |
+---------+-------------+
24 rows in set (0.00 sec)
4-3. 自连接
4-3-1 自连接的练习
mysql> #查询员工名和上级的名称
mysql> select e.last_name e ,m.last_name m from employees e,employees m where 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 |
| Raphaely | K_ing |
| Khoo | Raphaely |
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
