|
无论是在小得可怜的免费数据库空间或是大型电子商务网站，合理的设计表结构、充分利用空间是十分必要的。这就要求我们对数据库系统的常用数据类型有充分的认识。下面我就将我的一点心得写出来跟大家分享。 
数字类型按照我的分类方法分为三类：整数类、小数类和数字类。 我所谓的‚数字类?就是指DECIMAL和NUMERIC，它们是同一种类型。它严格的说不是一种数字类型，因为他们实际上是将数字以字符串形式保存的；他的值的每一位（包括小数点）占一个字节的存储空间，因此这种类型耗费空间比较大。但是它的一个突出的优点是小数的位数固定，在运算中不会‚失真，所以比较适合用于‚价格?金额?这样对精度要求不高但准确度要求非常高的字段。 小数类，即浮点数类型，根据精度的不同，有FLOAT（单精度）和DOUBLE（双精度）两种。它们的优势是精确度，FLOAT可以表示绝对值非常小、小到约1.17E-38 （0.000...0117， 小数点后面有37个零）的小数，而DOUBLE更是可以表示绝对值小到约 2.22E-308 （0.000...0222， 小数点后面有307个零）的小数。FLOAT类型和DOUBLE类型占用存储空间分别是4字节和8字节。如果需要用到小数的字段，精度要求不高的，当然用FLOAT了！可是说句实在话，我们‚民用?的数据，哪有要求精度那么高的呢 这两种类型至今我没有用过——我还没有遇到适合于使用它们的事例。 用的最多的，最值得精打细算的，是整数类型。从只占一个字节存储空间的TINYINT到占8个字节的BIGINT，挑选一个‚够用?并且占用存储空间最小的类型是设计数据 库时应该考虑的。TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT占用存储空间分别为1字节、2字节、3字节、4字节和8字节，就无符号的整数而言，这些类型能表示的最大整数分别为255、65535、16777215、4294967295和18446744073709551615。如果用来保存用户的年龄（举例来说，数据库中保存年龄是不可取的），用TINYINT就够了；九城的《纵横》里，各项技能值，用MALLINT也够了；如果要用作一个肯定不会超过16000000行的表的AUTO_INCREMENT的IDENTIFY字段，当然用 MEDIUMINT 不用 INT ，试想，每行节约一个字节，16000000行可以节约10兆多呢！ 日期和时间类型比较简单，无非是 DATE、TIME、DATETIME、TIMESTAMP和YEAR等几个类型。只对日期敏感，而对时间没有要求的字段，就用DATE而不用DATETIME是不用说的了；单独使用时间的情况也时有发生——使用TIME；但最多用到的还是用DATETIME。在日期时间类型上没有什么文章可做，这里就不再详述。 不要以为字符类型就是 CHAR ！CHAR和VARCHAR的区别在于CHAR是固定长度，只要你定义一个字段是CHAR(10),那么不论你存储的数据是否达到了10个字节，它都要占去10个字节的空间；而VARVHAR则是可变长度的，如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的，VARCHAR 类型的实际长度是它的值的(实际长度+1)。为什么‚+1?呢这一个字节用于保存实际使用了多大的长度呀！从这个‚+1?中也应该看到，如果一个字段， 它的可能值最长是10个字符，而多数情况下也就是用到了10个字符时，用VARCHAR就不合算了：因为在多数情况下，实际占用空间是11个字节，比用CHAR(10)还多占用一个字节！ 举个例子，就是一个存储股票名称和代码的表，股票名称绝大部分是四个字的，即8个字节；股票代码，上海的是六位数字，深圳的是四位数字。这些都是固定长度的，股票名称当然要用 CHAR(8) ；股票代码虽然是不固定长度，但如果使用VARVHAR(6)，一个深圳的股票代码实际占用空间是5个字节，而一个上海的股票代码要占用7个字节！考虑到上海的股票数目比深圳的多，那么用VARCHAR(6)就不如CHAR(6)合算了。 虽然一个CHAR或VARVHAR的最大长度可以到255，我认为大于20的CHAR是几乎用不到的——很少有大于20个字节长度的固定长度的东东吧不是固定长度的就用VARCHAR！大于100的VARCHAR也是几乎用不到的——比这更大的用TEXT就好了。TINYTEXT，最大长度为255，占用空间也是(实际长度+1)；TEXT，最大长度65535，占用空间是(实际长度+2)；MEDIUMTEXT，最大长度16777215，占用空间是(实际长度+3)；LONGTEXT，最大长度4294967295，占用空间是(实际长度+4)。为什么‚+1?‚+2?‚+3?‚+4?你要是还不知道就该打PP了。这些可以用在论坛啊、新闻啊，什么的，用来保存文章的正文。根据实际情况的不同，选择从小到大的不同类型。 枚举（ENUM）类型，最多可以定义65535种不同的字符串从中做出选择，只能并且必须选择其中一种，占用存储空间是一个或两个字节，由枚举值的数目决定；集合（SET）类型，最多可以有64个成员，可以选择其中的零个到不限定的多个，占用存储空间是一个到八个字节，由集合可能的成员数目决定。 举个例子来说，在SQLServer中，你可以节约到用一个Bit类型来表示性别（男/女），但MySQL没有Bit，用TINTINT不，可以用ENUM('帅哥','美眉')！只有两种选择，所以只需一个字节——跟TINYINT一样大，但却可以直接用字符串'帅哥'和'美眉'来存取。真是太方便啦！ 好了，MySQL的数据类型介绍得差不多，我的建库策略也随着介绍数据类型介绍给大家一些。但这只是其中一部分，篇幅有限，不能再细说；其他的，就靠各人在对数据类型理解的基础上，多多实践、多多讨论。 除非最终检索它们并利用它们来做点事情，否则将记录放入数据库没什么好处。这就是SELECT 语句的用途，即帮助取出数据。SELECT 大概是 SQL 语言中最常用的语句，而且怎样使用它也最为讲究；用它来选择记录可能相当复杂，可能会涉及许多表中列之间的比较。本节介绍Select语句关于查询的最基本功能。 SELECT 语句的语法如下： SELECT selection_list 选择哪些列 FROM table_list 从何处选择行 WHERE primary_constraint 行必须满足什么条件 GROUP BY grouping_columns 怎样对结果分组 HAVING secondary_constraint 行必须满足的第二条件 ORDER BY sorting_columns 怎样对结果排序 LIMIT count 结果限定 注意：所有使用的关键词必须精确地以上面的顺序给出。例如，一个HAVING子句必须跟在GROUP BY子句之后和ORDER BY子句之前。 除了词‚SELECT?和说明希望检索什么的 column_list 部分外，语法中的每样东西都是可选的。有的数据库还需要 FROM 子句。MySQL 有所不同，它允许对表达式求值而不引用任何表。 普通查询 SELECT最简单的形式是从一张表中检索每样东西： mysql> SELECT * FROM pet; 其结果为： +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 查询特定行： 你能从你的表中只选择特定的行。例如，如果你想要验证你对Bowser的出生日期所做的改变，像这样精选Bowser的记录： mysql> SELECT * FROM pet WHERE name = "Bowser"; 其结果为： +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 你可以对照前一个例子来验证。 查询特定列如果你不想要看到你的表的整个行，就命名你感兴趣的列，用逗号分开。例如，如果你想要知道你的动物什么时候出生的，精选name和birth列： mysql> SELECT name, birth FROM pet where owner="Gwen"; 其结果为： +----------+------------+ | name | birth | +----------+------------+ | Claws | 1994-03-17 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | +----------+------------+ 进行表达式计算 前面的多数查询通过从表中检索值已经产生了输出结果。MySQL 还允许作为一个公式的结果来计算输出列的值。表达式可以简单也可以复杂。下面的查询求一个简单表达式的值（常量）以及一个涉及几个算术运算符和两个函数调用的较复杂的表达式的值。例如，计算Browser生活的天数： mysql> SELECT death-birth FROM pet WHERE name="Bowser"; 其结果是： +-------------+ | death-birth | +-------------+ | 49898 | +-------------+ 由于MySQL允许对表达式求值而不引用任何表。所以也可以这样使用: mysql>select (2+3*4.5)/2.5; 其结果为： +---------------+ | (2+3*4.5)/2.5 | +---------------+ | 6.200 | 条件查询 不必每次查询都返回所有的行记录，你能从你的表中只选择特定的行。为此你需要使用WHERE或者HAVING从句。HAVING从句与WHERE从句的区别是，HAVING表达的是第二条件，在与其他从句配合使用，显然不能在WHERE子句中的项目使用HAVING。因此本小节紧介绍WHERE从句的使用，HAVING从句的使用方法类似。另外WHERE从句也可以实现HAVING从句的绝大部分功能。 为了限制 SELECT 语句检索出来的记录集，可使用 WHERE 子句，它给出选择行的条件。可通过查找满足各种条件的列值来选择行。 WHERE 子句中的表达式可使用表1 中的算术运算符、表2 的比较运算符和表3 的逻辑运算符。还可以使用圆括号将一个表达式分成几个部分。可使用常量、表列和函数来完成运算。在本教程的查询中，我们有时使用几个 MySQL 函数，但是 MySQL 的函数远不止这里给出的这些。请参阅附录 一，那里给出了所有MySQL 函数的清单。 表1 算术运算符 运算符 说明 运算符 说明 加 乘 + * - / 减 除 表2 比较运算符 运算符 说明 运算符 说明 小于 不等于 < != 或 <> <= 小于或等于 大于或等于 >= = > 等于 大于 表3 逻辑运算符 运算符 说明 NOT或 ！ 逻辑非 OR 或 || 逻辑或 AND或 && 逻辑与 例如，如果你想要验证你对Bowser的出生日期所做的改变，像这样精选Bowser的记录： mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 输出证实出生年份现在正确记录为1990，而不是1909。 字符串比较通常是大小些无关的，因此你可以指定名字为"bowser"、"BOWSER"等等，查询结果将是相同的。 你能在任何列上指定条件，不只是name。例如，如果你想要知道哪个动物在1998以后出生的，测试birth列： mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 你能组合条件，例如，找出雌性的狗： mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 上面的查询使用AND逻辑操作符，也有一个OR操作符： mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+ AND和OR可以混用。如果你这样做，使用括号指明条件应该如何被分组是一个好主意： mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") -> OR (species = "dog" AND sex = "f"); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 查询排序 使用ORDER BY子句对查询返回的结果按一列或多列排序。ORDER BY子句的语法格式为： ORDER BY column_name [ASC|DESC] [,…] 其中ASC表示升序，为默认值，DESC为降序。ORDER BY不能按text、text和image 数据类型进行排 序。另外，可以根据表达式进行排序。 例如，这里是动物生日，按日期排序： mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1990-08-31 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ 为了以逆序排序，增加DESC（下降 )关键字到你正在排序的列名上： mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Bowser | 1990-08-31 | | Fang | 1990-08-27 | | Buffy | 1989-05-13 | +----------+------------+ 你能在多个列上排序。例如，按动物的种类排序，然后按生日，首先是动物种类中最年轻的动物，使用下列查询： mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Bowser | dog | 1990-08-31 | | Fang | dog | 1990-08-27 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+ 注意DESC关键词仅适用于紧跟在它之前的列名字(birth)；species值仍然以升序被排序。 注意，输出首先按照species排序，然后具有相同species的宠物再按照birth降序排列。 查询分组与行计数 GROUP BY 从句根据所给的列名返回分组的查询结果，可用于查询具有相同值的列。 其语法为： GROUP BY col_name，…. 你可以为多个列分组。 例如： mysql>SELECT * FROM pet GROUP BY species; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+-------+ 由以上结果可以看出： 查询显示结果时，被分组的列如果有重复的值，只返回靠前的记录，并且返回的记录集是排序的。这并不是一个很好的结果。仅仅使用GROUP BY从句并没有什么意义，该从句的真正作用在于与各种组合函数配合，用于行计数。 1、COUNT()函数计数非NULL结果的数目。 你可以这样计算表中记录行的数目： mysql> select count(*) from pet; +----------+ | count(*) | +----------+ | 9 | +----------+ 计算sex为非空的记录数目： mysql> select count(sex) from pet; +------------+ | count(sex) | +------------+ | 8 | +------------+ 现在配合GROUP BY 从句使用。 例如：要知道每个主人有多少宠物 mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ 又如，每种宠物的个数： mysql> SELECT species,count(*) FROM pet GROUP BY species; +---------+----------+ | species | count(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ １５ 如果你除了计数还返回一个列的值，那么必须使用GROU BY语句，否则无法计算记录。 例如上例，使用GROUP BY对每个owner分组所有记录，没有它，你得到的一切是一条错误消息： mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause 也可以根据多个列分组，例如： 按种类和性别组合的动物数量： mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 查询多个表 查询多个表，FROM子句列出表名，并用逗号分隔，因为查询需要从他们两个拉出信息。 当组合(联结-join)来自多个表的信息时，你需要指定在一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。 因为name列出现在两个表中，当引用列时，你一定要指定哪个表。这通过把表名附在列名前做到。 现在有一个event表： mysql>select * from event; +----------+------------+----------+-----------------------------+ | name | date | type | remark | +----------+------------+----------+-----------------------------+ | Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | | Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | | Buffy | 1994-06-19 | litter | 3 puppies, 3 female | | Chirpy | 1999-03-21 | vet | needed beak straightened | | Slim | 1997-08-03 | vet | broken rib | | Bowser | 1991-10-12 | kennel | NULL | | Fang | 1991-10-12 | kennel | NULL | | Fang | 1998-08-28 | birthday | Gave him a new chew toy | | Claws | 1998-03-17 | birthday | Gave him a new flea collar | | Whistler | 1998-12-09 | birthday | First birthday | +----------+------------+----------+-----------------------------+ 当他们有了一窝小动物时，假定你想要找出每只宠物的年龄。 event表指出何时发生，但是为了计算母亲的年龄，你需要她的出生日期。既然它被存储在pet表中，为了查询你需要两张表： mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ 同样方法也可用于同一张表中，你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较，联结一个表到自身有时是有用的。例如， 为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对： mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。 总结 本文总结了SELECT语句检索记录的简单使用方法。其中涉及到的内容主要包括以下一些内容： 1、WHERE从句的用法 2、GROUP BY从句的用法 3、ORDER BY从句的用法 4、连接多个表的简单介绍 有时，希望除去某些记录或更改它们的内容。DELETE 和 UPDATE 语句令我们能做到这一点。 用update修改记录 UPDATE tbl_name SET 要更改的列 WHERE 要更新的记录 这里的 WHERE 子句是可选的，因此如果不指定的话，表中的每个记录都被更新。 例如，在pet表中，我们发现宠物Whistler的性别没有指定，因此我们可以这样修改这个记录： mysql> update pet set sex=’f’ where name=? Whistler?; 用delete删除记录 DELETE 语句有如下格式： DELETE FROM tbl_name WHERE 要删除的记录 WHERE 子句指定哪些记录应该删除。它是可选的，但是如果不选的话，将会删除所有的记录。这意味着最简单的 DELETE 语句也是最危险的。 这个查询将清除表中的所有内容。一定要当心！ 为了删除特定的记录，可用 WHERE 子句来选择所要删除的记录。这类似于 SELECT 语句中的 WHERE 子句。 mysql> delete from pet where name=?Whistler?; 可以用下面的语句清空整个表： mysql>delete from pet; 总结 本节介绍了两个SQL语句的用法。使用UPDATE和DELETE语句要十分小心，因为可能对你的数据造成危险。尤其是DELETE语句，很容易会删除大量数据。使用时，一定小心。 思考题 1、请亲自按照本章所述的步骤，让MySQL服务器在Linux系统启动时，自动启动。并尝试其它启动、重启、关闭服务器的方法。 2、现在有一个位于主机database.domain.net的MySQL服务器，用root用户的身份，密码为newpass，连接到数据库test。如何给出合适的命令行如果使用选项文件，如何添加选项 3、在test数据库中建立一个本章举例中所述的表pet，其结构如下所述： name：30个宽度的定长字符串 owner：30个宽度的定长字符串 species：10个宽度的定长字符串 sex：由m和f组成的非空枚举类型 birth：date类型 death：date类型 4、本章中pet表的数据录入表中： +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 请把数据记录到一个数据文件中，然后使用LOAD DATA INFILE语句装载数据。提示： 如果在Windows环境中，那么文件的换行符是?
?。 如果是使用实用程序mysqlimport命令行如何书写。 使用INSERT语句插入新数据 语法：INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression,...),… INSERT [INTO] tbl_name SET col_name=expression, ... 让我们开始利用 INSERT 语句来增加记录，这是一个 SQL 语句，需要为它指定希望插入数据行的表或将值按行放入的表。INSERT 语句具有几种形式： 可指定所有列的值： 例如： shell> mysql –u root –p mysql> use mytest; mysql> insert into worker values(‚tom?,?tom@yahoo.com?); ‚INTO?一词自 MySQL 3.22.5 以来是可选的。（这一点对其他形式的 INSERT 语句也成立。）VALUES 表必须包含表中每列的值，并且按表中列的存放次序给出。（一般，这就是创建表时列的定义次序。如果不能肯定的话，可使用 DESCRIBE tbl_name 来查看这个次序。） 使用多个值表，可以一次提供多行数据。 Mysql>insert into worker values(‘tom’,’tom@yahoo.com’),(‘paul’,’paul@yahoo.com’); 有多个值表的INSERT ... VALUES的形式在MySQL 3.22.5或以后版本中支持。 可以给出要赋值的那个列，然后再列出值。这对于希望建立只有几个列需要初始设臵的 记录是很有用的。 例如： mysql>insert into worker (name) values (‘tom’); 自 MySQL 3.22.5 以来，这种形式的 INSERT 也允许多个值表： mysql>insert into worker (name) values (‘tom’), (‘paul’); 在列的列表中未给出名称的列都将赋予缺省值。 自 MySQL 3.22 .10 以来，可以 col_name = value 的形式给出列和值。 例如： mysql>insert into worker set name=’tom’; 在 SET 子句中未命名的行都赋予一个缺省值。 使用这种形式的 INSERT 语句不能插入多行。 一个expression可以引用在一个值表先前设臵的任何列。例如，你能这样： mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 但不能这样： mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); 使用INSERT…SELECT语句插入从其他表选择的行 当我们在上一节学习创建表时，知道可以使用select从其它表来直接创建表，甚至可以同时复制数据记录。如果你已经拥有了一个表，你同样可以从select语句的配合中获益。 从其它表中录入数据，例如： mysql>insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2; 你也可以略去目的表的列列表，如果你每一列都有数据录入。 mysql>insert into tbl_name1 select col3,col4 from tbl_name2; INSERT INTO ... SELECT语句满足下列条件： 查询不能包含一个ORDER BY子句。 INSERT语句的目的表不能出现在SELECT查询部分的FROM子句，因为这在ANSI SQL中被禁止让从你正在插入的表中SELECT。（问题是SELECT将可能发现在同一个运行期间内先前被插入的记录。当使用子选择子句时，情况能很容易混淆） 使用replace、replace…select语句插入REPLACE功能与INSERT完全一样，除了如果在表中的一个老记录具有在一个唯一索引上的新记录有相同的值，在新记录被插入之前，老记录被删除。对于这种情况，insert语句的表现是产生一个错误。 REPLACE语句也可以褐SELECT相配合，所以上两小节的内容完全适合REPALCE.。 应该注意的是，由于REPLACE语句可能改变原有的记录，因此使用时要小心。 使用LOAD语句批量录入数据本章的前面讨论如何使用SQL向一个表中插入数据。但是，如果你需要向一个表中添加许多条记录，使用SQL语句输入数据是很不方便的。幸运的是，MySQL提供了一些方法用于批量录入数据，使得向表中添加数据变得容易了。本节以及下一节，将介绍这些方法。本节将介绍SQL语言级的解决方法。 1、基本语法 语法：LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。(LOCAL在MySQL3.22.6或以后版本中可用。) 为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用LOAD DATA INFILE，在服务器主机上你必须有file的权限。见第七章 数据库安全。 REPLACE和IGNORE关键词控制对现有的唯一键记录的重复的处理。如果你指定 REPLACE，新行将代替有相同的唯一键值的现有行。如果你指定IGNORE，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键键时，出现一个错误，并且文本文件的余下部分被忽略时。 如果你使用LOCAL关键词从一个本地文件装载数据，服务器没有办法在操作的当中停止文件的传输，因此缺省的行为好像IGNORE被指定一样。 2、文件的搜寻原则 当在服务器主机上寻找文件时，服务器使用下列规则： 如果给出一个绝对路径名，服务器使用该路径名。 如果给出一个有一个或多个前臵部件的相对路径名，服务器相对服务器的数据目录搜索文件。 如果给出一个没有前臵部件的一个文件名，服务器在当前数据库的数据库目录寻找文件。 注意这些规则意味着一个像‚./myfile.txt?给出的文件是从服务器的数据目录读取，而作为‚myfile.txt?给出的一个文件是从当前数据库的数据库目录下读取。也要注意，对于下列哪些语句，对db1文件从数据库目录读取，而不是db2： mysql> USE db1; mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 3、FIELDS和LINES子句的语法 如果你指定一个FIELDS子句，它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可选的，除了你必须至少指定他们之一。 如你不指定一个FIELDS子句，缺省值与如果你这样写的相同： FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '' 如果你不指定一个LINES子句，缺省值与如果你这样写的相同： LINES TERMINATED BY '
' 换句话说，缺省值导致读取输入时，LOAD DATA INFILE表现如下： 在换行符处寻找行边界在定位符处将行分进字段不要期望字段由任何引号字符封装 将由‚?开头的定位符、换行符或‚?解释是字段值的部分字面字符 LOAD DATA INFILE能被用来读取从外部来源获得的文件。例如，以dBASE格式的文件将有由逗号分隔并用双引号包围的字段。如果文件中的行由换行符终止，下面显示的命令说明你将用来装载文件的字段和行处理选项： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
'; 任何字段或行处理选项可以指定一个空字符串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是一个单个字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超过一个字符。例如，写入由回车换行符对（CR+LF）终止的行，或读取包含这样行的一个文件，指定一个LINES TERMINATED BY '
'子句。 FIELDS [OPTIONALLY] ENCLOSED BY控制字段的包围字符。对于输出(SELECT ... INTO OUTFILE)，如果你省略OPTIONALLY，所有的字段由ENCLOSED BY字符包围。对于这样的输出的一个例子(使用一个逗号作为字段分隔符)显示在下面： "1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a " quote","102.20" "4","a string containing a ", quote and comma","102.20" 如果你指定OPTIONALLY，ENCLOSED BY字符仅被用于包围CHAR和VARCHAR字段： 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20 注意，一个字段值中的ENCLOSED BY字符的出现通过用ESCAPED BY字符作为其前缀来转义。也要注意，如果你指定一个空ESCAPED BY值，可能产生不能被LOAD DATA INFILE正确读出的输出。例如，如果转义字符为空，上面显示的输出显示如下。 注意到在第四行的第二个字段包含跟随引号的一个逗号，它(错误地)好象要终止字段： 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20 FIELDS ESCAPED BY控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY字符不是空的，它被用于前缀在输出上的下列字符： FIELDS ESCAPED BY字符 FIELDS [OPTIONALLY] ENCLOSED BY字符 FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符 ASCII 0（实际上将后续转义字符写成 ASCII'0'，而不是一个零值字节） 如果FIELDS ESCAPED BY字符是空的，没有字符被转义。指定一个空转义字符可能不是一个好主意，特别是如果在你数据中的字段值包含刚才给出的表中的任何字符。 对于输入，如果FIELDS ESCAPED BY字符不是空的，该字符的出现被剥去并且后续字符在字面上作为字段值的一个部分。例外是一个转义的‚0?或‚N?（即，�或N，如果转义字符是‚?)。这些序列被解释为ASCII 0（一个零值字节）和NULL。见下面关于NULL处理的规则。 总结 为数据库装载数据是管理员的重要职责之一，正因为重要，所以MySQL提供的方法也是非常繁多。其中主要的在本节已经列举： 1、使用INSERT、REPLACE语句 2、使用INSERT/REPLACE…SELECT语句 3、使用LOAD DATA INFILE语句 4、使用实用程序mysqlimport 一、INSERT和REPLACE INSERT和REPLACE语句的功能都是向表中插入新的数据。这两条语句的语法类似。 它们的主要区别是如何处理重复的数据。 1. INSERT的一般用法 MySQL中的INSERT语句和标准的INSERT不太一样，在标准的SQL语句中，一次插入一条记录的INSERT语句只有一种形式。 INSERT INTO tablename(列名…) VALUES(列值); 而在MySQL中还有另外一种形式。 INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…; 第一种方法将列名和列值分开了，在使用时，列名必须和列值的数一致。如下面的语句 向users表中插入了一条记录： INSERT INTO users(id, name, age) VALUES(123, '姚明', 25); 第二种方法允许列名和列值成对出现和使用，如下面的语句将产生中样的效果。 INSERT INTO users SET id = 123, name = '姚明', age = 25; 如果使用了SET方式，必须至少为一列赋值。如果某一个字段使用了省缺值(如默认或自增值)，这两种方法都可以省略这些字段。如id字段上使用了自增值，上面两条语句 可以写成如下形式： INSERT INTO users (name, age) VALUES('姚明',25); INSERT INTO uses SET name = '姚明', age = 25; MySQL在VALUES上也做了些变化。如果VALUES中什么都不写，那MySQL将使用表中每一列的默认值来插入新记录。 INSERT INTO users () VALUES(); 如果表名后什么都不写，就表示向表中所有的字段赋值。使用这种方式，不仅在VALUES中的值要和列数一致，而且顺序不能颠倒。 INSERT INTO users VALUES(123, '姚明', 25); 如果将INSERT语句写成如下形式MySQL将会报错。 INSERT INTO users VALUES('姚明',25); 2. 使用INSERT插入多条记录 看到这个标题也许大家会问，这有什么好说的，调用多次INSERT语句不就可以插入多条记录了吗!但使用这种方法要增加服务器的负荷，因为，执行每一次SQL服务器都要同样对SQL进行分析、优化等操作。幸好MySQL提供了另一种解决方案，就是使用一条INSERT语句来插入多条记录。这并不是标准的SQL语法，因此只能在MySQL中使用。 INSERT INTO users(name, age) VALUES('姚明', 25), ('比尔.盖茨', 50), ('火星人', 600); 上面的INSERT 语句向users表中连续插入了3条记录。值得注意的是，上面的INSERT语句中的VALUES后必须每一条记录的值放到一对(…)中，中间使用","分割。假设有一个表table1 CREATE TABLE table1(n INT); 如果要向table1中插入5条记录，下面写法是错误的： INSERT INTO table1 (i) VALUES(1,2,3,4,5); MySQL将会抛出下面的错误 ERROR 1136: Column count doesn't match value count at row 1 而正确的写法应该是这样： INSERT INTO table1(i) VALUES(1),(2),(3),(4),(5); 当然，这种写法也可以省略列名，这样每一对括号里的值的数目必须一致，而且这个数目必须和列数一致。如： INSERT INTO table1 VALUES(1),(2),(3),(4),(5); 3. REPLACE语句 我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引，当我们再向这个表中使用已经存在的键值插入一条记录，那将会抛出一个主键冲突的错误。当然，我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法，必须先使用 DELETE语句删除原先的记录，然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案，这就是REPLACE语句。使用 REPLACE插入一条记录时，如果不重复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。 使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。 在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。 在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了 DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。 REPLACE的语法和INSERT非常的相似，如下面的REPLACE语句是插入或更新一条记录。 REPLACE INTO users (id,name,age) VALUES(123, '赵本山', 50); 插入多条记录： REPLACE INTO users(id, name, age) VALUES(123, '赵本山', 50), (134,'Mary',15); REPLACE也可以使用SET语句 REPLACE INTO users SET id = 123, name = '赵本山', age = 50; 上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。 CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE); 假设table1中已经有了3条记录 a b c 1 1 1 2 2 2 3 3 3 下面我们使用REPLACE语句向table1中插入一条记录。 REPLACE INTO table1(a, b, c) VALUES(1,2,3); 返回的结果如下 Query OK, 4 rows affected (0.00 sec) 在table1中的记录如下 a b c 1 2 3 我们可以看到，REPLACE将原先的3条记录都删除了，然后将(1, 2, 3)插入。 二、UPDATE UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式，在后面可以加WHERE以限制更新的记录范围。 UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ; 如下面的语句将users表中id等于123的记录的age改为24 UPDATE users SET age = 24 WHERE id = 123; 同样，可以使用UPDATE更新多个字段的值 UPDATE users SET age = 24, name = 'Mike' WHERE id = 123; 上面的UPDATE语句通过WHERE指定一个条件，否则，UPDATE将更新表中的所有记录的值。 在使用UPDATE更新记录时，如果被更新的字段的类型和所赋的值不匹配时，MySQL将这个值转换为相应类型的值。如果这个字段是数值类型，而且所赋值超过了这个数据类型的最大范围，那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长，MySQL就将多余的字符串截去。如果设臵非空字段为空，那么将这个字段设臵为它们的默认值，数字的默认值是0，字符串的默认值是空串(不是null，是"")。 有两种情况UPDATE不会对影响表中的数据。 1. 当WHERE中的条件在表中没有记录和它匹配时。 2. 当我们将同样的值赋给某个字段时，如将字段abc赋为'123'，而abc的原值就是'123'。 和INSERT、REPLACE一样，UPDATE也返回所更新的记录数。但这些记录数并不包括满足WHERE条件的，但却未被更新的记录。如下同的UPDATE语句就未更新任何记录。 UPDATE users SET age = 30 WHERE id = 12; Query OK, 0 rows affected (0.00 sec) 需要注意的时，如果一个字段的类型是TIMESTAMP，那么这个字段在其它字段更新时自动更新。 在有些时候我们需要得到UPDATE所选择的行数，而不是被更新的行数。我们可以通过一些API来达到这个目的。如MySQL提供的C API提供了一个选项可以得到你想要的记录数。而MySQL的JDBC驱动得到的默认记录数也是匹配的记录数。 UPDATE和REPLACE基本类似，但是它们之间有两点不同。 1. UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。 2. UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。 三、DELETE和TRUNCATE TABLE 在MySQL中有两种方法可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的所有记录。因此，DELETE语句更灵活。 如果要清空表中的所有记录，可以使用下面的两种方法： DELETE FROM table1 TRUNCATE TABLE table1 其中第二条记录中的TABLE是可选的。 如果要删除表中的部分记录，只能使用DELETE语句。 DELETE FROM table1 WHERE ; 如果DELETE不加WHERE子句，那么它和TRUNCATE TABLE是一样的，但它们有一点不同，那就是DELETE可以返回被删除的记录数，而TRUNCATE TABLE返回的是0。 如果一个表中有自增字段，使用TRUNCATE TABLE和没有WHERE子句的DELETE删除所有记录后，这个自增字段将起始值恢复成1.如果你不想这样做的话，可以在DELETE语句中加上永真的WHERE，如WHERE 1或WHERE true。 DELETE FROM table1 WHERE 1; 上面的语句在执行时将扫描每一条记录。但它并不比较，因为这个WHERE条件永远为true。这样做虽然可以保持自增的最大值，但由于它是扫描了所有的记录，因此，它的执行成本要比没有WHERE子句的DELETE大得多。 DELETE和TRUNCATE TABLE的最大区别是DELETE可以通过WHERE语句选择要删除的记录。但执行得速度不快。而且还可以返回被删除的记录数。而TRUNCATE TABLE无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。 与标准的SQL语句不同，DELETE支持ORDER BY和LIMIT子句，通过这两个子句，我们可以更好地控制要删除的记录。如当我们只想删除WHERE子句过滤出来的记录的一部分，可以使用LIMIB，如果要删除后几条记录，可以通过ORDER BY和LIMIT配合使用。假设我们要删除users表中name等于"Mike"的前6条记录。可以使用如下的DELETE语句： DELETE FROM users WHERE name = 'Mike' LIMIT 6; 一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用ORDER BY对记录进行排序。 DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6; 到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。 这些函数的最大特点就是经常和GROUP BY语句配合使用，需要注意的是集合函数不能和非分组的列混合使用。 行列计数 计算查询语句返回的记录行数 直接计算函数COUNT(*)的值，例如，计算pet表中猫的只数： mysql>SELECT count(*) FROM pet WHERE species=’cat’; +----------+ | count(*) | +----------+ | 2 | +----------+ 统计字段值的数目 例如，计算pet表中species列的数目： mysql> SELECT count(species) FROM pet; +----------------+ | count(species) | +----------------+ | 9 | +----------------+ 如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个，你可以使用WHERE子句，如下例所示： mysql> SELECT COUNT(species) FROM pet WHERE species='cat' ; 注意这条语句的结果： +----------------+ | COUNT(species) | +----------------+ | 2 | +----------------+ 这个例子返回种类为'cat'的作者的数目。如果这个名字在表pet中出现了两次，则次函数的返回值是2。 而且它和上面提到过的语句的结果是一致的： SELECT count(*) FROM pet WHERE species=’cat’ 实际上，这两条语句是等价的。 假如你想知道有多少不同种类的的宠物数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示： mysql> SELECT COUNT(DISTINCT species) FROM pet; +-------------------------+ | COUNT(DISTINCT species) | +-------------------------+ | 5 | +-------------------------+ 如果种类'cat'出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。 通常，当你使用COUNT（）时，字段中的空值将被忽略。 另外，COUNT()函数通常和GROUP BY子句配合使用，例如可以这样返回每种宠物的 数目： mysql> SELECT species,count(*) FROM pet GROUP BY species; +---------+----------+ | species | count(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ 计算字段的平均值 需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。 假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）： SELECT AVG(vote) FROM opinion 这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。 再给出一个实际例子，例如我们要计算pet表中每种动物年龄的平均值，那么使用AVG()函数和GROUP BY子句： mysql> SELECT species,AVG(CURDATE()-birth) FROM pet GROUP BY species; 返回的结果为： +---------+----------------------+ | species | AVG(CURDATE()-birth) | +---------+----------------------+ | bird | 34160 | | cat | 74959.5 | | dog | 112829.66666667 | | hamster | 19890 | | snake | 49791 | +---------+---------------------+ 计算字段值的和 假设你的站点被用来出售某种商品，已经运行了两个月，是该计算赚了多少钱的时候了。 假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）： SELECT SUM(purchase_amount) FROM orders 函数SUM（）的返回值代表字段purchase_amount中所有值的总和。字段purchase_amount的数据类型也许是DECIMAL类型，但你也可以对其它数值型字段使用函数SUM（）。 用一个不太恰当的例子说明，我们计算pet表中同种宠物的年龄的总和： mysql> SELECT species,SUM(CURDATE()-birth) FROM pet GROUP BY species; 你可以查看结果，与前一个例子对照： +---------+----------------------+ | species | SUM(CURDATE()-birth) | +---------+----------------------+ | bird | 68320 | | cat | 149919 | | dog | 338489 | | hamster | 19890 | | snake | 49791 | +---------+----------------------+ 计算字段值的极值 求字段的极值，涉及两个函数MAX()和MIN()。 例如，还是pet表，你想知道最早的动物出生日期，由于日期最早就是最小，所以可以使用MIN()函数： mysql> SELECT MIN(birth) FROM pet; +------------+ | MIN(birth) | +------------+ | 1989-05-13 | +------------+ 但是，你只知道了日期，还是无法知道是哪只宠物，你可能想到这样做： SELECT name,MIN(birth) FROM pet; 但是，这是一个错误的SQL语句，因为集合函数不能和非分组的列混合使用，这里name列是没有分组的。所以，你无法同时得到name列的值和birth的极值。 MIN()函数同样可以与GROUP BY子句配合使用，例如，找出每种宠物中最早的出生日期： mysql> SELECT species,MIN(birth) FROM pet GROUP BY species; 下面是令人满意的结果： +---------+------------+ | species | MIN(birth) | +---------+------------+ | bird | 1997-12-09 | | cat | 1993-02-04 | | dog | 1989-05-13 | | hamster | 1999-03-30 | | snake | 1996-04-29 | +---------+------------+ 另一方面，如果你想知道最近的出生日期，就是日期的最大值，你可以使用MAX()函数，如下例所示： mysql> SELECT species,MAX(birth) FROM pet GROUP BY species; +---------+------------+ | species | MAX(birth) | +---------+------------+ | bird | 1998-09-11 | | cat | 1994-03-17 | | dog | 1990-08-31 | | hamster | 1999-03-30 | | snake | 1996-04-29 | +---------+------------+ 总结 在本节中，介绍了一些典型的集合函数的用法，包括计数、均值、极值和总和，这些都是SQL语言中非常常用的函数。 这些函数之所以称之为集合函数，是因为它们应用在多条记录中，所以集合函数最常见的用法就是与GROUP BY子句配合使用，最重要的是集合函数不能同未分组的列混合使用。 日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。 日期和时间类型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每一个都有合法值的一个范围，而‚零?当你指定确实不合法的值时被使用。注意，MySQL允许你存储某个‚不严格地?合法的日期值，例如1999-11-31，原因我们认为它是应用程序的责任来处理日期检查，而不是SQL服务器。为了使日期检查更‚快?，MySQL仅检查月份在0-12的范围，天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATE或DATETIME列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储日期象1999-00-00或1999-01-00。（当然你不能期望从函数如DATE_SUB()或DATE_ADD()得到类似以这些日期的正确值）。 返回当前日期和时间通过函数GETDATE（），你可以获得当前的日期和时间。例如， CURDATE() 返回当前日期 CURRENT_DATE 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。 mysql> select CURDATE(); +------------+ | CURDATE() | +------------+ | 2001-02-20 | +------------+ mysql> select CURDATE() + 0; +-------------+ | CURDATE()+0 | +-------------+ | 20010220 | +-------------+ CURTIME() 返回当前时间 以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。 mysql> select CURTIME(); +-----------+ | CURTIME() | +-----------+ | 10:42:38 | +-----------+ mysql> select CURTIME() + 0; +-------------+ | CURTIME()+0 | +-------------+ | 104525 | +-------------+ NOW() 返回当前时期和时间 NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYYYMMDDHHMMSS的格式返回日 期和时间值，取决于上下文。 mysql>select now(); +---------------------+ | now() | +---------------------+ | 2001-02-20 10:45:57 | +---------------------+ mysql>select now()+0; +----------------+ | now()+0 | +----------------+ | 20010220105635 | +----------------+ 这些得到当前日期和时间的函数，对于日期和时间的计算很方便，尤其是计算一个时间到现在的时间差。例如，在pet表中，我们以天为单位计算宠物的年龄： mysql> SELECT name,CURDATE()-birth FROM pet; +----------+-----------------+ | name | CURDATE()-birth | +----------+-----------------+ | Fluffy | 80016 | | Claws | 69903 | | Buffy | 119707 | | Chirpy | 29309 | | Fang | 109393 | | Bowser | 109389 | | Whistler | 39011 | | Slim | 49791 | | Puffball | 19890 | +----------+-----------------+ 自动记录数据的改变时间 TIMESTAMP列类型提供一种类型，TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。 自动更新第一个TIMESTAMP列在下列任何条件下发生： 列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。 列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设臵一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设臵一个列为它当前的值，MySQL为了效率而忽略更改。） 你明确地设定TIMESTAMP列为NULL. 除第一个以外的TIMESTAMP列也可以设臵到当前的日期和时间，只要将列设为 NULL，或NOW()。 例如，创建如下的表： mysql> CREATE TABLE student -> ( -> id int, -> name char(16), -> english tinyint, -> chinese tinyint, -> history tinyint, -> time timestamp -> ); 向表中插入记录，可以查看效果： mysql> INSERT student(id,name,englisht,Chinese,history) VALUES(11,?Tom?,66,93,67); 查看记录的存储情况： mysql> SELECT * FROM student; +------+---------+---------+---------+---------+----------------+ | id | name | english | chinese | history | time | +------+---------+---------+---------+---------+----------------+ | 11 | Tom | 66 | 93 | 67 | 20010220123335 | +------+---------+---------+---------+---------+----------------+ 你可以看到time列纪录下了数据录入时的时间值。如果你更新改记录，在查看操作的结果： mysql> UPDATE student SET english=76 WHERE id=11; mysql> SELECT * FROM student; +------+------+---------+---------+---------+----------------+ | id | name | english | chinese | history | time | +------+------+---------+---------+---------+----------------+ | 11 | Tom | 76 | 93 | 67 | 20010220125736 | +------+------+---------+---------+---------+----------------+ 可以清楚的看到，time列的时间被自动更改为修改记录的时间。 有时候你希望不更改任何值，也能打到修改TIMESTAMP列的值，这时只要设臵该列的值为NULL，MySQL就可以自动更新TIMESTAMP列的值： mysql> UPDATE student SET time=NULL WHERE id=11; mysql> select * from student where id=11; +------+------+---------+---------+---------+----------------+ | id | name | english | chinese | history | time | +------+------+---------+---------+---------+----------------+ | 11 | Tom | 76 | 93 | 67 | 20010220130517 | +------+------+---------+---------+---------+----------------+ 通过明确地设臵希望的值，你可以设臵任何TIMESTAMP列为不同于当前日期和时间的值，即使对第一个TIMESTAMP列也是这样。例如，如果，当你创建一个行时，你想要一个TIMESTAMP被设臵到当前的日期和时间，但在以后无论何时行被更新时都不改变，你可以使用这样使用： 让MySQL在行被创建时设臵列，这将初始化它为当前的日期和时间。 当你执行随后的对该行中其他列的更改时，明确设定TIMESTAMP列为它的当前值。 例如，当你在修改列时，可以把原有的值付给TIMESTAMP列： mysql> UPDATE student SET english=66,time=time WHERE id=11; mysql> select * from student where id=11; +------+------+---------+---------+---------+----------------+ | id | name | english | chinese | history | time | +------+------+---------+---------+---------+----------------+ | 11 | Tom | 66 | 93 | 67 | 20010220130517 | +------+------+---------+---------+---------+----------------+ 另一方面，你可能发现，当你想要实现上面这个效果时，很容易用一个你用NOW()初始化的DATETIME列然后不再改变它，这样也许直接些。 但是，TIMESTAMP列的以后好处是存储要求比较小，节省空间。TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。 返回日期和时间范围当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表来模仿一个web站点的记录。 mysql> CREATE TABLE weblog -> ( -> data float, -> entrydate datetime -> ); 然后随机的增加几个数据： mysql> INSERT weblog VALUES(rand(),now()); rand()函数返回一个随机的浮点值，now()函数返回当前时间。多执行上面语句几次，得到一个作为测试的表。 最为测试你还可以增加一个值： mysql> INSERT weblog VALUES(rand(),?2001-02-08?); 这条语句，插入一个entry为?2001-02-08 00:00:00?的值（假定现在为2001年2月8日），你可以查看这个表的值： mysql> select * from weblog; +-----------+---------------------+ | data | entrydate | +-----------+---------------------+ | 0.973723 | 2001-02-08 00:00:00 | | 0.437768 | 2001-02-08 13:57:06 | | 0.327279 | 2001-02-08 13:57:09 | | 0.0931809 | 2001-02-08 13:58:29 | | 0.198805 | 2001-02-08 13:57:54 | +-----------+---------------------+ 你也许对特定的某一天中――比如说2001年2月18日――访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的SELECT语句： mysql> SELECT * FROM weblog WHERE entrydate="2001-02-08" 不要这样做。这个SELECT语句不会返回正确的记录――它将只返回值为2000-02-08 00:00:00的记录，换句话说，只返回当天零点零时的记录。上面语句的结果为： +----------+---------------------+ | data | entrydate | +----------+---------------------+ | 0.973723 | 2001-02-08 00:00:00 | +----------+---------------------+ 要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。 1、使用关系运算符和逻辑运算符来限制时间范围 例如，下面的这个SELECT 语句将能返回正确的记录： mysql> SELECT * FROM weblog -> WHERE entrydate>="2001-02-08" AND entrydate<"2001-02-09" ; 这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于2001-02-08 00:00:00 并小于2001-02-09 00:00:00的记录。换句话说，它将正确地返回2000年2月8日这一天输入的每一条记录。 其结果为： +-----------+---------------------+ | data | entrydate | +-----------+---------------------+ | 0.973723 | 2001-02-08 00:00:00 | | 0.437768 | 2001-02-08 13:57:06 | | 0.327279 | 2001-02-08 13:57:09 | | 0.0931809 | 2001-02-08 13:58:29 | | 0.198805 | 2001-02-08 13:57:54 | +-----------+---------------------+ 2、另一种方法是，你可以使用LIKE来返回正确的记录。通过在日期表达式中包含通配符‚％?，你可以匹配一个特定日期的所有时间。 这里有一个例子： mysql> SELECT * FROM weblog WHERE entrydate LIKE '2001-02-08%' ; 这个语句可以匹配正确的记录。因为通配符‚％?代表了任何时间。 +-----------+---------------------+ | data | entrydate | +-----------+---------------------+ | 0.973723 | 2001-02-08 00:00:00 | | 0.437768 | 2001-02-08 13:57:06 | | 0.327279 | 2001-02-08 13:57:09 | | 0.0931809 | 2001-02-08 13:58:29 | | 0.198805 | 2001-02-08 13:57:54 | +-----------+---------------------+ 3、上面两种方法的异同 由于使用关系运算符进行的是比较过程，时转换成内部的存储格式后进行的，因此，因此时间的书写可以不是那么严格要求。 例如，下面几种写法是等价的： mysql> SELECT * FROM weblog WHERE entrydate>="2001-02-08"; mysql> SELECT * FROM weblog WHERE entrydate>="2001-2-8"; mysql> SELECT * FROM weblog WHERE entrydate>="2001*02*08"; mysql> SELECT * FROM weblog WHERE entrydate>="20010208"; SELECT * FROM weblog WHERE entrydate>="2001/2/8"; 而使用LIKE运算符和模式匹配，是通过比较串值进行的，因此必须使用标准的时间书写格式，YYYY-MM-DD HH-MM-SS。 比较日期和时间 已知两个日期，比较它们的前后，可以直接求出它们的差和零值比较，也可以利用已知的时间函数： TO_DAYS(date) 给出一个日期date，返回一个天数(从0年的天数)，date可以是一个数字，也可以是一个串值，当然更可以是包含日期的时间类型。 mysql> select TO_DAYS(960501); +-----------------+ | TO_DAYS(960501) | +-----------------+ | 729145 | +-----------------+ mysql> select TO_DAYS('1997-07-01'); +-----------------------+ | TO_DAYS('1997-07-01') | +-----------------------+ | 729571 | +-----------------------+ 例如：返回2个时间相差的天数(21世纪已经过去了多少天) mysql> select to_days(now())-to_days('20010101'); +---------------------------------------------------+ | to_days(now()-00000012000000)-to_days('20010101') | +---------------------------------------------------+ | 38 | +---------------------------------------------------+ MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。 标准的SQL模式匹配 SQL的模式匹配允许你使用‚_?匹配任何单个字符，而‚%?匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。 例如，在表pet中，为了找出以‚b?开头的名字： mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 为了找出以‚fy?结尾的名字： mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 为了找出包含一个‚w?的名字： mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 为了找出包含正好5个字符的名字，使用‚_?模式字符： mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 扩展正则表达式模式匹配 由MySQL提供的模式匹配的其他类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 扩展正则表达式的一些字符是： ‚.?匹配任何单个的字符。 一个字符类‚[...]?匹配在方括号内的任何字符。例如，‚[abc]?匹配‚a?、‚b?或‚c?。 为了命名字符的一个范围，使用一个‚-?。 ‚[a-z]?匹配任何小写字母，而‚[0-9]?匹配任何数字。 ‚ * ?匹配零个或多个在它前面的东西。例如，‚x*?匹配任何数量的‚x?字符，‚[0-9]*?匹配的任何数量的数字，而‚.*?匹配任何数 量的任何东西。 正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，‚[aA]?匹配小写或大写的‚a?而‚[a-zA-Z]?匹 配两种写法的任何字母。 如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用‚^?或在模式的结尾用‚$?。 为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写： 为了找出以‚b?开头的名字，使用‚^?匹配名字的开始并且‚[bB]?匹配小写或大写的‚b?： mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 为了找出以‚fy?结尾的名字，使用‚$?匹配名字的结尾： mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 为了找出包含一个‚w?的名字，使用‚[wW]?匹配小写或大写的‚w?： mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 既然如果一个正规表达式出现在值的任何地方，其模式匹配了，就不必再先前的查询中在模式的两方面放臵一个通配符以使得它匹配整个值， 就像如果你使用了一个SQL模式那样。 为了找出包含正好5个字符的名字，使用‚^?和‚$?匹配名字的开始和结尾，和5个‚.?实例在两者之间： mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 你也可以使用‚{n}?‚重复n次?操作符重写先前的查询： mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 总结 本介绍了有关字符串模式匹配的有关知识。标准的SQL模式匹配是SQL语言的标准，可以被其它关系数据库系统接受。扩展正规表达式模式匹配 是根据Unix系统的标准开发了，一般只可使用在MySQL上，但是其功能要比标准的SQL模式匹配更强。 本节将讲述SELECT语句的一些高级功能。 列和表的别名 列的别名 精选输出的列可以用列名、列别名或列位臵在ORDER BY和GROUP BY子句引用，列位臵从1开始。 例如，我们从pet表中检索出宠物和种类，直接引用列名： mysql> SELECT name,species FROM pet ORDER BY name, species; 其输出为： +----------+---------+ | name | species | +----------+---------+ | Bowser | dog | | Buffy | dog | | Chirpy | bird | | Claws | cat | | Fang | dog | | Fluffy | cat | | Puffball | hamster | | Slim | snake | | Whistler | bird | +----------+---------+ 在子句中使用列的位臵： mysql> SELECT name,species FROM pet ORDER BY 1,2; 这条语句的输出与上面并无不同。 最后，你还可以为列命名： mysql> SELECT name AS n,species AS s FROM pet ORDER BY n,s; 注意返回的结果： +----------+---------+ | n | s | +----------+---------+ | Bowser | dog | | Buffy | dog | | Chirpy | bird | | Claws | cat | | Fang | dog | | Fluffy | cat | | Puffball | hamster | | Slim | snake | | Whistler | bird | +----------+---------+ 返回的记录顺序并无不同。但是列的名字有了改变，这一点在使用CREATE TABLE…SELECT语句创建表时是有意义的。 例如，我们想从pet表生成包括其中name，owner字段的表，但是想把name和owner字段的名字重新命名为animal和child，一个很笨的方法就是创建表再录入数据，如果使用别名，则仅仅一条SQL语句就可以解决问题，非常简单，我们要使用的语句使CREATE TABLE： mysql> CREATE TABLE pet1 -> SELECT name AS animal,owner AS child -> FROM pet; 然后，检索生成的表，看看是否打到目的： mysql> SELECT * FROM pet1; +----------+--------+ | animal | child | +----------+--------+ | Fluffy | Harold | | Claws | Gwen | | Buffy | Harold | | Chirpy | Gwen | | Fang | Benny | | Bowser | Diane | | Whistler | Gwen | | Slim | Benny | | Puffball | Diane | +----------+--------+ 在子句中使用列的别名 你可以在GROUP BY、ORDER BY或在HAVING部分中使用别名引用列。别名也可以 用来为列取一个更好点的名字： mysql> SELECT species,COUNT(*) AS total FROM pet -> GROUP BY species HAVING total>1; +---------+-------+ | species | total | +---------+-------+ | bird | 2 | | cat | 2 | | dog | 3 | +---------+-------+ 注意，你的 ANSI SQL 不允许你在一个WHERE子句中引用一个别名。这是因为在WHERE代码被执行时，列值还可能没有终结。例如下列查询是不合法： SELECT id,COUNT(*) AS total FROM pet WHERE total > 1 GROUP BY species 会有下面的错误： ERROR 1054: Unknown column 'total' in 'where clause' WHERE语句被执行以确定哪些行应该包括GROUP BY部分中，而HAVING用来决定应该只用结果集合中的哪些行。 表的别名 别名不仅可以应用于列，也可以引用于表名，具体方法类似于列的别名，这里不再重复。 列的别名经常用于表自身的连接中。你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较，联结一个表到自身有时是有用的。 例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对： mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。 取出互不相同的记录 有时候你可能希望取出的数据互不重复，因为重复的数据可能对你没有意义。 解决的办法是使用DISTINCT关键字，使用这个关键字保证结果集中不包括重复的记录，也就是说，你取出的记录中，没有重复的行。 例如，我们取出pet表中Benny所拥有的宠物的记录： mysql> SELECT name,owner,species,sex FROM pet WHERE owner="Benny"; +------+------+---------+------+ | name | owner | species | sex | +------+-------+---------+------+ | Fang | Benny | dog | m | | Slim | Benny | snake | m | +------+-------+---------+------+ 注意上面的结果，因为我们要使用它。 假定我们指定DISTINCT关键字，并返回列name，species，sex列： mysql> SELECT DISTINCT name,species,sex FROM pet WHERE owner="Benny"; +------+---------+------+ | name | species | sex | +------+---------+------+ | Fang | dog | m | | Slim | snake | m | +------+---------+------+ 你可以看到有两条结果，这是因为返回的结果集中的行不同，如果我们做以下更改，只返回owner，sex列，你可以观察变化： mysql> SELECT DISTINCT owner,sex FROM pet WHERE owner="Benny"; +-------+------+ | owner | sex | +-------+------+ | Benny | m | +-------+------+ DISTINCT关键字的存在，使查询只返回不同的记录行。 如果一个表中，有完全相同的行，你可以使用DISTINCT，以去除冗余的输出： SELECT DISTINCT * FROM tbl_name NULL值的问题 NULL值可能很奇怪直到你习惯于它。概念上，NULL意味着‚没有值?或‚未知值?，且它被看作有点与众不同的值。为了测试NULL，你不能使用算术比较运算符例如=、<或!=。为了说明它，试试下列查询： mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 很清楚你从这些比较中得到毫无意义的结果。相反使用IS NULL和IS NOT NULL操作符： mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 在MySQL中，0意味着假而1意味着真。 NULL这样特殊的处理是为什么，在前面的章节中，为了决定哪个动物不再是活着的，使用death IS NOT NULL而不是death != NULL是必要的 mysql> SELECT * FROM pet WHERE death IS NOT NULL; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ NULL值的概念是造成SQL的新手的混淆的普遍原因，他们经常认为NULL是和一个空字符串''的一样的东西。不是这样的！例如，下列语句是完全不同的： mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); 两个语句把值插入到phone列，但是第一个插入一个NULL值而第二个插入一个空字符串。第一个的含义可以认为是‚电话号码不知道?，而第二个则可意味着‚她没有电话?。 在SQL中，NULL值在于任何其他值甚至NULL值比较时总是假的（FALSE）。包含NULL的一个表达式总是产生一个NULL值，除非在包含在表达式中的运算符和函数的文档中指出。在下列例子，所有的列返回NULL： mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL); +------+--------+--------------------------+ | NULL | 1+NULL | CONCAT('Invisible',NULL) | +------+--------+--------------------------+ | NULL | NULL | NULL | +------+--------+--------------------------+ 如果你想要寻找值是NULL的列，你不能使用=NULL测试。下列语句不返回任何行，因为对任何表达式，expr = NULL是假的： mysql> SELECT * FROM my_table WHERE phone = NULL; 要想寻找NULL值，你必须使用IS NULL测试。下例显示如何找出NULL电话号码和空的电话号码： mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ""; 在MySQL中，就像很多其他的SQL服务器一样，你不能索引可以有NULL值的列。你必须声明这样的列为NOT NULL，而且，你不能插入NULL到索引的列中。 当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUP BY时，所有的NULL值被认为是相等的。 为了有助于NULL的处理，你能使用IS NULL和IS NOT NULL运算符和IFNULL()函数。 对某些列类型，NULL值被特殊地处理。如果你将NULL插入表的第一个IMESTAMP列，则插入当前的日期和时间。如果你将NULL插入一个AUTO_INCREMENT列，则插入顺序中的下一个数字。 大小写敏感性 1、数据库和表名 在MySQL中，数据库和表对应于在那些目录下的目录和文件，因而，内在的操作系统的敏感性决定数据库和表命名的大小写敏感性。这意味着数据库和表名在Unix上是区分大小写的，而在Win32上忽略大小写。 注意：在Win32上，尽管数据库和表名是忽略大小写的，你不应该在同一个查询中使用不同的大小写来引用一个给定的数据库和表。下列查询将不工作，因为它作为my_table和作为MY_TABLE引用一个表： mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1; 2、列名 列名在所有情况下都是忽略大小写的。 3、表的别名 表的别名是区分大小写的。下列查询将不工作，: 因为它用a和A引用别名： mysql> SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2; 4、列的别名 列的别名是忽略大小写的。 5、字符串比较和模式匹配 缺省地，MySQL搜索是大小写不敏感的(尽管有一些字符集从来不是忽略大小写的，例如捷克语)。这意味着，如果你用col_name LIKE 'a%'搜寻，你将得到所有以A或a开始的列值。如果你想要使这个搜索大小写敏感，使用象INDEX(col_name, "A")=0检查一个前缀。或如果列值必须确切是"A"，使用STRCMP(col_name, "A") = 0。 简单的比较操作(>=、>、= 、< 、<=、排序和聚合)是基于每个字符的‚排序值?。有同样排序值的字符(象E，e)被视为相同的字符！LIKE比较在每个字符的大写值上进行(‚E?=?e?)。 如果你想要一个列总是被当作大小写敏感的方式，声明它为BINARY。 例如： mysql> SELECT "E"="e","E"=BINARY "e"; +---------+----------------+ | "E"="e" | "E"=BINARY "e" | +---------+----------------+ | 1 | 0 | +---------+----------------+ 检索语句与多个表的连接 SELECT语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这里将介绍全连接和左连接的作用。 我们创建两个表作为例子。 mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); 录入如下数据： +------+-----------+ | id | first_name| +------+-----------+ | 1 | Tom | | 2 | Marry | | 3 | Jarry | +------+-----------+ mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) ６９ -> ); 录入数据 +------+-----------+ | id | last_name | +------+-----------+ | 2 | Stone | | 3 | White | | 4 | Donald | +------+-----------+ 全连接 全连接：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表的行数之和。 那么观察下面的检索的结果： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 你可以看到输出的结果集中共有3×3=9 行，这就是全连接的结果。 你也可以这样使用SQL语句： mysql> SELCT first.*,last.* FROM first,last; 输出结果与上面的例子相同，并无二致。记录集的输出的排序是以FROM子句后的表的顺序进行，即先排列位臵靠前的表，即使你改变记录集中列的显示顺序，例如下面的例子： mysql> SELECT last.*,first.* FROM first,last; +------+-----------+------+------------+ | id | last_name | id | first_name | +------+-----------+------+------------+ | 2 | Stone | 1 | Tom | | 2 | Stone | 2 | Marry | | 2 | Stone | 3 | Jarry | | 3 | White | 1 | Tom | | 3 | White | 2 | Marry | | 3 | White | 3 | Jarry | | 4 | Donald | 1 | Tom | | 4 | Donald | 2 | Marry | | 4 | Donald | 3 | Jarry | +------+-----------+------+------------+ 上面的例子是两个非常小的表的例子，如果是几个非常大的表的全连接，例如，两个行数分别为1000的表，这样的连接可以产生非常大的结果集合1000×1000＝100万行。而实际上你并不需要这么多行的结果，通常你需要使用一个WHERE从句来限制返回的记录集的行数： mysql> SELECT * FROM first,last WHERE first.id= last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 左连接 左连接：全连接给出FROM子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为NULL。即，LEFT JOIN强制包含左边表的每一行，而不管右边表是否匹配。 语法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr 其中table_reference为连接的表，ON子句后接类似WHERE子句的条件。 下面我们详细讲述左连接的使用： 首先，返回一个全连接的结果集： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 注意上面的结果，下面的例子要与这个例子对照。 我们在给出一个限制条件的查询： mysql> SELECT * FROM first,last WHERE first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 这个结果类似于是从上一个全连接中选择出first.id>last.id 的行。 现在我们给出一个真正的左连接的例子，你可以仔细观察它的结果，要了解检索的记录顺序： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | NULL | NULL | | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中，右边表相应的列臵为NULL。 为了进一步理解这一点，我们给出一个有点奇怪的例子： mysql> SELECT * FROM first LEFT JOIN last ON first.id=1; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | NULL | NULL | | 3 | Jarry | NULL | NULL | +------+------------+------+-----------+ 因为，在结果的最后两行有似乎你不希望的结果。记住，如果只有ON子句的条件，那么左边表的每一行都会返回，只是如果没有匹配的右边表（虽然本例没有约束右边表的列），则记录集中显示为NULL。 前面只是帮助你理解左连接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常见的是与WHERE子句共同使用。 使用IS NULL或者IS NOT NULL操作符可以筛选NULL或者非NULL值的列，这是最常见的技巧。 例如，选出first.id=last.id的组合,并且剔除其中没有右表的匹配记录： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 你可以看到这样做的例子结果与语句 SELECT * FROM first,last WHERE first.id=last.id 的输出是相同的。 又如，检索id值只在左边表出现，而不再右边表出现的记录： mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; +------+------------+ | id | first_name | +------+------------+ | 1 | Tom | +------+------------+ 这个语句是不能用功能相同的带WHERE子句的全连接代替的。 注意：全连接和左连接的结果集排列顺序是不同的，例如： mysql> SELECT * FROM first,last WHERE first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 3 | White | | 2 | Marry | 4 | Donald | | 3 | Jarry | 2 | Stone | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 总结 本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数据时很常用的技巧，主要的一些内容如下： 1、为表和列使用别名 2、注意NULL值在查询中的使用 3、注意表名、列名、别名和字符串的大小写问题 4、如何避免取出重复的记录 检索语句与多个表的连接 SELECT语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这里将介绍全连接和左连接的作用。 我们创建两个表作为例子。 mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); 录入如下数据： +------+-----------+ | id | first_name| +------+-----------+ | 1 | Tom | | 2 | Marry | | 3 | Jarry | +------+-----------+ mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) -> ); 录入数据 +------+-----------+ | id | last_name | +------+-----------+ | 2 | Stone | | 3 | White | | 4 | Donald | +------+-----------+ 全连接 全连接：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表的行数之和。 那么观察下面的检索的结果： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+----------- | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 你可以看到输出的结果集中共有3×3=9 行，这就是全连接的结果。 你也可以这样使用SQL语句： mysql> SELCT first.*,last.* FROM first,last; 输出结果与上面的例子相同，并无二致。记录集的输出的排序是以FROM子句后的表的顺序进行，即先排列位臵靠前的表，即使你改变记录集中列的显示顺序，例如下面的例子： mysql> SELECT last.*,first.* FROM first,last; +------+-----------+------+------------+ | id | last_name | id | first_name | +------+-----------+------+------------+ | 2 | Stone | 1 | Tom | | 2 | Stone | 2 | Marry | | 2 | Stone | 3 | Jarry | | 3 | White | 1 | Tom | | 3 | White | 2 | Marry | | 3 | White | 3 | Jarry | | 4 | Donald | 1 | Tom | | 4 | Donald | 2 | Marry | | 4 | Donald | 3 | Jarry | +------+-----------+------+------------+ 上面的例子是两个非常小的表的例子，如果是几个非常大的表的全连接，例如，两个行数分别为1000的表，这样的连接可以产生非常大的结果集合1000×1000＝100万行。而实际上你并不需要这么多行的结果，通常你需要使用一个WHERE从句来限制返回的记录集的行数： mysql> SELECT * FROM first,last WHERE first.id= last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 左连接 左连接：全连接给出FROM子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为NULL。即，LEFT JOIN强制包含左边表的每一行，而不管右边表是否匹配。 语法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr 其中table_reference为连接的表，ON子句后接类似WHERE子句的条件。 下面我们详细讲述左连接的使用： 首先，返回一个全连接的结果集： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 注意上面的结果，下面的例子要与这个例子对照。 我们在给出一个限制条件的查询： mysql> SELECT * FROM first,last WHERE first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 这个结果类似于是从上一个全连接中选择出first.id>last.id 的行。 现在我们给出一个真正的左连接的例子，你可以仔细观察它的结果，要了解检索的记录顺序： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | NULL | NULL | | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中，右边表相应的列臵为NULL。 为了进一步理解这一点，我们给出一个有点奇怪的例子： mysql> SELECT * FROM first LEFT JOIN last ON first.id=1; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | NULL | NULL | | 3 | Jarry | NULL | NULL | +------+------------+------+-----------+ 因为，在结果的最后两行有似乎你不希望的结果。记住，如果只有ON子句的条件，那么左边表的每一行都会返回，只是如果没有匹配的右边表（虽然本例没有约束右边表的列），则记录集中显示为NULL。 前面只是帮助你理解左连接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常见的是与WHERE子句共同使用。 使用IS NULL或者IS NOT NULL操作符可以筛选NULL或者非NULL值的列，这是最常见的技巧。 例如，选出first.id=last.id的组合,并且剔除其中没有右表的匹配记录： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 你可以看到这样做的例子结果与语句 SELECT * FROM first,last WHERE first.id=last.id 的输出是相同的。 又如，检索id值只在左边表出现，而不再右边表出现的记录： mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; +------+------------+ | id | first_name | +------+------------+ | 1 | Tom | +------+------------+ 这个语句是不能用功能相同的带WHERE子句的全连接代替的。 注意：全连接和左连接的结果集排列顺序是不同的，例如： mysql> SELECT * FROM first,last WHERE first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 3 | White | | 2 | Marry | 4 | Donald | | 3 | Jarry | 2 | Stone | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 总结 本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数据时很常用的技巧，主要的一些内容如下： 1、为表和列使用别名 2、注意NULL值在查询中的使用 3、注意表名、列名、别名和字符串的大小写问题 4、如何避免取出重复的记录 由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。 在MySQL 中，GROUP BY 的实现同样有多种(三种)方式，其中有两种方式会利用现有的索引信息来完成 GROUP BY，另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。 1.使用松散(Loose)索引扫描实现 GROUP BY 何谓松散索引扫描实现 GROUP BY 呢?实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。 下面我们通过一个示例来描述松散索引扫描实现 GROUP BY，在示例之前我们需要首先调整一下 group_message 表的索引，将 gmt_create 字段添加到 group_id 和 user_id 字段的索引中： 1 sky@localhost : example 08:49:45> create index idx_gid_uid_gc 2 3 -> on group_message(group_id,user_id,gmt_create); 4 5 Query OK, rows affected (0.03 sec) 6 7 Records: 96 Duplicates: 0 Warnings: 0 8 9 sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid 10 11 -> on group_message; 12 13 Query OK, 96 rows affected (0.02 sec) 14 15 Records: 96 Duplicates: 0 Warnings: 0然后再看如下 Query 的执行计划： 1 sky@localhost : example 09:26:15> EXPLAIN 2 3 -> SELECT user_id,max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id < 10 8 9 -> GROUP BY group_id,user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 8 26 27 ref: NULL 28 29 rows: 4 30 31 Extra: Using where; Using index for group-by 32 33 1 row in set (0.00 sec) 我们看到在执行计划的 Extra 信息中有信息显示‚Using index for group-by?，实际上这就是告诉我们，MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的 GROUP BY 操作。 下面这张图片描绘了扫描过程的大概实现：  要利用到松散索引扫描实现 GROUP BY，需要至少满足以下几个条件： ?GROUP BY 条件字段必须在同一个索引中最前面的连续位臵; ?在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数; ?如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在; 为什么松散索引扫描的效率会很高? 因为在没有WHERE子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。 2.使用紧凑(Tight)索引扫描实现 GROUP BY 紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取恶的数据来完成 GROUP BY 操作得到相应结果。 1 sky@localhost : example 08:55:14> EXPLAIN 2 3 -> SELECT max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id = 2 8 9 -> GROUP BY user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: ref 20 21 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 4 26 27 ref: const 28 29 rows: 4 30 31 Extra: Using where; Using index 32 33 1 row in set (0.01 sec) 这时候的执行计划的 Extra 信息中已经没有‚Using index for group-by?了，但并不是说 MySQL 的 GROUP BY 操作并不是通过索引完成的，只不过是需要访问 WHERE 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 GROUP BY 的执行计划输出信息。 下面这张图片展示了大概的整个执行过程：  在 MySQL 中，MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作，当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后，才会尝试通过紧凑索引扫描来实现。 当 GROUP BY 条件字段并不连续或者不是索引前缀部分的时候，MySQL Query Optimizer 无法使用松散索引扫描，设臵无法直接通过索引完成 GROUP BY 操作，因为缺失的索引键信息无法得到。但是，如果 Query 语句中存在一个常量值来引用缺失的索引键，则可以使用紧凑索引扫描完成 GROUP BY 操作，因为常量填充了搜索关键字中的‚差距?，可以形成完整的索引前缀。这些索引前缀可以用于索引查找。而如果需要排序GROUP BY结果，并且能够形成索引前缀的搜索关键字，MySQL还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。 3.使用临时表实现 GROUP BY MySQL 在进行 GROUP BY 操作的时候要想利用所有，必须满足 GROUP BY 的字段必须同时存放于同一个索引中，且该索引是一个有序索引(如 Hash 索引就不能满足要求)。而且，并不只是如此，是否能够利用索引来实现 GROUP BY 还与使用的聚合函数也有关系。 前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的，当 MySQL Query Optimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY 操作。 1 sky@localhost : example 09:02:40> EXPLAIN 2 3 -> SELECT max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id > 1 and group_id < 10 8 9 -> GROUP BY user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 4 26 27 ref: NULL 28 29 rows: 32 30 31 Extra: Using where; Using index; Using temporary; Using filesort 这次的执行计划非常明显的告诉我们 MySQL 通过索引找到了我们需要的数据，然后创建了临时表，又进行了排序操作，才得到我们需要的 GROUP BY 结果。整个执行过程大概如下图所展示： 当 MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后，他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。 在这样示例中即是这样的情况。 group_id 并不是一个常量条件，而是一个范围，而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现，只能先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。 下面将通过实例分析两种排序实现方式及实现图解： 假设有 Table A 和 B 两个表结构分别如下： 1 sky@localhost : example 01:48:2 1> show create table AG 2 3 *************************** 1. row *************************** 4 5 Table: A 6 7 Create Table: CREATE TABLE `A` ( 8 9 `c1` int(11) NOT NULL default '0', 10 11 `c2` char(2) default NULL, 12 13 `c3` varchar(16) default NULL, 14 15 `c4` datetime default NULL, 16 17 PRIMARY KEY (`c1`) 18 19 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 20 21 sky@localhost : example 01:48:32> show create table BG 22 23 *************************** 1. row *************************** 24 25 Table: B 26 27 Create Table: CREATE TABLE `B` ( 28 29 `c1` int(11) NOT NULL default '0', 30 31 `c2` char(2) default NULL, 32 33 `c3` varchar(16) default NULL, 34 35 PRIMARY KEY (`c1`), 36 37 KEY `B_c2_ind` (`c2`) 38 39 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1、利用有序索引进行排序，实际上就是当我们Query 的ORDER BY 条件和Query 的执行计划中所利用的Index的索引键(或前面几个索引键)完全一致，且索引访问方式为rang、ref 或者index的时候，MySQL可以利用索引顺序而直接取得已经排好序的数据。这种方式的ORDER BY 基本上可以说是最优的排序方式了，因为MySQL不需要进行实际的排序操作。 假设我们在Table A和B上执行如下SQL： 1 sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B 2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where; Using index 我们通过执行计划可以看出，MySQL实际上并没有进行实际的排序操作，实际上其整个执行过程如下图所示： 2、通过相应的排序算法，将取得的数据在内存中进行排序方式，MySQL 比需要将数据在内存中进行排序，所使用的内存区域也就是我们通过ort_buffer_size 系统变量所设臵的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。 第二种方式在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为filesort。在这种方式中，主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给客户端。在MySQL中filesort 的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。 在MySQL4.1版本之前只有第一种排序算法，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sort buffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义max_length_for_sort_data 参数的设臵。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。 我们再看看 MySQL 需要使用filesort 实现排序的实例。 假设我们改变一下我们的Query，换成通过A.c2来排序，再看看情况： 1 sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B 2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c2G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where; Using filesort 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where; Using index MySQL 从 Table A 中取出了符合条件的数据，由于取得的数据并不满足ORDER BY 条件，所以MySQL进行了 filesort 操作，其整个执行过程如下图所示：  在MySQL 中，filesort 操作还有一个比较奇怪的限制，那就是其数据源必须是来源于一个Table，所以，如果我们的排序数据如果是两个(或者更多个) Table 通过Join所得出的，那么 MySQL 必须通过先创建一个临时表(Temporary Table)，然后再将此临时表的数据进行排序，如下例所示： 1 sky@localhost : example 02:46:15> explain select A.* from A,B 2 3 -> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where; Using temporary; Using filesort 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where 这个执行计划的输出还是有点奇怪的，不知道为什么，MySQL Query Optimizer 将 ‚Using temporary? 过程显示在第一行对Table A 的操作中，难道只是为让执行计划的输出少一行? 实际执行过程应该是如下图所示：  备份是最简单的保护数据的方法，本节将介绍多种备份方法。为了得到一个一致的备份，在相关的表上做一个LOCK TABLES，你只需一个读锁定，当你在数据库目录中做文件的一个拷贝时，这允许其他线程继续查询该表；当你恢复数据时，需要一个写锁定，以避免冲突。 使用SQL语句备份和恢复 你可以使用SELECT INTO OUTFILE语句备份数据，并用LOAD DATA INFILE语句恢复数据。这种方法只能导出数据的内容，不包括表的结构，如果表的结构文件损坏，你必须要先恢复原来的表的结构。 语法： SELECT * INTO {OUTFILE | DUMPFILE} 'file_name' FROM tbl_name LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name SELECT ... INTO OUTFILE 'file_name'格式的SELECT语句将选择的行写入一个文件。文件在服务器主机上被创建，并且不能是已经存在的(不管别的，这可阻止数据库表和文件例如‚/etc/passwd?被破坏)。SELECT ... INTO OUTFILE是LOAD DATA INFILE逆操作。 LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。(LOCAL在MySQL3.22.6或以后版本中可用。) 为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用LOAD DATA INFILE，在服务器主机上你必须有file的权限。使用这种SELECT INTO OUTFILE语句，在服务器主机上你必须有FILE权限。 为了避免重复记录，在表中你需要一个PRIMARY KEY或UNIQUE索引。当在唯一索引值上一个新记录与一个老记录重复时，REPLACE关键词使得老记录用一个新记录替代。如果你指定IGNORE，跳过有唯一索引的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复索引值时，出现一个错误，并且文本文件的余下部分被忽略时。 如果你指定关键词LOW_PRIORITY，LOAD DATA语句的执行被推迟到没有其他客户读取表后。 使用LOCAL将比让服务器直接存取文件慢些，因为文件的内容必须从客户主机传送到服务器主机。在另一方面，你不需要file权限装载本地文件。如果你使用LOCAL关键词从一个本地文件装载数据，服务器没有办法在操作的当中停止文件的传输，因此缺省的行为好像IGNORE被指定一样。 当在服务器主机上寻找文件时，服务器使用下列规则： ?如果给出一个绝对路径名，服务器使用该路径名。 ?如果给出一个有一个或多个前臵部件的相对路径名，服务器相对服务器的数据目录搜索文件。 ?如果给出一个没有前臵部件的一个文件名，服务器在当前数据库的数据库目录寻找文件。 假定表tbl_name具有一个PRIMARY KEY或UNIQUE索引，备份一个数据表的过程如下： 1、锁定数据表，避免在备份过程中，表被更新 mysql>LOCK TABLES READ tbl_name; 2、导出数据 mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name; 3、解锁表 mysql>UNLOCK TABLES; 相应的恢复备份的数据的过程如下： 1、为表增加一个写锁定： mysql>LOCK TABLES tbl_name WRITE; 2、恢复数据 mysql>LOAD DATA INFILE ‘tbl_name.bak’ ->REPLACE INTO TABLE tbl_name; 如果，你指定一个LOW_PRIORITY关键字，就不必如上要对表锁定，因为数据的导入将被推迟到没有客户读表为止： mysql>LOAD DATA LOW_PRIORITY INFILE ‘tbl_name’ ->REPLACE INTO TABLE tbl_name; 3、解锁表 使用mysqlimport恢复数据 如果你仅仅恢复数据，那么完全没有必要在客户机中执行SQL语句，因为你可以简单的使用mysqlimport程序，它完全是与LOAD DATA 语句对应的，由发送一个LOAD DATA INFILE命令到服务器来运作。执行命令mysqlimport --help，仔细查看输出，你可以从这里得到帮助。 shell> mysqlimport [options] db_name filename ... 对于在命令行上命名的每个文本文件，mysqlimport剥去文件名的扩展名并且使用它决定哪个表导入文件的内容。例如，名为‚patient.txt?、 ‚patient.text?和‚patient?将全部被导入名为patient的一个表中。 常用的选项为： -C, --compress 如果客户和服务器均支持压缩，压缩两者之间的所有信息。 -d, --delete 在导入文本文件前倒空表格。 l, --lock-tables 在处理任何文本文件前为写入所定所有的表。这保证所有的表在服务器上被同步。 --low-priority，--local，--replace，--ignore分别对应LOAD DATA语句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE关键字。 例如恢复数据库db1中表tbl1的数据，保存数据的文件为tbl1.bak，假定你在服务器主机上： shell>mysqlimport --lock-tables --replace db1 tbl1.bak 这样在恢复数据之前现对表锁定，也可以利用--low-priority选项： shell>mysqlimport --low-priority --replace db1 tbl1.bak 如果你为远程的服务器恢复数据，还可以这样： shell>mysqlimport -C --lock-tables --replace db1 tbl1.bak 当然，解压缩要消耗CPU时间。 象其它客户机一样，你可能需要提供-u，-p选项以通过身分验证，也可以在选项文件my.cnf中存储这些参数，具体方法和其它客户机一样，这里就不详述了。 mysql->UNLOCAK TABLES; 用mysqldump备份数据 同mysqlimport一样，也存在一个工具mysqldump备份数据，但是它比SQL语句多做的工作是可以在导出的文件中包括SQL语句，因此可以备份数据库表的结构，而且可以备份一个数据库，甚至整个数据库系统。 mysqldump [OPTIONS] database [tables] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] mysqldump [OPTIONS] --all-databases [OPTIONS] 如果你不给定任何表，整个数据库将被倾倒。 通过执行mysqldump --help，你能得到你mysqldump的版本支持的选项表。 1、备份数据库的方法 例如，假定你在服务器主机上备份数据库db_name shell> mydqldump db_name 当然，由于mysqldump缺省时把输出定位到标准输出，你需要重定向标准输出。 例如，把数据库备份到bd_name.bak中： shell> mydqldump db_name>db_name.bak 你可以备份多个数据库，注意这种方法将不能指定数据表： shell> mydqldump --databases db1 db1>db.bak 你也可以备份整个数据库系统的拷贝，不过对于一个庞大的系统，这样做没有什么实际的价值： shell> mydqldump --all-databases>db.bak 虽然用mysqldump导出表的结构很有用，但是恢复大量数据时，众多SQL语句使恢复的效率降低。你可以通过使用--tab选项，分开数据和创建表的SQL语句。 -T，--tab= 在选项指定的目录里，创建用制表符（tab）分隔列值的数据文件和包含创建表结构的SQL语句的文件，分别用扩展名.txt和.sql表示。该选项不能与--databases或--all-databases同时使用，并且mysqldump必须运行在服务器主机上。 例如，假设数据库db包括表tbl1，tbl2，你准备备份它们到/var/mysqldb shell>mysqldump --tab=/var/mysqldb/ db 其效果是在目录/var/mysqldb中生成4个文件，分别是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。 2、mysqldump实用程序时的身份验证的问题同其他客户机一样，你也必须提供一个MySQL数据库帐号用来导出数据库，如果你不是使用匿名用户的话，可能需要手工提供参数或者使用选项文件： 如果这样： shell>mysql -u root –pmypass db_name>db_name.sql 或者这样在选项文件中提供参数： [mysqldump] user=root password=mypass 然后执行 shell>mysqldump db_name>db_name.sql 那么一切顺利，不会有任何问题，但要注意命令历史会泄漏密码，或者不能让任何除你之外的用户能够访问选项文件，由于数据库服务器也需要这个选项文件时，选项文件只能被启动服务器的用户（如，mysql）拥有和访问，以免泄密。在Unix下你还有一个解决办法，可以在自己的用户目录中提供个人选项文件（~/.my.cnf）,例如，/home/some_user/.my.cnf，然后把上面的内容加入文件中，注意防止泄密。在NT系统中，你可以简单的让c:my.cnf能被指定的用户访问。 你可能要问，为什么这么麻烦呢，例如，这样使用命令行： shell>mysql -u root –p db_name>db_name.sql 或者在选项文件中加入 [mysqldump] user=root password 然后执行命令行： shell>mysql db_name>db_name.sql 你发现了什么往常熟悉的Enter password:提示并没有出现，因为标准输出被重定向到文件db_name.sql中了，所以看不到往常的提示符，程序在等待你输入密码。在重定向的情况下，再使用交互模式，就会有问题。在上面的情况下，你还可以直接输入密码。 然后在文件db_name.sql文件的第一行看到: Enter password:#…….. 你可能说问题不大，但是mysqldump之所以把结果输出到标准输出，是为了重定向到其它程序的标准输入，这样有利于编写脚本。例如： 用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的： shell>mysqldump --opt database | mysql --host=remote-host -C database 如果mysqldump仍运行在提示输入密码的交互模式下，该命令不会成功，但是如果mysql是否运行在提示输入密码的交互模式下，都是可以的。 如果在选项文件中的[client]或者[mysqldump]任何一段中指定了password选项，且不提供密码，即使，在另一段中有提供密码的选项password=mypass,例如 [client] user=root password [mysqldump] user=admin password=mypass 那么mysqldump一定要你输入admin用户的密码： mysql>mysqldump db_name 即使是这样使用命令行： mysql>mysqldump –u root –ppass1 db 也是这样，不过要如果-u指定的用户的密码。 其它使用选项文件的客户程序也是这样 3、有关生成SQL语句的优化控制 --add-locks 生成的SQL 语句中，在每个表数据恢复之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。 --add-drop-table 生成的SQL 语句中，在每个create语句之前增加一个drop table。 -e, --extended-insert 使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句） 下面两个选项能够加快备份表的速度： -l, --lock-tables. 为开始导出数据前，读锁定所有涉及的表。 -q, --quick 不缓冲查询，直接倾倒至stdout。 理论上，备份时你应该指定上诉所有选项。这样会使命令行过于复杂，作为代替，你可以简单的指定一个--opt选项，它会使上述所有选项有效。 例如，你将导出一个很大的数据库： shell> mysqldump --opt db_name > db_name.txt 当然，使用--tab选项时，由于不生成恢复数据的SQL语句，使用--opt时，只会加快数据导出。 4、恢复mysqldump备份的数据 由于备份文件是SQL语句的集合，所以需要在批处理模式下使用客户机 如果你使用mysqldump备份单个数据库或表，即： shell>mysqldump --opt db_name > db_name.sql 由于db_name.sql中不包括创建数据库或者选取数据库的语句，你需要指定数据库 shell>mysql db2 < db_name.sql 如果，你使用--databases或者--all-databases选项，由于导出文件中已经包含创建和选用数据库的语句，可以直接使用，不比指定数据库，例如： shell>mysqldump --databases db_name > db_name.sql shell>mysql 如果你使用--tab选项备份数据，数据恢复可能效率会高些 例如，备份数据库db_name后在恢复： shell>mysqldump --tab=/path/to/dir --opt test 如果要恢复表的结构，可以这样： shell>mysql < /path/to/dir/tbl1.sql 如果要恢复数据，可以这样 shell>mysqlimport -l db /path/to/dir/tbl1.txt 如果是在Unix平台下使用（推荐），就更方便了： shell>ls -l *.sql | mysql db shell>mysqlimport --lock-tables db /path/to/dir/*.txt 用直接拷贝的方法备份恢复 根据本章前两节的介绍，由于MySQL的数据库和表是直接通过目录和表文件实现的，因此直接复制文件来备份数据库数据，对MySQL来说特别方便。而且自MySQL 3.23起MyISAM表成为缺省的表的类型，这种表可以为在不同的硬件体系中共享数据提供了保证。 使用直接拷贝的方法备份时，尤其要注意表没有被使用，你应该首先对表进行读锁定。 备份一个表，需要三个文件： 对于MyISAM表： tbl_name.frm 表的描述文件 tbl_name.MYD 表的数据文件 tbl_name.MYI 表的索引文件 对于ISAM表： tbl_name.frm 表的描述文件 tbl_name.ISD 表的数据文件 tbl_name.ISM 表的索引文件 你直接拷贝文件从一个数据库服务器到另一个服务器，对于MyISAM表，你可以从运行在不同硬件系统的服务器之间复制文件，例如，SUN服务器和INTEL PC机之间。 总结 本节介绍了备份恢复数据库的多种方法，读者可以根据需要选用，对于文中涉及到的SQL语句、工具主要有： 1、SELECT…INTO OUTFILE和LOAD DATA INFILE 2、mysqldump 3、mysqlimport 对于这些内容，读者需要注重掌握的是mysqldump实用程序的使用，以及mysql批处理模式运行包含SQL语句的文件的方法。这在备份和恢复数据库表中非常常用。另外要注意的是直接拷贝的方法。
信息发布：广州名易软件有限公司 http://www.myidp.net
|
名易MyOA协同综合办公平台
名易MyCRM客户关系管理平台
名易MyHR人力资源管理平台
名易MyIDP智能开发平台
名易MyIBP保险业务管理平台
名易MyHMS酒店综合管理系统
名易MyPM项目管理平台
名易MyTMS物流运输管理平台
名易MyVMS汽车综合管理系统
名易MyIMS贷款管理系统
名易MyEDU教育管理平台
名易MyPCS生产事务协调系统
名易MyWIDP微信智能开发平台
