SQL语言

SQL

数据库和SQL

数据库是什么

  1. 数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合（Database，DB）。
  • 数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按照一定的数据模型组织、描述和储存，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享。
  1. 数据（Data）
  • 数据是数据库中存储的基本对象
  • 描述事物的符号记录称为数据
  • 数据的含义称为数据的语义，数据与其语义是不可分的
  1. 用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

  • 数据库管理系统和操作系统一样是计算机的基础软件

  • 主要功能

    • 数据定义功能
    • 数据组织、存储和管理
    • 数据操作功能
    • 数据库的事务管理和运行管理
    • 数据库的建立和维护功能
    • 其他功能

    DBMS的种类

    • 层次数据库（Hierarchical Database，HDB）
    • 关系数据库（Relational Database，RDB）
    • 面向对象数据库（Object Oriented Database，OODB）
    • XML数据库（XML Database，XMLDB）
    • 键值存储系统（Key-Value Store，KVS）
  1. 数据库系统（DataBase System，DBS）
  • 数据库系统是由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DataBase Administrator，DBA）组成的存储、管理、处理和维护数据的系统

202412101630827

数据库的结构

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

  1. 人工管理阶段

    • 数据不保存
    • 应用程序管理数据
    • 数据不共享
    • 数据不具有独立性

  2. 文件系统阶段

    • 数据共享性差，冗余度大
    • 数据独立性差

  3. 数据库系统阶段

    • 数据结构化
    • 数据的共享性高、冗余度低且易扩充
    • 数据独立性高
    • 数据由数据库管理系统统一管理和控制
      1. 数据的安全性保护
      2. 数据的完整性检查
      3. 并发控制
      4. 数据库恢复

数据库管理系统在数据库建立、运用和维护时对数据进行统一控制，以保证数据的完整性和安全性，并在多用户同时使用数据库时进行并发控制，在发生故障后对数据库进行恢复。

从文件系统到数据库系统标志着数据管理技术的飞跃
使信息系统从以加工数据的程序为中心转向围绕共享的数据库为中心的新阶段

RDBMS的常见系统结构

​ 客户端 / 服务器类型（C/S类型）

补充 数据模型

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

两类数据模型：

  1. 概念模型
    1. 实体(entity)：客观存在并可相互区别的实物称为实体
    2. 属性(attribute)：实体所具有的某一特性称为属性
    3. 码(key)：唯一标识实体的属性集称为码
    4. 实体型(entity key)：用实体名及其属性名集合来抽象和刻画同类实体，称为实体型
    5. 实体集(entity set)：同一类型实体的集合称为实体集
    6. 联系(relationship)：实体之间的联系通常是指不同实体集之间的联系（一对一，一对多，多对多等）
    • 实体-联系方法(E-R方法、E-R模型)
  2. 逻辑模型和物理模型

数据模型的组成要素

  1. 数据结构：描述数据库的组成对象以及对象之间的联系
  2. 数据操作：对数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关的操作规则
  3. 数据的完整性约束条件：数据的完整性约束条件是一组完整性规则

常用的数据模型

  • 层次模型
  • 网状模型（与层次模型统称为格式化模型）
  • 关系模型
  • 面向对象数据模型
  • 对象关系数据模型
  • 半结构化数据模型

基本层次联系：两个记录以及他们之间的一对多（包括一对一）的联系

层次模型

  1. 有且只有一个结点没有双亲结点，这个节点称为根节点
  2. 根以外的其他节点有且只有一个双亲结点

完整性约束：

  1. 层次模型的数据操纵主要有查询、插入、删除和更新
  2. 进行插入操作时，如果没有相应的双亲结点值就不能插入它的子女节点值
  3. 进行删除操纵时，如果删除双亲结点值，则相应的子女结点值也将被同时删除

优点

  1. 数据结构简单清晰
  2. 查询效率高
  3. 提供了良好的完整性支持
    缺点
  4. 现实世界中很多联系是非层次的，不适用
  5. 对插入和删除的限制较多
  6. 查询子女结点必须通过双亲结点
  7. 层次命令趋于程序化

层次模型像一棵倒立的树，结点的双亲是唯一的

网状模型

  1. 允许一个以上的结点无双亲
  2. 一个节点可以有多于一个的双亲

完整性约束

  1. 支持记录码的概念，码即唯一标识记录的数据项的集合
  2. 保证一个联系中双亲记录和子女记录之间是一对多的联系
  3. 可以支持双亲记录和子女记录之间的某些约束条件

优点

  1. 能够更为直接地描述现实世界
  2. 具有良好的性能，存储效率较高
    缺点
  3. 结构比较复杂，而且随着应用环境的扩大，数据库的结构就变得越来越复杂，不利于最终用户掌握
  4. 网状模型的DDL，DML复杂，并且要嵌入某一种高级语言中。用户不容易掌握，不容易使用
  5. 由于记录之间的联系是通过存取路径实现的，应用程序在访问数据时必须选择适当的存储路径，因此用户必须了解系统结构的细节，加重了编写应用程序的负担

关系模型
建立在严格的数学概念的基础上

关系(relation)：一个关系对应通常说的一张表
元组(tuple)：表中的一行即为一个元组
属性(attribute)：表中一个列即为一个属性
码(key)：码键，表示某个属性组，可以唯一确定一个元组
域(domain)：一组具有相同数据类型的值的集合
分量：元组中的一个属性值
关系模式：对关系的描述，一般表示为 关系名(属性名1，属性名2...属性n)

  1. 关系模型要求关系必须是规范化的
  2. 关系的每一个分量必须是一个不可分的数据项
关系术语 一般表格的术语
关系名 表名
关系模式 表头（表格的描述）
关系 （一张）二维表
元组 记录或行
属性 列
属性名 列名
属性值 列值
分量 一条记录中的一个列值
非规范关系 表中有表

完整性约束

  1. 实体完整性
  2. 参照完整性
  3. 用户定义的完整性

优点

  1. 关系模型与格式化模型不同，它是建立在严格的数学概念的基础上的
  2. 关系模型的概念单一
  3. 关系模型的存取路径对用户透明，从而有更高的数据独立性、更好的安全保密性，也简化了程序员的工作和数据库开发建立的工作

补充 数据库系统的结构

在数据库中有”型“(type)和”值“(value)的概念

模式是相对稳定的，而实例是相对变动的

数据库系统的三级模式结构

  1. 模式(schema)
    模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据试图

  2. 外模式(external schema)
    外模式也称子模式(subschema)或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

  3. 内模式(internal schema)
    内模式也称存储模式(storage schema)，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

  • 外模式/模式映像：当模式改变时，由数据库管理员对各个外模式/模式的映像作相应改变，可以使外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

  • 模式/内模式映像：当数据库的存储结构改变时，由数据库管理员对模式/内模式映像作相应的改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据与程序的物理独立性，简称数据的物理独立性。

补充 关系数据结构及形式化定义

关系模型的数据结构——关系

  1. 域（domain）：一组具有相同数据类型的值的集合
  2. 笛卡尔积（cartesian product）
    一个域允许的不同取值个数称为这个域的基数（cardinal number）
  3. 关系（relation）：
    1. 某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码（candidate key）
    2. 若一个关系有多个候选码，则选定其中一个为主码（primary key）
    3. 候选码的诸属性称为主属性。不包含在任何候选码中的属性称为非主属性（non-prime attribute）或非码属性（non-key attribute）
    4. 最极端的情况下，关系模式的所有属性是这个关系模式的候选码，称为全码（all-key）
  4. 关系可以有三种类型：
    1. 基本关系（基本表/基表）
      • 列是同质的（每一列中的分量是同一类型的数据，来自同一个域）
      • 不同的列可出自同一个域，称其中的每一列为一个属性，不同的属性要给予不同的属性名
      • 列的顺序无所谓
      • 任意两个元组的候选码不能取相同的值
      • 行的顺序无所谓
      • 分量必须取原子值，即每一个分量都必须是不可分的数据项
    2. 查询表
    3. 视图表
  5. 关系模式：R（U，D，DOM，F）

补充 关系的完整性

  1. 关系模型中有三类完整性约束：
    1. 实体完整性（entity integrity）
    2. 参照完整性（referential integrity）
    3. 用户定义的完整性（user-defined integrity）
    • 实体完整性和参照完整性是关系模型必须满足的完整性约束条件（关系的两个不变性）
  2. 实体完整性
    • 若属性（指一个或一组属性）A是基本关系R的主属性，则A不能取空值（null value）
  3. 参照完整性
    • 若属性（或属性组）F是基本关系R的外码，他与基本关系S的主码K相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须：
      1. 或者取空值（F的某个属性值均为空值）
      2. 或者等于S中某个元素的主码值
      3. 空值
      4. 非空值

SQL概要

  1. SQL语句及其种类

    • **DDL（Data Definition Language，数据定义语言） **：

      CREATE：创建数据库和表等对象
      DROP： 删除数据库和表等对象
      ALTER： 修改数据库和表等对象的结构

    • **DML（Data Manipulation Language，数据操纵语言） **：

      SELECT：查询表中的数据
      INSERT：向表中插入新数据
      UPDATE：更新表中的数据
      DELETE：删除表中的数据

    • DCL（Data Control Language，数据控制语言）：

      COMMIT： 确认对数据库中的数据进行的变更
      ROLLBACK：取消对数据库中的数据进行的变更
      GRANT： 赋予用户操作权限
      REVOKE： 取消用户的操作权限

  2. SQL的基本书写规则

表的创建

  1. 数据库的创建（CREATE DATABASE语句）

    1
    CREATE DATABASE <数据库名称>;

  2. 表的创建（CREATE TABLE语句）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE <表名>
    （<列名1> <数据类型> <该列所需约束>,
     <列名2> <数据类型> <该列所需约束>,
     <列名3> <数据类型> <该列所需约束>,
     <列名4> <数据类型> <该列所需约束>,
     .
     .
     .
     <该表的约束1>, <该表的约束2>，……）;

  3. 命名规则

  4. 数据类型的指定

  • **INTEGER型:**用来指定存储整数的列的数据类型（数字型），不能存储小数。
  • **CHAR型:**用来指定存储字符串的列的数据类型（字符型）,字符串长度固定，长度不足时会用半角空格补足
  • VARCHAR型：可变长字符串
  • DATE型：用来指定存储日期（年月日）的列的数据类型（日期型）。
  1. 约束的设置
  • NULL/NOT NULL
  • PRIMARY KEY

表的删除和更新

  1. 表的删除（DROP TABLE语句）

    1
    DROP TABLE <表名>;

  2. 表定义的更新（ALTER TABLE语句）

  • 添加列
    1
    ALTER TABLE <表名> ADD COLUMN <列的定义>;
    Sever
    1
    ALTER TABLE <表名> ADD <列名> ;
    1
    ALTER TABLE <表名> ADD （<列名>,<列名>,……）;
  • 删除列
    1
    ALTER TABLE <表名> DROP COLUMN <列名>;
    Sever
    1
    ALTER TABLE <表名> DROP <列名> ;
    1
    ALTER TABLE <表名> DROP （<列名>,<列名>,……）;
  1. 变更表名
    1
    ALTER TABLE Poduct RENAME TO Product;
    1
    RENAME TABLE Poduct TO Product;
    Sever
    1
    sp_rename 'Poduct', 'Product';
    1
    RENAME TABLE Poduct to Product;

查询基础

SELECT语句基础

基本的SELECT语句

基本的SELECT语句

1
2
SELECT <列名>,……
	FROM <表名>;

查询全部的列

1
2
SELECT *
	FROM <表名>;

为列设定别名

1
2
3
4
SELECT product_id AS id,
    product_name AS name,
    purchase_price AS price
FROM <表名>;

设定中文别名

1
2
3
4
SELECT product_id AS "商品编号",
	product_name AS "商品名称",
	purchase_price AS "进货单价"
FROM <表名>;

查询常数

1
2
3
4
5
6
SELECT '商品' AS string, 
	38 AS number, 
	'2009-02-24' AS date,
	product_id,
	product_name
FROM <表名>;

使用DISTINCT删除product_type列中重复的数据

1
2
SELECT DISTINCT <列名>
	FROM <表名>;

在多列之前使用DISTINCT

1
2
SELECT DISTINCT <列名>，……
	FROM <表名>;

DISTINCT 关键字只能用在第一个列名之前。

select语句子句顺序:

子句 说明 是否必须使用
select 要返回的列或表达式 是
from 要从中检索数据的表 仅在要从表中选择数据时使用
where 行级过滤 否
group by 分组说明 仅在按组计算聚集时使用
having 组级过滤 否
order by 输出排序顺序 否
limit 要检索的行数 否

select语句完整执行顺序：

​ 1、from 子句组装来自不同数据源的数据；
​ 2、where 子句基于指定的条件对记录行进行筛选；
​ 3、group by 子句将数据划分为多个分组；
​ 4、使用聚集函数进行计算；
​ 5、使用having 子句筛选分组；
​ 6、计算所有的表达式；
​ 7、select 的字段；
​ 8、使用order by 对结果集进行排序。

SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在SQL 语句中，第一个被处理的子句式FROM，而不是第一出现的SELECT。SQL 查询处理的步骤序号：

1
2
3
4
5
6
7
8
9
10
11
(1) FROM <left_table>
(2) <join_type> JOIN <right_table>
(3) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(8) SELECT
(9) DISTINCT
(9) ORDER BY <order_by_list>
(10) <TOP_specification> <select_list>

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

逻辑查询处理阶段简介：

​ 1、FROM：对FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。
​ 2、ON：对VT1 应用ON 筛选器，只有那些使为真才被插入到TV2。
​ 3、OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN 或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM 子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1 到步骤3，直到处理完所有的表位置。
​ 4、WHERE：对TV3 应用WHERE 筛选器，只有使为true 的行才插入TV4。
​ 5、GROUP BY：按GROUP BY 子句中的列列表对TV4 中的行进行分组，生成TV5。
​ 6、CUTE|ROLLUP：把超组插入VT5，生成VT6。
​ 7、HAVING：对VT6 应用HAVING 筛选器，只有使为true 的组插入到VT7。
​ 8、SELECT：处理SELECT 列表，产生VT8。
​ 9、DISTINCT：将重复的行从VT8 中删除，产品VT9。
​ 10、ORDER BY：将VT9 中的行按ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。
​ 11、TOP：从VC10 的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。

根据WHERE语句来选择记录

SELECT语句中的WHERE子句

1
2
3
SELECT <列名>, ……
	FROM <表名>
WHERE <条件表达式>;

注释

1
2
3
4
-- 本SELECT语句会从结果中删除重复行。

/* 本SELECT语句，
 会从结果中删除重复行。*/

算术运算符和比较运算符

算术运算符

+ - / *

所有包含NULL的计算，结果肯定是NULL

比较运算符

= > < >= <= <>

选取NULL的记录

1
2
3
SELECT product_name, purchase_price
	FROM Product
WHERE purchase_price IS NULL;

选取不为NULL的记录

1
2
3
SELECT product_name, purchase_price
	FROM Product
WHERE purchase_price IS NOT NULL;

逻辑运算符

NOT 与 <> 等价

AND 运算符在其两侧的查询条件都成立时整个查询条件才成立

OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立

() 增加运算优先级

含有NULL时的真值

AND

P Q P AND Q
真 真 真
真 假 假
真 不确定 不确定
假 真 假
假 假 假
假 不确定 假
不确定 真 不确定
不确定 假 假
不确定 不确定 不确定

OR

P Q P OR Q
真 真 真
真 假 真
真 不确定 真
假 真 真
假 假 假
假 不确定 不确定
不确定 真 真
不确定 假 不确定
不确定 不确定 不确定

聚合与排序

聚合(集)函数

1
2
3
4
5
COUNT：计算表中的记录数（行数）
SUM： 计算表中数值列中数据的合计值
AVG： 计算表中数值列中数据的平均值
MAX： 求出表中任意列中数据的最大值
MIN： 求出表中任意列中数据的最小值

计算全部数据的行数

1
2
SELECT COUNT(*)
	FROM <表名>;

对于 COUNT 函数来说，参数列不同计算的结果也会发生变化

将包含NULL的列作为参数时，COUNT（*）和COUNT（<列名>）的结果并不相同

计算合计值

1
2
SELECT SUM(列名)
	FROM <表名>;

对于 SUM 函数来说，即使包含 NULL，也可以计算出合计值。

计算平均值

1
2
SELECT AVG(列名)
	FROM <表名>;

计算最大值和最小值

1
2
SELECT MAX(列名),MIN(列名)
	FROM <表名>;

是 SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

计算去除重复数据后的数据行数

1
2
SELECT COUNT(DISTINCT 列名)
	FROM <表名>;

先计算数据行数再删除重复数据的结果

1
2
SELECT DISTINCT COUNT(列名)
	FROM <表名>;

不仅限于 COUNT 函数，所有的聚合函数都可以使用 DISTINCT。

对表进行分组

使用GROUP BY子句进行汇总

1
2
3
SELECT <列名1>, <列名2>, <列名3>, ……
	FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;

在 GROUP BY 子句中指定的列称为聚合键或者分组列

GROUP BY 子句的书写位置也有严格要求，一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。

当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据

使用WHERE子句和GROUP BY子句进行汇总处理

1
2
3
4
SELECT <列名1>, <列名2>, <列名3>, ……
	FROM <表名>
WHERE 
GROUP BY <列名1>, <列名2>, <列名3>, ……;

为聚合结果指定条件

HAVING 子句

1
2
3
4
SELECT <列名1>, <列名2>, <列名3>, ……
	FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>;

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件

将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。

对查询结果进行排序

ORDER BY 子句

升序

1
2
3
SELECT <列名1>, <列名2>, <列名3>, ……
	FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, …… (ASC);

降序

1
2
3
SELECT <列名1>, <列名2>, <列名3>, ……
	FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, …… DESC;

规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。

NULL 会在结果的开头或末尾汇总显示

ORDER BY子句中可以使用列的别名

SELECT 子句中未包含的列也可以在 ORDER BY子句中使用

ORDER BY 子句中也可以使用聚合函数

ORDER BY子句中可以使用列的编号

1
2
3
4
5
6
7
8
-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
	FROM Product
ORDER BY sale_price DESC, product_id;
-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
	FROM Product
ORDER BY 3 DESC, 1;

REMEMBER : FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

四、数据更新

数据的插入（INSERT语句的使用方法）

INSERT语句

1
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

表名后面的列清单和 VALUES 子句中的值清单的列数必须保持一致

通常的 INSERT 和多行 INSERT

1
2
3
4
5
6
7
8
9
-- 通常的INSERT
INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO ProductIns VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO ProductIns VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （Oracle以外）
INSERT INTO ProductIns VALUES 
('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
1
2
3
4
INSERT ALL INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
		   INTO ProductIns VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
		   INTO ProductIns VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;

省略列清单（ 对表进行全列 INSERT 时，可以省略表名后的列清单。)

1
2
3
4
-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO ProductIns VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES 子句的值清单中写入 NULL。但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束。

可以在创建表的 CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值

插入默认值有显式和隐式两种方法

从其他表中复制数据

INSERT … SELECT 语句

1
2
3
4
-- 将商品表中的数据复制到商品复制表中
INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
	FROM Product;

插入其他表中数据合计值的 INSERT … SELECT 语句

1
2
3
4
INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
	FROM Product
GROUP BY product_type;

数据的删除（DELETE语句的使用方法）

DELETE语句的基本语法（保留数据表，仅删除全部数据行的 DELETE 语句）

1
DELETE FROM <表名>;

删除部分数据行的搜索型 DELETE

1
2
DELETE FROM <表名>
	WHERE <条件>;

只能删除表中全部数据的 TRUNCATE 语句

1
TRUNCATE <表名>;

数据的更新（UPDATE语句的使用方法）

改变表中数据的 UPDATE 语句

1
2
UPDATE <表名>
	SET <列名> = <表达式>;

更新部分数据行的搜索型 UPDATE

1
2
3
UPDATE <表名>
	SET <列名> = <表达式>
WHERE <条件>;

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

1
2
3
4
5
-- 使用逗号对列进行分隔排列
UPDATE Product
	SET sale_price = sale_price * 10,
		purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
1
2
3
4
-- 将列用()括起来的清单形式
UPDATE Product
	SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
WHERE product_type = '厨房用具';

事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。

事务的语法

1
2
3
4
5
6
事务开始语句;
	DML语句①;
	DML语句②;
	DML语句③;
 . . .
事务结束语句（COMMIT或者ROLLBACK）;

开始语句

Server、PostgreSQL
1
BEGIN TRANSACTION;
1
START TRANSACTION;
1
无

更新商品信息的事务

Server、PostgreSQL
1
2
3
4
5
6
7
8
9
10
BEGIN TRANSACTION;
 -- 将运动T恤的销售单价降低1000日元
UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = '运动T恤';
 -- 将T恤衫的销售单价上浮1000日元
UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T恤衫';
COMMIT;
1
2
3
4
5
6
7
8
9
10
START TRANSACTION;
 -- 将运动T恤的销售单价降低1000日元
UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = '运动T恤';
 -- 将T恤衫的销售单价上浮1000日元
UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T恤衫';
COMMIT;
1
2
3
4
5
6
7
8
9
 -- 将运动T恤的销售单价降低1000日元
UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = '运动T恤';
 -- 将T恤衫的销售单价上浮1000日元
UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T恤衫';
COMMIT;

COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。

ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态。

事务回滚的例子

Server、PostgreSQL
1
2
3
4
5
6
7
8
9
10
BEGIN TRANSACTION;
 -- 将运动T恤的销售单价降低1000日元
UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = '运动T恤';
 -- 将T恤衫的销售单价上浮1000日元
UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T恤衫';
ROLLBACK;

ACID特性

  • 原子性（Atomicity）:

    原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。

  • 一致性/完整性（Consistency）:

    一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。

  • 隔离性（Isolation）:

    隔离性指的是保证不同事务之间互不干扰的特性。

  • 持久性（Durability）:

    持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。

复杂查询

视图 ( 保存好的 SELECT 语句 )

从 SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。

视图的优点:

  • 由于视图无需保存数据，因此可以节省存储设备的容量。
  • 可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。

创建视图的 CREATE VIEW 语句

1
2
3
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
1
2
3
4
5
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
	FROM Product
	GROUP BY product_type;

使用视图

1
2
SELECT product_type, cnt_product
	FROM ProductSum;

多重视图会降低 SQL 的性能。

TIP:

  1. 定义视图时不能使用 ORDER BY 子句
  2. 对视图进行更新。标准 SQL 中有这样的规定：如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新。
    • SELECT 子句中未使用 DISTINCT
    • FROM 子句中只有一张表
    • 未使用 GROUP BY 子句
    • 未使用 HAVING 子句

删除视图的DROP VIEW语句

1
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……);

子查询

一言以蔽之，子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。

视图 ProductSum 和确认用的 SELECT 语句

1
2
3
4
5
6
7
8
9
-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
	FROM Product
	GROUP BY product_type;
-- 确认创建好的视图
SELECT product_type, cnt_product
	FROM ProductSum;

能够实现同样功能的子查询

Server、DB2、PostgreSQL、MySQL
1
2
3
4
5
6
7
8
9
-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
	FROM ( SELECT product_type, COUNT(*) AS cnt_product
 			FROM Product
			GROUP BY product_type ) 
	AS ProductSum;

/*在Oracle的FROM子句中，不能使用AS（会发生错误），因此，在Oracle中执行代
码时，需要将“) AS ProductSum;”变为“) ProductSum;”。*/

尝试增加子查询的嵌套层数

Server、DB2、PostgreSQL、MySQL
1
2
3
4
5
6
7
8
SELECT product_type, cnt_product
	FROM (SELECT *
 			FROM (SELECT product_type, COUNT(*) AS cnt_product
 					FROM Product
 					GROUP BY product_type) 
          	AS ProductSum 
          	WHERE cnt_product = 4)
    AS ProductSum2;

原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。

标量子查询 : 必须而且只能返回 1 行 1列的结果

由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。

注意事项 : 该子查询绝对不能返回多行结果。

关联子查询

通过关联子查询按照商品种类对平均销售单价进行比较

Server、DB2、PostgreSQL、MySQL
1
2
3
4
5
6
SELECT product_type, product_name, sale_price
	FROM Product AS P1
	WHERE sale_price > (SELECT AVG(sale_price)
 		FROM Product AS P2
 		WHERE P1.product_type = P2.product_type
 		GROUP BY product_type);

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

函数、谓词、CASE表达式

各种各样的函数

函数大致可以分为以下几种：

  • 算术函数（用来进行数值计算的函数）
  • 字符串函数（用来进行字符串操作的函数）
  • 日期函数（用来进行日期操作的函数）
  • 转换函数（用来转换数据类型和值的函数）
  • 聚合函数（用来进行数据聚合的函数）

补：NUMERIC 是大多数 DBMS 都支持的一种数据类型，通过 NUMBERIC( 全体位数 , 小数位数 ) 的形式来指定数值的大小

算术函数:

ABS ( 绝对值 )

1
ABS(数值/列名)

MOD ( 求余 )

1
MOD(被除数/列名，除数/列名)

SQL Server使用特殊的运算符（函数）“%”来计算余数

ROUND ( 四舍五入 )

1
ROUND(对象数值/列名，保留小数的位数)

字符串函数:

|| ( 拼接 )

1
字符串1||字符串2

进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是NULL。

TIP:|| 函数在 SQL Server 和 MySQL 中无法使用。

SQL Server使用 “+” 运算符（函数）来连接字符串A。MySQL使用CONCAT()函数来完成字符串的拼接。在 SQL Server 2012及其之后的版本中也可以使用CONCAT()函数。

LENGTH ( 字符串长度 )

1
LENGTH(字符串/列名)

SQL Server使用 LEN() 函数来计算字符串的长度。

LOWER ( 小写转换 )

1
LOWER(字符串/列名)

LOWER 函数只能针对英文字母使用

UPPER ( 大写转换 )

1
UPPER(字符串/列名)

UPPER 函数只能针对英文字母使用

REPLACE ( 字符串的替换 )

1
REPLACE(对象字符串/列名，替换前的字符串/列名，替换后的字符串/列名)

SUBSTRING ( 字符串的截取 )

1
SUBSTRING（对象字符串/列名 FROM 截取的起始位置 FOR 截取的字符数）
Server
1
SUBSTRING(对象字符串/列名，截取的起始位置，截取的字符数)
1
SUBSTR(对象字符串/列名，截取的起始位置，截取的字符数)

日期函数:

CURRENT_DATE ( 当前日期 )

1
CURRENT_DATE
1
SELECT CURRENT_DATE;
Server
1
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
1
2
SELECT CURRENT_DATE
	FROM dual;
1
2
SELECT CURRENT DATE
	FROM SYSIBM.SYSDUMMY1;

CURRENT_TIME ( 当前时间 )

1
CURRENT_TIME
1
SELECT CURRENT_TIME;
Server
1
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
1
2
SELECT CURRENT_TIMESTAMP
	FROM dual;
1
2
SELECT CURRENT TIME
	FROM SYSIBM.SYSDUMMY1;

CURRENT_TIMESTAMP ( 当前日期和时间 )

1
CURRENT_TIMESTAMP
Server
1
SELECT CURRENT_TIMESTAMP;
1
2
SELECT CURRENT_TIMESTAMP
	FROM dual;
1
2
SELECT CURRENT TIMESTAMP
	FROM SYSIBM.SYSDUMMY1;

EXTRACT ( 截取日期元素 )

1
EXTRACT(日期元素 FROM 日期)
1
2
3
4
5
6
7
SELECT CURRENT_TIMESTAMP,
	EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
	EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
	EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
	EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
	EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
	EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
Server
1
2
3
4
5
6
7
SELECT CURRENT_TIMESTAMP,
	DATEPART(YEAR , CURRENT_TIMESTAMP) AS year,
	DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
	DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
	DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
	DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
	DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
1
2
3
4
5
6
7
8
SELECT CURRENT_TIMESTAMP,
	EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
	EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
	EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
	EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
	EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
	EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second
FROM DUAL;
1
2
3
4
5
6
7
8
SELECT CURRENT TIMESTAMP,
	EXTRACT(YEAR FROM CURRENT TIMESTAMP) AS year,
	EXTRACT(MONTH FROM CURRENT TIMESTAMP) AS month,
	EXTRACT(DAY FROM CURRENT TIMESTAMP) AS day,
	EXTRACT(HOUR FROM CURRENT TIMESTAMP) AS hour,
	EXTRACT(MINUTE FROM CURRENT TIMESTAMP) AS minute,
	EXTRACT(SECOND FROM CURRENT TIMESTAMP) AS second
FROM SYSIBM.SYSDUMMY1;

转换函数:

SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为 castA ；另一层意思是值的转换。

CAST ( 类型转换 )

1
CAST（转换前的值 AS 想要转换的数据类型）
  1. 将字符串类型转换为数值类型
Server、PostgreSQL
1
SELECT CAST('0001' AS INTEGER) AS int_col;
1
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
1
2
SELECT CAST('0001' AS INTEGER) AS int_col
	FROM DUAL;
1
2
SELECT CAST('0001' AS INTEGER) AS int_col
	FROM SYSIBM.SYSDUMMY1;
  1. 将字符串类型转换为日期类型
Server、PostgreSQL、MySQL
1
SELECT CAST('2009-12-14' AS DATE) AS date_col;
1
2
SELECT CAST('2009-12-14' AS DATE) AS date_col 
	FROM DUAL;
1
2
SELECT CAST('2009-12-14' AS DATE) AS date_col 
	FROM SYSIBM.SYSDUMMY1;

COALESCE ( 将NULL转换为其他值 )

1
COALESCE(数据1，数据2，数据3……)

该函数会返回可变参数 A 中左侧开始第1个不是 NULL 的值

Server、PostgreSQL、MySQL
1
2
3
SELECT COALESCE(NULL, 1) AS col_1,
	COALESCE(NULL, 'test', NULL) AS col_2,
	COALESCE(NULL, NULL, '2009-11-01') AS col_3;
1
2
3
4
SELECT COALESCE(NULL, 1) AS col_1,
	COALESCE(NULL, 'test', NULL) AS col_2,
	COALESCE(NULL, NULL, '2009-11-01') AS col_3 
FROM DUAL;
1
2
3
4
SELECT COALESCE(NULL, 1) AS col_1,
	COALESCE(NULL, 'test', NULL) AS col_2,
	COALESCE(NULL, NULL, '2009-11-01') AS col_3 
FROM SYSIBM.SYSDUMMY1;

谓词

谓词就是返回值为真值的函数。

LIKE谓词 ( 字符串的部分一致查询 )

  1. 前方一致：选取出“dddabc”
1
2
3
SELECT *
	FROM SampleLike
	WHERE strcol LIKE 'ddd%';
  1. 中间一致：选取出“abcddd” “dddabc” “abdddc”
1
2
3
SELECT *
	FROM SampleLike
	WHERE strcol LIKE '%ddd%';
  1. 后方一致：选取出“abcddd”
1
2
3
SELECT *
	FROM SampleLike
	WHERE strcol LIKE '%ddd';

% 是代表 “0 字符以上的任意字符串”的特殊符号

***_*（下划线）代表 “任意 1 个字符”

使用 LIKE 和 _（下划线）进行后方一致查询

1
2
3
SELECT *
	FROM SampleLike
	WHERE strcol LIKE 'abc__';

BETWEEN谓词 ( 范围查询 )

选取销售单价为100～1000 ( 闭区间 )日元的商品

1
2
3
SELECT product_name, sale_price
	FROM Product
	WHERE sale_price BETWEEN 100 AND 1000;

IS NULL、IS NOT NULL （ 判断是否为NULL ）

IN谓词 ( OR的简便用法 )

通过 OR 指定多个进货单价进行查询

1
2
3
4
5
SELECT product_name, purchase_price
	FROM Product
	WHERE purchase_price = 320
	OR purchase_price = 500
	OR purchase_price = 5000;

通过 IN 来指定多个进货单价进行查询

1
2
3
SELECT product_name, purchase_price
	FROM Product
	WHERE purchase_price IN (320, 500, 5000);

使用 IN 和 NOT IN 时是无法选取出 NULL 数据的

可以使用子查询作为 IN 谓词的参数以应对数据变更

EXIST谓词

为 EXIST 是只有 1 个参数的谓词。EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件，只有存在这样的记录时才返回真

CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

搜索CASE表达式

1
2
3
4
5
6
CASE WHEN <求值表达式> THEN <表达式>
	WHEN <求值表达式> THEN <表达式>
 	WHEN <求值表达式> THEN <表达式>
 	. . .
 	ELSE <表达式>
END

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。

可以利用 CASE 表达式将 SELECT 语句结果中的行和列进行互换。

简单CASE表达式

1
2
3
4
5
6
7
CASE <表达式>
    WHEN <表达式> THEN <表达式>
	WHEN <表达式> THEN <表达式>
	WHEN <表达式> THEN <表达式>
 	. . .
	ELSE <表达式>
END

两者的不同之处在于，简单 CASE 表达式最初的 “CASE< 表达式 >” 也会作为求值的对象。

集合运算

表的加减法

UNION ( 选取表中非公共部分/并集 )

1
2
3
4
5
6
SELECT (列1, 列2, 列3, ……)
	FROM <表1>...
UNION
SELECT (列1, 列2, 列3, ……)
	FROM <表2>...;
(ORDER BY)

注意事项：

  1. 作为运算对象的记录的列数必须相同
  2. 作为运算对象的记录中列的类型必须一致，一定要使用不同数据类型的列时，可以使用类型转换函数 CAST。
  3. 可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次

UNION ALL ( 包含重复行的集合运算 )

1
2
3
4
5
6
SELECT (列1, 列2, 列3, ……)
	FROM <表1>...
UNION ALL /*保留重复行*/
SELECT (列1, 列2, 列3, ……)
	FROM <表2>...;
(ORDER BY)

INTERSECT ( 选取表中公共部分/交集 )

Server、DB2、PostgreSQL
1
2
3
4
5
6
SELECT (列1, 列2, 列3, ……)
	FROM <表1>...
INTERSECT
SELECT (列1, 列2, 列3, ……)
	FROM <表2>...;
(ORDER BY)

INTERSECT ALL ( 包含重复行的集合运算 )

Server、DB2、PostgreSQL
1
2
3
4
5
6
SELECT (列1, 列2, 列3, ……)
	FROM <表1>...
INTERSECT ALL
SELECT (列1, 列2, 列3, ……)
	FROM <表2>...;
(ORDER BY)

EXCEPT ( 记录的减法/差集 )

Server、DB2、PostgreSQL
1
2
3
4
5
6
SELECT (列1, 列2, 列3, ……)
	FROM <表1>...
EXCEPT
SELECT (列1, 列2, 列3, ……)
	FROM <表2>...;
(ORDER BY)

Oracle需用 MINUS 代替 EXCEPT

与 UNION 和 INTERSECT 不同，被减数和减数位置不同，得到的结果不同

联结（以列为单位对表进行联结）

联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。

INNER JOIN ( 内联结 )

将两张表进行内联结

Server、DB2、PostgreSQL、MySQL
1
2
3
SELECT (<表1>.列1, <表1>.列2, <表1>.列3, ……<表2>.列1, <表2>.列2……)
	FROM <表1> AS SP INNER JOIN <表2> AS P
	ON SP.共有的列 = P.共有的列;

要点：

  1. FROM 子句同时使用了两张表，别名并不是必需的
  2. ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用。需要指定多个键时，同样可以使用 AND、OR。在进行内联结时 ON 子句是必不可少的（如果没有 ON会发生错误），并且 ON 必须书写在 FROM 和 WHERE 之间。联结条件也可以使用 “=” 来记述。在语法上，还可以使用 <= 和 BETWEEN 等谓词。
  3. 为了避免混乱，在使用联结时按照“< 表的别名 >.< 列名 >”的格式来书写 SELECT 子句中全部的列。

OUTER JOIN ( 外联结 )

Server、DB2、PostgreSQL、MySQL
1
2
3
SELECT (<表1>.列1, <表1>.列2, <表1>.列3, ……<表2>.列1, <表2>.列2……)
	FROM <表1> AS SP RIGHT/LEFT OUTER JOIN <表2> AS P
	ON SP.共有的列 = P.共有的列;

要点：

  1. 内联结只能选取出同时存在于两张表中的数据。对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。
  2. *最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT。使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT 时右侧的表是主表。*

3张以上的表的联结

对3张表进行内联结

Server、DB2、PostgreSQL、MySQL
1
2
3
4
5
SELECT (<表1>.列1, <表1>.列2, <表1>.列3, ……<表2>.列1, <表2>.列2……<表3>.列1, <表3>.列2……)
	FROM <表1> AS SP INNER JOIN <表2> AS P
	ON SP.共有的列 = P.共有的列;
		INNER JOIN <表3> AS IP
		ON SP.共有的列 = IP.共有的列;

原则上联结表的数量并没有限制

CROSS JOIN ( 交叉联结 )

Server、DB2、PostgreSQL、MySQL
1
2
SELECT (<表1>.列1, <表1>.列2, <表1>.列3, ……<表2>.列1, <表2>.列2……)
	FROM <表1> AS SP CROSS JOIN <表2> AS P;

交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。

SQL高级处理

窗口函数

窗口函数也称为 OLAP 函数，OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据

进行实时分析处理。

窗口函数

1
2
<窗口函数> OVER ([PARTITION BY <列清单>] 
	ORDER BY <排序用列清单>)

PARTITION BY 能够设定排序的对象范围。ORDER BY 能够指定按照哪一列、何种顺序进行排序。

通过PARTTION BY分组后的记录的集合可以称为窗口

窗口函数大体可以分为以下两种 :

  1. 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
  2. RANK、DENSE_RANK、ROW_NUMBER 等*专用窗口函数 ( 标准 SQL 定义的 OLAP 专用函数 )*

无需指定 PARTITION BY

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。

具有代表性的专用窗口函数:

  • RANK 函数
1
2
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
Eg : 有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
  • DENSE_RANK 函数
1
2
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
Eg : 有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
  • ROW_NUMBER 函数
1
2
赋予唯一的连续位次。
Eg : 有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

**窗口函数的适用范围 : **窗口函数只能书写在SELECT 子句之中。( ORDER BY 子句或者 UPDATE 语句的 SET 子句中也可以使用。)

作为窗口函数使用的聚合函数 : 累计

**计算移动平均 : **用框架。只需要在 ORDER BY 子句之后使用指定范围的关键字

指定框架（汇总范围）: ROWS(“行”) 、PRECEDING(“之前”)、FOLLOWING(“之后”)

1
2
3
4
5
SELECT product_id, product_name, sale_price,
	AVG (sale_price) OVER (ORDER BY product_id
	ROWS 2 PRECEDING) /*截止到之前 2 行*/
    AS moving_avg
FROM Product;

两个ORDER BY : OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。因此也有可能得到一个记录的排列顺序比较混乱的结果。

GROUPING运算符

GROUPING 运算符包含以下 3 种 :

  • ROLLUP
  • CUBE
  • GROUPING SETS

ROLLUP ( 同时得出合计和小计 )

Server、DB2、PostgreSQL
1
2
3
SELECT product_type, SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY ROLLUP(product_type);
1
2
3
SELECT product_type, SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY product_type WITH ROLLUP(product_type);

GROUPING ( 让NULL更加容易分辨 )

该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0

Server、DB2、PostgreSQL
1
2
3
4
SELECT GROUPING(product_type) AS product_type, 
	GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
	FROM Product
	GROUP BY ROLLUP(product_type, regist_date);

在超级分组记录的键值中插入恰当的字符串

Server、DB2、PostgreSQL
1
2
3
4
5
6
7
8
9
SELECT CASE WHEN GROUPING(product_type) = 1 
		THEN '商品种类 合计' 
		ELSE product_type END AS product_type,
	   CASE WHEN GROUPING(regist_date) = 1 
		THEN '登记日期 合计' 
		ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
 	   SUM(sale_price) AS sum_price
 FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

CUBE

CUBE 的语法和 ROLLUP 相同

所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。

GROUPING SETS

该运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录

法则

一

  1. 关系数据库以行为单位读写数据。

  2. 一个单元格中只能输入一个数据。

  3. 学会标准 SQL就可以在各种RDBMS中书写 SQL语句了。

  4. SQL根据功能不同可以分为三类，其中使用最多的是 DML。

  5. SQL语句以分号（;）结尾。

  6. 关键字不区分大小写。

  7. 字符串和日期常数需要使用单引号（’）括起来，数字常数无需加注单引号（直接书写数字即可）。

  8. 单词之间需要使用半角空格或者换行符进行分隔。

  9. 数据库名称、表名和列名等可以使用以下三种字符。

    角英文字母　 半角数字　　下划线（_）

  10. 名称必须以半角英文字母作为开头。

  11. 名称不能重复。

  12. 删除了的表是无法恢复的。在执行DROP TABLE语句之前请务必仔细确认。

  13. 表定义变更之后无法恢复。在执行ALTER TABLE语句之前请务必仔细确认。

二

  1. 星号（*）代表全部列的意思。
  2. 设定汉语别名时需要使用双引号（”）括起来。
  3. 在SELECT语句中使用DISTINCT可以删除重复行。在使用 DISTINCT 时，NULL 也被视为一类数据。
  4. WHERE子句要紧跟在FROM子句之后。
  5. 注释是 SQL语句中用来标识说明或者注意事项的部分。分为 1行注释和多行注释两种。
  6. SELECT子句中可以使用常数或者表达式。
  7. 使用比较运算符时一定要注意不等号和等号的位置。
  8. 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
  9. 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。
  10. NOT运算符用来否定某一条件，但是不能滥用。
  11. 多个查询条件进行组合时，需要使用AND运算符或者OR运算符。
  12. 文氏图很方便。
  13. AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。
  14. 通过创建真值表，无论多复杂的条件，都会更容易理解。

三

  1. COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
  2. 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
  3. MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
  4. 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
  5. 在聚合函数的参数中使用DISTINCT，可以删除重复数据。
  6. GROUP BY就像是切分表的一把刀。
  7. SQL子句的顺序不能改变，也不能互相替换。
  8. 聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。
  9. 使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
  10. 在GROUP BY子句中不能使用SELECT子句中定义的别名。
  11. GROUP BY子句结果的显示是无序的。
  12. 只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
  13. HAVING子句要写在GROUP BY子句之后。
  14. 聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。
  15. ORDER BY子句通常写在SELECT语句的末尾。
  16. 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。
  17. 排序键中包含NULL时，会在开头或末尾进行汇总。
  18. 在ORDER BY子句中可以使用SELECT子句中定义的别名。
  19. 在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。
  20. 在ORDER BY子句中不要使用列编号。

四

  1. 原则上，执行一次INSERT语句会插入一行数据。
  2. 省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。
  3. INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何 SQL语法（但使用ORDER BY子句并不会产生任何效果）。
  4. DELETE语句的删除对象并不是表或者列，而是记录（行）。
  5. 可以通过WHERE子句指定对象条件来删除部分数据。
  6. 使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
  7. 事务是需要在同一个处理单元中执行的一系列更新处理的集合。
  8. 虽然我们可以不清楚事务开始的时间点，但是在事务结束时一定要仔细进行确认。

五

  1. 表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。
  2. 应该将经常使用的SELECT语句做成视图。
  3. 应该避免在视图的基础上创建视图。
  4. 定义视图时不要使用ORDER BY子句。
  5. 视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。
  6. 子查询作为内层查询会首先执行。
  7. 标量子查询就是返回单一值的子查询。
  8. 在细分的组内进行比较时，需要使用关联子查询。

六

  1. 通常指定关联子查询作为EXIST的参数。
  2. 作为EXIST参数的子查询中经常会使用SELECT *。
  3. 虽然CASE表达式中的ELSE子句可以省略，但还是希望大家不要省略。
  4. CASE表达式中的END不能省略。

七

  1. 集合运算符会除去重复的记录。
  2. 在集合运算符中使用ALL选项，可以保留重复行。
  3. 进行联结时需要在FROM子句中使用多张表。
  4. 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
  5. 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
  6. 外联结中使用LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。
  7. 对于联结的过时语法和特定语法，虽然不建议使用，但还是需要能够读懂。

八

  1. 窗口函数兼具分组和排序两种功能。
  2. 通过PARTITION BY分组后的记录集合称为“窗口”。
  3. 由于专用窗口函数无需参数，因此通常括号中都是空的。
  4. 原则上窗口函数只能在SELECT子句中使用。
  5. 将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。
  6. 超级分组记录默认使用NULL作为聚合键。
  7. ROLLUP可以同时得出合计和小计，是非常方便的工具。
  8. 使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL。
  9. 可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体。

引用

SQL基础教程第二版