一、前期的准备
1.学生表
student(s_id,s_name,s_birth,s_gender)
字段意义:学生编号,学生姓名, 出生年月,学生性别
2.课程表
course(c_id,c_name,t_id)
字段意义:课程编号, 课程名称, 教师编号
注意:这里的教师编码其实是一个外键
3.教师表
teacher(t_id,t_name)
字段意义:教师编号,教师姓名
4.成绩表
score(s_id,c_id,score)
字段意义:学生编号,课程编号,分数
二、分析过程
三、建表代码
-- 创建学生表
CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth DATE,
s_gender VARCHAR(10)
);
-- 创建教师表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20) NOT NULL DEFAULT ''
);
-- 创建课程表
CREATE TABLE course(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id INT NOT NULL
);
-- 创建成绩表
CREATE TABLE score(
s_id INT,
c_id INT,
score DOUBLE(4,1),
PRIMARY KEY(s_id,c_id)
);
-- 创建学生教师关联表(其实可以不要)
CREATE TABLE student_teacher(
s_id INT NOT NULL,
t_id INT NOT NULL
);
四、测试数据(数据来源于网上搜索的)
-- 插入学生表测试数据
INSERT INTO Student VALUES(1, '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES(2, '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES(3, '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES(4, '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES(5, '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES(6, '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES(7, '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES(8, '王菊' , '1990-01-20' , '女');
-- 插入教师表测试数据
INSERT INTO Teacher VALUES(1, '张三');
INSERT INTO Teacher VALUES(2, '李四');
INSERT INTO Teacher VALUES(3, '王五');
-- 插入课程表测试数据
INSERT INTO Course VALUES(1, '语文' ,2);
INSERT INTO Course VALUES(2, '数学' ,1);
INSERT INTO Course VALUES(3, '英语' ,3);
-- 插入成绩表测试数据
INSERT INTO Score VALUES(1 , 1 , 80);
INSERT INTO Score VALUES(1 , 2 , 90);
INSERT INTO Score VALUES(1 , 3 , 99);
INSERT INTO Score VALUES(2 , 1 , 70);
INSERT INTO Score VALUES(2 , 2 , 60);
INSERT INTO Score VALUES(2 , 3 , 80);
INSERT INTO Score VALUES(3 , 1 , 80);
INSERT INTO Score VALUES(3 , 2 , 80);
INSERT INTO Score VALUES(3 , 3 , 80);
INSERT INTO Score VALUES(4 , 1 , 50);
INSERT INTO Score VALUES(4 , 2 , 30);
INSERT INTO Score VALUES(4 , 3 , 20);
INSERT INTO Score VALUES(5 , 1 , 76);
INSERT INTO Score VALUES(5 , 2 , 87);
INSERT INTO Score VALUES(6 , 1 , 31);
INSERT INTO Score VALUES(6 , 3 , 34);
INSERT INTO Score VALUES(7 , 2 , 89);
INSERT INTO Score VALUES(7 , 3 , 98);
题目
1、查询1课程比2课程成绩高的学生的信息及课程分数
分析:
1.1 把课程号为1的学生号和分数查询出来 得到了一个多行多列的表 作为表a
1.2 把课程号为2的学生号和分数查询出来 得到了一个多行多列的表 作为表b
1.3 当a表中的 s_id 等于b表中的 s_id 的时候,这个时候比较分数 得到s_id
1.4 再开始查询s.*(学生信息)通过s_id来自student as s中 ,
再查询课程分数1为语文,2为数学 来自表a和表b
代码:
SELECT s.*,a.score AS 语文,b.score AS 数学
FROM
student AS s,
(SELECT s_id,score FROM score WHERE c_id = 1) AS a,
(SELECT s_id,score FROM score WHERE c_id = 2) AS b
WHERE
a.s_id = b.s_id AND a.score>b.score AND s.s_id = a.s_id
结果:
s_id s_name s_birth s_gender 语文 数学
------ ------ ---------- -------- ------ --------
2 钱电 1990-12-21 男 70.0 60.0
4 李云 1990-08-06 男 50.0 30.0
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
分析:
3.1 首先查找出学生编号和学生平均成绩从分数表中
代码:
SELECT a.s_id,a.score
FROM score AS a
结果:
s_id score
------ --------
1 80.0
1 90.0
1 99.0
2 70.0
2 60.0
2 80.0
3 80.0
3 80.0
3 80.0
4 50.0
4 30.0
4 20.0
5 76.0
5 87.0
6 31.0
6 34.0
7 89.0
7 98.0
3.2 从拿到的结果集来看,需要利用到分组
代码:
SELECT a.s_id,a.score
FROM score AS a
GROUP BY a.s_id
结果:
s_id score
------ --------
1 80.0
2 70.0
3 80.0
4 50.0
5 76.0
6 31.0
7 89.0
从结果来看,如果这样分组其实只显示了每个s_id对应的第一个分数,存在问题,因此就需要用到平均值avg()函数
3.3 再加上聚合函数avg()
代码:
SELECT a.s_id,AVG(a.score)
FROM score AS a
GROUP BY a.s_id
结果:
s_id avg(a.score)
------ --------------
1 89.66667
2 70.00000
3 80.00000
4 33.33333
5 81.50000
6 32.50000
7 93.50000
3.4 找出大于等于60分的,想到having,因为where只能再聚合函数之前使用,having可以聚合函数之后使用
代码:
SELECT a.s_id,AVG(a.score)
FROM score AS a
GROUP BY a.s_id
HAVING AVG(a.score)>=60
结果:
s_id avg(a.score)
------ --------------
1 89.66667
2 70.00000
3 80.00000
5 81.50000
7 93.50000
3.5 还需要添加一列学生名字来源于学生表中,这里思考了好久
子查询的应用
(3)子查询
概念:查询中嵌套子查询,称嵌套查询为子查询
类型:
①子查询的结果是单行单列
结果用作条件,使用运算符来判断
②子查询的结果是多行单列
结果用作条件,使用运算符in来判断
③子查询的结果是多行多列
结果用作一个虚拟表来进行表的内连接查询
①使用了子查询,也就是直接从学生表里查到学生名字,直接作为一个字段
代码:
SELECT
a.s_id AS 编号,
(SELECT s.s_name FROM student AS s WHERE s.s_id = a.s_id) AS 姓名,
AVG(a.score) AS 平均成绩
FROM
score AS a
GROUP BY
a.s_id
HAVING
平均成绩>=60
结果:
编号 姓名 平均成绩
------ ------ --------------
1 赵雷 89.66667
2 钱电 70.00000
3 孙风 80.00000
5 周梅 81.50000
7 郑竹 93.50000
②使用连接查询应该也是可以的
其实相当于先查一个,再补充一个查询
代码:
SELECT a.s_id,s.s_name,AVG(a.score)
FROM score AS a
INNER JOIN student AS s ON s.s_id = a.s_id
GROUP BY a.s_id
HAVING AVG(a.score)>=60
结果:
s_id s_name avg(a.score)
------ ------ --------------
1 赵雷 89.66667
2 钱电 70.00000
3 孙风 80.00000
5 周梅 81.50000
7 郑竹 93.50000
6、查询"李"姓老师的数量
-- 模糊查询使用like这个运算符
-- %代替是后面所有,_代替的是一个字符
代码:
SELECT COUNT(t.t_name) AS '数量'
FROM teacher AS t
WHERE t.t_name LIKE '李%'
结果:
数量
--------
1
描述:7、查询学过"张三"老师授课的同学的信息(其实这道题有bug,老师可能会重名)
分析:
①从教师表中查询到张三老师对应的教师编号(这里是一个教师只有一个编号)
SELECT t.t_id
FROM teacher AS t
WHERE t.t_name = '张三'
②把①中做为条件,用来查询课程表,找到张三老师教的课程,得到结果集
SELECT c.c_id
FROM course AS c
WHERE c.t_id =
(
SELECT t.t_id
FROM teacher AS t
WHERE t.t_name = '张三'
)
③再把②中的这个结果集做为条件,用来查询学过该课程的学生编号,得到结果集;
注意:该处使用了IN这个运算符
SELECT a.s_id
FROM score AS a
WHERE a.c_id =
(
SELECT c.c_id
FROM course AS c
WHERE c.t_id IN
(
SELECT t.t_id
FROM teacher AS t
WHERE t.t_name = '张三'
)
)
④再把③中这个结果集做为条件,用来查询学生表,得到最终的结果
SELECT s.*
FROM student AS s
WHERE s.s_id IN
(
SELECT a.s_id
FROM score AS a
WHERE a.c_id IN
(
SELECT c.c_id
FROM course AS c
WHERE c.t_id =
(
SELECT t.t_id
FROM teacher AS t
WHERE t.t_name = '张三'
)
)
)
结果:
s_id s_name s_birth s_gender
------ ------ ---------- ----------
1 赵雷 1990-01-01 男
2 钱电 1990-12-21 男
3 孙风 1990-05-20 男
4 李云 1990-08-06 男
5 周梅 1991-12-01 女
7 郑竹 1989-07-01 女
最后总结:
用到IN这个关键字,注意条件如果是一个集合的时候需要使用,即张三老师可能教学多门课程
9、查询学过编号为1并且也学过编号为2的课程的同学的信息
分析:
①查询学过编号为1的课程学生,得到一个表,称为表a
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=1
②查询学过编号为2的课程学生,得到一个表,称为表b
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=2
③这个时候表a和表b内联,查询条件是a.s_id=b.s_id,找到学过1和学过2的学生编号
SELECT a.s_id
FROM
(
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=1
) AS a
INNER JOIN
(
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=2
) AS b
ON a.s_id = b.s_id
④通过编码来查询学生信息
SELECT s.*
FROM student AS s
WHERE s.s_id IN
(
SELECT a.s_id
FROM
(
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=1
) AS a
INNER JOIN
(
SELECT sc.s_id
FROM score AS sc
WHERE sc.c_id=2
) AS b
ON a.s_id = b.s_id
)
结果
s_id s_name s_birth s_gender
------ ------ ---------- ----------
1 赵雷 1990-01-01 男
2 钱电 1990-12-21 男
3 孙风 1990-05-20 男
4 李云 1990-08-06 男
5 周梅 1991-12-01 女