•【Part 2】MySQL 進階查詢：REGEXP、LIKE 模糊搜尋、CONCAT／GROUP_CONCAT、GROUP BY HAVING、SUBSTRING 實作

☑️ 資料搜尋 : REGEXP (包含…)

REGEXP (正規表達式操作符)，可以查找符合特定模式的字串，通常比 = 慢

SELECT * FROM <table_name> WHERE username REGEXP 'catalina';
SELECT * FROM <table_name> WHERE email REGEXP 'catalina*06';  -- 後面跟著 零個或多個 a，然後再跟著 06

SELECT * FROM <table_name> WHERE email REGEXP 'catalina.*06';     -- 包含 catalina，後面跟著任意字符零次或多次（可以是什麼都沒有），然後是 06
SELECT * FROM your_table WHERE your_column LIKE 'catalina%06';    -- 以 catalina 開頭，後面跟著任意字符零次或多次（可以是什麼都沒有），然後是 06

SELECT * FROM <table_name> WHERE first_date REGEXP '2023-10';

如果我想搜尋2023/10/15後入職的

SELECT * FROM <table_name> WHERE first_date REGEXP '2023-10-1[6-9]|2023-10-[2-3][0-9]';

= SELECT * FROM <table_name> WHERE first_date < '2023-10-15';

☑️ 模糊搜尋 (% 無限字元、_ 單一字元、LIKE、NOT LIKE)

% 無限字元 _ 單一字元
LIKE

NOT LIKE

☑️ 練習正則表達式

找出 前面有一個字+o 的字串

SELECT * FROM <table_name> WHERE username REGEXP '.o';

找出開頭為 john的字串

SELECT * FROM <table_name> WHERE email REGEXP '^john';

找出開頭有字符 + 第二個字為a or b or c 的字串
^ 開頭
. 點號 (period) 是一個萬用字元，類似配合like的_，可以匹配任何單一字元，除了換行符

SELECT * FROM <table_name> WHERE username REGEXP '^.[abc]';

找出前面有字符 + a or b or c 結尾的字串
$ 結尾

SELECT * FROM <table_name> WHERE username REGEXP '.[abc]$'

PS 如果裡面也有^， '^.[^a-f]'; 代表第二個字不是a-f

找出結尾為 gmail.com 的字串

SELECT * FROM <table_name> WHERE email REGEXP 'gmail\.com$';

找出當中含有 d or y 的字串

SELECT * FROM <table_name> WHERE email REGEXP '[d,y]';

找出當中含有 d or 字符+y 的字串

SELECT * FROM <table_name> WHERE email REGEXP '[d,.y]';

找出當中含有 一個字符+y 的字串，.{1}表示前面的字符只能出現一次

SELECT * FROM <table_name> WHERE email REGEXP '.{1}[.]y';

找出當中含有 一個A-Z字符 + 一個數字 1-2之間 + 八個數字 0-9之間

SELECT * FROM <table_name> WHERE id_number REGEXP '^[A-Z][1-2][0-9]{8}$'

郵箱的規則，大小寫英文、數字、符號 + @gmail.com 或 @qmail.com 結尾

SELECT * FROM <table_name> WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com|@qmail.com$'

郵箱的規則，包含至少一個數字一個字母 + @gmail.com 或 @qmail.com 結尾

SELECT * FROM <table_name> WHERE email REGEXP '^(?=.*[a-zA-Z])(?=.*[0-9])[a-zA-Z0-9._%+-]+@(gmail\.com|qmail\.com)$'

☑️ 資料倒裝 : REVERSE

SELECT REVERSE ('Hello World!');    # !dlroW olleH
SELECT CONCAT('hello',REVERSE('hello'));   # helloolleh
SELECT CONCAT('name',REVERSE('name')) FROM unique_cats;

☑️ 資料連接 : CONCAT

顯示新欄位

SELECT CONCAT(name,'-', age) AS cat_name_age FROM unique_cats;
= SELECT CONCAT_WS('-', name, age) AS cat_name_age FROM unique_cats;

永久性增加欄位
ALTER TABLE unique_cats
ADD COLUMN cat_name_age VARCHAR(100);

UPDATE unique_cats
SET cat_name_age = CONCAT(name, '-', age);

☑️ 資料連接 : GROUP_CONCAT

SELECT customer_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS ordered_products
FROM orders
GROUP BY customer_id;

承上，原始資料
| customer_id | product_name |
| ----------- | ------------ |
| 1           | Product A    |
| 1           | Product B    |
| 2           | Product C    |
| 2           | Product D    |
| 2           | Product E    |

結果
| customer_id | ordered_products     |
|-------------|----------------------|
| 1           | Product A, Product B |
| 2           | Product C, Product D, Product E |

☑️ 分組顯示 GROUP BY…HAVING + 條件

☑️ 取字串 SUBSTRING

SELECT SUBSTRING('Hello World', 1, 4); (第1個字開始，取到第4個字)
SELECT SUBSTRING('Hello World', -4, 5);
SELECT SUBSTRING('Hello World', 7);  (第七個字取到結束)
SELECT SUBSTR(name, 1, 3) AS name_abbr  FROM unique_cats; (表中的name欄位，第一個字開始1，取到第3個字)

WITH ROLL UP 新增一個欄位，為自動加總行

SELECT
    CONCAT(
        SUBSTR(name, 1, 3),
        SUBSTR(age,1),
    )
AS name_age
FROM unique_cats;

☑️ 綜合應用

這樣會顯示錯誤!!! 因為 emp_birth_date 表格還不在TABLES中

先建立新表單 CREATE TABLE，抓取資料，才會對!