mysql-(单元函数/日期时间类型)

#第七章——单行函数
#1.数值函数
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-42.43),FLOOR(32.99),
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,1), ROUND (RAND(),5),ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;
SELECT TRUNCATE(123.456,0), TRUNCATE(123.696,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),
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,10,8)
FROM DUAL;

#2.字符串函数
SELECT ASCII(‘abc’), CHAR_LENGTH(‘hello’),CHAR_LENGTH(‘我们’),
LENGTH(‘HELLO’),LENGTH(‘我们’)
FROM DUAL;

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’,’lol’,’mmm’)
FROM DUAL;
#upper:转换大写
#loser:转换小写
SELECT UPPER(‘hello’),LOWER(‘Hello’)
FROM DUAL;

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name) = ‘King’;

#left(’‘,x):从左边开始截取x位
#right(’‘,x):从右边开始截取x位
SELECT LEFT(‘hello’,2),RIGHT(‘hello’,3),RIGHT(‘hello’,13)
FROM DUAL;
#lpad(变量,多少位,‘用什么补齐’):从左边开始补位,右对齐效果
#rpad(变量,多少位,‘用什么补齐’):从右边开始补位,左对齐效果
SELECT employee_id,last_name,LPAD(salary,10,’*’)
FROM employees;
SELECT employee_id,last_name,RPAD(salary,10,’*’)
FROM employees;

#trim:去掉字符串首尾空格
#ltrim:去掉字符串左侧的空格
#rtrim:去掉字符串右侧的空格
SELECT LENGTH(TRIM(‘   h el lo   ‘))
FROM DUAL;
#repeat(str,x):重复x次
#space(x):创建x个空格
#strcmp(”,”):判断哪个字符串大
SELECT REPEAT(‘hello’,4),LENGTH(SPACE(5)),STRCMP(‘abc’,’abd’)
FROM DUAL;
#substr(‘str’,x,y):从x开始截取y个字符
#locate(‘x’,’xxxx’):返回x在字符串中首次出现的位置
SELECT SUBSTR(‘hello’,2,2),LOCATE(‘lll’,’hello’)
FROM DUAL;

#ELT():返回指定位置的字符串
#filed():返回字符串s在字符串列表中第一次出现的位置
#find_in_set():返回字符串s1在字符串s2中出现的位置,其中,字符串s2是一个以逗号分割的字符串
SELECT ELT(2,’a’,’b’,’c’,’d’),FIELD(‘mm’,’gg’,’jj’,’mm’,’dd’,’mm’),
FIND_IN_SET(‘mm’,’gg,mm,jj,dd,mm,gg’)
FROM DUAL;

#reverse:反转
#NULLIF(value1,value2):如果两个值相等则返回null,否则返回value1
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()+0,NOW()+0
FROM DUAL;

#3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(‘2021-10-01 12:12:32’),
FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(‘2021-10-01 12:12:32’),
FROM_UNIXTIME(1645691732),FROM_UNIXTIME(1633061552)
FROM DUAL;

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

SELECT YEAR(NOW()),MONTH(SYSDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(SYSDATE()),SECOND(NOW())
FROM DUAL;

SELECT MONTHNAME(‘2022-2-24’),DAYNAME(‘2022-2-24’),WEEKDAY(‘2022-2-24’),
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 ‘2021-05-12’)
FROM DUAL;

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR FROM NOW()),EXTRACT(MONTH FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(YEAR_MONTH FROM NOW())
FROM DUAL;
#3.5 时间和秒钟转换的函数

SELECT TIME_TO_SEC(NOW()),
SEC_TO_TIME(66400)
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 col1,DATE_ADD(‘2021-10-21 23:32:12’,INTERVAL 1 SECOND) AS col2,
ADDDATE(‘2021-10-21 23:32:12’,INTERVAL 1 SECOND) AS col3,
DATE_ADD(‘2021-10-21 23:32:12’,INTERVAL ‘1_1’ MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL ‘1_1’ YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30), SUBTIME(NOW(),’1:1:3′),DATEDIFF(NOW(),’2021-2-24′),
TIMEDIFF(NOW(),’2022-2-24 17:45:10′),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 *
FROM employees
WHERE hire_date = ‘1993-01-13’;

#格式化
SELECT DATE_FORMAT(CURDATE(),’%Y-%M-%D’),
DATE_FORMAT(NOW(),’%Y-%m-%d’),TIME_FORMAT(CURTIME(),’%H:%i:%S’),
DATE_FORMAT(NOW(),’%Y-%M-%D %H:%i:%S %W %T %r’)
FROM DUAL;

#解析:格式化的逆过程
SELECT STR_TO_DATE(‘2021-October-25th 11:37:30 Monday 1′,’%Y-%M-%D %h:%i:%S %W %w’)
FROM DUAL;

SELECT STR_TO_DATE(‘2022-February-24th 20:33:28 Thursday 20:33:28 08:33:28 PM’,’%Y-%M-%D %H:%i:%S %W %T %r’)
FROM DUAL;

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

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

 

给TA打赏
共{{data.count}}人
人已打赏
共享资源知识碎片

酸性风格素材分享

2022-1-17 18:16:24

知识碎片

水仙花数

2022-3-3 19:54:35

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧