函数与数据类型：让原始值变成可用信息

SELECT
    oi.order_item_id,
    oi.order_id,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.discount_rate
FROM order_items AS oi
JOIN products AS p
  ON p.product_id = oi.product_id
WHERE oi.order_item_id <= 4
ORDER BY oi.order_item_id;

+---------------+----------+----------------------+----------+------------+---------------+
| order_item_id | order_id | product_name         | quantity | unit_price | discount_rate |
+---------------+----------+----------------------+----------+------------+---------------+
|             1 |      101 | 白瓷马克杯           |        1 |      39.90 |        0.0000 |
|             2 |      101 | 原木托盘             |        1 |      89.00 |        0.1124 |
|             3 |      102 | 65W 氮化镓充电器     |        1 |     169.00 |        0.0947 |
|             4 |      102 | 编织数据线           |        1 |      39.00 |        0.1026 |
+---------------+----------+----------------------+----------+------------+---------------+

SELECT
    product_name,
    unit_price,
    unit_price * 0.9 AS vip_price,
    UPPER(status) AS status_code,
    CHAR_LENGTH(product_name) AS name_chars
FROM products
WHERE product_id = 5;

+----------------------+------------+-----------+-------------+------------+
| product_name         | unit_price | vip_price | status_code | name_chars |
+----------------------+------------+-----------+-------------+------------+
| 65W 氮化镓充电器     |     169.00 |   152.100 | 在售        |         10 |
+----------------------+------------+-----------+-------------+------------+

SELECT
    product_id,
    product_name,
    CHAR_LENGTH(product_name) AS name_chars
FROM products
WHERE product_id IN (1, 5, 7)
ORDER BY product_id;

+------------+----------------------+------------+
| product_id | product_name         | name_chars |
+------------+----------------------+------------+
|          1 | 白瓷马克杯           |          5 |
|          5 | 65W 氮化镓充电器     |         10 |
|          7 | 亚麻抱枕             |          4 |
+------------+----------------------+------------+

SELECT
    unit_price * stock AS stock_value
FROM products
WHERE product_id = 5;

+-------------+
| stock_value |
+-------------+
|     5408.00 |
+-------------+

SELECT
    '咖啡豆A' AS sample_text,
    CHAR_LENGTH('咖啡豆A') AS character_count,
    LENGTH('咖啡豆A') AS byte_count;

+-------------+-----------------+------------+
| sample_text | character_count | byte_count |
+-------------+-----------------+------------+
| 咖啡豆A     |               4 |         10 |
+-------------+-----------------+------------+

SELECT
    CONCAT('[', ' 苏小满 ', ']') AS raw_name,
    CONCAT('[', TRIM(' 苏小满 '), ']') AS clean_name,
    CHAR_LENGTH(' 苏小满 ') AS raw_chars,
    CHAR_LENGTH(TRIM(' 苏小满 ')) AS clean_chars;

+-------------+------------+-----------+-------------+
| raw_name    | clean_name | raw_chars | clean_chars |
+-------------+------------+-----------+-------------+
| [ 苏小满 ]  | [苏小满]   |         5 |           3 |
+-------------+------------+-----------+-------------+

SELECT
    product_name,
    LEFT(product_name, 6) AS short_name,
    LOCATE('杯', product_name) AS cup_position,
    REPLACE(product_name, ' ', '·') AS normalized_name
FROM products
WHERE product_id IN (1, 5, 7)
ORDER BY product_id;

+----------------------+------------+--------------+----------------------+
| product_name         | short_name | cup_position | normalized_name      |
+----------------------+------------+--------------+----------------------+
| 白瓷马克杯           | 白瓷马克杯 |            5 | 白瓷马克杯           |
| 65W 氮化镓充电器     | 65W 氮化   |            0 | 65W·氮化镓充电器     |
| 亚麻抱枕             | 亚麻抱枕   |            0 | 亚麻抱枕             |
+----------------------+------------+--------------+----------------------+

SELECT
    customer_id,
    CONCAT(TRIM(customer_name), ' / ', phone) AS contact_label
FROM customers
WHERE customer_id IN (1, 3, 5)
ORDER BY customer_id;

+-------------+--------------------------+
| customer_id | contact_label            |
+-------------+--------------------------+
|           1 | 苏小满 / 13800001001     |
|           3 | NULL                     |
|           5 | 乔木 / 13800001005       |
+-------------+--------------------------+

SELECT
    customer_id,
    CONCAT(
        TRIM(customer_name),
        ' / ',
        COALESCE(phone, '未填写')
    ) AS contact_label
FROM customers
WHERE customer_id IN (1, 3, 5)
ORDER BY customer_id;

+-------------+--------------------------+
| customer_id | contact_label            |
+-------------+--------------------------+
|           1 | 苏小满 / 13800001001     |
|           3 | 白露 / 未填写            |
|           5 | 乔木 / 13800001005       |
+-------------+--------------------------+

SELECT
    customer_id,
    CONCAT_WS(' / ', TRIM(customer_name), phone, city) AS contact_card
FROM customers
WHERE customer_id IN (1, 3, 5)
ORDER BY customer_id;

+-------------+-----------------------------------+
| customer_id | contact_card                      |
+-------------+-----------------------------------+
|           1 | 苏小满 / 13800001001 / 杭州       |
|           3 | 白露 / 成都                       |
|           5 | 乔木 / 13800001005 / 北京         |
+-------------+-----------------------------------+

SELECT
    'XIAOMAN@EXAMPLE.COM' AS raw_email,
    LOWER('XIAOMAN@EXAMPLE.COM') AS normalized_email;

+---------------------+---------------------+
| raw_email           | normalized_email    |
+---------------------+---------------------+
| XIAOMAN@EXAMPLE.COM | xiaoman@example.com |
+---------------------+---------------------+

SELECT
    CAST(128.00 AS DECIMAL(10, 2)) AS price,
    CAST(0.0500 AS DECIMAL(5, 4)) AS discount_rate,
    CAST(243.02 AS DECIMAL(12, 2)) AS order_amount;

+--------+---------------+--------------+
| price  | discount_rate | order_amount |
+--------+---------------+--------------+
| 128.00 |        0.0500 |       243.02 |
+--------+---------------+--------------+

discount_rate DECIMAL(5, 4) NOT NULL DEFAULT 0
    CHECK (discount_rate >= 0 AND discount_rate <= 1)

SELECT
    order_item_id,
    quantity,
    unit_price,
    discount_rate,
    quantity * unit_price AS original_amount,
    quantity * unit_price * (1 - discount_rate) AS discounted_amount
FROM order_items
WHERE order_item_id = 3;

+---------------+----------+------------+---------------+-----------------+-------------------+
| order_item_id | quantity | unit_price | discount_rate | original_amount | discounted_amount |
+---------------+----------+------------+---------------+-----------------+-------------------+
|             3 |        1 |     169.00 |        0.0947 |          169.00 |        152.995700 |
+---------------+----------+------------+---------------+-----------------+-------------------+

SELECT
    order_item_id,
    ROUND(
        quantity * unit_price * (1 - discount_rate),
        2
    ) AS payable_amount
FROM order_items
WHERE order_item_id = 3;

+---------------+----------------+
| order_item_id | payable_amount |
+---------------+----------------+
|             3 |         153.00 |
+---------------+----------------+

SELECT
    ROUND(12.345, 2) AS rounded_value,
    TRUNCATE(12.345, 2) AS truncated_value,
    FORMAT(12345.6, 2) AS display_text;

+---------------+-----------------+--------------+
| rounded_value | truncated_value | display_text |
+---------------+-----------------+--------------+
|         12.35 |           12.34 | 12,345.60    |
+---------------+-----------------+--------------+

SELECT
    CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) AS float_sum,
    CAST(0.1 AS DECIMAL(10, 2))
      + CAST(0.2 AS DECIMAL(10, 2)) AS decimal_sum;

+---------------------+-------------+
| float_sum           | decimal_sum |
+---------------------+-------------+
| 0.30000000000000004 |        0.30 |
+---------------------+-------------+

SELECT
    ABS(
        (CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE)) - 0.3
    ) < 0.000001 AS close_enough;

+--------------+
| close_enough |
+--------------+
|            1 |
+--------------+

SELECT
    7 / 2 AS normal_division,
    7 DIV 2 AS integer_division,
    MOD(7, 2) AS remainder_value;

+-----------------+------------------+-----------------+
| normal_division | integer_division | remainder_value |
+-----------------+------------------+-----------------+
|          3.5000 |                3 |               1 |
+-----------------+------------------+-----------------+

SELECT
    79.90 * (
        1 - CAST(NULL AS DECIMAL(5, 4))
    ) AS payable_amount;

+----------------+
| payable_amount |
+----------------+
|           NULL |
+----------------+

SELECT customer_id, customer_name
FROM customers
WHERE phone = NULL;

Empty set

SELECT customer_id, customer_name
FROM customers
WHERE phone IS NULL;

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           3 | 白露          |
|           7 | 陆野          |
+-------------+---------------+

SELECT customer_id, customer_name, phone
FROM customers
WHERE phone <> '13800001001';

SELECT customer_id, customer_name, phone
FROM customers
WHERE phone <> '13800001001'
   OR phone IS NULL;

SELECT
    customer_id,
    COALESCE(phone, email, '暂无联系方式') AS preferred_contact
FROM customers
WHERE customer_id IN (1, 3, 5)
ORDER BY customer_id;

+-------------+--------------------------+
| customer_id | preferred_contact        |
+-------------+--------------------------+
|           1 | 13800001001              |
|           3 | bailu@example.com        |
|           5 | 13800001005              |
+-------------+--------------------------+

SELECT
    ROUND(
        79.90
        * (1 - COALESCE(
            CAST(NULL AS DECIMAL(5, 4)),
            0
        )),
        2
    ) AS payable_amount;

+----------------+
| payable_amount |
+----------------+
|          79.90 |
+----------------+

SELECT
    100.00 / NULLIF(0, 0) AS safe_result;

+-------------+
| safe_result |
+-------------+
|        NULL |
+-------------+

SELECT
    CURRENT_DATE AS today,
    CURRENT_TIMESTAMP AS current_moment;

+------------+---------------------+
| today      | current_moment      |
+------------+---------------------+
| 2025-03-10 | 2025-03-10 10:30:00 |
+------------+---------------------+

SELECT
    TIMESTAMP('2025-03-10 10:30:00') AS fixed_moment,
    DATE('2025-03-10 10:30:00') AS fixed_date;

+---------------------+------------+
| fixed_moment        | fixed_date |
+---------------------+------------+
| 2025-03-10 10:30:00 | 2025-03-10 |
+---------------------+------------+

SELECT
    order_id,
    order_date,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day,
    HOUR(order_date) AS order_hour
FROM orders
WHERE order_id = 110;

+----------+---------------------+------------+-------------+-----------+------------+
| order_id | order_date          | order_year | order_month | order_day | order_hour |
+----------+---------------------+------------+-------------+-----------+------------+
|      110 | 2026-03-08 18:25:00 |       2026 |           3 |         8 |         18 |
+----------+---------------------+------------+-------------+-----------+------------+

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month
FROM orders
WHERE order_id = 110;

+------------+-------------+
| order_year | order_month |
+------------+-------------+
|       2026 |           3 |
+------------+-------------+

SELECT
    order_id,
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS service_deadline
FROM orders
WHERE order_id = 101;

+----------+---------------------+---------------------+
| order_id | order_date          | service_deadline    |
+----------+---------------------+---------------------+
|      101 | 2025-06-18 10:05:00 | 2025-06-25 10:05:00 |
+----------+---------------------+---------------------+

SELECT
    DATEDIFF('2025-03-15', '2025-03-08') AS days_between;

+--------------+
| days_between |
+--------------+
|            7 |
+--------------+

SELECT DATEDIFF(
    '2025-03-09 00:10:00',
    '2025-03-08 23:50:00'
) AS calendar_day_difference;

+-------------------------+
| calendar_day_difference |
+-------------------------+
|                       1 |
+-------------------------+

SELECT TIMESTAMPDIFF(
    SECOND,
    '2025-03-08 23:50:00',
    '2025-03-09 00:10:00'
) AS elapsed_seconds;

+-----------------+
| elapsed_seconds |
+-----------------+
|            1200 |
+-----------------+

SELECT
    DATE_ADD('2025-01-31', INTERVAL 1 MONTH) AS next_month_date;

+-----------------+
| next_month_date |
+-----------------+
| 2025-02-28      |
+-----------------+

SELECT
    CAST('2025-03-08 23:50:00' AS DATETIME) AS utc_time,
    CONVERT_TZ(
        '2025-03-08 23:50:00',
        '+00:00',
        '+08:00'
    ) AS shanghai_time;

+---------------------+---------------------+
| utc_time            | shanghai_time       |
+---------------------+---------------------+
| 2025-03-08 23:50:00 | 2025-03-09 07:50:00 |
+---------------------+---------------------+

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2025-09-09 00:00:00'
                     AND '2025-09-09 23:59:59';

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2025-09-09 00:00:00'
  AND order_date <  '2025-09-10 00:00:00'
ORDER BY order_date;

+----------+---------------------+
| order_id | order_date          |
+----------+---------------------+
|      104 | 2025-09-09 09:20:00 |
+----------+---------------------+

SELECT
    CAST('2025-03-10' AS DATE) AS parsed_date,
    CAST('128.90' AS DECIMAL(10, 2)) AS parsed_price,
    CAST(101 AS CHAR) AS order_code;

+-------------+--------------+------------+
| parsed_date | parsed_price | order_code |
+-------------+--------------+------------+
| 2025-03-10  |       128.90 | 101        |
+-------------+--------------+------------+

SELECT CAST('79.90元' AS DECIMAL(10, 2)) AS suspicious_price;

+------------------+
| suspicious_price |
+------------------+
|            79.90 |
+------------------+
1 row in set, 1 warning

CREATE TEMPORARY TABLE staging_products (
    raw_product_name VARCHAR(80) NOT NULL,
    raw_price        VARCHAR(40) NOT NULL
);
 
INSERT INTO staging_products (raw_product_name, raw_price)
VALUES
    ('便携电子秤', '128.00'),
    ('咖啡滤纸', '79.9'),
    ('异常记录', '79.90元');

SELECT raw_price
FROM staging_products
WHERE REGEXP_LIKE(raw_price, '^[0-9]+(\\.[0-9]{1,2})?$');

+-----------+
| raw_price |
+-----------+
| 128.00    |
| 79.9      |
+-----------+

SELECT CAST(raw_price AS DECIMAL(10, 2)) AS unit_price
FROM staging_products
WHERE REGEXP_LIKE(raw_price, '^[0-9]+(\\.[0-9]{1,2})?$');

+------------+
| unit_price |
+------------+
|     128.00 |
|      79.90 |
+------------+

DROP TEMPORARY TABLE staging_products;

SELECT
    CAST('2025-03-04' AS DATE) AS clear_date,
    CAST('2025-03-04 11:20:00' AS DATETIME) AS clear_datetime;

+------------+---------------------+
| clear_date | clear_datetime      |
+------------+---------------------+
| 2025-03-04 | 2025-03-04 11:20:00 |
+------------+---------------------+

SELECT CAST(order_id AS CHAR) AS business_code, '订单' AS source_type
FROM orders
WHERE order_id = 101
UNION ALL
SELECT CAST(payment_id AS CHAR) AS business_code, '支付' AS source_type
FROM payments
WHERE payment_id = 1;

+---------------+-------------+
| business_code | source_type |
+---------------+-------------+
| 101           | 订单        |
| 1             | 支付        |
+---------------+-------------+

SELECT
    1 + '2' AS implicit_sum,
    CONCAT('订单-', 101) AS implicit_text;

+--------------+---------------+
| implicit_sum | implicit_text |
+--------------+---------------+
|            3 | 订单-101      |
+--------------+---------------+

SELECT
    0 = 'abc' AS surprising_result,
    7 > '6x' AS another_result;

+-------------------+----------------+
| surprising_result | another_result |
+-------------------+----------------+
|                 1 |              1 |
+-------------------+----------------+

SELECT product_id, product_name
FROM products
WHERE product_id = 5;

SELECT product_id, product_name
FROM products
WHERE CAST(product_id AS CHAR) = '5';

SELECT product_id, product_name
FROM products
WHERE product_id = CAST('5' AS UNSIGNED);

SELECT
    total_amount,
    CONCAT('¥', FORMAT(total_amount, 2)) AS amount_text
FROM orders
WHERE order_id = 101;

+--------------+-------------+
| total_amount | amount_text |
+--------------+-------------+
|       118.90 | ¥118.90     |
+--------------+-------------+

SELECT
    FORMAT(12800.5, 2) AS formatted_amount,
    FORMAT(12800.5, 2, 'de_DE') AS german_style;

+------------------+--------------+
| formatted_amount | german_style |
+------------------+--------------+
| 12,800.50        | 12.800,50    |
+------------------+--------------+

SELECT
    order_id,
    FORMAT(total_amount, 2) AS amount_text
FROM orders
WHERE order_id IN (101, 107, 110)
ORDER BY amount_text DESC;

SELECT
    order_id,
    FORMAT(total_amount, 2) AS amount_text
FROM orders
WHERE order_id IN (101, 107, 110)
ORDER BY total_amount DESC;

+----------+-------------+
| order_id | amount_text |
+----------+-------------+
|      107 | 137.00      |
|      101 | 118.90      |
|      110 | 99.00       |
+----------+-------------+

SELECT
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%Y年%m月%d日 %H:%i') AS order_time_text
FROM orders
WHERE order_id = 110;

+----------+---------------------+--------------------------+
| order_id | order_date          | order_time_text          |
+----------+---------------------+--------------------------+
|      110 | 2026-03-08 18:25:00 | 2026年03月08日 18:25    |
+----------+---------------------+--------------------------+

SELECT order_id, order_date
FROM orders
WHERE status = '已完成'
  AND DATE(order_date) = '2025-09-09';

SELECT order_id, order_date
FROM orders
WHERE status = '已完成'
  AND order_date >= '2025-09-09 00:00:00'
  AND order_date <  '2025-09-10 00:00:00';

+----------+---------------------+
| order_id | order_date          |
+----------+---------------------+
|      104 | 2025-09-09 09:20:00 |
+----------+---------------------+

EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE status = '已完成'
  AND order_date >= '2025-09-09 00:00:00'
  AND order_date <  '2025-09-10 00:00:00';

+-------+------------------------+------------------------+------------------------+
| type  | possible_keys          | key                    | Extra                  |
+-------+------------------------+------------------------+------------------------+
| range | idx_orders_status_date | idx_orders_status_date | Using index condition  |
+-------+------------------------+------------------------+------------------------+

ALTER TABLE customers
ADD COLUMN email_normalized VARCHAR(120)
    GENERATED ALWAYS AS (LOWER(TRIM(email))) STORED,
ADD INDEX idx_customers_email_normalized (email_normalized);

SELECT customer_id, customer_name, email
FROM customers
WHERE email_normalized = 'xiaoman@example.com';

+-------------+---------------+---------------------+
| customer_id | customer_name | email               |
+-------------+---------------+---------------------+
|           1 | 苏小满        | xiaoman@example.com |
+-------------+---------------+---------------------+

CREATE INDEX idx_customers_lower_email
ON customers ((lower(email)));

SELECT customer_id, customer_name, email
FROM customers
WHERE lower(email) = 'xiaoman@example.com';

-- 参数类型错误：把手机号作为数字
SELECT customer_id, customer_name
FROM customers
WHERE phone = 13800001001;

SELECT customer_id, customer_name
FROM customers
WHERE phone = '13800001001';

SELECT
    oi.order_item_id,
    o.order_id,
    TRIM(c.customer_name) AS customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.discount_rate,
    oi.quantity * oi.unit_price AS original_amount,

+---------------+----------+---------------+----------------------+----------+------------+---------------+-----------------+----------------+---------------------+------------------+-----------------------+
| order_item_id | order_id | customer_name | product_name         | quantity | unit_price | discount_rate | original_amount | payable_amount | order_date          | order_time_text  | preferred_contact     |
+---------------+----------+---------------+----------------------+----------+------------+---------------+-----------------+----------------+---------------------+------------------+-----------------------+
|             1 |      101 | 苏小满        | 白瓷马克杯           |        1 |      39.90 |        0.0000 |           39.90 |          39.90 | 2025-06-18 10:05:00 | 2025-06-18 10:05 | 13800001001           |
|             2 |      101 | 苏小满        | 原木托盘             |        1 |      89.00 |        0.1124 |           89.00 |          79.00 | 2025-06-18 10:05:00 | 2025-06-18 10:05 | 13800001001           |
|             3 |      102 | 顾言          | 65W 氮化镓充电器     |        1 |     169.00 |        0.0947 |          169.00 |         153.00 | 2025-07-02 21:10:00 | 2025-07-02 21:10 | 13800001002           |
+---------------+----------+---------------+----------------------+----------+------------+---------------+-----------------+----------------+---------------------+------------------+-----------------------+

CREATE VIEW v_order_item_details AS
SELECT
    oi.order_item_id,
    o.order_id,
    o.order_date,
    o.status AS order_status,
    TRIM(c.customer_name) AS customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price

SELECT
    order_id,
    customer_name,
    product_name,
    payable_amount
FROM v_order_item_details
WHERE order_id IN (101, 102)
ORDER BY order_id, payable_amount DESC;

+----------+---------------+----------------------+----------------+
| order_id | customer_name | product_name         | payable_amount |
+----------+---------------+----------------------+----------------+
|      101 | 苏小满        | 原木托盘             |          79.00 |
|      101 | 苏小满        | 白瓷马克杯           |          39.90 |
|      102 | 顾言          | 65W 氮化镓充电器     |         153.00 |
|      102 | 顾言          | 编织数据线           |          35.00 |
+----------+---------------+----------------------+----------------+

SELECT
    order_item_id,
    quantity,
    unit_price,
    discount_rate,
    1 - discount_rate AS remaining_rate,
    quantity * unit_price AS original_amount,
    quantity * unit_price * (1 - discount_rate) AS raw_result
FROM order_items
WHERE order_item_id <= 4
ORDER BY order_item_id;

+---------------+----------+------------+---------------+----------------+-----------------+------------+
| order_item_id | quantity | unit_price | discount_rate | remaining_rate | original_amount | raw_result |
+---------------+----------+------------+---------------+----------------+-----------------+------------+
|             1 |        1 |      39.90 |        0.0000 |         1.0000 |           39.90 |  39.900000 |
|             2 |        1 |      89.00 |        0.1124 |         0.8876 |           89.00 |  78.996400 |
|             3 |        1 |     169.00 |        0.0947 |         0.9053 |          169.00 | 152.995700 |
|             4 |        1 |      39.00 |        0.1026 |         0.8974 |           39.00 |  34.998600 |
+---------------+----------+------------+---------------+----------------+-----------------+------------+

SELECT
    customer_id,
    phone,
    phone <> '13800001001' AS is_other_phone,
    phone IS NULL AS phone_missing
FROM customers
WHERE customer_id IN (1, 3, 5)
ORDER BY customer_id;

+-------------+-------------+----------------+---------------+
| customer_id | phone       | is_other_phone | phone_missing |
+-------------+-------------+----------------+---------------+
|           1 | 13800001001 |              0 |             0 |
|           3 | NULL        |           NULL |             1 |
|           5 | 13800001005 |              1 |             0 |
+-------------+-------------+----------------+---------------+

SELECT
    total_amount,
    FORMAT(total_amount, 2) AS amount_text
FROM orders
ORDER BY total_amount;

CREATE TEMPORARY TABLE tmp_amounts AS
SELECT
    total_amount,
    FORMAT(total_amount, 2) AS amount_text
FROM orders;
 
SHOW COLUMNS FROM tmp_amounts;

DROP TEMPORARY TABLE tmp_amounts;

-- 错误方向：日期已经被截断，时区转换来不及了
SELECT DATE(order_date) AS utc_calendar_date
FROM (
    SELECT CAST('2025-03-08 23:50:00' AS DATETIME) AS order_date
) AS sample_order;

+-------------------+
| utc_calendar_date |
+-------------------+
| 2025-03-08        |
+-------------------+

SELECT DATE(
    CONVERT_TZ(order_date, '+00:00', '+08:00')
) AS shanghai_calendar_date
FROM (
    SELECT CAST('2025-03-08 23:50:00' AS DATETIME) AS order_date
) AS sample_order;

+------------------------+
| shanghai_calendar_date |
+------------------------+
| 2025-03-09             |
+------------------------+

SELECT product_id, product_name, created_at
FROM products
WHERE status = '在售'
  AND DATE_FORMAT(created_at, '%Y-%m') = '2025-09';

SELECT customer_id, customer_name, email
FROM customers
WHERE email <> 'xiaoman@example.com';

quantity * unit_price * (1 - discount_rate)