MYSQL -- 存储过程和函数

MYSQL – 存储过程和函数

一、数据库的库函数

1、聚合函数

1. count 计数函数

   一般格式：select count(*) from 表名；

   select count(<属性名>) from student where s_name like “%张”；

   统计数量：返回的是一个结果；

2. max 求最大值。

   一般格式：select max(c_stunum) from class;

   嵌套查询：select * from student where s_age in (select max(s_age) from studen);

   聚合函数可以作为查询条件。

3. min 求最小值

   一般格式：select min(c_stunum) from class;

   嵌套查询：select * from student where s_age in (select min(s_age) from studen);

   聚合函数可以作为查询条件。

4. avg 求平均值

   一般格式：avg（）

   select avg(<属性>) from <表名> ;

5. sum 求和。

   一般格式：sum（）

   select sum(<属性>) from <表名> ;

2、日期时间函数

1. now

   求当前日期和时间。

   格式：now().

   如：select now();

   insert into <表名> values (1,now());

2. date 求日期函数。

   一般格式：date（日期时间类型的属性名）。

   如：select date (birthday) from student;

    	select date("2021-06-01 12:12:12");
   

   ​ select date (now());

3. time 求时间函数，

   select time(now());— 获取当前时间。

4. date_format 时间日期类型转字符类型函数。

   select date_format(now(),’%Y%m%d:%H%i%s’)； (格式化字符串)

   时间大写H:是24小时制，h ：是12小时制。

   日期大写Y：是全写的数字，y：是缩写的年。

3、常用的一般函数

1. upper 小写字母转换大写字母。

   select upper(“abcd123456!@#”)

2. lower 大写字母转小写字母

3. substring 提取字符串函数，以给定的参数求字符串中的一个子串。

   substring(字符串，start[开始字母]，lenth[长度])；

4. round 四舍五入。

   round（数值类型，[，精确位数]）；

   select round(1234.56);不加精确位数默认精确到整数位。

   select round(123.456,2);加上精确位数的就保留n位小数。

5. power/ pow 求次方函数。

   powet (2,3)2的三次幂。

6. length 返回字符串的长度。

   length(字符串)

7. concat 字符串连接函数，连接多个字符串。

   concat (字符串1，字符串2，…，字符串n)；

8. database 获取当前数据库名。

   database();

   select database ();

9. user 获取当前用户名。

   user();

   select user();

10. 其他： mod 求余，rand 随机，sin、cos、tan…

二、自定义函数

1、自定义函数的创建。

基本格式：create function 函数名(函数参数 数据类型)

returns 返回值类型。-- 函数必须有返回值。

begin

#函数体

return(函数返回值)

end；

delimiter //
create function sum1(a int,b int)
retuens int	--给定返回值类型。
begin
return a+b;
end//
delimiter ;

如果创建函数是出现错误，没有语法错误，那么就是没有去掉安全检查，那么就需要执行下列代码。

set global log_bin_trust_function_creators=TRUE;

2、函数的调用 select .

• select db_2.sum1(12,10);
• 调用select 函数名<参数>；
• 用表格数据作为函数参数select <函数名>(表格属性) from 表名；

3、自定义结束符 delimiter

基本格式：delimiter <符号>； --将<符号>定义为语句结束符。

4、自定义函数删除

​ 格式drop function <数据库名.函数名>;

-- 完成根据学生学号查询学生信息的功能
delimiter //
create function sum1(x int,y int)
returns int
return x+y;
select sum(4,5);
drop function db_2.sum();
数据库中每条语句，必须以“；”号结束，

三、存储过程 procedure

想要在某个功能函数里处理数据库SQL，就需要用数据库中的存储过程。

存储过程和函数类似，都是实现编写好的功能，

存储过程：参数的传递（参数类型）能用 IN 、OUT、 INOUT、并且没有直接的返回值。IN　表示数据传入，OUT表示数据传出，INOUT表示既可以传入也可以传出。存储过程内不能用use切换数据库。

函数：参数只能使用IN，并且必须有返回值。

1. 创建的基本格式：create procedure <存储过程名>()；

--无参创建--学生信息查询
create procedure select_student()
select * from db_2.student;

2. 存储过程的调用 call

   基本格式：call <存储过程名>[()];

   如：call select_student().

3. 查看存储过程。

   show create procedure select_student;

4. 删除存储过程。

   drop procedure <存储过程名>;

5. 修改存储过程。

   当前版本不支持修改。

   需要修改存储过程的可以删除后重建。

6. 带参存储过程的创建。

   create procedure<存储过程名>

-- 创建插入学生数据的存储过程 并查询插入结果。
delimiter //
create procedure pro_insert_student(
    -- 参数类型  in,out,inout
    in sname varchar(20),in scid int,
    in ssex varchar(4),in sage int)
    begin
    insert into student(s_name, s_cid,s_sex,s_age)
    values(sname,scid,ssex,sage);
    select *from studle where s_name=sname;
    end//
    delimiter ;
    

7. 调用带参存储过程。

call pro_insert_stu('小兰',15,'女',20)；

存储过程——删除一个数据，

create procedure pro_delete_stu(in sid int )
delete from student where s_id = sid;
call pro_delete_stu(<sid>);

总结：存储过程和函数的区别

1. 函数只能返回一个数据，而存储过程可以返回多个。

2. 函数的限制比较多，不能返回临时表（select），不能执行sql语句，只能用表变量，而存储过程可以使用sql语句。

3. 存储过程处理的功能比较复杂，而函数实现数据的计算针对性强。

4. 存储过程可以执行修改表的操作，但是函数不能执行一组修改全局数据库状态的操作。

5. 存储过程可以通过out返回多个参数，而函数只能返回单个数据或者表对象。

以上仅是我在【六星教育】的学习笔记，仅供参考。如有任何错误，敬请指正。欢迎大家的评价与点评。谢谢！