MySQL学习笔记七：常用函数

一、字符串函数

1.1、CONCAT(str1,str2,...)

作用：将传入的字符连接成一个字符串，任何字符与null进行连接结果都是null。

SELECT CONCAT(`name`,'-',sex) FROM study11;

1.2、INSERT(str,pos,len,newstr)

作用：将字符串str从pos位置开始len个字符长的子串，替换为指定的字符newstr。

SELECT INSERT('ABCDEFG',2,3,'XXX');

说明：SQL Server中对应的函数是STUFF。

SELECT STUFF('ABCDEFG',2,3,'XXX')

1.3、LOWER(str)

作用：将字符串转成小写。

SELECT LOWER('ABC');

1.4、UPPER(str)

作用：将字符串转成大写。

SELECT UPPER('abc');

1.5、LEFT(str,len)

作用：返回字符串str最左边的len个字符。

SELECT LEFT('abc',2);

1.6、RIGHT(str,len)

作用：返回字符串str最右边的len个字符。

SELECT RIGHT('abc',2);

1.7、LPAD(str,len,padstr)

作用：用字符串padstr对str最左边进行填充，直到总长度达到len个字符为止。

SELECT LPAD('abc',10,'def');

说明：SQL Server中没有对应的函数。

1.8、RPAD(str,len,padstr)

作用：用字符串padstr对str最右边进行填充，直到总长度达到len个字符为止。

SELECT RPAD('abc',10,'def')；

 说明：SQL Server中没有对应的函数。

1.9、LTRIM(str)

作用：去除字符串当中最左侧的空格。

SELECT LTRIM('   abc');

1.10、RTRIM(str)

作用：去除字符串当中最右侧的空格。

SELECT RTRIM('abc   ');

1.11、TRIM([remstr FROM] str)

完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

作用：返回字符串str，其中所有remstr前缀和/或后缀都已被去除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的，则假设为BOTH。remstr为可选项，在未指定情况下，可去除空格。

1）去除两侧空格。

SELECT TRIM('   abc   ');

2）去除两侧'x'字符。

SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');

3）去除左侧'x'字符。

SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');

4）去除右侧'x'字符。

SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx');

5）去除右侧'xyz'字符串。

SELECT TRIM(TRAILING 'xyz' FROM 'barxyzxyz');

 说明：SQL Server中没有对应的函数。

1.12、REPEAT(str,count)

作用：返回str重复count次的结果。

SELECT REPEAT('abc',3);

 说明：SQL Server中没有对应的函数。

1.13、REPLACE(str,from_str,to_str)

作用：用字符串to_str替换字符串str中所有出现的字符串from_str。

SELECT REPLACE('mysql','my','hello my');

1.14、SUBSTRING(str FROM pos FOR len)

作用：返回字符串str中第pos位置起len个字符长度的字符。

SELECT SUBSTRING('abc',2,2);

二、数值函数

2.1、ABS(X)

作用：返回X的绝对值。

SELECT ABS(-1);

2.2、CEILING(X)

作用：小数不为零部分向上取整，即向上取最近的整数。

SELECT CEILING(1.1);

2.3、FLOOR(X)

作用：小数不为零部分向下取整，即向下取最近的整数。

SELECT FLOOR(2.3);

2.4、MOD(N,M)

作用：返回N/M的模，即求余。

SELECT MOD(5,2);
SELECT 5%2;

说明：SQL Server中没有对应的函数，只能通过%求余。

SELECT 5%2

2.5、RAND()

作用：返回0-1内容的随机值。

SELECT CEILING(RAND()*10);

三、日期和时间函数

3.1、CURDATE()

作用：返回当前日期，只包含年月日。

SELECT CURDATE();

说明：SQL Server中没有对应的函数。

3.2、CURTIME()

作用：返回当前时间，只包含时分秒。

SELECT CURTIME();

说明：SQL Server中没有对应的函数。

3.3、NOW()

作用：返回当前日期和时间，年月日时分秒都包含。

SELECT NOW();

说明：SQL Server中对应的函数是GETDATE。

SELECT GETDATE()

3.4、UNIX_TIMESTAMP()

作用：

SELECT UNIX_TIMESTAMP();

说明：SQL Server中没有对应的函数。

3.5、FROM_UNIXTIME(unix_timestamp)

作用：

SELECT FROM_UNIXTIME(1599560172);

说明：SQL Server中没有对应的函数。

3.6、WEEK(date)

作用：返回当前是一年中的第几周。

SELECT WEEK(NOW());

说明：SQL Server中对应的函数是DATEPART。

SELECT DATEPART(WEEK,GETDATE())

3.7、YEAR(date)

作用：返回所给日期是哪一年。

SELECT YEAR(NOW());

3.8、HOUR(time)

作用：返回当前时间的小时。

SELECT HOUR(NOW());

说明：SQL Server中对应的函数是DATEPART。

SELECT DATEPART(HOUR,GETDATE())

3.9、MINUTE(time)

作用：返回当前时间的分钟。

SELECT MINUTE(NOW());

说明：SQL Server中对应的函数是DATEPART。

SELECT DATEPART(MINUTE,GETDATE())

3.10、DATE_FORMAT(date,format)

作用：用于以不同的格式显示日期/时间数据。

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%Y%m%d');
SELECT DATE_FORMAT(NOW(),'%y%m%d');

说明：SQL Server中对应的函数是CONVERT。

SELECT CONVERT(VARCHAR(10),GETDATE(),120)
SELECT CONVERT(VARCHAR(10),GETDATE(),112)
SELECT CONVERT(VARCHAR(10),GETDATE(),12)

3.11、DATE_ADD(date,INTERVAL expr unit)

作用：向日期添加指定的时间间隔。

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

说明：SQL Server中对应的函数是DATEADD。

SELECT DATEADD(DAY,3,GETDATE())
SELECT DATEADD(MONTH,1,GETDATE())

3.12、DATEDIFF(expr1,expr2)

作用：返回两个日期之间的天数。

SELECT DATEDIFF('2020-06-06',NOW());

说明：SQL Server中对应的函数是DATEDIFF，不过结果是相反的。

SELECT DATEDIFF(DAY,'2020-06-06',GETDATE())

四、流程函数

4.1、IF(expr1,expr2,expr3)

作用：如果expr1是真，返回expr2，否则返回expr3。

SELECT IF(2>3,TRUE,FALSE);

说明：SQL Server中对应的函数是IIF。

SELECT IIF(2>3,1,0)

4.2、IFNULL(expr1,expr2)

作用：如果expr1不为空，返回expr1，否则返回expr2。

SELECT IFNULL('abc','def');
SELECT IFNULL(NULL,'def');

说明：SQL Server中对应的函数是ISNULL。

SELECT ISNULL('abc','def')
SELECT ISNULL(NULL,'def')

4.3、CASE WHEN THEN END

作用：查询满足多种条件的情况。

/*写法一*/
#用户变量，需使用@符号，也可以定义为SELECT @sex:='male';
SET @sex='male';
SELECT CASE @sex
    WHEN 'male' THEN
        '男'
    ELSE
        '女'
END AS '性别';

/*写法二*/
SET @score=90;
SELECT 
    CASE 
        WHEN @score BETWEEN 90 AND 100 THEN 'A+'
        WHEN @score BETWEEN 80 AND 89 THEN 'A'
        WHEN @score BETWEEN 60 AND 79 THEN 'B'
        ELSE 'C'
    END AS '评级';

五、其它常用函数

5.1、DATABASE()

作用：返回当前数据库名。

SELECT DATABASE();

说明：SQL Server中对应的函数是DB_NAME。

--方法一
SELECT DB_NAME()
--方法二
SELECT NAME FROM MASTER..SYSDATABASES WHERE DBID=(SELECT DBID FROM MASTER..SYSPROCESSES WHERE SPID = @@SPID)

5.2、VERSION()

作用：返回当前数据库版本。

SELECT VERSION();

5.3、USER()

作用：返回当前登录用户名。

SELECT USER();

说明：SQL Server中对应的函数是SUSER_NAME。

SELECT SUSER_NAME()

5.4、PASSWORD(str)

作用：返回str的PASSWORD加密值。

SELECT PASSWORD('abc');

说明：SQL Server中没有对应的函数。

5.5、MD5(str)

作用：返回str的MD5值。

SELECT MD5('abc');

说明：SQL Server中对应的函数是HASHBYTES。

SELECT HASHBYTES('MD5','abc')