--建表
--学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);
--课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
--教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
--成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
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);
MySQL经典练习题及答案，常用SQL语句练习50题
=================================================================================
SELECT * from student SELECT * from course SELECT * from teacher SELECT * from score
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT temp.s_id,temp.score1 ,temp.score2 ,student.* from ( (SELECT t1.s_id,t1.score1 ,t2.score2 from (SELECT s1.s_score as score1 ,s1.s_id from score s1 where s1.c_id=01) t1 LEFT JOIN (SELECT s2.s_score as score2,s2.s_id from score s2 where s2.c_id=02) t2 on t1.s_id =t2.s_id where t1.score1>t2.score2)) temp LEFT JOIN student on temp.s_id=student.s_id
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT temp.s_id,temp.score1 ,temp.score2 ,student.* from ( (SELECT t1.s_id,t1.score1 ,t2.score2 from (SELECT s1.s_score as score1 ,s1.s_id from score s1 where s1.c_id=01) t1 LEFT JOIN (SELECT s2.s_score as score2,s2.s_id from score s2 where s2.c_id=02) t2 on t1.s_id =t2.s_id where t1.score1<t2.score2)) temp LEFT JOIN student on temp.s_id=student.s_id
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT score.s_id ,AVG(score.s_score) ac,student.s_name from score
left join student
on score.s_id=student.s_id
GROUP BY score.s_id HAVING ac>=60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT score.s_id ,AVG(score.s_score) ac,student.s_name from score
left join student
on score.s_id=student.s_id
GROUP BY score.s_id HAVING ac<60
UNION
SELECT student.s_id,IFNULL(AVG(score.s_score),0) ac, student.s_name from student
LEFT JOIN score
on student.s_id=score.s_id
where score.s_id is null
GROUP BY student.s_id
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT student.s_id, student.s_name,COUNT(score.c_id) as 选课总数,sum(score.s_score) FROM student LEFT JOIN score on student.s_id=score.s_id GROUP BY student.s_id
-- 6、查询"李"姓老师的数量 SELECT count(0) from teacher where teacher.t_name like '李%'
-- 7、查询学过"张三"老师授课的同学的信息 SELECT * from (SELECT s_id from (SELECT course.c_id from course LEFT JOIN teacher on course.t_id=teacher.t_id where teacher.t_name = '张三' )t left join score on score.c_id=t.c_id )s LEFT JOIN student on s.s_id=student.s_id
-- 8、查询没学过"张三"老师授课的同学的信息 SELECT * from student WHERE student.s_id not in ( SELECT student.s_id from (SELECT s_id from (SELECT course.c_id from course LEFT JOIN teacher on course.t_id=teacher.t_id where teacher.t_name = '张三' )t left join score on score.c_id=t.c_id )s LEFT JOIN student on s.s_id=student.s_id)
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * from (SELECT score.s_id from score where score.c_id =01 and score.s_id in (SELECT score.s_id from score where score.c_id =02)) t LEFT JOIN student on t.s_id=student.s_id
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 SELECT * from (SELECT score.s_id from score where score.c_id =01 and score.s_id not in (SELECT score.s_id from score where score.c_id =02)) t LEFT JOIN student on t.s_id=student.s_id
-- 11、查询没有学全所有课程的同学的信息 SELECT * from student where student.s_id in( select s_id from score where s_id not in ( SELECT score.s_id from score where score.s_id in (SELECT score.s_id from score where score.c_id =01) and score.s_id in (SELECT score.s_id from score where score.c_id =02) and score.s_id in (SELECT score.s_id from score where score.c_id =03) ))
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 SELECT * from ( SELECT score.s_id from score where score.c_id in ( SELECT score.c_id from score where score.s_id=01) GROUP BY score.s_id) t LEFT JOIN student on t.s_id=student.s_id
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 SELECT * from (SELECT score.s_id from score where score.c_id in ( SELECT score.c_id from score where score.s_id=01) and score.s_id!=01 GROUP BY score.s_id having count(1)=(select count(1) from score where s_id='01') ) t LEFT JOIN student on t.s_id=student.s_id
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * from student where student.s_id not in (
(SELECT DISTINCT score.s_id from score where score.c_id in (
SELECT course.c_id from course
LEFT JOIN teacher
on course.t_id =teacher.t_id
where teacher.t_name ='张三' ) ) )
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 SELECT * from ( SELECT score.s_id ,avg(score.s_score) from score where score.s_score<60 GROUP BY score.s_id HAVING COUNT(1)>=2) t LEFT JOIN student on t.s_id=student.s_id
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息 SELECT * from (SELECT * from score where score.s_score<60 and score.c_id=01 ORDER BY score.s_score desc) t LEFT JOIN student on t.s_id=student.s_id
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT t.s_id,t.ac as 平均分, IFNULL((SELECT score.s_score from score where score.s_id=t.s_id and score.c_id=01),0) as c1, (SELECT score.s_score from score where score.s_id=t.s_id and score.c_id=02) as c2, (SELECT score.s_score from score where score.s_id=t.s_id and score.c_id=03) as c3 from ( SELECT score.s_id,avg(score.s_score) as ac from score GROUP BY score.s_id ) t ORDER BY t.ac desc
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 SELECT * from ( SELECT score.c_id,max(score.s_score),min(score.s_score), avg(score.s_score) from score GROUP BY score.c_id) t LEFT JOIN course on t.c_id=course.c_id
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- 20、查询学生的总成绩并进行排名 SELECT t.*,@a:=@a+1 as 排名 from (SELECT score.s_id,sum(score.s_score) alla from score GROUP BY score.s_id ORDER BY alla desc) t,(SELECT @a:=0) s
-- 21、查询不同老师所教不同课程平均分从高到低显示 SELECT * from ( SELECT course.c_id,teacher.t_id,teacher.t_name from course left join teacher on course.t_id=teacher.t_id ) a LEFT JOIN (SELECT score.c_id,avg(score.s_score) pjf from score GROUP BY score.c_id) t on a.c_id=t.c_id ORDER BY t.pjf desc
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id='01' ORDER BY a.s_score desc LIMIT 1,2 )c left join student d on c.s_id=d.s_id UNION select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id='02' ORDER BY a.s_score desc LIMIT 1,2 )c left join student d on c.s_id=d.s_id UNION select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id='03' ORDER BY a.s_score desc LIMIT 1,2 )c left join student d on c.s_id=d.s_id
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT score.c_id,
course.c_name,
COUNT(score.s_id) 总人数,
sum(CASE when score.s_score>=0 and score.s_score<=60 THEN 1 ELSE 0 end) '[0-60]',
(sum(CASE when score.s_score>=0 and score.s_score<=60 THEN 1 ELSE 0 end)/COUNT(score.s_id))*100 '[0-60]及所占百分比',
sum(CASE when score.s_score>60 and score.s_score<=70 THEN 1 ELSE 0 end) '[60-70]',
(sum(CASE when score.s_score>60 and score.s_score<=70 THEN 1 ELSE 0 end)/COUNT(score.s_id))*100 '[60-70]及所占百分比',
sum(CASE when score.s_score>70 and score.s_score<=85 THEN 1 ELSE 0 end) '[70-85]',
(sum(CASE when score.s_score>70 and score.s_score<=85 THEN 1 ELSE 0 end)/COUNT(score.s_id))*100 '[70-85]及所占百分比',
sum(CASE when score.s_score>85 and score.s_score<=100 THEN 1 ELSE 0 end) '[85-100]',
(sum(CASE when score.s_score>85 and score.s_score<=100 THEN 1 ELSE 0 end)/COUNT(score.s_id))*100 '[85-100]及所占百分比'
from score
left join course
on score.c_id=course.c_id
GROUP BY score.c_id
-- 24、查询学生平均成绩及其名次 SELECT t.* , @i:=@i+1 as 排名 from (SELECT score.s_id,AVG(score.s_score) alla from score GROUP BY score.s_id ORDER BY alla DESC) t, (SELECT @i:=0)s
-- 25、查询各科成绩前三名的记录 -- 查询各科成绩前三名的记录（不考虑成绩并列情况）
(SELECT score.s_score,score.s_id,score.c_id from score where score.c_id=01 ORDER BY score.s_score desc LIMIT 3)
UNION all
(SELECT score.s_score,score.s_id,score.c_id from score where score.c_id=02 ORDER BY score.s_score desc LIMIT 3)
union all
(SELECT score.s_score,score.s_id,score.c_id from score where score.c_id=03 ORDER BY score.s_score desc LIMIT 3)
==================================================================================================================
SELECT
sc1.s_id,
sc1.c_id,
sc1.s_score,
(SELECT
COUNT(*)
FROM
score sc3
WHERE
sc3.c_id = sc1.c_id
AND sc3.s_score > sc1.s_score
)+1 AS rank排名
FROM
score sc1
LEFT JOIN
score sc2
ON
sc1.c_id = sc2.c_id
AND sc1.s_score < sc2.s_score
GROUP BY
sc1.c_id,
sc1.s_id,
sc1.s_score
HAVING
COUNT(sc2.s_id) <= 2
ORDER BY
sc1.c_id,
sc1.s_score DESC
-- 26、查询每门课程被选修的学生数 SELECT COUNT(score.s_id), score.c_id from score GROUP BY score.c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名 SELECT * from student where student.s_id in (SELECT score.s_id from score GROUP BY score.s_id HAVING COUNT(score.c_id)=2)
-- 28、查询男生、女生人数 SELECT COUNT(0) from student GROUP BY student.s_sex
-- 29、查询名字中含有"风"字的学生信息
SELECT * from student WHERE student.s_name like '%风%'
-- 30、查询同名同性学生名单，并统计同名人数 select student.s_name,student.s_sex,COUNT(1) 人数 from student group by student.s_name,student.s_sex having COUNT(1)>1
-- 31、查询1990年出生的学生名单 select s_name from student where s_birth like '1990%'
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 SELECT score.c_id,avg(score.s_score) as ss from score GROUP BY score.c_id ORDER BY ss DESC,score.c_id asc
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 SELECT * from (SELECT avg(score.s_score) aa,score.s_id from score GROUP BY score.s_id HAVING aa>=85) t LEFT JOIN student on t.s_id=student.s_id
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 SELECT * from (SELECT * from score where score.c_id=( SELECT course.c_id from course where course.c_name='数学') and score.s_score<60 ) t LEFT JOIN student on t.s_id=student.s_id
-- 35、查询所有学生的课程及分数情况； select a.s_id,a.s_name,
SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',
SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',
SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',
SUM(b.s_score) as '总分'
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT * from ( SELECT ts.*,course.c_name from ( SELECT score.s_id,score.s_score,score.c_id from score where score.s_score>70) ts left join course on ts.c_id=course.c_id ) t LEFT JOIN student on t.s_id=student.s_id -- 37、查询不及格的课程
SELECT * from score left join course on score.c_id=course.c_id where score.s_score<60
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； SELECT * from student where student.s_id in(SELECT score.s_id from score where score.c_id='01' and score.s_score>80)
-- 39、求每门课程的学生人数 SELECT count(0) from score GROUP BY score.c_id
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 SELECT * from ( SELECT score.s_id, max(score.s_score)from score where score.c_id in ( SELECT course.c_id from course where course.t_id in ( SELECT teacher.t_id from teacher where teacher.t_name='张三'))) t LEFT JOIN student on t.s_id=student.s_id
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT b.s_id,b.c_id,b.s_score from score
join score b
on score.c_id!=b.c_id
and score.s_score=b.s_score
-- 42、查询每门功成绩最好的前两名 select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id -- 43、统计每门课程的学生选修人数（超过5人的课程才统计）
SELECT score.c_id,COUNT(score.s_id) aa from score GROUP BY score.c_id HAVING COUNT(score.s_id)>5 ORDER BY aa asc
-- 45、查询选修了全部课程的学生信息 select * from student where s_id in(
select s_id from score GROUP BY s_id HAVING count()=(select count() from course))
--46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') -
(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
from student;
-- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)
-- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
-- 50、查询下月过生日的学生
