MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

MySQL—关联（连接）查询

知识大纲

概述
笛卡尔积
关联条件
内连接 [INNER JOIN]
外连接 [OUTER JOIN]
左连接 [LEFT JOIN] 右外
连接 [RIGHT JOIN]
外连接 [FULL JOIN]
自连接

1.概述

在这里插入图片描述

关联查询一共有几种情况：

内连接：INNER JOIN 、CROSS JOIN
外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
自连接：当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义

说明：

连接 n 个表,至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分
当如果表名比较长时，可以给表取别名，简化 SQL 语句

在这里插入图片描述

示例

1#连接[关联]查询
2
3DROP TABLE IF EXISTS souce;
4DROP TABLE IF EXISTS student;
5
6#学生表
7CREATE TABLE student
8(
9		s_id VARCHAR(20),
10		name VARCHAR(20),
11		sex CHAR(2),
12		age INT,
13		PRIMARY KEY(s_id)
14);
15INSERT INTO student VALUES
16	('A1001','张三','男',15),
17	('A1002','李四','女',16),
18	('B1001','王五','男',17),
19	('C1001','赵六','女',18);
20
21#成绩表
22CREATE TABLE souce
23(
24		t_id VARCHAR(20),
25		C FLOAT,
26		MySQL FLOAT,
27		Java FLOAT,
28		s_id VARCHAR(20),
29		PRIMARY KEY(t_id),
30		FOREIGN KEY(s_id) REFERENCES student(s_id) ON UPDATE CASCADE
31);
32INSERT INTO souce VALUES
33	('T001',66,77,88,'A1001'),
34	('T002',65,75,85,'A1002'),
35	('T003',84,85,86,'B1001');
36
37SELECT * FROM student;
38SELECT * FROM souce;
39
40#查询每一个参加了考试的同学 的三科测试成绩[学生学号、学生姓名、C、MySQL、Java]
41SELECT student.s_id,name,C,MySQL,Java FROM student,souce 
42	WHERE student.s_id=souce.s_id;
43#使用别名 简化查询语句中表名的录入
44SELECT st.s_id,name,C,MySQL,Java FROM student st,souce s 
45	WHERE st.s_id=s.s_id;
46
47#查询每一个所有同学 的三科测试成绩[学生学号、学生姓名、C、MySQL、Java]
48SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
49	ON st.s_id=s.s_id;

2.笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数.

在 MySQL 中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确注：外连接必须写关联条件，否则报语法错误

示例

1#笛卡尔积 [原因：没有提供关联条件 或 关联条件失效]
2SELECT name,Java FROM student,souce;
3SELECT name,Java FROM student INNER JOIN souce;
4SELECT name,Java FROM student JOIN souce;
5SELECT name,Java FROM student CROSS JOIN souce;

3.关联条件

表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询
ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到 WHERE 中和其他条件一起写，但分开写可读性更好
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

示例

1#WHERE [关联条件 AND 查询条件]
2SELECT student.s_id,name,C,MySQL,Java FROM student,souce 
3	WHERE student.s_id=souce.s_id AND student.s_id='A1001';
4#JOIN ON [关联条件]
5SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
6	ON st.s_id=s.s_id;
7#USING [关联条件]
8SELECT st.s_id,name,C,MySQL,Java FROM student st LEFT JOIN souce s 
9	USING(s_id);

4.内连接

有两种格式，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行

隐式：

SELECT [cols_list] from 表 1,表 2 where [condition]

显式：

1SELECT [cols_list] from 表 1 INNER JOIN 表 2 ON [关联条件] where [其他筛选条件]
2SELECT [cols_list] from 表 1 CROSS JOIN 表 2 ON [关联条件] where [其他筛选条件]
3SELECT [cols_list] from 表 1 JOIN 表 2 ON [关联条件] where [其他筛选条件]

在这里插入图片描述

示例

1#查询员工姓名，基本工资，部门名称
2#隐式内连查询
3SELECT ename,basic_salary,dname FROM t_employee e,t_department d,t_salary s
4	WHERE e.dept_id=d.did AND e.eid=s.eid;
5
6SELECT ename,basic_salary,dname FROM t_employee e,t_department d,t_salary s
7	WHERE e.dept_id=d.did AND e.eid=s.eid AND ename='孙红雷';
8
9#隐式内连查询
10SELECT ename,basic_salary,dname FROM t_employee e INNER JOIN t_department d
11	INNER JOIN t_salary s
12	ON e.dept_id=d.did AND e.eid=s.eid;
13
14SELECT ename,basic_salary,dname FROM t_employee e JOIN t_department d
15	INNER JOIN t_salary s
16	ON e.dept_id=d.did AND e.eid=s.eid;
17
18SELECT ename,basic_salary,dname FROM t_employee e 
19	CROSS JOIN t_department d
20	INNER JOIN t_salary s
21	ON e.dept_id=d.did AND e.eid=s.eid
22	WHERE ename='孙红雷';

5.外连接

外连接分为：左外连接（LEFT OUTER JOIN），简称左连接（LEFT JOIN）

右外连接（RIGHT OUTER JOIN），简称右连接（RIGHT JOIN）

全外连接（FULL OUTER JOIN），简称全连接（FULL JOIN）。

6.左连接

在这里插入图片描述

7.右外连接

在这里插入图片描述

示例

1#左连接
2#查询所有部门信息以及该部门员工信息
3SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
4	ON d.did=e.dept_id;
5
6SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
7	ON d.did=e.dept_id;
8
9SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
10	ON d.did=e.dept_id;
11
12UPDATE t_employee SET dept_id=NULL where eid=5;
13
14SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
15	ON d.did=e.dept_id;
16	WHERE e.dept_id IS NULL;
17#查询部门信息，保留没有员工的部门信息
18SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
19	ON d.did=e.dept_id;
20	WHERE e.dept_id IS NULL;
21
22SELECT did,dname,eid,ename FROM t_employee e LEFT JOIN t_department d
23	ON d.did=e.dept_id;
24	WHERE e.dept_id IS NULL;
25	
26#查询所有员工信息，以及员工的部门信息
27SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
28	ON e.dept_id=d.did;
29
30SELECT eid,ename,did,dname FROM t_department d LEFT JOIN t_employee e
31	ON e.dept_id=d.did;
32
33#查询员工信息，仅保留没有分配部门的员工
34SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
35	ON e.dept_id=d.did
36	WHERE e.dept_id IS NULL;
37#查询员工信息，不保留没有分配部门的员工	
38SELECT eid,ename,did,dname FROM t_employee e LEFT JOIN t_department d 
39	ON e.dept_id=d.did
40	WHERE e.dept_id IS NOT NULL;
41
42SELECT id,ename,tid,dname FROM student_info s LEFT JOIN student_test t
43	ON s.id_id=t.tid
44	WHERE t.tid IS NOT NULL;
45
46SELECT * FROM student;
47SELECT * FROM souce;
48#分析 左右外连接
49#显示所有学生的测试信息
50SELECT st.s_id,name,C,Java FROM student st LEFT JOIN souce s
51	ON st.s_id=s.s_id;
52
53#显示参加考试的学生的测试信息
54SELECT st.s_id,name,C,Java FROM student st RIGHT JOIN souce s
55	ON st.s_id=s.s_id;

8.外连接

mysql 不支持 FULL JOIN，但是可以用 left join union right join 代替

在这里插入图片描述

示例

1#使用UNION 实现MySQL中不支持的FULL JOIN操作[完全外连接]
2#查询所有部门信息和员工信息
3SELECT did,dname,eid,ename FROM t_department d LEFT JOIN t_employee e
4	ON d.did=e.dept_id
5	UNION #联合查询 [整合多个查询]
6SELECT did,dname,eid,ename FROM t_department d RIGHT JOIN t_employee e
7	ON d.did=e.dept_id;

9.自连接

当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

1#自连接
2#查询员工姓名以及领导姓名，仅显示有领导的员工[自连接 内连]
3SELECT e1.ename,e2.ename FROM t_employee e1, t_employee e2
4	WHERE e1.mid=e2.eid;
5
6SELECT e1.ename,e2.ename FROM t_employee e1 INNER JOIN t_employee e2
7	ON e1.mid=e2.eid;
8
9#查询所有员工姓名及其领导姓名[自连接 外连]
10SELECT e1.ename,e2.ename FROM t_employee e1 LEFT JOIN t_employee e2
11	ON e1.mid=e2.eid;
12SELECT e1.ename,e2.ename FROM t_employee e1 RIGHT JOIN t_employee e2
13	ON e1.mid=e2.eid;
14	
15#若涉及到多表连接查询 应使中间表连接居中
16#book JOIN readbook ON xxx=xxx JOIN stuinfo ON xxx=xxx
17#查询哪一本书[b_name] 在 什么时间[r_time] 被 何人所借[s_name]
18SELECT b.b_name,r.r_time,s.s_name FROM book b JOIN readbook r ON b.b_id=r.b_id
19	JOIN stuinfo s ON s.s_id=r.s_id;

MySQL—关联（连接）查询（笛卡尔积、内连接、外连接、左连接、右外连接、外连接、自连接）

MySQL—关联（连接）查询

知识大纲

1.概述

示例

2.笛卡尔积

示例

3.关联条件

示例

4.内连接

示例

5.外连接

6.左连接

7.右外连接

示例

8.外连接

示例

9.自连接

评论区

相关推荐