MySQL表操作—存储

建表：

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 (id,name,glass) values (1,'zhizhuo','glass 1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sch (id,name,glass) values (2,'haodong','glass 2');
Query OK, 1 row affected (0.00 sec)

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

mysql> set global log_bin_trust_function_creators = 1;   #关闭binlog日志
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> \d @
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.02 sec)

mysql> select count_sch()@

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

mysql> \d //

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.00 sec)

mysql> call avg_sal (90,'AD_VP',@s)//