MySQL測(cè)試題
一愕贡、表關(guān)系
請(qǐng)創(chuàng)建如下表，并創(chuàng)建相關(guān)約束
二巷屿、操作表
1固以、自行創(chuàng)建測(cè)試數(shù)據(jù)
2、查詢(xún)“生物”課程比“物理”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)嘱巾；
3憨琳、查詢(xún)平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
4旬昭、查詢(xún)所有同學(xué)的學(xué)號(hào)篙螟、姓名、選課數(shù)问拘、總成績(jī)遍略；
5、查詢(xún)姓“李”的老師的個(gè)數(shù)骤坐；
6绪杏、查詢(xún)沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名纽绍；
7蕾久、查詢(xún)學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名拌夏；
8僧著、查詢(xún)學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名障簿；
9霹抛、查詢(xún)課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名卷谈；
10杯拐、查詢(xún)有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名世蔗；
11端逼、查詢(xún)沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名污淋；
12顶滩、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為“001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
13寸爆、查詢(xún)至少學(xué)過(guò)學(xué)號(hào)為“001”同學(xué)所選課程中任意一門(mén)課的其他同學(xué)學(xué)號(hào)和姓名礁鲁；
14盐欺、查詢(xún)和“002”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；
15仅醇、刪除學(xué)習(xí)“葉平”老師課的SC表記錄冗美；
16、向SC表中插入一些記錄析二，這些記錄要求符合以下條件：①?zèng)]有上過(guò)編號(hào)“002”課程的同學(xué)學(xué)號(hào)粉洼；②插入“002”號(hào)課程的平均成績(jī)；
17叶摄、按平均成績(jī)從低到高顯示所有學(xué)生的“語(yǔ)文”属韧、“數(shù)學(xué)”、“英語(yǔ)”三門(mén)的課程成績(jī)蛤吓，按如下形式顯示： 學(xué)生ID,語(yǔ)文,數(shù)學(xué),英語(yǔ),有效課程數(shù),有效平均分宵喂；
18、查詢(xún)各科成績(jī)最高和最低的分：以如下形式顯示：課程ID会傲，最高分锅棕，最低分；
19唆铐、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序哲戚；
20、課程平均分從高到低顯示（現(xiàn)實(shí)任課老師）艾岂；
21顺少、查詢(xún)各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
22、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)王浴；
23脆炎、查詢(xún)出只選修了一門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名；
24氓辣、查詢(xún)男生秒裕、女生的人數(shù)；
25钞啸、查詢(xún)姓“張”的學(xué)生名單几蜻；
26、查詢(xún)同名同姓學(xué)生名單体斩，并統(tǒng)計(jì)同名人數(shù)梭稚；
27、查詢(xún)每門(mén)課程的平均成績(jī)絮吵，結(jié)果按平均成績(jī)升序排列弧烤，平均成績(jī)相同時(shí)，按課程號(hào)降序排列蹬敲；
28暇昂、查詢(xún)平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)莺戒、姓名和平均成績(jī)；
29急波、查詢(xún)課程名稱(chēng)為“數(shù)學(xué)”从铲，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)；
30幔崖、查詢(xún)課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名食店；
31渣淤、求選了課程的學(xué)生人數(shù)
32赏寇、查詢(xún)選修“楊艷”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)价认；
33嗅定、查詢(xún)各個(gè)課程及相應(yīng)的選修人數(shù)；
34用踩、查詢(xún)不同課程但成績(jī)相同的學(xué)生的學(xué)號(hào)渠退、課程號(hào)、學(xué)生成績(jī)脐彩；
35碎乃、查詢(xún)每門(mén)課程成績(jī)最好的前兩名；
36惠奸、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)梅誓；
37、查詢(xún)?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名佛南；
38梗掰、查詢(xún)沒(méi)學(xué)過(guò)“葉平”老師講授的任一門(mén)課程的學(xué)生姓名；
39嗅回、查詢(xún)兩門(mén)以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)及穗；
40、檢索“004”課程分?jǐn)?shù)小于60绵载，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)埂陆；
41、刪除“002”同學(xué)的“001”課程的成績(jī)娃豹；
1焚虱、自行創(chuàng)建測(cè)試數(shù)據(jù)
2、查詢(xún)“生物”課程比“物理”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)培愁；
思路：
獲取所有有生物課程的人（學(xué)號(hào)著摔，成績(jī)） - 臨時(shí)表
獲取所有有物理課程的人（學(xué)號(hào)，成績(jī)） - 臨時(shí)表
根據(jù)【學(xué)號(hào)】連接兩個(gè)臨時(shí)表：
學(xué)號(hào) 物理成績(jī) 生物成績(jī)
然后再進(jìn)行篩選
select A.student_id,sw,ty from
(select student_id,num as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A
left join
(select student_id,num as ty from score left join course on score.course_id = course.cid where course.cname = '體育') as B
on A.student_id = B.student_id where sw > if(isnull(ty),0,ty);
3定续、查詢(xún)平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)谍咆；
思路：
根據(jù)學(xué)生分組禾锤，使用avg獲取平均值，通過(guò)having對(duì)avg進(jìn)行篩選
select student_id,avg(num) from score group by student_id having avg(num) > 60
4摹察、查詢(xún)所有同學(xué)的學(xué)號(hào)恩掷、姓名、選課數(shù)供嚎、總成績(jī)黄娘；
select score.student_id,sum(score.num),count(score.student_id),student.sname
from
score left join student on score.student_id = student.sid
group by score.student_id
5、查詢(xún)姓“李”的老師的個(gè)數(shù)克滴；
select count(tid) from teacher where tname like '李%'
select count(1) from (select tid from teacher where tname like '李%') as B
6逼争、查詢(xún)沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名劝赔；
思路：
先查到“李平老師”老師教的所有課ID
獲取選過(guò)課的所有學(xué)生ID
學(xué)生表中篩選
select * from student where sid not in (
select DISTINCT student_id from score where score.course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老師'
)
)
7誓焦、查詢(xún)學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名着帽；
思路：
先查到既選擇001又選擇002課程的所有同學(xué)
根據(jù)學(xué)生進(jìn)行分組杂伟，如果學(xué)生數(shù)量等于2表示，兩門(mén)均已選擇
select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1
8仍翰、查詢(xún)學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)赫粥、姓名；
同上予借，只不過(guò)將001和002變成 in (葉平老師的所有課)
9越平、查詢(xún)課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名蕾羊；
同第1題
10喧笔、查詢(xún)有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名龟再；
select sid,sname from student where sid in (
select distinct student_id from score where num < 60
)
11书闸、查詢(xún)沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名利凑；
思路：
在分?jǐn)?shù)表中根據(jù)學(xué)生進(jìn)行分組浆劲，獲取每一個(gè)學(xué)生選課數(shù)量
如果數(shù)量 == 總課程數(shù)量，表示已經(jīng)選擇了所有課程
select student_id,sname
from score left join student on score.student_id = student.sid
group by student_id HAVING count(course_id) = (select count(1) from course)
12哀澈、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為“001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名牌借；
思路：
獲取 001 同學(xué)選擇的所有課程
獲取課程在其中的所有人以及所有課程
根據(jù)學(xué)生篩選，獲取所有學(xué)生信息
再與學(xué)生表連接割按，獲取姓名
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id
13膨报、查詢(xún)至少學(xué)過(guò)學(xué)號(hào)為“001”同學(xué)所有課的其他同學(xué)學(xué)號(hào)和姓名；
先找到和001的學(xué)過(guò)的所有人
然后個(gè)數(shù) ＝ 001所有學(xué)科 ＝＝》 其他人可能選擇的更多
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) ＝ (select count(course_id) from score where student_id = 1)
14、查詢(xún)和“002”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名现柠；
個(gè)數(shù)相同
002學(xué)過(guò)的也學(xué)過(guò)
select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
15院领、刪除學(xué)習(xí)“葉平”老師課的score表記錄；
delete from score where course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '葉平'
)
16够吩、向SC表中插入一些記錄比然，這些記錄要求符合以下條件：①?zèng)]有上過(guò)編號(hào)“002”課程的同學(xué)學(xué)號(hào)；②插入“002”號(hào)課程的平均成績(jī)周循；
思路：
由于insert 支持
inset into tb1(xx,xx) select x1,x2 from tb2;
所有强法，獲取所有沒(méi)上過(guò)002課的所有人，獲取002的平均成績(jī)
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
)
17湾笛、按平均成績(jī)從低到高 顯示所有學(xué)生的“語(yǔ)文”饮怯、“數(shù)學(xué)”、“英語(yǔ)”三門(mén)的課程成績(jī)迄本，按如下形式顯示： 學(xué)生ID,語(yǔ)文,數(shù)學(xué),英語(yǔ),有效課程數(shù),有效平均分硕淑；
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 sy,
(select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course on score.course_id = course.cid where course.cname = "體育" and score.student_id=sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc
18课竣、查詢(xún)各科成績(jī)最高和最低的分：以如下形式顯示：課程ID嘉赎，最高分，最低分于樟；
select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;
19公条、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序；
思路：case when .. then
select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;
20迂曲、課程平均分從高到低顯示（現(xiàn)實(shí)任課老師）靶橱；
select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id
21、查詢(xún)各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(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 3,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
22路捧、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù)关霸；
select course_id, count(1) from score group by course_id;
23、查詢(xún)出只選修了一門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名杰扫；
select student.sid, student.sname, count(1) from score
left join student on score.student_id = student.sid
group by course_id having count(1) = 1
24队寇、查詢(xún)男生、女生的人數(shù)章姓；
select * from
(select count(1) as man from student where gender='男') as A ,
(select count(1) as feman from student where gender='女') as B
25佳遣、查詢(xún)姓“張”的學(xué)生名單；
select sname from student where sname like '張%';
26凡伊、查詢(xún)同名同姓學(xué)生名單零渐，并統(tǒng)計(jì)同名人數(shù)；
select sname,count(1) as count from student group by sname;
27系忙、查詢(xún)每門(mén)課程的平均成績(jī)诵盼，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列风宁；
select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc;
28耕腾、查詢(xún)平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)杀糯；
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;
29扫俺、查詢(xún)課程名稱(chēng)為“數(shù)學(xué)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)固翰；
select student.sname,score.num from score
left join course on score.course_id = course.cid
left join student on score.student_id = student.sid
where score.num < 60 and course.cname = '生物'
30狼纬、查詢(xún)課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
select * from score where score.student_id = 3 and score.num > 80
31骂际、求選了課程的學(xué)生人數(shù)
select count(distinct student_id) from score
select count(c) from (
select count(student_id) as c from score group by student_id) as A
32疗琉、查詢(xún)選修“楊艷”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)歉铝；
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;
33盈简、查詢(xún)各個(gè)課程及相應(yīng)的選修人數(shù)；
select course.cname,count(1) from score
left join course on score.course_id = course.cid
group by course_id;
34太示、查詢(xún)不同課程但成績(jī)相同的學(xué)生的學(xué)號(hào)柠贤、課程號(hào)、學(xué)生成績(jī)类缤；
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;
35臼勉、查詢(xún)每門(mén)課程成績(jī)最好的前兩名；
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(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
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
36餐弱、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)宴霸；
select student_id from score group by student_id having count(student_id) > 1
37、查詢(xún)?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名膏蚓；
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);
38瓢谢、查詢(xún)沒(méi)學(xué)過(guò)“葉平”老師講授的任一門(mén)課程的學(xué)生姓名；
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
39驮瞧、查詢(xún)兩門(mén)以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)氓扛；
select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2
40、檢索“004”課程分?jǐn)?shù)小于60剧董，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)幢尚；
select student_id from score where num< 60 and course_id = 4 order by num desc;
41、刪除“002”同學(xué)的“001”課程的成績(jī)翅楼；
delete from score where course_id = 1 and student_id = 2