2022-1-8数据库期末复习提纲（三）

[例49] 查询选修课程1的学生集合与选修课程2的学生集合的交集
本例实际上是查询既选修了课程1又选修了课程2的学生

select Sno
from SC
where Cno = '1' and Sno in
(select Sno
from SC
where Cno = '2');

[例50] 查询学生姓名与教师姓名的交集
本例实际上是查询学校中与教师同名的学生姓名

select distinct Sname
#要考虑到去重的问题呀
#select Sname
from Student
where Sname in
(select Tname
from Teacher);

所谓的其他方法就是：翻译
[例51] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
本例实际上是查询计算机科学系中年龄大于19岁的学生

select *
from Student
where Sdept = 'CS' and Sage > 19;

[例52] 查询学生姓名与教师姓名的差集
本例实际上是查询学校中未与教师同名的学生姓名

select distinct Sname
from Student
where Sname not in
(select Tname
from Teacher);

[例1] 将一个新学生记录
（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

#insert into Student(Sno,Sname,Ssex,Sdept,Sage) 
#values('95020','陈冬','男','IS','18岁');
insert into Student 
values('95020','陈冬','男','IS',18);

[例2] 插入一条选课记录( ‘95020’，'1 ')。

insert into SC(Sno,Cno)
values ( '95020','1');

[例3] 对每一个系，求学生的平均年龄，并把结果存入数据库。

#建立表格
create table DeptAge
(Dept char(15),
AVEage smallint);
#插入数据
insert into DeptAge(Dept,Aveage)
#之后不会写了

1. 修改某一个元组的值
   [例4] 将学生95001的年龄改为22岁。

UPDATE Student
Set Sage = 22
where Sno = '95001';

2. 修改多个元组的值
   [例5] 将所有学生的年龄增加1岁。

update Student
SEt Sage = Sage + 1;

[例6] 将信息系所有学生的年龄增加1岁

update Student
Set Sage = Sage + 1
where Sdept = 'IS';

3. 带子查询的修改语句

[例7] 将计算机科学系全体学生的成绩置零。

update SC
set Grade = 0
where Sno in 
(Select Sno 
from Student
where Sdept = 'CS';

如果不满足完整性约束条件就不会被修改

1. 删除某一个元组的值
   [例8] 删除学号为95019的学生记录。

#delete Student
#where Sno = '95019';
delete
from Student
where Sno = '95019';

2. 删除多个元组的值
   [例9] 删除2号课程的所有选课记录。

delete
from SC
where Cno = '2';

[例10] 删除所有的学生选课记录。

delete
from SC;

3. 带子查询的删除语句
   [例11] 删除计算机科学系所有学生的选课记录。

delete 
from SC
where Sno in 
(Select Sno
from Student
where Sdept = 'CS');

[例1] 建立信息系学生的视图。

create view ISStu
As
select Sname,Sno,Sage
from Student
where Sdept = 'IS';

[例2] 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。

在使用视图的时候，会自动检查视图定义时候的约束条件

基于多个基表的视图
[例4] 建立信息系选修了1号课程的学生视图。

create view IS_S1(Sname,Sno,Grade)
as
select Student.Sno,Sname,Grade 
from Student,SC
#where Cno = '1' and Sdept = 'IS';
#没有注意到两个表格需要连接
where Cno = '1' and Sdept = 'IS' and Student.Sno = SC.Sno;

[例5] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

#我的写法，不够简洁
create view IS_S2
as
select Student.Sno ,Sname,Grade
from Student,SC
where Cno = '1' and Grade >= 90 and Sdept = 'IS' and Student.Sno = SC.Sno;
#其实可以创建基于视图的视图
CREATE VIEW IS_S2
               AS
               SELECT Sno，Sname，Grade
               FROM  IS_S1
               WHERE  Grade>=90；

带表达式的视图
其实只是在为视图选择数据的时候有表达式
[例6] 定义一个反映学生出生年份的视图。

CREATE  VIEW BT_S(Sno，Sname，Sbirth)
#create view Stu_info
#这句写得不对，带有表达式的需要虚拟属性的列名
as
select Sno,Sname,2000-Sage
from Student;

设置一些派生属性列, 也称为虚拟列–Sbirth
带表达式的视图必须明确定义组成视图的各个属性列名

建立分组视图
[例7] 将学生的学号及他的平均成绩定义为一个视图

create view S_G(Sno,AvgGrade)
as
select Sno,AVG(Grade)
from SC;

啊啊啊啊啊啊忘记了平均分要分组

[例8]将Student表中所有女生记录定义为一个视图

create view Girl(Sno,Sname,Sage,Ssex,Sdept)
as
select *
from Student
where Ssex = '女';
🚺😡

缺点：修改基表Student的结构后，Student表
与F_Student1视图的映象关系被破坏，
导致该视图不能正确工作。

        CREATE VIEW
               F_Student2 (stdnum，name，sex，age，dept)
        AS  SELECT Sno，Sname，Ssex，Sage，Sdept
             FROM  Student
                WHERE Ssex='女'；

为基表Student增加属性列不会破坏Student表
与F_Student2视图的映象关系。

[例9] 删除视图IS_S1

drop IS_S1;

[例1] 在信息系学生的视图中找出年龄小于20岁的学生。

select Sno,Sname,Sage
from IS_Student
where Sage < 20;

[例2] 查询信息系选修了1号课程的学生

select Sno,Sname
from IS_Student,SC
where IS_Student.Sno = SC.Sno and SC.Cno = '1';

[例3]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

S_G视图定义： 
       CREATE VIEW S_G (Sno，Gavg)
         AS 
SELECT  Sno，AVG(Grade)
FROM  SC
GROUP BY Sno；

select Sno,Gavg
from S_G
#where GAvg > 90;
where GAvg >= 90;

使用了group by 的集函数查询得使用having 来作为表达式子的条件

[例1] 将信息系学生视图IS_Student中学号95002
的学生姓名改为“刘辰”。

update IS_Student 
set Sname = '刘辰'
where Sno = '95002';

[例2] 向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，20岁

insert into IS_S values('95029','赵新',20);

[例3] 删除视图CS_S中学号为95029的记录

#delete CS_S
delete from CS_S
where Sno = '95029';

例：视图GOOD_SC(修课成绩在平均成绩之上的元组)

create view GOOD_SC
as
select Sno,Cno,Grade
from SC
where Grade >
(select AVG(Grade)
from SC)