# **【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
<br/>
### ☑️ 內文編輯 UPPER、LOWER、INSTR
UPPER()、LOWER()
```=
SELECT CONCAT('Name: ', UPPER(name)) FROM unique_cats;
```
<br/>
INSERT(第?個字開始, 覆蓋後面?個字, 插入內容)
<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;
```
<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 數字會都顯示出來
<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)
算出每個部門平均薪資
<br/>
算出所屬部門的總薪資
所有人總薪資
SUM(salary) OVER( )
<br/>
算出所屬部門的總薪資+每組排序
<br/>
算出每個部門各有幾行
<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
```
<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
>
搭配 NOW()
<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;
```
會顯示
<br/>
ROW_NUMBER( ) OVER(ORDER BY …) 最後一行會給排序
ROW_NUMBER( ) OVER(PARTITION BY… ORDER BY ….)
<br/>
### ☑️ RANK() OVER() 排名
RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，會跳號
RANK( ) OVER(PARTITION BY… ORDER BY ….)
<br/>
### ☑️ DENSE_RANK( ) OVER() 密集排名
DENSE_RANK( ) OVER(ORDER BY ….) 可能會有同名次情況，不會跳號
DENSE_RANK( ) OVER(PARTITION BY… ORDER BY ….)
練習
<br/>
### ☑️ 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…)
<br/>
### ☑️ 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;
```
<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;
```
如果預期會返回大量結果，索引可能無法提供顯著的性能提升，還可能因維護成本而降低整體性能。就需要考慮其他優化策略，例如，數據分區、物化視圖或更複雜的查詢重寫等
Sign in with Wallet
Connect another wallet