数据库系统（5）：SQL数据操作
1. SQL简介
SQL是关系模型发展过程中出现的一种特殊语言，已经成为了标准的关系数据库语言。SQL是面向转换语言 ( $transform-oriented\ \ language$ ) 的例子，将输入关系转换为所需的输出关系，包括两个主要部分：DML和DDL。
SQL具有许多实现版本，每个实现称为方言 ( $dialect$ ) ，供应商在标准之外提供的特性称为扩展 ( $extensions$ )。
1.1 优点
- 满足数据库语言的思想
- 易于学习
- 结构简洁、轻便
- 存在
SQL标准 - 可以通过终端或者嵌入式访问
- 所有人都可以使用
1.2 缺点
- 混合了编程规范和嵌入式访问
- 缺乏正交性，有多种表达方式
- 语言越来越复杂和庞大
- 聚合函数中对空的处理问题
- 结果表中可以有重复元组，对表中的行和列都强加了一个顺序
2. SQL命令
SQL语言包括保留字 ( $reserved\ \ words$ ) 和用户自定义字 ( $user-defined\ \ words$ ) 。保留字是SQL语言的固定部分，有固定含义。用户自定义字由用户定义，用于表示表、列、视图和索引等数据库对象的名称。虽然标准并没有要求，但是SQL的许多实现版本要求用句子终结符来表示SQL语句的结束。SQL语句的多数组成部分是不区分大小写的，除了字符数据常量外。
2.1 数据操作
DML语句有以下几种：
- $SELECT$,
- $INSERT$,
- $UPDATE$,
- $DELETE$.
常量 ( $Literals$ ) 是指SQL语句中的不变量 ( $constants$ )。不同的数据具有不同的常量形式，所有的非数值型数据必须使用引号，而所有的非数值型数据不能使用引号。
2.2 查询
查询形式：
SELECT [DISTINCT|ALL] {*|[columnExpression [AS newName]][...]}
FROM TableName [alias][...]
[WHERE condition]
[GROUP BY coulumnList]
[HAVING condition]
[ORDER BY columnList]
处理顺序：
- $FROM$ :
指定所用的表，$alias$ 是可选的表的别名 - $WHERE$ :
过滤满足条件的行 - $GROUP\ \ BY$ :
分组 - $HAVING$ :
过滤满足条件的组 - $ORDER\ \ BY$ :
指定查询结果的顺序
2.2.1 行选择
逻辑运算符：$AND/OR/NOT$,
| 比较运算符 | 作用 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| < | 小于 |
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
在某些方言中，不等于也可以表示为 $!=$,
范围条件查找：$[NOT]\ \ BETWEEN$,
集合成员查找：$[NOT]\ \ IN$,
模式匹配查找：$[NOT]\ \ LIKE$ 。SQL有两种模式匹配符号：
- $%$ 代表零个或多个字符
- $_$ 代表任意单个字符
- $#$ 为转义字符
空条件查找：$IS\ \ [NOT]\ \ NULL$.
2.2.2 排序
$ORDER\ \ BY$ 子句包括所需排序的列标识符 ( $column\ \ identifiers$ ) 列表，用逗号分开。在之后使用 $ASC/DESC$ 指定升序和降序排列，默认为升序排列。
在 $ORDER\ \ BY$ 子句的多个关键字中，第一个关键字称为主排序关键字 ( $major\ \ sort\ \ key$ ) 。在主排序关键字非唯一时，之后的次排序关键字 ( $minor\ \ sort\ \ key$ ) 则会决定相同关键字的行的顺序。
2.2.3 聚合函数
- $COUNT$,
- $SUM$,
- $AVG$,
- $MIN$,
- $MAX$.
除了 $COUNT(*)$ 以外，每一个函数都要去掉空，再计算非空值。$COUNT(DISTINCT)$ 语句可以计算所有非重复项。
聚合函数只能用于 $SELECT$ 列表和 $HAVING$ 子句中。如果 $SELECT$ 列表包括聚合函数，却没有使用 $GROUP\ \ BY$ 分组，那么 $SELECT$ 列表的任何项都不能引用列。
2.2.4 分组
$GROUP\ \ BY$ 子句的列名又称为组列名 ( $grouping\ \ columns$ )。当使用 $GROUP\ \ BY$ 时，$SELECT$ 列表中的项必须每组都有一个单一的值。
$HAVING$ 子句设计用于与 $GROUP\ \ BY$ 子句一起使用，用于过滤分组。实际上，$HAVING$ 子句的条件运算至少要包含一个聚合函数，否则就可以将其条件判断转移到 $WHERE$ 中。
2.3 子查询
当一个 $SELECT$ 语句内部嵌套另一个 $SELECT$ 语句时，内部的 $SELECT$ 语句称为子查询 ( $subquery$ ) /嵌套查询 ( $nested\ \ query$ ) 。子查询可以用于外部 $SELECT$ 语句的 $WHERE/HAVING$ 子句中。子查询也可以用于 $INSERT$, $UPDATE$, $DELETE$ 语句中，有三种类型：
- 标量子查询 ( $Scalar\ \ subquery$ ) 返回单个值，即单行单列的表
- 行子查询 ( $Row\ \ subquery$ ) 返回一个单行的表
- 表子查询 ( $Table\ \ subquery$ ) 返回多行多列的表
2.3.1 ANY/ALL
$ANY$ 和 $ALL$ 用于产生单个列的子查询中。若子查询前缀关键字 $ALL$ ，那么仅当子查询的所有值都满足条件时才为真。若前缀关键词为 $ANY$/ $SOME$ 时，那么只要有一个值满足条件就为真。对于空，$ALL$ 返回真，$ANY$/ $SOME$ 返回假。
2.3.2 EXISTS
$EXISTS$ 用于子查询中，返回真或假。当子查询的结果表至少存在一行时，返回真。$NOT\ \ EXISTS$ 则相反。
2.4 连接
连接 ( $Join$ ) 用于组合多个表的结果。SQL连接通过配对相关行来合并表，而配对行指的是对应表的匹配列上具有相同的值。
2.4.1 简单连接/内连接
SELECT a.col1, a.col2, a.col3, b.col1, b.col2
FROM a, b
WHERE a.col3 = b.col3;
在查询条件中指定相等的两个列称为匹配列 ( $matching\ \ columns$ )。也可以改写为：
FROM a JOIN b
ON a.col = b.col;
FROM a JOIN b
USING col;
FROM a NATURAL JOIN b;
第一种方式产生表有两个相同的 $col$ 列，而后两者没有。
2.4.2 交叉连接
连接操作是更一般的两表合并，是笛卡尔乘积的子集。SQL中有特殊的笛卡尔乘积的表示形式：
FROM a CROSS JOIN b
从概念上看，使用连接的 $SELECT$ 语句的查询过程为：
- 形成 $FROM$ 中指定的笛卡尔乘积
- 执行 $WHERE$ 子句，即对笛卡尔乘积进行限制 ( $restriction$ )
- 确定 $SELECT$ 列表中每一项的值，形成查询结果表的每一行
- 如果指定了 $DISTINCT$ ，消除重复行
- 执行 $ORDER\ \ BY$ 语句
2.4.3 外连接
在内连接中，如果表中的某一行在另一个表中没有匹配的行，那么将会被删除。而外连接 ( $outer\ \ join$ ) 会保留。外连接有三种类型：左外连接、右外连接和全外连接。
FROM a LEFT JOIN b
ON a.col = b.col
FROM a RIGHT JOIN b
ON a.col = b.col
FROM a FULL JOIN b
ON a.col = b.col
2.5 合并
- $UNION$,
- $INTERSECT$,
- $EXCEPT$.
进行操作要求两个表具有并相容性。
{UNION|INTERSECT|EXCEPT} [ALL] [CORRESPONDING [BY {column1[,...]}]]
如果指定了 $CORRESPONDING\ \ BY$ ，那么集合操作就会在指定的列上进行。在一些SQL版本中并不支持 $INTERSECT/EXCEPT$ ，也有一些使用 $MINUS$ 代替 $EXCEPT$ 。
2.6 更新
2.6.1 插入
INSERT INTO TableName [(columnList)]
VALUES (dataValueList)
$columnList$ 是列名列表，如果省略，则会按照创建时的列序进行插入。
INSERT INTO TableName [(columnList)]
SELECT ...
结合 $SELECT$ 语句，$INSERT\ \ INTO$ 可以把表中行插入另一个表中。
2.6.2 更新
UPDATE TableName
SET columnName1 = dataValue1 [, columnuName2 = dataValue2...]
[WHERE searchCodition]
2.6.3 删除
DELETE FROM TableName
[WHERE searchCondition]
如果不指定 $WHERE$ ，则会删除表中的所有信息。