1、插入学生数据
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、插入学生成绩数据
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;
【SQL】通过SQL语句对学生信息表（学号 学生姓名 班级） 学习成绩表（学号 学科 成绩）的数据进行创建 查询 删除 更新
