MySql-07-存储过程和函数

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); 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值