MYSQL – 存储过程和函数
一、数据库的库函数
1、聚合函数
count 计数函数
一般格式:select count(*) from 表名;
select count(<属性名>) from student where s_name like “%张”;
统计数量:返回的是一个结果;
max 求最大值。
一般格式:select max(c_stunum) from class;
嵌套查询:select * from student where s_age in (select max(s_age) from studen);
聚合函数可以作为查询条件。
min 求最小值
一般格式:select min(c_stunum) from class;
嵌套查询:select * from student where s_age in (select min(s_age) from studen);
聚合函数可以作为查询条件。
avg 求平均值
一般格式:avg()
select avg(<属性>) from <表名> ;
sum 求和。
一般格式:sum()
select sum(<属性>) from <表名> ;
2、日期时间函数
- now
求当前日期和时间。
格式:now().
如:select now();
insert into <表名> values (1,now()); - date 求日期函数。
一般格式:date(日期时间类型的属性名)。
如:select date (birthday) from student;
select date("2021-06-01 12:12:12");
select date (now());
- time 求时间函数,
select time(now());— 获取当前时间。 - date_format 时间日期类型转字符类型函数。
select date_format(now(),’%Y%m%d:%H%i%s’); (格式化字符串)
时间大写H:是24小时制,h :是12小时制。
日期大写Y:是全写的数字,y:是缩写的年。
3、常用的一般函数
- upper 小写字母转换大写字母。
select upper(“abcd123456!@#”) - lower 大写字母转小写字母
- substring 提取字符串函数,以给定的参数求字符串中的一个子串。
substring(字符串,start[开始字母],lenth[长度]); - round 四舍五入。
round(数值类型,[,精确位数]);
select round(1234.56);不加精确位数默认精确到整数位。
select round(123.456,2);加上精确位数的就保留n位小数。 - power/ pow 求次方函数。
powet (2,3)2的三次幂。 - length 返回字符串的长度。
length(字符串) - concat 字符串连接函数,连接多个字符串。
concat (字符串1,字符串2,…,字符串n); - database 获取当前数据库名。
database();
select database (); - user 获取当前用户名。
user();
select user(); - 其他: 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,并且必须有返回值。
- 创建的基本格式:create procedure <存储过程名>();
--无参创建--学生信息查询
create procedure select_student()
select * from db_2.student;
- 存储过程的调用 call
基本格式:call <存储过程名>[()];
如:call select_student(). - 查看存储过程。
show create procedure select_student; - 删除存储过程。
drop procedure <存储过程名>; - 修改存储过程。
当前版本不支持修改。
需要修改存储过程的可以删除后重建。 - 带参存储过程的创建。
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 ;
- 调用带参存储过程。
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>);
总结:存储过程和函数的区别
- 函数只能返回一个数据,而存储过程可以返回多个。
- 函数的限制比较多,不能返回临时表(select),不能执行sql语句,只能用表变量,而存储过程可以使用sql语句。
- 存储过程处理的功能比较复杂,而函数实现数据的计算针对性强。
- 存储过程可以执行修改表的操作,但是函数不能执行一组修改全局数据库状态的操作。
- 存储过程可以通过out返回多个参数,而函数只能返回单个数据或者表对象。
以上仅是我在【六星教育】的学习笔记,仅供参考。如有任何错误,敬请指正。欢迎大家的评价与点评。谢谢!