MySQL数据库（分组与排序）答案-武洪萍主编

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;