mysql测试题蔡铜_MySQL：测试题
一，表關系的練習測試
請創建如下表關系，并建立相關約束
一，創建表結構數據：
創建的話肯定先創建沒有關聯的表，老師，課程(關聯老師)，年級，班級(關聯年級)，學生(關聯班級)，
班級任職表 (關聯老師，課堂)
create table teacher(
tid int primary key auto_increment,
tname varchar(16) not null
);
create table class_grade(
gid int primary key auto_increment,
gname varchar(16) not null unique
);
create table course(
cid int primary key auto_increment,
cname varchar(16) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
);
create table class(
cid int primary key auto_increment,
caption varchar(16) not null,
grade_id int not null,
foreign key(grade_id) references class_grade(gid)
on update cascade
on delete cascade
);
create table student(
sid int primary key auto_increment,
sname varchar(16) not null,
gender enum('女','男') not null default '男',
class_id int not null,
foreign key(class_id) references class(cid)
on update cascade
on delete cascade
);
create table score(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
score int not null,
primary key(student_id,course_id),
foreign key(student_id) references student(sid)
on delete cascade
on update cascade,
foreign key(course_id) references course(cid)
on delete cascade
on update cascade
);
create table teach2cls(
tcid int not null unique auto_increment,
tid int not null,
cid int not null,
primary key(tid,cid),
foreign key(tid) references teacher(tid)
on delete cascade
on update cascade,
foreign key(cid) references class(cid)
on delete cascade
on update cascade
);
2，插入表數據
插入數據
老師的數據
年級的數據
班級的數據
課程的數據
學生的數據
成績的數據
老師班級的數據
insert into teacher(tname) values
('張三'),
('李四'),
('王五');
insert into class_grade(gname) values
('一年級'),
('二年級'),
('三年級');
insert into class(caption,grade_id) values
('一年一班',1),
('一年二班',1),
('一年三班',1),
('二年一班',2),
('二年二班',2),
('二年三班',2),
('三年一班',3),
('三年二班',3),
('三年三班',3);
insert into course(cname,teacher_id) values
('生物',1),
('體育',1),
('物理',2),
('數學',2),
('馬克思',3),
('外語',3),
('計算機',3);
insert into student(sname,gender,class_id) values
('喬丹','男',1),
('艾弗森','男',1),
('科比','男',2);
insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(1,3,58),
(2,1,99),
(2,2,99),
(2,3,89),
(3,1,59),
(3,3,30);
insert into teach2cls(tid,cid) values
(1,1),
(1,2),
(1,3),
(1,5),
(2,4),
(2,6),
(2,8),
(2,9),
(2,1),
(2,5),
(3,7),
(3,1),
(3,3),
(3,5),
(3,9);
補充數據
insert into teacher(tname) values
('趙六'),
('苗七');
insert into class_grade(gname) values
('四年級');
insert into class(caption,grade_id) values
('四年一班',4),
('四年二班',4),
('四年三班',4),
('四年四班',4);
insert into course(cname,teacher_id) values
('線性代數',4);
insert into student(sname,gender,class_id) values
('張一','女',3),
('詹姆斯','男',3),
('荷花','女',3),
('杜蘭特','男',3),
('哈登','男',4),
('尼克','男',4),
('青青','女',4),
('阿里扎','男',4);
insert into score(student_id,course_id,score) values
(3,4,60),
(4,1,59),
(4,2,100),
(4,3,90),
(4,4,80),
(5,1,59),
(5,2,33),
(5,3,12),
(5,4,88),
(6,1,100),
(6,2,60),
(6,3,59),
(6,4,100),
(7,1,20),
(7,2,36),
(7,3,57),
(7,4,60),
(8,1,61),
(8,2,59),
(8,3,62),
(8,4,59),
(9,1,60),
(9,2,61),
(9,3,21);
insert into teach2cls(tid,cid) values
(4,1),
(4,2),
(4,3),
(4,4),
(5,1),
(5,2),
(5,3),
(5,4);
二，操作表格內容
1、自行創建測試數據；
上面已經完成。
2、查詢學生總人數；
select count(sid) from student;
3、查詢“生物”課程和“物理”課程成績都及格的學生id和姓名；
思路：獲取所有生物課程的人(學號，成績)--臨時表
獲取所有物理課程的人(學號，成績)--臨時表
根據學號連接兩個臨時表：學號，物理成績，生物成績
然后篩選及格的
select sid,sname
from student
where sid in(
select score.student_id from score inner join course on score.course_id=course.cid
where course.cname in('生物','物理') and score.score >=60
group by score.student_id having count(course_id) = 2);
4、查詢每個年級的班級數，取出班級數最多的前三個年級；
思路：首先分析班級前三的情況，分為班級數相同的情況和班級數不同的情況
如果班級數相同，那么只需要考慮在班級里面統計班級數量即可，
然后在班級年級表中取出對應的年級數目
如果班級數不相同，那么首先班級里面統計班級數量，
然后在按照降序排列，取前三即可
#包含班級數不相同的排名前三年級
select class_grade.gname from class_grade inner join(
select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)
as t1 on class_grade.gid = t1.grade_id;
#包含了班級數相同的排名前三年級
select gname from class_grade where gid in (
select grade_id from class group by grade_id having count(cid) in (
5、查詢平均成績最高和最低的學生的id和姓名以及平均成績；
create view t1 as
select student_id avg(score) as avg_score from score group by student_id;
select sname,avg_score from t1 left join student on t1.student_id =student.sid
where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =
(select min(t1.avg_score) from t1);
6、查詢每個年級的學生人數；
思路：先在學生表和班級表對應一下
然后在對應班級表中查找學生人數
select t1.grade_id,count(t1.sid) as count_student from (
select student.sid ,class.grade_id from student,class
where student.class_id =class.cid) as t1 group by t1.grade_id;
7、查詢每位學生的學號，姓名，選課數，平均成績；
思路：學生表中有學生學號，姓名，性別，班級 成績表中對應成績，所以
我們可以聯立成績表和學生表，并按學生id分類，直接查找即可。
select score.student_id,student.sname,sum(score.course_id),avg(score.score)
from score left join student on score.student_id = student.sid
group by score.student_id;
8、查詢學生編號為“2”的學生的姓名、該學生成績最高的課程名、成績最低的課程名及分數；
思路：首先在成績表中查找學生編號為2 的學生的最大最小成績，學生id，課程id，
然后在課程表和學生表中找到對應的學生姓名和課程名稱，
最后聯立表格得出學生姓名，課程名稱，分數
select student.sname,course.cname,t1.score from (
select student_id,course_id,score from score where student_id = 2 and score in((
select max(score) from score where student_id = 2),
(select min(score) from score where student_id = 2))) as t1
inner join student on t1.student_id = student.sid
inner join course on t1.course_id = course.cid;
9、查詢姓“李”的老師的個數和所帶班級數；
思路：首先在老師表中尋找姓李老師的id
然后在teach2cls中找到老師和班級的聯系，并統計姓李老師所帶的班級數
最后在老師表中查詢老師id和姓名。
select teacher.tid as '姓李id', teacher.tname as '老師姓名' ,GROUP_CONCAT(teach2cls.cid) as '班級數'
from teacher left join teach2cls on teacher.tid = teach2cls.tid
where teacher.tname like '李%' group by teacher.tid;
10、查詢班級數小于5的年級id和年級名；
思路：首先查詢班級表中班級小于5的年級id號碼
然后在年級表中查找對應班級表中的年級id即可
select gid,gname from class_grade where gid in (
select grade_id from class group by grade_id having count(caption)<5
);
11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，示例結果如下；
班級id班級名稱年級年級級別
1
一年一班
一年級
低
select
class.cid as '班級id',
class.caption as '班級名稱',
class_grade.gname as '年級',
case
when class_grade.gid between 1 and 2 then '低'
when class_grade.gid between 3 and 4 then '中'
when class_grade.gid between 5 and 6 then '高' else 0
end as '年級級別'
from class
left join class_grade on class.grade_id = class_grade.gid;
12、查詢學過“張三”老師2門課以上的同學的學號、姓名；
首先找到張三老師的id，
然后聯立成績表和課程表，并在成績表中查看選修張三老師課程數量大于2的學生id
最后在學生表中查找學生的學號，姓名。
select sid,sname from student
where sid in
(
select score.student_id from score
left join course
on score.course_id = course.cid
where course.teacher_id in
(
select tid from teacher
where tname = '張三'
)
group by student_id
having count(course.cid) >2
);
13、查詢教授課程超過2門的老師的id和姓名；
思路：先在course中按照老師的id進行分組，并統計代課大于2門的老師id的總數---臨時表
然后在teacher表中查找老師的id和姓名
select tid,tname from teacher where tid in (
select teacher_id from course group by teacher_id having count(cid)>2);
14、查詢學過編號“1”課程和編號“2”課程的同學的學號、姓名；
思路：創建一個虛擬表，用于查找課程中的編號1和編號2課程
然后在學生表中查找學生的學號和姓名
select sid,sname from student where sid in (
select distinct student_id from score where course_id in (1,2));
15、查詢沒有帶過高年級的老師id和姓名；
思路：在班級表中設定高年級為五六年級，---虛擬表
然后在teach2cls中找到老師和班級的聯系 ---虛擬表
最后在老師表中查詢老師id和姓名
select tid,tname from teacher where tid not in (select tid from teach2cls
where cid in (select cid from class where grade_id in (5,6)));
16、查詢學過“張三”老師所教的所有課的同學的學號、姓名；
思路：首先將張三老師的id從課程表中和老師表中對應起來，并找出他教的課程id ————虛擬表
然后在成績表中查找與上面表對應的課程id所對應的學生的id
最后在學生表中查找學生的學號，姓名。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select cid from course inner join teacher on teacher.tid = course.teacher_id
where teacher.tname = '張三'));
17、查詢帶過超過2個班級的老師的id和姓名；
思路： 先在teac2cls中找到班級cid大于2的老師id(ｔｉｄ)－－－－虛擬表
然后在老師表中找老師ｉｄ和姓名對應的ｉｄ
select tid,tname from teacher where tid in (
select tid from teach2cls group by tid having count(cid)>2);
18、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名；
思路：先在成績表中查找課程2 的學生id和成績，設為表1 ，
再查找課程編號為1的學生id和成績，設為表二
最后在學生表中查找課程2比課程1的成績低的學生的學號和姓名。
select sid,sname from student where sid in (
select t1.student_id from (
select student_id, score from score where course_id = 2 group by student_id) as t1,
select student_id, score from score where course_id = 1 group by student_id) as t2
where t1.student_id = t2.student_id and t1.score < t2.score);
19、查詢所帶班級數最多的老師id和姓名；
思路：首先在老師-課程表中統計老師所帶的課程數量，并按照老師id分類，并取一個
然后在老師表中查找對應老師id和姓名
select tid,tname from teacher where tid =(
select tid from teach2cls group by tid order by count(cid) desc limit 1);
20、查詢有課程成績小于60分的同學的學號、姓名；
思路：先在成績表中查找成績小于６０分的學生ｉｄ
然后學生表中查找學生id與成績表中的學生id對應的學生學號，姓名
select sid,sname from student where sid in (
select distinct student_id from score where score<60 );
21、查詢沒有學全所有課的同學的學號、姓名；
思路：首先分析題目意思，是沒有學完所有課的同學
那么考慮學生應該是選完課程，沒有考試，視為沒有學完
所以首先查找學生選擇的課程，在成績表中是否有對應的成績，如果有則學完，如果沒有則沒有學完
select sid ,sname from student where sid not in(
select student_id from score group by student_id having count(course_id)=
(select count(cid) from course)
);
22、查詢至少有一門課與學號為“1”的同學所學相同的同學的學號和姓名；
思路：首先查找學號為1的學生的成績id，
然后在成績表中按照學號對應上面的成績id
最后在學生表中查找學生的學號，姓名。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id =1) group by student_id);
23、查詢至少學過學號為“1”同學所選課程中任意一門課的其他同學學號和姓名；
思路：首先查找學號為1的學生所選的課程id，
然后再對應其他學生所選的課程id，
最后在學生表中查找學生的學號，姓名。
select sid,sname from student where sid in (
select student_id from score where course_id in (
select course_id from score where student_id = 1)
group by student_id) and sid !=1;
24、查詢和“2”號同學學習的課程完全相同的其他同學的學號和姓名；
思路：首先在成績表中查詢學生2的課程id，
然后進行篩選其他人的課程id和姓名id，不包含2號學生，
最后在學生表中查找學生的學號，姓名。
select sid,sname from student where sid in (
select score.student_id from score ,(
select course_id from score where student_id = 2) as t1
where score.course_id = t1.course_id and score.student_id !=2
group by score.student_id
having count(score.course_id) =(
select count(course_id) from score where student_id = 2));
25、刪除學習“張三”老師課的score表記錄；
思路：首先在score表中找到對應張三老師課程，
然后刪除即可
delete from score where course_id in (
select course.cid from course,teacher where
course.teacher_id =teacher.tid and teacher.tname = '張三');
26、向score表中插入一些記錄，這些記錄要求符合以下條件：
①沒有上過編號“2”課程的同學學號；
②插入“2”號課程的平均成績；
思路：首先在score找出沒有上過編號2課程的同學id，
然后在成績表中找到編號2的學生的所有成績，取平均值
最后插入數據即可。
insert into score(student_id,course_id,score)
select t1.sid,2,t2.avg from (
select sid from student where sid not in (
select student_id from score where course_id = 2)) as t1,
(select avg(score) as avg from score group by course_id having course_id =2) as t2;
27、按平均成績從低到高顯示所有學生的“語文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分；
思路：注意平均成績是由低到高desc
最重要的是查詢各科成績，
在課程表中找到成績表中對應的課程id，然后在成績表中查找對應的成績
select sc.student_id,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '語文' and score.student_id = sc.student_id) as Chinese,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '數學' and score.student_id = sc.student_id) as Math,
(select score.score from score left join course on score.course_id = course.cid
where course.cname = '外語' and score.student_id = sc.student_id) as English,
count(sc.course_id),avg(sc.score)
from score as sc group by sc.student_id order by avg(sc.score) asc;
28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
思路：直接在score中查找課程id，最高分數，最低分數
select course_id ,max(score),min(score) from score
group by course_id;
29、按各科平均成績從低到高和及格率的百分數從高到低順序；
思路：平均成績asc 及格率desc
在score中找到學生的平均成績，并求出及格率。
select course_id,avg(score) as avg_score,
sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
from score group by course_id order by avg(score) asc,percent desc;
30、課程平均分從高到低顯示(現實任課老師)；
思路：查找成績表中的各科平均分數，并讓分數對應課程表中的課程id
然后把課程表中的課程id對應的老師課程表的老師id
最后在老師表中查詢老師id和姓名
select t1.course_id,t1.avg_score,teacher.tname from course,teacher,
(select course_id,avg(score) as avg_score from score group by course_id ) as t1
where course.cid = t1.course_id and course.teacher_id = teacher.tid
order by avg_score desc;
31、查詢各科成績前三名的記錄(不考慮成績并列情況)
select score.sid,score.student_id, score.course_id,score.score,
t1.first_score,t1.second_score,t1.third_score
from score inner join (
select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id
order by score desc limit 0,1) as first_score,
(select score from score as s3 where s1.course_id = s3.course_id
order by score desc limit 1,1) as second_score,
(select score from score as s4 where s1.course_id = s4.course_id
order by score desc limit 2,1) as third_score
from score as s1) as t1 on score.sid = t1.sid
where score.score in (t1.first_score,t1.second_score,t1.third_score);
32、查詢每門課程被選修的學生數；
思路：在成績表中查找課程id，每門課的學生總數，
最后在課程表中找到對應的課程名稱
select course.cname as '課程名稱',t1.student_num as '學生數量' from course,
(select course_id,count(student_id) as student_num from score
group by course_id) as t1 where course.cid = t1.course_id;
33、查詢選修了2門以上課程的全部學生的學號和姓名；
思路：在成績表中查找課程id大于2們的學生id
然后在學生表中查找對應的學生的學號和姓名
select sid,sname from student where sid in (
select student_id from score group by student_id having count(course_id)>2);
34、查詢男生、女生的人數，按倒序排列；
思路： 在學生表中按照性別分類 按照數量排序desc
select gender,count(sid) as num from student
group by gender order by num desc;
35、查詢姓“張”的學生名單；
思路：在學生表中查找姓張的學生名單
select sid,sname,gender from student where sname like '張%';
36、查詢同名同姓學生名單，并統計同名人數；
思路：直接在學生表中查看學生姓名相同的學生，并統計人數
select sname,count(sname) from student group by sname having count(sname)>1;
37、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列；
思路：在成績表中按照課程id 查找學生的平均成績
select course_id,avg(score) as avg_score from score
group by course_id order by avg_score,course_id desc;
38、查詢課程名稱為“數學”，且分數低于60的學生姓名和分數；
思路：先在course中查找課程為數學的課程id號碼，
然后在score中查找數學分數低于60的學生id,分數
最后在學生表中查找對于id 的學生姓名
select student.sname,score.score from score left join student
on score.student_id = student.sid where score.course_id = (
select cid from course where cname ='數學') and score.score
39、查詢課程編號為“3”且課程成績在80分以上的學生的學號和姓名；
思路：先創建一個課程編號為3 且成績在80分以上的學生id表，在score中 --虛擬表
然后在student中查找對應id的學生姓名
select sid,sname from student where sid in (
select student_id from score where score> 80 and course_id = 3
);
40、求選修了課程的學生人數
思路：直接在成績表中按照課程id排序，并統計學生id即可
select course_id,count(student_id) from score group by course_id;
41、查詢選修“王五”老師所授課程的學生中，成績最高和最低的學生姓名及其成績；
思路：首先在老師表中尋找姓王老師的id，
然后對應課程表中對應的所教課程id，
然后在score中查找課程所對應的成績和學生id
最后在學生表中查找學生的學號，姓名。
select student.sname,score,score from score
left join student on score.student_id = student.sid where course_id in (
select cid from course where teacher_id in (
select tid from teacher where tname = '王五'))
order by score.score desc limit 1;
42、查詢各個課程及相應的選修人數；
思路：聯立課程表中的課程id和成績表中的課程id，
然后查找各個課程對應的選修人數
select course.cname,count(student_id) from score
left join course on score.course_id = course.cid group by course_id;
43、查詢不同課程但成績相同的學生的學號、課程號、學生成績；
思路：查找不同學生之間，課程不同成績相同
查找同一個學生，課程不同成績相同
的學生，課程號，學生成績
#1，不同學生之間
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.course_id != s2.course_id and s1.score = s2.score;
#2，同一個學生
select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
from score as s1,score as s2
where s1.student_id = s2.student_id and
s1.course_id != s2.course_id and s1.score = s2.score;
44、查詢每門課程成績最好的前兩名學生id和姓名；
select
student.sid,
student.sname,
t2.course_id,
t2.score,
t2.first_score,
t2.second_score
from
student
inner join (
select
score.student_id,
score.course_id,
score.score,
t1.first_score,
t1.second_score
from
score
inner join (
select
s1.sid,
(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
from
score as s1
) as t1 on score.sid = t1.sid
where
score.score in (
t1.first_score,
t1.second_score
)
) as t2 on student.sid = t2.student_id;
45、檢索至少選修兩門課程的學生學號；
思路：在score表中直接查找大于2門課程的學生id
select student_id from score group by student_id having
count(course_id)>=2;
46、查詢沒有學生選修的課程的課程號和課程名；
思路：在成績表中按照課程id分組作為一個臨時表
如果在課程表中，id沒有在上面的臨時表中，則就是沒有學生選修
select cid,cname from course where cid not in
(select course_id from score group by course_id);
47、查詢沒帶過任何班級的老師id和姓名；
思路：在老師-課程表中按照老師分組作為一個臨時表
如果在老師表中，id沒有在這個臨時表，則就是沒有帶過任何班級
select tid tname from teacher where tid not in (
select tid from teach2cls group by tid);
48、查詢有兩門以上課程超過80分的學生id及其平均成績；
思路：首先，在成績表中獲取有兩門課程成績大于80分的學生id，---臨時表
然后在成績表中查找其id和平均成績
或者在score表中直接查找大于2門課程的學生id和平均成績
select student_id,avg(score) from score
where student_id in (
select student_id from score where score > 80 group by student_id
having count(course_id) > 2);
select student_id,avg(score) from score
where score >80 group by student_id having count(course_id) >2;
49、檢索“3”課程分數小于60，按分數降序排列的同學學號；
思路：查找成績表中課程三而且分數小于60的學生學號，并按照分數降序排列desc
select student_id,score from score where course_id = 3 and score<60
order by score desc;
50、刪除編號為“2”的同學的“1”課程的成績；
思路：首先在成績表中，先把編號為2和課程為1的找到，
然后刪除在成績表中對應學生的成績
delete from score where sid =
(select sid from score where student_id = 2 and course_id=1
);
51、查詢同時選修了物理課和生物課的學生id和姓名；
思路：在課程中首先找到物理，生物的id，
然后在成績表中對應課程的id，此時找到了生物課和物理課的id
最后在學生表中，找到學生id和姓名
select sid,sname from student where sid in(
select student_id from score where course_id in (
select cid from course where course.cname in('物理','生物'))
group by student_id having count(course_id) = 2
);
所有的題目代碼*的意思：
題目中打***??? 表示難，掌握不深
題目中打*****? 表示復制別人的
1、自行創建測試數據；
見create_tabledata.txt
insert_tabledata.txt2、查詢學生總人數；select count(sid) fromstudent;3、查詢“生物”課程和“物理”課程成績都及格的學生id和姓名；
思路：獲取所有生物課程的人(學號，成績)--臨時表
獲取所有物理課程的人(學號，成績)--臨時表
根據學號連接兩個臨時表：學號，物理成績，生物成績
然后篩選及格的selectsid,snamefromstudentwhere sid in(select score.student_id from score inner join course on score.course_id=course.cidwhere course.cname in('生物','物理') and score.score >=60group by score.student_id having count(course_id)= 2);***4、查詢每個年級的班級數，取出班級數最多的前三個年級；
思路：首先分析班級前三的情況，分為班級數相同的情況和班級數不同的情況
如果班級數相同，那么只需要考慮在班級里面統計班級數量即可，
然后在班級年級表中取出對應的年級數目
如果班級數不相同，那么首先班級里面統計班級數量，
然后在按照降序排列，取前三即可
#包含班級數不相同的排名前三年級select class_grade.gname fromclass_grade inner join(select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)as t1 on class_grade.gid =t1.grade_id;
#包含了班級數相同的排名前三年級select gname from class_grade where gid in(select grade_id from class group by grade_id having count(cid) in(*****5、查詢平均成績最高和最低的學生的id和姓名以及平均成績；
create view t1as
select student_id avg(score) as avg_score fromscore group by student_id;select sname,avg_score from t1 left join student on t1.student_id =student.sidwhere t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =(select min(t1.avg_score) fromt1);6、查詢每個年級的學生人數；
思路：先在學生表和班級表對應一下
然后在對應班級表中查找學生人數select t1.grade_id,count(t1.sid) as count_student from(select student.sid ,class.grade_id from student,class
where student.class_id =class.cid) ast1 group by t1.grade_id;7、查詢每位學生的學號，姓名，選課數，平均成績；
思路：學生表中有學生學號，姓名，性別，班級 成績表中對應成績，所以
我們可以聯立成績表和學生表，并按學生id分類，直接查找即可。selectscore.student_id,student.sname,sum(score.course_id),avg(score.score)from score left join student on score.student_id =student.sid
group by score.student_id;***8、查詢學生編號為“2”的學生的姓名、該學生成績最高的課程名、成績最低的課程名及分數；
思路：首先在成績表中查找學生編號為2 的學生的最大最小成績，學生id，課程id，
然后在課程表和學生表中找到對應的學生姓名和課程名稱，
最后聯立表格得出學生姓名，課程名稱，分數select student.sname,course.cname,t1.score from(select student_id,course_id,score from score where student_id = 2 and score in((select max(score) from score where student_id = 2),
(select min(score) from score where student_id = 2))) ast1
inner join student on t1.student_id=student.sid
inner join course on t1.course_id=course.cid;9、查詢姓“李”的老師的個數和所帶班級數；
思路：首先在老師表中尋找姓李老師的id
然后在teach2cls中找到老師和班級的聯系，并統計姓李老師所帶的班級數
最后在老師表中查詢老師id和姓名。select teacher.tid as '姓李id', teacher.tname as '老師姓名' ,GROUP_CONCAT(teach2cls.cid) as '班級數'
from teacher left join teach2cls on teacher.tid =teach2cls.tidwhere teacher.tname like '李%'group by teacher.tid;10、查詢班級數小于5的年級id和年級名；
思路：首先查詢班級表中班級小于5的年級id號碼
然后在年級表中查找對應班級表中的年級id即可select gid,gname from class_grade where gid in(select grade_id from class group by grade_id having count(caption)<5);*****11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，
示例結果如下；
班級id 班級名稱 年級 年級級別1一年一班 一年級 低select
class.cid as '班級id',class.caption as '班級名稱',
class_grade.gnameas '年級',casewhen class_grade.gid between1 and 2 then '低'when class_grade.gid between3 and 4 then '中'when class_grade.gid between5 and 6 then '高' else 0endas '年級級別'
from classleft join class_grade onclass.grade_id =class_grade.gid;12、查詢學過“張三”老師2門課以上的同學的學號、姓名；
首先找到張三老師的id，
然后聯立成績表和課程表，并在成績表中查看選修張三老師課程數量大于2的學生id
最后在學生表中查找學生的學號，姓名。select sid,sname fromstudentwhere sid in(select score.student_id fromscore
left join course
on score.course_id=course.cidwhere course.teacher_id in(select tid fromteacherwhere tname = '張三')
group by student_id
having count(course.cid)>2);13、查詢教授課程超過2門的老師的id和姓名；
思路：先在course中按照老師的id進行分組，并統計代課大于2門的老師id的總數---臨時表
然后在teacher表中查找老師的id和姓名select tid,tname from teacher where tid in(select teacher_id from course group by teacher_id having count(cid)>2);14、查詢學過編號“1”課程和編號“2”課程的同學的學號、姓名；
思路：創建一個虛擬表，用于查找課程中的編號1和編號2課程
然后在學生表中查找學生的學號和姓名select sid,sname from student where sid in(select distinct student_id from score where course_id in (1,2));15、查詢沒有帶過高年級的老師id和姓名；
思路：在班級表中設定高年級為五六年級，---虛擬表
然后在teach2cls中找到老師和班級的聯系---虛擬表
最后在老師表中查詢老師id和姓名select tid,tname from teacher where tid not in (select tid fromteach2clswhere cid in (select cid from class where grade_id in (5,6)));16、查詢學過“張三”老師所教的所有課的同學的學號、姓名；
思路：首先將張三老師的id從課程表中和老師表中對應起來，并找出他教的課程id ————虛擬表
然后在成績表中查找與上面表對應的課程id所對應的學生的id
最后在學生表中查找學生的學號，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select cid from course inner join teacher on teacher.tid =course.teacher_idwhere teacher.tname = '張三'));17、查詢帶過超過2個班級的老師的id和姓名；
思路： 先在teac2cls中找到班級cid大于2的老師id(ｔｉｄ)－－－－虛擬表
然后在老師表中找老師ｉｄ和姓名對應的ｉｄselect tid,tname from teacher where tid in(select tid from teach2cls group by tid having count(cid)>2);18、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名；
思路：先在成績表中查找課程2 的學生id和成績，設為表1 ，
再查找課程編號為1的學生id和成績，設為表二
最后在學生表中查找課程2比課程1的成績低的學生的學號和姓名。select sid,sname from student where sid in(select t1.student_id from(select student_id, score from score where course_id = 2 group by student_id) ast1,select student_id, score from score where course_id = 1 group by student_id) ast2where t1.student_id = t2.student_id and t1.score
思路：首先在老師-課程表中統計老師所帶的課程數量，并按照老師id分類，并取一個
然后在老師表中查找對應老師id和姓名select tid,tname from teacher where tid =(select tid from teach2cls group by tid order by count(cid) desc limit 1);20、查詢有課程成績小于60分的同學的學號、姓名；
思路：先在成績表中查找成績小于６０分的學生ｉｄ
然后學生表中查找學生id與成績表中的學生id對應的學生學號，姓名select sid,sname from student where sid in(select distinct student_id from score where score<60);21、查詢沒有學全所有課的同學的學號、姓名；
思路：首先分析題目意思，是沒有學完所有課的同學
那么考慮學生應該是選完課程，沒有考試，視為沒有學完
所以首先查找學生選擇的課程，在成績表中是否有對應的成績，如果有則學完，如果沒有則沒有學完select sid ,sname from student where sid not in(select student_id from score group by student_id having count(course_id)=(select count(cid) fromcourse)
);22、查詢至少有一門課與學號為“1”的同學所學相同的同學的學號和姓名；
思路：首先查找學號為1的學生的成績id，
然后在成績表中按照學號對應上面的成績id
最后在學生表中查找學生的學號，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select course_id from score where student_id =1) group by student_id);23、查詢至少學過學號為“1”同學所選課程中任意一門課的其他同學學號和姓名；
思路：首先查找學號為1的學生所選的課程id，
然后再對應其他學生所選的課程id，
最后在學生表中查找學生的學號，姓名。select sid,sname from student where sid in(select student_id from score where course_id in(select course_id from score where student_id = 1)
group by student_id) and sid!=1;***24、查詢和“2”號同學學習的課程完全相同的其他同學的學號和姓名；
思路：首先在成績表中查詢學生2的課程id，
然后進行篩選其他人的課程id和姓名id，不包含2號學生，
最后在學生表中查找學生的學號，姓名。select sid,sname from student where sid in(select score.student_id fromscore ,(select course_id from score where student_id = 2) ast1where score.course_id = t1.course_id and score.student_id !=2group by score.student_id
having count(score.course_id)=(select count(course_id) from score where student_id = 2));25、刪除學習“張三”老師課的score表記錄；
思路：首先在score表中找到對應張三老師課程，
然后刪除即可
deletefrom score where course_id in(select course.cid from course,teacher wherecourse.teacher_id=teacher.tid and teacher.tname = '張三');26、向score表中插入一些記錄，這些記錄要求符合以下條件：
①沒有上過編號“2”課程的同學學號；②插入“2”號課程的平均成績；
思路：首先在score找出沒有上過編號2課程的同學id，
然后在成績表中找到編號2的學生的所有成績，取平均值
最后插入數據即可。
insert into score(student_id,course_id,score)select t1.sid,2,t2.avg from(select sid from student where sid not in(select student_id from score where course_id = 2)) ast1,
(select avg(score) as avg from score group by course_id having course_id =2) ast2;27、按平均成績從低到高顯示所有學生的“語文”、“數學”、“英語”三門的課程成績，
按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分；
思路：注意平均成績是由低到高desc
最重要的是查詢各科成績，
在課程表中找到成績表中對應的課程id，然后在成績表中查找對應的成績selectsc.student_id,
(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '語文' and score.student_id = sc.student_id) asChinese,
(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '數學' and score.student_id = sc.student_id) asMath,
(select score.score from score left join course on score.course_id =course.cidwhere course.cname = '外語' and score.student_id = sc.student_id) asEnglish,
count(sc.course_id),avg(sc.score)from score assc group by sc.student_id order by avg(sc.score) asc;28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
思路：直接在score中查找課程id，最高分數，最低分數select course_id ,max(score),min(score) fromscore
group by course_id;29、按各科平均成績從低到高和及格率的百分數從高到低順序；
思路：平均成績asc 及格率desc
在score中找到學生的平均成績，并求出及格率。select course_id,avg(score) asavg_score,
sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 aspercentfromscore group by course_id order by avg(score) asc,percent desc;30、課程平均分從高到低顯示(顯示任課老師)；
思路：查找成績表中的各科平均分數，并讓分數對應課程表中的課程id
然后把課程表中的課程id對應的老師課程表的老師id
最后在老師表中查詢老師id和姓名select t1.course_id,t1.avg_score,teacher.tname fromcourse,teacher,
(select course_id,avg(score) as avg_score from score group by course_id ) ast1where course.cid = t1.course_id and course.teacher_id =teacher.tid
order by avg_score desc;*****31、查詢各科成績前三名的記錄(不考慮成績并列情況)selectscore.sid,score.student_id, score.course_id,score.score,
t1.first_score,t1.second_score,t1.third_scorefromscore inner join (select s1.sid,(select score from score as s2 where s1.course_id =s2.course_id
order by score desc limit0,1) asfirst_score,
(select score from score as s3 where s1.course_id =s3.course_id
order by score desc limit1,1) assecond_score,
(select score from score as s4 where s1.course_id =s4.course_id
order by score desc limit2,1) asthird_scorefrom score as s1) as t1 on score.sid =t1.sidwhere score.score in(t1.first_score,t1.second_score,t1.third_score);32、查詢每門課程被選修的學生數；
思路：在成績表中查找課程id，每門課的學生總數，
最后在課程表中找到對應的課程名稱select course.cname as '課程名稱',t1.student_num as '學生數量' fromcourse,
(select course_id,count(student_id) as student_num fromscore
group by course_id)as t1 where course.cid =t1.course_id;33、查詢選修了2門以上課程的全部學生的學號和姓名；
思路：在成績表中查找課程id大于2們的學生id
然后在學生表中查找對應的學生的學號和姓名select sid,sname from student where sid in(select student_id from score group by student_id having count(course_id)>2);34、查詢男生、女生的人數，按倒序排列；
思路： 在學生表中按照性別分類 按照數量排序descselect gender,count(sid) as num fromstudent
group by gender order by num desc;35、查詢姓“張”的學生名單；
思路：在學生表中查找姓張的學生名單select sid,sname,gender from student where sname like '張%';36、查詢同名同姓學生名單，并統計同名人數；
思路：直接在學生表中查看學生姓名相同的學生，并統計人數select sname,count(sname) from student group by sname having count(sname)>1;37、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列；
思路：在成績表中按照課程id 查找學生的平均成績select course_id,avg(score) as avg_score fromscore
group by course_id order by avg_score,course_id desc;38、查詢課程名稱為“數學”，且分數低于60的學生姓名和分數；
思路：先在course中查找課程為數學的課程id號碼，
然后在score中查找數學分數低于60的學生id,分數
最后在學生表中查找對于id 的學生姓名select student.sname,score.score fromscore left join student
on score.student_id= student.sid where score.course_id =(select cid from course where cname ='數學') and score.score
思路：先創建一個課程編號為3 且成績在80分以上的學生id表，在score中--虛擬表
然后在student中查找對應id的學生姓名select sid,sname from student where sid in(select student_id from score where score> 80 and course_id = 3);40、求選修了課程的學生人數
思路：直接在成績表中按照課程id排序，并統計學生id即可select course_id,count(student_id) fromscore group by course_id;41、查詢選修“王五”老師所授課程的學生中，成績最高和最低的學生姓名及其成績；
思路：首先在老師表中尋找姓王老師的id，
然后對應課程表中對應的所教課程id，
然后在score中查找課程所對應的成績和學生id
最后在學生表中查找學生的學號，姓名。select student.sname,score,score fromscore
left join student on score.student_id= student.sid where course_id in(select cid from course where teacher_id in(select tid from teacher where tname = '王五'))
order by score.score desc limit1;42、查詢各個課程及相應的選修人數；
思路：聯立課程表中的課程id和成績表中的課程id，
然后查找各個課程對應的選修人數select course.cname,count(student_id) fromscore
left join course on score.course_id=course.cid group by course_id;43、查詢不同課程但成績相同的學生的學號、課程號、學生成績；
思路：查找不同學生之間，課程不同成績相同
查找同一個學生，課程不同成績相同
的學生，課程號，學生成績
#1，不同學生之間selectdistinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.scorefrom score as s1,score ass2where s1.course_id != s2.course_id and s1.score =s2.score;
#2，同一個學生selectdistinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.scorefrom score as s1,score ass2where s1.student_id =s2.student_id and
s1.course_id!= s2.course_id and s1.score =s2.score;*****44、查詢每門課程成績最好的前兩名學生id和姓名；selectstudent.sid,
student.sname,
t2.course_id,
t2.score,
t2.first_score,
t2.second_scorefromstudent
inner join (selectscore.student_id,
score.course_id,
score.score,
t1.first_score,
t1.second_scorefromscore
inner join (selects1.sid,
(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) asfirst_score,
(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) assecond_scorefromscoreass1
)as t1 on score.sid =t1.sidwherescore.scorein(
t1.first_score,
t1.second_score
)
)as t2 on student.sid =t2.student_id;45、檢索至少選修兩門課程的學生學號；
思路：在score表中直接查找大于2門課程的學生idselect student_id fromscore group by student_id having
count(course_id)>=2;46、查詢沒有學生選修的課程的課程號和課程名；
思路：在成績表中按照課程id分組作為一個臨時表
如果在課程表中，id沒有在上面的臨時表中，則就是沒有學生選修select cid,cname from course where cid not in(select course_id fromscore group by course_id);47、查詢沒帶過任何班級的老師id和姓名；
思路：在老師-課程表中按照老師分組作為一個臨時表
如果在老師表中，id沒有在這個臨時表，則就是沒有帶過任何班級select tid tname from teacher where tid not in(select tid fromteach2cls group by tid);48、查詢有兩門以上課程超過80分的學生id及其平均成績；
思路：首先，在成績表中獲取有兩門課程成績大于80分的學生id，---臨時表
然后在成績表中查找其id和平均成績
或者在score表中直接查找大于2門課程的學生id和平均成績select student_id,avg(score) fromscorewhere student_id in(select student_id from score where score > 80group by student_id
having count(course_id)> 2);select student_id,avg(score) fromscorewhere score >80 group by student_id having count(course_id) >2;49、檢索“3”課程分數小于60，按分數降序排列的同學學號；
思路：查找成績表中課程三而且分數小于60的學生學號，并按照分數降序排列descselect student_id,score from score where course_id = 3 and score<60order by score desc;50、刪除編號為“2”的同學的“1”課程的成績；
思路：首先在成績表中，先把編號為2和課程為1的找到，
然后刪除在成績表中對應學生的成績
deletefrom score where sid =(select sid from score where student_id = 2 and course_id=1);51、查詢同時選修了物理課和生物課的學生id和姓名；
思路：在課程中首先找到物理，生物的id，
然后在成績表中對應課程的id，此時找到了生物課和物理課的id
最后在學生表中，找到學生id和姓名select sid,sname from student where sid in(select student_id from score where course_id in(select cid from course where course.cname in('物理','生物'))
group by student_id having count(course_id)= 2);
View Code
總結
以上是生活随笔為你收集整理的mysql测试题蔡铜_MySQL：测试题的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Python实现淘宝爬取——奶粉销售信息
- 下一篇： 八款好用的浏览器兼容性测试工具推荐