由数据库练习浅析子查询和链接查询

本周我主要学习了mysql的相关概念及SQL、DML、DQL操作语言。下面我将以一个数据库练习为例介绍DQL中的子查询和连接查询。

在结束了本周的学习后，我进行了一个数据库练习，以下是所建的表和相关题目及答案。

1.student表

2.score表

3.teacher表

4.course表

5.grade表

以下是45道练习题及答案（均为亲自完成）

 -- 1.查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM student;

-- 2.查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher; 

-- 3.查询Student表的所有记录。
SELECT * FROM student;

-- 4.查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

-- 5.查询Score表中成绩为85，86或88的记录。
SELECT * FROM score WHERE degree IN(85,86,88);

-- 6.查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class = '95031' OR ssex = '女';

-- 7.以Class降序查询Student表的所有记录。
SELECT * FROM student ORDER BY class DESC;

-- 8.以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM score ORDER BY cno, degree DESC;

-- 9.查询“95031”班的学生人数。
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 10.查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
SELECT sno,cno FROM score WHERE degree = (SELECT MAX(degree) FROM score);

-- 11.查询每门课的平均成绩。
SELECT AVG(degree) FROM score WHERE cno = '3-245';
SELECT AVG(degree) FROM score WHERE cno = '3-105';
SELECT AVG(degree) FROM score WHERE cno = '6-166';
SELECT AVG(degree) FROM score WHERE cno = '9-888';
SELECT cno,AVG(degree) FROM score GROUP BY cno;

-- 12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree) FROM score WHERE cno LIKE '3%' AND cno IN(SELECT cno FROM score GROUP BY sno HAVING COUNT(*) >= 5);

-- 13.查询分数大于70，小于90的Sno列。
SELECT sno FROM score WHERE degree BETWEEN 70 AND 90;

-- 14.查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM student s1,score s2 WHERE s1.sno = s2.sno;

-- 15.查询所有学生的Sno、Cname和Degree列。
SELECT sno,cname,degree FROM score s,course c WHERE s.cno = c.cno;

-- 16.查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree FROM score s1 JOIN student s2 ON s1.sno = s2.sno JOIN course c ON s1.cno = c.cno; 

--  17.查询“95033”班学生的平均分。
SELECT AVG(degree) FROM student s1 JOIN score s2 ON s1.sno = s2.sno WHERE class = '95033';
SELECT AVG(degree) FROM student s1,score s2 WHERE s1.sno = s2.sno AND class = '95033';

-- 18.查询所有同学的Sno、Cno和rank列。
SELECT sno,cno,rank FROM score,grade WHERE s1.sno = s2.sno AND degree WHERE degree BETWEEN low AND upp ORDER BY rank;
SELECT sno,cno,rank FROM score JOIN grade ON degree BETWEEN low AND upp ORDER BY rank;

-- 19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM student s1,score s2 
WHERE  s1.sno = s2.sno AND s2.cno = '3-105' AND degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

-- 20.查询score中选学多门课程的同学中分数为非最高分成绩的记录。
SELECT * FROM score a 
WHERE sno IN(SELECT sno FROM score GROUP BY sno HAVING COUNT(*) > 1) AND a.degree NOT IN(SELECT MAX(degree) FROM score b WHERE a.cno = b.cno);

-- 21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score WHERE degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

-- 22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,sbirthday FROM student WHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = '108');

-- 23.查询“张旭“教师任课的学生成绩。
SELECT sno,degree FROM score WHERE cno = (SELECT cno FROM teacher t,course c WHERE t.tno = c.tno AND tname = '张旭');

-- 24.查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname FROM teacher WHERE tno IN(SELECT tno FROM course WHERE cno IN(SELECT cno FROM score GROUP BY cno HAVING COUNT(cno) > 5));

-- 25.查询95033班和95031班全体学生的记录。
SELECT * FROM student WHERE class IN('95033','95031');

-- 26.查询存在有85分以上成绩的课程Cno.
SELECT cno FROM score WHERE degree IN(SELECT degree FROM score GROUP BY cno HAVING degree > 85);

-- 27.查询出“计算机系“教师所教课程的成绩表。
SELECT DISTINCT * FROM score WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系'));

-- 28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT tname,prof 
FROM teacher 
WHERE prof NOT IN(SELECT prof FROM teacher WHERE depart='计算机系' AND prof IN(SELECT prof FROM teacher WHERE depart='电子工程系'));

-- 29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT cno,sno,degree FROM score WHERE cno='3-105' AND degree>ANY(SELECT degree FROM score WHERE cno='3-245') ORDER BY degree DESC;

-- 30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT cno,sno,degree FROM score WHERE cno='3-105' AND degree>(SELECT MAX(degree) FROM score WHERE cno='3-245');

-- 31.查询所有教师和同学的name、sex和birthday.
SELECT tname NAME,tsex sex,tbirthday birthday FROM teacher UNION SELECT sname NAME,ssex sex,sbirthday birthday FROM student;

-- 32.查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT tname NAME,tsex sex,tbirthday birthday 
FROM teacher 
WHERE tsex='女' UNION SELECT sname NAME,ssex sex,sbirthday birthday FROM student WHERE ssex='女';

-- 33.查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score a WHERE a.degree<(SELECT AVG(degree) FROM score b WHERE b.cno=a.cno);

-- 34.查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM teacher WHERE tno IN(SELECT tno FROM course WHERE cno IN(SELECT DISTINCT cno FROM score));

-- 35.查询所有未讲课的教师的Tname和Depart. 
SELECT tname,depart FROM teacher WHERE tno NOT IN(SELECT tno FROM course WHERE cno IN(SELECT DISTINCT cno FROM score));

-- 36.查询至少有2名男生的班号。
SELECT class FROM student HAVING COUNT(ssex='男')>=2;
SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(*) >= 2;

-- 37.查询Student表中不姓“王”的同学记录。
SELECT * FROM student WHERE sname NOT LIKE'王%';

-- 38.查询Student表中每个学生的姓名和年龄。
SELECT sname AS NAME,(YEAR(NOW())-YEAR(sbirthday)) AS age FROM student;

-- 39.查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday),MIN(sbirthday) FROM student;

-- 40.以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student ORDER BY class DESC,sbirthday;

-- 41.查询“男”教师及其所上的课程。
SELECT tname,cname FROM teacher t JOIN course c ON t.tno=c.tno AND tsex='男';

-- 42.查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM score a WHERE a.degree = (SELECT MAX(degree) FROM score b WHERE b.cno=a.cno);

-- 43.查询和“李军”同性别的所有同学的Sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');

-- 44.查询和“李军”同性别并同班的同学Sname.
SELECT sname 
FROM student 
WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND class=(SELECT class FROM student WHERE sname='李军') AND sname<>'李军';

-- 45.查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT * FROM score WHERE sno IN(SELECT sno FROM student WHERE ssex='男') AND cno IN(SELECT cno FROM course WHERE cname='计算机导论');

在SQL中常需要对多个表的数据进行查询，这时我们需要将多个表连接起来实现查询，称作连接查询。而连接查询可以分为内连接查询和外连接查询。

注：笛卡尔积--以两个表为例：将甲表中的一条记录拿出来，分别与乙表的每一条记录进行横向的连接继续延伸将甲表每一条记录都拿出来，与乙表的每一条记录进行横向的连接--笛卡尔积操作。

1.其中内连接查询又可分为等值内连接查询、不等值内连接查询、自连接查询。

1）等值内查询：顾名思义，筛选依据是两侧的关联字段中的值相等时保留此数据。

以第14题为例，由题可知，我们需要对student表和score表的数据进行连接查询，

-- 14.查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM student s1,score s2 WHERE s1.sno = s2.sno;

当我们不设置where条件时，可以看到整整查出了6*12=72条记录，其中有很多重复或无效的查询结果。

而当我们设置了where条件（s1.sno = s2.sno）后在进行查询，得到

可见等值内连接查询有助于我们剔除多表连接查询时由于笛卡尔积产生的重复字段从而得到笛卡尔积的子集。

2）不等值内连接查询：从笛卡尔积中筛选依据是一个区间判断。

以第18题为例

-- 18.查询所有同学的Sno、Cno和rank列。
SELECT sno,cno,rank FROM score JOIN grade ON degree BETWEEN low AND upp ORDER BY rank;

当查询的结果是成绩等级（区间）时可以使用不等值内连接查询。

3）自连接查询：是指表自己与自己连接，表本身可以扮演不同角色（在这个表中存在两个关联字段）。

以第42题为例

-- 42.查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM score a WHERE a.degree = (SELECT MAX(degree) FROM score b WHERE b.cno=a.cno);

2.外连接查询：实际上外连接是内连接的一个特例，实际上就是在内连接的基础上，使哪一侧的表的数据全包括，另一侧没有与之对应的关联数据。

左外连接：使查询结果中处于左侧的表数据全被查出来

右外连接：使查询结果中处于右侧的表数据全被查出来

3.子查询：就是一个查询被包含在另一个查询之中，被包起来的那个就是子查询。

子查询可以出现在select子句部分，from子句部分，where子句部分

1）出现在where子句部分，作为查询值

例如第43题

-- 43.查询和“李军”同性别的所有同学的Sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');

当子查询结果使单列多行时可以将子查询的结果作为集合，例如第45题

-- 45.查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT * FROM score WHERE sno IN(SELECT sno FROM student WHERE ssex='男') AND cno IN(SELECT cno FROM course WHERE cname='计算机导论');

其中子查询的结果分别为：

SELECT cno FROM course WHERE cname='计算机导论'----

SELECT cno FROM course WHERE cname='计算机导论'----

2）出现在from子句部分，即from（select...），这里的子查询作为表（被查询的对象）。