mysql 基础提升 50道题目（包含答案） sql实例

1.1数据表构建

--1.学生表

Student(SId,Sname,Sage,Ssex)

--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表

Course(CId,Cname,TId)

--CId 课程编号,Cname 课程名称,TId 教师编号

--3.教师表

Teacher(TId,Tname)

--TId 教师编号,Tname 教师姓名

--4.成绩表

SC(SId,CId,score)

--SId 学生编号,CId 课程编号,score 分数

1.2创建并插入表数据

学生表 student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-12-20' , '男');

insert into Student values('04' , '李云' , '1990-12-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , '1989-01-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2012-06-06' , '女');

insert into Student values('12' , '赵六' , '2013-06-13' , '女');

insert into Student values('13' , '孙七' , '2014-06-01' , '女');

 

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

 

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

 

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

1.3sql题目和答案

 

#2查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT * FROM

student,(

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score) >= 60) AS r

WHERE Student.`SId`= r.sid

 

#3.查询在 SC 表存在成绩的学生信息

SELECT *

FROM Student

WHERE sid IN (

SELECT DISTINCT(sid)

FROM sc

)

SELECT DISTINCT Student.*

FROM Student,sc

WHERE Student.sid=sc.sid

 

#4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

 

SELECT s.sid,s.sname,r.su_count,r.sc_sum

FROM Student AS s LEFT JOIN(

SELECT sid,COUNT(cid) AS su_count,SUM(score) AS sc_sum

FROM sc

GROUP BY sid) AS r

ON s.sid=r.sid

 

#4.1 查有成绩的学生信息

SELECT *

FROM Student

WHERE sid IN (

SELECT DISTINCT sid

FROM sc

)

 

#5.查询「李」姓老师的数量

SELECT COUNT(*)

FROM Teacher

WHERE Tname LIKE '李%'

 

#6.查询学过「张三」老师授课的同学的信息

 

SELECT *

FROM Teacher,Student,sc,course

WHERE Teacher.`TId` = course.`TId`

AND Course.`CId` = sc.`CId`

AND sc.sid= Student.`SId`

AND Teacher.`Tname` = '张三'

 

#7.查询没有学全所有课程的同学的信息

 

SELECT *

FROM Student

WHERE sid IN (

SELECT sid

FROM sc

GROUP BY sid HAVING COUNT(*) <> 3

)

 

#8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT *

FROM Student

WHERE sid IN (

SELECT DISTINCT sid

FROM sc

WHERE cid IN (

SELECT cid

FROM sc

WHERE sid=01)

AND sid <> 01)

#9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

SELECT * FROM Student WHERE Sid IN

(SELECT Sid FROM sc WHERE Cid IN

(SELECT Cid FROM sc WHERE Sid = '01')

AND Sid <> '01'

GROUP BY Sid

HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE Sid ='01'));

 

#10.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT sname FROM Student WHERE sid NOT IN (

SELECT DISTINCT sid FROM sc WHERE cid IN (

SELECT cid FROM Course WHERE tid = (

SELECT TId FROM Teacher WHERE Tname='张三')))

 

 

#11,查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.sid,s.sname,r.avg_sco FROM Student AS s,(

SELECT sid,AVG(score) AS avg_sco FROM sc WHERE sid IN (

SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*) >=2)

GROUP BY sid ) AS r

WHERE s.`SId` = r.sid

 

#12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT * FROM Student AS s,

(SELECT * FROM sc WHERE cid=01 AND score < 60) AS r

WHERE s.sid=r.sid

ORDER BY score DESC

 

#13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT* FROM

(SELECT * FROM sc,

(SELECT sid AS sid1,AVG(score) AS avg_sco FROM sc GROUP BY sid ) AS s

WHERE sc.`SId`=s.sid1) AS r

LEFT JOIN Student

ON r.SID=Student.`SId`

ORDER BY avg_sco DESC

 

#14.查询各科成绩最高分、最低分和平均分

SELECT

sc.CId ,

MAX(sc.score)AS 最高分,

MIN(sc.score)AS 最低分,

AVG(sc.score)AS 平均分,

COUNT(*)AS 选修人数,

SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END )/COUNT(*)AS 及格率,

SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END )/COUNT(*)AS 中等率,

SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END )/COUNT(*)AS 优良率,

SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS 优秀率

FROM sc

GROUP BY sc.CId

ORDER BY COUNT(*)DESC, sc.CId ASC

 

#15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT a.cid, a.sid, a.score, COUNT(b.score)+1 AS rank

FROM sc AS a

LEFT JOIN sc AS b

ON a.score<b.score AND a.cid = b.cid

GROUP BY a.cid, a.sid,a.score

ORDER BY a.cid, rank ASC;

 

#17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT r.cid ,

r.a85,r.a85/r.acount AS s85,

r.a70,r.a70/r.acount AS s70,

r.a60,r.a60/r.acount AS s60,

r.a59,r.a59/r.acount AS s59

FROM (

SELECT

cid,

SUM(CASE WHEN score >=85 THEN 1 ELSE 0 END) AS a85,

SUM(CASE WHEN score >=70 AND score<85 THEN 1 ELSE 0 END) AS a70,

SUM(CASE WHEN score >=60 AND score<70 THEN 1 ELSE 0 END) AS a60,

SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) AS a59,

COUNT(*) AS acount

FROM sc

GROUP BY cid)AS r

 

 

#18查询各科成绩前三名的记录

SELECT s1.* FROM sc AS s1

WHERE (SELECT * FROM sc AS s1, sc AS s2 WHERE s1.cid=s2.cid AND s1.score<s2.score)<3

ORDER BY s1.cid,s1.score DESC;

 

#19.查询每门课程被选修的学生数

SELECT course.`Cname`,r.r_cou

FROM Course,(

SELECT cid,COUNT(*) AS r_cou

FROM sc

GROUP BY cid

) AS r

WHERE Course.cid=r.cid

 

#20.查询出只选修两门课程的学生学号和姓名

SELECT *

FROM Student AS s,(

SELECT sid

FROM sc

GROUP BY sid HAVING COUNT(*) = 2

) AS r

WHERE s.sid=r.sid

 

#20.查询男生、女生人数

SELECT Ssex,COUNT(*) AS S_COU

FROM Student s

GROUP BY Ssex

 

#21.查询名字中含有「风」字的学生信息

SELECT *

FROM Student

WHERE Sname LIKE '%风%'

 

#22.查询同名同性学生名单，并统计同名人数

SELECT Sname,COUNT(*)

FROM Student AS s

GROUP BY Sname HAVING COUNT(*) >1

 

#24.查询 1990 年出生的学生名单

SELECT *

FROM Student

WHERE YEAR(sage) = '1990'

 

#25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

 

SELECT * FROM Course,(

SELECT sc.cid,AVG(SC.SCORE) AS average

FROM sc

GROUP BY sc.cid ) AS c

WHERE c.cid=course.`CId`

ORDER BY c.average DESC,c.cid ASC;

 

#26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT  s.sid,Student.`Sname`,s.s_avg

FROM Student,(

SELECT sid,AVG(score) AS s_avg FROM sc GROUP BY sid HAVING AVG(score)>=85

) AS s

WHERE Student.sid = s.sid

 

#27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT Student.`Sname`,r.score FROM student,(

SELECT * FROM (

SELECT sid,score FROM sc WHERE cid IN

(SELECT cid FROM Course WHERE Course.`Cname`='数学')) AS a

WHERE a.score < 60) AS r

WHERE Student.`SId`=r.sid

 

#28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT * FROM student LEFT JOIN sc  ON sc.sid=Student.sid

 

#29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT * FROM sc

 

SELECT student.sname, course.cname,sc.score FROM student,course,sc

WHERE sc.score>70

AND student.sid = sc.sid

AND sc.cid = course.cid;

 

#30.查询不及格的课程

SELECT course.`Cname`,r.score FROM course RIGHT JOIN(

SELECT *

FROM sc

WHERE score < 60

)AS r ON course.`CId`=r.cid

 

#31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT student.`Sname`,r.sid,r.score FROM student RIGHT JOIN (

SELECT * FROM sc WHERE score >=80 AND cid='01') AS r

ON student.sid=r.sid

 

#32.求每门课程的学生人数

SELECT *

FROM course,

(SELECT cid,COUNT(*) FROM sc GROUP BY cid) AS r

WHERE course.cid=r.cid

 

#33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

 

SELECT * FROM teacher,course,sc,student

WHERE teacher.`TId`=course.Tid AND

course.Cid = sc.`CId` AND

sc.Sid=student.`SId` AND

teacher.`Tname`='张三'

ORDER BY score DESC LIMIT 0,2

 

#34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student.*, sc.score, sc.cid FROM student, teacher, course,sc

WHERE teacher.tid = course.tid

AND sc.sid = student.sid

AND sc.cid = course.cid

AND teacher.tname = "张三"

AND sc.score = (

    SELECT MAX(sc.score)

    FROM sc,student, teacher, course

    WHERE teacher.tid = course.tid

    AND sc.sid = student.sid

    AND sc.cid = course.cid

    AND teacher.tname = "张三"

);

 

#35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT  a.cid, a.sid,  a.score FROM sc AS a

INNER JOIN

sc AS b

ON a.sid = b.sid

AND a.cid != b.cid

AND a.score = b.score

GROUP BY a.cid, a.sid,a.score;

 

#36.查询每门功成绩最好的前两名

SELECT a.sid,a.cid,a.score FROM sc AS a

LEFT JOIN sc AS b

ON a.cid = b.cid AND a.score<b.score

GROUP BY a.cid, a.sid,a.score

HAVING COUNT(b.cid)<2

ORDER BY a.cid;

 

#37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT cid,COUNT(*) FROM sc GROUP BY cid    HAVING COUNT(*) > 5

 

#38.检索至少选修两门课程的学生学号

SELECT sid,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*)>=2

 

#39.查询选修了全部课程的学生信息

SELECT * FROM student

WHERE sid IN (

SELECT sid FROM sc GROUP BY sid

)

 

#40.查询各学生的年龄，只按年份来算

 

 

#41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT student.SId AS 学生编号,student.Sname  AS  学生姓名,

TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 学生年龄   # 时间戳返回相差时间 CURDATE()返回年月日

FROM student

 

SELECT DATEDIFF('2018-05-01 08:00:00','2018-05-09') AS DiffDate;  #返回相差几天

SELECT NOW(),CURDATE(),CURTIME()  #具体时间，，年月日 ，，， 时分秒

 

#42.查询本周过生日的学生

SELECT *

FROM student

WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());  #返回当时时间属于一年的第几周

 

#43.查询下周过生日的学生

SELECT *

FROM student

WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

 

#44.查询本月过生日的学生

SELECT *

FROM student

WHERE MONTH(student.Sage)=MONTH(CURDATE());

 

#45.查询下月过生日的学生

SELECT *

FROM student

WHERE MONTH(student.Sage)=MONTH(CURDATE())+1;