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

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由于没有更多的行工循环而不能继续时,出现这个条件。这里还调用了另外一个存储过程CALL ordertotal(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:用来针对许多问题对表进行检查

MySQL之21-29 重点: 视图,触发器,存储过程,游标,事务处理,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。