多表查询
多表查询-----两种方式(有无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');