¶首先来首歌曲来放松一下吧！

¶一、准备数据

廖雪峰SQL教程例子：例子代码点击这里！

将上方链接或者下方代码保存到文本文件，改名为init-test-data.sql。然后再本地cmd运行$ mysql -u root -p < init-test-data.sql即可创建一个名为test的数据库，有两张表classes和students!

注意要在cmd，不要使用powershell，powershell无法识别命令行<这个符号！

-- 如果test数据库不存在，就创建test数据库：
CREATE DATABASE IF NOT EXISTS test;

-- 切换到test数据库
USE test;

-- 删除classes表和students表（如果存在）：
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- 创建classes表：
CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建students表：
CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入classes记录：
INSERT INTO classes(id, name) VALUES (1, '一班');
INSERT INTO classes(id, name) VALUES (2, '二班');
INSERT INTO classes(id, name) VALUES (3, '三班');
INSERT INTO classes(id, name) VALUES (4, '四班');

-- 插入students记录：
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, '小红', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (3, 1, '小军', 'M', 88);
INSERT INTO students (id, class_id, name, gender, score) VALUES (4, 1, '小米', 'F', 73);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, '小白', 'F', 81);
INSERT INTO students (id, class_id, name, gender, score) VALUES (6, 2, '小兵', 'M', 55);
INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 2, '小林', 'M', 85);
INSERT INTO students (id, class_id, name, gender, score) VALUES (8, 3, '小新', 'F', 91);
INSERT INTO students (id, class_id, name, gender, score) VALUES (9, 3, '小王', 'M', 89);
INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 3, '小丽', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';

¶二、基本查询

¶1、SELECT语句

使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。

该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。

1 2	`$ USE test; $ SELECT * FROM <表名>;`

¶2、DISTINCT只返回不同值

用于返回不同的值！

DISTINCT 关键字应用于所有列而不仅是前置它的列。如果给出 SELECT DISTINCT vend_id,prod_price ，除非指定的两个列都不同，否则所有行都将被检索出来。

1	`SELECT DISTINCT vend_id FROM products;`

¶3、用于计算的SELECT

上述查询会直接计算出表达式的结果。虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

$ mysql> SELECT 200 * 3000000000;
+------------------+
| 200 * 3000000000 |
+------------------+
|     600000000000 |
+------------------+
1 row in set (0.00 sec)

¶三、条件查询

¶1、WHERE 条件

使用SELECT * FROM <表名>可以查询到一张表的所有记录。但是，很多时候，我们并不希望获得所有记录，而是根据条件选择性地获取指定条件的记录，例如，查询分数在80分以上的学生记录。在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。

格式如下：

1	`$ SELECT * FROM <表名> WHERE <条件表达式>`

效果如下：

¶2、逻辑运算

与其他语言不同：等于号不是==而是=！

¶2.1 AND

就是与运算符！

$ mysql> SELECT * FROM students WHERE score >= 80 AND gender = 'M';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | 小明   | M      |    90 |
|  3 |        1 | 小军   | M      |    88 |
|  7 |        2 | 小林   | M      |    85 |
|  9 |        3 | 小王   | M      |    89 |
+----+----------+--------+--------+-------+
4 rows in set (0.00 sec)

¶2.2 OR

就是或运算符！

$ mysql> SELECT * FROM students WHERE score >= 80 OR gender = 'M';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | 小明   | M      |    90 |
|  2 |        1 | 小红   | F      |    95 |
|  3 |        1 | 小军   | M      |    88 |
|  5 |        2 | 小白   | F      |    81 |
|  6 |        2 | 小兵   | M      |    55 |
|  7 |        2 | 小林   | M      |    85 |
|  8 |        3 | 小新   | F      |    91 |
|  9 |        3 | 小王   | M      |    89 |
| 10 |        3 | 小丽   | F      |    85 |
+----+----------+--------+--------+-------+
9 rows in set (0.00 sec)

¶2.3 NOT

就是非运算符！

MySQL支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反，这与多数其他 DBMS允许使用 NOT 对各种条件取反有很大的差别。

$ mysql> SELECT * FROM students WHERE NOT class_id = 2;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | 小明   | M      |    90 |
|  2 |        1 | 小红   | F      |    95 |
|  3 |        1 | 小军   | M      |    88 |
|  4 |        1 | 小米   | F      |    73 |
|  8 |        3 | 小新   | F      |    91 |
|  9 |        3 | 小王   | M      |    89 |
| 10 |        3 | 小丽   | F      |    85 |
+----+----------+--------+--------+-------+
7 rows in set (0.00 sec)

¶2.4 多条件使用括号

同样：加括号改变了优先级！

$ mysql> SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  6 |        2 | 小兵   | M      |    55 |
+----+----------+--------+--------+-------+
1 row in set (0.00 sec)

¶3、条件表达式符号

注意：不等于可以是!=或者是<>！

字符串要用单引号引起来！

%可以匹配任意字符，包括空字符且不一定是一个字符！

条件	表达式举例1	表达式举例2	说明
使用=判断相等	score = 80	name = ‘abc’	字符串需要用单引号括起来
使用>判断大于	score > 80	name > ‘abc’	字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等	score >= 80	name >= ‘abc’
使用<判断小于	score < 80	name <= ‘abc’
使用<=判断小于或相等	score <= 80	name <= ‘abc’
使用<>判断不相等	score <> 80	name <> ‘abc’
使用!=判断不相等	score!=80	name!=‘abc’
使用LIKE判断相似	name LIKE ‘ab%’	name LIKE ‘%bc%’	%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’
BETWEEN a AND b	BETWEEN 80 AND 90		在指定两个值之间
IS NULL	score IS NULL		指的是空，不是0，空字符串，空格，可能出现在建表时设置的NOT NULL的属性列

¶4、其他操作符

¶4.1 IN

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取合法值的由逗号分隔的清单，全都括在圆括号中。

1
2
3

IN (值1，值2....);

SELECT * FROM student WHERE class_id IN (1,2) ORDER BY name;

IN 与 OR相比的优点：

在使用长的合法选项清单时，IN 操作符的语法更清楚且更直观。
在使用 IN 时，计算的次序更容易管理（因为使用的操作符更少）。
IN 操作符一般比 OR 操作符清单执行更快。
IN 的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 子句。（后续会介绍）

¶4.2 LIKE

LIKE 指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

¶4.2.1 百分号（%）通配符

% 表示任何字符出现任意（0,1，…）次数！

此搜索区分大小写， 'jet%' 与 JetPack 1000 将不匹配。

可以出现在任意位置，可以出现任意多个。

例如：s%e,%ss%

注意：

尾空格：%abc无法匹配%abc<空格>，要想匹配可以在最后再加一个%abc%，后面会讲到一个更好的方法，使用函数
%无法匹配NULL。

¶4.2.2 下划线（_）通配符

_只匹配单个（只能是1个）任意字符，

1	`SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ abc';`

¶4.2.3 使用通配符的优缺点

可以使用通配符完成一些不易完成的搜索
使用通配符会带来效率的降低，会比其他搜索更耗时
不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

¶四、投影查询

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

投影查询：仅返回指定列！

¶1、普通投影

1	`$ SELECT 列1, 列2, 列3 FROM ...，;`

例子如下：

$ mysql> SELECT id, score, name FROM students;
+----+-------+--------+
| id | score | name   |
+----+-------+--------+
|  1 |    90 | 小明   |
|  2 |    95 | 小红   |
|  3 |    88 | 小军   |
|  4 |    73 | 小米   |
|  5 |    81 | 小白   |
|  6 |    55 | 小兵   |
|  7 |    85 | 小林   |
|  8 |    91 | 小新   |
|  9 |    89 | 小王   |
| 10 |    85 | 小丽   |
+----+-------+--------+
10 rows in set (0.01 sec)

¶2、别名投影

1	`$ SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...;`

例子如下：score 一列改别名为 points ！

$ mysql> SELECT id, score points, name FROM students;
+----+--------+--------+
| id | points | name   |
+----+--------+--------+
|  1 |     90 | 小明   |
|  2 |     95 | 小红   |
|  3 |     88 | 小军   |
|  4 |     73 | 小米   |
|  5 |     81 | 小白   |
|  6 |     55 | 小兵   |
|  7 |     85 | 小林   |
|  8 |     91 | 小新   |
|  9 |     89 | 小王   |
| 10 |     85 | 小丽   |
+----+--------+--------+
10 rows in set (0.00 sec)

¶3、复杂投影

将投影与别名与WHERE条件查询结合！

mysql> SELECT id, score points, name FROM students WHERE gender = 'M';
+----+--------+--------+
| id | points | name   |
+----+--------+--------+
|  1 |     90 | 小明   |
|  3 |     88 | 小军   |
|  6 |     55 | 小兵   |
|  7 |     85 | 小林   |
|  9 |     89 | 小王   |
+----+--------+--------+
5 rows in set (0.00 sec)

¶五、排序

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。

在给出 ORDER BY 子句时，应该保证它位于 FROM 子句之后。如果使用 LIMIT ，它必须位于 ORDER BY之后。使用子句的次序不对将产生错误消息。

¶1、升序

使用 ORDER BY ***语句；ASC：升序，可省略！

$ mysql> SELECT id, name, gender, score FROM students ORDER BY score;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  6 | 小兵   | M      |    55 |
|  4 | 小米   | F      |    73 |
|  5 | 小白   | F      |    81 |
|  7 | 小林   | M      |    85 |
| 10 | 小丽   | F      |    85 |
|  3 | 小军   | M      |    88 |
|  9 | 小王   | M      |    89 |
|  1 | 小明   | M      |    90 |
|  8 | 小新   | F      |    91 |
|  2 | 小红   | F      |    95 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)

¶2、降序

使用 ORDER BY *** DESC，DESC表示“倒序”，不可省略！

$ mysql> SELECT id, name, gender, score FROM students ORDER BY score DESC;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
|  9 | 小王   | M      |    89 |
|  3 | 小军   | M      |    88 |
|  7 | 小林   | M      |    85 |
| 10 | 小丽   | F      |    85 |
|  5 | 小白   | F      |    81 |
|  4 | 小米   | F      |    73 |
|  6 | 小兵   | M      |    55 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)

¶3、复杂排序

¶3.1 多列排序

若score列有相同的数据，要进一步排序，可以继续添加列名。

即先按score降序，再按gender升序（默认为ASC升序）。

$ mysql> SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
|  9 | 小王   | M      |    89 |
|  3 | 小军   | M      |    88 |
| 10 | 小丽   | F      |    85 |
|  7 | 小林   | M      |    85 |
|  5 | 小白   | F      |    81 |
|  4 | 小米   | F      |    73 |
|  6 | 小兵   | M      |    55 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)

¶3.2 条件排序

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面！

$ mysql> SELECT id, name, gender, score
    -> FROM students
    -> WHERE class_id = 1
    -> ORDER BY score DESC;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  1 | 小明   | M      |    90 |
|  3 | 小军   | M      |    88 |
|  4 | 小米   | F      |    73 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)

¶六、分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1 ~ 100条记录作为第1页，显示第101 ~ 200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M ~ N条记录。

通过LIMIT <M> OFFSET <N>子句实现：LIMIT表示每页最多三条信息，OFFSET表示从第几条开始。（SQL索引从0开始）

可以使用LIMIT控制要输出的结果！

¶注意：

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

¶规则：

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。

¶如下：

$ mysql> SELECT id, name, gender, score
    -> FROM students
    -> ORDER BY score DESC
    -> LIMIT 3 OFFSET 0;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | 小红   | F      |    95 |
|  8 | 小新   | F      |    91 |
|  1 | 小明   | M      |    90 |
+----+--------+--------+-------+
3 rows in set (0.00 sec)

$ mysql> SELECT id, name, gender, score
    -> FROM students
    -> ORDER BY score DESC
    -> LIMIT 3 OFFSET 3;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  9 | 小王   | M      |    89 |
|  3 | 小军   | M      |    88 |
| 10 | 小丽   | F      |    85 |
+----+--------+--------+-------+
3 rows in set (0.00 sec)

...

$ mysql> SELECT id, name, gender, score
    -> FROM students
    -> ORDER BY score DESC
    -> LIMIT 3 OFFSET 9;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  6 | 小兵   | M      |    55 |
+----+--------+--------+-------+
1 row in set (0.00 sec)

若OFFSET设置的越界了，并不会报错，会返回一个空集合！

$ mysql> SELECT id, name, gender, score
    -> FROM students
    -> ORDER BY score DESC
    -> LIMIT 3 OFFSET 20;
Empty set (0.00 sec)

¶七、聚合（聚集）查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合(聚集)函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

¶1、COUNT()函数

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

可以设置一个别名，便于处理结果：

COUNT(*)和COUNT(id)实际上是一样的效果。

如果指定列名，则指定列的值为空的行被 COUNT()函数忽略，但如果 COUNT() 函数中用的是星号（ * ），则不忽略。

$ mysql> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

-- 取一个别名 num
mysql> SELECT COUNT(*) num FROM students;
+-----+
| num |
+-----+
|  10 |
+-----+
1 row in set (0.00 sec)

同样可以使用WHERE条件

mysql> SELECT COUNT(*) boys FROM students WHERE gender = 'M';
+------+
| boys |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

¶2、其他聚集函数

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型，SUM() 函数忽略列值为 NULL 的行
AVG	计算某一列的平均值，该列必须为数值类型，AVG() 函数忽略列值为 NULL 的行。
MAX	计算某一列的最大值，MAX() 函数忽略列值为 NULL 的行
MIN	计算某一列的最小值，MIN() 函数忽略列值为 NULL 的行
CEILING	上取整
FLOOR	下取整

$ mysql> SELECT AVG(score) average FROM students WHERE gender = 'M';
+---------+
| average |
+---------+
| 81.4000 |
+---------+
1 row in set (0.01 sec)

-- WHERE找不到返回NULL

$ mysql> SELECT AVG(score) average FROM students WHERE gender = 'X';
+---------+
| average |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

-- 计算页数

$ SELECT CEILING(COUNT(*) / 3)  pageSize FROM students;
mysql> SELECT CEILING(COUNT(*) / 3)  pageSize FROM students;
+----------+
| pageSize |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

¶3、使用DISTINCT

默认为ALL，可选DISTINCT，即去掉重复值

上面的聚集（聚合）函数都可以使用DISTINCT来去重计算

对于MIN，MAX添加该参数无意义，因为去不去重结果无影响！

下面的这个例子就是将价格不同的过滤掉，仅计算价格不同的均值。

SELECT AVG(DISTINCT prod_price) avg_rice
FROM products
WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15 .998000|
+-----------+

¶4、组合聚集函数

即一条语句使用多个聚集函数。

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;

¶八、多表查询

由于多表查询的笛卡尔乘积问题，会导致每行两表的数据并没有逻辑对应关系。。。

即直接查出来的数据对应不一定正确，需要进行条件限定，如students表的class_id 与 classes表的id相同才能唯一确定一个对应关系。

一般使用主键或外键进行多表关联查询，笛卡尔乘积没太多意义，而且很容易查询量爆炸，不建议使用！

¶1、语法：

SELECT * FROM <表1> <表2>。

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

给表起别名，再给相同列起别名有助于查看！

$ mysql> SELECT
    ->     s.id sid,
    ->     s.name,
    ->     s.gender,
    ->     s.score,
    ->     c.id cid,
    ->     c.name cname
    -> FROM students s, classes c;
+-----+--------+--------+-------+-----+--------+
| sid | name   | gender | score | cid | cname  |
+-----+--------+--------+-------+-----+--------+
|   1 | 小明   | M      |    90 |   1 | 一班   |
|   1 | 小明   | M      |    90 |   2 | 二班   |
|   1 | 小明   | M      |    90 |   3 | 三班   |
|   1 | 小明   | M      |    90 |   4 | 四班   |
|   2 | 小红   | F      |    95 |   1 | 一班   |
|   2 | 小红   | F      |    95 |   2 | 二班   |
|   2 | 小红   | F      |    95 |   3 | 三班   |
|   2 | 小红   | F      |    95 |   4 | 四班   |
|   3 | 小军   | M      |    88 |   1 | 一班   |
|   3 | 小军   | M      |    88 |   2 | 二班   |
|   3 | 小军   | M      |    88 |   3 | 三班   |
|   3 | 小军   | M      |    88 |   4 | 四班   |
|   4 | 小米   | F      |    73 |   1 | 一班   |
|   4 | 小米   | F      |    73 |   2 | 二班   |
|   4 | 小米   | F      |    73 |   3 | 三班   |
|   4 | 小米   | F      |    73 |   4 | 四班   |
|   5 | 小白   | F      |    81 |   1 | 一班   |
|   5 | 小白   | F      |    81 |   2 | 二班   |
|   5 | 小白   | F      |    81 |   3 | 三班   |
|   5 | 小白   | F      |    81 |   4 | 四班   |
|   6 | 小兵   | M      |    55 |   1 | 一班   |
|   6 | 小兵   | M      |    55 |   2 | 二班   |
|   6 | 小兵   | M      |    55 |   3 | 三班   |
|   6 | 小兵   | M      |    55 |   4 | 四班   |
|   7 | 小林   | M      |    85 |   1 | 一班   |
|   7 | 小林   | M      |    85 |   2 | 二班   |
|   7 | 小林   | M      |    85 |   3 | 三班   |
|   7 | 小林   | M      |    85 |   4 | 四班   |
|   8 | 小新   | F      |    91 |   1 | 一班   |
|   8 | 小新   | F      |    91 |   2 | 二班   |
|   8 | 小新   | F      |    91 |   3 | 三班   |
|   8 | 小新   | F      |    91 |   4 | 四班   |
|   9 | 小王   | M      |    89 |   1 | 一班   |
|   9 | 小王   | M      |    89 |   2 | 二班   |
|   9 | 小王   | M      |    89 |   3 | 三班   |
|   9 | 小王   | M      |    89 |   4 | 四班   |
|  10 | 小丽   | F      |    85 |   1 | 一班   |
|  10 | 小丽   | F      |    85 |   2 | 二班   |
|  10 | 小丽   | F      |    85 |   3 | 三班   |
|  10 | 小丽   | F      |    85 |   4 | 四班   |
+-----+--------+--------+-------+-----+--------+
40 rows in set (0.00 sec)

¶2、使用WHERE

同样可以使用WHERE进行限制。

mysql> SELECT
    ->     s.id sid,
    ->     s.name,
    ->     s.gender,
    ->     s.score,
    ->     c.id cid,
    ->     c.name cname
    -> FROM students s, classes c
    -> WHERE s.gender = 'M' AND c.id = 1;
+-----+--------+--------+-------+-----+--------+
| sid | name   | gender | score | cid | cname  |
+-----+--------+--------+-------+-----+--------+
|   1 | 小明   | M      |    90 |   1 | 一班   |
|   3 | 小军   | M      |    88 |   1 | 一班   |
|   6 | 小兵   | M      |    55 |   1 | 一班   |
|   7 | 小林   | M      |    85 |   1 | 一班   |
|   9 | 小王   | M      |    89 |   1 | 一班   |
+-----+--------+--------+-------+-----+--------+
5 rows in set (0.01 sec)

¶九、连接（联结）查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

¶1、INNER JOIN查询（内连接）

现在问题来了，存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。

这时，连接查询就派上了用场。

看下方结果，就知道这是连接了classes表的name列，尽可能满足两个表情况，不会出现NULL的情况。

先确定主表，仍然使用FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
可选：加上WHERE子句、ORDER BY等子句。

示例图如下：

代码如下：

$ mysql> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> INNER JOIN classes c
    -> ON s.class_id = c.id;
+----+--------+----------+------------+--------+-------+
| id | name   | class_id | class_name | gender | score |
+----+--------+----------+------------+--------+-------+
|  1 | 小明   |        1 | 一班       | M      |    90 |
|  2 | 小红   |        1 | 一班       | F      |    95 |
|  3 | 小军   |        1 | 一班       | M      |    88 |
|  4 | 小米   |        1 | 一班       | F      |    73 |
|  5 | 小白   |        2 | 二班       | F      |    81 |
|  6 | 小兵   |        2 | 二班       | M      |    55 |
|  7 | 小林   |        2 | 二班       | M      |    85 |
|  8 | 小新   |        3 | 三班       | F      |    91 |
|  9 | 小王   |        3 | 三班       | M      |    89 |
| 10 | 小丽   |        3 | 三班       | F      |    85 |
+----+--------+----------+------------+--------+-------+
10 rows in set (0.01 sec)

¶2、RIGHT OUTER JOIN（右外连接）

尽量满足第二个表的情况，第一个表若没有对应的信息，会以NULL显示：

注意：左右外连接可以省略写OUTER。

示例图如下：

代码如下：

$ mysql> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> RIGHT OUTER JOIN classes c
    -> ON s.class_id = c.id;
+------+--------+----------+------------+--------+-------+
| id   | name   | class_id | class_name | gender | score |
+------+--------+----------+------------+--------+-------+
|    1 | 小明   |        1 | 一班       | M      |    90 |
|    2 | 小红   |        1 | 一班       | F      |    95 |
|    3 | 小军   |        1 | 一班       | M      |    88 |
|    4 | 小米   |        1 | 一班       | F      |    73 |
|    5 | 小白   |        2 | 二班       | F      |    81 |
|    6 | 小兵   |        2 | 二班       | M      |    55 |
|    7 | 小林   |        2 | 二班       | M      |    85 |
|    8 | 小新   |        3 | 三班       | F      |    91 |
|    9 | 小王   |        3 | 三班       | M      |    89 |
|   10 | 小丽   |        3 | 三班       | F      |    85 |
| NULL | NULL   |     NULL | 四班       | NULL   |  NULL |
+------+--------+----------+------------+--------+-------+
11 rows in set (0.01 sec)

¶3、LEFT OUTER JOIN（左外连接）

尽量满足第一个表的情况，第二个表若没有对应信息，会以NULL显示：

由于students表都可以在classes表匹配，所以添加一行吧唧id为5的，来说明左外连接问题。

示例图如下：

代码如下：

-- 
$ mysql> INSERT INTO students (class_id, name, gender, score) values (5, '新生', 'M', 88);
Query OK, 1 row affected (0.01 sec)

$ mysql> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> LEFT OUTER JOIN classes c
    -> ON s.class_id = c.id;
+----+--------+----------+------------+--------+-------+
| id | name   | class_id | class_name | gender | score |
+----+--------+----------+------------+--------+-------+
|  1 | 小明   |        1 | 一班       | M      |    90 |
|  2 | 小红   |        1 | 一班       | F      |    95 |
|  3 | 小军   |        1 | 一班       | M      |    88 |
|  4 | 小米   |        1 | 一班       | F      |    73 |
|  5 | 小白   |        2 | 二班       | F      |    81 |
|  6 | 小兵   |        2 | 二班       | M      |    55 |
|  7 | 小林   |        2 | 二班       | M      |    85 |
|  8 | 小新   |        3 | 三班       | F      |    91 |
|  9 | 小王   |        3 | 三班       | M      |    89 |
| 10 | 小丽   |        3 | 三班       | F      |    85 |
| 11 | 新生   |        5 | NULL       | M      |    88 |
+----+--------+----------+------------+--------+-------+
11 rows in set (0.01 sec)

¶4、FULL OUTER JOIN（全外连接）

也就是左右外连接的并集，没有的信息显示为NULL：

MySQL并不支持全连接。。。

示例图如下：

代码如下：

$ mysql> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> FULL OUTER JOIN classes c
    -> ON s.class_id = c.id;
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 'FULL OUTER JOIN classes c
ON s.class_id = c.id' at line 3

代替解决方案：使用UNION，左连接一次，右连接一次，再使用UNION合并。

代码如下：

mysql> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> RIGHT OUTER JOIN classes c ON class_id = c.id
    -> UNION
    -> SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    -> FROM students s
    -> LEFT OUTER JOIN classes c ON class_id = c.id;
+------+--------+----------+------------+--------+-------+
| id   | name   | class_id | class_name | gender | score |
+------+--------+----------+------------+--------+-------+
|    1 | 小明   |        1 | 一班       | M      |    90 |
|    2 | 小红   |        1 | 一班       | F      |    95 |
|    3 | 小军   |        1 | 一班       | M      |    88 |
|    4 | 小米   |        1 | 一班       | F      |    73 |
|    5 | 小白   |        2 | 二班       | F      |    81 |
|    6 | 小兵   |        2 | 二班       | M      |    55 |
|    7 | 小林   |        2 | 二班       | M      |    85 |
|    8 | 小新   |        3 | 三班       | F      |    91 |
|    9 | 小王   |        3 | 三班       | M      |    89 |
|   10 | 小丽   |        3 | 三班       | F      |    85 |
| NULL | NULL   |     NULL | 四班       | NULL   |  NULL |
|   11 | 新生   |        5 | NULL       | M      |    88 |
+------+--------+----------+------------+--------+-------+
12 rows in set (0.00 sec)

¶十、正则搜索查询

可以使用正则的所有语法！

参考我之前的Java教程的正则表达式！，以及JavaScript教程的正则表达式！，以及加强版的正则表达式!

这里只介绍一些不同点！

mysql> SELECT * FROM students WHERE score REGEXP '8\\d';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  3 |        1 | 小军   | M      |    88 |
|  4 |        1 | 小米   | F      |    83 |
|  5 |        2 | 小白   | F      |    81 |
|  7 |        2 | 小林   | M      |    85 |
|  9 |        3 | 小王   | M      |    89 |
| 10 |        3 | 小丽   | F      |    85 |
| 11 |        5 | 新生   | M      |    88 |
| 12 |        2 | 大牛   | M      |    80 |
| 13 |        1 | 大宝   | M      |    87 |
| 14 |        2 | 二宝   | M      |    81 |
+----+----------+--------+--------+-------+
10 rows in set (0.00 sec)

¶1、语法格式

与LIKE的通配符类似！

注意：

mysql的正则表达式不区分大小写，指的是这样子的 SELECT 'jack 001' REGEXP 'Jack 001';
可以加上BINARY来区分大小写，eg：SELECT 'jack 001' REGEXP BINARY 'Jack 001';

1
2
3

SELECT * FROM students WHERE score REGEXP '8\\d';

SELECT * FROM students WHERE score LIKE '8%';

特殊字符格式：

\\- \\. \\| \\( \\{ \\[

以及 \\f \\r \\t \\v分别表示换页，换行，回车，制表符，纵向制表符。

\\\表示\

匹配字符类：

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类.

如下：

1	`SELECT 'HHH123' REGEXP '[[:digit:]]{3}';`

¶2、使用SELECT进行简单测试

匹配返回 1，不匹配返回 0

LIKE和REGEXP都可以！

mysql> SELECT 'habc123' LIKE 'ha%3';
+-----------------------+
| 'habc123' LIKE 'ha%3' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

-- 如下
mysql> SELECT 'habc123' REGEXP '^\\w*\\d{3}$';
+---------------------------------+
| 'habc123' REGEXP '^\\w*\\d{3}$' |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

¶3、REGEXP 和 LIKE

一些区别：

REGEXP可以匹配子串，只要有子串匹配就可以返回真值，当然也可以匹配原串。
LIKE只能匹配原串，必须将原串全部匹配才可。

¶十一、创建计算字段

创建计算字段主要是为了格式化输出，可以获得需要的输出结果！

其他DBMS使用 + ，||来实现拼接来处理格式化，MySql使用Concat()函数进行拼接来处理格式化。

这类普通函数与聚集函数写法规范略有不同，聚集函数全部大写如AVG()，普通函数首字母大写Concat()。

这仅仅是一个规定，约定俗成的规矩，大小写MySql是忽略的。

¶1、使用Concat()函数

mysql> SELECT Concat(name, ' ---------- ', score) name_score FROM students;
+----------------------+
| name_score           |
+----------------------+
| 小明 ---------- 98   |
| 小红 ---------- 95   |
| 小军 ---------- 88   |
| 小米 ---------- 83   |
| 小白 ---------- 81   |
| 小兵 ---------- 65   |
| 小林 ---------- 85   |
| 小新 ---------- 91   |
| 小王 ---------- 89   |
| 小丽 ---------- 85   |
| 新生 ---------- 88   |
| 大牛 ---------- 80   |
| 大宝 ---------- 87   |
| 二宝 ---------- 81   |
+----------------------+
14 rows in set (0.00 sec)

同样可以使用一些其他函数来进行控制，使用别名来替换该字段：

这里用到了RTrim()，LTrim()，Trim()，来进行配合，分别为去掉右边空格，左边空格，及两边空格！

也可以使用别名来重命名该列。

别名可以使用a AS b来表示，也可以直接空格隔开，两种写法完全一致，但一般空格隔开即可！

1
2
3

SELECT Concat(RTrim(name), ' - ', LTrim(score)) name_score FROM students;

SELECT Concat(RTrim(name), ' - ', LTrim(score)) AS name_score FROM students;

¶2、执行算术计算

可以使用+-*/来进行计算得到新的一列。

SELECT prod_id, quantity, item_price,
quantity * item_price expanded_price
FROM orderitems
WHERE order_num = 20005;

可以使用SELECT的测试功能来简单测试一下

mysql> SELECT Trim('   bsd      ');
+----------------------+
| Trim('   bsd      ') |
+----------------------+
| bsd                  |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT 3 * 5;
+-------+
| 3 * 5 |
+-------+
|    15 |
+-------+
1 row in set (0.01 sec)

¶十二、其他普通函数

¶1、文本处理函数

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX 不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

可以在你拼错的时候返回正确的匹配项！有发音相似性匹配。。神奇！

mysql> SELECT Soundex('Lie') = Soundex('Lee');
+---------------------------------+
| Soundex('Lie') = Soundex('Lee') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

SELECT cust_name，cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
+-------------+--------------+
| cust_name   | cust_contact |
| Coyote Inc. | Y Lee        |
+-------------+--------------+

¶2、日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

主要是从DATETIME数据类型中截取需要的数据！

部分示例：

mysql> SELECT Now();
+---------------------+
| Now()               |
+---------------------+
| 2020-06-29 21:45:11 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT Date('2020--06-29 00:00:00');
+------------------------------+
| Date('2020-06-29 00:00:00') |
+------------------------------+
| 2020-06-29                   |
+------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DayOfWeek('2020-06-29 00:00:00');
+-----------------------------------+
| DayOfWeek('2020-06-29 00:00:00') |
+-----------------------------------+
|                                 2 |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT Now();
+---------------------+
| Now()               |
+---------------------+
| 2020-06-29 21:56:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CurTime();
+-----------+
| CurTime() |
+-----------+
| 21:57:09  |
+-----------+
1 row in set (0.01 sec)

-- 实际用于按日期，时间等匹配日期
-- 此处的order_date为datetime类型：
SEL ECT cust_id，order_num
FROM orders
WHERE Date(order_date) = '2020-09-01';

-- 使用 Between And
SELECT cust_id，order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

¶3、数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期 — 时间处理函数的使用那么频繁。具有讽刺意味的是，在主要DBMS的函数中，数值函数是最一致最统一的函数。

¶十三、分组（GROUP）

用于统计一类数据的方法：GROUP BY 某一列 HAVING 条件

GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(eg:nums * score)（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。

除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。

如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。

GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

¶1、单列分组

$ mysql> SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
+----------+-----+
| class_id | num |
+----------+-----+
|        1 |   4 |
|        2 |   3 |
|        3 |   3 |
+----------+-----+
3 rows in set (0.00 sec)

¶2、多列分组

$ mysql> SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
+----------+--------+-----+
| class_id | gender | num |
+----------+--------+-----+
|        1 | M      |   2 |
|        1 | F      |   2 |
|        2 | F      |   1 |
|        2 | M      |   2 |
|        3 | F      |   2 |
|        3 | M      |   1 |
+----------+--------+-----+
6 rows in set (0.00 sec)

注意：SQL引擎不能把多个name的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。

原因：class_id = 1 有 4 个人，他们班级名相同，但name不同，如果有了name列，应该显示几个名字呢？？？

所以干脆直接报错！

1
2

$ mysql> SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.students.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

¶3、WITH ROLLUP

在GROUP BY后面加WITH ROLLUP来进行统计分组的总行数。

如下方的最后一行！

mysql> SELECT class_id, COUNT(*) total FROM students GROUP BY class_id WITH ROLLUP;
+----------+-------+
| class_id | total |
+----------+-------+
|        1 |     5 |
|        2 |     5 |
|        3 |     3 |
|        5 |     1 |
|     NULL |    14 |
+----------+-------+
5 rows in set (0.01 sec)

¶4、对分组的过滤

WHERE用于过滤行，HAVING用于过滤分组。

语法与WHERE一模一样。

WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。

mysql> SELECT class_id, COUNT(*) total FROM students GROUP BY class_id HAVING COUNT(*) >= 3;
+----------+-------+
| class_id | total |
+----------+-------+
|        1 |     5 |
|        2 |     5 |
|        3 |     3 |
+----------+-------+
3 rows in set (0.01 sec)

¶全部使用的顺序

SELECT子句顺序：SELECT (FROM) (WHERE) (GROUP BY) (ORDER BY) (LIMIT)

括号表示可选！

mysql> SELECT class_id, COUNT(*) total
    -> FROM students
    -> WHERE gender = 'M'
    -> GROUP BY class_id HAVING COUNT(*) >= 2
    -> ORDER BY total
    -> LIMIT 3;
+----------+-------+
| class_id | total |
+----------+-------+
|        1 |     2 |
|        2 |     4 |
+----------+-------+
2 rows in set (0.01 sec)

¶十四、子查询

就是嵌套的多级SELECT语句。

使用连接（联结）通常比子查询要更快！

¶1、利用子查询进行过滤

子查询总是从内向外处理，使用适当的缩进可以更易于阅读和调试。内层子查询的结果作为上层的基础进行查询，如下所示，将内层查询结果转变为IN的格式进行下一次查询。

不仅仅可以使用IN，其他的操作符都是可以的，LIKE，>，，，等等都可以！

嵌套太多会降低性能，不建议嵌套太多！

注意：要保证WHERE的列与子查询SELECT的列相同！

同样：使用多表连接通过WHERE也可以实现相同效果。

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 P1ace | Y Sam		|
+----------------+--------------+

-- 完全相同
SELECT cust_name，cust_contact
FROM customers，orders，orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2' ;

¶2、利用子查询处理计算字段

除了在WHERE后进行过滤，还可以在WHERE前进行计算字段的查询处理！

这里使用了完全限定列名，即使用表.列 orders.cust_id来进行限定列的范围，当然是为了防止多表存在相同列名。

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	 | 0H		  |	0	   |
| Wascals		 | IN		  |	1	   |
| Yosemite Place | AZ		  |	1	   |
+----------------+------------+--------+

¶十五、组合查询

即使用UNION来合并多条SELECT语句。

¶1、UNION 和 WHERE

UNION 几乎总是完成与多个WHERE 条件相同的工作。

UNION ALL 为 UNION 的一种形式，它完成WHERE 子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用 UNION ALL 而不是 WHERE 。

UNION 中的每个查询必须包含相同的列、表达式或聚集函数！

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

¶2、UNION

会过滤重复行！

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);

¶3、UNION ALL

不会过滤重复行！

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);

¶4、组合结果排序

只能在最后使用一条ORDER BY 进行排序，排序作用于UNION的结果！

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 ven_id, prod_price;

¶十六、全文本搜索

¶1、两种引擎

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索，但支持事务处理。
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

性能：通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

明确控制：使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词匹配和指定一个词不匹配…

智能化的结果：虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如：一个特殊词的搜索将会返回包含这些词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好地匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

所有这些限制以及更多的限制都可以用全文本搜索来解决，在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效的决定那些词分配（那些行包含它们），那些词不匹配，它们匹配的频率，等等。

¶2、启用全文本搜索

这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据字句FULLTEXT(note_text)的指示对它进行了索引，这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定FULLTEXT，或者在稍后指定。但是不要在导入数据时使用FULLTEXT，因为更新索引需要时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后在修改表，定义FULLTEXT，这要有助于更快地导入数据（而且使索引的总时间小于在导入每行每行时分别进行索引所需的总时间）。

在建表最后指出引擎：ENGINE=MyISAM

CREATE TABLE productnotes           
(
    note_id int NOT NULL AUTO_INCREMENT,
    prod_id char(10) NOT NULL,
    note_text text NOT NULL,
    note_date datetime NOT NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;

¶3、进行全文本搜索

全文本搜索用到的两个函数：

传递给Match的值必须与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们（而且次序相同）。

搜索不区分大小写，除非使用BINARY方式。

全文本搜索的一个重要部分就是对结果排序。具有较高等级（单词出现的位置靠前，数量多的优先级更高）的行先返回。LIKE同样可以实现，但是由于使用通配符搜索，效率低，速度慢，且输出无序！

Match()指定被搜索的列
Against()指定要使用的搜索表达式。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

如下：可以返回每行计算后的优先级作为RANK一列！

等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

1
2
3

SELECT note_text
       Match(note_text) Against('rabbit') AS RANK
FROM productnotes;

¶4、使用查询拓展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。

在Against内添加WITH QUERY EXPANSION表名使用查询拓展！

使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有航
其次，MySQL检查这些匹配行并选择所有有用的词
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还是用使用所有有用的词

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION) ;

¶5、布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式（boolean mode）。

即使没有FULLTEXT索引也可以使用
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义fulltext索引，也可以使用它。但是这是一种非常缓慢的操作（其性能随着数据量的增加而降低）。

在Against内添加 IN BOOLEAN MODE来表名使用布尔文本搜索！

此全文本搜索检索包含词heavy的所有行（有两行）。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同：

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

可匹配包含heavy但不包含任意以rope开始的词的所有行：

1
2
3

SELECT` `note_text
FROM` `productnotes
WHERE` `Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

全文本布尔操作符：

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，而且减少等级制
()	把词组成表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语(与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语)

部分示例如下：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
这个搜索匹配包含词rabbit和bait的行
 
 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
没有指定操作符，这个搜索匹配包含词rabbit和bait中的至少一个词的行；
 
 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('“rabbit bait”' IN BOOLEAN MODE);
这个搜索匹配短语rabbit bait，而不是匹配两个词；
 
 
 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
这个搜索匹配rabbit 和carrot，增加前者的等级，降低后者的等级；
 
 
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
这个搜索匹配safe 和combination，降低后者的等级；

小总结：

全文本搜索数据时，短词被忽略而且从索引中删除。短词定义为那些具有3个或者3个以下字符的词（如果需要，这个数目可以修改）。
MySQL带有一个内建的非用词（``stopword`）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
许多词出现的频率高，搜索它们没有用处（返回太多的结果），因此，MySQL定义了一条50%的规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
如果表中的行数小于3行，则全文本搜索将不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
忽略词中的单引号 ’ 。例如， don't 索引为 dont 。
仅在MyISAM的数据库引擎中支持全文本搜索。
不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。

数据库教程之查询数据已完成！敬请期待后续内容！

¶首先来首歌曲来放松一下吧！

¶一、准备数据

¶二、基本查询

¶1、SELECT语句

¶2、DISTINCT只返回不同值

¶3、用于计算的SELECT

¶三、条件查询

¶1、WHERE 条件

¶2、逻辑运算

¶2.1 AND

¶2.2 OR

¶2.3 NOT

¶2.4 多条件使用括号

¶3、条件表达式符号

¶4、 其他操作符

¶4.1 IN

¶4.2 LIKE

¶4.2.1 百分号（%）通配符

¶4.2.2 下划线（_）通配符

¶4.2.3 使用通配符的优缺点

¶四、投影查询

¶1、普通投影

¶2、别名投影

¶3、复杂投影

¶五、排序

¶1、升序

¶2、降序

¶3、复杂排序

¶3.1 多列排序

¶3.2 条件排序

¶六、分页查询

¶注意：

¶规则：

¶如下：

¶七、聚合（聚集）查询

¶1、COUNT()函数

¶2、其他聚集函数

¶3、使用DISTINCT

¶4、组合聚集函数

¶八、多表查询

¶1、语法：

¶2、使用WHERE

¶九、连接（联结）查询

¶1、INNER JOIN查询（内连接）

¶2、RIGHT OUTER JOIN（右外连接）

¶3、LEFT OUTER JOIN（左外连接）

¶4、FULL OUTER JOIN（全外连接）

¶十、正则搜索查询

¶1、语法格式

¶2、使用SELECT进行简单测试

¶3、REGEXP 和 LIKE

¶十一、创建计算字段

¶1、使用Concat()函数

¶2、执行算术计算

¶十二、其他普通函数

¶1、文本处理函数

¶2、日期和时间处理函数

¶3、数值处理函数

¶十三、分组（GROUP）

¶1、单列分组

¶2、多列分组

¶3、WITH ROLLUP

¶4、对分组的过滤

¶全部使用的顺序

¶十四、子查询

¶1、利用子查询进行过滤

¶2、利用子查询处理计算字段

¶十五、组合查询

¶1、UNION 和 WHERE

¶2、UNION

¶3、UNION ALL

¶4、组合结果排序

¶十六、全文本搜索

¶1、两种引擎

¶2、启用全文本搜索

¶3、进行全文本搜索

¶4、使用查询拓展

¶5、布尔文本搜索

¶4、其他操作符