多表查询和连接查询

一、多表查询（笛卡尔乘积查询）

1. 多表查询的概念

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

SELECT * FROM classes,students

1	一班	1	1	小明	M	90
2	二班	1	1	小明	M	90
3	三班	1	1	小明	M	90
4	四班	1	1	小明	M	90
1	一班	2	1	小红	F	95
2	二班	2	1	小红	F	95
3	三班	2	1	小红	F	95
4	四班	2	1	小红	F	95
1	一班	3	1	小军	M	88
2	二班	3	1	小军	M	88
3	三班	3	1	小军	M	88
4	四班	3	1	小军	M	88
1	一班	4	1	小米	F	73
2	二班	4	1	小米	F	73
3	三班	4	1	小米	F	73
4	四班	4	1	小米	F	73
1	一班	5	2	小白	F	81
2	二班	5	2	小白	F	81
3	三班	5	2	小白	F	81
4	四班	5	2	小白	F	81
1	一班	6	2	小兵	M	55
2	二班	6	2	小兵	M	55
3	三班	6	2	小兵	M	55
4	四班	6	2	小兵	M	55
1	一班	7	2	小林	M	85
2	二班	7	2	小林	M	85
3	三班	7	2	小林	M	85
4	四班	7	2	小林	M	85
1	一班	8	3	小新	F	91
2	二班	8	3	小新	F	91
3	三班	8	3	小新	F	91
4	四班	8	3	小新	F	91
1	一班	9	3	小王	M	89
2	二班	9	3	小王	M	89
3	三班	9	3	小王	M	89
4	四班	9	3	小王	M	89
1	一班	10	3	小丽	F	85
2	二班	10	3	小丽	F	85
3	三班	10	3	小丽	F	85
4	四班	10	3	小丽	F	85


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

2. 使用多表查询千万要注意

正式如同上面这个数据一样，本来很小的两个表，现在变成了40条数据。
结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

3. 重名问题的解决

• 对不同表的相同列明名进行区分，对查询到的列起别名

SELECT classes.id cid,classes.`name` cname,students.id sid from classes,students
来点操作，把东西玩花
1
2

• SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点
  对，你没有看错，我们不仅可以给列起别名，现在还可以给表起别名！

对于这样的写法，我只能说一句舒服了！

SELECT
	c.id cid,
	c.`name` cname,
	s.id sid,
	s.`name` sname
FROM
	classes c,
	students s
1
2
3
4
5
6
7
8

再次体会，SQL语句的执行顺序是从后往前执行的，告诉SQL要查询哪个表，就会把哪个表中的列读取出来

4. 在多表查询中使用条件查询

根据实际情况需要使用就行了

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
1
2
3
4
5
6
7
8
9

5. 小结

• 使用多表查询可以获取M x N行记录；

• 多表查询的结果集可能非常巨大，要小心使用。

---

二、连接查询

1. 基本概念

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

我们希望在结果集同时包含所在的班级名称

2. 解决办法-内连接 INNER-JOIN

SELECT
	s.id,
	s. NAME,
	s.class_id,
	c. NAME class_name,
	s.gender,
	s.score
FROM
	students s
INNER JOIN classes c ON s.class_id = c.id;

1	小明	1	一班	M	90
2	小红	1	一班	F	95
3	小军	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小丽	3	三班	F	85
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3. 内连接查询的写法

内连接的注意事项

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

使用别名不是必须的，但可以更好地简化查询语句。

4. 根据实际情况选择使用

tableA看作左表，把tableB看成右表

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

---
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;


1
2
3
4
5
6
7
8
9
10
11
12

5. 小结

• JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

• INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT … FROM <表1> INNER JOIN <表2> ON <条件…>；

• JOIN查询仍然可以使用WHERE条件和ORDER BY排序。