SQL整理（1）查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩

-- 学生表-- 学生编号,学生姓名, 出生年月,学生性别CREATE TABLE `Student`(`s_id` int,`s_name` VARCHAR(32) NOT NULL DEFAULT '',`s_birth` VARCHAR(16) NOT NULL DEFAULT '',`s_sex` CHAR(2) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`

bbb332

16624人浏览 · 2021-06-01 14:30:44

bbb332 · 2021-06-01 14:30:44 发布

先写上最终语句，后面进行分析

SELECT
st.s_id,
st.s_name,
ROUND(AVG(sc.s_score),2) as score
FROM  student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;

建表语句（由于测试使用，没有建立索引）

-- 学生表  
-- 学生编号,学生姓名, 出生年月,学生性别

CREATE TABLE `Student`(
`s_id` int,
`s_name` VARCHAR(32) NOT NULL DEFAULT '',
`s_birth` VARCHAR(16) NOT NULL DEFAULT '',
`s_sex` CHAR(2) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

-- 教师表 
-- 教师编号,教师姓名
CREATE TABLE `Teacher`(
`t_id` int,
`t_name` VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
); 


-- 课程表 
-- 课程编号, 课程名称, 教师编号
CREATE TABLE `Course`(
`c_id` int,
`c_name` VARCHAR(32) NOT NULL DEFAULT '',
`t_id` int,
PRIMARY KEY(`c_id`)
);

-- 成绩表
-- 学生编号,课程编号,分数
CREATE TABLE `Score`(
`s_id` int,
`c_id` int,
`s_score` INT(4),
PRIMARY KEY(`s_id`,`c_id`)
);


		 
-- 插入学生表测试数据
insert into Student values(1 , '赵雷' , '1990-11-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 Teacher values(4 , '林一');


-- 课程表测试数据
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);

下面分析语句

①查询所有人的所有成绩

-- 查询所有人的所有成绩
select 
st.s_id,st.s_name,sc.s_score 
FROM student st left join score sc on st.s_id=sc.s_id

②查询所有人的平均成绩

-- 查询所有人的平均成绩
select
st.s_id,st.s_name,AVG(sc.s_score)
FROM student st left join score sc on st.s_id=sc.s_id
group by st.s_id

备注：此时可以根据成绩排序

③根据成绩筛选大于等于60分的

select 
st.s_id,st.s_name,AVG(sc.s_score) as score
FROM student st left join score sc on st.s_id=sc.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;

可以使用ROUND函数优化显示平均成绩小数点后两位

SELECT
st.s_id,
st.s_name,
ROUND(AVG(sc.s_score),2) as score
FROM  student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;