MySQL50道练习题及答案#建⽴学⽣表编号（主键）学⽣姓名⽣⽇性别
CREATE TABLE student(
s_id int PRIMARY key auto_increment,
s_name VARCHAR(20) not null,
s_birthday VARCHAR(20) not null,
s_sex VARCHAR(20) not null
);
#建⽴课程表编号（主键）课程名教师
CREATE TABLE course(
c_id int auto_increment,
c_name VARCHAR(20) not null,
t_id int not null,
PRIMARY KEY(c_id)
);
#建⽴教师表编号（主键）教师姓名
CREATE TABLE teacher(
t_id int auto_increment ,
t_name VARCHAR(20) not null
);
ALTER TABLE teacher ADD CONSTRAINT pk_id PRIMARY key(t_id);
#建⽴成绩表编号（主键）课程编号（主键）成绩
CREATE TABLE score(
s_id int ,
c_id int ,
s_score FLOAT(4,2),
PRIMARY KEY(s_id,c_id)
);
#插⼊学⽣表数据
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘⼥’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘⼥’);
insert into Student values(‘07’ , ‘郑⽵’ , ‘1989-07-01’ , ‘⼥’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘⼥’);
#插⼊课程表数据
insert into Course values(‘01’ , ‘语⽂’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
#插⼊教师表数据
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);
#插⼊成绩表数据
insert into Score values(‘01’ , ‘01’ , 80);
insert into Score values(‘01’ , ‘02’ , 90);
insert into Score values(‘01’ , ‘03’ , 99);
insert into Score values(‘02’ , ‘01’ , 70);
insert into Score values(‘02’ , ‘02’ , 60);
insert into Score values(‘02’ , ‘03’ , 80);
insert into Score values(‘03’ , ‘01’ , 80);
insert into Score values(‘03’ , ‘02’ , 80);
insert into Score values(‘03’ , ‘03’ , 80);
insert into Score values(‘04’ , ‘01’ , 50);
insert into Score values(‘04’ , ‘02’ , 30);
insert into Score values(‘04’ , ‘03’ , 20);
insert into Score values(‘05’ , ‘01’ , 76);
insert into Score values(‘05’ , ‘02’ , 87);
insert into Score values(‘06’ , ‘01’ , 31);
insert into Score values(‘06’ , ‘03’ , 34);
insert into Score values(‘07’ , ‘02’ , 89);
insert into Score values(‘07’ , ‘03’ , 98);
#查询表
SELECT * from student;
SELECT * from course;
SELECT * from score;
SELECT * from teacher;
#1、查询课程编号为“01”的课程⽐“02”的课程成绩⾼的所有学⽣的学号。
SELECT s_id from score s1 inner join score s2 using(s_id)
where s1.s_id=s2.s_id and s1.c_id=‘01’ and s2.c_id=‘02’ and s1.s_score>s2.s_score;
#2、查询平均成绩⼤于60分的学⽣的学号和平均成绩
SELECT s_id,avg(s_score) 平均成绩 from score
GROUP BY s_id HAVING avg(s_score)>60;
#3、查询所有学⽣的学号、姓名、选课数、总成绩
SELECT s_id 学号,s_name 姓名,count(s_id) 选课数,sum(s_score) 总成绩 FROM student inner join score USING(s_id) GROUP BY s_id;
#4、查询姓“张”的⽼师的个数
SELECT count(t_id) from teacher where t_name like ‘张%’;
#5.查询没学过“张三”⽼师课的学⽣的学号、姓名(重点)
SELECT s_id,s_name from student where s_id not in
input file onchange(SELECT s_id from score where c_id = (SELECT c_id from course inner join teacher USING(t_id) where t_name=‘张三’));
#6、查询学过“张三”⽼师所教的所有课的同学的学号、姓名
SELECT s_id,s_name from student where s_id in
(SELECT s_id from score inner join course USING(c_id) join teacher USING(t_id) WHERE t_name=‘张三’);
#7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号、姓名
SELECT s_id,s_name from student inner join score USING(s_id)
where s_id in(SELECT s_id from score left join course USING(c_id)
where c_id=‘01’) and c_id=‘02’;
#8、查询课程编号为“02”的总成绩
SELECT sum(s_score) 总成绩 from score where c_id=‘02’;
#9、查询所有课程成绩⼩于60分的学⽣的学号、姓名
SELECT s_id,s_name from student where s_id not in
(SELECT s_id from score GROUP BY s_id HAVING max(s_score)>=60);
#10、查询没有学全所有课的学⽣的学号、姓名
SELECT s_id,s_name from student join score USING(s_id)
GROUP BY s_id HAVING count(c_id) <(SELECT count(1) from course);
#11、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名
SELECT DISTINCT s_id,s_name from student join score USING(s_id)
where c_id in
( SELECT c_id from score where s_id=‘01’)
and s_id <>‘01’;
#12、查询和“01”号同学所学课程完全相同的其他同学的学号
SELECT s_id,count(score.c_id) from student sc join score USING(s_id) where c_id in
(SELECT c_id from score where s_id=‘01’) and
s_id<>‘01’ GROUP BY s_id HAVING count(1)=
(SELECT count(s_id) from score where s_id=‘01’) and
(SELECT count(1) from score where s_id=‘01’)=(SELECT count(1) from score where s_id=sc.s_id); #13.把“Score”表中“张三”⽼师教的课的成绩都更改为此课程的平均成绩
update score a join
(select avg(s_score) t, Score.c_id from score
join course USING(c_id)
join teacher USING(t_id)
where t_name =‘张三’ group by c_id) b
USING(c_id)
set a.s_score= b.t;
#14、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名(同12题）
SELECT s_id,count(score.c_id) from student sc join score USING(s_id)
where c_id in
(SELECT c_id from score where s_id=‘02’) and
s_id<>‘02’ GROUP BY s_id HAVING count(1)=
(SELECT count(s_id) from score where s_id=‘02’) and
(SELECT count(1) from score where s_id=‘02’)=
ppt滚动条怎么做(SELECT count(1) from score where s_id=sc.s_id);
#15、删除学习“张三”⽼师课的SC表记录
DELETE from score where c_id in
(SELECT c_id from course JOIN teacher USING(t_id) where t_name=‘张三’);
#16.检索"01"课程分数⼩于60，按分数降序排列的学⽣信息
SELECT * from student JOIN score USING(s_id)
where c_id=‘01’ and s_score<60
ORDER BY s_score desc
#17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”（c_id=‘04’）、“企业管理”（c_id=‘01’）、“英语”（c_id=‘06’）三门的课程成绩，按如下形式显⽰：学⽣ID，数据库，企业管理，英语，有效课程数，有效平均分
select s_id as ‘学⽣ID’,
(case when c_id=‘04’ then s_score else NULL end) as ‘数据库’,
(case when c_id=‘01’ then s_score else NULL end) as ‘企业管理’,
(case when c_id=‘06’ then s_score else NULL end) as ‘英语’,
count(c_id) as 有效课程数,
avg(s_score) as 有效平均分
from Score
group by s_id
order by avg(s_score) DESC;
#18、查询各科成绩最⾼和最低的分：以如下的形式显⽰：课程ID，最⾼分，最低分(之前删除了张三⽼师的课程，所以不存在02课程)
SELECT c_id 课程ID,max(s_score) 最⾼分,min(s_score) 最低分 from score GROUP BY c_id;
mysql怎么读英语
#19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低排列，以如下形式显⽰：课程号，课程名，平均成绩，及格百分数
SELECT c_id 课程号,c_name 课程名, avg(s_score) 平均成绩,
concat((SELECT count(1) from score b where a.c_id=b.c_id and s_score>=60)/count(*)*100,"%") 及格百分数
from score a join course USING(c_id)
GROUP BY c_id
match函数跨表使用
javabean实现购物车ORDER BY avg(s_score)
#21、查询不同⽼师所教不同课程平均分从⾼到低显⽰
SELECT *,avg(s_score) 平均分 from score
join course USING(c_id)
join teacher USING(t_id)
GROUP BY t_id ORDER BY avg(s_score) desc
#23、使⽤分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段⼈数：课程ID和课程名称
SELECT score.c_id 课程ID,c_name 课程名称,
sum(case when s_score BETWEEN 85 and 100 then 1 else 0 end) ‘[100-85]’,
sum(case when s_score>=70 and s_score<85 then 1 else 0 end) ‘[85-70]’,
sum(case when s_score>=60 and s_score<70 then 1 else 0 end) ‘[70-60]’,
sum(case when s_score<60 then 1 else 0 end) ‘[<60]’
from score join course USING(c_id) GROUP BY score.c_id,c_name;
#24、查询学⽣平均成绩及其名次
SELECT s_id 学号,avg,
@a:=@a+1 排名
from (SELECT *, avg(s_score) avg from score sc
GROUP BY s_id ORDER BY avg(s_score) desc) as a ,(SELECT @a:=0) as b;
#25、查询各科成绩前三名的记录
SELECT * from score s1 where
(SELECT count(1) from score s2
where s1.s_score<s2.s_score
and s1.c_id=s2.c_id)<=2
order by c_id ,s_score desc;
#26、查询每门课程被选修的学⽣数
SELECT count(1) from score
GROUP BY c_id;
#27、查询出只选修了两门课程的全部学⽣的学号和姓名
SELECT s_id,s_name from student where s_id in
(SELECT s_id from score GROUP BY s_id HAVING count(c_id)=2);
#28、查询男⽣、⼥⽣⼈数
SELECT s_sex,count(1) from student GROUP BY s_sex;
#29、查询名字中含有“风”字的学⽣信息
SELECT * from student where s_name like ‘%风%’;
#30、查询同名同姓学⽣名单并统计同名⼈数
inarray jquerySELECT s_name,count(1) from student a
where s_name in (SELECT s_name from student b where a.s_id<>b.s_id);
#31、1990年出⽣的学⽣名单
SELECT * from student where s_birthday like ‘1990%’;
#32、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩
SELECT student.*,avg(s_score) from student join score
USING(s_id) GROUP BY s_id HAVING avg(s_score)>85 ;
#33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列SELECT avg(s_score) from score GROUP BY c_id ORDER BY avg(s_score),c_id desc;
#34、查询课程名称为“数学”且分数低于60的学⽣姓名和分数
SELECT s_name,s_score from student
join score USING(s_id)
join course USING(c_id)
where c_name=‘数学’ and s_score<60;
#35、查询所有学⽣的选课情况
SELECT DISTINCT s_id,s_name,c_id,c_name from student
join score USING(s_id)
join course USING(c_id);
#36、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数
SELECT s_name,c_name,s_score from student
join score USING(s_id)
join course USING(c_id)
where s_id in
(SELECT s_id from score where s_score>70);
#37、查询不及格的课程并按课程号从⼤到⼩排列
SELECT c_id,c_name,s_score from score
join course USING(c_id)
where s_score<60 ORDER BY c_id desc;
#38、查询课程编号为03且课程成绩在80分以上的学⽣的学号和姓名

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。