MySQL/第七章、单行函数.sql

#第七章、单行函数

#1、数值函数
#基本操作
SELECT ABS(-123),ABS(32),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),MOD(12,5)
FROM DUAL;

#取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

#四舍五入、截断操作
SELECT ROUND(123.456),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),
ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;

SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;

#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;

#角度与弧度的互换
SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90)
FROM DUAL;

SELECT DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;

#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;

#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;

SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4)
FROM DUAL;

#进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8),CONV(10,10,8)
FROM DUAL;

#2、字符串函数
SELECT ASCII('abc'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),LENGTH('hello'),LENGTH('我们')
FROM DUAL;

# xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details"
FROM employees emp JOIN employees mgr
WHERE emp.manager_id = mgr.employee_id;

SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;

#字符串的索引是从1开始的
SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','ll','mmm')
FROM DUAL;

SELECT REPLACE('hello','lol','mmm')
FROM DUAL;

SELECT UPPER('Hello'),LOWER('heLLo')
FROM DUAL;

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = 'king';

SELECT LEFT('hello',2),LEFT('hello',12),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;

#LPAD：实现右对齐效果
#RPAD：实现左对齐效果

SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;

SELECT CONCAT('---',TRIM('   he  l  l o  '),'***'),TRIM('oo' FROM 'ooheollo')
FROM DUAL;

SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abd')
FROM DUAL;

SELECT SUBSTR('hello',2,2),LOCATE('ll','hello')
FROM DUAL;

SELECT ELT(2,'aa','bb','cc','dd'),FIELD('mm','gg','jj','mm','dd','mm'),FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;

#3、日期和时间函数
#3.1、获取日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;

SELECT CURDATE(),CURDATE() + 0,CURTIME(),CURTIME() + 0,NOW(),NOW() + 0
FROM DUAL;

#3.2、日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-11-14 23:45:32'),FROM_UNIXTIME(1669634117),FROM_UNIXTIME(1668440732)
FROM DUAL;

#3.3、获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME())
FROM DUAL;

#以下日期类型存在隐式转换，将字符串转换成日期类型
SELECT MONTHNAME('2022-11-14'),DAYNAME('2022-11-14'),WEEKDAY('2022-11-14'),QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

#3.4、日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2022-11-14')
FROM DUAL;

#3.5、时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(71239)
FROM DUAL

#3.6、计算日期和时间的函数
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR),DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS addDay,DATE_ADD('2022-11-14 23:32:12',INTERVAL 10 SECOND) AS addSecond,ADDDATE('2022-11-14 23:32:12',INTERVAL 10 SECOND) AS addSecond2,DATE_ADD('2022-11-14 23:32:12',INTERVAL '10_10' MINUTE_SECOND) AS addMS,DATE_ADD(NOW(),INTERVAL -1 YEAR) AS addY,DATE_ADD(NOW(),INTERVAL '10_10' YEAR_MONTH) AS addYM
FROM DUAL;

SELECT DATE_SUB('2022-11-14',INTERVAL 28 DAY) AS col1,SUBDATE('2022-11-14',INTERVAL 28 DAY) AS col2,DATE_SUB('2022-11-14 13:32:45',INTERVAL '1_1' DAY_HOUR) AS col3
FROM DUAL;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2022-11-14'),TIMEDIFF(NOW(),'2022-11-14 22:10:10')
FROM DUAL;

SELECT FROM_DAYS(366),TO_DAYS('0000-12-25'),LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

#3.7、日期的格式化与解析
#格式化：日期 ---> 字符串
#解析：	字符串 ---> 日期

#此时我们谈的是日期的显式格式化和解析

#格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D') AS col1,DATE_FORMAT(NOW(),'%y-%m-%d') AS col2,TIME_FORMAT(CURTIME(),'%h:%i:%s') AS col3,DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s %W %w %T %r') AS col4
FROM DUAL;

#解析：格式化的逆过程
SELECT STR_TO_DATE('2022-11-29 10:13:49 Tuesday 2 10:13:49 10:13:49 AM','%Y-%m-%d %h:%i:%s %W %w %T %r') AS col
FROM DUAL

SELECT GET_FORMAT(DATE, 'USA')
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA'))
FROM DUAL;

#4、流程控制函数
#4.1、IF(value,value1,value2)
SELECT last_name,salary,IF(salary > 6000,'高工资','低工资') "details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",salary * 12 * (1+IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;

#4.2、IFNULL(value1,value2)：看做是IF(value,value1,value2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

#4.3、CASE WHEN...THEN...WHEN...THEN...END
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
														 WHEN salary >= 10000 THEN '潜力股'
														 WHEN salary >= 8000 THEN '小屌丝'
														 ELSE '草根' END AS "details",department_id
FROM employees;

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
														 WHEN salary >= 10000 THEN '潜力股'
														 WHEN salary >= 8000 THEN '小屌丝'
														 END AS "details",department_id
FROM employees;

#4.4、CASE...WHEN...THEN...WHEN...THEN...END
#相当于Java的switch…case…
/*
练习1
查询部门号为10,20,30的员工信息，
若部门号为10，则打印其工资的1.1倍，
若部门号为20，则打印其工资的1.2倍，
若部门号为30，则打印其工资的1.3倍,
其他部门，则打印其工资的1.4倍。
*/

SELECT employee_id,last_name,department_id,salary,
CASE department_id
	WHEN 10 THEN salary*1.1
	WHEN 20 THEN salary*1.2
	WHEN 30 THEN salary*1.3
	ELSE salary*1.4 END "details"
FROM employees;


/*
练习2：
查询部门号为10,20,30的员工信息，
若部门号为10，则打印其工资的1.1倍，
若部门号为20，则打印其工资的1.2倍，
若部门号为30，则打印其工资的1.3倍
*/

SELECT employee_id,last_name,department_id,salary,
CASE department_id
	WHEN 10 THEN salary*1.1
	WHEN 20 THEN salary*1.2
	WHEN 30 THEN salary*1.3
	END "details"
FROM employees
WHERE department_id IN(10,20,30);

#5、加密与解密的函数
#PASSWORD()：在MySQL8.0中已被弃用，在MySQL5.7中可正常使用
-- SELECT PASSWORD('mysql')
-- FROM DUAL;

SELECT MD5('mysql'),SHA('mysql')
FROM DUAL;

#ENCODE()/DECODE()：在MySQL8.0中弃用，在MySQL5.7中可正常使用

-- SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
-- FROM DUAL;

#6、MySQL信息函数
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER(),CHARSET('世界杯'),COLLATION('世界杯')
FROM DUAL;

#7、其他函数
#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

SELECT CONV(16,10,2),CONV(8888,10,16),CONV(NULL,10,2)
FROM DUAL;

#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

#BENCHMARK(n,expr)：用于测试表达式的执行效率
SELECT BENCHMARK(1000000,MD5('mysql'))
FROM DUAL;

#CONVERT()：可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;