mysql简单练习题--答案版

mysql简单练习题–答案版

查询名字中含有「龙」字的学生信息

SELECT *
FROM student
WHERE sname LIKE '%龙%';

查询「数」姓的所有老师的信息

SELECT * FROM teacher
WHERE Tname LIKE '数%';

查询男生、女生人数

SELECT Ssex,COUNT(ssex) as 人数 
FROM student
GROUP BY Ssex; 

查询2000年后出生的学生名单

SELECT sname
FROM student
WHERE sage LIKE '20%';

查询同名同姓学生名单，并统计同名人数

-- 思路：sname分组即可，本数据无同名
SELECT sname,COUNT(sname) AS 同名人数
FROM student
GROUP BY sname
HAVING 同名人数>1;

查询每门课程选修人数

-- 思路：分组后，用个count即可
SELECT Cid,COUNT(sid) AS num
FROM  sc 
GROUP BY cid;

查询每门课程的平均成绩

SELECT cid,AVG(score) '平均成绩'
FROM sc
GROUP BY cid 

查询每门成绩最好的前3名

-- 思路：三个课程的乘积表，排名取前三，然后合并
(SELECT * FROM sc WHERE Cid='s1'  ORDER BY score DESC LIMIT 3)
UNION
(SELECT * FROM sc WHERE Cid='s2'  ORDER BY score DESC LIMIT 3)
UNION
(SELECT * FROM sc WHERE Cid='s3'  ORDER BY score DESC LIMIT 3) 

查询选修过任一课程的学生的学生信息

-- 思路：没选修课程的同学不在成绩表中，所以直接联结即可。
-- 这种等值联结，相当于内联结，只有两表都有的记录才会被留下。
-- 如果不理解，可以网上查一下，深刻理解一下联结部分的内容。
SELECT  DISTINCT student.*
FROM student,sc
WHERE student.Sid=sc.Sid

查询至少选修两门课程的学生学号

SELECT 
  sid,COUNT(cid) 选修数 
FROM
  sc 
GROUP BY sid 
HAVING 选修数 >= 2 ; 

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

SELECT sc.sid,sname
FROM sc INNER JOIN student
ON sc.sid = student.sid
GROUP BY sc.sid
HAVING COUNT(sc.cid) = 2; 

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

-- 思路：分组求出每个学生选课数，加上选课数等于全部课程数的条件。
-- 后用in把剩下的Sid带到student表即可。
SELECT * FROM student 
WHERE Sid in (
SELECT Sid FROM sc 
GROUP BY Sid 
HAVING COUNT(*)=(SELECT COUNT(cid) FROM course)) 

查询没有选修全部课程的同学的信息。

-- 思路：先查询选了所有课的学生，再反向选择这些人之外的学生.否则会漏掉什么都没选的。
SELECT * FROM student 
WHERE Sid  NOT IN(
SELECT Sid FROM sc 
GROUP BY Sid 
HAVING COUNT(*)=(SELECT COUNT(cid) FROM course));

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

-- 思路：用时间函数，看一眼就明白了，注意记忆。
SELECT sname,YEAR(NOW()) - YEAR(sage) AS 年龄
FROM student ;

查询所有学生的姓名、课程名称和分数

SELECT sname,sc.cid,score 
FROM sc INNER JOIN student 
ON sc.sid = student.sid ;

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

-- 思路：上一题的基础上，加上一个where条件判断
SELECT sname,sc.cid,score 
FROM
  sc INNER JOIN student AS s 
    ON sc.sid = s.sid 
AND score > 70 ; 

查询有不及格课程的学生的姓名、课程名称和分数

-- 思路：同上一题
SELECT sname,sc.cid,score 
FROM
  sc INNER JOIN student AS s 
    ON sc.sid = s.sid 
AND score < 60 ; 

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

-- 思路：同上一题,多加个条件
SELECT sc.sid,sname
FROM sc INNER JOIN student AS s
ON sc.sid = s.sid
WHERE sc.cid = 's1' AND score >= 80; 

查询本周过生日的学生

-- 思路：时间函数week()，返回日期的所在周数，一年大概52周，试一下就知道了
SELECT sname
FROM student
WHERE WEEK(sage) = WEEK(NOW());

查询下周过生日的学生

SELECT 
  sname 
FROM
  student 
WHERE WEEK(sage) = WEEK(NOW()) + 1 ; 

查询本月过生日的学生

SELECT 
  sname,
  sage 
FROM
  student 
WHERE MONTH(sage) = MONTH(NOW());

查询下月过生日的学生

SELECT 
  sname,
  sage 
FROM
  student 
WHERE MONTH(sage) = MONTH(NOW()) + 1 ;