--where子句操作符：

　--通配符：%与_

--正则表达式：注意[]是另一种形式的OR，比如[123]Ton是[1|2|3]Ton的缩写，如果不使用[]，则会匹配出其他行的数据，

如：1|2|3 ton就会检索出Jet 1000

　　　　　　  [1-9]、[a-Z]，如果是匹配一些特殊的字符要使用\\进行转义，如\\.表示查找.　　\\\表示查找\

　　　　　　  空白元字符：\\f换页　　\\n换行  　　\\r回车  　　\\t制表 　　\\v纵向制表

--匹配字符类：

匹配多个实例：

--创建拼接字段

MySQL中使用concat(a,b)函数来拼接两个列，但是多数DBMS使用+或||来实现拼接

Trim函数去掉串左右两边的空格，RTrim()函数去掉值右边的所有空格，LTrim()函数去掉值左边的所有空格

--使用别名

关键字是as，当然as可以省略，但尽量不省略，根据经验，有时候普as不写会报错

--执行算术计算

操作符：+加-减*乘/除

select NOW()返回当前日期和时间

--使用数据处理函数

文本处理函数：

日期和时间处理函数：

数值处理函数：

----汇总数据

--聚集函数：

--聚集不同值

上述的5个聚集函数都可以如下使用：

　　1、对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）

　　2、只包含不同的值，指定DISTINCT参数

如果指定列名，则DISTINCT只能用于COUNT()，按技术上来说，DISTINCT可以用于MAX()和MIN()，但是没有意义

--组合聚集函数

意思就是select可以包含多个聚集函数

--分组数据

关键字GROUP BY

规定：

1、GROUP BY子句可以包含任意数目的列

2、如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总，换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）

3、GROUP BY子句中列出的每个列都必须时检索列或者有效的表达式（但不能是聚集函数），如果在select中使用表达式，则必须在GROUP BY子句中指定

　　相同的表达式，不能使用别名

4、除聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出

5、如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组

6、GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

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

过滤分组：

关键字HAVING

除了能用GROUP BY分组数据外，MySQL还允许使用过滤分组，规定包括哪些分组，排除哪些分组。因为WHERE没有分组的概念，所以WHERE过滤指定的是行而不是分组。

目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代，唯一的差别是WHERE是过滤行，而HAVING过滤分组。过滤是基于分组聚集值而不是特定行值的。

另一种思维区分HAVING和WHERE：

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要区别，WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

分组和排序：

PS：一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法，千万不要依赖于GROUP BY排序数据

SELECT子句顺序：

--使用子查询

在select中创建多个select

利用子查询进行过过滤

作为计算字段使用子查询，就是子查询作为一个字段

----联结表

PS：叉联结（cross join），有时我们会听到返回成为叉联结的笛卡尔积的联结类型

--内部联结

目前所用的联结成为等值联结（equijoin），基于两个表之间的相等的测试，又称为内部联结。

ANSI SQL规范首选INNER JOIN语法，此外尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

----创建高级联结

除了使用别名引用呗检索的表列，给列起别名的用法如下，写的原因主要是没见过这种concat的用法：

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')

    AS vend_title

    FROM vendors

    ORDER BY vend_name

----使用不同类型的联结

内部联结或等值联结都是简单联结，其实还有其他联结，分别是自联结、自然联结和外部联结

--自联结

用自联结而不用子查询：自然联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是一样的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

select prod_id,prod_name

　　from products

　　where vend_id = (select vend_id

                   　　from products

                   　　where prod_id = 'DTNTR');
---------------------------------------------------
select p1.prod_id, p1.prod_name
　　from products as p1, products as p2
　　where p1.vend_id = p2.vend_id
　　　　　　and p2.prod_id = 'DTNTR';

--自然联结

PS:事实上，迄今为止，我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结

--外部联结

联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结

select customer.cust_id, orders.order_num

    from customers inner join orders

    on customers.cust_id = orders.cust_id;

select customer.cust_id, orders.order_num

    from customers left join orders

    on customers.cust_id = orders.cust_id;

PS：

1、没有*=操作符，MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中很流行

　　 2、外部联结的类型：存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒from或where子句中表的顺序转化为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

--使用带聚集函数的联结

select customer.cust_id,

         customer.cust_name,

         count(orders.order_num) as num_ord

    from customer

    inner join orders

    on customer.cust_id = orders.cust_id

    group by customer.cust_id

--使用联结和联结条件

要点：

　　1、注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的

　　2、保证使用正确的联结条件，否则将返回不正确的数据

　　3、应该总是提供联结条件，否则会得出笛卡尔积

　　4、在一个联结中可以包含多个表，甚至对于每个联结可以采取不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将是故障排除更为简单。

----组合查询

--组合查询

MySQL允许执行多个查询（多条select语句），并将结果作为单位查询结果集返回。这些组合查询通常成为并（union）或复合查询（compound query）。

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

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

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

组合查询和多个where条件：多数情况下，组合相同表的两个查询完成的工作与具有多个where子句条件的单条查询完成的工作相同。换句话说，任何具有多个where子句的select语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中，性能也不同。因此，应该试一下两种技术，以确定对特定的查询哪一种性能更好。

--使用union创建组合查询

给出每条select语句，在各条语句之间放上关键字union，下述写法就是将两个select查询的东西合并在一起（3列）

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)

简单例子中，union比where复杂，但是遇到复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用union可能会使处理更简单。

--union规则

注意：

　　1、union必须由两条以上的select语句组成，语句之间用关键字union分隔（因此，如果组合4条select语句，将要使用3个union关键字）

　　2、union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）

　　3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）

如果遵守了这些基本规则或限制，则可以将并用于任何数据检索任务。

--包含或取消重复的行

在union的默认行为中，是取消了重复行的，但是可以使用union all来返回所有匹配行

ps：union与where：union几乎总是完成与多个where条件相同的工作。union all为union的一种形式，它完成where子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用union all而不是where。

--对组合查询结果排序

在用union组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后。对于结果集，不存在用一种方式排序一部分，而有用另一种方式排序另一部分的情况，因此不允许使用多条order by子句。

ps：组合不同的表：使用union的组合查询可以应用不同的表。

----全文本搜索

并非所有引擎都支持全文本搜索：MySQL支持几种基本的数据库引擎。并非所有的引擎都支持该文章所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB。前者支持全文本搜索，而后者不支持。

like和正则表达式等搜索机制存在几个重要的限制：

　　1、性能：通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时

　　2、明确控制：使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配

　　3、智能化的结果：虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回多个包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能时更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

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

--使用全文本搜索

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

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

-------演示FULLTEXT子句的使用

create table productnotes

(

    note_id int not null auto_increment,

    prod_id char(10) not null,

    note_date datetime not null,

    PRIMARY KEY(note_id),

    FULLTEXT(note_text)

) ENGINE=MyISAM;

ps：不要在导入数据时使用FULLTEXT：更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需要的总时间）。

--进行全文本搜索

在索引之后，使用两个函数match()和against()执行全文本搜索，其中match()指定被搜索的列，against()指定要使用的搜索表达式。

关于match()和against()函数的解释：

函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT 索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。当 MATCH() 被使用在一个 WHERE 子句中时 (参看上面的例子)，返回的记录行被自动地以相关性从高到底的次序排序。相关性值是非负的浮点数字。零相关性意味着不相似。相关性的计算是基于：词在记录行中的数目、在行中唯一词的数目、在集中词的全部数目和包含一个特殊词的文档(记录行)的数目。它也可以执行一个逻辑模式的搜索。

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

　　搜索不区分大小写：除非使用binary方式，否则全文本搜索不区分大小写；

　　排序多个搜索项：如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

全文本搜索时对结果排序，具有较高等级的行先返回。全文本提供了简单like搜索不能提供的功能。而且由于数据是索引，全文本搜索还相当快。

--使用查询扩展

在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

　　1、首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行

　　2、其次，MySQL检查这些匹配行并选择所有有用的词

　　3、再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

查询扩展增加了返回的行数，，但这样做也增加了实际上并不想要的数目。

ps：行越多越好：表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

--布尔文本搜索

以布尔方式，可以提供关于如下内容的细节：

　　1、要匹配的词

　　2、要排斥的词（如果某行包含这个词，则不反悔该行，即使它包含其他指定的词也是如此）

　　3、排列提示（指定某些词比其他词更重要，更重要的词等级更高）

　　4、表达式分组

　　5、另外一些内容

ps：即使没有FULLTEXT索引也可以使用：布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）

　　IN BOOLEAN MODE的行为差异

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

-全文本搜索的使用说明

　　1、在索引全文本数据时，短词被忽略且从索引中排除。短语定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）

　　2、MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表

　　3、许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE

　　4、如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）

　　5、忽略此中的单引号。例如，don't索引为dont

　　6、不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果

　　7、仅在MyISAM数据库引擎中支持全文本搜索

----插入数据

--数据插入

插入可以用几种方式使用：

　　1、插入完整的行

　　2、插入行的一部分

　　3、插入多行

　　4、插入某些查询的结果

插入及系统安全：可针对每个表或每个用户，利用MySQL的安全机制机制禁止使用INSERT语句

--插入完整的行

insert into customers

values (null,

'LeeMichael',

'100 street',

'los angle',

null,

'USA');
-----------------------------------

insert into customers(
cust_name,
cust_addr,
cust_city,
cust_country)

values (

'LeeMichael',

'100 street',

'los angle',

'USA');

ps：总是使用列的列表：一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。

　　仔细地给出值：不管使用哪种INSERT语法，都必须给出values的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对梅格列出的列给出一个值。如果不这样，将产生一条错误信息，相应的行插入不成功。

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

　　　　　　1、该列定义为允许null值（无值或空值）

　　　　　　2、在表定义中给出默认值。这表示如果不给出值，将使用默认值

　　　　　　如果对表中不允许null值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。

　　提高整体性能：数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的select语句的性能

　　　　如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如：INSERT LOW_PRIORITY INTO，这也适用于UPDATE和DELETE。

--插入多个行

insert into customers(

cust_name,

cust_addr,

cust_city,

cust_country)

values (

'LeeMichael',

'100 street',

'los angle',

'USA');

insert into customers(

cust_name,

cust_addr,

cust_city,

cust_country)

values (

'LGQMichael',

'100 street',

'los angle',

'USA');

多个语句，用 ; 隔开

或者可以这样（这样写可以提高性能）：

insert into customers(

cust_name,

cust_addr,

cust_city,

cust_country)

values (

'LeeMichael',

'100 street',

'los angle',

'USA')，

(

'LGQMichael',

'100 street',

'los angle',

'USA')，

ps：提高insert的性能：此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

--插入检索出的数据

新例子的说明：这个例子把一个名为custnew的表中的数据导入customers表中。为了试验这个例子，应该首先创建和填充custnew表。custnew表的结构于customers表相同。在填充custnew时，不应该使用已经在customers中使用过的cust_id值（如果主键值重复，后续的INSERT操作将会失败）或仅省略这列值让MySQL在导入数据的过程中产生新值。

insert into customers (cust_id,

cust_contact,

cust_email,

cust_name,

cust_addr,

cust_city)

select cust_id,

cust_contact,

cust_email,

cust_name,

cust_addr,

cust_city

from custnew

ps：INSERT SELECT中的别名：为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据时非常有用的。

INSERT SELECT中SELECT语句中可以包含where子句以过滤插入的数据。

----更新和删除数据

--更新数据

为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：

　　1、更新表中特定行

　　2、更新表总所有行

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

　　UPDATE与安全：可以限制和控制UPDATE语句的使用。

基本的UPDATE语句由3个部分组成，分别是：

　　1、要更新的表

　　2、列名和它们的新值

　　3、确定要更新行的过滤条件

update customers

set cust_email = 'lgq@163.com'

where cust_id = 1005;

ps： 在UPDATE语句中使用子查询：UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据

　　 IGNORE关键字：如果能用UPDATE语句更新多行，并且在更新这些行中的一行

--删除数据

为了从一个表中删除（去掉）数据，使用delete语句。分为两种方式：

　　1、从表中删除特定的行

　　2、从表中删除所有行

ps：不要省略where子句：在使用delete时一定要注意细心。因为稍不注意，就会错误地删除表中所有行。

　　delete与安全：可以限制和控制delete语句的使用

　　删除表的内容而不是表：delete语句从表中删除行，甚至是删除表中所有行。但是，delete不是删除表本身。

　　更快的删除：如果想从表中删除所有行，不要使用delete。可以使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的 表并不重新创建一个表，而不是逐行删除表中的数据）

--更新和删除的指导原则

许多SQL程序员使用UPDATE和DELETE时所遵循的习惯：

　　1、除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的UPDATE和DELETE

　　2、保证每个表都有主键，尽可能想where子句那样使用它（可以指定各主键、多个值或值的范围）

　　3、在对UPDATE或DELETE语句使用where子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的where子句不正确

　　4、使用强制实施引用完整性的数据库，这样MySQL将不允许删除与其他相关联的数据的行

ps：小心使用：MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

----创建和操纵表

--创建表

方法：

　　1、使用具有交互式创建和管理表的工具

　　2、表也可以直接用MySQL语句操纵

--表创建基础

1、新表的名字，在关键字create table之后给出

2、表列的名字和定义，用逗号分隔开

create table customers

(

cust_id     int    not null    auto_increment,

cust_name    char(50)    not null,

primary key (cust_id)

)ENGINE=InnoDB;

语句格式化：MySQL语句中忽略空格。语句可以在一个长行上输出，也可以分成许多行。它们的作用都相同。这允许你以最适合自己的方式安排语句的格式。强烈建议采用某种缩进格式。

处理现有的表：在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

--使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

理解NULL：不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定  ''  （两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字而不是空串指定。

--主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。创建多个列组成的主键primary key(a, b)

主键可以在创建表时定义，或者在创建表之后定义

ps：主键和NULL值：主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

--使用AUTO_INCREMENT

ATUO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操纵时，告诉MySQL自动对该列增量（从而才有这个关键字），给该列赋予下一个可用的值。这样给每个行分配一个唯一的id，从而可以用作主键。

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

ps：覆盖AUTO_INCREMENT：如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值

　　确定AUTO_INCREMENT值：让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。

--指定默认值

create table orderitems

(

    order_num    int    not null,

    order_item    int    not null,

    prod_id    char(10)    not null,

    quantity    int    not null    default   1,

    item_price    decimal(8,2)    not null,

    primary_key(order_num, order_item)

)ENGINE=InnoDB;

上述代码中就设置了quantity的默认值位1.

ps：不允许函数：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量

　　使用默认值而不是NULL值：许多数据库开发人员使用默认值而不是NULL值，特别是对用于计算或数据分组的列更是如此。

--引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它

但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。这些引擎具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。如果省略ENGINE=语句，则使用默认引擎MyISAM，多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

引擎介绍：

　　1、InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索

　　2、MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适用于临时表）

　　3、NyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。

ps：更多知识：所支持引擎的完整列表（及它们之间的不同），参阅https://dev.mysql.com/doc/refman/5.0/en/storage_engines.html

　　外键不能跨引擎：混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

--更新表

为更新表定义，可使用ALTER TABLE语句。但是，在理想状态下，当表中存储数据之后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

为了使用ALTER TABLE更改表结构，必须给出下面的信息：

　　1、在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）

　　2、所作更改的列表

ALTER TABLE vendors

ADD vend_phone char(20);

--------------------------------------

ALTER TABLE vendors

DROP COLUMN vend_phone;

复杂表结构更改一般需要手动删除过程，它设计以下步骤：

　　1、用新的列布局创建一个新表

　　2、使用INSERT SELECT语句从旧表复制数据到新表，如有必要可使用转换函数和计算字段

　　3、检验包含所需数据的新表

　　4、重命名旧表（如果确定，可以删除它）

　　5、用旧表原来的名字重命名新表

　　6、根据需要，重新创建触发器、存储过程、索引和外键

ps：小心使用ALTER TABLE：使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似的，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

--删除表

DROP TABLE customers;

--重命名表

使用RENAME TABLE语句可以重命名一个表

RENAME TABLE backup_customers TO customers,

             backup_vendors TO vendors;

----使用视图

视图的一些常见应用：

　　1、重用SQL语句

　　2、简单复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节

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

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

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

重点：视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

ps：性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建一个复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

--视图的规则和限制

　　1、与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）

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

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

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

　　5、ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖

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

　　7、视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

--使用视图

视图的创建：

　　1、视图用CREATE VIEW语句来创建

　　2、使用SHOW CREATE VIEW viewname;来查看创建视图的语句

　　3、用DROP删除视图，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

--利用视图简化复杂的联结

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

CREATE VIEW productcustomers AS

SELECT cust_name, cust_contact, prod_id

FROM customers, orders, orderitems

WHERE customer.cust_id = orders.cust_id

AND orderitems.order_num = orders.order_num;

ps：创建可重用的视图：创建不受特定数据限制的视图是一种好办法。

--用视图重新格式化检索出的数据

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')

AS vend_title

FROM vendors

ORDER BY vend_name;
-------------------------------------------------------------------
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;

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

视图对于应用普通的WHERE子句也很有用，过滤掉一些不用的字段。

ps：WHERE子句与WHERE子句：如果从视图检索数据时使用了一条WHERE子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

--使用视图与计算字段

SELECT prod_id, quantity, item_price,

            quantity * item_price AS expanded_price

FROM orderitems

WHERE order_num = 20005;

--更新视图

迄今为止的所有视图都是和SELECT语句使用的。然而，视图的数据能否更新？答案视情况而定。

通常视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELET）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有的视图都是可更新的。基本可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

　　1、分组（使用GROUP BY和HAVING）

　　2、联结

　　3、子查询

　　4、并

　　5、聚集函数（Min()、Count()、Sum()等）

　　6、DISTINCT

　　7、导出（计算）列

要记住视图主要是用于数据检索。

ps：可能的变动：未来的MySQL很可能会取消某些限制

　　将视图用于检索：一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERtT、UPDATE和DELETE）

----使用存储过程

--存储过程

迄今位置，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。

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

--为什么要使用存储过程

原因如下：

　　1、通过把处理封装在容易使用的单元中，简单复杂的操作

　　2、由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

　　3、简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化。只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

这一点的延伸就是为了安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

　　1、提高性能。因为适应存储过程比使用单独的SQL语句要快

　　2、存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

总的来说，使用存储过程的3个主要好处就是，简单、安全和高性能。

一般来说，编写存储过程需要安全访问权限。

ps：MySQL将编写存储过程的安全和访问与执行存储过程的安全和访问区分开。即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

--使用存储过程

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

----------执行叫做productpricing的存储过程
CALL productpricing

(

  @pricelow,

  @pricehigh,

  @priceaverage

);

--创建存储过程

CREATE PROCEDURE productpricing()

BEGIN

        SELECT Avg(prod_price)    AS    priceaverage

        FROM     products;

END

BEGIN和END是用来限定存储过程体，过程仅是简单的SELECT语句。

在MySQL处理这段代码时，它创建一个新的存储过程product-pricing。没有返回数据，因为这段代码并未调用存储过程，这里知识为了以后使用而创建。

ps：MySQL命令行客户机的分隔符：如果你使用的是MySQL命令行使用程序，应该仔细阅读此说明。

　　默认的MySQL语句分隔符为；（正如你已经在迄今为止所使用的MySQL语句中所看到的那样）。MySQL命令行实用程序也使用；作为语句分隔符。如果命令行实用程序要解释存储过程自身内的；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现语法错误。

　　解决办法是临时更改命令行使用程序的语句分隔符，如下所示：

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

    SELECT Avg(prod_price) AS priceaverage

    FROM products;

END //
DELIMITER;

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

除 \ 符号外，任何字符都可以用作语句分隔符。

使用存储过程关键字是CALL

CALL productpricing();

--删除存储过程

存储过程创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。

DROP PROCEDURE productpricing;

ps：仅当存在时删除：如果指定的国策灰姑娘不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时（如果过程不存在也产生）可使用DROP PROCEDURE IF EXISTS。

--使用参数

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

变量（variable）：内存中一个特定的位置，用来临时存储过程。

以下时productpricing的修改版本（如果不先删除此存储过程，则不能再次创建它）：

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;

ps：参数的数据类型：存储过程的参数允许的数据类型与表中使用的数据类型相同。

　　注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是前面的例子为什么要使用3个参数的原因。

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

CALL productpricing(

    @pricelow,

    @pricehigh,

    @priceaverage

);

----------------------------------------

SELECT @priceaverage;

----------------------------------------

SELECT @pricelow, @pricehigh, @priceaverage;

变量名：所有MySQL变量都必须以@开始

----------------使用IN和OUT参数

CREATE PROCEDRUE ordertotal(

    IN onumber INT,

    OUT ototal DECIMAL(,)

)

BEGIN

    SELECT Sum(item_price * quantity)

    FROM orderitems

    WHERE order_num = onumber

    INTO ototal;

END;

onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程中返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

为调用这个新存储过程，可以使用以下语句：

CALL ordertotal(20005, @total);

--建立智能存储过程

只有在存储过程内包含业务规则和智能处理时，它们的威力才能真正显现出来。

以下的demo演示下面几个事情：

　　1、获得合计；

　　2、把营业税有条件地添加到合计；

　　3、返回合计（带或不带税）

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

    if taxable then

    select total+(total/100*taxrate) into total;

    end if;

    select total into ototal;

end;

DECLARE语句定义局部变量，要求指定变量名和数据类型，也支持可选的默认值。

ps：COMMENT关键字：不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

--检查存储过程

为显示用来创建一个存储过程的create语句，使用show create procedure语句；

show create procedure ordertotal;

ps：限制过程状态结果：SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，

　　例如：show procedure status like 'ordertotal'

----使用游标

--游标

MySQL5增加了对游标的使用。

MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行（零行或者多行）。使用简单的SELECT语句，例如，没有办法得到第一行、下一行或前10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。

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

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

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

--使用游标

使用游标涉及几个明确的步骤：

　　1、在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的select语句。

　　2、一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。

　　3、对于填有数据的游标，根据需要取出（检索）各行。

　　4、在结束游标使用时，必须关闭游标。

--创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。例如，下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句。

CREATE PROCEDURE processorders()

BEGIN

    DECLARE ordernumbers CURSOR

    FOR

    SELECT order_num FROM orders;

END;

--打开和关闭游标

游标用OPEN CURSOR语句来打开。。

OPEN ordernumbers;

关闭用CLOSE CURSOR，close释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都用改关闭。

CLOSE ordernumbers;

ps：隐含关闭：如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

--使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条（不重复读取同一行）。

ps：DECLARE语句的次序：DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误信息。

　　重复或循环？“除这里使用的REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出位置。通常REPEAT语句的语法使它更适合于对游标进行循环。

-----使用触发器

--触发器

需要MySQL5：对触发器的支持是在MySQL5中增加的。因此，本章内容适用于MySQL5或之后。

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，就需要使用触发器。

触发器时MySQL响应以下任意语句而自动执行的一条SQL语句（或位于BEGIN或END语句之间的一组语句）：

　　DECLARE

　　INSERT

　　UPDATE

其他MySQL语句不支持触发器。

--创建触发器

在创建触发器时，需要给出4条信息：

　　1、唯一的触发器名

　　2、触发器关联的表

　　3、触发器应该响应的活动（DECLARE、INSERT或UODATE）

　　4、触发器何时执行（处理之前或之后）

ps：保持每个数据库的触发器名唯一：在MySQL5中，触发器名必须在每个表中的唯一，但不是在每个数据库中唯一。这表示同一个数据库中的两个表可具有相同的名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围捏使用唯一的触发器名。

CREATE TRIGGER newproduct AFTER INSERT ON products

FOR EACH ROW SELECT 'Product added'

ps：仅支持表：只有表才支持触发器，试图不支持（临时表也不支持)

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

ps：触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）

--删除触发器

使用DROP TRIGGER语句

DROP TRIGGER newproduct;

--使用触发器

-INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

　　1、在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行

　　2、在BEFORE INSERT触发器内，NEW中的值也可以被更新（允许更改被插入的值）

　　3、对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

以下代码，创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它：

CREATE TRIGGER neworder AFTER INSERT ON orders

FOR EACH ROW SELECT NEW.order_num;

ps：BEFORE或AFTER？：通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

--DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下几点：

　　1、在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行

　　2、OLD中的值全都是只读的，不能更新

以下代码，使用OLD保存将要被删除的行到一个存档表中：

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;

ps：多语句触发器：正如所见，触发器deleteorder使用BEGIN和END语句标记触发器体。这在此例子中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

--UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

　　1、在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问更新的值

　　2、在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句的值）

　　3、OLD中的值全都是只读的，不能更新

下面的例子是保证周明缩写总是大写（不管UPDATE语句中给出的是大写还是小写）：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors

FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_stats);

--关于触发器的进一步介绍

重点：

　　1、与其他DBMS相比，MySQL5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划

　　2、创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行

　　3、应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的有点是它总是进行这种处理，而且是透明地进行，与客户机应用无关

　　4、触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易

　　5、遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

----管理事务的处理

--事务处理

ps：并非所有引擎都支持事务处理：MySQL支持几种基本的数据库引擎。并非所有引擎都支持明确的事务处理管理。MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。如果你的应用中需要事务处理功能，则一定要使用正确的引擎类型。

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

在使用事务和事务处理时，有几个关键字会反复出现。下面关于事务处理需要知道的几个术语：

　　1、事务（transaction）指一组SQL语句

　　2、回退（rollbac）指撤销指定SQL语句的过程

　　3、提交（commit）指将未存储的SQL语句结果写入数据库表

　　4、保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）

--控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

MySQL使用下面的语句来标识事务的开始：

START TRANSACTION;

--使用ROLLBACK

MySQL使用ROLLBACK命令用来回退（撤销）MySQL语句：

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

根据上述例子可知，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

ps：哪些语句可以回退：事务管理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句（因为这么做没有意义）。你不能回退CREATE和DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

--使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implict commit），即提交（写或保存）操作是自动进行的。但是，在事务处理某块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

ps：隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）

--使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT语句：

SAVEPOINT delete1;

每个占位符都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO delete1;

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

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

--更改默认的提交行为

正如所述，默认的MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为提示MySQL不自动提交更改，需要使用以下语句：

SET sutocommit=0;

autocommit标志决定是都自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）只是MySQL不自动提交更改（直到autocommit被设置为真为止）。

ps：标志为连接专用：autocommit标志是针对每个连接而不是服务器的。

----全球化和本地化

--字符集和校对顺序

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

重要属于：

　　1、字符集为字母和符号的集合

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

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

ps：校对很重要，因为不同的字符集排序方式不同，比如大小写就是一种校对顺序，还有就是法文或德文等字符，情况更复杂，在不基于拉丁文的字符集（日文、希伯来语、俄文等）时，情况就更为复杂了。

--使用字符集和校对顺序

MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用以下语句：

--------这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。
SHOW CHARACTER SET;

-------此语句显示所有可用的校对，以及它们适用的字符集

SHOW COLLATION;

通常系统管理在安装定义时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句：

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation%';

实际上，字符集很少是服务器范围（甚至是数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。

为了给表指定字符集和校对，可使用带子句的CREATE TABLE，以下语句创建包含两列的表，并且指定一个字符集和一个校对顺序，这个例子中指定了CHARACTER SET和COLLATE。一般，MySQL如下确定使用什么样子的字符集和校对。

　　1、如果指定CHARACTER SET和COLLATE两者，则使用这些值

　　2、如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如果SHOW CHARACTER SET的结果中所示）

　　3、如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。

CREATE TABLE mytable

(

    colum1 INT ,

    colum2 VARCHAR(10)

) DEFAULT CHARACTER SET hebrew

  COLLATE hebrew_general_ci;

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们，如下所示：

CREATE TABLE mytable

(

    colum1 INT,

    colum2 VARCAHR(10),

    colum3 VARCAHR(10) CHARACTER SET latin1 COLLATE latin1_general_ci

) DEFAULT CHARACTER SET hebrew

  COLLATE hebrew_general_ci;

-------这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE

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

SELECT * FROM customers

ORDER BY lastname, fistname COLLATE latin1_general_cs;

----此SELECT使用COLLATE指定一个备用的校对顺序（在这个例子中，为区分大小写的校对）。这显然会影响到结果排序的次序。

ps：临时区分大小写：上面的SELECT语句演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术。当然，反过来也是可以的。

　　SELECT的其他COLLATE子句：除了这里可以看到的在ORDER BY子句中使用以外，COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等。

**值得注意的是，如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。

----安全管理

--访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。

考虑以下内容：

　　1、多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表

　　2、某些用户需要读表，但可能不需要更新表

　　3、你可能想允许添加数据，但不允许他们删除数据

　　4、某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要

　　5、你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据

　　6、你可能想根据用户登录的地点限制对某些功能的访问

这些都只是例子，但有助于说明一个重要的事实，即你需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访问控制，管理访问控制需要创建和管理用户账号。

ps：使用MySQL Administrator：MySQL Administrator提供了一个图形用户界面，可用来管理用户及账户权限。MySQL Administrator在内部利用本章介绍的语句，使用能交互地、方便地管理访问控制。

我们知道，为了执行数据库操作，需要登录MySQL。MySQL创建一个名为root的用户账户，它对整个MySQL服务器具有完全的控制。但在实际应用中，应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用。

ps：防止无意的错误：重要的是注意到，访问控制的目的不仅仅是防止用户的恶意企图。数据梦魇更为常见的是无意识错误的结果，如打错MySQL语句，在不适合的数据库中操作或其他一些用户错误。通过保证用户不能执行他们不应该执行的语句，访问控制有助于避免这些情况的发生。

　　不要使用root：应该严肃对待root登录的使用。仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用root。

--管理用户

MySQL用户账号和信息存储在名为mysql数据表中。一般不需要直接访问mysql数据库和表，但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此，可使用以下代码：

USE mysql;

SELECT user FROM user;

ps：用多个客户机进行试验：试验对用户账号和权限进行更改的最好办法是打开多个数据库客户机（如mysql命令行实用程序的多个副本），一个作为管理登录，其他作为被测试的用户登录。

--创建用户账号

为创建一个新用户账号，使用CREATE USER语句，如下所示：

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

----CREATE USER创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子用IDENTIFIED BY 'p@$$w0rd'给出了一个口令，如果你再次列出用户账号，将会在输出中看到新账号。

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

　　使用GRANT或INSERT：GRANT语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这么做。MySQL用来存储用户账号信息的表（以及表模式等）极为重要，对他们的任何毁坏都可能严重地伤害到MySQL服务器。因此，相对于直接处理来说，最好是用标记和函数来处理这些表。

为重新命名一个用户账号，使用RENAME USER语句，如下所示：

RENAME USER ben TO bforta;

--删除用户账号

为了删除一个用户账号（以及相关的权限），使用DROP USER语句，如下所示：

DROP USER bforta;

ps：MySQL 5之前：自MySQL 5以来，DROP USER删除用户账号和所有相关的账号和权限。在MySQL5以前，DROP USER只能用来删除用户账号，不能删除相关的 权限。因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

--设置访问权限

在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

为看到赋予用户账号的权限，使用SHOW GRANT FOR，如下所示：

SHOW GRANT FOR bforta;

输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限，所以此结果表示在任意数据库和任意表上对任何东西没有权限。

ps：用户定义为user@host：MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

　　1、要授予的权限

　　2、被授予访问权限的数据库或表

　　3、用户名

以下给出GRANT的用法：

GRANT SELECT ON crashcourse.* TO bforta;

SHOW GRANT反应这个更改：

SHOW GRANT FOE beforta;

GRANT的反操作为REVOKE，用它来撤销特定的权限，举个例子：

REVOKE SELECT ON crashcourse.* FROM bforta;

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

　　1、整个服务器，使用GRANT ALL和REVOKE ALL

　　2、整个数据库，使用ON database.*

　　3、特定的表，使用ON database.table

　　4、特定的列

　　5、特定的存储过程

以下列出可以授予或撤销的每个权限：

使用GRANT和REVOKE，在结合上表列出权限，你能用户可以就你的宝贵数据做什么事情和不能做什么事情具有完全的控制。

ps：未来的授权：在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。还允许管理员在创建数据库的和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在，而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

　　简化多次授权：可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所示：

GRANT SELECT, INSERT, ON crashcourse.* TO bforeta;

--更改口令

为了更改用户口令，可使用SET PASSWORD语句。新口令必须如下加密：

SELECT PASSWORD FOR bforta = Password('n3w p@$$w0rd');
-----分析：SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

SET PASSWORD还可以用来设置你自己的口令：

SET PASSWORD = Password('n3w p@$$w0rd');

----在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

----数据库维护

--备份数据

解决普通的文件副本不一定总是有效的方案：

　　1、使用命令行使用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件

　　2、可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）

　　3、可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原

ps：首先刷新未写数据：为了保证所有数据被写道磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

--进行数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。

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

ANALIZE TABLE orders;

-CHECK TABLE，用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检查，QUICK只进行快速扫描。如下所示，CHECK TABLE发现和修复问题：

CHECK TABLE orders, orderitems;

-如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

-如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所有的空间，从而优化表的性能。

--诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题上发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

　　1、--help显示帮助———一个选项列表

　　2、--safe-mode装载减去某些最佳配置的服务器

　　3、--verbose显示全文本消息（为获得更详细的帮助消息与--help联合使用）

　　4、--version显示版本信息然后退出

--查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种：

　　1、错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用--log-error命令行选项更改。

　　2、查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志文件通常名为hostname.log，位于data目录中。此名字可以用--log命令行选项更改。

　　3、二进制日志。它记录更新过数据（或者可能更新过的数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用--log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。

　　4、缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用--log-slo-queries命令行选项更改。

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

----改善性能

　　1、首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议

　　2、一般来说，关键的生产DBMS应该运行在自己的专用服务器上

　　3、MySQL是用一系列的默认设置预先设置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;）

　　4、MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）

　　5、总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳方法

　　6、使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句

　　7、一般来说，存储过程执行的比一条一条地执行其中的各条MySQL语句快

　　8、应该总是使用正确的数据类型

　　9、决不要检索比需要还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）

　　10、有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作

　　11、在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们

　　12、必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象

　　13、你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能的改进

　　14、索引改善数据索引的性能，但损害数据插入、删除和更行的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除）

　　15、LIKE很慢。一般来说，最好使用FULLTEXT而不是LIKE

　　16、数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变

　　17 、最重要的规则就是，每条规则在某些条件下都会被打破

ps：浏览文档：http://dev.mysql.com/doc，更多的内容参考MySQL手册

------------------------------------------------

本文章参考自《mysql必知必会》