数据库的增删改查.docVIP

学习收藏数据库增删改查 --查询信息系和计算机系的学生，并按学生所在系和学号排序。 select sno,sname,Sdept from Student where Sdept=CS OR Sdept=IS order by Sdept, sno ASC --查询学生表中最小的年龄。 select MIN(sage)from student --查询课程名中包含“数据”的课程名。 select cno ,cname from course where Cname like %数据% --查询先行课程为空值的课程号、课程名及学分 select cno,cname,ccredit from Course where Cpno is null --查询李勇选修的数据库课程的成绩 select grade from SC where Sno =(select Sno from Student where Sname=李勇)and Cno =(select Cno from Course where cname=数据库) --查询平均成绩分以上的学生的学号 select distinct sno from SC scx where (select AVG (Grade ) from SC scy where scy.sno=scx.Sno )85 --求计算机系没有选修数据库课程的学生姓名 select sname from Student where Sno not in (select Sno from SC where Cno in (select Cno from Course where Sname=数据库 ))and Sdept=IS --求至少选修了学号为S1所选修的全部课程的学生学号 select distinct sno from SC scx where not exists (select * from SC scy where scy.Snoand not exists(select * from sc scz where scz.sno=scx.sno and scy.Cno=scz.Cno )) --求各系的系的学生人数的，并将结果按学生人数的降序排序 select Sdept ,COUNT(sno)from Student group by Sdept order by Sdept ASC --查询选修了数学课程并且成绩高于该门课程平均分的学生学号和成绩 select sno, grade from SC scx where Grade =(select AVG(Grade)from SC scy where Cno =(select Cno from Course where Cname=数学) )and Cno =(select Cno from Course where Cname=数学) /* 将学习了数据库课程的学生成绩加分。*/ update SC set grade=grade+5 where Cno in (select Cno from SC where Cno=(select Cno from Course where Cname=数据库)) select * from SC /* 将计算机系学习了号课程的学生成绩置。*/ update SC set Grade=0 where Sno in ( select sc.Sno from Student,SC where Cno =2 and Sdept=CS) select * from SC /* 将李勇的数据库成绩改为。*/ update SC set Grade=85 where Sno=(select Sno from Student where Sname=李勇) and Cno=(select Cno from Course where Cname=数据库) select * from SC /* 将选修了号课程且成绩为空的选课记录删除。*/ delete from SC where Cno=2 and Grade is null select * from SC /* 从课程表中删除在选课表中没有选课记录的课程记录。*/ delete from Course where Cno not in(select cno from SC ) select * from Course /* 删除计算机系学生选修了数据库课程的选课记录。*/ delete from SC where S