利用MySQL进行数据复杂查询（2）

2） 某中学在线考试系统数据库及表创建
某中学开发了一套在线考试系统，完整系统涵盖教师端、学员端、家长端的查询和操作功能，本实验是将其中的成绩管理子模块单独取出来作为实验案例，涉及了教师管理、学员管理、科目管理、成绩管理四个方向。案例从学校实际需求出发设计，帮助学生系统了解MySQL数据库及数据表的创建等内容。
根据系统功能分析，可划分如下表：
表2-6 某中学在线考试系统数据库表
序号 表名称 描述
1 tbl_course 科目表
2 tbl_sc 成绩表
3 tbl_student 学生表
4 tbl_teacher 老师表
同时本案例对如上表的字段进行约束设计，包括主键、非空、唯一、检查约束等约束，具体如下：
表2-7 tbl_course表结构
编号 列名称 说明 类型 长度 约束
1 cid 科目编号 varchar 50 主键约束
2 cname 科目名称 varchar 50 非空约束
3 tid 老师编号 varchar 50 外键约束
表2-8 tbl_sc表结构
编号 列名称 说明 类型 长度 约束
1 scid 成绩编号 int 11 主键约束
2 sid 学生编号 varchar 50 外键约束
3 cid 科目编号 varchar 50 外键约束
4 score 成绩 int 11 非空约束
表2-9 tbl_student表结构
编号 列名称 说明 类型 长度 约束
1 sid 学生编号 varchar 50 主键约束
2 sname 学生姓名 varchar 50 非空约束
3 sage 学生年龄 int 11 非空约束
4 sex 学生性别 int 11 非空约束
表2-10 tbl_teacher表结构
编号 列名称 说明 类型 长度 约束
1 tid 老师编号 varchar 50 主键约束
2 tname 老师姓名 varchar 50 非空约束

插入如下数据在表中：
tbl_course表

tbl_sc表

tbl_student表

tbl_teacher表

1）根据姓名查询学生信息（4分）

SELECT * from tbl_student WHERE sname = 'Jack'

2）查询年龄小于30岁的学生信息（4分）

SELECT * from tbl_student WHERE sage < 30

3）查询年龄在25至30之间的学生信息（4分）

SELECT * FROM tbl_student WHERE sage BETWEEN 25 and 30

4）按照年龄从大到小顺序输出学生信息（4分）

SELECT * FROM tbl_student ORDER BY sage desc

5）查询年龄最小的学生信息（4分）

SELECT * FROM tbl_student ORDER BY sage LIMIT 1

6）查询姓“刘”“张”的老师的个数（4分）

SELECT COUNT(*) FROM tbl_teacher WHERE tname LIKE '刘%';
SELECT COUNT(*) FROM tbl_teacher WHERE tname LIKE '张%'; 

7）查询老师的总数（4分）

SELECT COUNT(tname) FROM tbl_teacher

8）查询每个老师的授课数目（4分）

SELECT tname,COUNT(*) as '授课科目' FROM tbl_teacher GROUP BY tname WHERE tid IN(SELECT tid FROM tbl_course)

9）查询每个老师有多少学生（4分）

SELECT tname,COUNT(*) FROM(
SELECT DISTINCT tname,sid FROM tbl_teacher a
JOIN tbl_course b on a.tid = b.tid
JOIN tbl_sc c on b.cid = c.cid
)a GROUP BY tname

10）根据科目名称查询科目信息（4分）

select * from tbl_course where cname='体育'

11）查询每个科目对应的老师名字（4分）

SELECT cid,cname,tname FROM tbl_course a JOIN tbl_teacher b on a.tid = b.tid

12）查询每个科目有多少学生在学（4分）

 SELECT cid,COUNT(*) FROM tbl_sc GROUP BY cid

13）查询科目名称中带有's'关键字的科目信息（4分）

SELECT * FROM tbl_course WHERE cname like '%语%'

14）查询不止1个老师教的科目（4分）

SELECT cname FROM tbl_course GROUP BY cname HAVING COUNT(*)>1

15）查询平均成绩大于60分的同学的学号和平均成绩（5分）

SELECT sid,AVG(score) FROM tbl_sc GROUP BY sid HAVING AVG(score)>60

16）查询所有同学的学号、姓名、选课数、总成绩 （10分）

SELECT a.sid,a.sname,COUNT(b.cid),sum(b.score) FROM tbl_student a JOIN tbl_sc b on a.sid = b.sid GROUP BY sid

17）查询所有课程成绩小于60分的同学的学号、姓名（5分）

SELECT sid,sname FROM tbl_student  WHERE sid NOT IN(SELECT sid from tbl_sc WHERE score >= 60)

18）查询不同老师所教不同课程平均分从高到低显示（10分）

SELECT tname,cname,AVG(c.score) as 平均分 FROM tbl_teacher a
JOIN tbl_course b on a.tid = b.tid
JOIN tbl_sc c on b.cid = c.cid
GROUP BY tname,cname ORDER BY AVG(c.score) desc