目录
创建表并插入数据
查看表结构
创建触发器
创建INSERT 触发器
创建DELETE 触发器
创建更新触发器
创建存储过程
创建提取emp_new表所有员工姓名和工资的存储过程s1
创建存储过程s2，实现输入员工姓名后返回员工的年龄
创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资
创建表并插入数据
# 创建goods商品表
mysql> CREATE TABLE goods-> (-> gid CHAR(8) PRIMARY KEY, #商品编号-> name VARCHAR(10), #商品名-> price DECIMAL(8,2), #价格-> num INT #数量-> );
Query OK, 0 rows affected (0.01 sec)# 创建orders订单表
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.05 sec)# 给goods表插入数据
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查看表结构
mysql> desc goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid | char(8) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| num | int | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> desc orders;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| oid | int | NO | PRI | NULL | auto_increment |
| gid | char(10) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| onum | int | YES | | NULL | |
| otime | date | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)创建触发器
创建INSERT 触发器
create trigger insert_after_orders_trigger after insert on orders for each row update goodsbeginset num=num-new.onum where gid=new.gid;end //mysql> insert into orders(gid,name,price,onum,otime) value('A0001','橡皮','2.5',20,now());
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from goods;
+-------+-----------+-------+------+
| gid | name | price | num |
+-------+-----------+-------+------+
| A0001 | 橡皮 | 2.50 | 80 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)mysql> select * from orders;
+-----+-------+--------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+--------+-------+------+------------+
| 1 | A0001 | 橡皮 | 2.50 | 20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)创建DELETE 触发器
mysql> DELIMITER //
mysql> CREATE TRIGGER TRIGGER_DELETE_AFTER_ORDERS-> 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> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid | name | price | num |
+-------+-----------+-------+------+
| A0001 | 橡皮 | 2.50 | 80 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+--------+-------+------+------------+
| 1 | A0001 | 橡皮 | 2.50 | 20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)mysql> DELETE FROM orders WHERE gid = 'A0001';
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid | name | price | num |
+-------+-----------+-------+------+
| A0001 | 橡皮 | 2.50 | 100 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 120 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)创建更新触发器
mysql> delimiter //
mysql> CREATE TRIGGER TRIGGER_UPDATE_AFTER_ORDERS-> AFTER UPDATE ON orders-> FOR EACH ROW-> BEGIN-> UPDATE goods SET num = num + (old.onum-new.onum);-> END //
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid | name | price | num |
+-------+-----------+-------+------+
| A0001 | 橡皮 | 2.50 | 100 |
| B0001 | 小楷本 | 2.80 | 210 |
| C0001 | 铅笔 | 1.20 | 100 |
| D0001 | 计算器 | 28.00 | 20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+--------+-------+------+------------+
| 2 | C0001 | 铅笔 | 1.20 | 20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)mysql> UPDATE orders SET onum = 40 WHERE name = '铅笔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid | name | price | num |
+-------+-----------+-------+------+
| A0001 | 橡皮 | 2.50 | 80 |
| B0001 | 小楷本 | 2.80 | 190 |
| C0001 | 铅笔 | 1.20 | 80 |
| D0001 | 计算器 | 28.00 | 0 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid | name | price | onum | otime |
+-----+-------+--------+-------+------+------------+
| 2 | C0001 | 铅笔 | 1.20 | 40 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)创建存储过程
创建提取emp_new表所有员工姓名和工资的存储过程s1
mysql> USE mydb7_openlab
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+-------------------------+
| Tables_in_mydb7_openlab |
+-------------------------+
| dept |
| emp |
| emp_new |
| user |
+-------------------------+
4 rows in set (0.00 sec)mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid | int | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int | YES | | NULL | |
| worktime_start | date | YES | | NULL | |
| incoming | int | YES | | NULL | |
| dept2 | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)mysql> SELECT name,age FROM emp_new;
+---------+------+
| name | age |
+---------+------+
| 张三 | 35 |
| 李四 | 32 |
| 王五 | 24 |
| 赵六 | 57 |
| 荣七 | 64 |
| 牛八 | 55 |
+---------+------+
6 rows in set (0.00 sec)mysql> DELIMITER //
mysql> CREATE PROCEDURE s1()-> BEGIN -> SELECT name,age FROM emp_new;-> END //
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> CALL s1();
+---------+------+
| name | age |
+---------+------+
| 张三 | 35 |
| 李四 | 32 |
| 王五 | 24 |
| 赵六 | 57 |
| 荣七 | 64 |
| 牛八 | 55 |
+---------+------+
6 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)创建存储过程s2，实现输入员工姓名后返回员工的年龄
mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid | int | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int | YES | | NULL | |
| worktime_start | date | YES | | NULL | |
| incoming | int | YES | | NULL | |
| dept2 | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+mysql> SELECT * FROM emp_new;
+------+---------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+---------+------+----------------+----------+-------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+---------+------+----------------+----------+-------+
6 rows in set (0.00 sec)mysql> DELIMITER //
mysql> CREATE PROCEDURE s2(IN in_name VARCHAR(11),OUT out_age INT)-> BEGIN-> SELECT age INTO out_age FROM emp_new WHERE name = in_name;-> END //
Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;
mysql> CALL s2('张三',@out_age);
Query OK, 1 row affected (0.00 sec)mysql> SELECT @out_age;
+----------+
| @out_age |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资
mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid | int | YES | | NULL | |
| name | varchar(11) | YES | | NULL | |
| age | int | YES | | NULL | |
| worktime_start | date | YES | | NULL | |
| incoming | int | YES | | NULL | |
| dept2 | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+mysql> SELECT * FROM emp_new;
+------+---------+------+----------------+----------+-------+
| sid | name | age | worktime_start | incoming | dept2 |
+------+---------+------+----------------+----------+-------+
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
+------+---------+------+----------------+----------+-------+
6 rows in set (0.00 sec)mysql> DELIMITER //
mysql> CREATE PROCEDURE s3(IN in_dept2 INT,OUT avg_incoming DOUBLE)-> BEGIN-> SELECT ROUND(AVG(incoming),2) INTO avg_incoming FROM emp_new WHERE dept2 = in_dept2;-> END //
Query OK, 0 rows affected (0.00 sec)mysql> CALL s3(101,@AVG_incoming);-> //
Query OK, 1 row affected (0.00 sec)mysql> DELIMITER ;
mysql> SELECT @AVG_incoming;
+---------------+
| @AVG_incoming |
+---------------+
| 3166.67 |
+---------------+
1 row in set (0.00 sec)
