mysql触发器 5天学完《MySQL必知必会》学习笔记之第五天

发布时间：2022-09-12 16:06:34 所属栏目：MySql教程来源：

导读：　　最后一部分看的很潦草（接近尾声的浮躁），现在把笔记整理上来，再看有些知识点还是很陌生，后续要多练习。

　　本篇知识点

　　游标、触发器、事务处理、字符集和校对、安全管理、数据库维护

　　

　　最后一部分看的很潦草（接近尾声的浮躁），现在把笔记整理上来，再看有些知识点还是很陌生，后续要多练习。

　　本篇知识点

　　游标、触发器、事务处理、字符集和校对、安全管理、数据库维护

　　使用游标

　　游标使得用户可以根据需要滚动浏览屏幕上的数据。

　　游标只能用于存储过程（和函数）

　　创建游标

　　DELIMITER //
　　CREATE PROCEDURE processorders( )
　　BEGIN
　　DECLARE ordernumbers CURSOR
　　FOR
　　SELECT order_num FROM orders;
　　END//
　　DELIMITER ;
　　DECLARE语句用来定义和命名游标，这里为ordernumbers。

　　OPEN ordernumbers;
　　在处理OPEN语句时执行查询，存储检索出的数据供以浏览和滚动。

　　CLOSE ordernumbers;
　　CLOSE释放游标使用的所有内部内存和资源

　　注意：以上两句单独在MySQL中运行是会报错的，因为游标只用于存储过程。

　　整合了打开关闭语句的命令

　　DELIMITER //
　　CREATE PROCEDURE processorders( )
　　BEGIN
　　     -- Declare the cursor
　　     DECLARE ordernumbers CURSOR
　　     FOR
　　     SELECT order_num FROM orders;
　　
　　     -- Open the cursor
　　     OPEN ordernumbers;
　　
　　     --Close the cursor
　　     CLOSE ordernumbers;
　　END//
　　DELIMITER ;
　　这个存储过程声明、打开和关闭一个游标，但对检索出的数据什么也没做。

　　CREATE PROCEDURE processorders( )
　　BEGIN
　　
　　     -- Decalre local variable
　　     DECLARE o INT;
　　
　　     -- Decalre the cursor
　　     DECLARE ordernumbers CURSOR
　　
　　     -- Get order number
　　     FETCH ordernumbers INTO o;
　　
　　     -- Close the cursor
　　     CLOSE ordernumbers;
　　
　　END;
　　使用FETCH来检索当前行的order_num列到一个名o的局部声明的变量中。

　　CREATE PROCEDURE processorders( )
　　BEGIN
　　
　　     -- Declare local variables
　　     DECALRE done BOOLEAN DEFAULT 0;
　　     DECLARE o INT
　　
　　     -- Declare the cursor
　　     DECLARE ordernumbers CURSOR
　　     FOR
　　     SELECT order_num FROM orders;
　　
　　     -- Declare continue handler
　　     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
　　
　　     -- Open the cursor
　　     OPEN ordernumbers;
　　
　　     -- Loop through all rows
　　     REPEAT
　　
　　     -- Get order number
　　     FETCH ordernumbers INTO o;
　　
　　     -- End of loop
　　     UNTIL done END REPEAT;
　　
　　     -- Close the cursor
　　     CLOSE ordernumbers;
　　
　　END;
　　在REPEAT中使用FETCH检索当前order_num到声明为o的变量中，反复执行直到done为真（由UNTIL done END REPEAT;规定）

　　DECLARE CONTINUE HANDLER '02000' SET done=1;
　　这条语句定义了一个CONTINUE HANDLER，在条件出现时被执行，表示当SQLSTATE '02000'出现时，SET done =1。

　　CREATE PROCEDURE processorders( )
　　BEGIN
　　
　　     -- Declare local variables
　　     DECALRE done BOOLEAN DEFAULT 0;
　　     DECLARE o INT;
　　     DECALRE t DECIMAL(8,2);
　　
　　     -- Decalre the cursor
　　     DECLARE ordernumbers CURSOR
　　     FOR
　　     SELECT order_num FROM orders;
　　     -- Declare continue handler
　　     DECALRE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
　　
　　     -- Create a table to store the results
　　     CREATE TABLE IF NOT EXISTS ordertotals
　　          (order_num INT, total DECIMAL(8, 2));
　　
　　     -- Open the cursor
　　     OPEN ordernumbers;
　　
　　     -- Loop through all rows
　　     REPEAT
　　
　　          -- Get order number
　　          FETCH ordernumbers INTO o;
　　
　　          -- Get the total for this order
　　          CALL ordertotal(o, 1, t);
　　
　　          -- Insert order and total into ordertotals
　　          INSERT INTO ordertotals (order_num, total)
　　          VALUES(o, t);
　　
　　     -- End of loop
　　     UNTIL done END REPEAT;
　　
　　     -- Close the cursor
　　     CLOSE ordernumbers;
　　
　　END;
　　这个例子中增加了一个名为t的变量用来存储每个订单的合计，此存储过程中还在运行中创建了一个新表，名为ordertotals，这个表保存存储过程生成的结果。FETCH语句里的CALL执行的是另一个存储过程（前一章中创建）来计算每个订单的带税的合计。最后，用INSERT保存每个订单号和合计。此存储过程不返回数据，它创建和填充另一个表，可用SELECT语句查看。

　　使用触发器创建触发器

　　CREATE TRIGGER newproduct AFTER INSERT ON products
　　FOR EACH ROW SELECT 'product added';
　　CREATE TRIGGER用来创建名为newporoduct的新触发器，触发器在INSERT操作之后执行，FOR EACH ROW指示文本Product added将对每个插入的行显示一次。

　　代码执行错误ERROR 1415 (0A000): Not allowed to return a result set from a trigger

　　原因：Earlier versions of MySQL5 were supported, and the current version does not support this. The trigger does not allow the form of SELECT *, because this will return a result set, which is not allowed, so this error will be reported

　　解决 " data-url="" data-numero="1" data-draft-node="inline" data-draft-type="reference" data-tooltip="来自 " data-tooltip-preset="white" data-tooltip-classname="ztext-referene-tooltip">：In the trigger, you can use the form of SELECT INTO to query, put the result into a variable, and then query the variable.

　　CREATE TRIGGER newproduct AFTER INSERT ON products
　　FOR EACH ROW SELECT 'Product added' INTO @arg;
　　触发器仅支持表，视图、临时表不支持

　　DROP TRIGGER newproduct;
　　为修改一个触发器，必须先删除它。

　　CREATE TRIGGER neworder AFTER INSERT ON orders
　　FOR EACH ROW SELECT NEW.order_num INTO @arg;
　　触发器仅支持表，视图、临时表不支持

　　DROP TRIGGER newproduct;
　　为修改一个触发器，必须先删除它。

　　CREATE TRIGGER neworder AFTER INSERT ON orders
　　FOR EACH ROW SELECT NEW.order_num INTO @arg;
　　此代码创建一个名为neworder的触发器，按照AFTER INSERT ON orders 执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回。NEW是在INSERT触发器代码内引用的一个虚拟表，访问被插入的行。

　　这种方法可以用来确定AUTO_INCREMENT列新生成的值

　　测试触发器，插入一个新行

　　INSERT INTO orders(order_date, cust_id)
　　VALUES(Now(), 10001);
　　SELECT @arg;
　　CREATE TRIGGER deleteorder 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;
　　在任意订单被删除前将执行此触发器。它使用一条ISNERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中。若订单不能存档，DELETE本身将被放弃。OLD是在触发器内被引用的一个表你表，用来访问被删除的行，OLD中的值全都是只读，不能更新。

　　多语句触发器触发器deleteorder可以使用BEGIN和END语句标记触发器体，使用BEGIN END块的好处是触发器能容纳多条SQL语句。

　　CRATE TRIGGER updateevendor BEFORE UPDATE ON vendors
　　FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
　　任何数据净化都要在UPDATE语句之前执行。每更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vned_state)替换。

　　管理事务处理

　　START TRANSACTION;
　　标识事务的开始。

　　SELECT * FROM ordertotals;
　　START TRANSACTION;
　　DELETE FROM ordertotals;
　　SELECT * FROM ordertotals;
　　ROLLBACK;
　　SELECT * FROM ordertotals;
　　开始事务后，用一条DELETE语句删除ordertotals中所有行，另一条SELECT语句验证ordertotals为空，ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。

　　START TRANSANCTION;
　　DELETE FROM orderitems WHERE order_num = 20010;
　　DELETE FROM orders WHERE order_num = 20010;
　　COMMIT;
　　这个例子中，从系统中完全删除订单20010，因为涉及更新两个数据表orders和orderitems，使用事务处理块来保证订单不被部分删除。若两条DELETE没有同时起作用，则DELETE不会提交，被自动撤销。

　　SAVEPOINT deletel;
　　每个保留点都取标识它的唯一名字，回退操作：

　　ROLLBACK TO deletel;
　　释放保留点：

　　RELEASE SAVEPOINT;
　　SET autocommit=0;
　　autocommit标志决定是否自动提交更改，不管有没有COMMIT语句，0指示MySQL不自动提交更改，直到autocommit设置为真。

　　全球化和本地化显示所有可用的字符集以及每个字符集的描述和默认校对

　　SHOW CHARACTER SET;
　　SHOW COLLATION;
　　CREATE TABLE mytable
　　(
　　  Column1 INT,
　　  Column2 VARCHAR(10)
　　) DEFAULT CHARACTER SET hebrew
　　  COLLATE hebrew_general_ci;
　　CREATE TABLE mytable
　　(
　　  Column1 INT,
　　  Column2 VARCHAR(10),
　　  Column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
　　) DEFAULT CHARACTER SET hebrew
　　  COLLATE hebrew_general_ci;
　　SELECT * FROM customers
　　ORDER BY lastname, firstname COLLATE latin1_general_cs;
　　安全管理

　　USE mysql;
　　SELECT user FROM user;
　　CREATE USER ben INDENTIFIED BY 'p@$$w0rd';
　　创建用户账号时不一定需要口令mysql触发器，这个例子用IDENTIFIED BY给出了口令。

　　RENAME USRE ben to bforta;
　　DROP USER bforta;
　　GRANT SELECT ON crashcourse.* TO bforta;
　　允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT，表示只有只读访问权限。

　　SHOW GRANTS FOR bforta;
　　REVOKE SELECT ON crashcourse.* FROM bforta;
　　SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
　　SET PASSWWORD = Password('n3w p@$$w0rd')
　　数据库维护

　　ANALYZE TABLE orders;
　　CHECK TABLE orders, orderitems;

（编辑：开发网_商丘站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MariaDB10.3 系统版本	Amoeba达成数据库读写
orchestrator 高可用y	该如何使用MySQL事务