字段名 数据类型 主键 外键 非空 唯一 自增
id          INT        是     否     是     是       否
name VARCHAR(50) 否 否 是   否 否
glass  VARCHAR(50) 否 否 是   否 否
sch 表内容
id name glass
1 xiaommg glass1
2 xiaojun glass2
1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

mysql> create table sch (-> id int primary key,-> name varchar(50) not null,-> glass varchar(50) not null-> );
Query OK, 0 rows affected (0.01 sec)

插入数据

mysql> insert into sch values  (1,'xiaommg','glass1'),(2,'xiaojun','glass2');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from sch-> ;
+----+---------+--------+
| id | name    | glass  |
+----+---------+--------+
|  1 | xiaommg | glass1 |
|  2 | xiaojun | glass2 |
+----+---------+--------+
2 rows in set (0.00 sec)

2、创建一个存储过程avg_sal，有3个参数，分别是deptno，job，接收平均工资(out);
功能查询employees表的平均薪资满足条件department_id为deptno，job_id为job的平均工资。

cmysql> create function count_sch()
    -> returns int
    -> return (select count(*) from sch);
Query OK, 0 rows affected (0.05 sec)
 
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> create procedure avg_sal(in deptno int,in job varchar(50),out  s  double)-> begin-> select avg(salary) into s from employees where department_id=deptno and job_id=job;-> select s,-> end;
Query OK, 0 rows affected (0.01 sec)mysql> call avg_sal(90,'AD_VP',@s);
+-------+
| s     |
+-------+
| 17000 |
+-------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)