sql语句学习04-多表查询

一，连接查询

1.等值查询 =

-- 1.等值查询 =
-- 查询每个学生的信息和选修课程的信息
-- stu sc
	SELECT stu.*,sc.* FROM stu,sc WHERE stu.sno=sc.sno;
-- 查询选修‘20201’学生的姓名sname
	SELECT sname FROM stu,sc WHERE stu.sno=sc.sno AND sc.cno='20201';
-- 多表查询
-- 查询每个学生的信息和选修课程的信息和学时
	SELECT stu.*,sc.*,ctime FROM stu,sc,cou WHERE stu.sno=sc.sno AND sc.cno=cou.cno;

2.左外连接 LEFT OUTER JOIN ON

-- 2.左外连接 LEFT OUTER JOIN ON
-- 查询所有的学生信息和选课信息，但是没有选修的学生也要显示出来
	SELECT stu.*,sc.* FROM stu LEFT OUTER JOIN sc ON stu.sno=sc.sno;
-- 查询每个专业的学生人数，假设每个专业都有人
	SELECT mno,COUNT(sno) FROM stu GROUP BY mno HAVING mno BETWEEN 1 AND 4;
	INSERT INTO major VALUES(5,'aa');
-- 查询每个专业的学生人数，但是有的专业可能没有人
	SELECT major.mno,COUNT(sno) FROM major LEFT OUTER JOIN stu ON major.mno=stu.mno GROUP BY major.mno;

二，嵌套查询

1.不相关嵌套查询（子查询不依赖父查询）

-- 1.不相关嵌套查询（子查询不依赖父查询）
-- 查询选修‘20201’学生的姓名sname
	SELECT sname FROM stu WHERE sno IN (SELECT sno FROM sc WHERE cno='20201');
-- 查询选修‘20202’学生的姓名sname
-- IN （返回多个结果）           =（返回一个结果）  建议使用in
	SELECT sname FROM stu WHERE sno IN (SELECT sno FROM sc WHERE cno='20202');
	SELECT sname FROM stu WHERE sno = (SELECT sno FROM sc WHERE cno='20202');

2.相关嵌套查询（将连接放在子查询里）

-- 2.相关嵌套查询（将连接放在子查询里）
-- 查询选修‘20201’学生的姓名sname
	SELECT sname FROM stu WHERE '20201' IN (SELECT cno FROM sc WHERE stu.sno=sc.sno);
-- 查询选择‘C语言’课程的学生学号
	SELECT sno FROM sc WHERE 'C语言' IN (SELECT cname FROM cou WHERE sc.cno=cou.cno)
-- 查询每个学生超过他平均分的课程号，第二种方法用派生表实现
	SELECT sno,AVG(grade) FROM sc GROUP BY sno
	SELECT sno,cno FROM sc x WHERE grade > (SELECT AVG(grade) FROM sc y GROUP BY sno HAVING x.sno=y.sno)
	SELECT sno,cno FROM sc,(SELECT sno,AVG(grade) FROM sc GROUP BY sno) as avg_sc(avg_sno,avg_grade) WHERE sc.sno=avg_sc.avg_sno AND sc.grade >avg_sc.avg_grade

三，带有exists 查询

-- 带有exists 查询（涉及两个表也需要连接）
-- 查询选修‘20201’学生的姓名sname
-- 返回 true or false，每次取一个sno连接
	SELECT sname FROM stu WHERE EXISTS(SELECT * FROM sc WHERE stu.sno=sc.sno AND cno='20201')

四，集合查询

intersect交,且

-- 查询年龄是18且mno=1的学生学号 intersect
-- SELECT sno FROM stu WHERE age=18 AND mno=1;
	SELECT sno FROM stu WHERE age=18 intersect SELECT sno FROM stu WHERE mno=1
-- 查询选修‘20201’号课程且‘20203’的学生学号
	SELECT DISTINCT sno FROM sc WHERE cno='20201' intersect SELECT DISTINCT sno FROM sc WHERE cno='20203'

except差

-- 查询年龄是18且mno=1的学生学号 except
	SELECT sno FROM stu WHERE age=18 except SELECT sno FROM stu WHERE mno!=1;

union并,或

-- 查询选修‘20201’号课程或‘20203’的学生学号 UNION 或
-- SELECT DISTINCT sno FROM sc WHERE cno IN ('20201','20203')
	SELECT sno FROM sc WHERE cno='20201' UNION SELECT sno FROM sc WHERE cno='20203'