•【Part 3】MySQL 進階實戰：ORDER BY 排序、時間函數、統計與排名函數實作
MySQL 進階查詢
☑️ 字符長度 CHAR_KENGTH、LENGTH
☑️ 內文編輯 UPPER、LOWER、INSTR
UPPER()、LOWER()
SELECT CONCAT('Name: ', UPPER(name)) FROM unique_cats;
INSERT(第?個字開始, 覆蓋後面?個字, 插入內容)
INSTR 找出位置
INSTR('cat','a') (答案為2)
INSTR('cat','hello_cat') (答案為0)
SELECT LEFT (‘ ‘, ) 印出左邊?位
SELECT RIGHT(‘ ‘, ) 印出右邊?位
SELECT REPEAT(‘ ‘, ) 字串重複?次
SELECT LEFT ('catalina',4);
SELECT REPEAT('c',4);
SELECT REPEAT((SELECT name FROM unique_cats WHERE cat_id < 10), 3)
☑️ 限制輸入 ENUM
ENUM(‘value1’, ‘value2’, ‘value3’, …) 限制內容
性別只能放 M or F
gender ENUM('M', 'F')
☑️ 排列 ORDER BY
ORDER BY + DECE/ ASC (默認ASC) ＋LIMIT
SELECT name, age FROM 'unique_cats' ORDER BY age DESC LIMIT 10;
☑️ 計算、符號 (< > = AND OR IN( , , ) BETWEEN CASE)
< > =
SELECT name, gender, birth_date FROM personals WHERE DAY(birth_date) > 15;
AND OR
SELECT name, gender, birth_date FROM personals
WHERE name NOT LIKE UPPER('%p%')
AND gender LIKE 'M';
IN( , , ) = OR
SELECT name, gender, birth_date FROM personals WHERE name = 'Cata' OR name = 'Biga';
=SELECT name, gender, birth_date FROM personals WHERE name in ('Cata', 'Biga');
BETWEEN，USE “CAST”, IF HAVE TIME
SELECT * FROM employee
WHERE birth_date
BETWEEN CAST(‘12:00:00’ AS TIME)
AND CAST(‘16:00:00’ AS TIME)
count
SELECT COUNT( col ) FROM <table_name>; (所有行數)
SELECT COUNT(column_name) FROM table_name; (非NULL的所有行數)
每組的customer_id個數
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
搭配 SUM()、GROUP BY()
SELECT customer_id, COUNT(*) AS order_count, SUM(order_value)
FROM orders
GROUP BY customer_id;
MAX(), MIN()
SELECT MAX(price) AS max_price
FROM products;
SELECT MIN(price) AS minrice
FROM products;
AVG() 搭配子查詢
SELECT name, age, salary FROM personales WHERE salary>=
(SELECT AVG(salary) AS avg_salary FROM personales);
MAX(), MIN(), AVG() + OVER 數字會都顯示出來
綜合應用
SELECT name, age, salary FROM personales ORDER BY salary DESC LIMIT 3; (找出前三高)
SELECT SUM(salary), AVG(salary) FROM personales; (找出全部人薪水總支出、總平均)
☑️ AVG/ SUM/ COUNT + OVER (PARTITION BY)
算出每個部門平均薪資
算出所屬部門的總薪資
所有人總薪資
SUM(salary) OVER( )
算出所屬部門的總薪資+每組排序
算出每個部門各有幾行
☑️ 永久更動表內容
ADD COLUMN + 條件
ALTER TABLE personals ADD COLUMN class VARCHAR(20);
=ALTER TABLE personals ADD class VARCHAR(20);
DROP COLUMN
ALTER TABLE personals DROP COLUMN class ;
=ALTER TABLE personals DROP class ;
更改 tablename
ALTER TABLE <tablename> RENAME TO <new_tablename>;
更改 col_name
ALTER TABLE <tablename> RENAME COLUMN <col_name> TO <new_col_name>;
更改 col_schema
ALTER TABLE <tablename> MODIFY <col_name> + 新條件;
更改 col_name + col_schema (RENAME TO + MODIFY)
ALTER TABLE <table_name> CHANGE <col_name> <new_col_name> + 新條件;
增加新條件，給新條件取名 + 確認條件內容
ALTER TABLE houses
ADD CONSTRAINT positive_price
CHECK (purchase_price >= 0);
刪除新條件
ALTER TABLE houses
DROP CONSTRAINT positive_pprice;
☑️ 時間 (CURDATE、CURTIME、NOW、MONTHNAME、YEAR、MONTH、WEEK、DAYOFWEEK、DAYOFYEAR、HOUR、MINUTE、SECOND、DATE、DETEIFF、DATE_ADD、DATE_SUB、TIMEIFF、INTERVAL、TIMESTAMP)
常見
CURDATE() (顯示 2023-09-03)
CURTIME() (顯示 23:20:00)
NOW() (顯示 2023-09-03 23:20:00)
SELECT DAY('2023-09-15'); (返回 15)
SELECT MONTHNAME('2023-09-15'); (返回 'September')
SELECT YEAR('2023-09-15'); (返回 2023)
SELECT DAYOFWEEK('2023-09-15'); (返回 5 (星期四))
SELECT DAYOFYEAR('2023-09-15'); (返回 258 (第258天))
SELECT HOUR('14:30:45'); (返回 14)
SELECT MINUTE('14:30:45'); (返回 30)
SELECT SECOND('14:30:45'); (返回 45)
DETEIFF、DATE_ADD、DATE_SUB
SELECT DETEIFF('2023-04-31 23:59:59', '2023-04-30') 會得到 1
SELECT DETEIFF('2023-04-30 23:59:59', '2023-05-31’') 會得到 31
SELECT `birth_date`, DETEIFF(CURDATE(), ‘1996-05-12') 算出出生到現在幾天
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY); 會得到一天後的日期
SELECT DATE_ADD(`birth_date`, INTERVAL 18 YEAR); 會得到滿18歲的日期
SELECT DATE_ADD(‘2022-05-15’, INTERVAL 1 DAY); 會得到 2022-05-16
SELECT DATE_ADD(‘2022-05-15’, INTERVAL 1 YEAR); 會得到 2023-05-15
SELECT DATE_SUB(‘2022-05-15’, INTERVAL 1 DAY); 會得到 2022-05-14
SELECT DATE_SUB(‘2022-05-15’, INTERVAL 1 YEAR); 會得到 2021-05-15
TIMEIFF、INTERVAL
SELECT TIMEIFF(CURDATE(), , '07:30:00') 計算起床多久了
SELECT NOW() - INTERVAL 18 YEAR; 計算18年前的日期＋天數
SELECT YEAR(`birth_date` + INTERVAL 18 YEAR) AS will_be_18_years_old:
TIMESTAMP()
CREATE AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
UPDATED AT TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
☑️ DISTINCT
SELECT DISTINCE TIME from future_day ;
承上，原始數據
| time |
|-----------|
| 09:00:00 |
| 09:30:00 |
| 10:00:00 |
| 10:30:00 |
| 09:00:00 |
| 11:00:00 |
| 10:00:00 |
結果
| time |
|-----------|
| 09:00:00 |
| 09:30:00 |
| 10:00:00 |
| 10:30:00 |
| 11:00:00 |
常搭配 COUNT
# 有個投放廣告表格，我想知道各廣告有幾個不同的USER投放過
INSERT INTO website_sessions (website_session_id, user_id, utm_content)
VALUES
(1001, 1, 'CampaignA'),
(1002, 2, 'CampaignB'),
(1003, 1, 'CampaignA'),
(1004, 3, 'CampaignC'),
(1005, 3, 'CampaignB');
SELECT utm_content, COUNT(DISTINCT user_id) AS distinct_users_count
FROM website_sessions
GROUP BY utm_content;
# 會出現
utm_content | distinct_users_count
-------------+----------------------
CampaignA | 1
CampaignB | 2
CampaignC | 1
如果要刪除重複行
SELECT DISTINCT
columns
FROM
table_name
WHERE
where_conditions;
PS FORMATTING DATES
搭配 NOW()
COUNT 統計次數
# 有個投放廣告活動，我想知道各廣告被投放過幾次
INSERT INTO website_sessions (website_session_id, user_id, utm_content)
VALUES
(1001, 1, 'CampaignA'),
(1002, 2, 'CampaignB'),
(1003, 1, 'CampaignA'),
(1004, 3, 'CampaignC'),
(1005, 3, 'CampaignB');
SELECT utm_content, COUNT(*) AS ad_impressions
FROM website_sessions
GROUP BY utm_content;
# 會出現
| utm_content | ad_impressions |
+-------------+------------------+
| CampaignA | 2 |
| CampaignB | 2 |
| CampaignC | 1 |
☑️ IS NULL, <=> NULL, IS NOT NULL, <> NULL 判斷
select * from store where cost IS NULL;
= select * from store where cost <=> NULL;
☑️ if 判斷
SELECT student_name, score,
IF(score >= 90, '優秀',
IF(score >= 60, '及格', '不及格')) AS grade
FROM students;
☑️ 加入條件 (CASE (WHEN… THEN…. ELSE) END、CHECK)
CASE (WHEN… THEN…. ELSE) END
SELECT name, birth date, sex
CASE
WHEN sex = 'F' THEN 'girl'
ELSE 'boy'
END AS gender
FROM personals ;
= SELECT name, birth_date, sex,
IF(sex = 'F', 'girl', 'boy') AS gender
FROM personals;
CHECK() 下條件時再次確認
CREATE TABLE users (
username VARCHAR(20) NOT NULL, age INT CHECK (age > 0)
);
條件取名+再次確認
CREATE TABLE users2 (
username VARCHAR(20) NOT NULL,
age INT,
CONSTRAINT age_not_negative CHECK (age >= 0)
);
☑️ ROW_NUMBER() OVER() 行號分配
SELECT
(@row_number:=@row_number + 1) AS row_number,
t.*
FROM
(SELECT * FROM your_table_name ORDER BY your_column_name ASC) AS t,
(SELECT @row_number:=0) AS r;
會顯示
ROW_NUMBER( ) OVER(ORDER BY …) 最後一行會給排序
ROW_NUMBER( ) OVER(PARTITION BY… ORDER BY ….)
☑️ RANK() OVER() 排名
RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，會跳號
RANK( ) OVER(PARTITION BY… ORDER BY ….)
☑️ DENSE_RANK( ) OVER() 密集排名
DENSE_RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，不會跳號
DENSE_RANK( ) OVER(PARTITION BY… ORDER BY ….)
練習
☑️ NTILE() OVER() 分級距
NTILE(4) OVER(ORDER BY…)
NTILE(5) OVER(PARTITION BY.. ORDER BY…)
☑️ FIRST_VALUE() OVER() 取那組第一個值，用DESC取到最大 ASC取到最小
FIRST_VALUE( ) OVER(ORDER BY…)
FIRST_VALUE( ) OVER(PARTITION BY… ORDER BY…)
☑️ LAG( ) OVER() 取整體前ㄧ行，LEAD( ) OVER() 取整體後一行，可以查看兩者差距
LAG/LEAD( ) OVER(ORDER BY…)
LAG/LEAD( ) OVER(PARTITION BY… ORDER BY…)
第一行代表，customer service 61000 的前一位薪水為 56000
如果要算出兩者差異?
SELECT emp_no, department, salary, salary - LAG(salary) OVER (ORDER BY salary) AS salary_diff FROM employees ORDER BY salary DESC;
☑️ INDEX()
很常操作的表格，可以設置INDEX()，優點: 會加速操作、缺點:佔容量
CREATE INDEX()
CREATE INDEX idx_last_name_first_name ON users (last_name, first_name);
SELECT *
FROM users
WHERE last_name = 'Wang' AND first_name = 'Catalina';
DROP INDEX()
DROP INDEX idx_last_name_first_name ON users;
比較
直接利用索引快速定位到所有 category 為 ‘Electronics’ 的產品，而不需要掃描整個 products 表
CREATE INDEX idx_category ON products (category);
SELECT product_id, product_name, price
FROM products
WHERE category = 'Electronics';
庫存量都很少的，不適合使用index(佔空間)
SELECT product_id, product_name, stock_quantity
FROM products
WHERE stock_quantity < 10;
如果預期會返回大量結果，索引可能無法提供顯著的性能提升，還可能因維護成本而降低整體性能。就需要考慮其他優化策略，例如，數據分區、物化視圖或更複雜的查詢重寫等
