SQL语言⭐️
目标：给定环境和语义，会写SQL查询语言
数据定义
模式
创建模式
create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|< 授权定义子句>]
定义模式实际上是定义一个命名空间，在这个空间上可以进一步定义该模式包含的数据库对象，如基本表、视图 和索引等。
删除模式
drop schema <模式名> <CASCADE|RESTRICT>
CASCADE (级联)，表示在删除模式的同时把该模式中所有的数据库对象全部删除。
RESTRICT(限制)，表示若模式中已经定义下属的数据库对象 (表、视图)时，则拒绝该删除语句的执行。
表
创建表
CREATE TABLE <表名> (<列名> <数据类型>[<列级完整性约束条件>], <列名> <数据类型>[<列级完整性约束条件>], [<表级完整性约束条件>]);
列级完整性约束条件：涉及相应属性列的完整性约束条件
表级完整性约束条件：涉及一个或多个属性列的完整性约束条件
常用完整性约束：
- 主码约束：primary key
- 唯一性约束：unique
- 非空值约束：not null
- 参照完整性约束
修改表
ALTER TABLE <表名> [ADD <新列名> <数据类型> [完整性约束]] [DROP [COLUMN] <列名>] [DROP CONSTRAINT] <完整性约束> [ALTER COLUMN <列名> <数据类型>];
- <表名 >：要修改的基本表
- ADD子句：增加新列和新的完整性约束条件
- DROP子句：删除指定的列
- DROP CONSTRAINT子句：删除完整性约束
- ALTER COLUMN子句：用于修改列名和数据类型
删除表
drop table <表名>
基本表删除：数据、表上的索引都删除
表上的视图往往仍然保留，但无法引用
删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述
索引
建立索引是加快查询速度的有效手段
建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> ( <列名>[<次序>], [<列名>[<次序>]] );
- 用<表名>指定要建索引的基本表名字
- 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
- 用<次序>指定索引值的排列次序，升序：ASC，降序： DESC。缺省值：ASC
- UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
- CLUSTER表示要建立的索引是聚簇索引
对于已含重复值的属性列不能建UNIQUE索引
对某个列建立UNIQUE索引后，插入新记录时 DBMS会自动检查新记录在该列上是否取了重复 值。这相当于增加了一个UNIQUE约束
删除索引
drop index <索引名>;
删除索引时，系统会从数据字典中删去有关该索引的描述。
数据查询
单表查询⭐️
语句格式
SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>] … FROM <表名或视图名>[， <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式>]] [ ORDER BY <列名2> [ ASC|DESC ] ]；
- SELECT子句：指定要显示的属性列
- FROM子句：指定查询对象(基本表或视图)
- WHERE子句：指定查询条件
- GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
- HAVING短语：筛选出只有满足指定条件的组
- ORDER BY子句：对查询结果表按指定列值的升序或降序排序
WHERE子句常用的查询条件
通配符
- % (百分号) 代表任意长度（长度可以为0）的字符串
例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串
- _ (下横线) 代表任意单个字符
例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串
ESCAPE 短语
当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE ‘<换码字符>’ 短语对通配符进行转义。
涉及空值的查询
使用谓词 IS NULL 或 IS NOT NULL
多重条件查询
- 用逻辑运算符AND和 OR来联结多个查询条件
- AND的优先级高于OR
- 可以用括号改变优先级
- 可用来实现多种其他谓词
- [NOT] IN
- [NOT] BETWEEN … AND …
对查询结果排序
- 使用ORDER BY子句
- 可以按一个或多个属性列排序
- 升序：ASC；降序：DESC；缺省值为升序
- 当排序列含空值时
- ASC：排序列为空值的元组最后显示
- DESC：排序列为空值的元组最先显示
使用集函数
- 计数
COUNT （[DISTINCT|ALL] * ） COUNT （[DISTINCT|ALL] <列名 > ）
- 计算总和
SUM （[DISTINCT|ALL] <列名 > ）
- 计算平均值
AVG （[DISTINCT|ALL] <列名 > ）
- 求最大值
MAX （[DISTINCT|ALL] <列名 > ）
- 求最小值
MIN （[DISTINCT|ALL] <列名 > ）
DISTINCT短语：在计算时要取消指定列中的重复值
ALL短语：不取消重复值 =
ALL为缺省值
对查询结果分组
使用GROUP BY子句分组
- 未对查询结果分组，集函数将作用于整个查询结果
- 对查询结果分组后，集函数将分别作用于每个组
- GROUP BY子句的作用对象是查询的中间结果表
- 分组方法：按指定的一列或多列值分组，值相等的为一组
- 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数
使用HAVING短语筛选最终输出结果
- 只有满足HAVING短语指定条件的组才输出
- HAVING短语与WHERE子句的区别：作用对象不同
- WHERE子句作用于基表或视图，从中选择满足条件的元组。
- HAVING短语作用于组，从中选择满足条件的组。
连接查询⭐️
同时涉及多个表的查询称为连接查询
在where子句中用来连接两个表的条件称为 连接条件 或 连接谓词
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
比较运算符：=、>、<、>=、<=、!=
广义笛卡尔积
不带连接谓词的连接
很少使用
等值连接（含自然连接）
- 连接运算符为
=的连接操作[<表名1>.]<列名1> = [<表名2>.]<列名2> - 任何子句中引用表1和表2中同名属性时，都必须加 表名前缀。引用唯一属性名时可以加也可以省略表 名前缀。
- 自然连接是等值连接的一种特殊情况，把目标列中 重复的属性列去掉。
非等值连接查询
连接运算符不是=的连接操作
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
比较运算符： > 、 < 、>= 、<= 、!=
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
自身连接查询
- 一个表与其自己进行连接
- 需要给表起别名以示区别
- 所有属性必须使用别名前缀
外连接查询
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
复合条件连接查询
WHERE子句中含多个连接条件
嵌套查询
嵌套查询概述
- 一个
SELECT-FROM-WHERE语句称为一个查询块 - 将一个查询块嵌套在另一个查询块的 WHERE子句或HAVING短语的条件中的查询称为嵌套查询
- 子查询被限制：不能使用ORDER BY子句
不相关子查询
- 子查询的查询条件不依赖于父查询
不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询
- 子查询的查询条件依赖于父查询
相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE 子句返回值为真，则取此元组放入结果表；
然后再取外层表的下一个元组；
重复这一过程，直至外层表全部检查完为止。
子查询的谓词
- 带有IN谓词的子查询
- 带有比较运算符的子查询
- 带有ANY或ALL谓词的子查询
- 带有EXISTS谓词的子查询
用集函数实现子查询通常比直接用ANY或 ALL查询效率要高，因为前者通常能够减少比较次数
带有EXISTS谓词的子查询
- EXISTS谓词
- 存在量词
- 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 若内层查询结果非空，则返回真值
- 若内层查询结果为空，则返回假值
- 由EXISTS引出的子查询，其目标列表达式通常都用 * 因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
- NOT EXISTS谓词
不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他 形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询 都能用带EXISTS谓词的子查询等价替换。
集合查询
集合操作种类
- 并操作(UNION)
- 交操作(INTERSECT)
- 差操作(EXCEPT)
SELECT语句的一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ，<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ，<表名或视图名> [别名]] … [WHERE <条件表达式>] [GROUP BY <列名1>[，<列名1’>] ... [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC] [，<列名2’> [ASC|DESC] ] … ]；
数据更新⭐️
插入数据
两种插入数据方式
- 插入单个元组
- 插入子查询结果
插入单个元组：
INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … );
- INTO子句
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组， 且属性列属性与表定义中的顺序一致
- 指定部分属性列：插入的元组在其余属性列上取空 值
- VALUES子句
- 提供的值必须与INTO子句匹配
- 值的个数
- 值的类型
插入子查询结果：
INSERT INTO <表名> [(<属性列1> [，<属性列2>… )] 子查询;
INTO子句(与插入单条元组类似)
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组
- 指定部分属性列：插入的元组在其余属性列上取空值
- 子查询
- SELECT子句目标列必须与INTO子句匹配
- 值的个数
- 值的类型
修改数据
UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>]；
功能：修改指定表中满足WHERE子句条件的元组
- 修改某一个元组的值
- 修改多个元组的值
- 带子查询的修改语句
删除数据
DELETE FROM <表名> [WHERE <条件>]；
功能： 删除指定表中满足WHERE子句条件的元组
WHERE子句
- 指定要删除的元组
- 缺省表示要修改表中的所有元组
三种删除方式
- 删除某一个元组的值
- 删除多个元组的值
- 带子查询的删除语句
视图⭐️
视图的特点
- 虚表，是从一个或几个基本表（或视图） 导出的表
- 只存放视图的定义，不会出现数据冗余
- 基表中的数据发生变化，从视图中查询 出的数据也随之改变
基于视图的操作
- 查询
- 删除
- 受限更新
- 定义基于该视图的新视图
创建视图
CREATE VIEW <视图名> [(<列名>[,<列名>]…)] AS <子查询> [WITH CHECK OPTION]；
DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
组成视图的属性列名全部省略或全部指定
- 全部省略: 由子查询中SELECT目标列中的诸字段组成
- 全部指定:
- 某个目标列是聚集函数或列表达式
- 目标列为 *
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
WITH CHECK OPTION
透过视图进行增删改操作时，不得破坏视图定义中的谓词条件 （即子查询中的条件表达式）
删除视图
DROP VIEW <视图名>；
该语句从数据字典中删除指定的视图定义
由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除
删除基表时，由该基表导出的所有视图定义都必须显式删除
查询视图
视图消解法（View Resolution）
- 进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义
- 把视图定义中的子查询与用户的查询结合起来， 转换成等价的对基本表的查询
- 执行修正后的查询
视图消解法的局限
- 有些情况下，视图消解法不能生成正确查询。 采用视图消解法的DBMS会限制这类查询。
更新视图
用户角度：更新视图与更新基本表相同
DBMS实现视图更新的方法：视图消解法（View Resolution）
指定WITH CHECK OPTION子句后
- DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新
一些视图是不可更新的，因为对这些视图的更 新不能唯一地有意义地转换成对相应基本表的 更新(对两类方法均如此)
视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
