数据库作业七

例题所使用的表如下：

Create Table student(
Sno char(12) Primary Key, 
Sname char(12),
Ssex char(2),
Sdept char(40),
Sage SmallInt
);
Insert Into Student Values ('20191102062','刘哲轩','男','Computer Science',19)
Insert Into Student Values ('20191102063','叶晓晓','男','information security',20)
Insert Into Student Values ('20191102064','刘小轩','女','Computer Science',21)
Insert Into Student Values ('20191102065','叶大大','男','information security',20)
Insert Into Student Values ('20191102066','刘大轩','男','Computer Science',22)
Insert Into Student Values ('20191102067','刘轩','女','information security',20)
Insert Into Student Values ('20191102068','刘车干','男','Computer Science',19)
Insert Into Student Values ('20191102069','刘刘','女','information security',20)
Insert Into Student Values ('20191102070','刘刘流','男','Computer Science',22)
Insert Into Student Values ('20191102071','叶小刘','女','information security',20)
Create Table Course(
   Cno Char(12) Primary Key,--课序号
   Cname Char(12),
   Cpno Char(12),--先行课的课序号
   Ccredit SmallInt,
   Foreign Key(Cpno) References Course(Cno)
);
Insert Into Course Values('1','数据库',null,4)
Insert Into Course Values('2','数学',null,2)
Insert Into Course Values('3','信息系统',null,4)
Insert Into Course Values('4','操作系统',null,3)
Insert Into Course Values('5','数据结构',null,4)
Insert Into Course Values('6','数据处理',null,2)
Insert Into Course Values('7','PASCAL语言',null,4)
update course set cpno='5' where cno='1'
update course set cpno='1' where cno='3'
update course set cpno='6' where cno='4'
update course set cpno='7' where cno='5'
update course set cpno='6'where cno='7'
Create Table SC(--学生选课表
  Sno Char(12),
  Cno Char(12),
  Grade SmallInt,
  Primary Key(Sno, Cno),
  Foreign Key(Sno) References Student(Sno),
  Foreign Key(Cno) References Course(Cno)
);
Insert Into SC Values('20191102062', '1', 100)
Insert Into SC Values('20191102062', '2', 51)
Insert Into SC Values('20191102063', '1', 65)
Insert Into SC Values('20191102064', '2', 46)
Insert Into SC Values('20191102065', '3', 85)
Insert Into SC Values('20191102066', '3', 95)
Insert Into SC Values('20191102067', '7', 99)
Insert Into SC Values('20191102068', '6', 93)

例题练习

在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（nestedquery)。例如:

Select Sname From Student Where Sno In
(Select Sno From SC Where Cno = '2')

执行结果如下：

在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。

例3.55 查询与“刘哲轩”在同一个系学习的学生。

Select Sno, Sname, Sdept
From Student Where Sdept In(
Select Sdept From Student
Where Sname = '刘哲轩'
)

结果如下：

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件.

例3.56 查询选修了课程名为“信息系统”的学生学号和姓名。

本查询涉及学号、姓名和课程名三个属性。学号和姓名存放在:Student表中，课程名存放在Course表中，但 Student与 Course两个表之间没有直接联系，必须通过SC 表建立它们二者之间的联系。所以本查询实际上涉及三个关系。

Select Sno, Sname From Student
Where Sno In(
Select Sno From SC
Where Cno In(
Select Cno From Course
Where Cname = '信息系统'
)
)

查询结果如下：

例3.55和例3.56中子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是较简单的一类子查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询(correlated subquery)，整个查询语句称为相关嵌套查询（correlatednested query）语句。

例3.57 找出每个学生超过他自己选修课程平均成绩的课程号。

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用>、<、=、>=、<=、=或<>等比较运算符。

Select Sno, Cno 
From SC x
Where Grade >= (Select AVG(Grade) From SC y Where y.Sno=x.Sno)

结果如下：

x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询是求一个学生所有选修课程平均成绩的，至于是哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的,因此这类查询称为相关子查询。

---

Any 和All与各类比较运算符组合的效果如下：

例3.58 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

Select Sname, Sage
From Student Where Sage < Any(Select Sage From Student
Where Sdept='Computer Science' 
)
And Sdept <> 'Computer Science'

结果如下：

例3.59 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECT Sname,Sage FROM Student
WHERE Sage<ALL
(SELECT Sage FROM Student
WHERE Sdept = 'Computer Science')
AND Sdept <> 'Computer Science';

本查询同样也可以用聚集函数实现。

Select Sname, Sage From
Student Where Sage < (
Select Min(Sage) From Student
Where Sdept = 'Computer Science'
)
And Sdept <> 'Computer Science'

事实上，用聚集函数实现子查询通常比直接用ANY 或ALL查询效率要高
Any与All的对应聚集函数的关系如下：

---

EXISTS 代表存在量词$?$:。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

例3.60 查询所有选修了1号课程的学生姓名。

本查询涉及Student 和SC表。可以在Student 中依次取每个元组的Sno 值，用此值去检查SC表。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送入结果表。将此想法写成SQL语句是:

Select Sname From Student
Where Exists(
Select * From SC
Where Sno=Student.Sno And Cno='1');

结果如下：

使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。
本例中子查询的查询条件依赖于外层父查询的某个属性值（Student 的 Sno值)，因此也是相关子查询。

例3.61 查询没有选修1号课程的学生姓名。

使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的 WHERE子句返回真值，否则返回假值。

Select Sname From Student 
Where Not Exists(
Select * From SC
Where Sno = Student.Sno And Cno = '1'
)

运行结果如下：

由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值,因此其效率并不一定低于不相关子查询，有时是高效的方法。

例3.62 查询选修了全部课程的学生姓名。

SQL中没有全称量词(for all)，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
$(?x)P\equiv ?((?x)(?P))$
SQL语句如下：

Select Sname From Student
Where Not Exists (
Select * From Course
Where Not Exists(
Select * From SC
Where Sno = Student.Sno
And Cno = Course.Cno
)
);

例3.63 查询至少选修了学生201215122选修的全部课程的学生号码。

书上的证明如下：
本查询可以用逻辑蕴涵来表达:查询学号为x的学生,对所有的课程y,只要201215122学生选修了课程y，则x也选修了y。形式化表示如下:
用p表示谓词“学生201215122选修了课程y”
$(?y)p\to q$
用q表示谓词“学生x选修了课程y”就学以虑贤淡本则上述查询为
$p\to q\equiv ?p\vee q$
该查询可以转换为如下等价形式:
$(?y)p\to q$ $\equiv$ $?$ $?$ $y(p\wedge ?q)$
它所表达的语义为:不存在这样的课程y，学生201215122选修了y，而学生x没有选。用SQL 语言表示如下:

Select Distinct Sno
From SC SCX Where Not Exists (
Select * From SC SCY
Where SCY.Sno = '20191102062' And
Not Exists(
Select * From
SC SCZ Where SCZ.Sno=SCX.Sno And
SCZ.Cno=SCY.Cno
)
)

运行结果如下：

这是因为本表中没有选修了’20191102062’所选修的全部课程的人,hhh.

---

SELECT 语句的查询结果是元组的集合,所以多个SELECT 语句的结果可进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 EXCEPT。

例3.64 查询计算机科学系的学生及年龄不大于19岁的学生。

Select * From Student 
Where Sdept = 'Computer Science'
Union Select * From Student Where Sage <= 19;

结果如下：

本查询实际上是求计算机科学系的所有学生与年龄不大于19 岁的学生的并集。使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用UNION ALL 操作符。

例3.65 查询选修了课程1或者选修了课程2的学生。

Select Sno From SC Where Cno = '1'
Union
Select Sno From SC Where Cno = '2'

结果如下：

例3.66 查询计算机科学系的学生与年龄不大于19岁的学生的交集。

Select Sno From Student Where Sdept = 'Computer Science' 
Intersect
Select Sno From Student Where Sage <= 19

结果如下：

例3.67 查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

Select Sno From SC Where Cno = '1'
Intersect
Select Sno From SC Where Cno = '2'

结果如下：

例3.68 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

Select Sno, Sage From Student Where Sdept = 'Computer Science' 
Except
Select Sno,Sage From Student Where Sage <= 19

结果如下：

---

子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件,也可以嵌套在 INSERT语句中用以生成要插入的批量数据。

插入子查询结果的INSERT语句格式为
INSERT
INTO<表名>[(<属性列1>[,<属性列2>…])子查询;

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

首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄。
然后对 Student表按系分组求平均年龄，再把系名和平均年龄存入新表中。

Create Table Dept_avg(
Sdept char(40),
Avg_ SmallInt
)
Insert Into Dept_avg(Sdept, Avg_) 
Select Sdept, Avg(Sage) From Student Group By(Sdept)
Select * From Dept_avg

结果如下：

---

修改操作又称为更新操作，其语句的一般格式为：

UPDATE<表名>
SET<列名>=<表达式>[,列名>-<表达式>]…
[WHERE<条件>];

其功能是修改指定表中满足 WHERE子句条件的元组。其中 SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

例3.73 将学生20191102062的年龄改为22岁。

Update Student
Set Sage = 22
Where Sno = '20191102062'

结果如下：

例3.74 将所有学生的年龄增加1岁。

Update Student 
Set Sage = Sage + 1

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

Update SC
Set Grade = 0
Where Sno In(
Select Sno From Student
Where Sdept = 'Computer Science'
)

结果如下

---

删除语句的一般格式为

DELETE
FROM<表名>
[WHERE<条件>];

DELETE 语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE 语句删除的是表中的数据,而不是关于表的定义。

例3.76 删除学号为20191102062的学生记录。

Delete 
From Student Where Sno = '20191102062'

发生错误，原因是该Sno是SC的外码， 所以要先删除SC相关的值

例3.77 删除所有的学生选课记录。

Delete From SC

可以发现SC表空了

例3.78 删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
(SELETE Sno FROM Student
WHERE Sdept= 'CS');

例3.79 向SC表中插入一个元组，学生号是“20191102071”，课程号是“1”，成绩为空。

INSERT INTO SC(Sno,Cno,Grade)
VALUES('20191102071','1', NULL);

例3.80 将Student表中学生号为“20191102062”的学生所属的系改为空值。

UPDATE Student SET Sdept = NULL
WHERE Sno='20191102062';

---

例3.81 判断一个属性的值是否为空值，用IS NULL 或IS NOT NULL来表示。

SELECT*
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

空值与另一个值(包括另一个空值)的算术运算的结果为空值,空值与另一个值(包括另一个空值)的比较运算的结果为UNKNOWN。有了UNKNOWN 后,传统的逻辑运算中二值(TRUE,FALSE）逻辑就扩展成了三值逻辑。
具体如下表：

在查询语句中,只有使WHERE和HAVING
子句中的选择条件为TRUE的元组才被选出作为输出结果。

例3.82 找出选修1号课程的不及格的学生。

SELECT Sno FROM SC
WHERE Grade<60 AND Cno='1';

例3.83 选出选修1号课程的不及格的学生以及缺考的学生。

SELECT Sno FROM SC
WHERE Grade<60 AND Cno='1'UNION
SELECT Sno FROM SC
WHERE Grade IS NULL AND Cno='1';

---

SQL 语言用 CREATE VIEW命令建立视图，其一般格式为

CREATE VIEW<视图名>[(<列名>[,<列名>]…)]
AS<子查询>
[WITH CHECK OPTION];

其中，子查询可以是任意的SELECT 语句，是否可以含有ORDER BY子句和 DISTINCT短语，则取决于具体系统的实现。WITH CHECK OPTION表示对视图进行UPDATE、INSERT和 DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。

例3.84 建立信息系学生的视图。

CREATE VIEW IS_Student AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='information security';

关系数据库管理系统执行CREATE VIEW 语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

例3.85 建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

CREATE VIEW IS_Student AS
SELECT Sno,Sname,Sage FROM Student
WHERE Sdept='information security'
WITH CHECK OPTION;

例3.85 建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)。

视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS SELECT Student.Sno,Sname,Grade FROM Student,sc
WHERE Sdept='IS' AND 
Student.Sno=SC.Sno AND
sC.Cno='1';

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

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade FROM IS_S1
WHERE Grade>=90;

这里的视图IS_S2就是建立在视图IS_S1之上的。

例3.88 定义一个反映学生出生年份的视图。

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage 
FROM Student;

这里视图BT_S是一个带表达式的视图。视图中的出生年份值是通过计算得到的。还可以用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图。

例3.89 将学生的学号及平均成绩定义为一个视图。

CREATE VIEW S_G(Sno,Gavg)AS
SELECT Sno,AVG(Grade) FROM SC
GROUP BY Sno;

例3.90 将Student表中所有女生记录定义为一个视图。

CREATE VIEW F_Student(F_sno,name,sex,age,dept)AS
SELECT* FROM Student WHERE Ssex='女';

这里视图F_Student是由子查询"SELECT*"建立的。F_Student视图的属性列与Student表的属性列一一对应。如果以后修改了基本表 Student 的结构，则 Student表与 F_Student视图的映像关系就会被破坏，该视图就不能正常工作了。为避免出现这类问题，最好在修改基本表之后删除由该基本表导出的视图，然后重建这个视图。将

---

该语句的格式为

DROP VIEW<视图名>[CASCADE];

视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用CASCADE 级联删除语句把该视图和由它导出的所有视图一起删除。

例3.91 删除视图BT_S和视图IS_S1:

DROP VIEW BT_S;
/*成功执行*/
DROP VIEW IS_Sl;
/*拒绝执行*/
执行此语句时由于IS_S1视图上还导出了IS_S2视图，所以该语句被拒绝执行。如果确定要删除,则使用级联删除语句:
DROP VIEW IS_S1 CASCADE;
/*删除了视图IS S1和由它导出的所有视图*/

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

SELECT Sno,Sage FROM IS_Student WHERE Sage<20;

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的
表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和
用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转
换过程称为视图消解(view resolution)。

例3.93 查询选修了1号课程的信息系学生。

SELECT IS_Student.Sno,Sname FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

结果如下：

例3.94 在SG视图（例3.89中定义的视图）中查询平均成绩在90分以上的学生学号和平均成绩

SELECT * FROM S_G
WHERE Gavg>=90;

并未对该语句报错，成功查询：

目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询（如例3.94）就不一定能做转换了，因此这类查询应该直接对基本表进行。

---

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

例3.95 将信息系学生视图IS_Student 中学号为“20191102062”的学生姓名改为“刘辰”。

UPDATE IS_Student SET Sname='刘辰'
WHERE Sno='20191102062';

例3.96 向信息系学生视图 IS_Student中插入一个新的学生记录，其中学号为“20191102070”，姓名为“赵新”,年龄为20岁。

INSERT
INTO IS_Student
VALUES('20191102074','赵新',25);

例3.97 删除信息系学生视图IS_Student中学号为“20191102070”的记录。

DELETE
FROM IS_Student
WHERE Sno='201215129';