【转载】mysql必会基础（四）

1.视图

1. 视图的定义

视图就是从一个或多个表中，导出来的表，是一个虚拟存在的表。视图就像一个窗口（数据展示的窗口），通过这个窗口，可以看到系统专门提供的数据（也可以查看到数据表的全部数据），使用视图就可以不用看到数据表中的所有数据，而是只想得到所需的数据。

在数据库中，只存放了视图的定义，并没有存放视图的数据，数据还是存储在原来的表里，视图的数据是依赖原来表中的数据的，所以原来的表的数据发生了改变，那么显示的视图的数据也会跟着改变,例如向数据表中插入数据，那么在查看视图的时候，会发现视图中也被插入了同样的数据。

视图在外观上和表很相似，但是它不需要实际上的物理存储，视图实际上是由预定义的查询形式的表所组成的。

视图可以包含表的全部或者部分记录，也可以由一个表或者多个表来创建，当我们创建一个视图的时候，实际上是在数据库里执行了SELECT语句，SELECT语句包含了字段名称、函数、运算符，来给用户显示数据。

在数据库中，视图的使用方式与表的使用方式一致，我们可以像操作表一样去操作视图，或者去获取数据。

一般来说，我们只是利用视图来查询数据，不会通过视图来操作数据。但是视图可以更新（即可以进行INSERT、UPDATE、SELECT等操作），但是并非所有视图都能更新，如果视图定义中有如下操作则不能更新：分组，联结，子查询，并，聚集函数，DISTINCT，计算列

1.1 基于视图的视图

基于已存在的视图，还可以再创建视图。

1.2 视图和表的区别

视图和表的主要区别，就是看是否占用物理空间。

1.3 视图的作用

（1）选取有用的信息，筛选的作用

（2）操作简单化，所见即所需，视图看到的信息，就是需要了解的信息

（3）增加数据的安全性：查询或者修改指定的数据，非指定的数据是触碰不到的。

（4）提高逻辑的独立性

1.4 视图的特点

（1）简单性（简单化）：可以展现特定的数据，而无需重复设置查询条件，简化操作。

（2）安全性：视图可以只展现数据表的一部分数据，对于我们不希望让用户看到全部数据，只希望用户看到部分数据的时候，可以选择使用视图。

（3）逻辑独立性：当真实的数据表结构发生了变化，可以通过视图来屏蔽真实表的结构变化，从而实现了视图的逻辑独立性。

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

视图可以在以下几个方面使程序与数据独立：

①如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动。

②如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

③如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序可以不动。

④如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。

2. 创建视图

CREATE VIEW 视图名称[(column_list)] AS SELECT 语句

例：

CREATE VIEW province_view AS SELECT * FROM province;

SELECT * FROM province_view;

说明：创建的视图表province_view与province表一模一样。

2.1 指定视图显示的字段：

CREATE VIEW province_view1(id,name) AS SELECT id,pro_name FROM province;

mysql> SELECT * FROM province_view1;

+-----+------+

| id | name |

+-----+------+

| 1 | 北京 |

| 2 | 上海 |

| 3 | 辽宁 |

| 4 | 天津 |

| 5 | 广东 |

| 6 | 福建 |

| 100 | 吉林 |

+-----+------+

7 rows in set (0.00 sec)

2.2 创建基于两个表的视图：

使用WHERE连接两个表：

CREATE VIEW v3(name,score) AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

and score.c_id='BY';

2.3 视图的算法

ALGORITHM=

UNDEFINED:MYSQL自动选择要使用的算法

MERGE：使用视图的语句与视图的定义是合并在一起的，视图定义的某一部分取代语句对应的部分

TEMPTABLE：临时表，视图的结果存入临时表，然后使用临时表来执行语句

WHIT [CASCADED|LOCAL] CHECK OPTION：表示更新视图的时候，要保证在视图的权限范围之内：

CASCADED 默认值，表示更新视图的时候，要满足视图和表的相关条件

LOCAL：表示更新视图的时候，要满足该视图定义的一个条件即可

说明：使用WHIT [CASCADED|LOCAL] CHECK OPTION选项可以保证数据的安全性

3.创建完整的视图

CREATE ALGORITHM VIEW 视图名称[(column_list)] AS SELECT 语句

WITH [CASCADED|LOCAL] CHECK OPTION

语法提示命令：? CREATE VIEW

Name: 'CREATE VIEW'

Description:

Syntax:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

例子：

CREATE ALGORITHM=UNDEFINED VIEW user_view3(id,username,age) AS SELECT

id,username,age FROM users2 WITH CASCADED CHECK OPTION;

4. 查看视图

查看已创建好的视图：

4.1 查看已创建好的视图的方法：

DESC

DESCRIBE

SHOW COLUMNS FROM 视图名称

SHOW TABLE STATUS LIKE

SHOW CREATE VIEW

4.1.1 DESC

mysql> desc user_view3;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

4.1.2 DESCRIBE

mysql> DESCRIBE user_view3;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

4.1.3 SHOW COLUMNS FROM 视图名称

mysql> SHOW COLUMNS FROM user_view3;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

4.2 查看视图的基本信息（也可查看原表的信息）：

SHOW TABLE STATUS LIKE ‘视图名称’;

mysql> SHOW TABLE STATUS LIKE 'province_view'\G;

*************************** 1. row ***************************

Name: province_view

Engine: NULL

Version: NULL

Row_format: NULL

Rows: NULL

Avg_row_length: NULL

Data_length: NULL

Max_data_length: NULL

Index_length: NULL

Data_free: NULL

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: NULL

Checksum: NULL

Create_options: NULL

Comment: VIEW

1 row in set (0.00 sec)

说明：

（1）可以从Comment: VIEW看出它是一个视图，如果是数据表，Comment选项的值为空。

（2）因为视图是虚拟出的一张表，所以很多选项的值都是NULL，如果SHOW TABLE STATUS LIKE ‘table_name’; 那么这些选项将会显示出数值。

4.3 查看指定视图的创建信息（专门查看视图信息的命令）

SHOW CREATE VIEW 视图名称;

mysql> SHOW CREATE VIEW user_view3\G;

*************************** 1. row ***************************

View: user_view3

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_view3` AS select `users2`.`id` AS `id`,`users2`.`username` AS `username`,`users2`.`age` AS `age` from `users2` WITH CASCADED CHECK OPTION

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

4.4 视图数据的存储位置

mysql> SELECT * FROM information_schema.views\G

所有的视图都保存在了information_schema.views中。

4.5.修改视图：

如果视图不存在，则创建视图，如果视图存在，则修改视图：

（1）CREATE OR REPLACE VIEW 视图名称[(column_list)] AS SELECT 语句

（2）ALTER VIEW视图名称[(column_list)] AS SELECT 语句

4.5.1 CREATE OR REPLACE VIEW 视图名称[(column_list)] AS SELECT 语句

（1）例子：

CREATE OR REPLACE VIEW user_view3(id,username) AS SELECT id,username FROM users2;

（2）如果输入的视图名称不存在，这MYSQL自动创建该视图：

（3）修改视图：

CREATE OR REPLACE ALOGRITHM=TEMPTABLE VIEW user_view4(id) AS SELECT id FROM

users2;

（4）修改基于两个表的视图，两个表使用WHERE进行连接：

CREATE OR REPLACE VIEW v3 AS SELECT s_name,s_sex,score FROM student,score

WHERE student.s_id=score.s_id AND score.c_id='BY';

4.5.2 ALTER

ALTER VIEW 视图名称[(column_list)] AS SELECT 语句

ALTER VIEW user_view4(id,username,age) AS SELECT id,username,age FROM users2;

修改基于两个表的视图：

ALTER VIEW v3 AS SELECT s_name,score FROM student,score

WHERE student.s_id=score.s_id

AND score.c_id='TC';

5.更新视图

所谓更新视图，其实就是通过视图，对数据进行插入，修改和删除的操作。

5.1 修改视图的数据

注意：修改视图的数据，将直接修改数据表（即原表）的真实数据。

UPDATE v3 SET score=100 WHERE s_name='倪妮';

5.2 通过视图插入、删除数据的原理与5.1一致，均与数据表的操作语法一致

6.删除视图：

删除视图，不会影响原表的数据，但是删除视图的数据，则会影响到原表。

6.1 DROP VIEW 视图名称;

DROP VIEW 视图名称;

DROP VIEW user_view4;

6.2 DROP VIEW IF EXISTS

在删除已不存在的视图的时候，不进行任何操作：

DROP VIEW IF EXISTS视图名称;

例：

DROP VIEW IF EXISTS v1;

6.3 删除多个视图

DROP VIEW IF EXISTS v2,v3;

2.存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

为什么要使用存储过程：优点

1 通过吧处理封装在容易使用的单元中，简化复杂的操作

2 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。

3 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码了都。

4 提高性能，因为使用存储过程比使用单条SQL语句要快

5 存在一些职能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

换句话说3个主要好处简单、安全、高性能

缺点

1 一般来说，存储过程的编写要比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

2 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建，允许用户使用存储过程，但不允许创建存储过程

存储过程是非常有用的，应该尽可能的使用它们

执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL .CALL接受存储过程的名字以及需要传递给它的任意参数

CALL productpricing(@pricelow , @pricehigh , @priceaverage);

//执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

创建存储过程

CREATE PROCEDURE 存储过程名()

一个例子说明：一个返回产品平均价格的存储过程如下代码：

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products;

END;

//创建存储过程名为productpricing，如果存储过程需要接受参数，可以在（）中列举出来。即使没有参数后面仍然要跟（）。BEGIN和END语句用来限定存储过程体，过程体本身是个简单的SELECT语句

在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

Mysql命令行客户机的分隔符

默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误

解决方法是临时更改命令实用程序的语句分隔符

DELIMITER // //定义新的语句分隔符为//

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products;

END //

DELIMITER ; //改回原来的语句分隔符为 ;

除\符号外，任何字符都可以作为语句分隔符

CALL productpricing(); //使用productpricing存储过程

执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后面要有()符号

删除存储过程

DROP PROCEDURE productpricing ; //删除存储过程后面不需要跟(),只给出存储过程名

为了删除存储过程不存在时删除产生错误，可以判断仅存储过程存在时删除

DROP PROCEDURE IF EXISTS

使用参数

Productpricing只是一个简单的存储过程，他简单地显示SELECT语句的结果。

一般存储过程并不显示结果，而是把结果返回给你指定的变量

CREATE PROCEDURE productpricing(

OUT p1 DECIMAL(8,2),

OUT ph DECIMAL(8,2),

OUT pa 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(@pricelow , @pricehigh , @priceaverage);

这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

变量名所有的MySQL变量都必须以@开始

使用变量

SELECT @priceaverage ;

SELECT @pricelow , @pricehigh , @priceaverage ; //获得3给变量的值

下面是另一个例子，这次使用IN和OUT参数。ordertotal接受订单号，并返回该订单的合计

CREATE PROCEDURE ordertotal(

IN onumber INT,

OUT ototal DECIMAL(8,2)

)

BEGIN

SELECT Sum(item_price*quantity)

FROM orderitems

WHERE order_num = onumber

INTO ototal;

END;

//onumber定义为IN，因为订单号时被传入存储过程，ototal定义为OUT，因为要从存储过程中返回合计，SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计

为了调用这个新的过程，可以使用下列语句：

CALL ordertotal(2005 , @total); //这样查询其他的订单总计可直接改变订单号即可

SELECT @total;

建立智能的存储过程

上面的存储过程基本都是封装MySQL简单的SELECT语句，但存储过程的威力在它包含业务逻辑和智能处理时才显示出来

例如：你需要和以前一样的订单合计，但需要对合计增加营业税，不活只针对某些顾客（或许是你所在区的顾客）。那么需要做下面的事情：

1 获得合计（与以前一样）

2 吧营业税有条件地添加到合计

3 返回合计（带或不带税）

存储过程的完整工作如下：

-- Name: ordertotal

-- Parameters: onumber = 订单号

-- taxable = 1为有营业税 0 为没有

-- ototal = 合计

CREATE PROCEDURE ordertotal(

IN onumber INT,

IN taxable BOOLEAN,

OUT ototal DECIMAL(8,2)

-- COMMENT()中的内容将在SHOW PROCEDURE STATUS ordertotal()中显示，其备注作用

) COMMENT 'Obtain order total , optionally adding tax'

BEGIN

-- 定义total局部变量

DECLARE total DECIMAL(8,2)

DECLARE taxrate INT DEFAULT 6;

-- 获得订单的合计，并将结果存储到局部变量total中

SELECT Sum(item_price*quantity)

FROM orderitems

WHERE order_num = onumber

INTO total;

-- 判断是否需要增加营业税，如为真，这增加6%的营业税

IF taxable THEN

SELECT total+(total/100*taxrate) INTO total;

END IF;

-- 把局部变量total中才合计传给ototal中

SELECT total INTO ototal;

END;

此存储过程有很大的变动，首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样很重要。在存储体中，用DECLARE语句定义了两个局部变量。DECLARE要求制定变量名和数据类型，它也支持可选的默认值（这个例子中taxrate的默认设置为6%），SELECT 语句已经改变，因此其结果存储到total局部变量中而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total，最后用另一SELECT语句将total（增加了或没有增加的）保存到ototal中。

COMMENT关键字本列中的存储过程在CREATE PROCEDURE 语句中包含了一个COMMENT值，他不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示

IF语句这个例子中给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）

检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW PROCEDURE STATUS.限制过程状态结果，为了限制其输出，可以使用LIKE指定一个过滤模式，例如：SHOW PROCEDURE STATUS LIKE ''ordertotal;

更详细的使用方法可以参考：http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

3.游标
MySQL5添加了对游标的支持
只能用于存储过程

由前几章可知，mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行（0行或多行），使用简单的SELECT语句，没有办法得到第一行、下一行或前10行，也不存在每次行地处理所有行的简单方法（相对于成批处理他们）

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MYSQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

使用游标

使用游标涉及几个明确的步骤：

1 在能够使用游标前，必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句

2 一旦声明后，必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来

3 对于填有数据的游标，根据需要取出（检索）的各行

4 在接受游标使用时，必须关闭它如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它

创建游标

游标可用DECLARE 语句创建。 DECLARE命名游标，并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如：下面第一名为ordernumbers的游标，使用了检索所有订单的SELECT语句

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders ;

END;

存储过程处理完成后，游标就消失，因为它局限于存储过程

打开和关闭游标

CREATE PROCEDURE processorders()

BEGIN

DECLAREordernumbers CURSOR

FOR

SELECT order_num FROM orders ;

Open ordernumbers ;

Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

END;

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的要列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行，相当于PHP中的each()函数

循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders()

BEGIN

-- 声明局部变量

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLAREordernumbers CURSOR

FOR

SELECT order_num FROM orders ;

-- 当SQLSTATE为02000时设置done值为1

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

--打开游标

Open ordernumbers ;

-- 开始循环

REPEAT

-- 把当前行的值赋给声明的局部变量o中

FETCH ordernumbers INTO o;

-- 当done为真时停止循环

UNTIL done END REPEAT;

--关闭游标

Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

END;

语句中定义了CONTINUE HANDLER ,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT没有更多的行供循环时，出现这个条件。

DECLARE 语句次序用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此规则就会出错

重复和循环除这里使用REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。

为了把这些内容组织起来，这次吧取出的数据进行某种实际的处理

CREATE PROCEDURE processorders()

BEGIN

-- 声明局部变量

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE t DECIMAL(8,2)

DECLAREordernumbers CURSOR

FOR

SELECT order_num FROM orders ;

-- 当SQLSTATE为02000时设置done值为1

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- 创建一个ordertotals的表

CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))

--打开游标

Open ordernumbers ;

-- 开始循环

REPEAT

-- 把当前行的值赋给声明的局部变量o中

FETCH ordernumbers INTO o;

-- 用上文讲到的ordertotal存储过程并传入参数，返回营业税计算后的合计传给t变量

CALL ordertotal(o , 1 ,t)

-- 把订单号和合计插入到新建的ordertotals表中

INSERT INTO ordertotals(order_num, total) VALUES(o , t);

-- 当done为真时停止循环

UNTIL done END REPEAT;

--关闭游标

Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

END;

最后SELECT * FROM ordertotals就能查看结果了

4.触发器
MySQL5版本后支持触发器

只有表支持触发器，视图不支持触发器

MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：

1 每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写

2 每当订购一个产品时，都从库存数量中减少订购的数量

3 无论何时删除一行，都在某个存档中保留一个副本

这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

1 DELETE

2 INSERT

3 UPDATE

其他的MySQL语句不支持触发器

创建触发器

创建触发器需要给出4条信息

1 唯一的触发器名； //保存每个数据库中的触发器名唯一

2 触发器关联的表；

3 触发器应该响应的活动（DELETE、INSERT或UPDATE）

4 触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）

创建触发器用CREATE TRIGGER

CREATE TRIGGER newproduct AFTER INSERT ON products

FOR EACH ROW SELECT'Product added'

创建新触发器newproduct ，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added

FOR EACH ROW 针对每个行都有作用，避免了INSERT一次插入多条语句

触发器定义规则

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，则应该定义两个触发器

触发器失败如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER(之后)触发器

删除触发器

DROP TRIGGER newproduct；

触发器不能更新或覆盖，所以修改触发器只能先删除再创建

使用触发器

我们来看看每种触发器以及它们的差别

INSERT 触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

1 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行

2 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）

3 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器

DELETE 触发器

DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：

1 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；

2 OLD中的值全部是只读的，不能更新

例子演示适用OLD保存将要除的行到一个存档表中

CREATE TRIGGERdeleteorder BEFORE DELETE ON orders

FOR EACH ROW

BEGIN

INSERT INTO archive_orders(order_num , order_date , cust_id)

VALUES(OLD.order_num , OLD.order_date , OLD.cust_id);

END;

//此处的BEGIN END块是非必需的，可以没有

在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive_orders的存档表中

BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。

多语言触发器正如上面所见，触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句。

UPDATE触发器

UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：

1 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值

2 在BEFORE UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）

3 OLD中的值全都是只读的，不能更新

例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH ROW SETNEW.vend_state = Upper(NEW.vend_state)

触发器的进一步介绍

1 与其他DBMS相比，MySQL5中支持的触发器相当初级。以后可能会增强

2 创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的.如果INSERT UPDATE DELETE能执行，触发器就能执行

3 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这个处理，而且是透明地进行，与客户机应用无关

4 触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改（如果需要，甚至还有之前和之后的状态）记录到另一表非常容易

5 遗憾的是，MySQL触发器中不支持CALL语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

5.事务处理
并非所有引擎都支持事务处理，MyISAM不支持.InnoDB支持

事务处理可以用来维护数据库的完整性，它保证成批的MySQL语句操作要骂完全执行，要么完全不执行

一些操作（如：添加订单，银行转账等）如果执行到一半的时候因某种数据库故障（如超出磁盘空间、安全限制、表锁等）阻止了这个过程的完成是非常危险的。这怎么样才能解决呢？

这就要用到事务处理了。事务处理是一种机制，用来管理必须成批执行的MySQLCZ ,以保证不包含不完整的支持结果。利用事务处理，可以保证一组操作要么整体执行，要么完全不执行。发生错误后以前执行的SQL语句进行回退（撤销）已恢复数据到某个已知且安全的状态。

下面是关于事务处理需要知道的几个术语：

事务(transaction) 指一组SQL语句

回退(rollback) 指撤销指定SQL语句的过程

提交(commit) 指将末存储的SQL语句结果写入数据库表；

保留点(savepoint) 指事务处理中设置的临时占位符（place-holder）你可以对它发布回退（与回退整个事务处理不同）

控制事务处理

既然知道了什么是事务处理，下面来讨论事务处理的管理中涉及的问题

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

使用ROLLBACK

MySQL的ROLLBACK命令用来回退（撤销）MySQL语句。

SELECT * FROM ordertotals; //24章填充的表不为空

START TRANSACTION;

DELETE FROM ordertotals; //删除所有行

SELECT * FROM ordertotals; //为空

ROLLBACK; //回退

SELECT * FROM ordertotals; //不为空

显然ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）

哪些语句可以回退 INSERT 、UPDATE、 DELETE语句可以回退，SELECT不可以，也没意义。不能回退CREATE或DROP操作

使用COMMIT

一般的MySQL语句都是直接对数据库表执行和编写的。这就是所谓的隐含提交即提交或保存操作是自动执行的。

但是在事务处理块中，提交不会隐含提交。必须进行明确提交。未了进行明确提交。使用COMMIT语句。

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 2000;

DELETE FROM orders WHERE order_num = 2000;

COMMIT;

这个例子中，完全删除一个订单需要更新两个表。所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错是写出更改。如果任何一条语句出错。这DELETE不会提交（实际上，他是被自动撤销的）

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，简单的可以这么做，复杂的事务处理可能需要部分提交或回退

为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符

这些占位符称为保留点。为了创建占位符。可如下使用SAVEPOINT语句：

SAVEPOINT delete1;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退懂啊某个保留点，可如下进行：

ROLLBACK TO delete1;

保留点越多越好可以在MySQL中设置多个保留点，越多越好，越多控制回退就越灵活

释放保留点保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。 MySQL5以来，也可以用RELEASE SAVEPOINT 明确地释放保留点

更改默认提交行为

默认MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为了指示MySQL不自动提交更改。需要使用以下语句：

SET autocommit = 0;

说在后面：这是《mysql必知必会》学习笔记的第四篇，这本书比较薄，内容也比较基础，讲的也都应该是面试必须掌握的知识点，用四篇整理一下。后续将继续学习《深入浅出mysql》，此系列教程参考资料如下：
《Mysql必知必会》
http://blog.sina.com.cn/s/blog_4acbd39c0100qp80.html
http://www.cnblogs.com/4php/p/4108157.html
---------------------
作者：一对儿程序猿
来源：CSDN
原文：https://blog.csdn.net/liukanglucky/article/details/51111608
版权声明：本文为博主原创文章，转载请附上博文链接！

posted @ 2019-01-24 18:47 Delo 阅读(102) 评论(0) 收藏举报

刷新页面返回顶部

Delo

【转载】mysql必会基础（四）

公告