数据库原理学习(上)

笔记参考：广州大学软件工程课程

一、绪论

数据库的本质是用数据库管理系统高效支持的、使用数据结构组织的数据的集合

1.1数据库系统概述

1.1.1数据库的4个基本概念

数据 data：数据库中的基本对象，描述事物的符号记录，符号可以是数字，文字，图片，音频…

• 数据的含义称为数据的语义，（数据与其语义是不可分的）

• 数据库 database(DB)：长期存储在计算机内，有组织的、可共享的大量数据的集合

数据库的基本特征
数据按一定的数据模型组织、描述和存储
可为各种用户共享
冗余度较小
数据独立性较高
易扩展

• 数据库管理系统(DBMS)：位于用户与操作系统之间的一层数据管理软件。
• 数据库+一组用于访问、更新和管理这些数据的系统

数据库管理系统的主要功能
数据定义功能(DDL，定义数据库中的对象)
数据组织、存储和管理
数据操纵功能(DML和基本操作)
数据库的事务管理和运行管理
数据库的建立和维护功能
其他功能

数据库管理系统的主要特性
数据访问的高效和可扩展性
缩短应用开发的时间
数据独立性(物理独立性/逻辑独立性)
数据完整性和安全性
并发访问和鲁棒性(恢复)

数据库系统（DBS）
构成：数据库、数据库管理系统(及其应用开发工具)、应用数据、数据库管理员（DBA）

1.1.2数据管理技术的产生和发展

三个阶段：
人工管理阶段

• 特点
  • 数据的管理者：用户(程序员)，数据不保存
  • 数据面向的对象：某一应用程序
  • 数据的共享：不共享，冗余大
  • 数据的独立性：不独立，完全依赖程序
  • 数据的结构：无结构
  • 数据控制能力：应用程序自己控制

文件系统阶段

• 特点
  • 数据的管理者：文件系统，可长期保存
  • 数据面向的对象：某一应用程序
  • 数据的共享：共享差，冗余大
  • 数据的独立性：独立性差
  • 数据的结构：记录内有结构，整体无结构
  • 数据控制能力：应用程序自己控制

数据库系统阶段

1.1.3数据库系统的特点

用文件系统：程序员必须关注记录结构和不同文件记录之间的联系，工作量大，变成复杂，开发速度慢，缺乏并发控制
数据库系统的特点：

• 特点
  • 数据结构化
  • 数据的共享性高，冗余度低且易扩充
  • 数据独立性高，数据独立性由数据库管理系统的二级映像功能来保证
    • 物理独立性：当数据的物理存储改变了，应用数据不用改变
    • 逻辑独立性：数据的逻辑结构改变了，应用程序不用改变
  • 数据由数据库管理系统管理和控制
    • 数据的安全性保护
    • 数据的完整性检查
    • 并发控制
    • 数据库恢复

1.2数据模型

数据模型是对现实世界数据特征的抽象。——现实世界的模型
数据模型师数据库系统的核心和基础。

数据库设计的步骤：
1、需求分析
需要什么样的数据，应用程序和业务
2、概念数据库设计
使用E-R模型或类似的高层数据模型，描述数据
3、逻辑数据库设计
将概念设计转换成某个DBMS所支持的数据模型
4、结构优化
关系标准化，检查冗余和相关的异常关系结构
5、物理数据库设计
索引，集群和数据库调优
6、创建并初始化数据库&安全设计
加载初始数据，测试
识别不同的用户和他们的角色

1.2.1两类数据模型

• 概念模型 ——信息模型
  • 将现实世界抽象为信息世界
• 逻辑模型和物理模型
  • 逻辑模型主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等等。
  • 物理模型是对数据最底层的抽象。描述在系统内部的表示方法和存取方法
  • 将信息世界转换为机器世界

1.2.2概念模型

表示方法：E-R图
注意： E-R图中，实体用矩形表示，属性用椭圆表示，关系用菱形表示。

1.信息世界中的基本概念
（1）实体——类似于对象(人、老师、学生…)
（2）属性——类似于属性(张三、李四…)
（3）码

• 唯一标识实体的属性集叫做超码
• 唯一标识实体的属性集的最小子集为码
• 码肯定是超码，但是超码不一定是码
• 一个实体可以有多个码
• 一个码可以包括一到多个属性

例子：班级号，序号，学号，姓名
学号是一个码，也是一个超码
[班级号，序号]也是一个码
[学号，姓名]是超码，不是码
（4）实体型

• 实体名和属性
• 如学生（学号、姓名、性别、出生年月、专业、入学时间）注意是实体数据的描述。

（5）实体集

• 同一类型实体的集合
• 如全体学生就是一个实体集。注意是数据
  （6）联系（一对一，一对多，多对多）
• 实体内部的联系
• 实体之间的联系
  （7）域
• 属性的取值范围

1.2.3数据模型的组成要素

• 数据结构(对象以及对象之间的关系)

  • 数据结构是对系统静态特征的描述

• 数据操作(操作以及操作规则)

  • 数据操作是对系统动态特性的描述
  • 操作的类型
    • 查询
    • 更新(删除、插入、修改)

• 数据的完整性约束条件

  • 给定的数据模型中的数据及其联系所具有的制约和依存规则

三类约束条件

• 实体完整性约束条件-主码非空
  • 学生的学号不能为空
• 参照完整性约束条件-外码取参照值或为空
  • 学生的系必须是存在的系，或者是为空(没有被分配系，但是这个学生是存在的)
• 用户定义的约束条件(如：年龄的取值必须在一定的范围)

1.2.4最常用的数据模型

如何选择数据模型，考虑依据：

• 划分依据：数据的组织方式(实体数据之间的联系的表示方式)
• 格式化模型(层次模型&网状模型)
  • 数据使用和实现都要涉及数据库物理层的复杂结构
• 关系模型
  • 使用时不涉及物理结构
  • 数据结构：二维表

三种数据模型的比较：

	数据结构	处理实体间的关系	例子	数据操作	完整性约束	存储结构	缺点	优点
层次模型	根节点没有双亲节点，其他节点只有一个双亲节点	只能处理一对对，修正处理多对多，冗余节点，虚拟节点	组织结构家谱	增删改查	1.没有双亲节点不能插入子节点2.删除双亲节点的同时删除子节点	邻接法，链接法	1.现实世界很多情况不能表示2.如果一点节点有双亲节点的情况复杂，插入和删除程序复杂3.查询子节点必须经过双亲4.层次命令趋于层次化	1.数据结构简单清晰 2.查询效率高 3.提供了良好的完整性支持
网状模型	层次模型的泛化	一对多，修正多对多，引入新的联结记录	学生/选课/成绩	增删改查	1.可插入没有双亲节点2.可只删除双亲节点	链接法等	1.复杂2.不易使用3.记录间的联系是通过存储路径实现的，用户必须了解系统结构的细节，加重了编写程序的负担	1.能更直接描述现实世界 2.效率高
关系模型	实体和联系都用二维表表示(表中不能有表)	一对多，多对多	学生/选课/成绩	增删改查	1.实体完整性2.参照完整性3.用户定义的完整性	文件	查询效率往往不如格式化结构模型；查询优化，增加了开发数据库管理系统的难度	1.建立在严格的数学概念的基础上 2.概念单一 3.关系的存储路径对用户透明

1.2.5层次模型

1.2.6网状模型

1.2.7关系模型

• 关系：一个关系对应的通常是一张表
• 元组：表中的一行通常为一个元组
• 属性：表中的一列为一个属性
• 主码(码键):表中的某个属性组，可以唯一确认一个元组
• 域：属性的取值范围
• 分量：元组的一列取值
• 关系模式
  • 关系名(属性1，属性2…属性n)
  • 学生(姓名，学号，年纪，系名)

关系术语	一般表格的术语
关系名	表名
关系模式	表头(表格的描述)
关系	表
元组	一行
属性	一列
属性名	列名
属性值	列值
分量	一条记录中的一个列值
非规范关系	表中有表

数据模型的设计方法：E-R图

• 一对一：将任意主码放在另一关系中
• 一对多：将一方主码放在多方关系中
• 多对多：直接用关系表示(由两方的主码及联系的属性组成)

1.3数据库系统的结构

• 从数据库开发应用人员角度：三级模式结构
• 最终用户角度：
  • 单用户结构
  • 主从式结构
  • 分布式结构
  • 客户-服务器
  • 浏览器-应用服务器/数据库服务器多层结构

1.3.1 数据库系统模式的概念

模式：

• 数据库逻辑结构和特征的描述
• 是型的描述，不涉及具体值
• 反映的是数据的结构及其联系
• 模式是相对稳定的

实例：

• 模式的一个具体值
• 反映数据库某一时刻的状态
• 同一个模式可以有很多实例
• 实例随数据库中的数据的更新而变动

例如：在学生选课数据库模式中，包含学生记录，课程记录和学生选课记录

1.3.2 数据库系统的三级模式

1.3.3 数据库的二级映像功能和数据独立性

	别名	说明	个数限制
外模式	子模式或用户模式	数据库用户的数据视图，是与某一应用有关的数据的逻辑表示	多个
模式	逻辑模式	数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图	一个
内模式	存储模式	数据物理结构和存储方式的描述，是数据在数据库内部的表示方式	一个

二级模式映像	说明	保证了
外模式/模式	定义外模式与模式之前的对应关系(数据的局部逻辑结构与全局逻辑结构之间的对应关系)	逻辑独立性
模式/内模式	定义了数据全局逻辑结构与存储结构之间的对应关系	物理独立性

	主要功能
外模式	数据库用户的数据视图，是与某一应用有关的数据的逻辑表示
模式	需求分许；定义数据逻辑结构；定义数据之间的关系；定义数据的安全性、完整性要求
内模式	数据物理结构和存储方式的描述，是数据在数据库内部的表示方式

模式的定义(描述的是数据的全局逻辑结构)：

• 数据的逻辑结构
• 数据之间的关系
• 数据有关的安全性、完整性要求

外模式描述的是数据的局部逻辑结构

1.4数据库系统的组成

• 数据库
• 数据库管理系统
• 应用程序
• 数据库管理员

1.5小结

关系是数据，关系模式是数据定义，关系模型是设计理论
关系数据库管理系统：RDBMS
关系数据库： RDB
关系数据库系统： RDBS

例子解释：
关系：包括学生表，课程表，选课表的具体数据
关系模式：包括学生表、课程表、选课表的定义
关系模型：设计这些表定义的关系模式设计的理论
关系数据库管理系统(RDBMS)：比如采用Oracle 或Mysql
关系数据库：比如学生表，课程表，选课表的数据的集合
关系数据库系统(RDBS)：由关系数据库，数据库管理系统、应用程序和数据库管理员(DBA)组成的存储，管理，处理和维护数据的系统

模式 包括 各种数据结构，表/视图 等等
实体、 联系表现为各种 表
属性 是 表中的列

二、关系数据库

2.1 关系数据结构及其形式化定义

2.1.1关系

• 单一结构的数据结构–关系
• 逻辑结构-- 二维表(在用户的角度)

域：是相同数据类型值集合
笛卡尔积：笛卡尔积可以表示成一张表，表中的每一行对应着一个元组，表中的每一列对应着域
关系中的每一列对应着一个域，不同的列可以对应相同的域，为了加以区分，必须对每一列取名字，称为属性
n目关系必须有n个属性

候选码：
关系中的某一属性组的值能够唯一标识一个元组，而其子集不能(所有真子集都不能唯一标识元组)，那么该属性组就是候选码
一个关系可能有多个候选码:每个候选码包含1到n个属性
全码：
若所有的属性组是候选码，那么称之为全码
超码：
任意候选码的任意父集称为超码
主码：
一个关系中有多个候选码，那么选择一个称为主码
主属性：
候选码的属性称为主属性
不包含在任何候选码的属性叫做非主属性或非码属性
范式：
关系必须满足一定的规范条件，规范化后的关系称为范式

2.1.2关系模式

关系模式是型(静态)，关系是值(动态)。
关系模式是对关系的描述：

• 元组集合的结构
• 完整性约束条件

2.1.3关系数据库

所有关系的集合构成一个关系数据库

2.2.1基本的关系操作

常用的关系操作：选择，投影，连接，除，并，差，交，笛卡尔积

• 选择，投影，并，差，笛卡尔积是5种基本操作
  数据的更新：插入，删除，修改

2.2.2关系的三类完整性约束

• 实体完整性
  • 主属性非空
• 参照完整性
  • 非主属性为空或者为已定义的属性值
• 用户定义的完整性
  • 针对某一具体的数据库的约束条件，反映某一具体运用所涉及的数据必须满足的语义要求

外码：
例子说明：
选修关系中的"学号"与学生关系的主码"学号"相对应
选修关系的"课程号"与课程关系的主码“课程号”对应

• “学号”和“课程号”是选修关系的外码
• 学生关系和课程关系均为被参照关系
• 选修关系为参照关系
  

那么为什么这样要求呢：
如果参照的码作为主码，那么这两个表的主码相同，就可以合并成一张表了

2.2.3关系代数

关系代数是一种抽象的查询语言，他用对关系的运算来表达查询

关系代数运算符：

为什么要学关系代数：

• 关系代数是关系模型运算，SQL可以看为关系代数的一种常见实现方式
• 关系代数表示比SQL语句更加简洁
• 关系代数用于查询优化中的代数优化

选择
选择运算是从关系R中选取使逻辑关系式F为真的元组，是从行的角度进行的运算

投影
从R中选择若干属性的列组成新的关系

投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组(避免重复行)

连接
连接也称为θ连接
从两个关系的笛卡尔积中选取属性满足一定条件的元组

• 一般连接
  • 两个表对应的列符合某个条件的连接，保留所有符合条件的行和列
• 等值连接
  • 对应列值相等的一般连接，保留所有符合的行和列
• 自然连接(特殊的等值连接)
  • R和S具有相同的属性组B，且连接后移除重复列
  • 需要两个关系有同名的相同属性，并且自然连接的结果要移除重复列

自然连接VS等值连接
连接运算符为“=”的连接运算称为等值连接，他是从关系R与S的广义笛卡尔积中选取A,B属性值相等的那些元组
自然连接是一种特殊的等值连接，他要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

悬浮元组
两个关系R和S在做自然连接，关系R中的某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组在操作时被舍弃，这些被舍弃的元组称为悬浮元组

所谓的左外连接和右外连接其实是相对于用左边的表去匹配右边的表(左外)还是用右边的表匹配左边的表(右外)。

• 除运算

象集$Z_x$：
给定一个关系R(X,Z),X和Z为属性组
当t[X]=x时，x在R中的象集为：$Z_x$={$t[Z]|t\in R,t[X]=x$}
他表示R中的属性组X上值为x的诸元组在Z上分量的集合。

说明：拿R中的X的象集和S进行对比，如果X的象集能够包含S，那么就是符合条件的结果。注意到行和列的顺序是没有关系的。

分析：在关系R中，A可以取四个值{$a_1$,$a_2$,$a_3$,$a_4$}
$a_1$的象集为{($b_1$,$c_2$),($b_2$,$c_3$),($b_2$,$c_1$)}
$a_2$的象集为{($b_3$,$c_7$)，($b_2$,$c_3$)}
$a_3$的象集为{($b_4$,$c_6$)}
$a_4$的象集为{($b_6$,$c_6$)}
S在(B，C)上的投影为：{($b_1$,$c_2$),($b_2$,$c_1$),($b_2$,$c_3$)}
只有$a_1$的象集包含了S在(B,C)属性组上的投影，所以，R/S={$a_1$}

综合例子：以学生-课程数据库为例子

查询至少选修了1号课程和3号课程的学生号码
建立一个临时关系K：
$${\pi }_{Sno,Cno}(SC)÷K$$

另外一种写法：
${\pi }_{Sno}({\sigma }_{Cno{=}^{\prime }{1}^{\prime }}(SC))$∩${\pi }_{Sno}({\sigma }_{Cno{=}^{\prime }{3}^{\prime }}(SC))$
总结：除运算类似与all in

查询没有选修到1号课程的学生学号
${\pi }_S(SC)-{\pi }_{Sno}({\sigma }_{Cno{=}^{\prime }{1}^{\prime }}(SC))$

查询至少选修了一门其直接先行课为5号的学生姓名
$${\pi }_{Same}({\sigma }_{Cpno{=}^{\prime }{5}^{\prime }}(Course\infty {\pi }_{Sno,Sname}(Student)))$$或者：
$${\pi }_{Same}({\pi }_{Sno,Sname}({\sigma }_{Cpno{=}^{\prime }{5}^{\prime }}(Course)\infty SC)\infty {\pi }_{Sno,Sname}(Student))$$

查询选修了全部课程的学生号码和姓名
$${\pi }_{Sno}(SC)÷{\pi }_{Cno}(Course)\infty {\pi }_{Sno,Sname}(Student)$$

总结：
关系代数运算

• 关系代数运算
  • 并、差、交、笛卡尔积、投影、选择、连接、除
• 基本运算
  • 并、差、笛卡尔积、投影、选择

三、关系数据库操作语言SQL

3.1 SQL概述

SQL结构化查询语言，是关系数据库的标准语言，是一个通用的，功能极强的关系数据库语言。

3.1.2 SQL的特点

1. 集数据定义语言(DDL),数据操纵语言(DML)，数据控制语言(DCL)功能于一体
2. 可以独立完成数据库生命周期中的全部活动
3. 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行
4. 数据操作符统一
5. 高度非过程
6. 面向集合的操作方式
7. 以同一种语法结构提供多种使用方式
8. 语言简洁，易学易用

3.1.3 SQL基本概念

基本表

• 本身独立存在的表
• SQL中一个关系对应一个基本表
• 一个(或多个)基本表对应一个存储文件
• 一个表可以带若干个索引

存储结构

• 逻辑结构组成了关系数据库的内模式
• 物理结构对用户是隐蔽的

视图

• 从一个或几个基本表中导出的表
• 数据库中只存放视图的定义不存放视图对应的数据
• 视图是一个虚表
• 用户可以在视图上再定义视图

3.2 索引的建立与删除

建立索引是为了加快查询速度。关系数据库常见的索引有：
（1）顺序文件上的索引

• 顺序文件中物理记录的顺序和逻辑记录的顺序是一致的
• 适合顺序存储：可以比较相等，比较大小
  （2）B+树索引
• B+树的非叶子节点只包含导航信息，不包含实际值
• 所有的叶子节点和相连的节点使用链表相连
• 适合顺序存储，链表存储，且列值大都不重复(例如身份证，分数)
• 可以比较大小，相等
  

（3）散列(hash)索引

• 建立若干桶bucket，通过散列函数映射数值-例：求余，把整数值属性映射到10个桶。
• 适合顺序存储，链表存储
• 只适合比较相等，不适合比较大小
• 主要适合文本
  

（4）位图索引（bitmap index）

• map应该理解为从一个K值列映射为K个二进制列(独热编码)
• 适合只有几个固定值且不频繁更新的列（如性别，婚姻，国别等）
• 只适合比较相等
• 例子，n=5个人，分数有A,B,C三种，生成k=3个长度为n的二进制向量，查分=A时，直接和A对应的第一个位图向量求位与运算
  

索引的不足
虽然索引大大提高了查询速度，同时可能会显著降低更新表的速度，因为更新表时，DBMS不仅要存储数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件，一般情况这个问题不太严重。

选择什么样的索引
（1）设计列时，列的数据类型的选择
（2）已经确定列的数据类型之后，再选择对应的索引

补充：复合索引的建立
格式：create index idx1 on table1(col2，col3，col5)
注意：根据where条件建复合索引时，应考虑被索引字段的优先顺序：

• 应将经常作为限制条件的字段放在首位
• 重复值少，即高基数的列往前靠，而重复值多的列往后靠

对于复合索引，查询时，最好将条件顺序按照索引顺序，这样效率最高，例如上面的，col2表示区，col3表示市，col5表示省

3.2.1 比较不同的索引类型

1. 顺序文件上的索引(聚簇存取)

• 顺序文件各记录本身在介质上也是顺序的，他包含了直接处理和修改记录的能力
• 可对一组记录建立一个索度引项。这种索引表称为稀疏索引。即不是每一个键值都出现在索引数组中。稀疏索引一定程度上牺牲了精确查找的效率，但大大提高了查询效率。
• 既适合批量读写，也适合随机读写
  • 例子：比如总共有30000个学生，总共有300个班，每个班的学生的学号都是顺序的，那么，对于聚簇存取，也就是我不需要对每个学生都建立索引，只需要对每个班为一组建立索引，也就是只需要建立100个索引就可以了
  • 比如我需要查找20号同学的信息，但是这种索引的情况下，我需要先找到1号同学的索引再往下找才能找到20号同学的信息
• B+树索引可以用来组织适用于组织索引顺序文件的动态索引结构(B+树索引中，所有记录节点都是按键值的大小顺序存放在同一层叶子节点中)
• 聚簇索引下，数据在物理上按顺序排在数据页上。一个表只能有一个聚簇(比如学生，可以按照班级聚簇，那么这个时候就不能再按照学号进行聚簇了)，经常连接的多个表也可以按照连接属性值聚簇存放(相当于预连接)，例如Student.Sno和SC.sno，利于连接访问，但不利于单表访问

2. 非顺序文件的索引(索引存取)

• 需要稠密索引，即每个记录都要有一个索引指针。适合随机读写，不适合批量读写(磁头需要不断移动)
• 非顺序文件的B+树索引：可以比较相等，比较大小。不适合批量读写
• 哈希索引：只适合比较相等，不适合比较大小
• 位图索引：适合只有几个固定值且不频繁更新的列(例如性别，国籍)，只适合比较相等

3.3 数据查询

3.3.1 嵌套查询

1. 一个SELECT-FROM-WHERE语句称为一个查询块
2. 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

上层查询块称为外层查询或父查询
下层查询块称为内层查询或子查询
SQL语言允许多层嵌套查询

• 即一个子查询中还可以嵌套其他子查询
  子查询限制
• 不能使用ORDER BY 子句

不相关子查询：
子查询的查询条件不依赖于父查询

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

相关子查询：
子查询的查询条件依赖于父查询

• 首先取外层查询中表的第一个元组，根据他与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。
• 然后再取外层表的下一个元组
• 重复这一过程，直至外层表全部检查完为止

解释：
相关子查询：

select t.id,t.name,t.pass from student t where 80<=(select f.score from f where f.id=t.id and f.name='xxx') 

这就是1个相关子查询，可以理解为2层循环，要想执行内层的查询，需要先从外层查询到1个值出来。
执行的顺序是，父查询1个值，子查询对这个得到的值进行1轮查询，总查询次数是m*n
因为子查询需要父查询的结果才能执行，所以叫相关子查询

不相关子查询

select t.id,t.name,t.pass from t where t.id in (select f.id from  f where f.score=70) 

例子：
相关子查询：

select t.id,t.name,t.pass from student t where 80<=(select f.score from f

这就是1个不相关子查询，子查询的不需要父查询把结果传进来，所以叫不相关子查询
执行顺序是子查询先执行，得到结果后传给父查询，父查询就不用每次查询完1个值后再执行1轮子查询
由于2个查询是分开的，无关联的，所以叫不相关子查询，查询次数是m+n

1. 带有IN谓词的子查询

查询与“刘晨”在同一个Sdept的学生的Sno，Sname，Sdept
SELECT Sno ,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept FROM Student WHERE Sname='刘晨')；

#用自身连接查询完成要求
SELECT S1.Sno ,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨'；

此查询为不相关子查询
由于DBMS系统对子查询优化不如连接优化做得好，一般鼓励用连接查询代替嵌套子查询

2. 带有比较运算符的子查询

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

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

#查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄
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');

4. 带有EXISTS，NOT EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值’true’或‘‘false"

• 若内层查询结果非空，则外层的WHERE子句返回真值
• 若内层查询结果为空，则外层WHERE子句返回假值
• 注意这里是否为“空”指是否有返回结果记录行数是否为零

如果exists子查询返回记录但是记录的值为NULL，exists()返回true

例子：

#查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS(
	SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

3.3.2 集合查询

1. 并操作UNION

• 可以将多个查询结果合并起来，并且系统会自动去重。如果需要保留重复元组，需要用union all

#查询选修了课程1和课程2的学生
select Sno from SC where Cno='1'
union
select Sno from SC where Cno='2';

#不用集合查询的写法
select Sno from SC where Cno='1' or Cno='2';

2. 交操作intersect

#查询计算机系的学生与年龄不大于19岁的学生的交集
select * from SC where Sdept='CS'
intersect 
select *from SC where Sage<='19';

#实际上就是查询计算机系中学生年龄不大于19岁的学生
select *from SC where Sdept='CS' and Sage<='19';

3. 差操作except

#查询计算机系中学生与年龄不大于19岁的学生的差集
select * from SC where Sdept='CS'
except
select * from SC where Sage<='19';

#也就是查询计算机系中年龄大于19的学生
select *from SC where Sdept='CS' and Sage<='19';

3.3.3 基于派生表的查询

根据子查询的位置不同，出现在from子句中的子查询生成的临时表称为派生表。
需要注意的是，通过From子句生成派生表时，as关键字可以省略，但必须为派生关系起一个别名，对于基本表，别名是可选项。

3.4 数据更新

3.4.1 插入数据

1. 插入元组
2. 插入子查询结果：可以生成批量需要插入的数据

#对于每一个系，求其平均年龄并放入数据库中
insert into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage) from Student group by Sdept;

3.4.2 修改数据

1. 修改某一个元组的值

update Student set Sage=22 where Sno='2012888';

2. 修改多个元组的值

#将所有学生的年龄+1
update Student set Sage=Sage+1;

3. 带子查询的修改语句

update Student set Grade=0 where Sno in (
	select Sno from Studnet where Sdept='CS'
);

3.4.3 删除数据

delete 的功能是删除满足where子句条件的所有元组。注意：delete语句删除的是表中的数据，但是表的定义还在

1. 删除一个元组

delete from Studnet where Sno='201233242'；

2. 删除所有记录

delete from SC；

3. 带子查询的删除语句

delete from SC where Sno in (
	select Sno from Student where Sdept='CS'
);

3.4.4 NULL

NULL表示不确定，并不是表示空的意思
空值的约束条件

• 属性定义(或者域定义)中
  • 有not null 约束条件的不能取空值
  • 标准SQL加了UNIQUE限制的属性不能取空值，但实际上像oracle和mysql都是可以用的，加了也不会报错
  • 码属性不能取空值

#找出选修1号课程的不及格的学生
SELECT Sno FROM SC WHERE Grade<60 AND Cno=‘1’;
#注意：查询结果不包括缺考的学生，因为他们的Grade值为null

#选出选修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 Grade<60 AND (Grade<60 OR Grade IS NULL);

3.5 视图

视图的特点

• 虚表，是从一个或几个基本表(或视图)导出的表
• 只存放视图的定义，不存放视图对应的数据
• 基表中的数据发生变化，从视图中查询出的数据也随之变化

3.5.1 定义视图

语法格式：
create view <视图名> [(<列名>[,<列名>]…)] as <子查询> [with check option]；

with check option：

• 对视图进行update，insert和delete操作时要保证更新，插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)

#建立信息系学生的视图
create view IS_Student 
as
select  Sno,Sname,Sage from Student where Sdept='IS';
#如果加上with check option表示要求进行修改和插入操作时仍然保证该视图只有信息系的学生
create view IS_Student 
as
select  Sno,Sname,Sage from Student where Sdept='IS'
with check option;

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和列，但保留了主码，就称为行列子集视图

基于多个基表的视图：

#建立信息系选修了1号课程的学生的视图(包括学号，姓名，成绩)
SELECT 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号课程且成绩在90分以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno，Sname，Grade
FROM IS_S1
WHERE Grade>=90；

带表达式的视图

#定义一个反映学生出生年份的视图
CREATE VIEW BT_S(Sno，Sname，Sbirth)
AS
SELECT Sno，Sname，2014-Sage
FROM Student；

分组视图：带有聚集函数group by子句的查询来定义视图

#将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Grade)
AS 
SELECT Sno,AVG(Grade)
FROM SC 
GROUD BY Sno;

3.5.2 删除视图

语法：drop view 视图名 [cascade]
cascade：级联删除

3.5.3 查询视图

从用户的角度，查询视图与查询基本表的操作相同

3.5.4 更新视图

从用户的角度，更新视图与更新基本表的操作相同
注意：

• 允许对行列子集视图进行更新
• 对其他类型视图的更新不同系统有不同限制

3.5.5 视图的作用

• 简化用户操作
• 使用户能够以多种角度看待同一数据
• 对重构数据库提供了一定程度的逻辑独立性
• 能够对机密数据提供安全保护
• 使得可更加清晰的表达查询