MySQL高级SQL语句

使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。以下就是 MySQL 中 ORDER BY 语句的语法结构。

ELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC | DESC;

排序的字段可以根据具体需求进行选择，没有限制。排序的关键字可以使用 ASC 或者 DESC。ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。

例如，执行以下操作可查询成绩表（score），按分数进行降序排序。

mysql> select * from score order by 分数 desc;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 03           |     99 |
| 07           | 03           |     98 |
| 01           | 02           |     90 |
| 07           | 02           |     89 |
| 05           | 02           |     87 |
| 03           | 03           |     80 |
| 03           | 02           |     80 |
| 03           | 01           |     80 |
| 02           | 03           |     80 |
| 01           | 01           |     80 |
| 05           | 01           |     76 |
| 02           | 01           |     70 |
| 02           | 02           |     60 |
| 04           | 01           |     50 |
| 06           | 03           |     34 |
| 06           | 01           |     31 |
| 04           | 02           |     30 |
| 04           | 03           |     20 |
+--------------+--------------+--------+
18 rows in set (0.00 sec)

mysql>

对于排序要求，多数情况下一个字段就可以实现。其实 ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序。例如，执行以下操作可查询学生表（student），按出生日期升序排序。

mysql> select * from student order by 出生日期 asc;
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 07 | 郑竹   | 1989-07-01   | 女     |
| 01 | 赵雷   | 1990-01-01   | 男     |
| 08 | 王菊   | 1990-01-20   | 女     |
| 03 | 孙风   | 1990-05-20   | 男     |
| 04 | 李云   | 1990-08-06   | 男     |
| 02 | 钱电   | 1990-12-21   | 男     |
| 05 | 周梅   | 1991-12-01   | 女     |
| 06 | 吴兰   | 1992-03-01   | 女     |
+----+--------+--------------+--------+
8 rows in set (0.00 sec)

mysql>

查询成绩表（score），按课程编号及分数进行降序排序。

mysql> select * from score order by 课程编号 desc,分数 desc;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 03           |     99 |
| 07           | 03           |     98 |
| 02           | 03           |     80 |
| 03           | 03           |     80 |
| 06           | 03           |     34 |
| 04           | 03           |     20 |
| 01           | 02           |     90 |
| 07           | 02           |     89 |
| 05           | 02           |     87 |
| 03           | 02           |     80 |
| 02           | 02           |     60 |
| 04           | 02           |     30 |
| 03           | 01           |     80 |
| 01           | 01           |     80 |
| 05           | 01           |     76 |
| 02           | 01           |     70 |
| 04           | 01           |     50 |
| 06           | 01           |     31 |
+--------------+--------------+--------+
18 rows in set (0.00 sec)

mysql>

ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定。下面以 A 和 B 分别表示两个字段。

ORDER BY A,B desc 指 A 用升序，B 用降序；
ORDER BY A asc,B desc 指 A 用升序，B 用降序；
ORDER BY A desc,B desc 指 A 用降序，B 用降序；

2）对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。

GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。

GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），这些聚合函数的用法在后面函数小节会有更详细的讲解。GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。其语法结构如下。

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name；

GROUP BY 除了配合聚合函数一起使用外，还可以引入 WHERE 子句。首先通过 WHERE 过滤掉一部分不符合需求的查询结果，然后再对结果进行分组。如果有排序的需求，也可以引入 ORDER BY 语句。例如，执行以下操作查询成绩表（score），统计每门课程的考生人数。

mysql> select count(学生编号),课程编号 from score group by 课程编号;
+---------------------+--------------+
| count(学生编号)     | 课程编号     |
+---------------------+--------------+
|                   6 | 01           |
|                   6 | 02           |
|                   6 | 03           |
+---------------------+--------------+
3 rows in set (0.00 sec)

mysql>

查询成绩表（score），求每门课程的平均分。

mysql> select avg(分数),课程编号 from score group by 课程编号;
+-------------+--------------+
| avg(分数)   | 课程编号     |
+-------------+--------------+
|     64.5000 | 01           |
|     72.6667 | 02           |
|     68.5000 | 03           |
+-------------+--------------+
3 rows in set (0.00 sec)

mysql>

查询成绩表（score），求每门课程的最高分。

mysql> select max(分数),课程编号 from score group by 课程编号;
+-------------+--------------+
| max(分数)   | 课程编号     |
+-------------+--------------+
|          80 | 01           |
|          90 | 02           |
|          99 | 03           |
+-------------+--------------+
3 rows in set (0.00 sec)

mysql>

查询成绩表（score），求每门课程的最低分。

mysql> select min(分数),课程编号 from score group by 课程编号;
+-------------+--------------+
| min(分数)   | 课程编号     |
+-------------+--------------+
|          31 | 01           |
|          30 | 02           |
|          20 | 03           |
+-------------+--------------+
3 rows in set (0.00 sec)

mysql>

从以上结果可以看出，查询结果出来，但是是无序的。而 GROUP BY 结合 ORDER BY 即可实现分组并排序的查询。例如，查询成绩表（score），求每门课程的平均分，并根据平均分降序排序。

mysql> select avg(分数),课程编号 from score group by 课程编号 order by avg(分数)  desc;
+-------------+--------------+
| avg(分数)   | 课程编号     |
+-------------+--------------+
|     72.6667 | 02           |
|     68.5000 | 03           |
|     64.5000 | 01           |
+-------------+--------------+
3 rows in set (0.00 sec)

mysql>

排序后的结果一目了然，方便用户获取用户最多的等级信息。

3）限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。其语法结构如下。

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

如果 SELECT 查询出的结果记录比较多，用户查看不是很方便。这个时候可以返回固定的、有限的记录数量，使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。例如，执行以下操作查询学生表（student），打印学生表前三行的内容。

mysql> select * from student limit 3;
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 01 | 赵雷   | 1990-01-01   | 男     |
| 02 | 钱电   | 1990-12-21   | 男     |
| 03 | 孙风   | 1990-05-20   | 男     |
+----+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

查询学生表（student），打印学生表第三行到第五行的记录。

mysql> select * from student limit 2,3;
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 03 | 孙风   | 1990-05-20   | 男     |
| 04 | 李云   | 1990-08-06   | 男     |
| 05 | 周梅   | 1991-12-01   | 女     |
+----+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

LIMIT 子句的使用也可以结合 ORDER BY：先进行排序，然后再 LIMIT 限制固定的记录。也就是说 LIMIT 是放在最后的，将处理好的结果集按要求选出几行来。例如，将查询成绩表（score），求课程编号1成绩前三名的学生编号。

mysql> select * from score where 课程编号=1 order by 分数 desc limit 3;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 01           |     80 |
| 03           | 01           |     80 |
| 05           | 01           |     76 |
+--------------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

4）设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。设置别名使用 AS 语句，其语法格式如下

列的别名语法结构：

SELECT column_name AS alias_nameFROM table_name；

表的别名语法结构：

SELECT column_name(s)FROM table_name AS alias_name;

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的。例如，在统计表(student)内所有记录共有多少条时，使用 count（*），这么写不便于识别，可以将其别名设置为 number，具体操作如下所示。

mysql> select count(*) as number from student;
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) number from student;  //省略as是一样的结果
+--------+
| number |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名。例如，执行以下操作即可将 student 表的别名设置成 s。

mysql> select s.id,s.姓名,s.出生日期,s.性别 from student as s limit 3;
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 01 | 赵雷   | 1990-01-01   | 男     |
| 02 | 钱电   | 1990-12-21   | 男     |
| 03 | 孙风   | 1990-05-20   | 男     |
+----+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

此外，AS 还可以作为连接语句的操作符。例如，执行以下操作即可实现用一条 SQL 语句完成在创建表 tmp 的时候将 score 表内的数据写入 tmp 表。

mysql> create table tmp as select * from score;
Query OK, 18 rows affected (0.02 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 01           |     80 |
| 01           | 02           |     90 |
| 01           | 03           |     99 |
| 02           | 01           |     70 |
| 02           | 02           |     60 |
| 02           | 03           |     80 |
| 03           | 01           |     80 |
| 03           | 02           |     80 |
| 03           | 03           |     80 |
| 04           | 01           |     50 |
| 04           | 02           |     30 |
| 04           | 03           |     20 |
| 05           | 01           |     76 |
| 05           | 02           |     87 |
| 06           | 01           |     31 |
| 06           | 03           |     34 |
| 07           | 02           |     89 |
| 07           | 03           |     98 |
+--------------+--------------+--------+
18 rows in set (0.00 sec)

mysql>

注意：

在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

5）通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符
_：下划线表示单个字符

例如，查询学生表（student）中姓李的学生，具体操作如下

mysql> select * from student where 姓名 like '李%';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 04 | 李云   | 1990-08-06   | 男     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

查询学生表（student）中姓名只有两个字并且以电结尾的学生。

mysql> select * from student where 姓名 like '_电';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 02 | 钱电   | 1990-12-21   | 男     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

通配符“%”和“_”不仅可以单独使用，也可以组合使用。例如：查询学生表（student）中的1980年-1989年出生的学生。

mysql> select * from student where 出生日期 like '198_%';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 07 | 郑竹   | 1989-07-01   | 女     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

1.2、NULL值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

例如，创建一个表 test，该表包含不设置 NOT NULL 的字段，然后向表中插入不同的记录值，其中包括 NULL 值和实际有值的记录，最后通过 SELECT 查询字段中包括 NULL 和不包括 NULL 的记录值，具体操作如下所示。

mysql> create table test(id int(10) not null auto_increment,name varchar(32) character set utf8 collate utf8_bin not null,level int(10) not null,coin int(32),primary key(id)) engine=innodb default charset=utf8;  //coin字段默认未设置not null
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test(name,level) values('aa','10');  //插入的记录中不包括coin字段
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(name,level,coin) values('ab','20',100);  //插入的记录中包括coin字段
Query OK, 1 row affected (0.05 sec)

mysql> select * from test where coin is null;  //查询coin字段为空值的记录
+----+------+-------+------+
| id | name | level | coin |
+----+------+-------+------+
|  1 | aa   |    10 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from test where coin is not null;  //查询coin字段不为空的记录
+----+------+-------+------+
| id | name | level | coin |
+----+------+-------+------+
|  2 | ab   |    20 |  100 |
+----+------+-------+------+
1 row in set (0.00 sec)

mysql>

提到 NULL 值就不得不说下空值，空值就是在表的字段中存储空字符（‘’），那么 NULL 值和空值有什么区别呢？二者的区别如下：

空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的；
IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的。
空值的判断使用=’’或者<>’’来处理；
在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算。

1.3、正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如下表所示。

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个 m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1\|p2	匹配 p1 或 p2	‘bg\|fg’ 匹配 bg 或者 fg
[...]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[^...]	匹配不在括号中的任何字符	‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多 m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

了解正则表达式的匹配规则之后，就可以将这些规则技巧应用于 SQL 语句中，从而可以更准确、更快速的查出所需的记录。下面通过示例的方式详细介绍 MySQL 正则表达式的使用方法。

1）以特定字符串开头的记录

查找出学生表（student）中姓李的学生。

mysql> select * from student where 姓名 regexp '^李';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 04 | 李云   | 1990-08-06   | 男     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

2）以特定字符串结尾的记录

查找出学生表（student）中姓名以电结尾的学生。

mysql> select * from student where 姓名 regexp '电$';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 02 | 钱电   | 1990-12-21   | 男     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

3）包含指定字符串的记录

查找出学生表（student）中姓名中包含王字的学生。

mysql> select * from student where 姓名 regexp '王';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 08 | 王菊   | 1990-01-20   | 女     |
+----+--------+--------------+--------+
1 row in set (0.00 sec)

mysql>

4）匹配包含或者关系的记录

查找出学生表（student）中91年或者92年出生的学生。

mysql> select * from student where 出生日期 regexp '91|92';
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 05 | 周梅   | 1991-12-01   | 女     |
| 06 | 吴兰   | 1992-03-01   | 女     |
+----+--------+--------------+--------+
2 rows in set (0.00 sec)

mysql>

1.4、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。下面分别对这四种运算符进行说明。

1）算术运算符

MySQL 支持使用的算术运算符，如下表所所示。

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余数

例如，以 SELECT 命令来实现最基础的加减乘除运算，具体操作如下所示。

查询成绩表（score），为每个分数加10分

mysql> select 学生编号,课程编号,分数+10 from score;
+--------------+--------------+-----------+
| 学生编号     | 课程编号     | 分数+10   |
+--------------+--------------+-----------+
| 01           | 01           |        90 |
| 01           | 02           |       100 |
| 01           | 03           |       109 |
| 02           | 01           |        80 |
| 02           | 02           |        70 |
| 02           | 03           |        90 |
| 03           | 01           |        90 |
| 03           | 02           |        90 |
| 03           | 03           |        90 |
| 04           | 01           |        60 |
| 04           | 02           |        40 |
| 04           | 03           |        30 |
| 05           | 01           |        86 |
| 05           | 02           |        97 |
+--------------+--------------+-----------+
20 rows in set (0.00 sec)

mysql>

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。

在 MySQL 的字段值内还有一种情况：某些字符串类型的字段存储的数字型字符串，这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字，在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串，无法转换为数字时，将被转换为 0。这些细节在进行算术运算时需多加注意。

2）比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符如下表所示。

运算符	描述	运算符	描述
=	等于	IS NOT NULL	判断一个值是否不为NULL
>	大于	BETWEEN AND	两者之间
<	小于	IN	在集合中
>=	大于等于	LIKE	通配符匹配
<=	小于等于	GREATEST	两个或多个参数时返回最大值
!=或<>	不等于	LEAST	两个或多个参数时返回最小值
IS NULL	判断一个值是否为NULL	REGEXP	正则表达式

下面对几种常用的比较运算符进行一些讲解和举例。

等于运算符

等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用，具体操作如下所示。

查询成绩表（score），课程编号为1，分数等于80分的记录。

mysql> select * from score where 课程编号=1 and 分数=80;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 01           |     80 |
| 03           | 01           |     80 |
+--------------+--------------+--------+
2 rows in set (0.00 sec)

mysql>

不等于运算符

不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。例如，关于数字、字符串和表达式的不等于运算符的使用，具体操作如下所示。

查询成绩表（score），分数不等于80分的记录。

mysql> select * from score where 分数!=80;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 02           |     90 |
| 01           | 03           |     99 |
| 02           | 01           |     70 |
| 02           | 02           |     60 |
| 04           | 01           |     50 |
| 04           | 02           |     30 |
| 04           | 03           |     20 |
| 05           | 01           |     76 |
| 05           | 02           |     87 |
+--------------+--------------+--------+
13 rows in set (0.00 sec)

mysql>

大于、大于等于、小于、小于等于运算符

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。

小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。

大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。

小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。

例如，关于大于、大于等于、小于、小于等于运算符的使用，具体操作如下所示。

查询成绩表（score），课程编号为1，分数大于等于70分的记录。

mysql> select * from score where 课程编号=1 and 分数>=70;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 01           | 01           |     80 |
| 02           | 01           |     70 |
| 03           | 01           |     80 |
| 05           | 01           |     76 |
+--------------+--------------+--------+
4 rows in set (0.00 sec)

mysql>

IS NULL、IS NOT NULL

IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回0。

IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回0。

例如，关于数字、字符和 NULL 值的运用，具体操作如下所示

查询成绩表（score），课程编号为1，分数为null的记录。

mysql> select * from score where 课程编号=1 and 分数 is null;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 08           | 01           |   NULL |
| 09           | 01           |   NULL |
+--------------+--------------+--------+
2 rows in set (0.00 sec)

mysql>

IS NULL 和 IS NOT NULL 一个判断为空，另一个判断不为空，只是有无 NOT 这个关键字的区别，同时返回值不同。

BETWEEN AND

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，具体操作如下所示。

查询成绩表（score），课程编号为1，分数在60-70之间的记录。

mysql> select * from score where 课程编号=1 and 分数 between 60 and 70;
+--------------+--------------+--------+
| 学生编号     | 课程编号     | 分数   |
+--------------+--------------+--------+
| 02           | 01           |     70 |
+--------------+--------------+--------+
1 row in set (0.00 sec)

mysql>

LEAST、GREATEST

LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。

GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL，则返回结果就为 NULL。

例如，若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现，具体操作如下所示。

查询出最低工资

mysql> select 姓名,least(一月份工资,二月份工资,三月份工资) as 最低工资 from salary;
+--------+--------------+
| 姓名   | 最低工资     |
+--------+--------------+
| zs     |         4800 |
+--------+--------------+
1 row in set (0.00 sec)

mysql>

从以上结果可以看出，LEAST 比较的参数为数字时，返回的是其中最小的值；当比较的参数为字符串时，返回字母表中顺序最靠前的字符。GREATEST 比较的参数为数字时，返回的是其中最大的值；当比较的参数为字符串时，返回字母表中顺序最靠后的字符。

IN、NOT IN

IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0

NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。

例如，查询成绩表（student），查出id为1、2、3的学生信息，具体操作如下所示。

mysql> select * from student where id in(1,2,3);
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 01 | 赵雷   | 1990-01-01   | 男     |
| 02 | 钱电   | 1990-12-21   | 男     |
| 03 | 孙风   | 1990-05-20   | 男     |
+----+--------+--------------+--------+
3 rows in set (0.00 sec)

mysql>

3）逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种，具体如下表所示。

运算符	描述
NOT 或！	逻辑非
AND 或 &&	逻辑与
OR 或 \|\|	逻辑或
XOR	逻辑异或

逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。

逻辑与

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。

逻辑或

逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0。逻辑或通常使用 OR 或者||来表示。

逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。

4）位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符，具体如下表所示。

运算符	描述
&	按位与
\|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

例如，对数字进行按位与、或和取反运算，具体操作如下所示。

mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~1;
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
|      10 |      15 |       5 |     4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)

mysql>

10 转换为二进制数是 1010, 15 转换为二进制数是 1111。

按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。

按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0，所以 10 | 15 的结果为 15。

按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15 的结果为 5。

按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4。

例如，对数字进行左移或右移的运算，具体操作如下所示。

mysql> select 1<<2,2<<2,10>>2,15>>2;
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
|    4 |    8 |     2 |     3 |
+------+------+-------+-------+
1 row in set (0.00 sec)

mysql>

左移或右移运算符，都是将数转换为二进制后，然后在左移或右移指定的位数，超出的位数将被移除并丢弃，空出来的位置用 0 补齐。例如，“2>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11，左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3 。

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级。常用的运算符优先级情况如下表所示。“!”的优先级最高，而“:=”的优先级最低。

优先级	运算符	优先级	运算符
1	！	8	\|
2	~	9	=,<=>,>=,>,<=,<,<>!=,IS,LIKE,REGEXP,IN
3	^	10	BETWEEN,CASE,WHEN,THEN,ELSE
4	* , /(DIV), %(MOD)	11	NOT
5	+,-	12	&&,AND
6	>>,<<	13	\|\|,OR,XOR
7	&	14	:=

1.5、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。

为了便于理解，这里使用两个表 a_player 和 b_player 来进行演示。首先需要创建这两个表，然后插入一些初始数据。

mysql> create table a_player(a_id int(11) default null,a_name varchar(32) default null,a_level int(11) default null) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> create table b_player(b_id int(11) default null,b_name varchar(32) default null,b_level int(11) default null) engine=innodb defaault charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a_player(a_id,a_name,a_level) values(1,'aaaa',10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into a_player(a_id,a_name,a_level) values(2,'bbbb',20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a_player(a_id,a_name,a_level) values(3,'cccc',30);
Query OK, 1 row affected (0.01 sec)

mysql> insert into a_player(a_id,a_name,a_level) values(4,'dddd',40);
Query OK, 1 row affected (0.02 sec)

mysql> insert into b_player(b_id,b_name,b_level) values(2,'bbbb',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into b_player(b_id,b_name,b_level) values(3,'cccc',30);
Query OK, 1 row affected (0.01 sec)

mysql> insert into b_player(b_id,b_name,b_level) values(5,'eeee',50);
Query OK, 1 row affected (0.01 sec)

mysql> insert into b_player(b_id,b_name,b_level) values(6,'ffff',60);
Query OK, 1 row affected (0.00 sec)

mysql>

1）内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件。内连接的语法格式如下。

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name；

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。例如，在刚才创建的 a_player 和 b_player 表中使用内连接查询出通过判断 a_id 和 b_id 相等，包含在两个表内的部分，具体操作如下所示。

mysql> select * from a_player as a inner join b_player as b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
+------+--------+---------+------+--------+---------+
2 rows in set (0.00 sec)

mysql>

上述操作是将 a_player 表的每一行与 b_player 表的每一行进行比较，以检查它们是否都满足条件。当满足条件时，INNER JOIN 将返回由 a_player 表和 b_player 表中的列组成的新行。如果没有匹配项，查询将返回一个空的结果集。INNER JOIN 结果集中的行必须出现在两个表 a_player 和 b_player 中，是这两个表的交叉部分，具体的 INNER JOIN 工作原理如下图所示。

如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。如果没有匹配项，则不会有输出结果。

2）左连接

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，有左连接和右连接之分。

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。例如，从 a_player 和 b_player 表中，查询出 a_player 表中所有内容，并且查询出通过 a_id 和 b_id 相等判断出的 b_player 中的部分，具体操作如下。

mysql> select * from a_player as a left join b_player as b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

mysql>

从以上左连接查询结果可以看出，除了匹配的行，a_id 和 b_id 是 2 和 3 那两行，还包括了左表中有但右表中没有的行，如果有表没有对应值，则使用 NULL 代替。也就是说在左连接查询中，使用 NULL 值表示右表中没有找到与左表中匹配的记录。左连接的查询原理如下图所示。

从原理图可以看出，左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，也就是图中交叉的部分。右表记录不足的地方均为 NULL

3）右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。例如，从 a_player 和 b_player 表中，查询出在 b_player 表内的所有记录，并且通过判断 a_id 和 b_id 相等，在 a_player 表内的部分，具体的实现操作如下所示。

mysql> select * from a_player as a right join b_player as b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

mysql>

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以 NULL 补足。右连接的实现原理如下图所示。

二、数据库函数

MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。

2.1、数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。常用的数学函数如下表所示。

数学函数	描述
abs(x)	返回 x 的绝对值
rand()	返回 0 到 1 的随机数
mod(x,y)	返回 x 除以 y 以后的余数
power(x,y)	返回 x 的 y 次方
round(x)	返回离 x 最近的整数
round(x,y)	保留 x 的 y 位小数四舍五入后的值
sqrt(x)	返回 x 的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2...)	返回集合中最大的值
least(x1,x2...)	返回集合中最小的值

例如，MySQL 数学函数的使用方法，具体操作如下所示。

mysql> select abs(-1),rand(),mod(5,3),power(2,3),round(1.89);
+---------+-----------------------+----------+------------+-------------+
| abs(-1) | rand()                | mod(5,3) | power(2,3) | round(1.89) |
+---------+-----------------------+----------+------------+-------------+
|       1 | 0.0012408141058318448 |        2 |          8 |           2 |
+---------+-----------------------+----------+------------+-------------+
1 row in set (0.00 sec)

mysql> select round(1.8937,3),truncate(1.235,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,2.1);
+-----------------+-------------------+-----------+------------+-----------------------+
| round(1.8937,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) |
+-----------------+-------------------+-----------+------------+-----------------------+
|           1.894 |              1.23 |         6 |          2 |                  1.89 |
+-----------------+-------------------+-----------+------------+-----------------------+
1 row in set (0.00 sec)

mysql>

2.2、聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。常见的聚合函数如下表所示。

聚合函数	描述
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最小值
sum(x)	返回指定列的所有值之和

例如，MySQL 聚合函数的使用方法，具体操作如下所示。

mysql> select sum(a_level) as sum_level from a_player;
+-----------+
| sum_level |
+-----------+
|       100 |
+-----------+
1 row in set (0.00 sec)

mysql> select max(a_level) as max_level from a_player;
+-----------+
| max_level |
+-----------+
|        40 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(a_level) as min_level from a_player;
+-----------+
| min_level |
+-----------+
|        10 |
+-----------+
1 row in set (0.00 sec)

mysql>

聚合函数中最常用到的是 count()函数，用于统计表中的总记录。

2.3、字符串函数

常用函数不仅包括数学函数和聚合函数，还包含字符串函数，MySQL 为字符串的相关操作设计了丰富的字符串函数。常用的字符串函数如下表所示。

字符串函数	描述
length(x)	返回字符串 x 的长度
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
strcmp(x,y)	比较 x 和 y，返回的值可以为 -1，0，1
substring(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
reverse(x)	将字符串 x 反转

例如，MySQL 字符串函数的使用方法，具体操作如下所示。

mysql> select length('bdqn'),trim('yellow'),concat('bd','qn'),upper('abc'),right('hello',3);
+----------------+----------------+-------------------+--------------+------------------+
| length('bdqn') | trim('yellow') | concat('bd','qn') | upper('abc') | right('hello',3) |
+----------------+----------------+-------------------+--------------+------------------+
|              4 | yellow         | bdqn              | ABC          | llo              |
+----------------+----------------+-------------------+--------------+------------------+
1 row in set (0.00 sec)

mysql> select repeat('kgc',2),replace('hello','ll','kgc'),strcmp(4,5),substring('bjbdqn',4,2),reverse('hello');
+-----------------+-----------------------------+-------------+-------------------------+------------------+
| repeat('kgc',2) | replace('hello','ll','kgc') | strcmp(4,5) | substring('bjbdqn',4,2) | reverse('hello') |
+-----------------+-----------------------------+-------------+-------------------------+------------------+
| kgckgc          | hekgco                      |          -1 | dq                      | olleh            |
+-----------------+-----------------------------+-------------+-------------------------+------------------+
1 row in set (0.00 sec)

mysql>

2.4、日期时间函数

MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。一些常用的日期时间函数如下表所示。

时间日期函数	描述
curdate()	返回当前时间的年月日
curtime()	返回当前时间的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期 x 中的月份值
week(x)	返回日期 x 是年度第几个星期
hour(x)	返回 x 中的小时值
minute(x)	返回 x 中的分钟值
second(x)	返回 x 中的秒钟值
dayofweek(x)	返回 x 是星期几，1 星期日，2 星期一
dayofmonth(x)	计算日期 x 是本月的第几天
dayofyear(x)	计算日期 x 是本年的第几天

例如，MySQL 日期时间函数的使用方法，具体操作如下所示。

mysql> select curdate(),curtime(),now(),month('2024-03-10'),week('2024-03-10'),hour('20:15:53');
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| curdate()  | curtime() | now()               | month('2024-03-10') | week('2024-03-10') | hour('20:15:53') |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| 2024-03-10 | 20:18:43  | 2024-03-10 20:18:43 |                   3 |                 10 |               20 |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
1 row in set (0.00 sec)

mysql> select minute('20:15:53'),second('20:15:53'),dayofweek('2024-03-10'),dayofmonth('2024-03-10'),dayofyear('2024-03-10');
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
| minute('20:15:53') | second('20:15:53') | dayofweek('2024-03-10') | dayofmonth('2024-03-10') | dayofyear('2024-03-10') |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
|                 15 |                 53 |                       1 |                       10 |                      70 |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

因为 MySQL 函数的数量比较多，存在很多使用频率不是很高的函数，所以本章仅列举了一些具有代表性、比较常用的函数。在实际的工作中，需要什么类型和功能的函数可以通过手册去查找，了解实际功能后再使用。

三、存储过程

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的 SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程，轻松而高效的去完成这个需求。

3.1、存储过程简介

MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。

存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、在不同的应用程序或平台上执行相同的函数等等。如果了解一些编程语言，可能会发现存储过程更像是面向对象方法的模拟。

以下存储过程的优点：

存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载。
存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL 语句进行修改，不影响调用它的客户端。
存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限。

3.2、创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程，其语法格式如下所示。

CREATE PROCEDURE ( [过程参数[,…] ] ) <过程体>

[过程参数[,…] ] 格式

[ IN | OUT | INOUT ] <参数名><参数类型>

存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会发生错误。存储过程可以添加参数，具有自己的参数列表。参数包括参数名和其对应的类型。存在多个参数时，参数列表之间用逗号进行分隔。创建存储过程的时候可以不使用参数，但是括号要存在，也可以有一个或多个参数。

MySQL 的参数分为：输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

此外，存储过程的主体部分，被称为过程体，包含了在调用时必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

在存储过程的创建过程中，会用到 DELIMITER 命令。因为在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后面都是分号结尾，那么 MySQL 服务器在处理时遇到第一条 SQL 语句就会结束整个过程，不再去处理后面的 SQL 语句。为了解决这个问题，在创建存储过程时，使用 DELIMITER 命令，如下所示。

mysql> DELIMITER $$
//省略存储过程其他步骤
mysql> DELIMITER ; //分号前有空格

要创建存储过程，必须要具有 CREATE ROUTINE 权限

使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程。如果要查看某个存储过程的具体信息，则可以使用SHOW CREATE PROCEDURE <存储过程名称>。

例如，通过存储过程查询 player 表的三条数据，存储过程是不带参数的，具体操作如下所示。

mysql> delimiter $$
mysql> create procedure a_playerrole()
    -> begin
    -> select a_id,a_name,a_level from a_player limit 2;
    -> end $$
Query OK, 0 rows affected (0.02 sec)

mysql> call a_playerrole() $$;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    1 | aaaa   |      10 |
|    2 | bbbb   |      20 |
+------+--------+---------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

通过存储过程查询 player 表中某一条记录，存储过程是带参数的，具体操作如下所示。

mysql> create procedure getrole(in inname varchar(16)) begin select id,姓名,出生日期,性别 from student where 姓名=inname; end$$
Query OK, 0 rows affected (0.01 sec)

mysql> call getrole('孙风') $$
+----+--------+--------------+--------+
| id | 姓名   | 出生日期     | 性别   |
+----+--------+--------------+--------+
| 03 | 孙风   | 1990-05-20   | 男     |
+----+--------+--------------+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

3.3、修改存储过程

存储过程在创建之后，随着开发业务的不断推进，业务需求难免有所调整，相应的存储过程也会发生变动，这个时候就需要修改存储过程。存储过程的修改分为特征的修改和业务内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现，其语法结构如下所示。

ALTER PROCEDURE [ <特征> … ]

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

3.4、删除存储过程

存储过程创建之时是存储到 MySQL 数据库中的，当程序不在调用这个存储过程时，也就意味这个存储过程被废弃了，废弃的存储过程需要从数据库中将其删除。使用 DROP PROCEDURE 语句即可删除存储过程，其语法格式具体如下。

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

从以上语法结构可以看出，在删除时存储过程的名字是放到最后的，前面可以添加 IF EXISTS 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误。删除存储过程的具体操作如下所示。

mysql> drop procedure a_playerrole $$
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure getrole $$
Query OK, 0 rows affected (0.00 sec)

mysql> call getrole $$
ERROR 1305 (42000): PROCEDURE info.getrole does not exist
mysql>

需要注意的是：存储过程名称后面没有参数列表，也没有括号。在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行。