当前位置：主页 > 查看内容

数据库作业7：SQL Server 的部分操作（集合查询、数据更新、空值

发布时间：2021-07-17 00:00| 有位朋友查看

简介：操作目录四.集合查询五.基于派生表的查询六.SELECT 语句的一般格式 3.5 数据更新一.插入数据 2. 插入子查询结果二.修改数据 1.修改某一个元组的值 2.修改多个元组的值 3.带子查询的修改语句三.删除数据 1.删除某一个元组的值 2.删除多个元组的值 3.带……

四.集合查询

多个 SELECT 语句的结果可以进行 集合操作 。集合操作主要包括 并操作 UNION 、 交操作 INTERSECT 、 差操作 EXCEPT 。

【例3.64】查询计算机科学系的学生及年龄不大于19岁的学生。

即为查询计算机科学系的全体学生和其他系年龄不大于19岁的学生。

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

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

【例3.65】查询选修了课程1 或者选修了课程2的学生。

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

查询结果：
在这里插入图片描述
用完整的SC表作为对照：

【例3.66】查询计算机科学系的学生与年龄不大于19岁的学生的交集。

即查询计算机科学系的年龄不大于19岁的学生。

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

查询结果：
在这里插入图片描述
用完整的Student表作为对照：

【例3.67】查询既选修了课程1 又选修了课程2的学生。就是查询选修课程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.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;

结果同上。

五.基于派生表的查询

子查询不仅可以出现在 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

查询结果为：
在这里插入图片描述
若去掉最后一行 and SC.Grade >=Avg_sc.avg_grade 语句，则为找出每个学生选修课程的课程号。
SQL语句为：

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

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

----------【例3.60】查询所有选修了1号课程的学生姓名，可以用如下查询完成：

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

通过 FROM 子句生成派生表时， AS 关键字可以省略，但 必须为派生关系指定一个别名，例该例题中的SC1不可省 。

故等价于：

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

查询结果为：李勇，赵阳阳

六.SELECT 语句的一般格式

SELECT 语句的一般格式：

SELECT [ALL|DISTINCT]  <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

3.5 数据更新

一.插入数据

2. 插入子查询结果

INSERT 语句格式：

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

【例3.72]】对每一个系，求学生的平均年龄，并把结果存入数据库。

首先在数据库建一个新表，其中一列存放系名，另一列存放相应的学生平均年龄。

CREATE TABLE Dept_age
(Sdept CHAR(15) 
Avg_age SMALLINT);

遇到的问题 ：我在输入这些语句后，系统一直报错，在重复输入了好几遍，比对了一次又一次后，突然福至心灵，在 CHAR (15) 与 Avg_age 之间加了一个逗号，然后就成功执行了操作。

然后对Student表按系分组求平均年龄，再把系名和平均年龄存在新表中。

INSERT
INTO  Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept;

操作结果：
在这里插入图片描述

二.修改数据

修改操作又称更新操作，其语句的一般格式为：

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

1.修改某一个元组的值

【例3.73】将学生201215121的年龄改为22岁。

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

查表得知，学号 201215121 对应的学生年龄为20岁，可以进行更改操作。
在这里插入图片描述
操作结果：年龄成功修改为22岁。

2.修改多个元组的值

【例3.74】将所有学生的年龄增加1岁。

UPDATE Student
SET Sage= Sage+1;

操作结果：所有学生的年龄加一
在这里插入图片描述

3.带子查询的修改语句

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

【例3.75】将计算机科学系全体学生的成绩置零。

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

操作结果：计算机科学系全体学生成绩置零。
在这里插入图片描述

三.删除数据

删除语句的一般格式：

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

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

注意！ ：慎用删除。

1.删除某一个元组的值

【例3.76】删除学号为201215128的学生记录。

DELETE
FROM Student
WHERE Sno= '201215128';

操作结果：
操作前：
在这里插入图片描述

操作后：
在这里插入图片描述

2.删除多个元组的值

【例3.77】删除所有的学生选课记录。

DELETE
FROM SC;

操作结果： SC表成为了空表，SC中所有元组被删除。

3.带子查询的删除语句

子查询 也可以嵌套在 DELETE 语句中，用以构造执行删除操作的条件。
【例3.78】删除计算机科学系所有学生的选课记录。

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

操作结果：所有计算机科学系的学生的选课记录被删除。

3.6 空值的处理

空值就是“不知道”或“不存在”或“无意义”的值。

一般有以下几种情况：

1）该属性应该有一个值，但目前不知道它的具体值。
2）该属性不应该有值。
3）由于某种原因不便于填写。

1.空值的产生

【例 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';

操作结果：
操作前：
在这里插入图片描述

操作后：
在这里插入图片描述

2.空值的判断

【例 3.81】从Student表中找出漏填了数据的学生信息。

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

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

3.空值的约束条件

1）有NOT NULL约束条件的不能取空值
2）加了UNIQUE限制的属性不能取空值
3）码属性不能取空值

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

【例3.82】找出选修1号课程的不及格的学生。

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

查询结果为：201215121（该学生是计算机科学系，在之前的操作中成绩被置零）

【例 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);

查询结果为：201215121，201215126

3.7 视图

1）视图是从一个或几个基本表（或视图）导出的表，是一个虚表。
2）数据库中只存放视图的定义，不存放视图对应的数据。
3）一旦基表中的数据发生变化，从视图中查询出的数据也随之改变。

一.定义视图

1.建立视图

用 CREATE VIEW 命令建立视图，其一般格式为：

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

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

组成视图的属性列名：全部省略 或 全部指定 。
全部省略 :
隐含该视图由子查询中SELECT目标列中的诸字段组成。
全部指定 （明确指定所有列名）：
1）某个目标列不是单纯的属性名，而是聚集函数或列表达式；
2）多表连接时选出了几个同名列作为视图的字段；
3）需要在视图中为某个列启用新的更合适的名字。

【例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’ 的条件。

建立在多个基本表上的视图 ：

【例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;

操作结果：
在这里插入图片描述

分组视图： 用带有 聚集函数 和 GROUP BY 子句的查询来定义视图。

【例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='女';

操作结果：
在这里插入图片描述

2.删除视图

语句的格式：

DROP  VIEW  <视图名>[CASCADE];

【例3.91】删除视图 BT_S 和 IS_S1

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

DROP VIEW BT_S;
DROP VIEW IS_S1 CASCADE;---删除了IS_1及其导出的所有子图

视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用 CASCADE 级联 删除语句 把该视图和由它导出的所有视图一起删除。

二.查询视图

视图消解法（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';

我设置的数据没有选修了1号课程的信息系学生，所以查询结果为空。

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

SELECT *
FROM   S_G
WHERE  Gavg>=90;

因我设置的数据里没有均分在90以上的学生，所以我查询了均分在88及以上的学生学号和平均成绩，查询结果为：
在这里插入图片描述
用完整的S-G视图作为对照：

例3.89定义S_G视图的子查询为：

SELECT Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

将本例中的查询语句与定义S_G视图的的子查询结合，
错误语句：

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;

也可以用如下SQL语句完成：

SELECT *
FROM  (SELECT Sno,AVG(Grade) ---子查询生成一个派生表S_G
		FROM  SC 
 		GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=90;

三.更新视图

【例3.95】将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

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

转换后的更新语句：

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

因我设置的数据信息系的学生的学号为201215124 张立，所以操作时，WHERE Sno= '201215124';

操作结果：
在这里插入图片描述

【例3.96】向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

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

转换为对基本表的更新：

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

操作结果：
在这里插入图片描述

【例3.97】删除信息系学生视图IS_Student中学号。

DELETE
FROM IS_Student
WHERE Sno= '201215129';

转换为对基本表的更新：

DELETE
FROM Student
WHERE Sno= '201215129' AND Sdept= 'IS';

限制：在关系数据库中，并不是所有的视图都是 可更新的 ，因为有些视图的更新不能唯一地有意义的转换成对相应基本表的更新。

四.视图的作用

1）简化用户的操作
2）使用户能以多种角度看待同一数据
3）对重构数据库提供了一定程度的逻辑独立性
4）对机密数据提供安全保护
5）适当利用视图可以更清晰的表达查询

总结：本次实验内容较多，但难度不大。

；原文链接：https://blog.csdn.net/weixin_46009153/article/details/115613575
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！

上一篇：mysql错题集——ERROR: No query specified 下一篇：没有了

随机推荐

微软 Windows10 Dev 预览版 21337 发布：

3月18日消息据微软博客发布，微软发布了针对 Dev 频道的 Windows Insider build...
ASP.NET Core应用错误处理之三种呈现错误

前言由于ASP.NET Core应用是一个同时处理多个请求的服务器应用，所以在处理某个...
如何实现 DevOps 工具链

一套完整启用的 DevOps 工具链可推动你的创新计划，实现快速部署并节约成本。不...
微软Windows10 21H1版本服务体验包更新与

1月25日消息外媒 Windows Latest 报道，根据此前报道，微软 Windows 10 的 21H1...
循环取值Request.QueryString的用法

当页面上的FORM以GET方式向页面发送请[/url]求数据(如数据含有不安全字符，则浏...
详解html中background-image属性的设置

对于图片，首先我们先想到是背景图片。因为我们许许多的装饰都是用背景图片来实...
基于JavaScript实现简单的轮播图

本文实例为大家分享了JavaScript实现简单的轮播图的具体代码，供大家参考，具体...
java简易计算器实现

java简易计算器实现web 一web版环境tomcat8jdk1.8STSjava ee Java Web是用Java...
JSP上传文件到指定位置实例代码

Servlet 代码：复制代码代码如下: /** 直接取上传的File */ public void doPos...
Windows10玩游戏死机？显卡驱动这秘籍你

最近有朋友向笔者反馈说，电脑玩游戏很不稳定，玩了一会儿后就黑屏甚至死机，电...

数据库作业7：SQL Server 的部分操作（集合查询、数据更新、空值

操作目录

四.集合查询

五.基于派生表的查询

六.SELECT 语句的一般格式

3.5 数据更新

一.插入数据

2. 插入子查询结果

二.修改数据

1.修改某一个元组的值

2.修改多个元组的值

3.带子查询的修改语句

三.删除数据

1.删除某一个元组的值

2.删除多个元组的值

3.带子查询的删除语句

3.6 空值的处理

1.空值的产生

2.空值的判断

3.空值的约束条件

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

3.7 视图

一.定义视图

1.建立视图

2.删除视图

二.查询视图

三.更新视图

四.视图的作用

推荐图文

随机推荐

关于我们