（20） 性能
最后更新于：2022-04-01 23:40:24
**目录**
[TOC]
数据库主要的功能是帮你存储资料，而且要可以很方便的让你随时查询或者维护数据。但是在数据库运行一段时间，尤其是里面存储了大量数据的時候，你常发现在查询或维护数据的时候，要等待比较长的时间。所以数据库除了储存数据外，效率的问题也是很重要的。数据库在关于效率上的问题会比较复杂一些，跟软、硬体还有网路都有关，这裡只会讨论跟数据库有关的部份，而且会是比较基础的概念。
查询数据算是资料库中最常执行的工作，想要让查询数据的性能可以好一点，查询叙述本身就很重要。另外也可以依照需求建立增加效率的索引，建立正确的索引可以提高查询工作的效率；索引也可以在某些修改与删除工作上看到效果。
储存引擎在性能上也是一个很重要的因素，你会考虑资料库的大小与种类，还有使用者的数量，然后选择一个适合的储存引擎。
# 1 索引
## 1.1 索引的种类
主索引键的应用很常见，而且一个表格通常会有一个，而且只能有一个。在一个表格中，设定为主索引键的栏位值不可以重复，而且不可以储存「NULL」值。因为这样的限制，所以很适合使用在类似编码、代号或身份证字号这类栏位。
唯一索引也称为「不可重复索引」，在一个表格中，设定为唯一索引的栏位值不可以重複，但是可以储存「NULL」值。这种索引适合用在类似员工资料表格中储存电子邮件帐号的栏位，因为员工不一定有电子邮件帐号，所以允许储存「NULL」值，可以每一个员工的电子邮件帐号都不可以重复。
非唯一索引用来增加查询与维护资料效率的索引。设定为非唯一索引的栏位值可以重复，也可以储存「NULL」值。
「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」与「TEXT」型态的栏位，而且表格使用的储存引擎必须是「MyISAM」，一般会称为「全文检索」，可以提高搜索大量文字的性能。
「SPATIAL」索引是「SPATIAL」型态栏位专用的，而且表格使用的储存引擎必须是「MyISAM」。「FULLTEXT」与「SPATIAL」这两种索引不会在这裡讨论。
注：建立与管理索引的方式，在「表格与索引」中讨论。
## 1.2 建立需要的索引
索引有两个主要的用途：主索引键与唯一索引可以避免重复的资料；主索引键、唯一索引与非唯一索引都可以增加资料库的效率。如果需要為了增加效率而建立索引的话，你可以使用下列最基本的原则：
[](http://box.kancloud.cn/2015-07-17_55a91a2eb0413.png)
除了使用在「WHERE」子句中判断条件的栏位，还有「ORDER BY」与「GROUP BY」子句中指定的栏位，也都可以使用建立索引来增加效率。不过建立这样的索引的前提，还是你的表格会储存比较大量的资料，如果表格的资料量不大的话，建立索引反而会浪费储存的空间，效率也增加不多，而且还会让执行新增或修改时的效率变差。
如果想要為了增加效率而建立索引的话，你应该要考虑下列几点：
* 最重要的，当然是不要建立没有必要的索引，例如上列讨论的情况
* 索引的栏位儘量不要有「NULL」值
* 虽然某个栏位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中，也不一定要建立索引。例如性别栏位的值只有两种(使用ENUM(‘M’, ‘F’)型态)，建立索引所增加的效率也不多
* 主索引键与唯一索引的效率会比非唯一索引好
## 1.3 建立部份内容的索引
下列是一个用来示范用的表格，它可以储存一般的个人资料，在建立表格的时候，就先把身份证字号的栏位设定為主索引键：
[](http://box.kancloud.cn/2015-07-17_55a91a32506ec.png)
在使用这个表格一段时间以后，如果储存的资料量很大，而且又很常使用姓名与地址栏位执行条件的判断，你应该会帮它们建立下列的索引：
[](http://box.kancloud.cn/2015-07-17_55a91a3e99b2b.png)
為姓名栏位建立索引是比较没有问题的，不过地址栏位的长度有255个字元，这样的索引是比较没有效率的，而且你应该比较不会执行所有地址的条件判断，如果比较经常执行的条件判断，是类似「某某县某某市」的话，其实你只要建立部份内容的索引就好了：
[](http://box.kancloud.cn/2015-07-17_55a91a3f33d4a.png)
虽然建立部份内容的索引可以减少索引的大小，不过你还要注意之前讨论的原则，就是建立索引的栏位值不应该有太多重复的值。以上列建立的索引来说，為地址栏位的前六个字元建立索引的话，应该就会有很多重复的值。所以你应该先「分析」表格中的资料：
[](http://box.kancloud.cn/2015-07-17_55a91a41cbde9.png)
上列的叙述可以知道地址栏位是不是有很多重复的资料，為了建立部份内容的索引前，你也可以先使用下列的查询叙述来确认：
[](http://box.kancloud.cn/2015-07-17_55a91a42ca398.png)
如果上列的查询结果，确认地址栏位的前六个字元有很多重复的资料，你可以增加字元的数量后再查询，直到你可以接受的数量后，再使用这个数量来建立部份内容的索引。
# 2 判断条件的设定
如果想要查询一个表格所有的资料，你就不会使用「WHERE」设定查询条件，那就只能请资料库读取表格中所有的资料后传回来，有没有索引就不会有效率上的影响。不过如果使用「WHERE」子句设定查询条件的话，就要儘量使用索引来增加查询的效率。以下列的表格来说：
[](http://box.kancloud.cn/2015-07-17_55a91a438ef5e.png)
虽然你为生日栏位建立了索引，如果你在索引栏位使用函式或运算式的话：
[](http://box.kancloud.cn/2015-07-17_55a91a45b59b5.png)
下列的叙述就会使用索引，虽然比较长一些，不过它执行的效率会比上列的叙述好一些：
[](http://box.kancloud.cn/2015-07-17_55a91a492e536.png)
MySQL资料库在下列的情况下，都会自动帮你执行转换的工作：
[](http://box.kancloud.cn/2015-07-17_55a91a49b0e90.png)
虽然上列的查询叙述在执行后也可以传回你想要的资料，不过MySQL在处理每一笔资料的时候，都要帮你执行一次转换的工作，这样的写法是很没有效率的。所以你要尽可能避免这样的情形：
[](http://box.kancloud.cn/2015-07-17_55a91a4a2155c.png)
另外在关联式数据库的设计下，你应该会很常执行类似下列叙述的结合查询：
[](http://box.kancloud.cn/2015-07-17_55a91a556da45.png)
结合查询是一种很没有效率的查询，因为数据库要比对两个表格中，结合条件所设定的字段值，如果资料数量很多的话，这样的比对工作就会花很多时间。所以你通常会帮结合条件中的字段建立索引，以上列的查询来说，国家表格的“Code”字段已经是主索引键；而城市表格的“CountryCode”并没有建立索引，为了增加结合查询的效率，你可以建立下列的索引：
[](http://box.kancloud.cn/2015-07-17_55a91a5632a6c.png)
如果经常使用国家名称执行条件判断的话，你可能会帮它建立一个索引：
[](http://box.kancloud.cn/2015-07-17_55a91a56eac46.png)
使用完整的国家名称执行条件判断的话，因为使用索引执行搜寻，所以效率会比较好一些。可是如果使用字串样式执行条件判断的话，就不一定会使用索引了：
[](http://box.kancloud.cn/2015-07-17_55a91a5776d1c.png)
有一些索引可能会包含多个字段：
[](http://box.kancloud.cn/2015-07-17_55a91a5d202e3.png)
在查询的条件中，如果跟多个字段的索引有关的话，MySQL会依照索引字段的顺序来决定是否使用索引。以上列的例子来说，主索引键的顺序是CountryCode字段在前面，Language字段在后面，如果你的查询条件只有使用Language字段的话，这个索引就不会生效：
[](http://box.kancloud.cn/2015-07-17_55a91a6210c60.png)
# 4 EXPLAIN与查询叙述
MySQL数据库提供“EXMPLIN”指令，可以让你分析一个查询叙述。以下列的查询来说，你可以清楚的知道数据库在执行这个查询时后发生“full table scan”：
[](http://box.kancloud.cn/2015-07-17_55a91a6e2d44e.png)
下列的查询叙述可以看出数据库使用索引来传回资料：
[](http://box.kancloud.cn/2015-07-17_55a91a6ef41fa.png)
如果是包含有子查询的查询叙述，“EXPLAIN”也会分别帮你执行分析的工作：
[](http://box.kancloud.cn/2015-07-17_55a91a7faa933.png)
使用“EXPLAIN”来检查在这章讨论索引的查询叙述：
[](http://box.kancloud.cn/2015-07-17_55a91a860c195.png)
换成下列的查询叙述后，“EXPLAIN”会告诉你数据库使用索引来传回资料：
[](http://box.kancloud.cn/2015-07-17_55a91a88c54fb.png)
# 5 资料维护
当你使用“INSERT”、“UPDATE”或“DELETE”叙述执行资料维护的工作时，也要注意效率上的问题。在执行修改或删除资料的时候，除了要修改或删除表格中所有的资料以外，你都会加入条件的设定。在“UPDATE”和“DELETE”叙述中使用“WHERE”子句设定条件时，跟查询时候该注意的地方都一样，除了尽量使用索引来增加执行的效率，也要避免不必要的资料转换。
MySQL提供的“EXPLAIN”叙述，只可以为你分析一个查询叙述，它不可以使用在“SELECT”以外的叙述。不过你也可以这样作：
[](http://box.kancloud.cn/2015-07-17_55a91a8a07734.png)
MySQL提供使用一个“INSERT”叙述新增多笔资料的语法，如果你一次要新增多笔资料的话，使用这样的方式新增资料会是比较有效率的：
[](http://box.kancloud.cn/2015-07-17_55a91a8acf48d.png)
# 6 LIMIT子句
在查询和维护资料的时候，都有可能会使用“LIMIT”子句设定查询或维护资料的数量。“LIMIT”子句在某些应用上是非常方便的，不过要特别注意在效率上的问题。以下列的例子来说：
[](http://box.kancloud.cn/2015-07-17_55a91a8c5ff09.png)
虽然这个查询叙述只有传回五笔资料，可以数据库总共读取了105笔资料，这样的查询会是比较没有效率的。你可以使用索引与“ORDER BY”子句来增加效率：
[](http://box.kancloud.cn/2015-07-17_55a91a8cd0ddd.png)
# 7 使用暂时表格
在执行比较复杂的查询工作时，在一个查询叙述中，可能会有结合查询、子查询和其它复杂的判断条件。一个看起来比较长而且比较复杂的查询，效率并一定比较不好。不过你可以使用一些比较特别的方式，进一步改善查询的复杂度与效率。以下列的查询来说：
[](http://box.kancloud.cn/2015-07-17_55a91a8e9e10c.png)
上列的查询是一个不算太复杂的结合查询，如果还要在加上其它的条件判断的话，看起来就会更长一些：
[](http://box.kancloud.cn/2015-07-17_55a91a8f24e6c.png)
如果还要再结合另外一个表格的话，这个查询看起来就真的很复杂了：
[](http://box.kancloud.cn/2015-07-17_55a91a9077a82.png)
上列的查询看起来虽然复杂，不过如果都有可以使用的索引，它执行的效率也会是不错的。如果在查询工作中，很常使用第一个查询的结果，再加上不同的条件或结合，你就可以考虑使用下列的叙述，先建立好一个暂时的表格：
[](http://box.kancloud.cn/2015-07-17_55a91aa5227e4.png)
因为查询的结果已经储存在“countrycapital”表格中，所以要加入其它的条件就变得简单多了：
[](http://box.kancloud.cn/2015-07-17_55a91aa697bac.png)
如果要再结合另外一个表格的话，也会比较容易：
[](http://box.kancloud.cn/2015-07-17_55a91aaa70824.png)
在“第九章、子查询、FROM子句与子查询”讨论到可以把一个查询放在“FROM”子句中：
[](http://box.kancloud.cn/2015-07-17_55a91aad0dc29.png)
使用这样的方式虽然可以得到一样的查询结果，不过在你很常使用上列子查询来增加条件的情况下，每次执行不同条件的查询，数据库都要重新执行子查询叙述；先建立暂时的表格，再使用暂时表格执行查询的作法会是比较有效率的。
# 8 储存引擎
MySQL数据库是一种允许多个用户端同时使用的数据库管理系统，在多用户端的的运作环境下，数据库就使用“锁定、Locking”来避免资料的混乱：
[](http://box.kancloud.cn/2015-07-17_55a91ab1415d4.png)
MySQL提供的“MyISAM”和“InnoDB”两种储存引擎，使用不同的锁定方式来处理上列的情况。MyISAM使用的是“table-level”的锁定方式：
[](http://box.kancloud.cn/2015-07-17_55a91ac32d074.png)
MyISAM储存引擎使用的“table-level”锁定方式，适合使用在查询工作非常多，资料维护比较少的数据库，这样的数据库运作起来的效率会比较好。
InnoDB储存引擎使用的是“row-level”的锁定方式：
[](http://box.kancloud.cn/2015-07-17_55a91aca7906b.png)
InnoDB储存引擎使用的“row-level”锁定方式，适合使用在查询与资料维护工作都差不多的数据库，这样的数据库运作起来的效率会比较好。
';
（19） 导入和导出数据
最后更新于：2022-04-01 23:40:22
**目录**
[TOC]
# 1 备份与回复
在你开始使用MySQL数据库以后，MySQL会帮你储存与管理所有的资料，依照不同的设定，会有许多的资料档案储存在档案系统中，如果这些档案不小心遗失或损坏，储存的资料可能就全部不见了。为了预防这类的情况发生，MySQL提供许多备份资料的功能，让你可以依照自己的需求，汇出数据库中储存的资料，另外保存起来。如果数据库发生严重的问题，而且储存的资料不见了，你就可以把之前备份的资料，回复到数据库中。备份资料的工作称为“汇出资料、exporting data”；回复资料的工作称为“汇入资料、importing data”。
你可以使用SQL叙述或MySQL提供的用户端程式，执行汇出与汇入的工作。汇出资料可以使用“SELECT INTO OUTFILE”叙述，或是“mysqldump”用户端程式，它们都可以将指定的资料储存为档案保存起来；汇入资料可以使用“LOAD DATA INFILE”叙述，或是“mysqlimport”用户端程式，它们都可以将指定档案中的资料新增到数据库中。
# 2 使用SQL叙述汇出资料
MySQL提供“SELECT INTO OUTFILE”叙述汇出资料，它的用法与一般查询叙述一样，另外使用“INTO OUTFILE”子句指定一个档案名称，执行叙述以后回传的资料会储存为档案。下列是它的语法：
[](http://box.kancloud.cn/2015-07-17_55a91b04e52d1.png)
使用“INTO OUTFILE”子句指定档案名称时，要特别注意资料夹的符号，不论是“UNIX”或“WINDOWS”作业系统，都要使用“/”。下列的叙述会将查询后的结果储存到“C:\cmdev\dept.txt”档案中：
[](http://box.kancloud.cn/2015-07-17_55a91b1accc2d.png)
使用文字编辑软件开启上列范例汇入的档案，它的内容会像这样：
[](http://box.kancloud.cn/2015-07-17_55a91b1b6c004.png)
MySQL默认的分隔字符使用“TAB”，你可以在汇出档案的叙述中，使用“FIELDS TERMINATED BY”子句设定新的分隔字符：
[](http://box.kancloud.cn/2015-07-17_55a91b26b2cfb.png)
使用“FIELDS ENCLOSED BY”子句可以设定包围字段资料的字符符号：
[](http://box.kancloud.cn/2015-07-17_55a91b2872cf0.png)
汇出的资料如果遇到“NULL”值的时候，MySQL会使用“\N”储存在档案中：
[](http://box.kancloud.cn/2015-07-17_55a91b2a92068.png)
MySQL默认的跳脱字符符号是“\”，你可以在汇出档案的叙述中，使用“FIELDS ESCAPED BY”子句设定新的跳脱字符符号：
[](http://box.kancloud.cn/2015-07-17_55a91b2b7c035.png)
使用“LINES STARTING BY”与“TERMINATED BY”子句可以设定每一列资料开始与结束字串：
[](http://box.kancloud.cn/2015-07-17_55a91b2fb26a6.png)
使用文字储存资料有许多不同的格式，有一种很常见的格式称为“comma-separated values、CSV”，它的每一笔资料的结尾使用换行字符，每一个资料都使用逗号隔开，而且前后使用双引号包围起来。许多应用程式都认识这种资料的格式，你可以使用下列的设定输出一个CSV格式的资料档案：
[](http://box.kancloud.cn/2015-07-17_55a91b32f26ad.png)
# 3 使用SQL叙述汇入资料
“LOAD DATA”叙述可以汇入资料到数据库的某个表格中，“LOAD DATA”叙述提供许多子句，可以让你设定资料档案、档案的格式，或是汇入资料的处理。下列是它的语法：
[](http://box.kancloud.cn/2015-07-17_55a91b346b124.png)
## 3.1 指定资料档案
“LOAD DATA”叙述可以将一个包含资料的档案，汇入到一个指定的表格中，下列是它的基本语法：
[](http://box.kancloud.cn/2015-07-17_55a91b3934d01.png)
使用“LOAD DATA”叙述汇入资料前，要明确的指定数据库：
[](http://box.kancloud.cn/2015-07-17_55a91b444aa31.png)
如果你的资料档案放在用户端的电脑中，在使用“LOAD DATA”叙述时要加入“LOCAL”关键字。指定资料档案时，可以包含磁盘机代号、资料夹与档案名称：
[](http://box.kancloud.cn/2015-07-17_55a91b45530bd.png)
指定的资料档案如果没有磁盘机代号，可是包含资料夹与档案名称，MySQL会使用目前工作中的磁盘机：
[](http://box.kancloud.cn/2015-07-17_55a91b46ead19.png)
指定的资料档案没有磁盘机代号，只有资料夹与档案名称，可是最前面没有资料夹符号，MySQL会使用目前工作中的资料夹：
[](http://box.kancloud.cn/2015-07-17_55a91b478f986.png)
指定的资料档案只有档案名称，MySQL会使用目前工作中的资料夹：
[](http://box.kancloud.cn/2015-07-17_55a91b4c78c2a.png)
如果你的资料档案放在服务器的电脑中，在使用“LOAD DATA”叙述时就不要使用“LOCAL”关键字。指定资料档案时，可以包含磁盘机代号、资料夹与档案名称：
[](http://box.kancloud.cn/2015-07-17_55a91b57a2e1e.png)
指定的资料档案如果没有磁盘机代号，可是包含资料夹与档案名称，MySQL会使用服务器的磁盘机：
[](http://box.kancloud.cn/2015-07-17_55a91b5b211d6.png)
指定的资料档案没有磁盘机代号，只有资料夹与档案名称，可是最前面没有资料夹符号，MySQL会使用
数据库资料夹：
[](http://box.kancloud.cn/2015-07-17_55a91b7524eca.png)
指定的资料档案只有档案名称，而且在“INTO TABLE”中指定数据库名称，MySQL会使用数据库资料夹的数据库名称：
[](http://box.kancloud.cn/2015-07-17_55a91b8071f79.png)
指定的资料档案只有档案名称，在执行“LOAD DATA INFILE”叙述前先使用“USE”叙述指定数据库，而且在“INTO TABLE”中没有指定数据库名称，MySQL会使用数据库资料夹的目前使用中数据库名称：
[](http://box.kancloud.cn/2015-07-17_55a91b838f5c2.png)
注：使用“SHOW VARIABLES LIKE ‘datadir’”叙述，可以查询MySQL数据库服务器使用的数据库资料夹。
## 3.2 设定资料格式
如果没有另外设定的话，使用“LOAD DATA INFILE”叙述汇入的资料档案，MySQL会使用下列的格式：
[](http://box.kancloud.cn/2015-07-17_55a91b8599e7e.png)
如果你的资料档案格式跟上列的档案一样的话，使用下列的计就可以汇入资料：
[](http://box.kancloud.cn/2015-07-17_55a91b878e4e9.png)
如果要汇入资料的档案是“CSV”格式的话，就要使用“FIELDS”与“LINES”子句设定格式：
[](http://box.kancloud.cn/2015-07-17_55a91b88dd326.png)
## 3.3 处理汇入的资料
如果汇入的资料档案与表格完全对应的话，“LOAD DATA INFILE”叙述都可以把资料正确的汇入到数据库中。可是以下列储存在资料档案中的部门资料来说：
[](http://box.kancloud.cn/2015-07-17_55a91b8b88252.png)
因为“cmdev.dept”表格有“deptno”、“dname”与“location”三个字段，所以执行下列的“LOAD DATA INFILE”叙述就会产生错误：
[](http://box.kancloud.cn/2015-07-17_55a91b8c13318.png)
你可以在“LOAD DATA INFILE”叙述中，指定汇入资料的数量和字段：
[](http://box.kancloud.cn/2015-07-17_55a91b8c910b5.png)
下列的“LOAD DATA INFILE”叙述指定汇入资料时会跳过第一笔，而且指定汇入的字段只有“deptno”与“dname”两个字段：
[](http://box.kancloud.cn/2015-07-17_55a91b8ee9d6c.png)
你也可以在“LOAD DATA INFILE”叙述中加入使用者变量：
[](http://box.kancloud.cn/2015-07-17_55a91b904d528.png)
下列的叙述将“ename”与“job”两个字段的资料先转换大写后，再汇入到数据库中：
[](http://box.kancloud.cn/2015-07-17_55a91b90c1657.png)
## 3.4 索引键重复
在新增、修改或汇入资料到数据库的时候，都有可能发生索引值重复的错误，在使用“LOAD DATA INFILE”汇入资料的时候，如果发生索引值重复的情况，你可以使用“IGNORE”或“REPLACE”来决定数据库该作什么处理：
[](http://box.kancloud.cn/2015-07-17_55a91b923155e.png)
以部门资料表来说，部门编号已经设定为主索引键，所以它是不可以重复的：
[](http://box.kancloud.cn/2015-07-17_55a91b98f0efb.png)
如果资料档储存在MySQL服务器的电脑中，在汇入资料时没有使用“IGNORE”或“REPLACE”，发生索引重复的情况时，数据库会产生错误讯息，而且不会汇入任何资料：
[](http://box.kancloud.cn/2015-07-17_55a91b9ae4b89.png)
资料档储存在MySQL服务器的电脑中时，你可以使用“IGNORE”关键字忽略错误的资料，正确的资料还是汇入到数据库中；使用“REPLACE”关键字请数据库会帮你执行修改资料的动作：
[](http://box.kancloud.cn/2015-07-17_55a91bb172bb0.png)
下列的“LOAD DATA INFILE”叙述中使用“IGNORE”关键字汇入资料时，处理索引重复资料的效果：
[](http://box.kancloud.cn/2015-07-17_55a91bb3c178e.png)
下列的“LOAD DATA INFILE”叙述中使用“REPLACE”关键字汇入资料时，处理索引重复资料的效果：
[](http://box.kancloud.cn/2015-07-17_55a91bb5b7e7b.png)
资料档储存在用户端的电脑中时，处理汇入资料发生索引重复的作法会不太一样：
[](http://box.kancloud.cn/2015-07-17_55a91bb9a098b.png)
使用“REPLACE”关键字的时候，效果就跟资料档储存在MySQL服务器的电脑中时一样：
[](http://box.kancloud.cn/2015-07-17_55a91bbf5d039.png)
## 3.5 汇入资讯
在执行汇入资料的叙述以后，你应该会想要知道有多少资料汇入到数据库中。如果你在“MySQL Query Browser”工具中执行“LOAD DATA INFILE”叙述的话，它会告诉你总共影响了几笔资料，包含新增与修改：
[](http://box.kancloud.cn/2015-07-17_55a91bc037d33.png)
如果你在命令提示字符中执行“LOAD DATA INFILE”叙述的话，除了影响的资料数量以外，还会告诉你比较完整的汇入资讯：
[](http://box.kancloud.cn/2015-07-17_55a91bc16a9ce.png)
在上列的资讯中：
* Records：表示从资料档案中读取的资料数量
* Deleted：表示在发生索引重复的情况下更新资料的数量
* Skipped：表示在发生索引重复的情况下被忽略的资料数量
* Warnings：表示资料档案中有问题的资料数量，例如转换Hello字串为数值
# 4 使用mysqldump程式汇出资料
MySQL提供许多不同应用的工具程式，让你可以在命令提示字符中执行，这些工具程式都是MySQL才有的，而且它们并不是SQL叙述。你可以使用“mysqldump”工具程式汇出资料。下列是它的用法：
[](http://box.kancloud.cn/2015-07-17_55a91bc2d1736.png)
下列是“mysqldump”工具程式的基本选项：
| 选项 | 说明 |
| --- | --- |
| –host=数据库服务器 或 -h 数据库服务器 | 指定要连线的的数据库服务器名称，“-h”后面必须有空格；没有使用这个选项的话，表示连线到本机 |
| –user=使用者帐号 或 -u 使用者帐号 | 指定连线的使用者帐号，“-u”后面必须有空格 |
| –password[=密码] 或 -p[密码] | 指定连线的密码，“-p”后面不可以有空格；没有提供密码的话，执行程式以后会提示你输入密码；没有使用这个选项的话，表示密码为空白 |
下列的命令为“mysqldump”加入指定数据库服务器、使用者帐号与数据库名称的相关资讯。在命令提示字符中执行下列的命令以后，会在萤幕中显示“cmdev”数据库的资讯：
[](http://box.kancloud.cn/2015-07-17_55a91bc56660f.png)
这些选项都有两种设定方式，以使用者帐号来说：
[](http://box.kancloud.cn/2015-07-17_55a91bc8154fe.png)
下列是与汇出资料相关的选项：
| 选项 | 说明 |
| --- | --- |
| –result-file=档案名称 | 指定汇出资料的档案名称，资料夹符号必须使用“/” |
| –all-databases | 汇出数据库服务器中所有数据库的资料 |
| –tab=资料夹 | 指定汇出资料档案存放的资料夹 |
下列的命令使用“–result-file”指定汇出的档案名称。执行后储存盘案的位置就是你执行“mysqldump”的位置，如果在“C:/cmdev/data/out”资料夹下执行“mysqldump”，你就可以在“C:/cmdev/data/out”资料夹下找到“cmdev.sql”档案：
[](http://box.kancloud.cn/2015-07-17_55a91bc8c344b.png)
执行上列的命令以后，开启“C:/cmdev/data/out/cmdev.sql”档案，里面的内容只有建立表格的叙述，并不包含储存在表格里面的资料纪录。
如果想要“mysqldump”工具程式也帮你汇出资料纪录的话，就要使用下列的作法：
[](http://box.kancloud.cn/2015-07-17_55a91bca51bea.png)
“mysqldump”工具程式汇出资料纪录档案的格式，字段资料间使用“TAB”隔开,每一列资料以“\N”结尾。如果要控制资料档案格式的话，可以使用下列的选项：
| 选项 | 说明 |
| --- | --- |
| –fields-terminated-by=字串 | 设定字段资料间的分隔符号 |
| –fields-enclosed-by=字符 | 设定每一个字段资料的前后字符 |
| –fields-optionally-enclosed-by=字符 |
| –fields-escaped-by=字符 | 设定跳脱字符的符号 |
| –lines-terminated-by=字串 | 设定每一行的结尾 |
# 5 使用mysqlimport程式汇入资料
你可以使用“mysqlimport”工具程式汇入资料。下列是它的用法：
[](http://box.kancloud.cn/2015-07-17_55a91bcc3e2fb.png)
在指定资料档案的名称时，要特别注意下列两个重点：
* 资料档案中不可以包含SQL叙述
* 档案名称会决定汇入数据库中的哪个表格，MySQL会使用去除附加档名后的名称。例如“dept.dat”为“dept”表格；“dept.txt.dat”同样为“dept”表格
下列是“mysqlimport”工具程式的基本选项，它们的用法与“mysqldump”工具程式一样，其实大部份的MySQL工具程式都有这些选项：
| 选项 | 说明 |
| --- | --- |
| –host=数据库服务器 或 -h 数据库服务器 | 指定要连线的的数据库服务器名称，“-h”后面必须有空格；没有使用这个选项的话，表示连线到本机 |
| –user=使用者帐号 或 -u 使用者帐号 | 指定连线的使用者帐号，“-u”后面必须有空格 |
| –password[=密码] 或 -p[密码] | 指定连线的密码，“-p”后面不可以有空格；没有提供密码的话，执行程式以后会提示你输入密码；没有使用这个选项的话，表示密码为空白 |
如果你的资料档案是下列格式的话：
[](http://box.kancloud.cn/2015-07-17_55a91bcd31cbb.png)
下列的命令可以把资料档案汇入到“cmdev.dept”中：
[](http://box.kancloud.cn/2015-07-17_55a91bcdc7ec6.png)
下列的选项可以设定资料档案的格式：
| 选项 | 说明 |
| --- | --- |
| –fields-terminated-by=字串 | 设定字段资料间的分隔符号 |
| –fields-enclosed-by=字符 | 设定每一个字段资料的前后字符 |
| –fields-optionally-enclosed-by=字符 |
| –fields-escaped-by=字符 | 设定跳脱字符的符号 |
| –lines-terminated-by=字串 | 设定每一行的结尾 |
下列的选项可以决定发生索引值重复的错误时，数据库该作什么处理：
| 选项 | 说明 |
| --- | --- |
| –ignore | 忽略索引键重复的汇入资料 |
| –replace | 索引键重复时，以汇入的资料更新数据库中的资料 |
| –local | 指定汇入的资料档案来源为用户端 |
';
（18） 错误处理和查询
最后更新于：2022-04-01 23:40:20
**目录**
[TOC]
# 1 错误的资料
在规划与设计一个数据库的时候，你会针对储存资料的需求，定义每一个表格中的字段，包含字段的资料型态与其它的设定，这些定义都会影响资料的查询与维护。数据库中储存的资料应该是正确而且没有误差的，如果你尝试储存一个错误的资料，数据库应该要发现问题并告诉你不可以这样做；不过在不同的需求下，你可能会希望数据库允许不太严重的错误，不要每次都产生错误讯息。
MySQL数据库环境中，可以使用“sql_mode”系统变量设定数据库对于检查错误资料的“严格”程度，分为“strict”与“non-strict”两种模式。在strict模式下，数据库会严格的检查与发现错误的资料，而且不会储存错误的资料；在non-strict模式下，数据库同样会检查与发现错误的资料，不过它会尽量试着处理这些错误的资料，再把资料储存起来。
你可以依照自己的需求设定“sql_mode”系统变量，下列的指令可以设定为“non-strict”模式：
[](http://box.kancloud.cn/2015-07-18_55a9d50fef0ae.png)
下列的叙述设定为“strict”模式：
[](http://box.kancloud.cn/2015-07-18_55a9d527046ab.png)
“STRICT_TRANS_TABLES”与“STRICT_ALL_TABLES”同样可以设定为“strict”模式，在使用支援“交易、transaction”的数据库，应该要设定为“STRICT_TRANS_TABLES”，这样可以确定资料的完整性。
设定为“strict”与“non-strict”两种不同的模式，对于错误资料的处理会有很大的差异。下列是一个用来测试的表格“cmdev.debug”，它包含许多不同资料型态与设定的字段：
| 字段名称 | 型态 | NULL | 索引 | 默认值 | 其它资讯 |
| --- | --- | --- | --- | --- | --- |
| fint | tinyint(4) | NO | NULL |
| fchar | varchar(3) | YES | NULL |
| fdouble | double(5, 2) | YES | NULL |
| fdate | date | YES | NULL |
| ftime | time | YES | NULL |
| fenum | enum('A','B','C') | YES | NULL |
| fset | set('A','B','C') | YES | NULL |
# 2 Non-Strict模式
下列是使用“SET”设定“sql_mode”变量的语法：
[](http://box.kancloud.cn/2015-07-18_55a9d53149871.png)
如果没有指定“SESSION”或“GLOBAL”的话，MySQL会把这个设定当成“SESSION”，设定的效果只有一个用户端的连线，并不会影响其它用户端连线的设定。下列的范例设定为“non-strict”模式后，使用“SHOW”或“SELECT”叙述查询设定后的结果：
[](http://box.kancloud.cn/2015-07-18_55a9d545054e9.png)
如果你希望将所有用户端都设定为“non-strict”模式，那就要使用“GLOBAL”关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d546eb92f.png)
设定为“non-strict”模式以后，在执行资料维护时，如果资料完全符合字段资料型态的规定，那就不会发生任何警告或错误：
[](http://box.kancloud.cn/2015-07-18_55a9d54e6ae1c.png)
如果数据库发现不符合字段规定的资料，它会尽量试着处理这些错误的资料，再把资料储存起来。以下列的范例来说，想要储存到字串型态字段的值有六个字符，可是“fchar”字段只能储存三个字符，数据库在“non-strict”模式下，会忽略多余的字符后再储存起来，然后使用警告讯息通知你：
[](http://box.kancloud.cn/2015-07-18_55a9d54f3d6ed.png)
在non-strict模式运作时，下列几种情形都有可能会启动自动修正资料的功能：
* 执行新增或修改叙述，包含INSERT、REPLACE、UPDATE与LOAD DATA INFILE
* 使用ALTER TABLE修改表格的字段定义
* 在字段定义中使用“DEFAULT”指定字段的默认值
注：“LOAD DATA INFILE”在“汇入与汇出资料、使用SQL叙述汇入资料”中讨论。
## 2.1 数值
数据库在“non-strict”模式下，处理数值资料型态会使用比较宽松的方式。以整数型态“TINYINT”来说，如果储存的数值超过规定的范围，数据库会依照下列的方式来处理错误的数值资料：
[](http://box.kancloud.cn/2015-07-18_55a9d5512f1c5.png)
浮点数型态与整数型态一样有规定的范围，如果你在定义浮点数型态字段时，也设定了长度与小数位数，那就只能储存设定的范围：
[](http://box.kancloud.cn/2015-07-18_55a9d5558db88.png)
注：储存小数到整数型态的字段，或是小数位数超过浮点数型态定义的位数，MySQL会针对小数的部份执行四舍五入，并不会有任何错误或警告。
## 2.2 列举(ENUM)与集合(SET)
“ENUM”型态只能储存一个规定好的成员资料，以“fenum”字段来说，它设定了A、B、C三个成员，你也可以使用数值1、2、3表示。在“non-strict”模式下，如果你尝试储存错误的资料，数据库都会储存空的字串“"”，数值为0：
[](http://box.kancloud.cn/2015-07-18_55a9d557cf49a.png)
“SET”型态可以储存一组规定好的成员资料，以以“fset”字段来说，它设定了X、Y、Z三个成员。在“non-strict”模式下，如果你尝试储存错误的资料，数据库都会储存空的字串“"”，数值为0；如果指定的成员不正确的话，数据库也会自动忽略它们：
[](http://box.kancloud.cn/2015-07-18_55a9d56ea8399.png)
注：重复的集合成员不会造成任何错误或警告。例如储存“’X,X,Y,Y,Z,Z’”的值到“fset”字段，实际储存的是“’X,Y,Z’”。
## 2.3 字串转换为其它型态
数据库设定为“non-strict”模式的时候，如果你想要储存字串资料到非字串型态的字段，数据库都会帮你转换为字段的型态后再储存。如果字串的内容不能转换为字段的型态，例如想要储存字串“Hello!”到数值型态字段，数据库会储存下列的默认值，然后产生警告讯息：
| 字段型态 | 默认值 | 字段型态 | 默认值 |
| --- | --- | --- | --- |
| 数值 | 0 | TIMESTAMP | '0000-00-00 00:00:00' |
| DATE | '0000-00-00' | YEAR | 0000或00 |
| TIME | '00:00:00' | ENUM | '' |
| DATETIME | '0000-00-00 00:00:00' | SET | '' |
在执行字串转换型态的时候，数据库会使用很宽松的方式，尽量把你的资料储存起来，尤其是字串转换为数值与日期型态：
| 字串值 | fint | fdate |
| --- | --- | --- |
| '10-10-10' | 10 | '2010-10-10' |
| '007' | 7 | '0000-00-00' |
| 'SAM36' | 0 | '0000-00-00' |
| '36SAM' | 36 | '0000-00-00' |
| '25-SAM' | 25 | '0000-00-00' |
| '12 SAM' | 12 | '0000-00-00' |
| 'SAM' | 0 | '0000-00-00' |
## 2.4 NULL与NOT NULL
在规划表格字段的时候，你会根据需求设定字段是否可以储存“NULL”值。如果你设定某一个字段不可以储存“NULL”值，不论在“non-strict”或“strict”模式下，储存“NULL”值的叙述都会发生错误讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d56f17ad7.png)
数据库设定为“non-strict”模式的时候，下列的情况只会产生警告讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d57082753.png)
## 2.5 Strict模式与IGNORE关键字
你也可以将数据库设定为“strict”模式，在这个模式下，只有在储存字串资料到非字串型态的字段时，数据库会尝试帮你指定的字串转换为字段型态；其它任何违反资料型态的问题，数据库不会储存错误的资料，而且会产生错误讯息。
在“strict”模式模式下执行新增与修改时，可以依照需求加入“IGNORE”关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d57aa2bf6.png)
# 3 其它设定
“sql_mode”变量设定为“non-strict”或“strict”模式后，还可以依照自己的需求加入额外的设定：
| 设定值 | 说明 |
| --- | --- |
| ALLOW_INVALID_DATES | 允许错误的日期资料 |
| NO_ZERO_DATE | 不允许全部是0的日期资料 |
| NO_ZERO_IN_DATE | 日期资料中不可以有0 |
| ERROR_FOR_DIVISION_BY_ZERO | 除以0时产生错误，而不是产生NULL值 |
如果你希望数据库设定为“strict”模式，可是对于日期资料的检查又可以宽松一些，你可以执行下列的设定：
[](http://box.kancloud.cn/2015-07-18_55a9d58d4bc73.png)
加入“ALLOW_INVALID_DATES”的设定以后，就算是“2000-02-31”这样一个错误的日期资料，数据库也会储存它，不会有任何警告或错误讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d59c3d99a.png)
日期型态的字段，不论在“non-strict”或“strict”模式下，你都可以储存年月日为0的日期资料，不会产生任何警告或错误讯息。如果不希望储存这样的日期资料，你可以加入“NO_ZERO_DATE”与“NO_ZERO_IN_DATE”的设定：
[](http://box.kancloud.cn/2015-07-18_55a9d5ada2442.png)
如果在你执行的叙述中出现除以零的运算式，数据库会产生“NULL”值，并不会产生任何警告或错误讯息。你可以加入“ERROR_FOR_DIVISION_BY_ZERO”设定：
[](http://box.kancloud.cn/2015-07-18_55a9d5af8ac4e.png)
在叙述中出现除以零的运算式时，数据库会产生除以零的错误讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d5c54ea34.png)
你可以使用不同的设定项目，让数据库中的资料更符合自己的需求。MySQL也为你准备了许多不同的设定组合，让你可以方便的完成“sql_mode”的设定：
| 设定值 | 设定项目 |
| --- | --- |
| ANSI | REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE |
| DB2 | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| MAXDB | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER |
| MSSQL | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| MYSQL323 | NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE |
| MYSQL40 | NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE |
| ORACLE | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER |
| POSTGRESQL | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| TRADITIONAL | STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER |
注：“sql_mode”的完整设定可以参考MySQL参考手册中的“5.2.6\. SQL Modes”。
# 4 查询错误与警告
在执行SQL叙述后，如果发生警告或错误，你可能需要根据这些讯息来执行一些补救工作。MySQL提供的“SHOW”指令可以查询这些讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d5d30f207.png)
以下列的新增叙述来说，在“non-strict”模式下，虽然会新增一笔纪录到“debug”表格中，不过想要储存的三个资料都是有问题的：
[](http://box.kancloud.cn/2015-07-18_55a9d5d40eff4.png)
执行上列的新增叙述后，你可以使用“SHOW WARNINGS”查询所有的问题：
[](http://box.kancloud.cn/2015-07-18_55a9d5ea8133a.png)
下列这个删除表格的叙述，因为使用了“IF EXISTS”，可以预防因为要删除的表格不存在而产生错误，所以执行叙述以后，指会产生一个“Note”告诉你要删除的表格不存在：
[](http://box.kancloud.cn/2015-07-18_55a9d5f61a91e.png)
如果查询叙述中指定的字段不存在的话，就会产生错误讯息，在执行叙述以后，可以使用“SHOW ERRORS”查询发生了哪些错误：
[](http://box.kancloud.cn/2015-07-18_55a9d5f9c9f72.png)
如果是因为执行SQL叙述，导致数据库产生的警告或错误，都可以使用“SHOW WARNINGS”或“SHOW ERRORS”查询；不过也有可能是因为作业系统发生问题，例如下列执行汇出资料的叙述，执行叙述以后，数据库应该建立一个“C:\hello\mydata.sql”档案，不过因为指定的资料夹并不存在，所以会产生错误讯息：
[](http://box.kancloud.cn/2015-07-18_55a9d60414624.png)
如果发生这类的错误，数据库只会告诉你不能储存盘案，详细的错误讯息要在命令提示字符下，使用“perror”程式来查询：
[](http://box.kancloud.cn/2015-07-18_55a9d63227def.png)
注：汇出资料会在“汇入与汇出资料”中详细讨论。
如果需要知道警告或错误的数量，可以使用下列的查询叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d63f0f667.png)
';
（17） 资料库资讯
最后更新于：2022-04-01 23:40:18
**目录**
[TOC]
# 1 information_schema数据库
一个建立好并且运作中的数据库，通常会包含表格、字段与索引，为了扩充数据库的功能，也可能会加入stored routines与triggers元件。MySQL把这些数据库的资讯放在“information_schema”数据库，下列是这个数据库中主要的表格：
| 表格名称 | 说明 |
| --- | --- |
| CHARACTER_SETS | MySQL数据库支援的字符集 |
| COLLATIONS | MySQL数据库支援的collation |
| COLLATION_CHARACTER_SET_APPLICABILITY | 字符集与collation对应资讯 |
| COLUMNS | 字段资讯 |
| COLUMN_PRIVILEGES | 字段授权资讯 |
| KEY_COLUMN_USAGE | 索引字段的限制资讯 |
| ENGINES | MySQL数据库支援的储存引擎 |
| GLOBAL_STATUS | MySQL数据库服务器状态资讯 |
| GLOBAL_VARIABLES | MySQL数据库服务器变量资讯 |
| KEY_COLUMN_USAGE | 索引键资讯 |
| ROUTINES | Stored routines资讯 |
| SCHEMATA | 数据库资讯 |
| SESSION_STATUS | 用户端连线状态资讯 |
| SESSION_VARIABLES | 用户端连线变量资讯 |
| STATISTICS | 表格索引资讯 |
| TABLES | 表格资讯 |
| TABLE_CONSTRAINTS | 表格限制资讯 |
| TABLE_PRIVILEGES | 表格授权资讯 |
| TRIGGERS | Triggers资讯 |
| USER_PRIVILEGES | 使用者授权资讯 |
| VIEWS | Views资讯 |
“information_schema”数据库称为“database metadata”，包含数据库元件与服务器运作的完整资讯都储存在这个数据库中。你不须要自己建立与维护“information_schema”数据库，它是由MySQL数据库服务器负责建立与维护的。你只能够在需要的时候，使用“SELECT”叙述来查询储存在里面的资料。
下列的查询叙述可以传回MySQL数据库服务器中所有的stored routines资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d67a9dd28.png)
在之前所讨论过的查询叙述用法，都可以用来查询“information_schema”数据库：
[](http://box.kancloud.cn/2015-07-18_55a9d67b729b4.png)
# 2 SHOW指令
除了使用查询叙述直接查询“information_schema”数据库中的资讯外，MySQL数据库服务器供有许多不同用法的“SHOW”指令，同样可以查询数据库资讯。“SHOW”指令是MySQL数据库服务器专用的指令，并不是标准的SQL叙述。
## 2.1 数据库元件资讯
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的数据库资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d68dde284.png)
“SHOW DATABASES”指令也可以搭配使用“LIKE”关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d6950b3f9.png)
注：字串样式的设定在“基础查询、条件查询、字串样式”中讨论。
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的表格资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d69823063.png)
“SHOW TABLES”叙述会传回目前使用中数据库的所有表格名称，你可以搭配“FROM”与“LIKE”关键字查询需要的表格资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d69a925cf.png)
“SHOW TABLES”叙述只会传回表格名称，如果需要详细的表格资讯，可以使用下列的“SHOW TABLE STATUS”叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d6a6a6c73.png)
“SHOW TABLE STATUS”叙述可以搭配“LIKE”或“WHERE”关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d6b20ced9.png)
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的字段资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d6b3f2e73.png)
“SHOW COLUMNS FROM 表格”叙述会传回目前使用中数据库，指定表格名称的字段资讯，你可以搭配第二个“FROM”关键字指定数据库：
[](http://box.kancloud.cn/2015-07-18_55a9d6b5817bb.png)
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的索引资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d6c6ee53a.png)
“SHOW INDEX FROM 表格”叙述会传回目前使用中数据库，指定表格名称的索引资讯，你可以搭配第二个“FROM”关键字指定数据库：
[](http://box.kancloud.cn/2015-07-18_55a9d70358145.png)
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的trigger资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d7183da9d.png)
“SHOW TRIGGERS”叙述会传回目前使用中数据库的所有trigger资讯，你可以搭配“FROM”关键字指定数据库；“LIKE”或“WHERE”关键字可以设定查询条件：
[](http://box.kancloud.cn/2015-07-18_55a9d71a45b8b.png)
## 2.2 建立元件资讯
下列的“SHOW”指令语法可以查询MySQL数据库服务器中建立各种元件的详细资讯：
| 指令 | 说明 |
| --- | --- |
| SHOW CREATE DATABASE 数据库名称 | 查询建立数据库的详细资讯 |
| SHOW CREATE TABLE 表格名称 | 查询建立表格的详细资讯 |
| SHOW CREATE FUNCTION 名称 | 查询建立Function的详细资讯 |
| SHOW CREATE PROCEDURE 名称 | 查询建立Procedure的详细资讯 |
| SHOW CREATE VIEW 名称 | 查询建立View的详细资讯 |
下列的叙述可以查询建立“world.city”表格的叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d72aa3998.png)
## 2.3 字符集与collation
下列的“SHOW”指令语法可以查询MySQL数据库服务器中的字符集与collation资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d72c977f6.png)
“SHOW CHARACTER SET”与“SHOW COLLATION”叙述都可以搭配“LIKE”或“WHERE”关键字设定查询条件：
[](http://box.kancloud.cn/2015-07-18_55a9d7326f228.png)
## 2.4 其它资讯
下列的“SHOW”指令语法可以查询MySQL数据库服务器中支援的储存引擎资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d73385b5e.png)
下列的“SHOW”指令语法可以查询MySQL数据库服务器状态与系统变量资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d73b22ca5.png)
下列的叙述可以查询MySQL数据库服务器中与字符集相关的变量资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d73c5d9f5.png)
# 3 DESCRIBE指令
“DESCRIBE”是MySQL数据库服务器提供的特殊指令，并不是标准的SQL叙述。它可以查询指定表格的字段资讯：
[](http://box.kancloud.cn/2015-07-18_55a9d73de765d.png)
“DESCRIBE”叙述可以指定要查询的字段名称，或是使用样版字串设定查询条件：
[](http://box.kancloud.cn/2015-07-18_55a9d741dfddc.png)
# 4 mysqlshow
MySQL数据库服务器提供一个可以在命令提示字符下执行的工具程式“mysqlshow”：
[](http://box.kancloud.cn/2015-07-18_55a9d74b9e7ff.png)
“mysqlshow”工具程式有下列几种不同的用法：
[](http://box.kancloud.cn/2015-07-18_55a9d76419f3e.png)
';
（16） 触发器
最后更新于：2022-04-01 23:40:15
**目录**
[TOC]
# 1 Triggers的应用
在“cmdev”资料中有一个“emplog”表格，如果有人执行任何修改“cmdev.emp”表格资料的动作，都要新增一笔讯息到“cmdev.emplog”表格中，查询这个表格的资料，就可以知道在什么时候曾经修改过“cmdev.emp”表格中的资料：
| 字段名称 | 型态 | NULL | 索引 | 默认值 | 其它资讯 | 说明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 纪录编号 |
| logdt | timestamp | NO | | CURRENT_TIMESTAMP | | 日期时间 |
| message | varchar(64) | YES | | NULL | | 讯息 |
要完成这样的需求，每一次修改“cmdev.emp”表格资料时，你都必需执行下列的工作：
[](http://box.kancloud.cn/2015-07-18_55a9d77c279e6.png)
要完成这样的需求，你可以使用stored routines来处理修改与新增纪录的工作，或是在应用程式中撰写程式来解决。不过都会是一件很麻烦的事情，而且比较容易造成遗漏纪录的情况。
MySQL数据库提供一种特别的数据库元件，称为“triggers”，一般会把它称为“触发器”。Triggers可以让你先把一些在特定状况要执行的叙述储存起来，MySQL数据库会在正确的时机自动帮你执行这些叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d7852e89f.png)
以上列讨论的需求来说，每一次修改“cmdev.emp”表格资料，都必须新增一笔纪录到“cmdev.emplog”表格中。这个需求的主角是“cmdev.emp”表格，所以你可以为这个表格建立一个trigger元件；因为是在修改资料的情况时才需要执行特定的工作，所以你要选择“UPDATE trigger”；新增一笔纪录到“cmdev.emplog”表格中的叙述就是储存在“cmdev.emp”表格的“UPDATE trigger”中。
如果你建立好需要的trigger元件后，MySQL数据库就会自动帮你执行这些工作：
[](http://box.kancloud.cn/2015-07-18_55a9d78a1305d.png)
# 2 建立Triggers
下列是建立trigger元件的语法：
[](http://box.kancloud.cn/2015-07-18_55a9d78b37d7c.png)
如果trigger元件执行的工作比较复杂，需要一个以上的叙述时，就要把叙述放在“BEGIN”与“END”区块中：
[](http://box.kancloud.cn/2015-07-18_55a9d78f97a28.png)
你可以依照需求为一个表格建立不同的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7903c68e.png)
以上列讨论的需求来说，每一次修改“cmdev.emp”表格资料，都必须新增一笔纪录到“cmdev.emplog”表格中。你要为“cmdev.emp”表格建立一个“UPDATE TRIGGER”；而“BEFORE”与“AFTER”就是“之前”与“之后”的意思。如果建立“BEFORE UPDATE TRIGGER”，那就表示在修改资料前会执行trigger；如果建立“AFTER UPDATE TRIGGER”，那就表示在修改资料后会执行trigger。以这个需求来说，“BEFORE”或“AFTER”都是一样的。
建立trigger元件与建立stored routines的方式一样，你也要使用“SQL script”来执行建立trigger的工作。下列的范例建立一个名称为“emp_before_update”的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d79334bf0.png)
执行上列的叙述后，MySQL数据库会储存你建立的trigger元件，可是它并不像stored routines可以用来呼叫与执行；MySQL数据库会自动帮你执行这些储存在trigger中的叙述。
为“cmdev.emp”表格建立一个“BEFORE UPDATE TRIGGER”以后，只要发生修改“cmdev.emp”表格资料的情况，MySQL数据库会自动执行这个trigger中的叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d79fe010d.png)
不论是“UPDATE”或是其它两种Trigger元件，MySQL数据库都是以“纪录”来执行trigger。以下列的范例来说，一个会修改三笔纪录的“UPDATE”叙述，MySQL数据库会执行trigger三次：
[](http://box.kancloud.cn/2015-07-18_55a9d7b6966cf.png)
如果在执行修改“cmdev.emp”表格叙述以后，实际上并没有修改任何纪录资料，那MySQL数据库也不会执行trigger：
[](http://box.kancloud.cn/2015-07-18_55a9d7c3eed40.png)
在你建立trigger元件时，要特别注意下列的限制：
* 同一个数据库不可以有相同名称的Trigger
* TEMPORARY表格与View不可以建立Trigger
* 不可以使用“SELECT”叙述
* 不可以使用“CALL”叙述
* 不可以使用与交易(transactions)相关的叙述，包含“START TRANSACTION”、“COMMIT”与“ROLLBACK”
# 3 删除Triggers
你可以使用下列的语法删除不再需要的trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7eb4238d.png)
如果想要修改trigger元件中的叙述，你要先删除以后，再建立新的trigger元件。所以你可以在在建立trigger元件的叙述中，加入删除trigger元件的叙述：
[](http://box.kancloud.cn/2015-07-18_55a9d7ec993f7.png)
# 4 OLD与NEW关键字
在triggers元件中，可以使用一般的SQL叙述完成需要执行的工作，也可以使用在stored routines中讨论过的变量与流程控制，让triggers元件可以处理比较复杂的需求。MySQL数据库在triggers元件中额外提供“OLD”与“NEW”两个关键字：
[](http://box.kancloud.cn/2015-07-18_55a9d7f246a50.png)
因为“OLD”与“NEW”两个关键字的特性，所以它们可以使用的triggers种类会有一些限制：
| Trigger种类 | OLD | NEW |
| --- | --- | --- |
| INSERT | 不能使用 | 新增的字段资料 |
| UPDATE | 修改前的字段资料 | 修改后的字段资料 |
| DELETE | 删除前的字段资料 | 不能使用 |
以“cmdev.emp”表格的“UPDATE TRIGGER”来说，下列是使用“OLD”与“NEW”关键字取得的字段值：
[](http://box.kancloud.cn/2015-07-18_55a9d7f4b87b5.png)
延续上列为更新“cmdev.emp”表格执行纪录工作的trigger来说，如果想要让纪录的讯息更加详细，包含修改前与修改后的部门编号：
[](http://box.kancloud.cn/2015-07-18_55a9d7f56a9f7.png)
要完成上列的需求，就必须使用“OLD”与“NEW”关键字取得的字段值：
[](http://box.kancloud.cn/2015-07-18_55a9d7f96755f.png)
为表格建立“UPDATE TRIGGER”以后，就表示执行这个表格的修改动作，都会执行这个trigger元件：
[](http://box.kancloud.cn/2015-07-18_55a9d7fe9fed7.png)
如果要将“emp_before_update”的需求，修改为“只有在修改员工的部门编号时，才需要新增修改纪录”，你就可以使用在sotred routines讨论过的“IF”指令来完成这个需求：
[](http://box.kancloud.cn/2015-07-18_55a9d8066d681.png)
在“INSERT TRIGGER”中使用“NEW”关键字时，要特别注意“AUTO_INCREMENT”字段型态：
[](http://box.kancloud.cn/2015-07-18_55a9d80c21f93.png)
如果有需要的话，你也可以使用“SET”叙述设定“NEW”关键字指定的字段值。以下列的情况来说：
[](http://box.kancloud.cn/2015-07-18_55a9d8194ce34.png)
要解决上列的问题，你可以要求在新增资料的时候，不要使用小写的文字。不过使用下列的“BEFORE INSERT TRIGGER”来处理的话，会更方便一些：
[](http://box.kancloud.cn/2015-07-18_55a9d81af2f91.png)
建立好这个“BEFORE INSERT TRIGGER”以后，就算新增的员工资料包含小写的名称与职务，这个trigger元件都会在新增纪录之前，把它们转换为大写：
[](http://box.kancloud.cn/2015-07-18_55a9d8281264b.png)
# 5 查询Triggers的相关资讯
如果想要查询triggers的相关资讯，可以查询“information_schema.TRIGGERS”表格，下列是它的主要字段：
| 字段名称 | 型态 | 说明 |
| --- | --- | --- |
| TRIGGER_SCHEMA | varchar(64) | 数据库 |
| TRIGGER_NAME | varchar(64) | 名称 |
| EVENT_MANIPULATION | varchar(6) | 启动的事件，有INSERT、UPDATE与DELETE |
| EVENT_OBJECT_SCHEMA | varchar(64) | 作用的数据库 |
| EVENT_OBJECT_TABLE | varchar(64) | 作用的表格 |
| ACTION_STATEMENT | longtext | 执行的工作 |
| ACTION_TIMING | varchar(6) | 启动的时机，有BEFORE与AFTER |
如果你想要查询建立某个stored routines的详细资讯，可以使用下列的语法：
[](http://box.kancloud.cn/2015-07-18_55a9d83673948.png)
';
（15） 存储过程进阶
最后更新于：2022-04-01 23:40:13
**目录**
[TOC]
## 1 错误编号
使用SQL叙述请资料库执行一些工作的时候，可能会因为输入错误或其它的原因，造成资料库产生错误讯息，下列的SQL叙述在SQL query browser中执行以后，MySQL会传回一个错误编号与错误讯息，告诉你查询的表格名称不存在：
MySQL用来表示错误的编号有两种，一种是MySQL资料库伺服器用的错误编号，使用四位数的数字来表示各种不同的错误；另外一种是各种资料库软体都适用的「SQL state」编号，使用五个字元的字串，来表示执行一个叙述以后各种不同的状况：
注：MySQL的错误编号称为「Server Error Codes」，详细的错误编号与对应的错误讯息可以参考MySQL参考手册的附录B(MySQL 5.0 Reference Manual、Appendix B. Error Codes and Messages、1584页)。
## 2 Handlers
在撰写stored routines时，MySQL提供一种很特别的宣告语法，你可以使用它宣告「handler」，handler用来处理stored routines中可能会发生的错误，让你可以针对发生的错误执行必要的补救工作，也可以防止stored routines因为发生错误而中止。首先要特别注意宣告「handler」的位置：
Handler是用来处理错误用的，所以在宣告的时候，要设定处理的错误种类和决定后续的流程。下列是宣告handler的语法：
Handler的宣告包含发生的错误时要执行的叙述，如果有多个叙述时，就一定要使用「BEGIN-END」区块，把这些叙述放在区块中：
下列是一个新增部门资料的procedur，呼叫它的时候要提供部门编号、名称与地点三个参数，这个procedure会使用你的参数帮你新增一笔纪录到「cmdev.dept」表格中，新增后会显示「Success!」的讯息：
下列是呼叫「cmdev.test_handler」procedure的范例：
因为在「cmdev.dept」表格的定义中，部门编号「deptno」栏位设定为primary key，所以它的栏位值是不可以重复的。所以如果再执行一次上列呼叫「cmdev.test_handler」procedure的范例：
在执行一个stored routine的过程中，如果发生任何错误，MySQL都会停止继续执行，再传回错误编号与错误讯息，告诉呼叫的人发生了什么状况：
撰写stored routines处理资料库的工作，除了之前已经讨论过的许多好处外，使用handler来处理错误，让执行工作的过程可以更加顺利，也是使用stored routines的主要原因。
下列的范例同样是提供新增部门资料功能的procedure，不过为了希望发生索引值重复的错误时，不要因为错误而中断执行的工作，也不要传回错误编号与错误讯息，而是自己显示一个错误讯息，清楚的告诉使用者发生了什么状况。这样的需求就必须在procedure中加入handler的宣告。索引值重复的SQL state是「23000」，这个编号会使用在handler的宣告中：
加入handler宣告的stored routines，在执行过程中如果没有发生任何问题，handler是没有任何作用的，stored routines会正常的执行完所有的叙述：
呼叫加入handler的宣告的「cmdev.test_handler2」，如果没有发生任何问题，在新增部门纪录后会显示「Success!」的讯息：
如果在执行过程中发生任何问题了，MySQL会使用发生的错误编号，与你在handler宣告中指定的错误执行比对的工作，如果一样的话，接下来就交由handler来处理这个错误，MySQL就不会中断执行与回传错误：
呼叫加入handler的宣告的「cmdev.test_handler2」时，如果指定的部门编号在资料表中已经存在，执行新增的叙述时就会发生发生索引值重复的错误。这种错误的SQL state是「23000」，MySQL错误编号是「1062」：
在宣告handler时，除了指定handler要处理哪一种错误外，还要根据自己的需求，决定处理错误以后的后续流程：
一个宣告为「EXIT」的handler，在执行完handler包含的叙述以后，会离开handler所在的区块；而宣告为「CONTINUE」的handler，执行的流程会像这样：
上列新增部门资料的procedure范例，根据新增纪录的结果，会显示「Success!」或「Error!」两种结果。如果希望不论新增纪录成功或发生问题，都要把结果储存到下列的「cmdev.deptlog」表格中：
| 栏位名称 | 型态 | NULL | 索引 | 预设值 | 其它资讯 | 说明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 纪录编号 |
| logdt | timestamp | NO | CURRENT_TIMESTAMP | 日期时间 |
| message | varchar(64) | YES | NULL | 讯息 |
下列的范例使用「CONTINUE HANDLER」来执行新增部门纪录资料，而且会记录执行后的结果：
呼叫「test_handler3」procedure后，如果没有发生任何问题，除了新增部门纪录外，还会新增一笔成功的讯息到「cmdev.deptlop」表格：
如果新增部门纪录时发生错误，「CONTINUE HANDLER」会把「v_message」变数值设定为「Error!」，然后再新增一笔错误的讯息到「cmdev.deptlop」表格：
下列的范例是呼叫「test_handler3」procedure后，纪录在「cmdev.deptlop」表格中的结果：
索引值重复与不允许NULL值的错误，都是属于SQL state中的「23000」，如果你想要分别处理这两种错误的话，你可以针对每一种错误，宣告不同的handler来处理，不过在指定错误时，就要使用MySQL错误编号：
下列的范例是呼叫「test_handler4」procedure后，纪录在「cmdev.deptlop」表格中的结果：
在宣告handler时指定的错误情况有下列几种：
## 3 Conditions
如果在stored routines中需要宣告handler来处理错误的话，你还可以宣告「conditions」给handler使用，下列是区块中conditions宣告的位置：
你可以宣告condition用来代表某一种问题，下列是宣告condition的语法：
下列的范例宣告两个condition，分别代表不允许NULL值与索引值重复的错误，宣告好的condition，就可以使用在handler的宣告中：
## 4 Cursors
如果stored routines需要针对一个查询结果中的每一笔纪录执行需要的处理工作，你可以宣告一个「cursor」来代表一个查询的结果，并且使用cursor依序处理所有纪录资料。下列是在区块中宣告cursor的位置：
宣告好cursors以后，可以使用「OPEN」叙述来开启，接着使用「FETCH」叙述读取资料，最后要使用「CLOSE」叙述关闭用完的cursor：
宣告cursor时所指定的查询叙述，与使用「FETCH」读取资料时，要特别注意相对的顺序：
一般来说，都会把cursor称为「游标」或「指标」。当你宣告好一个需要的cursor以后，接着使用「OPEN」叙述开启cursor，这时会有一个游标指向查询结果的第一笔纪录：
当你使用「FETCH」叙述时，除了读取目前游标的纪录资料外，还会将游表指向下一笔纪录：
以上列宣告的cursor来说，从开启到读取所有纪录资料的游标状况会像这样：
在stored routines中使用cursor，通常需要下列的流程：
下列是流程与对应的叙述：
为了读取cursor中所有的纪录资料，要另外宣告handler来控制在没有资料读取时可以离开回圈：
除了使用「EXIT HANDLER」外，也可以使用「CONTINUE HANDLER」来控制在没有资料读取时可以离开回圈：
下列的说明表示没有资料可以读取时的流程：
在资料库的应用中，通常是需要针对一个查询的结果执行比较复杂的工作，才会在sotred routines中宣告与使用cursor。如果你常常需要查询月薪在某个金额以上的员工资料，而且要把这些员工资料储存到一个表格中。这样的需求包含执行查询与处理新表格的工作，你就可以考虑使用包含cursor的procedure来完成这些工作。
下列的范例可以将月薪在指定金额以上的员工资料储存到「cmdev.topemp」表格中：
## 5 设定、修改与删除Stored routines
### 5.1 建立Stored routines时的设定
建立stored routines时，也可以加入一些额外的设定：
下列是这些额外设定的说明：
* LANGUAGE {SQL}：设定Stored routine中用来撰写叙述的语言，目前只有支援SQL，所以只能在LANGUAGE后面指定SQL
* [NOT] DETERMINISTIC：如果传送相同的参数给Stored routine，每次执行它以后都会产生同样的结果，这个Stored routine就应该设定为「DETERMINISTIC」；否则就要设定为「NOT DETERMINISTIC」。预设值为「NOT DETERMINISTIC」
* SQL SECURITY { DEFINER | INVOKER }：设定Stored routine要以建立者或执行者的权限执行
* COMMENT '说明字串'：设定Stored routine的说明
### 5.2 修改Stored routines设定
使用「ALTER PROCEDURE」与「ALTER FUNCTION」可以修改它们的额外设定，如果要修改参数或里面的叙述，必须删除后再重新建立。下列是修改stored routines设定的语法：
下列的范例执行修改「cmdev.gen_top_emp」的设定：
### 5.3 删除Stored routines
如果不再需要一个已经建立的stored routines，你可以使用下列的语法来删除它们：
## 6 查询Stored routines的相关资讯
如果想要查询stored routines的相关资讯，可以查询「information_schema.ROUTINES」表格，下列是它的主要栏位：
| 栏位名称 | 型态 | 说明 |
| --- | --- | --- |
| ROUTINE_SCHEMA | varchar(64) | 资料库 |
| ROUTINE_NAME | varchar(64) | 名称 |
| ROUTINE_TYPE | varchar(9) | procedure或function |
| DTD_IDENTIFIER | varchar(64) | procedure固定为「NULL」；function为回传值型态 |
| ROUTINE_DEFINITION | longtext | Stored routine的内容 |
| IS_DETERMINISTIC | varchar(3) | DETERMINISTIC的设定 |
| SECURITY_TYPE | varchar(7) | DEFINER或INVOKER |
| CREATED | datetime | 建立的日期时间 |
| LAST_ALTERED | datetime | 最后修改的日期时间 |
| ROUTINE_COMMENT | varchar(64) | 说明 |
| DEFINER | varchar(77) | 建立Stored routine的资料库使用者 |
你也可以使用MySQL提供的「SHOW」指令来查询stored routines的相关资讯：
如果你想要查询建立某个stored routines的详细资讯，可以使用下列的语法：
';
（14） 存储过程的变量和流程
最后更新于：2022-04-01 23:40:11
**目录**
[TOC=2]
## 1 宣告与使用变数
在Stored routines中，除了可以宣告需要的参数外，如果需要处理比较复杂的资料，你也可以宣告「区域变数、local variables」。下列是宣告区域变数的语法与位置：
下列是几种宣告区域变数的范例：
宣告需要的区域变数后，你就可以在stored routines中使用它们，需要指定变数值的话，可以使用下列两种语法：
下列是宣告与使用「SET」叙述指定变数的范例：
下列的范例使用「SELECT」叙述，把查询叙述回传的资料指定给变数：
在Stored routines中宣告区域变数，一定要放在「BEGIN」与「END」区块中：
在一个Stored routines中，除了基本的「BEGIN」与「END」区块，也可以再使用「BEGIN」与「END」设定一个区块，每一个区块都可以宣告需要的区域变数：
在「BEGIN」与「END」区块中宣告的区域变数，只有在宣告的区块中有效，这也是它称为区域变数的原因：
如果你使用一个已经被清除的区域变数，在建立stored routines时不会有问题，不过使用的时候就会发生错误：
在同一个区块宣告变数时，不可以使用同样的变数名称；不过你可以在内层区块中，使用外层区块已经使用过的变数名称，可是要特别注意它们的有效范围：
注：在撰写stored routines时，如果在多个区块中宣告变数，应该还是使用不同的变数名称会好一些。
## 2 判断
建立与使用stored routines可以帮你一次执行许多叙述，简化资料库的操作；除了这个好处外，stored routines还提供许多判断的语法，让你可以执行需要的判断，再根据判断的结果执行不同的工作。
### 2.1 IF
MySQL在stored routines中提供「IF」叙述，你可以在「IF」叙述中设定判断的条件，与条件成立时要执行的工作。下列是「IF」叙述的语法：
下列的procedure范例接收一个表示体重的整数参数，它会使用这个参数来判断体重是否太重，如果超过100公斤的话，就会显示「You are heavy!」：
呼叫上列的「test_weight」procedure范例会有下列的结果：
如果你希望体重超过100公斤时，显示「You are heavy!」，体重没有超过100公斤时，显示「Good!」。这样的需求可以在「IF」叙述中使用「ELSEIF」判断其它需要的条件：
呼叫上列的「test_weight2」procedure范例会有下列的结果：
你可以依照需求在「IF」叙述中使用多个「ELSEIF」来判断不同的条件，也可以使用一个「ELSE」来处理所有条件都不成立时要执行的工作：
呼叫上列的「test_weight3」procedure范例会有下列的结果：
标准体重会依照身高与性别而不同，所以会有类似下列这样的表格：
| 身高范围 | 性别 | 标准体重 |
| --- | --- | --- |
| 160~164 | 男 | 58 |
| 165~169 | 男 | 60 |
女 |56 |
| 170~174 | 男 | 64 |
女 |60 |
下列是一个依照上列表格所完成的标准体重函式：
完成可以传回标准体重的「std_weight」函式以后，就可以用在下列这个判断体重的procedure中：
### 2.2 CASE
在stored routines中还可以使用「CASE」叙述执行条件判断的工作。「CASE」叙述有两种语法，第一种语法跟「IF」叙述是很类似的：
以判断体重的需求来说，使用「CASE」叙述同样可以完成：
「CASE」叙述还可以使用下列这种语法：
这样的语法很适合使用在类似「ENUM」资料型态的判断，例如下列这个判断季节的procedure：
使用这种「CASE」语法来执行判断工作时，要特别注意错误资料的处理：
你应该加入「ELSE」来预防错误资料造成的问题：
## 3 回圈
在stored routines中如果需要执行一个工作多次的时候，就可以使用「回圈、loops」，搭配使用判断与回圈，把一些固定又繁复的工作撰写成stored routines储存起来，可以大幅度简化资料库的操作。
### 3.1 WHILE
下列是可以用来执行一个工作多次的「WHILE」回圈语法：
你必须依照需求设定「WHILE」回圈语法中的判断条件，由它来控制回圈是否继续执行：
下列的「summary_while」范例可以为你从1开始加总到参数指定的数字：
### 3.2 REPEAT
下列是可以用来执行一个工作多次的「REPEAT」回圈语法：
你必须依照需求在「REPEAT」回圈语法中的「UNTIL」设定判断条件，由它来控制回圈是否继续执行：
下列的「summary_repeat」范例可以为你从1开始加总到参数指定的数字：
### 3.3 LOOP
下列是可以用来执行一个工作多次的「LOOP」回圈语法：
如果只是单纯的使用「LOOP」回圈的话，只要进入回圈后，就会不断重复执行回圈中的叙述，永远不会停止：
## 4 标签
在使用「BEGIN-END」、「WHILE」、「REPEAT」与「LOOP」四种区块时，都可以为它们设定「标签、label」：
标签是由你自己为这些区块取的名字，下列使用「LOOP」回圈来说明标签的设定规则，这个规则同样适用在其它三种区块：
在一般的状况下，通常不需要为区块设定标签。如果为了控制sotred routines的执行流程，才会设定区块的标签。设定标签以后，就可以搭配使用「LEAVE」叙述来控制流程，下列是「LEAVE」叙述在「LOOP」回圈中的效果：
「LEAVE」叙述在其它三种区块中有同样的效果：
搭配使用「LEAVE」叙述来控制流程，就可以控制「LOOP」回圈在需要的时候离开。下列的「summary_loop」范例可以为你从1开始加总到参数指定的数字：
设定标签以后，也可以搭配使用「ITERATE」叙述来控制流程，下列是「ITERATE」叙述在「LOOP」回圈中的效果：
「ITERATE」叙述不可以使用在「BEGIN-END」区块中，不过它在其它两种区块中有同样的效果：
下列的「summary_iterate」范例可以为你从1开始加总到参数指定的数字，不过额外使用「ITERATE」叙述控制，让这个function只会加总奇数：
';
（13） 存储过程入门
最后更新于：2022-04-01 23:40:09
**目录**
[TOC]
## 1 Stored Routines的应用
在资料库管理系统的应用中，不论是一般或网页的应用程式，它们在执行资料查询与维护的时候，都必须使用SQL叙述来请资料库执行各种不同的工作。在比较复杂的应用程式需求下，很常会遇到类似下列的一组工作：
SQL叙述的特点是一次只能执行一件工作，所以要完成上列的工作，就必须执行数个SQL叙述。如果这样的一组工作是很常执行的，你就可以考虑把这些要执行的叙述建立为「Stored procedure」元件：
把这一组工作建立为Stored procedure元件以后，以后要执行这些工作时，就可以「呼叫、call」这个建立好的Stored procedure元件：
要建立人口数比「USA」多的国家表格时，只要传入指定的国家代码就可以了：
Stored procedures是Stored routines其中一种元件，你可以视需要在资料库中建立许多不同用途的Stored procedure。它可以包含你需要执行的一组工作，也可以依照需求设定必要的参数资料(例如上列「new_mycountry」中的国家代码)。呼叫这些建立好的Stored procedure可以帮你省掉很多繁复的工作，请资料库一次完成你要执行的工作。
Stored routines另外提供一种「Stored functions」元件，除了MySQL资料库提供许多各种不同的函式外，你也可以建立自己的函式，这种函式称为Stored functions。例如下列的范例：
你可以自己建立一个名称为「ROUND2」的Stored functions，这个函式固定将一个指定的数值四舍五入到小数两位：
建立好需要的Stored function元件以后，它使用起来就跟你在使用MySQL提供的函式一样：
你同样可以在资料库中建立许多需要的Stored functions，把一些比较复杂工作建立为Stored functions元件以后，你就可以跟使用MySQL提供的函式一样来使用它们，同样可以简化许多繁复的工作。
在MySQL资料库管理系统中，把Stored procedures与Stored functions合称为「Stored routines」。在后续的内容中，会把Stored procedures简称为「procedures」；把Stored functions简称为「functions」。
### 1.1 Stored Procedures介绍
Stored procedures元件也是一种可以建立、维护与删除的资料库元件。表格元件是用来储存资料用的；索引元件是储存索引与增加效率用的；而Stored procedures元件是用来「储存程序」用的，程序表示一组特定的工作，如果在使用资料的过程中，常常需要执行一组同样的工作，你就可以考虑把执行工作需要的叙述建立为Stored procedures元件。
下列是建立Stored procedures元件的基本语法：
下列是删除Stored procedures元件的基本语法：
下列是呼叫Stored procedures元件的基本语法：
### 1.2 Stored Functions介绍
如果MySQL提供的函式无法完成你的工作，或是想要改善一些比较复杂的叙述，你都可以建立需要的Sotred functions元件。跟Stored procedures一样，它也是一种用来「储存程序」的元件，不过建立好的Stored procedures元件要使用「CALL」来呼叫，也就是请资料库执行储存在Stored procedures中的工作；要使用建立好的Stored functions元件，就跟使用MySQL提供的函式一样来使用它们。
下列是建立Stored functions元件的基本语法：
下列是删除Stored functions元件的基本语法：
## 2 在MySQL Workbench中管理Stored routines
Stored routines元件中可以包含许多要执行的SQL叙述，在后续的讨论中，它也可以包含宣告与设定变数，和控制执行流程的指令。所以Stored routines元件其实就有一点类似开发应用程式用的程式语言，不过它不会像程式语言那么复杂，而且大部份都是跟资料库相关的SQL叙述。
### 2.1 SQL Script、DELIMITER与Stored routines
建立需要的Stored routines元件要使用「CREATE PROCEDURE」或「CREATE FUNCTION」叙述，虽然它们跟其它的SQL叙述一样，也是请资料库执行一件工作，不过Stored routines通常会包含许多需要的叙述，所以通常会使用「SQL script」来执行建立Stored routines的工作。
SQL script是一个包含许多SQL叙述的档案，你可以把想要执行的SQL叙述都集中在一个档案中。以建立课程范例资料库的「cmdev.sql」档案来说，它的内容会像这样：
MySQL使用分号作为预设的delimiter，delimiter在SQL script档案中的使用是很重要的，MySQL在执行档案中的叙述时，是以delimiter来分辨一个SQL叙述的范围。MySQL提供「DELIMITER」指令，可以修改预设的delimiter符号：
在一般的应用时，你通常不会去修改预设的delimiter符号；可是在建立Stored routines元件的SQL script档案中就一定要使用了。下列是建立Stored procedure元件的基本内容：
在「MySQL Workbench」中选择功能表「File > New Query Tab」，接下来就可以输入下列建立procedure的叙述：
完成建立procedure的叙述后，要执行这个叙述来建立需要的procedure元件：
上列范例所建立的「show_countries」procedure元件中，只有包含一个查询国家资料的叙述，如果一个procedure元件执行的工作只是这样的话，应该就不需要建立procedure元件了。所以procedure元件通常会包含许多要执行的叙述，这时候就一定要使用「BEGIN」与「END」。下列是建立包含多个叙述Stored procedure元件的基本内容：
以下列的「my_world_count」procedure元件来说，它可以一次查询国家、语言与城市三个表格的数量：
使用SQL script建立functions同样要使用「DELIMITER」关键字设定delimiter。「CREATE FUNCTION」的语法另外包含「RETURNS」与「RETURN」两个关键字。下列是建立Stored functions的基本内容：
以下列的「my_date」Stored function来说，它会传回「年/月/日时:分:秒星期」格式的日期时间资料：
如果function元件包含许多要执行的叙述，也一定要使用「BEGIN」与「END」。下列是建立包含多个叙述Stored functions元件的基本内容：
下列建立「my_date2」Stored function的叙述中，因为包含多个叙述，所以一定要使用「BEGIN」与「END」：
注：在Stored routines中使用「DECLARE」与「SET」在「Sotred Routines的变数与流程」中讨论。
### 2.2 管理Stored Procedures
除了使用SQL script建立需要的Stored Procedures外，你也可以使用「MySQL Workbench」提供的功能来管理Stored Procedures。以建立procedure元件来说，在「Stored Procedures」目录上按滑鼠右键后选择「Create Stored Procedure…」：
MySQL Workbench会帮你准备一个建立procedure元件的样版，你只要在「BEGIN」与「END」之间，输入这个procedure元件需要执行的叙述，再选择「Apply」按钮：
在确认的视窗选择「Apply」按钮：
最后选择「Finish」就可以建立指定的Stored Procedure：
在建立好的Stored Procedure上按滑鼠右键后选择「Alter Stored Procedure…」和「Drop Stored Procedure…」可以修改或删除指定的Stored Procedure。
### 2.3 管理Stored Functions
你也可以使用「MySQL Workbench」提供的功能来管理Stored functions。以建立function元件来说，在「Functions」目录上按滑鼠右键后选择「Create Function…」：
MySQL Workbench会帮你准备一个建立Function元件的样版，输入这个Function的内容后，再选择「Apply」按钮：
在确认的视窗选择「Apply」按钮：
最后选择「Finish」就可以建立指定的Function：
在建立好的Function上按滑鼠右键后选择「Alter Function…」和「Drop Function…」可以修改或删除指定的Function。
## 3 Stored Routines的参数
Stored routines可以使用参数(parameters)让使用者传送资料给stored routines使用，procedures与functions都可以依照需要决定参数的个数与型态。
### 3.1 Stored Functions的参数
Functions参数的决定会比procedures简单，因为functions的参数只是用来接收资料后，在functions中使用。你必须决定每一个参数的名称和型态，再依照想要的顺序定义在functions中：
以下列一个合计功能的function来说，它需要两个「INT」型态的整数参数：
在呼叫「my_summary」的时候，依照参数的定义，指定两个要合计的整数数值，这个function会将两个传入的整数数值加起来后回传给你：
在呼叫function的时候，一定要依照参数的定义，传送正确个数的参数资料：
除了参数的个数外，你也要遵守参数型态的规定：
一个function的定义不一定需要参数，以下列的范例来说，呼叫「my_date」时并不需要传送任何参数资料，不过无论是否需要参数，在呼叫function时，名称后面的左右刮号是不可以省略的：
### 3.2 Stored Procedures的参数
Procedures参数的定义与functions大致上相同，除了必须决定每一个参数的名称、型态与顺序，你还需要决定每一个参数的用途：
下列是参数用途的说明：
* IN：「输入、input」用的参数。这种参数与functions中的参数完全一样，在呼叫procedures时传送资料给procedures用的
* OUT：「输出、output」用的参数。在呼叫procedures时，不能接收传送的资料，不过在执行procedures时，可以设定这类参数的值，新的值在执行完成后，可以回传给呼叫的地方使用
* INOUT：「输入与输出、input与output」用的参数。同时具有「IN」与「OUT」两种用途
下列是一个说明三种用途参数的范例：
呼叫procedures时要依照定义的参数个数与型态来传送资料：
在呼叫Procedures时传送的参数资料，会因为不同的用途而有不同的限制：
如果违反参数用途上的规定就会发生错误：
所以在呼叫procedures时，「OUT」与「INOUT」参数必须指定变数名称，这是因为「OUT」与「INOUT」参数在执行完成后会回传资料，使用变数名称才可以接收procedures回传的资料：
执行procedures以后，指定给「OUT」与「INOUT」的变数名称，就会储存procedures中设定的值：
如果在呼叫procedures之前，先把参数资料设定为使用者变数，再把它们指定给参数使用：
执行上列呼叫procedures的叙述后，你可以发现设定为「OUT」用途的参数是不能接收参数资料的；而下列查询使用者变数的叙述，可以发现设定为「IN」用途的参数没有回传资料的功能：
以下列的范例来说，呼叫「country_count」需要一个洲名的参数，执行以后，它会使用你的洲名执行查询国家的数量。这个洲名参数的需求，只是用来设定查询条件用的，并不需要回传资料，所以这样的参数适合设定为「IN」：
下列的范例先设定好一个使用者变数储存洲名，再呼叫「country_count」：
在procedures与functions中，MySQL提供一种特别的查询叙述。一般的查询叙述是用来回传需要的资料用的，而这种查询叙述可以把「SELECT」子句中指定的资料指定给变数：
以下列的范例来说，呼叫「country_count2」需要一个洲名的参数，它会使用你的洲名执行查询国家的数量，不过执行以后，它会回传国家的数量给你。所以这个procedure需要第二个参数用来回传国家的数量，以这样的参数需求，国家的数量的参数适合设定为「OUT」：
呼叫「country_count2」时要提供洲名与接收国家数量的变数名称，在procedure执行以后，使用者变数「my_count」就会储存国家数量了：
';
（12） 预处理语句
最后更新于：2022-04-01 23:40:06
**目录**
[TOC]
# 1 使用者变量
MySQL数据库服务器提供一种简易的储存资料方式，称为“使用者变量、user variables”。使用者变量储存一些简单的资料，例如数字或字串，它们可以在后续的操作中使用。下列是设定使用者变量的语法：
[](http://box.kancloud.cn/2015-09-15_55f7ef88abe52.png)
下列的叙述设定两个储存字串资料的使用者变量：
[](http://box.kancloud.cn/2015-09-15_55f7ef8bacf3d.png)
设定好使用者变量以后，你可以在“SELECT”叙述中查询它们储存的内容：
[](http://box.kancloud.cn/2015-09-15_55f7ef8c0150c.png)
如果你需要设定多个变量的话，可以在一个“SET”叙述中设定多个需要的使用者变量：
[](http://box.kancloud.cn/2015-09-15_55f7ef9137382.png)
使用查询叙可以确认上列的叙述已经设定好的两个使用者变量：
[](http://box.kancloud.cn/2015-09-15_55f7efa09b80f.png)
使用“SELECT”叙述也可以设定需要的使用者变量，不过要特别注意指定的符号只能使用“:=”：
[](http://box.kancloud.cn/2015-09-15_55f7efa1433c0.png)
下列的叙述设定两个储存整数资料的使用者变量，因为是使用“SELECT”叙述，所以设定好使用者变量以后，也会显示设定的使用者变量内容：
[](http://box.kancloud.cn/2015-09-15_55f7efa1a1461.png)
再使用查询叙确认上列的叙述已经设定好的两个使用者变量：
[](http://box.kancloud.cn/2015-09-15_55f7efa229afb.png)
已经设定好的使用者变量，可以使用在大部份的叙述中，例如下列的范例使用变量来设定查询叙述的条件设定：
[](http://box.kancloud.cn/2015-09-15_55f7efa287250.png)
使用“SELECT”叙述设定使用者变量的方式，也可以直接把查询叙述传回的资料储存起来：
[](http://box.kancloud.cn/2015-09-15_55f7efa2d55de.png)
上列范例执行后所设定的使用者变量，也可以使用在后续的叙述中：
[](http://box.kancloud.cn/2015-09-15_55f7efa3631d0.png)
你也可以拿使用者变量来执行需要的运算：
[](http://box.kancloud.cn/2015-09-15_55f7efa3c1233.png)
注：“LIMIT”子句指定的数字不可以使用变量。
# 2 Prepared Statements的应用
一个数据库在建立好并开始使用以后，数据库服务器就会接收各种不同的叙述来执行工作。以查询叙述来说，有一些叙述可能大部份的内容都是一样的，只有在条件的设定上会不一样。就算这些叙述的内容是差不多的，数据库服务器每次接收到叙述时，还是要执行一些同样的工作：
[](http://box.kancloud.cn/2015-09-15_55f7efa478f37.png)
如果有“许多要执行的叙述，可是内容却相似”的情况，可以使用“Prepared statements”改善数据库的效率。首先，你要把这种叙述先准备好：
[](http://box.kancloud.cn/2015-09-15_55f7efa9f20a5.png)
服务器已经准备好的叙述就称为“prepared statement”，后续要使用这种叙述前，要先设定好prepared statement需要的资料。在上列的范例中，因为prepared statement的内容中有一个问号，所以你要先设定好一个资料，也就是国家的代码。然后再请服务器执行指定的prepared statement，服务器就会传回执行后的结果了：
[](http://box.kancloud.cn/2015-09-15_55f7efaa8a62d.png)
# 3 建立、执行与移除Prepared Statements
如果有“许多要执行的叙述，可是内容却相似”的情形，你就可以考虑请服务器把这种叙述建立为prepared statement。下列是建立prepared statement的语法：
[](http://box.kancloud.cn/2015-09-15_55f7efab487cd.png)
如果常需要查询某个国家的代码、名称与GNP的话，你可以建立一个下列的prepared statement。叙述中的问号是“参数标记、parameter marker”，表示执行这个prepared statement需要一个参数资料：
[](http://box.kancloud.cn/2015-09-15_55f7efab9018c.png)
建立好需要的prepared statement以后，你必须使用“EXECUTE”来执行它：
[](http://box.kancloud.cn/2015-09-15_55f7efabe329b.png)
执行一个prepared statement并不一定需要传送资料给它，要依据prepared statement包含的叙述中有没有问号来决定。如果有问号的话，一个问号就需要先设定好一个使用者变量，然后再使用“USING”传送资料给prepared statement使用：
[](http://box.kancloud.cn/2015-09-15_55f7efac7687a.png)
后续要执行这个查询时，只要依照同样的步骤就可以查询别个国家资料了：
[](http://box.kancloud.cn/2015-09-15_55f7efad67190.png)
如果一个prepared statement已经不需要了，你可以使用下列的语法，从服务器中删除指定的prepared statement：
[](http://box.kancloud.cn/2015-09-15_55f7efadab667.png)
下列的叙述执行以后会删除名称为“my_country”的prepared statement：
[](http://box.kancloud.cn/2015-09-15_55f7efb2e0a7f.png)
# 4 Prepared Statements的参数
在建立prepared statement时，你会依照叙述的需求设定参数标记，这些参数标记也决定执行prepared statement时，须要传多少参数资料给它才可以正确的执行。以下列新增纪录的叙述来说，它就使用了三个参数标记，依序为部门编号、名称与地点：
[](http://box.kancloud.cn/2015-09-15_55f7efb330798.png)
根据prepared statement使用的参数标记，在执行prepared statement时一定传送正确的参数资料，否则会产生错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7efb388717.png)
下列的范例先把要新增部门的编号、名称与地点资料设定为使用者变量，在执行“new_dept”时传送给它使用：
[](http://box.kancloud.cn/2015-09-15_55f7efb3c1f02.png)
如果传送的参数数量不对的话，就会产生错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7efb44a88d.png)
如果传送的使用者变量不存在的话，会自动使用“NULL”值代替：
[](http://box.kancloud.cn/2015-09-15_55f7efb498cbc.png)
# 5 有效范围
所有使用者变量与prepared statements都是某一个用户端专属的：
[](http://box.kancloud.cn/2015-09-15_55f7efb4ebe4b.png)
如果用户端离线以后，他所设定的使用者变量与prepared statements都会被清除：
[](http://box.kancloud.cn/2015-09-15_55f7efb57ee1b.png)
所以建立prepared statements时，不可以指定它是属于哪一个数据库，否则会有错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7efb5c9585.png)
';
（11） 视图
最后更新于：2022-04-01 23:40:04
**目录**
[TOC]
# 1 View的应用
在使用MySQL数据库的时候，你会使用各种不同的SQL叙述来执行查询与维护的工作。数据库在运作一段时间后，你会发觉不论是查询与维护的叙述，都可能会出现一些类似、而且很常使用的SQL叙述：
[](http://box.kancloud.cn/2015-09-15_55f7ef13444e1.png)
以上列的查询叙述来说，虽然它并不是很复杂，只是一个加入排序设定的一般查询而已。可是如果常常会执行这样的查询，你每次都要输入这个查询叙述再执行它；就算你把这个查询叙述储存为文字档保存起来，需要的时候再开启档案使用，这样做的话是比较方便一些，不过还是很麻烦，而且比较没有灵活性。
如果在数据库的应用中，出现这种很常执行的查询叙述时，你可以在MySQL数据库中建立一种“View”元件，View元件用来保存一段你指定的查询叙述：
[](http://box.kancloud.cn/2015-09-15_55f7ef142092a.png)
建立好需要的View元件以后，除了有一些限制外，它使用起来就像是一个表格，所以当你需要执行这样的查询时，可以在查询叙述的“FROM”子句指定一个View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef14c7b61.png)
也有很多人称“View”元件是一种“虚拟表格”，因为它不是一个真正储存纪录资料的表格，可是它又跟表格的用法类似。所以如果有需要的话，你也可以使用View元件回传的纪录资料，执行统计、分组与其它需要的处理：
[](http://box.kancloud.cn/2015-09-15_55f7ef154580f.png)
View元件就像是一个表格，大部份使用表格可以完成的工作，也可以套用在View元件。所以把View元件和表格一起放在“FROM”子句中，执行需要的结合查询也是可以的：
[](http://box.kancloud.cn/2015-09-15_55f7ef1fc0f67.png)
# 2 建立需要的View
不论是为了查询或维护，如果你很常需要使用到同一个查询叙述，你就可以考虑建立一个View元件把这个查询叙述储存起来。下列是建立View元件基本的语法：
[](http://box.kancloud.cn/2015-09-15_55f7ef20450b6.png)
如果你很常执行查询“每个地区GNP最高的国家”资料，这样的需求可以使用子查询来完成，为了不想要每次重复输入这个查询叙述，你可以建立一个名称“CountryMaxGNP”的View元件，这样以后要执行这个查询的时候就方便多了：
[](http://box.kancloud.cn/2015-09-15_55f7ef20b3bd5.png)
在上列建立View元件的范例中，只有“Name”与“GNP”两个字段，如果想要在已经建立好的“CountryMaxGNP”的View元件中，再加入新的“Code”字段的话：
[](http://box.kancloud.cn/2015-09-15_55f7ef213f73e.png)
如果需要修改一个已经建立好的View元件，你就要加入“OR REPLACE”的设定，这样才不会出现错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7ef219dade.png)
如果想要查询一个View元件中会传回哪些字段的资料，可以使用“DESCRIBE”或是比较简短的“DESC”指令：
[](http://box.kancloud.cn/2015-09-15_55f7ef226020d.png)
下列是MySQL关于View元件的规定与限制：
* 在同一个数据库中，View的名称不可以重复，也不可以跟表格名称一样
* View不可以跟Triggers建立联结
储存在View中的查询叙述也有下列的规定：
* 查询叙述中只能使用到已存在的表格或View
* “FROM”子句中不可以使用子查询
* 不可以使用“TEMPORARY”表格
* 不可以使用自行定义的变量、Procedure与Prepared statement参数
注：“TEMPORARY”表格在“表格与索引、建立表格、建立暂存表格”中讨论。“Triggers”、定义变量、“Procedure”与“Prepared statement”在后面都会有章节详细的讨论。
结合查询在关联式数据库中几乎是必要的一种查询，以下列查询“国家与城市人口比例”的需求来说，就需要从“country”与“city”表格中查询必要的字段资料：
[](http://box.kancloud.cn/2015-09-15_55f7ef23170df.png)
如果会经常执行这个结合查询的话，你应该会很希望把它储存为View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef236f346.png)
你不会在一个表格中，为不同的两个字段取一样的名称；在使用查询叙述提供View元件的字段时，也要注意名称重复的问题，虽然在单纯的结合查询回传的资料中，有一样的字段名称并不会造成错误。要解决这个错误有两种方式，第一种是在查询叙述的“SELECT”子句中，自己为名称重复的字段取不同的字段别名：
[](http://box.kancloud.cn/2015-09-15_55f7ef24d83b2.png)
另外一种方式可以在建立View元件的时候，另外指定View元件的字段名称：
[](http://box.kancloud.cn/2015-09-15_55f7ef256fd40.png)
这样的作法不用修改查询叙述，依照查询叙述回传的字段顺序，另外指定View元件使用的字段名称：
[](http://box.kancloud.cn/2015-09-15_55f7ef26c8fc7.png)
# 3 修改View
使用“ALTER VIEW”叙述，可以让你修改一个已经建立好的View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef275bc06.png)
下列的范例使用“ALTER VIEW”叙述修改已经存在的“CountryMaxGNP”View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef279543d.png)
上列范例执行的工作也可以使用“CREATE OR REPLACE VIEW”叙述来完成：
[](http://box.kancloud.cn/2015-09-15_55f7ef323fd0c.png)
如果以修改View元件的工作来说，使用“ALTER VIEW”或“CREATE OR REPLACE VIEW”叙述的效果是完全一样的。唯一的差异是要修改View元件如果不存在的话，“CREATE OR REPLACE VIEW”叙述会直接建立新的View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef3369f6e.png)
# 4 删除View
下列的语法可以删除一个不需要的View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef33f157e.png)
如果“DROP VIEW”叙述指定的View元件不存在的话，执行叙述以后会产生错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7ef3455dfa.png)
你可以在“DROP VIEW”叙述加入“IF EXISTS”，这样就可以防止产生View元件不存在的错误讯息：
[](http://box.kancloud.cn/2015-09-15_55f7ef349eae7.png)
# 5 资料维护与View
View元件除了提供比较方便的查询方式外，你也可以使用View元件来执行资料维护的工作。与View元件应用在查询资料时提供的方便性一样，不使用表格元件，而使用View元件来执行新增、修改或删除的工作，也可以增加资料维护的方便性。
要使用View元件来执行新增、修改或删除的工作，View元件所包含的查询叙述必须符合下列的规则：
* 不可以包含计算或函式的字段
* 只允许一对一的结合查询
* View元件的“ALGORITHM”不可以设定为“TEMPTABLE”
如果符合上列规定的View元件，就会称为“可修改的View元件、updattable views”。只有可修改的View元件，可以使用在“INSERT”、“UPDATE”或“UPDATE”叙述中执行资料维护的工作。
注：View元件的“ALGORITHM”设定在这一章后面的“View的算法”中讨论。
## 5.1 使用View元件执行资料维护
下列是一个可以执行资料维户的View元件，它的字段没有包含计算或函式，也没有使用结合查询：
[](http://box.kancloud.cn/2015-09-15_55f7ef3511e1f.png)
如果要修改员工编号“7844”的佣金为600的话，你除了可以在“UPDATE”叙述中指定修改的表格名称为“emp”外，也可以在“UPDATE”叙述中指定View元件“EmpDept30View”：
[](http://box.kancloud.cn/2015-09-15_55f7ef3b22e3e.png)
在执行上列的“UPDATE”叙述以后，不论是查询View元件或表格，都可以确定资料已经修改了：
[](http://box.kancloud.cn/2015-09-15_55f7ef3c12633.png)
使用“INSERT”叙述新增纪录时，也可以指定View元件“EmpDept30View”：
[](http://box.kancloud.cn/2015-09-15_55f7ef3d1d0dc.png)
在执行上列的“INSERT”叙述以后，查询View元件所得到的结果并没有刚才新增的员工资料，查询表格时才可以确定资料已经新增，这是因为新增纪录的部门编号字段资料为“NULL”的关系：
[](http://box.kancloud.cn/2015-09-15_55f7ef3d7fe29.png)
与“INSERT”和“UPDATE”叙述一样，“DELETE”叙述也可以指定View元件的纪录资料：
[](http://box.kancloud.cn/2015-09-15_55f7ef3ddd807.png)
不过执行上列的删除叙述后，千万不要以为你已经删除员工编号“9001”的员工纪录了：
[](http://box.kancloud.cn/2015-09-15_55f7ef3e3772f.png)
## 5.2 使用“WITH CHECK OPTION”
你可以使用View元件来执行资料维护的工作，可是在执行新增或修改的时候，又可能会造成一些有问题的资料。如果你不希望产生这类的问题，你可以为View元件加入“WITH CHECK OPTION”的设定：
[](http://box.kancloud.cn/2015-09-15_55f7ef488897b.png)
加入“WITH CHECK OPTION”设定的View元件，在执行资料维护工作时，会先执行检查的工作，规则是一定要符合“View元件中WHERE设定的条件”：
[](http://box.kancloud.cn/2015-09-15_55f7ef4916b08.png)
因为上列范例所新增的纪录资料，“deptno”字段会储存“NULL”值，这样就违反View元件中“WHERE deptno = 30”的条件设定了，所以在执行以后会产生错误讯息。下列的修改叙述就可以正确的执行：
[](http://box.kancloud.cn/2015-09-15_55f7ef4e666dd.png)
View元件中的“WITH CHECK OPTION”设定，还有额外的“CASCADE”和“LOCAL”两个控制检查范围的设定：
[](http://box.kancloud.cn/2015-09-15_55f7ef4ea1f07.png)
会有“CASCADE”和“LOCAL”这两个设定的原因，是因为View元件的资料来源可以一个表格，也可以是一个View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef4f1df77.png)
查询“EmpDept20View”后，传回的纪录资料包含“deptno = 20”条件，与设定在“EmpSalaryView”的“salary >= 1500”条件：
[](http://box.kancloud.cn/2015-09-15_55f7ef4fb798e.png)
检查范围设定为“LOCAL”的View元件，在执行资料维护的时候，只会检查是否符合自己的条件设定：
[](http://box.kancloud.cn/2015-09-15_55f7ef501e60c.png)
如果执行资料维护的叙述违反“EmpSalaryView”的条件设定，还是可以正确的执行：
[](http://box.kancloud.cn/2015-09-15_55f7ef506742c.png)
如果你希望所有的View元件在执行资料维护的时候，都不可以出现这类的问题，就应该把View元件的检查范围设定为“CASCADE”：
[](http://box.kancloud.cn/2015-09-15_55f7ef55c1bbb.png)
检查范围设定为“CASCADE”的View元件，在执行资料维护的时候，就不能违反所有VIew元件的条件设定：
[](http://box.kancloud.cn/2015-09-15_55f7ef56699f7.png)
# 6 View的算法
View元件可以提供更方便的资料查询与维护方式，在你建立View元件的时候，除了指定的查询叙述要符合规定，还可以指定数据库执行View元件时所使用的“算法、algorithm”：
[](http://box.kancloud.cn/2015-09-15_55f7ef56e6d76.png)
一般来说，你不需要特别指定View元件使用的算法。如果在建立View元件的时候，没有指定使用的算法为“MERGE”或“TEMPTABLE”，MySQL会设定为“UNDEFINED”，这个设定表示MySQL会依照View元件中包含的叙述，自动选择一个适合的算法，可能是“MERGE”或“TEMPTABLE”。
下列是一个算法设定为“MERGE”的View元件，在MySQL数据库中的运作情形：
[](http://box.kancloud.cn/2015-09-15_55f7ef57c2874.png)
下列是一个算法设定为“TEMPTABLE”的View元件，在MySQL数据库中的运作情形：
[](http://box.kancloud.cn/2015-09-15_55f7ef586f8cb.png)
并不是所有的View元件都可以指定算法设定为“MERGE”，以下列查询员工统计资讯的叙述来说：
[](http://box.kancloud.cn/2015-09-15_55f7ef5941dae.png)
如果执行下列建立View元件的叙述，就会产生警告的讯息：
[](http://box.kancloud.cn/2015-09-15_55f7ef59a520c.png)
如果View元件包含的查询叙述有下列的情况，MySQL都会自动把算法设定为“UNDEFINED”：
* 群组函式：SUM()、MIN()、MAX()、COUNT()
* DISTINCT
* GROUP BY
* HAVING
* UNION或UNION ALL
* “SELECT”子句中包含一个明确的值，而不是表格的字段
# 7 View的维护与资讯
## 7.1 检验View的正确性
在你建立一个View元件的时候，MySQL会检查View元件包含的查询叙述是否正确，如果没有问题的话，才会储存View元件的设定。不过以下列的范例来说：
[](http://box.kancloud.cn/2015-09-15_55f7ef5eea03e.png)
如果不小心删除“EmpSalaryView”这个View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef5fb6973.png)
执行查询“EmpDept20View”的时候，就会产生警告讯息了：
[](http://box.kancloud.cn/2015-09-15_55f7ef61161fb.png)
这样的问题也可以经由使用检查表格或View元件的叙述发现：
[](http://box.kancloud.cn/2015-09-15_55f7ef618d305.png)
执行检查“EmpDept20View”的叙述可以发现这是一个有问题的View元件：
[](http://box.kancloud.cn/2015-09-15_55f7ef61cbb32.png)
## 7.2 取得View的相关资讯
MySQL数据库在启动以后，会有一个很特别的数据库，名称是“information_schema”，这个数据库通常会称为“系统资讯数据库”。这个数据库中有一个表格叫作“VIEWS”，它储存所有MySQL数据库中View元件的相关资讯，“VIEWS”表格有下列主要的字段：
| 字段名称 | 型态 | 说明 |
| --- | --- | --- |
| TABLE_SCHEMA | varchar(64) | 数据库名称 |
| TABLE_NAME | varchar(64) | 表格名称 |
| VIEW_DEFINITION | longtext | 算法定义与储存的查询叙述 |
| CHECK_OPTION | varchar(8) | 检查范围设定 |
| IS_UPDATABLE | varchar(3) | 是否可以执行资料维护 |
执行下列的叙述就可以查询数据库中的View元件资讯：
[](http://box.kancloud.cn/2015-09-15_55f7ef674bcf8.png)
';
（10） 子查询
最后更新于：2022-04-01 23:40:02
**目录**
[TOC]
# 1 一个叙述中的查询叙述
子查询(subquery)是一种很常见的应用，不论是查询、新增、修改或删除都有可能出现。子查询是一个放在左右刮号中的“SELECT”叙述，而这个查询叙述会放在另一个SQL叙述中。在执行一些工作的时候，使用子查询可以简化SQL叙述。以查询“人口比美国多的国家”来说，你要先执行下列查询美国人口数量的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eaffa8a17.png)
知道美国人口数量以后，再执行下列的叙述就可以传回人口比美国多的国家了：
[](http://box.kancloud.cn/2015-09-15_55f7eb003beda.png)
以这样的查询来说，你要执行两次查询叙述来完成这个工作。不过遇到类似这样的需求时：
[](http://box.kancloud.cn/2015-09-15_55f7eb008ee9b.png)
你就可以考虑把它们写成一个叙述就可以了：
[](http://box.kancloud.cn/2015-09-15_55f7eb00f25cb.png)
上列的范例是一种很常见的子查询应用，使用子查询的好处是不用执行多次查询就可以完成工作，这样可以简化查询的工作；对于处理资料的应用程式来说，也可以节省一些程式码。
# 2 WHERE、HAVING子句与子查询
子查询大部份使用在提供判断条件用的资料，在“WHERE”和“HAVING”子句中，都可能出现子查询：
[](http://box.kancloud.cn/2015-09-15_55f7eb01b3c00.png)
## 2.1 比较运算子
在“WHERE”和“HAVING”子句中，你会使用许多不同的运算子来判断条件是否符合。这些运算子中的比较运算子都可以搭配子查询来完成你的需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb020d9ba.png)
使用比较运算子的时候，你要提供一个资料让运算子判断条件是否符合。在使用子查询提供判断用的资料时，要特别注意子查询回传的资料是否符合规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb07681f5.png)
以下列“查询GNP最大的国家”需求来说，子查询传回的数字是“country”表格中“GNP”字段的最大值，这个数字就给外层查询当作“WHERE”子句中的条件设定：
[](http://box.kancloud.cn/2015-09-15_55f7eb07c5b9d.png)
使用在比较运算子的子查询，在“SELECT”子句中不可以指定超过一个字段的回传资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb0843997.png)
子查询也不可以回传超过一笔以上的纪录：
[](http://box.kancloud.cn/2015-09-15_55f7eb087d11b.png)
## 2.2 “IN”运算子
除了一般的比较运算子外，你可能很常使用“IN”运算子来执行多个资料的比较，你也可以使用子查询提供“IN”运算子判断的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb08c0e34.png)
如果你想要查询“城市人口超过九百万的国家”，“IN”运算子就会出现在这类的需求中：
[](http://box.kancloud.cn/2015-09-15_55f7eb0929e97.png)
这类的需求，也可以改成使用子查询来完成：
[](http://box.kancloud.cn/2015-09-15_55f7eb0eae3be.png)
以上列的范例来说，如果你用错运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb0f2773a.png)
“IN”运算子可以视需要搭配“NOT”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb0f8a2ba.png)
## 2.3 其它运算子
比较运算子与子查询搭配使用时，另外还提供“ALL”、“ANY”与“SOME”三个运算子，其中“ANY”和“SOME”运算子的效果是一样的，所以只需要讨论“ALL”与“ANY”这两个运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb105b94b.png)
比较运算子与“ALL”与“ANY”搭配使用时，可以完成比较特殊的查询需求，下列是两个用来测试的表格：
[](http://box.kancloud.cn/2015-09-15_55f7eb10ce012.png)
下列是比较运算子与“ALL”搭配使用的范例：
[](http://box.kancloud.cn/2015-09-15_55f7eb11570e8.png)
“ALL”运算子从字面上来看，是“全部”的意思，所以你也可以这样来看“ALL”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb11d5887.png)
“ANY”运算子从字面上来看，是“任何一个”的意思，所以你也可以这样来看“ANY”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb122aa6c.png)
注：在MySQL中，“ANY”与“SOME”运算子的效果是一样的。
在你了解“ALL”运算子的效果以后，如果在解决你的需求时，使用了“ ALL”这样的运算子，它的效果其实跟“NOT IN”是一样的：
[](http://box.kancloud.cn/2015-09-15_55f7eb12b144a.png)
另外“= ANY”运算子的效果跟“IN”是一样的：
[](http://box.kancloud.cn/2015-09-15_55f7eb1335111.png)
## 2.4 多字段子查询
在条件设定的时候，通常会也遇到比较复杂一点的设定，例如下列这个查询“在亚洲而且政府型式为Republic的国家”叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb13732f9.png)
上列的条件设定，有另外一种比较简单的设定方式：
[](http://box.kancloud.cn/2015-09-15_55f7eb1406c1b.png)
如果想要查询“跟Iraq国家同一个地区，而且跟Iraq国家的政府型式一样的国家”，因为判断条件都要经由查询才可以得到，所以你可能会写出这样的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb1474a92.png)
遇到类似这样的需求时，你也可以套用这种比较简单的设定方式：
[](http://box.kancloud.cn/2015-09-15_55f7eb14a89fe.png)
如果想要查询“每一洲GNP最高的国家”，你可以使用下列的叙述先查询每一洲最高的GNP：
[](http://box.kancloud.cn/2015-09-15_55f7eb1556271.png)
跟单一资料的判断一样，子查询传回多比纪录时就要使用“IN”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb1594241.png)
# 3 SELECT子句与子查询
如果需要的话，子查询也可以使用在“SELECT”子句中。以查询“国家Japan的GNP”的需求来说，下列的范例使用的是你已经熟悉的查询叙述来完成这个需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb163696f.png)
这类的需求也可以直接在“SELECT”子句中使用子查询传回你需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb1baadc7.png)
下列的叙述可以查询“India国家占全世界人口的比例”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1bda0f8.png)
# 4 FROM子句与子查询
子查询可以使用在“WHERE”与“HAVGIN”子句中用来设定条件，还有使用在“SELECT”子句中，用来传回需要的资料；除了这两种用法外，子查询还可以使用在“FROM”子句。你通常会在在查询叙述的“FROM”子句中，指定需要的表格名称，有需要的话，你也可以使用子查询，这个子查询回传的结果会被当成一个“表格”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1e2f02a.png)
下列的范例可以查询“亚洲GNP前十名国家”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1e72e42.png)
注：要完成上列的需求，并不需要在“FROM”子句中使用子查询，只要使用一般的查询叙述就可以了。
如果以查询“国家的官方语言与人口比例”的需求来说，你可以使用下列的查询叙述来完成这个工作：
[](http://box.kancloud.cn/2015-09-15_55f7eb23c84a6.png)
注：要完成上列的需求，并不需要在“FROM”子句中使用子查询，使用结合查询也可以得到一样的结果。
# 5 资料维护与子查询
在使用“INSERT”、“UPDATE”与“DELETE”叙述执行新增、修改与删除资料时，也可以依照需要使用子查询来简化资料维护的叙述。
## 5.1 新增
一般来说，使用“INSERT”叙述执行新增纪录的工作时，通常是直接指定新增纪录的资料；如果你要新增的资料，可以执行一个查询来取得的话，就可以搭配子查询来简化新增纪录的工作：
[](http://box.kancloud.cn/2015-09-15_55f7eb29cf80b.png)
以下列这个储存国家资料的表格(world.mycountry)来说：
[](http://box.kancloud.cn/2015-09-15_55f7eb2a62fe3.png)
如果你想要新增亚洲国家的资料到“mycountry”表格中，你可以使用子查询传回新增纪录需要的资料给“INSERT”叙述使用：
[](http://box.kancloud.cn/2015-09-15_55f7eb2ae4a0c.png)
使用子查询提供“INSERT”叙述需要的资料，要特别注意子查询回传的字段资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb30640a4.png)
注：搭配“ON DUPLICATE KEY UPDATE”的效果在“资料维护、新增、索引值与ON DUPLICATE KEY UPDATE”中讨论。
MySQL另外一种新增纪录的“REPLACE”叙述，也可以使用子查询提供需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42749af.png)
## 5.2 修改
使用“UPDATE”叙述执行修改资料时，如果没有使用“WHERE”子句指定修改的条件，“UPDATE”叙述会修改表格中所有的纪录；所以执行修改纪录资料的时候，通常会使用“WHERE”子句指定修改的条件。在“UPDATE”叙述的“WHERE”子句中，也可以使用子查询提供判断条件的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要执行“SALES部门的员工加薪百分之五”，因为你需要先知道“SALES”部门的编号，所以你可以使用子查询传回“SALES”部门的编号，给“UPDATE”叙述中的“WHERE”子句设定部门编号的条件：
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“UPDATE”叙述中的子查询有一个特别的规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb437f635.png)
## 5.3 删除
使用“DELETE”叙述执行删除纪录时，如果没有使用“WHERE”子句指定删除的条件，“DELETE”叙述会删除表格中所有的纪录；所以执行删除纪录的时候，通常会使用“WHERE”子句指定删除的条件。在“DELETE”叙述的“WHERE”子句中，也可以使用子查询提供判断条件的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要执行“删除SALES部门员工”，因为你需要先知道“SALES”部门的编号，所以你可以使用子查询传回“SALES”部门的编号，给“DELETE”叙述中的“WHERE”子句设定部门编号的条件：
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“DELETE”叙述中出现的子查询有一个特别的规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb4e7dbfd.png)
# 6 关联子查询
在查询或维护的查询中，都有可能会使用子查询来提供执行叙述所需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb4ec20a9.png)
在使用子查询的的时候，通常不会跟外层查询有直接的关系，也就是子查询不会使用外层查询的资料；不过遇到一些比较特殊的需求时，在“WHERE”或“HAVING”子句中的子查询，也需要使用外层查询的资料来执行判断的工作，这样的叙述称为“关联子查询、correlated subqueries”：
[](http://box.kancloud.cn/2015-09-15_55f7eb54281d1.png)
在“WHERE”或“HAVING”子句中用来设定条件的子查询，可以依照需求使用像“IN”、“ANY”这些运算子来判断条件是否符合。除了上列以经讨论的比较运算子外，还有一个“EXISTS”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb5a199f6.png)
“EXISTS”运算子判断条件是否成立的依据比较不一样，如果子查询有任何纪录资料回传，条件就算成立：
[](http://box.kancloud.cn/2015-09-15_55f7eb5a77c2b.png)
“EXISTS”运算子通常会在使用关联子查询中：
[](http://box.kancloud.cn/2015-09-15_55f7eb5aabb74.png)
“EXISTS”与“NOT”一起使用时，就可以完成下列的查询需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb5b43fba.png)
# 7 子查询与结合查询
子查询的应用通常可以简化许多工作，而一些子查询完成的工作，也可以改用其它的作法来完成。例如下列查询“所有国家首都名称”的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb60a9d82.png)
把上列的需求改用结合查询来完成的话，其实看起来会更简单一些：
[](http://box.kancloud.cn/2015-09-15_55f7eb76002c6.png)
如果需求换成查询“不是首都的城市名称”，可以使用下列搭配子查询的作法：
[](http://box.kancloud.cn/2015-09-15_55f7eb767cbf0.png)
上列的需求要改成使用结合查询来完成的话，会比较不一样。所以要先了解使用“LEFT JOIN”结合查询的效果：
[](http://box.kancloud.cn/2015-09-15_55f7eb7793072.png)
根据“LEFT JOIN”结合查询产生的效果，为这个结合查询设定适当的条件，就可以完成查询“不是首都的城市名称”：
[](http://box.kancloud.cn/2015-09-15_55f7eb786f8ff.png)
';
（9） 表格和索引
最后更新于：2022-04-01 23:40:00
**目录**
[TOC]
# 1 建立表格
在建立好数据库以后，就可以根据储存资料的需求，使用SQL叙述建立所有需要的表格(table)。建立表格的设定非常多，以建立“world.city”表格来说，它的叙述会像这样：
[](http://box.kancloud.cn/2015-09-15_55f7e96e360eb.png)
根据不同的需求，建立表格的语法有好几种，下列是建立表格基本的语法：
[](http://box.kancloud.cn/2015-09-15_55f7e96f0dfb2.png)
MySQL规定一个表格中至少要有一个字段，在设定表格中的字段时，至少要明确的决定字段的名称与型态，其它的字段设定都是选择性的，如果有一个以上字段，要使用逗号隔开：
[](http://box.kancloud.cn/2015-09-15_55f7e96f50f47.png)
使用需要的资料型态，就可以建立一个可以储存亲友通讯录的表格：
[](http://box.kancloud.cn/2015-09-15_55f7e975143ff.png)
建立表格的时候可以使用“IF NOT EXISTS”选项，预防发生表格已存在的错误：
[](http://box.kancloud.cn/2015-09-15_55f7e97619ce9.png)
## 1.1 表格属性
建立表格的时候也可以为表格加入需要的表格属性(table attributes)设定，这里会先讨论关于储存引擎、字符集和collation的属性设定。如果你在建立表格的时侯，没有指定这些属性，MySQL会使用服务器默认的储存引擎作为表格的储存引擎，字符集与collation会使用数据库默认的设定。
你可以针对表格的需求，设定它使用的储存引擎、字符集与collation：
[](http://box.kancloud.cn/2015-09-15_55f7e97696d08.png)
下列的叙述在建立“addressbook”表格的时候，使用“ENGINE”、“CHARCTER SET”和“COLLATE”设定表格自己使用的储存引擎、字符集与collation：
[](http://box.kancloud.cn/2015-09-15_55f7e976dfaf5.png)
注：根据语法的说明，“CHARCTER SET”也可以使用比较简短的“CHARSET”；另外在设定时都可以省略“=”。
MySQL数据库服务器支援许多不同应用的储存引擎，你可以使用“SHOW ENGINES”查询：
[](http://box.kancloud.cn/2015-09-15_55f7e97769f6a.png)
在建立表格的时候，如果没有使用“ENGINE”设定储存引擎，那就会使用MySQL数据库服务器默认的储存引擎。你可以使用下列的方式修改MySQL数据库服务器默认的储存引擎设定：
* 修改设定档：MySQL数据库服务器在启动时会读取一个名称为“my.ini”的设定档，档案中有许多启动数据库服务器时需要的资讯。其中就包含默认的储存引擎设定，你可以修改这个设定后再重新启动数据库服务器，让新的设定生效：
[](http://box.kancloud.cn/2015-09-15_55f7e9782a5ca.png)
* 设定储存引擎：你也可以使用“SET”叙述设定默认的储存引擎：
[](http://box.kancloud.cn/2015-09-15_55f7e97854eab.png)
在建立表格时指定字符集与collation会有一些不同的组合。如果只有指定字符集，MySQL会使用你指定字符集的默认collation：
[](http://box.kancloud.cn/2015-09-15_55f7e98d6cb22.png)
如果只有使用“COLLATE”指定collation，MySQL会使用你指定collation所属的字符集：
[](http://box.kancloud.cn/2015-09-15_55f7e997cd707.png)
注：建立表格的时候，不管你有没有指定，表格都会有字符集与collation的设定。在这个表格中的“非二进位制、non-binary”字串型态字段，还有“ENUM”与“SET”型态字段，都会使用表格默认的字符集与collation。
## 1.2 字串字段属性
如果一个字段的型态是字串的话，你还可以依照需求加入字串型态的字段属性(column attributes)。“非二进位制、non-binary”字串可以额外设定字符集与collation：
[](http://box.kancloud.cn/2015-09-15_55f7e99865dcb.png)
每一个表格都会有一个默认的字符集与collation设定，如果没有指定字段的字符集与collation，就会使用默认的设定：
[](http://box.kancloud.cn/2015-09-15_55f7e998a970e.png)
## 1.3 数值字段属性
数值型态字段专用的属性设定有“UNSIGNED”、“ZEROFILL”与“AUTO_INCREMENT”：
[](http://box.kancloud.cn/2015-09-15_55f7e998f236f.png)
注：数值型态字段设定为“UNSIGNED”与“ZEROFILL”的效果在“第八章、表格与索引、建立表格、数值字段属性”中已经讨论过；而“AUTO_INCREMENT”的设定与索引有关，所以在这一章后面索引的部份一起讨论。
## 1.4 通用字段属性
除了字串与数值两种字段专用的字段属性设定外，还有许多可以用在所有型态的字段属性：
[](http://box.kancloud.cn/2015-09-15_55f7e999d8a69.png)
“NOT NULL”字段属性可以用来禁止某个字段储存“NULL”值，一般来说，“NULL”值用来表示一个字段的资料是“不确定”、“未知”或“没有”。不过有一些字段并不能出现“NULL”值，不然就会成为一笔很奇怪的纪录了：
[](http://box.kancloud.cn/2015-09-15_55f7e99b09fa4.png)
使用“NULL”或“NOT NULL”设定字段属性后，在查询表格字段资讯时，是在“Null”字段用“YES”或“NO”来表示：
[](http://box.kancloud.cn/2015-09-15_55f7e9a5620f5.png)
如果一个表格中，有设定为“NOT NULL”的字段，那就要注意你在新增或修改纪录时指定的资料，不能够违反这些规则：
[](http://box.kancloud.cn/2015-09-15_55f7e9a7717fd.png)
使用“DEFAULT”关键字可以设定字段的默认值，你可以自己指定任何想要的默认值，在新增或修改资料的时候都有可能会使用到字段的默认值。要特别注意的是MySQL限制你的默认值只能是“一个明确的值”，也就是默认值的设定不可以使用任何函式或运算式。
如果你没有为字段使用“DEFAULT”关键字设定默认值，而且也没有设定为“NOT NULL”，MySQL会自动为你加入默认值的设定：
[](http://box.kancloud.cn/2015-09-15_55f7e9a977758.png)
以通讯录表格来说，如果纪录的地址大部份都是“Taipei”的话，你可以为“address”字段设定一个默认值：
[](http://box.kancloud.cn/2015-09-15_55f7e9a9d5111.png)
使用“DEFAULT”关键字加入默认值的设定以后，就可以在新增或修改资料的时候使用：
默认值的设定要注意下列的规则：
* “BLOB”与“TEXT”字段型态不可以使用[DEFAULT]关键字指定默认值，其它的字段型态都可以
* 不能与其它的字段设定造成冲突。例如一个设定为“NOT NULL”的字段，却使用“DEFAULT NULL”设定默认值为“NULL”
* 指定的默认值要符合字段型态。例如“DATE”型态字段使用“DEFAULT ‘Hello!’”指定默认值
注：“UNIQUE KEY”与“PRIMARY KEY”在这一章后面索引的部份一起讨论。
## 1.5 TIMESTAMP字段型态与默认值
“TIMESTAMP”字段是日期时间资料的一种，它除了具有“时区、timezone”的特性外，也可以搭配“DEFAULT”和“ON UPDATE”来完成一些比较特殊的需求：
[](http://box.kancloud.cn/2015-09-15_55f7e9aaa6eea.png)
在表格中使用“TIMESTAMP”型态的字段时，如果你没有设定它们的字段属性，MySQL会自动帮你在第一个“TIMESTAMP”字段加入“NOT NULL”、“DEFAULT”和“ON UPDATE”三个字段属性的设定。
* “NOT NULL”不允许你储存“NULL”值
* “DEFAULT CURRENT_TIMESTAMP”设定默认值为目前的日期时间。在所有字段型态中，只有“TIMESTAMP”可以使用“CURRENT_TIMESTAMP”指定默认值；其它的字段型态，在指定默认值只能是“一个明确的值”
* “ON UPDATE”可以指定在修改纪录的时候，MySQL自动帮你填入的资料
其它没有设定字段属性的“TIMESTAMP”字段，MySQL会帮你加入“NOT NULL”与“DEFAULT”两个字段属性。
“DEFAULT CURRENT_TIMESTAMP”字段属性的效果，在你新增纪录的时候就可以看得出来了：
[](http://box.kancloud.cn/2015-09-15_55f7e9ab1fe9f.png)
而“ON UPDATE CURRENT_TIMESTAMP”字段属性，会在修改纪录的时候产生效果：
[](http://box.kancloud.cn/2015-09-15_55f7e9abeae78.png)
“TIMESTAMP”字段型态很适合用来记录资料新增或修改的日期与时间。可是如果在同一笔纪录中，要使用一个字段记录新增资料的日期与时间，而使用另一个字段记录修改资料的日期与时间。为了应付这样的需求，你应该会使用下列的字段定义：
[](http://box.kancloud.cn/2015-09-15_55f7e9acd19e8.png)
在一个表格中，MySQL限制“CURRENT_TIMESTAMP”只能在一个字段出现，所以当有这样的需求出现时，你必须使用MySQL提供给你的特殊设定方式来解决：
[](http://box.kancloud.cn/2015-09-15_55f7e9ae46448.png)
建立好这样的表格以后，看起来虽然怪怪的，不过当你指定“created”字段的值为“NULL”的时候，MySQL会自动为你填入目前的日期与时间：
[](http://box.kancloud.cn/2015-09-15_55f7e9aea8acf.png)
后续在修改资料的时候，就只会在“updated”字段填入目前的日期与时间：
[](http://box.kancloud.cn/2015-09-15_55f7e9af56271.png)
## 1.6 使用其它表格建立一个新表格
在数据库中建立需要的表格，通常是使用上列讨论的方式，根据自己的需求，建立一个新的表格来储存需要保存的资料；在一些比较特别的情况，你可能会使用一个现有的表格来建立新的表格，这样的需求可以使用下列的语法：
[](http://box.kancloud.cn/2015-09-15_55f7e9afe74b6.png)
以“world”数据库中的“city”来说，下列的查询叙述可以传回台湾的城市与人口数：
[](http://box.kancloud.cn/2015-09-15_55f7e9b07a901.png)
如果你想要建立一个新表格，这个表格中的资料就是上列查询的结果，就可以使用这种建立表格的语法：
[](http://box.kancloud.cn/2015-09-15_55f7e9b103012.png)
使用这种语法建立的新表格，可以省略字段定义的工作，新表格会使用原有表格的字段名称与定义，而且在查询叙述中传回的资料，会直接新增到新建立的表格中：
[](http://box.kancloud.cn/2015-09-15_55f7e9b2f2a9f.png)
你也可以在建立新表格的时候，使用字段定义来设定新表格的字段型态与其它属性：
[](http://box.kancloud.cn/2015-09-15_55f7e9bda4e7a.png)
如果需要的话，也可以加入查询叙述中没有的字段：
[](http://box.kancloud.cn/2015-09-15_55f7e9be389b4.png)
使用这种语法建立表格时有下列几个重点：
* MySQL使用查询结果的字段名称与型态来建立新的表格
* 如果没有指定储存引擎、字符集或collation的话，建立的新表格使用数据库默认的储存引擎、字符集与collation
* 查询表格中，字段的索引与“AUTO_INCREMENT”设定都会被忽略
如果只需要借用一个已经存在的表格字段定义，可是并不需要纪录资料的话，你可以使用下列的语法来建立新表格：
[](http://box.kancloud.cn/2015-09-15_55f7e9beb1a3b.png)
使用这种语法建立的新表格，并不会新增纪录到新表格中，可是包含索引与“AUTO_INCREMENT”设定都会套用在新表格，除了下列两个例外：
* 使用“MyISAM”储存引擎时，你可以在建立表格的时候使用“DATA DIRECTORY”与“INDEX DIRECTORY”指定资料与索引档案的资料夹位置；建立的新表格会忽略这些设定，而使用数据库默认的资料夹
* 字段的“FOREIGN KEY”与表格的“REFERENCES”属性设定都会被忽略
## 1.7 建立暂存表格
上列讨论的建立表格方式，都可以在建立表格的时候，依照需要加入“TEMPORARY”关键字，指定这个新建立的表格为“用户端暂时存在”的表格：
[](http://box.kancloud.cn/2015-09-15_55f7e9bf3fbbd.png)
“TEMPORARY”表格有下列重点：
* “TEMPORARY”表格是每一个用户端专属的表格，用户端离线后，MySQL就会自动删除这些表格
* 因为“TEMPORARY”表格是用户端专属的表格，其它用户端不能使用，所以不同的用户端，使用同样名称建立“TEMPORARY”表格也没有关系
* “TEMPORARY”表格名称可以跟数据库中的表格名称一样，不过在“TEMPORARY”表格存在的时候，数据库中的表格会被隐藏起来
* 可以使用“ALTER TABLE”修改“TEMPORARY”表格名称，不可以使用“RENAME TABLE”修改“TEMPORARY”表格名称
# 2 修改表格
使用“CREATE TABLE”叙述建立表格以后，如果发现某个字段或设定打错，或是在使用一阵子以后，发觉表格中有一些设定不太对。在这些情况下，你可以使用“ALTER TABLE”叙述来修改一个表格的结构：
[](http://box.kancloud.cn/2015-09-15_55f7e9bf6a030.png)
## 2.1 增加字段
你可以使用下列的修改定义增加一个本来没有的字段：
[](http://box.kancloud.cn/2015-09-15_55f7e9bfb77d8.png)
如果你在增加字段的时候，没有指定新增字段的位置，MySQL会把这个字段放在最后一个：
[](http://box.kancloud.cn/2015-09-15_55f7e9bfebdf3.png)
你可以搭配使用“FIRST”关键字，把新增的字段放在第一个：
[](http://box.kancloud.cn/2015-09-15_55f7e9c069f0a.png)
或是使用“AFTER”关键字，指定新增的字段要放在哪一个字段后面：
[](http://box.kancloud.cn/2015-09-15_55f7e9c126aa3.png)
如果需要增加多个字段的话，也可以使用下列的语法一次把需要新增的字段，全部加到表格中；不过这种语法加入的新字段，都会放在最后面的位置：
[](http://box.kancloud.cn/2015-09-15_55f7e9c1cd399.png)
注：“ALTER TABLE”叙述也可以用来增加索引，在这一章后面索引的部份一起讨论。
## 2.2 修改字段
如果需要修改字段的名称、型态、大小范围或其它字段属性，你可以使用下列两种修改定义来执行修改的工作。“CHANGE”可以修改字段的名称与定义，“MODIFY”只能修改字段的定义，不能修改字段名称：
[](http://box.kancloud.cn/2015-09-15_55f7e9c28a4d6.png)
以下列使用“CHANGE”关键字修改表格的叙述来说，它将“one”字段的名称修改为“changecolumn”，型态从“INT”修改为“BIGINT”，而且把修改后的字段位置放在“two”字段后面：
[](http://box.kancloud.cn/2015-09-15_55f7e9e0eb944.png)
下列使用“MODIFY”关键字修改表格的叙述，它将“two”字段的型态从“INT”修改为“BIGINT”，而且把修改后的字段位置放在“three”字段后面：
[](http://box.kancloud.cn/2015-09-15_55f7e9e189a4a.png)
## 2.3 删除字段
如果要删除一个表格中不需要的字段，可以使用下列的修改定义：
[](http://box.kancloud.cn/2015-09-15_55f7e9ece038d.png)
下列格的叙述会删除“two”字段：
[](http://box.kancloud.cn/2015-09-15_55f7e9f71f1dd.png)
## 2.4 修改表格名称
如果需要修改表格的名称，你可以使用下列两种叙述，包含在“ALTER TABLE”叙述中使用修改表格名称的修改定义；或是使用“RENAME TABLE”叙述：
[](http://box.kancloud.cn/2015-09-15_55f7e9f7af503.png)
下列两个叙述都可以把“mytable”表格名称修改为“mynewtable”：
[](http://box.kancloud.cn/2015-09-15_55f7e9f86275c.png)
# 3 删除表格
你可以使用下列的叙述删除一个不需要的表格：
[](http://box.kancloud.cn/2015-09-15_55f7e9f90fb7d.png)
注：使用“DROP TABLE”叙述执行删除表格的工作时，MySQL并不会再次跟你确认是否真的要删除，而是真的就直接删除了，表格储存的纪录资料当然也不见了。
# 4 索引介绍
数据库与表格是MySQL数据库的基本元件，依照需求建立好的数据库与表格后，就可以使用它们来为你保存资料。一个设计良好的数据库，不论是资料的正确性，还有后续的维护与查询都比较不会发生问题。除了好好规划与建立数据库与表格外，你还可以利用“索引、index”预防你的资料出现问题，尤其是表格储存非常大量的纪录时，建立适当的索引，可以增加查询与维护资料的效率。
以“MyISAM”储存引擎来说，资料表的储存的纪录资料，是储存在电脑中的一个档案：
[](http://box.kancloud.cn/2015-09-15_55f7e9f986e02.png)
当你执行一个像这样的查询叙述时：
[](http://box.kancloud.cn/2015-09-15_55f7e9f9e2355.png)
数据库要找到你需要查询或维护的纪录，如果没有索引帮助的话，就会从头开始一边读取，一边判断是否有符合条件的资料。你可以为表格建立索引来改善这种比较没有效率的方式：
[](http://box.kancloud.cn/2015-09-15_55f7e9faa4155.png)
建立城市名称的索引档以后，同样执行下列的查询叙述，MySQL会自动使用索引来快速找到你需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7ea053b854.png)
注：索引同样可以增加删除或修改的效率。
索引分为主索引键(primary key)、唯一索引(unique index)与非唯一索引(non-unique index)三种。
主索引键的应用很常见，而且一个表格通常会有一个，而且只能有一个。在一个表格中，设定为主索引键的字段值不可以重复，而且不可以储存“NULL”值。因为这样的限制，所以很适合使用在类似编码、代号或身份证字号这类字段。
唯一索引也称为“不可重复索引”，在一个表格中，设定为唯一索引的字段值不可以重复，但是可以储存“NULL”值。这种索引适合用在类似员工资料表格中储存电子邮件帐号的字段，因为员工不一定有电子邮件帐号，所以允许储存“NULL”值，可以每一个员工的电子邮件帐号都不可以重复。
上列两种索引都可以预防储存的资料发生重复的问题，也可以增加查询与维护资料的效率。非唯一索引就只是用来增加查询与维护资料效率的索引。设定为非唯一索引的字段值可以重复，也可以储存“NULL”值。
# 5 建立索引
MySQL提供许多不同的方式让你建立需要的索引。通常在规划一个数据库的时候，会把表格所需要的索引一并规划好，在这样的情况下，你可以把建立索引的定义，加在“CREATE TABLE”叙述中，建立表格的时候就一起把索引建立好；不过也有可能在使用表格一阵子以后，才发觉有建立索引的需求，在这样的情况下，你可以使用“ALTER TABLE”或“CREATE INDEX”建立需要的索引。
## 5.1 在建立表格的时候建立索引
在建立表格的叙述中，你会定义出许多表格所需要的字段，在字段的定义中，除了名称、型态与属性，还可以加入“唯一索引”与“主索引键”的定义：
[](http://box.kancloud.cn/2015-09-15_55f7ea106abb5.png)
以下列这个建立储存联络簿的表格来说，你可以使用这样的语法在“id”字段后面加入“PRIMARY KEY”，指定“id”字段为主索引键，这表示“id”字段的值不可以重复，而且不可以储存“NULL”值；另外在“email”字段加入“UNIQUE KEY”，指定“email”字段为唯一索引，这表示“email”字段的值不可以重复：
[](http://box.kancloud.cn/2015-09-15_55f7ea10dc464.png)
下列是另外一种在“CREATE TABLE”叙述中建立索引的语法：
[](http://box.kancloud.cn/2015-09-15_55f7ea2047a64.png)
同样以建立储存联络簿的表格来说，下列两种建立索引语法的效果是一样的：
[](http://box.kancloud.cn/2015-09-15_55f7ea21091ed.png)
如果你要建立一般索引(可以重复的索引)，或是要建立包含多个字段的索引时，就一定要把建立索引的定义加在所有字段定义后面：
[](http://box.kancloud.cn/2015-09-15_55f7ea21a0879.png)
在建立索引的时候，你可以指定某一个字段为建立索引的字段，不过有时候你只想要为一个字串型态字段的部份资料建立索引，或是指定建立的索引资料，是要依照由小到大，还是由大到小排列。有这样的需求时，你可以依照下列的语法来指定：
[](http://box.kancloud.cn/2015-09-15_55f7ea2286e33.png)
以建立联络簿的表格来说，为地址资料“address”字段建立索引的时候，如果你希望建立地址前五个字符的索引资料，而且依照由大到小的顺序。下列的叙述就可以建立这样的索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea232ed70.png)
注：只有“CHAR”、“VARCHAR”、“BINARY”与“VARBINARY”型态的字段可以指定制作索引的长度。“ASC”或“DESC”可以使用在任何型态的字段。
如果一个表格使用的储存引擎是“MEMORY”的话，建立索引的时候还可以额外指定索引使用的“算法、algorithm”。使用其它储存引擎的表格，MySQL会忽略这个设定。索引使用的算法有“BTREE”与“HASH”两种，你可以使用下列的语法来指定索引使用的算法：
[](http://box.kancloud.cn/2015-09-15_55f7ea28d2db9.png)
默认的“HASH”算法适合用在主索引键和唯一索引，这种算法在搜寻不能重复的资料时，效率会比较好；而“BTREE”算法适合用在可以允许重复资料的一般索引，在搜寻上会比“HASH”有更好的效率。
注：“FULLTEXT”索引只能用在“CHAR”、“VARCHAR”与“TEXT”型态的字段，而且表格使用的储存引擎必须是“MyISAM”。“SPATIAL”索引是“SPATIAL”型态字段专用的，而且表格使用的储存引擎必须是“MyISAM”。这两种索引不会在这里讨论。
## 5.2 在修改表格的时候建立索引
如果你想要为一个已经存在的表格建立索引的话，你可以在修改表格“ALTER TABLE”中建立索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea2eb0db8.png)
以下列的范例来说，在建立联络簿表格时没有建立索引，你可以使用“ALTER TABLE”叙述建立需要的索引，不过一个“ALTER TABLE”叙述只能建立一个索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea354b0f0.png)
## 5.3 使用“CREATE INDEX”建立索引
需要为一个已经存在的表格建立索引，除了使用“ALTER TABLE”叙述建立索引外，还可以使用“CREATE INDEX”叙述建立唯一索引与一般索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea363c177.png)
使用“CREATE INDEX”叙述只能建立唯一索引与一般索引，你还是要使用“ALTER TABLE”叙述建立主索引键：
[](http://box.kancloud.cn/2015-09-15_55f7ea354b0f0.png)
为一个已经存在的表格建立索引时，要特别注意主索引键与唯一索引这两种索引。如果这个表格没有任何纪录资料的话，那就不会有问题；可是如果表格中已经有纪录了，而且你想要建立一个主索引键时，有可能会发生下列的错误：
[](http://box.kancloud.cn/2015-09-15_55f7ea3c14d37.png)
为一个已经存在、而且已经有纪录的表格建立唯一索引时，也有可能会发生下列的错误：
[](http://box.kancloud.cn/2015-09-15_55f7ea3ca1c31.png)
# 6 索引的名称
在“CREATE TABLE”或是“ALTER TABLE”叙述中建立索引的话，你可以为建立的索引取一个名称：
[](http://box.kancloud.cn/2015-09-15_55f7ea3ce4c97.png)
如果你在使用上列的语法建立索引的时候没有指定索引名称，MySQL会帮你取一个，索引的名称就是字段名称，如果是多个字段的索引，就会使用第一个字段当作索引名称。
使用“CREATE INDEX”建立索引的时候，就一定要指定一个索引名称：
[](http://box.kancloud.cn/2015-09-15_55f7ea3da6ea8.png)
注：在一般的操作中，你并不会用到索引名称；不过在删除索引的时候就会用到。
# 7 删除索引
如果一个已经建立好的索引已经不需要了，为了节省储存的空间，你可以使用下列的语法删除索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea3ddb46f.png)
下列的叙述使用修改表格“ALTER TABLE”叙述删除不需要的索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea3e4a71a.png)
你也可以使用下列的“DROP INDEX”叙述删除不需要的索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea43a7a7e.png)
使用“ALTER TABLE”叙述可以一次删除多个索引，“DROP INDEX”叙述一次只能删除一个索引：
[](http://box.kancloud.cn/2015-09-15_55f7ea442b13b.png)
# 8 数值字段型态与AUTO_INCREMENT
在数据库的应用中，很常会遇到为纪录“编流水号”的需求，如果资料表中的每一笔纪录都需要一个递增的数值编号，你可以选择整数型态的字段后，再使用“AUTO_INCREMENT”字段属性：
[](http://box.kancloud.cn/2015-09-15_55f7ea446528c.png)
如果一个公司想要储存员工开会的资料，你可以在建立开会资料表格的时候，为这个表格定义一个储存开会编号的字段，这个字段需要自动递增，而且会为它建立主索引键：
[](http://box.kancloud.cn/2015-09-15_55f7ea44e0c67.png)
建立开会资料表格以后，另外建立一个储存参加会议的员工资料表格：
[](http://box.kancloud.cn/2015-09-15_55f7ea4a75fcf.png)
设定为“AUTO_INCREMENT”的整数字段，在新增资料的时候可以不用指定数值，MySQL会为你自动编制一个流水号并储存在纪录中；而接着要新增参加这次开会的员工资料到“participate”表格时，你需要用到MySQL刚才会为你在“meeting”表格中自动编制的流水号，这样的需求可以使用“LAST_INSERT_ID()”函式来取得：
[](http://box.kancloud.cn/2015-09-15_55f7ea4bd83ed.png)
新增这些开会与参加会议的员工资料后，就可以使用结合查询来查询开会资料了：
[](http://box.kancloud.cn/2015-09-15_55f7ea4c90188.png)
在新增资料时，要让MySQL为你自动编制一个流水号，并储存到纪录中的方式有下列几种：
[](http://box.kancloud.cn/2015-09-15_55f7ea4d8ac4e.png)
MySQL是一个可以让多人同时使用的数据库，使用“LAST_INSERT_ID()”函式来取得自动编制的流水号数值，并不会因为不同的用户端同时使用而造成混乱：
[](http://box.kancloud.cn/2015-09-15_55f7ea4df3e59.png)
“AUTO_INCREMENT”字段的一般用法通常是用来储存从“1”开始的流水号，每一笔新增的纪录都会自动加一成为新的编号。可是如果在新增纪录的时候，自己指定“AUTO_INCREMENT”字段一个数值，就会造成下列的情况：
[](http://box.kancloud.cn/2015-09-15_55f7ea4e8ba96.png)
“AUTO_INCREMENT”字段在你删除纪录以后，也不会帮你重新使用已经用过的编号：
[](http://box.kancloud.cn/2015-09-15_55f7ea4f1e5fc.png)
注：使用“TRUNCATE TABLE”叙述删除包含“AUTO_INCREMENT”字段表格的所有纪录，编号会重新从头开始。
不要指定值，或是指定“NULL”值给“AUTO_INCREMENT”字段，都可以让MySQL为你自动编制一个流水号，并储存到纪录中，这两种也是比较好的方式；另外指定“AUTO_INCREMENT”字段值为“0”的方式也可以，不过会因为MySQL数据库服务器的环境设定而有不同的效果：
[](http://box.kancloud.cn/2015-09-15_55f7ea4fa60c4.png)
如果你需要编制的流水号范围是非常大的，你应该选择“AUTO_INCREMENT”字段的型态为“BIGINT”；MySQL另外提供一个“SERIAL”关键字，让你在定义这种字段时可以比较方便一些：
[](http://box.kancloud.cn/2015-09-15_55f7ea5023fd5.png)
使用“MyISAM”储存引擎的表格，可以使用下列这种比较特殊的“AUTO_INCREMENT”字段：
[](http://box.kancloud.cn/2015-09-15_55f7ea50a0f90.png)
这样的设定同样是请MySQL为你自动编制流水号，不过因为“AUTO_INCREMENT”字段包含在主索引键中，编制流水号的动作会不太一样：
[](http://box.kancloud.cn/2015-09-15_55f7ea560c753.png)
注：在上列的范例中，是把“empno,location,counter”设定为主索引键；如果设定为唯一索引的话，也会有一样的效果；设定为一般索引的话，会造成错误。
使用“AUTO_INCREMENT”字段属性有下列几个重点：
* 一个表格只能有一个“AUTO_INCREMENT”字段，而且要为它建立一个索引，而且通常是建立主索引键或唯一索引，这样可以防止重复的编号；不过MySQL也允许你建立可重复的索引
* 只有整数型态才可以使用“AUTO_INCREMENT”字段属性，你可以根据编号大小的需求，选择使用“TINYINT”、“SMALLINT”、“MEDIUMINT”、“INT”或“BIGINT”，而且因为只会使用到正数，所以你可以加入“UNSIGNED”来增加编号的范围
* 如果编号已经到字段型态的最大范围，例如一个“SMALLINT”型态，而且是指定为“UNSIGNED”的“AUTO_INCREMENT”字段，编号已经到“65535”了，如果再执行新增的叙述，就会造成“Duplicate entry ’65535′ for key ‘字段名称’”的错误
# 9 查询表格与索引资讯
一个数据库在建立许多表格与索引以后，不论是程式开发或是数据库管理人员，都会有查询表格与索引相关资料的需求。例如查询一个表格中有哪些字段，还有字段的型态与属性的设定；也可能需要查询某一个表格建立了哪些索引与设定的资讯。
## 9.1 表格相关资讯
想要知道一个数据库中有哪一些表格，可以执行下列的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7ea56d7854.png)
这个叙述可以使用“字串样式”设定表格名称的条件：
[](http://box.kancloud.cn/2015-09-15_55f7ea5c43f56.png)
MySQL数据库在启动以后，会有一个很特别的数据库，名称是“information_schema”，这个数据库通常会称为“系统资讯数据库”。这个数据库中有一个表格叫作“TABLES”，它储存所有MySQL数据库中的表格相关资讯，“TABLES”表格有下列主要的字段：
| 字段名称 | 型态 | 说明 |
| --- | --- | --- |
| TABLE_SCHEMA | varchar(64) | 数据库名称 |
| TABLE_NAME | varchar(64) | 表格名称 |
| ENGINE | varchar(64) | 使用的储存引擎名称 |
| TABLE_ROWS | bigint(21) unsigned | 纪录数量 |
| AUTO_INCREMENT | bigint(21) unsigned | 如果包含“AUTO_INCREMENT”字段的话，这个字段会储存下一个编号 |
| TABLE_COLLATION | varchar(32) | 表格使用的collation |
执行下列的查询叙述就可以查询表格详细的资讯：
[](http://box.kancloud.cn/2015-09-15_55f7ea5cb0146.png)
MySQL也提供下列的叙述让你查询一个表格的定义：
[](http://box.kancloud.cn/2015-09-15_55f7ea5d3e7e3.png)
下列的叙述可以查询建立表格的“CREATE TABLE”叙述：
[](http://box.kancloud.cn/2015-09-15_55f7ea6290068.png)
回传的“Create Table”字段的内容就是一个建立表格的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7ea63022c4.png)
## 9.2 索引相关资讯
MySQL提供“SHOW INDEX”叙述查询一个表格的索引详细资讯，下列是执行这个叙述以后，传回的主要字段资料：
| 字段名称 | 说明 |
| --- | --- |
| Table | 表格名称 |
| Non_unique | “0”表示不可重复；“1”可以重复 |
| Key_name | 索引名称 |
| Seq_in_index | 单一字段的索引为“1”；多个字段的索引表示建立索引的字段顺序 |
| Column_name | 索引字段名称 |
| Sub_part | 如果是指定长度的索引，这里会显示长度；不是的话显示“NULL” |
| Null | 是否允许“NULL”值 |
| Index_type | 索引种类，“BTREE”或“HASH” |
你可以在“SHOW INDEX FROM”后面指定一个表格名称，执行以后就可以查询这个表格所有的索引资讯：
[](http://box.kancloud.cn/2015-09-15_55f7ea6393f9d.png)
';
（8） 存储引擎和数据类型
最后更新于：2022-04-01 23:39:57
**目录**
[TOC]
# 1 表格与储存引擎
表格(table)是数据库中用来储存纪录的基本单位，在建立一个新的数据库以后，你必须为这个数据库建立一些储存资料的表格：
[](http://box.kancloud.cn/2015-09-15_55f7e84a3a7d1.png)
每一个数据库都会使用一个资料夹，这些数据库资料夹用来储存所有数据库各自需要的档案：
[](http://box.kancloud.cn/2015-09-15_55f7e84d02adb.png)
“Storage engine、储存引擎”是MySQL用来储存资料的技术，为了数据库多样化的应用，你可以在建立表格的时候，依照自己的需求指定一种储存引擎，不同的储存引擎会有不同的资料储存方式与运作的特色。MySQL提供许多储存引擎让你选择，下列是主要的三种储存引擎的简介：
* MyISAM：MySQL默认的储存引擎，虽然它支援的功能并没有像一般的数据库那么多(例如交易、transaction)；不过也因为它比较简单，所以运作的效率相对也比较好
* InnoDB：这种储存引擎所提供的功能已经跟大型的商用数据库软件一样了，像是交易(transaction)、纪录锁定(row-level locking) 与自动回复(auto-recovery)。
* MEMORY：这是一个比较特殊的储存引擎，它把资料储存在纪忆体中，所以运作的效率是最快的；不过只要MySQL服务器关闭后，储存的资料就全部不见了。
## 1.1 MyISAM
“MyISAM”是MySQL默认的储存引擎，“默认”的意思是如果你在建立表格的时候没有指定一种储存引擎，MySQL会帮你建立的新表格指定为“MyISAM”储存引擎。以下列一个使用MyISAM储存引擎的数据库来说，在数据库资料夹中的档案会像这样：
[](http://box.kancloud.cn/2015-09-15_55f7e8539b492.png)
当你建立一个表格以后，“MyISAM”储存引擎会建立以表格名称为档案名称的三个档案，以“city”表格来说：
[](http://box.kancloud.cn/2015-09-15_55f7e8543e60e.png)
使用“MyISAM”储存引擎的数据库具有“可携性、portable”的特色，你可以很容易的把一个数据库复制到另外一台电脑的MySQL服务器中：
[](http://box.kancloud.cn/2015-09-15_55f7e854ab4ed.png)
注：使用“MyISAM”储存引擎时，MySQL并不会限制一个数据库中可以包含的表格数量。不过一个表格会在档案系统中建立三个档案，如果超过作业系统对于档案数量或容量的限制，你就不能再建立任何新的表格。
## 1.2 InnoDB
MySQL数据库服务器从3.23.49版本开始把“InnoDB”储存引擎列为正式支援的功能，所以从这个版本开始，MySQL也提供与大型商用数据库软件一样的功能。最主要的功能是支援“交易、transaction”，在比较复杂的数据库应用系统中，很常遇到这样的情况：
[](http://box.kancloud.cn/2015-09-15_55f7e855394d4.png)
在顺利的情况下，当然不会有任何问题。可是如果发生下列的情况：
[](http://box.kancloud.cn/2015-09-15_55f7e8559c64b.png)
这样的情况是一定要避免，否则数据库中储存的资料就会出现很大的问题了。所以一般的大型商用数据库都会使用交易的功能来处理这样的情况：
[](http://box.kancloud.cn/2015-09-15_55f7e86168f52.png)
“InnoDB”储存引擎除了提供许多功能外，与“MyISAM”储存引擎最大的差异是档案的储存方式：
[](http://box.kancloud.cn/2015-09-15_55f7e861ea9a8.png)
“InnoDB”储存引擎实际储存在档案系统中的档案会像这样：
[](http://box.kancloud.cn/2015-09-15_55f7e862423fa.png)
注：因为使用“InnoDB”储存引擎的表格会使用同一个储存空间，所以不同数据库的表格资料也会储存在一起。“InnoDB”储存引擎限制在这个共用的储存空间中不能超过两百万个表格。
## 1.3 MEMORY
“MEMORY”储存引擎与其它储存引擎有一个主要的差异，就是它会把纪录与索引资料储存在内存中。所以使用“MEMORY”储存引擎的表格，不论在查询或维护资料时的效率都是很好的。在档案系统中储存的档案只有“frm”档，也就是储存表格结构资讯的档案：
[](http://box.kancloud.cn/2015-09-15_55f7e866c5af6.png)
注：因为“MEMORY”储存引擎会把纪录与索引资料储存在内存中，所以只要MySQL服务器关闭、重新启动、当机，所有使用“MEMORY”储存引擎的表格资料都会全部消失，只剩下表格结构；它也不适合储存大量资料的表格，会耗用太多内存
## 1.4 储存引擎与作业系统
虽然MySQL数据库是一个独立运作的软件，不过它还是得安装在某一个作业系统中，例如Windows或Linux。而由作业系统控制的档案系统可能会有许多限制，例如档案的数量和档案的大小。如果MySQL数据库软件在建立或使用数据库档案的时候，超过作业系统的限制，就会发生错误。
如果以支援的功能来决定储存引擎的话，那就会比较明确。如果要以作业系统的限制来决定储存引擎的话，你可以参考下列的作法：
* 使用“MyISAM”储存引擎可以避免违反档案大小的限制
* 使用“InnoDB”储存引擎可以避免违反档案数量的限制
* 如果在档案数量与大小的限制都遇到问题的话，你只好增加硬件和修改作业系统在档案系统上的设定
# 2 字段资料型态
在建立表格时，你会帮每一个字段指定适合的“资料型态、data type”。正确的选择字段资料型态，除了可以帮你储存正确的资料外，还可以让数据库使用最少的内存与储存空间，这样会让数据库运作的效率更好一些。资料型态主要分为下列三大类：
* 数值：任何包含正、负号的整数与小数资料；另外还有位元(bit)的数值资料，它使用二进制来表示一个数字。
* 字串：包含non-binary与binary两种字串值，non-binary字串值是一些使用字符集与collation的字符(character)组合起来的；binary字串值是一些字节(bytes)组合的资料。
* 日期与时间：包含日期、时间与日期加时间。
## 2.1 数值
数值资料分为整数与小数资料，下列是MySQL提供的整数型态：
| 型态 | **Byte(s)** | **默认长度** | **有号数范围** | **无号数范围** |
| --- | --- | --- | --- | --- |
| TINYINT[(长度)] | 1 | 4 | -128~127 | 0~255 |
| SMALLINT[(长度)] | 2 | 6 | -32768~32767 | 0~65535 |
| MEDIUMINT[(长度)] | 3 | 9 | -8388608~8388607 | 0~16777215 |
| INT[(长度)] | 4 | 11 | -2147683648~2147683647 | 0~4294967295 |
| BIGINT[(长度)] | 8 | 20 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
整数型态的意思就是它们不能储存小数，在建立表格的时候，如果需要一个可以储存整数资料的字段，你可以依照整数资料的大小需求，选择一个够用又不会太浪费空间的整数形态。以下列的“cmdev.integertable”表格来说：
| **字段名称** | **型态** | **范围** |
| --- | --- | --- |
| n | TINYINT(4) | -128~127 |
| n2 | SMALLINT(6) | -32768~32767 |
| n3 | MEDIUMINT(9) | -8388608~8388607 |
| n4 | INT(11) | -2147683648~2147683647 |
| n5 | BIGINT(20) | -9223372036854775808~9223372036854775807 |
整数型态的后面会在左右刮号中指定一个数字，以“SMALLING”型态来说：
[](http://box.kancloud.cn/2015-09-15_55f7e8672f909.png)
当你在执行资料的新增或修改的时候，就要特别注意它们的可以储存数字的范围：
[](http://box.kancloud.cn/2015-09-15_55f7e87690ade.png)
整数型态的字段，就表示它们不可以储存小数的数值：
[](http://box.kancloud.cn/2015-09-15_55f7e87bcd8fa.png)
数值型态还有下列几种可以储存小数资料的浮点数型态：
| **型态** | **Byte(s)** | **默认长度** | **最大长度** | **说明** |
| --- | --- | --- | --- | --- |
| FLOAT[(长度,小数位数)] | 4 | 注1 | 255, 30 | 单精确度浮点数(近似值) |
| DOUBLE[(长度,小数位数)] | 8 | | 255, 30 | 双精确度浮点数(近似值) |
| DECIMAL[(长度[,小数位数])] | 注2 | 10, 0 | 65, 30 | 自行指定位数的精确值 |
注1：FLOAT与DOUBLE的默认长度会因为不同的作业系统而有不一样的长度
注2：依照指定的位数决定实际储存的空间
“FLOAT”和“DOUBLE”型态的字段可以用来储存包含小数的数值，储存空间分别是4和8个字节，它们是一种占用储存空间比较小，执行运算比较快的型态。不过因为它们是使用“近似值”来储存你的数值，所以如果你需要储存完全精准的数值，就不能使用这两种型态。
另外一种可以储存小数数值的“DECIMAL”型态就可以用来储存完全精准的数值，储存在这个型态中的数值，不论是查询或是运算，都不会有任何误差，不过“DECIMAL”型态占用的储存空间就比“FLOAT”和“DOUBLE”型态大。“DECIMAL”型态在MySQL还有一个一样的关键字是“NUMERIC”，这两种型态完全一样。
在MySQL中，“FLOAT”、“DOUBLE”和“DECIMAL”都可以依照自己的需要设定长度与位数：
[](http://box.kancloud.cn/2015-09-15_55f7e87f4b8c2.png)
在设定长度与小数位数的时候，要注意下列几个规则：
* 不可以超过最大长度
* 小数位数不可以超过长度
* 长度与小数位数一样的时候，表示只可以储存小数，例如“0.123”
MySQL的数值型态，包含整数与浮点数都可以设定为“只能储存正数”，以下列的“cmdev.numerictable”表格来说：
| **字段名称** | **型态** |
| --- | --- |
| i | TINYINT(3) UNSIGNED |
| i2 | SMALLINT(5) UNSIGNED |
| i3 | MEDIUMINT(8) UNSIGNED |
| i4 | INT(10) |
| i5 | BIGINT(20) UNSIGNED |
| f | FLOAT UNSIGNED |
| f2 | DOUBLE |
| f3 | DECIMAL(10, 0) UNSIGNED |
设定为只能储存正数的字段，就跟字面上的效果一样，任何希望储存负数的动作都会造成错误：
[](http://box.kancloud.cn/2015-09-15_55f7e87fc3351.png)
MySQL的数值型态都可以依照自己的需要设定长度，以下列的“cmdev.numerictable2”表格来说：
| **字段名称** | **型态** |
| --- | --- |
| i | TINYINT(3) |
| i2 | SMALLINT(3) |
| i3 | MEDIUMINT(3) |
| i4 | INT(3) |
| i5 | BIGINT(3) |
| f | FLOAT(5, 2) |
| f2 | DOUBLE(5, 2) |
| f3 | DECIMAL(5, 2) |
同样为数值型态设定长度，在整数和浮点数会有不一样的效果。如果你为整数型态的字段设定长度的话，这个长度只是设定显示的长度而已，并不会影响实际储存的长度：
[](http://box.kancloud.cn/2015-09-15_55f7e8852e81a.png)
为浮点数型态设定长度与小数位数的时候，效果就跟整数型态不一样了：
[](http://box.kancloud.cn/2015-09-15_55f7e88faf65b.png)
不过在整数位数的部份，就一定会依照设定来储存，否则会造成错误：
[](http://box.kancloud.cn/2015-09-15_55f7e8901949e.png)
MySQL的在数值型态的设定上，还有一个比较特别的设定，就是“ZEROFILL”，以下列的“cmdev.numerictable3”表格来说：
| **字段名称** | **型态** |
| --- | --- |
| i | TINYINT(3) UNSIGNED ZEROFILL |
| i2 | SMALLINT(4) UNSIGNED ZEROFILL |
| i3 | MEDIUMINT(5) UNSIGNED ZEROFILL |
| i4 | INT(6) UNSIGNED ZEROFILL |
| i5 | BIGINT(7) UNSIGNED ZEROFILL |
| f | FLOAT(5, 2) UNSIGNED ZEROFILL |
| f2 | DOUBLE(7, 3) UNSIGNED ZEROFILL |
| f3 | DECIMAL(9, 5) UNSIGNED ZEROFILL |
“ZEROFILL”的设定表示在查询这些字段的时候，回传的资料会在左侧根据长度的设定填满“0”：
[](http://box.kancloud.cn/2015-09-15_55f7e89071b74.png)
注：“ZEROFILL”一定要跟“UNSIGNED”一起使用，就算你只有为字段设定“ZEROFILL”，MySQL也会自动加入“UNSIGNED”的设定。
整数型态的部份，在补0的处理上会不太一样：
[](http://box.kancloud.cn/2015-09-15_55f7e890e8dd4.png)
## 2.2 位元
“位元、BIT”型态其实也是用来储存数值用的，不过它是以二进制的型式储存资料，也就是只有0跟1两种资料。MySQL的在数值型态的设定上，还有一个比较特别的设定，就是“ZEROFILL”，以下列的“cmdev.numerictable3”表格来说：
| **字段名称** | **型态** | **数字范围** |
| --- | --- | --- |
| n | BIT | 0~1 |
| n2 | BIT(8) | 0~255 |
| n3 | BIT(64) | 0~18446744073709551615 |
你可以直接储存数字到位元型态的字段；也可以指定一个使用二进制表示的值：
[](http://box.kancloud.cn/2015-09-15_55f7e89128f6a.png)
## 2.3 字串
MySQL把字串型态分为两大类：“非二进位制、non-binary”与“二进位制、binary”。非二进位制就是储存一般文字的字串，会有特定的字符集与collation；二进位制使用字节储存资料，不包含字符集与collation，所以大多用来储存图片或音乐这类资料。“非二进位制、non-binary”的字串型态有下列几种：
| **型态** | **最大长度** | **实际储存的空间** | **说明** |
| --- | --- | --- | --- |
| CHAR[(长度)] | 255 | 指定的长度 | 固定长度的字串，默认长度为1 |
| VARCHAR(长度) | 65535 | 字符个数加1或2bytes | 变动长度的字串 |
| TINYTEXT | 255 | 字符个数加1byte | |
| TEXT | 65535 | 字符个数加2bytes | |
| MEDIUMTEXT | 16,772,215 | 字符个数加3bytes | |
| LONGTEXT | 4,294,967,295 | 字符个数加4bytes | |
固定长度与变动长度的两种字串型态都可以储存字串，差异在储存的文字个数小于型态指定的长度时，变动长度实际储存的空间会小一些，以下列的“cmdev.nonbinarytable”表格来说：
| **字段名称** | **型态** |
| --- | --- |
| s | CHAR(10) |
| s2 | VARCHAR(10) |
同样把长度设定为10的“CHAR”与“VARCHAR”字串型态，它们在储存字串资料的时候会不太一样：
[](http://box.kancloud.cn/2015-09-15_55f7e8a069968.png)
“非二进位制、non-binary”的字串都会包含特定的字符集与collation，所以可以用来储存各种不同国家的文字。不同的字符集会占用不同的储存空间，以下列的“cmdev.nonbinarytable2”表格来说：
| **字段名称** | **型态** | **字符集** |
| --- | --- | --- |
| s | VARCHAR(6) | latin1 |
| s2 | VARCHAR(6) | big5 |
| s3 | VARCHAR(6) | utf8 |
上列的表格中，三个字段分别设定为“latin1”、“big5”与“utf8”字符集，你可以查询MySQL数据库支援的字符集特性，“MAXLEN”字段是关于储存空间的资讯：
[](http://box.kancloud.cn/2015-09-15_55f7e8a0acfcc.png)
使用在“LENGTH”函式来查询储存在这个表格中的字串资料，就可以很明显的看出不同的字符集，在储存字符时使用的储存空间：
[](http://box.kancloud.cn/2015-09-15_55f7e8a5e85ec.png)
“LENGTH”函式会传回字串资料实际的储存长度(byte)；如果你要查询字串的字符数量的话，就要使用“CHAR_LENGTH”函式：
[](http://box.kancloud.cn/2015-09-15_55f7e8ab3414c.png)
字符集会影响字串的储存空间，collation会影响字串排列顺序。以下列的“cmdev.nonbinarytable3”表格来说：
| 字段名称 | 型态 | 字符集 | Collation |
| --- | --- | --- | --- |
| s | VARCHAR(6) | latin1 | latin1_general_ci |
| s2 | VARCHAR(6) | latin1 | latin1_general_cs |
上列表格中字段的字符集都指定为“latin1”，不过“s”字段的collation设定为“latin1_general_ci”，表示排序时不区分大小写；“s2”字段设定为“latin1_general_cs”，表示排序时会区分大小写。以下列储存在这个表格中纪录来说：
[](http://box.kancloud.cn/2015-09-15_55f7e8ad2576a.png)
Collation设定中的“latin1_general_ci”，最后的“ci”表示“case insensitive”，是不分大小写的意思。在这样的设定下，MySQL会把字串“ABC”和“abc”当成是一样的；“latin1_general_cs”，最后的“cs”表示“case sensitive”，是区分大小写的意思。在这样的设定下，MySQL就会把字串“ABC”和“abc”当成是不一样的字串。
是否区分大小写的collation设定会影响排序的结果：
[](http://box.kancloud.cn/2015-09-15_55f7e8b34aa3e.png)
另外一个影响是条件的判断：
[](http://box.kancloud.cn/2015-09-15_55f7e8b3d6f67.png)
“二进位制、binary”的字串型态是使用字节(byte)为单位来储存字串资料，跟非二进位制的字串类似，它也提供许多应用在不同长度的型态：
| 型态 | 最大长度(byte) | 实际储存的空间(byte) | 说明 |
| --- | --- | --- | --- |
| BINARY[(长度)] | 255 | 指定的长度 | 固定长度的字串，默认长度为1 |
| VARBINARY(长度) | 65535 | 长度加1或2bytes | 变动长度的字串 |
| TINYBLOB | 255 | byte数加1byte | |
| BLOB | 65535 | byte数加2bytes | |
| MEDIUMBLOB | 16,772,215 | byte数加3bytes | |
| LONGBLOB | 4,294,967,295 | byte数加4bytes | |
“BINARY”与“VARBINARY”两种型态的差异，与“CHAR”和“VARCHAR”的差异一样。在一般的情况下，使用“VARBINARY”会比“BINARY”节省一点储存空间。你也可以使用“二进位制、binary”型态储存文字资料，只不过MySQL都是以字节来储存所有的资料，也就是0到255的数字：
[](http://box.kancloud.cn/2015-09-15_55f7e8b45c6d1.png)
所有“二进位制、binary”的字串型态都不可以指定字符集与collation，不过你可以使用它们来储存任何语言的文字，也可以储存类似音乐或图片资料，因为MySQL都是一个一个byte的把资料储存到数据库中；所以在执行查询时的排序和条件设定，都是以使用字节为单位来判断。
## 2.4 列举与集合
列举(ENUM)与集合(SET)是一种特殊的“非二进位制、non-binary”字串型态，所以它们也可以指定字符集与collation。下列是这两种型态的说明：
| 型态 | 最大个数 | 储存空间 | 说明 |
| --- | --- | --- | --- |
| ENUM(字串值[,...]) | 65535 | 1byte(255个)2bytes(256到65535个) | 包含一组合法的字串值(单一值) |
| SET(字串值[,...]) | 64 | 1byte(8个)2bytes(16个)3bytes(24个)4bytes(32个)8bytes(64个) | 包含一组合法的字串值(多个值) |
列举(enumeration)的资料在数据库中的应用很常见，例如服装的大小就会以S、M与L来表示小、中与大。你可以使用字串来储存这类资料，不过这类的资料也很适合使用“ENUM”型态来储存。以下列的“cmdev.enumtable”表格来说：
[](http://box.kancloud.cn/2015-09-15_55f7e8b9a33df.png)
在储存资料的时候，“ENUM”型态看起来似乎与“VARCHAR”完全一样：
[](http://box.kancloud.cn/2015-09-15_55f7e8b9dc896.png)
可是列举型态在资料的正确性方面，就会比单纯的字串型态好多了。例如下列错误示范：
[](http://box.kancloud.cn/2015-09-15_55f7e8ba60a67.png)
列举型态字段除了可以直接使用字串值来新增与更新资料外，还可以使用数值资料的编号来代替，任何一个列举型态中的成员，MySQL都会帮它们编一个号码：
[](http://box.kancloud.cn/2015-09-15_55f7e8bacd743.png)
了解列举型态中成员的编号以后，你可以选择字串值或数值来管理列举型态字段储存的资料：
[](http://box.kancloud.cn/2015-09-15_55f7e8bb32038.png)
虽然在查询列举型态字段资料的时候，所得到的结果都是成员的字串值；不过真正储存在数据库中的资料却是成员的编号，所以指定列举型态字段为排序字段的时候，数据库会使用编号来排序，而不是以成员的字串值：
[](http://box.kancloud.cn/2015-09-15_55f7e8bbb8ba4.png)
在指定列举型态字段的查询条件时，可以使用成员的字串值或编号：
[](http://box.kancloud.cn/2015-09-15_55f7e8bc66f72.png)
集合(SET)型态同样可以设定一组成员，不过它可以储存多个成员资料。例如星期的成员总共有七个，而需要工作的星期就会有一个以上了，类似这样的需求就应该使用集合型态。以下列的“cmdev.settable”表格来说：
| 字段名称 | 型态 |
| --- | --- |
| workingday | SET(‘MON’,'TUE’,'WED’,'THU’,'FRI’,'SAT’,'SUN’) |
你可以使用一个字串值来管理集合型态字段，在这个字串值中，使用逗号来隔开不同的成员字串：
[](http://box.kancloud.cn/2015-09-15_55f7e8bca5c85.png)
集合型态字段与列举型态字段同样具有检查资料是否正确的能力：
[](http://box.kancloud.cn/2015-09-15_55f7e8bd09eff.png)
列举型态字段的成员编号使用简单的连续数字；集合型态字段会比较复杂一些：
[](http://box.kancloud.cn/2015-09-15_55f7e8bd50a06.png)
了解集合型态字段的成员所代表的数字后，你就可以使用数值来管理储存的资料：
[](http://box.kancloud.cn/2015-09-15_55f7e8c2911c4.png)
要使用数值来代表多个成员的时候，你只要把所有成员的数字加总起来就可以了：
[](http://box.kancloud.cn/2015-09-15_55f7e8c7d130b.png)
列举与集合型态都可以设定需要的字符集与collation，以下列的“cmdev.estable”表格来说：
| 字段名称 | 型态 | 字符集 | Collation |
| --- | --- | --- | --- |
| enumsize | enum(‘XS’, …) | latin1 | latin1_general_ci |
| enumsize2 | enum(‘XS’, …) | latin1 | latin1_general_cs |
| workingday | set(‘MON’, …) | latin1 | latin1_general_ci |
| workingday2 | set(‘MON’, …) | latin1 | latin1_general_cs |
字符集的设定可以决定可以储存字串资料的编码，而collation的设定会决定字串值是否区分大小写：
[](http://box.kancloud.cn/2015-09-15_55f7e8cd613d7.png)
如果指定字串值的时候违反collation设定的大小写规则，就会发生错误：
[](http://box.kancloud.cn/2015-09-15_55f7e8cdbe9b5.png)
## 2.5 日期与时间
MySQL提供下列几个可以储存日期与时间资料的字段型态：
| 型态 | Byte(s) | 说明 | 范围 |
| --- | --- | --- | --- |
| DATE | 3 | 日期 | ’1000-01-01′~’9999-12-31′ |
| TIME | 3 | 时间 | ‘-838:59:59′~’838:59:59′ |
| DATETIME | 8 | 日期与时间 | ’1000-01-01 00:00:00′~’9999-12-31 23:59:59′ |
| YEAR[(4 | 2)] | 1 | 西元年 | 1901~2155[YEAR(4)]1970~2069[YEAR(2)] |
| TIMESTAMP | 4 | 日期与时间 | ’1970-01-01 00:00:00′~2037 |
日期(DATE)型态字段可以储存年、月、日的资料，范围从“1000-01-01”到“9999-12-31”，你的日期资料不可以超过“9999-12-31”，可是你可以储存“1000-01-01”以前的日期，不过MySQL建议你最好不要这么作，不然可能会造成一些奇怪的问题。
因为日期中的西元年份可以使用四个或两个数字，使用两个数字的时候，“70”到“99”表示“1970”到“1999”；如果是“00”到“69”就是“2000”到“2069”。所以要注意下列的情况：
[](http://box.kancloud.cn/2015-09-15_55f7e8ce3886d.png)
另一个日期资料会变成这样：
[](http://box.kancloud.cn/2015-09-15_55f7e8ce7b922.png)
时间(TIME)型态可以储存时、分、秒的资料，范围从“-838:59:59”到“838:59:59”。这个储存时间资料的范围可能会跟你想的不太一样。一般来说，时间资料指的是从“00:00:00”到“23:59:59”，也就是一天的时间。MySQL的时间型态字段可以让你储存类似“经过的时间”这样的资料：
[](http://box.kancloud.cn/2015-09-15_55f7e8ceceb94.png)
在指定一个时间资料的时候，你可以省略秒或分，省略的部份，MySQL都会帮你设定为“0”：
[](http://box.kancloud.cn/2015-09-15_55f7e8cf38f61.png)
日期与时间(DATETIME)型态可以储存完整的年、月、日与时、分、秒资料，范围从“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。在表示一个日期与时间资料的时候，日期与时间之间，至少要使用一个空白隔开。时间部份的时、分、秒都可以省略，省略的部份，MySQL都会帮你设定为“0”：
[](http://box.kancloud.cn/2015-09-15_55f7e8d4b276a.png)
如果只需要储存年份资料的话，你可以使用西元年(YEAR)型态，这样会节省很多储存空间。你可以视需要把西元年型态设定为两位或四位数字，四位数字可以储存的范围从“1901”到“2155”；两位数字的范围从“00”到“99”，实际的西元年份是“1970”到“2069”。
在指定一个年份资料给西元年型态字段的时候，可以使用字串值或数值来表示西元年份，不同个数的资料会有不同的储存效果：
[](http://box.kancloud.cn/2015-09-15_55f7e8d6ec46d.png)
如果西元年型态字段的值是“0”的话，MySQL会把它当成是一个不正确的西元年资料，所以你应该不会指定这样的资料，不过指定不同的资料也会有不同的储存结果：
[](http://box.kancloud.cn/2015-09-15_55f7e8d77067e.png)
“TIMESTAMP”型态的格式与“DATETIME”一样，都包含完整的年、月、日与时、分、秒资料，不过它使用的储存空间只有4bytes，是“DATETIME”型态的一半。
“TIMESTAMP”也是MySQL日期与时间型态中具有“时区”特性的型态。它可以储存从“1970-01-01 00:00:00”到目前经过的秒数。这个起始日期与时间使用“Coordinated Universal Time、UTC”世界标准时间为储存资料的依据，它与“Greenwich Mean Time、GMT”格林威治标准时间是一样的。
全世界分为许多不同时区(time zone)，所有时区都使用跟标准时间的差异来当作自己的标准时间。以台湾来说，你会在安装Windows平台的电脑中，经由控制台里的日期和时间，看到这个关于时区的设定：
[](http://box.kancloud.cn/2015-09-15_55f7e8d7b38c8.png)
MySQL数据库采用与作业系统同样的时区设定，所以在储存“TIMESTAMP”型态字段的资料时，过程中会有一些计算的动作：
[](http://box.kancloud.cn/2015-09-15_55f7e8d85bd2e.png)
而查询“TIMESTAMP”型态字段资料的时候，也会有这样的情况：
[](http://box.kancloud.cn/2015-09-15_55f7e8d8e805a.png)
了解时区设定与“TIMESTAMP”型态的关系后，你就可以知道下列的动作为什么会发生错误了：
[](http://box.kancloud.cn/2015-09-15_55f7e8d9325e0.png)
你可以使用查询叙述取得MySQL数据库服务器关于时区的设定：
[](http://box.kancloud.cn/2015-09-15_55f7e8d975d39.png)
如果想要设定其它的时区，可以使用“+时时:分分”或“-时时:分分”的格式。例如日本东京时区比格林威治标准时间晚九小时，你可以设定为“+09:00”；而美国旧金山比格林威治标准时间早七小时，可以设定为“-07:00”：
[](http://box.kancloud.cn/2015-09-15_55f7e8df060b6.png)
设定新的时区以后，使用下列的范例测试“DATETIME”和“TIMESTAMP”两种型态，可以看出在储存日期时间资料上的差异：
[](http://box.kancloud.cn/2015-09-15_55f7e8df64430.png)
因为“TIMESTAMP”型态储存的是格林威治标准时间，所以在修改时区后，查询得到的日期时间资料就会有差异了：
[](http://box.kancloud.cn/2015-09-15_55f7e8dfe0aab.png)
';
（7） 字符集和数据库
最后更新于：2022-04-01 23:39:55
**目录**
[TOC]
# 1 Character Set与Collation
任何资讯技术在处理资料的时候，如果只是单纯的数值和运算，那就不会有太复杂的问题；如果处理的资料是文字的话，就会面临世界上各种不同语言的问题。以资料库来说，它必须正确的储存各种不同语言的文字，也就是一个资料库中，有可能同时储存繁体和简体中文、法文等不同语言的文字。
电脑在处理文字资料大多是使用一个「编码」来表示某一个字，对MySQL资料库来说，为了要处理不同语言的文字，它使用一套编码来处理一种语言的文字，称为「字元集、character set」。以英文字母来说，每一个字母都有一个编码，例如A=65、B=66、C=67。
MySQL可以依照你的需要为资料库设定不同的字元集：
Collation指的是在一个字元集中，所有字元的大小排序规则。以英文字母来说，我们会依照A到Z的顺序当成大小的顺序，小写的字母也是一样的。这样的大小顺序是依照编码的大小来决定的，MySQL把它称为「binary collation」。
可是在真实的世界中，大小顺序却不是这么单纯，有时候你会把大小写的英文字母当成是一样的，例如大写的A和小写的a。在这种情况下，大写和小写的字母会被当成是一样的大小，然后再依照编码来决定，例如大写A的编码比小写a的编码小。MySQL把这样的方式称为「case-insensitive collation」。
在决定大小顺序的时候，如果只有考虑字母大小写因素的话，那还不算是太复杂的。如果再考虑各种不同语言特性的话，在决定大小顺序的时候就会变得很复杂。以繁体中文来说，它是没有区分大小写的，而且一个中文字会包含一个以上的位元组，其它的语言也都会有类似的情况。
## 1.1 Character Set
MySQL资料库把各种不同字元集的编码资料纪录在系统资料库中，你可以使用下列的指令查询MySQL资料库支援的字元集资讯：
~~~
SHOW CHARACTER SET
~~~
执行上列的查询指令后可以得到下列的结果：
## 1.2 COLLATION
MySQL除了支援各种不同的字元集，让资料库可以储存不同语言的文字外，每一种字元集都可以依照实际需要，搭配不同的Collation设定。你可以使用下列的指令查询MySQL支援的Collation资讯：
~~~
SHOW COLLATION
~~~
执行上列的查询指令后可以得到下列的结果：
你也可以使用类似「WHERE」子句中的条件设定，查询某一种字元集支援的Collation资讯：
你可以从Collation名称分辨出排序的准则：
# 2 资料库
资料库(Database)是用来保存各种资料元件的容器，在安装好MySQL资料库伺服器软体后，就可以依照自己的需求建立资料库，MySQL对于资料库的数量并没有限制：
每一个MySQL资料库伺服器软体都会使用一个储存资料的资料夹，称为「data directory」。在这个资料夹下，每建立一个资料库，MySQL都会建立一个资料夹，称为「资料库资料夹、database directory」，一个资料库包含的档案就会放在各自的资料库资料夹中：
注：使用`SHOW VARIABLES LIKE 'datadir'`叙述，可以查询MySQL资料库伺服器使用的资料库资料夹。
因为一个资料库会是档案系统中的一个资料夹，所以你要特别留意下列的特性：
* 虽然MySQL对于资料库的数量并没有限制，可是你要注意MySQL资料库伺服器软体所安装的作业系统，它对于资料夹与档案大小的限制。
* MySQL使用资料库名称作为资料库资料夹的名称，所以你要特别注意大小写的问题。在资料夹名称不分大小写的作业系统(例如Windows)，资料库名称「MyDB」和「mydb」是一样的；可是在资料夹名称会区分大小写的作业系统(例如Linux)，资料库名称「MyDB」和「mydb」就不一样了。
* 每一个资料库资料夹中都有一个特别的档案，档案名称是「db.opt」，这个档案的内容是资料库的字元集与collation设定。
注：MySQL把「DATABASE」与「SCHEMA」当成是一样的，所有你在后续使用的指令，都可以把「DATABASE」换成「SCHEMA」。
## 2.1 建立资料库
下列是建立资料库的语法：
在你执行新增资料库的指令以后，MySQL会使用你指定的资料库名称建立一个资料库资料夹：
如果你指定的资料库名称已经存在了，MySQL会产生一个错误讯息：
为了避免上列的错误，你可以在建立资料库的时候加入「IF NOT EXISTS」，如果你指定的资料库名称不存在，同样会建立新的资料库；如果已经存在，MySQL只会产生警告讯息：
你可以在建立资料库的时候指定资料库预设的字元集与collation，如果没有指定的话，就会使用MySQL伺服器预设的设定：
注：如果没有修改过MySQL设定的话，预设的字元集是「latin1」，Collation是「latin1_swedish_ci」。
建立资料库的时候指定字元集与collation会有一些不同的组合，例如下列只有指定字元集的话，MySQL会使用你指定字元集的预设collation：
另外一种是只有使用「COLLATE」指定collation，MySQL会使用你指定collation所属的字元集：
注：建立资料库的时候，不管你有没有指定，资料库都会有预设的字元集与collation。以后在这个资料库建立的表格，都会使用资料库预设的字元集与collation。
## 2.2 修改资料库
建立资料库以后，你唯一能执行的修改是资料库预设的字元集与collation。下列是修改资料库设定的语法：
下列的叙述执行修改资料库预设的字元集与collation。在修改的时候，如果只有指定字元或只有指定collation的话，设定的规则与建立资料库一样：
注：修改资料库的字元集是或Collation，并不会影响原来已经存在的表格。
## 2.3 删除资料库
下列是删除资料库的语法：
如果在删除资料库的叙述中指定的资料库名称不存在，MySQL会产生一个错误讯息：
为了避免上列的错误，你可以在删除资料库的时候，加入「IF EXISTS」指令，如果你指定的资料库名称存在，同样会删除指定的资料库；如果不存在，MySQL只会产生警告讯息：
注：执行删除资料库的叙述，MySQL不会再跟你确认是否删除资料库，而是直接删除；删除资料库以后，表示资料库资料夹也会从档案系统中删除，除非你另外还有这个资料库的备份，否则原来在资料库中的所有资料就全部消失了。
## 2.4 取得资料库资讯
MySQL提供「SHOW」指定让你取得跟资料库相关的资讯，执行下列的指令可以取得MySQL伺服器中所有资料库的名称：
你也可以执行下列的指令取得建立资料库的叙述：
MySQL资料库伺服器有一个很重要的资料库，名称为「information_schema」，这个资料库通常会把它称为「系统资料库」，资料库中储存伺服器所有重要的资讯。跟资料库相关的资讯储存在「SCHEMATA」表格中，所以你可以使用查询叙述取得所有资料库的相关资讯：
';
（6） CRUD 和资料维护
最后更新于：2022-04-01 23:39:53
**目录**
[TOC]
# 1 取得表格资讯
## 1.1 DESCRIBE指令
「DESCRIBE」是MySQL资料库提供的指令，它只能在MySQL资料库中使用，这个指令可以取得某个表格的结构资讯，它的语法是这样的：
你在MySQL的工具中执行「DESC cmdev.dept」指令以后，MySQL会传回「cmdev.dept」表格的结构资讯：
## 1.2 栏位顺序
每一个表格在设计的时候，都会决定它有哪一些栏位，和所有栏位的详细设定。另外也会决定表格中的栏位顺序，知道表格栏位顺序在接下来的讨论中是很重要的：
注：如何建立一个新的表格会在「第八章、表格与索引」中讨论。
# 2 新增
## 2.1 基础新增叙述
新增资料到资料库的表格中使用「INSERT」叙述，下列是这个叙述的基本语法：
使用这个语法新增纪录的时候，要特别注意表格的栏位个数与顺序，下列的新增叙述会新增一笔部门的纪录到「cmdev.dept」表格中：
除了明确的指定新增纪录的每一个栏位资料外，你也可以使用「DEFAULT」关键字，让MySQL为你写入在设计表格的时候，为栏位指定的预设值。下列的新增叙述同样会新增一笔部门的纪录到「cmdev.dept」表格中，不过部门的所在位置(location)栏位值指定为使用预设值：
使用这种语法新增纪录的时候，如果资料个数与栏位个数不一样的话，就会发生错误：
资料个数虽然没有错，顺序却不对了，也有可能会造成错误：
新增叙述的另外一种语法，就提供比较灵活的新增纪录方式，你可以自己指定新增纪录的栏位个数和顺序：
在你额外为这个新增叙述指定栏位以后，指定储存资料的时候就要依照自己指定的栏位个数与顺序：
如果没有依照自己指定的栏位个数与顺序，就会发生错误：
因为这种新增叙述的语法可以自己指定栏位的个数与顺序，所以你只要指定写入栏位的资料就可以了。不过要特别注意下列两种语法的差异：
也因为这样的规定，所以下列这个新增叙述在语法上虽然没有错误，如果违反表格设计上的规定，同样会造成错误：
这种新增叙述的语法还有一个比较特别的用法，如果你要新增的纪录，所有栏位的值都要使用预设值，就可以使用下列的写法。不过要特别注意下列的新增叙述执行以后会造成错误，因为「deptno」与「dname」栏位的预设值是「NULL」，可是它们又不能储存「NULL」：
下列是新增叙述的第三种语法：
这种语法只是提供你另外一种新增纪录的写法，下列两个新增叙述的效果是一样的：
## 2.2 同时新增多笔纪录
上列讨论的新增叙述执行以后，都是一个叙述新增一笔纪录，如果需要的话，你也可以在一个新增叙述新增多笔纪录，差异只有在「VALUES」子句后面新增资料的指定：
如果你要新增下列三个员工资料到「cmdev.emp」表格中：
~~~
----------- ----------- ------------ ------------- -------------- ------------ ---------- ------------
**empno** **ename** **job** **manager** **hiredate** **salary** **comm** **deptno**
8001 SIMON MANAGER 7369 2001-02-03 3300 NULL 50
8002 JOHN PROGRAMMER 8001 2002-01-01 2300 NULL 50
8003 GREEN ENGINEER 8001 2003-05-01 2000 NULL 50
----------- ----------- ------------ ------------- -------------- ------------ ---------- ------------
~~~
你当然可以分别执行三个新增叙述将三个员工资料新增到「cmdev.emp」表格中；你也可以使用下列一个新增叙述，这个叙述执行以后，同样会新增三笔纪录：
## 2.3 索引值
在设计表格的时候，通常会视需要指定表格中的某一个栏位为「主索引」栏位：
注：一个表格除了可以设定「主索引」栏位外，资料库还提供其它几种不同的「索引」，索引的应用与设定会在后面「第八章、表格与索引」中详细讨论。
如果一个表格设定了某一个栏位为主索引以后，你在新增纪录时就不可以违反主索引的规定，否则会产生错误：
你可以在使用「INSERT」叙述的时候，加入「IGNORE」关键字，它可以在执行一个违反主索引规定的新增叙述时，自动忽略新增的动作，这样就不会产生错误讯息了：
## 2.4 索引值与ON DUPLICATE KEY UPDATE
使用「INSERT」叙述新增纪录的时候，还可以视需要在最后搭配一串关键字「ON DUPLICATE KEY UPDATE」，它可以用来指定在违反重复索引值的规定时要执行的修改：
需要为「INSERT」叙述搭配「ON DUPLICATE KEY UPDATE」的情况会比较特殊一些，所以接下来会使用「cmdev.travel」这个表格来讨论它的用法，「cmdev.travel」是员工资料库中用来储存出差资料的表格，每一个员工到某个地方出差的资料，都会储存在这个表格中：
因为这个表格的设计方式，所以如果要处理编号「7900」的员工到「BOSTON」出差资料的话，你就要执行下列的动作：
注：修改叙述「UPDATE」在下一节讨论。
你会发现要处理员工出差资料会是一件不算简单的工作，搭配「ON DUPLICATE KEY UPDATE」的「INSERT」叙述，可以让处理这类需求的叙述比较简单一些：
这个「INSERT」叙述执行以后，资料库会帮你执行需要的检查，根据检查的结果执行不同的动作：
## 2.5 「REPLACE」叙述
除了使用「INSERT」叙述新增纪录外，「REPLACE」叙述同样可以新增纪录，它们的语法几乎相同：
「INSERT」叙述的另一种写法也可以套用给「REPLACE」叙述：
会使用「REPLACE」叙述新增纪录的原因，主要还是考虑索引值的情况，「REPLACE」叙述在没有违反索引值的规定时，效果跟「INSERT」叙述一样，同样会新增纪录到表格中。
在发生重复索引值的时候，「INSERT」叙述会发生错误：
「INSERT」叙述搭配「IGNORE」关键字的时候：
同样的情况改用「REPLACE」叙述的话，它会执行修改纪录的动作：
# 3 修改
修改已经储存在表格中的纪录使用「UPDATE」叙述，下列是它的基本语法：
使用「UPDATE」叙述的时候，通常会搭配使用「WHERE」子句，用来指定要修改的纪录：
所以你在执行「UPDATE」叙述的时候，一定要依照实际的需求，正确的设定修改的条件。以下列两个修改叙述来说，它们执行后的差异是很大的：
## 3.1 搭配「IGNORE」
在使用「UPDATE」叙述的时候，也可以视需要加入「IGNORE」关键字，它可以防止错误的修改叙述出现错误讯息：
除了上列的情况外，你还必须特别注意修改多个栏位值的情况。首先是没有「IGNORE」关键字的时候，错误的资料会在执行修改叙述的时候产生错误讯息，当然也不会执行任何修改的动作：
同样的修改叙述加入「IGNORE」关键字后，执行后的结果可能会跟你想得不太一样了：
## 3.2 搭配「ORDER BY」与「LIMIT」
执行修改的时候使用「WHERE」子句是一般最常见的用法，在处理一些比较特殊的修改需求时，也会搭配「ORDER BY」与「LIMIT」子句：
「LIMIT」子句也可以在查询叙述中使用，不过在「UPDATE」叙述中使用「LIMIT」子句会有一个限制：
以同样为员工加薪一百的需求来说，搭配「ORDER BY」与「LIMIT」子句，可以完成许多不同的情况：
# 4 删除
## 4.1 「DELETE」叙述
删除表格中不再需要的纪录使用「DELETE」叙述，下列是它的语法：
使用「DELETE」叙述的时候，通常也会使用「WHERE」子句设定要删除哪些纪录：
执行删除的时候也可以搭配「ORDER BY」与「LIMIT」子句：
## 4.2 「TRUNCATE」叙述
如果要删除一个表格中所有的纪录，你可以选择使用「TRUNCATE」叙述，下列是它的语法：
要执行删除表格中所有的纪录，下列两个叙述的效果是一样的：
「TRUNCATE」叙述在执行删除纪录的时候，会比使用「DELETE」叙述的效率好一些，尤其是表格中的纪录非常多的时候会更明显。
';
（5） JOIN 和 UNION 查询
最后更新于：2022-04-01 23:39:50
**目录**
[TOC]
# 1 使用多个表格
在「world」资料库的「country」表格中，储存世界上所有的国家资料，其中有一个栏位「Capital」用来储存首都资料，不过它只是储存一个编号；另外在「city」表格中，储存世界上所有的城市资料，它主要的栏位有城市编号和城市的名称：
虽然「country」表格自己没有储存城市名称，不过它可以使用「Capital」栏位的值，对照到「city」表格中的「ID」栏位，也可以知道城市的名称。在这样的表格设计架构下，如果你想要查询「所有国家的首都名称」：
这样的查询需求就称为「结合查询」，也就是你要查询的资料，来自于一个以上的表格，而且两个表格之间具有上列讨论的「对照」情形。
# 2 Inner Join
「Inner join」通常称为「内部结合」，它可以应付大部份的结合查询需求，内部结合有两种写法，差异在把结合条件设定在「WHERE」子句或「FROM」子句中。
## 2.1 使用结合条件
下列是在「WHERE」子句中设定结合条件来执行结合查询的语法：
虽然这里会先介绍使用结合条件的结合查询，不过不管使用哪一种写法，在使用结合查询时都会有一样的想法。首先是你想要查询的栏位：
把需要查询的栏位列在「SELECT」之后，「FROM」子句后面该需要哪一些表格就很清楚了：
最后把表格与表格之间「对照」的结合条件放在「WHERE」子句中：
这样的叙述就可以查询「所有国家的首都名称」。
## 2.2 指定表格名称
在上列的讨论中，因为使用到多个表格了，所以在使用表格的栏位时，都特别提醒你要在栏位名称前面加上表格名称。其实并不是全部都要指定表格名称，你只有在一种情况下，才「一定要」在栏位名称前指定表格名称：
在查询叙述的「FROM」子句中用到的表格，如果有一样的栏位名称，而且你在查询叙述中也用到了这些栏位，就「一定要」在栏位名称前指定表格名称，否则都可以省略：
所以省略掉一些表格名称以后，查询叙述就简短多了，不过它执行查询后的结果也是一样的：
~~~
SELECT Code, Capital, city.Name
FROM country, city
WHERE Capital = ID
~~~
如果不小心违反上列的规则，你的查询叙述在执行以后就会发生错误：
## 2.3 表格别名
如果你想要查询「国家和首都的人口和比例」：
这样的结合查询刚好都使用到两个表格中，有同样名称的栏位，所以你一定要指定表格名称：
~~~
SELECT country.name, country.Population coPop,
city.Name, city.Population ciPop,
city.Population / country.Population * 100 Scale
FROM country, city
WHERE Capital = ID
~~~
这样的查询叙述就会比较长一些，也比较容易打错；所以在结合查询的叙述中，通常为帮「FROM」子句后面的表格都取一个「表格别名」：
使用表格别名以后：
帮「FROM」子句中使用到的表格都取一个表格别名，这样的查询叙述通常也可以比较简短一些了。
## 2.4 使用「INNER JOIN」
执行结合查询除了使用上列讨论的方式外，还有另外一种结合查询语法：
虽然这两种写法看起来的差异很大，不过它们的想法会是一样的。首先是需要查询的栏位：
接下来是需要用到的表格，不过你要使用「INNER JOIN」把两个表格「结合」起来：
最后是结合条件：
上列使用「INNER JOIN」的结合查询执行以后，跟之前使用结合条件的结合查询，所得到的结果是完全一样的。所以查询「国家和首都的人口和比例」的结合查询，也可以改用下列的写法：
~~~
SELECT a.name, a.Population coPop,
b.Name, b.Population ciPop,
b.Population / a.Population * 100
FROM country a INNER JOIN city b ON Capital = ID
~~~
使用「INNER JOIN」的结合查询还有另外一种选择：
下列是使用「ON」或是「USING」来设定结合条件的情况：
所以如果想要查询「cmdev」资料库中，员工资料和他们的部门名称，就会有三种写法可以选择了：
# 3 Outer Join
在「cmdev」的员工资料(emp)表格中，部门编号(deptno)栏位是用来储存员工所属的部门用的；不过有一些员工并没有部门编号：
所以如果你使用「内部结合」的作法执行下列的查询，你会发现少了两个员工的资料：
这是因为使用「内部结合」的查询，一定要符合「结合条件」的资料才会出现：
如果你想查询的资料是「包含部门名称的员工资料，可是没有分派部门的员工就不用出现了」，那使用「内部结合」就可以完成你的工作了；可是如果你想要查询的资料是「包含部门名称的员工资料，没有分派部门的员工也要出现」，那你就要使用「OUTER JOIN」，这种结合查询通常称为「外部结合」：
除了多一个「LEFT」或「RIGHT」，还有把「INNER」换成「OUTER」外，其它的部份与内部结合的作法都是一样的。
## 3.1 LEFT OUTER JOIN
所以在结合查询的应用中，如果你想要查询的资料是「包含部门名称的员工资料，没有分派部门的员工也要出现」，也就是希望不符合结合条件的资料也要出现的话，就要换成使用「LEFT OUTER JOIN」来执行结合查询。OUTER JOIN分为LEFT和RIGHT两种，在这个范例中，要使用LEFT才符合查询的需求：
## 3.2 RIGHT OUTER JOIN
其实使用「LEFT OUTER JOIN」或是「RIGHT OUTER JOIN」并没有差异，以上列的需求来说，要查询「包含部门名称的员工资料，没有分派部门的员工也要出现」，就是要以「cmdev.emp」表格的资料为主，所以下列两种写法所得到的结果是完全一样的：
了解两种「OUTER JOIN」的后，下列这两个看起来会有点混淆的查询，虽然只有「LEFT」与「RIGHT」的差异，它们所完成的查询需求，却是完全不一样的：
所以使用「RIGHT OUTER JOIN」的查询需求，就成为「部门名称与该部门的员工资料，没有员工的部门也要出现」：
# 4 合并查询
在关联式资料库中，因为表格的设计，你常会使用结合查询来取得需要的资料，结合查询指的是在「一个」查询叙述中使用「多个」资料表。而现在要讨论的「合并、UNION」查询，指的是把一个以上的查询叙述所得到的结果合并为一个，有这样的需求时，你会在多个查询叙述之间使用「UNION」关键字：
以下列这两个独立的查询来说，它们在执行以后会得到各自传回查询的纪录：
如果使用「UNION」关键字把这两个查询合并起来的话，就只会得到一个查询结果，不过这个查询结果会包含两个查询所得到的纪录：
在执行合并查询的时候，有一些规则要知道与遵守。第一个规则是回传结果的栏位名称：
第二个规则是所有查询叙述的栏位数量一定要一样：
上列的范例比较看不出为什么要使用合并查询，一般来说，你大概会因为下列的原因，把原来的查询叙改用合并查询的写法来完成你的需求：
';
（4） 运算式和函数
最后更新于：2022-04-01 23:39:48
**目录**
[TOC]
# 1 值与运算式
不论在执行查询或资料异动的时候，你都可能会使用各种不同种类的值(literal values)来完成你的工作：
不同种类的值会有不同的用法与规定，可以搭配使用的运算子和函式也不一样。根据资料类型可以分为下列几种：
* 数值：可以用来执行算数运算的数值，包含整数与小数，分为精确值与近似值两种
* 字串：使用单引号或双引号包围的文字
* 日期/时间：使用单引号或双引号包围的日期或时间
* 空值：使用「NULL」表示的值
* 布林值：「TRUE」或「1」表示「真」，「FALSE」或「0」表示「假」
## 1.1 数值
数值分为「精确值(exact-value)」与「近似值(approximate-value)」两种。精确值在使用时不会因为进位而产生差异；使用近似值的时候，可能会因为进位而产生些微的差异。精确值使用一个明确的数字来表示一个整数或小数数值：
* 整数：没有小数的数字，范围从-9223372036854775808到9223372036854775807
* 小数：包含小数的数字，整数范围与上面一样，小数位数最多可以有30个
一般来说，使用精确值在执行各种算数运算的时候，所得到的结果都不会有误差的问题，你只要特别注意范围就可以了。例如下列这个比较奇怪的查询需求：
包含小数的数字，在整数部份的限制与整数相同，小数位数会有这样的限制：
近似值的的数字通常称为「科学表示法」，它使用下列的方式来表示一个数值：
这两种表示方式所代表的数值是这样计算的：
* XE+Y，X * 10Y，例如5E+3，代表的数字为5000
* XE-Y，X * 10-Y，例如5E-3，代表的数字为0.005
注：「XE+Y」格式中的「+」可以省略，例如「5E+3」与「5E3」是一样的。
使用近似值来表示一个数值的时候，你一定要牢记它是一个「近似值」，也就是它真正储存的数值可能不是你所看到的。下列的情况是你比较容易理解的：
不过下列的状况就会有不一样的结果：
第一个运算值采用精确值的方式，所以它们一定会相等；第二个运算使用近似值的方式，所以它们不一定相等。
## 1.2 字串值
字串值是以单引号或双引号包围的文字资料，就文字资料来说，你不会拿文字执行加、减、乘、除这类的算数运算。如果你拿字串来执行算数运算的话，MySQL会先把字串中的内容转换为数字，然后再执行算数运算：
如果字串内容包含不是数值的文字，MySQL在执行转换的时候会出现警告讯息：
字串与字串可以执行连接的运算，就是把一些字串的内容连接起来后，产生一个新的字串。要执行字串连接的工作，可以使用「||」运算子，这个运算子在条件的判断中是「或」的意思，如果你直接使用「||」运算子连接字串的话：
这是因为在预设的设定下，MySQL把「||」运算子当成数值的「或」运算，所以会出现这样的情况；你可以透过设定MySQL的SQL模式，来改变这个预设处理方式：
~~~
SET sql_mode = 'PIPES_AS_CONCAT'
~~~
这个设定会把「||」运算子用在字串值的时候，把它当成「连接」运算子：
注：字串的连接也可以使用函式来处理，在这章的后面讨论；另外字串的比较因为跟编码有关，会在后面的章节详细讨论。
## 1.3 日期与时间值
日期与时间值(temporal values)有下列几种：
* 日期：年年年年-月月-日日，`2007-01-01`
* 日期时间：年年年年-月月-日日 时时:分分:秒秒，`2007-01-01 12:00:00`
* 时间：时时:分分:秒秒：`12:00:00`
在日期与时间值中西元年的部份，可以使用四个或两个数字。如果指定的两个数字是「70」到「99」之间，就代表「1970」到「1999」；如果是「00」到「69」之间，就代表「2000」到「2069」。日期值中预设的分隔字元是「-」，你也可以使用「/」，所以「2000-1-1」与「2000/1/1」都是正确的日期值。
日期时间资料可以使用在条件的判断外，也可以用来「运算」，不过当然不是数值的算数运算，而是「一个日期的36天后是哪一天」这类的运算，而且只能使用「+」与「-」的运算。它的语法是：
语法中的单位可以使用下列表格中的单位关键字：
* YEAR：年
* QUARTER：季
* MONTH：月
* DAY：日
* HOUR：时
* MINUTE：分
* SECOND：秒
注：上列「单位关键字」并没有列出所有的单位关键字，全部的单位关键字请参考MySQL手册「12.5\. Date and Time Functions」。
## 1.4 NULL值
「NULL」值的处理比任何其它型态的值都来得奇怪一些，它也是一个很常见的资料，可以用来表示「未知的资料」；而且它最特别的地方是「NULL值与其它任何值都不一样，包含NULL自己」。
「NULL」是一个SQL关键字，大小写都可以。你已经知道判断一个栏位资料是否为「NULL」值的时候，跟其它一般资料判断是不一样的；如果算数运算式或比较运算式中有任何「NULL」值的话，结果都会是「NULL」：
~~~
SELECT NULL = NULL, NULL < NULL, NULL != NULL, NULL + 3
~~~
上列的查询所得到的结果全部都是「NULL」。所以在比较「NULL」值的时侯要使用下列的方式：
# 2 函式
在你在执行查询或维护资料的时候，可能会有下列这个比较特殊的需求：
以这样的需求来说，你当然不用自己去计算两个日期之间的天数，MySQL提供许多不同的函式(functions)，可以完成这类的需求，不论在执行查询或维护的叙述中，都可以使用这些函式。函式基本的用法会像这样：
注：MySQL规定函式预设的写法是函式名称和左括号之间不可以有任何空格，否则会造成错误；你可以执行`SET sql_mode='IGNORE_SPACE'`，这个设定让你可以在函式名称和左括号之间加入空格也不会出错。
以上列「计算两个日期之间的天数」来说，就会在查询叙述中使用到这样的函式：
MySQL提供的函式非常多，你不用把每一个函式的名称和用法都背起来，就算是为了参加认证考试也一样。这个章节只有介绍「部份」函式，并不是全部，所以你在了解这章讨论的函式以后，需要到MySQL参考手册中的「Chapter 12\. Functions and Operators」，进一步认识MySQL还有提供哪一些函式。
## 2.1 字串函式
字串资料的处理是一种很常见的工作，处理字串的函式也非常多，所以这里使用分类的方式来介绍。下列是处理字串内容的相关函式：
* LOWER(字串)：将[字串]转换为小写
* UPPER(字串)：将[字串]转换为大写
* LPAD(字串1, 长度, 字串2)：如果[字串1]的长度小于指定的[长度]，就在[字串1]左边使用[字串2]补满
* RPAD(字串1, 长度, 字串2)：如果[字串1]的长度小于指定的[长度]，就在[字串1]右边使用[字串2]补满
* LTRIM(字串)：移除[字串]左边的空白
* RTRIM(字串)：移除[字串]右边的空白
* TRIM(字串)：移除[字串]左、右的空白
* REPEAT(字串, 个数)：重复[字串]指定的[个数]
* REPLACE(字串1, 字串2, 字串3)：将[字串1]中的[字串2]替换为[字串3]
「LPAD」与「RPAD」在处理报表资料的时候，很常用来控制报表内容的格式。例如下列的需求：
使用「LPAD」函式让查询后得到的字串内容向右对齐：
下列是截取字串内容的函式：
* LEFT(字串, 长度)：传回[字串]左边指定[长度]的内容
* RIGHT(字串, 长度)：传回[字串]右边指定[长度]的内容
* SUBSTRING(字串, 位置)：传回[字串]中从指定的[位置]开始到结尾的内容
* SUBSTRING(字串, 位置,
长度)：传回[字串]中从指定的[位置]开始，到指定[长度]的内容
下列是一个测试这些函式的查询叙述：
下列是连接字串的函式：
* CONCAT(参数 [,…])：传回所有参数连接起来的字串
* CONCAT_WS(分隔字串, 参数 [,…])：传回所有参数连接起来的字串，参数之间插入指定的[分隔字串]
你可以使用「||」运算子连接字串，「CONCAT」函式也可以完成同样的需求。唯一的差异是要先设定「sql_mode」为「PIPES_AS_CONCAT」后，才可以使用「||」运算子连接字串；而「CONCAT」函式不用执行任何设定就可以连接字串。
「CONCAT_WS」函式提供一种比较方便的字串连接功能，例如下列这个使用「||」运算子连接字串的查询叙述：
改成使用「CONCAT_WS」函式的话，就会比较简单一些：
注：「CONCAT」与「CONCAT_WS」两个函式的参数可以接受任何型态的资料，它们都会把全部的资料转为字串后连接起来；「CONCAT」函式的参数中如果有「NULL」值，结果会是「NULL」；「CONCAT_WS」函式的参数中如果有「NULL」值，「NULL」值会被忽略。
下列是取得字串资讯的函式：
* LENGTH(字串)：传回[字串]的长度(bytes)
* CHAR_LENGTH(字串)：传回[字串]的长度(字元个数)
* LOCATE(字串1, 字串2)：传回[字串1]在[字串2]中的位置，如果[字串2]中没有[字串1]指定的内容就传回0
使用「LENGTH」函式可以完成类似「国家名称长度排行榜」的查询：
注：「LENGTH」与「CHAR_LENGTH」的差异在「第六章、字元集与资料库」与「第七章、储存引擎与资料型态」中会详细的讨论。
如果有需要的话，你也会搭配许多函式来完成你的工作，例如：
上列的叙述可以查询「名称是一个单字以上的国家」。
## 2.2 数学函式
下列是数值舍去与进位的函式：
* ROUND(数字)：四舍五入到整数
* ROUND(数字, 位数)：四舍五入到指定的位数
* CEIL(数字)、CEILING(数字)：进位到整数
* FLOOR(数字)：舍去所有小数
* TRUNCATE(数字, 位数)：将指定的[数字]舍去指定的[位数]
下列是一个测试这些函式的查询叙述：
在这些函式中，「TRUNCATE」函式的用法会比较不一样：
下列是算数运算的函式：
* PI()：圆周率
* POW(数字1, 数字2)、POWER(数字, 数字2)：[数字1]的[数字2]平方
* RAND()：乱数
* SQRT(数字)：[数字]的平方
每次使用「RAND」函式的时候，它都会传回一个大于等于0而且小于等于1的小数数字，通常会把它称为「乱数」，这个数值是由MySQL随机产生的。如果你的叙述中需要一个固定范围内的乱数，可以搭配「RAND」函式套用下列的公式来产生：
使用「RAND」函式也可以完成「随机查询」的需求：
注：MySQL还有提供的许多不同应用的数学函式，例如三角函式，你可以查询MySQL参考手册中的「12.4.2.
Mathematical Functions」。
## 2.3 日期时间函式
下列是取得日期与时间的函式：
* CURDATE()：取得目前日期，相同功能：CURRENT_DATE、CURRENT_DATE()
* CURTIME()：取得目前时间，相同功能：CURRENT_TIME、CURRENT_TIME()
* YEAR(日期)：传回[日期]的年
* MONTH(日期) 数字 传回[日期]的月
* DAY(日期)：传回[日期]的日，相同功能：DAYOFMONTH()
* MONTHNAME(日期)：传回[日期]的月份名称
* DAYNAME(日期)：传回[日期]的星期名称
* DAYOFWEEK(日期)：传回[日期]的星期，1到7的数字，表示星期日、一、二…
* DAYOFYEAR(日期)：传回[日期]的日数，1到366的数字，表示一年中的第几天
* QUARTER(日期)：传回[日期]的季，1到4的数字，代表春、夏、秋、冬
* EXTRACT(单位 FROM 日期/时间)：传回[日期]中指定的[单位]资料
* HOUR(时间)：传回[时间]的时
* MINUTE(时间)：传回[时间]的分
* SECOND(时间)：传回[时间]的秒
「CURDATE」与「CURTIME」可以取得目前伺服器的日期与时间，搭配其它函式就可以完成下列的「建国最久的国家排行」查询：
「EXTRACT」函式用来取得日期时间资料的指定「单位」，例如日期中的月份，使用的「单位」与这一章之前在「日期与时间值」中讨论的一样，这个函式让你不用记太多「YEAR」或「MONTH」这类函式的名称：
下列是计算日期与时间的函式：
* ADDDATE(日期, 天数)：传回[日期]在指定[天数] 以后的日期
* ADDDATE(日期, INTERVAL 数字 单位)：传回[日期]在指定[数字]的[单位]以后的日期
* ADDTIME(日期时间, INTERVAL数字 单位)：传回[日期时间]在指定[数字]的[单位]以后的日期时间
* SUBDATE(日期, 天数)：传回[日期]在指定[天数] 以前的日期
* SUBDATE(日期, INTERVAL 数字 单位)：传回[日期]在指定[数字]的[单位]以前的日期
* SUBTIME(日期时间, INTERVAL数字 单位)：传回[日期时间]在指定[数字]的[单位]以前的日期时间
* DATEDIFF(日期1, 日期2)：计算两个日期差异的天数
在计算日期方面的函式，MySQL也提供两种不同的用法：
上列函式中使用的「单位」与这一章之前在「日期与时间值」中讨论的一样。
## 2.4 流程控制函式
在处理一般工作的时候，使用各种SQL叙述与函式，通常就可以完成你的需求；可是在实际的应用上，难免会遇到类似下列这样比较复杂一点的需求：
像这种依照条件判断结果而显示不同资料的需求，可以使用下列这个「IF」函式来处理：
使用「IF」函式可以在查询的时候，依照员工进公司的日期判断是资深或是一般员工：
如果要依照资深员工与一般员工计算不同的奖金，也可以使用「IF」函式来完成：
「IF」函式可以用来判断一个条件「成立」或「不成立」两种状况的需求；但是像下列的需求就不适合使用「IF」函式了：
如果要完成多种条件的判断，就要使用下列的「CASE」语法，它应该不能算是一个函式，因为它的长像实在不像是一个函式：
套用上列的语法，就可以判断出所有员工的新资等级：
在「CASE」的语法中，要判断一种条件就使用一个「WHEN」来完成；如果有「所有条件以外」的情况要处理的话，就可以使用「ELSE」来处理：
如果要依照员工新资等级计算不同的奖金，也可以使用「CASE」语法来完成这个需求：
「CASE」除了上列介绍的语法外，还有另外一种写法可以处理一些比较特别的需求，例如下列七大洲的名称与缩写对照表：
* Asia：AS
* Europe：EU
* Africa：AF
* Oceania：OA
* Antarctica：AN
* North America：NA
* South America：SA
如果要在SQL叙述中有类似这样的需求，就可以使用下列这种「CASE」的语法：
套用上列的语法就可以完成这样的查询：
以上列的查询来说，你也可以换成这样的写法：
~~~
SELECT Name, Continent,
CASE
WHEN Continent='Asia' THEN 'AS'
WHEN Continent='Europe' THEN 'EU'
WHEN Continent='Africa' THEN 'AF'
WHEN Continent='Oceania' THEN 'OA'
WHEN Continent='Antarctica' THEN 'AN'
WHEN Continent='North America' THEN 'NA'
WHEN Continent='South America' THEN 'SA'
END ContinentCode
FROM country
~~~
经由这样的对照，应该可以很容易看得出来，使用哪一种写法来完成这个查询会好一些。
## 2.5 其它函式
* IFNULL(参数, 运算式)：如果[参数]为NULL就传回[运算式]的值；否则传回[参数]的值
* ISNULL(参数)：如果[参数]为NULL就传回TRUE；否则传回FALSE
当资料库中有「NULL」资料出现的时候，就可能会发生下列这样奇怪的结果：
所以要得到正确的结果，就要使用「IFNULL」函式来特别处理NULL值的运算：
「ISNULL」函式用来判断一个指定的资料是否为「NULL」，它的效果跟之前在「第二章、基础查询、条件比较」中讨论的「IS」和「」运算子是一样的，你可以自己决定要使用哪一种来执行判断。
# 3 群组查询
资料库通常是用来储存庞大数量的资料，这也是它最善长跟主要的工作，所以查询并计算资料的统计分析资讯也是一种很常见的需求：
你也可能会进一步的查询更详细的统计与分析资讯：
## 3.1 群组函式
想要完成上列讨论的统计与分析查询，你会用到下列的「群组函式」：
* MAX(运算式)：最大值
* MIN(运算式)：最小值
* SUM(运算式)：合计
* AVG(运算式)：平均
* COUNT([DISTINCT]*|运算式)：使用「DISTINCT」时，重复的资料不会计算；使用[*]时，计算表格纪录的数量：使用[运算式]时，计算的数量不会包含「NULL」值
使用上列的群组函式可以很容易的查询需要的统计与分析资讯：
这些函式套用在数值资料时会比较明确一些，把它们用在日期资料也是可以完成「员工最早和最晚进公司的日期」的查询需求：
在这些群组函式中，「COUNT」函式的用法会比较不一样：
利用「COUNT」函式的特性，也可以查询一些特别的资讯：
## 3.2 GROUP_CONCAT函式
「GROUP_CONCAT」函式是比较特别的一个群组函式，它用来将一些字串资料「串接」起来。在执行一般查询的时候，会根据查询的资料，将许多纪录传回来给你：
使用「GROUP_CONCAT」函式的话，只会回传一笔纪录，这笔纪录包含所有字串资料串接起来的内容：
下列是「GROUP_CONCAT」函式的语法：
上列的范例是「GROUP_CONCAT」函式最简单的用法，你还可以在函式中使用与「ORDER BY」子句一样的用法来指定资料的排列顺序：
「GROUP_CONCAT」函式连接字串的时候，预设是使用逗号分隔资料，你可以自己指定分隔的字串：
在「GROUP_CONCAT」函式中还可以使用类似在「基础查询、限制查询」中讨论过的「DISTINCT」来排除重复的资料，例如：
在「GROUP_CONCAT」函式中使用「DISTINCT」也会有同样的效果：
## 3.3 GROUP BY与HAVING子句
在上列使用群组函式的所有范例中，都是将「FROM」子句中指定的表格当成是一整个「群组」，群组函式所处理的资料是表格中所有的纪录。如果希望依照指定的资料来计算分组统计与分析资讯，在执行查询的时候，可能会有下列几种不同的结果：
上列的范例使用「GROUP BY」子句指定分组的设定，下列是分组查询中的语法：
「GROUP BY」子句指定是依照你自己的需求来决定的，同样以人口数量合计来说，不同的指定可以得到不同的统计资讯：
使用不同的群组函式，就可以得不同的资讯：
如果需要的话，你可以在一个查询中，一次取得所有需要的统计与分析资讯：
在查询群组统计与分析资讯的时候，你可以指定多个群组设定取得更详细的资讯：
使用「GROUP BY」指定群组的设定以后，回传的群组查询资料都会依照指定的群组排序，预设定排序方式是递增排序，使用「DESC」关键字可以指定排序的方式为递减排序：
使用「GROUP BY」子句的时候可以搭配「WITH ROLLUP」：
使用「WITH ROLLUP」以后，效果会作用在查询中的每一个群组函式：
在「GROUP BY」子句中有多个群组设定的时候，你可以在最后面加入「WITH ROLLUP」：
在执行群组查询的时候，一般的条件设定同样使用「WHERE」子句就可以了：
可是以类似上列的查询来说，把查询条件从「亚洲的地区」换成「人口合计大于一亿的地区」，如果还是把条件设定放在「WHERE」子句的话：
包含群组函式的条件设定就一定要放在「HAVING」子句中
依照需求在执行群组查询的时候，应该不会出现下列的查询叙述：
MySQL资料库在执行上列的查询叙述后，并不会产生任何错误，为了预防这样的状况，你可以执行下列的设定：
~~~
SET sql_mode = 'ONLY_FULL_GROUP_BY'
~~~
在「sql_mode」的设定中加入「ONLY_FULL_GROUP_BY」，表示多了下列的规定：
如果查询叙述违反「ONLY_FULL_GROUP_BY」的规定，就会产生错误讯息：
';
（3） SELECT 基础查询
最后更新于：2022-04-01 23:39:46
**目录**
[TOC]
# 1 查询资料前的基本概念
## 1.1 表格、纪录与栏位
表格是资料库储存资料的基本元件，它是由一些栏位组合而成的，储存在表格中的每一笔纪录就拥有这些栏位的资料。以储存城市资料的表格「city」来说，设计这个表格的人希望一个城市资料需要包含编号、名称、国家代码、区域和人口数量，所以他为「city」表格设计了这些「栏位(column)」：
储存在表格中的每一笔资料称为「列(row)」或「纪录(record)」：
在设计表格的时候，通常会指定一个栏位为「主索引键(primary key)」：
注：主索引键会在「第八章、表格与索引」中详细的讨论。
## 1.2 认识资料型态
资料库中可以储存各种不同的资料，SQL提供许多不同的「资料型态」让你应付这些不同的需求。在开始查询资料之前，你要先认识最常见、也是最基本的资料型态。第一种是数值，为了更精准的保存数值资料，SQL提供整数与小数两种数值型态：
你可以依照自己的需求，使用储存的数值资料执行数学运算：
常用的资料型态还有「字串」与「日期」：
在SQL叙述中使用字串资料的时候，字串资料的前后要使用单引号或双引号：
使用日期资料的时候，MySQL资料库预设的日期格式是「年-月-日」。与字串资料一样，前后也要使用单引号或双引号：
注：字串与日期资料型态会在「第七章、储存引擎与资料型态、栏位资料型态」中详细的讨论。
另外一种在资料库中比较特殊的资料型态是「NULL」，它不像数值、字串或日期资料型态是一个明确的资料，「NULL」是用来表示「不确定」、「未知」或「没有」的资料：
# 2 查询叙述
在执行资料库的操作中，查询算是最常见也是最复杂的工作，所以一个查询叙述所使用到的子句也最多，下列是查询叙述的基本语法：
这一章会讨论「SELECT」、「FROM」、「WHERE」、「ORDER BY」和「LIMIT」五个子句组合起来的查询叙述。其它的子句会在下一章继续讨论。
在你使用「SELECT」搭配各种子句来查询资料时，要特别注意子句使用的顺序：
就算你每一个子句的写法都没有出错，如果顺序不对了：
## 2.1 指定使用中的资料库
一个资料库伺服器可以建立许多需要的资料库，所以在你执行任何资料库的操作前，通常要先指定使用的资料库。下列是指定资料库的指令：
如果你使用「MySQL Workbench」这类的工具软体，画面上看起来会像这样：
## 2.2 只有SELECT
一个SQL查询叙述一定要以「SELECT」子句开始，再搭配其它的子句完成查询资料的工作。你可以单独使用「SELECT」子句，只不过这样的用法跟资料库一点关系都没有，它只不过把你输入的内容显示出来而已：
例如下列的查询叙述，只是简单的显示字串和计算结果，并不会查询资料库中的资料：
~~~
SELECT 'My name is Simon Johnson', 35 * 12
~~~
## 2.3 指定栏位与表格
一般所谓的查询叙述，通常是查询资料库中的资料，所以「SELECT」子句会搭配「FROM」子句来使用，而「SELECT」后面可以指定「*」表示要查询指定表格的所有栏位：
如果目前使用中的资料库为「world」，下列的叙述可以查询「world」资料库中，「city」表格的所有资料：
~~~
SELECT * FROM city
~~~
一个资料库伺服器可以建立许多需要的资料库，所以在你执行任何资料库的操作前，都要使用「USE」叙述指定一个使用中的资料库。不过你也可以在SQL叙述中使用下列的语法来指定资料库：
如果目前使用中的资料库是「world」，你不用先使用「USE cmdev」叙述切换使用中的资料库，可以使用下列的语法查询「cmdev」资料库中的「emp」表格：
~~~
SELECT * FROM cmdev.emp
~~~
## 2.4 指定需要的栏位
有时候你并不需要查询一个表格中所有的栏位，所以你可以在「SELECT」子句后面自己指定需要的栏位：
如果你在「SELECT」后面使用「*」的话：
你可以依照自己的需要决定要查询哪些栏位和顺序：
## 2.5 数学运算
除了查询表格中的栏位外，你可以加入任何需要的运算，这里先讨论一般常见的数学运算。下列是很常用来执行数学运算的运算子：
| 优先顺序 | 运算子 | 说明 | 范例 | 运算结果 |
| --- | --- | --- | --- | --- |
| 1 | % | 余数 | 7 % 3 | 1 |
| 1 | MOD | 余数 | 7 MOD 3 | 1 |
| 1 | * | 乘 | 7 * 3 | 21 |
| 1 | / | 除 | 7 / 3 | 2.333 |
| 1 | DIV | 除（整数） | 7 DIV 3 | 2 |
| 2 | + | 加 | 7 + 3 | 10 |
| 2 | - | 减 | 7 – 3 | 4 |
注：优先顺序的数字从1开始，1表示优先权比较高，2比较低，以此类推。就跟一般数学运算的先乘除后加减一样：在一个运算式中，优先权高的先算完，再换低优先权继续算；同样优先权的就由左到右计算。你也可以在运算式中使用左右括号，括号中的运算会先执行。
以「cmdev」资料库中的员工表格(emp)来说，想要计算员工的年薪，就可以使用这些运算子来完成你的查询工作：
## 2.6 别名
你可以另外为「SELECT」后面查询的资料取一个自己想要的名称，这个作法称为「别名(alias
name)」：
取栏位别名会让执行查询后的结果，使用你自己取的名称为栏位名称：
注：帮一般栏位取一个栏位别名是比较没有必要的，如果是运算式的话，通常就要帮它取一个栏位别名来取代原来一大串的运算式。
在取栏位别名的时候要特别注意下列的状况：
另外如果你「坚持」要使用SQL语法中的保留字来当作栏位别名的话：
如果违反上列两个规定，执行叙述以后会发生错误。
# 3 条件查询
使用「SELECT」和「FROM」执行的查询叙述，是把你在「FROM」子句指定表格里所有的纪录传回来。资料库最大的好处就是可以随时依照需要查询部份纪录资料，你可以搭配「WHERE」子句执行查询条件的设定：
## 3.1 比较运算子
要使用「WHERE」执行查询条件的设定，你会使用下列基础的比较运算子：
|优先顺序 |运算子| 说明|
|1| =| 等于|
|1| ```| 等于|
|1| ``!=`| 不等于|
|1| ``| 大于|
|1| `>＝`| 大于等于|
注：``运算子在后面「NULL值的判断」会讨论。
使用这些基础的比较运算子就可以完成一些简单的条件设定：
设定日期资料型态的条件也是很常见的：
## 3.2 逻辑运算子
查询条件的设定，有时候会像前面讨论的单一条件一样，并不会太复杂；不过也很常遇到在一个查询的需求中，需要设定一个以上的条件，那你就会用到下列的运算子：
| 优先顺序 | 运算子 | 说明 |
| --- | --- | --- |
| 1 | NOT | 非 |
| 2 | && | 而且 |
| 2 | AND | 而且 |
| 3 | ` | | ` | 或 |
| 3 | OR | 或 |
| 3 | XOR | 互斥 |
「NOT」运算子比较特殊一些，在一般的需求中，比较不会用到它。以下列的需求来说：
如果想要查询国家代码是「TWN」，而且人口数量小于十万的城市，就必须设定两个条件，而两个条件之间，依照「而且」的需求，使用「AND」来结合两个条件：
如果想要查询国家代码是「TWN」或是「USA」的城市，在两个条件之间依照「或」的需求，使用「OR」来结合两个条件：
在逻辑运算子的介绍中，它们也同样有「优先顺序」的。如果你想要查询在欧洲(Europe)或非洲(Aftica)国家，而且人口数要小于一万。使用下列的查询条件所得到的资料，跟你想要的却不一样：
如果有多个查询条件的设定，全部都是「AND」或全部都是「OR」的话，就没有这类问题；如果查询条件中，有「AND」和「OR」同时出现的话，就要依照你的需要，视情况加上左右刮号来控制条件的设定：
## 3.3 其它条件运算子
一般的条件和逻辑运算子，已经可以应付大部份的查询条件需求。下列还有一些可以用在特殊用途或是提供替代写法的条件设定：
* BETWEEN … AND …：范围比较
* IN (…)：成员比较
* IS：是…
* IS NOT：不是…
* LIKE：像…
「BETWEEN … AND …」用来执行一个指定范围条件的设定：
如果要查询人口数量在八万到九万之间的城市资料，可以有下列两种条件的写法，它们执行以后的结果是完全一样的：
使用「BETWEEN … AND …」的条件设定会包含指定的资料，所以下列两个查询条件所得到的结果就不一样了：
「BETWEEN … AND …」使用在日期资料时，也可以完成某一个日期范围的判断：
「IN (…)」使用在一组成员资料的比对条件设定：
下列两个查询叙述，都可以得到国家代码是「TWN、USA、JPN、ITA和KOR」的城市资料，可是使用「IN (…)」来设定条件的话，看起来会简洁很多：
## 3.4 NULL值的判断
在国家表格中，有一个储存平均寿命的栏位「LifeExpectancy」，不过资料库中的资料并没有很完整，所以有一些国家是没有这个资料的，所以会使用「NULL」值来表示：
如果想要查询没有平均寿命资料的国家，也就是平均寿命的栏位值是「NULL」，你可能会使用下列的叙述：
~~~
SELECT Name, LifeExpectancy
FROM country
WHERE LifeExpectancy = NULL
~~~
上列的叙述执行以后，并没有传回任何纪录，这表示并没有资料符合你设定的查询条件。
所以「NULL」值的判断，不可以使用判断一般资料的条件设定：
注：``在判断一般资料的时候，跟「=」完全一样；不过它用在判断「NULL」资料的时候，效果跟「IS」一样。
如果换成要查询「有」平均寿命资料的国家，也就是平均寿命的栏位值不是「NULL」：
## 3.5 字串样式
在使用字串资料的条件判断时，会有一种很常见、也比较特殊的需求，像是「想要查询名称以w字元开始的城市」，如果你使用下列的查询叙述：
~~~
SELECT Name FROM city WHERE Name = 'w'
~~~
这样的查询条件，当然不是「名称以w字元开始的城市」，而是名称只有一个「w」字元的城市。所以这类的查询就会使用下列这个特殊的条件设定：
上列语法中，在「LIKE」后面的「样版」字串中，会使用到下列两种「样版字元」：
* %：0到多个任何字元
* _ ：一个任何字元
所以要查询「名称以w字元开始的城市」的话：
参考上列的作法，就可以延伸出其它的查询条件设定了：
上列的查询条件中，「w%」表示第一个字元是「w」就符合条件；「%w」表示最后一个字元是「w」就符合条件；最后一个「%w%」表示不论在什么位置有「w」字元，都符合条件。
另外一种样版字元「_」表示一个任何字元：
把这些样版中的底线换到后面的话：
你也可以搭配两种样版字元完成条件的设定：
甚至像查询「名称是三十(包含)个字元以上的城市」：
注：其实完成上列的查询条件的需求是不用这么麻烦的，在后面的章节会讨论比较简单的方式。
# 4 排序
在你执行任何一个查询以后，MySQL传回的资料是依照「自然」的顺序排列的。所谓的自然顺序，通常是资料新增到表格中的顺序，可是在资料库运作一段时间后，陆续会有各种不同的操作，所以这个「自然」顺序对你来说，通常是没什么意义的。
一般的查询通常会有资料排序上的需求，所以你会使用「ORDER BY」子句：
如果你希望在查询城市资料的时候，资料库会依照国家代码帮你排序的话：
你也可以指定资料排列的顺序为由大到小：
「ORDER BY」子句后面可以依照需求指定多个排序的资料：
「ORDER BY」子句后面指定多个排序资料的时候，都可以依照需求，各自指定资料排列的方式：
「ORDER BY」子句指定的资料可以是栏位名称、编号、运算式或是栏位别名：
虽然比较不会有下列这样的需求，不过你还是可以这样作：
注：资料排列的顺序在「第六章、字元集与资料库」与「第七章、储存引擎与资料型态」中进一步详细的讨论。
# 5 限制查询
## 5.1 指定回传纪录数量
在你执行一个查询叙述后，资料库会将你查询的资料传回来给你；如果你使用「WHERE」子句设定查询条件的话，资料库就只会传回符合条件的资料；除了上列的状况外，你也可以另外使用「LIMIT」子句指定回传纪录的数量：
如果你在「LIMIT」子句后面指定一个数字：
「LIMIT」子句后面也可以指定两个数字：
在查询叙述中，使用「ORDER BY」子句搭配「LIMIT」子句，就可以完成下列查询「排名」的工作：
注：如果出现类似「… LIMIT 1000000, 10」这样的查询叙述，虽然你只会得到十笔资料，资料库总共会查询一百万零一十笔资料，只不过资料库会帮你跳过前一百万笔；类似这样的需求，还是要使用「WHERE」子句先挑出想要的资料会比较好一些。
## 5.2 排除重复纪录
在一个查询叙述执行以后，资料库不会帮你检查回传的资料是否重复(回传的两笔纪录资料完全一样)，在「SELECT」子句后面可以让你设定「回传的资料是否重复」：
没有使用「ALL」或「DISTINCT」的效果，跟你自己加上「ALL」的查询效果是一样的，资料库会依照你的查询传回所有的资料：
使用「DISTINCT」的话，资料库会特别执行回传纪录是否重复的检查：
';
（2） 数据库概论和 MySQL 安装
最后更新于：2022-04-01 23:39:43
**目录**
[TOC]
# 1、存储与管理资料
储存与管理资料一直是资讯应用上最基本、也是最常见的技术。在还没有使用电脑来管理你的资料时，你可能会使用这样的方式来保存世界上所有的国家资料：
这样的作法在生活中是很常见的，例如亲友的通讯录，你可能也会使用一张卡片来记录一个亲友的通讯资料，上面有名字、电话、住址，与所有你想要保存的资料。这种保存资料的方式很直接，也很省钱。不过你应该会遇到这样的问题：
如果你买了一台电脑，电脑中也安装了一种工作表的软体，像这类国家或是亲友通讯录的资料，可能就会用这样的方式把它们储存在电脑里面：
使用这种工作表来储存国家资料，当然比用卡片好多了，尤其是想要寻找某个国家的资料，然后修改它的人口数量。虽然方便多了，不过在你查询国家资料时，可能会有这样的问题：
你不太可能把一个洲的国家资料，储存为一个工作表档案；就算你这么作了，如果你想要查询人口数小于十万的国家时，你会发现这会是一件很困难的工作。
## 1.1 资料库管理系统与资料库伺服器
在资讯的应用软体中，「资料库管理系统」是一种用来储存与管理资料的软体，它使用安全、稳定与有效率的方式把资料储存起来，也可以方便与快速的维护资料。尤其是资料的数量很庞大的时候，使用资料库管理系统来储存与管理资料，会是一种令人安心而且比较有效率的方式。
资料库管理系统是一种软体程式，它主要的工作就是储存与管理资料，如果你把这个软体程式安装在一台电脑中，这台电脑就会称为「资料库伺服器」：
在你有了一台资料库伺服器以后，你就可以依照自己的需求，使用资料库管理系统建立一些资料库：
## 1.2 资料库
在使用资料库前，要先在资料库伺服器中建立需要的「资料库、database」，你会依照自己的需求，建立一个或多个资料库：
各种资料库伺服器软体通常会提供一些用户端软体程式，让使用者可以输入与执行SQL叙述，或是执行管理与设定资料库的工作：
以储存世界资料的资料库来说，你想要把世界上所有的国家、城市和语言资料，在这个资料库中储存与管理。所以你会针对国家资料的部份，在世界资料库中建立一个储存国家资料的「表格、table」：
储存在世界资料库中的国家资料，随时可以依照不同的需求，查询需要的国家资料：
除了国家表格外，你还会在世界资料库中建立储存城市和语言资料的表格：
# 2\. SQL介绍
有许多厂商开发各种不同的资料库管理系统产品，它们都可以执行储存与管理资料的工作，而且使用的方式都是差不多的。执行资料储存与管理的工作，主要有建立资料库与表格，和执行资料的新增、修改、删除与查询。想要请资料库管理系统执行这些工作，你会使用一种叫作「Structured Query Language、SQL」的叙述，一般会把「SQL」念为「sequel」。
SQL在很久以前就已经是一种标准的技术，不同的资料库管理系统产品，在执行资料库的工作时，使用的SQL的叙述几乎是一样的：
SQL有一套国际通用的标准，里面规定了所有执行资料库工作的SQL叙述要怎么写，不同的资料库管理系统产品都会以这套标准为基础。不过不同的产品通常会增加或修改一些SQL叙述，其它的资料库管理系统就不认识这些SQL叙述了。
与资料库伺服器相对的是「用户端、client」，跟资料库伺服器比起来，用户端就会比较复杂一些：
使用像是Java程式设计技术开发的各种应用程式，例如进销存系统或会计系统，对资料库伺服器来说，也算是一种用户端软体：
不论是哪一种用户端软体，它们都是使用SQL叙述跟资料库沟通：
# 3\. MySQL Workbench
MySQL提供的工具软体，在这几年有很大的进步，目前已经把所有常用的软体整合在一起，称为MySQL Workbench，里面包含：
* SQL Development：SQL开发工具，让使用者输入并执行SQL叙述
* Database Design Modeling：资料库设计与模型工具
* Database Administration：资料库管理工具
* Database Migration：资料库转换工具
SQL Development是这个系列文章使用的工具软体，使用这个内建的工具，可以很方便输入需要执行的SQL叙述，并检视执行后的结果：
Database Design Modeling是一个图形化的资料库设计工具，可以帮助开发人员设计需要的资料库，或是产生资料库模型的文件：
Database Administration可以提供开发人员执行管理MySQL资料库的基本功能，也可以监控资料库的状态：
# 4\. 下载与安装MySQL资料库
如果你已经安装过MySQL资料库和可以输入和执行SQL叙述的软体，接下来的内容就可以忽略，直接到第五节安装范例资料库就可以了。
MySQL的官方网站目前提供一个完整的安装程式，在Windows平台只要下载与安装一个档案，就包含资料库伺服器和所有需要的工具软体，包含这里需要使用的MySQL Workbench。你可以到这个连结准备开始下载：
[http://dev.mysql.com/downloads/windows/installer/](http://dev.mysql.com/downloads/windows/installer/)
进入这个网站以后，参考下面的说明，下载与储存完整的安装档案：
下载完成后，执行安装程式，选择开始安装并同意版权声明后，在选择安装种类的画面选择Developer Default：
后面的步骤依照画面的指示，选择Execute或Next，就会进入开始安装的步骤。安装完成后，就可以准备进入设定MySQL资料库的步骤：
依照画面的指示，选择Next进入设定资料库管理员(root)密码的步骤，输入一个你自己决定的密码：
依照画面的指示，选择Next完成设定资料库的工作。在最后完成安装与设定的步骤，勾选Start MySQL Workbench after Setup选项后，选择Finish结束安装与设定MySQL资料库的工作。
安装程式会启动MySQL Workbench，依照下面的说明，准备设定资料库连线的基本资讯：
选择下面画面说明的按钮：
在出现的对话框中输入在安装过程中决定的密码：
选择Test Connection按钮：
如果出现这样的画面，表示可以正确的连线到MySQL资料库：
在MySQL Workbench主画面选择Connect：
连线到资料库后，在左侧的World资料库名称上点两下(Double click)，会发现World会变成粗体字，表示目前开启(作用中)的资料库。在画面中输入一个测试的SQL叙述，SELECT * FROM country。输入完后，按下执行叙述的快速键Ctrl + Enter，就可以看到所有的国家资料：
# 5\. 安装范例资料库
完成前面的安装与设定工作后，MySQL资料库伺服器中已经有一个内建的范例资料库world，后面的文章会使用这个资料库讨论与说明一些主题。不过因为这个资料库比较简单一些，所以要请你安装另外一个范例资料库，后面的文章讨论到一些不同的主题时，就会用到这个额外的范例资料库。
在下面的连结按滑鼠右键后，选择另存连结，下载与储存一个建立资料库的SQL Script档案：
[https://dl.dropboxusercontent.com/u/61562257/cmdev.sql](https://dl.dropboxusercontent.com/u/61562257/cmdev.sql)
在MySQL Workbench中选择File->Open SQL Script，选择刚才下载与储存的档案，就可以看到像这样的画面：
在MySQL Workbench中选择Query->Execute(All or Selection)，Workbench会花一点时间执行所有的叙述。执行完成后，在资料库列表区块的任何空白位置，按滑鼠右键后选择Refresh All，就可以看到安装好的新资料库cmdev：
完成所有准备工作，下一篇文章就可以开始进入SQL的世界了。
';
（1） 重新开始
最后更新于：2022-04-01 23:39:41
> 原文出处：http://www.codedata.com.tw/tag/mysql/
> 作者：[張益裕](http://www.codedata.com.tw/author/michael)
> 注：在转载编辑的过程中，对标题部分做了一些简体中文的修正，并增加了目录导航。
# 三国演义
三国演义是一部在华人世界非常普及的历史小说，是由罗贯中根据元朝的三国志平话改编，他以东汉末年魏、蜀、吴三国斗争为主题，收集历史资料和说书人的故事，成为这一部大家都非常熟悉的故事。或许我们现在觉得这些历史已经跟我们没什么关系了，不过大家都知道关公过五关斩六将，刘备三顾茅芦，诸葛孔明的空城记。这些老掉牙的故事，总是不断的出现在电影、电视剧和各种平台的游戏，一代又一代的传承下去。这应该是因为三国演义的确是一个好故事，很多很精采的好故事，就像美国畅销作家史帝芬金所说的，一个好故事是不会寂寞的。
三国演义的普及，让人认为里面讲的故事其实就是真的历史，罗贯中在编这本书的时候，大概是为了让它可以比较戏剧化一些，采用了很多当时说书人的内容，这些内容是在民间流传或由说书人编造的，跟历史并不一样。例如大家熟悉的关公斩华雄，在三国演义中是一段非常的精采故事，作者使用很短的内容让关云长的豪勇，简单、清楚而且非常震憾的呈现给读者。不过根据史料的考证，其实华雄的头是被孙坚砍掉的。这也是为什么清朝的时候就有人评论三国演义是「七实三虚，惑乱观者」。
提倡白话文的胡适之，对三国演义的批评更是激烈，他认为三国演义把诸葛亮的足智多谋写成一个呼风唤雨的妖道；张飞在历史上其实是一个很有君子风度的武将，可是却被写成粗鲁的莽夫。虽然有很多精采的故事，可是没有经过更好的整理，所以三国演义在华人古典文学上的地位，一直不如红楼梦（注），甚至连水浒传都比不上。
# MySQL与SQL
MySQL在资讯应用的角色，好像跟三国演义这本著作有点类似。MySQL是目前最普及的资料库伺服器，可是大家也最不在意它，可能因为它是一套免费的软体，如果不要对它太过份，它会默默的在电脑中为你服务，在一般情况下都不太会出问题。MySQL跟其它一般的资料库一样，同样支援ANSI SQL92，也加入少许MySQL自己特别的指令。不论是网页或应用程式的开发人员，当你第一次接触资料库，学习SQL这种古老的指令，应该不会觉得太难。如果你正要进入开发应用程式的领域，在学习的路上，你会分配给SQL的时间应该也不会太多，因为它跟程式语言比较起来是比较单纯一些的。
因为MySQL和SQL几乎是最常见的应用，而且大家也觉得它们是简单的，当然就不会在它们身上花太多时间。所以慢慢的我们会发现一些情况，有一些应用程式发生的问题，其实是来自MySQL资料库伺服器和应用程式中的SQL叙述，这些问题相对是比较单纯的，只是大家忽略了。
例如MySQL提供方便好用的「LIMIT」子句，在应用程式中让开发人员可以很容易完成一些特定的功能，例如网页应用程式中的分页查询。不过LIMIT子句是MySQL才有的，如果应用程式更换资料库伺服器，例如Oracle，应用程式就会产生一堆错误了。还有资料库的交易（transaction）管理，MySQL预设的MYISAM储存引擎并没有支***易管理，因为比较简单一些，所以运作的效率也会比较好；如果应用程式需要执行交易管理，就要在建立资料库的时候指定储存引擎为InnoDB。
各种关于MySQL资料库管理和SQL的问题，开发人员通常在遇到错误的时候，才会开始寻求解决问题的方法。这似乎也是MySQL的宿命，因为我们虽然一直在使用它，可是却不太重视它，也认为这本来就是合理的，开发人员不应该分配太多时间给它。有一个很明显的情况，在逛书局的时候，你应该已经看不到只有讨论关于MySQL和SQL的书籍了。
# OCP MySQL 5 Developer
在我们台湾这里，跟开发人员相关的认证考试，这应该算是最冷门的OCP认证科目之一。这个认证考试的主要内容是MySQL的SQL，通过这个考试的人，表示它具备在应用程式中使用SQL的技能。你应该会觉的这是一个有点诡异的认证考试，它好像没有存在的必要。对一个有经验的开发人员来说，使用SQL的技能就像是本来就应该存在的，你甚至已经忘记当初是怎么学会SQL；对一个新手来说，不会有人建议你去买一本关于SQL的书籍来学习这方面的技能，因为可能也买不到了，不过有各种网站提供SQL的学习，认识一些基础的叙述后，遇到问题再说吧！
SQL在目前的环境下，越来越不受到开发人员的关爱，尤其是现在各种关于资料库应用的框架，例如Hibernate和MyBatis，它们的任务就是要杀死SQL这只远古巨兽，让开发人员不用受到SQL的煎熬。我也认为开发应用程式一直是一件很困难的事情，各种越来越进步的科技让生活更方便，可是应用程式开发技术却越来越复杂，开发人员必须具备的技能也更多，如果真的能有一种技术可以完全消灭SQL，那绝对是一件非常美好的事情。不过目前的情况应该还是有很多困难，就以大约十年前的应用程式来说，SQL还是一个必要的成员，除非放弃原来已经运作正常的程式，否则你还是要面对这些冗长的SQL叙述。
这就是「MySQL超新手入门」系列文章的目的，内容的范围涵盖OCP MySQL 5 Developer认证考试，因为它的范围也是一个开发人员必须具备的SQL技能。从安装MySQL资料库与相关的工具程式开始，到学习所有MySQL提供的SQL，虽然是针对MySQL资料库撰写的，不过绝大部份都符合ANSI SQL92的标准，也就是在其它资料库产品也可以正确的运作。
内容规划为19章：
1. [数据库概论与 MySQL 安装](database-mysql-installation)
2. [SELECT 基础查询](basic-query)
3. [表达式和函数](expression-function)
4. [JOIN 和 UNION 查询](join-union)
5. [CRUD 和数据维护](crud-maintenance)
6. [字符集和数据库](charset-database)
7. [储存引擎和数据类型](charset-database)
8. [表格和索引](table-index)
9. [子查询](subquery)
10. [视图](views)
11. [预处理语句](prepared-statements)
12. [存储过程入门](stored-routines)
13. [存储过程的变量和流程](stored-routines-variable-flow)
14. [存储过程进阶](stored-routines-advanced)
15. [触发器](triggers)
16. [资料库资讯](information_schema)
17. [错误处理和查询](errors-warnings)
18. [导入和导出数据](outfile-dump-infile-import)
19. [性能](performance)
在第一章介绍基本的资料库概念与安装需要的软体后，第二章到第五章讨论基本的新增、修改、删除和查询；第六章到第八章讨论资料库、表格和索引的建立与管理，这个部份的内容会有比较多MySQL独有的特色；第九章是子查询；第十章到第十五章讨论资料库进阶的应用，这些在其它资料库产品都会提供类似的技术，例如Oracle的PL/SQL；第十六章到第十九章讨论的内容比较偏向于资料库管理和效率的进阶应用，这些也是一个开发人员需要了解的。
**注：** 红楼梦在文学上的重视让它演变成一门「红学」，可是红楼梦的故事与人物对一般人来说，却不如三国演义来得熟悉
**参考资料：**
* 三国演义校注，罗贯中原著，吴小林校注，里仁书局
* 三国演义的文学特质及其悲剧艺术，罗龙治
* [](http://www.oracle.com/)[http://www.oracle.com](http://www.oracle.com/)
';