数据库学习第四次小作业

题目：

字段名 数据类型 主键 外键 非空 唯一 自增
id INT 是 否 是  是 否
name VARCHAR(50) 否 否 是   否 否
glass  VARCHAR(50) 否 否 是   否 否
sch 表内容
id name glass
1 xiaommg glass1
2 xiaojun glass2
1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()
2、创建一个存储过程avg_sal，有3个参数，分别是deptno，job，接收平均工资(out);
功能查询employees表的平均薪资满足条件department_id为deptno，job_id为job的平均工资。

一，创建一个sch表，并将以上内容插入表中

mysql> use dxl;
Database changed
mysql> show tables;
Empty set (0.00 sec)

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

mysql> insert into sch(name,glass)
    -> values('xiaoming','glass1'),
    -> ('xiaojun','glass2');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看表内容：

mysql> desc sch;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| glass | varchar(50) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from sch;
+----+----------+--------+
| id | name     | glass  |
+----+----------+--------+
|  1 | xiaoming | glass1 |
|  2 | xiaojun  | glass2 |
+----+----------+--------+
2 rows in set (0.00 sec)

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

mysql> create function count_sch() 
    -> returns int
    -> begin
    -> declare a int default 0;
    -> select count(*) into a from sch;
    -> return a;
    -> end //


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

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

mysql> \d //
mysql> create procedure avg_sal(
in b int,
in c varchar(50) character set utf8,
out d int) 
begin 
select avg(salary) into d from employees where department_id=b and job_id=c; 
end  //
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> call avg_sal(90,'AD_VP',@d)//
Query OK, 1 row affected (0.01 sec)

mysql> select @d //
+-------+
| @d    |
+-------+
| 17000 |
+-------+
1 row in set (0.00 sec)