MySQL数据库视图索引、存储过程案例

视图索引存储过程

准备工作

字段名id	数据类型INT	主键是	外键否	非空是	唯一是	自增否
name	VARCHAR(50)	否	否	是	否	否
glass	VARCHAR(50)	否	否	是	否	否

sch	表内容
id	name	glass
1	xiaommg	glass 1
2	xiaojun	glass 2

CREATE TABLE sch (
	id INT(10) PRIMARY KEY NOT NULL UNIQUE,
	NAME VARCHAR(50) NOT NULL,
	glass VARCHAR(50) NOT NULL
);

INSERT INTO sch VALUES
( 1,'xiaoming','glass 1'),
( 2,'xiaojun','glass 2');

创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

# 写函数当中如果报如下错误，没有则忽略
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
# 解决办法
mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION count_sch()
    -> RETURNS int
    -> BEGIN
    -> DECLARE c INT DEFAULT 0;
    -> SELECT COUNT(*) INTO c FROM sch;                                         
    -> RETURN c;                                                                 
    -> END $                                                                 Query OK, 0 rows affected (0.00 sec)

mysql> \d ;
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资。

mysql> create procedure avg_sal(in p1 int,in p2 varchar(50),out avg_salary float)
    -> begin
    -> select avg(sal) into avg_salary from emp
    -> where deptno=p1 and job=p2;
	-> end /
	
mysql> call avg_sal(30,'销售员',@a) /
mysql> select @a/
+--------------------+
| @a			     |
+--------------------+
| 3222.22249609375   |
+--------------------|
1 row in set (0.00 sec)