sql规范

• 关键字用大写，列和表名用小写
  
• 长的sql语句要分行写（因为sql忽略空格，分行不影响执行）
  
• 除非确实要用到所有列，否则一般不在select中使用*，因为降低检索效率和性能
  
• sql过滤还是代码（应用）过滤？
  一般情况下，我们都应使用sql过滤，因为用代码对查询出来的结果进行过滤会极大地影响应用的性能
  
• 当心自动生成的schema
  如果schema是自动生成的，一定要反复检查确认没有问题
  ORM框架是另一种常见的性能噩梦，一些ORM系统会存储任意类型的数据到任意类型的后端数据存储中，这通常意味着其没有设计使用更优的数据类型来存储。有时会为每个对象的每个属性使用单独的行，甚至使用基于时间戳的版本控制，导致单个属性会有多个版本存在
  ORM框架对开发者很有吸引力，因为这使得他们可以用面向对象的方式工作，不需要考虑数据是怎么存储的。然而，“对开发者隐藏复杂性”的应用通常不能很好地扩展
  
• 任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确地分组操作符
  
• 关于Like模糊匹配中使用通配符：

• 通配符搜索的处理一般比其他搜索所花时间更长，不要过度使用通配符。若其他操作符能达到相同的目的，应该使用其他操作符
  
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的
  

• 转换和格式化工作如果能在SQL语句内完成，则尽量在SQL语句内完成
  可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作要比在应用程序中完成快得多
  
• 如果要的是日期，请使用Date()
  
• 应保证所有联结（join操作，即from aaa,bbb）都有where子句，否则MySQL将返回比想要的数据多得多的数据。同理，应保证where子句的正确性
  
• 一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使MySQL代码继续发挥作用，即使表结构发生了变化
  
• 在使用UPDATE时不能省略WHERE子句，因为稍不注意，就会更新表中所有行
  
• 在使用DELETE时不能省略WHERE子句，因为稍不注意，就会错删表中所有行
  
• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句
  
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确
  
• 在建表时，尽量使用默认值而不是NULL值，特别是对用于计算或数据分组的列更是如此
  
• 为更新表定义，可使用ALTER TABLE语句。但是，在理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动
  
• 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，若增加了不需要的列，可能不能删除它们。类似地，若删除了不该删除的列，可能会丢失该列中的所有数据
  
• 复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表
  
• 使用INSERT SELECT语句从旧表复制数据到新表。若有必要，可使用转换函数和计算字段
  
• 检验包含所需数据的新表
  
• 重命名旧表（如果确定，可以删除它）
  
• 用旧表原来的名字重命名新表
  
• 根据需要，重新创建触发器、存储过程、索引和外键
  

• 一般，应该将视图用于检索（SELECT)，而不用于更新（INSERT、UPDATE、DELETE）
  
• 所有MySQL变量都以@开始
  
• 决不要检索比需求还要多的数据。换言之，不要用SELECT * （除非你真正需要每个列）
  

sql优化

• 更小的通常更好
  描述：一般情况下，应尽量使用可以正确存储数据的最小数据类型
  原因：占更少磁盘、内存和CPU缓存
  注意：要确保没有低估需要存储的值的范围，如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型
  
• 简单就好
  描述：简单数据类型的操作需要更少的CPU周期
  例子：应使用MySQL内建的类型（date、time、datetime）而不是字符串来存储日期和时间；应用整形存储IP地址
  
• 尽量避免NULL
  描述：通常情况下最好指定列为Not Null，除非真的需要存储null值
  原因：如果查询中包含可为NULL的列，对MySql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySql中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节
  注意：通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列
  
• 尽量只在对小数进行精确计算时才使用DECIMAL类型
  原因：DECIMAL类型用于存储精确的小数，MySQL实现了DECIMAL的高精度计算。但DECIMAL需要额外的空间和计算开销
  注意：在数据量比较大的时候，可以考虑使用bigint代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在bigint里，这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题
  
• varchar与char类型
  下面这些情况下使用varchar是合适的：字符串列的最大长度比平均长度大很多，列的更新很少，所以碎片不是问题，使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储
  原因：varchar比定长类型更节省空间，因为它仅使用必要的空间。但是，由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作
  CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率
  
• 慷慨是不明智的
  描述：分配真正需要的空间是最好的策略。例如，使用varchar(5)和varchar(200)存储'hello'的空间开销是一样的，但更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
  
• 除了特殊行为之外，通常应该尽量使用timestamp，因为它比datetime空间效率更高
  原因：TIMESTAMP只使用4个字节的存储空间，只能表示从1970~2038年。DATETIME使用8个字节的存储空间，能保存大范围的值，从1001年到9999年
  注意：DATETIME与时区无关，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中。TIMESTAMP与时区有关
  
• 应谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型
  
• 为标识列选择合适的数据类型非常重要。整数类型通常是标识列最好的选择
  标识列又称为自增长列，即可以不手动地插入值，由系统提供默认的序列值
  通常选择整数类型的原因是因为它们很快并且可以使用AUTO_INCREMENT
  
• 特殊类型数据例子：IPv4地址
  人们经常使用varchar(15)来存储IP地址，然而，IPv4地址实际上是32位无符号整数，不是字符串。用小数点将地址分为四段的表示方式只是让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换
  
• 自联结与子查询。自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好
  
• MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快
  
• 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快
  
• 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。若一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象
  
• 若SELECT语句中有一系列复杂的OR条件，则可以通过使用多条SELECT语句和连接它们的UNION语句，得到极大的性能改进
  
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。若你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们（索引可根据需要添加和删除）
  
• LIKE模糊搜索很慢，一般来说，最好使用FULLTEXT而不是LIKE
  

sql技巧

• select可以进行测试和试验函数计算，select可以省略from子句以便简单地访问和处理表达式
  例如：select 3 * 2;select now()
  
• 若想从表中删除所有行，不要使用DELETE,可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际上是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）
  
• 默认的MySQL语句分隔符为;     命令行程序使用；作为语句分隔符，若它要解释存储过程自身内的；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决方法是临时更改命令行程序的语句分隔符为//，即DELIMITER //，存储过程END之后跟//，存储过程创建完毕后，再DELIMITER ;将分隔符改回来
  
• 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关
  
• MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。若这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。若遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用该命令需要管理员权限）
  

连接MySQL命令

mysql -u xxx -p -h xxx -P xxx

-h表示主机名

-P表示端口

Use命令

use xxx

使用哪个数据库

show命令

show databases

展示数据库

show tables

展示库中的表

show columns from xxx

展示表中的列

show status

显示mysql服务器的所有信息

show create database xxx

显示创建数据库的语句

show create table xxx

显示创建数据表的语句

show grants

显示所有用户的安全权限

show errors

显示服务器错误信息

show warnings

显示服务器警告信息

describe命令

describe xxx

和 show columns from xxx一样的作用，展示表中的列

查

select xxx from aaa

从aaa表中查xxx列

select xxx,xxx,xxx from aaa

多列查询

select * from aaa

查询所有列

select distinct xxx from aaa

只返回不同的值，不会出现重复值

需要注意的是，DISTINCT关键字用于所有列而不仅是前置它的列

比如这里，两个卡里欧name相同，但是因为money不同，所以都被显示出来，即money也需要distinct，若这两个卡里欧money相同，才会只显示一个

select xxx from aaa limit n

limit n表示返回的结果不超过n个

select xxx from aaa limit s,n

返回从行s开始的共n行，注意第一行是行0

带一个值的limit即从第一行开始，返回共n行，带两个值的limit可指定从某行号开始

在行数不够时，能够返回多少行就返回多少行

select xxx from aaa limit n offset s

为了防止上一种语句引起误解，添加了offset关键字，表示从s行开始取一共n行数据

Order   By

select xxx from aaa order by xxx

检索结果根据xxx列进行默认排序，默认排序是升序

order by字句所选择的列可以是检索列，也可以是非检索列

select aaa,bbb from ccc order by aaa,bbb

想根据多列进行排序，用逗号分隔即可

写在前面的列先排序

select aaa,bbb from ccc order by aaa desc

desc表示降序排序

select a,b from c order by a desc,b

先按a降序排序，再按b升序排序

desc关键字只应用到直接位于其前面的列名

若想在多个列上进行降序排序，必须对每个列指定desc关键字

与desc相反的关键字是asc，但可以不写，因为升序是默认的

在字典序排序中，mySql认为a和A相同，不过也可以自定义设置

最大最小值

select a from b order by a desc limit 1

最大值

select a from b order by a limit 1

最小值

注意order by字句需放在from字句后，limit字句需放在order by字句后

过滤

select a from b where a = xxx

在同时使用where和order by时，order by应在where之后

mysql在执行匹配时默认不区分大小写

单引号''用于限定字符串

所有的where字句操作符

select a,b from c where b < xxx

select a,b from c where b <> xxx

select a,b from c where b != xxx

select a,b from c where b between xx and xxx

在使用between时，必须指定两个值——所需范围的最低值和最高值。这两个值必须用and关键字分隔。between匹配范围中所有的值，包括指定的开始值和结束值

select a from c where b is null

返回没有b（b为null）对应行的a值，null表示无值、没有值（no value）

在通过过滤选择不具有特定值的行时，该列为null的行不会被返回，因为未知具有特殊的含义，数据库不知道它们是否匹配，因此在匹配过滤或不匹配过滤时都不返回它们

例如：

组合where子句

select a from c where b = xxx and a = xxx

select a from c where b = xxx or a = xxx

select a from c where (b < xxx or d > xxx) and e = xxx

组合and或or记得打括号

In操作符

in操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中

in操作符也可以用or操作符替代，但in操作符有其特有的好处：

1、in操作符一般比or操作符执行更快

2、in的最大优点是可以包含其他select语句，使得能够更动态地建立where字句

select a from b where c in (xxx,xx,xxx)

not操作符

MySQL支持使用not对in、between和exists字句取反

select a from b where c not in (xx,xxx,xxxx)

用通配符进行过滤

Like操作符

为在搜索字句中使用通配符，必须使用like操作符。like指示MySql，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

百分号%通配符——select a from b where c like 'xx%'

%表示任何字符出现任意次数

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符

除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符

注意尾空格：

尾空格可能会干扰通配符匹配。例如，在保存词anvil时，若它后面有一个或多个空格，则字句where prod_name like '%anvil'将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单方法是在搜索模式最后附加一个%。更好的方法是使用函数去掉首尾空格

注意NULL：

%并不能匹配null

下划线_通配符——select a from b where c like ' _ xx'

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少

正则表达式

正则表达式是用来匹配文本的特殊的串（字符集合）

select a from b where c REGEXP '.xxx'

小数点.是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符

like与REGEXP的重要区别：

like匹配整个列，而REGEXP在列值内进行匹配。什么意思？举个例子，若列name中有一个'JetPack 1000'，使用select name from xxx where name like '1000'将不会找到匹配的列，而select name from xxx where name REGEXP '1000'将返回该列

REGEXP在列值内进行匹配，若被匹配的文本在列值中出现，REGEXP将找到它，相应的行将被返回。REGEXP也能用来匹配整个列值，使用^和$定位符即可

需注意，MySQL中的正则表达式匹配不区分大小写（即，大写和小写都匹配）。为区分大小写，可以使用BINARY关键字，如select name from xxx where name REGEXP BINARY '1000'

正则表达式|

为搜索两个串之一（或者为这个串，或者为另一个串），使用|

|相当于or匹配，可给出多个or条件，例如'1000|2000|3000'

select a from b where c REGEXP 'xx|xxx'

正则表达式 []

若只想匹配特定的字符，可通过指定一组用[和]括起来的字符来完成

select a from b where a REGEXP '[123] xxx'

这里，使用了正则表达式[123]，[123]定义一组字符，它的意思是匹配1或2或3

[]是另一种形式的or语句。事实上，正则表达式[123]Ton是[1|2|3]Ton的缩写

select a from b where a REGEXP '[ ^123 ] xxx'

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可

比如[ ^ 123]匹配除1或2或3外的任何东西

正则表达式 -

集合[]可用来定义要匹配的一个或多个字符。可在其中使用 - 来定义一个范围，例如[0-9]表示匹配数字，[a-z]匹配任意字母字符

select a from b where a REGEXP '[1-3] xxx'

正则表达式 \ \

正则表达式语言由具有特定含义的特殊字符构成。如果要匹配这些特殊字符，需用\ \为前导进行转义

\ \ 也用来引用元字符（具有特殊含义的字符），如下表所示：

注意，为了匹配反斜杠( \ )字符本身，需要使用\ \ \

select a from b where a REGEXP '\ \ .'

匹配字符类

为更方便工作，可以使用预定义的字符集，称为字符类，如下表所示：

匹配多个实例

有时需要对匹配的数目进行更强的控制，可以使用正则表达式重复元字符来完成，重复元字符如下表所示：

举几个例子：

select a from b where c REGEXP '\ \ ([0-9] sticks?'\ \ )'

\ \ (匹配左括号(，[0-9]匹配任意数字，sticks?匹配stick和sticks，因为s后的？表示s可选，s可以出现也可以不出现，?匹配它前面的任何字符的0次或1次出现

select a from b where c REGEXP '[[:digit:]]{4}'

[:digit:]为数字的集合，{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字

定位符

为了匹配特定位置的字符，需要使用下表列出的定位符：

例如，若想找出以一个数（或小数点）开始的所有product：

select prod_name from products where prod_name REGEXP '^[0-9\ \ .]'

^匹配串的开始，因此，^[0-9\ \ .]只在.或任意数字为串的第一个字符时才匹配它们

注意^具有双重用途：

^在集合中（用[和]定义），用来否定该集合，否则，用来指串的开始处

Like和REGEXP的不同在于，Like匹配整个串而REGEXP匹配子串，利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与Like一样

可以使用字符串来测试正则表达式，不需要使用数据库，例如：

select 'hello' REGEXP '[0-9]'

REGEXP检查在这种情况下总是返回0（未匹配成功），或1（匹配成功）

创建计算字段

计算字段是运行时在select语句内创建的，其实就是对查出的列进行处理，例如拼接字段处理

拼接字段——Concat()

select Concat(name, '(', country, ')') from user

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔

去除空格——RTrim()、LTrim()、Trim()

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

select RTrim(name) from user

使用别名——AS

别名是一个字段或值的替换名。别名用AS关键字赋予

select Concat(name, '(', country, ')') as title from user

计算字段之后跟了as，指示SQL创建一个包含指定计算的名为title的计算字段。这样任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。

执行算术计算

MySQL支持+、-、*、/四种基本算术操作

select id, quantity, price , price * quantity as total_price from order

数据处理函数

文本处理函数

常用的文本处理函数如下标所示：

SOUNDEX是一个将任何文本串转换为描述其语言表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较

下面给出一个SOUNDEX的例子，name中有一个Y.Lee，但若这是输入错误，实际应该是Y.Lie，那么可以使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的name：

日期和时间处理函数

常用的日期和时间处理函数如下表所示：

需要注意的是MySQL使用的日期格式。无论什么时候指定一个日期，不管是插入或更新表值还是用WHERE字句进行过滤，日期必须为格式yyyy-mm-dd。应该总是使用4位数字的年份

数值处理函数

数值处理函数用于代数、三角或几何计算，常用的数值处理函数如下表所示：

汇总数据

聚集函数

聚集函数指运行在行组上，计算和返回单个值的函数

常用的聚集函数如下表所示：

select AVG(a) from b where c = xxx

AVG()函数忽略列值为NULL的行

select COUNT(*) from a

count(*)对表中行的数目进行计数，不管表列中包含的是空值Null还是非空值

select COUNT(column) from a

count(column)对特定列中具有值的行进行计数，忽略Null值

select MAX(a) from b

MAX()返回指定列中的最大值，MAX()要求指定列名

虽然Max()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，若数据按相应的列排序，则MAX()返回最后一行

Max()函数忽略列值为Null的行

select MIN(a) from b

min()的功能与max()相反

select SUM(a) from b where c = xxx

sum()用来返回指定列值的和（总计）

select SUM(a * b) from c where d = xxx

利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算

sum()函数忽略列值为Null的行

聚集不同值

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

• 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）
  
• 只包含不同的值，指定DISTINCT参数
  

select AVG(DISTINCT a) from b where c = xxx

DISTINCT必须使用列名，不能用于计算或表达式

组合聚集函数

select count(*) as num, MIN(a) as a_min, MAX(b) as b_max, AVG(c) as c_avg from d

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

创建分组

分组是在select语句的group by字句中建立的

GROUP BY字句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集

需要注意的点有：

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

select id, count(*) as num from product GROUP BY id

过滤分组

HAVING过滤分组，HAVING支持所有WHERE操作符，也就是说绝大部分情况下，Where都可以用Having替代

select id, count(*) as num from product GROUP BY id HAVING COUNT( * ) >= xxx

select id,count(*) as num from product where price >= xx GROUP BY id HAVING count( * ) >= xxx

Where在数据分组前进行过滤，Having在数据分组后进行过滤。Where排除的行不包括在分组中。这可能会改变计算值，从而影响Having子句中基于这些值过滤掉的分组

分组和排序

一般在使用GROUP BY子句时，应该也给出Order By子句。这是保证数据正确排序的唯一方法。

select num, SUM(q * p) as total from items GROUP BY num HAVING SUM(q * p) >= xxx ORDER BY total

使用子查询

子查询即嵌套查询

利用子查询进行过滤

可以把一条select语句返回的结果用于另一条select语句的where子句

select user_id from orders where order_id in (select order_id from orderitems where product_id = 'xxx')

在select语句中，子查询总是从内向外处理

select username from user where user_id in (select user_id from orders where order_id in (select order_id from orderitems where product_id = 'xxx'))

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询

作为计算字段使用子查询

select user_name, (select count(*) from orders where orders.user_id = user.user_id) as orders from user

这种类型的子查询称为相关子查询，指涉及外部查询的子查询

任何时候只要列名可能有多义性，就必须使用完全限定的列名

联结表

联结

select user_name, prod_name from user, products where user.user_id = products.user_id

from字句列出了两个表，它们就是这条select语句联结的两个表的名字。这两个表用where子句正确联结，where子句指示MySQL匹配两个表中的user_id

要匹配的两个列使用完全限定列名，在引用的列可能出现二义性时，必须使用完全限定列名

在一条select语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对。Where子句作为过滤条件，它只包含那些匹配给定条件的行

内联结

select user_name,prod_name from user Inner Join products  on user.user_id = products.user_id

上述Sql中，两个表之间的关系是from子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用on而不是where

内联结即等值联结，它基于两个表之间的相等测试

上面的sql和select user_name, prod_name from user, products where user.user_id = products.user_id 是等价的

联结多个表

select prod_name, user_name, prod_price, quantity from orderitems, products, users where products.user_id = users.user_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害

创建高级联结

使用表别名

select user_name from users as u, orders as o, orderitems as oi where u.user_id = o.user_id and oi.order_num = o.order_num and prod_id = 'TNT2'

表别名只在查询执行中使用

使用不同类型的联结

自联结

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.user_id = p2.user_id AND p2.prod_id ='xxx'

自联结中需要的两个表实际上是相同的表，WHERE（通过匹配p1中的user_id和p2中的user_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据

上述SQL语句与SELECT prod_id, prod_name FROM products WHERE user_id = (SELECT user_id FROM products WHERE prod_id ='xxx')等价

自然联结

标准的联结（内联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

外连接

若联结包含那些在相关表中没有关联行的行，则称为外连接

SELECT user.user_id, orders.order_num from users LEFT OUTER JOIN orders ON user.user_id = orders.user_id

上述SQL检索所有客户，包括那些没有订单的客户，例如（10,NULL）将被检索出来，10号客户没有订单

上述SQL语句使用了关键字OUTER JOIN来指定联结的类型。与内联结关联两个表中的行不同的是，外连接还包括没有关联行的行。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT关键字指出的是OUTER JOIN左边的表）

使用带聚集函数的连接

SELECT users.user_name, users.user_id, COUNT(orders.order_num) AS num_ord from users INNER JOIN orders ON users.user_id = orders.user_id GROUP BY users.user_id

SELECT users.user_name, users.user_id, COUNT(orders.order_num) AS num_ord FROM users LEFT OUTER JOIN orders ON users.user_id = orders.user_id GROUP BY users.user_id

使用外连接来包含所有客户，甚至包含那些没有下任何订单的客户

组合查询

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

在两种情况下需要使用组合查询：

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

多数情况下，组合相同表的两个查询完成的工作与具有多个where子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出

创建组合查询

select user_id, prod_id, prod_price from products where prod_price <= 5 UNION select user_id, prod_id, prod_price from products where user_id IN (1001, 1002)

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集

UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION

上述SQL中，UNION指示MYSQL执行两条SELECT语句，并把输出组合成单个查询结果集

UNION使用的规则如下：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，若组合4条SELECT语句，将要使用3个UNION关键字）
  
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
  
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型
  

UNION将从查询结果集中自动去除重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。在使用UNION时，重复的行会被自动取消

这是UNION的默认行为，如果需要，可以改变它。若想返回所有匹配行，可使用UNION ALL而不是UNION

当需要对组合查询结果排序，在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果

全文本搜索

MyISAM支持全文本搜索，InnoDB不支持。

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行

为了进行全文本搜索，必须索引被搜索的列，在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索

启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。记得还要指定存储引擎为MyISAM，如：

CREATE TABLE productnotes

(

note_idint NOT NULLAUTO_INCREMENT,

prod_idchar(10)NOT NULL,

note_datedatetimeNOT NULL,

note_texttextNULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

)ENGINE=MyISAM

MySQL根据子句FULLTEXT(note_text)的指示对列note_text进行索引。这里的FULLTEXT索引单个列，如果需要也可指定多个列。在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

注意不要在导入数据时使用FULLTEXT，若正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该先导入所有数据，然后再修改表，定义FULLTEXT，这样有助于更快地导入数据

进行全文本搜索

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit')

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

Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本

注意传递给Match()的值必须与FULLTEXT()定义中的相同，若指定多个列，则必须列出它们（且次序正确）

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

上述SQL也可以用LIKE子句完成：SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';但使用全文本搜索的方式将返回以文本匹配的良好程度排序的数据。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。并且，由于数据是索引的，因此全文本搜索还比模糊查询快

使用查询扩展

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

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

利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION)

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

布尔文本搜索

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

• 要匹配的词
  
• 要排斥的词（若某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）
  
• 排列提示（指定某些词比其他词更重要，更重要的词等级更高）
  
• 表达式分组
  

布尔方式即使没有FULLTEXT索引，也可以使用，但会非常缓慢

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE)

此全文本搜索检索包含词heavy的所有行。其中使用了关键词IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同

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

上述SQL匹配包含heavy但不包含任意以rope开始的词的行

所有的全文本布尔操作符如下表所示：

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE)

上述搜索匹配包含词rabbit和bait的行

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait'INBOOLEAN MODE)

没有指定操作符，上述搜索匹配包含rabbit和bait中的至少一个词的行

SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"'INBOOLEAN MODE)

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait

SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot'INBOOLEAN MODE)

匹配rabbit和carrot，增加前者的等级，降低后者的等级

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)'INBOOLEAN MODE)

匹配词safe和combination，降低后者的等级

注意布尔方式排列而不排序，它不按等级值降序排序返回的行

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）
  
• MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
  
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则， 若一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
  
• 若表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）
  
• 忽略词中的单引号。例如，don't索引为dont
  
• 不具有词分隔符的语言（汉语），不能恰当地返回全文本搜索结果
  
• 仅在MyISAM引擎中支持全文本搜索
  
• MySQL全文本搜索不支持邻近操作符，邻近搜索指搜索相邻的词（在相同的句子中、相同的段落中或在特定数目的词的部分中等）
  

增

数据插入

INSERT是用来插入（或添加）行到数据库表的。插入可以用几种方式使用：

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

插入完整的行

INSERT INTO users VALUES(NULL,'i', 'Los Angeles'，NULL）

基本的INSERT语法，它要求指定表名和被插入到新行中的值

存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值，应该使用NULL值（假定表允许对该列指定空值）。各个列必须以它们在表定义中出现的次序填充。

第一列主键id也为NULL，这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值，又不能省略此列，所以指定一个NULL值

上述SQL语句并不安全，应尽量避免使用，INSERT语句应使用下面这种更安全的方式：

INSERT INTO users(user_id, user_name, user_address, user_email) VALUES(NULL, 'i', 'Los Angeles' ,NULL)

上述SQL在表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项

使用这种语法，还可以省略列。这表示可以只给某些列提供值，给其他列不提供值

user_id的NULL值是不必要的，user_id列并没有出现在列表中，所以不需要任何值

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

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

若对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误信息

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

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

INSERT LOW_PRIORITY INTO

插入多个行

INSERT INTO users(user_name, user_address) VALUES ('ppp', 'CA'); INSERT INTO users(user_name, user_address) VALUES ('xxx', 'NY');

若想插入多个行，可以使用多条INSERT语句，甚至一次性提交它们，每条语句用一个分号结束

INSERT INTO users(user_name, user_address) VALUES ('ppp', 'CA'), ('xxx', 'NY‘）

只要每条INSERT语句中的列名（和次序）相同，可以使用上述SQL，其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔

插入检索出的数据

可以利用INSERT将一条SELECT语句的结果插入表中，这就是INSERT SELECT，顾名思义，它是由一条INSERT语句和一条SELECT语句组成的

INSERT INTO users(user_id, user_name, user_email) SELECT user_id, user_name, user_email FROM users_old

上述SQL将一个名为users_old的表中的数据导入users表中。使用INSERT SELECT从users_old表中将所有数据导入users

这个例子导入了user_id（假设能确保user_id的值不重复）。也可以省略此列，这样MySQL就会自动生成主键user_id

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

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

改

更新数据

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

• 更新表中特定行
  
• 更新表中所有行
  

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

• 要更新的表
  
• 列名和它们的新值
  
• 确定要更新行的过滤条件
  

UPDATE users SET user_email = 'xxx' WHERE user_id = xxx

UPDATE语句以WHERE子句结束，它告诉MySQL需要更新哪一行。没有WHERE子句，MySQL将会用这个email更新users表中的所有行，这不是我们所期望的

UPDATE users SET user_name = 'xxx', user_email = 'xxx' WHERE user_id = xxx

在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔

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

若用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。即使是发生错误，也继续进行更新，可使用IGNORE关键字，如：

UPDATE IGNORE users ...

UPDATE users SET user_email = NULL WHERE user_id = xxx

为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）

删

删除数据

为了从一个表中删除数据，使用DELETE语句。可用两种方式使用DELETE：

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

DELETE FROM users WHERE user_id = xxx

DELETE FROM要求指定从中删除数据的表名。WHERE子句过滤要删除的行

若省略WHERE子句，它将删除users表中每个user

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身

创建和操纵表

创建表

使用CREATE TABLE创建表，需要给出下列信息：

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

CREATETABLE users

(

   user_id intNOTNULL AUTO_INCREMENT,

   user_name char(50) NOTNULL,

   user_email char(255) NULL,

   PRIMARY KEY (user_id)

) ENGINE = InnoDB

在创建新表时，指定的表名必须不存在，否则将出错。若要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它

若仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定

主键

若主键使用单个列，则它的值必须唯一。若使用多个列，则这些列的组合值必须唯一

为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名，如下所示：

CREATETABLE orderitems

(

   order_num intNOTNULL,

   order_item intNOTNULL,

   prod_id char(10) NOTNULL,

   PRIMARY KEY(order_num, order_item)

) ENGINE=InnoDB

主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识

AUTO_INCREMENT

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

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

若一个列被指定为AUTO_INCREMENT，可以简单地在INSERT语句中指定一个值，只要它是唯一的即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值

让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。可以通过last_insert_id()函数返回最后一个AUTO_INCREMENT值，如：SELECT last_insert_id()

指定默认值

若在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定

CREATETABLE orderitems

(

   order_num intNOTNULL,

   order_item intNOTNULL,

   prod_id char(10) NOTNULL,

   quantity intNOTNULL DEFAULT 1,

   PRIMARY KEY (order_num, order_item)

) ENGINE=InnoDB

MySQL不允许使用函数作为默认值，它只支持常量

引擎类型

引擎类型可以混用，但外键不能跨引擎，使用一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

可使用ALTER TABLE更改表结构，必须给出以下信息：

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

ALTER TABLE users ADD user_phone CHAR(20)

上述SQL给users表增加一个名为user_phone的列，必须明确其数据类型

ALTER TABLE users DROP COLUMN user_phone

删除表中的列

ALTER TABLE orders ADD CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(user_id)

上述SQL用于定义外键

为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔

删除表

DROP TABLE users

删除表（删除整个表而不只是内容），使用DROP TABLE语句即可

删除表没有确认，也不能撤销，执行这条语句将永久删除该表

重命名表

RENAME TABLE users TO users2

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

RENAME TABLE所做的仅是重命名一个表，可以使用下面的语句对多个表重命名：

RENAME TABLE users TO users1,

            users2 TO users3

使用视图

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

视图的一些常见应用如下：

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

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

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

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

视图的规则和限制

以下是关于视图创建和使用的一些最常见的规则和限制：

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

使用视图

• 视图用CREATE VIEW语句来创建
  
• 使用SHOW CREATE VIEW viewname来查看创建视图的语句
  
• 用DROP删除视图，其语法为DROP VIEW viewname
  
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。若要更新的视图不存在，则CREATE OR REPLACE会创建一个视图，否则会替换原有视图
  

利用视图简化复杂的联结

CREATE VIEW productusers AS SELECT user_name, prod_id FROM users, orders, orderitems WHERE users.user_id = orders.user_id AND orderitems.order_num = orders.order_num

可以在视图中进行检索：SELECT user_name FROM productusers WHERE prod_id = 'xxx'

在MySQL处理上述查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据

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

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

CREATE VIEW userlocations AS SELECT Concat(RTrim(user_name), '(', RTrim(user_country), ')') AS user_loc FROM users

用视图过滤不想要的数据

CREATE VIEW usersemaillist AS SELECT user_id, user_name, user_email FROM users WHERE user_email IS NOT NULL

使用视图与计算字段

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

更新视图

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表，若对视图增加或删除行，实际上是对其基表增加或删除行。

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

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

使用存储过程

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

使用存储过程的理由如下：

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

执行存储过程

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

CALL productpricing(@pricelow, @pricehigh, @priceaverage)

存储过程可以显示结果，也可以不显示

创建存储过程

CREATE PROCEDURE productpricing()

BEGIN

   SELECT Avg(prod_price) AS priceaverage

   FROM products;

END;

上述存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义，若存储过程接收参数，将在（）中列举出来

BEGIN和END语句用来限定存储过程体

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

使用CALL productpricing()即可使用上述存储过程

删除存储过程

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

DROP PROCEDURE XXX

若指定的过程不存在，则DROP PROCEDURE将产生一个错误。当只在过程存在时才删除（若不存在也不报错），可使用DROP PROCEDURE IF EXISTS

使用参数

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

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;

上述存储过程接收3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数

上述存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量中（通过指定INTO关键字）

存储过程的参数允许的数据类型与表中使用的数据类型相同。但不能通过一个参数返回多个行和列

为调用上述存储过程，必须指定3个变量名，如：

CALL productpricing(@pricelow, @pricehigh, @priceaverage)

这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

在调用时，这条语句并不显示任何数据。它返回之后可以显示或使用的变量

如：

SELECT @priceaverage

接下来再看另一个例子，这次使用IN和OUT参数：

CREATE PROCEDURE ordertotal(

   IN onumber INT,

   OUT ototal DECIMAL(8,2)

)

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(2000, @total)

显示结果：

SELECT @total

建立智能存储过程

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

-- Name: ordertotoal

-- Parameters: onumber = order number

--       taxable = 0 if not taxable, 1 if taxable

--             ototal = order total variable

CREATE PROCEDURE ordertotal(

IN onumber INT,

IN taxable BOOLEAN,

OUT ototal DECIMAL(8,2)

) COMMENT 'Obtain order total, optionally adding tax'

BEGIN

-- Declare variable for total

DECLARE total DECIMAL(8,2)

-- Declare tax percentage

DECLARE taxrate INT DEFAULT 6;

 

-- Get the order total

SELECT Sum(item_price * quantity)

FROM orderitems

WHERE order_num = onumber

INTO total;

 

-- Is this taxable?

IF taxable THEN

-- Yes, so add taxrate to the total

SELECT total + (total / 100 * taxrate) INTO total;

END IF;

 

-- And finally, save to out variable

SELECT total INTO ototal;

END;

上述存储过程添加了注释（用--表示），在存储过程复杂性增加时，这样做特别重要。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（如taxrate的默认值被设置为6）

上述存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必须的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示

上述例子中还使用了IF语句，IF语句还支持ELSEIF和ELSE子句，ELSEIF还需使用THEN子句，ELSE不需使用THEN

调用上述存储过程：

CALL ordertotal(2000, 0, @total);

SELECT @total

BOOLEAN值非零值都为真，0为假

检查存储过程

SHOW CREATE PROCEDURE xxx

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

SHOW PROCEDURE STATUS

使用SHOW PROCEDURE STATUS获得包括何时、由谁创建等详细信息的存储过程列表，不过它会列出所有存储过程，为限制其输出，可使用LIKE指定过滤模式，如：

SHOW PROCEDURE STATUS LIKE 'ordertotal'

使用游标

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

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

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

MySQL游标只能用于存储过程（和函数）

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

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

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作

创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句，例如：

CREATE PROCEDURE processorders()

BEGIN

   DECLARE ordernumbers CURSOR

   FOR

   SELECT order_num FROM orders;

END;

DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）

在定义游标之后，可以打开它

打开和关闭游标

游标用OPEN CURSOR语句来打开：

OPEN ordernumbers;

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

游标处理完成后，应当使用如下语句关闭游标：

CLOSE ordernumbers;

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

在一个游标关闭后，若没有重新打开，则不能使用它。但使用声明过的游标不需要再次声明，用OPEN语句打开它就可以

若不明确关闭游标，MySQL将会在到达END语句时自动关闭它

CREATE PROCEDURE processorders()

BEGIN

-- Declare the cursor

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

 

-- Open the cursor

OPEN ordernumbers;

 

-- Close the cursor

CLOSE ordernumbers;

END;

使用游标数据

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

下面的例子从游标中检索单个行（第一行）：

CREATE PROCEDURE processorders()

BEGIN

-- Declare local variables

DECLARE o INT;

 

-- Declare the cursor

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

 

-- Open the cursor

OPEN ordernumbers;

 

-- Get order number

FETCH ordernumbers INTO o;

 

-- Close the cursor

CLOSE ordernumbers;

 

END;

其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中

下面的例子中，循环检索数据，从第一行到最后一行：

CREATE PROCEDURE processorders()

BEGIN

   -- Declare loacl variables

   DECLARE done BOOLEAN DEFAULT 0;

   DECLARE o INT;

   

   -- Declare the cursor

   DECLARE ordernumbers CURSOR

   FOR

   SELECT order_num FROM orders;

   

   -- Declare continue handler

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET done = 1;

   

   -- Open the cursor

   OPEN ordernumbers;

   

   -- Loop through all rows

   REPEAT

   

       -- Get order number

       FETCH ordernumbers INTO o;

   

   -- End of loop

   UNTIL done END REPEAT;

   

   -- Close the cursor

   CLOSE ordernumbers;

END;

上述例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT规定）。DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE'02000'出现时，SET done = 1。SQLSTATE'02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件

若调用上述存储过程，它将定义几个变量和一个CONTINUE HANDLER，定义并打开一个游标，重复读取所有行，然后关闭游标。可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前）

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

下面的例子对取出的数据进行某种实际的处理：

CREATE PROCEDURE processorders()

BEGIN

   -- Declare loacl variables

   DECLARE done BOOLEAN DEFAULT 0;

   DECLARE o INT;

   DECLARE t DECIMAL(8,2);

   

   -- Declare the cursor

   DECLARE ordernumbers CURSOR

   FOR

   SELECT order_num FROM orders;

   -- Declare continue handler

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET done = 1;

   

   -- Create a table to store the results

   CREATETABLE IF NOT EXISTS ordertotals

       (order_num INT, total DECIMAL(8,2));

       

   -- Open the cursor

   OPEN ordernumbers;

   

   -- Loop through all rows

   REPEAT

   

       -- Get order number

       FETCH ordernumbers INTO o;

       

       -- Get the total for this order

       CALL ordertotal(o, 1, t);

       

       -- Insert order and total into ordertotals

       INSERTINTO ordertotals(order_num, total)

       VALUES(o, t);

       

   -- End of loop

   UNTIL done END REPEAT;

       

   -- Close the cursor

   CLOSE ordernumbers;

END;

FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程

使用触发器

若想要某条语句（或某些语句）在事件发生时自动执行，就得使用触发器。触发器在某个表发生更改时自动触发。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

• DELETE
  
• INSERT
  
• UPDATE
  

创建触发器

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

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

需要保证每个数据库的触发器名唯一

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

触发器用CREATE TRIGGER语句创建。在上述SQL中，CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT,所以该触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在此例子中，文本"Product added"将对每个插入的行显示一次

注意只有表才支持触发器，视图不支持（临时表也不支持）

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

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

删除触发器

DROP TRIGGER newproduct

使用DROP TRIGGER删除一个触发器。

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建

使用触发器

INSERT触发器

INSERT触发器在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;

上述SQL创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL根据AUTO_INCREMENT生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新的order_num还未生成

通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）

DELETE触发器

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

• 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
  
• 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;

在任意订单被删除前将执行上述触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，若由于某种原因，订单不能存档，DELETE本身将被放弃

上述SQL中触发器deleteorder使用BEGIN和END语句标记触发器体。使用BEGIN END块的好处是触发器能容纳多条SQL语句

UPDATE触发器

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

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

CREATE TRIGGER updatevendor BEFORE UPDATEON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)

任何数据净化都需要在UPDATE语句之前进行，上述例子中每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换

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

MySQL不能从触发器内调用存储过程，所需的存储过程代码需要复制到触发器内

事务处理

并非所有的存储引擎都支持事务处理，MyISAM不支持事务处理，InnoDB支持

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

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。若没有错误发生，整组语句提交给（写到）数据库表。若发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态

一些概念：

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

控制事务处理

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

MySQL使用START TRANSACTION来标识事务的开始

使用ROLLBACK

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

SELECT * FROM ordertotals;

START TRANSACTION;

DELETEFROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

上述例子从显式ordertotals表的内容开始。首先执行一条SELECT以显式该表不为空。然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。此时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空

ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）

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

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交，即提交（写或保存）操作是自动进行的

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，需使用COMMIT语句

START TRANSACTION;

DELETEFROM orderitems WHERE order_num = 20010;

DELETEFROM orders WHERE order_num = 20010;

COMMIT;

上述SQL中最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）

当COMMIT或ROLLBACK语句执行后，事务会自动关闭

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理，但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符

这些占位符称为保留点。为了创建占位符，可以使用SAVEPOINT语句：

SAVEPOINT delete1;

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

ROLLBACK TO delete1;

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

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

更改默认的提交行为

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

SET autocommit = 0

autocommit标志决定是否自动提交更改。autocommit标志针对每个连接而不是针对服务器

全球化和本地化

字符集和校对顺序

一些概念：

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

在MySQL的正常数据库活动（SELECT 、INSERT等）中，不需要操心太多的东西。使用何种字符集和校对的决定在服务器、数据库和表级进行

使用字符集和校对顺序

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

SHOW CHARACTER SET;

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

为查看所支持校对的完整列表，使用以下语句：

SHOW COLLATION;

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

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

SHOW VARIABLES LIKE'character%';

SHOW VARIABLES LIKE'collation%';

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

为了给表指定字符集和校对，可使用带子句的CREATE TABLE：

CREATETABLE mytable

(

   column1 INT,

   column2 VARCHAR(10)

) DEFAULT CHARACTER SET xxx

 COLLATE xxx;

上述SQL指定一个字符集和一个校对顺序

MySQL如下确定使用什么样的字符集和校对：

• 若指定CHARACTER SET 和 COLLATE两者，则使用这些值
  
• 若只指定CHARACTER SET，则使用此字符集及其默认的校对
  
• 若既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认
  

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

CREATE TABLE mytable

(

column1 INT,

column2 VARCHAR(10),

column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci

) DEFAULT CHARACTER SET hebrew

 COLLATE hebrew_general_ci;

上述SQL对整个表以及一个特定的列指定了CHARACTER SET 和 COLLATE

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

SELECT * FROM customers

ORDERBY lastname, firstname COLLATE latin1_general_cs;

上述SQL也演示了在通常不区分大小写的表上进行区分大小写搜索的一种技术，反过来也是可以的

COLLATE子句除了可在ORDER BY子句中使用以外，还可用于GROUP BY、HAVING、聚集函数、别名等

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

安全管理

数据库维护

备份数据

备份数据的解决方案：

• 使用命令行程序mysqldump转储所有数据库的内容到某个外部文件
  
• 用命令行程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持）
  
• 可使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接收将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可用RESTORE TABLE来复原
  

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

进行数据库维护

MySQL提供了一系列的语句，可以用来保证数据库正确运行：

• ANALYZE TABLE，用来检查表键是否正确
  如 ANALYZE TABLE orders;
  
• CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式
  如 CHECK TABLE xxx;
  

查看日志文件

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

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

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

改善性能

在诊断应用的滞缓现象和性能问题时，性能不良的数据库（以及数据库查询）通常是最常见的祸因

以下列出了改善性能的一些入手点：

• MySQL是用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等（为查看当前设置，可使用SHOW VARIABLES；和SHOW STATUS；）
  
• 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法
  
• 有的操作（包括INSERT）支持一个可选的DELAYED关键字，若使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作
  
• 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们
  
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变