mysql学习笔记（九）—— 连接查询（sql99）_综合

sql99语法

select	查询列表from	表1 别名 【连接类型】join	表2 别名 on 	连接条件where	筛选条件【group by 分组】【having 筛选条件】【order by 排序列表】

分类：
1.内连接【inner】
2.外连接：
（1）左外：left【outer】
（2）右外：right【outer】
（3）全外：full【outer】
3.交叉连接：cross

**文氏图理解：
在这里插入图片描述
**
*/

一. 内连接

/*
语法：

select	查询列表
from	表1 别名
inner join 表2 别名
on 连接条件;

分类：
（1）等值连接
（2）非等值连接
（3）自连接

#1.等值连接
/*
特点：
（1）可添加排序、分组、筛选
（2）inner可以省略
（3）筛选条件放在where（或having）后，连接条件放在on后，提高了可读性
（4）sql99和sql92在等值连接的效果一样，都是查询多表的交集
*/

案例1:查询员工名、部门名

SELECTlast_name,department_name
FROMemployees e
INNER JOINdepartments d    # employees和departments可以调换位置
ONe.`department_id`=d.`department_id`;

#案例2. 查询名字中包含e的员工名和工种名（筛选）

SELECTlast_name,job_title
FROMemployees e
INNER JOIN	jobs j
ONe.job_id=j.`job_id`
WHERElast_name LIKE ("%e%")

#案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）
/*
分析：
（1）先查询每个城市的部门个数（分组）
（2）再筛选部门个数大于3的的城市
*/

SELECTcity,COUNT(department_id) 部门个数
FROM departments d
INNER JOINlocations l
ONd.location_id=l.location_id
GROUP BYcity     #因为要查询每个城市的部门个数，所以要用到分组
HAVING部门个数>3;

#案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
/*
分析：
（1）先查询每个部门的员工个数（分组）
（2）再筛选员工个数>3的部门
（3）最后按员工个数降序排列
*/

SELECTdepartment_name 部门名,COUNT(employee_id) 员工个数
FROMdepartments d
INNER JOIN	employees e
ON d.`department_id`=e.`department_id`
GROUP BYd.`department_id`
HAVING员工个数>3
ORDER BY员工个数 DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序（三表连接）

SELECTlast_name 员工名,department_name 部门名,job_title 工种名
FROMemployees e     # 因为e可以同时和d、j相连接，所以放在这个位置
INNER JOINdepartments d ON e.`department_id`=d.`department_id`   # JOIN 和 ON跟着
INNER JOINjobs j ON  e.`job_id`=j.`job_id`
ORDER BY部门名 DESC;

#2. 非等值连接

#案例1：查询员工的工资级别以及工资级别个数>20的具体个数，并按级别降序排列
/*
分析：
（1）查询员工的工资级别（非等值）
（2）查询每个工资级别的个数（分组）
（3）筛选个数>20的员工(having)
*/

SELECTCOUNT(grade_level) 工资级别个数,grade_level 工资级别
FROMemployees e
JOINjob_grades g
ONe.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY工资级别
HAVING工资级别个数>20
ORDER BY工资级别 DESC;

#3. 自连接

案例：查询名字中包含"k"的员工的名字和其上级的名字

SELECTe.last_name 员工名,m.last_name 上级名
FROMemployees e
JOINemployees m
ONe.`manager_id`=m.`employee_id`
WHEREe.`last_name` LIKE("%k%");

二. 外连接

/*
外连接适用于：查询一个表中有，另一个表中没有的信息（所以前者为主表，后者为从表）
特点：

外连接的查询结果为主表中的信息，如果从表中有和其匹配的值，则显示该值；如果没有与之匹配的值，则用null填充。
所以：外连接查询的结果 = 内连接结果 + 主表中有从表中没有的记录
左外连接：left join左边的是主表
右外连接：right join右边的是主表
*/

#案例1：查询没有男朋友的女生名
/*
分析：
查看beauty和boys表可以看出，有些女生的男朋友编号在boys表中的boy_id中找不到可匹配的值，所以符合外连接的应用
其中beauty表为主表，boys表为从表
*/
#左外连接

SELECTb.`name`,bo.*
FROMbeauty b
LEFT OUTER JOINboys bo
ONb.`boyfriend_id`=bo.`id`
WHEREbo.`id` IS NULL;

#右外连接

SELECTb.`name`,bo.*
FROMboys bo
RIGHT OUTER JOINbeauty b
ONb.`boyfriend_id`=bo.`id`   # 使boys中不存在的boy_id用null填充
WHEREbo.`id` IS NULL;

#案例2：查询哪个部门没有员工
/*
分析：
查询没有员工的部门，因为在employees表中，每个员工都对应有部门编号，所以在employees表中的department_id
中所有编号都是有员工的，只有在departments中的department_id中有但employees表中的department_id中没有的id
才是没有员工的部门编号。
因此departments为主表，employees为从表
*/

SELECTdepartment_name 部门名,e.`department_id`
FROMdepartments d
LEFT OUTER JOIN	employees e
ONd.`department_id`=e.`department_id`  # 使employees中的部门编号在departments中不存在的那部分用null填充
WHEREe.`department_id` IS NULL;

#3. 全连接（mysql不支持）

三. 交叉连接（笛卡尔乘积）

SELECTb.*,bo.*
FROMbeauty b
CROSS JOINboys bo;

练习

#1.查询编号>3的女生的男朋友信息，如果有则列出详情，如果没有，用null填充
/*
分析：
（1）先筛选出编号>3的女生
（2）再看该女生是否有男朋友（明显是外连接，且beauty作为主表，boys作为从表）
*/

SELECT b.`name`,bo.*
FROMbeauty b
LEFT JOINboys bo
ONb.`boyfriend_id`=bo.`id`
WHEREb.`id`>3;

在这里插入图片描述

#2.查询哪个城市没有部门
/*
分析：
很明显是查询location_id在departments表中没有，但在locations表中存在的location_id对应的城市
所以：locations为主表，departments为从表
*/

SELECT l.city
FROMdepartments d
RIGHT JOINlocations l
ONl.`location_id`=d.`location_id`
WHEREd.`location_id` IS NULL;

#3.查询部门名为SAL或IT的员工信息
/*
分析：
查询员工信息，筛选条件是部门名，应为两表内连接
*/

SELECTe.*,d.`department_name` 部门名
FROMemployees e
JOINdepartments d
ONe.`department_id`=d.`department_id`
WHEREd.`department_name`='SAL' OR d.`department_name`="IT";

连接查询总结：

什么时候该用内连接？什么时候该用外连接？ 以数据库girls举例说明：
下图为beauty表内的信息：
在这里插入图片描述
下图为boys表中的信息：

因为内连接是取交集，所以可以列出有男朋友的女生名（即：beauty下的boyfriend_id字段和boys下的id重合部分）

SELECTb.name 女生名, bo.boyName 男朋友名
FROMbeauty b
JOINboys bo
ONb.`boyfriend_id`=bo.`id`;

在这里插入图片描述
外连接以左外连接为例，如果以beauty为主表，boys为从表（主表中有但从表中没有），则它可以查询出那些在boys表中找不到对应男朋友的女生：

这里我们先查询加入左外连接后，作为从表的boys中的id的变化：

SELECTb.`name` 没有男朋友的女生名, bo.`id`
FROMbeauty b
LEFT JOINboys bo
ONb.`boyfriend_id`=bo.`id`;

在这里插入图片描述
如上图可知，主表boyfriend_id中有，但从表id中没有的部分，会被填充为null，所以，想要查询出没有男朋友的女生，只需要加入bo.id is null的筛选条件即可，如下：