读SQL学习指南（第3版）笔记09_条件逻辑与事务

吃西瓜不吐西瓜子

1. 条件逻辑

1.1. SQL逻辑根据特定列或表达式转向不同的分支来处理

1.2. 在程序执行时从多个路径中选取一个路径的能力

1.3. case表达式

1.3.1. 所有的主流数据库服务器都提供了旨在模拟大多数编程语言中if-then-else 语句的内建函数

1.3.1.1. Oracle的decode()函数

1.3.1.2. MySQL的if()函数

1.3.1.3. SQL Server的coalesce()函数

1.3.2. case表达式是SQL标准的一部分（SQL92发布版）

1.3.2.1. 在Oracle Database、SQL Server、MySQL、PostgreSQL、IBM UDB等数据库中实现

1.3.3. case表达式内建于SQL语法中

1.3.3.1. 用于select、insert、update和delete语句

1.3.4. 搜索型case表达式

1.3.4.1. sql

1. CASE
2.   WHEN C1 THEN E1
3.   WHEN C2 THEN E2
4.   ...
5.   WHEN CN THEN EN
6.   [ELSE ED]
7. END

复制代码

1.3.4.2. mysql

1. -＞ SELECT c.first_name, c.last_name,
2.     -＞   CASE
3.     -＞     WHEN active = 0 THEN 0
4.     -＞     ELSE
5.     -＞      (SELECT count(*) FROM rental r
6.     -＞       WHERE r.customer_id = c.customer_id)
7.     -＞   END num_rentals
8.     -＞ FROM customer c;

复制代码

1.3.5. 简单的case表达式

1.3.5.1. simple case expression

1.3.5.2. 类似于搜索型case表达式，但是在灵活性上略逊

1.3.5.3. sql

1. CASE V0
2.   WHEN V1 THEN E1
3.   WHEN V2 THEN E2
4.   ...
5.   WHEN VN THEN EN
6.   [ELSE ED]
7. END

复制代码

1.3.5.4. sql

1. CASE category.name
2.   WHEN 'Children' THEN 'All Ages'
3.   WHEN 'Family' THEN 'All Ages'
4.   WHEN 'Sports' THEN 'All Ages'
5.   WHEN 'Animation' THEN 'All Ages'
6.   WHEN 'Horror' THEN 'Adult'
7.   WHEN 'Music' THEN 'Teens'
8.   WHEN 'Games' THEN 'Teens'
9.   ELSE 'Other'
10. END

复制代码

1.4. 结果集变换

1.4.1. mysql

1. -＞ SELECT monthname(rental_date) rental_month,
2.     -＞   count(*) num_rentals
3.     -＞ FROM rental
4.     -＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01'
5.     -＞ GROUP BY monthname(rental_date);

复制代码

1.4.2. mysql

1. -＞ SELECT
2.     -＞   SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1
3.     -＞         ELSE 0 END) May_rentals,
4.     -＞   SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1
5.     -＞         ELSE 0 END) June_rentals,
6.     -＞   SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1
7.     -＞         ELSE 0 END) July_rentals
8.     -＞ FROM rental
9.     -＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';

复制代码

1.4.3. 如果monthname()函数返回了所需的列值，case表达式返回1；否则，返回0

1.5. 检查存在性

1.5.1. 只希望确定两个实体之间是否存在某种关系，而并不关心数量

1.5.2. mysql

1. -＞ SELECT a.first_name, a.last_name,
2.     -＞   CASE
3.     -＞     WHEN EXISTS (SELECT 1 FROM film_actor fa
4.     -＞                    INNER JOIN film f ON fa.film_id = f.film_id
5.     -＞                  WHERE fa.actor_id = a.actor_id
6.     -＞                    AND f.rating = 'G') THEN 'Y'
7.     -＞     ELSE 'N'
8.     -＞   END g_actor,
9.     -＞   CASE
10.     -＞     WHEN EXISTS (SELECT 1 FROM film_actor fa
11.     -＞                    INNER JOIN film f ON fa.film_id = f.film_id
12.     -＞                  WHERE fa.actor_id = a.actor_id
13.     -＞                    AND f.rating = 'PG') THEN 'Y'
14.     -＞     ELSE 'N'
15.     -＞   END pg_actor,
16.     -＞   CASE
17.     -＞     WHEN EXISTS (SELECT 1 FROM film_actor fa
18.     -＞                    INNER JOIN film f ON fa.film_id = f.film_id
19.     -＞                  WHERE fa.actor_id = a.actor_id
20.     -＞                    AND f.rating = 'NC-17') THEN 'Y'
21.     -＞     ELSE 'N'
22.     -＞   END nc17_actor
23.     -＞ FROM actor a
24.     -＞ WHERE a.last_name LIKE 'S%' OR a.first_name LIKE 'S%';

复制代码

1.6. 除零错误

1.6.1. 执行涉及除法的运算时，应该始终注意确保分母不能为0

1.6.2. mysql

1. -＞ SELECT c.first_name, c.last_name,
2.     -＞   sum(p.amount) tot_payment_amt,
3.     -＞   count(p.amount) num_payments,
4.     -＞   sum(p.amount) /
5.     -＞     CASE WHEN count(p.amount) = 0 THEN 1
6.     -＞       ELSE count(p.amount)
7.     -＞     END avg_payment
8.     -＞ FROM customer c
9.     -＞   LEFT OUTER JOIN payment p
10.     -＞   ON c.customer_id = p.customer_id
11.     -＞ GROUP BY c.first_name, c.last_name;

复制代码

1.7. 条件更新

1.7.1. 在更新数据表中的行时，有时候需要根据条件逻辑生成列值

1.7.2. sql

1. UPDATE customer
2. SET active =
3.   CASE
4.     WHEN 90 ＜= (SELECT datediff(now(), max(rental_date))
5.                 FROM rental r
6.                 WHERE r.customer_id = customer.customer_id)
7.     THEN 0
8.    ELSE 1
9. END
10. WHERE active = 1;

复制代码

1.8. 处理null值

1.8.1. null是在列值未知时存储在数据表中的值，但是在检索时显示null值或者null作为表达式的组成部分时未必总是合适

1.8.2. sql

1. SELECT c.first_name, c.last_name,
2.   CASE
3.     WHEN a.address IS NULL THEN 'Unknown'
4.     ELSE a.address
5.   END address,
6.   CASE
7.     WHEN ct.city IS NULL THEN 'Unknown'
8.     ELSE ct.city
9.   END city,
10.   CASE
11.     WHEN cn.country IS NULL THEN 'Unknown'
12.     ELSE cn.country
13.   END country
14. FROM customer c
15.   LEFT OUTER JOIN address a
16.   ON c.address_id = a.address_id
17.   LEFT OUTER JOIN city ct
18.   ON a.city_id = ct.city_id
19.   LEFT OUTER JOIN country cn
20.   ON ct.country_id = cn.country_id;

复制代码

2. 锁定

2.1. 锁是数据库服务器用来控制数据资源被同时使用的一种机制

2.2. 当部分数据库被锁定时，任何打算修改（也可能是读取）相应数据的用户必须等到锁被释放

2.3. 数据库的写操作必须向服务器发出请求并获得写入锁才能修改数据，而读操作必须发出请求并获得读取锁才能查询数据

2.3.1. 数据库的写操作必须向服务器发出请求并获得写入锁才能修改数据，而读操作必须发出请求并获得读取锁才能查询数据

2.3.2. 在有较多并发读写请求时等待时间过长

2.3.3. Microsoft SQL Server采用

2.3.4. 反映服务器为报表应用程序发放读取锁时的数据库状态

2.4. 数据库的写操作必须向服务器发出请求并获得写入锁才能修改数据，而读操作不需要任何类型的锁就可以查询数据，服务器要确保从查询开始到结束期间读操作读取到的是一致的数据视图

2.4.1. 版本控制

2.4.2. 在修改数据时存在耗时查询

2.4.3. Oracle Database采用

2.4.4. 反映报表开始生成时的数据库状态

2.5. MySQL则两者皆用，取决于存储引擎的选择

2.6. 锁的粒度

2.6.1. 数据表锁

2.6.1.1. 避免多个用户同时修改同一数据表中的数据

2.6.1.2. 锁定整个数据表只需要很少的簿记（bookkeeping）

2.6.1.3. 随着用户量的增加，该方法会迅速产生无法接受的等待时间

2.6.2. 页锁

2.6.2.1. 避免多个用户同时修改某数据表中同一页的数据

2.6.2.2. 一页通常是大小为2～16KB的内存段

2.6.3. 行锁

2.6.3.1. 避免多个用户同时修改某数据表中同一行的数据

2.6.3.2. 需要更多的簿记

2.6.3.3. 允许多个用户修改同一数据表，前提是被处理的是不同的行

2.6.3.4. Oracle Database只有行锁

2.6.3.5. Oracle Database不能提升锁

2.6.4. 锁选择

2.6.4.1. Microsoft SQL Server采用数据表锁、页锁和行锁

2.6.4.2. SQL Server可以将锁从行提升至页，再从页提升至数据表

2.6.4.3. MySQL则采用数据表锁、页锁或行锁（同样取决于存储引擎的选择）

3. 事务

3.1. 应用程序逻辑通常包含多个需要作为逻辑工作单元共同执行的SQL语句

3.2. 语句作为整体，要么执行成功，要么执行失败

3.2.1. 原子性

3.3. 数据库服务器上线前必须完成的任务之一就是查找服务器宕机前正在运行但未完成的事务并将其回滚。

3.4. 如果程序完成了事务并发出了commit命令，但还没有将变更应用于永久性存储（修改的数据仍然位于内存，尚未被写入磁盘）时服务器就宕机了，那么当服务器重启时，数据库服务器必须重新应用来自事务的变更

3.4.1. 持久性

3.5. 启动事务

3.5.1. 一个活跃的事务总是和数据库会话相关联，所以没有必要也没有方法显式地开始一个事务。当前事务结束后，服务器会自动为会话启动一个新的事务

3.5.1.1. Oracle Database采用

3.5.1.2. 哪怕只发出了一个SQL命令，如果对结果不满意或是改变了主意，也能够回滚变更

3.5.2. 除非显式地开始一个事务，否则各个SQL语句会被自动提交，彼此独立。要想启动事务，必须先发出一个命令

3.5.2.1. Microsoft SQL Server和MySQL采用

3.5.2.2. 一旦你按下Enter键，你的SQL语句所带来的变更将是永久性的

3.5.2.3. SQL:2003标准提供了start transaction命令，可用于显式地启动事务

3.5.2.3.1. MySQL遵守该标准

3.5.2.3.2. SQL Server用户必须使用替代命令begin transaction

3.5.2.4. MySQL允许关闭单个会话的自动提交模式

3.5.2.4.1. SET AUTOCOMMIT=0

3.5.2.5. SQL Server都允许关闭单个会话的自动提交模式

3.5.2.5.1. SET IMPLICIT_TRANSACTIONS ON

3.5.2.6. 建议每次登录时关闭自动提交模式，养成在事务内运行所有SQL语句的习惯

3.6. 结束事务

3.6.1. 必须显式地结束事务，可以通过commit命令来实现

3.6.1.1. 指示服务器将此变更标记为永久性的并释放事务中使用的任何资源（页锁或者行锁）

3.6.2. 如果打算撤销自事务启动后所做的所有变更，必须发出rollback命令

3.6.2.1. 指示服务器将数据恢复到事务之前的状态

3.6.2.2. rollback命令完成后，会话所使用的资源全都会被释放

3.6.3. 导致事务结束的其他场景

3.6.3.1. 服务器宕机，在这种情况下，事务会在服务器重启时被自动回滚

3.6.3.2. 发出SQL模式语句

3.6.3.2.1. 会使当前事务被提交并启动一个新的事务

3.6.3.2.2. 数据库的更改，无论是增加一个新数据表或新索引，还是删除数据表中的一列，都无法被回滚，因此，更改模式的命令必须出现在事务之外

3.6.3.2.3. 应该注意保护那些组成一个工作单元的语句不被服务器意外地拆分成多个事务

3.6.3.2.4. 服务器不会通知你到底发生了什么

3.6.3.3. 发出另一个start transaction命令，这会造成前一个事务被提交

3.6.3.4. 如果服务器检测到死锁并认定当前事务就是导致死锁的根源，服务器就会提前结束当前事务

3.6.3.4.1. 在这种情况下，该事务会被回滚，同时也会接收到错误消息

3.6.3.4.2. 如果检测到死锁，就要选择一个事务（任意选择或者根据某种标准）回滚，以便让其他事务得以继续

3.6.3.4.3. 被终止的事务可以重启，如果没有再遇到死锁，就可以顺利完成

3.6.3.4.4. 数据库服务器会显示错误消息，告知你由于检测到死锁，该事务已经被回滚

3.6.3.4.5. 重新尝试由于检测到死锁而被回滚的事务是一种合理的做法

3.6.3.4.6. 如果死锁出现得过于频繁，那么可能需要对访问数据库的应用程序进行修改，以降低死锁的可能性

3.6.3.4.7. 一个常用的策略是确保始终按照同样的顺序访问数据资源，比如总是在插入交易数据之前修改账户数据

3.7. 事务保存点

3.7.1. 可以在事务内创建一个或多个保存点，利用其回滚到事务内的特定位置，而不必一路回滚到事务的启动点

3.7.2. MySQL数据库服务器被设计成可以用多个存储引擎提供低层级数据库功能，包括资源锁定和事务管理

3.7.3. MySQL 8.0版

3.7.3.1. MyISAM

3.7.3.1.1. 采用数据表锁定的非事务引擎

3.7.3.2. MEMORY

3.7.3.2.1. 用于内存数据表（in-memory table）的非事务引擎

3.7.3.3. CSV

3.7.3.3.1. 在逗号分隔的文件（comma-separated file）中存储数据的事务引擎

3.7.3.4. InnoDB

3.7.3.4.1. 采用行级锁定的事务引擎

3.7.3.5. Merge

3.7.3.5.1. 使多个相同的MyISAM数据表以单表形式出现（也称为表分区）的专用引擎

3.7.3.6. Archive

3.7.3.6.1. 用于存储大量未索引数据的专用引擎，多作为归档之用

3.7.3.7. 允许以数据表为单位来选择存储引擎

3.7.4. 如果不知道数据表使用的是什么引擎，可以使用show table命令

3.7.5. sql

1. START TRANSACTION;
2. UPDATE product
3. SET date_retired = CURRENT_TIMESTAMP()
4. WHERE product_cd = 'XYZ';
5. SAVEPOINT before_close_accounts;
6. UPDATE account
7. SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
8.   last_activity_date = CURRENT_TIMESTAMP()
9. WHERE product_cd = 'XYZ';
10. ROLLBACK TO SAVEPOINT before_close_accounts;
11. COMMIT;

复制代码

3.7.6. 创建保存点时，除了名称，什么都没有保存。要想使事务具有持久性，最终必须发出commit命令

3.7.6.1. SAVEPOINT my_savepoint;

3.7.7. 如果发出rollback命令的时候没有指定保存点名称，事务中的所有保存点都会被忽略，整个事务都会被撤销

3.7.7.1. ROLLBACK TO SAVEPOINT my_savepoint;

3.7.8. 如果使用的是SQL Server，需要使用专有命令save transaction创建一个保存点，使用rollback transaction命令回滚到某个保存点

3.7.8.1. 后跟保存点的名称


来源:https://www.cnblogs.com/lying7/p/17674359.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！