#-*- coding: utf-8 -*-

__author__ = 'caiqinxiong_cai'

#2019/9/25 15:22#多表查詢的作業(yè) : https://www.cnblogs.com/Eva-J/articles/9688383.html

# 答案：https://www.cnblogs.com/Eva-J/articles/9765370.html

'''1、查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；'''

#1、先查詢一共有幾門課程#mysql> select count(cid) from course;#+------------+#| count(cid) |#+------------+#| 4 |#+------------+#1 row in set (0.00 sec)

#2、在分?jǐn)?shù)表中根據(jù)學(xué)生進(jìn)行分組，獲取每一個(gè)學(xué)生選課數(shù)量，如果數(shù)量等于總課程數(shù)量，說(shuō)明已經(jīng)選擇了所有課程#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”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；'''

#1、先查找2號(hào)同學(xué)學(xué)習(xí)了哪些課程#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、統(tǒng)計(jì)2號(hào)同學(xué)一共學(xué)了幾門課程#mysql> select count(course_id) from score where student_id = 2;#+------------------+#| count(course_id) |#+------------------+#| 3 |#+------------------+#1 row in set (0.00 sec)#

#3、找出和2號(hào)同學(xué)學(xué)習(xí)課程數(shù)量一樣的同學(xué)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、最后找出既課程數(shù)量和2號(hào)同學(xué)一樣的并且選課也一樣的#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)

#沒(méi)有和2號(hào)同學(xué)選課一樣的，改成3號(hào)同學(xué)看看效果#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、刪除學(xué)習(xí)“李平”老師課的SC表記錄；'''

#1、查出李平老師的課程id號(hào)#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 | 美術(shù) | 2 | 2 | 李平老師 |#+-----+--------+------------+-----+--------------+#2 rows in set (0.00 sec)#

#2、在成績(jī)表中刪除掉李平老師相關(guān)的課程#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表中插入一些記錄，這些記錄要求符合以下條件：①?zèng)]有上過(guò)編號(hào)“002”課程的同學(xué)學(xué)號(hào)；②插入“002”號(hào)課程的平均成績(jī)；'''

#表插入數(shù)據(jù)方法一#insert into 表名(字段名) values (值);#方法二#inset into t1(a,b) select c,d from t2; # t2中選擇兩個(gè)字段的值插入到t1，數(shù)據(jù)類型必須一致。#1、查看score表的字段約束，發(fā)現(xiàn)sid是自動(dòng)增長(zhǎng)的，所以，只需要插入student_id, course_id, num三個(gè)字段的值就行了,而且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的平均成績(jī)#mysql> select avg(num) from score where course_id = 2;#+----------+#| avg(num) |#+----------+#| 0.0000 |#+----------+#1 row in set (0.01 sec)

#3、獲取所有沒(méi)上過(guò)002課的所有同學(xué)#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語(yǔ)句進(jìn)行整合，插入數(shù)據(jù)到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、按平均成績(jī)從低到高顯示所有學(xué)生的“生物”、“物理”、“體育”三門的課程成績(jī)，按如下形式顯示： 學(xué)生ID,生物,物理,體育,有效課程數(shù),有效平均分；'''

#按平均成績(jī)從低到高顯示有效課程數(shù),有效平均分#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)

#求出生物的成績(jī)#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)#

#以此類推，加上物理和體育的成績(jī)進(jìn)行整合#mysql> select sc.student_id as 學(xué)生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 有效課程數(shù),avg(sc.num) as 有效平均分 from score as sc group by student_id order by avg(sc.num) asc;#+----------+--------+--------+--------+-----------------+-----------------+#| 學(xué)生ID | 生物 | 物理 | 體育 | 有效課程數(shù) | 有效平均分 |#+----------+--------+--------+--------+-----------------+-----------------+#| 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、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程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、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序；'''

#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、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)'''

#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 | 美術(shù) | NULL | NULL | NULL |#+----------+--------+--------+--------+--------+#4 rows in set (0.00 sec)

'''9、查詢每門課程被選修的學(xué)生數(shù)；'''

#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 | 美術(shù) | 0 |#| 1 | 生物 | 12 |#| 2 | 物理 | 16 |#| 3 | 體育 | 12 |#+-----+--------+-------------------+#4 rows in set (0.00 sec)

'''10、查詢同名同姓學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)；'''

#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、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列；'''

#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 | 美術(shù) | 0.0000 |#| 2 | 物理 | 0.0000 |#| 1 | 生物 | 53.4167 |#| 3 | 體育 | 64.4167 |#+-----+--------+-----------+#4 rows in set (0.00 sec)

'''12、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)；'''

#1、先查詢所有學(xué)生的平均成績(jī)#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、過(guò)濾出平均成績(jī)大于85分的，沒(méi)有大于85分的同學(xué)#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、查詢課程名稱為“生物”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)'''

#方法一#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個(gè)左連接并用)#mysql> SELECT student.sid AS 學(xué)號(hào),student.sname AS 姓名,score.num AS 成績(jī) 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;#+--------+--------+--------+#| 學(xué)號(hào) | 姓名 | 成績(jī) |#+--------+--------+--------+#| 1 | 理解 | 10 |#| 2 | 鋼蛋 | 8 |#| 6 | 張四 | 9 |#| 7 | 鐵錘 | 9 |#| 8 | 李三 | 9 |#+--------+--------+--------+#5 rows in set (0.00 sec)

'''14、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；'''

#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、求選了課程的學(xué)生人數(shù)'''

#方法一#mysql> select count(t1.student_id) as 選課總?cè)藬?shù) from (select student_id from score group by student_id) as t1;#+-----------------+#| 選課總?cè)藬?shù) |#+-----------------+#| 16 |#+-----------------+#1 row in set (0.00 sec)#

#方法二#mysql> select count(distinct student_id) as 選課總?cè)藬?shù) from score;#+-----------------+#| 選課總?cè)藬?shù) |#+-----------------+#| 16 |#+-----------------+#1 row in set (0.01 sec)

'''16、查詢選修“張磊老師”所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)；'''

#方法一#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個(gè)左連接并用)#mysql> SELECT student.sid AS 學(xué)號(hào),student.sname AS 姓名,num AS 成績(jī) 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;#+--------+--------+--------+#| 學(xué)號(hào) | 姓名 | 成績(jī) |#+--------+--------+--------+#| 9 | 李一 | 91 |#+--------+--------+--------+#1 row in set (0.00 sec)

#方法三(多個(gè)子查詢套用)#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、查詢各個(gè)課程及相應(yīng)的選修人數(shù)；'''

#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 | 美術(shù) | 0 |#| 1 | 生物 | 12 |#| 2 | 物理 | 16 |#| 3 | 體育 | 12 |#+-----+--------+-------------------+#4 rows in set (0.00 sec)#

'''18、查詢不同課程但成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)；'''

#利用同一張表重命名成兩張表，再比較。#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)#沒(méi)有不同課程一樣成績(jī)的同學(xué)，手動(dòng)修改某個(gè)同學(xué)的成績(jī)查看效果#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、查詢每門課程成績(jī)最好的前兩名；'''

#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 | 美術(shù) | NULL | NULL |#+----------+--------+--------+--------+#4 rows in set (0.00 sec)#

'''20、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)；'''

#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、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名；'''

#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、查詢沒(méi)學(xué)過(guò)“李平”老師講授的任一門課程的學(xué)生姓名；'''

#方法一 (多個(gè)子查詢套用)#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、查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)；'''

#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”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)；'''

#mysql> select student_id from score where course_id=4 and num<60 order by num desc;#Empty set (0.00 sec)#“004”課程沒(méi)有同學(xué)，改成查詢1號(hào)課程看一下效果#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”同學(xué)的“001”課程的成績(jī)；'''

#mysql> delete from score where course_id = 1 and student_id = 2;#Query OK, 1 row affected (0.08 sec)

總結(jié)

以上是生活随笔為你收集整理的python查询mysql表名字动态日期_Python之路day11作业-MySQL表查询的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。