MySQL必知必会
最近复习了《MySQL必知必会》这本书，收获颇丰。书，常读常新；知识，温故而知新。加油！
本博文部分内容节选自《MySQL必知必会》，以及自己的见解，如有不当，还请各位看客指出，相互学习，不胜感激！
Database
数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）。
表（table）：某种特定类型数据的结构化清单。表名是唯一的。
模式（schema）：关于数据库和表的布局及特性的信息。
列（column）：表中的一个字段，所有表都是由一个或多个列组成的。正确地将数据分解成列很重要。
数据类型（datatype）：所容许的数据的类型，它所限制该列中存储的数据。
行（row）：表中的一个记录（record）,很多时候行=记录。
主键（primary key）：一列（或一组列），其值能都唯一区分表中的每一行，即唯一标识表中每一行的这个列（这组列）被称为主键。方便以后操作和管理。
主键的条件：①任意两行都不具有相同的主键值；②每一行都必须具有一个主键值（不能为NULL）。
MySQL 简介及使用
MySQL：是一种DBMS，是一种数据库软件。
连接MySQL需要的信息：①主机名（计算机名），如果连接本地的MySQL服务器，为Localhost；②端口，默认为3306；③合法的用户名；④用户口令。
/***********注意：以下各例子使用一个名为crashcourse 的数据源***********/
必须先通过USER crashcourse 选择打开数据库，才能读取其中的数据。
查看可用的数据库：
>>>SHOW DATABASES;
查看当前选择的数据库内可用的表：
>>>SHOW TABLES;
查看当前选择的数据库内customers表内的列信息：
>>>SHOW COLUMNS FROM customers;
customers为表名。
上述语句等价于：
>>>DESCRIBE customers;
大家注意到了：每一句SQL语句后，都加上了分号（;）为结尾，是的，这是结束符号。
此外，SQL语句不区分大小写，SELECT和select是一样的，但是开发人员习惯对关键字使用大写，对所有列和表名使用小写，便于阅读和调试。
检索数据
检索单个列：
>>>SELECT prod_name FROM products;
//从products表中检索出一个名为prod_name的列。
这里返回的数据是无序的，因为没有指定select的排序查询结果。
检索多个列：
>>>SELECT prod_name, prod_id, prod_price FROM products;
//从products表中检索出一个名为prod_name, prod_id, prod_price的列。不同的列之间用逗号（,）隔开，最后一个不需要逗号，否则会出错。
检索所有列：
>>>SELECT * FROM products;
//星号（*）为通配符，返回所有。最好别使用会降低性能，毕竟你把所有信息都取出来了。
检索不同的行：
>>>SELECT vend_id FROM products
//从产品中选出供应商的id。可能你的产品有15个，这里就会返回15行，但是供应商可能只有两三个，那么，如果我不希望出现重复的供应商的id，那么就需要去重。需要使用DISTINGCT。
>>>SELECT DISTINCT vend_id FROM products
//这里就会返回两三个没有重复的供应商id。
NOTE：DISTINCT 用在所有的检测列之前，并且 它是作用于 所有列，不能部分使用。
原始表：
corporation number
Alibaba 1001
Tencent 1002
Alibaba 1003
Netease 1004
>>>SELECT DISTINCT corporation FROM table
corporation
Alibaba
Tencent
Netease
>>>SELECT DISTINCT corporation, name FROM table
返回数据是原始数据，这里也就是最容易引起疑惑的，而类似SELECT id ,DISTINCT corporation这样的语句是错误的。那么 在使用 distinct 时候，我们可以把它后面的 所有参数当成一个 也就是 DISTINCT (id,corporation) ，即只有 （id,corporation ）这个组合的数据都相同时候，才会被“去重”，否则 还是会保留。最后我们回到 题目，如果每个公司我只想得到一个代表就行 ，可以使用 group by 语句：
SELECT corporation ,MAX(number),
FROM table
GROUP BY corporation
输出：这样对同一家公司，会保留number 最大的一行，返回
corporation number
Tencent 1002
Alibaba 1003
Netease 1004
限制结果：
输入：
SELECT prod_name
FROM products
LIMIT 5;
//指检索单列，并返回结果不多于5行。
输入：
SELECT prod_name
FROM products
LIMIT 5, 5;
//LIMIT 5, 5;指返回的结果是从行5开始的5行。即第一个参数为开始位置，第二个参数为要检索的行数。行数从0开始计数。
如LIMIT 1, 1;
//获取的是第二行（行号为1，因为从0 开始）。
/************************** 数据排序 order by *****************************/
子句（clause）：SQL语句由子句构成，有些子句必须，有些可选。
一个子句由一个关键字+数据组成。如SELECT语句中的FROM子句。
ORDER BY子句可取一个或多列，并对此输出进行排序。
如：
SELECT prod_name
FROM products
ORDER BY prod_name;
//指示输出按照字母顺序排序。
NOTE：ORDER BY子句中的列既可以是显示列，也可以是非检索列数据。
按多列排序：
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
//即当order by后跟多个列条件时，会排序会按所规定的顺序进行，即如上命令中，在只有第一个列条件prod_price相同时，才会按照第二个列条件进行排序。
如果第一个列条件prod_price都不同，则忽略第二个条件；否则如果第一个列条件prod_price都相同时，即按照第二列条件进行排序。
指定排序方向：
升序：（A -> Z）默认;
降序：（Z -> A）DESC;
另外，DESC只直接作用于其前面的列。如：
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
//该命令表示按照价格从高到低排序，价格一样的，按照名字的字典顺序排序。
纳闷问题来了，如果想要在多列都是按照降序排序怎么办呢？
当然是在每列后都加上DESC关键字了。。。
上述命令ORDER BY prod_price DESC可以找到价格从高到低排序，那么如果我只想要最高价格的产品怎么办？在这里可以用到LIMIT 关键字。
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC
LIMIT 1;
//此处的LIMIT 1约束我们只能返回一行。
NOTE：使用ORDER BY子句时，必须放在FROM后，而且必须是SELECT语句的最后一条子句；使用LIMIT子句必须放在LIMIT后。
/************************** 数据过滤 where *****************************/
搜索条件（search crieria）:DB中一般包含海量的数据，但是很少需要检索所有行，通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据就需要指定搜索条件，也称为过滤条件（filter condition）。
同样的，在SELECT语句中，where子句在表名（FROM子句）之后给出。
SELECT prod_name,prod_price
FROM products
WHERE prod_price=2.50;
NOTE：同时使用ORDER BY和WHERE时，ORDER BY应该在WHERE之后。
WHERE子句操作符：
|
操作符 |
说明 |
|
= |
等于 |
|
<> |
不等于 |
|
!= |
不等于 |
|
< |
小于 |
|
<= |
小于等于 |
|
> |
大于 |
|
>= |
大于等于 |
|
BETWEEN |
在指定两个值之间 |
检查单个值：
SELECT prod_name,prod_price
FROM products
WHERE prod_name=’fuses’;
//输出name 是fuses的这一行数据；
SELECT prod_name,prod_price
FROM products
WHERE prod_price<10;
//检索价格小于10的所有产品；
SELECT prod_name,prod_price
FROM products
WHERE vend_id <> 1003;
//输出运营商不是1003的所有产品；
NOTE：注意到上述有些WHERE子句里的值需要用引号（‘’），即：如果值与字符串类型的相比较则需要用单引号，如果是数值列进行比较则不需要。
范围值检查：
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
//返回价格在5 <= price <= 10 之间的所有产品；
空值检查：
NULL（无值， no value）：与字段包含0，或空字符串，或空格不同。
SELECT prod_name,prod_price
FROM products
WHERE prod_price IS NULL;
//返回没有价格的所有产品，这里的没有价格并不是价格为0 ，而是价格这一列缺失。
组合WHERE子句
MySQL允许多个WHERE子句，这些子句可以以两种方式连接起来：AND 和 OR。
操作符（operator）：用来联结WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。
多列作为过滤条件：
SELECT prod_name,prod_price
FROM products
WHERE prod_price <= 10 AND vend_id=1003;
//指定商品价格范围和生产商输出；AND用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。每添加一条就要使用一个AND。
SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003;
//指定商品生产商输出；OR用在WHERE子句中的关键字，用来指示检索满足任一给定条件的行。每添加一条就要使用一个AND。
NOTE：WHERE可包含任意数目的AND和OR操作符，以进行复杂和高级的过滤。但是有优先级的区分，AND优先级高于OR。可以通过加小括号来明确分组。
/************************** 数据过滤 IN *****************************/
但是小括号在WHERE子句中还有其他用法：
IN操作符用来指定条件范围，范围中每个条件都可以进行匹配。
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
//检索出供应商为1002和1003的所有产品，且产品名字按照升序输出。
其实，WHERE vend_id=1002 OR vend_id=1003 语句等价于WHERE vend_id IN (1002,1003)语句。
IN后可跟多个值，如：
SELECT *
FROM products
WHERE vend_id IN (1002,1003,1004,1005);
IN后还可跟select子句，如：
SELECT *
FROM products
WHERE vend_id
IN (
SELECT vend_id
FROM products
WHERE prod_price <= 10
);
IN跟子句会遇到查询效率低下的问题，则需要用到Join语句，后面再细说。
IN语句的优点：
*在使用长的合法选项清单时，IN操作符的语法更为清楚直观；
*计算次序更易管理（因为操作符少）；
*IN操作符一般比OR操作符执行的更快；
*最大的优点是可以包含其他SELECT子句。
NOT操作符：
否定的意思。
SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
//检索出供应商不为1002和1003的所有产品，且产品名字按照升序输出。
/************************** 通配符过滤 LIKE *****************************/
通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。
为在搜索子句中使用通配符，必须使用LIKE操作符。
LIKE指示MySQL后跟的搜索模式是利用通配符进行匹配而不是直接相等匹配进行比较。
谓词（predicate）：从技术上讲，LIKE是谓词，而不是操作符。
百分号（%）通配符：
%：表示任何字符出现任意次数。
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘jet%’;
//找出所有以词jet开头的产品名字和价格。%告诉MySQL接受jet之后的任意字符，不管它有多少字符（包括0个字符）。%可以出现在搜索字符的任意位置，并可以使用多个，如
*放两端：’%anvil%’。该搜索匹配表示匹配任何位置包含文本anvil的值，而且不论它之前或之后出现什么字符。
*放中间：’s%e’。表示找出以s起头，以e结尾的所有产品。
NOTE：似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE ‘%’，也不能匹配用值NULL作为产品的行。
下划线（_）通配符：
用途与% 一样，只不过区别是只匹配单个字符而不是多个字符。
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘_ ton anvil’;
//会输出：1 ton anvil，2 ton anvil等，而不会匹配 .6 ton anvil，因为要求两个通配符。所以，下划线（_）通配符总是匹配一个字符，不能多也不能少。
通配符使用技巧：
*不要过度使用通配符；
*尽可能不要让通配符处于搜索模式的开始；
*注意放置的位置；
/************************** 正则表达式*****************************/
正则表达式：用来匹配文本的特殊的串（字符集合）。
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;
//该语句和上述的LIKE子句非常相似，区别就是红色部分变为了REGEXP，这表示REGEXP后所跟的东西作为正则表达式处理，在这里表示与文字正文1000匹配的一个正则表达式，即检索列prod_name包含文本1000的所有行。
那么此处抛出一个问题：
如果上述结果返回
Prod_name
JetPack 1000
此处将上述检索语句改为：
SELECT prod_name
FROM products
WHERE prod_name LIKE ‘1000’
ORDER BY prod_name;
那么，请问返回值结果和利用REGEXP 语句一样吗？
答案是否定的，什么也不会返回。
WHY？
【解析】：这里就是LIKE和REGEXP的区别：LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不返回，除非使用通配符。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，并返回行数据。
举个例子：
https://www.cnblogs.com/Guhongying/p/10542792.html
摘录如下：
在以下的学生信息表中，用LIKE和REGEXP操作Sno来找出张无忌的信息：
SELECT *
FROM students
WHERE sno LIKE ‘108’
不会返回任何数据
SELECT *
FROM students
WHERE sno LIKE ‘20162180108’
输出：
*******略******
SELECT *
FROM students
WHERE sno LIKE ‘%108’
输出：
*******略******
SELECT *
FROM students
WHERE sno REGEXP ‘108’
输出：
*******略******
MySQL中的正则表达式匹配不区分大小，为区分大小写，可使用BINARY关键字。
进行OR匹配：
表示或，在政策表达式中通过使用管道符（|）来实现or匹配，如：
SELECT prod_name
FROM products
WHERE prod_name GEGEXP ‘1000 | 2000’
ORDER BY prod_name;
返回：
Prod_name
JetPack 1000
JetPack 2000
///这里1000 | 2000表示匹配1000或2000，所以返回如上。
进行匹配几个字符之一：
匹配某一个单一字符，可以使用中括号（[ ]）如：
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
返回：
Prod_name
1 ton anvil
2 ton anvil
//这里[123] 定义了一组字符，它的意思是匹配1或2或3，所以返回如上。
即：有一种说法是，[ ] 是另一种形式的OR语句。
匹配范围：
[0123456789] = [0-9] ：表示取0到9任一个数字。
[a-z] ：表示取字母a到字母z的任一个。
匹配特殊字符：
如：(“.” ，“、”，“[”，“]”，“|”，“-”等)
需要在前面加上转义符号：（“\\”）
如：\\- 表示查找 - ，\\. 表示查找 . 。
再有：
\\f 换页
\\n 换行
\\r 回车
\\t 制表
为了匹配反斜杠本身，需要使用 \\\ 。
疑问：一般正则表达式都是用单个左倾斜杠转义特殊字符，以便能够使用这些字符本身。为何MySQL中要求两个？
回答：MySQL之所以要求两个左倾斜杠，只因为：MySQL自己解释一个，正则表达式解释一个。
匹配字符类：
|
类 |
说明 |
|
[:alnum:] |
同[a-zA-Z0-9] |
|
[:alpha:] |
同[a-zA-Z] |
|
[:blank:] |
同[\\t] |
|
[:cntrl:] |
ASCII 控制字符（ASCII 0到31和127） |
|
[:digit:] |
同[0-9] |
|
[:print:] |
任意可打印字符 |
|
[:graph:] |
同[:print:]，但不包括空格 |
|
[:lower:] |
同[a-z] |
|
[:upper:] |
同[A-Z] |
|
[:space:] |
包括空格在内的任意空白字符，同[\\f\\n\\r\\t\\v] |
|
[:xdiigit:] |
任意十六进制数字，同[a-fA-F0-9] |
NOTE：这里需要说明一下下：
|
[:digit:] |
同[0-9] |
这里的“同”的意思代表结果一样，即：[:digit:] 结果是表示0|1|2|3|4|5|6|7|8|9，0-9中的任一个，[0-9]也是表示0|1|2|3|4|5|6|7|8|9，0-9中的任一个。故，如果想要表达出[0-9][0-9][0-9][0-9]，就是[0-9]{4}，也是[[:digit:] ]{4}。故：使用的时候，外面都要再加一层。示例见下。
匹配多个实例：
重复元字符
|
元字符 |
说明 |
|
* |
0个或多个匹配 |
|
+ |
匹配前面的字符1个或多个，同{1, } |
|
? |
匹配前面的字符0个或1个，同{0, 1} |
|
{n} |
指定数目的匹配 |
|
{n, } |
不少于指定数目的匹配 |
|
{n, m} |
匹配数目的范围，m<=255 |
如：
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)’
ORDER BY prod_name;
输出：
prod_name
TNT (1 stick)
TNT (1 sticks)
//正则表达式‘\\([0-9] sticks?\\)’，\\(匹配)，[0-9]匹配任何数字（示例中为1和5），sticks?匹配stick和sticks（s后的？使s 可选，因为？匹配它前面的任何字符的0次或1次出现），\\)匹配）。如果没有？的语法，想要精确匹配到stick和sticks会非常困难。
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[[:digit:]]{4}’
ORDER BY prod_name;
输出：
prod_name
jectPack 1000
jectPack 2000
//匹配连在一起的4位数字。
以上所有的例子都是匹配一个串中任意位置的文本，为了匹配特定位置的文本，需要使用定位符：
定位符：
|
元字符 |
说明 |
|
^ |
文本开头 |
|
$ |
文本结尾 |
|
[[:<:]] |
词的开始 |
|
[[:>:]] |
词的结尾 |
示例：
想找出以一个数（包括小数点开始的数）开始的所有产品？简单搜索[0-9\\.]是不行的，因为它将在文本内任意位置查找匹配。解决办法是使用定位符^。
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘^[0-9\\.]’
ORDER BY prod_name;
输出：
prod_name
.5 ton anvil
1 ton anvil
2 ton anvil
//注：上述的区别是：
[0-9\\.]从任意位置匹配，意思是：可能匹配到val 5 ton anvil，val .4 ton anvil这样的行；
^[0-9\\.]从开始位置匹配，意思是，只能匹配到开头第一个字符符合要求的行。
^的双重作用：
*一是在集合中（用[ 和] 定义），用它来否定该集合；[^0-9]
*在集合外：用来指串的开始处。^[0-9]
/************************** 创建计算字段 *****************************/
计算字段：将数据库里的原始数据进行一系列转化，形成用户所需要的格式。
计算字段并不实际存在于数据库表中，而是运行时在SELECT语句内创建的。
字段（field）：基本上与列（column）意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
需要注意的是：只有数据库知道SELECT语句中哪些列是实际的表列，哪些是计算字段。从客户机的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。
举例：将两列vend_name和vend_country拼接成单个值。
可使用Concat()函数：
SELECT Concat(vend_name, ‘ (’, vend_country, ‘)’)
FROM vendors
ORDER BY vend_name;
输出：
Concat(vend_name, ‘(’, vend_country, ‘)’)
ACME (USA)
Anvill R Us (USA)
Jet Set (England)
LT Supplies (France)
//上述的Concat()函数里拼接的各串是：存储在vend_name列中的名字，包含一个空格和一个左圆括号的串，存储在vend_country列中的国家，包含一个右圆括号的串。
LTrim()函数用来去掉左边的空格。
RTrim()函数用来去掉右边的空格。
Trim()函数用来去掉两边的空格。
别名：
从上述的拼接语句中看出，次计算列是没有名字的，或者可以称名字为：concat(vend_name, ‘(’, vend_country, ‘)’)，实际上它只是一个值。如果只在MySQL查询工具中查询结果倒没什么，但是一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。
所以别名（alias）就出来了。
别名（alias）：是一个字段或值的替换名。用AS关键字赋予。
SELECT Concat(RTrim(vend_name), ’ (’, RTrim(vend_country), ’)’) AS vend_title
FROM vendors
ORDER BY vend_name;
输出：
vend_title
ACME (USA)
Anvill R Us (USA)
Jet Set (England)
LT Supplies (France)
//此处结果和上述语句返回一致，只不过返回的列名换了。此时任何用户机都能按列名引用该列。相当于我们平时保存文档时的重命名操作。
SELECT prod_name, quantity, item_price
FROM orderitems
WHERE order_num = 20005
输出：
prod_id | quantity | item_price
ANV01 10 5.99
ANV02 3 9.99
TNT2 5 10.00
FB 1 10.00
//检索订单号20005中的所有物品：
如果想要汇总物品的价格（单价(item_price)*数量(quantity)）：
SELECT prod_name, quantity, item_price, quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005
输出：
prod_id | quantity | item_price | expanded_price
ANV01 10 5.99 59.90
ANV02 3 9.99 29.97
TNT2 5 10.00 50.00
FB 1 10.00 10.00
//输出中 expanded_price列是一个计算字段，客户机可以使用。
/************************** 函 数 *****************************/
11.1函数
可移植性（portable）：能运行在多个系统上的代码。
大多数的SQL实现支持以下四类函数：
*文本处理函数：RTrim()，Upper()，Left()，Length()，Locate()//找子串，Right()、、返回串右边的字符。
*日期和时间处理函数：Date(), Hour(), Minute(), Month(), Now(), Second(), Time(), Year()。
*数值处理函数：Abs(), Mod()//求余数, Rand(), Sqrt();
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
输出：
vend_name | vend_name_upcase
ACME | ACME
Amvils R Us | AMVILS R US
Furball Inc. | FURBALL INC.
Jet Set | JET SET
//如所见，upper()将所有的vend_name中的字符都转换为大写字母。
基本的日期比较查询：
SELECT cust_id, order_num
FROM orders
WHERE order_date = ‘2005-09-01’;
输出：
cust_id | order_num
10001 | 20005
//检索一个订单记录，该订单记录的order_date为2005-09-01；
但是使用WHERE order_date = ‘2005-09-01’; 语句一定靠谱吗？order_date的数据类型为datatime，这种类型存储日期和时间值，样例表中的值全都具有时间值00:00:00，但实际中很可能并不总是这样，如果存储的时间值order_date为2005-09-01 ：11:30：05，则where语句是找不到的，即使给出具有该日期的一行，也不会把它检索出来，因为where匹配失败。
一种有效的解决办法是：只是MySQL 仅将给出的日期与列中的 日期部分 进行比较，而不是将给出的日期与整个列值进行比较。为此，我们可以使用Date()函数，Date（order_date）只是MySQL仅仅提取列的日期部分，故：更可靠的SELECT语句为：
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = ‘2005-09-01’;
//NOTE：Date()和Time()是在MySQL4.1.1中第一次引入。
匹配月份中的天数：
SELECT cust_id, order_num
FROM oeders
WHERE Date(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;
//但是这种需要你记住闰年及每个月都多少天。
还有一种方案是不需要记住这些信息：即，单独匹配年和月信息：
SELECT cust_id, order_num
FROM oeders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
//Year()是从一个日期中返回一个年份的函数，用AND 与来精确获取年和月匹配。
/************************** 汇 总 数 据 *****************************/
聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数。
如：确定表中的行数，获得表中好行组的和，找出表列中的最大值，最小值和平均值等。
这些例子都需要对表中数据（而不是实际数据本身）汇总，所以返回实际表数据是对时间和处理资源的一种浪费，【重要】实际想要的是汇总信息。
|
函数 |
说明 |
|
AVG() |
返回某列的平均值【注意是某一列，如想要获取多列可多次使用该函数】 |
|
COUNT() |
返回某列的行数 |
|
MAX() |
返回某列的最大值 |
|
MIN() |
返回某列的最小值 |
|
SUM() |
返回某列值之和 |
NOTE：
*COUNT( )函数：COUNT( * )不忽略NULL，COUNT( column )忽略NULL。
*AVG( )，MAX( )，MIN( )和SUM( ) 函数忽略列值为NULL的行。
聚集不同值：
DISTINCT，在MySQL 5 及以后版本中引入。
例子：
求均值：
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
输出：
avg_price
13.212857
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_ id = 1003;
输出：
avg_price
15.998000
//有人会问，同样的是AVG( )为什么输出的结果不一样？因为加了DISTINCT嘛，那到底是什么作用？从上述两个语句可看出，加了DISTINCT后，avg_price比较高，是因为有多个物品具有相同的较低价格，排除了重复的低价格，故就提升了均价。如：价格有：1,1,1,2,3,4。未使用DISTINCT的话，均价就是（1+1+1+2+3+4）/6=2，使用了之后就是（1+2+3+4）/4=2.5。
以上计算都是在表的所有数据或者匹配特定的WHERE子句的数据上进行操作。
以下开始分组操作。
/************************** 分 组 数 据 *****************************/
我们来看下面的语句：
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_ id = 1003;
//输出的是供应商1003提供的产品数目。
但是如果想要返回每个供应商提供的产品数目，或返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办？
这就需要分组了。
分组是在SELECT语句的GROUP BY子句中建立的。
我们看下这个例子：
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
//上述语句指定了两个列值，一个是vend_id，一个是num_prods，为计算字段（用COUNT(*)函数建立）。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。
上述输出表示：供应商为1001的产品有3个，供应商为1002的产品有2个，供应商为1003的产品有7个，供应商为1005的产品有2个。
NOTE：因为使用了GROUP BY，就不必要指定要计算和估值的每个组了，系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组进行聚集，而不是对整个结果。
除了使用GROUP BY 分组数据之外，还可以使用HAVING来过滤分组。
HAVING与WHERE之间的联系与区别：
*WHERE过滤行，HAVING过滤分组（既然是过滤分组，那肯定得先完成分组了）；
*以上所有类型的WHERE子句都可以用HAVING来替代。
还有一种理解是：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。s
例子：
SELECT cust_id, count(*) AS orders
FROM orders
GROUP BY cust_id;
SELECT cust_id, count(*) AS orders
FROM orders
GROUP BY cust_id //先分组
HAVING COUNT(*) >=2; //后过滤
//该语句和上个语句类似，只不过最后加了一句HAVING子句。她过滤COUNT(*) >= 2（两个以上的订单）的那些分组。
分组和排序：GROUP BY 和 ORDER BY
|
GROUP BY |
OEDER BY |
|
分组行，但输出可能不是分组的顺序。 |
排序产生的输出。 |
|
只可能使用选择列或者表达式列，而且必须使用每个选择列表达式。 |
任意列都可以使用（甚至非选择的列）。 |
|
如果与聚集函数一起使用列（或表达式），则必须使用。 |
不一定需要。 |
补充：
聚集函数：AVG(), COUNT(), MAX(), MIN(), SUM().
NOTE：一般在使用GROUP BY子句时，也应该给出ORDER BY，因为这是保证数据正确排序的唯一办法，切记不可仅依赖GROUP BY来排序数据。
例子：
注：order_num表示订单号，quantity订单数量，item_price单个商品价格：
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;
输出：
order_num | ordertotal
20005 149.87
20006 55.00
20007 1000.00
20008 125.00
//检索订单总价大于50的订单的订单号和订单总价格。
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
//检索订单总价大于50的订单的订单号和订单总价格，并按照订单价格升序输出。
子句顺序：
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
以上查询语句都是从单个数据表中检索数据的单条语句。
下面开始嵌套查询。
/************************** 子 查 询 *****************************/
查询（query）：任何SQL语句都是查询，但此术语一般指SELECT语句。
SQL还允许子查询（subquery），即嵌套在其他查询中的查询。
原因有两个：
* 利用子查询进行过滤；
* 作为计算字段使用子查询；
利用子查询进行过滤：
假如有三个表：order，orderitems和customers。
Order表中存储订单号，客户ID（该表中唯一的客户信息），订单日期等信息；
orderitems表中存储个订单的物品信息；
customers表中存储各客户信息。
如果我们的要求是，检索出订购TNT2物品的所有客户。该怎么做？
也可以分为三步：
1）先从orderitems表中检索出含有TNT2物品的所有订单号；
2）再从Order表中，根据上述订单，检索出对应的客户ID；
3）最后从customers表中，根据上述客户ID，检索出客户的所有信息。
上述每个步骤都可以单独作为一个查询来执行，可以把前一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
接下来我们一一实现各个步骤：
1）
SELECT order_num
FROM orderitems
WHERE prod_id = ‘TNT2’;
输出：
order_num
20005
20007
2）
SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);
输出：
cust_id
10001
10004
3）
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001, 10004);
输出：
cust_name | cust_contact
Coyote Inc. Y Lee
Yosemite Place Y Sam
//即输出即是所要结果。
上述三步其实我们可以合为一步，在此，偶尔们一步一步来，先前两部合二为一：
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = ‘TNT2’
);
将第三步和上述再合二为一：
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’
)
);
虽然对于嵌套子语句没有限制，但是实际使用时由于性能的限制，不会嵌套太多。
另外，子查询一般与IN , = , < , >等操作符配合使用。
作为计算字段使用子查询：
假如需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中。
步骤：
1）从customers表中检索出所有的客户ID；
2）从Orders表中检索出与上述客户ID相对应的订单数，并根据客户ID分组，直接count即可。
如：
//从表customers中检索出所有的客户ID：
SELECT cust_id, cust_name, cust_state
FROM customers
ORDER BY cust_id;
//从表orders中检索客户ID为10001的订单数：
SELECT COUNT(*) AS orderscount
FROM orders
WHERE cust_id = 10001;
上述二合一：
SELECT cust_name, cust_state, (SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id
) AS orderscount
FROM customers
ORDER BY cust_id;
输出：
cust_name | cust_contact | orderscount
Coyote Inc. MI 2
Yosemite Place AZ 1
//该语句会返回三列：cust_name , cust_contact , orderscount。orderscount是一个计算字段，是由圆括号中的子查询建立的，该子查询对检索出的每个客户执行一次。在此例子中，该语句一共执行了2次，因为检索出了2个客户。
/************************** 联 结（join） 表 *****************************/
关系表：为了解决相同数据在同一表中出现多次的现象，关系表要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（即关系设计中的关系[relational]）互相关联。
主键（primary key）：表中区分行的唯一的标识。
外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性（scale）： 能够适应不断增加的工作而不失败，设计良好的db 或app称之为可伸缩性好（scale well）。
通过关系表，就是把单个复杂的表分为多个简单的表，但是，表并不是越多越好，因为数据存储在多个表中就不能用一条简单的SELECT语句来检索出数据了。故为了解决这个问题，可以使用联结。
联结：是一种机制，用来在一条SELECT语句中关联表，故称为联结。但是联结不是一个物理实体，在实际的数据库中是不存在的。联结由MySQL根据需要建立，存在于查询的执行当中。
创建联结：
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
输出：
vend_name | prod_name | prod_price
ACME Bird seed 10.00
ACME CArrots 2.50
ACME Detonator 13.00
ACME Safe 50.00
ACME Sling 4.49
ACME TNT(1 stick) 2.50
ACME TNT(5 stick) 10.00
Anvils R Us .5 ton anvil 5.99
Anvils R Us 1 ton anvil 9.99
Anvils R Us 2 ton anvil 14.99
Jet Set JetPack 1000 35.00
Jet Set JetPack 2000 55.00
LT Supplies Fuses 3.42
LT Supplies Oil can 8.99
//此处WHERE vendors.vend_id = products.vend_id 是关键，因为指定的两个列不在同一个表中：prod_name和prod_price在一个表内，vend_name在另一个表内。
还有一点：这里的FROM后跟的也不太一样，这里跟了两个表，分别是vendors和products。这两个表就是要联结的两个表的名字，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
这里出现一个名词：
完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。
NOTE：在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西，你必须自己做这件事情。在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行进行匹配。WHERE子句作为过滤条件，它只包含哪些匹配给定条件（这里是联结条件）的行。如果没有WHERE子句，第一个表中的每一行将与第二个表中的每个行匹配，而不管他们逻辑上是否可以匹配在一起。
笛卡尔积（cartesian product）:由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是一个表中的行数乘以第二个表中的行数。
不要忘了WHERE子句，应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。
以上所用都是等值联结（equijoin）也称为内部联结。可以使用稍微不同的语法来明确指定联结的类型。如：
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
//以上返回和上个例子返回的数据一模一样。等价于以下子句：(为了便于对比，又贴过来)
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
需要注意的是，联结条件用特定的ON子句而不是WHERE子句给出，传递给ON的实际条件与传递给WHERE的相同。
联结多个表：
举例子：
SELECT vend_name, prod_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE vendors.vend_id = products.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
//NOTE：MySQL在运行时关联指定的每个表以处理联结，但是这种处理是非常耗费资源的，而且联结的表越多性能下降的越厉害，所以不要联结不必要的表。
这里的FROM子句里除了3个表（vendors，products，orderitems），而WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单20005中的物品。
/************************** 创 建 高 级 联 结 *****************************/
使用表别名：
SELECT Concat(RTrim(vend_name), ‘ (’, RTrim(vend_country), ‘) ’ ) AS vend_title
FROM vendors
ORDER BY vend_name;
//别名除了用于列名和计算字段外，SQL还允许给表名起别名，主要因为是：
* 缩短SQL检索语句；
* 允许在单条SELECT 语句中多次使用相同的表。
如：
SELECT cust_name, cust_contact
FROM customer AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust
AND oi.order_num = o.order_num
AND prod_id = ‘TNT2’;
除了以上使用的内部联结（等值联结[equijoin]）外，还有其他三种联结，分别是自联结，自然联结和外部联结。
自联结
案例：
假如发现某物品（其ID为DTNTR）存在问题，因此想要知道生产该物品的供应商生产的其他物品是否也存在这些问题。
查询思路是：先找到生产ID为DTNTR的物品，然后找到生产该物品的厂商，最后找出该供应商生产的其他物品。
SELECT prod_id, prod_name
FROM products
WHERE vend_id = ( SELECT vend_id
FROM products
WHERE prod_id = ‘DTNTR’
);
输出：
prod_id | prod_name
DTNTR Detonator
FB Bird seed
FC Carrots
SAFE Safe
SLING Sling
TNT1 TNT（1 stick）
TNT2 TNT（5 sticks）
//这是第一种思路，使用了我们上面学过的子查询。、
下面是使用联结的相同查询：
SELECT prod_id, prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR’;
输出一样。
//此查询中需要的两个表实际上是相同的表，因此Products表在FROM 子句中出现了两次。为了防止出现二义性，使用了表别名p1 和 p2。
用自联结而不是用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。
自然联结
无论何时使用联结，应该至少有一个列出现在不知一个表中（被联结的列）。
标准的联结（内部联结）返回所有的数据，甚至相同的列出现多次，而自然联结排除多次出现，使每个列只返回一次。
自然联结不是系统来完成的，而是由你自己来完成。为什么呢？因为自然联结中，你只能选择那些唯一的列。
实现方式：一般通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。
案例：
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = ‘FB’;
//在该例子中，通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来。
到这里可能有人会疑惑内部联结和自然联结到底是什么关系了吧？其实，目前为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
外部联结
许多联结将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行。如：列出所有产品以及订购数量，包括没有人订购的产品。
案例：
SELECT customers.cust_id, order.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = order.cust_id;
//以上是内部联结的写法；下面给一个外部联结的实现：
SELECT customers.cust_id, order.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
输出：
cust_id | order_num
10001 20005
10001 20009
10002 NULL
10003 20006
10004 20007
10005 20008
//与内部联结相比较，外部链联结使用关键字OUTER JOIN来指定（注意不是在WHERE子句中指定）。与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。
在使用OUTER JOIN语法时，必须使用RIGHT 或者LEFT关键字来指定包括 其所有行的表（RIGHT指出的是OUTER JOIN右边的表，LEFT指出的是OUTER JOIN左边的表）。
上面的例子中，FROM customers LEFT OUTER JOIN orders，表示从FROM子句的左边表（customers）中选择所有行，如果为了从右边的表中（orders）选择所有行，应该使用RIGHT OUTER JOIN。
由此可看出外部联结的类型主要有两个：左外部联结和右外部联结，他们之间的唯一差别是所关联的表的顺序不一样。换句话说，左外部联结可通过颠倒FROM或WHERE子句中的表的顺序转换为右外部联结。所以两者可互换使用。
使用带聚集函数的联结
聚集函数用来汇总数据，虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但是这些函数也可以与联结一起使用。
示例：
SELECT customers.cust_name, custtomers.cust_id, COUNT (orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
输出：
cust_name | cust_id | num_ord
Coyote Inc. 10001 2
Wascals 10003 1
Yosemite Place 10004 1
E Fudd 10005 1
//以上语句的含义为：使用内部联结，检索所有客户及每个客户所下的订单数。
SELECT customers.cust_name, custtomers.cust_id, COUNT (orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
输出：
cust_name | cust_id | num_ord
Coyote Inc. 10001 2
Mouse House 10002 0
Wascals 10003 1
Yosemite Place 10004 1
E Fudd 10005 1
//这个例子使用左外部联结来包含所有的客户，甚至也包含那些没有下任何订单的客户，因为LEFT左侧是customers 表。
联结条件用ON来限定。
/************************** 组 合 查 询（UNION） *****************************/
主要讲述如何利用UNION操作符将多条SELECT 语句组合成一个结果集。
并（UNION）或符合查询（compound query）：多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句，其实MySQL也支持执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并，或符合查询。
有两种情况下需要使用组合查询：
* 在单个查询中从不同的表返回类似结构的数据；
* 对单个表执行多个查询，按单个查询返回结果。
组合查询和多个WHERE条件查询的区别：
多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。
UNION的使用：在每条SELECT语句之间放上关键字UNION。
案例：
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
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
//第一条SELECT语句检索价格不高于5的所有物品，第二条SELECT语句检索出供应商1001和1002生产的所有物品。
为了组合这两条语句，按如下进行：
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
1002FU13.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
同样的，不使用UNION，而是用多条WHERE子句的相同查询：
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 //注意这里没分号了
ON vend_id IN (1001,1002);
//从这个例子中我们可以发现：
1> 使用UNION比使用WHERE子句更为复杂，但是对于更为复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情况。使用UNION可能会更为简单。
2> 红色加粗那一行数据值显示了一次，在分别查询时，返回数据分别是4行和5行，UNION查询时返回数据是8行，而不是9行。这里就是包含或取消重复的行：若包含重复的行，可使用UNION ALL（返回9条），不包含重复的行就直接使用UNION即可（返回8条）。
UNION的使用规则：
* UNION必须由两条或两条以上的SELECT语句组成，语句间使用UNION隔开（故：如果组合4条SELECT语句，将会使用3个UNION关键字）；
* UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；
* 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（如，不同的数值类型或不同的日期类型）。
* 本例中使用的是组合查询是相同的表，其实UNION组合查询也可以应用于不同的表。
* 对结果进行排序，可在最后一条SELECT语句后添加ORDER BY子句，注意该子句是对整个返回结果进行排序的，并不是针对最后一条SELECT语句的返回结果进行排序的。
/************************** 全文本搜索 *****************************/
注意：并非所有的引擎都支持全文本搜索，如最常用的MyISAM支持，而InnoDB不支持。
在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含他们），哪些词不匹配，他们的匹配频率等。
启用全文本搜索支持
一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，他给出被索引列 的一个逗号分隔的列表。
案例：
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_data datatime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
//CREATE TABLE语句定义表 productnotes并列出它所包含的列，这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT（note_text）的指示对note_text进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
在定义之后，MySQL自动维护该索引，在增加更新或删除行时，索引随之自动更新。
注意：不能再导入数据时使用FULLTEXT，应该先导入数据，然后再修改表，定义FULLTEXT。
进行全文本搜索
在索引之后，使用两个函数Match()，和Against()执行全文本搜索，其中：
Match()：指定被搜索的列；
Against()：指定要使用的搜索表达式。
案例：
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against(‘rabbit’);
返回：
note_text
--------------
Customer complaint: rabbit has been able to delect 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.
//此SELECT语句检索单个列note_text，从WHERE子句中，可看出全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against(‘rabbit’)指定词rabbit作为搜索文本。由于有两个行包含词rabbit，故返回这两行。
注意：
1> 使用完整的Match() 说明：传递给Match() 的值必须与FULLTEXT() 定义中的相同（语句中绿色部分），如果指定多个列，则必须列出它们（而且次序相同）。
2> 搜索不区分大小写：除非使用BINARY方式，否则不区分大小写。
上述检索语句结果，也可以通过模糊查询来获得：
SELECT note_text
FROM productnotes
WHERE note_text LIKE ‘%rabbit%’;
返回：
note_text
--------------
Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait.
Customer complaint: rabbit has been able to delect trap, food apparently less effective now.
//和使用全文本搜索获得到的返回数据相比，输出顺序不一样。
这是为什么呢？
原因是：上述两个SELECT语句中都不包含ORDER BY语句，且LIKE以不特别有用的顺序返回数据，而全文本搜索返回以文本匹配的良好程度排序的数据。什么叫文本匹配的良好程度？我们观察到，返回的两个行都包含词rabbit，但包含词rabbit作为第三个词的行的等级要比作为第20个词的行的等级高。所以，全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。
Note：其实等级是由MySQL根据行中的词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算而来。正如所见，不包含词的行等级为0，包含词的行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。
使用查询扩展（MySQL4.1.1版本及以后）
查询扩展是用来设法放宽所返回的全文本搜索结果的范围。
什么意思呢？就是你想返回1,4两个数字，结果不仅会返回1,4，也会返回235等数字，返回范围会扩大。故利用查询扩展，能找 出可能相关的结果，即使它们并不精确包含所查找的词。
原理：进行两次全文本查询。
1> 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
2> 其次，MySQL检查这些匹配行并选择所有有用的词（后面解释什么是有用，什么无用）；
3> 最后，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
布尔文本匹配
由于性能比较低，就不多做说明了。
好了，接下来我们来到MySQL的重量级知识点部分了，就是CRUD，增删改查。
/************************** 插入数据 Insert *****************************/
Insert：用来插入或添加行到数据库表中的，而且Insert语句一般不会有输出的。
案例：
INSERT INTO customers
VALUES(‘Pep E. Lapew’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
//我直接说结论：该种方式是不安全的，因为这种方式高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息，即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES(‘Pep E. Lapew’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
//该种方式是安全的，因为在表名后明确给出了列名，以及和列名一一对应的列值。各值按位入坑即可。这种方式即使表的结构改变，该语句仍然有效仍能正常工作。
NOTE：
省略列：如果表的定义允许，则可以在INSERT操作中省略某些列，省略的列必须满足一下某个条件：
* 该列定义为允许NULL值（无值或空值）；
* 在表定义中给出默认值，这表示如果不给出默认值，将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。
插入多个行
使用多条SELECT语句，也可以一次性提交（不过每条语句中用分号结束隔开）。
插入检索出的数据
Insert一般用来给表插入一个指定列值的行，此外，还可以将一条SELECT语句的结果插入表中。这就是INSERT SELECT，这是由一条INSERT 和一条SELECT语句组成的。
案例：
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
//以上语句表示：把一个名为custnew的表中的数据导入customers表中。这里不需要再每次读取一行然后再将读取的数据再用INSERT插入。
/************************** 更新和删除数据*****************************/
更新数据：
* 更新表中特定行；
* 更新表中所有行。
案例：
UPDATE customers
SET cust_email = ‘elmer@fudd.com’
WHERE cust_id = 10005;
//如上所见：UPDATE语句总是以要更新的表的名字开始，再次例子中，要更新的表的名字为custpmers，set命令用来将新值赋给被更新的列，这里是将cust_email赋值为指定值。
这里以WHERE子句结束，它告诉MySQL更新哪一行，没有改子句，将更新所有行。
当更新多个列时，只需要使用单个set命令即可，后面每个“列 = 值”对之间用逗号（，）隔开（最后一列不用逗号），如：
UPDATE customers
SET cust_name = ‘THE Fudds’
cust_email = ‘elmer@fudd.com’
WHERE cust_id = 10005;
注意：
* IGNORE关键字：如果用UPDATE语句更新多行，在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消（错误发生前更新的所有行被回复到他们原来的值）。为了即使是发生了错误，也要保证更新继续，我们可使用IGNORE关键字，如下：
UPDATE IGNORE customers
删除数据
为了从一个表中删除（去掉）数据，我们可使用DELETE语句，有两种情况：
* 从表中删除特定行；
* 从表中删除所有行。
DELETE FROM customers
WHERE cust_id = 10006;
//删除customers表中的cust_id为10006的行。
总结：
1> 使用UPDATE 和DELETE语具全都具有WHERE子句，否则就会应用到所有行。
/************************** 创建和操纵表 *****************************/
表的创建用CREATE TABLE tablename语句。
如：
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
.....
PRIMARY KEY(cust_id)
) ENGINE=InnoDB;
注意：
1> NULL值：不要把NULL值与空串相混淆。NULL值是没有值，它不是空串，如果指定’’（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值，NULL值用关键字NULL而不是空串指定。
2> 主键值必须唯一。如果主键使用单个列，则它的值必须唯一，如果使用多个列，则这些列的组合值必须唯一。故主键只能使用不允许NULL值的列。
AUTO_INCREMENT
AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id，从而可以用作主键值。
缺点：让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁。
引擎类型
* MyISAM：默认，支持全文本搜索，但不支持事务处理；
* InnoDB：支持事务处理，但不支持全文本搜索。
* MEMORY：功能上等同于MyISAM，但由于数据存储在内存，而不是磁盘，所以速度很快。
ALTER TABLE：更新表；
DROP TABLE：删除表；
RENAME TABLE：重命名表；
/************************** 使用视图 *****************************/
视图在MySQL5版本及以后才支持。
视图：是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
案例：
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = order.order_num
AND prod_id = ‘TNT2’;
//此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，兵器恩知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。
故现在如果我们把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据。
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = ‘TNT2’;
//productcustomers就是一个视图。它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确连接表的相同的查询）。
为什么使用视图？
* 重用SQL语句；
* 简化复杂的SQL操作；
* 使用表的组成部分而不是整个表；
* 保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
* 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。
视图创建后可用与表基本相同的方式利用他们。重要的是，我们要知道视图仅仅是用来查看存储在别处的数据的一种设施。
使用视图
* 创建：CREATE VIEW；
* 查看创建视图的语句：SHOW CREATE VIEW viewname;
* 删除视图：DROP VIEW viewname;
案例：
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
//创建了一个productcustomers 视图，它联结了三个表，以返回已订购了任意产品的所有客户的列表。如果再执行SELECT * FROM productcustomers ，将会列出以上结果。
还有：
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = ‘TNT2’;
返回：
cust_name | cust_contact
Coyote Inc. Y Lee
Yosemite Place Y Sam
//这条语句通过WHERE子句从视图中检索出特定的数据。
NOTE：
视图是否能更新？
答：视情况而定。当MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除），这意味着如果视图定义中有如下操作，则不能进行视图的更新：分组（使用GROUP BY和HAVING），联结，子查询，并。聚集函数（Min()，Count()等），DISTINCT，导出（计算）列。有很多情况下视图都是不可更新的，看起来似乎是一个限制，但实际上不是，因为视图主要用于数据检索（Select）,而不是用于更新（insert,update和delete）。
小结：
视图为虚拟的表，它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。
/************************** 使用存储过程 *****************************/
存储过程：MySQL5及以后版本支持。
存储过程：简单来说就是为了以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，但他们的作用远不止批处理。
优点：简单、安全和高性能。
缺点：编写复杂难度大，非管理员缺少创建权限。
1> 执行存储过程：
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL，CALL接受存储过程的名字以及需要传递给它的任意参数。如：
CALL productpricing(@pricelow
@pricehigh
@priceaverage);
//其中执行名为productpricing 的存储过程， 它计算并返回产品的最低，最高和平均价格。
存储过程可以显示结果，也可以不显示结果。
NOTE：所有MySQL变量都必须以@ 开始。
2> 创建存储过程：
案例：
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
//此存储过程名为productpricing，用CREATE PROCEDURE productpricing() 语句来定义。如果有参数，将在 ( ) 中列举出来，此存储过程没有参数，但仍需要后跟括号 ( ) 。BEGIN 和END 用来限定存储过程体，过程体本身就是一个简单的SELECT语句。
带参案例：
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8, 2),
OUT p2 DECIMAL(8, 2),
OUT p3 DECIMAL(8, 2),
)
BEGIN
SELECT Min(prod_price)
INTO p1
FROM products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
//此存储过程接受3 个参数：p1存储最低价格，ph存储最高价格，pa存储平均价格。
每个参数必须具有指定的类型，这里使用十进制值。关键字OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）三种类型的参数。
存储过程的代码位于BEGIN和END语句内，如前所见，他们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。
3> 使用存储过程：
CALL productpricing();
输出：
priceaverage
16.1333571
//像调用函数一样，所以存储过程名后需要有（） 符号，即使不传递参数也需要。
带参调用：
CALL productpricing(@pricelow
@pricehigh
@priceaverage);
为了显示检索出的产品平均价格，可进行：
SELECT @priceaverage;
输出：
@priceaverage
16.133571428
为了获得三个值，可使用以下语句：
SELECT @pricelow, @pricehigh, @priceaverage;
输出：
@pricelow, @pricehigh, @priceaverage;
2.50 55.00 16.133571428
4> 删除存储过程：
DROP PROCEDURE productpricing;
//注意，删除时不需要加小括号 ( )。
procedure
5> 检查存储过程：
为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表，使用：
SHOW PROCEDURE STATUS;
/************************** 使用游标cursor *****************************/
游标：MySQL 5.0及以上版本支持。
我们知道，MySQL检索操作返回一组称为结果集的行。
问题：
使用简单的SELECT语句没有办法得到第一行、下一个行或前十行，也不存在每次一行一行地处理所有行的简单办法（相对于成批地处理）。有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。
游标（cursor）：是一个存储在MySQL服务器上的数据库查询。它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标后，应用程序可以根据需要滚动或者浏览其中的数据。而且，MySQL游标只能应用于存储过程（和函数）。
1> 使用游标
* 在能使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
* 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
* 对于填有数据的游标，根据需要取出（检索）各行。
* 在结束游标使用时，必须关闭游标。
可频繁打开、关闭游标，可根据需要频繁地执行取操作。
2> 创建游标
用DECLARE语句创建游标，DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。
案例：
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num
FROM orders;
END;
//DECLARE 语句定义和命名游标，这里为ordernumbers 。存储过程处理完成后，游标就消失（因为它局限于存储过程）。
3> 打开和关闭游标
用 OPEN CURSOR打开，如：OPEN ordernumbers ；
用 CLOSE CURSOR关闭。如：CLOSE ordernumbers 。
案例：
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num
FROM orders;
OPEN ordernumbers ;
CLOSE ordernumbers ;
END;
4> 使用游标数据
在游标被打开后，可以使用FETCH 语句来分别访问它的每一行。
案例01：
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT; //声明局部变量
DECLARE ordernumbers CURSOR //声明游标
FOR
SELECT order_num
FROM orders;
OPEN ordernumbers ; //打开游标
FETCH ordernumbers INTO o; //得到订单号，并赋值给变量o;
CLOSE ordernumbers ; //关闭游标
END;
//只是取到数据并没有其他任何操作；
案例02：
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT; //声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE ordernumbers CURSOR //声明游标
FOR
SELECT order_num
FROM orders;
DECLEAR CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1; //声明continue handler
OPEN ordernumbers ; //打开游标
REPEAT //进入循环操作
FETCH ordernumbers INTO o; //得到订单号，并赋值给变量o;
UNTIL done END REPEAT;
CLOSE ordernumbers ; //关闭游标
END;
//进入repeat操作中，反复操作，直到done为真（由UNTIL done END REPEAT; 规定），为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。那么，done怎样才能在结束被设置为真呢？答案是：
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里的意思是，在当SQLSTATE ‘02000’时，置 done=1。
案例03：
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT; //声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE t DECIMAL(8, 2);
DECLARE ordernumbers CURSOR //声明游标
FOR
SELECT order_num
FROM orders;
DECLEAR CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1; //声明continue handler
OPEN ordernumbers ; //打开游标
REPEAT //进入循环操作
FETCH ordernumbers INTO o; //得到订单号，并赋值给变量o;
CALL ordertotal(o, 1, t);
INSERT INTO ordertotals(order_num,, total)
VALUES(o, t);
UNTIL done END REPEAT; //结束循环
CLOSE ordernumbers ; //关闭游标
END;
//解释：在这个案例中，我们增加了另一个名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个名为ordertotals的新表（保存存储过程生成的结果）。FETCH像以前一样取每个order_num ，然后用CALL执行另一个存储过程（在前一章中创建的）来计算每个订单的带税的合计（结果存储到 t），最后用INSERT保存每个订单的订单号和合计。
查看该表：
SELECT *
FROM ordertotals;
输出：
order_num total
20005 158.86
20006 58.30
20007 1060.00
20008 132.50
20009 40.78
//以上就是一个存储过程，游标，逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。
/************************** 使用触发器 *****************************/
在MySQL 5及之后支持。
触发器：是MySQL响应以下任意语句（DELETE，INSERT，UPSATE）时，走动执行的一条MySQL 语句。
创建触发器
前提准备：
* 唯一的触发器名；
* 触发器关联的表；
* 触发器应该响应的活动（DELETE，INSERT，UPSATE）；
* 触发器何时执行（处理之前或之后）。
案例：
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT ‘Product added’;
//CREATE TRIGGER用来创建名为newproduct 的新触发器。AFTER 表示触发器在INSERT 操作发生之后执行，FOR EACH ROW表示对每个插入行都执行。
只有表才支持触发器，视图不支持。触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，故每个表最多支持6 个触发器即（：每条INSERT，OPDATE，DELETE的前后）。
删除触发器
DROP TRIGGER newproduct;
/************************** 管理事务处理 *****************************/
事务处理（transaction processing）：可以用来维护数据库的完整性，可以保证成批的MySQL操作要么完全执行，要么完全不执行。
事务处理的优点：
事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，他们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
事务（transaction）:一组SQL语句；
回退（rollback）：指撤销指定SQL语句的过程；
提交（commit）：指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）：指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。
事务处理的关键在于：将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
START TRANSACTION：标识事务的开始；
回滚
案例：
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
//解释：
SELECT * FROM ordertotals; //显示表内的数据（表明该表不为空）；
START TRANSACTION; //开启事务处理
DELETE FROM ordertotals; //删除表中所有数据（行）
SELECT * FROM ordertotals; //显示表中数据（此时应该为空）
ROLLBACK; //回滚开启事务后的所有操作
SELECT * FROM ordertotals; //显示表中的数据（此时不为空，因为有回滚操作将数据恢复）
提交
在事务处理块中，提交不会隐含进行，必须要通过commit显式进行：
示例：
START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;
//该示例表示：从系统中完全删除订单20010，因为涉及到更新两个数据库表orders 和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的commit语句仅在不出错时写出更改，日过第一条DELETE起作用，但第二条失败，则DELETE不会提交。
特别注意：当COMMIT或ROLLBACK语句执行后，事务会自动关闭，将来的更改操作会隐含提交。
使用保留点
当我们不使用保留点时，rollback时，要回退到启动事务的地方，即启动事务时候的操作都会恢复。但是为了更为方便，就是希望回退到指定点，出现了保留点关键字。
设置保留点：SAVEPOINT delete1;
回退到保留点：ROLLBACK TO delete1;
保留点越多越好，因为保留点越多，就越能按自己的意愿灵活地进行回退。
保留点在事务处理完成后自动释放（即执行一条ROLLBACK或COMMIT）。
/************************** MySQL数据类型 *****************************/
总结：
学习到此为止，算是又重新复习了一遍，希望以后复习直接来这里看博文就行，不用再翻电子书了，哈哈哈****
Over......
