前言
MySQL的学习已经进入存储过程和函数了,这一章节还是稍微有点难度的,题目还是要从简到难入手,明确存储的系统结构,语法构造,
一、练习两小题
1、题目代码介绍
字段名 数据类型 主键 外键 非空 唯一 自增
id INT 是 否 是 是 否
name VARCHAR(50) 否 否 是 否 否
glass VARCHAR(50) 否 否 是 否 否
sch 表内容
id name glass
1 xiaommg glass 1
2 xiaojun glass 2
还需要另一个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, '2007-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, '2007-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, '2013-05-01', 80000, 50000, 50);
二、解题步骤及其答疑
1.题目:创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
代码如下(示例):
mysql> \d //
mysql> create procedure count_id()
-> begin
-> select count(1) from id;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> call count_id();
+----------+
| count(1) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
答疑:
1、第一个函数创建并不难,就是一个计数功能的作用,所以直接就先更改一下 MySQL的结束符号,标准的使用dlimiter &&,其实就是一个语句而已,将技术字符更改为// 以后就可以开始创建函数了
以下的东西就是存储过程的定义基本语句:
DELIMITER $$
CREATE
PROCEDURE `demo`.`demo1`()
-- 存储过程体
BEGIN
-- DECLARE声明 用来声明变量的
DECLARE de_name VARCHAR(10) DEFAULT '';
SET de_name = "jim";
-- 测试输出语句(不同的数据库,测试语句都不太一样。
SELECT de_name;
END$$
DELIMITER ;
2、其次就是核心语句 select count(1) from id
3、后面结尾语句以后 一定要 /d ;修改为MySQL的结束语句为英文逗号
4、然后调用一下 call count_id(); 就ok了
2.题目:创建一个存储过程avg_sai,有3个参数,分别是deptno,job,接收平均工资,功能查询emp表dept为30,job为销售员的平均工资。
代码如下(示例):
mysql> \d //
mysql> create procedure bb (in x int,in y varchar(255),out z int)
-> begin
-> select avg(sai) into z from emp where deptno=x and job=y;
-> end //
Query OK, 0 rows affected (0.00 sec)
mysql> \d;
mysql> call bb(30,'销售员',@z);
Query OK, 1 row affected (0.00 sec)
mysql> select @z;
+-------+
| @z |
+-------+
| 14000 |
+-------+
1 row in set (0.00 sec)
---
1、题目中说的是三个参数,分析一下其中有两个参数需要输入进这个函数当中,而要求输出这个平均工资,
所以in deptno int和in job varchar(255) 当然这里可以像我一样用单个字母将他替换掉,这样能避免看花或者单词打错
2、接下来就select语句,因为要输出平均值 肯定要运用函数avg(sai),然后就是判断语句where;
3、注意引用函数的时候,由于我们是输入了两个东西 deptno和job,所以在引用的时候需要将其标标注出来,你需要查询哪一个部门以及部门编号,然后带一个输出出来部门工资的平均值