SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
2021/4/14 2:25:36
本文主要是介绍SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
本节课内容数据查询、数据更新、空值处理、视图
目录
- 3.4 数据查询
- 3.4.4 集合查询
- 1.UNION并
- 2.INTERSECT 交
- 3.EXCEPT差
- 3.4.5 基于派生表的查询
- 3.4.6 SELECT语句的一般形式
- 3.5 数据更新
- 3.5.1 插入数据
- 3.5.2修改数据
- 3.5.3删除数据
- 1.删除某一个元组的值
- 2.删除多个元组的值
- 3.带子查询的删除语句
- 3.6空值的处理
- 1.空值的产生
- 2.空值的判断
- 3.空值的约数条件
- 4.空值的算术运算
- 3.7 视图
- 3.7.1 定义视图
- 1.建立视图
- 2.删除视图
- 3.7.2 查询视图
- 3.7.3 更新视图
- 3.7.4 视图的作用
3.4 数据查询
3.4.4 集合查询
集合操作的种类
并-UNION
交-INTERSECT
差-EXCEPT
注意,参加集合操作各查询结果的列数必须相同,对应项的数据类型也必须相同
1.UNION并
例 3.64查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19
使用UNION将多个查询结果合并起来时,系统自动去掉重复元组。若想保留重复元组则用UNION ALL操作符
例 3.65询选修了课程1或者选修了课程2的学生。
SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2';
2.INTERSECT 交
例3.66查询计算机科学系的学生与年龄不大于19岁的学生交集。
SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19;
实际上就是查询计算机科学系中年龄不大于19岁的学生。
用连接查询解决该问题:
SELECT * FROM Student WHERE Sdept='CS'AND Sage<=19;
例 3.67查询既选修了课程1又选修了课程2的学生
SELECT Sno FROM SC WHERE Cno='1' INTERSECT SELECT Sno FROM SC WHERE Cno='2';
或嵌套查询
SELECT Sno FROM SC WHERE Cno='1' AND Sno IN (SELECT Sno FROM SC WHERE Cno='2')
3.EXCEPT差
例 3.68 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage<=19;
实际上是查询计算机科学系中年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept='CS'AND Sage>19
3.4.5 基于派生表的查询
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,
这时子查询生成的临时派生表成为主查询的查询对象。
如例3.57找出每个学生超过他自己选修课程平均成绩的课程号,也可用如下的查询完成
SELECT Sno,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 GROUP BY Sno) AS SC1 WHERE Student.Sno=SC1.Sno
3.4.6 SELECT语句的一般形式
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ,<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] … |(<SELECT语句>)[AS]<别名> [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING<条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];
3.5 数据更新
3.5.1 插入数据
两种插入数据方式
①插入元组
之前的博客已经写过例3.69,例3.70,例3.71SQL Server 数据库实验课第三周——INDEX、INSERT、SELECT语句
②插入子查询结果(可以一次插入多个元组)
语句格式
INSERT INTO <表名> [(<属性列1> [,<属性列2>… )] 子查询;
子查询SELECT子句目标列必须与INTO子句匹配
例3.72对每一个系,求学生的平均年龄,并把结果存入数据库
①首先在数据库中建立一个新表,其中一列存放系名,另一列存放相应的学生平均年龄。
CREATE TABLE Dept_age(Sdept CHAR(15),--系名 Avg_age SMALLINT);--学生平均年龄
②对Student表按系分组求平均年龄,在把系名和平均年龄存入新表中。
INSERT INTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;
RDBMS在执行插入语句会自动检查完整性规则(实体完整性、参照完整性
、用户定义的完整性)。
3.5.2修改数据
语句格式
UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>];
修改指定表中满足WHERE子句条件的元组。SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句,表示要修改表中的所有元组。
三种修改方式
1.修改某一个元组的值
例3.73将学生201215121的年龄改为22岁
UPDATE Student SET Sage=22 WHERE Sno='201215121';
2.修改多个元组的值
例3.74将所有学生的年龄增加1岁。
UPDATE Student SET Sage=Sage+1;
3.带子查询的修改语句
例3.75将计算机科学系全体学生的成绩置零
UPDATE SC SET Grade=0 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS');
3.5.3删除数据
格式
DELETE FROM <表名> [WHERE <条件>];
省略WHERE子句表示要删除表中的全部元组,表的定义仍在字典中。
1.删除某一个元组的值
例3.76删除学号为201215128的学生记录。
DELETE FROM Student WHERE Sno='201215128';
2.删除多个元组的值
例3.77删除所有的学生选课记录。
DELETE FROM SC
3.带子查询的删除语句
子查询同样也可以嵌套在DELECT语句中,用以构造执行操作
例3.76删除学号为201215128的学生记录。
DELETE FROM SC WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS');
3.6空值的处理
空值就是**“不知道”或“不存在”或“无意义”**的值。
SQL语言中允许某些元组的某些属性在一定情况下取空值,一般有以下几种情况:
①该属性应该有一个值,但目前不知道它的具体值。
②该属性不应该有值。如缺考学生成绩为空。
③由于某种原因不便于填写。如一个人电话号码不想让大家知道。
1.空值的产生
例 3.79向SC表中插入一个元组,学生号是”201215128”,课程号是”1”,成绩为空。
INSERT INTO SC VALUES ('201215128',1,NULL);
例3.80将Student表中学生号为”201215128”的学生所属的系改为空值。
UPDATE Student SET Sdept=NULL WHERE Sno='201215128';
2.空值的判断
判断一个属性的值是否为空值,用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;
3.空值的约数条件
属性定义(或者域定义)中有
①NOT NULL约束条件的不能取空值,
②加了UNIQUE限制的属性不能取空值
③码属性不能取空值
4.空值的算术运算
例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)
3.7 视图
视图的特点
①虚表,是从一个或几个基本表(或视图)导出的表
②只存放视图的定义,不存放视图对应的数据
③基表中的数据发生变化,从视图中查询出的数据也随之改变
3.7.1 定义视图
1.建立视图
格式
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子句,对该视图进行插入、修改和删除操作时,RDBMS会自动加上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号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade>=90;
视图中的数据并不实际存储,所以定义视图时可以根据应用需要设置一些派生属性列。带虚拟列的视图也称带表达式的视图
例3.88定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2014-Sage FROM Student; --SELECT * FROM BT_S
例3.89将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno; --SELECT * FROM S_G
例3.90将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_sno,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex='女'; --SELECT * FROM F_Student
该视图F_Student是由SELECT 建立的,F_Student视图的属性列与Student表的属性列一一对应。若以后修改了基本表Student的结构,则Student表与F_Student视图映像关系就会破坏*。故工作中若修改基本表之后会删除由其导出的视图。
2.删除视图
格式:
DROP VIEW <视图名>[CASCADE];
该语句从数据字典中删除指定的视图定义
如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除
删除基表时,由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
例3.91删除视图BT_S和IS_S1
DROP VIEW BT_S; --成功执行 DROP VIEW IS_S1; --拒绝执行
要删除CS_S1,需使用级联删除CASCADE,同时删除CS_S2
DROP VIEW CS_S1 CASCADE;
不过在SQL Server中是可以先单独删除CS_S1的
3.7.2 查询视图
用户角度:查询视图与查询基本表相同
RDBMS实现视图查询的方法:视图消解法(View Resolution)
①进行有效性检查
②转换成等价的对基本表的查询
③执行修正后的查询
例3.92在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage FROM IS_Student WHERE Sage<=20
视图消解转换后的查询语句为:
SELECT Sno,Sage FROM Student WHERE Sdept='IS'AND Sage<=20
例3.93查询选修了1号课程的学生
SELECT IS_Student.Sno,Sname FROM IS_Student,SC WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
视图消解法的局限
有些情况下,视图消解法不能生成正确的查询。
例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 --WHERE子句是不能用聚集函数作表达式的 GROUP BY Sno;
正确:
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;
目前多数关系数据库系统对行列子图均能进行正确转换,但对非行列子集视图的查询就不一定做转换了。
例3.94可以用如下SQL语句完成
SELECT * FROM (SELECT Sno,AVG(Grade) --子查询生成一个派生表 FROM SC GROUP BY Sno) AS S_G(Sno,Gavg) WHERE Gavg>=90;
3.7.3 更新视图
更新视图是指插入、删除、修改数据。
对视图的更新最重要转换为对基本表的更新。为防止用户对不属于视图范围内的基本表数据进行操作,可在定义视图时加上WITH CHENCK OPYION子句。
例3.95将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student SET Sname='刘辰' WHERE Sno='201215122'
转换后语句
UPDATE Student SET Sname='刘辰' WHERE Sno='201215122'AND Sdept='IS'
例3.96向信息系学生视图IS_Student中插入一个新的学生记录,其中学号为”201215129”,姓名为”赵新”,年龄为20岁
IS_Student不能
INSERT INTO IS_Student VALUES('201215129','赵新',20);
转换为对基本表的更新:
INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES('201215129','赵新',20,'IS');
报错原因
SQL SERVER 之 with check option 功能
例3.97删除信息系学生视图IS_Student中学号为”201215129”的记录
DELETE FROM IS_Student WHERE Sno= ' 201215129 ';
转换为对基本表的更新:
DELETE FROM Student WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
3.7.4 视图的作用
①简化用户的操作
②使用户能以多种角度看待同一数据
③对重构数据库提供了一定程度的逻辑独立性
④对机密数据提供安全保护
⑤适当的利用视图可以更清晰的表达查询
这篇关于SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解,规避错误)