MySQL函数及存储过程(含图片)
MySQL函数及存储过程
- 参考文章:
https://www.cnblogs.com/wupeiqi/articles/5713323.html
1、函数
1.1内置函数
- 官方文档:
https://dev.mysql.com/doc/refman/5.7/en/functions.html
CHAR_LENGTH(str)
返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ,则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接(自定义连接符)
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
CONV(N,from_base,to_base)
进制转换
例如:
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。
例如:
SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos:要替换位置其实位置
len:替换的长度
newstr:新字符串
特别的:
如果pos超过原字符串长度,则返回原字符串
如果len超过原字符串长度,则由新字符串完全替换
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串 str ,其引导空格字符被删除。
RTRIM(str)
返回字符串 str ,结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL,则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ,顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始,返回从后边开始len个字符组成的子序列
SPACE(N)
返回一个由N空格组成的字符串。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
部分内置函数
1.2自定义函数
-
语法
delimiter \\ -- 修改结束符 create function f1( -- 创建函数的命令 i1 int, i2 int) -- 定义参数: 参数的类型必须是表名里面有的 returns int -- 定义返回值类型 BEGIN -- 函数体开始 -- 注意:函数体内不能写select * from table declare num int; -- 声名变量 -- declare num int default 0; --声名变量并设置默认值是一 set num = i1 + i2;-- 执行相加操作 return(num);-- 返回num END \\-- 函数体结束 delimiter ; -- 修改回结束符
- 报错解决方式
-
-- MYSQL 函数 -- 内置函数 -- 函数的使用,在select后面使用 -- 显示创建日期 SELECT CURRENT_DATE() -- 日期格式化,常用 -- use prozhjy SELECT 时间,count(1) from 任务点途径 GROUP BY DATE_FORMAT(时间,"%Y-%m") SELECT DATE_FORMAT(时间,"%Y-%m-%d") FROM `任务点途径` -- 自定义函数 delimiter \\ CREATE FUNCTION f1 ( i1 INT, i2 INT ) RETURNS INT BEGIN DECLARE num INT; SET num = i1 + i2; RETURN ( num ); END \\ delimiter; -- 删除函数 drop function func_name; -- 执行函数 # 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
2、存储过程
2.1 .简单存储过程的创建
-- 存储过程
-- 个人理解:一堆SQL语句的操作进行集合
-- 分类
-- 简单创建
-- 创建存储过程
delimiter //
create procedure p1() -- 创建命令
BEGIN
select * from student; -- 命令体
END//
delimiter ;
-- 执行存储过程
call p1()
-- 无参数存储过程
2.2. 有参数的存储过程
-
参数分类
- out 仅用于返回值(注:一般作为执行成功返回的标志)
- in 仅用于传入参数
- inout 既可以当做传入参数,也可以当做返回值来用
-
删除存储过程:
DROP PROCEDURE pro_name
-- ============================================== -- 创建存储过程 -- delimiter \\ -- create procedure p2( -- in i1 int,-- 传入参数 -- -- in i2 int, -- -- inout i3 int,-- 既可以传入也可以传出的参数 -- out r1 int -- 传出参数 -- ) -- BEGIN -- -- 声名变量类型 -- -- DECLARE n2 int; -- -- DECLARE temp2 int default 0; -- -- -- 声名session级别的变量调用是使用@ -- -- set temp1 = 1; -- -- -- set r1 = i1 + i2 + temp1 + temp2; -- SELECT * from student; -- -- set i3 = i3 + 100; -- set r1 =i1+1 -- end\\ -- delimiter ; -- -- -- 执行存储过程 -- -- 声名session变量 -- -- set @t1 =4; -- set @t2 = 0; -- CALL p1 (1,@t2); -- SELECT @t2; -- 有参数的存储过程 -- ************************************** -- 有参数的存储过程 delimiter \\ CREATE PROCEDURE p3( in n1 int, out n2 int ) BEGIN set n2=123123; SELECT * from student WHERE sid>n1; END \\ delimiter ; set @v1=123; call p3(2,@v1); SELECT @v1;
2.3事务
-- 事务
delimiter \\
CREATE PROCEDURE p4(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for SQLEXCEPTION
BEGIN
-- error检测异常,事物回滚
set p_return_code=1;
ROLLBACK;
END
-- DECLARE EXIT handler for SQLWARNING
-- BEGIN
-- -- waring,使用较少
-- set p_return_code=2;
-- ROLLBACK;
-- END
-- 开始事物
START TRANSACTION
DELETE from teacher WHERE tname='程心';
INSERT into teacher(tname)VALUES('章北海');
COMMIT;
-- 提交事物
-- success
set p_return_code=0;
END \\
delimiter ;
set @v2=123;
call p4(@v2);
SELECT @v2;
SELECT * from teacher
2.4 游标
delimiter \\
CREATE PROCEDURE p5()
BEGIN
DECLARE ssid int;-- 定义变量
DECLARE ssname VARCHAR(50);-- 自定义变量2
-- 设置标志,用来检测游标内是否还有数据,默认为flase
DECLARE done INT DEFAULT FALSE;
-- 定义游标 语法
DECLARE my_cursor CURSOR FOR SELECT sid,sname FROM student;
-- 当游标内没有数据的时候将done设置为TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN my_cursor;
xxyy:loop
FETCH my_cursor into ssid,ssname;
IF done THEN
LEAVE xxyy;
END IF;
INSERT into teacher (tname) VALUES (ssname);
END loop xxyy;
CLOSE my_cursor;
END\\
delimiter ;
-- =====================
-- 调用游标
call p5()
SELECT * from teacher;
SELECT * from student;
2.5 使用pymysql调用存储过程
-
无参调用
# -*- coding: utf-8 -*- ''' @Time : 2021/10/17 19:22 @Author : ziqingbaojian @File : sqlcon.py ''' import pymysql try: conn=pymysql.connect(host='127.0.0.1',user='root',password='1234567',database='testlearn') except Exception as e: print(e) cur=conn.cursor() # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)查询结果以字典的形式进行返回 # 普通查询 ''' sqlstr="select * from student where sid>%s" cur.execute(sqlstr,3) res=cur.fetchall() print(res) ''' # 调用无参的存储过程 cur.callproc('p1') res=cur.fetchall() print(res)
-
参数调用
# -*- coding: utf-8 -*- ''' @Time : 2021/10/17 19:22 @Author : ziqingbaojian @File : sqlcon.py ''' import pymysql try: conn=pymysql.connect(host='127.0.0.1',user='root',password='1234567',database='testlearn') except Exception as e: print(e) cur=conn.cursor() # 调用有参存储过程 # 执行存储过程 cur.callproc('p3',(2,123)) # 获取执行完存储的参数 # 接收存储过程中查询结果 res=cur.fetchall() print(res) # 存储过程返回的结果 # 固定 参数写法:@_pname_0,@_pname_1,..... cur.execute("select @_p3_0,@_p3_1;") result = cur.fetchall() conn.commit() cur.close() conn.close() print(result)
今天你学习了吗?