mosh数据库——第七章

1.数值函数 

 ROUND函数（轮函数）：用来四舍五入数字                                                                       SELECT ROUND (数字，有效小数位)

SELECT ROUND (5.73)
-- 得到结果6
SELECT ROUND (5.73,1)  -- 保留一位小数，做四舍五入
-- 得到结果5.7

TRUACATE函数（截断函数）：用来截断数字                                                                         SELECT ROUND (数字，剩余小数位)

SELECT TRUNCATE(5.7852,2) -- 保留两位小数
-- 得到结果5.78

CEILING函数（上限函数）:  返回大于或等于这个数字的最小整数

SELECT CEILING(5.7)
-- 得到6
SELECT CEILING(5.2)
-- 得到6

FLOOR函数（地板函数）：返回小于或等于这个数字的最大整数

SELECT FLOOR(5.2)
-- 得到5

ABS函数 ：计算绝对值【总是会返回一个正值】

SELECT ABS(-5.2)
-- 得到5.2

RAND函数 ：用来生成0~1之间的随机浮点数

SELECT RAND( )

函数的完整名单：打开浏览器，搜索mysql numeric functions

2.字符串函数

LENGTH函数 : 得到字符串中的字符数

UPPER和LOWER函数 ：将字符串转化成大写和小写字母

删除字符串中不需要的空格

LTRIM(left trim) ：左修正，消除前导空格（移除字符串左侧的空白字符或其他预定义字符）

RTRIM(right trim) : 右修正，消除尾随空格（移除字符串右侧的空白字符或其他预定义字符）

TRIM : 会删除所有前导或者尾随空格

LEFT函数 ：保留字符串左端的几位字符

RIGHT函数 ：保留字符串右端的几位字符

SUBSTR (SUB STRING)函数 : 字符截取函数

LOCATE函数 ：会返回第一个字符或者一串字符匹配位置

REPLACE函数 ：把字符串中的某一部分，替换成我们想要的

CONCAT函数：用来串联两个字符串

3.MY SQL中的日期函数

NOW函数 ：调用当前的日期和时间

CURDATE : 只返回当前日期

CURTIME :  只返回当前时间

我们有一些函数用来提取特定的日期或者时间的构成元素，如：

YEAR函数 ：用以获取当前日期时间并提取年份

MONTH函数 ：用以获取当前日期时间并提取月份

DAY函数:

HOUR函数：

MINUTE函数：

SECOND函数

这些函数会返回整数值，但是还有两种函数能返回字符串

1. DAYNAME函数 ：获取字符串格式的星期数
2. MONTHNAME函数 ：得到字符串格式的月份

除了这些函数，还有EXTRACT函数，这个函数是标准SQL语言的一部分，所以如果你们想要把代码录入别的DBMS，最好用EXTRACT函数

SELECT EXTRACT (想获取的单位   FROM关键词   时间日期值 )

SELECT EXTRACT (DAY FROM NOW ())
-- MONTH YEAR ....

 练习：

SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())

4.格式化日期和时间

在MySQL中日期的表达：是以一个四位的代表年份的字符串加上“-”，两位的月份加上一个 ”-”，最                                           后是两位数的日期  ，如"2024-05-30"

我们有几个更适合用户端的格式化日期和时间的函数

DATE_FORMAT :  要用到两个参数——一个日期值和一个格式字符串

SELECT DATE_FORMAT(NOW(), '%y')-- 得到后2位年份
SELECT DATE_FORMAT(NOW(), '%Y')-- 得到4位年份

同样我们有“%m”表示2位的月份,也可以把它改成大写M得到月份名称

TIME_FORMAT :  要用到两个参数——一个时间值和一串格式字符串

SELECT TIME_FORMAT(NOW(),'%H:%i %p')
-- %p：PM
-- %a：AM

5.计算日期和时间

DATE_ADD函数 ：给日期时间值添加日期成分

DATE_SUB函数 ：给日期时间值添加日期成分

第一个参数要转递当前日期时间，第二个参数我们要写一段表达式

DATEDIFF函数 :  计算两个日期的间隔，输入两个日期，注意这个函数也只返回天数的间隔，而不是小时或分钟，先写时间靠后的会得到正数，反之会得到负数

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
SELECT DATEDIFF('2019-01-05 09:00', '2019-01-01 17:20')

TIME_TO_SEC函数 ：会返回从零点计算的秒数

SELECT time_to_sec('09:00') - time_to_sec('09:08')
-- 得到-480

6.IFNULL和COALESCE函数

ISFULL函数：第一个参数 IS NULL（是空值）,会被替代成第二个参数

USE sql_store;
SELECT
    order_id,
    IFNULL(shipper_id, 'Not assigned') AS shipper -- 第二个参数是我们要传递的字符串
FROM orders

（如果发货人id是空值这个函数会返回“末分配”）

COALESCE函数：如果第一列是空值，先返回第二列的值，如果第二列也是空值，返回第三列的字符串

USE sql_store;
SELECT
    order_id,
    COALESCE(shipper_id, comments，'Not assigned'）AS shipper
FROM orders

假没发货人id是空值，你想要返回注释列的值，然后如果备注也是空值那就返回“末分配”

IFNULL函数里我们可以用其他内容替换空值，而COALESCE函数里我们提供一堆值，这个函数会返回这堆值中的第一个非空值。

SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    COALESCE(phone, 'Unknown') AS phone
FROM customers

7.IF函数

IF(测试表达式，a，b)

如果这个表达式被判定为真，这个函数会返回第一个值a，否则就会返回第二个值b

这可以是任何值 可以是字符串 可以是空值 可以是数字 可以是日期

SELECT
order_id,
order_date,
IF(
	YEAR(order_date) = YEAR(NOW()), -- 第一个为测试表达式
	'Active', -- 第二个是测试表达式为真时的返回值
	'Archived')-- 第三个是测试表达式为假时的返回值
FROM orders

SELECT
order_id,
order_date,
IF(
	YEAR(order_date) = 2017, -- 直接比较年份
	'Active', -- 如果年份是2017，返回'Active'
	'Archived') as category-- 如果年份不是2017，返回'Archived'
FROM orders

SELECT
product_id,
name,
COUNT (*) AS orders,
IF (COUNT(*) >1, 'Many times', 'Once') AS frequence
FROM products
JOIN order_items USING (product_id)

8.CASE运算符

IF函数只允许单一的测试表达式 , 如果我们有好几个表达式需要测试怎么办?

这种情况，我们可以用CASE表达式

CASE

        WHEN   测试表达式   THEN   “返回值1”

        WHEN   测试表达式   THEN   “返回值2”

        ELSE     “返回值3”

END   AS  列别名

1. 打上CASE，紧跟着一个或者多个WHEN子句，每个WHEN子句都有一个测试表达式，然后打上THEN 接着 ‘返回值’,（如果这个表达式判定为真 输入在这里的值就会被返回）
2. 我们可以选择性加上ELSE子句，如果这些条件没有一个是真，就会返回我们在这行输入的条件。
3. 最后我们要用END关键字关闭CASE语句块，再给它一个别名

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 'Future'
	END AS category
FROM orders

SELECT
	CONCAT(first_name, ' ', last_name) AS customer,
	points,
	CASE
		WHEN points > 3000 THEN 'Gold'
		WHEN points >= 2000 THEN 'Silver' 
		ELSE 'Bronze'
	END AS category
FROM customers