MySQL

從零開始的MySQL學習

第一節 數據庫

重點：數據庫通過SQL等標準語言進行動作，數據庫的概念、分類，數據管理系統（操縱和管理數據庫的大型軟件）

數據庫（Database）

是按照數據結構來組織、存儲和管理數據的倉庫。它允許用戶高效地訪問、插入、更新和刪除數據。

數據庫的相關概念

1. 數據（Data）

數據是描述事物的符號記錄，可以是數字、文字、圖形、聲音等。在數據庫中，數據是被組織和存儲的基本對象。

2. 數據庫管理系統（DBMS）

數據庫管理系統（Database Management System，DBMS）是位于用戶和操作系統之間的一層數據管理軟件。它為用戶或應用程序提供訪問數據庫的方法，包括數據定義、數據操縱、數據控制等功能。

3. 數據庫系統（DBS）

數據庫系統（Database System，DBS）是指在計算機系統中引入數據庫后的系統。它由數據庫、數據庫管理系統、數據庫管理員、硬件平臺和軟件平臺組成。

4. 數據模型（Data Model）

數據模型是數據庫中數據特征的描述，是數據庫系統的核心和基礎。常見的數據模型包括：

• 層次模型（Hierarchical Model）：數據以樹形結構組織，每個節點有且僅有一個父節點（根節點除外）。
• 網狀模型（Network Model）：數據以網狀結構組織，允許一個節點有多個父節點。
• 關系模型（Relational Model）：數據以表格形式組織，每個表稱為一個關系，表中的每一行稱為一個元組，每一列稱為一個屬性。

5. 關系數據庫（Relational Database）

關系數據庫是基于關系模型的數據庫。它使用表格來存儲數據，每個表格由行（記錄）和列（字段）組成。關系數據庫是最常用的一種數據庫類型，具有簡單、易懂、易于維護等優點。

6. SQL（Structured Query Language）

SQL 是一種用于管理和操作關系數據庫的標準編程語言。它提供了數據定義（DDL）、數據操縱（DML）、數據控制（DCL）等功能。常見的 SQL 語句包括：

• 數據定義：CREATE TABLE、ALTER TABLE、DROP TABLE 等。
• 數據操縱：INSERT、SELECT、UPDATE、DELETE 等。
• 數據控制：GRANT、REVOKE 等。

7. 數據庫設計（Database Design）

數據庫設計是創建一個滿足用戶需求的數據庫的過程。它包括需求分析、概念設計（如 E-R 圖）、邏輯設計（如關系模式）、物理設計（如存儲結構）等步驟。

8. 事務（Transaction）

事務是數據庫中一系列的操作，這些操作要么全部成功，要么全部失敗。事務具有四個特性，即 ACID 特性：

• 原子性（Atomicity）：事務中的所有操作要么全部完成，要么全部不完成。
• 一致性（Consistency）：事務執行前后，數據庫的狀態保持一致。
• 隔離性（Isolation）：并發執行的事務之間相互隔離，互不干擾。
• 持久性（Durability）：事務一旦提交，其結果就是永久的，即使系統故障也不會丟失。

9. 索引（Index）

索引是數據庫中用于加速數據檢索的一種數據結構。它類似于書籍的目錄，通過索引可以快速定位到數據的位置，從而提高查詢效率。

10. 視圖（View）

視圖是基于一個或多個表的虛擬表，其內容由 SQL 查詢定義。視圖不存儲數據，而是存儲查詢語句，當查詢視圖時，數據庫會動態生成數據。

數據庫的類型

1. 關系數據庫（Relational Database）

   • 代表：MySQL、PostgreSQL、Oracle、SQL Server。
   • 特點：使用表格存儲數據，支持 SQL 語言，具有強大的事務處理能力。

2. 非關系數據庫（NoSQL Database）

   • 代表：MongoDB、Redis、Cassandra。
   • 特點：不使用表格存儲數據，支持靈活的數據模型，適合處理大規模分布式數據。

3. 內存數據庫（In-Memory Database）

   • 代表：Redis、Memcached。
   • 特點：數據存儲在內存中，訪問速度極快，適合需要快速讀寫的應用。

4. 對象關系數據庫（Object-Relational Database）

   • 代表：PostgreSQL。
   • 特點：結合了關系數據庫和面向對象數據庫的特點，支持復雜數據類型和方法。

MySQL數據模型：

• 客戶端—>DBMS—>數據庫—>表

第二節 SQL

SQL分類

前四個分類為基礎

1. 數據定義語言（DDL，Data Definition Language）

DDL 提供了定義和修改數據庫結構的語句，包括創建、修改和刪除數據庫對象（如表、索引、視圖、觸發器等）。

• CREATE：創建新的數據庫對象。
• ALTER：修改現有數據庫對象的結構。
• DROP：刪除數據庫對象。
• TRUNCATE：快速刪除表中的所有行，但不刪除表本身。

2. 數據操縱語言（DML，Data Manipulation Language）

DML 提供了訪問和修改數據庫中數據的語句，包括插入、更新和刪除數據。

• INSERT：向表中插入新數據。
• UPDATE：修改表中的現有數據。
• DELETE：從表中刪除數據。

3. 數據查詢語言（DQL，Data Query Language）

DQL 主要包括 SELECT 語句，用于查詢數據庫中的數據。

• SELECT：從數據庫中檢索數據。

4. 數據控制語言（DCL，Data Control Language）

DCL 提供了控制數據庫訪問權限的語句，包括授予和撤銷用戶對數據庫對象的訪問權限。

• GRANT：授予用戶或角色對數據庫對象的特定權限。
• REVOKE：撤銷用戶或角色的權限。

5. 事務控制語言（TCL，Transaction Control Language）

TCL 提供了管理數據庫事務的語句，確保數據的一致性和完整性。

• BEGIN 或 START TRANSACTION：開始一個新的事務。
• COMMIT：提交事務，使自事務開始以來對數據庫的所有更改成為永久性更改。
• ROLLBACK：回滾事務，撤銷自事務開始以來對數據庫的所有更改。
• SAVEPOINT：設置事務的保存點，允許部分回滾。

6. 數據訪問語言（DAL，Data Access Language）

雖然不是 SQL 標準的一部分，但 DAL 通常指的是用于訪問數據庫的編程接口和工具，如 JDBC、ODBC、OLE DB 等。

7. 存儲過程和函數

存儲過程和函數是一組為了完成特定功能的 SQL 語句，它們被預先編寫并存儲在數據庫中，可以通過一個調用語句來執行。

• CREATE PROCEDURE：創建存儲過程。
• CREATE FUNCTION：創建函數。

8. 觸發器（Triggers）

觸發器是數據庫中的一種特殊類型的存儲過程，它們在特定的數據庫事件（如 INSERT、UPDATE 或 DELETE）發生時自動執行。

1. CREATE TRIGGER：創建觸發器。

具體操作

一.DDL

1. 數據查詢（SELECT）

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column;

2. 數據插入（INSERT）

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

3. 數據更新（UPDATE）

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

4. 數據刪除（DELETE）

DELETE FROM table_name
WHERE condition;

5. 創建表（CREATE TABLE）

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

6. 修改表結構（ALTER TABLE）

ALTER TABLE table_name
ADD column_name datatype;

7. 刪除表（DROP TABLE）

DROP TABLE table_name;

8. 創建索引（CREATE INDEX）

CREATE INDEX index_name
ON table_name (column);

9. 刪除索引（DROP INDEX）

DROP INDEX index_name;

10. 創建視圖（CREATE VIEW）

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

11. 刪除視圖（DROP VIEW）

DROP VIEW view_name;

12. 創建存儲過程（CREATE PROCEDURE）

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN-- SQL statements
END;

13. 調用存儲過程（CALL）

CALL procedure_name(value1, value2, ...);

14. 刪除存儲過程（DROP PROCEDURE）

DROP PROCEDURE procedure_name;

15. 創建觸發器（CREATE TRIGGER）

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
BEGIN-- SQL statements
END;

16. 刪除觸發器（DROP TRIGGER）

DROP TRIGGER trigger_name;

17. 設置權限（GRANT）

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

18. 撤銷權限（REVOKE）

REVOKE SELECT, INSERT ON database_name.table_name FROM 'username'@'host';

19. 創建數據庫（CREATE DATABASE）

CREATE DATABASE database_name;

20. 刪除數據庫（DROP DATABASE）

DROP DATABASE database_name;

列出數值類型

類型	大小（字節）	有符號范圍	無符號范圍	精度（小數點后位數）	用途
TINYINT	1	-128 到 127	0 到 255	不適用	小范圍整數
SMALLINT	2	-32768 到 32767	0 到 65535	不適用	中等范圍整數
MEDIUMINT	3	-8388608 到 8388607	0 到 16777215	不適用	大范圍整數
INT	4	-2147483648 到 2147483647	0 到 4294967295	不適用	通用整數
BIGINT	8	-9223372036854775808 到 9223372036854775807	0 到 18446744073709551615	不適用	極大范圍整數
FLOAT	4	±1.17549435 × 10^?38 到 ±3.40282347 × 10^38	±1.17549435 × 10^?38 到 ±3.40282347 × 10^38	不適用	單精度浮點數
DOUBLE	8	±2.2250738585072014 × 10^?308 到 ±1.7976931348623157 × 10^308	±2.2250738585072014 × 10^?308 到 ±1.7976931348623157 × 10^308	不適用	雙精度浮點數
DECIMAL	動態	取決于數值和精度	取決于數值和精度	可指定	精確的十進制表示
BIT	動態	不適用	不適用	不適用	位字段
BOOLEAN	1	不適用	不適用	不適用	布爾值

字符串類型

類型	大小限制	描述
CHAR(n)	0 到 255 字節	固定長度字符串。不足部分用空格填充。
VARCHAR(n)	0 到 65,535 字節	可變長度字符串。存儲時只占用實際需要的空間加上一個長度字節。
TINYTEXT	0 到 255 字節	短文本字符串。
TEXT	0 到 65,535 字節	長文本字符串。
MEDIUMTEXT	0 到 16,777,215 字節	較長文本字符串。
LONGTEXT	0 到 4,294,967,295 字節	非常長的文本字符串。
ENUM	1 或 2 字節	枚舉類型，由一組預定義的字符串值組成。
SET	1、2、3、4 或 8 字節	集合類型，由一組預定義的字符串值組成，可以存儲多個值。
BINARY(n)	0 到 255 字節	固定長度的二進制字符串。
VARBINARY(n)	0 到 65,535 字節	可變長度的二進制字符串。
TINYBLOB	0 到 255 字節	短二進制數據。
BLOB	0 到 65,535 字節	長二進制數據。
MEDIUMBLOB	0 到 16,777,215 字節	較長二進制數據。
LONGBLOB	0 到 4,294,967,295 字節	非常長的二進制數據。
VALCHAR相對于CHAR性能差一些

日期類型

類型	格式/范圍	描述
DATE	YYYY-MM-DD	存儲日期，格式為年-月-日。
	范圍：0001-01-01 至 9999-12-31
TIME	HH:MM:SS	存儲時間，格式為小時:分鐘:秒。
	范圍：-838:59:59 至 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS	存儲日期和時間，格式為年-月-日 時:分:秒。
	范圍：0001-01-01 00:00:00 至 9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD HH:MM:SS	存儲日期和時間，通常用于自動生成的時間戳。
	范圍：1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC
	范圍：0001-01-01 00:00:00 至 9999-12-31 23:59:59（有符號）
YEAR	YYYY	存儲年份，格式為四位數字。
	范圍：1901 至 2155

舉例

現在我們創建一張表舉例

CREATE TABLE EMP (ID INT,WORKNO VARCHAR(10) COMMENT '工號',NAME VARCHAR(10) COMMENT '姓名',GENDER CHAR(1) COMMENT '性別',AGE TINYINT UNSIGNED COMMENT '年齡',IDCARD CHAR(18) COMMENT '身份證',ENTRYDATE DATE COMMENT '入職時間'
) COMMENT '員工表';
--一些操作
ALTER TABLE EMP ADD NICKNAME VARCHAR(20) COMMENT '昵稱';
ALTER TABLE EMP MODIFY ;
ALTER TABLE EMP CHANGE NICKNAME USERNANE VARCHAR (30) COMMENT'用戶名';
ALTER TABLE EMP DROP USERNAME;
ALTER TABLE EMP RENAME TO EMPLOY;
DROP TABLE IF EXISTS EMPLOY;//刪除表，所有數據也跟著被刪除
TRUNCATE TABLE EMPLOY;//留下空表，刪除所有數據

注意事項：

中英文的輸入如引號分號等，括號不是大括號，COMMENT后面要有一個空格。

二.MySQL圖形化

三.DML(增刪改)

INSERT INTO EMP (ID,WORKNO,NAME,GENDER,AGE,IDCARD,ENTRYDATE) VALUES (1,'1','A','男',10,'123456789012345678','2000-01-01');  SELECT * FROM EMP;UPDATE EMP SET NAME = 'YOU',GENDER = '女' WHERE ID = 1;UPDATE EMP SET NAME = 'YOUNG' WHERE ID = 1;DELETE FROM EMP WHERE GENDER = '女';

四.DQL（查詢）

核心SELECT語句

SELECT NAME,WORKNO,AGE FROM ENP;SELECT * FROM EMP WHERE ID = 1;實際中盡量不要用*，不直觀，如此處直接把所有要查詢的輸出來就行SELECT NAME AS 'XINGMIN' FROM EMP;//AS可以省略SELECT  DISTINCT NAME FROM EMP;//查詢沒有重復

比較運算符

1. 等于 (=)：

   • 檢查兩個值是否相等。
   • 示例：SELECT * FROM table_name WHERE column_name = 'value';

2. 不等于 (<> 或 !=)：

   • 檢查兩個值是否不相等。
   • 示例：SELECT * FROM table_name WHERE column_name <> 'value';

3. 大于 (>)：

   • 檢查左邊的值是否大于右邊的值。
   • 示例：SELECT * FROM table_name WHERE column_name > 10;

4. 小于 (<)：

   • 檢查左邊的值是否小于右邊的值。
   • 示例：SELECT * FROM table_name WHERE column_name < 5;

5. 大于等于 (>=)：

   • 檢查左邊的值是否大于或等于右邊的值。
   • 示例：SELECT * FROM table_name WHERE column_name >= 20;

6. 小于等于 (<=)：

   • 檢查左邊的值是否小于或等于右邊的值。
   • 示例：SELECT * FROM table_name WHERE column_name <= 50;

7. IS NULL：

   • 檢查值是否為 NULL。
   • 示例：SELECT * FROM table_name WHERE column_name IS NULL;

8. IS NOT NULL：

   • 檢查值是否不為 NULL。
   • 示例：SELECT * FROM table_name WHERE column_name IS NOT NULL;

9. BETWEEN：

   • 檢查一個值是否在兩個指定的值之間（包括邊界值）。
   • 示例：SELECT * FROM table_name WHERE column_name BETWEEN 10 AND 20;

10. IN：

    • 檢查一個值是否包含在一組指定的值中。
    • 示例：SELECT * FROM table_name WHERE column_name IN ('value1', 'value2', 'value3');

11. LIKE：

    • 檢查一個值是否符合指定的模式（使用通配符 % 和 _）。
    • 示例：SELECT * FROM table_name WHERE column_name LIKE '%value%';

12. REGEXP 或 RLIKE（取決于數據庫系統）：

    • 檢查一個值是否符合指定的正則表達式模式。
    • 示例：SELECT * FROM table_name WHERE column_name REGEXP '^value.*';

邏輯運算符

1. AND：

   • 邏輯“與”運算符。只有當所有條件都為 true 時，結果才為 true。
   • 示例：SELECT * FROM table_name WHERE condition1 AND condition2;

2. OR：

   • 邏輯“或”運算符。只要有一個條件為 true，結果就為 true。
   • 示例：SELECT * FROM table_name WHERE condition1 OR condition2;

3. NOT：

   • 邏輯“非”運算符。用于反轉條件的邏輯值，將 true 轉換為 false，將 false 轉換為 true。
   • 示例：SELECT * FROM table_name WHERE NOT condition;

4. XOR：

   • 邏輯“異或”運算符。當且僅當兩個條件的邏輯值不同時，結果為 true。
   • 示例：SELECT * FROM table_name WHERE condition1 XOR condition2;（注意：并非所有數據庫系統都支持 XOR 運算符。）

使用邏輯運算符的注意事項：

• 優先級：邏輯運算符具有不同的優先級。通常，NOT 的優先級最高，其次是 AND，然后是 OR。可以使用括號來明確指定運算順序。
• 括號：在復雜的邏輯表達式中，使用括號來分組條件，確保邏輯運算的順序符合你的預期。
• 性能：在某些情況下，邏輯運算符的使用可能會影響查詢性能。合理地構建查詢條件可以提高查詢效率。

示例

假設有一個名為 employees 的表，包含員工的 age 和 salary 列，可以使用邏輯運算符來構建復雜的查詢：

-- 選擇年齡大于30且工資高于5000的員工
SELECT * FROM employees WHERE age > 30 AND salary > 5000;-- 選擇年齡小于30或工資低于5000的員工
SELECT * FROM employees WHERE age < 30 OR salary < 5000;-- 選擇工資高于5000但年齡不大于40的員工
SELECT * FROM employees WHERE salary > 5000 AND NOT (age > 40);-- 選擇年齡大于30或工資高于5000，但不是兩者都滿足的員工
SELECT * FROM employees WHERE (age > 30 XOR salary > 5000);

基本查詢

1. 查詢所有列：

   SELECT * FROM table_name;
   

2. 查詢特定列：

   SELECT column1, column2 FROM table_name;
   

3. 查詢符合條件的行：

   SELECT * FROM table_name WHERE condition;
   

4. 查詢結果排序：

   SELECT * FROM table_name ORDER BY column ASC;  -- 升序
   SELECT * FROM table_name ORDER BY column DESC; -- 降序
   

5. 查詢結果分頁（MySQL）：

   SELECT * FROM table_name LIMIT 10 OFFSET 20; -- 從第21行開始，取10行
   

聚合查詢

6. 使用聚合函數：

   SELECT COUNT(*) FROM table_name; -- 計算行數
   SELECT SUM(column) FROM table_name; -- 計算列的總和
   SELECT AVG(column) FROM table_name; -- 計算列的平均值
   SELECT MAX(column), MIN(column) FROM table_name; -- 找出列的最大值和最小值
   

7. 分組查詢：

   SELECT column, COUNT(*) FROM table_name GROUP BY column;
   

8. 過濾分組后的結果（使用 HAVING 子句）：

   SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 1;
   

連接查詢

9. 內連接（INNER JOIN）：

   SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
   

10. 左連接（LEFT JOIN）：

    SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
    

11. 右連接（RIGHT JOIN）：

    SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
    

子查詢

12. 在 SELECT 語句中使用子查詢：

    SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM other_table);
    

13. 在 FROM 子句中使用子查詢：

    SELECT * FROM (SELECT column_name FROM table_name) AS derived_table;
    

聯合查詢

14. 合并多個查詢結果（使用 UNION）：

    SELECT column_name FROM table1
    UNION
    SELECT column_name FROM table2;
    

15. 合并多個查詢結果并去重（使用 UNION DISTINCT 或 DISTINCT）：

    SELECT DISTINCT column_name FROM table1
    UNION DISTINCT
    SELECT DISTINCT column_name FROM table2;
    

直接說似乎不易理解其執行順序，這里給出樣例

1. FROM：

   • 首先，DBMS 處理 FROM 子句，這涉及到從指定的表或子查詢中檢索數據。

2. JOIN：

   • 如果查詢中包含 JOIN 操作，DBMS 將執行表的連接操作，根據指定的連接條件合并表中的數據。

3. WHERE：

   • WHERE 子句在 FROM 和 JOIN 之后執行，用于過濾結果集，只保留滿足條件的行。

4. GROUP BY：

   • 如果查詢包含 GROUP BY 子句，DBMS 將對 WHERE 過濾后的結果集進行分組。

5. HAVING：

   • HAVING 子句在 GROUP BY 之后執行，用于過濾分組后的結果，只保留滿足特定條件的組。

6. SELECT：

   • SELECT 子句指定了查詢需要返回的列。在這個階段，DBMS 會從處理過的數據中選擇指定的列。

7. DISTINCT：

   • 如果查詢中包含 DISTINCT 關鍵字，DBMS 將從 SELECT 列表中移除重復的行。

8. ORDER BY：

   • 最后，ORDER BY 子句對查詢結果進行排序，按照指定的列和順序返回最終結果。

9. LIMIT：

   • 在某些數據庫系統中，如 MySQL，LIMIT 子句用于限制返回的行數，通常在 ORDER BY 之后執行。

示例查詢及其執行順序

考慮以下查詢：

SELECT DISTINCT column1, column2
FROM table1
INNER JOIN table2 ON table1.id = table2.id
WHERE column1 > 100
GROUP BY column2
HAVING COUNT(*) > 5
ORDER BY column1 DESC
LIMIT 10;

執行順序如下：

1. FROM：從 table1 和 table2 中檢索數據。
2. INNER JOIN：根據 table1.id = table2.id 連接表。
3. WHERE：過濾 column1 > 100 的行。
4. GROUP BY：按 column2 分組。
5. HAVING：過濾 COUNT(*) > 5 的組。
6. SELECT：選擇 column1 和 column2。
7. DISTINCT：移除重復的行。
8. ORDER BY：按 column1 DESC 排序。
9. LIMIT：限制結果為前 10 行。

五.DCL

DCL 包括的常用 SQL 命令主要有 GRANT 和 REVOKE。

當然，以下是一些使用 DCL（數據控制語言）的 GRANT 和 REVOKE 命令的例子，這些命令用于管理用戶權限：

GRANT 命令的例子

1. 授予用戶對特定表的 SELECT 和 INSERT 權限：

   GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';
   

   這條命令授予名為 username 的用戶對 database_name 數據庫中 table_name 表的查詢（SELECT）和插入（INSERT）權限。

2. 授予用戶對所有表的 SELECT 權限：

   GRANT SELECT ON database_name.* TO 'username'@'host';
   

   這條命令授予用戶對 database_name 數據庫中所有表的查詢（SELECT）權限。

3. 授予用戶對數據庫的所有權限，并允許其將權限授予其他用戶：

   GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host' WITH GRANT OPTION;
   

   這條命令授予用戶所有權限，并允許該用戶將這些權限授予其他用戶。

4. 授予用戶對數據庫中所有新創建的表和存儲過程的權限：

   GRANT SELECT, INSERT ON database_name.* TO 'username'@'host' WITH GRANT OPTION;
   

   這條命令授予用戶對現有和未來創建的所有表的 SELECT 和 INSERT 權限。

REVOKE 命令的例子

1. 撤銷用戶對特定表的 INSERT 權限：

   REVOKE INSERT ON database_name.table_name FROM 'username'@'host';
   

   這條命令撤銷了之前授予 username 用戶對 database_name 數據庫中 table_name 表的 INSERT 權限。

2. 撤銷用戶對數據庫的所有權限：

   REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
   

   這條命令撤銷了用戶對 database_name 數據庫的所有權限。

3. 撤銷用戶授予其他用戶的權限：

   REVOKE GRANT OPTION ON database_name.* FROM 'username'@'host';
   

   這條命令撤銷了用戶 username 授予其他用戶權限的能力。

4. 級聯撤銷用戶對特定表的所有權限：

   REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host' CASCADE;
   

   這條命令不僅撤銷了用戶對 table_name 表的所有權限，還撤銷了該用戶可能已經授予其他用戶的權限。

在使用這些命令時，需要替換 database_name、table_name、username 和 host 為實際的數據庫名、表名、用戶名和主機名。此外，確保有足夠的權限來授予或撤銷權限，通常是數據庫管理員（DBA）或具有相應權限的用戶。

第三節 函數

字符串函數

1. CONCAT()

用于連接兩個或多個字符串。

SELECT CONCAT('Hello', ' ', 'World') AS ConcatenatedString;

結果：Hello World

2. LENGTH() 或 CHAR_LENGTH()

返回字符串的長度。

SELECT LENGTH('Hello World') AS StringLength;

結果：11

3. SUBSTRING() 或 SUBSTR()

從字符串中提取子字符串。

SELECT SUBSTRING('Hello World', 1, 5) AS SubString;

結果：Hello

4. POSITION() 或 INSTR()

返回子字符串在字符串中的位置。

SELECT POSITION('World' IN 'Hello World') AS Position;

結果：7

5. REPLACE()

替換字符串中的某些字符。

SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedString;

結果：Hello SQL

6.LTRIM() 和 RTRIM()

分別去除字符串左側和右側的空格。

SELECT LTRIM('  Hello World') AS LeftTrimmed, RTRIM('Hello World  ') AS RightTrimmed;

結果：Hello World 和 Hello World

7. TRIM()

去除字符串兩側的空格。

SELECT TRIM('  Hello World  ') AS TrimmedString;

結果：Hello World

8. UPPER() 和 LOWER()

分別將字符串轉換為大寫和轉換為小寫。

SELECT UPPER('Hello World') AS UpperString, LOWER('Hello World') AS LowerString;

結果：HELLO WORLD 和 hello world

9. LEFT() 和 RIGHT()

分別從字符串的左側和右側提取指定數量的字符。

SELECT LEFT('Hello World', 5) AS LeftString, RIGHT('Hello World', 5) AS RightString;

結果：Hello 和 World

10. REVERSE()

反轉字符串。

SELECT REVERSE('Hello World') AS ReversedString;

結果：dlroW olleH
在 SQL 中，數值函數用于執行各種數值計算，包括數學運算、四舍五入、取整、隨機數生成等。以下是一些常見的數值函數：

數值函數

1. ABS(x)

返回指定數值的絕對值。

SELECT ABS(-10) AS AbsoluteValue;

結果：10

2. CEILING(x)

返回大于或等于給定數值的最小整數。

SELECT CEILING(3.14) AS CeilingValue;

結果：4

3. FLOOR(x)

返回小于或等于給定數值的最大整數。

SELECT FLOOR(3.14) AS FloorValue;

結果：3

4. ROUND(x)

將給定數值四舍五入到最接近的整數。

SELECT ROUND(3.14159) AS RoundedValue;

結果：3

5. TRUNCATE(x, d)

截斷一個數字到指定的小數位數。

SELECT TRUNCATE(3.14159, 2) AS TruncatedValue;

結果：3.14

6. POWER(x, y) 或 EXP(x)

返回 x 的 y 次冪。

SELECT POWER(2, 3) AS PowerValue;

結果：8

7. SQRT(x)

返回給定數值的平方根。

SELECT SQRT(9) AS SquareRoot;

結果：3

8. LOG(x)

返回給定數值的自然對數（以 e 為底）。

SELECT LOG(2.718281828) AS NaturalLogarithm;

結果：1

9. LN(x)

返回給定數值的自然對數（以 e 為底）。

SELECT LN(2.718281828) AS NaturalLogarithm;

結果：1

10. MOD(x, y)

返回 x 除以 y 的余數。

SELECT MOD(7, 3) AS Modulus;

結果：1

11. SIGN(x)

返回給定數值的符號，正數返回 1，負數返回 -1，零返回 0。

SELECT SIGN(-5) AS SignValue;

結果：-1

12. PI()

返回圓周率 π 的值。

SELECT PI() AS PiValue;

結果：3.141592653589793

13. RANDOM() 或 RAND()

返回一個隨機浮點數，范圍從 0 到 1。

SELECT RANDOM() AS RandomValue;

結果：0.123456789（示例值，實際值隨機）

14. TRUNCATETABLE(x, y)

截斷數值 x 到 y 位小數（與 TRUNCATE 相似）。

SELECT TRUNCATETABLE(123.45678, 2) AS TruncatedValue;

結果：123.45

日期函數

1. CURRENT_DATE 或 CURDATE()

返回當前日期。

SELECT CURRENT_DATE;

2. CURRENT_TIME 或 CURTIME()

返回當前時間。

SELECT CURRENT_TIME;

3. NOW() 或 CURRENT_TIMESTAMP

返回當前日期和時間。

SELECT NOW();

4. DATE_ADD(date, INTERVAL expr type)

給定日期加上一個時間間隔。

SELECT DATE_ADD('2024-01-01', INTERVAL 10 DAY);

結果：2024-01-11

5. DATE_SUB(date, INTERVAL expr type)

從給定日期減去一個時間間隔。

SELECT DATE_SUB('2024-01-15', INTERVAL 10 DAY);

結果：2024-01-05

6. DATEDIFF(date1, date2)

返回兩個日期之間的天數差異。

SELECT DATEDIFF('2024-01-05', '2024-01-01');

結果：4

7. DATE_FORMAT(date, format)

將日期格式化為字符串。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

結果：格式化后的當前日期和時間字符串

8. STR_TO_DATE(string, format)

將字符串轉換為日期，根據提供的格式。

SELECT STR_TO_DATE('2024-06-13', '%Y-%m-%d');

結果：2024-06-13

9. ADDDATE(date, interval)

給定日期加上一個時間間隔（年、月、日等）。

SELECT ADDDATE('2024-01-01', INTERVAL 1 MONTH);

結果：2024-02-01

10. LAST_DAY(date)

返回一個月份的最后一天。

SELECT LAST_DAY('2024-01-15');

結果：2024-01-31

11. MONTH(), DAY(), YEAR(), HOUR(), MINUTE(), SECOND()

從日期時間值中提取特定的部分。

SELECT YEAR('2024-06-13 12:30:00'), MONTH('2024-06-13 12:30:00'), DAY('2024-06-13 12:30:00');

結果：2024 6 13

流程函數

1. 控制流程函數

這些函數主要用于控制 SQL 語句的執行流程，雖然它們并不直接類似于編程語言中的流程控制結構。

• CASE 表達式：類似于編程語言中的 if-else 語句，用于基于條件選擇不同的值。

  SELECT column1,CASE WHEN condition1 THEN result1WHEN condition2 THEN result2ELSE result3END AS new_column
  FROM table_name;
  

• IF() 函數（在某些數據庫系統中可用）：直接返回條件為真的結果。

  SELECT IF(column1 > 0, 'Positive', 'Non-positive') AS sign
  FROM table_name;
  

2. 窗口函數

窗口函數允許你對一組行執行計算，這些行與當前行有某種關系（例如，它們可能是查詢結果集中當前行的一部分）。

• ROW_NUMBER()：為結果集中的每行分配一個唯一的連續整數。

  SELECT ROW_NUMBER() OVER (ORDER BY column1) AS row_num, column1
  FROM table_name;
  

• RANK()：為結果集中的每行分配一個排名，相同值的行將獲得相同的排名。

  SELECT RANK() OVER (ORDER BY column1 DESC) AS rank, column1
  FROM table_name;
  

• DENSE_RANK()：類似于 RANK()，但排名之間沒有間隔。

  SELECT DENSE_RANK() OVER (ORDER BY column1 DESC) AS dense_rank, column1
  FROM table_name;
  

3. 遞歸查詢

在支持遞歸查詢的數據庫系統中（如 PostgreSQL、SQL Server 等），可以使用遞歸公用表表達式（CTE）來實現遞歸。

• 遞歸 CTE：用于執行遞歸查詢。

  WITH RECURSIVE cte (column1, column2) AS (SELECT column1, column2 FROM table_name WHERE conditionUNION ALLSELECT t.column1, t.column2 FROM table_name t INNER JOIN cte ON condition
  )
  SELECT * FROM cte;
  

第四節 約束

1. 主鍵約束（PRIMARY KEY）

確保列（或列的組合）中的每個值都是唯一的，并且不允許 NULL 值。

CREATE TABLE Employees (EmployeeID int NOT NULL,FirstName varchar(50),LastName varchar(50),Email varchar(100),PRIMARY KEY (EmployeeID)
);

2. 外鍵約束（FOREIGN KEY）

用于在兩個表之間建立鏈接，并確保引用的數據的完整性。

CREATE TABLE Orders (OrderID int NOT NULL,EmployeeID int,OrderDate date,PRIMARY KEY (OrderID),FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

3. 唯一約束（UNIQUE）

保證列中的所有值都是不同的。

CREATE TABLE Students (StudentID int NOT NULL,FirstName varchar(50),LastName varchar(50),Email varchar(100) UNIQUE
);

4. 非空約束（NOT NULL）

確保列中的值不能為 NULL。

CREATE TABLE Products (ProductID int NOT NULL,ProductName varchar(100) NOT NULL,Price decimal(10, 2)
);

5. 檢查約束（CHECK）

確保列中的值滿足特定的條件。

CREATE TABLE Products (ProductID int NOT NULL,ProductName varchar(100) NOT NULL,Price decimal(10, 2),CHECK (Price > 0)
);

6. 默認值約束（DEFAULT）

當沒有為列提供值時，將自動填充一個默認值。

CREATE TABLE Employees (EmployeeID int NOT NULL,FirstName varchar(50),LastName varchar(50),Email varchar(100),Salary decimal(10, 2) DEFAULT 50000,PRIMARY KEY (EmployeeID)
);

7. 級聯約束（CASCADE）

通常與外鍵約束一起使用，用于定義當被引用的鍵被更新或刪除時，應該對引用的鍵執行什么操作。

CREATE TABLE Orders (OrderID int NOT NULL,EmployeeID int,OrderDate date,PRIMARY KEY (OrderID),FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE ON UPDATE CASCADE
);

8. 索引（INDEX）

雖然不是標準的約束類型，但索引用于優化查詢性能，可以強制數據的排序和查找。

CREATE INDEX idx_lastname ON Employees (LastName);

第五節 多表查詢

一.多表查詢關系

多表查詢通常涉及到兩個或多個表之間的關系，這些關系可以是顯式的（如通過外鍵約束定義）或隱式的（在查詢中指定）。

1. 一對一關系（One-to-One Relationship）

在一對一關系中，一個表中的每一行只與另一個表中的一行相關聯。這種關系較少見，因為通常可以將這兩個表合并為一個表。

示例：

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.id = b.id;

2. 一對多關系（One-to-Many Relationship）

在一對多關系中，一個表中的一行可以與另一個表中的多行相關聯。這是最常見的關系類型，例如，一個客戶可以有多個訂單。

示例：

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

3. 多對多關系（Many-to-Many Relationship）

在多對多關系中，一個表中的多行可以與另一個表中的多行相關聯。這種關系通常需要通過一個中間表（也稱為聯結表或關聯表）來實現。

示例：

假設有兩個表 students 和 courses，它們通過中間表 enrollments 相關聯。

SELECT students.name, courses.name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

二.連接類型

在多表查詢中，可以使用不同類型的連接來檢索數據：

• 內連接（INNER JOIN）：只返回兩個表中匹配的行。
• 左連接（LEFT JOIN）：返回左表的所有行，即使右表中沒有匹配的行。
• 右連接（RIGHT JOIN）：返回右表的所有行，即使左表中沒有匹配的行。
• 全外連接（FULL OUTER JOIN）：返回兩個表中任一表的所有行，如果某一側沒有匹配的行，則該側的結果為 NULL。
• 內連接（INNER JOIN）是 SQL 中最常用的連接類型之一，用于結合兩個或多個表中相關的數據記錄。內連接只返回兩個表中匹配條件的行，即兩個表中都有對應值的記錄。

內連接

內連接的基本語法如下：

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

• column_list：你想從連接的結果中選擇的列列表。
• table1、table2：你想要連接的表名。
• common_field：兩個表中用于匹配行的共同列名。

注

不建議隱式內連接，無關鍵字而指代不清晰，如有需求再看

示例

假設有兩個表：Employees（員工表）和Departments（部門表），它們通過 DepartmentID 相關聯。

Employees 表：

• EmployeeID（員工ID）
• FirstName（名字）
• LastName（姓氏）
• DepartmentID（部門ID）

Departments 表：

• DepartmentID（部門ID）
• DepartmentName（部門名稱）

要獲取每位員工及其所在部門的名稱，可以使用以下 SQL 語句：

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

操作說明

1. 選擇相關列：從兩個表中選擇需要顯示的列。在這個例子中，選擇了員工的名字、姓氏和部門名稱。

2. 指定連接條件：使用 ON 子句指定連接條件，即 Employees.DepartmentID = Departments.DepartmentID。這表示只返回兩個表中 DepartmentID 匹配的行。

3. 返回匹配行：查詢結果將只包含 DepartmentID 在兩個表中都存在的行。

外連接

外連接（OUTER JOIN）是 SQL 中的一種連接類型，它用于返回兩個表中滿足連接條件的行，以及不滿足條件的行。外連接包括左外連接（LEFT JOIN）、右外連接（RIGHT JOIN）和全外連接（FULL JOIN）三種類型。

左外連接（LEFT JOIN）返回左表（LEFT JOIN 左邊的表）的所有行，即使右表中沒有匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有選擇列表列為空值。例如，以下語句展示了如何使用左外連接來獲取員工及其部門名稱，即使某些員工沒有分配部門：

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

左外連接的結果特點是包含左表所有記錄，右表無匹配時顯示NULL。

右外連接（RIGHT JOIN）與左外連接相反，它返回右表（RIGHT JOIN 右邊的表）的所有記錄，即使左表中沒有匹配的行。如果右表中的記錄在左表中沒有匹配，則左表的列會顯示為 NULL。例如：

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

右外連接的結果特點是包含右表所有記錄，左表無匹配時顯示NULL。

全外連接（FULL JOIN）結合了左外連接和右外連接的結果，包含兩個表中的所有記錄，無論是否有匹配。無匹配的部分顯示為 NULL。例如：

SELECT * FROM employees
LEFT JOIN departments USING(department_id)
UNION
SELECT * FROM employees
RIGHT JOIN departments USING(department_id);

全外連接的結果特點是包含兩個表所有記錄，無匹配部分顯示NULL。
左外連接（LEFT JOIN）和右外連接（RIGHT JOIN）是 SQL 中用于執行多表查詢的兩種不同的連接類型。每種連接類型都有其特定的用途和結果集，但在某些情況下，一個左外連接可以通過調整為右外連接，反之亦然。這種轉換可以通過改變連接的表和條件來實現。

左外連接轉換為右外連接

左外連接返回左表的所有行，即使右表中沒有匹配的行。如果右表中沒有匹配的行，則結果中右表的列會是 NULL。

要將左外連接轉換為右外連接，需要交換兩個表的位置，并調整 ON 子句中的條件。

示例：

假設有兩個表 employees 和 departments，其中 employees 表有一個外鍵 department_id 指向 departments 表的主鍵 id。

左外連接：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

轉換為右外連接：

SELECT departments.department_name, employees.name
FROM departments
RIGHT JOIN employees ON employees.department_id = departments.id;

自連接

自連接是一種特殊的數據庫連接操作，其中一個表與自身進行連接。

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b ON a.common_field = b.common_field;

• table_name 是進行自連接的表名。
• a 和 b 是該表的別名，用于區分連接的兩個部分。
• common_field 是用于連接的共同字段。

示例查詢：查找每位員工及其經理的姓名

SELECT a.name AS Employee, b.name AS Manager
FROM employees AS a
JOIN employees AS b ON a.manager_id = b.employee_id;

在這個查詢中：

• a 和 b 是 employees 表的別名。
• a 代表下屬員工，b 代表經理。
• 連接條件 a.manager_id = b.employee_id 確保每位員工與其經理正確匹配。

1. 別名：在自連接中，別名非常重要，因為它們幫助區分連接的兩個部分。
2. 連接條件：確保連接條件正確，以反映表中的實際關系。
3. 性能：自連接可能會影響查詢性能，特別是在大型數據集上。優化查詢和使用索引可以提高效率。

聯合連接

UNION 和 UNION ALL 是 SQL 中用于合并兩個或多個查詢結果集的關鍵詞，它們之間的主要區別在于是否去除重復行。

UNION

UNION 用于合并兩個或多個 SELECT 語句的結果集，并自動去除結果集中的重復行。每個 SELECT 語句必須具有相同數量的列，并且相應的列必須具有兼容的數據類型。

示例：

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

這個查詢會返回 customers 表和 suppliers 表中所有不重復的名字列表。

UNION ALL

UNION ALL（或簡單地寫作 UNION）與 UNION 類似，但它不會去除結果集中的重復行，即保留所有行，包括重復的行。

示例：

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

這個查詢會返回 customers 表和 suppliers 表中的所有名字，包括重復的名字。

區別

• 去重：UNION 會去除重復行，而 UNION ALL 不會。
• 性能：如果不需要去重，使用 UNION ALL 可能會更快，因為數據庫不需要額外處理來識別和去除重復行。

在實際使用中，選擇 UNION 還是 UNION ALL 取決于是否需要去除結果集中的重復數據。如果需要一個不包含重復項的列表，使用 UNION；如果需要包含所有項，包括重復的，使用 UNION ALL。

三.子查詢和派生表

• 子查詢：在主查詢中嵌套的查詢，通常用于篩選或計算。

  SELECT * FROM (SELECT column1, column2 FROM table1 WHERE condition
  ) AS derived_table;
  

  子查詢（Subquery）是嵌套在另一個查詢中的 SQL 查詢，它可以在 SELECT、INSERT、UPDATE、DELETE 語句中使用。子查詢通常用于從數據庫檢索數據，然后將其作為條件或值傳遞給外部查詢。

子查詢的基本用法

子查詢可以出現在 SQL 語句的 WHERE 子句、HAVING 子句、SELECT 列表、FROM 子句或 JOIN 子句中。

1. 在 WHERE 子句中使用子查詢

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table WHERE condition);

這個子查詢從 other_table 中選擇符合特定條件的 column_name 值，然后在外部查詢中檢查 table_name 中的 column_name 是否存在于這些值中。

2. 在 SELECT 列表中使用子查詢

SELECT column_name, (SELECT MAX(column_name) FROM other_table) AS max_value
FROM table_name;

這個子查詢為 table_name 的每一行檢索 other_table 中 column_name 的最大值。

3. 在 FROM 子句中使用子查詢（派生表）

SELECT a.column_name, b.column_name
FROM table_name a, (SELECT column_name FROM other_table WHERE condition) b;

或者使用 WITH 子句（公用表表達式，CTE）在 SQL 標準中：

WITH DerivedTable AS (SELECT column_name FROM other_table WHERE condition
)
SELECT a.column_name, b.column_name
FROM table_name a, DerivedTable b;

這個子查詢創建了一個派生表，該表可以在外部查詢中像普通表一樣使用。

4. 在 JOIN 子句中使用子查詢

SELECT a.column_name, b.column_name
FROM table_name a
JOIN (SELECT column_name FROM other_table WHERE condition) b ON a.common_field = b.common_field;

這個子查詢在 JOIN 操作中使用，將 other_table 表中符合特定條件的行與 table_name 表進行連接。

注意

• 子查詢必須用括號括起來。
• 子查詢可以是任何有效的 SELECT 語句，包括使用聚合函數、GROUP BY 子句或 HAVING 子句。
• 子查詢的結果集必須與外部查詢的相應部分兼容（例如，外部查詢的 WHERE 子句中使用子查詢時，子查詢的結果集應該是一個單一的列）。

分類

1.標量子查詢

標量子查詢的常見用途包括：

1. 作為條件：在 WHERE 子句中使用標量子查詢來過濾結果。

   SELECT * FROM emp
   WHERE salary > (SELECT AVG(salary) FROM emp);
   

   在這個例子中，標量子查詢 (SELECT AVG(salary) FROM emp) 用于找出工資高于平均工資的員工。

2. 作為列值：在 SELECT 列表中使用標量子查詢來計算或獲取額外的列值。

   SELECT empno, name, (SELECT deptno FROM dept WHERE deptno = emp.deptno) AS deptno
   FROM emp;
   

   這里，標量子查詢 (SELECT deptno FROM dept WHERE deptno = emp.deptno) 用于獲取與員工部門表中相應部門編號匹配的部門編號。

3. 作為排序依據：在 ORDER BY 子句中使用標量子查詢來對結果進行排序。

   SELECT * FROM emp
   ORDER BY (SELECT COUNT(*) FROM orders WHERE orders.empno = emp.empno) DESC;
   

   這個例子中，標量子查詢 (SELECT COUNT(*) FROM orders WHERE orders.empno = emp.empno) 用于根據員工的訂單數量對員工進行排序。

2.列子查詢

列子查詢通常用于在 SELECT 語句中計算派生列（Derived Column）的值。例如，你可能想要在查詢結果中包含一個計算字段或從另一個表中檢索的值。

SELECT column1, (SELECT column2 FROM other_table WHERE condition) AS alias
FROM table1;

在這個例子中，子查詢 (SELECT column2 FROM other_table WHERE condition) 被用來為 table1 的每一行計算或檢索一個值，并將這個值作為 alias 列返回。
假設有兩個表：employees 和 departments。employees 表包含員工信息，而 departments 表包含部門信息。每個員工屬于一個部門，部門有一個 department_id。

employees 表結構：

• employee_id
• name
• department_id

departments 表結構：

• department_id
• department_name

要獲取所有員工及其部門名稱，可以使用列子查詢：

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

實現相同的結果，可以這樣做：

SELECT e.name, (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name
FROM employees e;

在這個例子中，子查詢 (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) 為 employees 表中的每個員工檢索相應的部門名稱。

注意事項

• 列子查詢必須返回單個值，或者與外部查詢的行數相匹配的值。
• 如果子查詢返回多行，那么外部查詢的每一行都會與子查詢返回的每一行進行組合，這可能導致結果集的行數急劇增加（笛卡爾積）。
• 在使用列子查詢時，確保子查詢的邏輯正確，以避免產生錯誤的結果或性能問題。

表子查詢

表子查詢的基本語法如下：

SELECT column_list
FROM (SELECT column_listFROM table_nameWHERE condition
) AS alias_name
WHERE another_condition;

• column_list 是你想選擇的列名列表。
• table_name 是原始表的名稱。
• condition 是用于篩選原始表中行的條件。
• alias_name 是子查詢結果集的別名，用于在外部查詢中引用。

要獲取所有員工及其部門名稱，可以使用表子查詢：

SELECT e.name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_nameFROM departments
) d ON e.department_id = d.department_id;

在這個例子中，子查詢 SELECT department_id, department_name FROM departments 作為一個臨時表，與 employees 表進行連接。

• 派生表：在 FROM 子句中定義的子查詢，可以像普通表一樣使用。

  SELECT a.*, b.*
  FROM table1 a
  JOIN (SELECT * FROM table2 WHERE condition
  ) b ON a.common_field = b.common_field;
  

第六節 事務

事務（Transaction）是數據庫管理系統中的一個核心概念，用于確保數據的完整性和一致性。在 SQL 中，事務是一組不可分割的操作序列，這些操作要么全部成功，要么全部不做，是一個原子操作單元。

事務的特性

事務具有 ACID 特性，即：

• 原子性（Atomicity）：事務中的所有操作要么全部完成，要么全部不做，不能只執行其中的一部分。
• 一致性（Consistency）：事務執行前后，數據庫保持一致性狀態。
• 隔離性（Isolation）：事務的執行不受其他并發事務的干擾，事務之間是相互隔離的。
• 持久性（Durability）：一旦事務提交，則其結果將永久保存在數據庫中，即使系統發生故障也不會丟失。

事務的操作

事務的操作通常包括：

• 開始事務：標記事務的開始。
• 提交事務：將事務中的所有修改永久保存到數據庫中。
• 回滾事務：撤銷事務中的所有修改，保持數據庫狀態不變。

SQL 中的事務語句

在 SQL 中，可以使用以下語句來控制事務：

• BEGIN TRANSACTION 或 START TRANSACTION：開始一個新的事務。
• COMMIT：提交當前事務，將事務中的所有修改永久保存到數據庫。
• ROLLBACK：回滾當前事務，撤銷事務中的所有修改。
• SAVEPOINT：創建事務的保存點，可以在回滾時指定回滾到某個保存點。

事務的使用示例

-- 開始事務
START TRANSACTION;-- 執行一些操作
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;-- 提交事務，使操作永久生效
COMMIT;-- 如果需要回滾
ROLLBACK;

• 性能：雖然事務確保了數據的安全性，但過多或過長的事務可能會影響數據庫性能。
• 鎖定：事務可能會導致數據庫鎖定，影響其他用戶的訪問。
• 錯誤處理：在事務中的錯誤需要被適當處理，以避免事務失敗。
• 自動提交：許多數據庫系統默認在每個單獨的 SQL 語句后自動提交事務，可以通過設置數據庫的自動提交行為來改變這一行為。
• 并發事務問題通常出現在多用戶同時對數據庫進行讀寫操作時，可能會導致數據的不一致性。這些問題包括：

1. 臟讀（Dirty Read）：一個事務讀取了另一個事務未提交的修改數據。
2. 不可重復讀（Non-repeatable Read）：在同一事務中，多次讀取同一數據集合時，由于其他事務的修改，導致讀取結果不一致。
3. 幻讀（Phantom Read）：在同一事務中，多次查詢時，結果集的行數不一致，看起來像是出現了“幻影”的行。這通常是由其他事務插入或刪除了符合查詢條件的行導致的。

事務隔離級別是解決這些問題的關鍵機制，它定義了事務間的隔離程度。SQL標準定義了四個隔離級別：

1. 讀未提交（Read Uncommitted）：最低隔離級別，允許臟讀，因為事務可以讀取未提交事務修改的數據。
2. 讀已提交（Read Committed）：確保事務只能讀取已提交的數據，避免了臟讀，但可能會出現不可重復讀和幻讀。
3. 可重復讀（Repeatable Read）：保證在同一事務中多次讀取同一數據集合時，結果是一致的，避免了不可重復讀，但幻讀仍有可能發生。
4. 串行化（Serializable）：最高隔離級別，事務完全隔離，像事務是串行執行的一樣，避免了臟讀、不可重復讀和幻讀，但可能會犧牲一些并發性能。