你好，我是富士大顆 Aiko
此篇會談到 SQL 的：

• 資料型態
• 語法架構
• 基本面試題

SQL(Structured Query Language)怎麼念？

如題，官方聲明念法是" S-Q-L"，但也歡迎念"西 qote "。
或者老闆同事怎麼唸就怎麼念，有些小事不用太堅持。

Structured Query Language 結構化查詢語言，資料庫結構一複雜就會很需要用它進行資料的抓取，這也是它的特色--處理關聯資料。

資料型態是什麼？

資料型態有這幾種：

• 數值 (Numeric Data)
  例如：integer, float, money 能搭配內建的函式處理該數值欄位
• 字串(元) (Character & Strings Data) 
  例如：text, char（適用固定長度資料，節省空間）, varchar（會釋出不用的格子，但會在資料最前方用 byte 表示字元長度）
• 日期/時間資料 (Date Data)
  例如：date, time, timestamp
• 布林值 (Boolean Data)
• 二元/二進資料 (Binary Data)
  例如：BLOB（Binary Large Object），用於儲存如圖片、音樂等二進制資料。

基本操作：Create

CREATE DATABASE

建立資料庫

CREATE DATABASE database_name;

CREATE TABLE （就是很直覺的那個意思）

建立新的資料表，並定義其結構（包括列名、資料型態等）。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

範例：

-- 新建一個名為 'students' 的表格，包含 'id', 'name', 和 'age' 三個列
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

CREATE INDEX 建立索引

用於在一個或多個列上建立索引，以加速查詢操作。

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE VIEW 建立 view

用於建立一個視圖，它是基於 SQL 查詢的結果群集。

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE ...;

有 SELECT 為什麼要用 CREATE VIEW?

1. 簡化查詢：如果你有一個非常複雜的 SELECT 語句，使用視圖可以將其簡化。一旦視圖被建立，可以像使用普通資料表一樣使用它，而不需要每次都寫出那個複雜的查詢。對於一些常見的資料操作，如分組和排序，視圖可以預先設定好。

2. 資料安全性：視圖可以限制使用者只能看到某些欄位，提供一種資料安全的機制。

3. 代碼重複使用和維護：如果多個查詢都使用相同的 JOIN 或 WHERE 條件，則可以建立一個視圖來重用這些條件，這樣在未來需要更改條件時，只需更改視圖即可。

4. 邏輯分離：視圖可以將資料的實際儲存結構與用於報告和查詢的邏輯結構分離開來。

5. 計算欄位：視圖可以包含計算欄位，這些欄位的值是由其他欄位計算出來的，而不是實際存儲在資料庫中。

假設你有一個包含多個 JOIN 和 WHERE 條件的複雜查詢：

SELECT a.name, b.age, c.address
FROM table1 a
JOIN table2 b ON a.id = b.id
JOIN table3 c ON a.id = c.id
WHERE b.age > 21;

將其轉換為一個視圖：

CREATE VIEW view_name AS
SELECT a.name, b.age, c.address
FROM table1 a
JOIN table2 b ON a.id = b.id
JOIN table3 c ON a.id = c.id
WHERE b.age > 21;

之後，只需要使用簡單的查詢來使用這個視圖：

SELECT * FROM view_name;

CREATE USER 建立使用者

用於建立新的資料庫使用者。

CREATE USER 'username'@'hostname'
IDENTIFIED BY 'password';

CREATE PROCEDURE 和 CREATE FUNCTION 建立儲存過程和函式

CREATE PROCEDURE 和 CREATE FUNCTION 是用於在資料庫中建立儲存過程（Stored Procedure）和函式（Function）的 SQL 語句。這兩者都是一組預先編譯的 SQL 語句，可以被多次呼叫和重複使用。

CREATE PROCEDURE 和 CREATE FUNCTION 是用於在資料庫中建立儲存過程（Stored Procedure）和函式（Function）的 SQL 語句。這兩者都是一組預先編譯的 SQL 語句，可以被多次呼叫和重用。

儲存過程（Stored Procedure）

• 定義：儲存過程是一組為了完成特定功能而編寫的 SQL 語句集。
• 語法：CREATE PROCEDURE procedure_name ...
• 呼叫方式：通常使用 CALL 語句來執行。
• 特點：
  • 接受參數和回傳結果。
  • 可以執行條件邏輯。
  • 通常用於資料驗證或資料庫維護。

Stored Procedure

CREATE PROCEDURE UpdateAge(IN id INT, IN new_age INT)
BEGIN
  UPDATE table_name SET age = new_age WHERE id = id;
END;

• CREATE PROCEDURE UpdateAge(IN id INT, IN new_age INT): 這一行定義了一個新的儲存過程並命名為 UpdateAge。它接受兩個參數：
  • IN id INT: 第一個參數是 id，其資料類型為整數（INT）。
  • IN new_age INT: 第二個參數是 new_age，其資料類型也為整數（INT）。
• BEGIN ... END: 這兩個關鍵字之間的代碼是儲存過程的主體。
• UPDATE table_name SET age = new_age WHERE id = id;:
  這是儲存過程主要執行的 SQL 語句。它會更新名為 table_name 的資料表，將 id 欄位值等於輸入參數 id 的那一行的 age 欄位設定為 new_age。

-- 將 ID 為 1 的資料年齡更新為 25。
CALL UpdateAge(1, 25);

函式（Function）

• 定義：函式是一種特殊類型的儲存過程，主要用於計算並回傳一個值。
• 語法：CREATE FUNCTION function_name ...
• 呼叫方式：可以作為 SQL 語句的一部分來使用。
• 特點：
  • 必須回傳一個值。
  • 可以用在 SQL 語句中，如 SELECT。
  • 不能用來修改資料庫狀態，只能用來進行計算。

Function

CREATE FUNCTION GetTotalPrice(order_id INT)
RETURNS INT
BEGIN
  DECLARE total_price INT;
  SELECT SUM(price) INTO total_price FROM order_details WHERE order_id = order_id;
  RETURN total_price;
END;

這個函式的主要目的是計算特定訂單（由 order_id 指定）的總價格。

• CREATE FUNCTION GetTotalPrice(order_id INT): 這一行定義了一個新的函式並命名為 GetTotalPrice。它接受一個參數：

  • order_id INT: 輸入參數是 order_id，其資料類型為整數（INT）。

• RETURNS INT: 這個函式將回傳一個整數（INT）類型的值，即訂單的總價格。

• BEGIN ... END: 這兩個關鍵字之間的代碼是函式的主體。

• DECLARE total_price INT;: 在函式內部，聲明一個名為 total_price 的整數變數。

• SELECT SUM(price) INTO total_price FROM order_details WHERE order_id = order_id;: 這行代碼從 order_details 資料表中選取所有與輸入的 order_id 相符合的訂單明細，並計算這些明細的 price 欄位之和。然後，將這個總和儲存到 total_price 變數中。

• RETURN total_price;: 函式回傳 total_price 變數的值作為結果。

-- 回傳訂單 ID 為 1 的訂單的總價格
SELECT GetTotalPrice(1);

兩者的區別：

基本操作：Read

SELECT 選取

-- 從 table_name 拿出 column_name 吧！
SELECT column_name
FROM table_name

WHERE 條件是...

-- 符合條件的所有表格
SELECT column_name
FROM table_name
WHERE ...;

ORDER BY 根據...排序

加入 ORDER BY 擴展成：

-- 我要從 table_name 拿出 column_name 並且符合…的條件，排序以 column_name'為主（這裡的column_name'不用等於 column_name），default = ASC (小到大，a-z)
SELECT column_name
FROM table_name
WHERE ...
ORDER BY column_name';

要反過來就是 DESC：

SELECT column_name
FROM table_name
WHERE ...
ORDER BY column_name' DESC;

LIMIT 數量上限為

限制要顯示的數量：

-- 我要從 table_name 拿出 column_name 並且符合…的條件，排序以 column_name'為主，大到小，顯示前 100 組資料
SELECT column_name
FROM table_name
WHERE ...
ORDER BY column_name' DESC
LIMIT 100;

最後的輸入句請一定要加上分號 ; 表示動作完成。
是不是覺得還挺直覺的？淚推！
可以先反覆看順 SQL 的基本語法結構，比較不會怕（？）

WHERE 搭配好，條件沒煩惱

搭配 WHERE 好用的一些介詞、連接詞、符號：

AND而且

AND 將多個條件結合在一起。只有符合被 AND 連接的所有條件的 record 才會被包含在查詢結果中。

-- model 的資料一定是藍色，且製造年晚於 2014 
SELECT model 
FROM cars 
WHERE color = 'blue' 
  AND year > 2014;

OR 或者

符合被 OR 連接的任何一個條件的記錄都會被回傳至查詢結果。

SELECT name
FROM customers 
WHERE state = 'CA' 
   OR state = 'NY';

BETWEEN 在值的範圍內

BETWEEN 根據一個值的範圍進行過濾，範圍的值可以是文字、數字或日期資料。

-- 所有 1980-1990 （含）間的電影資料
SELECT *
FROM movies
WHERE year BETWEEN 1980 AND 1990;

LIKE +%/_ 模糊比對

LIKE 用在 WHERE 子句中進行篩選。LIKE 可以搭配 _ 或 %。
% 用於比對零個或多個未指定的 characters.

-- "Star" 開頭的任何字串，如 "Star War"、"Start"、"Staring" 等。
SELECT name
FROM movies
WHERE name LIKE 'Star%';

_
用於比對任何「單」個未指定的 characters ，包括數字。

-- 所有標題以單個字符開頭，後面跟著 "ove" 的電影。例如 "Love"、"Cove"、"Dove" 等。
SELECT name
FROM movies
WHERE name LIKE '_ove';

SELECT 搭配好，選擇沒煩惱

搭配 SELECT 好用的一些介詞、連接詞：

* 星，所有的

-- 顯示所有的資料
SELECT * FROM table_name

AS 別名設定（不會改動真正的資料表）

使用 AS可以對資料表中的欄位或整個資料表進行別名(alias)設定。任何別名的修改都只影響查詢結果的顯示，不會對資料表中的資料進行修改。

-- 將 name 重新命名為 'movie_title'
SELECT name AS 'movie_title'
FROM movies;

DISTINCT 只有唯一值

DISTINCT 用於去掉重複的資料，只留唯一的值 unique。這對於資料統計、篩選唯一值或者移除重複資料等操作非常有用。

SELECT DISTINCT city
FROM contact_details;
--Chicago, Madison, Boston, Denver

數字型態的資料操作

COUNT(), SUM(), AVG() ,ROUND(), MAX()/MIN(), FIRST()/LAST()

字串型態的資料操作

LENGTH(), UPPER()/LOWER(), REPLACE()

基本操作：Update

UPDATE

-- 更新 'students' 表格中，id 為 1 的學生的名字
UPDATE students
SET name = 'Aiko Chen'
WHERE id = 1;

常用搭配字

還有一些其他的關鍵字和子句可以與 UPDATE 一起使用。以下是一些常用的關鍵字和子句：

SET: 指定要更新的列和新值。

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

WHERE: 指定哪些記錄需要更新。

UPDATE table_name SET column_name = value WHERE condition;

ORDER BY: 對要更新的記錄進行排序（僅在某些資料庫系統中可用）。

UPDATE table_name SET column_name = value ORDER BY column_name';

LIMIT: 限制要更新的記錄數量（僅在某些資料庫系統中可用）。

UPDATE table_name SET column_name = value LIMIT number;

JOIN: 根據另一個表中的列的值來更新一個表中的列。

UPDATE table1 JOIN table2 ON table1.column = table2.column SET table1.column = value;

• UPDATE table1: 指定要更新的表是 table1。

• JOIN table2 ON table1.column = table2.column: 這裡使用 JOIN 子句來連接 table1 和 table2。連接的條件是 table1 中的某一列與 table2 中的某一列具有相同的值。

• SET table1.column = value: 這裡指定了要在 table1 中更新哪一列，以及新的值是什麼。

RETURNING: 回傳被更新的記錄（僅在某些資料庫系統中可用）。

UPDATE table_name SET column = value RETURNING column;

為什麼要用 RETURNING?

1. 動態更新：如果更新操作涉及到某種計算或條件判斷，RETURNING 可以立即提供更新後的值。

   UPDATE inventory SET stock = stock - 1 WHERE product_id = 101 RETURNING stock;
   

   這會回傳更新後的庫存數量，確認庫存。

2. 大量更新：如果一次更新多行，RETURNING 可以回傳所有被更新的行。

   UPDATE students SET age = age + 1 WHERE grade = 'Freshman' RETURNING id, age;
   

   這會回傳所有年級為 'Freshman' 的學生的 id 和更新後的 age。

3. 交易日誌或會計：記錄哪些資料被更改。

基本操作：Delete

DELETE

只刪除資料表中的資料，不刪除資料表結構（即列和索引等）。
可以有條件地刪除資料。

DELETE FROM table_name WHERE condition;

• 可以使用 ROLLBACK 指令來撤銷。

DROP

一旦執行，將不可回復。
因此，在使用時，需要特別小心以免刪除重要的資料或結構。

- 刪除整個資料表或資料庫，包括其中的所有資料和結構。
DROP TABLE table_name;

以下是它們的主要差異：

（觸發器（Trigger）是一種儲存在資料庫中的程式，它會在某個特定的資料庫事件發生時自動執行。例如 INSERT、UPDATE、DELETE 等操作。）

面試題

Column & Field 的差別？

SQL 跟 NoSQL 的差別？

SQL 是一種關聯式資料庫，適用於有固定結構和高度組織化的資料。NoSQL 則是非關聯式的，更適合儲存非結構化或半結構化的資料。

SQL 跟 MySQL 的差別？

SQL 是查詢語言，用於資料庫的查詢操作。MySQL 是一種關聯式資料庫管理系統，使用 SQL 作為其查詢語言。

SQL 的 constraint 是什麼？有哪些？

SQL 的約束（Constraint）用於限制資料表中資料的類型、範圍或者關聯，以維持資料的一致性和完整性。以下是一些常見的 SQL 約束：

1. PRIMARY KEY：用於唯一識別資料表中的每一條記錄。一個資料表只能有一個主鍵。

   CREATE TABLE Persons (
     ID INT PRIMARY KEY,
     LastName VARCHAR(255),
     FirstName VARCHAR(255)
   );
   

2. FOREIGN KEY：用於建立兩個資料表之間的關聯。

   CREATE TABLE Orders (
     OrderID INT PRIMARY KEY,
     CustomerID INT,
     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
   );
   

3. UNIQUE：規定某一列（或多列組合）的每個值都是唯一的。

   CREATE TABLE Persons (
     ID INT UNIQUE,
     LastName VARCHAR(255),
     FirstName VARCHAR(255)
   );
   

4. NOT NULL：某一列不能有 NULL 值。

   CREATE TABLE Persons (
     ID INT NOT NULL,
     LastName VARCHAR(255) NOT NULL,
     FirstName VARCHAR(255)
   );
   

5. CHECK：某一列的值必須滿足某個條件。

   CREATE TABLE Persons (
     ID INT,
     Age INT CHECK (Age >= 18)
   );
   

6. DEFAULT：為某一列設定預設值。

   CREATE TABLE Persons (
     ID INT,
     Country VARCHAR(255) DEFAULT 'Taiwan'
   );
   

7. INDEX：雖然不是約束，但索引用於提高資料檢索的速度。

   CREATE INDEX idx_lastname
   ON Persons (LastName);
   

8. AUTO_INCREMENT：自動遞增列的值，通常用於主鍵。

   CREATE TABLE Persons (
     ID INT AUTO_INCREMENT PRIMARY KEY,
     LastName VARCHAR(255),
     FirstName VARCHAR(255)
   );
   

這些約束可以在 CREATE TABLE 語句中定義，也可以在資料表創建後使用 ALTER TABLE 語句來新增或修改。約束有助於維護資料庫的資料品質和完整性。

請解釋 MySQL index 是什麼？為什麼它很重要以及如何使用它？

Ans:

1. Index 是什麼？
   Index 對於資料庫的查詢非常重要，類似於書籍的目錄，可以快速查找關鍵字在哪一頁，這樣就不用一頁一頁慢慢翻，可以想像翻一本國語字典，但是沒有目錄，這樣要查一個字就要花很久的時間。index 並且具有唯一值（不能重複）的特性，同時 index 也是 primary key ，當資料表 A 跟資料表 B 有關聯的時候，資料表 A 的 primary key 就會變成資料表 B 的 foreigne key ，進而可以透過在資料表 B 的 foreigne key 來查詢資料表 A 。但也不是說 index 很好用就狂生 index ，index 會佔儲存空間。

2. 使用的方式：
   在 MySQL 中，可以使用 CREATE INDEX 或 ALTER TABLE 關鍵字建立 index。假設是經常使用 tel 進行查詢 users，就可以在 tel 上建立 index：

CREATE INDEX idx_tel ON users (tel);

或者表格已經存在：

ALTER TABLE users ADD INDEX idx_tel (tel);

下面SQL的指令目的為何？哪裡有錯誤？

sql
SELECT name, AVG(score) AS avg_score FROM scores 
GROUP BY name
WHERE avg_score > 60;

可以使用下面連結測試
http://sqlfiddle.com/#!9/f5a745/2

Ans:

1. 此指令的目的：

(1)從 scores 資料表中選取 name 和 score 欄位。
(2)result 會先根據 name 進行分組，相同的 name 會組成一組。
(3)對每一組使用 AVG() 計算各組 score 的平均值。
(4)平均值會被重新命名為 avg_score。

2.SQL 指令字使用有誤。

GROUP BY 應該出現在 WHERE 之後。但在上面的查詢中，不能直接使用 WHERE avg_score > 60，因為 avg_score 是一個聚合後的結果。若要篩選聚合後的結果，應該使用 HAVING 而非 WHERE。
正確應該是：

SELECT name, AVG(score) AS avg_score 
FROM scores
GROUP BY name
HAVING avg_score > 60;

解釋什麼是 SQL Injection，如何防止？

SQL Injection 是一種攻擊手法，攻擊者通過輸入欄位注入惡意的 SQL 代碼，以達到非法存取資料庫的目的。防止的方法包括使用預備語句（Prepared Statements）、輸入驗證和使用 ORM 工具。

什麼是 Normalization？

正規化是一種資料庫設計技巧，用於減少資料重複和提高資料完整性。通常分為 1NF, 2NF, 3NF 等階段。

解釋什麼是 ACID 屬性？

ACID 是資料庫事務的四個基本屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。

什麼是索引（Index），它是如何進行的？

索引是一種資料結構，用於加速資料庫查詢。它會建立一個指向資料表中資料的指標，使得查詢操作更快。

解釋 JOIN，並列舉其類型。

JOIN 用於將兩個或多個資料表根據某個共同欄位連接起來。主要類型有 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN 等。

國慶日快樂！
祝我明天面試順利