MySQL学习记录 - 06-连接查询

最新推荐文章于 2021-12-05 17:41:24 发布

大贝祺的海

最新推荐文章于 2021-12-05 17:41:24 发布

阅读量203

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/Becky_Jia/article/details/108180787

版权

MYSQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

根据尚硅谷的视频教程学习MySQL，学习记录-06-连接查询
所有操作开始之前，建议先打开对应的库：

USE 库名;
USE employees;

=> 分类：
多表查询，当查询字段来自于多个表时，就会用到连接查询

按年份分类：
=>sql92标准: 仅仅支持内连接
=>sql99标准【推荐】：支持内连接+外连接（左外+右外）+交叉连接

按照功能分类：
=>内连接：等值连接、非等值连接、自连接
=>外连接：左外连接、右外连接、全外连接
=>交叉连接

一、sql92标准

=> 语法：

SELECT 查询列表
FROM 表1 别名，表2 别名
WHERE 连接条件 （以及其他筛选条件）

=> 案例：

等值连接

案例1：查询员工名和对应的部门名

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

案例2：查询员工名、工种号、工种名【为表起别名，区分多个重名字段】

SELECT last_name,e.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

但是，起了别名之后，不能识别原始表名称，不能使用原始表名限定

SELECT last_name,employees.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;
结果报错

案例3：【两个表的顺序可以调换】

SELECT last_name,e.job_id, job_title
FROM jobs j, employees e
WHERE e.job_id = j.job_id;

案例4：【添加筛选】查询有奖金的员工名、部门名

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;

案例5：【添加分组】查询每个城市的部门个数

SELECT l.city, COUNT(DISTINCT d.department_id) as num
FROM locations l, departments d
WHERE l.location_id = d.location_id
GROUP BY l.city

案例6：【分组、筛选】查询有奖金的每个部门的部门名、部门领导编号和该部门的最低工资

SELECT department_name, d.manager_id, MIN(salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id

案例7：【添加排序】查询每个工种的工种名、员工个数，按照员工个数降序

SELECT job_title, COUNT(DISTINCT e.employee_id) 个数
FROM jobs j, employees e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY 个数 DESC;

案例8：【实现3个表连接】查询员工名、部门名、所在城市，城市以S开头

SELECT e.last_name, d.department_name, l.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 e.last_name;

等值连接总结：

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面的所有子句一起使用：筛选、分组、排序等

非等值连接

案例1：查询员工的工资和工资级别，按照薪资从高到低排序

SELECT salary, grade 
FROM employees e, sal_grade s
WHERE e.salary >=min_salary AND e.salary < max_salary
ORDER BY salary DESC;
或者使用Between语句
SELECT salary, grade
FROM employees e, sal_grade s
WHERE salary BETWEEN min_salary AND max_salary
ORDER BY salary DESC;

自连接

案例1：查询员工名、上级的名称

SELECT e.last_name, e.manager_id, m.last_name as manager
FROM employees e, employees m
WHERE e.manager_id = m.employee_id

小节练习
练习1：查询90号部门员工的job_id 和 90号部门的location_id;

SELECT DISTINCT job_id, location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90;

练习2：选择所有有奖金的员工的名字、部门名、location_id和城市

SELECT last_name, department_name, l.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
AND commission_pct IS NOT NULL;

练习3：选择city在Toronto工作的员工的名字、工种、部门编号、部门名称

SELECT last_name, job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
AND city = 'Toronto';

练习4：查询每个国家下的部门个数大于2的国家编号

SELECT country_id, COUNT(*) as 部门个数
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY l.country_id
HAVING COUNT(*)>2;

练习5：查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name, job_title, MIN(salary)
FROM jobs j, employees e,departments d
WHERE j.job_id = e.job_id AND e.department_id = d.department_id
GROUP BY d.department_name, e.job_id;

练习6：选择指定员工的姓名、员工号，以及对应管理者的姓名和员工号，员工姓名为 kochhar 101

SELECT e.last_name employee, e.employee_id as "Emp#",
m.last_name manager, m.employee_id as "Mgr#"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar'

二、sql99标准

=> 语法：

SELECT 查询列表
FROM 表1 别名 【连接类型】 
JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件 GROUP BY 分组 HAVING 筛选 ORDER BY 排序】

连接类型：inner join / left join / right join / full join / cross (交叉连接）

=> 案例：

内连接 - 等值连接

案例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 last_name LIKE '%e%';

案例3：【添加分组、筛选】查询部门个数大于3的城市名、部门个数

SELECT l.city, COUNT(*) as 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY l.city
HAVING count(*)>3;

案例4：【添加排序】查询哪个部门的员工个数>3的部门名、员工个数，并按照员工个数降序

SELECT department_name, count(*)
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name
HAVING count(*) >3
ORDER BY count(*)  DESC;

案例5：【3表连接】查询员工名、部门名、工种名，并按部门名降序

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;

内连接 - 非等值连接

特点

可以添加排序、分组、筛选
inner可以省略
筛选条件放在WHERE后，连接语句在ON后
INNER JOIN连接和92语法中的等值连接一样，查询交集结果

案例1：查询员工的工资级别

SELECT salary, grade
FROM employees e
INNER JOIN sal_grade
ON e.salary BETWEEN min_salary AND max_salary;

案例2：查询每个工资级别个数大于3的个数，并且按照工资级别降序

SELECT grade, count(*)
FROM employees e
INNER JOIN sal_grade
ON e.salary BETWEEN min_salary AND max_salary
GROUP BY grade
HAVING count(*) > 3
ORDER BY grade DESC;

内连接 - 自连接

案例1：查询姓名中包含K的员工名、上级的名称

SELECT e.last_name, m.last_name as manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

外连接：查询一个表中有，另外一个表中没有的记录

外连接的查询结果为主表中的所有记录
如果从表中有和主表匹配的，则显示匹配值
如果从表中没有和主表匹配的，则显示NULL
外连接查询结果 = 内连接的结果 + 主表有而从表没有的记录
左外链接：LEFT JOIN左侧的是主表
右外连接：RIGHT JOIN 右侧的是主表
左外和右外交换两个表的顺序，可以实现同样的效果。
全外连接查询结果 = 内连接的结果 +表1有但是表2没有的+表2有但是表1 没有的

案例1：查询男朋友不在boys表中的女神名

SELECT b.name
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;

案例2：查询哪个部门没有员工

SELECT department_name
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
右外连接
SELECT department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

全外连接 - 目前99sql不支持

SELECT *
FROM beauty b
FULL JOIN boys bo
ON b.boyfriend_id = bo.id

交叉连接 - 笛卡尔积结果

SELECT *
FROM beauty b CROSS JOIN boys bo;

三、总结与习题

功能：sql99 支持更多
可读性：sql99将连接条件与其他筛选条件分开体现，可读性更高

练习1：查询哪个城市没有部门

SELECT city
FROM locations l
LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_id IS NULL

练习2：查询部门名为SAL或IT的员工信息

SELECT d.department_id,d.department_name, e.*
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE department_name IN ('SAL','IT');