SQL Server——从入门到放弃（6）--INSERT / UPDATE / DELETE / NULL / VIEW 练习

数据操作4大内容：增、删、改、查 ，之前的练习已经完成了查询操作（SELECT），今天开始对其余三个内容的练习。

数据更新

插入数据(INSERT)
两种插入数据方式：

• 插入元组
• 插入子查询结果
  可以一次插入多个元组

插入元组语句格式：
INSERT INTO <表名>[(<属性列1> [,<属性列2>] … )]
VALUES(<常量1> [,<常量2>] … )

【例3.69】将一个新学生元组（学号：201215139，姓名：王二，性别：男，所在系：CS，年龄：18岁）插到Student表中。

insert 
into Student(Sno,Sname,Ssex,Sdept,Sage)
values('201215139','王二','男','CS','18')

本例在插入时INTO指定了属性列名，VALUES之后的插入内容与指定的次序相同即可，不要求与表格定义时的次序相同，插入时也可以不写属性名，但元素次序需要与表格要求顺序相同。

【例3.70】将学生张成民的信息插入Student表中

insert 
into Student
values('201215140','张成民','男','18','CS')

【例3.71】插入一条选课记录（‘201215139’，‘1’）

insert 
into SC (Sno,Cno)
values('201215139','1')

关系数据库管理系统将在新插入记录的Grade列上自动的赋空值，或者指明空值

insert 
into SC 
values('201215139','1',null)

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

子查询SELECT子句目标列必须与INTO子句匹配

• 值的个数
• 值的类型

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

/*第一步：建表*/
create table Dept_age
(Sdept char(15),
Avg_age  smallint
);

/*第二步：插入数据*/
insert 
into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage)
from Student
group by Sdept

注意：建表时，列属性与列属性之间有‘ ，’，书上的例子中没有，照着书上的例子写会出错。

修改数据（UPDATE）
语句格式：
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>] …
[WHERE <条件>]

功能：

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

三种修改方式：

• 修改某一个元组的值
• 修改多个元组的值
• 带子查询的修改语句

【例3.73】将学生201215121的年龄改为22岁

update Student
set Sage=22
where Sno='201215121'

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

/*修改前*/
select Sage
from Student

update Student
set Sage=Sage+1

/*修改后*/
select Sage
from Student

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

UPDATE SC
SET Grade=0
WHERE Sno IN
		(SELECT Sno
		FROM Student
		WHERE Sdept='CS'
		)

删除数据（DELETE）
语句格式：
DELETE
FROM <表名>
[WHERE <条件>]

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

WHERE子句：

• 指定要删除的元组
• 缺省表示要删除表中的全部元组，表的定义仍在字典中

三种删除方式：

• 删除某一元组的值
• 删除多个元组的值
• 带子查询的删除语句

由于各个表之后还要用，删除之后不太好恢复，此模块本人会在确保代码正确的前提下粘贴代码，不会运行，没有截图，特此声明
PS：主要是因为懒，恢复太麻烦了😫

【例3.76】删除学号为201215128的学生记录

delete 
from Student
where Sno='201215128'

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

delete 
from SC

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

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

空值的处理

空值就是“不知道”或“不存在”或“无意义”的值
一般有以下情况：

• 该属性应该有一个值，但目前不知道它的具体值
• 该属性不应该有值
• 由于某种原因不便于填写

空值是一个很特殊的值，含有不确定性，对关系运算带来特殊的问题，需要做特殊的处理

【例3.79】向SC表中插入一个元组，学生号是“201215139”，课程号是“3”，成绩为空

insert into SC(Sno,Cno,Grade)
values ('201215139','3',null)
/*该学生还没有考试成绩，取空值*/

/*等价于*/

insert into SC(Sno,Cno)
values ('201215139','3')
/*没有赋值的属性，其值为空值*/

【例3.80】将Student表中学号为“201215130”的学生所属的系改为空值

update Student
set Sdept=null
where Sno='201215130'

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示
注意：不能用‘=’
【例3.81】从Student表中找出漏填了数据的学生信息

select *
from Student
where Sname is null or Ssex is null or Sage is null
or Sdept is null

属性定义（或者域定义）中

• 有NOT NULL约束条件的不能取空
• 加了UNIQUE限制的属性不能取空值
• 码属性不能取空值

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

select Sno
from SC
where Grade<60 and Cno='1'

结果为空，说明没有不及格的学生。
查询结果不包括缺考的学生，因为他们的Grade值为NULL

【例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'

/*等价于*/

select Sno
from SC
where  Cno='1' and (Grade<60 or Grade is null)

视图

视图的特点

• 虚表，是从一个或几个基本表（或视图）导出的表
• 只存放视图的定义，不存放视图对应的数据
• 基表中的数据发生变化，从视图中查询出的数据也随之改变

定义视图

建立视图
语句格式：
CREATE VIEW
<视图名>[(<列名>[,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]

WITH CHECK OPTION
对视图进行更新操作时自动添加子查询中的条件

组成视图的属性列名：全部省略或全部指定

• 全部省略
  由子查询中SELECT目标列中的诸字段组成
• 全部指定（明确指定所有列名）
  ①某个目标列是聚集函数或列表达式
  ②多表连接时选出了几个同名列作为视图的字段
  ③需要在视图中为某个列启用新的更合适的名字

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句

在对视图查询时，按视图的定义从基本表中将数据查出

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

create view IS_Student
as
  select Sno,Sname,Sage
  from Student
  where Sdept='IS'

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

create view IS_Student
as
  select Sno,Sname,Sage
  from Student
  where Sdept='IS'
  with check option

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept='IS’的条件

若一个视图时从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图
IS_Student视图就是一个行列子集视图

基于多个表的视图

【例3.86】建立信息系选修了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号课程且成绩在80分以上的学生的视图

create view IS_S2
as
  select Sno,Sname,Grade
  from IS_S1
  where Grade>=80

带表达式的视图

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

create view BT_S(Sno,Sname,Sbirth)
as
  select Sno,Sname,2020-Sage
  from Student

分组视图

【例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='女' 

删除视图
语句格式：
DROP VIEW <视图名>[CASCADE]

• 该语句从数据字典中删除指定的视图定义
• 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
• 删除基表时，由该基表导出的所有视图定义都必须显式的使用DROP VIEW语句删除

【例3.91】删除视图BT_S和IS_S1

drop view BT_S
drop view IS_S1

理论上：drop view IS_S1语句应该会拒绝执行，要删除IS_S1，需要使用级联删除，同时删除IS_S2，因此正确语句应为drop view IS_S1 cascade
但是~~ 这是标准SQL的情况，SQL Server使用的T-SQL不是这样，T-SQL中可以正确执行drop view IS_S1，将IS_S1删除，而IS_S2不会被删除，但执行drop view IS_S1 cascade语句则会报错，因为T-SQL不支持CASCADE

查询视图

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

• 进行有效性检查
• 转换成等价的对基本表的查询
• 执行修正后的查询

【例3.92】在信息系学生的视图中找出年龄小于22岁的学生

select Sno,Sage
from IS_Student
where Sage<22

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

select Sno,Sage
from Student
where Sdept='IS' and Sage<22

视图消解法的局限：有些情况下，视图消解法不能生成正确的查询

【例3.94】在S_G视图中查询平均成绩在80分以上的学生学号和平均成绩

select *
from S_G
where Gavg>=80

更新视图

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

update IS_Student
set Sname='刘辰'
where Sno='201215125'

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

insert
into IS_Student
values('201215143','赵新',20)

此语句执行会报错
因为在IS_Student中插入了新记录，而Student表中没有记录，所以应该转化为对基本表的更新

insert
into Student(Sno,Sname,Sage,Sdept)
values('201215143','赵新',20,'IS')

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

delete
from  IS_Student
where Sno='201215129'

可以转化为对基本表的更新

delete
from  Student
where Sno='201215129'and Sdept='IS'

更新视图的限制：
一些视图是不可更新的，因为对这些视图的更新不能唯一的有意义的转换成对相应基本表的更新

例：例3.89定义的视图S_G为不可更新视图

update S_G
set Gavg=90
where Sno='201215121'

因为Gavg并不存在对应的基本表中的实际数据
更多内容解释

视图的作用

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

OK，至此，第三章的内容就全部学完了，最后做一个小总结

第三章小结

• SQL概述
• 学生—课程数据库
• 数据定义
  SQL Server——从入门到放弃（1）–CREATE / DROP / ALTER 练习
• 数据查询
  SQL Server——从入门到放弃（2）–INDEX / INSERT / SELECT 练习
  SQL Server——从入门到放弃（3）–SELECT（单表查询）
  SQL Server——从入门到放弃（4）–SELECT（连接查询、嵌套查询）
  SQL Server——从入门到放弃（5）–SELECT（嵌套查询EXISTS、集合查询、基于派生表的查询）
• 数据更新
• 空值的处理
• 视图
  SQL Server——从入门到放弃（6）–INSERT / UPDATE / DELETE / NULL / VIEW 练习 也就是本篇了

其他的也就不多说了，前面的作业博文都已经包括了，总的来说，第三章内容挺多，也都很重要，需要好好消化。

OK，结束。