第一部分：Numeric Functions - 数值函数

SELECT ROUND(5.73)

ANSWER: 6

--- 这个函数还有第二个可选参数，可以用来制定四舍五入的精确度

SELECT ROUND(5.73)

ANSWER: 5.7

SELECT ROUND(5.7345， 2)

ANSWER: 5.73

SELECT TRUNCATE(5.7345， 2)

ANSWER: 5.73

SELECT CEILING(5.7)

ANSWER: 6

SELECT CEILING(5.2)

ANSWER: 6

--- CEILING函数会返回大于或者等于这个数字的最小整数

SELECT FLOOR(5.2)

ANSWER: 5

SELECT ABS(5.2)

ANSWER: 5.2

SELECT ABS(-5.2)

ANSWER: 5.2

--- ABS总会返回一个正值

SELECT RAND()

--- 用于生成0-1区间内的随机浮点数

第二部分：String Functions - 字符串值函数

SELECT LENGTH(‘sky’)

ANSWER: 3

--- 我们也有UPPER and LOWER函数，用以将字符串转化成大写和小写字母

SELECT LTRIM(‘   sky’)

ANSWER: sky

SELECT RTRIM(‘sky    ’)

ANSWER: sky

SELECT TRIM(‘   sky   ’)

ANSWER: sky

--- 它会删除所有前导或者尾随空格

SELECT LEFT(‘Kindergarten’, 4)

ANSWER: Kind

SELECT RIGHT(‘Kindergarten’, 6)

ANSWER: garten

SELECT SUBSTRING(‘Kindergarten’, 3, 5)

ANSWER: nderg

--- 这里的第三个参数其实是可选的
--- 如果你不写它
--- 就会返回从其实位置起算到字符串最后的所有字符

SELECT SUBSTRING(‘Kindergarten’, 3)

ANSWER: ndergarten

SELECT LOCATE(‘n’, ‘Kindergarten’)

ANSWER: 3

--- LOCATE函数会返回第一个字符或者一串字符匹配位置
--- 第一个参数是要搜索的字符串
--- 无所谓n或者N，结果都是一样的，这个查找不区分大小写

SELECT LOCATE(‘q’, ‘Kindergarten’)

ANSWER: 0

SELECT LOCATE(‘garten’, ‘Kindergarten’)

ANSWER: 7

--- 我们还可以搜索一串字符
--- 它会返回字符串中garten第一次出现的位置

SELECT REPLACE(‘Kindergarten’, 'garten', 'garden')

ANSWER: Kindergarden

SELECT CONCAT(‘first’, 'last')

ANSWER: firstlast

---

USE sql_store;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers

第三部分：Date Functions in MySQL - MySQL中的日期函数

--- 我们可以调用NOW函数 调用当前的日期和时间
SELECT NOW()

--- 这会把时间去掉，只返回当前日期
SELECT NOW(), CURDATE()

--- 只返回当前时间
SELECT NOW(), CURDATE(), CURTIME()

SELECT YEAR(NOW())

SELECT MONTH(NOW())

SELECT DAY(NOW())

SELECT HOUR(NOW())

SELECT MINUTE(NOW())

SELECT SECOND(NOW())

SELECT DAYNAME(NOW())
--- 调用DAYNAME 来获取字符串格式的星期数
ANSWER: Monday

SELECT MONTHNAME(NOW())
--- 调用MONTHNAME 得到字符串格式的月份
ANSWER: March

SELECT EXTRAC(DAY FROM NOW())
--- 这个函数是标准SQL语言的一部分
SELECT EXTRAC(YEAR FROM NOW())

EXERCISE：

Part3 - Date Functions in MySQL - MySQL中的日期函数

SELECT *
FROM orders
WHERE order_date >= ‘2019-01-01’
--- 修改下这段查询，来得到确切的在当前年下的订单

SOLUTION:

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

第四部分：Formatting Dates and Times - 格式化日期和时间

--- 在MySQL中 日期的表达是以一个四位的代表年份的字符串
--- like '2019-03-11'
--- 但是如果想把这个日期展示给用户看的话 这个格式就不是特别理想了
--- 所以我们有几个更适合用户端的格式化日期和时间的函数
--- 第一个是DATE_FORMAT()
--- 要用到两个参数一个日期值和一个格式化字符串，后者包含了格式化日期的几个构成成分的特殊代码
--- 这里我们可以用'%y'表示两位的年份
SELECT DATE_FORMAT(NOW(), '%y')
ANSWER: 23

SELECT DATE_FORMAT(NOW(), '%Y')
ANSWER: 2023

SELECT DATE_FORMAT(NOW(), '%m %Y')
ANSWER: 01 2023

SELECT DATE_FORMAT(NOW(), '%M %Y')
ANSWER: January 2023

SELECT DATE_FORMAT(NOW(), '%M %d %Y')
ANSWER: January 29 2023

--- 我们还有时间格式函数，思路相似，但是这会用其他的格式说明符
--- 比如 我们可以用"%H"代表小时，然后":"
--- 再加上"%i"代表分钟
--- 还可以再加上"%p"代表pm或者am
SELECT TIME_FORMAT(NOW(), '%H:%i %p')
ANSWER: 20:59 PM

第五部分：Calculating Dates and Times - 计算日期和时间

--- 例如 我们想在日期基础上增加一天或者一个小时
--- 或者计算两个日期的间隔
--- 我们可以使用DATE_ADD函数，给日期时间值增加日期成分
--- 假设我们想在当前日期时间上增加一天
--- 第一个参数就要传递当前日期时间
--- 第二个参数我们要写一段表达式
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)
ANSWER: '2023-01-30 21:17:55'
--- 这样就返回了明天的同一时间
--- 我们也可以在当前日期增加一年，只需要把单位从日改成年
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
ANSWER: '2024-01-29 21:28:34'

--- 如果你想要得到过去的时间可以怎么做
--- 我们可以传递一个负值
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)
ANSWER: '2022-01-29 21:30:24'

--- 或者我们也可以使用DATE_SUB函数
--- 在一个日期时间值上减去日期部分
--- 所以这里只需要传递正值，而不是负值
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
ANSWER: '2022-01-29 21:39:48'

SELECT DATEDIFF('2019-01-05', '2019-01-01')
ANSWER: 4
--- 注意这个函数也只返回天数的间隔，而不是小时或者分钟
--- 即使我们纳入日期时间值也一样
SELECT DATEDIFF('2019-01-05 09:00', '2019-01-01 17:00')
ANSWER: 4

--- 另外要注意的是，因为第一个参数是日期较后的日子
--- 所以ANSWER是一个正数
--- 如果将目前的参数的位置调整，那么ANSWER会是一个负数

SELECT TIME_TO_SEC()
--- 这个函数会返回从零点计算的秒数
SELECT TIME_TO_SEC(‘09:00’)
ANSWER: 32400

SELECT TIME_TO_SEC(‘09:00’) - TIME_TO_SEC(‘09:02’)
ANSWER: -120
--- ANSWER的结果是负值的原因是因为我们先写了靠前的时间
--- 如果对调参数的位置
--- ANSWER的结果就会为正值

第六部分：The INFULL and COALESCE Functions - INFULL和COALESCE函数

USE sql_store;

SELECT *
FROM orders
--- 有些订单没有发货人，shipper_id是空值
--- 假设对于这些订单，我们想让用户看到的是“未分配”标签，而不是“null”

SELECT
    order_id,
    IFNULL(shipper_id, ‘Not assigned’) AS shipper
FROM orders
--- 这里我们可以调用IFNULL函数，把shipper_id替换成别的什么
--- 第二个参数要传递一个字符串

--- 另一个更加巧妙的函数
--- 假设shipper_id是空值，你想要返回注释列的值
--- 然后如果备注也是空值，那就返回‘Not assigned’
SELECT
    order_id,
    COALESCE(shipper_id, comments, ‘Not assigned’) AS shipper
FROM orders
--- 所以我们调用COALESCE函数，并输入一堆值
--- 首先我们想返回shipper_id列的值
--- 如果这个值是空的，就返回注释列的值
--- 如果注释列也是空的，那就返回‘Not assigned’

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

EXERCISE：

Part6 - The INFULL and COALESCE Functions - INFULL和COALESCE函数
-- 在sql_store数据库中
-- 写一段查询 生成这个结果
-- 结果中有两列 customer and phone
-- customer列中展示的是customer的全名
-- 如果没有他们的电话号码，就写‘Unknown’

SOLUTION:

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

第七部分：The IF Functions - IF函数

--- 有时候你会需要测试条件
--- 并根据条件的成立与否 返回不同值
--- 例如 我们来看一下订单表
--- 假设 我们想把订单分成两组类别：“活跃”和“归档”
--- 如果订单是今年的 就放在活跃类别 否则就放在归档类别
--- IF(expression, first, second)
--- 如果expression被判定为真 这个函数就会返回first这个值 否则就会返回第二个值
--- 这可以是任何值 可以是字符串 可以是空值 可以是数字 可以是日期
SELECT
    order_id,
    order_date,
    IF(
    YEAR(order_date) = YEAR(NOW()), 
    'Active', 
    'Archived') AS category
FROM orders

EXERCISE：

Part7 - The IF Functions - IF函数
-- 在sql_store数据库中
-- 写一段查询 生成这个结果
-- 结果中有四列 product_id, name, orders, frequency
-- frequency列是根据我们订单列的内容生成的
-- 如果一个产品被多次订购 次数列里就写‘Many times’ 不然就写‘Once’

SOLUTION:

SELECT 
    product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1, 'Many times', 'Once') AS frequency
FROM products
JOIN order_items USING (product_id)
GROUP BY product_id, name
-- 聚合函数是指对一组值执行计算并返回单一的值的一类函数
-- 它们通常与GROUP BY子句一起使用 将数据集分组为子集

第八部分：The CASE Operator - CASE运算符

SELECT
    order_id,
    IF(
    YEAR(order_date) = YEAR(NOW()), 
    'Active', 
    'Archived') AS category
FROM orders
--- 第七部分学到了IF函数
--- IF函数可以测试一个表达式，并根据表达式判定结果返回不同值
--- 如果我们有好几个表达式需要测试怎么办？
--- 
--- 比如在上面的订单的例子中
--- 假设 如果订单是今年的 就返回‘Active’
--- 假设 如果是去年的 就返回‘Last year’
--- 假设 如果是再早之前下的订单 就返回‘Archived’
--- 所以我们就有三个测试表达式
--- 可是IF函数只允许单一的测试表达式
--- 这种情况下 我们可以用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
--- CASE后面可以是一个或者多个WHEN子句
--- 每个WHEN子句都有一个测试表达式
--- 概括一下就是在多个测试表达式且想要针对每个测试表达式返回不同值的时候可以使用CASE运算符

EXERCISE：

Part8 - The CASE Operator - CASE运算符
-- 之前是根据顾客的积分
-- 写一段查询 生成这个结果
-- 三列分别是：customer、points、category

SOLUTION:

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