查询（集合查询、基于派生表的查询）、数据更新、空值处理、视图

最新推荐文章于 2022-05-15 15:26:15 发布

啊佳啊呀啊

最新推荐文章于 2022-05-15 15:26:15 发布

阅读量325

点赞数 2

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

本文链接：https://blog.csdn.net/m0_55788811/article/details/115616376

版权

数据库实验专栏收录该内容

22 篇文章 5 订阅

订阅专栏

一、查询

1、集合查询

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

注意，参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。

（1）并操作（UNION）

例：查询计算机科学系的学生及年龄不大于19岁的学生。(OR)
代码：

SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

等价于

SELECT *
FROM Student
WHERE Sdept='CS' OR Sage<=19;

查询结果：
在这里插入图片描述
注意：使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则的UNION ALL操作符。

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

SELECT Sno
FROM SC
WHERE Cno=1
UNION
SELECT Sno
FROM SC
WHERE Cno=2;

等价于

SELECT DISTINCT Sno
FROM SC
WHERE Cno=1 OR Cno=2;

查询结果：
在这里插入图片描述

（2）交操作（INTERSECT）

例：查询计算机科学系的学生与年龄不大于19岁的学生的交集。(AND)
代码：

SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

等价于

SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;

查询结果：
在这里插入图片描述
例：查询选修了课程1又选修了课程2的学生。
代码：

SELECT Sno
FROM SC
WHERE Cno=1
INTERSECT
SELECT Sno
FROM SC
WHERE Cno=2;

等价于

SELECT  x.Sno
FROM SC x,SC y
WHERE x.Cno=1 AND y.Cno=2 AND x.Sno=y.Sno;

等价于

SELECT Sno
FROM SC
WHERE Cno=1 AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno=2);

查询结果：
在这里插入图片描述

（3）差操作（EXCEPT）

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

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;

等价于

SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;

查询结果：
在这里插入图片描述

2、基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
例：找出每个学生超过他自已选修课程平均成绩的课程号
代码：

SELECT Cno
FROM SC,(SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND
SC.Grade>Avg_sc.avg_grade;

查询结果：
在这里插入图片描述
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。

例：查询所有选修了1号课程的学生姓名
代码：

SELECT Sname
FROM Student,(SELECT Sno
FROM SC
WHERE Cno=1)
AS SC1
WHERE Student.Sno=SC1.Sno;

查询结果：
在这里插入图片描述

二、数据更新

1、插入数据（插入子查询结果）

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

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

CREAT 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; --插入数据

SELECT *
FROM Dept_age;

2、修改数据（带子查询的修改语句）

子查询嵌套在UPDATE语句中，用以构造修改的条件。

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

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

3、删除数据

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

（1）删除某一个元组的值

例：删除学号为002的学生记录。
代码：

DELETE
FROM Student
WHERE Sno='002';

（2）删除多个元组的值

例：删除所有的学生选课记录。
代码：

DELETE
FROM SC;

（3）带子查询的删除语句

子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。

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

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

三、空值的处理

控制是一个很特殊的值，含有不确定性。

1、空值的产生

（1）插入空值
例：向SC 表中插入一个元组，学生号是“201215126",课程号是“1”，成绩为空。

INSERT
INTO Student(Sno,Sname,Sdept,Sage,Ssex)
VALUES('201215126',李五,'CS',19,男)；

INSERT
INTO SC(Sno,Cno,Grade)
VALUES ('201215126','1',NULL);--注意：Sno是外码

--INSERT
--INTO SC(Sno,Cno)
--VALUES ('201215126','1');

SELECT *
FROM Student,SC
WHERE Sno='201215126' AND
Student.Sno=SC.Sno;

查询结果：
在这里插入图片描述
(2)修改为空值

例：将Student表中学生号为“201215126”的学生所属的系改为空值。
代码：

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

查询结果：
在这里插入图片描述

2、空值的判断

同IS NULL 或 IS NOT NULL来表示。

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

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

查询结果：
在这里插入图片描述

3、空值的约束条件

属性定义(或者域定义)中有NOT NULL，约束条件的不能取空值，码属性不能取空值。以及主码的值不允许为空。

4、空值的算术运算、比较运算和逻辑运算

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

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

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

查询结果：
在这里插入图片描述
由此可见：在查询语句中，只有使WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

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

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

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

查询结果：
在这里插入图片描述

四、视图

视图是从一个或几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。

1、定义视图

（1）建立视图

CREATE VIEW 命令

CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION]; --表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。

组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。

下列三种情况下必须明确指定组成视图的所有列名:

(1)某个目标列不是单纯的属性名，而是聚集函数或列表达式:

(2) 多表连接时选出了几个同名列作为视图的字段;

(3)需要在视图中为某个列启用新的更合适的名字。

建立在单个表上的视图：若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。
例1：建立信息系学生的视图。
代码：

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

建立结果：
在这里插入图片描述

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

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

建立结果：
在这里插入图片描述
建立在多个基本表上的视图：
例：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）
代码：

CREATE VIEW IS_S1(Sno,Sname,Grade) --由于视图属性列中包含了Student表与SC表的同名列Sno,所以必须在视图名后面明确说明视图的各个属性列名。
AS 
SELECT Student.Sno,Sname,Grade
FROM SC,Student
WHERE SC.Cno=1 AND
Student.Sno=SC.Sno AND
Student.Sdept='IS';`

创建结果：
在这里插入图片描述
视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

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

CREATE VIEW IS_S2
AS
SELECT *
FROM IS_S1
WHERE Grade>=90;

创建结果：
在这里插入图片描述
带表达式的视图：待虚拟列

例：定义一个反映学生出生年份的视图
代码：

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

创建结果：
在这里插入图片描述
分组视图：带有聚集函数和GROUP BY 子句的查询来定义视图。

例：将学生的学号及平均成绩定义为一个视图
代码：

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

创建结果：
在这里插入图片描述
例：将Student表中所有女生记录定义为一个视图
代码：

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

创建结果：
在这里插入图片描述

（2）删除视图

语句的格式为：

DROP VIEW <视图名> [CASCADE] ;

视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。
基本表删除后，由该基本表导出的所有视图均无法使用了，但是视图的定义没有集字典中清除。删除这些视图定义需要显式地使用DROP VIEW语句。

例：删除视图BT_S和视图IS_S1:
代码：

DROP VIEW BT_S;
DROP VIEW IS_S1 CASCADE;--	其上定义了IS_S2视图

2、查询视图

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

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

SELECT *
FROM IS_Student
WHERE Sage<20;

等价于：

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

查询结果：
在这里插入图片描述
例：查询选修了1号课程的信息系学生
代码：

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

查询结果：
在这里插入图片描述
有些视图查询不可直接转换为基本表的查询。

例：在S_C视图中查询平均成绩在90分以上的学生学号和平均成绩。
代码：

SELECT *
FROM S_C
WHERE Gavg>=90; --因为WHERE子句中是不能用聚集函数作为条件表达式的，因此执行此修正后的查询将会出现语法错误。

可等价于：

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

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

查询结果：
在这里插入图片描述
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就还一定能做转换了，因此这类查询应该直接对基本表进行。

但定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接引用该视图。而派生表只是在语句执行临时定义，语句执行后该定义即被删除。

3、更新视图

更新视图是指通过视图来插入(INSERT)、删除(DELETE)和修改(UPDATE) 数据。
由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。
为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION子句。这样在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。

例：将信息系学生视图 IS_Student 中学号为“002”的学生姓名改为“刘辰”。
代码：

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

等价于：

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

查询结果：
在这里插入图片描述

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

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

可等价于：

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

查询结果：
在这里插入图片描述
在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

一般地，行列子集视图是可更新的。除行列子集视图外，有些视图理论上是可更新的，但它们的确切特征还是尚待研究的课题。还有些视图从理论上就是不可更新的。

应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已正明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。

OVER！！！

啊佳啊呀啊

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录