学生表
CREATE TABLE student (
s_id VARCHAR(30),
s_name VARCHAR(30) NOT NULL,
s_birth date NOT NULL,
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);
成绩表
CREATE TABLE score (
s_id VARCHAR(30),
c_id VARCHAR(30),
s_score VARCHAR(3),
PRIMARY KEY(s_id, c_id)
);
课程表
CREATE TABLE course (
c_id VARCHAR(30),
c_name VARCHAR(30) NOT NULL,
t_id VARCHAR(30) NOT NULL,
PRIMARY KEY(c_id)
);
教师表
CREATE TABLE teacher (
t_id VARCHAR(30),
t_name VARCHAR(30) NOT NULL,
PRIMARY KEY(t_id)
);
学生表
insert into student(s_id,s_name,s_birth,s_sex)
values(‘0001‘ , ‘猴子‘ , ‘1989-01-01‘ , ‘男‘);
insert into student(s_id,s_name,s_birth,s_sex)
values(‘0002‘ , ‘猴子‘ , ‘1990-12-21‘ , ‘女‘);
insert into student(s_id,s_name,s_birth,s_sex)
values(‘0003‘ , ‘马云‘ , ‘1991-12-21‘ , ‘男‘);
insert into student(s_id,s_name,s_birth,s_sex)
values(‘0004‘ , ‘王思聪‘ , ‘1990-05-20‘ , ‘男‘);
成绩表
insert into score(s_id,c_id,s_score)
values(‘0001‘ , ‘0001‘ , 80);
insert into score(s_id,c_id,s_score)
values(‘0001‘ , ‘0002‘ , 90);
insert into score(s_id,c_id,s_score)
values(‘0001‘ , ‘0003‘ , 99);
insert into score(s_id,c_id,s_score)
values(‘0002‘ , ‘0002‘ , 60);
insert into score(s_id,c_id,s_score)
values(‘0002‘ , ‘0003‘ , 80);
insert into score(s_id,c_id,s_score)
values(‘0003‘ , ‘0001‘ , 80);
insert into score(s_id,c_id,s_score)
values(‘0003‘ , ‘0002‘ , 80);
insert into score(s_id,c_id,s_score)
values(‘0003‘ , ‘0003‘ , 80);
课程表
insert into course(c_id,c_name,t_id)
values(‘0001‘ , ‘语文‘ , ‘0002‘);
insert into course(c_id,c_name,t_id)
values(‘0002‘ , ‘数学‘ , ‘0001‘);
insert into course(c_id,c_name,t_id)
values(‘0003‘ , ‘英语‘ , ‘0003‘);
教师表
-- 教师表：添加数据
insert into teacher(t_id,t_name)
values(‘0001‘ , ‘孟扎扎‘);
insert into teacher(t_id,t_name)
values(‘0002‘ , ‘马化腾‘);
-- 这里的t_name是空值（null）
insert into teacher(t_id,t_name)
values(‘0003‘ , null);
-- 这里的t_name是空字符串（‘‘）
insert into teacher(t_id,t_name)
values(‘0004‘ , ‘‘);
-- 查询姓“猴”的学生名单
SELECT * from student where s_name LIKE ‘猴%‘;
-- 查询姓名中最后一个字是“猴”的学生名单
SELECT * FROM student where s_name LIKE ‘%猴‘;
-- 查询姓名中带“猴”字的学生名单
SELECT * FROM student where s_name LIKE ‘%猴%‘;
-- 查询姓“孟”老师的个数
SELECT COUNT(t_id) from teacher WHERE t_name LIKE ‘孟%‘;
汇总分析
-- 查询课程编号为“0002”的总成绩
SELECT SUM(s_score) FROM score WHERE c_id = ‘0002‘;
-- 查询选了课程的学生数
SELECT COUNT(DISTINCT(s_id)) AS 学生人数 FROM score;
分组
-- 查询各科成绩最高和最低的分
SELECT c_id, MAX(s_score) AS 最高分, MIN(s_score) AS 最低分 FROM score GROUP BY c_id;
-- 查询每门课程被选修的学生数
SELECT c_id, COUNT(s_id) AS 学生数 FROM score GROUP BY c_id;
-- 查询男生，女生人数
SELECT s_sex, COUNT(*) AS 人数 FROM student GROUP BY s_sex;
分组结果的条件
-- 查询平均成绩大于60分学生的学号和平均成绩
SELECT s_id, AVG(s_score) from score GROUP BY s_id HAVING AVG(s_score) > 60;
-- 查询至少选修两门课程的学生学号
SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) >= 2;
-- 查询同名同姓的学生名单并统计同名人数
SELECT s_name, COUNT(*) AS 人数 FROM student GROUP BY s_name HAVING COUNT(*) >= 2;
-- 查询不及格的课程并按课程号从大到小排列
SELECT c_id FROM score WHERE s_score < 60 ORDER BY c_id DESC;
-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT c_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY c_id ORDER BY AVG(s_score) ASC, c_id DESC;
-- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT s_id, s_score FROM score WHERE c_id = 0004 AND s_score < 60 ORDER BY s_score DESC;
-- 统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT c_id, COUNT(s_id) AS 选修人数 FROM score GROUP BY c_id HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC, c_id ASC;
-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s_id, AVG(s_score) AS 平均成绩 FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(c_id) > 2;
汇总分析
-- 查询学生的总成绩并进行排名
SELECT s_id, SUM(s_score) AS 总成绩 FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC;
-- 查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY s_id HAVING AVG(s_score) > 60;
TODO
参考：
原文：https://www.cnblogs.com/LMFrank/p/13227641.html