DQL 语言 --连接查询（多表查询）09
一、含义
当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2 from 表1，表2,...;
笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接，如：笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何解决：添加有效的连接条件
二、分类
按年代分类：
sql92：
等值连接
非等值连接
自连接
sql92：
等值连接
非等值连接
自连接
也支持一部分外连接（用于oracle、sqlserver，mysql不支持）
sql99【推荐使用】
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接（mysql不支持）
交叉连接
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接（mysql不支持）
交叉连接
三、SQL92语法
#1、等值连接
语法：
select 查询列表 from 表1 别名,表2 别名 where 表1.key=表2.key 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
特点：
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
#案例1：查询女神名和对应的男神名
SELECT `name`, `boyName` FROM `boys`, `beauty` WHERE `beauty`.`boyfriend_id` = `boys`.`id` ;
#案例2：查询员工名和对应的部门名
SELECT `last_name`, `department_name` FROM `departments`, `employees` WHERE `employees`.`department_id` = `departments`.`department_id`; #寻找公共判断条件
#2、为表起别名
优点：
①提高语句的简洁度
②区分多个重名的字段
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定。
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;
#3、两个表的顺序是否可以调换 （可以，原理：拿着一个表去匹配另一个表）
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title FROM jobs j,employees e WHERE e.`job_id`=j.`job_id`;
#4、可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL; #添加筛选
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.`location_id` = l.`location_id` AND city LIKE '_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.`location_id`=l.`location_id` GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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`;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GROUP BY job_title ORDER BY COUNT(*) DESC;
#7、可以实现三表连接
#案例：查询员工名、部门名和所在的以s开头的城市，以部门名降序排序。
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 's%' ORDER BY department_name DESC;
#8、非等值连接
语法：
select 查询列表 from 表1 别名,表2 别名 where 非等值的连接条件 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
#案例1：查询员工的工资和工资级别（用salary判断）
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#---------------------------------------------------------------------
#添加筛选
# AND g.`grade_level`='A'; /* select salary,employee_id from employees; select * from job_grades; CREATE TABLE job_grades (grade_level VARCHAR(3), lowest_sal int, highest_sal int); INSERT INTO job_grades VALUES ('A', 1000, 2999); INSERT INTO job_grades VALUES ('B', 3000, 5999); INSERT INTO job_grades VALUES('C', 6000, 9999); INSERT INTO job_grades VALUES('D', 10000, 14999); INSERT INTO job_grades VALUES('E', 15000, 24999); INSERT INTO job_grades VALUES('F', 25000, 40000); */
#9、自连接 (一个表中)
语法：
select 查询列表 from 表 别名1,表 别名2 where 等值的连接条件 【and 筛选条件】 【group by 分组字段】 【having 分组后的筛选】 【order by 排序字段】
#案例：查询 员工名和上级的名称
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`;
四、SQL99语法
1、内连接
语法：
语法：
select 查询列表 from 表1 别名 【inner】 join 表2 别名 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 子句;
特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
④添加排序、分组、筛选
⑤inner可以省略
⑥筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
⑦inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
⑤inner可以省略
⑥筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
⑦inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
分类：
等值连接
非等值连接
自连接
等值连接
非等值连接
自连接
#1.等值连接
#案例1.查询员工名、部门名
SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id`;
#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` WHERE e.`last_name` LIKE '%e%';
#案例3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY city HAVING COUNT(*)>3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*) 个数,department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;
#案例5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` INNER JOIN jobs j ON e.`job_id` = j.`job_id` ORDER BY department_name DESC;
#2.非等值连接
#案例1.查询员工的工资级别
SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例2.查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;
#3.自连接
#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`= m.`employee_id` WHERE e.`last_name` LIKE '%k%';
2、外连接
语法：
select 查询列表 from 表1 别名 left|right|full【outer】 join 表2 别名 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 子句;
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
应用场景：用于查询一个表中有，另一个表没有的记录。
#案例1：查询哪个部门没有员工
#左外 SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
#右外 SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
#全外 -------------mysql不支持 USE girls; SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id;
#交叉连接 ------------------------笛卡尔乘积 SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
测试：
#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.`boyfriend_id` = bo.`id` WHERE b.`id`>3;
#二、查询哪个城市没有部门
SELECT city FROM departments d RIGHT OUTER JOIN locations l ON d.`location_id`=l.`location_id` WHERE d.`department_id` IS NULL;
#三、查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name,d.`department_id` FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` WHERE d.`department_name` IN('SAL','IT');
