数据库系统(5):SQL数据操作

1. SQL简介

        SQL是关系模型发展过程中出现的一种特殊语言,已经成为了标准的关系数据库语言。SQL面向转换语言 ( $transform-oriented\ \ language$ ) 的例子,将输入关系转换为所需的输出关系,包括两个主要部分:DMLDDL
        SQL具有许多实现版本,每个实现称为方言 ( $dialect$ ) ,供应商在标准之外提供的特性称为扩展 ( $extensions$ )。

1.1 优点

  1. 满足数据库语言的思想
  2. 易于学习
  3. 结构简洁、轻便
  4. 存在SQL标准
  5. 可以通过终端或者嵌入式访问
  6. 所有人都可以使用

1.2 缺点

  1. 混合了编程规范和嵌入式访问
  2. 缺乏正交性,有多种表达方式
  3. 语言越来越复杂和庞大
  4. 聚合函数中对空的处理问题
  5. 结果表中可以有重复元组,对表中的行和列都强加了一个顺序

2. SQL命令

        SQL语言包括保留字 ( $reserved\ \ words$ ) 和用户自定义字 ( $user-defined\ \ words$ ) 。保留字是SQL语言的固定部分,有固定含义。用户自定义字由用户定义,用于表示表、列、视图和索引等数据库对象的名称。虽然标准并没有要求,但是SQL的许多实现版本要求用句子终结符来表示SQL语句的结束。SQL语句的多数组成部分是不区分大小写的,除了字符数据常量外。

2.1 数据操作

        DML语句有以下几种:

        常量 ( $Literals$ ) 是指SQL语句中的不变量 ( $constants$ )。不同的数据具有不同的常量形式,所有的非数值型数据必须使用引号,而所有的非数值型数据不能使用引号。

2.2 查询

        查询形式:

SELECT [DISTINCT|ALL] {*|[columnExpression [AS newName]][...]}
FROM TableName [alias][...]
[WHERE condition]
[GROUP BY coulumnList]
[HAVING condition]
[ORDER BY columnList]

        处理顺序:

  1. $FROM$ :
    指定所用的表,$alias$ 是可选的表的别名
  2. $WHERE$ :
    过滤满足条件的行
  3. $GROUP\ \ BY$ :
    分组
  4. $HAVING$ :
    过滤满足条件的组
  5. $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(*)$ 以外,每一个函数都要去掉空,再计算非空值。$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$ 语句中,有三种类型:

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$ 语句的查询过程为:

  1. 形成 $FROM$ 中指定的笛卡尔乘积
  2. 执行 $WHERE$ 子句,即对笛卡尔乘积进行限制 ( $restriction$ )
  3. 确定 $SELECT$ 列表中每一项的值,形成查询结果表的每一行
  4. 如果指定了 $DISTINCT$ ,消除重复行
  5. 执行 $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} [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$ ,则会删除表中的所有信息。

数据库系统(5):SQL数据操作