第五次作业

一. 触发器
1·建立两个表:goods(商品表)、orders(订单表)

mysql> create database mydb16_tigger;
Query OK, 1 row affected (0.01 sec)
 
mysql> use mydb16_tigger;
Database changed
mysql>
mysql> CREATE TABLE goods (
    ->     gid CHAR(8) PRIMARY KEY,
    ->     name VARCHAR(10),
    ->     price DECIMAL(8, 2),
    ->     num INT
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> CREATE TABLE orders (
    ->     oid INT PRIMARY KEY AUTO_INCREMENT,
    ->     gid CHAR(10) NOT NULL,
    ->     name VARCHAR(10),
    ->     price DECIMAL(8, 2),
    ->     onum INT,
    ->     otime DATE
    -> );
Query OK, 0 rows affected (0.02 sec)
在商品表中导入商品记录 

mysql> INSERT INTO goods VALUES
    -> ('A0001', '橡皮', 2.5, 100),
    -> ('B0001', '小楷本', 2.8, 210),
    -> ('C0001', '铅笔', 1.2, 120),
    -> ('D0001', '计算器', 28, 20);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

2·建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试

mysql> DELIMITER //
mysql>
mysql> CREATE TRIGGER update_goods_num_after_insert
    -> AFTER INSERT ON orders
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE goods
    -> SET num = num - NEW.onum
    -> WHERE gid = NEW.gid;
    -> END //
Query OK, 0 rows affected (0.01 sec)
INSERT INTO orders (gid, name, price, onum, otime)
VALUES ('A0001', '橡皮', 2.5, 10, CURDATE());
 
SELECT * FROM goods WHERE gid = 'A0001';
+-------+------+-------+------+
| gid   | name | price | num  |
+-------+------+-------+------+
| A0001 | 橡皮 |  2.50 |   90 |
+-------+------+-------+------+
1 row in set (0.01 sec)
 

3·建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量

mysql> CREATE TRIGGER restore_goods_num_after_delete
    -> AFTER DELETE ON orders
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE goods
    -> SET num = num + OLD.onum
    -> WHERE gid = OLD.gid;
    -> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> DELETE FROM orders WHERE oid = 1;
Query OK, 1 row affected (0.01 sec)
 
mysql>
mysql> SELECT * FROM goods WHERE gid = 'A0001';
+-------+------+-------+------+
| gid   | name | price | num  |
+-------+------+-------+------+
| A0001 | 橡皮 |  2.50 |  100 |
+-------+------+-------+------+
1 row in set (0.00 sec)
 

4.建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新

mysql> CREATE TRIGGER update_goods_num_after_update
    -> AFTER UPDATE ON orders
    -> FOR EACH ROW
    -> BEGIN
    ->     -- 计算订单数量的差值
    ->     DECLARE diff INT;
    ->     SET diff = NEW.onum - OLD.onum;
    ->     -- 更新商品表中的商品数量
    ->     UPDATE goods
    ->     SET num = num - diff
    ->     WHERE gid = NEW.gid;
    -> END //
Query OK, 0 rows affected (0.01 sec)
 
mysql> DELIMITER ;
mysql> UPDATE orders SET onum = 20 WHERE oid = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
 
mysql> SELECT * FROM goods WHERE gid = 'A0001';
+-------+------+-------+------+
| gid   | name | price | num  |
+-------+------+-------+------+
| A0001 | 橡皮 |  2.50 |  100 |
+-------+------+-------+------+
1 row in set (0.00 sec)

二.存储过程
使用mydb7 openlab库

 USE mydb7_openlab;

创建提取emp_new表所有员工姓名和工资的存储过程s

DELIMITER //
 
CREATE PROCEDURE s()
BEGIN
    SELECT ename, sal FROM emp_new;
END //
 
DELIMITER ;

创建存储过程s2，实现输入员工姓名后返回员工的年龄

mysql> CREATE PROCEDURE s2(IN emp_name VARCHAR(50), OUT emp_age INT)
    -> BEGIN
    ->     SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) INTO emp_age
    ->     FROM emp_new
    ->     WHERE ename = emp_name;
    -> END //
Query OK, 0 rows affected (0.01 sec)

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

mysql> DELIMITER ;
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE s3(IN dept_num INT, OUT avg_sal DECIMAL(8, 2))
    -> BEGIN
    ->     SELECT AVG(sal) INTO avg_sal
    ->     FROM emp_new
    ->     WHERE deptno = dept_num;
    -> END //
Query OK, 0 rows affected (0.01 sec)
————————————————