mysql 多表聯合查詢_資料庫 | MySQL多表查詢綜合練習及答案

一、綜合練習

1.1 init.sql檔案内容

/* 資料導入： Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `class`-- ----------------------------BEGIN;INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');COMMIT;-- ------------------------------ Table structure for `course`-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `course`-- ----------------------------BEGIN;INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '實體', '2'), ('3', '體育', '3'), ('4', '美術', '2');COMMIT;-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `score`-- ----------------------------BEGIN;INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');COMMIT;-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `student`-- ----------------------------BEGIN;INSERT INTO `student` VALUES ('1', '男', '1', '了解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四');COMMIT;-- ------------------------------ Table structure for `teacher`-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `teacher`-- ----------------------------BEGIN;INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱雲海老師'), ('5', '李傑老師');COMMIT;SET FOREIGN_KEY_CHECKS = 1;
           

1.2 從init.sql檔案中導入資料

# 準備表、記錄mysql> create database db1;mysql> use db1;mysql> source /root/init.sql
           

1.3 基礎練習

1. 查詢男生、女生的人數；
2. 查詢姓“張”的學生名單；
3. 課程平均分從高到低顯示
4. 查詢有課程成績小于60分的同學的學号、姓名；
5. 查詢至少有一門課與學号為1的同學所學課程相同的同學的學号和姓名；
6. 查詢出隻選修了一門課程的全部學生的學号和姓名；
7. 查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
8. 查詢課程編号“2”的成績比課程編号“1”課程低的所有同學的學号、姓名；
9. 查詢“生物”課程比“實體”課程成績高的所有學生的學号；
10. 查詢平均成績大于60分的同學的學号和平均成績;
11. 查詢所有同學的學号、姓名、選課數、總成績；
12. 查詢姓“李”的老師的個數；
13. 查詢沒學過“張磊老師”課的同學的學号、姓名；
14. 查詢學過“1”并且也學過編号“2”課程的同學的學号、姓名；
15. 查詢學過“李平老師”所教的所有課的同學的學号、姓名；

1.4 進階練習

1. 查詢沒有學全所有課的同學的學号、姓名；
2. 查詢和“002”号的同學學習的課程完全相同的其他同學學号和姓名；
3. 删除學習“葉平”老師課的SC表記錄；
4. 向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編号“002”課程的同學學号；②插入“002”号課程的平均成績；
5. 按平均成績從低到高顯示所有學生的“國文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,國文,數學,英語,有效課程數,有效平均分；
6. 查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
7. 按各科平均成績從低到高和及格率的百分數從高到低順序；
8. 查詢各科成績前三名的記錄:(不考慮成績并列情況)
9. 查詢每門課程被選修的學生數；
10. 查詢同名同姓學生名單，并統計同名人數；
11. 查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程号降序排列；
12. 查詢平均成績大于85的所有學生的學号. 姓名和平均成績；
13. 查詢課程名稱為“數學”，且分數低于60的學生姓名和分數；
14. 查詢課程編号為003且課程成績在80分以上的學生的學号和姓名；
15. 求選了課程的學生人數
16. 查詢選修“楊豔”老師所授課程的學生中，成績最高的學生姓名及其成績；
17. 查詢各個課程及相應的選修人數；
18. 查詢不同課程但成績相同的學生的學号、課程号、學生成績；
19. 查詢每門課程成績最好的前兩名；
20. 檢索至少選修兩門課程的學生學号；
21. 查詢全部學生都選修的課程的課程号和課程名；
22. 查詢沒學過“葉平”老師講授的任一門課程的學生姓名；
23. 查詢兩門以上不及格課程的同學的學号及其平均成績；
24. 檢索“004”課程分數小于60，按分數降序排列的同學學号；
25. 删除“002”同學的“001”課程的成績；

二、基礎練習答案

1、查詢“生物”課程比“實體”課程成績高的所有學生的學号；select * from(  (select * from score where course_id in (select cid from course where cname = '生物')) t1 left join  (select * from score where course_id in (select cid from course where cname = '實體')) t2 on t1.student_id = t2.student_id) where t1.num > t2.num; 2、查詢平均成績大于60分的同學的學号和平均成績;# 先檢視每個同學的平均分數select student_id,avg(num) from score group by student_id;# 在篩選成績大于60分的同學的學号和平均成績；# select student_id,avg(num) from score group by student_id having avg(num) > 60; 3、查詢所有同學的學号、姓名、選課數、總成績；# 先檢視每個同學的總成績select student_id,sum(num) from score group by student_id;# 學生和課程的關系隻有成績表中存在，是以要擷取每個學生選擇的課程，需要通過score表select count(sid),student_id from score group by student_id;# 将上面兩步合并select sum(num),count(sid),student_id from score group by student_id;# 将學生的資訊和成績選課情況拼在一起select sid,sname,sum_num ,count_stu from student left join  (select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2 on sid = student_id;# 還可以更嚴謹，那些沒有選課的同學選課數和總成績應該是0select sid,sname, ( CASE WHEN sum_num is null THEN 0  ELSE sum_num END ) as sum_num , ( CASE WHEN count_stu is null THEN 0  ELSE count_stu END ) as count_stu from student left join  (select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2 on sid = student_id; 4、查詢姓“李”的老師的個數；# 找到所有姓李的 # 方法一 # select * from teacher where tname like '李%'; # 方法二 # select * from teacher where tname regexp '^李';# 統計個數 select count(tid) from teacher where tname regexp '^李'; 或者 select count(id) from teacher where tname like '李%'; 5、查詢沒學過“張磊老師”課的同學的學号、姓名；# 找到張磊老師的id select tid from teacher where tname == '張磊老師';# 找到張磊老師所教課程select cid from course where teacher_id = (select tid from teacher where tname = '張磊老師');# 找到所有學習這門課的學生idselect student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '張磊老師'));# 找到沒有學過這門課的學生對應的學生學号、姓名select sid,sname from student where sid not in  (select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '張磊老師'))); 6、查詢學過“1”并且也學過編号“2”課程的同學的學号、姓名；# 先查詢學習課程id為1的所有學生select * from score where course_id = 1;# 先查詢學習課程id為2的所有學生select * from score where course_id = 2;# 把這兩張表按照學生的id 内連接配接起來 去掉隻學習某一門課程的學生select t1.student_id from(select student_id from score where course_id = 1) t1inner join(select student_id from score where course_id = 2) t2on t1.student_id = t2.student_id# 根據學号在學生表中找到對應的姓名select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id = 1) t1 inner join (select student_id from score where course_id = 2) t2 on t1.student_id = t2.student_id); 7、查詢學過“李平老師”所教的所有課的同學的學号、姓名；#找到李平老師的tidselect tid from teacher where tname ='李平老師';# 找到李平老師教的所有課程cid select cid from course where teacher_id in (select tid from teacher where tname ='李平老師');# 找到李平老師教的所有課程數 select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老師');# 找到所有學習李平老師課程的學生select * from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老師'));# 檢視所有學習李平老師課程的學生選課數select student_id,count(course_id) from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老師')) group by student_id;# 找到所有選擇了李平老師所有課程的學生idselect student_id from (select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老師')) group by student_id) t1where t1.course_count =(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老師'));# 找到學生的其他資訊select sid,sname from student where sid in (select student_id from (select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老師')) group by student_id) t1where t1.course_count =(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老師'))); 8、查詢課程編号“2”的成績比課程編号“1”課程低的所有同學的學号、姓名；# 先找到每個學生的課程編号“1”的和課程編号“2”的成績組成一張表select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id# 再找到課程編号“2”的成績比課程編号“1”課程低的所有學生的學号select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1# 再找到所有學生的學号、姓名select sid,sname from student where sid in(select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1); 9、查詢有課程成績小于60分的同學的學号、姓名；# 先查詢成績小于60分的同學的學号select distinct student_id from score where num < 60;# 再查詢有課程成績小于60分的同學的學号、姓名select sid,sname from student where sid in (select distinct student_id from score where num < 60); 10、查詢至少有一門課與學号為1的同學所學課程相同的同學的學号和姓名；# 先看看學号為1的同學都學了哪些課程select course_id from score where student_id = 1# 找到學習 學号為1的同學所學課程 的學号select distinct student_id from score where course_id in (select course_id from score where student_id = 1);# 找到學習 學号為1的同學所學課程 的學号姓名select sid,sname from student where sid in (select distinct student_id from score where course_id in (select course_id from score where student_id = 1)); 11、課程平均分從高到低顯示select course_id,avg(num) avg_num from score group by course_id order by avg_num desc; 12、查詢出隻選修了一門課程的全部學生的學号和姓名；# 查詢出隻選修了一門課程的全部學生的學号select student_id,count(student_id) from score group by student_id having count(student_id) =1;# 查詢出隻選修了一門課程的全部學生的學号和姓名；select sid,sname from student where sid in (select student_id from score group by student_id having count(student_id) =1); 13、查詢男生、女生的人數；select gender,count(sid) from student group by gender; 14、查詢姓“張”的學生名單；select * from student where sname like '張%'; 15、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；# 查詢成績的最高分select course_id c1,max(num) from score group by course_id# 查詢成績的最低分select course_id c1,min(num) from score group by course_id# 查詢成績的最高分和最低分拼接select * from ( (select course_id c1,max(num) from score group by course_id) t1 inner join (select course_id c2,min(num) from score group by course_id) t2 on t1.c1 = t2.c2 );# 格式整理select t1.c1,t1.max_num,t2.min_num from ( (select course_id c1,max(num) max_num from score group by course_id) t1 inner join (select course_id c2,min(num) min_num from score group by course_id) t2 on t1.c1 = t2.c2 );
           

三、進階練習答案

1、查詢沒有學全所有課的同學的學号、姓名；# 先統計一共有多少門課程select count(cid) from course;# 檢視每個學生選擇的課程書select count(course_id) from score group by student_id;# 查詢所學課程數小于總課程數的學生學号select student_idfrom (select count(course_id) c_course_id,student_id from score group by student_id) t1 where t1.c_course_id < (select count(cid) from course) ;# 查詢沒有學全所有課的同學的學号、姓名；select sid,sname from student where sid in ( select student_id from (select count(course_id) c_course_id,student_id from score group by student_id ) t1 where t1.c_course_id < (select count(cid) from course)) ; 2、查詢和“002”号的同學學習的課程完全相同的其他同學學号和姓名；# 先查詢2号同學學了哪些課程select * from score where student_id =2;# 找到學習了2号同學沒學習課程的所有同學(找到所有和2号同學學習的課程不一樣的同學)select student_id from score where course_id not in (select course_id from score where student_id=2)# 找到score表中所有的學生并且把 2号同學 以及(和2号同學學習的課程不一樣的同學)排除出去select student_id from score where student_id not in (select student_id from score where course_id not in (select course_id from score where student_id=2)) and student_id !=2# 對剩餘的和2号同學所選課程沒有不同的同學所選課程數進行統計，如果和2号同學的課程數相同，就是選擇了相同的課程select student_id from score where student_id not in ( select student_id from score where course_id not in (select course_id from score where student_id=2) ) and student_id !=2group by student_id having count(course_id)= (select count(course_id) from score where student_id=2); 3、删除學習“葉平”老師課的SC(score)表記錄；# 先查出李平老師的idselect tid from teacher where tname = '李平老師';# 檢視李平老師所教授的課程select cid from course where teacher_id = (select tid from teacher where tname = '李平老師');# 檢視李平老師所教課程的成績資料select * from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老師'));# 執行删除指令delete from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老師'));4、向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編号“002”課程的同學學号；②插入“002”号課程的平均成績； # 先找尋上過2号課程的同學select student_id from score where course_id = 2;# 再找到沒上過2号課程的所有同學select * from student where sid not in (select student_id from score where course_id = 2);# 計算出學習2号課程的同學的平均成績select avg(num) from score where course_id = 2 group by course_id;# 用笛卡爾積将上述兩個表拼起來select * from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) from score where course_id = 2 group by course_id) t2;# 向SC表中插入記錄insert into score (course_id,student_id,num) select 2,t1.sid,t2.avg_num from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) avg_num from score where course_id = 2 group by course_id) t2; 5、按平均成績從低到高顯示所有學生的“國文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,國文,數學,英語,有效課程數,有效平均分；# 檢視每個學生的數學成績select student_id,num from score where course_id = (select cid from course where cname = '數學');# 檢視每個學生的國文成績select student_id,num from score where course_id = (select cid from course where cname = '國文');# 檢視每個學生的英語成績select student_id,num from score where course_id = (select cid from course where cname = '英語');# 檢視每個學生的平均成績select student_id,avg(num),count(num) from score group by student_id;# 将上面的幾張表拼接起來,為了生成所有學生的資訊，用student表作為左連接配接的第一張表select sid 學生ID,t2.num 國文,t1.num 數學, t3.num 英語,t4.count_course 有效課程數,t4.avg_num 有效平均分 from student  left join (select student_id,num from score where course_id = (select cid from course where cname = '數學')) t1 on student.sid = t1.student_id left join (select student_id,num from score where course_id = (select cid from course where cname = '國文')) t2 on student.sid = t2.student_id left join (select student_id,num from score where course_id = (select cid from course where cname = '英語')) t3 on student.sid = t3.student_id left join (select student_id,avg(num) avg_num,count(num) count_course from score group by student_id) t4 on student.sid = t4.student_id 6、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；select course_id 課程ID,max(num) 最高分,min(num) 最低分 from score group by course_id; 7、按各科平均成績從低到高和及格率的百分數從高到低順序；# 方法1:# 先求平均成績select course_id,avg(num) from score group by course_id;# 解決計算各科及格率的問題所有及格的人/所有人數select t1.course_id,t1.count1/t2.count2 from (select course_id,count(course_id) count1 from score where num>60 group by course_id) t1 left join(select course_id,count(course_id) count2 from score group by course_id) t2on t1.course_id = t2.course_id;# 根據上述内容進行表的拼接select t_out1.course_id,t_out1.avgnum, t_out2.pass_per from (select course_id,avg(num) avgnum from score group by course_id ) t_out1left join (select t1.course_id,t1.count1/t2.count2 pass_per from (select course_id,count(course_id) count1 from score where num>60 group by course_id) t1 left join(select course_id,count(course_id) count2 from score group by course_id) t2on t1.course_id = t2.course_id) t_out2on t_out1.course_id = t_out2.course_id# 加上排序select t_out1.course_id,t_out1.avgnum, t_out2.pass_per from (select course_id,avg(num) avgnum from score group by course_id ) t_out1 left join (select t1.course_id,t1.count1/t2.count2 pass_per from (select course_id,count(course_id) count1 from score where num>60 group by course_id) t1 left join (select course_id,count(course_id) count2 from score group by course_id) t2 on t1.course_id = t2.course_id) t_out2 on t_out1.course_id = t_out2.course_id order by avgnum ,pass_per desc; # 方法2 # 使用case when直接計算合格率select sum(case when num>60 then 1 else 0 end)/count(course_id)from score group by course_id# 加上課程id和平均值select course_id,avg(num),sum(case when num>60 then 1 else 0 end)/count(course_id)from score group by course_id# 加上排序select course_id,avg(num) avgnum,sum(case when num>60 then 1 else 0 end)/count(course_id) pass_per from score group by course_id order by avgnum ,pass_per desc;  8、查詢各科成績前三名的記錄:(不考慮成績并列情況) selectt1.sid,t1.student_id,t1.course_id,t1.num from score t1left join ( select sid,course_id, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 2, 1) as third_num from score as s1 ) t2on t1.sid = t2.sidwhere t1.num = t2.first_num or t1.num = t2.second_num or t1.num = t2.third_num; 9、查詢每門課程被選修的學生數；select course_id,count(course_id) from score group by course_id; 10、查詢同名同姓學生名單，并統計同名人數；select sname,count(1) as count from student group by sname; 11、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程号降序排列；select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc; 12、查詢平均成績大于85的所有學生的學号、姓名和平均成績；select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id = student.sid group by student_id; 13、查詢課程名稱為“數學”，且分數低于60的學生姓名和分數；select student.sname,score.num from scoreleft join course on score.course_id = course.cidleft join student on score.student_id = student.sidwhere score.num < 60 and course.cname = '數學'  14、查詢課程編号為003且課程成績在80分以上的學生的學号和姓名； select * from score where score.student_id = 3 and score.num > 80 15、求選了課程的學生人數select sid,sname from student where sid not in (select student_id from score group by student_id); 16、查詢選修“楊豔”老師所授課程的學生中，成績最高的學生姓名及其成績；# 先找到“楊豔”老師的教師idselect tid from teacher where tname = '楊豔';# 再找到楊豔老師教的所有課程select cid from course where teacher_id in (select tid from teacher where tname = '楊豔');# 再找到楊豔老師教的所有課程的最高分select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'));# 再找到楊豔老師教的所有課程的最高分對應的學生select distinct student_id,num from score where num = (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'))) and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'));# 找到學生的姓名select student.sname,t1.num from(select distinct student_id,num from score where num = (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'))) and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'))) t1left joinstudenton t1.student_id = student.sid; 17、查詢各個課程及相應的選修人數；select course.cname,count(1) from scoreleft join course on score.course_id = course.cidgroup by course_id; 18、查詢不同課程但成績相同的學生的學号、課程号、學生成績；select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; 19、查詢每門課程成績最好的前兩名； 先查詢每條資料對應學科成績的第一名和第二名，這裡必須要保留所有的s1，以便後續進行連表查詢select sid,course_id, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_numfrom score as s1按照sid連表，把學生的成績和對應的第一名、第二名成績連起來select* from score t1left join ( select sid,course_id, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_num from score as s1 ) t2on t1.sid = t2.sid判斷如果學生的成績是第一名、第二名的成績，那麼就符合條件，顯示學生的id、學科和成績selectt1.sid,t1.student_id,t1.course_id,t1.num from score t1left join ( select sid,course_id, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0, 1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1, 1) as second_num from score as s1 ) t2on t1.sid = t2.sidwhere t1.num = t2.first_num or t1.num = t2.second_num;20、檢索至少選修兩門課程的學生學号；select student_id from score group by student_id having count(student_id) > 1; 21、查詢全部學生都選修的課程的課程号和課程名；# 先檢視一共有多少學生select count(sid) from student;# 檢視哪一門課選秀的學生個數和學生的總個數相等select course_id from score group by course_id having count(student_id) = (select count(sid) from student);22、查詢沒學過“葉平”老師講授的任一門課程的學生姓名；# 先檢視要查找老師的idselect tid from teacher where tname = '李平老師';# 檢視該老師交了哪些課程select cid from course where teacher_id in (select tid from teacher where tname = '李平老師')# 看看有多少學生學習了該老師的課程select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師'));# 把不在上表中的學生姓名查出來select sname from student where sid not in (select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老師')));23、查詢兩門以上不及格課程的同學的學号及其平均成績；select student_id,avg(num) from score where num<60 group by student_id having count(num)>=2; 24、檢索“004”課程分數小于60，按分數降序排列的同學學号；select student_id from score where num< 60 and course_id = 4 order by num desc;25、删除“002”同學的“001”課程的成績；delete from score where course_id = 1 and student_id = 2；