关系数据库标准语言SQL——详解版3（最终章）

SQL详解版

别的可以不会，这些必须整明白

掌握

• 数据更新：增、删、改
• 视图的定义、删除、查询、更新

重点

• 数据更新、视图

难点

• 视图更新

数据的插入

语句格式

INSERT
INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>] … )

功能
将新的元组插入到指定表

INTO子句

• 指定要插入数据的表名及属性列
• 属性列的顺序可与表定义中的顺序不一致
• 要是没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
• 指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句

• 提供的值必须与INTO子句匹配
• 值的个数
• 值的类型

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

insert into Student(Sno,Sname,Ssex,Sdept,Sage)
values('2020015128','李某','男','IS',18);

[例3.70]将学生张成民的信息插入到Student表中。

INSERT  INTO  Student
VALUES (‘202015129’, ‘张成民’, ‘男’, 18, ‘CS‘)；--（不可以省略属性）

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

功能

• 将子查询结果插入指定表中

注意

• 子查询的结果必须包含和insert的字段列表一样多的字段，并且数据类型兼容

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

首先：建表

CREATE  TABLE  Deptage
          (Sdept  CHAR(15) ,                  /* 系名*/
           Avgage SMALLINT)；           /*学生平均年龄*/

然后：插入数据

 INSERT  INTO  Deptage(Sdept，Avgage)
     SELECT  Sdept，AVG(Sage)       /*子查询*/
     FROM  Student
     GROUP BY Sdept；

注：通过前面的学习大家应该明白，要求每一个系的平均年龄，肯定要使用group by进行分组啦。

数据的修改（学习表的操作无非就是增删改查）

语句格式

• UPDATE <表名>
  SET <列名>=<表达式>[，<列名>=<表达式>]…
  [WHERE <条件>]；

SET子句

• 指定修改方式、要修改的列、修改后取值

WHERE子句

• 指定要修改的元组，缺省表示要修改表中的所有元组

功能

• 修改指定表中满足WHERE子句条件的元组

[例3.73] 将学生202015121的年龄改为22岁。

update Student
set Sage=22
where Sno='2020015121';

[例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='CS'
);

解：首先分析此题，会发现SC表中含有grade属性但是，并不含有Sdept这个属性，所以无法限制计算机科学系，所以就涉及到了Student表，因为要修改grade,所以update SC，那么子查询就是要找到位计算机科学系的学生的Sno，通过Sno来确定SC表中成绩该置为0的各个学生。

注意

DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

实体完整性

• 主码不允许修改

用户定义的完整性

• NOT NULL约束
• UNIQUE约束
• 值域约束

数据的删除

定义

• DELETE
  FROM <表名>
  [WHERE <条件>]；

WHERE子句

• 指定要删除的元组，如果不写表示要删除表中的所有元组

功能

• 删除指定表中满足WHERE子句条件的元组

[例3.76] 删除学号为202015128的学生记录。

delete 
from Student
where Sno='2020015128';

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

delete
from SC;

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

delete
from SC
where 'CS'=
(
select Sdept
from Student
where Student.Sno=SC.Sno
);

--法二：
delete 
from SC
where Sno in
(
select Sno
from Student
where Sdept='CS'
);

空值的处理

所谓空值就是“不知道”或“不存在”或无意义的值

• 空值的产生
• 空值的判断
• 空值的约束条件
• 空值的算术运算、比较运算和逻辑运算

注：空值与其他值进行算数运算，结果为空值，进行比较运算的结果为unknown；

视图

视图的特点：

• 虚表，是从一个或几个基本表（或视图）导出的表（查询过程）
• 只存放视图的定义，不会出现数据冗余（没有真实数据，数据是取自基表的）
• 基表中的数据发生变化，从视图中查询出的数据也随之改变

基于视图的操作

• 查询
• 删除
• 受限更新
• 定义基于该视图的新视图

语句格式

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

一般情况下视图的列名是要么不写要么全部写全的。
以下情况下视图的列名必须要写出来：
①视图中的目标列并不只是一个单纯的名字，比如涉及带聚集函数和一些表达式
②涉及到多表的连接查询
③人为的想要给视图列重命名

• 子查询不允许含有ORDER BY子句和DISTINCT短语（有的DBMS允许有的不允许）
• WITH CHECK OPTION
  透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

[例3.84] 建立信息系学生的视图，包括学号、姓名、年龄。

CREATE VIEW IS_Student
AS 
SELECT  Sno, Sname, Sage
FROM    student
WHERE  Sdept= 'IS'
WITH CHECK OPTION--加上这个子句表示，将来对视图进行修改的时候必须满足上面这个where子句的条件

从单个基本表导出,只是去掉了基本表的某些行和某些列,保留了码————行列子集视图

[例3.86] 建立信息系选修了1号课程的学生视图， 包含学号、姓名、成绩。

create view is_s1(Sno,Sname,Grade)
as
select Student.Sno,Sname,Grade
from Student,SC
where Student.Sno=SC.Sno and Sdept='IS' and SC.Cno='1';

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

create view is_s2
as
select Sno,Sname,Grade
from is_s1
where Grade>=90;

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

create view s_g(Sno,Gavg)
as
select Sno,AVG(Grade)--涉及到了聚集函数表达式，列名不能省略
from SC
group by Sno;

删除视图

语句格式

• DROP VIEW <视图名> [CASCADE]；

• 该语句从数据字典中删除指定的视图定义

• 由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除

• 删除基表时，由该基表导出的所有视图定义都必须显式删除

• 如果CASCADE选项，则删除该视图时会把由它导出的视图一块删除

查询视图

从用户角度：查询视图与查询基本表相同
DBMS实现视图查询的方法

• 实体化视图
  有效性检查：检查所查询的视图是否存在
  执行视图定义，将视图临时实体化，生成临时表
  查询视图转换为查询临时表
  查询完毕删除被实体化的视图(临时表)

视图消解法

• 进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义
  把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询
  执行修正后的查询

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

select Sno,Sname
from is_student
where Sage<20;

视图消解法 转换后的查询语句为：

select Sno,Sname
from Student
where Sdept='IS' and Sage<20;

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

SELECT  *
 FROM    S_G
 WHERE  Gavg>=90；

查询转换：

SELECT   Sno，AVG(Grade)
FROM      SC
GROUP BY  Sno
HAVING   AVG(Grade)>=90；

更新视图

• 更新视图指通过视图来插入、删除和修改数据
• 用户角度：更新视图与更新基本表相同
• DBMS实现视图更新的方法
  视图实体化法
  视图消解法
• 指定WITH CHECK OPTION子句后
  DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新

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

UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= ' 201215122 '；

转换后的语句

UPDATE  student
SET  Sname= '刘辰'
WHERE  Sno= ' 201215122 '  AND  Sdept = ‘IS’；

[例3.97] 删除信息系学生视图IS_Student中学号201215129的记录。

DELETE  
FROM  IS_Student 
WHERE  Sno= ' 201215129 '；

转换后的语句

DELETE  
FROM  Student 
WHERE  Sno= ' 201215129 '  AND  Sdept = ‘IS’；

注：对视图的操作只能操作视图里面显示的数据

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)

例：视图S_G为不可更新视图。

CREATE VIEW  S_G (Sno，Gavg)
AS 
SELECT  Sno，AVG(Grade)
FROM    SC
GROUP  BY  Sno；

对于如下更新语句

                          UPDATE  S_G
                          SET          Gavg=90
                          WHERE  Sno= ‘201215121’；

无论实体化发还是消除法都无法将其转换成对于基本表SC的更新

视图的作用

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
• 适当的利用视图可以更清晰的表达查询

总结之数据操作：

• insert into values()
• delete from where
• update set <列名>= where
• select <列名> from where group by having order by