数据库作业

字段名   数据类型    主键      外键       非空   唯一      自增
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的平均工资。

建表：

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

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

插入数据：

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

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

 

​
mysql> \d @
mysql> create function count_sch()
    -> return int
    -> begin
    -> declare count int default 0;
    -> select count(*)into count from sch;
    -> return count;
    -> end@

​

\d //
CREATE PROCEDURE avg_sal(IN deptno INT, IN job VARCHAR(50), OUT avg_salary DOUBLE)
BEGIN
SELECT AVG(salary) INTO avg_salary
FROM employees
WHERE department_id = deptno AND job_id = job;
END//