MySql --- 作业

一. 触发器
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: 0mysql> 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()
BEGINSELECT 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)