数据库第八次 集合查询，数据更新，视图
数据库第八次 集合查询，数据更新，视图
前言：孩子有幸在更新系统之后尝到了SQL Server突然崩掉的滋味????，折腾了几天????试了N多种方法最后，在烁老板的帮助下选择了在虚拟机上重装????????????
文章目录
1.集合查询
1.基于派生表的查询
2.数据更新
1.插入数据
2.插入子查询结果
3.修改数据
4.删除数据
3.空值的处理
1.空值的产生
2.空值的判断
3.空值的算术运算
4.视图
1.定义视图
2.删除视图
3.查询视图
4.更新视图
5.视图的作
1.集合查询
集合查询操作主要包含并操作（UNION)，交操作(INTERSECT)，差操作(EXCEPT)
查询计算机科学与技术系的学生及年龄不大于19岁的学生
查询选修了课程1或者选修了课程2的学生
查询计算机科学系的学生与年龄不大于19岁的学生的交集
查询既选修了课程1又选修了课程2的学生
这里会有问题，如果将 Sno 换成 * ，会返回空表
1
原因在于，使用select *
进行交操作的时候，（Sno，Cno，Grade）会被看做一个整体，在两个表中进行交操作时没有完全相同的整体集合，所以就不会出现任何结果了。
嵌套查询方法
1
查询计算机科学系的学生与年龄不大于19岁的学生的差集
也可以普通查询
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;
1
2
3
1.基于派生表的查询
找出每个学生超过他自己选修课程平均成绩的课程号
查询所有选修了1号课程的学生姓名
2.数据更新
1.插入数据
之前的文章中已记录过具体方法，在这就不赘述了
2.插入子查询结果
对每一个系，求学生的平均年龄，并把结果存入数据库
建立新表：
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;
1
2
3
4
5
6
7
8
9
10
11
3.修改数据
将学生201215121 的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
1
2
3
将所有学生的年龄增加1岁
UPDATE Student
SET Sage=Sage+1;
1
2
将计算机科学系全体学生的成绩置零
UPDATE SC
SET Grade =0;
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
1
2
3
4
5
6
4.删除数据
删除学号为201215128的学生记录
DELETE
FROM Student
WHERE Sno='201215128';
1
2
3
删除所有学生的选课记录。
DELETE
FROM SC;
1
2
删除计算机科学系所有学生的选课记录。
DELETE
FROM SC;
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
1
2
3
4
5
6
3.空值的处理
空值就是不存在的无意义的值
1.空值的产生
向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);
或
INSERT INTO SC(Sno,Cno)
VALUES('201215126','1');
1
2
3
4
5
将Student表中学生号为”201215200”的学生所属的系改为空值。
UPDATE Student
SET Sdept=NULL
WHERE Sno='201215200';
1
2
3
2.空值的判断
从Student表中找出漏填了数据的学生信息。
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
1
2
3
3.空值的算术运算
找出选修1号课程的不及格的学生。
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
1
2
3
找出选修1号课程的不及格的学生以及缺考的学生。
SELECT Sno
FROM sc
WHERE Grade<60 AND Cno='1'
UNION
SELECT Sno
FROM sc
WHERE Grade IS NULL AND Cno='1';
1
2
3
4
5
6
7
方法二
SELECT Sno
FROM sc
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
1
2
3
4.视图
建立视图的一般格式为
CREATE VIEW<视图名>[(<列名>[,<列名>]…)]
AS<子查询>
[WITH CHECK OPTION];
1
2
3
WITH CHECK OPTION在更新操作时自动检查子查询中的条件。
1.定义视图
建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';
1
2
3
4
5
建立信息系学生的视图,并要求进行修改和插入操作时需要保证视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
1
2
3
4
5
6
带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操时，RDBMS会自动加上Sdept='IS’的条件。
建立信息系选修了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';
1
2
3
4
5
6
7
建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT SSno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
1
2
3
4
5
定义一个反映学生出生年份的视图（带虚拟列的视图）。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;
1
2
3
4
将学生的学号和平均成绩定义为一个视图。
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
From SC
GROUP BY Sno;
1
2
3
4
5
将Student表中所有女生记录定义为一个视图。
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
From Student
WHERE Ssex='女';
1
2
3
4
5
2.删除视图
删除视图BT_S和视图IS_S1。
drop view BT_S;
drop view IS_S1 cascade;
1
2
在 SQL server 上可以直接运行，不需要考虑cascade的情况。
标准SQL则需要drop view IS_S1 cascade;
3.查询视图
在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
1
2
3
视图消解：在有效性检查中，如果视图存在，则转换成等价的对基本表的查询，然后再执行修正后的查询
视图消解转换后的查询语句为
SELECT Sno,Sage
FROM IS_Student
WHERE Sdept='IS' AND Sage<20;
1
2
3
查询选修了1号课程的信息系学生。
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
1
2
3
在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。
SELECT *
FROM S_G
WHERE Gavg>=90;
1
2
3
4.更新视图
更新视图是通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。
将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';
1
2
3
转换后的语句：
UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS';
1
2
3
向信息系学生视图 IS_Student 中插入一个新的学生记录，其中学号为“201215129”，姓名为“赵新”,年龄为20岁。
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
1
2
3
转化后语句：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');
1
2
3
删除信息系学生视图IS_Student中学号为“201215129”的记录。
DELETE
FROM IS_Student
WHERE Sno='201215129';
1
2
3
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno='201215129'AND Sdept='IS';
1
2
3
5.视图的作
简化用户的操作
使用户能以多种角度看待同一数据
对重构数据库提供了一定程度的逻辑独立性
对机密数据提供安全保护
适当的利用视图可以更清晰的表达查询
————————————————
版权声明：本文为CSDN博主「.Petrichor.」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_45958258/article/details/115860923