---
# System prepended metadata

title: 【Part3 - Mysql 是什麼？Mysql 優勢、指令】

---

# **【Part3 - Mysql 是什麼？Mysql 優勢、指令】**
:::info
* [Part1](https://hackmd.io/@workcata/BJtzg-kyT)
* [Part2](https://hackmd.io/@workcata/ry4sDHr4gg)
* Mysql 基礎操作
    - 字符長度 CHAR_KENGTH、LENGTH
    - 內文編輯 UPPER、LOWER、INSTR
    - 限制輸入 ENUM
    - 排列 ORDER BY
    - 計算、符號 (< > = AND OR IN( , , ) BETWEEN CASE)
    - AVG/ SUM/ COUNT + OVER (PARTITION BY)
    - 永久更動表內容
    - 時間 (CURDATE、CURTIME、NOW、MONTHNAME、YEAR、DAYOFWEEK、DAYOFYEAR、HOUR、MINUTE、SECOND、DETEIFF、DATE_ADD、DATE_SUB、TIMEIFF、INTERVAL、TIMESTAMP)
    - DISTINCT 
    - COUNT 統計次數
    - IS NULL, <=> NULL, IS NOT NULL, <> NULL 判斷
    - if 判斷
    - 加入條件 (CASE (WHEN… THEN…. ELSE)  END、CHECK)
    - ROW_NUMBER() OVER() 行號分配
    - RANK() OVER() 排名
    - DENSE_RANK( ) OVER() 密集排名
    - NTILE() OVER() 分級距
    - FIRST_VALUE() OVER() 取那組第一個值，用DESC取到最大 ASC取到最小
    - LAG( ) OVER() 取整體前ㄧ行，LEAD( ) OVER() 取整體後一行，可以查看兩者差距
    - INDEX()
:::


### ☑️ 字符長度 CHAR_KENGTH、LENGTH
![](https://hackmd.io/_uploads/H1YCCqbJ6.png)
<br/>

![](https://hackmd.io/_uploads/rJgfksWJa.png)


### ☑️ 內文編輯 UPPER、LOWER、INSTR
UPPER()、LOWER()
```=
SELECT CONCAT('Name: ', UPPER(name)) FROM unique_cats;
```
<br/>

INSERT(第?個字開始, 覆蓋後面?個字, 插入內容)
![](https://hackmd.io/_uploads/S1JIWsWkp.png)
<br/>

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)
```

<br/>

### ☑️ 限制輸入 ENUM
ENUM('value1', 'value2', 'value3', ...) 限制內容
性別只能放 M or F
```=
gender ENUM('M', 'F')   
```

<br/>

### ☑️ 排列 ORDER BY
ORDER BY + DECE/ ASC (默認ASC) ＋LIMIT
```=
SELECT name, age FROM 'unique_cats' ORDER BY age DESC LIMIT 10;
```
<br/>

### ☑️ 計算、符號 (< > = 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;
```
![](https://hackmd.io/_uploads/Bydq5abJp.png)
<br/>

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 數字會都顯示出來
![](https://hackmd.io/_uploads/HyO3pmEy6.png)
![](https://hackmd.io/_uploads/ByKGRXVJp.png)
<br/>

綜合應用
```=
SELECT name, age, salary FROM personales ORDER BY salary DESC LIMIT 3;  (找出前三高)
```
```=
SELECT SUM(salary), AVG(salary) FROM personales; (找出全部人薪水總支出、總平均)
```
<br/>

### ☑️ AVG/ SUM/ COUNT + OVER (PARTITION BY)
算出每個部門平均薪資 
![](https://hackmd.io/_uploads/rkB-1VVy6.png)
<br/>

算出所屬部門的總薪資
![](https://hackmd.io/_uploads/H1hI1N4J6.png)
所有人總薪資
SUM(salary) OVER( )

<br/>

算出所屬部門的總薪資+每組排序
![](https://hackmd.io/_uploads/rJdR1N4JT.png)
![](https://hackmd.io/_uploads/SkaGlNNka.png)

<br/>

算出每個部門各有幾行
![](https://hackmd.io/_uploads/B1_NJVNyp.png)
<br/>

### ☑️ 永久更動表內容
ADD COLUMN <new_col> + 條件

```=
ALTER TABLE personals ADD COLUMN class VARCHAR(20);
=ALTER TABLE personals ADD class VARCHAR(20);
```

DROP COLUMN <col_name>
```=
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;
```
<br/>

### ☑️ 時間 (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
```
![](https://hackmd.io/_uploads/BkR3bRW1a.png)

![](https://hackmd.io/_uploads/BkB2M0byp.png)
<br/>

### ☑️ DISTINCT

[參考!!!](https://www.yiibai.com/mysql/distinct.html)

```=
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;
```

<br/>

>PS FORMATTING DATES
>
![](https://hackmd.io/_uploads/r1NhyAWJp.png)

![](https://hackmd.io/_uploads/HkRC1RZJ6.png)

![](https://hackmd.io/_uploads/ByEyg0-ya.png)

![](https://hackmd.io/_uploads/rk7fe0WJT.png)

![](https://hackmd.io/_uploads/rktVl0W1a.png)

搭配 NOW()
![](https://hackmd.io/_uploads/BJj8E9Vkp.png)

<br/>

### 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;
```
會顯示
![](https://hackmd.io/_uploads/ryrrU7Vka.png)
<br/>

ROW_NUMBER( ) OVER(ORDER BY …) 最後一行會給排序
![](https://hackmd.io/_uploads/rJK5ZNVya.png)
ROW_NUMBER( ) OVER(PARTITION BY… ORDER BY ….)
![](https://hackmd.io/_uploads/S141G4N1T.png)
<br/>

### ☑️ RANK() OVER() 排名
RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，會跳號
![](https://hackmd.io/_uploads/ry26e4VyT.png)
RANK( ) OVER(PARTITION BY… ORDER BY ….)
![](https://hackmd.io/_uploads/BywxWNEJT.png)
<br/>

### ☑️ DENSE_RANK( ) OVER() 密集排名
DENSE_RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，不會跳號
![](https://hackmd.io/_uploads/Hy4fMNN16.png)

DENSE_RANK( ) OVER(PARTITION BY… ORDER BY ….)
![](https://hackmd.io/_uploads/HkLFfVVJT.png)

練習
![](https://hackmd.io/_uploads/rJP3ME41a.png)
<br/>

### ☑️ NTILE() OVER() 分級距
NTILE(4) OVER(ORDER BY…)
![](https://hackmd.io/_uploads/rJn2V4VJ6.png)
NTILE(5) OVER(PARTITION BY.. ORDER BY...)
![](https://hackmd.io/_uploads/BJmLVNVkp.png)

### ☑️ FIRST_VALUE() OVER() 取那組第一個值，用DESC取到最大 ASC取到最小
FIRST_VALUE( ) OVER(ORDER BY…) 
![](https://hackmd.io/_uploads/Sk57rE4kp.png)
FIRST_VALUE( ) OVER(PARTITION BY… ORDER BY…)
![](https://hackmd.io/_uploads/BJAmrV4yp.png)
<br/>

### ☑️ LAG( ) OVER() 取整體前ㄧ行，LEAD( ) OVER() 取整體後一行，可以查看兩者差距
LAG/LEAD( ) OVER(ORDER BY…)
![](https://hackmd.io/_uploads/Sy8DMc4kp.png)
![](https://hackmd.io/_uploads/HycPf54JT.png)

LAG/LEAD( ) OVER(PARTITION BY… ORDER BY…)
![](https://hackmd.io/_uploads/Hk6DM5V16.png)
![](https://hackmd.io/_uploads/r1bOzqVya.png)

第一行代表，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;
```
<br/>

### ☑️ 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;
```

如果預期會返回大量結果，索引可能無法提供顯著的性能提升，還可能因維護成本而降低整體性能。就需要考慮其他優化策略，例如，數據分區、物化視圖或更複雜的查詢重寫等