MySQL函数和存储过程

MySQL流程控制函数

(1)IF函数。
IF(expr1,expr2,expr3):如果expr1是真,返回expr2,否则返回expr3;
SELECT  name,IF(age>18,'成年','未成年')  FROM  user;
(2)IFNULL函数。
IFNULL(expr1,expr2);如果expr1不是NULL,返回expr1,否则返回expr2;
SELECT  name,IFNULL(age,0)  FROM  user;
(3)NULLIF函数。
NULLIF(expr1,expr2):如果expr1=expr2成立,返回值为NULL,否则返回值为expr1;
SELECT  name,NULLIF(name,loginName)  FROM  user;
(4)CASE函数。
CASE[expro]WHEN[value] THENIresulU …ELSE{default IEND:如果value是真,返回result,否则返回defant); SBLECT name,CASE  sex  WHEN  '女'  THEN  '女生'  WHEN  '男'  THEN  '男生' ELSE  '未知'  END as result  FROM  use;

MySQL常用函数

1.数学函数

1)CEILING(x):返回大于x的最小整数值,它是向上取整。

SELECT CEILING(12.6);


(2)FLOOR(x):返回小于x的最大整数值,它是向下取整。

SELECT FLOOR(12.6);


(3)ROUND(x,y);返回参数x的四舍五入的有y位小数的值,进行四舍五入,保留y位小数。 

SELECT ROUND(56.56847,2);

(4)TRUNCATE(x,y);“返回数学x截短为y位不数的结果,不进行四舍五入,直接保留y位小数。

SELECT TRUNCATE(56.56847,2);


(5)MOD(x,y):返回x/y的模,也是取余数,和x%y是等价的。

SELECT MOD(3,5);
SELECT 3%5;
SELECT 3/5;

 

2.字符串函数

(1)LENGTH(str):获取字符串的长度

SELECT LENGTH('hello');

(2)LOWER(str)、UPPER(str):进行大小写字母的转换

SELECT UPPER('hello');

SELECT LOWER('HELLO');

(3)STRCMP(s),s2):比较两个字符串的大小(开头字母的顺序) hello>yes:1 hello<yes:-1等于:0。                               

SELECT STRCMP('hello','world');

               

 (4)REPLACE(s,s1,s2):替换字符串。

SELECT REPLACE('hello','e','abc');


(5)CONCAT(s1,s2,...,sn):合并拼接字符串。

SELECT CONCAT('hello','world');


(6)CONCAT_WS(sep,s1,s2,灬, sn):使用分隔符合并拼接字符串。

SELECT CONCAT_WS('-','hello','world');


(7)LTRIM(str)、RTRIM(str)、TRIM(str):去除空格。

SELECT LTRIM('    hello');
SELECT RTRIM('hello    ');
SELECT TRIM('    hello     ');


(8)SUBSTRING(s,n, len):截取字符串

 SELECT SUBSTRING('helloworld',2,4);

 

3.日期和时间函数

(1)NOW():当前日期和时间,如2017-11-2923:21:19。

SELECT NOW();


(2)CURDATE():当前日期,如2017-11-29。

SELECT CURDATE();


(3)CURTIME():当前时间,如23:22:49。

SELECT CURTIME();


(4)YEAR(d):提取日期中的年份,如YEAR(2022-03-20)。

SELECT YEAR('2022-03-20');


(5)MONTH(d):提取日期中的导份,如MONTH(2022-03-20)。

SELECT MONTH('2022-03-20');


(6)DAYOFYEAR(d):提取日期里一年中的第几天,如DAYOFYEAR(2022-03-20)。

SELECT DAYOFYEAR('2022-03-20');


(7)DAYOFWEEK(d):提取日期里一星期中的第几天,如DAYOFWEEK(2022-03-20)。

SELECT DAYOFWEEK('2022-03-20');


(8)HOUR(d):提取时间中的小时,如HOUR(11:20)。

SELECT HOUR('11:20');


(9)MINUTE(d):提取时间中的分钟,如MINUTE(11:20)。

SELECT MINUTE('11:20');


(10)SECOND(d):提取时间中的秒数,如SECOND(11:30:59)。     

SELECT SECOND('11:30:59');

             

(11)DATE_ADD():向后推时间。DATE_ADD(NOW(), INTERVAL  3  YEAR )表示当前时间往后推3年;DATE_ADD(NOW(), INTERVAL  3  MONTH )表示当前时间往后推3个月;DATE_ADD(NOW0, INTERVAL  3  DAY )表示当前时间往后推3天。                 

SELECT DATE_ADD(NOW(),INTERVAL 3 YEAR);
SELECT DATE_ADD(NOW(),INTERVAL 3 MONTH);
SELECT DATE_ADD(NOW(),INTERVAL 3 DAY);

 

 

 (12)DATE_SUB():向前推时间。DATE_SUB(NOW(), INTERVAL  3  YEAR )表示当前时间往前推3年;DATE_SUB(NOW(), INTERVAL  3  MONTH )表示当前时间往前推3个月;DATE_SUB(NOW(), INTERVAL  3  DAY )表示当前时间往前推3天。

SELECT DATE_SUB(NOW(),INTERVAL 3 YEAR);
SELECT DATE_SUB(NOW(),INTERVAL 3 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 3 DAY);

 

 

 

4.系统信息函数

函数名称含义示例
VERSION()返回数据库的版本号SELECT  VERSION() ;
CONNECTION_ID()返回服务器的连接数SELECT  CONNECTION _ID();
DATABASE()、SCHEMA()返回当前数据库名SELECT  SCHEMA() ;
USER(),SYSTEM_USER()
SESSION_USER()
返回当前用户、系统用户、回话用户SELECT  USER() ;
CURRENT_USER()返回当前用户SELECT  CURRENT _USER()
CHARSET(str)返回字符串str的字符集SELECT  CHARSET (hello);
COLLACTION (str)返回字符串str的字符排列方式

LAST_INSERT_ID()

返回最近生成的AUTO_ INCREMENET 值SELECT  LAST_INSERT_ID();

5.加密函数

加密函数是MySQL用来对数据进行加密的函数,以保护数据的安全。
(1)PASSWORD(str)加密函数:可以对字符串str进行加密,采用MySQL的SHA1加密方式,生成的是41位字符串,其中*不加入实际的密码运算,加密函数常用于对用户的密码进行加密。

SELECT PASSWORD('123456');


(2)MD5(str)加密函数,可以对字符串str进行散列加密,计算字符串str的MD5校验和,常用于一些需要解密的数据。                            

SELECT MD5('123456');

            

 (3)ENCODE(str,pswd _ str)与DECODE(crypt str,pswd _ str)加解密函数:是一对加密解密函数,加密结果是二进制数,使用BLOB类型的字段进行存储,使用ENCODE(str,pswd _ str)加密函数进行加密,加密后生成解密字符串秘钥,然后使用DECODE(crypt _ str, pswd _ str)和秘钥进行解密。

SELECT ENCODE('zhangsan','key');

       

SELECT DECODE(ENCODE('zhangsan','key'),'key');

      

 (4)AES_ENCRYPT(str,key)与AES_DECRYPT(str,key)加解密函数:是一对加密解密函数,AES_ENCRYPT(str,key)用秘钥key对字符串利用高级加密标准算法进行加密,加密结果是一个二进制字符串,以BLOB类型的字段进行存储;AES_DECRYPT(str, key)用秘钥key对字符串str利用高级加密标准算法解密。         

SELECT AES_ENCRYPT('zhangsan','key');

         

SELECT AES_DECRYPT(AES_ENCRYPT('zhangsan','key'),'key');

 (5)SHA(str)加解密函数:计算字符串str的安全散列算法(SHA)校验和。

SELECT SHA('123456');

 

 6.格式化函数(此处省略)

变量

在MySQL中最常见的变量类型有局部变量和用户自定义变量两种。

1.局部变量 局部变量一般用在sql语句块中,比如存储过程的begin/end。其作用域仅限于该语句块,在该语句块执行完毕后,局部变量就消失了。要定义局部变量必须使用DECLARE来声明,定义的同时可以使用default对局部变量初始化赋值。DECLARE语句格式如下: DECLARE var_name[,…] type [DEFAULT value] value是给变量提供一个默认值,包含在一个DEFAULT子句中,它的值可以被指定为一个表达式,如果没有DEFAULT子句,初始值为NULL。例如: DECLARE num  int  DEFAULT 0;

局部变量使用举例:

DROP PROCEDURE IF EXISTS  p1;
DELIMITER $$
CREATE PROCEDURE  p1()
BEGIN
     DECLARE  num  INT  DEFAULT 3;
     SET num=10;
     SELECT num;
END$$
DELIMITER ;

CALL  p1()

2.用户自定义变量 用户自定义变量的声明以“@”开头,形如:@var_name。为了实现不同SQL语句中进行值的传递,可以把一些数值存储在自定义的用户变量中,不同的SQL语句都可以对它进行访问。用户变量在客户端和数据库的连接建立时被定义,当连接断开时,用户变量将会被释放。用户变量无需用DECLARE关键字进行定义,可以直接使用。例如: SET @c1=1, @c2=2, @c3=4;

用户自定义变量使用举例:

DROP PROCEDURE IF EXISTS p2;
DELIMITER $$
CREATE PROCEDURE p2()
BEGIN
     SET @n1=5;
     SELECT @n1;
END$$
DELIMITER ;

CALL p2();

SET @n1=20;
SELECT @n1;

对用户变量赋值有两种方式,一种是直接用"="号,另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用":="方式,因为在select语句中,"="号被看作是比较操作符。 

SET @n1=20; SELECT @sum:=@n1+1 AS sum1

存储过程

存储过程常用于一些复杂的SQL语句的编写,在开发过程中,如果不使用存储过程,SQL语句的执行要经过几个步骤:先校验SQL语句是否正确、语法是否正确,然后进行SQL语句的编译,最后再执行SQL语句:如果复杂语句经过这几个步骤,运行速度会很慢,而存储过程就是用来解决这个问题的。存储过程是完成特定功能的SQL语句集,经编译后存储在数据库中,在使用的时候直接调用存储过程来执行就可以省略了语法校验、编译的过程,大大提高了SQL语句的执行效率。
存储过程

 

DELIMITER $$
CREATE  
    PROCEDURE `shop`.`getUserInfo`()
    BEGIN
    SELECT * FROM USER;
    END$$
DELIMITER ;

CALL getUserInfo();

(1)DELIMITER:是用来修改SQL语句的结束符,可以将结束符分号(;)修改为其他特殊字符,因为在函数体内容里可以有多条SQL语句,是用分号(隔开的,代表该SQL语句结束,修改后的结束符在函数的末尾添加,代表定义函数完毕,所以要修改结束符。
(2)过程名:使用PROCEDURE来标识存储过程,存储过程的名称应该是合法的标识符,不能与已有的关键字或存储过程名称冲突。一个存储过程是属于某数据库的,可以使用db _ name、procedure _ name的形式执行当前存储过程所属的数据库。
(3)参数类型:OUT参数的值可在存储过程内部被改变,并可返回:INOUT参数在调用时指定,并且可被改变和返回。IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返
(4)BEGIN…END:用来标识过程体的开始和结束,当有多条SQL语句时,可以使用BEGIN…END来标识。
(5)DECLARE:用来声明变量,包括变量名和类型。
(6)用户变量:一般以@开头,如输入参数变量@p_ sex、输出参数变量@p_ count,注意滥用用户变量会导致程序难以理解及管理。
(7)SET:用来设置变量的值。
(8)CALL:用来调用存储过程。
(9)存储特性: characteristic 参数指定存储函数的特性。


查看存储过程
SHOW  FUNCTION  STATUS  like 'procedure _ name';
SHOW  CREATE  FUNCTION  procedure name;
SHOW  PROCEDURE  STATUS  like' getUserCountBySex '; 

SHOW PROCEDURE STATUS LIKE 'p1';

SHOW  CREATE  PROCEDURE  get User Count BySex;                                                

SHOW CREATE  PROCEDURE p1;


  SHOW  PROCEDURE  STATUS 用来查看自定义存储过程的状态,包括所属数据库、类型、函数名称、等改时间等状态的信息; SHOW  CREATE  PROCEDURE 用来查看存储过程的信息,包括存储过程内容等。

修改存储过程
ALTER  PROCEDURE  procedure name[ characteristic .]
characteristic :              ( CONTAINS SQL  |  NO SQL PREADS SQL DATA  I MODIFIES SQL DATA  )  l SQL SECURITY {DEFINER  |  INVOKER}
|  COMMENT  'string'
(1)只能修改存储过程的一些特性,不能修改过程体,如果要修改过程体,就需要先删除存储过程来重新创建。
(2) procedure _ name:存储过程的名称。
(3)  characteristic 参数:指定存储函数的特性。
(4) CONTAINSSQL :表示子程序包含SQL语句,但不包含读或写数据的语句。
(5)NOSQL:表示子程序中不包含SQL语句。
(6) READSSQLDATA :表示子程序中包含读数据的语句。
(7) MODIFIESSQLDATA :表示子程序中包含写数据的语句                                                              (8)SQLSECURITY {DEFINER+INVOKER}:指明谁有权限来执行。
(9)DEFINER:表示只有定义者自己才能够执行。
(10)INVOKER:表示调用者可以执行。
(11)COMMENT'string':表示注释信息。


删除存储过程
DROP  PROCEDURE  procedure _ name  DROPP  ROCEDURE  IF  EXISTS  procedure _ name
在删除存储过程的时候,在PROCEDURE后面是存储过程的名称,不要带括号,也可以使用      IF EXISTS 来判断存储过程是否存在,然后再进行删除。

创建带有IN类型参数的存储过程           

 IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回

DELIMITER $$

CREATE
    PROCEDURE `shop`.`getUserBySex`(IN p_sex VARCHAR(255))
    BEGIN
    SELECT p_sex;
    SELECT * FROM USER WHERE sex=p_sex;
    SET p_sex='未知';
    SELECT p_sex;
    END$$

DELIMITER ;

CALL getUserBySex('男');#调用存储过程

#IN类型的参数,也可以通过定义用户变量@p_sex来传递参数
SET @p_sex='男';
CALL getUserBySex(@p_sex);

 

创建带有IN和OUT类型参数的存储过程     

OUT参数的值可在存储过程内部被改变并可返回

DELIMITER $$

CREATE
    
    PROCEDURE `shop`.`getCountBySex`(IN p_sex VARCHAR(255),OUT p_count INT)
    BEGIN
    SELECT COUNT(*) INTO p_count FROM USER WHERE sex=p_sex;
    END$$

DELIMITER ;

CALL getCountBySex('男',@p_count);
SELECT @p_count;

                                                                                                                          

 创建带有INOUT类型参数的存储过程 

 INOUT参数在调用时指定,并且可被改变和返回

DELIMITER $$

CREATE
    PROCEDURE `shop`.`getUserById`(INOUT p_id INT)
    BEGIN
    SELECT p_id;
    SELECT * FROM USER WHERE id=p_id;
    SET p_id=1000;
    SELECT p_id;
    END$$

DELIMITER ;

SET @p_id=8;
CALL getUserById(@p_id);

 

 

 

 创建IF语句的存储过程

在存储过程中可以使用IF语句进行条件判断,这也是经常会使用的控制语句。下面创建一个存储过程,判断用户的年龄,0~6岁是输出童年,7~17岁是输出少年,18~40岁是输出青年

DELIMITER $$

CREATE
   
    PROCEDURE `shop`.`getTypeByAge`(IN p_age INT)
    BEGIN
    DECLARE c VARCHAR(255) DEFAULT '';
    IF(p_age>0 AND p_age<=6) THEN
    SET c='童年';
    ELSEIF(p_age>7 AND p_age<=17) THEN
    SET c='少年';
    ELSEIF(p_age>18 AND p_age<=40) THEN
    SET c='青年';
    END IF;
    SELECT c;
    END$$

DELIMITER ;

CALL getTypeByAge(5);
CALL getTypeByAge(10);
CALL getTypeByAge(30);

创建CASE语句的存储过程
存储过程可以使用CASE语句来进行条件判断。下面创建一个CASE语句的存储过程,根据传入的类型,输出用户记录的条数,输入0时输出2条记录,输入1时输出5条记录,不满足这两个条件时输出所有记录。

DELIMITER $$

CREATE
    
    PROCEDURE `shop`.`getUserByType`(IN p_type INT)
    BEGIN
    CASE p_type
    WHEN 0 THEN
    SELECT * FROM USER LIMIT 0,2;
    WHEN 1 THEN
    SELECT * FROM USER LIMIT 0,5;
    ELSE
    SELECT * FROM USER;
    END CASE;
    END$$

DELIMITER ;

CALL getUserByType(0);
CALL getUserByType(1);
CALL getUserByType(2);

 

 

 

创建while循环语句的存储过程

下面创建一个存储过程,根据输入参数的值,计算累加和

DELIMITER $$

CREATE
    
    PROCEDURE `shop`.`numSum`(IN n INT)
    BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE s INT DEFAULT 0;
    WHILE i<=n DO
    SET s=s+i;
    SET i=i+1;
    END WHILE;
    SELECT s;
    END$$

DELIMITER ;

CALL numSum(100);

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

老衲只用阿道夫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值