数据库作业九-INSERT/UPDATE/DELETE/NULL/VIEW

插入数据-INSERT

插入元组

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

例：

INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈冬','男','IS',18)

INSERT INTO Student
VALUES ('201215126','张老三','男',18,'CS')

INSERT INTO SC(Sno,Cno)
VALUES('200215128','1')
--两种写法在此等价 属性列不写时，默认为NULL
INSERT INTO SC
VALUES('200215128','1',NULL)

插入子查询结果

INSERT INTO <表名>[(<属性列1>[,<属性列2>...])]
子查询;

要插入的表要存在，并且子查询SELECT子句的目标列和要插入的表的目标列一致(数量，类型)。

如果要向一个不存在的表，插入数据

SELECT 列名 INTO 新表 FROM 源表

备份表不存在，会新建表，表的结构完全一致，但是不包含约束，如果只想包含结构不包含数据，可以加个top 0.

例：

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

RDBMS在插入数据和修改数据时都会自动检查是不是符合完整性规则：实体完整性、参照完整性、用户自定义的完整性。

更新数据-UPDATE

普通更新

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

例：

UPDATE  Student
SET     Sage=22
WHERE   Sno='201215121'

UPDATE Student
SET    Sage=Sage+1

带子查询的更新

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

删除数据-DELETE

普通删除

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

例：

DELETE
FROM Student
WHERE Sno=201215128

不加WHERE条件的话会删除整张表中的数据

--删除所有学生的学生选课记录
DELETE
FROM SC

如果真要删除表中的所有数据，还有一种形式，并且相比于DELETE FROM表名 推荐使用这种形式

TRUNCATE  TABLE 表名

truncate table 特点
1.无法跟where条件（无法根据条件删除，只能全部删除）
2.同时自动编号会恢复到初始值
3.是同truncate删除表中的数据会比delete效率高的多
4.truncate删除数据，不会触发delete触发器

带子查询的删除语句

--删除CS专业所有学生的选课记录
DELETE
FROM   SC
WHERE  Sno  IN(
	SELECT  Sno
	FROM    Student
	WHERE   Sdept='CS')

NULL处理

例：向SC表插入一个元组，学号为201215125，课程号为1，成绩为空

INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215125','1',NULL)
--两种方式等价
INSERT INTO SC(Sno,Cno)
VALUES('201215125','1')

将Student表中学号为201215130的学生所在的系改为NULL

UPDATE Student
SET    Sdept = NULL
WHERE  Sno='201215130'

对空值的判断
使用IS NULL 或IS NOT NULL

例：
从Student表中找出漏填了数据的学生的信息

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

空值的约束条件
属性定义（或者域定义）中：
1.有NOT NULL约束条件的不能取空值
2.加了UNIQUE限制的属性不能取空值
3.码属性不能取空值

空值运算

例：
找出选修1号课的不及格的学生

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

如果Grade为NULL，则不参与运算。因为NULL与60没法比

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

SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL)

视图

视图是一张虚拟表，在操作上和数据表没什么区别，但两者的本质不同：数据表是实际存储数据的地方，然而视图并不保存任何记录，存储的是select语句（唯有sqlserver中的索引视图可以保存记录）
优点：筛选表中的行/降低数据库的复杂程度
防止未经许可的用户访问敏感数据
将一个复杂的select语句进行封装，以更方便使用

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

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

组成视图的属性列可以全部省略，也可以全部指定。

全部省略时，由子查询中SELECT的目标列组成。
全部指定：
1.某个目标列是聚集函数或列表达式（没有名字）
2.多表连接时选出几个同名字段组成视图（避免混淆）
3.需要为某个列启用新名字时（增强可读性）
基于一个表建立的视图：

建立IS系学生的视图

CREATE VIEW VW_IS_Student
AS 
SELECT  Sno,Sname,Sage
FROM    Student
WHERE   Sdept= 'IS'

建立视图后，查看视图

--有了视图后不用再写那么长的SELECT语句了
SELECT *
FROM VW_IS_Student

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

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

在视图中修改数据不会影响视图，影响源表中的数据，视图存储的也就是SELECT语句，操作视图时，也就是操作其封装的SELECT语句，SELECT结果不改变，视图结果也不改变

基于多个基本表建立的视图
建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

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

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

CREATE VIEW VW_IS_S2
AS
SELECT Sno,Sname,Grade
FROM   VW_IS_S1
WHERE  Grade>=90

定义一个反应学生出生年份的视图

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

分组视图
将学生的学号和平均成绩定义为一个视图

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

删除视图

DROP  VIEW  <视图名>[CASCADE]

如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
查询视图
对于用户，查询基本表和查询视图相同。
RDBMS采用视图消解法对视图进行查询。

进行有效性检查,转换为等价的对基本表的查询
,执行转化后的查询

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

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20
--等价转换
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20

有些情况下，视图消解法不能生成正确的查询,因为试图消解法是把条件直接放到WHERE中

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

SELECT Sno,Gavg
FROM S_G
WHERE Gavg>=90

--错误，因为根据执行顺序，是先执行WHERE，再执行GROUP BY。执行WHERE时，表中没有AVG(Grade)
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90;
GROUP BY Sno

--正确
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90

修改视图

将信息系学生视图IS_Student中学号为‘201215122’的学生姓名改为刘辰

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

--转换后
UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS'

向IS_Student中插入一条新的学生记录，（‘201215129’,‘赵新’,20）

INSERT INTO IS_Student
VALUES('201215129','赵新',20)
--转换为
INSERT INTO Student
VALUES('201215129','赵新',NULL,20,'IS')

插入之后，如果直接SELECT *FROM IS_Student,是看不到刚才插入的语句的，原因很简单，在前边建立信息系学生视图的时候也说过这个问题了，只要记住视图是对SELECT语句的封装就可以了。

删除信息系学生视图中学号为‘201215129’学生的记录

DELETE
FROM IS_Student
WHERE Sno='201215129'
--转换后
DELETE
FROM Student
WHERE Sno='201215129' AND Sdept='IS'

最后仍要提醒一下，有些视图是不能直接修改的。
就那前边S_G视图来说，它含有聚集函数AVG，如果执行以下操作

UPDATE S_G
SET Gavg=100
WHERE Sno='201215125'

进行语法分析，语法没问题。但执行时，会报错
对视图或函数 ‘S_G’ 的更新或插入失败，因其包含派生域或常量域。
对视图进行修改，其实就是对源表进行修改，而源表中没有Gavg这一列。