MySQL连接查询知识总结
- MySQL连接查询知识总结
- 一、概述
- 1. 连接查询简介
- 2. 笛卡尔积
- 3. 代码标准分类
- 4. 连接类型分类
- 二、SQL 92 标准
- 1. 等值连接
- 要点总结:
- 代码示例:
- 2. 非等值连接
- 要点总结:
- 代码示例:
- 3. 自连接
- 要点总结:
- 代码示例:
- 三、SQL 99 标准
- 1. 基本语法
- 2. 内连接
- (1) 等值连接
- (2) 非等值连接
- (3) 自连接
- 3. 外连接
- (1) 左右外连接
- (2) 全外连接 (不支持)
- 4. 交叉连接
- 5. 多表连接案例
MySQL连接查询知识总结
一、概述
1. 连接查询简介
连接查询又称多表查询。
2. 笛卡尔积
多表多列所有匹配结果得出笛卡尔积, 共m*n行。
3. 代码标准分类
按年代分为:
① sql92标准 (仅支持内连接)
② sql99标准 [推荐] (不支持全外连接)
4. 连接类型分类
按功能分为:
① 内连接 (又分为 等值连接, 非等值连接, 自连接)
② 外连接 (又分为 左外连接, 右外连接, 全外连接)
③ 交叉连接
二、SQL 92 标准
1. 等值连接
要点总结:
① 多表等值连接的结果为多表的交集部分\
② n个表连接至少需要n-1个连接条件
③ 多表的顺序没有要求, 可以交换位置
④ 一般为了方便, 需为表起一个别名
⑤ 可以搭配排序、分组、筛选等子句使用
代码示例:
/* 1. 等值连接 */
# 案例1: 查询女神名和对应的男神名
SELECT
`name`,
boyName
FROM
boys,
beauty
WHERE
beauty.boyfriend_id = boys.id;
# 案例2: 查询员工名对应的部门名
USE myemployees;
SELECT
last_name,
department_name
FROM
employees, departments
WHERE
employees.department_id = departments.department_id;
# 查询员工名、工种号、工种名
USE myemployees;
SELECT
last_name,
employees.job_id,
job_title
FROM
employees,
jobs
WHERE
employees.job_id = jobs.job_id;
#----------------------------------
# 为表起别名法化简代码
# (注意: 使用别名后不允许使用原名字做查询)
USE myemployees;
SELECT
last_name,
e.job_id,
job_title
FROM
employees AS e,
jobs AS j
WHERE
e.job_id = j.job_id;
#----------------------------------
# 加筛选条件
# 案例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%';
#----------------------------------
# 加分组
# 案例1: 查询每个城市的部门个数
SELECT
city,
COUNT(*) 部门个数
FROM
locations l,
departments d
WHERE
l.location_id = d.location_id
GROUP BY
city;
# 案例2: 查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.department_name,
d.manager_id,
MIN(salary)
FROM
departments d,
employees e
WHERE
e.commission_pct IS NOT NULL
AND d.department_id = e.department_id
GROUP BY
d.department_name,
d.manager_id;
#----------------------------------
# 加排序
# 案例1: 查询每个工种的工种名和员工的个数, 并且按员工个数降序
SELECT
job_title, COUNT(*) AS 员工个数
FROM
employees e,
jobs j
WHERE
e.job_id = j.job_id
GROUP BY
job_title
ORDER BY
员工个数 DESC;
#----------------------------------
# 三表查询
# 案例1: 查询员工名、部门名和所在城市
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;
2. 非等值连接
要点总结:
与等值连接区别在于条件不为 ‘=’ 。
代码示例:
/* 非等值连接 */
# 数据准备
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);
SELECT * FROM job_grades;
# 案例: 查询员工的工资和工资级别(并显示出等级为A的)
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';
3. 自连接
要点总结:
等值连接相当于一个特殊的等值连接。
代码示例:
# 案例: 查询员工名和上级的名称
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;
三、SQL 99 标准
1. 基本语法
# 基本语法
SELECT 查询列表
FROM 表1 别名
[连接类型] JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组依据]
[HAVING 筛选条件]
[ORDER BY 排序依据];
# 连接类型
内连接: INNER
外连接:
左外连接: LEFT [OUTER]
右外连接: RIGHT [OUTER]
全外链接: FULL [OUTER]
交叉连接: CROSS
2. 内连接
要点总结:
① INNER可省略
② 连接条件在ON后, 筛选条件放在WHERE后, 提高分离性
③ 三表连接可能出现顺序问题
(1) 等值连接
# 1.查询员工名、部门名(调换位置)
SELECT
first_name,
department_name
FROM
employees e
INNER JOIN
departments d
ON
e.department_id = d.department_id;
# 2.查询名字中包含e的员工名和工种名(筛选)
SELECT
e.first_name,
j.job_title
FROM
employees e
INNER JOIN
jobs j
ON
e.job_id = j.job_id
WHERE
e.first_name LIKE '%e%';
# 3.查询部门个数>3的城市名和部门个数(分组+筛选)
SELECT
city,
COUNT(*)
FROM
locations l
INNER JOIN
departments d
ON
l.location_id = d.location_id
GROUP BY
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.查询员工名、部门名、工种名,并按部门名降序
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
INNER JOIN
job_grades g
ON
e.salary BETWEEN g.lowest_sal AND g.highest_sal;
# 2.查工资级别的个数 >20 的等级, 并且按工资级别降序
SELECT
COUNT(*),
grade_level
FROM
employees e
INNER 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
INNER JOIN
employees m
ON
e.manager_id = m.employee_id
WHERE
e.last_name LIKE '%k%';
3. 外连接
要点总结:
① 用于查询一个表中有一个表中没有的记录
② 可将连接两表分为主从表, 主表记录都会显示出来, 能匹配匹配, 匹配不上显示 NULL
③ 外连接结果 = 内连接结果 + 主表中有但从表中没有的记录
④ 左外连接: LEFT JOIN 左侧的表为主表
右外连接: RIGHT JOIN 右侧的表为主表
⑤ 左外和右外交换两表顺序, 可实现同样的效果
⑥ 全外连接 = 内连接结果 + 表1有但表2没有的 + 表2有但表1没有的
(1) 左右外连接
# 查询男朋友不在男神表的女神名
# 左外连接
SELECT
be.`name`,
bo.*
FROM
beauty be
LEFT OUTER JOIN
boys bo
ON
be.boyfriend_id = bo.id
WHERE
bo.id IS NOT NULL;
# 右外连接
SELECT
be.`name`,
bo.*
FROM
boys bo
LEFT OUTER JOIN
beauty be
ON
be.boyfriend_id = bo.id
WHERE
bo.id IS NOT NULL;
# 案例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;
(2) 全外连接 (不支持)
/* 注意: MySQL不支持 */
USE girls;
SELECT
be.*,
bo.*
FROM
beauty be
FULL OUTER JOIN
boys bo
ON
be.boyfriend_id = bo.id;
4. 交叉连接
/* 注意: 实际上为笛卡尔积 */
SELECT
be.*,
bo.*
FROM
beauty be
CROSS JOIN
boys bo;
5. 多表连接案例
# 案例:
# 1. 查询编号 >3 的女神的男朋友信息, 如果有则列出详细, 如果没有, 用null填充
USE girls;
SELECT
be.`name`,
bo.*
FROM
beauty be
LEFT JOIN
boys bo
ON
be.boyfriend_id = bo.id
WHERE
be.id > 3;
# 2. 查询哪个城市没有部门
USE myemployees;
SELECT
city,
# d.*
FROM
locations l
LEFT JOIN
departments d
ON
l.location_id = d.location_id
WHERE
d.department_id IS NULL;
# 3. 查询部门名为 SAL 或 IT 的员工信息
USE myemployees;
SELECT
e.*,
d.department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.department_id = d.department_id
WHERE
d.department_name IN('SAL', 'IT');# 案例:
注: 本文档查询使用的数据来源为尚硅谷