数据库day5-练习

创建表并插入数据

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

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

1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()
2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资。

// 按要求创建数据表，并插入数据
mysql> create table sch(
    -> id int primary key auto_increment not null,
    -> name varchar(50) not null,
    -> glass varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> desc sch;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| glass | varchar(50) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into sch values
    -> (1,'xiaommg','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 | xiaommg | glass1 |
|  2 | xiaojun | glass2 |
+----+---------+--------+
2 rows in set (0.00 sec)

// 1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()
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.00 sec)
mysql> \d;
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

// 2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资。
mysql> delimiter //
mysql> create procedure avg_sai
    ->( in dept int, in job varchar(50), out p1 float(10,2))
    ->begin select avg(sai) into p1 from emp
    ->where deptno=dept and job='SALESMAN';
    ->end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call avg_sai(30,'SALESMAN',@a);
Query OK, 1 row affected (0.00 sec)
mysql> select @a;
+-----------------+
| @a              |
+-----------------+
| 15666.669921875 |
+-----------------+
1 row in set (0.00 sec)