连接查询sql199
多表查询的条件不能少于表数-1，不然就会出现笛卡尔积
语法:
SELECT 查询列表
FROM 表1 别名
【连接类型】 JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【group by 分组 】
【having 筛选条件】
【order by 排序列表】
分类：
内连接：inner,只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.
外连接：
- 左外：left 【outer】
- 右外：right 【outer】
- 全外：full 【outer】
交叉连接： cross
内连接INNER JOIN
语法：
SELECT 查询列表
FROM 表1 别名
inner join 表2 别名
on 连接条件：
分类:
- 等值
- 非等职
- 自连接
等值连接
1.查询 员工名，部门名
SELECT last_name,department_name FROM employees p1 INNER JOIN departments p2 ON p1.`department_id`=p2.`department_id`;
结果：
Whalen Adm Hartstein Mar Fay Mar Raphaely Pur Khoo Pur
2.查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title FROM employees p1 INNER JOIN jobs p2 ON p1.`job_id`=p2.`job_id` WHERE p1.`last_name` LIKE '%e%';
结果
De Haan Administration Vice President Ernst Programmer Lorentz Programmer Greenberg Finance Manager Faviet Accountant
3.查询部门个数>3的城市名和部门数
SELECT COUNT(*) 部门个数,city FROM departments p1 INNER JOIN locations p2 ON p1.`location_id`=p2.`location_id` GROUP BY city HAVING COUNT(*)>3
结果
21 Seattle
4.查询 那个部门的部门员e工个数>3的部门名和员工个数，并按个数降序
SELECT COUNT(*) 部门个数,department_name FROM employees p1 INNER JOIN departments p2 ON p1.`department_id`=p2.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER BY 部门个数 DESC
结果
45 Shi 29 Sal 8 Exe 6 Fin 6 Pur 5 IT
5.查询员工名，部门名，工种名，并按部门名降序
SELECT last_name,department_name,job_title FROM employees p1 INNER JOIN departments p2 ON p1.`department_id`=p2.`department_id` INNER JOIN jobs p3 ON p1.`job_id`=p3.`job_id` ORDER BY department_name DESC
查询
Mikkilineni Shi Stock Clerk Dellinger Shi Shipping Clerk Matos Shi Stock Clerk Markle Shi Stock Clerk Chung Shi Shipping Clerk Weiss Shi Stock Manager Atkinson Shi Stock Clerk Gates Shi Shipping Clerk
非等职连接
1.查询员工工资级别
SELECT salary,grade_level FROM employees p1 INNER JOIN job_grades p2 ON p1.`salary` BETWEEN p2.`lowest_sal` AND p2.`highest_sal`
结果
24000 E 17000 E 17000 E 9000 C 6000 C
2.查询每个工资级别的人数>20的人数，并且进行按工资级别排序
SELECT COUNT(*) 个数,salary,grade_level FROM employees p1 INNER JOIN job_grades p2 ON p1.`salary` BETWEEN p2.`lowest_sal` AND p2.`highest_sal` GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;
结果
38 9000 C 26 4800 B 24 2900 A
自连接，用在一张表当做2张表使用的时候
1.查询员工的名字，上级的名字
SELECT p1.last_name, p2.last_name FROM employees p1 INNER JOIN employees p2 ON p1.`manager_id`=p2.`manager_id` WHERE p1.`last_name` LIKE '%k%'
结果
Kochhar Kochhar Kaufling Kochhar Zlotkey Kochhar Kochhar De Haan Kaufling De Haan Zlotkey De Haan Kochhar Raphaely Kaufling Raphaely Zlotkey Raphaely
外连接
应用场景：用于查询一个表中有，另一个表中没有的场景
特点
- 1.外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示null
- 外连接查询结果=内连接结果+住表中有而从表没有的记录
- 2.主表从表的划分
- 左外连接，left join左边的是主表
- 右外连接，right join右边的是 住表
- 3.左外和右外交换两个表的顺序，可以实现同样的效果
- 4.全外连接=内连接的结果+表1中有但表2在没有的+表二中有但表1没有的
1.查询一下男朋友不在boys表里的girl名
SELECT p1.name,p2.* FROM beauty p1 LEFT OUTER JOIN boys p2 ON p1.`boyfriend_id`=p2.`id`
结果
周芷若 1 张无忌 100 小昭 1 张无忌 100 赵敏 1 张无忌 100 热巴 2 鹿晗 800 Angelababy 3 黄晓明 50 王语嫣 4 段誉 300 柳岩 null null null 苍老师 null null null 周冬雨 null null null 岳灵珊 null null null 双儿 null null null 夏雪 null null null
1.查询编号>3的女神的男朋友信息，如果有则列出详情，如果没有，用NULL填充
SELECT p1.*,p2.boyName FROM beauty p1 LEFT OUTER JOIN boys p2 ON p2.`id`=p1.`boyfriend_id` WHERE p1.id>3 ORDER BY p1.id DESC
结果
12 赵敏 女 1992-02-03 00:00:00 18209179577 1 张无忌 11 夏雪 女 1993-02-03 00:00:00 18209876579 9 null 10 王语嫣 女 1992-02-03 00:00:00 18209179577 4 段誉 9 双儿 女 1993-02-03 00:00:00 18209876579 9 null 8 小昭 女 1989-02-03 00:00:00 18209876567 1 张无忌 7 岳灵珊 女 1987-12-30 00:00:00 18219876577 9 null 6 周芷若 女 1988-02-03 00:00:00 18209876577 1 张无忌 5 周冬雨 女 1992-02-03 00:00:00 18209179577 9 null 4 热巴 女 1993-02-03 00:00:00 18209876579 2 鹿晗
2.查询哪个城市没有部门
SELECT city,p1.* FROM locations p1 LEFT OUTER JOIN departments p2 ON p1.`location_id`=p2.`location_id` WHERE p2.`department_id` IS NULL
结果
Roma 1000 1297 Via Cola di Rie 00989 Roma IT Venice 1100 93091 Calle della Testa 10934 Venice IT Tokyo 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
3.查询部门名为SAL或IT的员工信息
SELECT p2.*,department_name
FROM departments p1
LEFT OUTER JOIN employees p2
ON p1.`department_id`=p2.`department_id`
WHERE p1.`department_name` IN('SAL','IT')结果
继续阅读
我的微信
这是我的微信扫一扫
评论