MySQL的基本函数

Numeric Functions. 数值函数

1.ROUND函数

SELECT ROUND(5.7365, 2)  -- 四舍五入
SELECT TRUNCATE(5.7365, 2)  -- 截断
SELECT CEILING(5.2)  -- 天花板函数，大于等于此数的最小整数
SELECT FLOOR(5.6)  -- 地板函数，小于等于此数的最大整数
SELECT ABS(-5.2)  -- 绝对值
SELECT RAND()  -- 随机函数，0到1的随机值

2. String Functions. 字符串函数

小结

依然介绍最常用的字符串函数：

SELECT LENGTH('sky')  -- 字符串字符个数/长度（LENGTH）
SELECT UPPER('sky')  -- 转大写
SELECT LOWER('Sky')  -- 转小写

1. LENGTH, UPPER, LOWER
2. TRIM, LTRIM, RTRIM
3. LEFT, RIGHT, SUBSTRING
4. LOCATE, REPLACE, 【CONCAT】

查看全部搜索关键词 'mysql string functions'

长度、转大小写：

SELECT LENGTH('sky')  -- 字符串字符个数/长度（LENGTH）。3
SELECT UPPER('sky')  -- 转大写
SELECT LOWER('Sky')  -- 转小写

用户输入时时常多打空格，下面三个函数用于处理/修剪（trim）字符串前后的空格，L、R 表示 LEFT、RIGHT：

SELECT LTRIM('  Sky')
SELECT RTRIM('Sky  ')
SELECT TRIM(' Sky ')

切片：

-- 取左边，取右边，取中间
SELECT LEFT('Kindergarden', 4)  -- 取左边（LEFT）4个字符
SELECT RIGHT('Kindergarden', 6)  -- 取右边（RIGHT）6个字符
SELECT SUBSTRING('Kindergarden', 7, 6)  
-- 取中间从第7个开始的长度为6的子串（SUBSTRING）
-- 注意是从第1个（而非第0个）开始计数的
-- 省略第3参数（子串长度）则一直截取到最后

定位：

SELECT LOCATE('gar', 'Kindergarden')  -- 定位（LOCATE）首次出现的位置
-- 没有的话返回0（其他编程语言大多返回-1，可能因为索引是从0开始的）
-- 这个定位/查找函数依然是不区分大小写的

替换：

SELECT REPLACE('Kindergarten', 'garten', 'garden')
-- 第一个是要操作的词，第二个是要更换的词，第三个是换成后的词

连接： 将表中，两列连接在一起。 使用concat()

USE sql_store;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
-- concatenate v. 连接
FROM customers

MySQL中的日期函数

1. NOW, CURDATE, CURTIME
2. YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYNAME, MONTHNAME
3. EXTRACT(单位 FROM 日期时间对象)， 如 EXTRACT(YEAR FROM NOW())

当前时间：

SELECT NOW()  -- 2020-09-12 08:50:46
SELECT CURDATE()  -- current date, 2020-09-12
SELECT CURTIME()  -- current time, 08:50:46

以上函数将返回时间日期对象

提取时间日期对象中的元素：

SELECT YEAR(NOW())  -- 2020

还有MONTH, DAY, HOUR, MINUTE, SECOND。

以上函数均返回整数，还有另外两个返回字符串的：

SELECT DAYNAME(NOW())  -- Saturday
SELECT MONTHNAME(NOW())  -- September

标准SQL语句有一个类似的函数 EXTRACT()，若需要在不同DBMS中录入代码，最好用EXTRACT()：

SELECT EXTRACT(YEAR FROM NOW())

当然第一参数也可以是MONTH, DAY, HOUR ……
总之就是：EXTRACT(单位 FROM 日期时间对象)

练习

返回【今年】的订单

用时间日期函数而非手动输入年份，代码更可靠，不会随着时间的改变而失效

select 
	*
from orders
where year(order_date) = year(now()) -4

4. 格式化日期和时间

小结

DATE_FORMAT(date, format) 将 date 根据 format 字符串进行格式化。

TIME_FORMAT(time, format) 类似于 DATE_FORMAT 函数，但这里 format 字符串只能包含用于小时，分钟，秒和微秒的格式说明符。其他说明符产生一个 NULL 值或0。

SELECT DATE_FORMAT(NOW(), '%M %d, %Y')  -- September 12, 2020
-- 格式说明符里，大小写是不同的，大写的占4位，小写是2位 这是目前SQL里第一次出现大小写不同的情况
SELECT TIME_FORMAT(NOW(), '%H:%i %p')  -- 11:07 AM

-- H for hour ,  i for minutes,  p for. pm or am

5. 计算日期和时间

Calculating Dates and Times (3:08)

小结

有时需要对日期事件对象进行运算，如增加一天或算两个时间的差值之类，介绍一些最有用的日期时间计算函数：

1. DATE_ADD, DATE_SUB
2. DATEDIFF
3. TIME_TO_SEC

增加或减少一定的天数、月数、年数、小时数等等

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) 
-- 通过改变day来改变 需要加减的。add 为➕ ，sub为➖
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)

但其实不用函数，直接加减更简洁：

NOW() - INTERVAL 1 DAY
NOW() - INTERVAL 1 YEAR 

计算日期差异

SELECT DATEDIFF('2019-01-01 09:00', '2019-01-05')  -- -4
-- 会忽略时间部分，只算日期差异

借助 TIME_TO_SEC 函数计算时间差异

TIME_TO_SEC：计算从 00:00 到某时间经历的秒数

​```sql
SELECT TIME_TO_SEC('09:00')  -- 32400
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')  -- -120

IFNULL和COALESCE函数

两个用来替换空值的函数：IFNULL, COALESCE. 后者更灵活

案例

将 orders 里 shipper.id 中的空值替换为 'Not Assigned'（未分配）

USE sql_store;

SELECT 
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper
    /* If expr1 is not NULL, IFNULL() returns expr1; 
    otherwise it returns expr2. */
FROM orders

将 orders 里 shipper.id 中的空值替换为 comments，若 comments 也为空则替换为 'Not Assigned'（未分配）

首先先看shipper_id，如果不为空则直接输出，为空输出orders 表中的comments， comments也为空，则输出自己命名的not assigned

USE sql_store;

SELECT 
    order_id,
    COALESCE(shipper_id, comments, 'Not Assigned') AS shipper
    /* Returns the first non-NULL value in the list, 
    or NULL if there are no non-NULLvalues. */
FROM orders

COALESCE 函数是返回一系列值中的首个非空值，更灵活

（coalesce vi. 合并；结合；联合）

练习

返回一个有如下两列的查询结果：
1. customer (顾客的全名)
2. phone (没有的话，显示'Unknown')

select 
	concat(first_name,'',last_name) as customer,
    coalesce(phone, 'Unknown') as phone
from customers

 IF函数

小结

根据是否满足条件返回不同的值:

IF(条件表达式, 返回值1, 返回值2) 返回值可以是任何东西，数值 文本 日期时间 空值null 均可

案例

将订单表中订单按是否是今年的订单分类为active（活跃）和archived（存档），之前讲过用UNION法，即用两次查询分别得到今年的和今年以前的订单，添加上分类列再用UNION合并，这里直接在SELECT里运用IF函数可以更容易地得到相同的结果

select 
	order_id,
    order_date,
    if(order_date > '2019-01-01', 'avtive','archive')
from orders

练习

得到包含如下字段的表：
1. product_id
2. name (产品名称)
3. orders (该产品出现在订单中的次数)
4. frequency (根据是否多于一次而分类为'Once'或'Many times')

select 
	p.product_id,
    p.name,
    count(oi.product_id) as orders,
    
   if(count(oi.product_id)>1, 'Many times', 'Once' ) as frequency
from products p
join order_items oi
using (product_id) 
group by oi.product_id；



SELECT
distinct product_id,
name,
(SELECT
COUNT(order_id)
FROM order_items
WHERE product_id = p.product_id) AS orders,
(select (if(orders >1, 'many times','once'))) as frequency
FROM products p
join order_items
using(product_id)



SELECT
distinct product_id,
name,
(SELECT
COUNT(order_id)
FROM order_items
WHERE product_id = p.product_id) AS orders,
IF((SELECT orders) > 1, 'many times', 'once') AS frequency
FROM products p
join order_items
using(product_id)

 CASE运算符

当分类多余两种时，可以用IF嵌套，也可以用CASE语句，后者可读性更好

CASE语句结构：

CASE 
    WHEN …… THEN ……
    WHEN …… THEN ……
    WHEN …… THEN ……
    ……
    [ELSE ……] （ELSE子句是可选的）
END

案例

不是将订单分两类，而是分为三类：今年的是 'Active', 去年的是 'Last Year', 比去年更早的是 'Achived'：

select 
	order_id,
    case
		when year(order_date) = year(now())-4 then 'actived'
        when year(order_date)= year(now())-5 then 'last year'
        when year(order_date) < year(now())-5 then 'archived'
        else 'future'
	end as category
from orders

ELSE 'Future' 是可选的，实验发现若分类不完整，比如只写了今年和去年的两个分类条件，则不在这两个分类的记录的 category 字段会是 null.

练习

得到包含如下字段的表：customer, points, category（根据积分 <2k、2k~3k（包含两端）、>3k 分为青铜、白银和黄金用户）

之前也是用过 UNION 法，分别查询增加分类字段再合并，很麻烦

select 
	 concat(first_name,'',last_name) as customer,
     points,
     case
		when points> 3000 then 'Gold'
        when points between 2000 and 3000 then 'Sliver'
        WHEN POINTS < 2000 then 'Brown'
	 End as category
from customers
order by points desc;


select 
	concat(first_name,'',last_name),
    points,
    case
		when points < 2000 then 'Bronze'
		when  points >= 2000 and points <=3000 then 'Sliver'
		when points > 3000 then 'Gold'
	End as category
from customers
order by points desc;



select 
	concat(first_name,'',last_name),
    points,
    if(points > 3000,'Gold', if(points between 2000 and 3000,'Sliver', if(points < 2000,'Bronze',null))) as category
from customers
order by points desc

注意：between and  不能用<=. <= 替代， 只能用 points >= 2000 and points <=3000 then 'Sliver'

两个这种 加 and 替代。