《数据库的增删改查精》由会员分享，可在线阅读，更多相关《数据库的增删改查精（9页珍藏版）》请在金锄头文库上搜索。
1、数据库的增删改查(精) 作者： 日期：学习收藏数据库增删改查-查询信息系和计算机系的学生，并按学生所在系和学号排序。select sno,sname,Sdept from Student where Sdept=CS OR Sdept=ISorder by Sdept, sno ASC-查询学生表中最小的年龄。select MIN(sagefrom student-查询课程名中包含“数据”的课程名。select cno ,cname from coursewhere Cname like %数据%-查询先行课程为空值的课程号、课程名及学分select cno,cname,ccredit fro
2、m Coursewhere Cpno is null-查询李勇选修的数据库课程的成绩select grade from SCwhere Sno =(select Sno from Studentwhere Sname=李勇and Cno =(select Cno from Coursewhere cname=数据库-查询平均成绩分以上的学生的学号select distinct sno from SC scxwhere (select AVG (Grade from SC scywhere scy.sno=scx.Sno 85-求计算机系没有选修数据库课程的学生姓名select sname fr
3、om Student where Sno not in (select Sno from SC where Cno in (select Cno from Coursewhere Sname=数据库 and Sdept=IS-求至少选修了学号为S1所选修的全部课程的学生学号select distinct sno from SC scx where not exists (select * from SC scywhere scy.Sno=20021522 and not exists(select * from sc sczwhere scz.sno=scx.sno and scy.Cno=s
4、cz.Cno -求各系的系的学生人数的，并将结果按学生人数的降序排序select Sdept ,COUNT(snofrom Student group by Sdept order by Sdept ASC-查询选修了数学课程并且成绩高于该门课程平均分的学生学号和成绩select sno, grade from SC scxwhere Grade =(select AVG(Gradefrom SC scywhere Cno =(select Cno from Course where Cname=数学 and Cno =(select Cno from Course where Cname=数
5、学 /* 将学习了数据库课程的学生成绩加分。*/update SC set grade=grade+5where Cno in (select Cno from SC where Cno=(select Cno from Course where Cname=数据库select * from SC /* 将计算机系学习了号课程的学生成绩置。*/update SC set Grade=0where Sno in ( select sc.Sno from Student,SC where Cno =2 and Sdept=CSselect * from SC /* 将李勇的数据库成绩改为。*/up
6、date SC set Grade=85where 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
7、 SC select * from Course /* 删除计算机系学生选修了数据库课程的选课记录。*/delete from SC where Sno in(select Sno from Student where Sdept=CSand Cno =(select Cno from Course where Cname=数据库select * from SC /* 求各系的系名及男女生人数并将结果保存到另一个表中。*/create table Dept_ssex_count(Sdept char(15,Ssex char(2,Count1 smallintinsert into Dept_
8、ssex_count(Sdept,Ssex , Count1select Sdept,Ssex,count(Sno from studentgroup by Sdept,Ssex;select *from Dept_ssex_countselect *from student/* 将平均成绩分以上的学生的学号，选学的课程数和平均成绩保存到另一个表中。*/create table abc(Sno char(10,Course_count smallint,avge floatinsert into abc(Sno,course_count,avgeselect Sno,count(cno,avg
9、(gradefrom sc scxgroup by snohaving avg(grade=80;select*from abcselect * from sc/* 创建一个视图，查询没有选修课程的学生学号、姓名和所在系，并利用该视图查询所有没有选修课程的学生信息。*/create view No_sc_studentasselect sno,sname,ssex,sage,sdept from studentwhere sno not in(select distinct Sno from SC select * from No_sc_student/* 创建一个给出学生的姓名、课程名和成绩
10、的视图，并利用该视图查询某个学生学习的课程名和成绩。*/create view sname_cname_gradeasselect sname,cname,grade from Student ,Course ,SCwhere Student.Sno=SC.Sno and SC.Cno =Course.Cno select * from sname_cname_grade/*创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询号课程的选课人数、平均分、最高分。*/create view CCAMasselect cno,COUNT(sno count1,AVG(grad
11、e avge,MAX(grade max1 from SC group by Cno select * from ccam select count1,avge,max1 from ccamwhere cno=1/* 创建一个视图，求选修了号课程且成绩高于该门课程平均分的学生学号和成绩。*/create view Cno_2(sno,Gradeasselect Sno,Grade from SC where Cno=2 and grade (select AVG(Gradefrom SC where Cno=2 select * from Cno_2/*将学生表的select权限赋给用户user1，然后以user1登录，检查权限情况。*/create role use1 /*创建角色use1*/grant select on Student to use1/*收回用户user1的对学生表的select权限，然后以user1登录，检查权限情况。*/revoke select on student from use1;
