mysql cmd lines

 有关数据库的操作 

从本节开始正式介绍各种 SQL 语句。本节介绍有关数据库级的 SQL 以及相关操作，
查看、建立和删除等操作。 
3.3.1  用SHOW显示已有的数据库 
句法：SHOW DATABASES [LIKE wild]  
3  数据库基本操作          
 
如果使用 LI KE wild 部分，wild 字符串可以是一个使用 SQL 的“%”和“_”通配符
的字符串。 
功能：SHOW DATABASES 列出在 MySQL服务器主机上的数据库。 
你可以尝试下面举例，观察输出结果，例如： 
mysql>show databases; 
+---------- + 
| Database |  
+---------- + 
| first    |  
| mysql    |  
| mytest   |  
| test     |  
| test1    |  
+---------- + 
mysql>show data bases like ‘my%’; 
+---------------- + 
| Database (my%) |  
+---------------- + 
| mysql          |  
| mytest         |  
+---------------- + 
 
用mysqlshow程序也可以得到已有数据库列表。 
3.3.2  用Create Dabase  创建数据库 
句法：CREATE DATABASE db_name  
功能：CREATE DATABASE用给定的名字创建一个数据库。 
如果数据库已经存在，发生一个错误。   
在MySQL中的数据库实现成包含对应数据库中表的文件的目录。因为数据库在初始
创建时没有任何表，C R E AT E  D ATA B A S E 语句只是在 MySQL数据目录下面创建一个目
录。 
例如： 
mysql>create database myfirst; 
然后利用 show databases观察效果。 
3.3.3  用DROP DATABASE删除数据库 
句法：DROP DATABASE [IF EXISTS] db_name 
功能：DROP DATABASE 删除数据库中的所有表和数据库。要小心地使用这个命
令！ 
 
DROP DATABASE返回从数据库目录被删除的文件的数目。通常，这 3 倍于表的数
量，因为每张表对应于一个“.MYD ”文件、一个“.MYI”文件和一个“.frm ”文件。   
在MySQL 3.22或以后版本中，你可以使用关键词 IF EXISTS 阻止一个错误的发生，
如果数据库不存在。   
3.3.4  使用 mysqladmin工具创建和删除 
在命令行环境下可以使用 mysqladmin 创建和删除数据库。 
创建数据库： 
shell> mysqladmin create db_name  
删除数据库： 
shell>  mysqladmin drop db_name 
如果出现下面的错误： 
mysqladmin: connect to server at 'localhost' failed 
error: 'Access denied for user: 'root @localhost' (Using password:  YES )'  
表示你需要一个可以正常连接的用户，请指定-u  -p 选项，具体方法与 3.2节介绍相同，在
第七章中你将会学到用户授权的知识。 
3.3.5  直接在数据库目录中创建或删除 
用上述方法创建数据库，只是 MySQL数据目录下面创建一个与数据库同名目录，同
样删除数据库是把这个目录删除。 
所以，你可以直接这么做，创建或删除数据库，或者给数据库更名。这对备份和恢
复备份有一定意义。 
3.3.6  用USE选用数据库 
句法：USE db_name   
USE db_name 语句告诉MySQL使用db_name 数据库作为随后的查询的缺省数据
库。数据库保持到会话结束，或发出另外一个USE 语句：   
mysql> USE db1; 
mysql> SELECT count(*) FROM mytable;      # selects from db1.mytable  
mysql> USE db2; 
mysql> SELECT count(*) FROM mytable;      # selects from db2.mytable 
 
如果你不是用 USE 语句，那么上面的例子应该写成： 
mysql> SELECT count(*) FROM db1. mytable; 
mysql> SELECT count(*) FROM db2. mytable; 


由于 use 也是一个 mysql 客户程序的命令，所以你可以在命令行最后不加分号，客
户程序可以得到结果。 
3.3.7  总结 
本节介绍了有关数据库操作的 SQL 语句、实用程序，其中包括： 
?  SQL 语句：CREATE/DROP DATABASE ，SHOW DATABASES ，USE  
?  程序 mysqladmin  
?  直接创建或删除数据库的目录 
3.4  有关数据表的操作 
用MySQL，目前(版本  3.23)你可以在三种基本数据库表格式间选择。当你创建一张
表时，你可以告诉 MySQL它应该对于表使用哪个表类型。MySQL将总是创建一个.frm
文件保存表和列定义。视表类型而定，索引和数据将在其他文件中存储。   
你能用 ALTER TABLE 语句在不同类型的表之间变换。见 7.8 ALTER TABLE 语法。   
?  MyISAM  
在MySQL 3.23中，MyISAM 是缺省表格类型，它是基于 ISAM 代码并且有很多有用
的扩展。索引存储在一个有.MYI（MYindex）扩展名的文件并且数据存储在有.MYD
（MYData）扩展名的文件中。你能用 myisamchk 实用程序检查/修复 MyISAM 表。 
?  ISAM  
你也可以使用放弃的 ISAM 。这将在不久消失，因为 MyISAM 是同一个东西的更好
实现。ISAM 使用一个 B-tree 索引，这个索引存储在一个有.ISM 扩展名的文件中并且数
据存储在有.ISD 扩展名的文件中，你可用 isamchk 实用程序检查/修复 ISAM 表。ISAM 表
不是跨 OS/平台二进制可移植的。 
?  HEAP   
HEAP 表格使用一个杂凑（hashed ）索引并且存储在内存中。这使他们更快，但是如
果MySQL崩溃，你将失去所有存储的数据。HEAP 作为临时表很可用！ 
3.4.1  用SHOW/ DESCRIBE语句显示数据表的信息 
句法： 
SHOW TABLES [FROM db_name] [LIKE wild] 
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] 
or SHOW INDEX FROM tbl_name [FROM db_name]  
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]  
 
{DESCRIBE | DESC} tbl_name {col_name | wild} 
# flag leizhou 
 
你能使用 db_name.tbl_name 作为 tbl_name FROM db_name 句法的另一种选择。 
?  SHOW TABLES 列出在一个给定的数据库中的表。你也可以用 mysqlshow 
db_name 命令得到这张表。   
注意：如果一个用户没有一个表的任何权限，表将不在SHOW TABLES 或
mysqlshow db_name中的输出中显示。 
?  SHOW COLUMNS 列出在一个给定表中的列。如果列类型不同于你期望的是基
于CREATE TABLE语句的那样，注意，MySQL有时改变列类型。 
?  DESCRIBE 语句提供了类似 SHOW COLUMNS的信息。DESCRIBE 提供关于一
张表的列的信息。col_name 可以是一个列名字或包含 SQL 的“%”和“_”通配
符的一个字符串。这个语句为了与  Oracle  兼容而提供的。   
?  SHOW TABLE STATUS（在版本 3.23 引入)运行类似 SHOW STATUS ，但是提供
每个表的更多信息。你也可以使用 mysqlshow -- status db_name命令得到这张表。   
?  SHOW FIELDS 是SHOW COLUMNS 一个同义词，SHOW KEYS是SHOW 
INDEX一个同义词。 
?  你也可以用 mysqlshow db_name tbl_name或mysqlshow -k db_name tbl_name  列 
出一张表的列或索引。 
?  SHOW INDEX以非常相似于 ODBC 的SQLStatistics 调用的格式返回索引信息。 
3.4.2  使用 mysqlshow 工具得到信息 
下面简单介绍一下 mysqlshow 实用程序的用法，在得到数据库和表的信息上，使用
起来非常方便。 
得到已有数据库的列表： 
shell> mysqlshow   
列出某数据库 db_name 中已有的表： 
shell> mysqlshow db_name  
列出某数据库表 db_name.tbl_name 的结构信息： 
shell>mysqlshow db_name tbl_name  
列出一张表的索引： 
shell> mysqlshow –k db_name tbl_name 
3.4.3  用CREATE TABLE  语句创建数据表 
用  CREATE TABLE  语句创建表。此语句的完整语法是相当复杂的，因为存在那么
多的可选子句，但在实际中此语句的应用相当简单。如我们在第  1  章中使用的所有 
CREATE TABLE 语句都不那么复杂。 
有意思的是，大多数复杂东西都是一些子句，这些子句MySQL在分析后扔掉。参阅
附录  1  可以看到这些复杂的东西。 
     3  
   数据库基本操作          
 
1、CREATE TABLE  语句的基本语法 
CREATE TABLE tbl_name(create_definition,...) [TYPE =table_type] 
create_definition:  col_name type [NOT NULL | NULL] [DEFAULT default_value] 
[AUTO_INCREMENT][PRIMARY KEY]  
在MySQL3.22 或以后版本中，表名可以被指定为 db_name.tbl_name ，不管有没有当
前的数据库都可以。 
例如，创建一个访问者留言表： 
shell> mysql –u root  –p 
mysql> create database mytest; 
mysql> CREATE   TABLE  guestbook 
-> ( 
-> visitor VARCHAR(40), 
-> comments TEXT,  
-> entrydate DATETIME  
->);   
如果一切正常，祝贺你，你已经建立了你的第一个表！   
你所创建的表名为 guestbook，你可以使用这个表来存储来字你站点访问者的信息。
你是用REE ATE TABLE 语句创建的这个表，这个语句有两部分：第一部份指定表的名
子；第二部份是括在括号中的各字段的名称和属性，相互之间用逗号隔开。   
表guestbook有三个字段：visitor,comments  和entrydate 。visitor 字段存储访问者的名
字，comments 字段存储访问者对你站点的意见，entrydate 字段存储访问者访问你站点的
日期和时间。   
注意每个字段名后面都跟有一个专门的表达式。例如，字段名comments 后面跟有表
达式 TEXT。这个表达式指定了字段的数据类型。数据类型决定了一个字段可以存储什么
样的数据。因为字段 comments包含文本信息，其数据类型定义为文本型。   
2、如何指定表的类型 
你也可以在创建表时指定表的类型，如果不指定表的类型，在 3.22 及以前版本中缺
省为 ISAM 表，在 3.23 版本中缺省为 MyISAM 表。你应该尽量使用 MyISAM 表。指定
表的类型经常用于创建一个 HEAP 表： 
mysql> CREATE TABLE fast(id int,articles TEXT) TYPE=HEAP; 
 
3、隐含的列说明的改变 
在某些情况下，MySQL隐含地改变在一个CREATE TABLE 语句给出的一个列说
明。（这也可能在 ALTER TABLE。）   
?  长度小于 4 的VARCHAR 被改变为 CHAR 。   
?  如果在一个表中的任何列有可变长度，结果是整个行是变长的。因此,  如果一张
表包含任何变长的列(VARCHAR、TEXT 或BLOB) ，所有大于3 个字符的
CHAR 列被改变为VARCHAR 列。这在任何方面都不影响你如何使用列；在
 
MySQL中，VARCHAR 只是存储字符的一个不同方法。MySQL实施这种改变，
是因为它节省空间并且使表操作更快捷。   
?  TIMESTAMP 的显示尺寸必须是偶数且在 2  ～  14 的范围内。如果你指定 0 显示
尺寸或比 14 大，尺寸被强制为 14 。从 1～13 范围内的奇数值尺寸被强制为下一
个更大的偶数。   
?  你不能在一个 TIMESTAMP 列里面存储一个文字 NULL；将它设为 NULL 将设
置为当前的日期和时间。因为TIMESTAMP 列表现就是这样，NULL 和NOT 
NULL 属性不以一般的方式运用并且如果你指定他们，将被忽略。DESCRIBE 
tbl_name 总是报告该 TIMESTAMP 列可能赋予了 NULL 值。   
如果你想要知道 MySQL是否使用了除你指定的以外的一种列类型，在创建或改变你
的表之后，发出一个 DESCRIBE tbl_name 语句即可。 
3.4.4利用  SELECT 的结果创建表 
关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条 
SELECT  语句的结果也都是一个行和列组成的表。在许多情况下，来自  SELECT  的
“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在  MySQL 3.23  以前，
如果想将  SELECT  的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排： 
1) 运行  DESCRIBE 或  SHOW COLUMNS  查询以确定想从中获取信息的表中的列
类型。 
2) 创建一个表，明确地指定刚才查看到的列的名称和类型。 
3) 在创建了该表后，发布一条  INSERT ... SELECT  查询，检索出结果并将它们插入
所创建的表中。 
在  MySQL 3.23 中，全都作了改动。CREATE TABLE ... SELECT 语句消除了这些浪
费时间的东西，使得能利用  SELECT  查询的结果直接得出一个新表。只需一步就可以完
成任务，不必知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的
数据填充的表，并且为进一步查询作了准备。 
?  如果你在 CREATE 语句后指定一个 SELECT ，MySQL将为在 SELECT 中所有的单元
创键新字段。例如： 
mysql> CREATE TABLE test  
->  (a int not null auto_increment,primary key (a), key(b))  
->  SELECT b,c from test2; 
这将创建一个有 3 个列(a ，b，c) 的表，其中 b，c 列的数据来自表 test2 。注意如果在
拷贝数据进表时发生任何错误，表将自动被删除。   
 
?  可以通过选择一个表的全部内容（无  WHERE  子句）来拷贝一个表，或利用一个总
是失败的  WHERE  子句来创建一个空表，如： 
mysql> CREATE TABLE test SELECT * from test2;  
mysql> CREATE TABLE test SELECT * from test2 where 0; 
 
如果希望利用  LOAD DATA  将一个数据文件装入原来的文件中，而不敢肯定是否具
有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时
以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用  LOAD  
D ATA  的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数
据装入原表了。 
可结合使用  CREATE TEMPORARY TABLE 与  SELECT  来创建一个临时表作为它
自身的拷贝，如： 
这允许修改  my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查
询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，
不需要为引用不同的表而编辑这些脚本；只需在脚本的起始处增加  CREATE 
TEMPORARY TABLE  语句即可。相应的脚本将创建一个临时拷贝，并对此拷贝进行操
作，当脚本结束时服务器会自动删除这个拷贝。 
要创建一个作为自身的空拷贝的表，可以与  CREATE TEMPORARY ... SELECT  一
起使用  WHERE 0 子句，例如： 
但创建空表时有几点要注意。在创建一个通过选择数据填充的表时，其列名来自所
选择的列名。如果某个列作为表达式的结果计算，则该列的“名称”为表达式的文本。表
达式不是合法的列名，可在  mysql  中运行下列查询了解这一点： 
为了正常工作，可为该列提供一个合法的别称： 
如果选择了来自不同表的具有相同名称的列，将会出现一定的困难。假定表  t1  和 
t2  两者都具有列  c ，而您希望创建一个来自两个表中行的所有组合的表。那么可以提供
别名指定新表中惟一性的列名，如： 
通过选择数据进行填充来创建一个表并会自动拷贝原表的索引。 
3.4.5  用ALTER TABLE 语句修改表的结构 
有时你可能需要改变一下现有表的结构，那么Alter Table语句将是你的合适选择。 
?  增加列 
alter table tbl_name add col_name type 
例如,给表增加一列 weight 
mysql>alter table pet add  weight int; 
?  删除列 
alter table tbl_name drop col_name  
例如，删除列 weight： 
mysql>alter table pet drop weight; 
?  改变列 
alter table tbl_name modify col_name type  
例如，改变 weight 的类型： 
mysql> alter table pet modify weight samllint; 
另一种方法是： 
alter table tbl_name change old_col_n ame col_name type  
例如： 
mysql> alter table pet change weight weight samllint; 
?  给列更名 
mysql>alter table pet change weight wei; 
?  给表更名 
alter table tbl_name rename new_tbl   
例如，把 pet表更名为 animal  
mysql>alter table pet rename animal; 
?  改变表的类型 
 
另外，可以为列增加或删除索引等属性，不再详述，请参阅附录。 
   
3.4.6  用DROP TABLE  语句删除数据表 
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] 
DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除，故小
心使用这个命令！   
在MySQL 3.22或以后版本，你可以使用关键词 IF EXISTS 类避免不存在表的一个错
误发生。   
例如： 
mysql> USE  mytest;  
mysql> DROP TABLE  guestbook; 
或者，也可以同时指定数据库和表： 
mysql> DROP TABLE mytest. guestbook; 
3.4.7  总结 
本节讲述了有关表的大部分操作，现在将所述内容总结如下： 
?  MySQL的表的三种类型 
?  如何创建表、删除表 
?  如何改变表的结构、名字 
?  如何使用 mysqlshow实用程序 
3.5  向数据表插入行记录 
3.5.1  使用 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); 
3.5.2  使用 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 将可能
发现在同一个运行期间内先前被插入的记录。当使用子选择子句时，情况能很容
易混淆） 
3.5.3  使用 replace、replace…select 语句插入 
REPLACE 功能与 INSERT 完全一样，除了如果在表中的一个老记录具有在一个唯一
索引上的新记录有相同的值，在新记录被插入之前，老记录被删除。对于这种情况，
insert 语句的表现是产生一个错误。 
REPLACE 语句也可以褐 SELECT 相配合，所以上两小节的内容完全适合
REPALCE.。 
应该注意的是，由于 REPLACE语句可能改变原有的记录，因此使用时要小心。 
3.5.4  使用 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 ' \t' ENCLOSED BY '' ESCAPED BY ' \\' 
如果你不指定一个 LINES 子句，缺省值与如果你这样写的相同：   
LINES TERMINATED BY '\n'   
换句话说，缺省值导致读取输入时，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 '\n';  
 
任何字段或行处理选项可以指定一个空字符串('') 。如果不是空，FIELDS 
[OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPED BY 值必须是一个单个字符。
FIELDS TERMINATED BY 和LINES TERMINATED BY值可以是超过一个字符。例如，
 
写入由回车换行符对（CR+LF）终止的行，或读取包含这样行的一个文件，指定一个
LINES TERMINATED BY '\r\n' 子句。   
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 ”（即，\0 或
\N，如果转义字符是“\”)。这些序列被解释为 ASCII 0（一个零值字节）和 NULL。见
下面关于 NULL 处理的规则。   
3.5.5  总结 
为数据库装载数据是管理员的重要职责之一，正因为重要，所以MySQL提供的方法
也是非常繁多。其中主要的在本节已经列举： 
1、 使用 INSERT 、REPLACE语句 
2、 使用 INSERT/REPLACE…SELECT 语句 
3、 使用 LOAD DATA INFILE 语句 
4、 使用实用程序 mysqlimport（将在第五章介绍） 
3.6  查询数据表中的记录 
除非最终检索它们并利用它们来做点事情，否则将记录放入数据库没什么好处。这
就是  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 有所不同，它允许对表达式求值
而不引用任何表。 
3.6.1  普通查询 
?  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>se lect (2+3*4.5)/2.5;  
其结果为： 
+---------------+ 
| (2+3*4.5)/2.5 | 
+---------------+ 
|         6.200 | 
+---------------+ 
 
3.6.2  条件查询 
不必每次查询都返回所有的行记录，你能从你的表中只选择特定的行。为此你需要
使用 WHERE或者 HAVING 从句。HAVING 从句与 WHERE从句的区别是，HAVING 表
达的是第二条件，在与其他从句配合使用，显然不能在WHERE 子句中的项目使用
HAVING 。因此本小节紧介绍 WHERE从句的使用，HAVING 从句的使用方法类似。另外
WHERE从句也可以实现 HAVING 从句的绝大部分功能。 
为了限制  SELECT  语句检索出来的记录集，可使用  WHERE  子句，它给出选择行
的条件。可通过查找满足各种条件的列值来选择行。 
WHERE  子句中的表达式可使用表 1-1  中的算术运算符、表 1-2  的比较运算符和表
1-3  的逻辑运算符。还可以使用圆括号将一个表达式分成几个部分。可使用常量、表列和
函数来完成运算。在本教程的查询中，我们有时使用几个  MySQL 函数，但是  MySQL 
的函数远不止这里给出的这些。请参阅附录  一，那里给出了所有 MySQL 函数的清单。 
表3-1    算术运算符 
运算符  说明  运算符  说明 
+  加  *  乘 
-  减 
/  除 
 
表3-2    比较运算符 
运算符  说明  运算符  说明 
<  小于  !=  或  <>  不等于 
<=  小于或等于  >=  大于或等于 
=  等于  >  大于 
表3-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 以
后出生的，测试 bi rth 列：   
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      | d eath |  
+-------+-------- +---------+------ +------------ +-------+ 
| Claws | Gwen   | cat     | m    | 1994 -03 -17 | NULL  |  
| Buffy | Harold | dog     | f    | 1989 -05 -13 | NULL  |  
+-------+-------- +---------+------ +------------ +-------+ 
3.6.3  查询排序 
使用 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 |  
| Pu ffball | 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 降序排
列。 
 
3.6.4  查询分组与行计数 
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 | 
+---------+------ +---------- + 
 
3.6.5  查询多个表 
查询多个表，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     |  
+-------- +------ +-------- +------ +---------+ 
 
在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪
个表实例更直观。 
3.3.6总结 
本文总结了 SELECT 语句检索记录的简单使用方法。其中涉及到的内容主要包括以
下一些内容： 
1、 WHERE从句的用法 
2、 GROUP BY 从句的用法 
3、 ORDER BY 从句的用法 
4、 连接多个表的简单介绍 
3.7  修改、删除数据记录 
有时，希望除去某些记录或更改它们的内容。DELETE  和  UPDATE 语句令我们能
做到这一点。 
3.7.1  用update修改记录 
UPDATE tbl_name SET  要更改的列 
WHERE  要更新的记录 
这里的  WHERE  子句是可选的，因此如果不指定的话，表中的每个记录都被更新。 
例如，在 pet 表中，我们发现宠物 Whistler的性别没有指定，因此我们可以这样修改
这个记录： 
mysql> update pet set sex=’f ’ where name=” Whistler” ; 
 
3.7.2  用delete 删除记录 
DELETE  语句有如下格式： 
DELETE FROM tbl_name WHERE  要删除的记录 
WHERE  子句指定哪些记录应该删除。它是可选的，但是如果不选的话，将会删除
所有的记录。这意味着最简单的  DELETE  语句也是最危险的。 
这个查询将清除表中的所有内容。一定要当心！ 
为了删除特定的记录，可用  WHERE  子句来选择所要删除的记录。这类似于 
SELECT  语句中的  WHERE  子句。 
mysql> delete from pet where name=”Whistler”; 
可以用下面的语句清空整个表： 
mysql>delete from pet; 
3.7.3  总结 
本节介绍了两个 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   | bi rd    | 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 环境中，那么文件的换行符是”\r\n”。 
如果是使用实用程序 mysqlimport命令行如何书写。 
5、重复本章中的 3.6节对表 pet进行检索的例子。 
本章要点： 
?  集合函数与时间函数 
?  字符串的模式匹配 
?  如何创建索引 
?  检索数据中的一些技巧 
 
第三章向你初步介绍了 SQL 。你学会了如何用 SELECT 语句进行查询，你还
学会了如何建立自己的表以及如何录入数据等。在这一章里，你将加深你SQL 语
言知识。你将学习如何建立索引来加快查询速度。你还将学会如果用更多的 SQL
语句和函数来操作表中的数据。 
尤其是对检索语句 SELECT 的使用，其中技巧繁多，可以说这是 SQL 语言中
最重要的语句，也是使用者最常使用的语句。本章将详细介绍 SELECT 语句的用
法。 
无论如何，这里的介绍都不能包括 SQL 语言的所有技巧，读者应该在平时的
不断使用中积累经验。 


MYSQL 高级特性 
第4 章 
4.1   集合函数 
到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，
假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测
验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的
任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小
值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个
统计值之一。 
这些函数的最大特点就是经常和 GROUP BY语句配合使用，需要注意的是集合函数
不能和非分组的列混合使用。 
4.1.1  行列计数 
?  计算查询语句返回的记录行数 
直接计算函数 COUNT(*)的值，例如，计算 pet表中猫的只数： 
mysql> SELECT  count(*)  FROM  pet WHERE species= ’cat’; 
+---------- + 
| count(*) |  
+---------- + 
|        2 |  
+---------- + 
4.1.2统计字段值的数目 
例如，计算 pet表中 species 列的数目： 
mysql> SELECT  count(species)  FROM  pet; 
+---------------- + 
| count(species) |  
+---------------- + 
|              9 |  
+---------------- + 
如果相同的种类出现了不止一次，该种类将会被计算多次。如果你想知道种类为某
个特定值的宠物有多少个，你可以使用WHERE子句，如下例所示：   
mysql> SELECT COUNT(species) FRO M pet WHERE species='cat' ; 
注意这条语句的结果： 
+---------------- + 
| COUNT(species) |  
+---------------- + 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
83 
 
|              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 |  
+---------+---------- + 
4.1.3  计算字段的平均值 
需要计算这些值的平均值。使用函数 AV G（），你可以返回一个字段中所有值的平
均值。   
假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1 到10 之间投票，
表示他们喜欢你站点的程度。你把投票结果保存在名为 vote 的INT 型字段中。要计算你
的用户投票的平均值，你需要使用函数AV G（）：   
SELECT AVG(vote) FROM opinion   
这个 SELECT 语句的返回值代表用户对你站点的平均喜欢程度。函数 AV G（）只能
 
    MySQL 金典培训教程 
 
 
 
84 
http://www.yurennet.com  
 
对数值型字段使用。这个函数在计算平均值时也忽略空值。   
再给出一个实际例子，例如我们要计算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 |                198 90 |  
| snake   |                49791 |  
+---------+---------------------- + 
4.1.4  计算字段值的和 
假设你的站点被用来出售某种商品，已经运行了两个月，是该计算赚了多少钱的时
候了。假设有一个名为 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 |  
+---------+---------------------- + 
4.1.5  计算字段值的极值 
求字段的极值，涉及两个函数 MAX() 和MIN()。 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
85 
 
例如，还是 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 |  
 
    MySQL 金典培训教程 
 
 
 
86 
http://www.yurennet.com  
 
+---------+------------ + 
 
4.1.6  总结 
在本节中，介绍了一些典型的集合函数的用法，包括计数、均值、极值和总和，这
些都是 SQL 语言中非常常用的函数。 
这些函数之所以称之为集合函数，是因为它们应用在多条记录中，所以集合函数最
常见的用法就是与 GROUP BY子句配合使用，最重要的是集合函数不能同未分组的列混
合使用。 
4.2  操作日期和时间 
日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据
何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。 
日期和时间类型是 DATETIME 、D AT E、TIMESTAMP 、TI ME 和YEAR。这些的每
一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，
MySQL允许你存储某个“不严格地”合法的日期值，例如 1999 -11 -31 ，原因我们认为它
是应用程序的责任来处理日期检查，而不是SQL 服务器。为了使日期检查更“快”，
MySQL仅检查月份在0-12 的范围，天在0-31 的范围。上述范围这样被定义是因为
MySQL允许你在一个 D AT E 或DATETIME 列中存储日期，这里的天或月是零。这对存
储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单
地存储日期象1999 -00 -00 或1999 -01 -00 。（当然你不能期望从函数如DATE_SUB()或
DATE_ADD() 得到类似以这些日期的正确值）。 
4.2.1  返回当前日期和时间 
通过函数 GETDATE（），你可以获得当前的日期和时间。例如， 
? 
CURDATE()  返回当前日期 
CURRENT_DATE   
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符
串还是数字上下文被使用。   
mysql> select CURDATE(); 
+------------ + 
| CURDATE()  | 
+------------ + 
| 2001 -02 -20 |  
+------------ + 
mysql> select CURDATE() + 0; 
+-------------+ 
| CURDATE()+0 | 
+-------------+ 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
87 
 
|    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 | 
+---------- +-----------------+ 
 
    MySQL 金典培训教程 
 
 
 
88 
http://www.yurennet.com  
 
| Fluffy   |           80016 |  
| Claw s    |           69903 |  
| Buffy    |          119707 |  
| Chirpy   |           29309 |  
| Fang     |          109393 |  
| Bowser   |          109389 |  
| Whistler |           39011 |  
| Slim     |           49791 |  
| Puffball |           19890 |  
+---------- +-- ---------------+ 
4.2.2  自动记录数据的改变时间 
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;  
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
89 
 
 
+------ +---------+---------+---------+---------+----------------+ 
| 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 列为它的当
前值。   
例如，当你在修改列时，可以把原有的值付给TI ME S TAM P 列： 
mysql> UPDATE student SET english=66,time=time WHERE id=11; 
mysql> select * from student where id=11;  
+------ +------ +---------+---------+---------+---------------- + 
| id   | name | english | chinese | history | time           | 
 
    MySQL 金典培训教程 
 
 
 
90 
http://www.yurennet.com  
 
+------ +------ +---------+---------+---------+---------------- + 
|   11 | Tom  |      66 |      93 |      67 | 200102 20130517 |  
+------ +------ +---------+---------+---------+---------------- + 
另一方面，你可能发现，当你想要实现上面这个效果时，很容易用一个你用 NOW()
初始化的 DATETIME 列然后不再改变它，这样也许直接些。  但是，TIMESTAMP 列的以
后好处是存储要求比较小，节省空间。TIMESTAMP 的存储需求是 4 字节，而
DATETIME 列的存储需求是 8 字节。 
4.2.3  返回日期和时间范围 
当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表
来模仿一个 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 "   
不要这样做。这个 SEL ECT 语句不会返回正确的记录――它将只返回值为 2000 -02 -
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
91 
 
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 | 
+-----------+---------------------+ 
 
    MySQL 金典培训教程 
 
 
 
92 
http://www.yurennet.com  
 
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。 
4.2.5  比较日期和时间 
已知两个日期，比较它们的前后，可以直接求出它们的差和零值比较，也可以利用
已知的时间函数： 
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 |  
+-------------------------------- -------------------+ 
 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
93 
 
4.3  字符串模式匹配 
MySQL提供标准的 SQL 模式匹配，以及一种基于象 Unix 实用程序如 vi 、grep 和
sed 的扩展正则表达式模式匹配的格式。   
4.3.1  标准的 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 金典培训教程 
 
 
 
94 
http://www.yurennet.com  
 
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  |  
+-------+-------- +---------+------ +------------ +-------+ 
4.3.2  扩展正则表达式模式匹配 
由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 |  
+-------- +-------- +---------+------ +------------ +------------ + 
 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
95 
 
为了找出以“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}$"; 
+-------+-------- +---------+------ +------------ +-------+ 
 
    MySQL 金典培训教程 
 
 
 
96 
http://www.yurennet.com  
 
| name  | owner  | species | sex  | birth      | death | 
+-------+-------- +---------+------ +------------ +-------+ 
| Claws | Gwen   | cat     | m    | 1994 -03 -17 | NULL  |  
| Buffy | Harold | dog     | f    | 1989 -05 -13 | NULL  |  
+-------+-------- +---------+------ +------------ +-------+ 
4.3.3  总结 
4.3 节介绍了有关字符串模式匹配的有关知识。标准的 SQL 模式匹配是 SQL 语言的
标准，可以被其它关系数据库系统接受。扩展正规表达式模式匹配是根据Unix 系统的标
准开发了，一般只可使用在 MySQL上，但是其功能要比标准的 SQL 模式匹配更强。 
4.4  深入 SELECT 的查询功能 
本节将讲述 SELECT 语句的一些高级功能。 
4.4.1  列和表的别名 
4.4.1.1列的别名 
精选输出的列可以用列名、列别名或列位置在 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; 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
97 
 
注意返回的结果： 
+---------- +---------+ 
| n        | s       |  
+---------- +---------+ 
| Bowser   | dog     |  
| Bu ffy    | 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  | 
+---------- +-------- + 
 
    MySQL 金典培训教程 
 
 
 
98 
http://www.yurennet.com  
 
4.4.1.2 在子句中使用列的别名 
你可以在 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 用来决
定应该只用结果集合中的哪些行。   
4.4.1.3表的别名 
别名不仅可以应用于列，也可以引用于表名，具体方法类似于列的别名，这里不再
重复。 
列的别名经常用于表自身的连接中。你不必有 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     | 
+-------- +------ +-------- +------ +---------+ 
 
在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
99 
 
个表实例更直观。   
 
4.4.2  取出互不相同的记录 
有时候你可能希望取出的数据互不重复，因为重复的数据可能对你没有意义。 
解决的办法是使用 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 金典培训教程 
 
 
 
100 
http://www.yurennet.com  
 
mysql> SELECT DISTINCT owner,sex FROM pet WHERE owner="Benny"; 
+-------+------ + 
| owner | sex  |  
+-------+------ + 
| Benny | m    |  
+-------+------ + 
DISTINCT 关键字的存在，使查询只返回不同的记录行。 
如果一个表中，有完全相同的行，你可以使用DISTINCT，以去除冗余的输出： 
SELECT DISTINCT * FROM tbl_name  
4.4.3 NULL值的问题 
NULL 值可能很奇怪直到你习惯于它。概念上，NULL 意味着“没有值”或“未知
值”，且它被看作有点与众不同的值。为了测试NULL，你不能使用算术比较运算符例如
=、<或!= 。为了说明它，试试下列查询：   
 
mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; 
+---------- +-----------+---------- +---------- + 
| 1 = NU LL | 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; 
+-------- +-------+---------+------ +------------ +------------ + 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
101 
 
| 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 插入表的第一个
TIMESTAMP 列，则插入当前的日期和时间。如果你将NULL 插入一个
AUTO_INCREMENT 列，则插入顺序中的下一个数字。 
 
    MySQL 金典培训教程 
 
 
 
102 
http://www.yurennet.com  
 
4.4.4  大小写敏感性   
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 |  
+---------+---------------- + 
4.4.5  检索语句与多个表的连接 
SELECT 语句不仅可以从单个表中检索数据，也可以通过连接多个表来检索数据。这
里将介绍全连接和左连接的作用。 
我们创建两个表作为例子。 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
103 
 
mysql> CREATE TABLE first 
  -> ( 
  -> id TINYINT,  
  -> first_name CHAR(10)  
  -> ); 
录入如下数据： 
+------ +-----------+ 
| id   | f irst_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    | 
+------ +-----------+ 
4.4.5.1 全连接 
全连接：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其
他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表
的行数之和。 
那么观察下面的检索的结果： 
mysql> SELECT * FROM first,last; 
+------ +------------ +------ +-----------+ 
| id   | first_name | id   | last_name |  
+------+------------ +------ +-----------+ 
|    1 | Tom        |    2 | Stone     |  
|    2 | Marry      |    2 | Stone     |  
|    3 | Jarry      |    2 | Stone     |  
 
    MySQL 金典培训教程 
 
 
 
104 
http://www.yurennet.com  
 
|    1 | Tom        |    3 | White     |  
|    2 | Marry      |    3 | White     |  
|    3 | Jar ry      |    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;  
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
105 
 
+------ +------------ +------ +-----------+ 
| id   | first_name | id   | last_name | 
+------ +------------ +------ +-----------+ 
|    2 | Marry      |    2 | Stone     |  
|    3 | Jarry      |    3 | White     |  
+------ +------------ +------ +-----------+ 
 
4.4.5.2 左连接 
左连接：全连接给出 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 |  
+------ +------------ +------ +-----------+ 
 
    MySQL 金典培训教程 
 
 
 
106 
http://www.yurennet.com  
 
|    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; 
 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
107 
 
+------ +------------ +------ +-----------+ 
| 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     |  
 
    MySQL 金典培训教程 
 
 
 
108 
http://www.yurennet.com  
 
|    1 | Tom        |    4 | Donald    |  
|    2 | Marry      |    3 | White     |  
|    2 | Marry      |    4 | Donald    |  
|    3 | J arry      |    2 | Stone     |  
|    3 | Jarry      |    4 | Donald    |  
+------ +------------ +------ +-----------+ 
4.4.6  总结 
本节的内容非常繁杂，各小节之间可能没有什么联系，但是本节所述的都是检索数
据时很常用的技巧，主要的一些内容如下： 
1、 为表和列使用别名 
2、 注意 NULL 值在查询中的使用 
3、 注意表名、列名、别名和字符串的大小写问题 
4、 如何避免取出重复的记录 
4.5  索引属性 
索引是加速表内容访问的主要手段，特别对涉及多个表的连接的查询更是如此。这
是第  8  章数据库优化中的一个重要内容，第 4 章讨论了为什么需要索引，索引如何工作
以及怎样利用它们来优化查询。本节中，我们将介绍索引的特点，以及创建和删除索引的
语法。 
4.5.1  索引的特点 
所有的 MySQL列类型能被索引。在相关的列上的使用索引是改进 SELECT 操作性
能的最好方法。   
?  一个表最多可有16 个索引。最大索引长度是256 个字节，尽管这可以在编译
MySQL时被改变。   
?  对于 CHAR 和VARCHAR 列，你可以索引列的前缀。这更快并且比索引整个列
需要较少的磁盘空间。对于 BLOB 和TEXT 列，你必须索引列的前缀，你不能
索引列的全部。   
?  MySQL能在多个列上创建索引。一个索引可以由最多15 个列组成。（在 CHAR
和VARCHAR 列上，你也可以使用列的前缀作为一个索引的部分）。 
虽然随着  MyS QL  的进一步开发创建索引的约束将会越来越少，但现在还是存在一
些约束的。下面的表根据索引的特性，给出了  ISAM  表和  MyISAM 表之间的差别： 
 
表2-1  通道信息特征字对照表 
索引的特点  ISAM  表  MyISAM 表 
NULL  值  不允许  允许 
BLOB  和  TEXT  列  不能索引  只能索引列的前缀 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
109 
 
每个表中的索引数  16   32  
每个索引中的列数  16   16  
最大索引行尺寸  256  字节  500  字节 
 
从此表中可以看到，对于  ISAM  表来说，其索引列必须定义为  NOT NULL，并且
不能对  BLOB  和  TEXT  列进行索引。MyISAM 表类型去掉了这些限制，而且减缓了其
他的一些限制。两种表类型的索引特性的差异表明，根据所使用的  MySQL 版本的不
同，有可能对某些列不能进行索引。例如，如果使用 3.23  版以前的版本，则不能对包含 
NULL  值的列进行索引。 
索引有如下的几种情况： 
?  INDEX 索引：通常意义的索引，某些情况下 KEY是它的一个同义词。索引的列
可以包括重复的值。 
?  UNIQUE 索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保
证值的组合不重复。 
?  PRIMAR Y KEY索引：也 UNIQUE 索引非常类似。事实上，PRIMARY KEY 索
引仅是一个具有 PRIMARY 名称的 UNIQUE 索引。这表示一个表只能包含一个
PRIMARY KEY 。 
4.5.2  用Alter Table 语句创建与删除索引 
为了给现有的表增加一个索引，可使用  ALTER TABLE  或  CREATE INDEX 语句。
ALTER TABLE  最常用，因为可用它来创建普通索引、UNIQUE 索引或  PRIMARY KEY 
索引，如： 
ALTER TABLE tbl_name ADD INDEX in dex_name  (column_list) 
ALTER TABLE tbl_name ADD UNIQUE index_name    (column_list)  
ALTER TABLE tbl_name ADD PRIMARY KEY index_name    (column_list)  
其中  tbl_name  是要增加索引的表名，而  column_list  指出对哪些列进行索引。一个
(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由
不止一列组成，各列名之间用逗号分隔。索引名  index_name  是可选的，因此可以不写
它，MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE  允许在单个语句中指
定多个表的更改，因此可以在同时创建多个索引。 
同样，也可以用 ALTER TABLE语句产出列的索引： 
ALTER TABLE tbl_name DROP INDEX index_name 
ALTER TABLE tbl_name DROP PRIMARY KEY 
注意上面第一条语句可以用来删除各种类型的索引，而第三条语句只在删除 
PRIMARY KEY 索引时使用；在此情形中，不需要索引名，因为一个表只可能具有一个
这样的索引。如果没有明确地创建作为  PRIMARY KEY 的索引，但该表具有一个或多个 
UNIQUE 索引，则  MySQL 将删除这些  UNIQUE 索引中的第一个。 
如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部
 
    MySQL 金典培训教程 
 
 
 
110 
http://www.yurennet.com  
 
分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。 
例如，对于上面所使用的 student 为例，你可能想为之创建这样的索引，以加速表的
检索速度： 
mysql> ALTER TABLE  st udent   
  -> ADD PRIMARY KEY(id), 
  -> ADD INDEX mark(english, Chinese,history);  
这个例子，既包括 PRIMARY 索引，也包括多列索引。记住，使用 PRIMARY 索引
的列，必须是一个具有 NOT NULL属性的列，如果你愿意产看创建的索引的情况，可以
使用 SHOW INDEX语句： 
mysql> SHOW INDEX FROM student;  
其结果为： 
+---------+------------ +---------- +--------------+-------------+- 
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | 
+---------+------------ +---------- +-------------- +-------------+- 
| student |          0 | PRIMARY  |            1 | id          | 
| student |          1 | mark     |             1 | english     | 
| student |          1 | mark     |            2 | chinese     | 
| student |          1 | mark     |            3 | history     | 
+---------+------------ +---------- +-------------- +-------------+- 
由于列数太多，上表并没有包括所有的输出，读者可以试着自己查看。 
再使用 ALTER TABLE 语句删除索引，删除索引需要知道索引的名字，你可以通过
SHOW INDEX语句得到： 
mysql> ALTER TABLE student DROP PRIMARY KEY, 
     -> DROP INDEX mark; 
再产看表中的索引，其语句和输出为： 
mysql> SHOW INDEX FROM student;  
Empty set (0.01 sec) 
4.5.3  用CREATE \DROP INDEX创建索引 
还可以用 CREATE INDEX语句来创建索引.CREATE INDEX  是在  MySQL 3.23版中
引入的，但如果使用 3.23  版以前的版本，可利用  ALTER TABLE 语句创建索引
（MySQL 通常在内部将  CREATE INDEX 映射到  ALTER TABLE）。该语句创建索引的
语法如下： 
CREATE UNIQUE INDEX index_name ON tbl_name (column_list) 
CREATE INDEX index_name ON tbl_name (column_l ist) 
tbl_name 、index_name  和  column_list  具有与  ALTER TABLE  语句中相同的含义。
这里索引名不可选。很明显，CREATE INDEX  可对表增加普通索引或  UNIQUE 索引，
不能用  CREATE INDEX 语句创建  PRIMARY KEY  索引。 
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
111 
 
可利用  DROP INDEX 语句来删除索引。类似于  CREATE INDEX  语句，DROP 
INDEX  通常在内部作为一条  ALTER TABLE 语句处理，并且DROP INDEX 是在 
MySQL 3.22 中引入的。 
删除索引语句的语法如下： 
DROP INDEX index_name ON tbl_name 
还是上一节的例子，由于 CREATE INDEX不能创建 PRIMARY 索引，所以这里我们
值创建一个多列索引： 
mysql> CREATE INDEX mark ON student(english,chinese,history); 
同样的检查 student 表，可知： 
mysql> SHOW INDEX FROM student;  
+---------+------------ +---------- +--------------+-------------+ 
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | 
+---------+------------ +---------- +-------------- +-------------+ 
| student |          1 | mark     |            1 | english     | 
| student |          1 | mark      |            2 | chinese     |  
| student |          1 | mark     |            3 | history     | 
+---------+------------ +---------- +-------------- +-------------+ 
然后使用下面的语句删除索引： 
mysql> DROP INDEX mark ON student; 
4.5.4  在创建表时指定索引 
要想在发布  CREATE TABLE  语句时为新表创建索引，所使用的语法类似于  ALTER 
TABLE  语句的语法，但是应该在您定义表列的语句部分指定索引创建子句，如下所示： 
CREATE TABLE tbl_name  
( 
… 
INDEX index_name (column_list),  
KEY index_name (column_list),  
UNIQUE index_name (column_list), 
PRIMARY KEY index_name (column_list), 
… 
) 
与ALTER TABLE  一样，索引名对于  INDEX  和  UNIQUE 都是可选的，如果未给
出，MySQL 将为其选一个。另外，这里 KEY时INDEX的一个别名，具有相同的意义。 
有一种特殊情形：可在列定义之后增加  PRIMARY KEY 创建一个单列的 PRIMARY 
KEY 索引，如下所示： 
CREATE TABLE tbl_name  
(   
 
    MySQL 金典培训教程 
 
 
 
112 
http://www.yurennet.com  
 
   i INT NOT NULL PRIM ARY KEY 
) 
该语句等价于以下的语句: 
CREATE TABLE tbl_name  
( 
   i INT NOT NULL,  
   PRIMARY KEY (i)  
) 
前面所有表创建样例都对索引列指定了  NOT NULL。如果是  ISAM  表，这是必须
的，因为不能对可能包含  NULL  值的列进行索引。如果是  MyISAM 表，索引列可以为 
NULL，只要该索引不是  PRIMARY KEY  索引即可。 
在CREATE TBALE 语句中可以某个串列的前缀进行索引（列值的最左边  n  个字
符）。 
如果对某个串列的前缀进行索引，应用  column_list  说明符表示该列的语法为 
col_name(n)  而不用 col_name 。例如，下面第一条语句创建了一个具有两个  CHAR  列的
表和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引： 
CREATE TABLE tbl_name  
( 
  name CHAR(30),  
  address CHAR(60),  
  INDEX (name,address) 
) 
CREATE TABLE tbl_name  
( 
  name CHAR(30),  
  address CH AR(60), 
  INDEX (name(10),address(20))  
) 
你可以检查所创建表的索引： 
 
mysql> SHOW INDEX FROM tbl_name;  
+---------- +------------ +---------- +-------------- +-------------+- 
| Table    | Non_unique | Key_name | Seq_in_index | Column_name |  
+---------- +------------ +---------- +-------------- +-------------+- 
| tbl_name |          1 | name     |            1 | name        |  
| tbl_name |          1 | name     |            2 | address     |  
+---------- +------------ +---------- +-------------- +-------------+- 
在某些情况下，可能会发现必须对列的前缀进行索引。例如，索引行的长度有一个
     4  
   MySQL高级特性         
 
http://www.yurennet.com        
113 
 
最大上限，因此，如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索
引。在  MyISAM 表索引中，对  BLOB  或  TEXT  列也需要前缀索引。 
对一个列的前缀进行索引限制了以后对该列的更改；不能在不删除该索引并使用较
短前缀的情况下，将该列缩短为一个长度小于索引所用前缀的长度的列。 
4.5.5  总结 
本节对索引的类型，已经如何创建索引做了介绍，其中涉及三个比较重要的 SQL 语
句——ALTER TABLE、CREATE/DROP INDEX 和CREATE TABLE，注意它们的用法。 
索引最重要的功能是，通过使用索引加速表的检索，有关这方面的知识，将在第十
章数据库优化中介绍