MySQL数据库 存储过程 & 存储函数 强化练习

前言

本环境是基于 Centos 7.8 系统构建MySQL-5.7.14

具体构建，请参考 MySQL-5.7.14 环境构建

sch 表结构字段名 数据类型 主键 外键 非空 唯一 自增id INT 是 否 是 是 否nameVARCHAR(50) 否 否 是 否 否glass VARCHAR(50) 否 否 是 否 否sch 表内容id name glass1 xiaommg glass 12 xiaojun glass 21、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()创建sch表：mysql> create table sch-> (id int primary key auto_increment,-> name varchar(50) not null,-> glass varchar(50) not null);Query OK, 0 rows affected (0.00 sec)mysql> desc sch;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra|+-------+-------------+------+-----+---------+----------------+| id | int(11)| NO | PRI | NULL | auto_increment || name | varchar(50) | NO || NULL ||| glass | varchar(50) | NO || NULL ||+-------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> insert into sch-> values(1,'xiaoming','glass 1'),(2,'xiaojun','glass 2');Query OK, 2 rows affected (0.01 sec)mysql> select * from sch;+----+----------+---------+| id | name| glass |+----+----------+---------+| 1 | xiaoming | glass 1 || 2 | xiaojun | glass 2 |+----+----------+---------+2 rows in set (0.00 sec)mysql> \d $mysql> create function count_stu_num()-> returns int-> begin-> declare num int;-> select count(1) into num from sch;-> return num;-> end $Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select count_stu_num();+-----------------+| count_stu_num() |+-----------------+|2 |+-----------------+1 row in set (0.00 sec)2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资。（使用之前的emp表）# 创建emp表/* 素材 */CREATE TABLE `emp` (`empno` int(4) NOT NULL,`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`mgr` int(4) NULL DEFAULT NULL,`hiredate` date NOT NULL,`sai` int(255) NOT NULL,`comm` int(255) NULL DEFAULT NULL,`deptno` int(2) NOT NULL,PRIMARY KEY (`empno`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '-04-19', 30000, NULL, 20);INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '-05-23', 11000, NULL, 20);INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '-05-01', 80000, 50000, 50);mysql> desc emp;+----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| empno | int(4) | NO | PRI | NULL | || ename | varchar(255) | YES || NULL | || job| varchar(255) | YES || NULL | || mgr| int(4) | YES || NULL | || hiredate | date | NO || NULL | || sai| int(255)| NO || NULL | || comm| int(255)| YES || NULL | || deptno | int(2) | NO || NULL | |+----------+--------------+------+-----+---------+-------+8 rows in set (0.00 sec)mysql> select * from emp;+-------+-----------+-----------+------+------------+-------+-------+--------+| empno | ename| job | mgr | hiredate | sai | comm | deptno |+-------+-----------+-----------+------+------------+-------+-------+--------+| 1001 | 甘宁| 文员| 1013 | 2000-12-17 | 8000 | NULL |20 || 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 |30 || 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 |30 || 1004 | 刘备| 经理| 1009 | 2001-04-02 | 29750 | NULL |20 || 1005 | 谢逊| 销售员 | 1006 | 2001-09-28 | 12500 | 14000 |30 || 1006 | 关羽| 经理| 1009 | 2001-05-01 | 28500 | NULL |30 || 1007 | 张飞| 经理| 1009 | 2001-09-01 | 24500 | NULL |10 || 1008 | 诸葛亮 | 分析师 | 1004 | -04-19 | 30000 | NULL |20 || 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL |10 || 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 |0 |30 || 1011 | 周泰| 文员| 1006 | -05-23 | 11000 | NULL |20 || 1012 | 程普| 文员| 1006 | 2001-12-03 | 9500 | NULL |30 || 1013 | 庞统| 分析师 | 1004 | 2001-12-03 | 30000 | NULL |20 || 1014 | 黄盖| 文员| 1007 | 2002-01-23 | 13000 | NULL |10 || 1015 | 张三| 保洁员 | 1001 | -05-01 | 80000 | 50000 |50 |+-------+-----------+-----------+------+------------+-------+-------+--------+15 rows in set (0.00 sec)# 创建存储过程mysql> \d $$mysql> create procedure avg_sai(in p1 int,in p2 varchar(255),out p3 int(255))-> begin-> select avg(sai) into p3 from emp-> where empno=p1 or job=p2;-> end $$mysql> call avg_sai(30,'销售员',@W);Query OK, 1 row affected (0.04 sec)mysql> select @W;+-------+| @W |+-------+| 14000 |+-------+1 row in set (0.00 sec)