MySQL连接查询知识总结

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');# 案例:

---

注: 本文档查询使用的数据来源为尚硅谷