03-SQL

• SQL 结构化查询语言：关系数据库的标准语言

  • 综合统一

  • 命令式语言，非过程化

  • 面向集合的操作方式

SQL 与 关系数据库三级模式

• 存储文件：对应内模式

• 基本表：对应模式

  • 一个关系对应一个基本表

  • 一个表可带若干索引

• 视图：对应外模式

  • 从基本表导出的表

  • 数据库中只存放视图的定义而不存放视图对应的数据

  • 视图是一个虚表

  • 用户可以在视图上再定义视图

SQL 数据定义

层次化机制

• 一个数据库实例可以建立多个数据库

• 一个数据库可以建立多个模式

• 一个模式包含多个表、视图、索引等对象

数据定义语法

对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

• 没有修改模式：避免增加复杂性

• 注：MySQL 创建数据库时，模式和数据库是同义词，会创建同名的模式

模式

• 定义模式

    CREATE SCHEMA <schema_name> AUTHORIZATION <user_name> 
      [ CREATE TABLE <table_name> ] 
      [ CREATE VIEW <view_name> ]
      [ GRANT <privilege> ON <object_name> TO <user_name> ]

• 删除模式

  • CASCADE：删除模式下的所有对象

  • RESTRICT：不删除模式下的对象，如果有对象存在则拒绝执行，仅当没有对象存在时才执行

    DROP SCHEMA <schema_name> <CASCADE | RESTRICT>

表

• 定义基本表

  • 约束条件

    • 列约束：

      • NOT NULL：非空约束

      • UNIQUE：唯一约束

      • CHECK (<condition>)：检查约束

      • DEFAULT <default_value>：默认值约束

      • PRIMARY KEY：主键约束

    • 表约束：

      • PRIMARY KEY (<column_name>, [<column_name>])：主键约束，可以包含多个列

      • FOREIGN KEY (<column_name>) REFERENCES <table_name> (<column_name>)：外键约束

    CREATE TABLE <table_name> (
      <column_name> <data_type> [<column_constraint>],
      ...
      [<table_constraint>]
    )

• 修改基本表：

  • ADD：添加列，新增列一律为空值

  • DROP：删除列

  • ALTER：修改列

  • RENAME：重命名表

    ALTER TABLE <table_name> 
      [ ADD[COLUMN]  <column_name> <data_type> [<column_constraint>]] 
      [ ADD <table_constraint> ]
      [ DROP [COLUMN] <column_name> [CASCADE | RESTRICT] ]
      [ DROP CONSTRAINT <constraint_name> [CASCADE | RESTRICT] ]
      [ ALTER COLUMN <column_name> <data_type> ]

• 删除基本表

  • CASCADE：删除表和相关的所有依赖对象

  • RESTRICT：若该表存在依赖对象/引用，则拒绝删除

    DROP TABLE <table_name> [CASCADE | RESTRICT]

索引

• 索引：加快查询速度

• 由数据库系统自动维护

• 常见索引

  • 顺序文件索引

  • B+树索引

  • 哈希索引

  • 位图索引：bitmap

• 建立索引

  • 索引可以建立在一列/多列上

  • 次序：ASC：升序（默认），DESC：降序

  • UNIQUE：唯一索引，索引对应唯一数据记录

  • CLUSTER：聚簇索引，根据索引重新排列物理储存聚簇，提高查询效率

    CREATE [UNIQUE] [CLUSTER] INDEX <index_name> ON <table_name> (<column_name> [ASC | DESC], ...)

• 修改索引

    ALTER INDEX <index_name> RENAME TO <new_index_name>

• 删除索引

    DROP INDEX <index_name>

SQL 数据类型

• 字符串

  • CHAR(n), CHARACTER(n)：定长字符串

  • VARCHAR(n), CHARACTER VARYING(n)：变长字符串

  • NCHAR(n), NVARCHAR(n): 定长/变长字符串，Unicode 字符集

• CLOB(n), TEXT: 大文本

• BLOB(n): 二进制大对象

• 数值

  • SMALLINT: 2字节整数

  • INT, INTEGER: 4字节整数

  • BIGINT: 8字节整数

  • REAL: 单精度浮点数（取决于机器精度）

  • DOUBLE PRECISION: 双精度浮点数（取决于机器精度）

  • FLOAT(n): n位精度浮点数

  • DECIMAL(p, d), NUMERIC(p,d), DEC(p, d): 定点数，p: 位数，d: 小·数位数

• 时间

  • DATE: 日期

  • TIME: 时间

  • TIMESTAMP: 时间戳

  • INTERVAL: 时间间隔

• BOOLEAN: 布尔值

• 选取属性的原则

  • 数据的取值范围

  • 数据需要做的运算

数据字典

• 数据库管理系统的内部表

• 记录关系模式、视图、索引、完整性约束、操作权限、统计信息等定义和信息

• SQL 语句执行时更新数据字典的内容

SQL 数据查询

• SELECT 要显示的列

  • 目标列表达式可以是表达式/函数，如<column_name> + 1，LOWER(<column_name>)

  • 可以使用AS给列起别名，也可以不用AS，如SELECT <column_name> AS <alias_name>，SELECT <column_name> <alias_name>

  • DISTINCT：去重

  • ALL：保留重复值（默认）

  • *：所有列

• FROM 要查询的表/视图

• WHERE 查询条件

  • 比较运算符：=, <>, !=, <, <=, >, >=，!<, !>, NOT + 运算符

  • 范围：BETWEEN <low> AND <high>，NOT BETWEEN <low> AND <high>

  • 包含：IN (<value>, ...)，NOT IN (<value>, ...)

  • 字符匹配

    • LIKE：模糊匹配，%表示0个或多个任意字符，_表示单个字符

    • NOT LIKE

    • 指定转义符：ESCAPE <escape_char>，如LIKE 'A\%' ESCAPE '\'查询A%

  • 空值：IS NULL，IS NOT NULL

  • 逻辑运算符：AND, OR, NOT

    • 优先级：NOT > AND > OR

    • 可使用括号更改优先级

• GROUP BY 按照指定列的值分组

• HAVING 分组后的条件

• ORDER BY 排序

  • 根据多个列排序：前面的列优先级高

SELECT [ALL | DISTINCT] <column_name_expr> [ [AS] <column_alias_name>], ...
FROM <table_name|view_name>, ...| (SELECT ...) [AS <alias_name>]
WHERE <condition>
GROUP BY <column_name>, ...
HAVING <condition>
ORDER BY <column_name> [ASC | DESC]

关系运算中的投影需要去重，对应于 SQL 中的 DISTINCT

聚集函数

• COUNT(*)：统计行数

• COUNT([DISTINCT|ALL] <column_name>)：统计列数，默认为ALL

• SUM([DISTINCT|ALL] <column_name>)：求和，默认为ALL

• AVG([DISTINCT|ALL] <column_name>)：平均值，默认为ALL

• MIN([DISTINCT|ALL] <column_name>)：最小值，默认为ALL

• MAX([DISTINCT|ALL] <column_name>)：最大值，默认为ALL

• 除了COUNT(*)，其他聚集函数跳过空值

对查询结果分组

• GROUP BY：分组

• 对查询结果分组后，聚集函数将分别作用于每个组

• HAVING作用于分组后的结果，WHERE作用于分组前的所有结果

  • 例：查询平均成绩大于等于90分的学生学号和平均成绩

   SELECT SNO, AVG(SCORE) AS AVG_SCORE
   FROM SC
   GROUP BY SNO
   HAVING AVG(SCORE) >= 90

连接查询

• 连接查询：同时涉及两个以上的表的查询

连接查询求解方法

• 嵌套循环法：在表中找到第一个元组后，在第二个表中找到符合条件的元组，直到找到所有符合条件的元组，时间复杂度为$O(n^2)$

• 排序合并法：（用于等值连接）

  • 将两个表按照连接条件的属性进行排序

  • 将两个表进行合并，时间复杂度为$O(nlogn)$

• 索引链接法：

  • 对表 2 按连接字段建立索引

  • 对表 1 中的每个元组，利用索引在表 2 中查找符合条件的元组进行拼接

  • 时间复杂度为$O(nlogm)$，$m$为表 2 的记录数

• 等值连接：连接条件是等值关系

  • 例：查询每个学生及其选修课程的情况

  SELECT Student.*, SC.*
  FROM Student, SC
  WHERE Student.SNO = SC.SNO

• 自身连接：表格与自身连接，由于属性名相同，必须使用别名

  • 例：查询每一门课的间接先修课

  SELECT FIRST.Cno, SECOND.Cpno
  FROM C FIRST, C SECOND
  WHERE FIRST.Cpno = SECOND.Cno

• 外连接：保留悬浮元组，空值填充 NULL

  • 左外连接：FROM <table_name> LEFT OUT JOIN <table_name> ON <condition>

  • 右外连接：FROM <table_name> RIGHT OUT JOIN <table_name> ON <condition>

  • 这里写了<condition>，就不用WHERE了

• 多表连接：两个以上的表进行连接

嵌套查询

• 嵌套查询：在一个查询中嵌套另一个查询

SELECT <column_name>, ...
FROM <table_name>
WHERE <column_name> IN | EXISTS | ANY | ALL (
  SELECT <column_name> 
  FROM <table_name> 
  WHERE <condition>
  )

• 上层：父查询，下层：子查询

• 子查询不能使用ORDER BY

• 嵌套查询可以通过连接替代：谨慎使用嵌套查询

• 若确定内存查询返回单个值，可以使用比较运算符代替IN

• ANY：子查询返回的值中有一个符合条件即可

• ALL：子查询返回的值中所有符合条件

• 可尝试将ANY和ALL替换为聚集函数，提升性能

• EXISTS：子查询返回的结果集非空

  • EXISTS和NOT EXISTS只能用于子查询

  • EXISTS：子查询返回的结果集非空

  • NOT EXISTS：子查询返回的结果集为空

• 实现 $\forall$：使用两次 NOT EXISTS，$(\forall x) P \equiv \neg((\exists x) \neg P)$，都符合条件=不存在不符合条件的元组

  • 例：查询选修了所有课程的学生

  SELECT SNO
  FROM STUDENT S
  WHERE NOT EXISTS (
    SELECT Cno
    FROM C
    WHERE NOT EXISTS (
      SELECT Cno
      FROM SC
      WHERE SC.SNO = S.SNO AND SC.Cno = C.Cno
    )
  )

• 实现蕴含：$P \Rightarrow Q \equiv \neg P \lor Q$

  • 例：查询了至少选修了学生 201215122 选修的全部课程的学生号码

  • $p$：201215122 选修了课程 y

  • $q$：学生 x 选修了课程 y

  • $\forall y (p \Rightarrow q)$

  • $\neg \exists y (p \land \neg q)$

  SELECT DISTINCT SNO
  FROM SC S1
  WHERE NOT EXISTS ( -- 不存在有课程，在 201215122 选的同时，不存在其他人选了这门课 -> 对于每一门 201215122 选的课，筛选出来的人都选了 (描述的主语是 `SELECT` 后的结果)
    SELECT CNO
    FROM SC S2
    WHERE S2.SNO = '201215122'
    AND NOT EXISTS ( -- 不存在有 S3(S1) 选了 201215122 选的课
      SELECT CNO
      FROM SC S3
      WHERE S3.SNO = S1.SNO AND S3.CNO = S2.CNO
    )
  )

嵌套查询求解方法

• 不相关子查询

  • 子查询的结果不依赖于父查询

  • 从内到外逐层处理

• 相关子查询

  • 子查询的结果依赖于父查询

  • 逐个选取外层表的元组，处理内层查询

  • 返回真保留，否则舍弃

集合查询

• SELECT xxx op SELECT yyy

• UNION：并集，默认去重

  • UNION ALL：并集，保留重复值

• INTERSECT：交集

• EXCEPT：差集

• 参与集合操作的查询结果必须列数相同，数据类型相同

基于派生表的查询

• FROM (SELECT ...) AS <alias_name>：在FROM中创建子查询

SQL 数据插入

插入元组

INSERT INTO <table_name> [<column_name>, ...]
VALUES (<value>, ...)

• 属性列的顺序可以和表中定义的顺序不一致

• 未指定属性列时默认插入完整的元组，顺序按照表中顺序

• 未指定属性列时，未指定的列取空值

• VALUES中值的个数和类型必须和表中一致

插入查询结果

INSERT INTO <table_name> [<column_name>, ...]
SELECT <column_name>, ...
FROM <table_name> [WHERE <condition>]

• SELECT中列数和类型必须和表中一致

SQL 数据更新

UPDATE <table_name>
SET <column_name> = <value>, ...
WHERE <condition>

• SET中列数和类型必须和表中一致

• 若省略WHERE，则更新所有元组

• 关系数据库管理系统执行修改语句时，会检查修改操作是否破坏表上已定义的完整性规则

  • 实体完整性

  • 参照完整性

  • 用户定义的完整性：NOT NULL，UNIQUE，值域

SQL 数据删除

DELETE FROM <table_name>
WHERE <condition>

• 若未指定WHERE，则删除所有元组，保留表的定义

空值

• 判断：IS NULL，IS NOT NULL

产生条件

• 应该有值，但是不知道具体值

• 不应该有值

• 因为某种原因不便于填写：性别类型是BOOLEAN，但是用户是小男娘

空值的约束条件

• NOT NULL：不允许空值

• UNIQUE：不允许重复值

• 码属性不能为空

空值的计算

• NULL参与算术计算时，结果为NULL

• NULL参与比较时，结果为UNKNOWN

• 含有UNKNOWN的逻辑运算:TRUE > UNKNOWN > FALSE

  • NOT UNKNOWN：UNKNOWN

  • AND：取“小”的

  • OR：取“大”的

视图

• 视图：从一个或多个基本表（或视图）导出的表

• 只存放定义，不重复存储数据

• 查询的数据随基本表的变化而变化

• 作用

  • 封装不同表的复杂性，简化用户操作

  • 为不同用户提供看待数据的不同角度

  • 便于重构底层数据库，无需更改用户的外模式

  • 安全防护：权限设置

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

建立视图

CREATE VIEW <view_name> [<column_name>, ...]
AS <query>
[WITH CHECK OPTION]

• <query>：任意查询语句 SELECT ... FROM ... WHERE ...

• WITH CHECK OPTION：检查视图的完整性约束，视图的插入、删除、更新操作必须满足视图定义的谓词条件（WHERE条件）

• 列名

  • 全部省略：视图的列为查询所得的全部字段

  • 明确指定：

    • 某个目标列是聚集函数或列表达式​

    • 多表连接时选出了几个同名列作为视图的字段​

    • 需要在视图中为某个列启用新的更合适的名字

• 行列子集视图：从单个基本表导出，只去掉某些行列，保留主码

删除视图

DROP VIEW <view_name> [CASCADE]

• CASCADE：删除视图的同时删除该视图导出的视图

查询视图

• 用户命令：和查询表相同

• 系统实现

  1. 进行有效性检查

  2. 转化为等价的对基本表的查询

  3. 执行修正后的查询

• 部分情况下无法正常转换：GROUP BY等

更新视图

UPDATE <view_name>
SET <column_name> = <value>, ...
WHERE <condition>

• 不可更新视图：对视图的更新无法有意义地转换为对基本表的更新

• 不可更新视图上定义的视图也不可更新