自从大学毕业以后，就很少接触数据库了。即使工作了3年，干的主流业务也只是编写客户端的工作，有时会接触一点数据库的查询操作，写写sql语句啥的，但也只涉及到单表查询。所以，我创建了几张表，来回味一下sql语句的写法。

创建数据库并插入数据

//创建学生表，字段为：学生ID、姓名、出生日期和性别。
CREATE TABLE Students(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id));
//创建老师表，字段为：老师ID和姓名
CREATE TABLE Teachers(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id));
//创建课程表，字段为：课程ID、课程名称和老师ID
CREATE TABLE Courses(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id));
//创建成绩表，字段为：学生ID、课程ID、学生成绩
CREATE TABLE Scores(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id));

插入数据

//插入学生数据
insert into Students values('01' , '赵雷' , '1990-01-01' , '男');
insert into Students values('02' , '钱电' , '1990-12-21' , '男');
insert into Students values('03' , '孙风' , '1990-05-20' , '男');
insert into Students values('04' , '李云' , '1990-08-06' , '男');
insert into Students values('05' , '周梅' , '1991-12-01' , '女');
insert into Students values('06' , '吴兰' , '1992-03-01' , '女');
insert into Students values('07' , '郑竹' , '1989-07-01' , '女');
insert into Students values('08' , '王菊' , '1990-01-20' , '女');
//插入老师数据
insert into Teachers values('01' , '张三');
insert into Teachers values('02' , '李四');
insert into Teachers values('03' , '王五');
//插入课程数据
insert into Courses values('02' , '数学' , '01');
insert into Courses values('01' , '语文' , '02');
insert into Courses values('03' , '英语' , '03');
//插入成绩数据
insert into Scores values('01' , '01' , '80');
insert into Scores values('01' , '02' , '90');
insert into Scores values('01' , '03' , '99');
insert into Scores values('02' , '01' , '70');
insert into Scores values('02' , '02' , '60');
insert into Scores values('02' , '03' , '80');
insert into Scores values('03' , '01' , '80');
insert into Scores values('03' , '02' , '80');
insert into Scores values('03' , '03' , '80');
insert into Scores values('04' , '01' , '50');
insert into Scores values('04' , '02' , '30');
insert into Scores values('04' , '03' , '20');
insert into Scores values('05' , '01' , '76');
insert into Scores values('05' , '02' , '87');
insert into Scores values('06' , '01' , '31');
insert into Scores values('06' , '03' , '34');
insert into Scores values('07' , '02' , '89');
insert into Scores values('07' , '03' , '98');

查询练习

//查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT st.*, sc.s_score AS '语文', sc2.s_score AS '数学'
FROM Students st
LEFT JOIN Scores sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN Scores sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE sc.s_score > sc2.s_score

//查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.*, st.s_name, ROUND(AVG(sc.s_score),2)
FROM Students st
LEFT JOIN Scores sc ON sc.s_id = st.s_id
GROUP BY st.s_id
HAVING AVG(sc.s_score) >= 60

//查询学过"张三"老师授课的同学的信息
SELECT st.*
FROM Students st
LEFT JOIN Scores sc ON sc.s_id = st.s_id
LEFT JOIN Courses c ON c.c_id = sc.c_id
LEFT JOIN Teachers t ON t.t_id = c.t_id
WHERE t.t_name = '张三'

//查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT st.* 
FROM Students st
INNER JOIN Scores sc ON sc.s_id = st.s_id
INNER JOIN Courses c ON c.c_id = sc.c_id
AND c.c_id = '01'
WHERE st.s_id IN (
    SELECT st2.s_id
    FROM Students st2
    INNER JOIN Scores sc2 ON sc2.s_id = st2.s_id
    INNER JOIN Courses c2 ON c2.c_id = sc2.c_id
    AND c2.c_id = '02'
)

//查询没有学全所有课程的同学的信息
SELECT st.* 
FROM Students st
LEFT JOIN Scores s ON st.s_id = s.s_id
GROUP BY st.s_id
HAVING COUNT(c_id) < (SELECT COUNT(c_id) FROM Courses)

//检索"01"课程分数小于60的学生信息，按分数降序排列
SELECT st.*, sc.s_score
FROM Students st
INNER JOIN Scores sc ON sc.s_id = st.s_id 
AND sc.c_id = '01'
AND sc.s_score < 60
ORDER BY sc.s_score DESC

//查询不同老师所教不同课程平均分从低到高显示
SELECT Teachers.t_id, Teachers.t_name, Courses.c_name, AVG(Scores.s_score) 
FROM Teachers
LEFT JOIN Courses ON Courses.t_id = Teachers.t_id
LEFT JOIN Scores ON Scores.c_id = Courses.c_id
GROUP BY Teachers.t_id, Courses.c_name
ORDER BY AVG(Scores.s_score) ASC

//查询名字中含有"风"字的学生信息
SELECT * FROM Students WHERE Students.s_name LIKE '%风%'