MySQL必知必会

《MySQL必知必会》

数据库基础知识

• 数据库：保存有组织的数据的容器（通常是一个文件或一组文件）
• 数据库 ≠ 数据库软件(DBMS)
• 表：某种特定类型数据的结构化清单
• 模式：关于数据库和表的布局及特性的信息

数据库语句

1.如何用use选择数据库，如何用show查看MySQL数据库、表和内部信息

#( help show;显示所有允许的show语句  )
show databases; -- 显示所有数据库
use database_name; -- 使用某个数据库
show tables; -- 返回当前选择数据库内可用表的列表
show columns from table_name; -- 为每个字段返回一行，行中包含字段名,数据类型,是否允许null,键信息,默认值等信息(如auto_increment)
describe table_name; -- show columns from的一种快捷方式
show status;-- 用于显示广泛的服务器状态信息
show create database xx; -- 显示创建特定数据库的mysql语句
show create table xx; -- 显示创建特定表的mysql语句
show grants; -- 显示授予用户的安全权限
show errors; -- 显示服务器错误信息
show warnings; -- 显示服务器警告信息

2.检索语句select

distinct – 去重

limit – 限定行

select prod_name from products; -- 从products表中检索一个名为prod_name的列
select prod_id, prod_name, prod_price from products;
select * from products;

# distinct
-- 关键字应用于所有列而不仅是前置它的列
select distinct vend_id from products; -- 只返回不同的vend_id行

# limit
-- 带一个值的limit总是从第一行开始，给出的数为返回的行数;带两个值的limit可以
-- 指定从行号为第一个值的位置开始(第一行为行0而不是行1)
select prod_name from products limit 5; -- limit 5指示MySQL返回不多于5行
select prod_name from products limit 3,4; -- 从第3行开始的4行记录

# 加限定名
select products.prod_name from products;
select products.prod_name from crashcourse.products;

3.排序检索数据 order by

desc – 倒序

# DESC 倒序（只应用到直接位于其前面的列名）
select prod_name from products order by prod_name;
select prod_id, prod_price, prod_name from products order by prod_price,prod_name;

-- order by和limit组合，找出一个列中最高或最低的值
select prod_price from products order by prod_price DESC limit 1;

4.过滤数据 where

between

is null

and

or

in

not

select prod_name, prod_price from products where prod_price = 2.50;


# where子句操作符
 =	等于;
<> 不等于;
!= 不等于;
<  小于;
<= 小于等于;
>  大于;
>= 大于等于;

# between 在指定的两个值之间
select prod_name, prod_price from products where prod_name = 'fuses';
select prod_name, prod_price from products where prod_price < 10;
select prod_name, prod_price from products where prod_price between 5 and 10;

# 空值检查NULL
select prod_name from products where prod_price is NULL;

# AND 和 OR
-- AND在计算次序中优先级更高,应恰当使用圆括号明确地分组
select product_name, prod_price from products where (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

# IN操作符
# 用来指定范围，范围中地每个条件都可以进行匹配。IN取合法值地由逗号分隔的清单，全都包括在圆括号中。
-- 1.在使用长的合法选项清单时，IN操作符的语法更清楚且更直观
-- 2.在使用IN时，计算的次序更容易管理(因为使用的操作符更少)
-- 3.IN操作符一般比OR操作符清单执行更快。
-- 4.IN的最大优点是可以包含其他select语句，使得能够更动态地建立where子句
select prod_name, prod_price from products where vend_id IN (1002,1003) order by prod_name;

# NOT操作符
-- MySQL支持使用NOT对IN,BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大差异
select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;

5.用通配符进行过滤

LIKE

# 百分号(%)通配符
-- 表示任何字符出现任意次数
select prod_id, prod_name from where prod_name 'jet%'; -- mysql可以配置搜索是否区分大小写

# 下划线(_)通配符
-- 下划线匹配单个字符，不能多也不能少
select prod_id, prod_name from products where prod_name like '_ to anvil';

搜索模式：由字面值、通配符或两者组合构成的搜索条件。

通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

6.用正则表达式进行搜索

-- 检索列prod_name包含文本1000的所有行
select prod_name from products where prod_name REGEXP '1000' order by prod_name;

select prod_name from products where prod_name REGEXP '.000' order by prod_name;

# 进行OR匹配 符号：|
select prod_name from products where prod_name REGEXP '1000|2000';

# 匹配几个字符之一  符号:[]
select prod_name from products where prod_name REGEXP '[123] Ton';

# 匹配特殊字符 用\\为前导
select vend_name from vendors where vend_name REGEXP '\\.'; -- 查找.

LIKE与REGEXP：

LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回。

REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它。

MySQL中的正则表达式不区分大小写，为区分大小写，可使用BINARY关键字，

select prod_name from products where prod_name REGEXP BINARY 'JetPack .000';

7.创建计算字段

Concat – 拼接字段（多数DBMS使用+或||来实现，MySQL则使用Concat函数实现）

# concat
-- 拼接字段
select Concat(vend_name, ' C', vend_country, ')') from vendors order by vend_name;

# RTrim()函数
-- 删除数据右侧多余的空格
select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') from vendors;

8.使用数据处理函数

常用文本处理函数：

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	返回串右边的字符
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

常用日期和时间处理函数

函数	说明
AddDate()	增加一个日期
AddTime()	增加一个时间
CurDate	返回当前日期
CurTime()	返回当前时间
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayofWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期时间的年份部分

数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方跟
Tan()	返回一个角度的正切

9.汇总数据

SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

COUNT()函数有两种使用方式：

• 使用COUNT(*)对表中行的数目进行技术，不管表列中包含的是NULL还是非NULL值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

10.数据分组

创建分组GROUP BY

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。简单来讲，就是在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在select中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用ROLLUP

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

select vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

过滤分组HAVING

select cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

在这里where子句不起作用，因为过滤是基于分组聚集值而不是特定行值的。也可以这么理解，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

GROUP BY 和ORDEY BY的差别

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列，则必须使用

SELECT子句顺序

从上至下：

子句
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

11.联结

INNER JOIN

LEFT JOIN

RIGHT JOIN

12.组合查询union

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询，并将结果作为单个查询结果集返回。

有两种基本情况，其中需要使用组合查询：

1.在单个查询中从不同的表返回类似结构的数据；

2.对单个表执行多个查询，按单个查询返回数据。

union相当于多个where子句or的工作。这两种技术在不同的查询中性能也不同，具体性能谁更好表现需要试一下才能确定。

Union规则：

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔。
• union中的每个查询必须包含相同的列、表达式或聚集函数。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换地类型。
• order by语句必须出现在最后一条select语句之后。

union和union all：

union从查询结果集中去除了重复地行，这是union的默认行为，如果需要可以改变。想返回所有匹配行，也可使用union all。

13.全文本搜索

并非所有的引擎都支持全文本搜索。

MySQL支持几种基本的数据库引擎。两个最常用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

LIKE和正则表达式进行文本搜索的几个重要限制：

• 性能 - 通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
• 明确控制 - 使用通配符和正则表达式匹配，很难明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配地情况下才可以匹配或者才可以不匹配。
• 智能化地结果 - 虽然基于通配符和正则表达式地搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。

所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配，哪些词不匹配，它们匹配地频率，等等。

全文本搜索返回以文本匹配的良好程序排序的数据。

使用全文本搜索：

为了进行全文本搜索，必须索引被搜索地列，而且要随着数据地改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有地索引和重新索引。

在索引之后，select可以与Match()和Against()一起使用以实际执行搜索。

# 启用全文本搜索
-- FULLTEXT子句
-- 在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引自动更新。
create table productnotes
(
    note_id int NOT NULL AUTO_INCREMENT,
    prod_id char(10) NOT NULL,
    note_date date_time NOT NULL,
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;


# 进行全文本搜索
-- Match() 指定被搜索的列
-- Against() 指定要使用的搜索表达式
-- 除非使用BINARY，否则不区分大小写
select note_text from productnotes where Match(note_text) Against('rabbit');

# 查询扩展
-- 使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索。找出可能相关的结果。
-- 首先做基本的全文本搜索，找出与搜索条件匹配的所有行。其次检查匹配行并选择所有有用的词再次进行全文本搜索，这次不仅使用原来的条件还使用所有有用的词
select note_text from productnotes where Match(note_text) Against('anvils' WITH QUERY EXPANSION);

使用完整的Match()说明： 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

布尔文本搜索：

即使没有FULLTEXT索引也可以使用布尔搜索，但这是一种非常缓慢的操作，性能将随着数据量的增加而降低。

布尔方式可以提供如下内容细节，

1.要匹配的词

2.要排斥的词

3.排列提示（只当某些词比其他词更重要）

4.表达式分组

5.另外一些内容

-- 匹配包含heavy的所有行
select note_text
from productnotes
where Match(note_text) Against('heavy' IN BOOLEAN MODE);

-- 匹配包含heavy但不包含任意以rope开始的词的行
select note_text
from productnotes
where Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); 

全文本布尔操作符：

布尔操作符	说明
+	包含，词必须出现
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，而且减少等级值
()	把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

14.数据插入insert

insert into(cust_name,
           cust_address,
           cust_state,
           cust_zip,
           cust_country,
           cust_contact,
           cust_email)
     values('Pep E. Lapew',
           '100 Main Street',
           'Los Angeles',
           'CA',
           '90046',
           'USA',
           NULL,
           NULL);
           
#插入检索出的数据
-- insert select
insert into customers(cust_id, cust_contact,cust_name)
select cust_id, cust_contact,cust_name from custnew;

15.更新和删除数据

# update
-- 三部分：要更新的表、列名和它们的新值、确定要更新行的过滤条件
update customers set cust_email = 'elmer@fudd.com' where cust_id = 10005;

# delete
-- 从表中删除行
delete from customers where cust_id = 10006;

# truncate table
-- 直接删除原来的表并重新创建一个表，而不是逐行删除表中的数据
truncate table customers;

16.创建和操纵表

# create
-- 创建表
create table customers
(
    cust_id		int NOT NULL AUTO_INCREMENT,
    cust_name 	char(50)	NOT NULL DEFAULT 'name',
    cust_address char(50)	NOT NULL,
    cust_city	char(50)	NULL,
    cust_state	char(50)	NULL,
    cust_email	char(50)	NULL,
    PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

# DEFAULT关键字指定默认值

# ENGINE指定引擎
-- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索;
-- MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快;
-- MyISAM是一个性能极高的引擎，它支持全文本搜索


# alter
-- 更新表
-- 添加列
alter table vendors add vend_phone char(20);
-- 删除列
alter table vendors drop column vend_phone;
-- 定义外键
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders (oder_num);


# drop
-- 删除表
drop table customers;

# rename
-- 重命名表
rename table customers to customers_bak;

在创建新表时，指定的表名必须不存在，否则将出错。

如果仅想在一个表不存在时创建它，应该在表名后给出 if not exists .

主键值必须唯一。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

17.使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。它不包含表中应该有的任何列或数据，它包含的是一个SQL查询。

视图的一些常见应用：

1.重用SQL语句

2.简化复杂的SQL操作

3.使用表的组成部分而不是整个表

4.保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

5.更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建之后，可以用与表相同的方式利用它们。可以对视图执行select操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据。

视图的规则和限制：

1.与表一样，视图必须唯一命名。

2.对于可以创建的视图数目没有限制。

3.为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

4.视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。

5.order by可以用在视图中，但如果从该视图检索数据select中也含有order by，那么该视图中的oder by将被覆盖。

6.视图不能索引，也不能有关联的触发器或默认值。

7.视图可以和表一起使用。

视图的创建：

• 视图用create view语句来创建。
• 使用show create view view_name; 来查看创建视图的语句。
• 用drop删除视图，其语法为drop view view_name;
• 更新视图时，可以先用drop再用create，也可以直接用create or replace view。

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;

18.存储过程

使用存储过程的理由(简单、安全、高性能)：

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延申就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写更强更灵活的代码。

# 创建存储过程
DELIMITER $$

create procedure productpricing(
    in pin decimal(8,2),
    out pout decimal(8,2),
    inout para decimal(8,2)
)
BEGIN
	select Avg(prod_price) as priceaverage
	from products;
END $$

DELIMITER ;

-- DELIMITER $$ 告诉命令行实用程序使用$$作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END $$而不是END; 。这样，存储过程内的;仍然保持不动，并且正确地传递给数据库引擎。最后，恢复为原来的语句分隔符可使用DELIMITER ;。除\符号外，任何字符都可以用作语句分隔符。


# 删除存储过程
drop procedure if exists productpricing;

# 检查存储过程
show create procedure ordertotal;
show procedure status; -- 列出所有存储过程
show procedure like 'ordertotal'; -- 用like指定一个过滤模式

19.游标

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

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

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

使用游标的步骤：

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

# 创建游标
create procedure processorder()
BEGIN
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 检索当前行的order_num列到一个名为o的局部声明的变量中
	FETCH ordernumbers INTO o;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

使用游标数据：

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

# 循环检索数据，从第一行到最后一行
create procedure processorder()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 声明continue处理器
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 循环
	REPEAT
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
	
	-- 关闭游标
	CLOSE ordernumbers;
END;

DECLARE语句的次序：

DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

20.触发器

创建触发器时，必须给出4条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（DELETE、INSERT、UPDATE）
• 触发器何时执行（处理之前或之后）

# 创建触发器
-- BEFORE触发器、AFTER触发器
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

# 删除触发器
DROP TRIGGER newproduct;

INSERT触发器：

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

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

DELETE触发器：

• 在delete触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
• OLD中的值全都是只读的，不能更新。

UPDATE触发器：

• 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新更新的值；
• OLD中的值全都是只读的，不能更新。

21.管理事务处理

MyISAM不支持明确的事务处理管理，InnoDB支持。

事物处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）。

# 标识事务开始
START TRANSACTION;;

# 回退MySQL语句
-- 只能在一个事务处理内使用
-- 事务处理用来管理INSERT,UPDATE,DELETE语句。不能回退SELECT,CREATE,DROP
ROLLBACK;

-- 例：
select * from ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

# 提交
-- 一般的MySQL语句提交操作是自动进行地，即隐含提交。在事务处理块中，提交不会隐含地进行。
COMMIT;

# 使用保留点
SAVEPOINT delete1;
...
ROLLBACK TO delete1;

保留点越多越好。

可以在MySQL代码中设置任意多地保留点，越多约好。因为保留点越多，你就越能按自己地意愿灵活地进行回退。

释放保留点。

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5之后，也可以用RELEASE SAVEPOINT明确地释放保留点。

更改默认的提交行为：

# autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。
# 设置autocommit为0指示MySQL不自动提交更改，直到autocommit被设置为真为止。
# autocommit标志是针对每个连接而不是服务器的。
SET autocommit = 0;

22.字符集和校对

字符集为字母和符号的集合；

编码为某个字符集成员的内部表示；

校对为规定字符如何比较的指令。

# 查看所支持的字符集完整列表
show CHARACTER SET;

# 查看所支持校对的完整列表
show COLLATION;

# 给表指定字符集和校对
-- 也可以对特定的列指定字符集和校对

CREATE TABLE mytable
(
    column1 INT,
    column2	VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
 COLLATE hebrew_general_ci;
 
# 如果需要在字符集之间进行转换，可以使用Cast()或Convert()函数。

23.安全管理

# 管理用户
USE mysql;
select user from user;

# 创建用户账号
CREATE USER ben IDENTIFIED BY 'p@$$word';

# 重命名用户账号
RENAME USER ben To bforta;

# 删除用户账号
DROP USER bforta;

# 查看访问权限
SHOW GRANTS FOR bforta;

# 设置权限
-- GRANT添加权限，REVOKE撤销权限
GRANT SELECT ON crashcourse.* TO bforta;

# 更改口令
SET PASSWORD FOR beforta = Password('s7*S2sga');

24.数据库维护

1.备份数据

• mysqldump。转储所有数据库内容到某个外部文件。
• mysqlhotcopy。从一个数据库复制所有数据（并非所有数据库引擎都支持）。
• MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。

2.进行数据库维护

ANALYZE TABLE orders;
-- ANALYZE TABLE，用来检查表键是否正确。

CHECK TABLE orders,orderitems;
-- CHECK TABLE 用来针对许多问题进行检查。
-- CHANGED 检查自最后一次检查来改动过的表
-- EXTENDED 执行最彻底的检查
-- FAST 只检查未正常关闭的表
-- MEDIUM 检查所有被删除的链接并进行键检验
-- QUICK 只进行快速扫描

• 如果MyISAM表访问产生不正确和不一致的结果，可能需要REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。
• 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。

3.诊断启动问题

mysqld命令行

• –help显示帮助；
• – safe-mode装载减去某些最佳配置的服务器；
• –verbose显示全文本消息（为获得更详细的帮助消息与–help联合使用）；
• –version显示版本信息然后退出。

4.查看日志文件

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。
• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。
• 缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。

25.改善性能

• 首先，MySQL具有特定的硬件建议。对于生产的服务器需要遵循这些硬件建议。
• 一般来说，关键的生成DBMS应该运行在自己的专用服务器上。
• MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过后一段时间可能需要调整内存分配、缓冲区大小等。（查看当前设置，可以用show variables;和show status;）
• MySQL是一个多用户多线程的DBMS，换言之它经常执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果遇到显著的性能不良，可使用show processlist显示所有活动进程。还可以用KILL命令终结某个特定的进程。
• 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
• 一般来说，存储过程执行得比一条一条执行其中得各条MySQL语句快。
• 应该总是使用正确得数据类型。
• 决不要检索比需求还要多的数据。换言之，不要用SELECT*(除非你真正需要每个列)。
• 有的操作可支持一个可选得DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
• 在导入数据时，应该关闭自动提交。你可能还想删除索引（FULLTEXT索引），然后在导入完成后再重建它们。
• 必须索引数据库表以改善数据检索得性能。确定索引不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的where和order by子句。如果一个简单的where子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
• 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
• LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
• 数据库是不断变化的实体。由于表的使用和内容的更改，理想的优化和配置也会改变。