Mysql数据库基础知识总结，结构分明，内容详细

一，Select的使用

1. 基本的Select语句

SELECT…

SELECT 1; SELECT 9/2;

SELECT … FROM

SELECT 标识选择哪些列 FROM 标识从哪个表中选择

SELECT * FROM departments;

选择全部列：

SELECT department_id, location_id FROM departments

选择特定的列：

SELECT last_name AS name, commission_pct comm FROM employees;

列的别名:紧跟列名，使用关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。关键词AS也可省略。

SELECT DISTINCT department_id FROM employees;

去除重复行

空值参与运算:

所有运算符或列值遇到null值，运算的结果都为null

着重号:

表中的字段、表名等尽量不要和保留字、数据库系统或常用方法冲突。如果一样，则在SQL语句中使用一对``（着重号）引起来。

DESC employees;

显示表结构

2.排序与分页

排序规则

使用 ORDER BY 子句排序 ASC（ascend）: 升序 DESC（descend）:降序 ORDER BY 子句在SELECT语句的结尾。

单列排序

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;

多列排序

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

分页

LIMIT [位置偏移量,] 行数 分页显式公式：（当前页数-1）*每页条数，每页条数 注意：LIMIT 子句必须放在整个SELECT语句的最后！

3.多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联

在 WHERE子句中写入连接条件

#案例：查询员工的姓名及其部门名称

SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;

4.函数

a.函数的理解

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来， 需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了 内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的.

b.单行函数

操作数据对象 接受参数返回一个结果 只对一行进行变换 每行返回一个结果 可以嵌套 参数可以是一列或一个值

数值函数

基本函数

举例

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32), FLOOR(-43.23),MOD(12,5) FROM DUAL;

三角函数

举例：

ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个 点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计 算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而 ATAN2(M,N)函数则仍然可以计算。 ATAN2(M,N)函数的使用示例如下

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) )FROM DUAL;

进制之间的转换

字符串函数

日期和时间函数

获取日期、时间

日期与时间戳的转换

获取月份、星期、星期数、天数等函数

获取月份、星期、星期数、天数等函数

日期的格式化与解析

c.聚合函数

定义

聚合函数作用于一组数据，并对一组数据返回一个值

聚合函数类型

AVG() SUM() MAX() MIN() COUNT()

基本使用

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

使用多个列分组

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

注意： 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

HAVING

基本使用

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

WHERE和HAVING的对比 区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。 这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为， 在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成 的。另外，WHERE排除的记录不再包括在分组中。 区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要 先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用 的资源就比较多，执行效率也较低。

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组 统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发 挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很 大的差别。

5.子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。 SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者 需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集 合）进行比较。

a.子查询的分类

单行子查询

单行比较操作符

代码示例

多行子查询

也称为集合比较子查询 内查询返回多行 使用多行比较操作符

多行比较操作符

代码示例

b.相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

代码示例

6.SELECT的执行过程

SELECT 查询时的两个顺序：

1.关键字的顺序是不能颠倒的：

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 WHERE height > 1.80 # 顺序 2 GROUP BY player.team_id # 顺序 3 HAVING num > 2 # 顺序 4 ORDER BY num DESC # 顺序 6 LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步 骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL 的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤： 1.首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1； 2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2； 3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟 表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。 当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2 。 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到 虚拟表 vt6 。 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7 。 当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。 同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的 关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

二，DDL、DML、DCL

一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。 从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数 据表的 行与列 。

1.数据库的创建与管理

a.创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。 注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删 旧库完成.

b.使用数据库

查看当前所有的数据库

SHOW DATABASES;

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名; 或者： SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

c.修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

2.表的创建与管理

必须具备： CREATE TABLE权限，存储空间 必须指定： 表名，列名(或字段名)，数据类型，长度 可选指定： 约束条件，默认值

a.表的创建

创建方式1

创建方式2

查看表结构

SHOW CREATE TABLE 表名\G

b.表的修改

修改表指的是修改数据库中已经存在的数据表的结构。 使用 ALTER TABLE 语句可以实现： 向已有的表中添加列 修改现有表中的列 删除现有表中的列 重命名现有表中的列

追加一个列

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

修改一个列

可以修改列的数据类型，长度、默认值和位置 修改字段数据类型、长度、默认值、位置的语法格式如下： ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;

重命名一个列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

删除一个列

ALTER TABLE 表名 DROP 【COLUMN】字段名

c.表的删除与清空

删除

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

清空

TRUNCATE TABLE detail_dept;

3.数据的增删改

a.增

VALUES的方式添加 INSERT INTO 表名 VALUES (value1,value2,…);

同时插入多条记录

INSERT INTO 表名 VALUES (列名值1，列值2，…), (列名值1，列值2，…), …… (列名值1，列值2，…);

中间用，隔开

b.改

使用 WHERE 子句指定需要更新的数据。

UPDATE 表名 SET department_id = 70 WHERE employee_id = 113;

c.删

使用 WHERE 子句删除指定的记录。

DELETE FROM departments WHERE department_name = ‘Finance’;

4.MySQL数据类型

任何字段如果为非负数，必须是 UNSIGNED 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得 到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并 分开存储。 【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。 【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大 于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

5.约束

a.为什么需要约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中 存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑： 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女” 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门 经理的工资不得高于本部门职工的平均工资的5倍。

b. 什么是约束

约束是表级的强制规定。 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定 约束。 根据约束数据列的限制，约束可分为： 单列约束：每个约束只约束一列 多列约束：每个约束可约束多列数据 根据约束的作用范围，约束可分为： 列级约束：只能作用在一个列上，跟在列的定义后面 表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束起的作用，约束可分为： NOT NULL 非空约束，规定某个字段不能为空 UNIQUE 唯一约束，规定某个字段在整个表中是唯一的 PRIMARY KEY 主键(非空且唯一)约束 FOREIGN KEY 外键约束 CHECK 检查约束 DEFAULT 默认值约束

c.如何指定自增约束

特点和要求 （1）一个表最多只能有一个自增长列 （2）当需要产生唯一标识符或顺序值时，可设置自增长 （3）自增长列约束的列必须是键列（主键列，唯一键列） （4）自增约束的列的数据类型必须是整数类型 （5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值。

建表时

建表后

如何删除自增约束

三，其它数据库对象

1.视图

a. 为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查 询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的 价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他 人的查询视图中则不提供这个字段。 刚才讲的只是视图的一个使用场景，实际上视图还有很多作用。最后，我们总结视图的优点。

b. 视图的理解

视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。 向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的SELECT语句 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化；反之亦然 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

c.创建视图

CREATEVIEW视图名称 AS查询语句

CREATEVIEWempvu80 AS SELECTemployee_id,last_name,salary FROM employees WHERE department_id=80;

创建多表联合视图

CREATEVIEWempview AS SELECTemployee_idemp_id,last_nameNAME,department_name FROMemployeese,departmentsd WHEREe.department_id=d.department_id;

利用视图对数据进行格式化

我们经常需要输出某个格式的内容，比如我们想输出员工姓名和对应的部门名，对应格式为emp_name(department_name)，就可以使用视图来完成数据格式化的操作：

CREATEVIEWemp_depart AS SELECTCONCAT(last_name,‘(’,department_name,‘)’)ASemp_dept FROMemployeeseJOINdepartmentsd WHEREe.department_id=d.department_id

d.查看视图

语法1：查看数据库的表对象、视图对象

SHOWTABLES;

语法2：查看视图的结构

DESC/DESCRIBE视图名称;

语法3：查看视图的属性信息

#查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）SHOWTABLESTATUSLIKE’视图名称’\G

语法4：查看视图的详细定义信息

SHOWCREATEVIEW视图名称;

d.修改、删除视图

修改视图

方式1：使用CREATEORREPLACEVIEW子句修改视图

CREATEORREPLACEVIEWempvu80 (id_number,name,sal,department_id) AS SELECTemployee_id,first_name||‘’||last_name,salary,department_id FROMemployees WHEREdepartment_id=80;

方式2：ALTERVIEW 修改视图的语法是：

ALTERVIEW视图名称AS 查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。删除视图的语法是： DROPVIEWIFEXISTS视图名称;

2.存储过程与函数

a. 存储过程理解

存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句 的封装. 执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用 存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。 好处： 1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力 2、减少操作过程中的失误，提高效率 3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器） 4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性 和视图、函数的对比： 它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是 虚拟表 ， 通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以 直接操作底层数据表 ，相比于面向集 合的操作方式，能够实现一些更复杂的数据处理。 一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于 函数，存储过程是 没有返回值 的。

b. 存储过程分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下： 1、没有参数（无参数无返回） 2、仅仅带 IN 类型（有参数无返回） 3、仅仅带 OUT 类型（无参数有返回） 4、既带 IN 又带 OUT（有参数有返回） 5、带 INOUT（有参数有返回） 注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

c.创建存储过程

语法 CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,…) [characteristics …] BEGIN 存储过程体 END 说明： 1、参数前面的符号的意思 IN ：当前参数为输入参数，也就是表示入参； 存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。 OUT ：当前参数为输出参数，也就是表示出参； 执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。 INOUT ：当前参数既可以为输入参数，也可以为输出参数。 2、形参类型可以是 MySQL数据库中的任意类型。 3、 characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。 [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定 的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定 的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使 用SQL语句的限制。 CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句； NO SQL表示当前存储过程的子程序中不包含任何SQL语句； READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句； MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。 默认情况下，系统会指定为CONTAINS SQL。 SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执 行当前存储过程。 DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程； INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END 编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。 1 BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。 2.DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进 行变量的声明。 3.SET：赋值语句，用于对变量进行赋值。 4.SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

d.创建举例

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER CREATE PROCEDURE select_all_data() BEGIN SELECT * FROM emps; END DELIMITER ;

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

CREATE PROCEDURE avg_employee_salary () BEGIN SELECT AVG(salary) AS avg_salary FROM emps; END // DELIMITER ;

e.调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行 其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

在 MySQL 中，存储过程不像普通的编程语言（比如 VC++、Java 等）那样有专门的集成开发环境。因 此，你可以通过 SELECT 语句，把程序执行的中间结果查询出来，来调试一个 SQL 语句的正确性。调试 成功之后，把 SELECT 语句后移到下一个 SQL 语句之后，再调试下一个 SQL 语句。这样 逐步推进 ，就可 以完成对存储过程中所有操作的调试了。当然，你也可以把存储过程中的 SQL 语句复制出来，逐段单独 调试

f… 存储过程和函数的查看、修改、删除

查看

使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名 SHOW CREATE FUNCTION test_db.CountProc \G

使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’] 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期

修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic …] 其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT ‘string’ CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。 NO SQL ，表示子程序中不包含SQL语句。 READS SQL DATA ，表示子程序中包含读数据的语句。 MODIFIES SQL DATA ，表示子程序中包含写数据的语句。 SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。 DEFINER ，表示只有定义者自己才能够执行。 INVOKER ，表示调用者可以执行。 COMMENT ‘string’ ，表示注释信息。

删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名 IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。 举例： DROP PROCEDURE CountProc; DROP FUNCTION CountProc;

3.触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分 别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时 在库存表中添加一条库存记录。 这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用 事务 包裹起来，确保这两个操 作成为一个 原子操作 ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手 动维护，这样就很 容易忘记其中的一步 ，导致数据缺失。 这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数 据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

a.触发器的创建

创建触发器语法

CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 触发器执行的语句块;

说明：

表名 ：表示触发器监控的对象。 BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。 INSERT|UPDATE|DELETE ：表示触发的事件。 INSERT 表示插入记录时触发； UPDATE 表示更新记录时触发； DELETE 表示删除记录时触发。

b. 代码举例

1、创建数据表：

CREATE TABLE test_trigger ( id INT PRIMARY KEY AUTO_INCREMENT, t_note VARCHAR(30) );

CREATE TABLE test_trigger_log ( id INT PRIMARY KEY AUTO_INCREMENT, t_log VARCHAR(30) );

2、创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。

DELIMITER // CREATE TRIGGER before_insert BEFORE INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log (t_log) VALUES(‘before_insert’); END // DELIMITER ;

3、向test_trigger数据表中插入数据

INSERT INTO test_trigger (t_note) VALUES (‘测试 BEFORE INSERT 触发器’);

4、查看test_trigger_log数据表中的数据

mysql> SELECT * FROM test_trigger_log; ±—±--------------+ | id | t_log | ±—±--------------+ | 1 | before_insert | ±—±--------------+ 1 row in set (0.00 sec)

c.查看、删除触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;

删除触发器

触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下 DROP TRIGGER IF EXISTS 触发器名称;

d.触发器的优缺点

优点

1、触发器可以确保数据的完整性。

假设我们用 进货单头表 （demo.importhead）来保存进货单的总体信息，包括进货单编号、供货商编 号、仓库编号、总计进货数量、总计进货金额和验收日期。

用 进货单明细表 （demo.importdetails）来保存进货商品的明细，包括进货单编号、商品编号、进货数 量、进货价格和进货金额。

额就不等于进货单明细表中数量合计和金额合计了，这就是数据不一致。 为了解决这个问题，我们就可以使用触发器，规定每当进货单明细表有数据插入、修改和删除的操作 时，自动触发 2 步操作： 1）重新计算进货单明细表中的数量合计和金额合计； 2）用第一步中计算出来的值更新进货单头表中的合计数量与合计金额。 这样一来，进货单头表中的合计数量与合计金额的值，就始终与进货单明细表中计算出来的合计数量与 合计金额的值相同，数据就是一致的，不会互相矛盾。

2、触发器可以帮助我们记录操作日志。

利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很 好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。

3、触发器还可以用在操作数据前，对数据进行合法性检查

比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的 时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏…… 这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止 错误数据进入系统

缺点

1、触发器最大的一个问题就是可读性差。

因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统 维护是非常有挑战的。 比如，创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失 败。我用下面的代码演示一下： mysql> update demo.membermaster set memberdeposit=20 where memberid = 2; ERROR 1054 (42S22): Unknown column ‘aa’ in ‘field list’ 结果显示，系统提示错误，字段“aa”不存在。 这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器， 很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表 添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力。

2、相关数据的变更，可能会导致触发器出错。

特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触 发器本身的隐蔽性，影响到应用中错误原因排查的效率。

注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此 时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子 表的UPDATE和DELETE语句定义的触发器并不会被激活。 例如：基于子表员工表（t_employee）的DELETE语句定义了触发器t1，而子表的部门编号（did）字段定 义了外键约束引用了父表部门表（t_department）的主键列部门编号（did），并且该外键加了“ON DELETE SET NULL”子句，那么如果此时删除父表部门表（t_department）在子表员工表（t_employee）