数据库的增删改查(精)

学习收藏数据库增删改查
--查询信息系和计算机系的学生，并按学生所在系和学号排序。

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.Sno='20021522'and not exists(select* from sc scz
where scz.sno=scx.sno and o=o
--求各系的系的学生人数的，并将结果按学生人数的降序排序
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,
Count1smallint
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 o=o select*from sname_cname_grade
/*创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询号课程的选课人数、平均分、最高分。

*/
create view CCAM
as
select cno,COUNT(sno count1,AVG(grade
avge,MAX(grade max1from SC
group by Cno
select*from ccam
select count1,avge,max1from 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;。