目录

第3章        使用mysql

第4章        检索数据

第5章        排序检索数据

第6章        过滤数据

第7章        数据过滤

第8章        用通配符进行过滤

第9章        用正则表达式进行搜索

第10章        创建计算字段

第11章        使用数据处理函数

第12章        汇总数据

第13章        分组数据

第14章        使用子查询

第15章        联结表

第16章        创建高级联结

第17章        组合查询

第18章        全文本搜索

第19章        插入数据

第20章        更新和删除数据

第21章        创建和操纵表

第22章        使用视图

第23章        使用存储过程

第24章        使用游标

第27章        全球化和本地化

第28章        安全管理

第29章        数据库维护

第30章        改善性能

---

第3章        使用mysql

show databases;use databasename;
show tables;
show columns from tablename;   #等价于describe table
show create database databasename;   show create table tablename#显示创建特定数据库或表的MySQL语句
SHOW GRANTS;     #用来显示授予用户（所有用户或特定用户）的安全权限；
SHOW ERRORS和SHOW WARNINGS，    用来显示服务器错误或警告消息。
HELP SHOW;显示允许的SHOW语句。

mysql> show create database xuexi; +----------+------------------------------------------------------------------+ | Database | Create Database                                                  | +----------+------------------------------------------------------------------+ | xuexi    | CREATE DATABASE `xuexi` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table  gradeinfo; +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table     | Create Table                                                                                                                                                                                | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | gradeinfo | CREATE TABLE `gradeinfo` (   `gradeId` int(11) NOT NULL AUTO_INCREMENT,   `gradeName` varchar(50) NOT NULL,   PRIMARY KEY (`gradeId`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.33 sec) mysql> show create table gradeinfo \G  #去掉边框 * 1. row *        Table: gradeinfo Create Table: CREATE TABLE `gradeinfo` (   `gradeId` int(11) NOT NULL AUTO_INCREMENT,   `gradeName` varchar(50) NOT NULL,   PRIMARY KEY (`gradeId`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

第4章        检索数据

select * from tablename limit 5;  limit 5只显示5行
select * from tablename limit 1,1; limit 1,显示第一行的下一行即第二行
select * from tablename.columnname from databasename.tablename; 完全限定的列名和表名

第5章        排序检索数据

子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。
select columnname1,columnname2,columnname3 from tablename order by columnname1 DESC,columnname2 DESC limit 1;
在多个列上降序排序 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。LIMIT 1告诉MySQL仅返回一行。ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息

第6章        过滤数据

select columnname1,columnname2 from tablename where columnname = '字符串' order by xxxx;
单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

WHERE子句操作符

操 作 符	说 明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间(BETWEEN匹配范围中所有的值，包括指定的开始值和结束值)

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句:select * from tablename where columnname IS NULL;

第7章        数据过滤

select * from tablename where condition1 and condition2;   #使用了只包含一个关键字AND的语句，把两个过滤条件组合在一起。还可以添加多个过滤条件，每添加一条就要使用一个AND。
select * from tablename where condition1 or condition2;
select * from tablename where condition1  or condition2 and condition3;会检索condition2 and condition3 or condition1的数据
select * from tablename where (condition1 or condition2) and condition3;会检索condition1 and condition3和condition2 and condition3的数据
SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。后一条的唯一差别是，这条语句中，前两个条件用圆括号括了起来。因为圆括号具有较AND或OR操作符高的计算次序，任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。
select * from tablename IN (xx,xx) order by columnname;
select * from tablename NOT IN (xx,xx) order by columnname;
MySQL支持使用NOT 对IN 、BETWEEN 和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

第8章        用通配符进行过滤

select * from tablename where columnname like 'xxx%';
select * from tablename where columnname like 'xx_';
通配符（wildcard） 用来匹配值的一部分的特殊字符。
搜索模式（search pattern）① 由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符:
百分号（%）通配符:，%表示任何字符出现任意次数
下划线（_）通配符:下划线只匹配单个字符而不是多个字符。
为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

使用通配符的技巧：
 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

第9章        用正则表达式进行搜索

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。
select * from tablename where columnname regexp ' 正则表达式';
LIKE匹配整个列。如果被匹配的文本在列值中(即列的一部分)出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。
MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

|:为搜索两个串之一（或者为这个串，或者为另一个串），使用|
[]:匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？可通过指定一组用[和]括起来的字符来完成
regexp '[123] ton'    #匹配列值带有1 ton，2 ton，3 ton
regexp '1|2|3 ton     #匹配列值带有1，2，3 ton
^:字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可
[0-9][a-z]:集合可用来定义要匹配的一个或多个字符,范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符
为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.匹配.。这种处理就是所谓的转义（escaping），\\也用来引用字符（具有特殊含义的字符）

字 符	说 明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配\ 为了匹配反斜杠（\）字符本身，需要使用\\\。\或\\? 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释另一个）。使用预定义的字符集，称为字符类（character class）

字符类

类	说 明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

匹配多个实例，可以用正则表达式重复字符来完成

重复字符

字 符	说 明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

定位符：定位字符

字 符	说 明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

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

简单的正则表达式测试 可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

mysql> select 'hello' regexp '[0-9]'; +------------------------+ | 'hello' regexp '[0-9]' | +------------------------+ |                      0 | #这个例子显然将返回0（因为文本hello中没有数字）。 +------------------------+ 1 row in set (0.00 sec) mysql> select * from vendors; +---------+----------------+-----------------+-------------+------------+----------+--------------+ | vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country | +---------+----------------+-----------------+-------------+------------+----------+--------------+ | 1001 | Anvils R Us | 123 Main Street | Southfield | MI | 48075 | USA | | 1002 | LT Supplies | 500 Park Street | Anytown | OH | 44333 | USA | | 1003 | ACME | 555 High Street | Los Angeles | CA | 90046 | USA | | 1004 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA | | 1005 | Jet Set | 42 Galaxy Road | London | NULL | N16 6PS | England | | 1006 | Jouets Et Ours | 1 Rue Amusement | Paris | NULL | 45678 | France | +---------+----------------+-----------------+-------------+------------+----------+--------------+ 6 rows in set (0.00 sec) mysql> select * from vendors where vend_id regexp '06'; +---------+----------------+-----------------+-----------+------------+----------+--------------+ | vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country | +---------+----------------+-----------------+-----------+------------+----------+--------------+ | 1006 | Jouets Et Ours | 1 Rue Amusement | Paris | NULL | 45678 | France | +---------+----------------+-----------------+-----------+------------+----------+--------------+ 1 row in set (0.00 sec) mysql> select prod_name from products where prod_name REGEXP '^[0-9\\.]'; #^[0-9\\.]表示匹配数字或者.符号开头的字符串 +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.00 sec) 

第10章        创建计算字段

存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。这就是计算字段发挥作用的所在了。与前面各章介绍过的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
重要的是要注意到，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。客户机与服务器的格式 可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

拼接（concatenate） 将值联结到一起构成单个值。concat()
多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。当把SQL语句转换成
MySQL语句时一定要把这个区别铭记在心。
select concat(rtrim(columnname1),'(',rtrim(columnname2),')') as aliasname from tablename;
示例：

mysql> select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors; +----------------------------------------+ | concat(rtrim(vend_name),'(',rtrim(vend_country),')' | +----------------------------------------+ | Anvils R Us(USA)                       | | LT Supplies(USA)                       | | ACME(USA)                              | | Furball Inc.(USA)                      | | Jet Set(England)                       | | Jouets Et Ours(France)                 | +----------------------------------------+ 6 rows in set (0.00 sec) mysql> select concat(Rtrim(vend_name),'(',rtrim(vend_country),')') as title from vendors; +------------------------+ | title                  | +------------------------+ | Anvils R Us(USA)       | | LT Supplies(USA)       | | ACME(USA)              | | Furball Inc.(USA)      | | Jet Set(England)       | | Jouets Et Ours(France) | +------------------------+ 6 rows in set (0.00 sec) mysql> select concat(rtrim('cust_name'),'(',ltrim('cust_country'),')') as title from customers; #直接用字符串 +-------------------------+ | title | +-------------------------+ | cust_name(cust_country) | | cust_name(cust_country) | | cust_name(cust_country) | | cust_name(cust_country) | | cust_name(cust_country) | +-------------------------+ 5 rows in set (0.00 sec)

执行算术计算
select columnname1,columnname2,columnname1 * columnname2 as aliasname from tablename where 子句;
输出中显示的aliasname列为一个计算字段，此计算为columnname1*columnname2。客户机应用现在可以使用这个新计算列，就像使用其他列一样。
MySQL支持表10-1中列出的基本算术操作符。此外，圆括号可用来区分优先顺序。关于优先顺序的介绍，请参阅第7章。
                                                       表10-1 MySQL算术操作符

操 作 符	说 明
+	加
-	减
*	乘
/	除

SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT3*2;将返回6，SELECT Trim('abc');将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据需要使用SELECT进行试验。

计算字段：串拼接和算术计算。创建和使用别名，以便应用程序能引用计算字段。

第11章        使用数据处理函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。
函数没有SQL的可移植性强 ：能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS非常有效地完成的工作。如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写SQL代码的含义。

使用函数大多数SQL实现支持以下类型的函数。
 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

表11-1列出了某些常用的文本处理函数。

表11-1 常用的文本处理函数

函 数	说 明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

mysql> select cust_city,upper(cust_city) as newcolumn from customers; +-----------+-----------+ | cust_city | newcolumn | +-----------+-----------+ | Detroit   | DETROIT   | | Columbus  | COLUMBUS  | | Muncie    | MUNCIE    | | Phoenix   | PHOENIX   | | Chicago   | CHICAGO   | +-----------+-----------+ 5 rows in set (0.00 sec)

日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要的作用。
表11-2列出了某些常用的日期和时间处理函数。

表11-2 常用日期和时间处理函数

函 数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

mysql> select * from orders where order_date = '2005-09-01'; +-----------+---------------------+---------+ | order_num | order_date          | cust_id | +-----------+---------------------+---------+ |     20005 | 2005-09-01 00:00:00 |   10001 | +-----------+---------------------+---------+ 1 row in set (0.00 sec)

mysql> select * from orders where date(order_date) = '2005-09-01'; +-----------+---------------------+---------+ | order_num | order_date          | cust_id | +-----------+---------------------+---------+ |     20005 | 2005-09-01 00:00:00 |   10001 | +-----------+---------------------+---------+ 1 row in set (0.00 sec)

mysql> select * from orders where date(order_date) between '2005-09-01' and '2005-09-30'; +-----------+---------------------+---------+ | order_num | order_date          | cust_id | +-----------+---------------------+---------+ |     20005 | 2005-09-01 00:00:00 |   10001 | |     20006 | 2005-09-12 00:00:00 |   10003 | |     20007 | 2005-09-30 00:00:00 |   10004 | +-----------+---------------------+---------+ 3 rows in set (0.00 sec)

mysql> select * from orders where year(order_date) = 2005 and month(order_date) = 9; +-----------+---------------------+---------+ | order_num | order_date          | cust_id | +-----------+---------------------+---------+ |     20005 | 2005-09-01 00:00:00 |   10001 | |     20006 | 2005-09-12 00:00:00 |   10003 | |     20007 | 2005-09-30 00:00:00 |   10004 | +-----------+---------------------+---------+ 3 rows in set (0.00 sec)

Year()是一个从日期（或日期时间）中返回年份的函数。类似，Month()从日期中返回月份。因此，WHERE Year(order_date)= 2005 AND Month(order_date) = 9检索出order_date为2005年9月的所有行。
MySQL的版本差异 MySQL 4.1.1中增加了许多日期和时间函数。如果你使用的是更早的MySQL版本，应该查阅具体的文档以确定可以使用哪些函数。

在主要DBMS的函数中，数值函数是最一致最统一的函数。表11-3列出一些常用的数值处理函数。

表11-3 常用数值处理函数

函 数	说 明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

第12章        汇总数据

如果需要对表中数据（而不是实际数据本身）汇总。因此，返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了），为方便这种类型的检索，MySQL给出了5个聚集函数，见表12-1。

表12-1 SQL聚集函数

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

select avg(columnname1) as aliasname from tablename where 子句;
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。
select count(*) as aliasname from tablename;  使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
select count(columnname) as aliasname from tablename;  使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

mysql> select count(*) as num_cast from customers; +----------+ | num_cast | +----------+ |        5 | +----------+ 1 row in set (0.00 sec) mysql> select count(cust_email) as num_cust from customers; +----------+ | num_cust | +----------+ |        3 | +----------+ 1 row in set (0.00 sec) mysql> select cust_email from customers; +---------------------+ | cust_email          | +---------------------+ |      | | NULL                | |  | |     | | NULL                | +---------------------+ 5 rows in set (0.00 sec)

如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。
MAX:虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行
MIN:MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。MIN()函数忽略列值为NULL的行。
SUM()用来返回指定列值的和（总计）。
函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品

mysql> select sum(quantity) as items_ordered from orderitems where order_num = 20005; +---------------+ | items_ordered | +---------------+ |            19 | +---------------+ 1 row in set (0.35 sec)

SUM()也可以用来合计计算值。合计每项物品的item_price*quantity，得出总的订单金额：
函数SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。

mysql> select sum(item_price*quantity) as total_price from orderitems where order_num = 20005; +-------------+ | total_price | +-------------+ |      149.87 | +-------------+ 1 row in set (0.00 sec)

在多个列上进行计算 ，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。SUM()函数忽略列值为NULL的行
DISTINCT:MySQL 5 及后期版本 下面将要介绍的聚集函数的DISTINCT的使用，已经被添加到MySQL 5.0.3中。下面所述内容在MySQL 4.x中不能正常运行。
下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

mysql> select avg(distinct prod_price) as avg_price from products where vend_id = 1003; +-----------+ | avg_price | +-----------+ | 15. | +-----------+ 1 row in set (0.39 sec)

注意 如果指定列名，则DISTINCT只能用于COUNT(distinct columnname)。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
组合聚集函数：实际上SELECT语句可根据需要包含多个聚集函数

mysql> select count(*) as num_items,     -> min(prod_price) as price_min,     -> max(prod_price) as price_max,     -> avg(prod_price) as price_avg     -> from products; +-----------+-----------+-----------+-----------+ | num_items | price_min | price_max | price_avg | +-----------+-----------+-----------+-----------+ |        14 |      2.50 |     55.00 | 16. | +-----------+-----------+-----------+-----------+ 1 row in set (0.00 sec)

聚集函数用来汇总数据。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多。

第13章        分组数据

select columnname1,聚集函数(columnname2) as aliasname from tablename where 子句 group by 列或者表达式 having 子句 order by 列或者列的别名
在具体使用GROUP BY子句前，需要知道一些重要的规定。
 GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
 GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

mysql> select vend_id,count(*) as num_prods from products group by vend_id ; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ |    1001 |         3 | |    1002 |         2 | |    1003 |         7 | |    1005 |         2 | +---------+-----------+ 4 rows in set (0.00 sec) mysql> select vend_id,count(*) as num_prods from products group by vend_id with rollup; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ |    1001 |         3 | |    1002 |         2 | |    1003 |         7 | |    1005 |         2 | |    NULL |        14 | #统计前面的所有值 +---------+-----------+ 5 rows in set (0.00 sec)

WHERE过滤行，而HAVING过滤分组。

表13-1 ORDER BY与GROUP BY

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

mysql> select order_num,sum(quantity*item_price) as ordertotal     -> from orderitems     -> group by order_num     -> having sum(quantity*item_price)>=50 #不可以使用别名     -> order by ordertotal; +-----------+------------+ | order_num | ordertotal | +-----------+------------+ |     20006 |      55.00 | |     20008 |     125.00 | |     20005 |     149.87 | |     20007 |    1000.00 | +-----------+------------+ 4 rows in set (0.00 sec)

SELECT子句顺序：表13-2以在SELECT语句中使用时必须遵循的次序，列出迄今为止所学过的子句。

表13-2 SELECT子句及其顺序

子 句	说 明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

第14章        使用子查询

子查询：
版本要求 MySQL 4.1引入了对子查询的支持，所以要想使用本章描述的SQL，必须使用MySQL 4.1或更高级的版本。
SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。
查询（query） 任何SQL语句都是查询。但此术语一般指SELECT语句。SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤 mysql> select cust_name,cust_contact from customers where cust_id in ( select cust_id from orders where order_num in ( select order_num from orderitems where prod_id = 'TNT2')); +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.31 sec) #作为计算字段使用子查询 mysql> select cust_name,cust_state,( select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name; +----------------+------------+--------+ | cust_name | cust_state | orders | +----------------+------------+--------+ | Coyote Inc. | MI | 2 | | E Fudd | IL | 1 | | Mouse House | OH | 0 | | Wascals | IN | 1 | | Yosemite Place | AZ | 1 | +----------------+------------+--------+ 5 rows in set (0.32 sec) orders是一个计算字段， 它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次， 因为检索出了5个客户。子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名（在 第4章中首次提到）。下面的语句告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id： where orders.cust_id = customers.cust_id 相关子查询（correlated subquery） 涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时 候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

第15章        联结表

mysql> select * from vendors; +---------+----------------+-----------------+-------------+------------+----------+--------------+ | vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country | +---------+----------------+-----------------+-------------+------------+----------+--------------+ | 1001 | Anvils R Us | 123 Main Street | Southfield | MI | 48075 | USA | | 1002 | LT Supplies | 500 Park Street | Anytown | OH | 44333 | USA | | 1003 | ACME | 555 High Street | Los Angeles | CA | 90046 | USA | | 1004 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA | | 1005 | Jet Set | 42 Galaxy Road | London | NULL | N16 6PS | England | | 1006 | Jouets Et Ours | 1 Rue Amusement | Paris | NULL | 45678 | France | +---------+----------------+-----------------+-------------+------------+----------+--------------+ 6 rows in set (0.00 sec) mysql> select * from products; +---------+---------+----------------+------------+----------------------------------------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+----------------+------------+----------------------------------------------------------------+ | ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook | | ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case | | ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case | | DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included | | FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) | | FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) | | FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long | | JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use | | JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use | | OL1 | 1002 | Oil can | 8.99 | Oil can, red | | SAFE | 1003 | Safe | 50.00 | Safe with combination lock | | SLING | 1003 | Sling | 4.49 | Sling, one size fits all | | TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick | | TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks | +---------+---------+----------------+------------+----------------------------------------------------------------+ 14 rows in set (0.31 sec) mysql> select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | +-------------+----------------+------------+ 14 rows in set (0.00 sec) products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。 vendors表的主键又叫作products的外键，它将vendors表与products表关联在引用的列可能出现二义性时， 必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名， MySQL将返回错误。

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

mysql> select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | +-------------+----------------+------------+ 14 rows in set (0.00 sec) 联结多个表 mysql> select vend_name,prod_name,prod_price,quantity from vendors,products,orderitems where vendors.vend_id = products.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005; +-------------+----------------+------------+----------+ | vend_name | prod_name | prod_price | quantity | +-------------+----------------+------------+----------+ | Anvils R Us | .5 ton anvil | 5.99 | 10 | | Anvils R Us | 1 ton anvil | 9.99 | 3 | | ACME | TNT (5 sticks) | 10.00 | 5 | | ACME | Bird seed | 10.00 | 1 | +-------------+----------------+------------+----------+ 4 rows in set (0.00 sec) 此例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中。每个产品按其产品ID存储， 它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个 产品的记录中。这里的FROM子句列出了3个表，而WHERE子句定义了这两个联结条件，而第三个联结条件用来过 滤出订单20005中的物品。 现在可以回顾一下第14章中的例子了。该例子如下所示，其SELECT语句返回订购产品TNT2的客户列表： mysql> select cust_name,cust_contact from customers where cust_id in ( select cust_id from orders where order_num in ( select order_num from orderitems where prod_id = 'TNT2')); +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.31 sec) 子查询并不总是执行复杂SELECT操作的最有效的方法，下面是使用联结的相同查询： mysql> select cust_name,cust_contact from customers,orderitems,orders where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2'; +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ 2 rows in set (0.00 sec) 这个查询中返回数据需要使用3个表。但这里我们没有在嵌套子查询中使用它们，而是使用了两 个联结。这里有3个WHERE子句条件。前两个关联联结中的表，后一个过滤产品TNT2的数据。

第16章        创建高级联结

mysql> select * from products; +---------+---------+--------------+ | prod_id | vend_id | prod_name | +---------+---------+--------------+ | ANV01 | 1001 | .5 ton anvil | | ANV03 | 1001 | 2 ton anvil | | DTNTR | 1003 | Detonator | | FB | 1003 | Bird seed | | FC | 1003 | Carrots | +---------+---------+--------------+ 5 rows in set (0.00 sec) mysql> select p1.*,p2.* from products as p1,products as p2; +---------+---------+--------------+---------+---------+--------------+ | prod_id | vend_id | prod_name | prod_id | vend_id | prod_name | +---------+---------+--------------+---------+---------+--------------+ | ANV01 | 1001 | .5 ton anvil | ANV01 | 1001 | .5 ton anvil | | ANV03 | 1001 | 2 ton anvil | ANV01 | 1001 | .5 ton anvil | | DTNTR | 1003 | Detonator | ANV01 | 1001 | .5 ton anvil | | FB | 1003 | Bird seed | ANV01 | 1001 | .5 ton anvil | | FC | 1003 | Carrots | ANV01 | 1001 | .5 ton anvil | | ANV01 | 1001 | .5 ton anvil | ANV03 | 1001 | 2 ton anvil | | ANV03 | 1001 | 2 ton anvil | ANV03 | 1001 | 2 ton anvil | | DTNTR | 1003 | Detonator | ANV03 | 1001 | 2 ton anvil | | FB | 1003 | Bird seed | ANV03 | 1001 | 2 ton anvil | | FC | 1003 | Carrots | ANV03 | 1001 | 2 ton anvil | | ANV01 | 1001 | .5 ton anvil | DTNTR | 1003 | Detonator | | ANV03 | 1001 | 2 ton anvil | DTNTR | 1003 | Detonator | | DTNTR | 1003 | Detonator | DTNTR | 1003 | Detonator | | FB | 1003 | Bird seed | DTNTR | 1003 | Detonator | | FC | 1003 | Carrots | DTNTR | 1003 | Detonator | | ANV01 | 1001 | .5 ton anvil | FB | 1003 | Bird seed | | ANV03 | 1001 | 2 ton anvil | FB | 1003 | Bird seed | | DTNTR | 1003 | Detonator | FB | 1003 | Bird seed | | FB | 1003 | Bird seed | FB | 1003 | Bird seed | | FC | 1003 | Carrots | FB | 1003 | Bird seed | | ANV01 | 1001 | .5 ton anvil | FC | 1003 | Carrots | | ANV03 | 1001 | 2 ton anvil | FC | 1003 | Carrots | | DTNTR | 1003 | Detonator | FC | 1003 | Carrots | | FB | 1003 | Bird seed | FC | 1003 | Carrots | | FC | 1003 | Carrots | FC | 1003 | Carrots | +---------+---------+--------------+---------+---------+--------------+ 25 rows in set (0.00 sec) 此处为叉联结，乘以5 mysql> select p1.*,p2.* from products as p1,products as p2 where p1.vend_id = p2.vend_id; +---------+---------+--------------+---------+---------+--------------+ | prod_id | vend_id | prod_name | prod_id | vend_id | prod_name | +---------+---------+--------------+---------+---------+--------------+ | ANV01 | 1001 | .5 ton anvil | ANV01 | 1001 | .5 ton anvil | | ANV03 | 1001 | 2 ton anvil | ANV01 | 1001 | .5 ton anvil | | ANV01 | 1001 | .5 ton anvil | ANV03 | 1001 | 2 ton anvil | | ANV03 | 1001 | 2 ton anvil | ANV03 | 1001 | 2 ton anvil | | DTNTR | 1003 | Detonator | DTNTR | 1003 | Detonator | | FB | 1003 | Bird seed | DTNTR | 1003 | Detonator | | FC | 1003 | Carrots | DTNTR | 1003 | Detonator | | DTNTR | 1003 | Detonator | FB | 1003 | Bird seed | | FB | 1003 | Bird seed | FB | 1003 | Bird seed | | FC | 1003 | Carrots | FB | 1003 | Bird seed | | DTNTR | 1003 | Detonator | FC | 1003 | Carrots | | FB | 1003 | Bird seed | FC | 1003 | Carrots | | FC | 1003 | Carrots | FC | 1003 | Carrots | +---------+---------+--------------+---------+---------+--------------+ 13 rows in set (0.00 sec) 根据p1.vend_id = p2.vend_id匹配， vend_id=1001表中有2个，那么乘以2，vend_id=1003表中有3个，那么乘以3 mysql> select p1.*,p2.* from products as p1,products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR'; +---------+---------+-----------+---------+---------+-----------+ | prod_id | vend_id | prod_name | prod_id | vend_id | prod_name | +---------+---------+-----------+---------+---------+-----------+ | DTNTR | 1003 | Detonator | DTNTR | 1003 | Detonator | | FB | 1003 | Bird seed | DTNTR | 1003 | Detonator | | FC | 1003 | Carrots | DTNTR | 1003 | Detonator | +---------+---------+-----------+---------+---------+-----------+ 3 rows in set (0.00 sec) 

mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee |  | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones |  | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam |  | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 5 rows in set (0.00 sec) mysql> select a.cust_id,b.order_num from customers as a,orders as b where a.cust_id = b.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 5 rows in set (0.00 sec) mysql> select a.cust_id,b.order_num from customers as a inner join orders as b on a.cust_id = b.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 5 rows in set (0.00 sec) mysql> select a.cust_id,b.order_num from customers as a left outer join orders as b on a.cust_id = b.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10002 | NULL | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 6 rows in set (0.00 sec) mysql> select a.cust_id,b.order_num from customers as a right outer join orders as b on a.cust_id = b.cust_id; +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 5 rows in set (0.00 sec)

第17章        组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

UNION规则 进行并时有几条规则需要注意。  UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT 语句，将要使用3个UNION关键字）。  UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。  列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或 不同的日期类型）。 mysql> select vend_id,prod_id,prod_price from products where prod_price <= 5; +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1003 | FC | 2.50 | | 1002 | FU1 | 3.42 | | 1003 | SLING | 4.49 | | 1003 | TNT1 | 2.50 | +---------+---------+------------+ 4 rows in set (0.00 sec) mysql> select vend_id,prod_id,prod_price from products where vend_id in (1001,1002); +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1002 | FU1 | 3.42 | | 1002 | OL1 | 8.99 | +---------+---------+------------+ 5 rows in set (0.00 sec) mysql> select vend_id,prod_id,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in (1001,1002); +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1003 | FC | 2.50 | | 1002 | FU1 | 3.42 | | 1003 | SLING | 4.49 | | 1003 | TNT1 | 2.50 | | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1002 | OL1 | 8.99 | +---------+---------+------------+ 8 rows in set (0.00 sec) 这条语句由前面的两条SELECT语句组成，语句中用UNION关键字分隔。UNION指示MySQL执行两条SELECT 语句，并把输出组合成单个查询结果集。 作为参考，这里给出使用多条WHERE子句而不是使用UNION的相同查询： mysql> select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in (1001,1002); +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1003 | FC | 2.50 | | 1002 | FU1 | 3.42 | | 1002 | OL1 | 8.99 | | 1003 | SLING | 4.49 | | 1003 | TNT1 | 2.50 | +---------+---------+------------+ 8 rows in set (0.00 sec) 在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从 多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。 我们注意到，在分别执行时，第一条SELECT语句返回4行，第二条SELECT语句返回5行。但在用UNION组合两条 SELECT语句后，只返回了8行而不是9行。UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与 单条SELECT语句中使用多个WHERE子句条件一样）。因为供应商1002生产的一种物品的价格也低于5，所以两条 SELECT语句都返回该行。在使用UNION时，重复的行被自动取消。 这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不 是UNION。 请看下面的例子： mysql> select vend_id,prod_id,prod_price from products where prod_price <= 5 union all select vend_id,prod_id,prod_price from products where vend_id in (1001,1002); +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1003 | FC | 2.50 | | 1002 | FU1 | 3.42 | | 1003 | SLING | 4.49 | | 1003 | TNT1 | 2.50 | | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1002 | FU1 | 3.42 | | 1002 | OL1 | 8.99 | +---------+---------+------------+ 9 rows in set (0.00 sec) 使用UNION ALL，MySQL不取消重复的行。因此这里的例子返回9行，其中有一行出现两次。 本章开始时说过，UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它 完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。 对组合查询结果排序 SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在 最后一条SELECT语句之后。 mysql> select vend_id,prod_id,prod_price from products where prod_price <= 5 union all select vend_id,prod_id,prod_price from products where vend_id in (1001,1002) order by vend_id,prod_price; +---------+---------+------------+ | vend_id | prod_id | prod_price | +---------+---------+------------+ | 1001 | ANV01 | 5.99 | | 1001 | ANV02 | 9.99 | | 1001 | ANV03 | 14.99 | | 1002 | FU1 | 3.42 | | 1002 | FU1 | 3.42 | | 1002 | OL1 | 8.99 | | 1003 | FC | 2.50 | | 1003 | TNT1 | 2.50 | | 1003 | SLING | 4.49 | +---------+---------+------------+ 9 rows in set (0.00 sec)

第18章        全文本搜索

两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。后者支持事务

CREATE TABLE `productnotes` (   `note_id` int(11) NOT NULL AUTO_INCREMENT,   `prod_id` char(10) NOT NULL,   `note_date` datetime NOT NULL,   `note_text` text,   PRIMARY KEY (`note_id`),   FULLTEXT KEY `note_text` (`note_text`) ) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8 这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指 示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。在定义之后，MySQL自动维护该 索引。在增加、更新或删除行时，索引随之自动更新。可以在创建表时指定FULLTEXT，或者在稍后指定（在这 种情况下所有已有数据必须立即索引）。不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多， 但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然 后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进 行索引所需的总时间）。 mysql> select note_text from productnotes where match(note_text) against('rabbit'); +----------------------------------------------------------------------------------------------------------------------+ | note_text | +----------------------------------------------------------------------------------------------------------------------+ | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | | Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. | +----------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.30 sec) Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文 本。由于有两行包含词rabbit，这两个行被返回。传递给Match() 的值必须与FULLTEXT()定义中的相同。如 果指定多个列，则必须列出它们（而且次序正确）。

第19章        插入数据

如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。
 该列定义为允许NULL值（无值或空值）。
 在表定义中给出默认值。这表示如果不给出值，将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功

插入数据not null的说明： mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | +---------+----------------+---------------------+-----------+------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | +---------+----------------+---------------------+-----------+------------+ 5 rows in set (0.00 sec) mysql> show create table customers \G * 1. row * Table: customers Create Table: CREATE TABLE `customers` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10008 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into customers values (null,null,'aaa','bbbb','erw'); ERROR 1048 (23000): Column 'cust_name' cannot be null mysql> insert into customers values (null,'ccc','aaa','bbbb','erw'); Query OK, 1 row affected (0.41 sec) cust_id 虽然是not null，但是还有自增， 那么在插入null的时候cust_id会忽略null值并进行自增。 cust_name在插入null值的时候会报错，定义的not null。 mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | +---------+----------------+---------------------+-----------+------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | | 10008 | ccc | aaa | bbbb | erw | +---------+----------------+---------------------+-----------+------------+ 6 rows in set (0.00 sec) 外键对于修改表数据的影响 mysql> update customers set cust_id = 10009 where cust_state = 'MI'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`lianxi`.`orders`, CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)) mysql> show tables; +------------------+ | Tables_in_lianxi | +------------------+ | customers | | orderitems | | orders | | productnotes | | products | | vendors | +------------------+ 6 rows in set (0.00 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 5 rows in set (0.00 sec) mysql> show create table orders \G * 1. row * Table: orders Create Table: CREATE TABLE `orders` ( `order_num` int(11) NOT NULL AUTO_INCREMENT, `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, PRIMARY KEY (`order_num`), KEY `fk_orders_customers` (`cust_id`), CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> update orders set cust_id = 10010 where order_num = 20009; 外键没有10010 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lianxi`.`orders`, CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)) mysql> update orders set cust_id = 10005 where order_num = 20009; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10005 | 主键有10005这个id +-----------+---------------------+---------+ 5 rows in set (0.00 sec) 

插入多行数据

mysql> select * from customers; +---------+----------------+---------------------+-----------+ | cust_id | cust_name | cust_address | cust_city | +---------+----------------+---------------------+-----------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | | 10002 | Mouse House | 333 Fromage Lane | Columbus | | 10003 | Wascals | 1 Sunny Place | Muncie | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | | 10005 | E Fudd | 4545 53rd Street | Chicago | | 10008 | ccc | aaa | bbbb | | 10009 | aaa | dsd | frf | +---------+----------------+---------------------+-----------+ 7 rows in set (0.00 sec) mysql> insert into customers (cust_name,cust_address,cust_city) values ('rer','rew','yry'),('rwer','wrw','twt'); Query OK, 2 rows affected (0.41 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from customers; +---------+----------------+---------------------+-----------+ | cust_id | cust_name | cust_address | cust_city | +---------+----------------+---------------------+-----------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | | 10002 | Mouse House | 333 Fromage Lane | Columbus | | 10003 | Wascals | 1 Sunny Place | Muncie | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | | 10005 | E Fudd | 4545 53rd Street | Chicago | | 10008 | ccc | aaa | bbbb | | 10009 | aaa | dsd | frf | | 10011 | rer | rew | yry | | 10012 | rwer | wrw | twt | +---------+----------------+---------------------+-----------+ 9 rows in set (0.00 sec)

mysql> show create table orders \G * 1. row * Table: orders Create Table: CREATE TABLE `orders` ( `order_num` int(11) NOT NULL AUTO_INCREMENT, `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, PRIMARY KEY (`order_num`), KEY `fk_orders_customers` (`cust_id`), CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create table xuexi ( 创建表xuexi -> ca int not null, -> cb datetime not null, -> cc int not null); Query OK, 0 rows affected (0.59 sec) mysql> show columns from xuexi; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | ca | int(11) | NO | | NULL | | | cb | datetime | NO | | NULL | | | cc | int(11) | NO | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into xuexi (ca,cb,cc) select order_num,order_date,cust_id from orders; Query OK, 5 rows affected (0.04 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from xuexi; +-------+---------------------+-------+ | ca | cb | cc | +-------+---------------------+-------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-------+---------------------+-------+ 5 rows in set (0.00 sec) mysql> truncate xuexi; 清空表数据 Query OK, 0 rows affected (0.18 sec) mysql> select * from xuexi; Empty set (0.00 sec) mysql> insert into xuexi (ca,cb,cc) select * from orders where cust_id = 10001; Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from xuexi; +-------+---------------------+-------+ | ca | cb | cc | +-------+---------------------+-------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-------+---------------------+-------+ 2 rows in set (0.00 sec) 测试 mysql> create table xuexi2 ( -> aa int, -> bb datetime, -> cc int -> ); Query OK, 0 rows affected (0.68 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 5 rows in set (0.00 sec) mysql> insert into xuexi2 select * from orders; Query OK, 5 rows affected (0.41 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from xuexi2; +-------+---------------------+-------+ | aa | bb | cc | +-------+---------------------+-------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-------+---------------------+-------+ 5 rows in set (0.00 sec)

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

第20章        更新和删除数据

更新列数据
update tablename
set columnname1 = 'xxxxx',
columnname2 = 'xxxxx'
where 子句;
在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。

mysql> select * from products; +---------+---------+--------------+--------+ | prod_id | vend_id | prod_name | price | +---------+---------+--------------+--------+ | aaaaa | 1004 | ceshi | 150.99 | | ANV01 | 1001 | .5 ton anvil | 150.99 | | ANV03 | 1001 | 2 ton anvil | 150.99 | | DTNTR | 1003 | Detonator | 150.99 | | FB | 1003 | Bird seed | 20.88 | | FC | 1003 | Carrots | 150.99 | +---------+---------+--------------+--------+ 6 rows in set (0.00 sec) 可以是update tablename set columnname1 = xxxxx,columnnam2 = xxxx where 子句; 如果是update tablename set columnname1 = xxx,set columnname2 = xxxx where 子句; 就会产生错误，错误如下： mysql> update products -> set price = 1.88, -> set price = 29.00 where prod_id ='aaaaa'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' set price = 29.00 where prod_id ='aaaaa'' at line 2 mysql> select * from xuexi2; +-------+---------------------+-------+------+ | aa | bb | cc | dd | +-------+---------------------+-------+------+ | 20005 | 2005-09-01 00:00:00 | 10000 | 6 | | 20009 | 2005-10-08 00:00:00 | 10001 | 5 | +-------+---------------------+-------+------+ 2 rows in set (0.00 sec) mysql> update xuexi2 set cc = '20000',dd = '88' where aa = '20005'; Query OK, 1 row affected (0.41 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from xuexi2; +-------+---------------------+-------+------+ | aa | bb | cc | dd | +-------+---------------------+-------+------+ | 20005 | 2005-09-01 00:00:00 | 20000 | 88 | | 20009 | 2005-10-08 00:00:00 | 10001 | 5 | +-------+---------------------+-------+------+ 2 rows in set (0.00 sec)

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

第21章        创建和操纵表

create table tablename(
columnname1 int not null auto_increment,
columnname2 char(50) not null,
columnname3 varchar(20) null,
quantity int not null default 1,
primary key(columnname1)
) engine=innodb;

mysql> show create table customers \G * 1. row * Table: customers Create Table: CREATE TABLE `customers` ( `cust_id` int(11) NOT NULL AUTO_INCREMENT, `cust_name` char(50) NOT NULL, `cust_address` char(50) DEFAULT NULL, `cust_city` char(50) DEFAULT NULL, `cust_state` char(5) DEFAULT NULL, `cust_zip` char(10) DEFAULT NULL, `cust_country` char(50) DEFAULT NULL, `cust_contact` char(50) DEFAULT NULL, `cust_email` char(255) DEFAULT NULL, PRIMARY KEY (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee |  | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones |  | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam |  | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 5 rows in set (0.00 sec) mysql> select last_insert_id(cust_id) from customers; +-------------------------+ | last_insert_id(cust_id) | +-------------------------+ | 10001 | | 10002 | | 10003 | | 10004 | | 10005 | +-------------------------+ 5 rows in set (0.00 sec)

表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

引擎类型：如果省略ENGINE=语句，则使用默认引擎（很可能是MyISAM），多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因（也就是为什么本书的样列表中使用两种引擎的原因）。
以下是几个需要知道的引擎：
 InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
 MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
 MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
外键不能跨引擎 混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

alter table tablename     添加字段
add columnname 字符类型 (default xxx);    
alter table tablename     删除字段
drop column columnname;

drop table tablename;   删除表

rename table new1 to old1,new2 to old2;      重命名多表

第22章        使用视图

create view viewname as select 语句    创建视图
show create view viewname     查看创建视图的语句
drop view viewname        删除视图
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。
视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

创建和使用视图 mysql> select concat(rtrim(vend_name),'(',rtrim(vend_country),')') from vendors; +------------------------------------------------------+ | concat(rtrim(vend_name),'(',rtrim(vend_country),')') | +------------------------------------------------------+ | Anvils R Us(USA) | | LT Supplies(USA) | | ACME(USA) | | Furball Inc.(USA) | | Jet Set(England) | | Jouets Et Ours(France) | +------------------------------------------------------+ 6 rows in set (0.00 sec) mysql> create view viewname as select concat(rtrim(vend_name), '(',rtrim(vend_country),')') from vendors; Query OK, 0 rows affected (0.41 sec) mysql> select * from viewname; +------------------------------------------------------+ | concat(rtrim(vend_name),'(',rtrim(vend_country),')') | +------------------------------------------------------+ | Anvils R Us(USA) | | LT Supplies(USA) | | ACME(USA) | | Furball Inc.(USA) | | Jet Set(England) | | Jouets Et Ours(France) | +------------------------------------------------------+ 6 rows in set (0.00 sec) 查看和删除视图 mysql> show tables; +------------------+ | Tables_in_lianxi | +------------------+ | bak | | customers | | orderitems | | orderitemsbak | | orders | | productnotes | | products | | vendors | | viewname | +------------------+ 9 rows in set (0.00 sec) mysql> drop view viewname; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +------------------+ | Tables_in_lianxi | +------------------+ | bak | | customers | | orderitems | | orderitemsbak | | orders | | productnotes | | products | | vendors | +------------------+ 8 rows in set (0.00 sec)

第23章        使用存储过程

创建和使用存储过程 mysql> select * from products; +---------+---------+--------------+--------+ | prod_id | vend_id | prod_name | price | +---------+---------+--------------+--------+ | aaaaa | 1004 | ceshi | 50.12 | | ANV01 | 1001 | .5 ton anvil | 88.99 | | ANV03 | 1001 | 2 ton anvil | 150.99 | | DTNTR | 1003 | Detonator | 150.99 | | FB | 1003 | Bird seed | 20.88 | | FC | 1003 | Carrots | 1.56 | +---------+---------+--------------+--------+ 6 rows in set (0.00 sec) mysql> delimiter // mysql> create procedure procedure2() -> begin -> select avg(price) as avgprice from products; -> end// Query OK, 0 rows affected (0.00 sec) mysql> call procedure2()// 使用存储过程其实就是调用函数 +-----------+ | avgprice | +-----------+ | 77. | +-----------+ 1 row in set (0.00 sec) mysql> delimiter ; mysql>drop procedure procedure1; 删除存储过程 Query OK, 0 rows affected (0.00 sec)

mysql> delimiter // mysql> select * from customers// +---------+----------------+---------------------+-----------+ | cust_id | cust_name | cust_address | cust_city | +---------+----------------+---------------------+-----------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | | 10002 | Mouse House | 333 Fromage Lane | Columbus | | 10003 | Wascals | 1 Sunny Place | Muncie | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | | 10005 | E Fudd | 4545 53rd Street | Chicago | | 10008 | ccc | aaa | bbbb | | 10009 | aaa | dsd | frf | | 10011 | rer | rew | yry | | 10012 | rwer | wrw | twt | +---------+----------------+---------------------+-----------+ 9 rows in set (0.37 sec) mysql> delimiter ; mysql> select * from customers// -> ^C mysql> select * from customers; +---------+----------------+---------------------+-----------+ | cust_id | cust_name | cust_address | cust_city | +---------+----------------+---------------------+-----------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | | 10002 | Mouse House | 333 Fromage Lane | Columbus | | 10003 | Wascals | 1 Sunny Place | Muncie | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | | 10005 | E Fudd | 4545 53rd Street | Chicago | | 10008 | ccc | aaa | bbbb | | 10009 | aaa | dsd | frf | | 10011 | rer | rew | yry | | 10012 | rwer | wrw | twt | +---------+----------------+---------------------+-----------+ 9 rows in set (0.00 sec)

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

mysql> delimiter // #更改命令行实用程序的语句分隔符为// mysql> select * from products// +---------+---------+----------------+------------+ | prod_id | vend_id | prod_name | prod_price | +---------+---------+----------------+------------+ | ANV01 | 1001 | .5 ton anvil | 5.99 | | ANV02 | 1001 | 1 ton anvil | 9.99 | | ANV03 | 1001 | 2 ton anvil | 14.99 | | DTNTR | 1003 | Detonator | 13.00 | | FB | 1003 | Bird seed | 10.00 | | FC | 1003 | Carrots | 2.50 | | FU1 | 1002 | Fuses | 3.42 | | JP1000 | 1005 | JetPack 1000 | 35.00 | | JP2000 | 1005 | JetPack 2000 | 55.00 | | OL1 | 1002 | Oil can | 8.99 | | SAFE | 1003 | Safe | 50.00 | | SLING | 1003 | Sling | 4.49 | | TNT1 | 1003 | TNT (1 stick) | 2.50 | | TNT2 | 1003 | TNT (5 sticks) | 10.00 | +---------+---------+----------------+------------+ 14 rows in set (0.00 sec) 注意，记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是下面的例子为什么要使 用3个参数（和3条SELECT语句）的原因。 mysql> create procedure productpricing( out pl decimal(8,2), #decimal为十进制，共8位，小数为2位 out ph decimal(8,2), out pa decimal(8,2)) -> begin -> select min(prod_price) into pl from products; select max(prod_price) into ph from products; select avg(prod_price) into pa from products; -> end// Query OK, 0 rows affected (0.34 sec) 所有MySQL变量都必须以@开始。在调用时，下面的语句并不显示任何数据。它返回以后可以显示（或在其 他处理中使用）的变量。 mysql> call productpricing(@pl, -> @ph, -> @pa)// Query OK, 1 row affected, 1 warning (0.00 sec) 用select显示变量的值 mysql> select @pl,@ph,@pa// +------+-------+-------+ | @pl | @ph | @pa | +------+-------+-------+ | 2.50 | 55.00 | 16.13 | +------+-------+-------+ 1 row in set (0.00 sec) mysql> delimiter ; 重新定义命令结束符; mysql> select * from products; +---------+---------+----------------+------------+ | prod_id | vend_id | prod_name | prod_price | +---------+---------+----------------+------------+ | ANV01 | 1001 | .5 ton anvil | 5.99 | | ANV02 | 1001 | 1 ton anvil | 9.99 | | ANV03 | 1001 | 2 ton anvil | 14.99 | | DTNTR | 1003 | Detonator | 13.00 | | FB | 1003 | Bird seed | 10.00 | | FC | 1003 | Carrots | 2.50 | | FU1 | 1002 | Fuses | 3.42 | | JP1000 | 1005 | JetPack 1000 | 35.00 | | JP2000 | 1005 | JetPack 2000 | 55.00 | | OL1 | 1002 | Oil can | 8.99 | | SAFE | 1003 | Safe | 50.00 | | SLING | 1003 | Sling | 4.49 | | TNT1 | 1003 | TNT (1 stick) | 2.50 | | TNT2 | 1003 | TNT (5 sticks) | 10.00 | +---------+---------+----------------+------------+ 14 rows in set (0.00 sec) MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传 出）类型的参数。存储过程的代码位于BEGIN和END语句内 mysql> select * from orderitems; +-----------+------------+---------+----------+------------+ | order_num | order_item | prod_id | quantity | item_price | +-----------+------------+---------+----------+------------+ | 20005 | 1 | ANV01 | 10 | 5.99 | | 20005 | 2 | ANV02 | 3 | 9.99 | | 20005 | 3 | TNT2 | 5 | 10.00 | | 20005 | 4 | FB | 1 | 10.00 | | 20006 | 1 | JP2000 | 1 | 55.00 | | 20007 | 1 | TNT2 | 100 | 10.00 | | 20008 | 1 | FC | 50 | 2.50 | | 20009 | 1 | FB | 1 | 10.00 | | 20009 | 2 | OL1 | 1 | 8.99 | | 20009 | 3 | SLING | 1 | 4.49 | | 20009 | 4 | ANV03 | 1 | 14.99 | +-----------+------------+---------+----------+------------+ 11 rows in set (0.00 sec) mysql> delimiter // mysql> create procedure procedure3( -> in number int, -> out ototal decimal(8,2) -> ) -> begin -> select sum(item_price * quantity) from orderitems where order_num = number into ototal; -> end// Query OK, 0 rows affected (0.00 sec) mysql> call procedure3(20005,@ototal)// Query OK, 1 row affected (0.00 sec) mysql> select @ototal// +---------+ | @ototal | +---------+ | 149.87 | +---------+ 1 row in set (0.00 sec)

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：
show create procedure procedurename;
为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。
show procedure status;
限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：show procedure status like 'ordertotal';

第24章        使用游标

MySQL 5添加了对游标的支持，不像多数DBMS，MySQL游标只能用于存储过程（和函数）。
创建游标：
delimiter //
create procedure procedurename() 
begin
declare cursorname cursor for 语句;             DECLARE命名游标
open cursorname;    打开游标
fetch cursorname;    FETCH指定检索什么数据（所需的列）
close cursorname;    关闭游标
end//

第27章        全球化和本地化

MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法
在讨论多种语言和字符集时，将会遇到以下重要术语：
 字符集为字母和符号的集合；
 编码为某个字符集成员的内部表示；
 校对为规定字符如何比较的指令。
使用何种字符集和校对的决定在服务器、数据库和表级进行。

#字符集与字符编码的区别

基础知识

计算机中储存的信息都是用二进制数表示的；而我们在屏幕上看到的英文、汉字等字符是二进制数转换之后的结果。通俗的说，按照何种规则将字符存储在计算机中，如'a'用什么表示，称为"编码"；反之，将存储在计算机中的二进制数解析显示出来，称为"解码"，如同密码学中的加密和解密。在解码过程中，如果使用了错误的解码规则，则导致'a'解析成'b'或者乱码。

字符：在计算机和电信技术中，一个字符是一个单位的字形、类字形单位或符号的基本信息。即一个字符可以是一个中文汉字、一个英文字母、一个阿拉伯数字、一个标点符号等。

字符集（Charset）：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

字符编码（Character Encoding）：是一套法则，使用该法则能够对自然语言的字符的一个集合（如字母表或音节表），与其他东西的一个集合（如号码或电脉冲）进行配对。即在符号集合与数字系统之间建立对应关系，它是信息处理的一项*本技术。通常人们用符号集合（一般情况下就是文字）来表达信息。而以计算机为*础的信息处理系统则是利用件（硬件）不同状态的组合来存储和处理信息的。件不同状态的组合能代表数字系统的数字，因此字符编码就是将符号转换为计算机可以接受的数字系统的数，称为数字代码。

常用的字符集与字符编码

常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、GB18030字符集、Unicode字符集等。计算机要准确的处理各种字符集文字，需要进行字符编码，以便计算机能够识别和存储各种文字。

MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用以下语句：
SHOW CHARACTER SET;   这条语句显示所有可用的字符集以及每个字符集的描述和默认校对
为了查看所支持校对的完整列表，使用以下语句：
SHOW COLLATION;    此语句显示所有可用的校对，以及它们适用的字符集。

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句：
show variables like 'character%';
show variables like 'collation%';
实际上，字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。为了给表指定字符集和校对，可使用带子句的CREATE TABLE（参见第21章）：
create table mytable
(
column1 int,
column2 varchar(10)
) default character set hebrew 
collate hebrew_general_ci;
此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序。
这个例子中指定了CHARACTER SET和COLLATE两者。一般，MySQL如下确定使用什么样的字符集和校对。
 如果指定CHARACTER SET和COLLATE两者，则使用这些值。
 如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）。
 如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认。
除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们
create table mytable
(
column1 int,
column2 varchar(10),
column3 varchar(10) character set latin1 collate latin1_general_ci
) default character set hebrew 
collate hebrew_general_ci;

第28章        安全管理

mysql> use mysql; Database changed mysql> select user from user; #查看用户 +---------------+ | user          | +---------------+ | mysql.session | | mysql.sys     | | root          | +---------------+ 3 rows in set (0.00 sec) mysql> create user ceshi identified by ''; 'ceshi'@'%' 和 'ceshi'@'localhost'是 二个不同的用户，%表示远程登录用户，localhost表示本地登录用户 Query OK, 0 rows affected (0.00 sec) mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | ceshi | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 4 rows in set (0.00 sec) mysql> show grants for ceshi; 用户名后不加@localhost或者@%表示默认ceshi@% +-----------------------------------+ | Grants for ceshi@% | +-----------------------------------+ | GRANT USAGE ON *.* TO 'ceshi'@'%' | #usage表示无权限 +-----------------------------------+ 1 row in set (0.00 sec) mysql> show grants for ceshi@localhost; ERROR 1141 (42000): There is no such grant defined for user 'ceshi' on host 'localhost' 切换到mysql客户端命令行登录 D:\mysql\mysql-5.7.37-winx64\bin>mysql -uceshi -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) 注意%表示是任何主机，是不匹配localhost的，它只是针对于通过tcp/ip连接过来的主机。 切换到root@localhost mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | ceshi | | % | xuexi | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 5 rows in set (0.00 sec) mysql> create user 'ceshi'@'localhost' identified by ''; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | ceshi | | % | xuexi | | localhost | ceshi | ceshi@localhost | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 6 rows in set (0.00 sec) mysql> show grants for ceshi@localhost; +-------------------------------------------+ | Grants for ceshi@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO 'ceshi'@'localhost' | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> grant select on lianxi.* to ceshi@localhost; 赋予select权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for ceshi@localhost; +---------------------------------------------------+ | Grants for ceshi@localhost | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'ceshi'@'localhost' | | GRANT SELECT ON `lianxi`.* TO 'ceshi'@'localhost' | +---------------------------------------------------+ 2 rows in set (0.00 sec) 切换到mysql客户端命令行登录 D:\mysql\mysql-5.7.37-winx64\bin>mysql lianxi -uceshi -p Enter password: mysql> show tables; +------------------+ | Tables_in_lianxi | +------------------+ | bak | | customers | | orderitems | | orderitemsbak | | orders | | productnotes | | products | | vendors | +------------------+ 8 rows in set (0.00 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10005 | +-----------+---------------------+---------+ 5 rows in set (0.00 sec) GRANT和REVOKE可在几个层次上控制访问权限：  所有权限，使用GRANT ALL和REVOKE ALL； ALL 除GRANT，REVOKE外的所有权限  整个数据库，使用ON database.*；  特定的表，使用ON database.table； 例：grant insert,select on database.* to user@localhost; show grants for user@localhost;

创建重命名以及删除用户

为重新命名一个用户账号，使用RENAME USER语句:rename user newname to oldname;
仅MySQL 5或之后的版本支持RENAME USER。为了在MySQL 5之前的MySQL中重命名一个用户，可使用UPDATE直接更新user表。
为了删除一个用户账号（以及相关的权限），使用DROP USER语句:drop user newname;
自MySQL 5以来，DROP USER删除用户账号和所有相关的账号权限。在MySQL 5以前，DROP USER只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

设置访问权限

授予权限：grant 权限 on databasename.tablename to username;
移除权限：revoke 权限 on databasename.tablename from username;
查看权限：show grants for username;

更改密码

set password = password('xxxxxx');     #设置自己的口令
set password for username = password('xxxxx');   # root用户更改username的口令   
SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

mysql8.0修改密码

Mysql>ALTER USER 'dongm'@'%' IDENTIFIED BY 'PASSWORD';

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

修改用户密码的实例

root用户登录 mysql> set password for ceshi@localhost = password(''); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; 更新权限 Query OK, 0 rows affected (0.00 sec) mysql客户端命令行（localhost） D:\mysql\mysql-5.7.37-winx64\bin>mysql lianxi -uceshi -p mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

第29章        数据库维护

备份数据：
1、mysqldump命令对单个库进行完全备份
mysqldump -u用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
单库备份的示例
mysqldump -uroot -p lianxi > /backup/lianxi.sql
mysqldump -uroot -p mysql > /bakcup/mysql.sql

windows系统下 D:\mysql\mysql-5.7.37-winx64\bin>mysqldump -uroot -p lianxi > D:\demo1\1.sql Enter password: 

2、mysqldump命令对多个库进行完全备份
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
多库备份的示例
mysqldump -uroot -p --databases lianxi mysql > /backup/databases-lianxi-mysql.sql  

3、对所有库进行完全备份
mysqldump -u用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
所有库备份的示例
mysqldump -u root -p  --all-databases > /backup/all-data.sql

4、使用mysqldump备份表的操作
mysqldump -u 用户名-p [密码] [|选项] 数据库名 表名 > /备份路径/备份文件名
备份表的示例
mysqldump -uroot -p mysql user > /backup/mysql-user.sql

第30章        改善性能

下面的内容并不能完全决定MySQL的性能。我们只是想回顾一下前面各章的重点，提供进行性能优化探讨和分析的一个出发点。
 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
 MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES; 和SHOW STATUS;）
 MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
explain select * from tablename;
 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
 应该总是使用正确的数据类型。
 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。
 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
 LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
 最重要的规则就是，每条规则在某些条件下都会被打破。

今天的文章 mysql必知必会笔记分享到此就结束了，感谢您的阅读。