MySQL小案例

Suexiao7

已于 2023-12-04 11:51:12 修改

阅读量55

点赞数

文章标签：数据库

于 2023-07-13 15:07:35 首次发布

本文链接：https://blog.csdn.net/Suexiao7/article/details/131684099

版权

创建表并插入数据
字段名数据类型主键外键非空唯一自增
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 emp( 
    -> id int primary key auto_increment, 
    -> name varchar(50) not null,
    -> glass varchar(50) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sch values (1,'xiaommg','glass 1'),(2,'xiaojun','glass 2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> \d $$
mysql> create function count_sch()
    -> returns int
    -> begin
    -> declare a int default 0;
    -> select count(*) into a from sch;
    -> return a;
    -> 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)

二、原表在第四天

mysql> \d $$
mysql> create procedure avg_sai(in p1 int,in p2 varchar(50),out p3 int)
    -> begin
    -> select avg(sai) into p3 from emp
    -> where deptno=p1 and job=p2;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> \d

mysql> call avg_sai(30,'销售员',@a);                                                                                                      
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+-------+
| @a    |
+-------+
| 14000 |
+-------+
1 row in set (0.00 sec)