MySQL(4-5)：DQL数据查询语言
十一、接上期，继续介绍DQL数据查询语言
本次为最后一次关于介绍DQL数据查询语言的实操课程。
-- 37、查询Student表中不姓“刘”的同学记录。
SELECT
*
FROM student
WHERE student.sname NOT LIKE "刘%"
-- 38、查询Student表中每个学生的姓名和年龄。
SELECT
student.sname,
ROUND(DATEDIFF(NOW(),student.sbirthday)/365,0) AS Age
FROM student
-- 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT
MAX(student.sbirthday) AS Max_sbirthday,
MIN(student.sbirthday) AS Min_sbirthday
FROM student
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT
*
FROM student
ORDER BY student.class DESC,DATEDIFF(NOW(),student.sbirthday) DESC
-- 41、查询“男”教师及其所上的课程。
SELECT DISTINCT
teacher.*,
course.cname
FROM teacher INNER JOIN course ON teacher.tno=course.tno
INNER JOIN score ON course.cno=score.cno
WHERE teacher.tsex="男"
-- 42、查询最高分同学的Sno、Cno和Grade列。
SELECT
score.sno,
score.cno,
score.grade
FROM score
ORDER BY score.grade DESC
LIMIT 1
-- 43、查询和“刘伟”同性别的所有同学的Sname.
SELECT
student.sname
FROM student
WHERE student.sname<>"刘伟"
AND student.ssex=(SELECT student.ssex FROM student WHERE student.sname="刘伟")
-- 44、查询和“刘伟”同性别并同班的同学Sname.
SELECT
student.sname
FROM student
WHERE student.sname<>"刘伟"
AND student.ssex=(SELECT student.ssex FROM student WHERE student.sname="刘伟")
AND student.class=(SELECT student.class FROM student WHERE student.sname="刘伟")
-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT
score.*
FROM student INNER JOIN score ON student.sno=score.sno
INNER JOIN course ON score.cno=course.cno
WHERE course.cname="计算机导论"
AND student.ssex="男"