[大数据]SQL语言笔记 第六章 MySQL中最有用的内置函数

使用的是MySQL数据库

1. 数值函数

• 搜索数值函数的完整名单 mysql numeric functions

SELECT
	-- ROUND() 轮函数 对数值四舍五入
	-- 只有一个参数时，对着一个参数值进行四舍五入
	ROUND(5.76), -- 6
	-- 有两个参数时，第二个参数表示保留的小数位数
	ROUND(5.7435, 2), -- 5.74
	
	-- TRUNCATE() 用来截断数字
	-- 第二个参数表示保留的小数位数
	TRUNCATE(5.74635, 2), -- 5.74
	
	-- CEILING() 上限函数 向上取整
	CEILING(5.2), -- 6
	
	-- FLOOR() 地板函数 向下取整
	FLOOR(5.8), -- 5
	
	-- ABS() 计算绝对值
	ABS(-5.2), -- 5.2
	
	-- RAND() 用来生成0-1区间的随机浮点数 包括0不包括1
	-- 执行这个函数每次得到的随机值都不一样
	RAND();

2. 字符串函数

• 搜索字符串函数的完整名单 mysql string functions

SELECT
-- LENGTH() 得到字符串中的字符数
LENGTH('sky'), -- 3

-- UPPER() 将字符串转化成大写
UPPER('sky'), -- SKY

-- LOWER() 将字符串转化成小写
LOWER('Sky'), -- sky

-- 移除字符串中的空格
-- (1) LTRIN() 移除字符串左侧的空白字符或其他预定义字符 left trim
LTRIM('   PINK'), -- PINK 左侧空格已移除
-- (2) RTRIN() 移除字符串右侧的空白字符或其他预定义字符 right trim
RTRIM('RED   '), -- RED 右侧空格已被移除
-- (3) TRIM() 移除字符串前后两侧的空格
TRIM('  blue  '), -- blue 字符串前后两侧的空格已被移除

-- 截取字符串函数
-- (1) LEFT() 有两个参数，第二个参数表示第一个参数从左边截取的字符个数
LEFT('Kindergarten', 4), -- Kind
-- (2) RIGHT() 有两个参数，第二个参数表示第一个参数从右边截取的字符个数
RIGHT('Kindergarten',4), -- rten
-- (3) SUBSTRING() 字符截取函数 得到字符串中任意位置的字符
-- 第二个参数是从第几个位置截取，注意没有第0位；第三个参数是截取的字符个数
SUBSTRING('Kindergarten',4,5), -- derga 从第4个位置向后截取5个字符
-- 第三个参数不写，就是一直截取到最后
SUBSTRING('Kindergarten',3), -- ndergarten

-- LOCATE() 返回第一个字符或者一串字符匹配位置
-- 第一个参数是要查找的字符或者是字符串 不区分大小写
LOCATE('n','Kindergarten'), -- 3 找到第一个n的位在第3个
-- *与编程语言的不同之处，若查找的不存在于字符串中，返回0；编程语言返回-1
LOCATE('q','Kindergarten'), -- 0

-- REPLACE() 替换一个字符或一串字符串
-- 有三个参数，第一个参数是字符串，第二个参数是需要替换的字符串，第三个参数是替换成什么字符串
REPLACE('Kindergarten','garten','garden'), -- Kindergarden

-- CONCAT() 串联两个字符串
CONCAT('first','last'); -- firstlast

3. 日期函数 处理日期和时间

-- 以下这些函数返回的是日期格式
SELECT
-- NOW() 调用当前的日期和时间
NOW(), -- 2021-12-02 15:06:17

-- CURDATE() 只返回当前的日期 当前日期（current date）的缩写
CURDATE(), -- 2021-12-02

-- CURTIME() 只返回当前的时间
CURTIME(); -- 15:06:17
 
 
-- 以下这些函数返回整数值 且NOW函数的返回值是这些函数的参数
SELECT
-- YEAR() 返回参数的年份
-- 这里先调用NOW函数 这个函数返回当前的日期和时间，接着把这个参数传给YEAR函数
YEAR(NOW()), -- 2021
YEAR('2021-12-02'), -- 2021

-- MONTH() 返回参数的月份
MONTH(NOW()), -- 12

-- DAY() 返回参数的日期
DAY(NOW()), -- 2

-- HOUR() 返回参数的时刻
HOUR(NOW()), -- 15
HOUR('01:02:03'), -- 1

-- MINUTE() 返回参数的分钟
MINUTE(NOW()), -- 6

-- SECOND() 返回参数的秒数
SECOND(NOW()); -- 17


 -- 以下函数返回字符串
SELECT
-- DAYNAME() 获取字符串格式的星期数
DAYNAME(NOW()), -- Thursday
DAYNAME('2021-12-02'), -- Thursday

-- MONTHNAME() 获取字符串格式的月份
MONTHNAME(NOW()); -- December
 
-- EXTRACT() 标准SQL语言
-- 调用这个函数，需要先指定单位，如年 月 日期 秒等等
-- 该函数的参数内容解读：YEAR表示获取的单位 FROM关键字 时间日期值
SELECT
EXTRACT(YEAR FROM NOW()), -- 2021
EXTRACT(MONTH FROM '2021-12-02'); -- 12
 
-- 练习：查询今年的所有订单
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW());

4. 格式化日期和时间

• 格式化日期和时间格式的完整名单：mysql date format string

-- 日期格式函数 DATE_FORMAT(日期值, 格式字符串)
SELECT
    DATE_FORMAT(NOW(), '%y'), -- %y显示两位的年份 21
    DATE_FORMAT('2021-12-02','%y'), -- 21
    DATE_FORMAT(NOW(), '%Y'), -- %Y显示四位的年份 2021
    DATE_FORMAT(NOW(), '%m'), -- %m显示数字的月份 12
    DATE_FORMAT(NOW(), '%M'), -- %M显示英文的月份 December
    DATE_FORMAT(NOW(), '%d'), -- %d显示数字的日期 02
    DATE_FORMAT(NOW(), '%D'), -- %D显示的日期英文 2nd
    DATE_FORMAT(NOW(), '%Y %m %d'); -- 2021 12 02
 
-- 时间格式函数 TIME_FORMAT(时间值, 格式字符串)
SELECT
    -- %p代表pm或am根据当前时间显示 没有%P
    -- %h是12小时制，%H是24小时制
    -- %i和%I一样
    TIME_FORMAT(NOW(), '%H:%i %p'); -- 15:27 PM
    -- 这里TIME_FORMAT可以替换成DATE_FORMAT
    -- *注意TIME_FORMAT不可以使用DATE_FORMAT的格式字符串，否则输出的是一堆0

5. 计算日期和时间

• (1) 在日期基础上增加一天或一小时；(2) 计算两个日期的间隔。

-- DATE_ADD() 给日期时间值添加日期或时间
-- 有两个参数，第一个是当前时间，第二个是表达式
SELECT
-- DAY 可以换成 YEAR MONTH HOUR MINUTE SECOND
DATE_ADD(NOW(), INTERVAL 1 DAY), -- 显示的是明天的这个时间
DATE_ADD('2021-12-04 01:02:03', INTERVAL -1 DAY); -- 2021-12-03 01:02:03 可以传递一个负值 显示过去的时间

-- DATE_SUB() 给日期时间值做减法 显示过去的时间直接传递正值
SELECT
DATE_SUB('2021-12-03', INTERVAL 1 DAY); -- 2021-12-02


-- DATEDIFF() 计算两个日期的间隔
-- 返回的是第一个日期减第二个日期的结果
SELECT
DATEDIFF('2021-12-03','2021-11-29'), -- 4
DATEDIFF('2021-11-29','2021-12-03'), -- -4
-- *注意返回的是天数的间隔，不考虑时间
DATEDIFF('2021-12-03 09:00','2021-11-29 05:00'); -- 4
 
-- TIME_TO_SEC() 计算时间间隔
SELECT
-- (1) 计算当天零点到现在或当天某个时间的秒数
TIME_TO_SEC(NOW()),
TIME_TO_SEC('09:00'), -- 32400
-- (2) 计算两个时间的间隔描述
TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02'); -- -120

6. IFNULL 和 COALESCE 函数

-- IFNULL() 用其他值替换空值
-- 第一个参数的值为空，则结果集显示第二个参数
SELECT
	order_id,
	IFNULL(shipper_id,'Not assigned') AS shipper
FROM orders;
 
-- COALESCE() 可以有许多的参数 结果集中返回这堆值中的第一个非空值
SELECT
	order_id,
	-- shipper_id值为空，就看comments列是否为空，不为空显示该列值，若为空看status列是否为空，为空则返回Not assigned；
	COALESCE(shipper_id, comments,status,'Not assigned') AS shipper
FROM orders;
 
-- 练习：显示顾客全名和电话，电话为空显示Unknown
SELECT
	CONCAT(first_name,' ',last_name) AS customer,
   IFNULL(phone,'Unknown') AS phone
FROM customers;

7. IF 函数 只允许单一的条件表达式

-- 查询：把订单分成两组类别：Active 和 Archived，之前使用UNOIN实现的，这里使用IF()
SELECT
	order_id,
    -- 如果订单是今年的，类别显示Active，否则显示Archived
    IF(YEAR(order_date) = YEAR(NOW()),
       'Active',
       'Archived') AS category 
FROM orders;
 
-- 练习：产品的订单数大于1，则显示次数为Many times,否则显示Once
SELECT
	p.product_id,
    p.name,
    -- 次数根据产品id和名字分组显示
    COUNT(*) AS orders,
    IF(COUNT(*) > 1,
     'Many times',
     'Once') AS frequency
FROM products p
JOIN order_items oi USING(product_id)
GROUP BY product_id,name;

8. CASE 语句块 针对多个条件表达式返回不同值时使用

-- 写法：
-- CASE
--    WHEN 条件表达式 THEN 结果
--    ... 可以写多个WHEN子句
--    ELSE 结果
-- END
 
-- 查询：今年的订单显示Active,去年的订单显示Last Year,以前的订单显示Archived
-- *注意CASE里面的WHEN与WHEN之间不需要用逗号隔开
SELECT
	order_id,
	CASE
	    WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
	    WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last Year'
	    WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Archived'
       -- ELSE子句可加可不加 如果上面的条件都不为真，就显示ELSE的结果
	    ELSE 'Future'
	-- 使用END关键字关闭CASE语句块
	END AS category
FROM orders;