sql server 嵌套查询,集合查询,基于派生表的查询,数据更新,空值处理,视图

嵌套查询

带有IN谓词的子查询

[例 3.55] 查询与“刘晨”在同一个系学习的学生。

第一种方法:分步完成

SELECT  Sdept  
FROM     Student                            
WHERE  Sname= '刘晨';
SELECT   Sno, Sname, Sdept     
FROM      Student                 
WHERE   Sdept= 'CS'; 

在这里插入图片描述
第二种方法:将第一步查询嵌入到第二步查询的条件中

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept  IN
      (SELECT Sdept
       FROM Student
       WHERE Sname= '刘晨');

在这里插入图片描述
第三种方法:用自身连接完成[例 3.55]查询要求

SELECT  S1.Sno, S1.Sname,S1.Sdept
FROM    Student S1,Student S2
WHERE  S1.Sdept = S2.Sdept  AND
       S2.Sname = '刘晨';

在这里插入图片描述

[例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname                --最后在Student关系中
FROM    Student                     --取出Sno和Sname
WHERE Sno  IN
      (SELECT Sno                     --然后在SC关系中找出选
       FROM    SC                    --修了3号课程的学生学号
       WHERE  Cno IN
                 (SELECT Cno            --首先在Course关系中找出
                  FROM Course           --“信息系统”的课程号,为3号
                  WHERE Cname= '信息系统')
 );

在这里插入图片描述
也可以用自身连接实现,注意这里的Sno要指明到底是哪个,因为在Student 和SC中都有Sno

SELECT Student.Sno,Sname
FROM    Student,SC,Course
WHERE Student.Sno = SC.Sno  AND
      SC.Cno = Course.Cno AND
      Course.Cname='信息系统';

在这里插入图片描述
一、不相关子查询:子查询的查询条件不依赖于父查询

由里向外 逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

二、相关子查询:子查询的查询条件依赖于父查询,由外向里

①取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表

②再取外层表的下一个元组

③重复这一过程,直至外层表全部检查完为止

[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。

SELECT * FROM SC;
SELECT Sno,Cno
FROM    SC  x
WHERE Grade >=(SELECT AVG(Grade) 
		        FROM  SC y
                WHERE y.Sno=x.Sno);

在这里插入图片描述

带有比较运算符的子查询

当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。

在[例 3.55]中,由于一个学生只可能在一个系学习,则可以用 = 代替IN :

SELECT Sno,Sname,Sdept
FROM    Student
WHERE Sdept   =
         (SELECT Sdept
          FROM    Student
          WHERE Sname= '刘晨');

带有ANY(SOME)或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算

语义为:

> ANY	大于子查询结果中的某个值       
> ALL	大于子查询结果中的所有值
< ANY	小于子查询结果中的某个值    
< ALL	小于子查询结果中的所有值
>= ANY	大于等于子查询结果中的某个值    
>= ALL	大于等于子查询结果中的所有值

[例 3.58] 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM    Student
WHERE Sage < ANY (SELECT  Sage
                  FROM    Student
                  WHERE Sdept= 'CS')
     AND Sdept <> 'CS'         --父查询块中的条件 

在这里插入图片描述

用聚集函数实现

SELECT Sname,Sage
FROM   Student
WHERE Sage< 
           (SELECT MAX(Sage)
            FROM Student
            WHERE Sdept= 'CS ')
       AND Sdept <> 'CS';

[例 3.59] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

方法一:用ALL谓词

SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
            (SELECT Sage
             FROM Student
             WHERE Sdept= 'CS')
      AND Sdept <> 'CS'

在这里插入图片描述

方法二:用聚集函数也可以实现

SELECT Sname,Sage
FROM Student
WHERE Sage < 
            (SELECT MIN(Sage)
             FROM Student
             WHERE Sdept= 'CS')
     AND Sdept< >'CS';

带有EXISTS谓词的子查询

EXISTS谓词

带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。

若内层查询结果非空,则外层的WHERE子句返回真值

若内层查询结果为空,则外层的WHERE子句返回假值

NOT EXISTS谓词

若内层查询结果非空,则外层的WHERE子句返回假值

若内层查询结果为空,则外层的WHERE子句返回真值

[例 3.60]查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE EXISTS
      (SELECT *
       FROM SC
       WHERE Sno=Student.Sno AND Cno= '1');

在这里插入图片描述
[例 3.61] 查询没有选修1号课程的学生姓名。

SELECT Sname
FROM   Student
WHERE NOT EXISTS
     (SELECT *
      FROM SC
      WHERE Sno = Student.Sno AND Cno='1');

在这里插入图片描述

[例 3.55]查询与“刘晨”在同一个系学习的学生。用带EXISTS谓词的子查询替换IN:

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
      (SELECT *
       FROM Student S2
       WHERE S2.Sdept = S1.Sdept AND
             S2.Sname = '刘晨');

在这里插入图片描述
对比IN,使用EXISTS语句时用到了两个Student表,和自身连接较为类似。

用EXISTS / NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词(For all)

[例 3.62] 查询选修了全部课程的学生姓名。

转化一下就是,挑选学生的姓名,对于所有的课程,没有一门课程是他没有选的,实现的过程类似于程序设计中的循环嵌套,三重循环

SELECT Sname
FROM Student
WHERE NOT EXISTS
      (SELECT *
       FROM Course
       WHERE NOT EXISTS
            (SELECT *
             FROM SC
             WHERE Sno= Student.Sno
             AND Cno= Course.Cno)
	)

数据中不存在这样的学生
在这里插入图片描述
[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。

转化一下就是,挑选学生的学生号码,对于201215122选的课程来说,没有一门课程是他没有选的

SELECT * FROM SC
SELECT DISTINCT Sno
FROM SC SCX --查询结果只会出现在选课的人中,故不用Student表而用SC表
WHERE NOT EXISTS
      (SELECT *
       FROM SC SCY  --因为要知道201215122选的课,而不是粗暴的选所有的课
       WHERE SCY.Sno = '201215122'  AND
             NOT EXISTS
             (SELECT *
              FROM SC SCZ
              WHERE SCZ.Sno=SCX.Sno AND
                    SCZ.Cno=SCY.Cno));

在这里插入图片描述

集合查询

集合操作的种类

并-UNION,交-INTERSECT,差-EXCEPT

[例 3.64] 查询计算机科学系的学生及年龄不大于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';

在这里插入图片描述
[例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.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 * FROM SC;
SELECT Sno,AVG(Grade) gra
FROM SC
GROUP BY Sno
--上面是添加行
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 WHERE Cno='1') AS SC1
WHERE  Student.Sno=SC1.Sno;

在这里插入图片描述
派生表可以间接的实现多表查询

数据更新

插入数据 insert

两种插入数据方式

1、插入元组,可以一次插入多个元组 2、插入子查询结果

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

VALUES (<常量1> [,<常量2>]… );

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

在这里插入图片描述

修改数据

1 语句格式

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

2 功能

修改指定表中满足WHERE子句条件的元组

SET子句给出<表达式>的值用于取代相应的属性列

如果省略WHERE子句,表示要修改表中的所有元组

3 三种修改方式

修改某一个元组的值

修改多个元组的值

带子查询的修改语句

[例3.73] 将学生201215121的年龄改为22岁

SELECT Sno,Sage FROM Student WHERE Sno='201215121'
UPDATE  Student
SET Sage=22
WHERE  Sno='201215121'; 
SELECT Sno,Sage FROM Student WHERE Sno='201215121'

在这里插入图片描述
[例3.74] 将所有学生的年龄减小1岁。

SELECT Sage FROM Student 
UPDATE Student
SET Sage= Sage-1;
SELECT Sage FROM Student 

在这里插入图片描述
[例3.75] 将计算机科学系全体学生的成绩置零。

SELECT  Student.Sno,Grade
FROM    Student,SC
WHERE  Sdept= 'CS'AND Student.Sno=SC.Sno

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

SELECT  Student.Sno,Grade
FROM    Student,SC
WHERE  Sdept= 'CS'AND Student.Sno=SC.Sno

在这里插入图片描述

删除数据

语句格式

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

功能

删除指定表中满足WHERE子句条件的元组

WHERE子句

指定要删除的元组
缺省表示要删除表中的全部元组,表的定义仍在字典中

三种删除方式

删除某一个元组的值
删除多个元组的值
带子查询的删除语句

[例3.76] 删除学号为201215128的学生记录。这里把陈冬给删除了

SELECT * FROM Student 
DELETE
FROM Student
WHERE Sno= '201215128';
SELECT * FROM Student

在这里插入图片描述
[例3.77] 删除所有的学生选课记录。

SELECT * FROM SC 
DELETE
FROM SC;
SELECT * FROM SC

在这里插入图片描述
[例3.78] 删除计算机科学系所有学生的选课记录。

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

空值的处理

空值是一个很特殊的值,含有不确定性。对关系运算带来特殊的问题,需要做特殊的处理。

空值的产生

[例 3.79]向SC表中插入一个元组,学生号是”201215128”,课程号是”1”,成绩为空。

INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215128','1',NULL);  
--或者
--INSERT INTO SC(Sno,Cno)
--VALUES('201215128','1'); 

在这里插入图片描述
[例3.80] 将Student表中学生号为”201215121”的学生所属的系改为空值。
在这之前,再重新更新一下数据。

SELECT * FROM Student WHERE Sno='201215121'
UPDATE Student
SET Sdept = NULL
WHERE Sno='201215121';
SELECT * FROM Student WHERE Sno='201215121'

在这里插入图片描述

判断空值

判断一个属性的值是否为空值,用IS NULL或IS NOT NULL来表示。

[例 3.81] 从Student表中找出漏填了数据的学生信息

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

在这里插入图片描述

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

在这里插入图片描述

视图

定义视图

语句格式

   CREATE  VIEW 
         <视图名>  [(<列名>  [,<列名>]…)]
   AS  <子查询>
   [WITH  CHECK  OPTION];
  (对视图进行更新操作时自动添加子查询中的条件)

[例3.84] 建立信息系学生的视图。

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

在这里插入图片描述
[例3.85]建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。

CREATE VIEW IS_Student1
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 age
FROM  Student;

[例3.89] 将学生的学号及平均成绩定义为一个视图 --分组视图

CREATE  VIEW S_G1(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='女';

删除视图

语句的格式:DROP VIEW <视图名>;

CASCADE是不被允许的
在这里插入图片描述
[例3.91 ] 删除视图BT_S和IS_S1

DROP VIEW BT_S;
DROP VIEW IS_S1; 

在这里插入图片描述
虽然IS_S2是根据IS_S1建立的,与标准sql不同,但是仍然可以直接删除IS_S1

查询视图

用户角度:查询视图与查询基本表相同

RDBMS实现视图查询的方法

视图消解法(View Resolution)1 进行有效性检查 2 转换成等价的对基本表的查询 3 执行修正后的查询

[例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_G1视图中查询平均成绩在80分以上的学生学号和平均成绩

SELECT *
FROM   S_G1
WHERE  Gavg>=80;
--错误
SELECT Sno,AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=80
GROUP BY Sno;
--正确
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=80;

在这里插入图片描述
也可以用以下的语句实现

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

更新视图

[例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岁

首先是插入没有添加WITH CHECK OPTION语句的视图IS_Student之中

INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
SELECT * FROM  IS_Student
SELECT * FROM Student

在这里插入图片描述
其实是转换为对基本表的更新:

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

sql server 在这里和标准的sql是不一样的

插入到添加WITH CHECK OPTION语句的视图IS_Student1之中

DELETE FROM Student WHERE Sno='201215129'
INSERT
INTO IS_Student1
VALUES('201215129','赵新',20);
SELECT * FROM  IS_Student1
SELECT * FROM Student

在这里插入图片描述
显示是不符合要求的插入,因为更新的前提是系别必须是IS系的,也就是不能在添加一个新的人。详情可以看一下这里

更新视图的限制:一些视图是不可更新的,因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例:例3.89定义的视图S_G为不可更新视图。

UPDATE  S_G1
SET    Gavg=90
WHERE  Sno= '201215121';

因为Gavg并不存在对应的基本表中的实际数据。

视图的作用

1 简化用户的操作

2 使用户能以多种角度看待同一数据

3 对重构数据库提供了一定程度的逻辑独立性

4 对机密数据提供安全保护

5 适当的利用视图可以更清晰的表达查询

  • 2
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值