MySQL数据库(分组与排序)答案-武洪萍主编
代码如下:
一,
-- 1,创建数据库 t10
1,删除数据库
DROP DATABASE IF EXISTS t10;
2,创建数据库
CREATE DATABASE t10;
3,修改数据库编码方式和字符集排列顺序
ALTER DATABASE t10 CHARACTER SET utf8 COLLATE utf8_bin;
4,使用数据库
USE t10;
-- 2, 表的结构数据从脚本a1导入
-- 3,查询a1中所有的表记录,并用别名显示
-- 查看各表数据
SELECT*FROM course; -- [课程表]主键cno(课程编号)课程名称cname
SELECT*FROM sc; -- [成绩表]双主键(sno,cno)外键sno参考学生表student,外键cno参考课程course,degree 分数,
SELECT*FROM student; -- [学生表]主键sno(学号),姓名snmae,性别ssex,电话sphone,地址saddress,生日sbirthday,所属院系sdept,照片sphoto,专业speciality,
SELECT*FROM suser; -- [用户表]用户名username,密码password,用户权限 userlevel
SELECT*FROM teacher; -- [教师表]主键tno(教师编号)教师性别ssex,教师生日sbirthday,所属院系sdept,
SELECT*FROM teaching; -- [授课表]课程编号cno(参考课程表),教师编号(教师表tno),开课学期cterm,
-- 查看各表结构
DESCRIBE course;
DESCRIBE sc;
DESCRIBE student;
DESCRIBE suser;
DESCRIBE teacher;
DESCRIBE teaching;
-- 分组与排序
-- 1、统计有学生选修的课程的门数
SELECT COUNT(DISTINCT cno) 有学生选修的课程的门数 FROM course;FROM sc;
-- 2、计算"C01"课程的平均成绩
SELECT AVG(degree) 平均成绩 FROM sc WHERE cno='C01';
-- 3、查询选修了"C03"课程的学生的学号及其成绩,查询结果按分数降序排列
SELECT sno 学号,degree 成绩 FROM sc WHERE cno='C02' ORDER BY degree DESC;
-- 4、查询各个课程号及相应的选课人数
SELECT cno 课程号,COUNT(*) 选课人数 FROM sc GROUP BY cno;
-- 5、统计每门课程的选课人数和最高分
SELECT cno 课程号,COUNT(*) 选课人数,MAX(degree) AS 最高分 FROM sc GROUP BY cno;
-- 6、统计每个学生的选课门数和考试总成绩,并按选课门数降序排列
SELECT sno 学号,COUNT(*) 选课门数,SUM(degree) 总成绩 FROM sc GROUP BY sno ORDER BY COUNT(*) DESC;
-- 7、查询选修了3门以上课程的学生学号
SELECT sno 学号 FROM sc GROUP BY sno HAVING COUNT(*)>3;
-- 8、查询成绩不及格的学生学号及课程号,并按成绩降序排列
SELECT sno 学号,cno 课程号 FROM sc WHERE degree<60 GROUP BY sno ORDER BY degree DESC;
-- 9、查询至少选修一门课程的学生学号
SELECT sno 学号 FROM sc GROUP BY sno HAVING COUNT(*)>=1;
-- 10、统计输出各系学生的人数
SELECT sdept 所属院系,COUNT(*) 人数 FROM student GROUP BY sdept;
-- 11、统计各系的男、女生人数
SELECT sdept 所属院系,ssex 性别,COUNT(*) 人数 FROM student GROUP BY sdept,ssex;
-- 12、统计各班级的学生人数
SELECT speciality 专业,COUNT(*) 人数 FROM student GROUP BY speciality;
-- 13、统计各班的男、女生人数
SELECT speciality 专业,ssex 性别,COUNT(*) 人数 FROM student GROUP BY speciality,ssex;
-- 14、统计各系的老师人数,并按人数降序排列
SELECT tdept 所属院系,COUNT(*) 人数 FROM teacher GROUP BY tdept ORDER BY COUNT(*) DESC;
-- 15、统计不及格人数超过10的课程号
SELECT cno 课程号,COUNT(*) 人数 FROM sc GROUP BY cno HAVING COUNT(*)>10;
-- 16、查询软件工程系的男生信息,查询结果按出生日期升序排序,出生日期相同的按地址降序排序
SELECT * FROM student WHERE sdept='软件工程系' AND ssex='男' ORDER BY sbirthday ASC, saddress DESC;