#-*- coding: utf-8 -*-
__author__ = 'caiqinxiong_cai'
#2019/9/25 15:22#多表查詢的作業 : https://www.cnblogs.com/Eva-J/articles/9688383.html
# 答案：https://www.cnblogs.com/Eva-J/articles/9765370.html
'''1、查詢沒有學全所有課的同學的學号、姓名；'''
#1、先查詢一共有幾門課程#mysql> select count(cid) from course;#+------------+#| count(cid) |#+------------+#| 4 |#+------------+#1 row in set (0.00 sec)
#2、在分數表中根據學生進行分組，擷取每一個學生選課數量，如果數量等于總課程數量，說明已經選擇了所有課程#mysql> select student_id,sname from score inner join student on score.student_id = student.sid group by student_id having count(course_id)=(select count(cid) from course);#+------------+--------+#| student_id | sname |#+------------+--------+#| 3 | 張三 |#| 4 | 張一 |#| 5 | 張二 |#| 6 | 張四 |#| 7 | 鐵錘 |#| 8 | 李三 |#| 9 | 李一 |#| 10 | 李二 |#| 11 | 李四 |#| 12 | 如花 |#+------------+--------+#10 rows in set (0.00 sec)
'''2、查詢和“002”号的同學學習的課程完全相同的其他同學學号和姓名；'''
#1、先查找2号同學學習了哪些課程#mysql> select * from score where student_id = 2;#+-----+------------+-----------+-----+#| sid | student_id | course_id | num |#+-----+------------+-----------+-----+#| 6 | 2 | 1 | 8 |#| 8 | 2 | 3 | 68 |#| 9 | 2 | 4 | 99 |#+-----+------------+-----------+-----+#3 rows in set (0.01 sec)#
#2、統計2号同學一共學了幾門課程#mysql> select count(course_id) from score where student_id = 2;#+------------------+#| count(course_id) |#+------------------+#| 3 |#+------------------+#1 row in set (0.00 sec)#
#3、找出和2号同學學習課程數量一樣的同學id#mysql> select student_id from score where student_id != 2 group by student_id having count(course_id)=(select count(course_id) from score where student_id = 2);#+------------+#| student_id |#+------------+#| 1 |#+------------+#1 row in set (0.00 sec)
#4、最後找出既課程數量和2号同學一樣的并且選課也一樣的#mysql> select student_id,sname from score inner join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 2 group by student_id having count(course_id) = (select count(course_id) from score where student_id = 2)) and course_id in (select course_id from score where student_id = 2) group by student_id having count(course_id) =(select count(course_id) from score where student_id = 2);#Empty set (0.00 sec)
#沒有和2号同學選課一樣的，改成3号同學看看效果#mysql> select student_id,sname from score inner join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 3 group by student_id having count(course_id) = (select count(course_id) from score where student_id = 3)) and course_id in (select course_id from score where student_id = 3) group by student_id having count(course_id) =(select count(course_id) from score where student_id = 3);#+------------+--------+#| student_id | sname |#+------------+--------+#| 4 | 張一 |#| 5 | 張二 |#| 6 | 張四 |#| 7 | 鐵錘 |#| 8 | 李三 |#| 9 | 李一 |#| 10 | 李二 |#| 11 | 李四 |#| 12 | 如花 |#+------------+--------+#9 rows in set (0.00 sec)
'''3、删除學習“李平”老師課的SC表記錄；'''
#1、查出李平老師的課程id号#mysql> select * from course inner join teacher on course.teacher_id = teacher.tid where tname = '李平老師';#+-----+--------+------------+-----+--------------+#| cid | cname | teacher_id | tid | tname |#+-----+--------+------------+-----+--------------+#| 2 | 實體 | 2 | 2 | 李平老師 |#| 4 | 美術 | 2 | 2 | 李平老師 |#+-----+--------+------------+-----+--------------+#2 rows in set (0.00 sec)#
#2、在成績表中删除掉李平老師相關的課程#mysql> delete from score where course_id in (select cid from course inner join teacher on course.teacher_id = teacher.tid where tname = '李平老師');#Query OK, 23 rows affected (0.14 sec)
'''4、向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編号“002”課程的同學學号；②插入“002”号課程的平均成績；'''
#表插入資料方法一#insert into 表名(字段名) values (值);#方法二#inset into t1(a,b) select c,d from t2; # t2中選擇兩個字段的值插入到t1，資料類型必須一緻。#1、檢視score表的字段限制，發現sid是自動增長的，是以，隻需要插入student_id, course_id, num三個字段的值就行了,而且course_id=2#mysql> desc score;#+------------+---------+------+-----+---------+----------------+#| Field | Type | Null | Key | Default | Extra |#+------------+---------+------+-----+---------+----------------+#| sid | int(11) | NO | PRI | NULL | auto_increment |#| student_id | int(11) | NO | MUL | NULL | |#| course_id | int(11) | NO | MUL | NULL | |#| num | int(11) | NO | | NULL | |#+------------+---------+------+-----+---------+----------------+#4 rows in set (0.00 sec)#
#2、擷取002的平均成績#mysql> select avg(num) from score where course_id = 2;#+----------+#| avg(num) |#+----------+#| 0.0000 |#+----------+#1 row in set (0.01 sec)
#3、擷取所有沒上過002課的所有同學#mysql> select student_id from score where course_id = 2;#+------------+#| student_id |#+------------+#| 1 |#| 2 |#| 3 |#| 4 |#| 5 |#| 6 |#| 7 |#| 8 |#| 9 |#| 10 |#| 11 |#| 12 |#| 13 |#| 14 |#| 15 |#| 16 |#+------------+#16 rows in set (0.00 sec)
#4、将這些滿足條件的sql語句進行整合，插入資料到score表。#mysql> insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) from student where sid not in ( select student_id from score where course_id = 2);#Query OK, 16 rows affected, 16 warnings (0.59 sec)#Records: 16 Duplicates: 0 Warnings: 16
'''5、按平均成績從低到高顯示所有學生的“生物”、“實體”、“體育”三門的課程成績，按如下形式顯示： 學生ID,生物,實體,體育,有效課程數,有效平均分；'''
#按平均成績從低到高顯示有效課程數,有效平均分#mysql> select student_id,count(course_id),avg(num) from score group by student_id order by avg(num) asc;#+------------+------------------+----------+#| student_id | count(course_id) | avg(num) |#+------------+------------------+----------+#| 14 | 1 | 0.0000 |#| 15 | 1 | 0.0000 |#| 16 | 1 | 0.0000 |#| 1 | 2 | 5.0000 |#| 6 | 3 | 25.3333 |#| 7 | 3 | 25.3333 |#| 8 | 3 | 25.3333 |#| 2 | 3 | 25.3333 |#| 13 | 2 | 43.5000 |#| 10 | 3 | 44.3333 |#| 11 | 3 | 44.3333 |#| 12 | 3 | 44.3333 |#| 4 | 3 | 48.6667 |#| 5 | 3 | 48.6667 |#| 9 | 3 | 52.6667 |#| 3 | 3 | 54.6667 |#+------------+------------------+----------+#16 rows in set (0.00 sec)
#求出生物的成績#mysql> select student_id,num from score left join course on course_id = cid where cname = "生物";#+------------+-----+#| student_id | num |#+------------+-----+#| 1 | 10 |#| 2 | 8 |#| 3 | 77 |#| 4 | 79 |#| 5 | 79 |#| 6 | 9 |#| 7 | 9 |#| 8 | 9 |#| 9 | 91 |#| 10 | 90 |#| 11 | 90 |#| 12 | 90 |#+------------+-----+#12 rows in set (0.00 sec)
#将前面兩張表整合#mysql> select sc.student_id,(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id ) as 生物,count(course_id),avg(sc.num) from score as sc group by student_id order by avg(sc.num) asc;#+------------+--------+------------------+-------------+#| student_id | 生物 | count(course_id) | avg(sc.num) |#+------------+--------+------------------+-------------+#| 15 | NULL | 1 | 0.0000 |#| 16 | NULL | 1 | 0.0000 |#| 14 | NULL | 1 | 0.0000 |#| 1 | 10 | 2 | 5.0000 |#| 6 | 9 | 3 | 25.3333 |#| 7 | 9 | 3 | 25.3333 |#| 2 | 8 | 3 | 25.3333 |#| 8 | 9 | 3 | 25.3333 |#| 13 | NULL | 2 | 43.5000 |#| 10 | 90 | 3 | 44.3333 |#| 11 | 90 | 3 | 44.3333 |#| 12 | 90 | 3 | 44.3333 |#| 4 | 79 | 3 | 48.6667 |#| 5 | 79 | 3 | 48.6667 |#| 9 | 91 | 3 | 52.6667 |#| 3 | 77 | 3 | 54.6667 |#+------------+--------+------------------+-------------+#16 rows in set (0.00 sec)#
#以此類推，加上實體和體育的成績進行整合#mysql> select sc.student_id as 學生ID,(select num from score inner join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as 生物,(select num from score inner join course on score.course_id = course.cid where course.cname = "實體" and score.student_id=sc.student_id) as 實體,(select num from score inner join course on score.course_id = course.cid where course.cname = "體育" and score.student_id=sc.student_id) as 體育,count(sc.course_id) as 有效課程數,avg(sc.num) as 有效平均分 from score as sc group by student_id order by avg(sc.num) asc;#+----------+--------+--------+--------+-----------------+-----------------+#| 學生ID | 生物 | 實體 | 體育 | 有效課程數 | 有效平均分 |#+----------+--------+--------+--------+-----------------+-----------------+#| 14 | NULL | 0 | NULL | 1 | 0.0000 |#| 15 | NULL | 0 | NULL | 1 | 0.0000 |#| 16 | NULL | 0 | NULL | 1 | 0.0000 |#| 1 | 10 | 0 | NULL | 2 | 5.0000 |#| 7 | 9 | 0 | 67 | 3 | 25.3333 |#| 8 | 9 | 0 | 67 | 3 | 25.3333 |#| 2 | 8 | 0 | 68 | 3 | 25.3333 |#| 6 | 9 | 0 | 67 | 3 | 25.3333 |#| 13 | NULL | 0 | 87 | 2 | 43.5000 |#| 11 | 90 | 0 | 43 | 3 | 44.3333 |#| 12 | 90 | 0 | 43 | 3 | 44.3333 |#| 10 | 90 | 0 | 43 | 3 | 44.3333 |#| 4 | 79 | 0 | 67 | 3 | 48.6667 |#| 5 | 79 | 0 | 67 | 3 | 48.6667 |#| 9 | 91 | 0 | 67 | 3 | 52.6667 |#| 3 | 77 | 0 | 87 | 3 | 54.6667 |#+----------+--------+--------+--------+-----------------+-----------------+#16 rows in set (0.00 sec)
'''6、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；'''
#mysql> select course_id as 課程ID, max(num) as 最高分, min(num) as 最低分 from score group by course_id;#+----------+-----------+-----------+#| 課程ID | 最高分 | 最低分 |#+----------+-----------+-----------+#| 1 | 91 | 8 |#| 2 | 0 | 0 |#| 3 | 87 | 43 |#+----------+-----------+-----------+#3 rows in set (0.00 sec)
'''7、按各科平均成績從低到高和及格率的百分數從高到低順序；'''
#mysql> select course_id, avg(num) as 平均分,sum(case when score.num > 60 then 1 else 0 end)/count(1)*100 as 及格率 from score group by course_id order by 平均分 asc,及格率 desc;#+-----------+-----------+-----------+#| course_id | 平均分 | 及格率 |#+-----------+-----------+-----------+#| 2 | 0.0000 | 0.0000 |#| 1 | 53.4167 | 58.3333 |#| 3 | 64.4167 | 75.0000 |#+-----------+-----------+-----------+#3 rows in set (0.00 sec)
'''8、查詢各科成績前三名的記錄:(不考慮成績并列情況)'''
#mysql> SELECT s1.cid AS 課程ID,s1.cname AS 課程, (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一, (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 2,1) AS 第三 FROM course AS s1;#+----------+--------+--------+--------+--------+#| 課程ID | 課程 | 第一 | 第二 | 第三 |#+----------+--------+--------+--------+--------+#| 1 | 生物 | 91 | 90 | 79 |#| 2 | 實體 | 0 | NULL | NULL |#| 3 | 體育 | 87 | 68 | 67 |#| 4 | 美術 | NULL | NULL | NULL |#+----------+--------+--------+--------+--------+#4 rows in set (0.00 sec)
'''9、查詢每門課程被選修的學生數；'''
#mysql> select cid,cname,count(student_id) from course left join score on course_id = cid group by course_id;#+-----+--------+-------------------+#| cid | cname | count(student_id) |#+-----+--------+-------------------+#| 4 | 美術 | 0 |#| 1 | 生物 | 12 |#| 2 | 實體 | 16 |#| 3 | 體育 | 12 |#+-----+--------+-------------------+#4 rows in set (0.00 sec)
'''10、查詢同名同姓學生名單，并統計同名人數；'''
#mysql> select sname,count(sname) from student group by sname;#+--------+--------------+#| sname | count(sname) |#+--------+--------------+#| 劉一 | 1 |#| 劉三 | 1 |#| 劉二 | 1 |#| 劉四 | 1 |#| 如花 | 1 |#| 張一 | 1 |#| 張三 | 1 |#| 張二 | 1 |#| 張四 | 1 |#| 李一 | 1 |#| 李三 | 1 |#| 李二 | 1 |#| 李四 | 1 |#| 了解 | 1 |#| 鋼蛋 | 1 |#| 鐵錘 | 1 |#+--------+--------------+#16 rows in set (0.00 sec)
'''11、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程号降序排列；'''
#mysql> select cid,cname,avg(if(isnull(num), 0 ,num)) as 平均分 from course left join score on course_id = cid group by course_id order by avg(num) asc,cid desc;#+-----+--------+-----------+#| cid | cname | 平均分 |#+-----+--------+-----------+#| 4 | 美術 | 0.0000 |#| 2 | 實體 | 0.0000 |#| 1 | 生物 | 53.4167 |#| 3 | 體育 | 64.4167 |#+-----+--------+-----------+#4 rows in set (0.00 sec)
'''12、查詢平均成績大于85的所有學生的學号、姓名和平均成績；'''
#1、先查詢所有學生的平均成績#mysql> select student_id,sname,avg(num) from student left join score on student_id = student.sid group by student_id;#+------------+--------+----------+#| student_id | sname | avg(num) |#+------------+--------+----------+#| 1 | 了解 | 5.0000 |#| 2 | 鋼蛋 | 25.3333 |#| 3 | 張三 | 54.6667 |#| 4 | 張一 | 48.6667 |#| 5 | 張二 | 48.6667 |#| 6 | 張四 | 25.3333 |#| 7 | 鐵錘 | 25.3333 |#| 8 | 李三 | 25.3333 |#| 9 | 李一 | 52.6667 |#| 10 | 李二 | 44.3333 |#| 11 | 李四 | 44.3333 |#| 12 | 如花 | 44.3333 |#| 13 | 劉三 | 43.5000 |#| 14 | 劉一 | 0.0000 |#| 15 | 劉二 | 0.0000 |#| 16 | 劉四 | 0.0000 |#+------------+--------+----------+#16 rows in set (0.00 sec)
#2、過濾出平均成績大于85分的，沒有大于85分的同學#mysql> select student_id,sname,avg(num) from student left join score on student_id = student.sid group by student_id having avg(num)>85;#Empty set (0.00 sec)
'''13、查詢課程名稱為“生物”，且分數低于60的學生姓名和分數'''
#方法一#mysql> select sname,num from student left join score on student_id = student.sid where course_id = ( select cid from course where cname='生物') and num<60;#+--------+------+#| sname | num |#+--------+------+#| 了解 | 10 |#| 鋼蛋 | 8 |#| 張四 | 9 |#| 鐵錘 | 9 |#| 李三 | 9 |#+--------+------+#5 rows in set (0.00 sec)
#方法二(3個左連接配接并用)#mysql> SELECT student.sid AS 學号,student.sname AS 姓名,score.num AS 成績 FROM score LEFT JOIN course ON score.course_id=course.cid LEFT JOIN student ON score.student_id= student.sid WHERE course.cname="生物" AND score.num<60;#+--------+--------+--------+#| 學号 | 姓名 | 成績 |#+--------+--------+--------+#| 1 | 了解 | 10 |#| 2 | 鋼蛋 | 8 |#| 6 | 張四 | 9 |#| 7 | 鐵錘 | 9 |#| 8 | 李三 | 9 |#+--------+--------+--------+#5 rows in set (0.00 sec)
'''14、查詢課程編号為003且課程成績在80分以上的學生的學号和姓名；'''
#mysql> select student_id,sname from student left join score on student_id = student.sid where course_id=3 and num >80;#+------------+--------+#| student_id | sname |#+------------+--------+#| 3 | 張三 |#| 13 | 劉三 |#+------------+--------+#2 rows in set (0.00 sec)
'''15、求選了課程的學生人數'''
#方法一#mysql> select count(t1.student_id) as 選課總人數 from (select student_id from score group by student_id) as t1;#+-----------------+#| 選課總人數 |#+-----------------+#| 16 |#+-----------------+#1 row in set (0.00 sec)#
#方法二#mysql> select count(distinct student_id) as 選課總人數 from score;#+-----------------+#| 選課總人數 |#+-----------------+#| 16 |#+-----------------+#1 row in set (0.01 sec)
'''16、查詢選修“張磊老師”所授課程的學生中，成績最高的學生姓名及其成績；'''
#方法一#mysql> select sname,num from score left join student on score.student_id = student.sid where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='張磊老師') order by num desc limit 1;#+--------+-----+#| sname | num |#+--------+-----+#| 李一 | 91 |#+--------+-----+#1 row in set (0.00 sec)
#方法二(3個左連接配接并用)#mysql> SELECT student.sid AS 學号,student.sname AS 姓名,num AS 成績 FROM score LEFT JOIN course ON score.course_id=course.cid LEFT JOIN student ON score.student_id=student.sid LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname = "張磊老師" ORDER BY num DESC LIMIT 1;#+--------+--------+--------+#| 學号 | 姓名 | 成績 |#+--------+--------+--------+#| 9 | 李一 | 91 |#+--------+--------+--------+#1 row in set (0.00 sec)
#方法三(多個子查詢套用)#mysql> select sname,num from student left join score on student_id = student.sid where course_id in (select cid from course where teacher_id = ( select tid from teacher where tname='張磊老師')) order by num desc limit 1;#+--------+------+#| sname | num |#+--------+------+#| 李一 | 91 |#+--------+------+#1 row in set (0.00 sec)
'''17、查詢各個課程及相應的選修人數；'''
#mysql> select cid,cname,count(student_id) from course left join score on course_id = cid group by course_id;#+-----+--------+-------------------+#| cid | cname | count(student_id) |#+-----+--------+-------------------+#| 4 | 美術 | 0 |#| 1 | 生物 | 12 |#| 2 | 實體 | 16 |#| 3 | 體育 | 12 |#+-----+--------+-------------------+#4 rows in set (0.00 sec)#
'''18、查詢不同課程但成績相同的學生的學号、課程号、學生成績；'''
#利用同一張表重命名成兩張表，再比較。#mysql> select distinct s1.course_id,s2.course_id,s1.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;#Empty set (0.00 sec)#沒有不同課程一樣成績的同學，手動修改某個同學的成績檢視效果#mysql> update score set num = 8 where sid=8;#Query OK, 1 row affected (0.07 sec)#Rows matched: 1 Changed: 1 Warnings: 0#
#mysql> select distinct s1.course_id,s2.course_id,s1.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;#+-----------+-----------+-----+#| course_id | course_id | num |#+-----------+-----------+-----+#| 3 | 1 | 8 |#| 1 | 3 | 8 |#+-----------+-----------+-----+#2 rows in set (0.00 sec)
'''19、查詢每門課程成績最好的前兩名；'''
#mysql> SELECT cid AS 課程ID,cname AS 課程,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二 FROM course AS s1;#+----------+--------+--------+--------+#| 課程ID | 課程 | 第一 | 第二 |#+----------+--------+--------+--------+#| 1 | 生物 | 91 | 90 |#| 2 | 實體 | 0 | NULL |#| 3 | 體育 | 87 | 68 |#| 4 | 美術 | NULL | NULL |#+----------+--------+--------+--------+#4 rows in set (0.00 sec)#
'''20、檢索至少選修兩門課程的學生學号；'''
#mysql> select student_id from score group by student_id having count(course_id)>=2;#+------------+#| student_id |#+------------+#| 1 |#| 2 |#| 3 |#| 4 |#| 5 |#| 6 |#| 7 |#| 8 |#| 9 |#| 10 |#| 11 |#| 12 |#| 13 |#+------------+#13 rows in set (0.00 sec)
'''21、查詢全部學生都選修的課程的課程号和課程名；'''
#mysql> select course_id,cname from course inner join score on course_id = cid group by course_id having count(course_id)=(select count(sid) from student);#+-----------+--------+#| course_id | cname |#+-----------+--------+#| 2 | 實體 |#+-----------+--------+#1 row in set (0.00 sec)
'''22、查詢沒學過“李平”老師講授的任一門課程的學生姓名；'''
#方法一 (多個子查詢套用)#mysql> select student_id,sname from student left join score on student.sid=student_id where course_id not in (select cid from course where teacher_id = (select tid from teacher where tname='李平老師')) group by student_id;#+------------+--------+#| student_id | sname |#+------------+--------+#| 1 | 了解 |#| 2 | 鋼蛋 |#| 3 | 張三 |#| 4 | 張一 |#| 5 | 張二 |#| 6 | 張四 |#| 7 | 鐵錘 |#| 8 | 李三 |#| 9 | 李一 |#| 10 | 李二 |#| 11 | 李四 |#| 12 | 如花 |#| 13 | 劉三 |#+------------+--------+#13 rows in set (0.00 sec)
#方法二(利用左連接配接)#mysql> select student_id,student.sname from score left join student on score.student_id = student.sid where score.course_id not in (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老師') group by student_id;#+------------+--------+#| student_id | sname |#+------------+--------+#| 1 | 了解 |#| 2 | 鋼蛋 |#| 3 | 張三 |#| 4 | 張一 |#| 5 | 張二 |#| 6 | 張四 |#| 7 | 鐵錘 |#| 8 | 李三 |#| 9 | 李一 |#| 10 | 李二 |#| 11 | 李四 |#| 12 | 如花 |#| 13 | 劉三 |#+------------+--------+#13 rows in set (0.00 sec)
'''23、查詢兩門以上不及格課程的同學的學号及其平均成績；'''
#mysql> select student_id,avg(num) from score where student_id in ( select student_id from score where num<60 group by student_id having count(course_id)>=2) group by student_id;#+------------+----------+#| student_id | avg(num) |#+------------+----------+#| 1 | 5.0000 |#| 2 | 25.3333 |#| 6 | 25.3333 |#| 7 | 25.3333 |#| 8 | 25.3333 |#| 10 | 44.3333 |#| 11 | 44.3333 |#| 12 | 44.3333 |#+------------+----------+#8 rows in set (0.00 sec)
'''24、檢索“004”課程分數小于60，按分數降序排列的同學學号；'''
#mysql> select student_id from score where course_id=4 and num<60 order by num desc;#Empty set (0.00 sec)#“004”課程沒有同學，改成查詢1号課程看一下效果#mysql> select student_id from score where course_id=1 and num<60 order by num desc;#+------------+#| student_id |#+------------+#| 1 |#| 6 |#| 7 |#| 8 |#| 2 |#+------------+#5 rows in set (0.00 sec)
'''25、删除“002”同學的“001”課程的成績；'''
#mysql> delete from score where course_id = 1 and student_id = 2;#Query OK, 1 row affected (0.08 sec)