数据库的增删改查　

数据库的增删改查　 学习收藏数据库增删改查 --查询信息系和计算机系的学生，并按学生所在系和学号排序。 select sno,sname,Sdept from Student where Sdept='CS' OR Sdept='IS' order by Sdept, sno ASC --查询学生 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中最小的年龄。 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.Sno='20021522' and 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 Sno in(select Sno from Student where Sdept='CS')and Cno =(select Cno from Course where Cname='数据库') select * from SC /* 求各系的系名及男女生人数并将结果保存到另一个表中。*/ create table Dept_ssex_count (Sdept char(15), Ssex char(2), Count1 smallint) insert into Dept_ssex_count(Sdept,Ssex , Count1) select Sdept,Ssex,count(Sno )from student group by Sdept,Ssex; select *from Dept_ssex_count select *from student /* 将平均成绩分以上的学生的学号，选学的课程数和平均成绩 保存到另一个表中。*/ create table abc ( Sno char(10), Course_count smallint, avge float) insert into abc(Sno,course_count,avge) select Sno,count(cno),avg(grade)from sc scx group by sno having avg(grade)>=80; select*from abc select * from sc /* 创建一个视图，查询没有选修课程的学生学号、姓名和所在系， 并利用该视图查询所有没有选修课程的学生信息。*/ create view No_sc_student as select sno,sname,ssex,sage,sdept from student where sno not in(select distinct Sno from SC ) select * from No_sc_student /* 创建一个给出学生的姓名、课程名和成绩的视图，并利用该 视图查询某个学生学习的课程名和成绩。*/ create view sname_cname_grade as select sname,cname,grade from Student ,Course ,SC where Student.Sno=SC.Sno and SC.Cno =Course.Cno select * from sname_cname_grade /*创建一个视图，求各门课程的课程号、选课人数、平均分、最高分 ，并利用该视图查询号课程的选课人数、平均分、最高分。*/ create view CCAM as select cno,COUNT(sno) count1,AVG(grade) avge,MAX(grade) max1 from SC group by Cno select * from ccam select count1,avge,max1 from ccam where cno='1' /* 创建一个视图，求选修了号课程且成绩高于该门课程平均分的学生 学号和成绩。*/ create view Cno_2(sno,Grade) as select Sno,Grade from SC where Cno='2' and grade >(select AVG(Grade)from 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;