create table t_score(
id int primary key comment '主键',
stu_num varchar(20) unique comment '学号',
stu_name varchar(10) not null comment '姓名',
stu_sub varchar(50) not null comment '科目',
stu_score double(11,1) comment '成绩'
);
insert into t_score(id,stu_num,stu_name,stu_sub,stu_score) value
(1,'2020101','张三','高数','85'),
(2,'2020102','李一','英语','63'),
(3,'2020103','李二','英语','78'),
(4,'2020104','李三','英语','76.5'),
(5,'2020105','李四','英语','83'),
(6,'2020106','王一','物理','26'),
(7,'2020107','王二','物理','59.5'),
(8,'2020108','王三','物理','85'),
(9,'2020109','王四','物理','22'),
(10,'2020110','小明','高数','13'),
(11,'2020111','小红','高数','69'),
(12,'2020112','小绿','高数','70');
select stu_score from t_score where stu_name like '%二%';
select stu_sub ,min(stu_score),max(stu_score) from t_score group by stu_sub;
select * from t_score where stu_score = (select max(stu_score) from t_score where stu_sub = '英语') and stu_sub = '英语';
select max(stu_score) - min(stu_score) from t_score where stu_sub = '英语'
select * from t_score where stu_sub = '英语' and stu_score > (select avg(stu_score) from t_score)
select * from t_score where stu_sub = '英语' and stu_score = '83'
select stu_score from t_score where stu_sub = '英语' order by stu_score desc
select subject from s_socer t where exists(
select a.* from
(select f.subject, avg(f.socer) as socerd from s_socer f group by f.subject) a where t.subject = '语文' and t.socer > a.socerd+5);