（MySQL）使用数据处理函数_文本，日期时间，数值处理函数

文本处理函数

常见的文本处理函数

函数	说明
Left()	返回串左边的字符
Right()	返回串右边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转化为小写
Upper()	将串转换为大写
LTrim()	去掉串左边的空格
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符

– Left(str,len)
Left()函数是一个字符串函数,它返回具有指定长度的字符串的左边部分
[接收两个参数,str：一个字符串 len:想要截取的长度,是一个正整数]

#案例1：
SELECT LEFT(cust_name,2)
FROM customers;

SELECT cust_name
FROM customers;

#案例2：结果为空
SELECT LEFT('2020-10-12',0);

SELECT LEFT('',4);

#案例3：结果为NULL
SELECT LEFT('2020-10-12',NULL);

SELECT LEFT(NULL,3);

– 返回串的长度 length(str)

SELECT LENGTH(cust_name) AS name_length
FROM customers

– 找出串的一个子串 Locate(substr, string[, pos])

SELECT cust_name
FROM customers
WHERE LOCATE('s', cust_name)>0;
#指定起始位置 Locate(substr, string, pos) 【从string串中的第pos(整数)个位置开始查找是否存在子串substr】
SELECT cust_name
FROM customers
WHERE LOCATE('s', cust_name, 4)>0;

– 将串转换为大写Upper()/将串转换为小写Lower()

SELECT UPPER(cust_name) AS NAME
FROM customers

SELECT LOWER(cust_name) AS NAME
FROM customers

SELECT vend_name, UPPER(vend_name) AS vend_name_up_case
FROM vendors
ORDER BY vend_name;

– 返回串的SOUNDEX值 Soundex(str)

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';

SELECT cust_contact
FROM customers;

SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie');

#SOUNDEX考虑了类似的发音字节和音节,使得能对串进行发音比较而不是字母比较

– 返回子串的字符 SubString(str, pos)【从str串的第4个位置开始提取子字符串,并返回子字符串。若pos为0，则返回空字符串】

SELECT SUBSTRING(cust_name, 4)
FROM customers;

SELECT cust_name
FROM customers;

SELECT SUBSTRING(cust_name, 0)
FROM customers;

日期和时间处理函数

– 案例：检索2005年9月下的所有订单

#一;
SELECT cust_id, order_num
FROM orders
WHERE DATE(order_date)BETWEEN '2005-09-01' AND '2005-09-30';
#二；
SELECT cust_id, order_num
FROM orders
WHERE YEAR(order_date)=2005 AND MONTH(order_date)=9; 

常见的日期和时间处理函数

函数	说明
Now()	返回当前日期和时间
AddDate()	增加一个日期(天，周等)
AddTime()	增加一个时间(时，分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
Month()	返回一个日期的月份部分
DayOfWeek()	返回一个日期对应的星期几
Time()	返回一个日期的时间部分
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Second()	返回一个时间的秒部分
Year()	返回一个日期的年份部分

–查询某日期是星期几

SELECT DAYOFWEEK('2020-10-18') AS 星期;
#DayOfWeek(date) 查询一个日期对应的星期几(1=星期日,2=星期一,...,7=星期六)
SELECT WEEKDAY('2020-10-18') AS 星期;
#WeekDay(date) 返回一个日期对应的星期几(0=星期一,1=星期二,...,6=星期日)

–查询该日期为当月中的第几天(1~31) DayOfMonth(date)

SELECT DAYOFMONTH('2020-10-18');
SELECT DAY('2020-10-18');

–查询date为当年的第几天(1~366) DayOfYear(date)

SELECT DAYOFYEAR('2020-10-18');

–查询某日的月份(1~12) Month(date)

SELECT MONTH('2020-10-18');

–查询某天的星期，返回英文星期几 DAYOFNAME(date)

SELECT DAYNAME('2020-10-18');

–查询某日期的月份，返回英文月份

SELECT MONTHNAME('2020-10-18');

–查询某日期的季度(1~4) quarter(date)

SELECT QUARTER('2020-10-18');

–查询日期的年份

SELECT YEAR('2020-10-18');

–返回当前系统日期+时间 now()

SELECT NOW();

–返回当前系统日期，不包含时间 CurDate()

SELECT CURDATE();

–返回当前时间，不包含日期 CurTime()

SELECT CURTIME();

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份(01,02,03,…11,12)
%c	月份(1,2,3,…,11,12)
%d	日(01,02,…)
%H	24小时制
%h	12小时制
%i	分钟(00,01,02…,59)
%s	秒(00,01,02,…,59)

– str_to_date(str,format):将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('18-10-2020','%d-%m-%Y') AS 默认日期格式;

– Date_Format(date,format): 将日期转换成字符

SELECT DATE_FORMAT('2020/10/18','%y年%m月%d日');

– 案例：查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name, DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

– DATE_ADD(date,INTERVAL expr TYPE) 从日期上加上指定时间间隔(与AddDate()函数同义)

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

– AddDate(expr,days) 将days作为天数添加至expr中

SELECT ADDDATE('2020-10-18',INTERVAL 1 YEAR);

SELECT ADDDATE('2020-10-18', 1);

– DateDiff(date1,date2) 计算两个日期之差(date1-date2)，并返回天数

SELECT DATEDIFF('2020-10-18','1998-05-10');

– Hour(time) 返回一个时间的小时部分

SELECT HOUR(NOW());

– Minute(time) 返回一个时间的分钟部分

SELECT MINUTE(NOW());

– Second(time) 返回一个时间的秒部分

SELECT SECOND(NOW());

数值处理函数

常用的数值处理函数

函数	说明
Pi()	返回圆周率
Rand()	返回一个0~1的随机数
Mod()	返回操作数的余数
Sqrt()	返回一个数的平方根
Abs()	返回一个数的绝对值
Sin()	返回一个角度的正弦
Cos()	返回一个角度的余弦
Tan()	返回一个角度的正切
Exp()	返回一个数的指数值

– Round() 四舍五入
– Round(x,d) 保留d位小数

SELECT ROUND(1.65);
SELECT ROUND(1.257,2);

– ceil() 向上取整(返回大于等于他本身的最小整数)

SELECT CEIL(1.2);
SELECT CEIL(1.00);
SELECT CEIL(-1.2);

– floor() 向下取整(返回小于等于他本身的最小整数)

SELECT FLOOR(-9.99);

– truncate(x,d) 截断,小数点后保留d位

SELECT TRUNCATE(1.789,1);

– mod取余 Mod(a,b)相当于a-a/b*b

SELECT MOD(10,3);
SELECT MOD(-10,3);
SELECT MOD(10,-3);
SELECT 10 % 3;

流程控制函数

1, IF(expr1, expr2, expr3) 相当于 三元操作符

SELECT IF(5<8, '5<8是真', '5<8是假');

SELECT last_name, commission_pct, IF(commission_pct IS NULL, '没奖金', '有奖金') AS 备注
FROM employees;

2, case函数
使用一：

case(变量或表达式)
when 常量1 then 要显示的值1或语句1;
...
when 常量2 then 要显示的值2或语句2;
else 要显示的值n或语句n;
end;

//用法与switch CASE类似

#案例：查询员工的工资,要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资


SELECT salary, department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS total_salary
FROM employees;

使用二

case
when 条件1 then 要显示的值1
when 条件2 then 要显示的值2
...
else 要显示的值n或语句n
end

#案例：查询员工的新工资
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/

SELECT salary, 
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees;