MySQL之21-29 重点： 视图，触发器，存储过程，游标，事务处理

tianzhig

　　21．创建和操作表
　　21.1.创建表
　　CREATE TABLE创建表，必须给出下列信息：
　　1） 新表的名字，在关键字CREATETABLE之后
　　2） 表列的名字和定义，用逗号分隔
　　CREATE TABLE customers
　　(
　　cust_id int NOT NULL AUTO_INCREMENT,
　　cust_name char(50) NOTNULL,
　　cust_address char(50) NULL,
　　cust_city char(50) NULL,
　　cust_state char(5) NULL,
　　cust_zip char(10) NULL,
　　cust_country char(50) NULL,
　　cust_contact char(50) NULL,
　　cust_email char(255) NULL,
　　primaryKEY (cust_id)
　　) ENGINE = InnoDB;
　　有以下几点需要注意：
　　1）其中主键也可以用多个列组成，如orderitems表中在主键表示如下：
　　PRIMARY KEY(order_num,order_item)
　　主键只能使用不允许NULL值的列。
　　2）每个表只允许一个AUTO_INCREMENT列。可用SELECTlast_insert_id()获取最后一个AUTO_INCREMENT值。
　　3）用DEFAULT指定默认值
　　4)引擎类型
　　InnoDB是一个可靠的事物处理引擎，它不支持全文本搜索
　　MEMORY在功能上等同于MyISAM,但由于数据存储在内存中，速度很快（特适合于临时表）
　　MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事物处理
　　引擎可以混用，但外键不能夸引擎。
　　21.2更新表
　　必须提供以下信息：
　　1）在ALTER TABLE之后给出要更改的表名
　　2）所做更改的列表
　　ALTER TABLE vendors ADD vend_phoneCHAR(20);//增加一个新列
　　ALTER TABLE vendors DROP vend_phone;//删除一个列
　　定义外键：
　　ALTER TABLE orderitems ADD CONSTRAINTfk_orderitems_orders FOREIGN KEY (order_num) REFERENCE orders(order_num);
　　21.3删除表
　　DROP TABLE customers;
　　21.4重命名表
　　RENAME TABLE backup_customers TO customers;
　　
　　22.使用视图
　　视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
　　视图不包含表中应该有的任何列或数据，它包含的是一个SQL查询。视图仅仅用来查看存储在别处数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些彪中国的数据时，视图将返回改变过的数据。每次使用视图时，都必须处理查询执行时所需要的任一检索。
　　视图不能索引，也不能有关联的触发器或默认值。
　　使用视图：
　　1）视图用CREATE VIEW来创建
　　2）使用SHOW CREATE VIEWviewname;来查看创建视图的语句
　　3）用DROP删除视图，其语法为DROPVIEW viewname;
　　4) 更新视图时，可以先用DROP再用CREATE,也可以直接用CREATEOR REPLACE VIEW。
　　22.1利用视图简化复杂的联结
　　mysql> CREATE VIEW productcustomers ASSELECT cust_name,cust_contact,prod_id FRO
　　M customers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND order
　　items.order_num = orders.order_num;
　　Query OK, 0 rows affected (0.13 sec)
　　为检索订购产品TNT2的客户，可如下执行：
　　mysql> SELECT cust_name,cust_contactFROM productcustomers WHERE prod_id = 'TNT2
　　';
　　+----------------+--------------+
　　| cust_name | cust_contact |
　　+----------------+--------------+
　　| Coyote Inc. | YLee |
　　| Yosemite Place | Y Sam |
　　+----------------+--------------+
　　2 rows in set (0.00 sec)
　　22.2用视图重新格式化检索出的数据
　　SELECT * FROM productcustomers;
　　22.2视图的更新
　　有时，视图是可更新的（即可以对它们使用INSERT、UPDATE和DELETE），对视图增加或删除行，实际上是对其基表增加或删除行。
　　但是，如果视图定义中有以下操作，则不能进行更新：
　　1） 分组（使用GROUP BY和HAVING）
　　2） 联结
　　3） 子查询
　　4） 并
　　5） 聚集函数
　　6） DISTINCT
　　7） 导出（计算）列
　　
　　23.使用存储过程
　　存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
　　存储过程有3个主要的好处：简单、安全、高性能。
　　23.1执行存储过程
　　MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。
　　CALL producpricing(@pricelow,@pricehigh,priceaverage);
　　其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。存储过程可以显示结果，也可以不显示结果。
　　23.2创建过程
　　CREATE PROCEDURE productpricing()
　　BEGIN
　　SELECTAve(prod_price) AS priceaverage
　　FROMproducts;
　　END;
　　此存储过程名为productricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本事仅是一个简单的SELECT语句。
　　在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
　　如果使用MySQL命令行使用程序，由于默认的MySQL语句结束符为；，则存储体内的；字符将不会最终成为储存过程的成分，这会导致语法错误。解决办法是使用新语句分隔符。DELIMITER //
　　CREATE PROCEDURE productpricing()
　　BEGIN
　　SELECTAvg(prod_price) AS priceaverage
　　FROMproducts;
　　END//
　　DELIMITER;
　　其中DELIMITER//告诉命令行实用程序使用//作为新的语句结束符，可以看到表示存储过程结束的END定义END//而不是END;作为语句结束。最后使用DELIMITER ；恢复原来的语句结束符。然后调用结果如下：
　　mysql> CALL productpricing();
　　+--------------+
　　| priceaverage |
　　+--------------+
　　|16.133571 |
　　+--------------+
　　1 row in set (0.30 sec)
　　
　　Query OK, 0 rows affected (0.30 sec)
　　23.2删除存储过程
　　DROP PROCEDURE productpricing;//当不存在时报错
　　DROP PROCEDURE productpricing IF EXISTS;//仅当存在时删除，不存在时不报错
　　23.3使用参数
　　一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
　　变量：内存中一个特定的位置，用来临时存储数据。所有MySQL变量必须以@开始。
　　CREATE PROCEDURE order（IN onumberINT, OUT ototal DECIMAL(8,2)）
　　BEGIN
　　SELECTSum(item_price*quantity)
　　FROMorderitems
　　WHEREorder_num = onumber
　　INTOototal;
　　END;
　　此存储过程接受两个参数，每个参数必须指定参数类型。关键字IN指出相应参数用来传递给存储过程，OUT（从存储过程传出），INOUT（对存储过程传入和传出）。存储过程的代码位于BEGIN和END语句内。
　　为调用这个存储过程，可使用如下语句：
　　CALL ordertotal(2005,@total);
　　为显示此合集，如下
　　SELECT @total;
　　为得到另一个订单的合计信息，需要再次调用存储过程
　　CALL ordertotal(2009,@total);
　　SELECT @total;
　　23.4 建立智能存储过程
　　只有在存储过程内包含业务规则和智能处理时，他们的威力彩真正显现出来。
　　CREATE PROCEDURE ordertotal(
　　INonumber INT,
　　INtaxable BOOLEAN,
　　OUTototal DECIMAL(8,2)
　　) COMMENT 'Obtain order total, optionallyadding tax'
　　BEGIN
　　--Declare variable for total
　　DECLARE total DECIMAL(8,2);
　　--DECLARE tax percentage
　　DECLARE taxrate INT DEFAULT 6;
　　
　　--Getthe order total
　　SELECT Sum(item_price*quantity)
　　FROM orderitems
　　WHERE order_num = onumber
　　INTO total;
　　
　　--Is this taxable?
　　IFtaxable THEN
　　--Yes, so add taxrate to total
　　SELECT total+(total/100*taxrate) INTO total;
　　ENDIF;
　　--And finally,save to out variable
　　SELECT total INTO ototal;
　　END；
　　这里COMMERNT关键字不是必须的，但如果给出，将在SHOW PROCEDURE STATU的结果中显示。
　　MySQL除了IF语句,还指出ELSEIF和ELSE子句（ELSEIF还须用THEN, ELSE不使用）
　　调用并显示如下结果1:如下
　　mysql> CALL ordertotal(20005,0,@total);
　　Query OK, 0 rows affected (0.05 sec)
　　
　　mysql> SELECT @total;
　　+--------+
　　| @total |
　　+--------+
　　| 149.87 |
　　+--------+
　　1 row in set (0.00 sec)
　　调用并显示如下结果2如下
　　mysql> CALL ordertotal(20005,1,@total);
　　Query OK, 0 rows affected, 1 warning (0.00sec)
　　
　　mysql> SELECT @total;
　　+--------+
　　| @total |
　　+--------+
　　| 158.86 |
　　+--------+
　　1 row in set (0.00 sec)
　　23.5检查存储过程
　　为了显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：
　　SHOW CREATE PROCEDURE ordertotal;
　　为了获得包括何时，由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS,并且可用LIKE指定一个过滤模式，例如
　　SHOW PROCEDURE STATUS LIKE ‘ordertotal’;
　　
　　24使用游标
　　有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结构集。在存储了游标之后，应用程序可以根据需要滚动或浏览或更改其中的数据。
　　24.1使用游标
　　步骤：
　　1)在使用有游标前，必须声明它。这个过程实际上没有检索数据，它只是ing医药使用的SELECT语句
　　2)一旦声明后，必须打开游标供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
　　3)对于填有数据的游标，根据需要取出各行
　　4)在结束游标使用时，必须关闭游标
　　24.2创建游标和使用游标数据
　　在一个游标被打开后，可以使用FETCH语句分别访问它的每一行，FETCH指定检索什么数据，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。
　　举例如下：
　　CREATE PROCEDURE processorders()
　　BEGIN
　　--Declarelocal variables
　　DECLAREdone BOOLEAN DEFAULT 0;
　　DECLAREo INT;
　　DECLAREt DECIMAL(8,2);
　　
　　--Declarethe cursor
　　DECLAREordernumbers CURSOR
　　FOR
　　SELECTorder_num FROM orders;
　　--Declarecontinue handler
　　DECLARECONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;
　　--Createa table to store the results
　　CREATETABLE IF NOT EXISTS ordertotals
　　(order_numINT, total DECIMAL(8,2));
　　--Openthe cursor
　　OPENordernumbers;
　　
　　--Loopthrough all rows
　　REPEAT
　　--Getorder number
　　FETCH ordernumbers INTO o;
　　--Getthe total for this order
　　CALL ordertotal(o,1,t);
　　--Insertorder and total into ordertotals
　　INSERTINTO ordertotals(order_num,total)
　　VALUES(o,t);
　　--ENDOF LOOP
　　UNITLdone END REPEAT;
　　--Closethe cursor;
　　CLOSEordernumbers;
　　END;
　　在这个例子中，FETCH在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0定义变量done。然后用DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done =1;这条语句定义一个CONTINUEHANDLER,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE ‘02000’出现时，SET done=1。SQLSTATE ‘02000’是一个未找到条件，当REPEAT由于没有更多的行工循环而不能继续时，出现这个条件。这里还调用了另外一个存储过程CALLordertotal(o,1,t);这是在前一章创建的存储过程，实现计算每个订单带税额合计。此存储过程不返回数据，但它能够创建和填充另一个表。
　　可以用以下语句执行存储过程和查看存储结果：
　　mysql> CALL processorders();
　　mysql> SELECT * FROM ordertotals;
　　+-----------+---------+
　　| order_num | total |
　　+-----------+---------+
　　|20005 | 158.86 |
　　|20006 | 58.30 |
　　|20007 | 1060.00 |
　　|20008 | 132.50 |
　　|20009 | 40.78 |
　　+-----------+---------+
　　6 rows in set (0.00 sec)
　　这样我们就得到了存储过程，游标，逐行处理以及存储过程调用其他存储工程的一个完整的工作样例。
　　
　　25.使用触发器
　　25.1触发器
　　触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：
　　DELETE
　　INSERT
　　UPDATE
　　25.2创建触发器
　　创建触发器时，需要给出4条信息：
　　1)唯一的触发器名；
　　2)触发器关联的表
　　3)触发器应该响应的活动（DELETE、INSERT或UPDATE）
　　4)触发器何时执行(处理之前或之后)
　　只有表可以支持触发器，视图不支持（临时表也不支持）。
　　CREATE TRIGGER newproduct AFTER INSERT ONproducts FOR EACH ROW SELECT 'd';
　　这里用CREATE TRIGGER创建一个名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以触发器将在INSERT语句成功后执行。这个触发器还制定了FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。PS.在我的MySQL版本中，不能成功实现，会提示不能返回结果集Not allowed to return a result set from a trigger错误。不知道是MySQL版本问题还是MySQL必知必会中的错误。
　　触发器按每个表每个时间每次的定义，每个表每个时间每次只能定义一个触发器，因此一个表最多支持6个触发器（INSERT,UPDATE和DELETE之前或之后）。
　　25.3删除触发器：
　　DROP TRIGGER newproduct;
　　25.4使用触发器
　　25.4.1 INSERT触发器
　　1)在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
　　2)在BEFORE INSERT触发器中，NEW中的值可以被更新（允许更改被插入的值）
　　3）对于AUTO_INCREMENT列，NEW在INSERRT执行之前包含0，在执行之后包含新的自动生成的值。
　　CREATE TRIGGER neworder AFTER INSERT ONorders FOR EACH ROW SELECT NEW.ORDER_num;
　　25.4.2DELETE触发器
　　1）在DELEYE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
　　2）OLD中的值全部都是只读的，不能更新。
　　CREATE TRIGGER deleteorder BEFORE DELETE ONorders
　　FOR EACH ROW
　　BEGIN
　　INSERT INTO
　　archive_orderss(order_num,order_date,cust_id)
　　VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
　　END;
　　25.5 UPDATE触发器
　　1)可以用OLD的虚拟表访问以前的值，也可以用名为NEW的虚拟表访问新更新的值
　　2）在BEFFORE UPDATE触发器中，NEW中的值可能也被更新
　　3）OLD中的值全部都是只读的，不能更新
　　
　　26.管理事务处理
　　事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
　　术语：
　　事务：指一组SQL语句
　　回退：指撤销指定SQL语句的过程
　　提交：指将未存储的SQL语句结果写入数据库表
　　保留点：值事务处理中设置的临时占位符，你可以对它发布回退。
　　26.1控制事务处理
　　管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
　　START TRANSACTION//标记是事务开始
　　26.2使用ROLLBACK
　　ROLLBACK用来回退MySQL语句
　　SELECT * FROM ordertotals;
　　START TRANSACTION;
　　DELETE FROM ordertotals;
　　SELECT * FROM ordertotals;
　　ROLLBACK;
　　SELECT * FROM ordertotals;
　　依次执行上面语句，可以看到删除的表格内容又被回退了。
　　ROLLBACK只能在一个事务处理内使用（在执行一条STARTTRANSACTION命令之后）。
　　26.3使用COMMIT
　　一般的MySQL都是直接对数据库表执行和编写的，这是隐含提交。但在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：
　　START TRANACTION;
　　DELETE FROM orderitems WHERE order_num =20010;
　　DELETE FROM orders WHERE order_num = 20010;
　　COMMIT;
　　如果第一条起作用，第二条失败，则事务不处理，也就是两条DELETE不会被提交。
　　26.4使用保留点
　　简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才嫩这样做，更复杂的事务处理可能需要部分提交或回退。
　　为了支持回退部分事务处理，必须使用保留点，可如下使用SAVEPOINR语句：
　　SAVEPOINT delete1;
　　每个保留点都表示它的唯一名字，以便回退时，MySQL知道要回退到何处。
　　ROLLBACK TO delete1;
　　26.5 更改默认的提交行为
　　为指示MySQL不自动提交更改，需要使用以下语句:
　　SET autocommit = 0;
　　
　　27.全球化和本地化
　　CREATETABLE mytable
　　(
　　Column1 INT,
　　Column2 VARCHAR(0)
　　)DEAULT CHARACTER SET hebrew CLLATEhebrew_general_ci;
　　这条语句创建一个表，并且制定一个字符集和一个校对顺序
　　SHOW CHARACTER SET;//可以查看所支持的字符集完整列表
　　SHOW COLLATION; //查看所支持的校对完整列表
　　
　　28．安全管理
　　管理用户
　　USE mysql;
　　SELECT user FROM user;
　　Mysql数据库有一个名为user的表，它包含所有用户账号。User表有一个user列。
　　28.1创建用户账户
　　为创建新用户账户，使用CREATE USER语句
　　CREATE USER ben IDENTIFIED BY ‘P@$$w0rd’;
　　创建一个用户名为ben的用户，口令为P@$$w0rd。
　　RENAME USER ben TO bforta;
　　28.2删除用户账号
　　DROP USER bforta;
　　28.3设置访问权限
　　mysql> SHOW GRANTS FOR bforta;
　　+-------------------------------------------------------------------------------
　　------------------------+
　　| Grants for bforta@%
　　|
　　+-------------------------------------------------------------------------------
　　------------------------+
　　| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6
　　9274F8C3D15B788433A9EB' |
　　+-------------------------------------------------------------------------------
　　------------------------+
　　1 row in set (0.00 sec)
　　输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示没有权限，所以USAGE ON *.*表示在任意数据库和人意表上对任何东西都没有权限。
　　使用GRANT设置权限，需要给出以下信息：
1）要授予的权限
　　2）被授予访问权限的数据库或表
　　3） 用户名
　　GRANT SELECT ON crashcourse.* TO bforta;
　　赋予bforta在crashcourse.*拥有SELECT权限。
　　在此显示权限如下：
　　SHOW GRANTS FOR bforta;
　　+-------------------------------------------------------------------------------
　　------------------------+
　　| Grants for bforta@%
　　|
　　+-------------------------------------------------------------------------------
　　------------------------+
　　| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6
　　9274F8C3D15B788433A9EB' |
　　| GRANT SELECT ON `crashcourse`.* TO'bforta'@'%'
　　|
　　+-------------------------------------------------------------------------------
　　------------------------+
　　2 rows in set (0.00 sec)
　　
　　可以用REVOKE取消权限。
　　REVOKE SELECT ON crashcourse.* FROM bforta;
　　28.4更改口令
　　更新用户口令
　　SET PASSWORD FOR bforta = Password(‘n3wp@$$w0rd’);
　　设置自己的口令
　　SET PASSWOR = Password(‘n3w p@$$w0rd’);
　　
　　第29章数据库维护
　　ANALYZE TABLE：用来检查表键是否正确
　　CHECK TABLE:用来针对许多问题对表进行检查