创建表并插入数据
字段名 id | 数据类型 INT | 主键 是 | 外键 否 | 非空 是 | 唯一 是 | 自增 否 |
name | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
glass | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
sch | 表内容 | |
id | name | glass |
1 | xiaommg | glass 1 |
2 | xiaojun | glass 2 |
mysql> create table sch(
-> id int primary key unique not null,
-> name varchar(20) not null,
-> glass varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into sch values (1,'xiaoming','glass 1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sch values (2,'xiaojun','glass 2');
Query OK, 1 row affected (0.01 sec)
mysql> select * from sch;
+----+----------+---------+
| id | name | glass |
+----+----------+---------+
| 1 | xiaoming | glass 1 |
| 2 | xiaojun | glass 2 |
+----+----------+---------+
2 rows in set (0.00 sec)
1、创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
mysql> \d $
mysql> create function count_sch()
-> returns int
-> begin
-> declare i int default 0;
-> select count(1) into i from sch;
-> return i;
-> end$
Query OK, 0 rows affected (0.00 sec)
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
| 2 |
+-------------+
1 row in set (0.06 sec)
2、创建一个存储过程avg_sai,有3个参数,分别是deptno,job,接收平均工资, 功能查询emp表为30,job为销售员的平均工资。