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;