MySql-07-存储过程和函数_mysql中函数和过程的使用-CSDN博客

本文链接：https://blog.csdn.net/m0_74388920/article/details/131197801

san数据库函数

自定义函数

存储过程

一.数据库函数

高级语言都要自己的库函数，数据库也不例外，在数据库中也提供了一些用于实现特定功能的函数。

1.聚合函数

对一组值进行组合计算，返回单个值。

①.count

计数函数，对元组或者属性计数。

一般格式：count(属性名)

select count(*) from 表名; # '*'是对整个表的元组进行计数

select count(属性名) from 表名 where 条件表达式;

# count 支持条件计数，只计算有效值，不计算null值

②.max

求最大值

一般格式：max(属性名)

如：select max(属性名) from 表名 [where 条件表达式];

③.min

求最小值

一般格式：min(属性名)

如：select min(属性名) from 表名 [where 条件表达式];

## 存储过程和函数

use db_2;

# 一、数据库的库函数

# 1、聚合函数
-- 聚合函数就是对一组值进行计算，结果返回单个值

-- ① 统计函数count
-- 统计学生人数
select count(s_id) 学生人数 from student;
-- 数据表中的属性可以作为函数参数传递

-- ② 求最大值函数max
-- 求学生的最大年龄
select max(s_age) from student;

-- 求最小值min、求平均值avg、求和sum……

2.日期时间函数

①.now

求当前日期和时间

一般格式：now()

如：select now();

②.date

求日期

一般格式：date(日期时间类型的属性名)

如：select date(birthday) from student;

# 2、日期时间函数
-- ① 获取当前日期时间now
select now(); -- 2023-05-31 20:14:58

-- insert into 表名(datetime) values(now());
-- 函数的执行结构可以作为数据进行插入，也可以作为条件使用

-- ② 获取日期函数date
-- 获取指定日期时间类型数据中的日期
select date("2023-05-31 20:14:58"); -- 2023-05-31
-- 获取当前日期
select date(now());

-- ③ 获取时间函数time
-- 获取指定日期时间类型数据中的时间
select time("2023-05-31 20:14:58"); -- 20:14:58
-- 获取当前时间
select time(now());

-- year()、month()、day()、hour()……

-- ④ 日期时间类型数据转换成特定格式的字符串类型date_format
select date_format(now(),"%Y-%m-%d %H:%i:%s"); -- 2023-05-31 20:25:32

-- 自定义分隔符
select date_format(now(),"%Y/%m/%d-%H.%i.%s"); -- 2023/05/31-20.26.35
-- 数字日期格式，24小时制时间格式

select date_format(now(),"%y-%M-%D %h:%I:%S"); -- 23-May-31st 08:08:11
-- 英文日期格式，12小时制时间格式

3.常用的一般函数

①.upper

小写字母转大写字母

一般格式：upper(字符型属性名)

如：select upper('abc');

# 也可用 ucase函数代替，如：select ucase('abc');

②.lower

大写字母转小写字母

一般格式：lower(字符型属性名)

如：select upper('ABC');

# 也可用 lcase函数代替，如：select lcase('ABC');

③.substring

提取字串函数，以给定的参数来字符串中的一个字串。

一般格式：substring（字符串,start,lenth);

#从字符串中第start个字符开始取出长度为length的字符串。

如：select substring('abc123@#$',4,3); #结果为123

#也可以用mid函数代替，如：select mid('abc123@#$',4,3);

④.round

四舍五入函数。（可代替floor函数，用于取整）

一般格式：round(数值类型[,精确位数])；

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

select round(123.456,2);#加上精确位数就保留多少小数

⑤.power

求次方函数

一般格式：power(底数，指数);

如：select power(2,3);#求2的3次方值

#可以用pow函数代替power函数，如：select pow(2,3);

#把指数改成小数或分数，就变成求次方根（开方）函数

⑥.length

返回字符串的长度

一般格式：length(字符串)

⑦.concat

字符串连接函数，连接多个字符串

一般格式：concat(字符串1,字符串2,······,字符串n)

⑧.database

返回当前数据库名

一般格式：database()

⑨.user

获取当前用户名

基本格式：user()

# 3、常用库函数
-- 获取当前操作的数据库名database
select database(); -- db_2

-- 获取当前操作的用户名user
select user(); -- root@localhost

-- 数据库中的函数只能用于数据处理，不能用于实现其他功能

4.其他函数

除了之前讲的函数之外，库函数中还有其他函数，如：求余mod，随机数rand，三角函数sin、cos、tan、art tan，圆周率pi等

C语言库函数里有的数据库基本也有；但是MySql中的函数只用于数据处理，不会进去其他命令行操作。

二.自定义函数

1.自定义函数的创建

基本格式：create function 函数名（函数参数数据类型）

returns 返回值类型

begin

#函数体

return (函数返回值)

end;

#8.0版本增加了一个安全选项，需要执行一下代码才能创建函数

set global log_bin_trust_function_creators=TRUE;

# 二、自定义函数function

# 1、自定义函数的创建
-- 求两个数的和
create function SUM1(n int,m int)
returns int -- 给定函数的返回值类型（数据库中的函数必须要有返回值类型）
    return n + m; -- 函数体（数据库中的函数必须要有返回值）
/*
Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled
 (you *might* want to use the less safe log_bin_trust_function_creators variable)

-- 安全检查问题：数据库中默认不允许创建自定义函数和存储过程，因为可能会有安全问题
系统设置了一个安全选项：log_bin_trust_function_creators，来确保安全
*/
-- 将安全选项打开
set global log_bin_trust_function_creators = true;

-- 2、函数调用
select  SUM1(10,20); -- 30

-- 3、自定义函数的删除
drop function SUM1;

-- 4、函数体中包含多条语句的自定义函数
/*
create function SUM1(n int,m int)
returns int
    begin -- 相当于C/C++中的{
set n = n + m; -- 复合语句
return n;
    end; -- 相当于C/C++中的}
*/

-- 数据库中的自定义函数用的特别少，不用掌握

-- 如果在数据库中进行数据处理需要用到函数时，一般使用数据库的库函数就基本能够完成了
-- 数据库中的函数只能用于数据处理，不能用于实现其他功能
-- 数据库函数中无法执行SQL语句，无法实现SQL语句的封装
/*
create function SUM1(n int,m
returns int
    begin -- 相当于C/C++中的{
select n + m;
        -- insert ……;
        -- delete ……;
-- 错误：数据库函数中无法执行SQL语句
        return n + m;
    end; -- 相当于C/C++中的}
*/

三.存储过程

存储过程和函数类似，都是一个事先写好命令并编译后存在数据库中的MySQL语句集合，用于简化开发人员的工作，减少数据在数据库和应用服务器之间传输，提高处理效率。

存储过程和函数的区别：

函数：函数参数只能传入数据，函数体中无法执行SQL语句，并且必须要有返回值。

存储过程：存储过程的参数有传递类型，既可以传入数据又可以传出数据，有IN、OUT或INOUT这三种参数传递类型，IN表示数据传入，OUT表示数据传出，INOUT表示即可传入又可传出。存储过程内能执行大部分SQL语句，并且没有返回值

1.无参存储过程

存储过程关键字：procedure

创建基本格式：

create procedure 存储过程名()

begin

SQL语句1;

`````

SQL语句n;

end;

2.带参存储过程

①.创建带参存储过程

create procedure 存储过程名(

in[out][inout] 参数名1 参数类型1,

in[out][inout] 参数名2 参数类型2,·····,

in[out][inout] 参数名n 参数类型n)

began

SQL语句;

end;

②.调用带参存储过程

call 存储过程名(参数值1,参数值2,·····,参数值n);

③.运用带参存储过程查询数据

如：

create procedure getstuname (in_stu_no int)

begin

select s_name 姓名

from student

where s_no = stu_no;

end;

call getstuname(1);

# 三、存储过程procedure

# 1、无参存储过程的创建
-- 创建一个查询学生信息的存储过程
create procedure pro_select_student()
select * from db_2.student;
-- 存储过程中支持执行SQL语句

# 2、存储过程的调用call
call pro_select_student();

# 3、存储过程的删除
-- drop procedure 存储过程名;

# 4、存储过程的修改
-- MySQL数据库中，存储过程一旦成功创建，它的功能就不支持修改
-- 如果想要修改一个存储过程的功能，需要先删除这个存储过程，然后重写功能，最后重新创建

# 5、带参存储过程的使用
-- 创建一个指定姓名来查询学生信息的存储过程
create procedure pro_select_stu_byname(in sname varchar(20)) -- in：传入类型参数
select * from db_2.student where s_name = sname;

call pro_select_stu_byname("陈小皮");

-- 创建一个插入学生信息的存储过程
create procedure pro_insert_stu(in sname varchar(20),
in scid int,sex varchar(4),sage int) -- 参数传递类型默认为in类型
    insert into db_2.student values(null,sname,scid,sex,sage);

call pro_insert_stu("张三",'103','男',23);

# 6、执行多条语句的存储过程
-- 创建一个插入学 生信息的存储过程，并且在插入数据完成后查询学生信息
delimiter // -- 重定义“//”符号作为语句最终结束符
create procedure pro_insert_student(in sname varchar(20),
in scid int,sex varchar(4),sage int) -- 参数传递类型默认为in类型
    begin -- 相当于C/C++中的{
insert into db_2.student values(null,sname,scid,sex,sage);
select * from db_2.student;
end// -- 相当于C/C++中的}
delimiter ; -- 将语句最终结束符改回‘;’

-- SQL语句默认以这条语句遇到的第一个分号作为整条语句的结束
-- 如果一个存储过程或者函数需要执行多条语句的，就要用begin……end将这多条语句括起来
-- 用begin……end将多条语句括起来，仍然会遇到SQL语句默认以这条语句遇到的第一个分号作为整条语句的结束的问题

/*
重定义语句最终结束符：delimiter
（delimiter不是关键字，也不是可以执行语句，不能执行）
    基本格式：delimiter 符号 -- 重定义“符号”作为新的语句最终结束符
    如：delimiter // -- 重定义'//'作为新的语句最终结束符
delimiter *** -- 重定义'***'作为新的语句最终结束符
        delimiter $+ -- 重定义'$+'作为新的语句最终结束符
-- 注意：一旦重定义了语句最终结束符，那么之后所有的语句都要以这个结束符作为最终结束

-- 注意：不要将已经有特殊意义的符号重定义为语句最终结束符，因为会覆盖掉这个符号本身的意义
如：delimiter , -- 不要重定义','作为新的语句最终结束符
delimiter ( -- 不要重定义'('作为新的语句最终结束符
        delimiter -- -- 不要重定义'--'作为新的语句最终结束符
*/

call pro_insert_student("李四",100,'男',21);