《MySQL必知必会》| 读书笔记(下)_创建触发强需要特殊的安全访问权限-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41360604/article/details/102630627

16.组合查询

MySQL允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。
有俩种情况需要使用组合查询：

在单个查询中从不同的表返回类似结构的数据；
对单个表执行多个查询；

//实例
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)
ORDER BY vend_id,prod_price;

//等价于
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
	OR vend_id IN (1001,1002) 
ORDER BY vend_id,prod_price;

UNION必须由俩条或者俩条以上的SELECT语句组成，语句之间用UNION分隔；
UNION中的每个查询必须包含相同的列、表达式或聚集函数；
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型；
UNION从查询结果集中自动去除了重复的行(即其行为与单条SELECT语句中使用WHERE子句条件一样);
如果想返回所有匹配行,不取消重复，可使用UNION ALL而不是UNION;
使用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后；
使用UNION的组合查询可以应用不同的表；

17.全文本搜索

MySQL中并非所有的数据库引擎都支持全文本搜索(全文检索)。最常见的俩个引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。
在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

//实例
CREATE TABLE productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
	prod_id char(10) NOT NULL ,
	note_date datetime NOT NULL,
	note_text text NULL,
	PRIMARY KEY(note_id),
	FULLTEXT(note_text)
) ENGINE=MyISAM;

定义过后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新;
不要在导入数据时使用FULLTEXT;

进行全文本搜索

索引之后，使用俩个函数Match()和Against()执行全文搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

//实例
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

传递给March()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确);
除非使用BINARY方式，否则全文本搜索不区分大小写；
全文搜索的一个重要部分就是对结果排序，具有较高等级的行先返回；
如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词的那些行高的等级值；

使用查询扩展

查询扩展用来设法放宽所返回的全文搜索结果的范围；
在使用查询扩展时，MySQL对数据和索引进行俩遍扫描来完成搜索：

首先，进行一个基本的全文搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词；
再其次，MySQL再次进行全文搜索，这次不仅使用原来的条件，而且使用所有有用的词；

//实例
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

表中的数据越多(即行中的文本越多)，使用查询扩展返回的结果就越好。

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式(boolean mode).
以布尔方式，可以提供关于如下内容的细节：

要匹配的词；
要排斥的词(如果某行包含这个词，则不返回该行，即使他包含其他指定词)；
排列提示(指定某些词比其他词更重要，等级更高)；
表达式分组；
其他内容；

布尔方式不同于其他全文本搜索语法，即使没有定义FULLTEXT索引，也可以使用；但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。

//实例
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

全文本布尔操作符

+			//包含，词必须存在
-			//排除，词必须不出现
>			//包含并且增加等级值
<			//包含并且减少等级值
()			//把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~			//取消一个词的排序值
*			//词尾的通配符
""			//定义一个短语(与单个词的列表不一样，他匹配整个短语以便包含或排除这个短语)

排列而不排序：在布尔方式中，不按等级值降序排序返回的行。

全文本搜索的使用说明

在索引全文本数据时，短词被忽略且从索引中被排除。短词定义为那些具有3个或3个一下字符的词；
MySQL带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略；如果需要，可以覆盖；
许多词出现的频率很高，搜素它们没有用处(返回太多结果)。因此，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略；(50%规则不适用于IN BOOLEAN MODE);
如果表中的行数少于3行，则全文本搜索不返回结果(出现则满足50%);
忽略词中的单引号(don't索引为dont)；
不具有词分隔符的语言(包括日语和汉语)不能恰当的返回全文本的搜索结果；
仅在MyISAM数据库引擎中支持全文本搜索；

MySQL全文本搜索不支持邻近操作符。
邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词。

18.插入数据

INSERT用于插入行到数据表。
插入可以用集中方式使用：

插入完整的行；
插入行的一部分；
插入多行；
插入某些查询的结果；

插入完整的行

安全的INSERT语句

INSERT INTO customers
	(cust_name,
	cust_address,
	cust_city,
	cust_email)
VALUES
	('Peter'，
	’WuHan‘，
	’Beijing‘，
	’10086@qq.com‘);

INSERT语句一般不会产生输出；
因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序；
其优点是即使表的结构改变，INSERT语句仍然能正确工作；

如果表的定义允许，则可以在INSERT操作中省略某些列。
省略的列必须满足以下某个条件：

该列定义为允许NULL值(无值或空值)；
在表定义中给出默认值。这表示如果不给出值，将使用默认值；

如果数据检索是最重要的，则可以通过INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如：INSERT LOW_PRIORITY INTO

插入多行

有俩种方式，一种是使用多条INSERT语句，每条语句用一个分号结束。
另一种方式，需要满足每条INSERT语句中的列名相同，如：

INSERT INTO customers(
	cust_name,
	cust_address,
	cust_city)
VALUES(
  'Peter',
 'shanghai',
 'beijing'
 ),
 (
 'Bob',
 'wuhan',
 'guangzhou'
 );

MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

插入检索的数据

将一条INSERT语句的结果插入表中，就是所谓的INSERT SELECT。

INSERT INTO customers(
	cust_id,
	cust_contact,
	cust_email,
	cust_name)
SELECT
	cust_id,
	cust_contact,
	cust_email,
	cust_name
FROM custnew;

MySQL并不关心SELECT返回的列名，它使用的是列的位置。

19.更新和删除数据

更新数据

更新(修改)表中的数据，使用UODATE语句。
可采用俩种方式使用UPDATE:

更新表中的特定行；
更新表中所有行；

不要省略WHERE子句，在使用UPDATE时一定要注意，因为稍不注意就会更新表中所有的行。

UPDATE customers
SET cust_email = '10086@qq.com'
WHERE cust_id = 10086;

IGNORE关键字：如果使用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消。即使发生错误，也继续进行更新，可以使用IGNORE关键字，如：UPDATE IGNORE table_name...

删除数据

删除表中的数据可以使用DELETE语句。
俩种方式：

从表中删除特定的行；
从表中删除所有行；

同更新操作一样，不要省略WHERE子句！！！

DELETE FROM table_name WHERE cust_id =10086;

DELETE不需要列名或通配符；
DELETE删除整行，而不是删除列；

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，其实际上就是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。

使用习惯

除非确实打算更新或删除表中的每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句；
保证每个表都有主键，尽可能像WHERE子句那样使用它；
在对UPDATE或DELETE语句使用WHERE子句前，应该使用SELECT进行测试，保证其过滤正确的记录，以防编写的WHERE子句不正确；
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行；

20.创建和操纵表

表创建基础

使用CREATE TABLE创建表，必须给出下列信息：

新表的名称，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔；

//实例
CREATE TABLE	customers
(
	cust_id			int				NOT NULL AUTO_INCREMENT,
	cust_name		char(50)		NOT NULL,
	cust_address	char(50)		NULL,
	cust_city		char(50)		NULL,
	quantity		int				NOT NULL DEFAULT 1,
	cust_email		char((255)		NULL,
	PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

表的主键可以在创建表时用PRMARY KEY关键字指定；
如果仅仅想在一个表不存在时创建，应该在表名后给出IF NOT EXISTS;
允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，即在插入或更新行时，该列必须有值；
主键为其值唯一标识表中每个行的列。主键只允许使用不允许NULL值的列；
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引；
默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定；
与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量；

NUL值是没有值，而不是空串。如果指定'',这在NOT NULL列中时允许的。空串是一个有效的值，而不是无值。NULL值用关键字NULL指定而不是空串。

AUTO_INCREMENT指定的列，每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。

SELECt last_insert_id();

返回最后一个AUTO_INCREMENT值，然后将其用于后续的MySQL语句；

引擎类型

InnoDB是一个可靠的事务处理引擎，不支持全文索引；
MEMORY在功能等同于MyISAM，但由于数据存储在内存中，速度很快(适合于临时表)；
MyISAM是一个性能极高的引擎，支持全文索引，但不支持事物；

外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

使用ALTER TABLE可以更改表结构：

在ALTER TABLE之后给出要更改的表名；
所做更改的列表；

复杂的表结构更改一般需要手动删除过程，涉及一下操作：
①用新的列布局创建一个新表
②使用INSERT SELECT语句，从旧表复制数据到新表。如有必要可以使用转换函数和计算字段
③检验包含所需数据的新表
④重命名旧表(如果确定，可以删除它)
⑤用旧表原来的名字重命名新表；
⑥根据需要，重新创建触发器、存储过程、索引和外键

删除表
DROP TABLE table_name;

重命名表
RENAME TABLE table_name TO new_table_name;

21.使用视图

视图时虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图包含的是一个SQL查询。

视图的常见应用：

重用SQL语句；
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节；
使用表的组成部分而不是整个表；
保护数据。可以给用户授予表特定部分的访问权限而不是整个表的访问权限；
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据；

视图仅仅是用来查看存储在别处的数据的一种设施。

视图的规则与限制：

与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名字)；
对于可以创建的视图数目没有限制；
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予；
视图可以嵌套；
ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句也含有ORDER BY，那么该视图中的ORDER BY将被覆盖；
视图不能索引，也不能有关联的触发器或默认值；
视图可以和表一起使用；

使用视图

视图用CREATE VIEW语句来创建；
使用SHOW CREATE VIEW viewname;来查看创建视图的语句；
用DROP来删除视图，语法为DROP VIEW viewname;
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW

利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂SQL，这通常会涉及联结。

//实例
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;

用视图重新格式化检索

//实例
CREATE VIEW vendorlocation AS
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')')
	AS vend_title
FROM vendors
ORDER BY vend_name;

使用视图过滤不想要的数据

//实例
CREATE VIEW customereamillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

使用视图与计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num
	   prod_id,
	   quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM orderitems;

更新视图

视图是可更新的，更新一个视图将更新其基表。如果你对视图增加或者删除行，实际上是对其基表增加或删除行。

但如果视图定义中有以下操作，则不能进行视图的更新：

分组(使用GROUP BY和HAVING);
联结；
子查询；
并；
聚集函数Min()、Count()、Sum()...
DISTINCT
导出(计算)列；

22.使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然他们的作用不仅限于批处理。

使用存储过程的好处

简单、安全、高性能

通过把处理封装在容易使用的单元中，简化复杂的操作；
由于不要求反复建立一系列处理步骤，这保证了数据的完整性；
简化对变动的管理；
提高性能；
存在一些只能用在单个请求中MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

缺陷

存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验；
可能没有创建存储过程的安全访问权限；

执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接收存储过程的名字以及需要传递给它的任意参数。

CALL productpricng(@pricelow,
				   @pricehign,
				   @priceaverage);

创建存储过程

//实例
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

使用该存储过程：CALL productpricing();

因为存储过程实际上是一种函数，所以存储过程名后需要有()符号；

删除存储过程

DROP PROCEDURE productpricing;

删除语句的存储过程名后不需要()
仅当存在时删除：DROP PROCEDURE IF EXISTS

使用参数

一般存储过程不显示结果，而是把结果返回给指定的变量。

变量(variable)：内存中一个特定的位置，用来临时存储数据。

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2)
)
BEGIN 
	SELECT Min(prod_price)
	INTO pl 
	FROM products;
	SELECT Max(prod_price)
	INTO ph
	FROM products;
	SELECT Avg(prod_price)
	INTO pa
	FROM products;
END;

每个参数必须具有指定的类型，这里使用十进制类型；
关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)；
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)、INOUT（对存储过程传入和传出）；
存储过程的代码位于BEGIN和END语句内，它们是一系列的SELECT语句，用来检索值，然后保存到相应的变量(通过指定INTO关键字)；

为调用此修改过的存储过程，必须指定3个变量名，如下所示：

CALL priductpricing(@pricelow,
					@pricehigh,
					@priceaverage);

该语句调用后不显示任何数据。只返回以后可以使用的变量。

使用变量：SELECT @pricelow,@pricehigh,@priceaverage;

//实例
CREATE PROCEDURE ordertotal(
	INT onnumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal;
END;

//调用
CALL ordertotal(20002,@total);

//使用变量
SELECT @total;

建立智能存储过程

在存储过程中包含业务规则和智能处理。

检查存储过程

显示用来创建一个存储过程的CREATE语句：
SHOW CREATE PROCEDURE procedure_name;

获得包括何时、由谁创建等详细信息的存储过程列表：
SHOW PROCEDURE STATUS

限制过程状态结果，可以使用LIKE指定一个过滤模式
SHOW PROCEDURE STATUS LIKE 'string';

23.使用游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一个SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

MySQL游标只能用于存储存储过程(和函数)。

使用游标的步骤：

在能够使用游标前，必须声明它；这个过程实际上是定义要使用的SELECT语句；
一旦声明后，必须打开游标以供使用；这个过程用前面定义的SELECT语句把数据实际检索出来；
对于填有数据的游标，根据需要取出(检索)各行；
在结束游标使用时，必须关闭游标；

创建游标

游标使用DECLARE语句创建：

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

DECLARE语句用于定义和命名游标
存储过程完成后，游标消失，因为游标仅局限于存储过程中

打开和关闭游标

OPEN cursor_name;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动；

CLOSE cursor_name;

CLOSE释放游标使用的所有内存和资源；
在一个游标关闭后，如果没有重新打开，则不能使用它；
隐含关闭：如果不明确关闭游标，MySQL会在到达END语句时自动关闭游标；

使用游标数据

打开游标后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEAFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);

	DECALRE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8,2));

	OPEN ordernumbers;

REPEAT

	FETCH ordernumbers INTO o;
	
	CALL ordertotal(o,1,t);

	INSERT INTO ordertotals(order_num,total)
	VALUES(o,t);

UNTILL done END REPEAT;

CLOSE ordernumbers;

24.使用触发器

触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句：

DELETE;
INSERT;
UODATE;
其他MySQL语句不支持触发器；

建议保证整个数据库中的触发器名的唯一性。

CREATE TRIGGER newproduct AFTER INSERT INTO ON products FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建触发器；
FOR EACH ROW指定每个插入行执行；
触发器按每个表每个事件每次地定义，每个表每个事件只允许一个触发器；
即每个表最多支持6个触发器

只有表才支持触发器，视图不支持(临时表也不支持)。
如果BEFORE触发器失败，则MySQL将不执行请求的操作。
如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器。

删除触发器

DROP TRIGGER trigger_name;

INSERT触发器

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

在一个INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)；
对于AUTO_INCREMENT列，NEW在INSERT执行前包含0，在INSERT执行之后包含新的自动生成值；

DELETE触发器

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id)
	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新；
使用BEGIN END块的好处是触发器能容纳多条SQl语句；

UPDATE触发器

CREATE TRIGGER updateventor BEFORE UPATE ON ventors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新更新的值；
在BEFORE UPDATE触发器中，NEW中的值可能也被更新(允许将要用于UODATE语句中的值)；
OLD中的值全都是只读的，不能更新；

关于触发器

创建触发器可能需要特殊的安全访问权限，但触发器的执行时自动的；
应该使用触发器来保证数据的一致性(大小写，格式等)；
触发器的一种非常有意的使用是创建审计跟踪；
触发器中不支持CALL语句，这表示触发器中不能调用存储过程。所需的存储过程代码需要复制到触发器内；

25.管理事务处理

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
相关术语：

事务(transaction)：指一组SQL语句；
回退(rollback)：指撤销指定SQL语句的过程；
提交(commit)：指将未存储的SQL语句结果写入数据库表；
保留点(savepoint)：指事务处理设置的临时占位符(placeholder)，可以对其发布回退；

控制事务处理

事务开始：START TRANSACTION;
使用ROLLBACK：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

ROLLBACK只能在一个事务处理内使用；
事务处理可以用来管理INSERT/UPDATE/DELETE语句；
不能回退SELECT语句，无意义，不能回退CREATE/DROP操作；
事务处理中可以使用CREATE/DROP,但这俩条语句不能执行回退；

使用COMMIT

隐含提交(implicit commit)，即提交操作是自动进行的。
但是在事务处理块中，提交不会隐含地执行。为进行明确的提交，使用COMMIT语句：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

COMMIT语句仅在不出错时写出更改，否则事务会被撤销；
当COMMIT或ROLLBACK语句执行后，事务会自动关闭；

使用保留点

简单的ROLLBACK和COMMIT语句可以写入或撤销整个事务处理。但是对于复杂的事务处理，需要部分提交或回退。
为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退，就可以回退到某个占位符。
这些占位符称为保留点。为了创建占位符，可使用SAVEPOINT语句：

SAVEPOINT delete1;

ROLLBACK TO delete1;

保留点越多越好；
保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放；
MySQL 5+版本可以使用RELEASE SAVEPOINT明确释放保留点；

更改默认的提交行为

默认的MySQL行为是自动提交所有更改的。
为了指示MySQL不自动提交更改，需要使用以下语句：
SET autocommit=0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句；
设置为0指示MySQL不自动提交更改；
autocommit是针对每个连接而不是服务器；

全球化和本地化

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此MySQL需要适应不同的字符集(不同的字母和字符)，适应不同的排序和检索数据的方法。
相关术语：

字符集：字母和符号的集合；
编码：某个字符集成员的内部表示；
校对：规定字符如何比较的指令；

显示所有可用的字符集以及每个字符集的描述和默认校对：
SHOW CHARACTER SET;
查看所支持校对的完整列表以及它们适用的字符集：
SHOW COLLATION;
确定所用的字符集合校对：

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

给表指定字符集和校对：

CREATE TABLE mytable
(
	column1		INT,
	column2		VARCHAR(10)，
	column3		VARCHAR(10)	CHARACTER SET latin1 COLLATE latin1_general_ci
) DEAFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

如果指定CHARACTER SET和COLLATE，则使用这些值；
如果未指定则使用默认值；
允许对列指定字符集和校对；

校对在对用ORDER BY子句检索出来的数据排序时起重要作用。如果需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：

SELECT * FROM customer
ORDER BY lastname,firstname COLLATE latin1_general_cs;

COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等；
如果绝对需要，串可以在字符集之间进行转换。Cast()或Convert()函数

27.安全管理

访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权。即用户不能对过多的数据具有过多的访问权。

管理用户

创建用户账号：CREATE USER user_name IDENTIFIED BY 'p@$$wOrd';

指定散列口令：IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD。

GRANT语句也可以创建用户账号，但一般CREATE USER是最清楚和最简单的句子。
此外可以直接插入user表中来增加用户，不过不建议这样做，因为对该表的任何毁坏都可能严重伤害到MySQL服务器。

重命名用户账号：RENAME USER user_name TO new_user_name;
删除用户账号：DROP USER user_name;

设置访问权限

创建用户账号后必须接着分配访问权限。
查看用户权限：
SHOW GRANTS FOR user_name;

设置权限

使用GRANT语句设置权限：

要授予的权限；
被授予访问权限的数据库或表；
用户名；

GRANT SELECT ON database_name.* TO username;

此GRANT允许用户username在database_name下的所有表上使用SELECT;

简化多次授权：GRANT SELECT,INSERT ON database_name.* TO username;

撤销权限

GRANT的反操作为REVOKE，用它来撤销特定的权限：
REVOKE SELECT ON database_name.* FROM username;

GRANT和REVOKE可在几个层次上控制访问权限：

整个服务器，使用GRANT ALL和REVOKE ALL;
整个数据库，使用ON database_name.*;
特定的表，使用ON database_name.table_name;
特定的列；
特定的存储过程；

【权限图】

更改口令

SET PASSWORD FOR username = Password('your_password');

设置当前用户的口令：SET PASSWORD = Password('your_password');

28.数据库维护

备份数据

由于MySQL是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但由于这些文件总是处于打开和使用状态，普通的文件副本不一定总是有效。
所以可以采取如下备份方式：

使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前，这个实用程序应该正常运行，以便能正确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序);
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这俩条语句都接收将要创建的系统文件名，此系统文件必须不存在，否则会报错。数据可以用RESTORE TABLE来复原。

为了保证所有数据被写到磁盘(包括索引数据)，可能需要在进行备份前使用FLUSH TABLES语句。

进行数据维护

MySQL提供一些语句，用于保证数据库正确和正常运行。

ANALYZE TABLE table_name;：用于检查表键是否正确；
CHECK TABLE table_name;：针对许多问题对表进行检查；CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表，EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。
如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表；若该语句需要经常使用，可能会有更大的问题需要解决。
如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。

诊断启动问题

MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

--help显示帮助；
--safe-mode装载减去某些最佳配置的服务器；
--verbose显示全文本消息；
--version显示版本信息然后退出；

查看日志文件

主要的日志文件有以下几种：

错误日志：包含启动和关闭问题以及任意关键错误的细节。通常名为hostname.err,位于date目录中。可用--log-error命令行选项更改；
查询日志：记录所有MySQL活动，在诊断问题时非常有用。通常名为hostname.log,位于date目录中。可用--log命令行选项更改；
二进制文件：记录更新过数据的所有语句。通常名为hostname-bin,位于date目录中。可用--log-bin命令行选项更改；
缓慢查询日志：记录执行缓慢的任何查询。通常名为hostname-slow.log,位于date目录中。可用--log-slow-queries命令行选项更改；

使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

29.改善性能

性能探讨与分析：

关键的生产DBMS应该运行在自己的专用服务器上；
MySQL是用一系列的默认配置预先配置的，这些设置开始通常是很好的。但过一段时间后可能需要调整内存分配、缓冲区大小等。(查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;)；
遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程。还可以用KILL命令终结某个特定的进程；
总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法；
使用EXPLAIN语句让MySQL解释它如何执行一条SELECT语句；
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快；
应该总是使用正确的数据类型；
绝不要检索比需求还要多的数据；尽量不使用SELECT *
有的操作支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作；
在导入数据时，应该关闭自动提交；
必须索引数据库表以改善数据检索的性能；
使用多条SELECT语句和连接它们的UNION语句，代替复杂的OR条件；
索引改善数据检索的性能，但损害数据插入、删除和更新的性能；
LIKE很慢；
数据库是不断变化的实体；

30.补充（附录）

数据类型

定长串：接受长度固定的字符串，其长度是在创建表时指定的。CHAR属于定长串类型。
变长串：存储可变长度的文本。有些变长数据类型具有最大的定长，而有些完全变长。

①MySQL处理定长列远比处理变长列快得多。此外，MySQL不允许对变长列进行索引。
②不管使用何种类型的数据类型，串值都必须括在引号内(通常单引号更好)。
③如果数值是计算中使用的数值，则应该存储在数值数据类型列中。如果作为字符串使用，则应该保存在串类型数据类型中。
④有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。使用UNSGINED关键字，将允许存储俩倍大小的值。