数据库作业9：SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

Yjing景

已于 2023-02-13 18:53:27 修改

阅读量2.2k

点赞数 1

分类专栏：数据库文章标签：数据库 sql java

于 2020-03-27 21:52:14 首次发布

本文链接：https://blog.csdn.net/weixin_45871977/article/details/105105621

版权

数据库专栏收录该内容

27 篇文章 12 订阅

订阅专栏

【例3.69】~【例3.97】
第三章总结

数据更新
注意对数据的操作和对表的操作（插入、修改、删除）的不同。
表：CREAT、ALTER、DROP
数据：INSERT、UPDATE、DELETE
数据更新—(1)插入数据
两种插入数据的方式：插入元组、插入子查询结果（可以一次插入多个元组）
插入数据—①插入元组：语句格式：INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>]… );
可以将每个属性名都列出来,属性值与之相对应；也可以不全部列出，只按照顺序列出属性值，没有的写作NULL即可。
对于外码，可以先设为NULL,确定后再进行修改。外码会受到限制，外码要么为空，要么源自于被参照关系的主码。
【例3.69】将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中。

INSERT 
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈冬','男','IS',18);
/*或者,注意按照Student表的列属性的顺序
INSERT 
INTO Student
VALUES('201215132','陈 ','男',18,'IS');*/

【例3.70】将学生张成民的信息插入到Student表中。
（INTO 子句不写属性名的情况）

INSERT 
INTO Student
VALUES('201215126','张成民','男',18,'CS');

【例3.71】插入一条选课记录（ ‘201215128’,'1 '）。

INSERT 
INTO SC
VALUES ('201215128','1',NULL);
/*或者,没有列出的会自动设为NULL
INSERT 
INTO SC(Sno,Cno)
VALUES('201215128','1')
或者,全部列出，未给定的设为NULL
INSERT 
INTO SC(Sno,Cno,Grade)
VALUES('201215128','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;

在这里插入图片描述

RDBMS在执行插入语句时会自动检查所插元组是否破坏表上已定义的完整性规则：实体完整性、参照完整性、用户自定义的完整性。
数据更新—(2)修改数据
语句格式：UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
功能：修改指定表中满足WHERE子句条件的元组；SET子句给出<表达式>的值用于取代相应的属性列；如果省略WHERE子句，表示要修改表中的所有元组。
修改数据—①修改某一个元组的值
【例3.73】将学生201215121的年龄改为22岁.

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

修改数据—②修改多个元组的值
【例3.74】将所有学生的年龄增加1岁。

UPDATE Student
SET Sage=Sage+1;

当没有WHERE子句时，表示的是对所有的学生进行修改。
另外，之前常用的自增++符号，经过测试，这里的修改子句不可以使用++，即不可以写作SET Sage++或SET ++Sage;
修改数据—③带子查询的修改语句
【例3.75】将计算机科学系全体学生的成绩置零。

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

先在Student表中找到CS系的学生学号，再在SC表中将这些学好的成绩置为0.
RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则：实体完整性、主码不允许修改、用户定义的完整性。
【拓展】

主码是唯一的，可以对其进行修改，但是主码必须是唯一的，不能重复。如果重复则无法唯一的确定出来一个元组；

如果修改外码，例如修改SC表中的Cno,会出现下面的错误。修改Cno可能会涉及到Course表，不能进行修改。

/*下面是错误的*/
UPDATE SC
SET Cno=15
WHERE Cno=2;

在这里插入图片描述
数据更新—③删除数据
语句格式：DELETE
FROM <表名>
[WHERE <条件>];
功能：删除指定表中满足WHERE子句条件的元组。
WHERE子句：指定要删除的元组；缺省表示要删除表中的全部元组，表的定义仍在字典中。
温馨提示：在删除数据时一定要谨慎，否则你会后悔的！！！
删除数据—①删除某一个元组的值
【例3.76】删除学号为201215128的学生记录。

DELETE 
FROM Student
WHERE Sno='201215128';

在这里插入图片描述
出现错误，这是由于我在SC表中使用了201215128的信息，可以先将SC表中该学生的信息删除之后，再删除该学生在Student表中的信息；如果SC表中没有使用，就可以直接删除了。

DELETE 
FROM SC
WHERE Sno='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表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。

/*该学生还没有考试成绩，取空值*/
INSERT 
INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',null);
/*或者，没有赋值的属性，其值为空
INSERT
INTO SC(Sno,Cno)
VALUES('201215126','1');*/

修改时空
【例3.80】将Student表中学生号为”201215200”的学生所属的系改为空值。

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

在这里插入图片描述
空值的处理—②空值的判断
判断一个属性的值是否为空值，用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 Cno='1' AND Grade<60;

查询结果不包括缺考的学生，因为他们的Grade值为NULL。
在这里插入图片描述
【例3.83】选出选修1号课程的不及格的学生以及缺考的学生。

SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
/*或者使用集合查询
SELECT Sno
FROM SC
WHERE Cno='1' AND Grade<60
UNION 
SELECT Sno
FROM SC
WHERE Cno='1' AND Grade IS NULL;*/

在这里插入图片描述
视图
视图的特点:(1)虚表，是从一个或几个基本表（或视图）导出的表;(2)只存放视图的定义，不存放视图对应的数据;(3)基表中的数据发生变化，从视图中查询出的数据也随之改变.
视图没有自己的数据，只是逻辑结构，把视图定义存放在数据字典中。
视图—①定义视图
(1)语句格式:CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
其中，WITH CHECK OPTION:对视图进行更新操作时自动添加子查询中的条件.
(2)组成视图的属性列名：全部省略或全部指定

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

(3)关系数据库管理系统(RDBMS)执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
【例3.84】建立信息系学生的视图。

GO
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM     Student
WHERE  Sdept= 'IS';
GO
SELECT * FROM IS_Student

在这里插入图片描述

【拓展】GO语句
在这里GO可以当做一段批处理的结束。一般创建对象的语句要求必须是批处理句首，所以要用go结束之前的批处理操作。

SELECT *
FROM Student

--GO
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM     Student
WHERE  Sdept= 'IS';
--GO
SELECT * FROM IS_Student
/*出现错误提示：'CREATE VIEW'必须是查询批次中的第一个语句*/

使用GO语句结束之前的批处理操作，进行定义视图的

SELECT *
FROM Student

GO
CREATE VIEW IS_Studentq
AS
SELECT Sno,Sname,Sage
FROM     Student
WHERE  Sdept= 'IS';
--GO
SELECT * FROM IS_Student
/*出现错误提示：关键字'SELECT'附近有语法错误*/

在定义完视图之后，如果不加GO语句直接查询该视图，会出现“关键词SELECT 附近有语法错误”。使用GO语句结束前面的定义操作，之后再进行查询操作。

SELECT *
FROM Student

GO
CREATE VIEW IS_Studentr
AS
SELECT Sno,Sname,Sage
FROM     Student
WHERE  Sdept= 'IS';
GO
SELECT * FROM IS_Student
/*正确*/

为了防止错误，如果在定义视图前后还有其他的操作，建议在定义视图的前后都加上GO语句。
【例3.85】建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

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

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件。

注意：视图插入数据时TSQL与标准SQL的区别
当没有WITH CHECK OPTION时，TSQL可以自动补上Sdept;
标准SQL中，VIEW定义中有WITH CHECK OPTION，insert 语句可以自动加条件 Sdept = IS，并最终写入表Student。
TSQL中，VIEW 定义中有 WITH CHECK OPTION，insert并不能完成标准SQL的操作，执行语句会报错；仅能完成对 Sdept的检查。
也就是说，TSQL中，INSERT必须写Sdept。当 Sdept = IS 时，正确；Sdept = CS 时，报错。

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。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 Cno='1' AND Student.Sno=SC,Sno;

在这里插入图片描述
将Student和SC表连接起来，从中取出Sno,Sname,Grade，当多个表有冲突时起名，必须明确指定所有列名。
基于视图的视图
【例3.87】建立信息系选修了1号课程且成绩在90分以上的学生的视图。

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

在这里插入图片描述
视图IS_S1是信息系统选修了1号课程的学生的视图，在此基础上建立成绩在90分以上的视图。
从视图IS_S1中选出成绩在90分以上的学生建立视图IS_S2.
带表达式的视图
【例3.88】定义一个反映学生出生年份的视图。

CREATE VIEW BT_S(Sno,Sname,Sbirt)
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='女';

在这里插入图片描述
在这里的SELECT *是由于要建立的的视图的属性列是Student表的所有属性列，需要查询出Student表中所有的信息。在视图的属性列名中可以是自定义的。
视图—②删除视图
语句格式：DROP VIEW <视图名>[CASCADE];

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

删除视图，并没有删除数据，数据并没有存在视图中，视图只是一个虚表。
【例3.91】删除视图BT_S和IS_S1.

DROP VIEW BT_S;/*成功执行*/
DROP VIEW IS_S1;/*拒绝执行*/

要删除IS_S1，需使用级联删除，同时删除IS_S2：
DROP VIEW IS_S1 CASCADE;
在SQL SERVER 中上面的语句可以执行，IS_S2没有被删除，但是却不能够再更新数据。
在这里插入图片描述
视图—③查询视图
用户角度：查询视图与查询基本表相同
RDBMS实现视图查询的方法：视图消解法

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

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

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

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

SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;

在这里插入图片描述

对视图的查询转换成对表的查询，实质上还是对表的查询。

视图消解法的局限：有些情况下，视图消解法不能生成正确的查询。
【例3.94】在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩.

SELECT *
FROM S_G
WHERE Gavg>=90;
/*S_G视图定义：
CREATE VIEW S_G (Sno,Gavg)
AS 
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;*/

视图消解后：

/*SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
错误,因为聚集函数不可以使用WHERE,只可以使用HAVING*/
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
/*正确*/

该题还可以使用临时派生表的方法完成。

SELECT *
FROM (SELECT Sno,AVG(Grade)
	  FROM SC
	  GROUP BY Sno)
	   AS S_G(Sno,Gavg)
WHERE Gavg>=90;

在这里插入图片描述
视图—③更新视图
【例3.95】将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
对视图里面的数据操作，使用UPDATE

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

转换后的语句：

UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS';

实质上是对Student表进行操作。
在这里插入图片描述
【例3.96】向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁.

INSERT 
INTO IS_Student
VALUES('201215129','赵新',20);

转换为对基本表的更新：

INSERT 
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');

运行得到，在视图中并没有显示出插入的数据，在基本表中会显示出来。
视图只是个虚拟表，不能添加数据到视图里，但可以通过视图向基表插入数据和更新基表中的数据、删除数据。
但是如果进行下面的操作，在视图中可以显示出来

/*建立一个包含系名的视图*/
CREATE VIEW IS_STU(Sno,Sname,Sage,Sdept)
AS
SELECT Sno,Sname,Sage,Sdept
FROM Student
WHERE Sdept='IS'

INSERT
INTO  IS_STU
VALUES('201215140','赵3新',20,'IS');

SELECT *
FROM IS_STU
SELECT *
FROM Student

在这里插入图片描述
【例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并不存在对应的基本表中的实际数据。
视图—④视图的作用

简化用户的操作(针对不同的使用人群建立不同的视图，使用起来更加方便)
使用户能以多种角度看待同一数据
对重构数据库提供了一定程度的逻辑独立性
对机密数据提供安全保护
适当的利用视图可以更清晰的表达查询

Yjing景

关注

1
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
数据库作业9：SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

【例3.69】~【例3.97】第三章总结数据更新注意对数据的操作和对表的操作（插入、修改、删除）的不同。表：CREAT、ALTER、DROP数据：INSERT、UPDATE、DELETE数据更新—(1)插入数据两种插入数据的方式：插入元组、插入子查询结果（可以一次插入多个元组）插入元组：语句格式：INSERT INTO <表名> [(<属性列1>[,&lt...
复制链接

扫一扫