第四章 sql多表查询

多表查询

多表查询-----两种方式（有无WHERE条件）

1.无WHERE条件的笛卡尔积方式

1.例题

1.1 创建表t1,并插入记录

CREATE TANLE t1(name VARCHAR(10));
INSERT INTO t1 VALUES('A1'),('A2'),('A3');

结果：

1.2创建表t2,并插入记录

CREATE TANLE t2(name VARCHAR(10));
INSERT INTO t2 VALUES('B1'),('B2');

结果：

多表查询：

 SELECT * FROM t1,t2;

结果：

2.有WHERE条件的普通多表查询方式

2.例题

2.1查询省市的信息

SELECT province.p_name,city.c_name FROM province,city
WHERE province.p_id=city.cfather_id;

2.2查询省市县的信息

SELECT province.p_name,city.c_name,county.cou_name FROM province,city,county
WHERE province.p_id=city.cfather_id AND city.c_id=county.coufather_id;

2.3查询每门课的间接先修课

SELECT A.Cno,B.Cpno FROM course AS A,course AS B
WHERE A.Cpno= B.Cno;

3.连接查询——内连接

3.内连接例题

3.1查询省市的信息

SELECT t1.p_name t2.c_name FROM province t1
	INNER JOIN city t2
		ON t1.p_id=t2.cfather_id;

3.2查询省市县的信息

SELECT t1.p_name t2.c_name t3.cou_name FROM province t1
	INNER JOIN city t2
		ON t1.p_id=t2.cfather_id
	INNER JOIN county t3
		ON t2.c_id=t3.coufather_id;

4.连接查询——外连接

4.1左连接例题

4.1.1查询省市的信息

SELECT t1.p_name t2.c_name FROM province t1
	LEFT JOIN city t2
		ON t1.p_id=t2.cfather_id;

4.1.2查询省市县的信息

SELECT t1.p_name t2.c_name t3.cou_name FROM province t1
	LEFT JOIN city t2
		ON t1.p_id=t2.cfather_id
	LEFT JOIN county t3
		ON t2.c_id=t3.coufather_id;

4.2右链接例题

4.2.1查询省市的信息

SELECT t1.p_name t2.c_name FROM province t1
	RIGHT JOIN city t2
		ON t1.p_id=t2.cfather_id;

4.2.2查询省市县的信息

SELECT t1.p_name t2.c_name t3.cou_name FROM province t1
	RIGHT JOIN city t2
		ON t1.p_id=t2.cfather_id
	RIGHT JOIN county t3
		ON t2.c_id=t3.coufather_id;

5.嵌套查询——比较运算符——分步实现思想

5.1带有比较运算符例题

5.1.1 查询攻击力小于平均攻击力的英雄和攻击值

SELECT name,attack FROM hero
WHERE attack<(SELECT AVG(attack) FROM hero);

结果：
5.1.2查询与“刘晨”在同一个系的学生

SELECT * FROM student
WHERE sdept=(SELECT sdept FROM student WHERE sname="刘晨")

结果：

6.嵌套查询——IN谓词的子查询

6.1带有IN谓词的子查询例题

6.1.1查询所有选修了2号课程的学生姓名

# 由于姓名没有和选修课程没有在同一个表中，因此先找到所有选修了2号课程的学号
SELECT sno FROM score
	WHERE cno='2';

SELECT sname FROM student
	WHERE sno in ('202015121','202015122')
#然后将两句结合到的如下：             		 
SELECT sname FROM student
	WHERE sno in (SELECT sno FROM score
             		 WHERE cno='2')

结果：

6.1.2查询选修了课程名为‘信息系统’的学生学号和姓名

SELECT sno,cname FROM student
WHERE son IN(SELECT son FROM score
						WHERE con=(SELECT con FROM course
											WHERE cname="信息系统"));

结果：

6.1.3查询每个国家中攻击力最高的英雄的名字、攻击值、国家（hero表）

SELECT country MAX(attack) FROM hero
GROUP BY country
#结果：
#country 	MAX(attack）
#吴国    	        110
#蜀国    	       1005
#魏国    	        666
SELECT name,attack,country FROM hero
	WHERE (country,attack) IN( SELECT country MAX(attack) FROM hero
													GROUP BY country );

结果：

7.嵌套查询——带有ANY或ALL子查询

注：用聚合函数实现子查询通常比直接用ANY或ALL的查询效率高

7.1例题

1.查询其他系中比计算机科学系某一学生年龄小的学生姓名个年龄
解法一：

#首先查询计算机系所有学生的年龄
SELECT sage FROM student
WHERE sdept="CS";

#然后查询不是计算机系且年龄小于计算机系某一学生的人。
```python
SELECT sname,sage FROM student
WHERE sdept!="CS", AND sage< ANY(SELECT sage FROM student
														WHERE sdept="CS");

解法二：

SELECT sname,sage FROM student
WHERE sdept!="CS", AND sage< (SELECT MAX(sage) FROM student
														WHERE sdept="CS");
# 注意：聚合语句必须在 SELECT 和 FROM 之间

2.查询其他系中比计算机科学系所有学生年龄小的学生姓名个年龄
解法一：

#首先查询计算机系所有学生的年龄
SELECT sage FROM student
WHERE sdept="CS";

#然后查询不是计算机系且年龄小于计算机系某一学生的人。
```python
SELECT sname,sage FROM student
WHERE sdept!="CS", AND sage< ALL(SELECT sage FROM student
														WHERE sdept="CS");

解法二：

SELECT sname,sage FROM student
WHERE sdept!="CS", AND sage< (SELECT MIN(sage) FROM student
														WHERE sdept="CS");
# 注意：聚合语句必须在 SELECT 和 FROM 之间

8.嵌套查询——带有EXISTS的查询

8.1例题

1.查询所有选修了1号课程的学生姓名

#这里是相关子查询
SELECT sname FROM student
WHERE EXISTS
			(SELECT * FROM socre
			WHERE score.sno=student.sno AND score.cno='1');

2.查询没有选修1号课程的学生姓名

#这里是相关子查询
SELECT sname FROM student
WHERE EXISTS
			(SELECT * FROM socre
			WHERE score.sno=student.sno AND score.cno!='1');

9.集合查询