2. 技术
    3. MySQL-存储过程(数据类型、函数)

    MySQL-存储过程(数据类型、函数)

    技术2022-07-10  177

    文章目录

    MySQL存储过程一、存储过程与函数二、命令行结束标志符号三、关于变量的使用1. 局部变量2. 用户变量3. 存储过程结果赋值到变量的几种方法 四、存储过程的查看、删除、调用、创建1. 查看存储过程2. 删除存储过程3. 调用存储过程4. 创建存储过程 五、控制结构语句1. 判断-if的使用2. 判断-case when的使用3. while 循环4. repeat 循环5. loop 循环 六、数据类型介绍1. 数值类型2. 日期和时间类型3. 字符串类型 七、常用函数1. 数学函数2. 字符串函数3. 日期和时间函数4. 条件判断函数5. 系统信息函数6. 加密和压缩函数7. 聚合函数8. 格式或类型转化函数

    MySQL存储过程

    使用环境： 8.0.12 MySQL+win10，不同环境可能会有些许差异。

    一、存储过程与函数

    存储过程类似于函数，把一段代码封装起来，当要执行这一段代码时候，可以通过调用该存储过程来实现，在封装的语句体里面，可以用if/else，case，while等控制结构，可以进行sql编程。与函数不同的是，创建关键字不同，函数使用create function 函数名创建，存储过程使用create procedure 存储过程名字创建。另一方面，存储过程没有返回值，函数最后使用return 变量名返回。

    二、命令行结束标志符号

    默认情况下，mysql的结束标志符delimiter是分号‘;’，输入分号后回车，mysql将会提交命令。在存储过程使用中，默认的结束标志符会与存储过程语句中的分号冲突，导致存储过程语句未执行完就提交，进而就报错了。所以在创建存储过程前，我们一般会声明标志符为其他符号。比如‘$’，当然其他符号也可以。注意delimiter声明语句后面没有分号‘;’delimiter $

    三、关于变量的使用

    1. 局部变量

    在存储过程中，我们使用declare来声明局部变量，该语句只能用在存储过程begin…end复合语句里，且只能在语句的开头。变量设置默认值，我们可以使用default子句（值可以是常数，也可以指定为一个表达式），如果没有默认值，变量初始值为null。# 声明i变量为int类型，初始值为10. declare i int default 10 先定义再使用，未定义的变量，select返回空。局部变量仅存在于存储过程中，当存储过程语句结束后，局部变量消失。相对地，用户变量可以用在存储过程内部和外部。

    2. 用户变量

    局部变量只有变量名字，没有@符号；用户变量名前有@符号。存储过程使用，我们也可以传入事先set的用户变量，用户变量命名以‘@’开头。set @a = 10 delimiter $ create procedure p1(inout num int) begin num = num + 1 select num; end$ delimiter ; #我们再调用存储过程 call p1(@a) select @a; -- 11 用户变量的参数类型：in、out、inout 如参数类型命名，我们解释下其区别： in：储存过程可以调用，但储存过程调用修改该变量后，该变量是不被修改的，只进不出。我们事先set用户变量并设置某个值，存储过程中可以修改，但修改仅作用于存储过程内部。存储过程结束后，返回的还是原来的值。inout：可以传入，存储过程中可以修改，修改结果对于存储过程结束后同样有效。比如上面的案例，传入时用户变量@a 是10，存储过程+1变成了11，再输出这个参数时就是11了。可进可出。out：变量传入存储过程为null（可理解为变量原来的值是不能传入存储过程的），但存储过程修改后，变量的修改于存储过程结束后同样有效，只出不进。

    3. 存储过程结果赋值到变量的几种方法

    方法一 delcare num int default 0 select count(*) into num from table_name; select num; 方法二 #我们使用下用户变量做个示例，注意一点是该方法的查询语句外边徐添加括号。 set @num = (select count(*) into num from table_name); select @num; 方法三 # 多结果赋值到变量 select min(sales), max(sales) into @min, @max from table_name group by month(date);

    四、存储过程的查看、删除、调用、创建

    1. 查看存储过程

    show procedure status; #查看创建procedure_name存储过程的内容。 show create procedure procedure_name;

    2. 删除存储过程

    drop procedure if exists procedure_name

    3. 调用存储过程

    call procedure_name() #如果传入变量@a call procedure_name(@a)

    4. 创建存储过程

    create procedure 过程名([[in|out|inout] 参数名 数据类型[,[in|out|inout]参数名 数据类型…]])过程体 过程体以begin开头，end结尾，示例：

    delimiter $ -- 修改结束标志符 set @n := 10 -- 设置用户变量@n，赋值10 create procedure p_test(in n int) -- 变量n参数为in，只进不出。 declare i int default 10; -- 声明局部变量i，设置默认值10 declare num varchar(10) ; -- delcare语句放在begin-end复合语句开头。 set num := "num变量赋值" ; -- 为变量num赋值 set n := 100 + n; -- 我们尝试将用户变量修改为100 + n begin -- 存储过程开始 select i, num, n; -- 存储过程内部，该处n修改有效。 end$ -- 存储过程结束 delimiter ; -- 还原结束标志符 call p_test(@n) select @n; -- 因为存储过程n参数是in，只进不出，该处@n返回还是原来的值：10

    五、控制结构语句

    大家在使用的时候，一定要注意语句各个地方的分号

    1. 判断-if的使用

    set @num = 75; delimiter $ create procedure p1(in num int) begin declare score char(3); if num >80 then set score = "优秀"; elseif num >= 60 then set score = "及格"; else set score = "不及格"; end if; select score; end$ delimiter ; call p1(@num);

    2. 判断-case when的使用

    set @num = 75; delimiter $ create procedure p2(in num int) begin declare score char(3); case num when ">80" then set score = "优秀"; when ">= 60" then set score = "及格"; else set score = "不及格"; end case; select score; end$ call p2(@num)$

    3. while 循环

    set @num = 10; delimiter $ create procedure p3(n smallint) begin declare i int; declare s float; set s = 0; set i = 0; while i < n do set i = i + 1; set s = s + i; end while; select s; end$ call p3(@num)$

    4. repeat 循环

    until+条件：结束循环

    delimiter $ set @n = 10$ create procedure p4(n smallint) begin declare i int; declare s int; set s = 0; set i = 0; repeat set i = i + 1; set s = s + i; until i = 10 end repeat; select s; end$ call p4(@n)$

    5. loop 循环

    这里的循环名称‘myloop’可以自定义，leave表示结束循环。

    delimiter $ set @n = 10$ create procedure p5(n smallint) begin declare i int; declare s int; set s = 0; set i = 0; myloop : loop set i = i + 1; set s = s + i; if i = 10 then leave myloop; end if; end loop myloop; select s; end$ call p5(@num)$

    六、数据类型介绍

    这里直接转载：https://www.runoob.com/mysql/mysql-data-types.html 感谢分享~~

    MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

    MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

    1. 数值类型

    MySQL支持所有标准SQL数值数据类型。

    这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

    关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

    BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

    作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

    类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

    2. 日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

    每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

    TIMESTAMP类型有专有的自动更新特性，将在后面描述。

    类型 大小(字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4

    1970-01-01 00:00:00/2038

    结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

    YYYYMMDD HHMMSS

    混合日期和时间值，时间戳

    3. 字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

    类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据

    CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

    BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

    BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

    有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

    七、常用函数

    转载出处：https://www.cnblogs.com/noway-neway/p/5211401.html 感谢分享~

    mysql内置函数列表可以从mysql官方文档查询，这里仅分类简单介绍一些可能会用到的函数。

    1. 数学函数

    abs(x) pi() mod(x,y)：余数 sqrt(x)：开平方根 ceil(x)或者ceiling(x) rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数 round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20 truncate(x, D):截断至保留D位小数，D可以为负数， 如trancate(19,-1)返回10 sign(x): 返回x的符号，正负零分别返回1， -1， 0 pow(x,y)或者power(x,y) exp(x)：e^x log(x)：自然对数 log10(x)：以10为底的对数 radians(x):角度换弧度 degrees(x):弧度换角度 sin(x)和asin(x): cos(x)和acos(x): tan(x)和atan(x): cot(x):

    2. 字符串函数

    char_length(str):返回str所包含的字符数，一个多字节字符算一个字符 length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节 concat(s1, s1, ...): 返回连接参数产生的字符串 concat_ws(x, s1, s2, ...): 使用连接符x连接其他参数产生的字符串 INSERT(str,pos,len,newstr):返回str,其起始于pos，长度为len的子串被newstr取代。 1. 若pos不在str范围内，则返回原字符串str 2. 若str中从pos开始的子串不足len,则将从pos开始的剩余字符用newstr取代 3. 计算pos时从1开始，若pos=3,则从第3个字符开始替换 lower（str)或者lcase(str): upper(str)或者ucase(str): left(s,n):返回字符串s最左边n个字符 right(s,n): 返回字符串最右边n个字符 lpad(s1, len, s2): 用s2在s1左边填充至长度为len, 若s1的长度大于len,则截断字符串s1至长度len返回 rpad(s1, len, s2): ltrim(s):删除s左侧空格字符 rtrim(s): TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)或TRIM([remstr FROM] str)：从str中删除remstr, remstr默认为空白字符 REPEAT(str,count)：返回str重复count次得到的新字符串 REPLACE(str,from_str,to_str)： 将str中的from_str全部替换成to_str SPACE(N):返回长度为N的空白字符串 STRCMP(str1,str2):若str1和str2相同，返回0， 若str1小于str2, 返回-1， 否则返回1. SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串 LOCATE(substr,str), LOCATE(substr,str,pos),INSTR(str,substr),POSITION(substr IN str): 返回字符串中特定子串的位置，注意这里INSTR与其他函数的参数位置是相反的 REVERSE(str) ELT(N,str1,str2,str3,...)：返回参数strN, 若N大于str参数个数，则返回NULL FIELD(str,str1,str2,str3,...): 返回str在后面的str列表中第一次出现的位置，若找不到str或者str为NULL, 则返回0 FIND_IN_SET(str,strlist)：strlist是由','分隔的字符串，若str不在strlist或者strlist为空字符串，则返回0；若任意一个参数为NULL则返回ＮＵＬＬ MAKE_SET(bits,str1,str2,...): 由bits的作为位图来选取strN参数，选中的参数用','连接后返回

    3. 日期和时间函数

    CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为'YYYY-MM-DD'; 若+0则返回YYYYMMDD UTC_DATE, UTC_DATE():返回当前世界标准时间 CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间， 格式为'HH:MM:SS' 若+0则返回 HHMMSS UTC_TIME, UTC_TIME([fsp]) CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]), LOCALTIME, LOCALTIME([fsp]), SYSDATE([fsp]), NOW([fsp]): 用于获取当前的时间日期，格式为'YYYY-MM-DD HH:MM:SS'，若+0则返回YYYYMMDDHHMMSS UTC_TIMESTAMP, UTC_TIMESTAMP([fsp]) UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回一个unix时间戳（'1970-01-01 00:00:00' UTC至今或者date的秒数），这实际上是从字符串到整数的一个转化过程 FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：从时间戳返回'YYYY-MM-DD HH:MM:SS' 或者YYYYMMDDHHMMSS，加入format后根据所需的format显示。 MONTH(date) MONTHNAME(date) DAYNAME(date) DAY(date)，DAYOFMONTH(date)：1-31或者0 DAYOFWEEK(date)：1-7==>星期天-星期六 DAYOFYEAR(date)： 1-365（366） WEEK(date[,mode])：判断是一年的第几周，如果1-1所在周在新的一年多于4天，则将其定为第一周；否则将其定为上一年的最后一周。mode是用来人为定义一周从星期几开始。 WEEKOFYEAR(date)：类似week(date,3)，从周一开始计算一周。 QUARTER(date)：返回1-4 HOUR(time)：返回时间中的小时数，可以大于24 MINUTE(time)： SECOND(time)： EXTRACT(unit FROM date)：提取日期时间中的要素

    SELECT EXTRACT(YEAR FROM '2009-07-02'); ##2009 SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');##200907 SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');##20102 SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');##123

    TIME_TO_SEC(time) SEC_TO_TIME(seconds)

    TO_DAYS(date): 从第0年开始的天数 TO_SECNDS(expr)：从第0年开始的秒数

    ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days),DATE_ADD(date,INTERVAL expr unit) DATE_SUB(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit) ADDTIME(expr1,expr2) SUBTIME(expr1,expr2)

    SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');##'2008-01-02 01:01:01.000001' SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');##'03:00:01.999997'

    注意：时间日期的加减也可以直接用+/-来进行

    date + INTERVAL expr unit date - INTERVAL expr unit SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;##'2009-01-01 00:00:00' SELECT INTERVAL 1 DAY + '2008-12-31';##'2009-01-01' SELECT '2005-01-01' - INTERVAL 1 SECOND;##'2004-12-31 23:59:59'

    DATE_FORMAT(date,format): DATEDIFF(expr1,expr2):返回相差的天数 TIMEDIFF(expr1,expr2)：返回相隔的时间

    4. 条件判断函数

    IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值 IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2 NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2 CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END 当compare_value=value时返回result CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 当condition为TRUE时返回result

    SELECT CASE 1 WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ ELSE ‘more’ END;##‘one’ SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;##‘true’ SELECT CASE BINARY ‘B’ WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;##NULL

    5. 系统信息函数

    VERSION():返回mysql服务器的版本，是utf8编码的字符串 CONNECTION_ID()：显示连接号（连接的线程号） DATABASE()，SCHEMA()：显示当前使用的数据库 SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串 CHARSET(str) COLLATION(str) LAST_INSERT_ID()：自动返回最后一个insert或者update查询， 为auto_increment列设置的第一个发生的值

    6. 加密和压缩函数

    PASSWORD(str):这个函数的输出与变量old_password有关。 old_password 在mysql5.6中默认为0。 old_password=1时， password(str)的效果与old_password(str)相同，由于其不够安全已经弃用（5.6.5以后）。 old_password=2时，在生成哈希密码时会随机加盐。

    MD5(str):计算MD5 128位校验和，返回32位16进制数构成的字符串，当str为NULL时返回NULL。可以用作哈希密码 SHA1(str), SHA(str)：计算160位校验和，返回40位16进制数构成的字符串，当str为NULL时返回NULL。 SHA2(str, hash_length)：计算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一个参数为待校验字符串，第二个参数为结果的位数（224， 256， 384， 512） ENCRYPT(str[,salt]): 用unix crypt()来加密str. salt至少要有两位字符，否则会返回NULL。若未指定salt参数，则会随机添加salt。

    ECODE(crypt_str,pass_str):解密crypt_str, pass_str用作密码 ENCODE(str,pass_str)：用pass_str作为密码加密str

    DES_ENCRYPT(str[,{key_num|key_str}])：用Triple-DES算法编码str， 这个函数只有在mysql配置成支持ssl时才可用。 DES_DECRYPT(crypt_str[,key_str])

    AES_ENCRYPT(str,key_str[,init_vector]) AES_DECRYPT(crypt_str,key_str[,init_vector])

    COMPRESS(string_to_compress)：返回二进制码 UNCOMPRESS(string_to_uncompress)

    7. 聚合函数

    若在没使用group by时使用聚合函数，相当于把所有的行都归于一组来进行处理。除非特殊说明，一般聚合函数会忽略掉NULL. AVG([DISTINCT] expr): 返回expr的平均值，distinct选项用于忽略重复值 COUNT([DISTINCT] expr)：返回select中expr的非0值个数，返回值为bigint类型 group_concat:连接组内的非空值，若无非空值，则返回NULL

    GROUP_CONCAT([DISTINCT] expr [,expr …]

    [ ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name …]

    MAX([DISTINCT] expr) MIN([DISTINCT] expr)

    SUM([DISTINCT] expr) VAR_POP(expr) VARIANCE(expr)：同VAR_POP(expr)，但是这是标准sql的一个扩展函数 VAR_SAMP(expr) STD(expr): 这是标准sql的一个扩展函数 STDDEV(expr)：这个函数是为了跟oracle兼容而设置的 STDDEV_POP(expr)：这个是sql标准函数

    STDDEV_SAMP(expr)：样本标准差

    8. 格式或类型转化函数

    FORMAT(X,D[,locale])：将数字X转化成'#,###,###.##'格式，D为保留的小数位数 CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串 INET_ATON(expr)：ip字符串转数字 INET_NTOA(expr)：数字转ip字符串 CAST(expr AS type)：转换数据类型 CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等

    Processed: 0.012, SQL: 9