首先我们有一个school数据库，然后它有如下结构的四张表：
接下来通过SQL语句创建对应的数据库和数据表(英语字母统一用小写，好看一点)：
创建school数据库（指定字符集）；
create database school charset=utf8;
切换到school数据库；
use school;
创建student数据表；
create table student (
s_id varchar(10) collate utf8_bin default null,
s_name varchar(20) collate utf8_bin default null,
s_brithday date default null,
s_sex char(2) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向student表插入测试数据
insert into student values ('01', '赵雷', '1990-01-01', '男');
insert into student values ('02', '钱电', '1990-12-21', '男');
insert into student values ('03', '孙风', '1990-05-20', '男');
insert into student values ('04', '李云', '1990-08-06', '男');
insert into student values ('05', '周梅', '1991-12-01', '女');
insert into student values ('06', '吴兰', '1992-03-01', '女');
insert into student values ('07', '郑竹', '1989-07-01', '女');
创建course数据表
create table course (
c_id varchar(10) collate utf8_bin default null,
c_name varchar(20) collate utf8_bin default null,
t_id varchar(10) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向course表插入测试数据
insert into course values ('01', '语文', '02');
insert into course values ('02', '数学', '01');
insert into course values ('03', '英语', '03');
创建sc数据表
create table sc (
s_id varchar(10) collate utf8_bin default null,
c_id varchar(10) collate utf8_bin default null,
score decimal(4,0) default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向sc表插入测试数据
insert into sc values ('01', '01', '80');
insert into sc values ('01', '02', '90');
insert into sc values ('01', '03', '99');
insert into sc values ('02', '01', '70');
insert into sc values ('02', '02', '60');
insert into sc values ('02', '03', '80');
insert into sc values ('03', '01', '80');
insert into sc values ('03', '02', '80');
insert into sc values ('03', '03', '80');
insert into sc values ('04', '01', '50');
insert into sc values ('04', '02', '30');
insert into sc values ('04', '03', '20');
insert into sc values ('05', '01', '76');
insert into sc values ('05', '02', '87');
insert into sc values ('06', '01', '31');
insert into sc values ('06', '03', '34');
insert into sc values ('07', '02', '89');
insert into sc values ('07', '03', '98');
insert into sc values ('07', '01', '99');
创建teacher数据表
create table teacher (
t_id varchar(10) collate utf8_bin default null,
t_name varchar(20) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向teacher插入测试数据
insert into teacher values('01', '张三');
insert into teacher values('02', '赵六');
insert into teacher values('03', '王五');
实际操作练习
统计同学的男女生个数
SELECT
SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END) 男生个数,
SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END) 女生个数
FROM
student;
统计同学的男女生占比（保留两位小数）
SELECT
cast(SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END)/count(1) AS DECIMAL(18,2)) 男生比例,
cast(SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END)/count(1) AS DECIMAL(18,2)) 女生比例
FROM
student;
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
a.*, b.score 课程01的分数,
c.score 课程02的分数
FROM
student a,
sc b,
sc c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.score > c.score;
查询"李"姓老师的数量
SELECT
count(t_name) 李姓老师的数量
FROM
teacher
WHERE
t_name LIKE '李%'
查询语文课程的任职老师姓名
SELECT
t_name
FROM
teacher
WHERE
t_id = (
SELECT
t_id
FROM
course
WHERE
c_name = '语文'
);
查询"张三"老师教过同学的信息
SELECT DISTINCT
student.*
FROM
student,
course,
sc,
teacher
WHERE
student.s_id = sc.s_id
AND course.c_id = sc.c_id
AND course.t_id = teacher.t_id
AND teacher.t_name = '张三';
查询"张三"老师没教过的同学信息
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT DISTINCT
student.s_id
FROM
student,
course,
sc,
teacher
WHERE
student.s_id = sc.s_id
AND course.c_id = sc.c_id
AND course.t_id = teacher.t_id
AND teacher.t_name = '张三'
);
查询平均成绩大于60分的同学的学生编号和学生姓名以及平均分（保留两位小数）
SELECT
student.s_id,
student.s_name,
cast(
avg(sc.score) AS DECIMAL (18, 2)
) avg_score
FROM
student,
sc
WHERE
student.s_id = sc.s_id
GROUP BY
student.s_id
HAVING
avg_score > 60;
查询任意一门课程都大于80分的学生信息
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
sc
GROUP BY
s_id
HAVING
min(score) >= 80
);
查询有一门课程分数超过80分的学生信息
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
sc
GROUP BY
s_id
HAVING
max(score) >= 80
);
查询没有学全所有课程的同学信息
SELECT
s.*
FROM
student s,
sc
WHERE
s.s_id = sc.s_id
GROUP BY
s.s_id
HAVING
count(c_id) < (
SELECT
count(c_id)
FROM
course
);
统计每门课程各等级（60分以下 D等级，60分~75分（不含75分） C等级，75分~85分（不含85分） B等级，85分以上 A等级）分数的人数
SELECT
c_id,
SUM(CASE WHEN score >=85 THEN 1 ELSE 0 end) A,
SUM(CASE WHEN score>=75 AND score <85 THEN 1 ELSE 0 END) B,
SUM(CASE WHEN score>=60 AND score<75 THEN 1 ELSE 0 END) C,
SUM(case WHEN score < 60 THEN 1 ELSE 0 END) D
FROM
sc
GROUP BY
c_id;
查询数学课程的前三名的学生姓名
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
(
SELECT
sc.s_id
FROM
sc,
course
WHERE
sc.c_id = course.c_id
AND course.c_name = '数学'
ORDER BY
sc.score DESC
LIMIT 3
) AS t
);
查询每门课的前三名的同学的信息
待研究
未完待续
