【SQL】通过SQL语句对学生信息表（学号、学生姓名、班级）、学习成绩表（学号、学科、成绩）的数据进行创建、查询、删除、更新

1、插入学生数据

学号	姓名	性别	所在班级	年龄
001	张静	女	一班	20
002	王伟	男	二班	19
003	张三	男	二班	18
004	王武	男	一班	19
005	谢文	男	一班	21
006	叶问	男	一班	22
007	张强	男	一班	20
008	李丽	女	二班	18

 

SQL语句

insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('001','张静','女','一班',20);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('002','王伟','男','二班',19);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('003','张三','男','二班',18);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('004','王武','男','一班',19);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('005','谢文','男','一班',21);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('006','叶强','男','一班',22);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('007','张强','男','一班',20);
insert into student (Sno,Sname,Ssex,Sclass,Sage) values ('008','李丽','女','二班',18);

执行结果

2、插入学生成绩数据

学号	科目	分数
001	语文	70
001	数学	84
002	数学	88
003	语文	92
003	数学	98
004	语文	86
005	数学	67
006	语文	89
006	数学	94
007	语文	93
007	数学	78
008	语文	69
008	数学	100
004	数学	99

 

SQL语句

insert into score (Sno,Ssubject,Sgrade) values ('001','语文',70);
insert into score (Sno,Ssubject,Sgrade) values ('001','数学',84);
insert into score (Sno,Ssubject,Sgrade) values ('002','数学',88);
insert into score (Sno,Ssubject,Sgrade) values ('003','语文',92);
insert into score (Sno,Ssubject,Sgrade) values ('003','数学',98);
insert into score (Sno,Ssubject,Sgrade) values ('004','语文',86);
insert into score (Sno,Ssubject,Sgrade) values ('005','数学',67);
insert into score (Sno,Ssubject,Sgrade) values ('006','语文',89);
insert into score (Sno,Ssubject,Sgrade) values ('006','数学',94);
insert into score (Sno,Ssubject,Sgrade) values ('007','语文',93);
insert into score (Sno,Ssubject,Sgrade) values ('007','数学',78);
insert into score (Sno,Ssubject,Sgrade) values ('008','语文',69);
insert into score (Sno,Ssubject,Sgrade) values ('008','数学',100);
insert into score (Sno,Ssubject,Sgrade) values ('004','数学',99);

执行结果

3、删除学号为005的学生和学生成绩

delete s2 from student s2 where s2.Sno = '005';
delete s1 from student s1 where s1.Sno = '005';

4、更新学号为001语文的成绩为78

update score set Sgrade=78 where Sno = '001' and Ssubject='语文';

5、查询一班男生的成绩

select s1.*,s2.Ssubject,s2.Sgrade from student s1 left join score s2 on s1.Sno=s2.Sno where s1.Sclass='一班';

6、查询一班语文科目成绩，并通过成绩降序

select s1.*,s2.Ssubject,s2.Sgrade from student s1 left join score s2 on s1.Sno=s2.Sno where s1.Sclass='一班' and s2.Ssubject='语文' order by s2.Sgrade desc;

 7、查询语文成绩在90-100之间

select s1.*,s2.Ssubject,s2.Sgrade from student s1 left join score s2 on s1.Sno=s2.Sno where s2.Sgrade between 90 and 100 and s2.Ssubject='语文';

8、 通过班级和科目分组，查询平均分和总分

left join score s2 on s1.Sno=s2.Sno group by s1.Sclass,s2.Ssubject order by s1.Sclass,s2.Ssubject desc;