drop table tb_student_score;
-- 创建表
create table tb_student_score(
student varchar(30),
course varchar(10),
score int
);
-- 批量插入数据
insert into tb_student_score(student,course,score)
values('李雷','语文',83),
('李雷','数学',98),
('李雷','英语',55),
('韩梅梅','语文',90),
('韩梅梅','数学',92),
('韩梅梅','英语',85),
('Lily','语文',58),
('Lily','数学',80),
('Lily','英语',99),
('Lucy','语文',53),
('Lucy','数学',64),
('Lucy','英语',88);
select * from tb_student_score;
A. 请用一个查询取出成绩不及格的学生和不及格的科目
select student,course from tb_student_score where score<60;
B. 请用一个查询取出每个学生三科成绩的平均分,并从高到低排列
-- 先按照每个学生进行分组,求出他们平均分
select student 学生 ,avg(score) 平均分 from tb_student_score group by student order by 平均分 desc;
C. 老师发现韩梅梅数学成绩不是92,应该是91,请用一个语句修改表中的内容
update tb_student_score set score=91 where student='韩梅梅' and course='数学';
D. Jim Green的三科成绩也出来了,分别是语文79,数学81,英语93,请将Jim的成绩插入到表中
insert into tb_student_score values('Jim Green','语文',79),('Jim Green','数学',81),('Jim Green','英语',93);
E. 检查系统数据发现有两条记录重复如('Lucy','英语',88 ),请问如何查出重复的记录并删除?
insert into tb_student_score values('Lucy','英语',88 );
-- -------办法1--------------------
-- 把查询出来的结果当做一张零时表
create table tb_temp as select distinct * from tb_student_score;
-- 删除原表中所有的数据
truncate table tb_student_score;
-- 把tb_temp中的数据插入到tb_student_score原表中
insert into tb_student_score select * from tb_temp;
select * from tb_student_score;