#进阶6 连接查询
/*
含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡尔乘积：表1 有m行， 表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外+右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
*/
SELECT NAME, boy_name FROM beauty, bosy WHERE beauty.boyfriend_id = boys.id;
#一、sql92标准
#1、等值连接
/*
1、夺表连接的部分为夺表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有子句使用，比如筛选、分组、排序
*/
#案例1：查询女神名和对应的男神名
SELECT NAME, boy_name FROM beauty, bosy WHERE beauty.boyfriend_id = boys.id;
#案例2：查询员工名对应的部门名
SELECT `last_name`, `department_name` FROM `departments`AS d, `employees` AS e
WHERE d.`department_id` = e.`department_id`;
#2、为表起别名
/*
1、提高语句简洁度
2、区分多个重名的字段
注意：如果表起了别名，则查询的字段就不能用原来的表名查询
*/
#查询员工名、工种号、工种名
SELECT `last_name`, e.`job_id`, `job_title` FROM `employees` AS e, `jobs` AS j
WHERE e.`job_id` = j.`job_id`;
#3、两个表的顺序是否可以调换
# 答：可以条换
#4、可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT `last_name`, `department_name`,`commission_pct` FROM `employees` AS e, `departments` d
WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL;
#案例2：查询城市名中第二个字符为 o的部门名和城市名
SELECT `department_name`, `city` FROM `locations` AS l, `departments` AS d
WHERE l.`location_id` = d.`location_id` AND l.`city` LIKE '_o%';
#5、 可以加分组？
#案例1：查询每个城市的部门个数
SELECT `city`, COUNT(*) FROM `departments` AS d, `locations` AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.`department_name`, d.`manager_id`
FROM `departments` AS d , `employees` AS e
WHERE d.`department_id`= e.`department_id` AND `commission_pct` IS NOT NULL
GROUP BY d.`department_name`, d.`manager_id`;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title`, COUNT(*) FROM `employees` AS e, `jobs` AS j
WHERE e.`job_id`=j.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;
#7、可以实现三表连接
#案例，查询员工名，部门名，所在的城市 按工种倒序排序
SELECT `last_name`, `department_name`, `city`
FROM `employees`AS e, `locations`AS l, `departments`AS d
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`
ORDER BY `job_id` DESC;
#2、非等值连接
#案例1：查询员工的工资和工资级别为 A的
SELECT `salary`, `grade_level` FROM `employees` AS e, `job_grades` AS j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND j.`grade_level` = 'A';
#3、自连接
#案例：查询 员工名和上级的名称
SELECT e.`employee_id`, e.`last_name`, ee.`manager_id`, ee.`last_name`
FROM `employees` AS e, `employees` AS ee
WHERE e.`manager_id`=ee.`employee_id`
#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by】
【having 筛选条件】
【order by排序条件】
分类：
内连接： inner
外连接
左外：left【outer】
右外：right【outer】
全外：full【outer】
交叉连接：cross
*/
#——> 内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
分类：
等值
非等值
自连接
特点：
1、添加排序分组筛选
2、inner 可以省略
3、筛选条件放在where后，连接条件放在on后，提高分离性，便于阅读
4、inner join连接和sql92语法中的等值连接效果是一样的，都是查询交集部分
*/
#1、等值连接
#案例1、查询员工名、部门名
SELECT `last_name`, `department_name`
FROM `employees` e, `departments` d
WHERE e.`department_id`=d.`department_id`
SELECT `last_name`, `department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
SELECT `last_name`, `department_name`
FROM `employees` e
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 `last_name` LIKE '%e%'
#案例3、查询部门个数>3的城市名和部门个数（添加分组+筛选）
SELECT `city`, COUNT(*) c FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id`=d.`location_id`
GROUP BY `city`
HAVING c>3;
#案例4、查询那个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT `department_name`, COUNT(*) AS 个数 FROM `departments` AS d
INNER JOIN `employees` AS e
ON d.`department_id`=e.`department_id`
GROUP BY `department_name`
HAVING 个数>3
ORDER BY 个数 DESC;
#案例5、查询员工名、部门名、工种名，并按部门名降序
SELECT `last_name`, `job_title`, `department_name` FROM
`employees` AS e
INNER JOIN `departments` AS d ON d.`department_id`=e.`department_id`
INNER JOIN`jobs` AS j ON e.`job_id`=j.`job_id`
ORDER BY d.`department_name` DESC;
SELECT `last_name`, `job_title`, `department_name` FROM
`departments` AS d
INNER JOIN `employees` AS e ON d.`department_id`=e.`department_id`
INNER JOIN`jobs` AS j ON e.`job_id`=j.`job_id`
ORDER BY d.`department_name` DESC;
#二 非等值连接
#查询员工的工资级别
SELECT e.`last_name`,`grade_level` FROM `job_grades` j INNER JOIN `employees` e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#查询工资级别的个数大于20的个数，并且进行按工资级别降序
SELECT j.`grade_level`, COUNT(*) AS c FROM `job_grades` j
INNER JOIN `employees` e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY j.`grade_level` HAVING c>20 ORDER BY c DESC;
#三、自连接
#查询员工的名字和上级的名字
SELECT e1.`last_name`, e2.`last_name` FROM `employees` e1
INNER JOIN `employees` e2
ON e1.`manager_id`=e2.`employee_id`;
#查询姓名中包含字符k的员工的名字和上级的名字
SELECT e1.`last_name`, e2.`last_name` FROM `employees` e1
INNER JOIN `employees` e2
ON e1.`manager_id`=e2.`employee_id`
WHERE e1.`last_name` LIKE '%k%';
#er、外连接
/*
应用场景：用于查询一个表上有，另一个表里没有的场景
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和他匹配的，则显示null
外连接查询结果=内连接结果+主表中有 而 从表中没有的
2、左外连接： left join左表是主表
右外连接： right join右表是从表
3、左外和右外交换两个表的顺序，可以实现同样的结果
4、全外连接= 内连接的结果+表1中有但表2中没有+表2中有表1中没有的结果
5、交叉连接 实际就是一个笛卡尔乘积，两个表全排列组合
*/
# 案例1： 查询那个部门没有员工
#左外
SELECT e.`last_name`, e.`department_id`FROM `employees` AS e
LEFT OUTER JOIN `departments` AS d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
SELECT e.`last_name`, e.`department_id`FROM `departments` AS d
RIGHT OUTER JOIN `employees` AS e
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
#全外
SELECT a.*, b.* FROM a FULL OUTER JOIN b ON b.'boy'= a.'boy';
#交叉连接
SELECT b.*, a.*
FROM a CROSS JOIN b;
#sql92 pk sql99
功能： sql99 支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性高
#2 列子查询（多行子查询）
#1、查询location_id是1400的部门所有员工的姓名
SELECT `last_name` FROM `employees`
WHERE `department_id` = (SELECT `department_id` FROM `departments` WHERE `location_id` = 1400);
#2、返回location_id是1400 或 1700 的部门所有员工的姓名
SELECT `last_name` FROM `employees`
WHERE `department_id` IN (SELECT `department_id` FROM `departments` WHERE `location_id` IN (1400, 1700));
#案例2：返回其他工种中比job_id为’IT_PROG‘ 工种任一工资低的员工的员工号、姓名、job_id 以及 salary
SELECT `employee_id`, `last_name`, `job_id`, `salary` FROM `employees`
WHERE `job_id` <> 'IT_PROG' AND `salary` < (SELECT MAX(salary) FROM `employees` WHERE `job_id`='IT_PROG');
SELECT `employee_id`, `last_name`, `job_id`, `salary` FROM `employees`
WHERE `job_id` <> 'IT_PROG' AND `salary` < ANY(SELECT salary FROM `employees` WHERE `job_id`='IT_PROG');
#案例3：返回其他部门中比job_id为’IT_PROG‘ 部门所有工资低的员工的员工号、姓名、job_id 以及 salary
SELECT `employee_id`, `last_name`, `job_id`, `salary` FROM `employees`
WHERE `job_id` <> 'IT_PROG' AND `salary` < ALL(SELECT salary FROM `employees` WHERE `job_id`='IT_PROG');
SELECT `employee_id`, `last_name`, `job_id`, `salary` FROM `employees`
WHERE `job_id` <> 'IT_PROG' AND `salary` < (SELECT MIN(salary) FROM `employees` WHERE `job_id`='IT_PROG');
#案例4：查询每个部门的员工个数
SELECT c.`department_name`, COUNT(*) cc
FROM (SELECT d.`department_name`, e.`last_name` FROM `departments` d INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`) c
GROUP BY `department_name` ORDER BY cc;
SELECT `department_id`, COUNT(*) cc FROM `employees` GROUP BY `department_id` ORDER BY cc;
#四、exists后面
# exixts 查找是否存在 （结果：1或0）
SELECT EXISTS (SELECT `employee_id` FROM `employees`);
#进阶9：联合查询
/*
union 联合，合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
查询语句3
......
应用场景：
要查询的结果来自多个表，且多个表没有直接的关系，但查询信息一致时
特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all可以包含重复项
*/
# 引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM `employees` WHERE `email` LIKE '%a%' OR `department_id`>90;
SELECT * FROM `employees` WHERE `email` LIKE '%a%'
UNION
SELECT * FROM `employees` WHERE `department_id`>90;
浙公网安备 33010602011771号