事务

事务(transaction)，指作为一个整体执行的数据库操作序列。

事务具有被统称为ACID的四个特性

  • 原子性(Atomicity)：事务作为一个整体被执行，其中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
  • 一致性(Consistency)：保证设置在数据库中的约束不会被事务中的操作打破
  • 隔离性(Isolation)：不同事务之间互不干扰
  • 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失

事务控制语句

  • COMMIT：提交处理
  • ROLLBACK：回滚操作

Note

  • 事务的提交有两种模式：一种是自动提交模式，即每条SQL语句就是一个事务；另一种是手动提交模式，用户需要显式地指明事务的开始和结束

复杂查询

视图

视图是一种由SQL查询语句定义的虚拟表格，它不存储实际数据，视图中的数据是定义它的SQL查询语句的执行结果。

视图是从实际表格中派生出来的，从这个角度看，视图可以认为是保存好的SQL查询语句。

对视图的操作

  • 创建视图 
      CREATE VIEW <view_name> (<column_name>,...)
  AS 
  <SELECT statement>
  • 删除视图 
      DROP VIEW <view_name>
  • 更新视图数据 在满足一定条件时，可以使用UPDATE语句对视图进行更新，此时实际被更新的是参与构建视图的表格（称为基表）。 能够更新的大致原则是视图的更新可以唯一确定地对应到基表的更新

Note

  • 视图是可以嵌套的，也就是可以在视图之上创建新的视图。若要把视图及嵌套在其中的视图一起删除，需要使用CASCADE关键字

子查询

子查询是嵌套在SQL查询中的内部查询，它的查询结果作为一张临时表格供外部查询使用。子查询可以认为是一次性的视图。

常用的子查询有

  • 标量子查询 标量子查询是指查询结果为标量的子查询。标量指只有一个数据值，因此标量子查询的查询结果有且只有一行一列。在能够使用常数的地方，都可以使用标量子查询

  • 相关子查询 相关子查询需要引用外部查询中的值，其执行过程为：

    1. 外部查询把候选的行传入相关子查询，对应于外部查询的FROM
    2. 相关子查询根据传入的候选行进行子查询，并返回查询结果给外部查询，对应于内部查询的FROM→WHERE→SELECT
    3. 外部查询从子查询的结果中取出需要的值，对应于外部查询的WHERE→SELECT

高级分组操作

SQL中的GROUP BY分组操作有几个扩展选项，它们是

  • ROLLUP 每次分组操作后，进行聚合计算，然后把最右端的分组列去掉。重复这个过程，直到所有分组列都被去掉。

    一个例子：GROUP BY ROLLUP (column_1, column_2, column_3) 执行时，会按照下列顺序对表格进行GROUP BY操作并计算出相应的聚合值

    1. column_1, column_2 , column_3
    2. column_1, column_2 , NULL
    3. column_1, NULL, NULL
    4. NULL, NULL, NULL

    把这些分组后进行聚合计算得到的结果，汇总到一起，就是最后输出的结果

  • CUBE 把分组列中的列以所有可能的方式进行组合，对所有这些组合都进行一次GROUP BY操作和聚合计算，然后把结果汇总到一起

    一个例子：(a, b)的所有可能组合有

    • (a, b)
    • (a, NULL)
    • (NULL, b)
    • (NULL, NULL)

  • GROUPING SETS 通过GROUPING SETS选项，可以把多个GROUP BY子句合并在一起，输出的结果是这些GROUP BY子句执行结果的多重集并集(UNION ALL)。

Note

  • ROLLUP和CUBE在进行聚合计算时会生成新的一行，在这一行中，被去掉的分组列的值设为NULL，这一行被称为超级聚合行(super-aggregate row)
  • 超级聚合行上的NULL值容易与表格中原来的NULL值弄混，要解决这个问题可以使用GROUPING函数。GROUPING函数对超级聚合行上的NULL值返回1，其他情况返回0

连接

连接(join)，是一种在列方向上把多个表格中的行拼接在一起，生成一张新表的操作。连接时可以使用连接条件来指明参与连接的行要满足什么要求。

与并集、交集、差集这些操作不同的是，连接操作是在列方向上进行的，而并集、交集、差集运算是在行方向上进行的。

连接操作是以笛卡儿积运算为基础的。在各个表格中分别选出一行，我们可以把这些行在列方向上拼接成一行，所有可能的这种拼接出来的行的集合，称为笛卡儿积。

连接两张表的方式有

  • 交叉连接(cross join) 返回的结果是两张表的笛卡儿积，即左表中的每一行，都与右表中的每一行进行拼接

  • 内连接(inner join) 内连接从左右两张表中选出符合连接条件的行进行拼接，它等价于从交叉连接中选出符合连接条件的行

  • 外连接(outer join) 从左右两张表中选定一张为主表，对主表中的每一行，在另一张表中找出符合连接条件的行进行拼接。若在另一张表中找不到符合连接条件的行，则用NULL填充的行与主表中的行进行拼接。 根据主表所处位置的不同，外连接有左外连接和右外连接两种

Note

  • 交叉连接没有连接条件，因为笛卡儿积计算的是所有可能的组合
  • 如何理解内连接的“内”和外连接的“外”：内连接的结果在交叉连接的结果之内；而外连接可能含有交叉连接之外的结果（带有NULL的行）

窗口函数

对于一张表，我们可以把它划分成几个不相交的集合，这些集合称为窗口。窗口函数(window functions)对这些窗口进行处理，并为其中的每一行计算出一个结果。

窗口函数与聚合函数的不同之处在于：窗口函数为窗口中的每一行返回一个值，而聚合函数是对一整个分组返回一个值。

基本形式

 <window_function> OVER ([PARTITION BY <column_name>,...]
			[ORDER BY <column_name>,...] [frame_clause])

  • 窗口由OVER子句定义，其中的PARTITION BY指定了用来划分窗口的列，ORDER BY指定了窗口函数处理窗口中每一行的顺序

  • 通过使用开窗子句(frame clause)，可以使窗口函数在计算一行的结果时，把这一行前后的行也考虑进来。开窗子句以ROWS开头，可用的关键字有 PRECEDING（向前获取行）、 FOLLOWING（向后获取行）、 BETWEEN … AND（在前后之间的范围获取行）等

Note

  • 窗口函数需要在表格内容已确定的情况下进行计算，所以窗口函数一般只用在SELECT子句中，若在WHERE或GROUP BY子句中使用会产生错误。这是由SQL语句的执行顺序决定的，窗口函数必须在FROM→WHERE→GROUP BY之后才能执行
  • 窗口函数中的ORDER BY只影响函数处理各行记录的顺序，不保证显示结果时会把表格排序，要保证输出的结果有序只能在SELECT语句后面另写一条ORDER BY子句

猜您还喜欢