#4.3.1 查询student表的所有记录
SELECT * FROM student
#4.3.2 查询student表中的所有记录的sname、ssex和class列
SELECT sname,ssex,class FROM student
#4.3.3 查询编号为101的同学记录
SELECT * FROM student
WHERE sno=101
#4.3.4 查询姓“王”的同学记录
SELECT * FROM student
WHERE sname LIKE '王%'
#4.3.5 查询不姓“王”的同学记录
SELECT * FROM student
WHERE sname NOT LIKE '王%'
#4.3.6 查询scores表中成绩在60到80之间的所有记录
SELECT * FROM scores
WHERE degree BETWEEN 60 AND 80
SELECT * FROM scores
WHERE degree>=60 AND degree<=80
#4.3.7 查询scores表中成绩为85,86或88的记录
SELECT * FROM scores
WHERE degree=85 OR degree=86 OR degree=88
SELECT * FROM scores
WHERE degree IN (85,86,88)
#4.3.8 查询“95031”班的“男”同学的记录
SELECT * FROM student
WHERE class='95031' AND ssex='男'
#4.3.9 查询“95031”班或性别为“女”的同学记录
SELECT * FROM student
WHERE class='95031' OR ssex = '女'
#4.3.10 查询teacher表所有的系即不重复的depart列
SELECT DISTINCT depart FROM teacher
#4.3.11 以班级class降序查询student表的所有记录
SELECT * FROM student
ORDER BY class DESC
#4.3.12 以cno升序、degree降序查询scores表的所有记录
SELECT * FROM scores
ORDER BY cno, degree DESC
#4.3.13 查询“95031”班的学生人数
SELECT COUNT(*) FROM student
WHERE class='95031'
#4.3.14 查询每个班的学生人数
#分组后,选择字段只能写两种
#1.聚合函数,2.分组中出现字段
SELECT class,COUNT(*) FROM student
GROUP BY class
#4.3.15 查询大于2名男生的班号
SELECT class,COUNT(*) FROM student
WHERE ssex='男'
GROUP BY class
HAVING COUNT(*)>2
#having条件,它和group by搭配
#4.3.16 查询最高分
SELECT MAX(degree) FROM scores
#4.3.17 查询最低分
SELECT MIN(degree) FROM scores
#4.3.18 查询scores表中的最高分的学生学号和课程号
SELECT cno,sno FROM scores
WHERE degree = (SELECT MAX(degree) FROM scores)
#4.3.19 查询‘3-105’号课程的平均分
SELECT cno,AVG(degree),ROUND(AVG(degree),1) FROM scores
GROUP BY cno
HAVING cno='3-105'
#4.3.20 查询各科的平均分
SELECT cno,ROUND(AVG(degree),2) FROM scores
GROUP BY cno
#4.3.21 查询最低分大于70,最高分小于90的sno列
select * from scores
where degree>70 and degree<90
#4.3.22 查询95033班和95031班全体学生的记录
select * from student
where class='95033' or class='95031'
#union 把两个结果集相加(优化)
select sno,sname as s1 from student where class='95033'
union
SELECT sno,sname as s2 FROM student WHERE class='95031'
#4.3.23 查询存在有85分以上成绩的课程cno
select cno from scores where degree>85
#4.3.24 查询所有教师teacher和同学student的name、sex和birthday
select tname name,tsex sex,tbirthday birthday from teacher
union
select sname,ssex,sbirthday from student
#4.3.25 查询所有“女”教师和“女”同学的name、sex和birthday
select tname,tsex,tbirthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女'
#4.3.26 查询所有任课教师的tname和depart
课程表courses中有老师编号tno的就算任课
SELECT tname,depart FROM teacher
WHERE tno IN ( SELECT DISTINCT tno FROM courses )
#4.3.27 查询所有未讲课的教师的tname和depart
SELECT tname,depart FROM teacher
WHERE tno NOT IN ( SELECT DISTINCT tno FROM courses )
#4.3.28 查询同名的同学记录
SELECT sname FROM student
GROUP BY sname
HAVING COUNT(*)>1
SELECT * FROM student
WHERE sname = (
SELECT sname FROM student
GROUP BY sname
HAVING COUNT(*)>1
)
#4.3.29 查询学生的姓名和年龄=当前年-birthday年 AS别名可省略
SELECT sname,sbirthday,YEAR(NOW())-YEAR(sbirthday) AS age
FROM student
#4.3.30 查询学生中最大和最小的出生年月
SELECT MIN(sbirthday),MAX(sbirthday) FROM student
#4.3.31 以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT * FROM student
ORDER BY class DESC, YEAR(NOW())-YEAR(sbirthday) DESC
#4.3.32 查询男教师及其所上的课程
SELECT t.tname,c.cname FROM
(SELECT * FROM teacher WHERE tsex='男') t
LEFT JOIN
(SELECT * FROM courses) c
ON t.tno=c.tno
SELECT t.tname,c.cname FROM
teachers t LEFT JOIN courses c
ON t.tno=c.tno
WHERE t.tsex='男'
#4.3.33 查询各门课程的最高分同学的sno、cno和degree列
SELECT cno,MAX(degree) FROM scores
GROUP BY cno
#4.3.34 查询课程对应的老师姓名、职称、所属系
SELECT cname,tname,prof,depart FROM
(SELECT tno,cname FROM courses) a
LEFT JOIN
(SELECT tno,tname,prof,depart FROM teacher) b
ON a.tno=b.tno
#4.3.35 查询课程对应的女老师姓名、职称、所属系
SELECT cname,tname,prof,depart FROM
(SELECT tno,cname FROM courses) a
LEFT JOIN
(SELECT tno,tname,prof,depart,tsex FROM teacher) b
ON a.tno=b.tno
WHERE tsex='女'
行列转置,SQL没有真正实现,即使实现效率也不高,但是毕竟是SQL直接写成,不是应用代码
4.3.36 行列转置:各科最好成绩
分组方式:
SELECT cno,MAX(degree) degree FROM scores
GROUP BY cno;
SELECT
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'
FROM scores
行列转置:
SELECT
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'
FROM scores;
#4.3.37 行列转置:每个学员各科最好成绩
每个学员(分组);各科成绩(行列转置);最好成绩(MAX函数)
SELECT
sno,
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'
FROM scores
GROUP BY sno;
4.3.38 行列转置:课程编号和课程名称
SELECT cno,cname FROM courses;
#group_concat
SELECT
REPLACE(GROUP_CONCAT(CASE cno WHEN '3-105' THEN cname ELSE '' END),',','') AS '3-105',
REPLACE(GROUP_CONCAT(CASE cno WHEN '3-245' THEN cname ELSE '' END),',','') AS '3-245',
REPLACE(GROUP_CONCAT(CASE cno WHEN '6-106' THEN cname ELSE '' END),',','') AS '6-106',
REPLACE(GROUP_CONCAT(CASE cno WHEN '6-166' THEN cname ELSE '' END),',','') AS '6-166',
REPLACE(GROUP_CONCAT(CASE cno WHEN '9-888' THEN cname ELSE '' END),',','') AS '9-888'
FROM courses;
SELECT DISTINCT
REPLACE(GROUP_CONCAT(CASE cno WHEN '3-105' THEN cname ELSE '' END),',','') AS '3-105',
REPLACE(GROUP_CONCAT(CASE cno WHEN '3-245' THEN cname ELSE '' END),',','') AS '3-245',
REPLACE(GROUP_CONCAT(CASE cno WHEN '6-106' THEN cname ELSE '' END),',','') AS '6-106',
REPLACE(GROUP_CONCAT(CASE cno WHEN '6-166' THEN cname ELSE '' END),',','') AS '6-166',
REPLACE(GROUP_CONCAT(CASE cno WHEN '9-888' THEN cname ELSE '' END),',','') AS '9-888'
FROM courses;
#4.3.39 课程号“3-105”的前3名学员排行
SELECT * FROM scores
WHERE cno='3-105'
ORDER BY degree DESC
LIMIT 3
4.3.40 课程“计算机导论”的前3名学员排行
SELECT * FROM scores
WHERE cno=(SELECT cno FROM courses WHERE cname='计算机导论')
ORDER BY degree DESC
LIMIT 3
4.3.41 课程号“3-105”的倒数最后3名学员排行
SELECT * FROM
(SELECT * FROM scores WHERE cno='3-105' ORDER BY degree
LIMIT 3) t
ORDER BY degree desc