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