MySQL函数

#1.统计函数
CREATE TABLE t5(
    `name` VARCHAR(20)
);
INSERT INTO t5 VALUES ('tom');
INSERT INTO t5 VALUES (NULL);

#COUNT(*) COUNT(列)返回非NULL的
SELECT COUNT(*) FROM t5;#2
SELECT COUNT(1) FROM t5;#2
SELECT COUNT(`name`) FROM t5;#1

#SUM(列)求和
SELECT SUM(math) FROM student;

#AVG(列)求平均
SELECT AVG(english + math) FROM student;

#MAX(列)、MIN(列)
SELECT MAX(math),MIN(math) FROM student;

#2.字符串函数
#拼接字符串
SELECT CONCAT(`name`,'的数学成绩是',math) FROM student;
#返回出现的位置,DUAL为亚元表、系统表，可用作测试表
SELECT INSTR(name,'飞') FROM student;
SELECT INSTR('hello world','ll') FROM DUAL;
#转为大写、小写
SELECT UCASE('hello') FROM DUAL;
SELECT LCASE('HELLO') FROM DUAL;
#返回长度[字节]
SELECT LENGTH(`name`) FROM student;
#替换
SELECT REPLACE(`name`,'张飞','赵云'),math FROM student;
#截取字符串SUBSTRING(str,position,length),从str的position开始[从1开始计算]，取length个字符
SELECT SUBSTRING(`name`,1,1) FROM student;

#3.数学函数
#绝对值
SELECT ABS(-1) FROM DUAL;
#10进制->2进制 10进制->8进制 10进制->16进制
SELECT BIN(8) FROM DUAL;
SELECT OCT(8) FROM DUAL;
SELECT HEX(16) FROM DUAL;
SELECT CONV(8,10,2) FROM DUAL;
#通用进制转换
SELECT CONV(8,10,2) FROM DUAL;#10进制->2进制
SELECT CONV(16,16,10) FROM DUAL;#16进制->10进制
#向上取整、向下取整
SELECT CEILING(1.1) FROM DUAL;
SELECT FLOOR(1.1) FROM DUAL;
#保留小数位数(四舍五入)
SELECT FORMAT(3.1415,3) FROM DUAL;
#求余
SELECT MOD(10,3) FROM DUAL;
#返回随机数,RAND([seed]),范围0~1.0, 使如果用RAND(seed) seed不变随机数也不变
SELECT RAND() FROM DUAL;
SELECT RAND(2) FROM DUAL;

#4.日期时间函数
#当前的时间(服务器时间),CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW()
# SYSDATE()返回执行当前函数的时间，而NOW()返回执行SQL语句时的时间
SELECT CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,NOW(),SYSDATE(),SLEEP(2),SYSDATE(),NOW() FROM DUAL;
#获取日期、时间、年、月、日等
SELECT DATE(NOW()) FROM DUAL;
SELECT TIME(NOW()) FROM DUAL;
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
#日期加、减
SELECT DATE_ADD(NOW(),INTERVAL 10 YEAR) FROM DUAL;
SELECT DATE_SUB(NOW(),INTERVAL 10 MINUTE) FROM DUAL;
#计算相差多少天、相差多少时间
SELECT DATEDIFF('2020-01-05 23:23:23','2020-01-01 23:23:21') FROM DUAL;
SELECT TIMEDIFF('2020-01-05 23:23:23','2020-01-01 23:23:21') FROM DUAL;
#返回的是1970-1-1到现在的秒数，在时间开发中，可以存放一个整数表示时间，通过FROM_UNIXTIME()转换为日期
SELECT UNIX_TIMESTAMP() FROM DUAL;
SELECT FROM_UNIXTIME(1632565570,'%Y-%m-%d %h:%i:%s') FROM DUAL;

#5.加密函数
#MD5(str)
SELECT MD5('hello') FROM DUAL;

#6.流程控制函数
#IF(expr1,expr2,expr3):
SELECT IF(TRUE, '重庆', '上海') FROM DUAL;
#IFNULL(expr1,expr2)
SELECT IFNULL(NULL,'重庆') FROM DUAL;
#CASE WHEN
SELECT `name`,(SELECT CASE
                WHEN math >= 80 THEN '优秀'
                WHEN math BETWEEN 60 AND 80 THEN '及格'
                ELSE '不及格' END) AS pass FROM student;