数据库的增删改查(精)

1、学习收藏数据库增删改查-查询信息系和计算机系的学生，并按学生所在系和学号排序 selectsno,sname,Sdeptfrom Student where Sdept='CS' OR Sdept='IS' order by Sdept, sno ASC-查询学生表中最小的年龄。 selectMIN (sagefrom student -查询课程名中包含 “数据 ”的课程名。 select cno ,cnamefrom course where Cname like '%数据 %'-查询先行课程为空值的课程号、课程名及学分 selectcno,c

2、name,ccredit from Course where Cpno is null-查询李勇选修的数据库课程的成绩 selectgradefrom SC where Sno =(selectSno from Student where Sname='李勇'and Cno =(select Cno from Course where cname='数据库'-查询平均成绩分以上的学生的学号 select distinct sno from SCscx where (select AVG (Grade from SCscy where scy.sno=scx.Sn

3、o >85-求计算机系没有选修数据库课程的学生姓名select snamefrom Studentwhere Sno not in (selectSno from SCwhere Cno in (selectCno from Coursewhere Sname='数据库' and Sdept='IS'-求至少选修了学号为 S1 所选修的全部课程的学生学号select distinct sno from SCscxwherenot exists (select * from SCscywhere scy.Sno='20021522'and n

4、ot exists(select* from scscz wherescz.sno=scx.snoandscy.Cno=scz.Cno-求各系的系的学生人数的，并将结果按学生人数的降序排序 selectSdept,COUNT(snofrom Studentgroup by Sdeptorder by Sdept ASC-查询选修了数学课程并且成绩高于该门课程平均分的学生学号和成绩 selectsno, gradefrom SCscxwhere Grade >=(selectAVG (Gradefrom SCscywhere Cno =(selectCno from Coursewhere

5、 Cname='数学' and Cno =(selectCno from Coursewhere Cname='数学 '/* 将学习了数据库课程的学生成绩加分。 */ updateSC setgrade=grade+5 where Cno in (selectCno from SC where Cno=(select Cno from Course where Cname='数据库' select* from SC/* 将计算机系学习了号课程的学生成绩置。 */ updateSCsetGrade=0where Sno in ( selectsc.

6、Sno from Student,SCwhere Cno ='2' andSdept='CS' select* from SC/* 将李勇的数据库成绩改为。 */ updateSCsetGrade=85where Sno=(select Sno from Studentwhere Sname='李勇' and Cno=(selectCno from Coursewhere Cname='数据库' select* from SC/* 将选修了号课程且成绩为空的选课记录删除。 */ deletefrom SC where Cno=&#

7、39;2' and Grade is null select* from SC /* 从课程表中删除在选课表中没有选课记录的课程记录。 */ deletefrom Coursewhere Cno not in(selectcno from SCselect * from Course/* 删除计算机系学生选修了数据库课程的选课记录。 */ deletefrom SCwhere Sno in(select Sno from Student where Sdept='CS'and Cno =(selectCno from Coursewhere Cname='数据库

8、 ' select* from SC/* 求各系的系名及男女生人数并将结果保存到另一个表中。 */ createtable Dept_ssex_count(Sdeptchar(15,Ssexchar(2,Count1 smallintinsert into Dept_ssex_coun(tSdept,Ssex, Count1select Sdept,Ssex,count(Sno from studentgroup by Sdept,Ssex;select * from Dept_ssex_countselect * from student/* 将平均成绩分以上的学生的学号，选学的课程

9、数和平均成绩保存到另一个表中。 */ createtable abc(Sno char(10,Course_countsmallint,avge floatinsert into abc(Sno,course_coun,tavge selectSno,count(cno,avg(gradefrom scscx group by snohaving avg(grade>=80;select* from abcselect * from sc/* 创建一个视图，查询没有选修课程的学生学号、姓名和所在系， 并利用该视图查询所有没有选修课程的学生信息。 */ createview No_sc_s

10、tudentasselect sno,sname,ssex,sage,sdept from student wheresno not in(selectdistinct Snofrom SC select * from No_sc_student /* 创建一个给出学生的姓名、课程名和成绩的视图，并利用该 视图查询某个学生学习的课程名和成绩。 */createview sname_cname_gradeasselect sname,cname,grade from Student ,Course ,SCwhere Student.Sno=SC.Sno and SC.Cno =Course.Cn

11、oselect * from sname_cname_grade /*创建一个视图，求各门课程的课程号、选课人数、平均分、最高分 ，并利用该视图查询号课程的选课人数、平均分、最高分。 */ createview CCAMasselectcno,COUNT(snocount1,AVG(gradeavge,MAX (grademax1 from SC group by Cnoselect * from ccamselectcount1,avge,max1 from ccamwhere cno='1'/* 创建一个视图，求选修了号课程且成绩高于该门课程平均分的学生 学号和成绩。 */createview Cno_2(sno,GradeasselectSno,Gradefrom SCwhere Cno='2' and grade >(select AVG (Gradefrom SC where Cno='2' select * from Cno_2/* 将学生表的 selec