SQL语句速查
基础名词
- 数据库： 保存有组织数据的容器，一般是一个或一组文件
- 表： 表在数据库中，用来进行分类存储各式各样的信息。如果把数据库看为一个柜子，那么表就是一个个抽屉，里面装着一些文件。表在实际应用中可以是顾客清单，产品目录等。
- 列： 表是由列和行构成。例如一个城市信息表，它的列可以使地址，城市，州，邮政编码等等。
- 行： 行是表中的一个记录。例如一个学生信息表，一个行包含一个学生的名字、年龄、性别等信息。
- 主键： 是一列，这一列可以唯一的标识每一个行。例如身份证号可以唯一的标识每一个人，而姓名不可以。因此身份证号可以作为主键。
- 外键： 将这个表中的某一列和另外一个表中的列关联起来。如果另一个表的对应列中不存在那个数据那么这个表中就无法插入这个数据。外键不是键
- 注释： 有
--、#、/*...*/三种，注意第一种后面要有一个空格
USE和SHOW
- USE 数据库名：
- 使用某个数据库
- SHOW DATABASES：
- 返回可用数据库的列表
- SHOW TABLES：
- 展示数据库内列表的信息
- SHOW COLUMNS FROM 列表名：
- DESCRIBE 列表名作用与它相同
- 展示数据列（表头）的信息.下面是一个输出样例
- SHOW STATUS：
- 用于显示服务器状态信息
- SHOW CREATE DATABASE和 SHOW CREATE TABLE
- 分别用来显示创建特定数据库或表
- SHOW GRANTS：
- 用来显示用户的安全权限
- SHOW ERRORS和SHOW WARNINGS：
- 用来显示服务器错误或警告信息
SELECT
- SELECT 列名 FROM 表名：
- 展示某个表中某一列的详细信息
- 列名可以有多个，同时检索多个列
- 列名前可以添加表名，表名前可以添加数据库名进行限定。
- 列名可以用
*代替来检索所有列 - 可以通过DISTINCT来排除相同的行（这时不能同时检索多个列）。例如
SELECT DISTINCT order_num FROM orderitems;
- LIMIT子句可用于限制结果行数。例如 SELECT prod_name FROM products LIMIT 5;限制为5行。LIMIT 5, 5;只显示从第五行开始的五行。注意：下标也是从0开始。
ORDER BY子句（排序）
- ORDER BY + 要排序的列;
- 以升序进行排序。 例如
SELECT cust_address FROM customers ORDER BY cust_address; - 要排序的列可以有多个，这时先对第一个列进行排序，然后对第二个列进行排序。
- 可以在后面加上 DESC表明是降序排序。例如
SELECT cust_address, cust_name FROM customers ORDER BY cust_address DESC, cust_name;
- 以升序进行排序。 例如
where子句（过滤数据）
where子句挑选出满足限制条件的行。
例：SELECT prod_name, prod_price FROM products WHERE prod_price=2.50;
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | … |
| >= | … |
| BETWEEN | 在指定的两个值之间，还要加上AND，例如SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 1 AND 2.5; |
| IS NULL | 检测是否是空值,例如SELECT cust_id FROM customers WHERE cust_email IS NULL |
| ANY | 任一， 可以与< , > , = 进行组合。例如 >ANY |
| ALL | 所有， 和上面类似，例如 <= ALL |
| EXISTS | 存在，后面可以接子查询，只要在后面这个范围内返回为真 |
进行比较的数据也可以是char型。例如：
SELECT prod_name, prod_price FROM products WHERE prod_name = 'Fuses'; |
MySQL默认是不区分大小写的，但是有的软件会帮助区分。
组合操作符（AND,OR， IN， NOT）
AND
两个条件同时发生才为真。和c语言的&&作用类似。
例如：SELECT vend_id, prod_price FROM products WHERE vend_id=1003 AND prod_price < 10;
OR
和上面一个相似
AND，OR组合
AND和OR都可以有无数个，并且这两个还可以组合使用。
例如：SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
输出
Fuses 3.42
Oil can 8.99
Detonator 13.00
Bird seed 10.00
Safe 50.00
TNT 10.00
上面的输出竟然有两个是小于10的，这是由于优先级导致的
AND的优先级比OR高，因此在写复杂表达式时最好用括号包围表达式
NOT
NOT的作用就是否定它之后的条件。可以对IN， BETWEEN和EXISTS子句取反。
例如：SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003);
IN：
IN和BETWEEN类似，都是用来表示条件的范围，例如：
SELECT prod_name, prod_price FROM products |
上面这两条语句的作用是相同的。
IN的优点如下：
- IN更方便、简洁；
- IN比一般的OR执行更快；
- IN中还可以包含其他SELECT语句。
例如：SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN (SELECT orders
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
子查询
- 第一种就是上面所示用的IN中使用子查询
- 第二种是在SELECT后就使用
例如：SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id)orders
FROM customers
ORDER BY cust_name;
或者select title
from Movies Old
where year <any
(
select year
from Movies
where title = Old.title
);
通配符与正则表达式
想要使用通配符，必须要使用LIKE操作符。LIKE后面加上搜索模式进行匹配。
| 通配符 | 含义 |
|---|---|
| % | 匹配任意字符任意次数 |
| _ | 匹配任意一个字符 |
例如：select cust_address FROM customers WHERE cust_address LIKE "%Lane";
使用正则表达式只需要将LIKE换成REGEXP即可。正则表达式语法可看这篇文章的正则表达式部分
拼接字段
可以用Concat函数拼接不同的字段。例如：Concat(vend_name, '(', vend_country, ')')由四部分拼接而成。
有时候需要删除右侧多余的空格，可以使用RTrim(str)。如果想删除左侧空格或者两侧空格可以使用LTrim或Trim.
例：
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ') ') FROM vendors ORDER BY vend_name;
结果输出： ACME (USA)
可以使用AS关键字为拼接后的字段赋予别名。
例：SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ') ') AS vend_title FROM vendors ORDER BY vend_name;
输出vend_title
ACME (USA)
Anvils R Us (USA)
Furball Inc. (USA)
Jet Set (England)
Jouets Et Ours (France)
LT Supplies (USA)
除了让Concat结果赋予名字外，还可以对算术计算结果赋予名字。算术计算支持加减乘除。
例如：quantity * item_price AS expanded_price
组合查询
UNION是将几个SELECT的输出结合到一起。
例如：SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
等同于：
SELECT vend_id, prod_id, prod_pric
FROM products
WHERE prod_pric <= 5 OR vend_id IN (1001, 1002);
这个例子中似乎直接用OR更为方便。但是如果在复杂的表达式或者查询两个表的时候可能会更加清楚
except和union用法相同，union是结果的并集，而except是差集，intersect是交集
例如：(select name, address from MovieStar)
except
(select name, address from MovieExec)
这里是挑选出不是制片人的电影明星
(select name, address from MovieStar where gender = 'f')
intersect
(select name, address from MovieExec where netWorth > 10000000);
这里是 挑选出性别是nv的明星并且她是制片人并且她的财富大于10000000
注意：
- 每两个SELECT之间都要有UNION
- 每个SELECT查询的内容必须相同
- UNION查询会自动去除重复的行。如果使用UNION ALL的话就可以不去除重复的行。
- 排序（ORDER BY）必须要在最后一个SELECT语句之后
SELECT子句的顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 输出要返回的表达式 | 是 |
| FROM | 从中检索数据的表 | 在要从表中选择数据时使用 |
| WHERE | 过滤 | 否 |
| GROUP BY | 分组 | 否 |
| HAVING | 过滤 | 否 |
| ORDER BY | 排序 | 否 |
| LIMIT | 限制检索行数 | 否 |
函数
类型
- boolean: 他不是两值而是三值的，除了true和false外还有unknown。当NULL值和其他值进行比较时得到的值就是unknown。unknown值可以看成1/2
- 当unknown和其他值相与时，取最小的值。也就是说Unknown和false相与是false，和true相与是unknown
- 当unknown和其他值相或时，取最大的值。unknown和true相或得true，和false相或得unknown
- unknown取非还是unknown
- TINYINT： 一字节整数
- smallint： 2字节
- int：
- bigint： 8字节
- float： 4字节
- double
- decimal(a, b): a表示小数点左边位数，最大为38.b是小数点右边允许位数，范围在0到a之间。
文本处理函数
| 函数 | 说明 |
|---|---|
| Left(str, len) | 返回串左边的字符 |
| Length(str) | 返回串的长度 |
| Locate(substr, str, begin_len) | 找出一个串的子串 |
| Lower(str) | 将串变成小写 |
| LTrim(str) | 去掉左边的空格 |
| Right(str, len) | 返回串右边的字符 |
| RTrim(str) | 去掉串右边的空格 |
| Soundex(str) | 返回串的SOUNDEX值 |
| SubString(str, pos, len) | 返回子串的字符 |
| Upper(str) | 将串变成大写 |
其中SOUNDEX是一个将字符串转化成按照语音表示的数字的算法。
例如：Y.lee和Y.lie虽然拼写不同但是读起来相同，这样他们的Soundex是相同的。
日期和时间处理函数
日期的格式是yyyy-mm-dd。例如2005-11-9.并且最好使用四位数的年份。
时间的格式是 hh:mm:ss
| 函数 | 说明 |
|---|---|
| AddDate(date, INTERVAL sum unit) | 增加一个日期 |
| AddTime() | 增加一个时间 |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date(expr) | 返回日期的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 更复杂的日期增加 |
| Date_Format() | 返回一个格式化的日期或时间 |
| Day() | 返回一个日期中的天数部分 |
| DayOfWeek() | 返回星期几 |
| Hour() | 返回时间的小时部分 |
| Minute() | 返回分钟 |
| Month() | … |
| Now() | 返回当前的日期和时间 |
| Second() | … |
| Time | 返回一个日期和时间的时间部分 |
| Year() | … |
例如 ：SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
INTERVAL是经过的意思，也就是过31天。后面也可以加MONTH，YEAR，HOUR等。
expr就是标准的日期时间或只有日期。要注意日期都是字符串，都要加引号。
上面这些函数在where语句后使用容易出现问题，他们适合的位置是在select后做汇总函数。如果在where中选择一段时间可以使用where date between '...' and '...'
数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回一个数的绝对值 |
| Cos() | … |
| Exp() | 返回一个数的指数 |
| Mod() | 返回一个数的余数，相当于 % |
| Pi() | 返回圆周率 |
| Rand() | 返回随机数 |
| Sin() | … |
| Sqrt() | 返回平方根 |
| Tan() | 返回一个数的正切值 |
汇总数据函数
| 函数 | 说明 |
|---|---|
| AVG(column) | 返回某列的平均值 |
| COUNT() | 返回某列的行数, COUNT(*)返回行数，无论是否有NULL行。而COUNT(column)忽略NULL行 |
| MAX(column) | 返回某列的最大值,如果是文本列，那么会返回最后一行 |
| MIN() | … |
| SUM() | 返回某列的和 |
AVG()
例：SELECT AVG(prod_price) as avg_price FROM products;
输出：
avg_price
16.133571
AVG()函数的参数只能是一行，不能是多行，也就是说如果想要分别求多行的平均值，就要用多个AVG
可以使用DISTINCT排除相同数据，如AVG(DISTINCT prod_price)
分组数据
GROUP BY子句可用于分组。
例如： SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id
- GROUP BY子句可以包含任意数目的列。但是指定了多个列，那么这些列同时进行计算，也就是说只有这几个列都匹配才可以算成一组。
- 如果列中有NULL，那么NULL会单独分成一组。
- ORDER BY要卸载GROUP BY的后面
过滤分组
过滤分组也就是挑选需要的组。关键字是HAVING。它的作用和WHERE相同，它后面接的语法也相同。
例如：SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
联结
连接实际上是对二者的笛卡尔积进行一些选择。我们可以使用cross join表示笛卡尔积，他会自动匹配列名相同的进行连接。例如：
SELECT customers.Name, orders.Order_No |
例如供应商表中存在一个唯一的供应商id，而产品表中只会存放这唯一的ID（为了节省空间），那么如果想找供应商供应那些商品首先需要从产品表中找到ID再在供应商表中找到名字。
使用联结就可以一次性输出供应商名字和产品信息。SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
上面这个式子的关键在于WHERE子句。通过筛选两个表中相同的vend_id来输出。
如果不进行限定的话将会对每一个供应商名字输出所有产品信息。
内部联结（INNER JOIN）
SELECT vend_name, prod_name, prod_price |
这条语句作用和上面相同，并且INNER可以算作FROM的组成部分，使用这种语法时，后面限定条件使用ON而不是WHERE（但实际作用相同）。
自联结
例如某见物品（ID=DTNTR）有问题，因此想知道生产该物品的供应商生产的其他物品是否有问题。这个查询首先要找到生产ID是DTNTR的物品的供应商，然后找到这个供应商生产的其他物品。
SELECT prod_id, prod_name |
自联结使用子查询代替联结。这样可以避免列名重复而导致的一些问题，但是有时候处理联结比处理查询快很多，所以要综合比较。
例如：departments(pk_departments, dept_name)和dept_emp(fk_dept_emp_employees, fk_dept_emp_departments, from_date, to_date)进行联结
select * from departments |
结果为
自然联结
标准的联结会返回所有数据，甚至完全相同的列会多次出现。而自然联结就是为了避免多次出现。自然联结通过对表使用通配符（*），对其他所有表使用明确的子集来完成。
例如：SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';
在这个例子中，通配符只对第一个表使用，其他列都明确给出。
我们也可以使用natural join进行自然连接。natural join就是找名字相同的列进行连接,并且名字相同的列只会出现一个。例如：SELECT customers.Name, orders.Order_No
FROM customers
NATURAL JOIN orders;
自然联结会把名字相同的列合并alter table dept_emp rename fk_dept_emp_departments to pk_departments ;//改名
select * from departments natural join dept_emp;
外部联结
外部联结指的是即使相关表没有关联行也进行输出。
SELECT customers.cust_id, orders.order_num |
上面的orders表中没有10002，但是输出却包含了10002.它是以左边的customer为基准的，在右边的表中寻找是否与左边的表有关联，有关联输出对应值，没有关联输出null。当然如果有右边有多个匹配还是输出多个的。
他和普通的连接的最大区别是左边一定全部输出，如果右边没有就输出为空。
如果是RIGHT OUTER JOIN，则不会输出10002.
数据修改
插入（INSERT）
数据插入的关键字是INSERT。它可以插入完整的行，也可以插入行的一部分，还可以插入查询结果。
例:INSERT INTO customers
VALUES{NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
它通过VALUES给出插入一行的信息。另外可以指定插入列是操作更为安全INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contract,
cust_email)
VALUES('Pep E. LaPew',
'100 Main Street',
...
这种方式更为清楚，但是也更加麻烦。但是这样可以更改插入信息的顺序。
插入多行
INSERT INTO customers(cust_name, |
插入检索出来的数据
INSERT INTO customers(cust_id, |
只需要使用select而不需要再放入VALUES中了。
在FROM中也可以使用子查询，一般都是
some values in(...)
更新和删除
更新数据关键字是UPDATE。
例如：UPDATE customers
SET cust_email = 'elmer@udd.com'
WHERE cust_id = 10005;
UPDATE后接要更新的表。SET接更新的列。WHERE用来限制要更新那些行，如果没有WHERE，那么会更新所有行。
SET后面可以使用子查询来更新列数据
如果想删除某个列的某些值，可以把它设成NULL。例如UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
删除数据使用DELETE
例如：DELETE FROM customers
WHERE cust_id = 10006;
这是删除一行的方法。
更新和删除中同样可以使用子查询，例如delete from Classes
where class in
(
select class
from Classes, Ships
where Clsses.class = Ships.class
and Classes.type = '军舰'
group by Classes.class
having count(*) >= 3
);
创建和使用表
创建
使用CREATE TABLE创建表。创建表至少需要给出表的名字和列名及列的类型等信息。
例如：CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
- NOT NULL： NOT NULL表示插入或更新行时，这一列必须有值。
- AUTO_INCREMENT: 每插入一行这个列的值加1.每个表只能允许有一个AUTO_INCREMENT的列，而且必须让他被索引。
- DEFAULT： 默认填充的值。
- 引擎：引擎是具体处理语句的函数。不同表之间引擎类型可以混用，但是外键不能跨引擎。
- InnoDB： 它不支持全文搜索， 但支持事务处理
- MEMORY： 数据储存在内存中，速度比较快，适合临时表
- MyISAM： 支持全文搜索，不支持事务处理。
主键和外键
表的主键可以在创建表时定义，使用PRIMARY KEY关键字。主键一定不是NULL，如果由多个列共同构成主键，那么每一列都不是NULL。
此外，还可以使用UNIQUE KEY关键字，他和PRIMARY KEY的区别是它可以定义NULL，并且可以多列同时使用UNIQUE KEY，而不是像PRIMARY KEY那样想要多列使用必须在最后声明
外键
例如：CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
PRIMARY KEY (cust_id),
FOREIGN KEY (cust_id) REFERENCES orders(order_id)
) ENGINE=InnoDB;
外键是用来对表的插入删除更新等操作进行约束的，外键所引用的列必须是主键，基本思想为另一个表中没有就不能插入，外键不是键。并且由于外键不是键，因此外键可以是NULL
| 操作 | 外键定义表 | 外键引用表 |
|---|---|---|
| Delete | 允许 | ？ |
| Insert | 如果引用表存在则成功 | 允许 |
| Update | ？ | ？ |
?是因为对于不同的模式有不同的选择。
- 默认： ？的操作都不允许
- Cascade： 在父表上update/delete时，子表同步update/delete
- Set null: 父表由变更时，把子表的外键列变成NULL
使用： on [update, delete] [set null, cascade]
例如：CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
PRIMARY KEY (cust_id),
FOREIGN KEY (cust_id) REFERENCES orders(order_id)
ON UPDATE SET NULL
ON DELETE CASCADE
) ENGINE=InnoDB;
现在考虑一些微妙的情况
一般情况下如果我想插入一条记录并且主表中不存在。那么我可以选择首先在主表中插入然后再在这个表中插入。
但是如果双方同时定义了外键并且把对方当做主表，这时就可能会陷入死循环。一个解决方法是首先让一个表的外键字段设为NULL，然后另一个表插入，然后再让开始那个表将NULL值修改。这种方法要求外键字段没有NOT NULL
约束和断言
primary key和not null，foreigin key等都是约束。可以使用constraint对这些约束进行命名，方便后面的操作。
例如：CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
constraint name_is_key PRIMARY KEY (cust_id),
FOREIGN KEY (cust_id) REFERENCES orders(order_id)
ON UPDATE SET NULL
ON DELETE CASCADE
) ENGINE=InnoDB;
check
check约束可以在插入更新时进行检查，如果符合条件就进行插入create table moviestart
(
gender char(1) check(gender in('F', 'M'))
);
它也可以写在最后面，这时就可以同时对多个列进行判断了。
写在最后会带来性能的负担，因为原来只会在gender列修改时才会触判断，现在所有列修改时都会触发判断
defer
constraint可以延时执行，这时需要deferrable关键字。
总共可以划分成三种情况：
- not deferrable（默认）
- deferrable initally immediate(可以延时初始设置为即时）
- deferrable initally deferred
延时提交指的是在插入更新删除的时候不触发约束检查。但是在最后提交(commit)的时候进行检查。
也就是说如果将primary key设置成deferrable，那么我们可以插入重复的key，但是键入commit命令时会报错。
例如：create table moviestart
(
gender char(1) constraint check_gender check(gender in('F', 'M')) deferrable initially immediate
);
set constraints check_gender deferred; //设置为延时
assert
assert也是用于检查更新和修改，但是它作用于所有表
定义： create assertion name check(condition)
它比表内的check要慢的多，因为每一个表的更新都会触发它，因此使用assert要谨慎。
更新
使用ALTER TABLE更新表。
- ADD： 添加一列
- DROP COLUMN： 删除一列
ALTER TABLE vendors |
它还可以用来定义外键ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num)
REFERENCES orders (order_num);
其中fk_orderitems_orders是外键约束的名字， 前面一个order_num是在orderitems中的名字。
删除表使用DROP TABLE. 例如DROP TABLE customers;
重命名表使用RENAME TABLE。例如RENAME TABLE customer2 TO customer
视图
视图是一个虚拟的表，视图可以选择各个表的一部分整合成一个表，这样就方便我们查询。视图本身并没有数据，它只是记录了一些表的信息。
视图的限制：
- 视图表和真实表一样需要我们创建并且需要唯一命名
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用，也就是说可以写一条联结表和视图的SELECT
- ORDER BY可以用于视图中
视图使用
- CREATE VIEW： 创建视图
- SHOW CREATE VIEW viewname： 查看创建的视图
- DROP VIEW; 删除视图
- 更新视图时可以先DROP再CREATE.
例如：CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
SELECT * FROM productcustomers;
这是将一个复杂的联结变成一个视图，之后需要这个联结就可以直接使用这个视图。
视图其实就可以看成一个sql语句，它并不会创建一个新的表，也不会在存储器中占据空间，他相当于在查询时帮我们添加了一些条件。
例如：create view paraMovie as
select title, year
from Movie
where studioname = 'Paramount';
select title
from paraMovie where year = 1979;
等同于
select title
from Movie
where studioname = 'Paramount' and year = 1979;
或者是
select title
from
(
select title, year
from Movie
where studioname = 'Paramount'
)paraMovie
where year = 1979;
甚至查询速度其实也没有什么变化。那么视图有什么作用呢？
首先他和函数类似，减少了我们写语句的数量，否则每进行一次查询都要加上studioname = 'Paramount'过于繁琐。
也就是说create view ... as
statement1
select ...
from ...
where statement2
等同于
select ...
from ...
where statement1 and statement2
视图更新
绝大多数视图都不可以更新，只有一些简单的视图可以更新。
可更新视图的条件为：
- select只允许简单的列名1, 列名2… 中间不允许进行更改表名或进行加减操作
- from中只允许出现一个表名
- where中子查询不允许使用关系R
在视图上修改其实就是对基表进行修改，因为视图实际上就是sql语句。
删除时会直接把那一列全部删除
插入时只能对视图中的列进行赋值，而不在视图中的列要么被赋默认值要么为空值。因此会导致一些微妙的情况。
例如我现在有一个表Score(ID, institute(学院), score(成绩))。然后创建一个视图create view CS_score as
select ID, score
from Score
where institute = 'CS'
之后进行插入
insert into CS_score values
(
'10001',
'93'
);
然后实际上在Score中存储为('10001', NULL, '93')
也就是说在插入之后他却在CS_score视图中消失了,这显然不符合常理
为了解决上述问题，一种方法时创建视图时加上institute列，但是毫无疑问这一列全是CS，虽然视图只是sql语句不会真实存储但是感觉还是很奇怪。尤其是在创建物化视图的时候这将是一个大问题。
为了解决这个问题，我们可以用一种特殊的触发器instead of来替换before或after。如果这么做，当事件唤醒触发器时，触发器的操作将取代事件本身执行。例如上例我们可以这样做create trigger score_insert
instead of insert on CS_score
referencing new row as New
for each row
insert into Score(ID, institute, score)
values(New.ID, 'CS', New.score)
物化视图
物化视图就是创建一个真实的表了，如果需要频繁使用视图可以尝试使用物化视图，它减少了视图需要的时间。例如create materialized view Movie_period as
select title, year, name
from Movies, MovieExec
where producerC# = cert#
但是如果基表更新的话物化视图也需要随之更新，如果创建的物化视图过多那么负载就太高了。有三种更新物化视图的方式
- recompute: 每次更新都更新物化视图
- incremental maintenance: 不更新
- Periodic maintenance: 定时更新。例如销售额数据，可以每天晚上汇总更新一次即可。
利用物化视图重写查询
假如物化视图为 |
满足以下条件可以用v替换q
- rv中的关系都在rq中出现
- cq和cv + c等价
- 如果存在c，则c中出现的rv中属性必定在lv中出现
- lq中属于rv的关系必定在lv中出现
替换过程为：
- 用v替换rq中属于rv的属性
- 用cv+c替换cq
存储过程
存储过程就是为以后使用而保存的一条或多条语句的集合（也就是函数或批处理文件）
创建
例：CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
使用CREATE PROCEDURE进行存储过程的定义，用BEGIN和END限定存储过程的范围。
也可以是：create function function_name([parameter])
returns type
如
create function a(id int)
returns varchar(300)
begin
return (select concat('employee name', employee_name, '---', 'salary:', employee_salary) from employees where employee_id = id;
end
create function就是创建带有返回值的存储过程，这样我们就可以直接使用SELECT了。
带有参数的存储过程CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8, 2),
OUT ph DECIMAL(8, 2),
OUT pa DECIMAL(8, 2)
)
BEGIN
SELECT Min(prod_price)
INTO p1
FROM products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
- OUT代表这个参数是作为输出数据使用（返回值）
- IN代表作为输入数据使用
- INOUT代表既作为输入又作为输出。
- INTO： 将这个值放入输出数据
使用
例如：CALL productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @priceaverage;
MySQL变量都必须要以@开始，但是如果是输入数据或输入输出不需要@
删除
DROP PROCEDURE productpricing;
只有这个过程存在时才可以删除，如果不存在会报错。因此可以使用DROP PROCEDURE IF EXISTS来提前检查
局部变量和类型限制
例：CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax`
BEGIN
DECLARE total DECIMAL(8, 2);
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
set textrate = 6;
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END;
首先参数可以限定类型, DECIMAL是限定范围的浮点型。
- DECLARE定义局部变量。
- IF由于检测条件是否为真
- COMMENT： 它不是必须的，使用后会在SHOW PROCEDURE STATUS中显示
SHOW CREATE PROCEDURE ordertotal;可以用来输出存储过程的一些信息，例如何时创建，谁创建等信息。- return: 这里的return和c语言不同，这里不会真正返回而是继续向下执行
- set: 设置变量的值，可以使用查询，但是查询结果必须是一个一行一列的值
- select into: 这个可以一次导入一行的值
if分支的大致过程为：if condition then
statements
elseif condition then
statements
else
statements
end if;
循环结构大致过程为loop name: loop
statements
end loop;
如果想退出循环
leave loop name;
其他的循环结构
for <loop name> as <cursor name> cursor for
query
do
statements
end for;
while conditions do
statements
end while;
repeat
statements
until condition
end repeat;
例如：
create procedure MeanVar
(
In s char(15),
OUT mean real,
OUT variance REAL
)
declare Not_Found condition for sqlstate '02000';
declarea MovieCursor cursor for
select length from Movies Where studioName = s;
declare new Length integer;
declare movieCount integer;
begin
set mean = 0.0;
set variance = 0.0;
set movieCount = 0;
open MovieCursor;
movieLoop: LOOP
fetch from MovieCursor into newLength;
if Not_Found then leave movieLoop end if;//退出循环，当游标到了最后一个后就会not_found
set movieCount = movieCount + 1;
set mean = mean + newLength;
set variance = variance + newLength + newLength;
end loop;
set mean = mean / movieCount;
set variance = variance / movieCount - mean * mean;
close MovieCursor;
end
//使用for循环
create procedure MeanVar
(
IN s CHAR(15),
OUT mean REAL,
OUT variance REAL
)
declare movieCount integer;
begin
set mean = 0.0;
set variance = 0.0;
set movieCount = 0;
for movieLoop as MovieCursor cursor fro
select length from Movies where studioName = s;
//上面的语句会被游标使用，一次扫描一行直到末尾
do
set movieCount = movieCount + 1;
set mean = mean + length;
set variance = variance + length * length;
end for;
set mean = mean / movieCount;
set variance = variance / movieCount - mean * mean;
end
异常处理
定义： declare <where to go> handler for <condition> <statement>
where to go 有三种：
- continue： 继续执行
- exit: 跳出程序
- undo: 取消这个事务所有已经更改的内容（回滚）
这三种都是先执行完handler中定义的代码再决定下一步
例如：create function get_year(t varchar(255)) returns integer
declare Not-Found condition for sqlstate '02000';#相当于文件结束符
declare Too-Many condition for sqlstate '21000';#基数违例
begin
declare exit handler for Not-Found, Too-Many
return null;
return (select year from Movie where title = t);
end
游标
游标相当于一个指针，可以根据需要上下移动查找数据
创建游标
使用DECLARE …CURSOR 创建。例如：
CREATE PROCEDURE processorders() |
- OPEN + name: 打开游标
- CLOSE + name： 关闭游标
使用游标
CREATE PROCEDURE processorders() |
FETCH INTO 是将数据放入局部变量中，其中REPEAT和UNTIL相当于do_while循环。而DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;用于设置终止条件，指出当SQLSTATE '02000'出现时，SET done=1.
索引
索引是一种特殊的查询结构，它通过hash或者b树对若干列进行组织从而提高查询速度。他可以使select更为高速。
索引其实是索引数据在磁盘中的位置，原来想要查找需要将所有块从磁盘中取出来，现在只需要取出索引块然后将根据索引块去除对应的磁盘块即可。
索引在数据量很大或者处理联结的时候是非常有效的。并且他对键或者是几乎没有重复的属性提升是最大的。假设索引建在性别上，那么最多也只可以筛选掉一半，剩下的一般还要遍历，与建立索引的代价来讲这是不值得的。选择哪个属性作为索引是DBMS决定的，不需要我们手动指定
- 创建索引：
create index index_name on 表名(列名) - 删除索引：
drop index index_name
虽然索引可以提高查询速度，但是并不建议所有表都使用索引，因为现在修改表的同时还要修改索引，也就减慢了update, insert, delete 的速度。
举个栗子说明索引对于速度的提升Q1：SELECT movieTitle, movieYear
FROM StarsIn
WHERE starName= S;
Q2: SELECT starName
FROM StarsIn
WHERE movieTitle= t AND movieYear= y;
I1：INSERT INTO StarsIn VALUES(t, y, s);
假设StarsIn在10个块中，并且一个明星出现在三部电影中，一部电影有三个明星主演
| Action | No Index | Star Index | Movie Index | Both Index |
|-|-|-|-|-|
| $Q_1$ | 10 | 4 | 10 | 4 |
| $Q_2$ | 10 |10 | 4 | 4 |
| I | 2 | 4 | 4| 6 |
4是因为一次取索引，然后三次从块中取数据。而插入需要两次是因为先要从磁盘中取出一个空块然后再写入磁盘。
触发器
触发器相当于一些编程语言中的event。当某些事件到来时触发这个函数。
例如： 当增加一个顾客到表中时，检查州的缩写是否为大写。
建立、删除
创建触发器时，要给出四个信息。
- 触发器名
- 触发器关联的表
- 触发器响应的活动（DELETE、INSERT或UPDATE）
- 触发器何时执行（处理前或处理后）
例：CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
这条语句的含义是对插入成功后的每一行（FOR EACH ROW）都输出一条Product added信息。
如果BEFORE触发器失败，MySQL将不执行之后的操作。如果是语句本身失败，MySQL将不进行AFTER触发器。
模板：create trigger [触发器名] after/before insert/update/delete [of 列名] on 表名
[referencing]
old row[table] as oldtuple,
new row[table] as newtuple
for each row[statement]
[when (触发条件)]
执行的语句
- of 列名： 这是仅对某些特定的列进行修改。并且只有update可以使用这个选项，因为insert和delete都是作用于整个元组
- referencing： 允许对暂时的表进行命名，不同的dbms有不同的语句，mysql直接使用new和old即可。old代表操作前的元组，new代表操作后的元组
- for each row: 代表一次只针对一个更新的元组。而for each statement表示一次针对一个操作中的所有元组。如果判断条件是平均值小于某个数的话那么可以其中某些行不满足也可以通过。
- when (触发条件): 表示在什么情况下才会激活触发器
- 执行语句： 执行的语句可以是一行也可以是多行，如果是多行需要使用begin-end括起来
例如：create trigger NetWorthTrigger
after update of netWorth on MovieExec
referecing
old row as OldTuple,
new row as NewTuple
for wach row
when(OldTuple.netWorth > NewTuple.netWorth)
update MovieExec
set netWorth = OldTuple.netWorth
where cert# = NewTuple.cert#;
删除触发器
DROP TRIGGER new products
触发器不能更新和覆盖，为了修改一个触发器，必须先删除它
事务处理
事物处理可以用来管理必须成批执行的操作（类似于线程或进程的屏障）。利用事务处理，要么都做，要么都不做，如果发生错误，则会进行回退。
例如想要在数据库中添加一个人的信息，如果添加过程中突然发生了某些故障导致接下来的步骤无法进行，那么就需要先前已经改变的数据进行恢复了。
- 事务： 一组SQL语句
- 回退： 撤销这些SQL语句
- 提交： 将未存储的结果写入数据库表
- 保留点： 事务处理设置的临时占位符，可以对它进行回退操作。
START TRANSACTION用来标识事务处理的开始。
事务处理的原理
计算机在运行过程中会出现许多奇奇怪怪的错误，例如事务故障（如死机）、系统崩溃（如断电）、磁盘故障等。我们如何确保遇到这些故障后数据库仍能保证数据可靠呢？
我们通常认为存到磁盘上的数据是可靠的，因此存到磁盘上也叫持久化。而在内存上的内容一般是不可靠的，因此需要采取一些机制来保证存到磁盘上数据的正确性。
假设一种理想情况，磁盘不会出错，语句写的很正确，操作系统也正常工作，那么这时就一定不会出现错误吗？
select max(price) from Sells where bar = 'Joe's Bar'; #从Joe的酒吧中找出价格最贵的酒，我们可以记为max |
就以上面四段代码为例，这四段代码从逻辑上没有任何问题，并且前两个是由John来控制的，他要时不时看看酒吧里酒的价钱，而后两句是Mike控制的，他要推广新的酒。
有这么一种可能
这时John觉得很奇怪，怎么最大值比最小值还小了。因此即使硬件条件玩好也会出现数据不一致问题
事务处理的特性
为了解决上面这些问题，便提出了事务处理，事务处理就是把多个数据修改的操作看成一个操作，统一进行管理，这样不仅可以解决同一个事务中数据变化的问题还方便掉电时数据的恢复。
acid原则主要是从这篇博客中学习的，里面还有一些很好的例子
事务处理需要基于acid原则，他们分别是
- atomic(原子性）： 事务处理只有两种状态，做了或没做。如果做的中间出现了某些错误需要回滚。
- consistency(一致性）： 也就是说一些数据约束在操作前和操作后都要满足。例如人不能大于200岁操作后仍不能大于两百岁，转账前两人钱的总和是1600，转账完成之后两个人的综合仍然是1600.
- isolated(隔离性）： 它的目的是让用户眼中一次只执行自己的操作，其他人的操作对他没有影响。为了实现这个目的，需要使用一些隔离手段，
- 读未提交：其他事务可以读取另一个未完成事务中的数据。
- 读提交： 一个事务要等另一个事务提交之后才可以读取数据。但是他不能阻止其他事务修改数据，修改数据可以不用读直接修改。
- 可重复读： 当一个事务开始之后，其他事务就不可以修改对应数据。但是他不能防止其他事务插入数据
- 序列化： 串行执行
- 除了串行执行外，其他三种多多少少有可能出现问题，需要根据具体情况进行调节
- Durable(持久性）： 事务提交完成之后，数据就被保存到磁盘中，可以认为数据被持久保存
并发控制
例如如图所示的两个事务,我们只需要保证A=B这个约束就可以随意修改，并且两个的初始值都是25。
如果是串行执行，则不会有冲突
但是如果是并行执行，并且中间的执行顺序犬牙交错，那么出错几率就非常大。
这个执行下来结果都是125，这样看似乎这样并行也是可以的，但是如果T2事务不是乘1而是乘2那么一个是250一个是125。
首先我们要检测是否会出现问题。当然串行化的一定没有问题，但是串行化的速度太慢，如果一个并行的指令的结果和串行相同，那么这种并行也是好的，称他为可串行化调度
例如
上图就是一种可串行化调度，虽然是并行但是实际上结果并不会受影响。
为了检测是否是可串行化程序，我们首先要定义一些符号：
- $r_i (x)$: 第i个事务读x
- $w_i (x)$: 第i个事务写x
之所以没有 i -> i*2对应的符号是因为他们不会影响内存，不会对可能导致的冲突产生影响，因此不把他考虑在内。
定义了上面的符号之后，这些操作就可以转换成一个符号序列了。例如$r_1 (A) w_1 (A) r_2 (A) w_2 (A) r_1 (B) w_1 (B) r_2 (B) w_2(B)$
我们最终的目标是要把它转化成一个串行化调度，也就是和上面例子类似的式子。
在这些序列中，有一些紧挨的序列交换位置是没有影响的，而另外一些是有影响的。
交换位置没有影响的类型：
- $r_i (x), r_j (y)$: 两个不同的事务读取两个不同的变量顺序变化是没有影响的，并且$r_i (x), w_j (y)$和$w_i (x), w_j (y)$即两个不同的事务操作两个不同的变量可以任意变换顺序
- $r_i (x), r_j (y)$： 两个不同的事务操作相同的变量也是允许的。
交换位置有影响的类型：
- $r_i (x), r_i (y)$: 相同的事务一定不可以改变顺序
- $r_i (x), w_i (y)$: 两个事务操作一个变量，只要有一个写就不能变换位置
根据这个我们就可以设计一个算法来判断是否是可串行化的了。
算法流程为：
- 初始化指针index=0，每次判断index和index+1是否是冲突序列，如果不是就把两个交换位置
- 将index+1重复上述过程，如果有交换位置就+2
- 每次扫描完成之后，如果有交换位置的继续扫描，如果没有则停止算法。停止算法时如果没有交叉即使可串行化算法
例如一个序列$r_1 (A); w_1 (A) r_2 (A) w_2(A) r_1(B) w_1(B) r_2(B) w_2(B)$
r1(A)和w1(A) 同一个事务，有冲突 |
但是这种算法的缺点是可能要循环很多次甚至可能导致死循环，并且它并不能保证可以判断出是可串行化序列。
另一种方法是优先图。和前面一种方法不同，这里更关注冲突的，冲突就代表不能更换次序，也就可以说明事务的先后次序。
它的判断条件是如果结果的图中无环则是可串行化序列
它的判断方法是选取操作的每一个变量看这个变量是否有冲突。例如以上面的序列为例，首先选取A变量
r1(A)和w1(A)是同一个事务，不需要考虑
w1(A)和r2(A)不是同一个事务并且有冲突，因此它确定了两个事务之间的顺序A->B
r2(A)和w2(A)是同一个事务，不考虑
A结束，发现A->B
选取B
r1(B)和w1(B)
w1(B)和r2(B)不是同一个事务且有冲突，因此他们确定了顺序A->B
...
总结为A->B
来看另一个例子w3(A) w2(C) r1(A) w1(B) r1(C) w2(A) r4(A) w4(D)
从A中我们可以得到3->1->2->4
从C中我们可以得到2->1
合起来为3->1<->2->4，有环因此不是可串行化序列
上面只是检测是否是冲突可串行化的方法，实际操作中我们不可能总是去检测是否是可串行化然后执行操作。为了解决数据冲突，我们可以使用锁进行管理。
首先定义几个符号
- $l_i (x)$: 第i个事务锁住x
- $u_i (x)$: 第i个事务解锁x
但是单纯的上锁解锁还是不能很好的解决约束，例如
它的约束是a = b。虽然每一步都进行了上锁解锁但是最后却没有保持约束。为了保持约束，人们开发出了二段锁
二段锁
二段锁的基本要求就是每一个事务加锁一定在解锁的前面。这也是二段的名字由来，一段全是加锁，一段全是解锁。
如果有多个变量需要操作那么二段锁会把这些变量全部加锁，然后执行完一个解锁一个
例如
这种情况下事务1在操作完A后不会立刻释放A的锁，而是首先把后面的锁全获得然后再释放A，这样提前获得就不会有其他事务中途插手而造成麻烦了，并且每执行完一个变量就可以释放这个锁。
但是这个锁还是会死锁并且无法避免。考虑这样一种情况。a事务获得A的锁，执行完成之后需要B的锁才会释放A的锁。与此同时b事务获得B的锁并且正好也需要A的锁。这样相互需要便导致了死锁
二段锁无疑会带来大量的资源浪费，尤其是当二者同时进行读操作时也不能共享，针对这个问题提出了一些改进措施。
一种方法是将锁分成共享锁和独占锁，也就是说有以下几种操作
- $sl_i (x)$: 共享锁
- $xl_i (x)$: 独占锁
- $u_i (x)$: 解锁
共享锁允许多个事务同时加共享锁来进行读事务。并且共享锁不能再加独占锁，也就是说必须等所有共享锁都解锁之后才可以加独占锁。
他们同样属于二段锁，需要遵循二段锁加锁在解锁前的限制。
对于上面一点又有一些改进措施，添加了可升级锁。可升级锁指的是可以在共享锁上直接加锁，加上可升级锁之后其他事务就无法操作这个变量了。
但是上述几种改进措施并没有解决死锁问题
死锁的解决方法
死锁的第一种解决方法是为每一个事务设定一个最大运行时间，超过这个时间强制截断。这种方法毫无疑问过于粗糙。
另外一个种方法是使用等待图，每个节点表示一个事务，一条边表示一个事务正在等待另一个事务的某些资源。如果出现环则表示死锁。可以每隔若干时间运行一次检测死锁。
发现死锁后，一种处理方法时直接杀死死锁代价最小的事务，让其撤销。
事务处理具体过程与日志
事务处理大致需要用到的几个函数
- input(): 将数据从磁盘读到内存中
- output(): 将数据写到磁盘中
- read(x, t): 如果有必要的话会执行input()。作用是把x赋给t，t是一个变量，在内存、寄存器或缓存中。
- write(x, t): 把t写给x
例如：
现在考虑如果中间某个过程突然掉电会怎样。首先前面几个过程掉电还不怕，毕竟没有写到磁盘中，最怕的是OutputA()后掉电了，这时不仅是自己一次操作失败了，连数据库中的数据都出问题了。为了解决这个问题，提出了使用日志来管理数据的修改。
undo日志
日志其实就是一个文件，里面存着一些事务相关的信息，并且数据只能从尾部增加，从首部删除，具体为
: 表示第i个事务开始 : 表示第i个事务完成 : 表示第i个事务失败终止 - <$T_i$, X, v>: 这个三元组是事务的核心，其中X表示数据库中的某个元素，而v表示未修改之前的数据。
日志开始也是要在内存中读写的，那么日志也有可能丢失，为了确保可以使用日志恢复数据，必须确保日志先于数据写入磁盘。
写日志到磁盘的大致时间为：
- 要写数据到磁盘中时
- 事务结束时要在日志中登记
.但是必须所有数据写到磁盘上了才会登记
下面使用一个gif来表示写日志的大致过程
写磁盘的顺序： 日志->数据->commit记录
commit可以晚一些，但是晚了也没什么好处，因为对于某些隔离级别，没有commit就无法读取数据，因此commit越早越好。
掉电后恢复
例如处理某个事务处理到一半时突然掉电，那么现在就是日志大展身手的时候了，但是他要怎么恢复数据呢？
- 首先从尾部扫描，如果碰到一个碰到一个commit或abort那么把它添加到一个表中，下次碰到这个事务可以直接跳过。
- 如果碰到某个<$T_i$, X, v>三元组并且不在commit表中那么就进行数据恢复,直到碰到对应的start后在尾部添加一个abort。需要添加abort的原因是为了防止再次掉电时重新执行
每次恢复之后我们也可以增加一个<CKPT>标记，表示上次我们已经恢复到这里了，下次再进行恢复的时候到<CKPT>标记就可以停止了
redo日志
上面是undo日志，也就是通过回滚进行恢复的，而redo日志通过重写进行恢复。
undo日志为了确保数据的可靠性，每次写数据时需要刷新两次磁盘，而最后commit还要刷新一次磁盘，磁盘刷新过多减慢了效率。而redo日志可以缓解这个问题。
redo日志和undo日志醉的的区别就是<$T_i$, X, v>中的v代表新值。这就使他可以先写日志和commit再写数据
也就是说写磁盘的顺序是： 写日志->写commit->写数据。
乍一看好像也没有变化，还是写三次磁盘，但是现在数据不一定非要跟在commit后面写了，它想什么时候写就什么时候写，可以几个甚至几十个写操作后存到一块一次将数据写到磁盘上。
但是这样数据丢失时如何恢复呢?
从头开始扫描日志，如果发现这个日志没有commit记录，就不进行操作。如果有commit记录，那么就从头开始把数据复写一遍，因为保存的是新数据，相当于重现了一次写数据的过程。
当然这样的话会发现redo比undo恢复起来慢多了，因为没问题的永远比有问题的多。为了解决这个问题，我们可以在每次写数据之后增加一些检查点，这样恢复时直接从检查点开始就可以了。
undo/redo日志
redo日志可以让数据延迟提交，但是commit必须立刻提交。而undo日志的commit可以延迟提交，我们可以把二者结合起来使commit和数据都可以延迟提交，这就是undo/redo日志的基本思想
undo/redo日志记录是： <$T_i$, X, v, w>，其中v和w一个是新值一个是旧值。
它的写数据过程是： 写日志->commit和数据都可以不急着写，并且顺序任意
因为commit先于数据写就是redo，后于数据写就是undo。在写多个数据过程中写也是redo
恢复过程：
- 首先从尾到头扫描一遍，如果没有commit就是需要undo的，有commit就是需要redo的。
- 然后再从尾开始扫描一遍，这次就是进行回滚，清除undo
- 从头开始扫描一遍清除redo
使用ROLLBACK
ROLLBACK进行回退操作。
SELECT * FROM ordertotals; |
这些语句首先删除了ordertotals中的内容，然后使用ROLLBACK进行回退，最后一个SELECT会看到这个表中还是有内容的。
INSERT、UPDATE、DELETE可以回退，SELECT、CREATE、DROP不能回退。
COMMIT
普通的SQL语句都是直接对数据库表进行读写的，但是在事件处理中提交并不会自动进行，只有使用了COMMIT语句才会进行提交。
START TRANSACTION; |
这两条删除语句只有同时成立时才会被提交，如果第二条失败则不会提交。
在使用COMMIT或ROLLBACK后事务会自动关闭。
如果平常想不自动提交的话，可以使用SET autocommit = 0;
保留点
- 设置保留点： SAVEPOINT delete1;
- 使用保留点： ROLLBACK TO delete1;
安全
试想一个普通用户可以随意修改更新数据库，那无疑会导致一个灾难性的后果。因此我们平常只会给用户以select权限。甚至给select权限都是不安全的，心怀歹意的人会通过sql注入或者通过select权限访问一些他们不应该知道的信息。
甚至更多时候使用事务处理，用户对数据库没有任何权限，只有通过事务的可执行权限才可以访问数据库。通过权限的控制尽可能的保证数据库的安全。
数据库一共有九种权限，我们平常使用的是select， insert， delete， update。 除此之外还有：
- reference： right to be referenced in a constraint，也就是建立外键等需要这个权限
- usage: right to use that element in one’s own declaration
- trigger: 创建触发器
- execute： 执行事务
- under： 给定类型创建子类的权利
创建表的人拥有所有九种权利，然后它可以把权利赋给其他用户。用户可以用授权id（authorization ID)来引用， 授权id一般是用户名。
有一种特殊的授权id是public， 给他赋予权限相当于给所有用户赋予权限。
授权语法：grant <list of privileges>
on <relation or other object>
to <list of authorization id>
(with grant option)
如果有with grant option表示该用户可以授权给其他人。
例如：
grant select, update(price)
on Sells to sally;
这条语句表示把Sells的select权限和price列的update权限给sally。
授权可以只授权关系上的列，此时插入时只能插入这个列，其他列会被赋予默认值或者null
撤销权利的语法：revoke <list of privileges>
on <relation or other object>
from <list of authorization id>
(cascade/restrict)
cascade表示删除该用户的权限和所有该用户授予的权限（干掉小老大和它的小弟）
restrict表示只有在该用户没有授予其他人权限的情况下才可以删除，否则不作任何事
这里有一些微妙的情况，如果a授权给b并且允许b授权给其他人，假如b授权给a，这就是循环授权。当然这种情况是可以的，但是没有任何意义，因为b的权利一定比a小，因此即使授权对a也没有任何影响。
但是如果此时a删除b并且是cascade那么结果会如何呢？结果a并没有被删除，虽然b也指向了a。
图形化表示
我们可以使用授权图清晰的表示权限的变化。授权图有以下要素
- 每个节点都有三个要素：用户、关系、权限。其中每个节点只能包含一个权限，也就是说如果是表的创建者那么一开始他就有九个节点
- 使用带方向的箭头表示权限的授予关系
- 权限有三种表示形式：
p**, p*, p.其中p**表示这是表的创建者，p*表示节点还可以授权给其他人（可以发展下线）， p表示他不可以授权给其他人
如果我们进行取消权限并且是cascade的话那么会将一颗子树全部删除。此外，我们还可以收回收小弟的权限
revoke grant option from b |