一、SQL基本數據類型
SQL 數據類型速查表
類別 | 數據類型 | 說明 | 范圍/示例 | 適用場景 |
---|---|---|---|---|
整數類型 | INT / INTEGER | 標準整數類型 | -231 到 231-1 (-2,147,483,648 到 2,147,483,647) | ID、年齡、數量等 |
SMALLINT | 小范圍整數 | -32,768 到 32,767 | 小范圍數值 | |
BIGINT | 大范圍整數 | -2?3 到 2?3-1 | 大額訂單號、時間戳 | |
TINYINT | 極小整數 (MySQL) | 0 到 255 或 -128 到 127 | 狀態碼、布爾值模擬 | |
小數類型 | DECIMAL(p,s) | 精確小數 p=總位數, s=小數位 | DECIMAL(10,2) → 12345678.99 | 金融金額、精確計算 |
FLOAT | 單精度浮點數 | ≈6-7位精度 | 科學計算 | |
DOUBLE | 雙精度浮點數 | ≈15位精度 | 高精度測量 | |
字符串類型 | CHAR(n) | 定長字符串 空格填充 | CHAR(10) ‘ABC’ → 'ABC ’ | 固定長度代碼(如國家代碼) |
VARCHAR(n) | 變長字符串 | VARCHAR(255) | 姓名、地址等變長文本 | |
TEXT | 大文本數據 | 最多 65,535 字節 (MySQL) | 文章內容、描述 | |
日期時間 | DATE | 日期 | ‘2023-08-15’ | 出生日期、事件日期 |
TIME | 時間 | ‘14:30:00’ | 會議時間 | |
DATETIME | 日期+時間 | ‘2023-08-15 14:30:00’ | 訂單時間、日志時間戳 | |
TIMESTAMP | 自動記錄的時間戳 | 自動記錄修改時間 | 創建時間/修改時間 | |
二進制類型 | BLOB | 二進制大對象 | 最大 65,535 字節 | 圖片、PDF等文件存儲 |
BINARY(n) | 定長二進制數據 | BINARY(16) | 加密數據、哈希值 | |
布爾類型 | BOOLEAN | 邏輯值 | TRUE/FALSE | 開關狀態、是否標記 |
特殊類型 | ENUM('val1','val2') | 枚舉值 | ENUM(‘Red’,‘Green’,‘Blue’) | 狀態選項、固定分類 |
JSON | JSON格式數據 (現代數據庫) | {“name”: “John”, “age”: 30} | 靈活數據結構 | |
UUID | 全局唯一標識符 (PostgreSQL等) | ‘a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11’ | 分布式ID生成 |
二、SQL關鍵字解釋
SQL查詢知識點
基本查詢結構
SELECT [DISTINCT] 列1, 列2, 聚合函數(列3) -- 選擇最終顯示的列
FROM 表1 -- 初始數據源
[JOIN 表2 ON 連接條件] -- 表連接
WHERE 行級過濾條件 -- 行過濾
GROUP BY 分組列 -- 數據分組
HAVING 組級過濾條件 -- 組過濾
ORDER BY 排序列 [ASC|DESC] -- 結果排序
LIMIT 結果數量; -- 行數限制
關鍵字詳解(含執行變化案例)
1. SELECT
-
作用:指定最終結果集中顯示的列
-
執行變化:從中間結果集中抽取指定列
-
示例:
/* 原始products表(執行前): | id | product_name | price | |----|--------------|-------| | 1 | Laptop | 1200 | | 2 | Phone | 800 | | 3 | Tablet | 600 | */ SELECT product_name, price * 0.9 AS sale_price FROM products;/* 執行后結果集: | product_name | sale_price | |--------------|------------| | Laptop | 1080 | | Phone | 720 | | Tablet | 540 | */
2. FROM
- 作用:指定查詢的主數據源
- 執行變化:加載初始數據集
- *示例:
FROM employees -- 加載員工表全部數據
3. JOIN(表連接)
- 作用:組合多個表的數據
- 執行變化:擴展列維度
INNER JOIN 示例:
/* employees表(左表):
| id | name | dept |
|----|-------|--------|
| 1 | Alice | Sales |
| 2 | Bob | IT |orders表(右表):
| order_id | emp_id | amount |
|----------|--------|--------|
| 101 | 1 | 200 |
| 102 | 1 | 300 | */SELECT e.name, o.amount
FROM employees e
INNER JOIN orders o ON e.id = o.emp_id;/* 執行后結果集:
| name | amount |
|-------|--------|
| Alice | 200 |
| Alice | 300 | */
4. WHERE
-
作用:行級數據過濾
-
執行變化:減少行數
-
示例:
/* 原始orders表: | id | amount | status | |----|--------|----------| | 1 | 100 | shipped | | 2 | 200 | pending | | 3 | 150 | shipped | */ SELECT id, amount FROM orders WHERE status = 'shipped';/* 執行后結果集: | id | amount | |----|--------| | 1 | 100 | | 3 | 150 | */
5. GROUP BY
- 作用:數據分組聚合
- 執行變化:行數減少,出現聚合值
-
執行變化:行數減少,出現聚合值
-
聚合值
示例:
/* 原始employees表:
| id | name | dept | salary |
|----|--------|--------|--------|
| 1 | Alice | Sales | 5000 |
| 2 | Bob | Sales | 6000 |
| 3 | Charlie| IT | 7000 | */SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept;/* 執行后結果集:
| dept | avg_salary |
|-------|------------|
| Sales | 5500 |
| IT | 7000 | */
6. HAVING
- 作用:分組后結果過濾
- 執行變化:減少分組數量
- 與WHERE對比:
特性 | WHERE | HAVING |
---|---|---|
執行時機 | 分組前 | 分組后 |
操作對象 | 原始行 | 分組結果 |
聚合函數 | 不可用 | 可用 |
示例:
/* 續上GROUP BY結果:
| dept | avg_salary |
|-------|------------|
| Sales | 5500 |
| IT | 7000 | */SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 6000;/* 執行后結果集:
| dept | avg_salary |
|------|------------|
| IT | 7000 | */
7. ORDER BY
-
作用:結果排序
-
執行變化:行順序改變,內容不變
-
排序方式:
ASC
升序(默認)DESC
降序
-
示例:
/* 原始結果: | product | price | |---------|-------| | Laptop | 1200 | | Phone | 800 | | Tablet | 600 | */ SELECT product, price FROM products ORDER BY price DESC;/* 執行后結果集: | product | price | |---------|-------| | Laptop | 1200 | | Phone | 800 | | Tablet | 600 | */
8. LIMIT
-
作用:限制返回行數
-
執行變化:截斷結果集
-
示例:
/* 假設排序后結果(10條記錄) */ SELECT product, price FROM products ORDER BY price DESC LIMIT 5; /* 執行后結果集:只保留前5條記錄 */
9. OFFSET
OFFSET
通常與 LIMIT
配合使用,用于實現分頁查詢或跳過指定行數
-
跳過結果集的前 N 行
-
典型應用場景:分頁查詢(如第2頁、第3頁數據)
-
語法結構:
LIMIT 返回行數 OFFSET 跳過行數; -- 或等效寫法 -- LIMIT 跳過行數, 返回行數; # MySQL專用語法
🧩 執行機制圖解
📊 案例演示(分步說明)
初始數據表 products
id | product_name | price |
---|---|---|
1 | Laptop | 1200 |
2 | Phone | 800 |
3 | Tablet | 600 |
4 | Monitor | 300 |
5 | Keyboard | 50 |
6 | Mouse | 30 |
7 | Headphones | 150 |
查詢1:基礎分頁(每頁3條)
/* 獲取第1頁數據 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 0; -- 從第0行開始取3條/* 結果集:
| id | product_name |
|----|--------------|
| 1 | Laptop |
| 2 | Phone |
| 3 | Tablet | */
查詢2:跳轉到第2頁
/* 獲取第2頁數據 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3; -- 跳過前3條,取接下來3條/* 結果集:
| id | product_name |
|----|--------------|
| 4 | Monitor |
| 7 | Headphones |
| 5 | Keyboard | */
查詢3:等效寫法(MySQL)
/* 獲取第3頁數據 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 6, 3; -- 等效于 OFFSET 6 LIMIT 3/* 結果集:
| id | product_name |
|----|--------------|
| 6 | Mouse | # 只剩1條數據 */
?? 重要注意事項
- 執行順序:
OFFSET
在ORDER BY
之后、LIMIT
之前執行
10.字符串操作與通配符
通配符 | 功能 | 示例 | 匹配示例 |
---|---|---|---|
% | 匹配任意長度字符 | 'John%' | John, Johnson |
_ | 匹配單個字符 | '_ean' | Dean, Jean |
[ ] | 匹配指定字符集 | '[a]%' (SQL Server) | apple, elephant |
[^] | 排除指定字符集 | '[^0-9]%' | Apple, #123 |
🧰 核心字符串函數
1. 基礎操作函數
函數 | 功能 | 示例 | 結果 |
---|---|---|---|
CONCAT(s1, s2) | 字符串拼接 | CONCAT('Hello', ' ', 'World') | Hello World |
LENGTH(s) | 字符串長度 | LENGTH('SQL') | 3 |
UPPER(s) | 轉為大寫 | UPPER('hello') | HELLO |
LOWER(s) | 轉為小寫 | LOWER('SQL') | sql |
TRIM(s) | 去除兩端空格 | TRIM(' text ') | text |
2. 子字符串操作
函數 | 功能 | 示例 | 結果 |
---|---|---|---|
SUBSTRING(s, start, len) | 提取子字符串 | SUBSTRING('Database', 2, 4) | atab |
LEFT(s, n) | 提取左側n個字符 | LEFT('2023-06-15', 4) | 2023 |
RIGHT(s, n) | 提取右側n個字符 | RIGHT('user@email.com', 3) | com |
REPLACE(s, old, new) | 替換字符串 | REPLACE('I like SQL', 'like', 'love') | I love SQL |
REVERSE(s) | 反轉字符串 | REVERSE('ABCD') | DCBA |
3. 高級搜索函數
函數 | 功能 | 示例 |
---|---|---|
POSITION(sub IN s) | 返回子串位置 | POSITION('@' IN 'user@domain.com') → 5 |
CHAR_LENGTH(s) | 字符數(支持多字節) | CHAR_LENGTH('中文') → 2 |
INSTR(s, sub) | 查找子串位置 | INSTR('SQL Tutorial', 'Tut') → 5 |
?? 實戰綜合應用
場景:郵箱格式標準化
UPDATE users
SET email = LOWER(CONCAT(SUBSTRING(email, 1, POSITION('@' IN email) - 1), -- 用戶名部分'@company.com' -- 統一域名)
)
WHERE email NOT LIKE '%@company.com'; -- 篩選需要修改的記錄
場景:產品編號驗證
/* 驗證格式:AA-000 */
SELECT product_code
FROM products
WHERE product_code LIKE '__-___' -- 長度驗證AND SUBSTRING(product_code, 1, 2) REGEXP '^[A-Z]{2}$' -- 前兩位大寫字母AND SUBSTRING(product_code, 4, 3) REGEXP '^[0-9]{3}$'; -- 后三位數字
場景:動態搜索
/* 根據輸入關鍵詞靈活搜索 */
SET @keyword = 'pro yoga';SELECT * FROM products
WHERE product_name LIKE CONCAT('%', REPLACE(@keyword, ' ', '%'), '%')OR category LIKE CONCAT('%', @keyword, '%');
完整案例:銷售分析查詢
業務場景:分析2023年各部門銷售業績
初始數據
employees表:
id | name | dept |
---|---|---|
1 | Alice | Sales |
2 | Bob | Sales |
3 | Charlie | IT |
orders表:
order_id | emp_id | amount | order_date |
---|---|---|---|
101 | 1 | 200 | 2023-02-01 |
102 | 1 | 300 | 2023-03-15 |
103 | 2 | 150 | 2023-01-10 |
104 | 3 | 400 | 2023-04-20 |
105 | 1 | 500 | 2022-12-31 |
分步執行過程
SELECT e.dept,SUM(o.amount) AS total_sales,COUNT(*) AS order_count
FROM employees e
INNER JOIN orders o ON e.id = o.emp_id
WHERE o.order_date >= '2023-01-01'
GROUP BY e.dept
HAVING SUM(o.amount) > 0
ORDER BY total_sales DESC;
分步結果變化:
-
FROM + JOIN(初始連接):
| e.id | e.name | dept | o.order_id | amount | order_date | |------|--------|-------|------------|--------|-------------| | 1 | Alice | Sales | 101 | 200 | 2023-02-01 | | 1 | Alice | Sales | 102 | 300 | 2023-03-15 | | 1 | Alice | Sales | 105 | 500 | 2022-12-31 | | 2 | Bob | Sales | 103 | 150 | 2023-01-10 | | 3 | Charlie| IT | 104 | 400 | 2023-04-20 |
-
WHERE(日期過濾):
| e.id | name | dept | order_id | amount | order_date | |------|--------|-------|----------|--------|-------------| | 1 | Alice | Sales | 101 | 200 | 2023-02-01 | | 1 | Alice | Sales | 102 | 300 | 2023-03-15 | | 2 | Bob | Sales | 103 | 150 | 2023-01-10 | | 3 | Charlie| IT | 104 | 400 | 2023-04-20 |
-
GROUP BY(按部門分組):
Sales組:Alice(200+300) + Bob(150) = 650 IT組:Charlie(400) = 400
-
HAVING(組過濾):
兩個組都滿足SUM(amount)>0
-
SELECT(顯示結果):
| dept | total_sales | order_count | |-------|-------------|-------------| | Sales | 650 | 3 | | IT | 400 | 1 |
-
ORDER BY(最終排序):
| dept | total_sales | order_count | |-------|-------------|-------------| | Sales | 650 | 3 | -- 銷售額最高 | IT | 400 | 1 |
SQL更新
1. 插入數據(INSERT)
關鍵字解釋
INSERT INTO
:指定要插入數據的表名。VALUES
:定義插入的具體值。SET
(可選):在部分數據庫(如MySQL)中可用,用于指定列名和值的對應關系。
語法結構
-- 插入單條數據
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);-- 插入多條數據
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...),(值3, 值4, ...);
示例
假設有一個 students
表:
id | name | age |
---|---|---|
1 | Alice | 20 |
插入新數據:
INSERT INTO students (name, age) VALUES ('Bob', 22);
2. 修改數據(UPDATE)
關鍵字解釋
UPDATE
:指定要修改數據的表名。SET
:定義要修改的列名及其新值。WHERE
(可選):限定修改的行范圍(必須謹慎使用)。
語法結構
UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2
WHERE 條件;
示例
修改 students
表中 id=1
的學生的年齡:
UPDATE students
SET age = 21
WHERE id = 1;
3. 刪除數據(DELETE)
關鍵字解釋
DELETE FROM
:指定要刪除數據的表名。WHERE
(可選):限定刪除的行范圍(必須謹慎使用)。
語法結構
DELETE FROM 表名
WHERE 條件;
示例
刪除 students
表中 id=2
的學生:
DELETE FROM students
WHERE id = 2;
4. 綜合示例
場景
修改訂單表中某個用戶的訂單狀態,并刪除過期訂單,最后插入新訂單。
SQL 操作
-- 修改訂單狀態
UPDATE orders
SET status = '已完成'
WHERE user_id = 1001 AND status = '處理中';-- 刪除過期訂單
DELETE FROM orders
WHERE order_date < '2023-01-01';-- 插入新訂單
INSERT INTO orders (user_id, product, status)
VALUES (1002, 'Laptop', '已下單');
三、創建,修改,刪除表
🏗? 一、創建表 (CREATE TABLE)
📌 核心語法
CREATE TABLE [IF NOT EXISTS] 表名 (列名1 數據類型 [約束],列名2 數據類型 [約束],...[表級約束]
);
🛡? 常用約束
約束類型 | 關鍵字 | 核心作用 | 示例 | 可視化說明 |
---|---|---|---|---|
主鍵約束 | PRIMARY KEY | 唯一標識每行記錄,禁止重復和 NULL | id INT PRIMARY KEY | 🔑 唯一標識符 |
外鍵約束 | FOREIGN KEY | 強制表間引用完整性 | user_id INT REFERENCES users(id) ON DELETE CASCADE | ?? 表間連接關系 |
唯一約束 | UNIQUE | 確保列值唯一(允許多個 NULL) | email VARCHAR(255) UNIQUE | ? 禁止重復值 |
非空約束 | NOT NULL | 禁止 NULL 值 | name VARCHAR(50) NOT NULL | 🚫 強制必填項 |
檢查約束 | CHECK | 強制自定義條件 | age INT CHECK (age >= 18) | ? 數據驗證規則 |
默認值約束 | DEFAULT | 未指定值時自動填充默認值 | created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ?? 自動填充值 |
自動增長約束 | AUTO_INCREMENT | 自動生成連續唯一值(MySQL) | id INT AUTO_INCREMENT PRIMARY KEY | 🔢 自增序列 |
枚舉約束 | ENUM | 限制列值為預定義選項 | status ENUM('active','inactive') | 📋 固定選項列表 |
1. 主鍵約束 (PRIMARY KEY)
作用:唯一標識表的每行記錄
特性:
- 值必須唯一且非 NULL
- 每表只能有一個主鍵(可多列組合)
案例:
CREATE TABLE students (student_id INT PRIMARY KEY, -- 單列主鍵name VARCHAR(50) NOT NULL
);-- 多列組合主鍵
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id) -- 復合主鍵
);
2. 外鍵約束 (FOREIGN KEY)
作用:維護表間數據一致性
關鍵參數:
ON DELETE CASCADE
:主表刪除時同步刪除關聯記錄ON UPDATE SET NULL
:主表更新時關聯字段置空
案例:
CREATE TABLE departments (dept_id INT PRIMARY KEY,dept_name VARCHAR(50)
);CREATE TABLE employees (emp_id INT PRIMARY KEY,name VARCHAR(50),dept_id INT,FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL -- 部門刪除時員工部門置空
);
3. 唯一約束 (UNIQUE)
作用:保證列值唯一性
特性:
- 允許 NULL 值(多個 NULL 視為不同值)
- 每表可創建多個唯一約束
案例:
CREATE TABLE users (user_id INT PRIMARY KEY,username VARCHAR(30) UNIQUE, -- 用戶名唯一email VARCHAR(255) UNIQUE, -- 郵箱唯一phone VARCHAR(20) UNIQUE -- 手機號唯一
);
4. 檢查約束 (CHECK)
作用:強制數據滿足業務規則
支持條件:比較運算、邏輯運算、函數調用
案例:
CREATE TABLE products (product_id INT PRIMARY KEY,name VARCHAR(100),price DECIMAL(10,2) CHECK (price > 0), -- 價格必須>0discount DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100), -- 折扣0-100%release_date DATE CHECK (release_date > '2020-01-01') -- 2020年后發布
);
5. 默認值約束 (DEFAULT)
作用:自動填充缺省值
常用默認值:
CURRENT_TIMESTAMP
:當前時間戳0
/''
:數值/字符串默認值TRUE
/FALSE
:布爾默認值
案例:
CREATE TABLE orders (order_id INT PRIMARY KEY,order_date DATE DEFAULT CURRENT_DATE, -- 默認當天日期status VARCHAR(20) DEFAULT 'pending', -- 默認狀態total DECIMAL(10,2) DEFAULT 0.00 -- 默認總價
);
?? 約束管理操作
添加約束(建表后)
-- 添加主鍵
ALTER TABLE students ADD PRIMARY KEY (student_id);-- 添加外鍵
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);-- 添加檢查約束
ALTER TABLE products
ADD CHECK (price > 0);
刪除約束
-- 刪除主鍵
ALTER TABLE students DROP PRIMARY KEY;-- 刪除外鍵
ALTER TABLE employees DROP FOREIGN KEY fk_dept;-- 刪除唯一約束
ALTER TABLE users DROP INDEX email;
🧩 案例1:創建用戶表
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(30) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL,password CHAR(60) NOT NULL, -- 存儲加密密碼created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,is_active BOOLEAN DEFAULT TRUE
);
🧩 案例2:創建訂單表(含外鍵)
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,amount DECIMAL(10,2) CHECK (amount > 0),order_date DATE DEFAULT (CURRENT_DATE),status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
🔧 二、修改表 (ALTER TABLE)
📌 核心操作類型
操作 | 語法示例 |
---|---|
添加列 | ALTER TABLE 表名 ADD COLUMN 列名 數據類型 |
刪除列 | ALTER TABLE 表名 DROP COLUMN 列名 |
修改列類型 | ALTER TABLE 表名 ALTER COLUMN 列名 新類型 |
重命名列 | ALTER TABLE 表名 RENAME COLUMN 舊名 TO 新名 |
添加約束 | ALTER TABLE 表名 ADD CONSTRAINT 約束內容 |
刪除約束 | ALTER TABLE 表名 DROP CONSTRAINT 約束名 |
🧩 案例1:添加新列
/* 在users表中添加手機號字段 */
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';/* 可視化變化 */
🧩 案例2:修改列屬性
/* 擴展email字段長度并添加唯一約束 */
ALTER TABLE users
MODIFY COLUMN email VARCHAR(150) NOT NULL;ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);
🧩 案例3:刪除列
/* 刪除已棄用的is_active列 */
ALTER TABLE users
DROP COLUMN is_active;
🧩 案例4:重命名表
/* 將orders表重命名為purchases */
ALTER TABLE orders RENAME TO purchases;
🗑? 三、刪除表 (DROP TABLE)
📌 核心語法
DROP TABLE [IF EXISTS] 表名 [CASCADE];
?? 注意事項
- 不可逆操作:表結構和數據永久刪除
- 依賴處理:
CASCADE
:級聯刪除依賴對象(視圖、外鍵等)RESTRICT
:默認行為,存在依賴時拒絕刪除
- 權限要求:需要DROP權限
🧩 案例1:基本刪除
/* 刪除臨時表 */
DROP TABLE IF EXISTS temp_logs;
🧩 案例2:級聯刪除
/* 刪除用戶表及關聯訂單 */
DROP TABLE users CASCADE;/* 可視化效果 */
💡 最佳實踐建議
- 創建表時:
-
優先使用
NOT NULL
約束 -
為關聯字段添加外鍵約束
-
使用
AUTO_INCREMENT
/SERIAL
作為代理主鍵
-
修改表時:
-
生產環境使用
ALTER TABLE ... ADD COLUMN
而非直接修改列類型 -
大表修改在低峰期進行
/* 安全添加列示例 */ ALTER TABLE large_table ADD COLUMN new_column INT DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE; -- MySQL優化參數
-
-
刪除表時:
-
始終使用
IF EXISTS
防止錯誤 -
正式環境先重命名再刪除(安全回收期)
/* 安全刪除流程 */ ALTER TABLE obsolete_table RENAME TO obsolete_table_202308; DROP TABLE obsolete_table_202308; -- 30天后執行
-
重要表刪除前執行數據備份
CREATE TABLE users_backup AS SELECT * FROM users; -- 全量備份
四、索引:加速查詢的核心技術
🚀 索引核心概念
索引本質:數據庫中的高效查找數據結構(類似書籍目錄)
核心價值:
- 提升查詢速度 10~1000 倍
- 保證數據唯一性(唯一索引)
- 加速表連接(JOIN 操作)
索引工作原理
訂單表 orders
初始結構
order_id | user_id | amount | order_date |
---|---|---|---|
1001 | 101 | 200 | 2023-08-01 |
1002 | 102 | 150 | 2023-08-02 |
1003 | 101 | 300 | 2023-08-03 |
1004 | 103 | 400 | 2023-08-04 |
🔍 無索引查詢(全表掃描)
SELECT * FROM orders WHERE user_id = 101;
執行過程:
- 逐行掃描所有記錄(4行)
- 對每行檢查
user_id=101
- 返回匹配行(1001和1003)
掃描行數:4行
🚀 創建索引后查詢
CREATE INDEX idx_user ON orders(user_id); -- 創建B樹索引
SELECT * FROM orders WHERE user_id = 101;
索引結構(B樹簡化版)
執行過程:
- 從根節點開始查找
user_id=101
- 定位到分支節點 “100-150”
- 找到葉子節點 “user_id=101”
- 通過指針直接訪問訂單1001和1003
掃描行數:2行(僅目標數據)
?? 索引效果對比
指標 | 無索引 | 有索引 |
---|---|---|
掃描行數 | 4行 | 2行 |
磁盤I/O | 4次 | 2次 |
查詢時間 | 15ms | 3ms |
新增訂單代價 | 直接寫入 | 更新索引+寫入 |
🔥 組合索引深度案例
創建組合索引:
CREATE INDEX idx_user_date ON orders(user_id, order_date);
執行范圍查詢:
SELECT * FROM orders
WHERE user_id = 101 AND order_date BETWEEN '2023-08-01' AND '2023-08-10';
組合索引結構
查詢過程:
- 在索引中定位
user_id=101
- 在該分區內按日期范圍查找
- 直接獲取訂單1001和1003
優勢:
- 避免全表掃描
- 避免額外排序(索引已預排序)
📊 真實性能測試數據
訂單表(100萬記錄)查詢對比:
查詢類型 | 無索引時間 | 索引時間 | 提升倍數 |
---|---|---|---|
等值查詢(user_id) | 1200ms | 25ms | 48x |
范圍查詢(date) | 980ms | 40ms | 24x |
排序(order_id) | 850ms | 30ms | 28x |
📌 索引類型速查表
索引類型 | 關鍵字 | 適用場景 | 可視化結構 |
---|---|---|---|
B樹索引 | INDEX | 等值查詢、范圍查詢(默認索引) | 平衡多叉樹 🌳 |
唯一索引 | UNIQUE INDEX | 主鍵/唯一約束字段 | 帶唯一標識的葉子節點 🔑 |
全文索引 | FULLTEXT INDEX | 文本內容搜索(文章、描述) | 倒排索引 📝 |
哈希索引 | HASH INDEX | 內存表精確匹配(不支持范圍查詢) | 鍵值對存儲 ? |
組合索引 | INDEX(col1,col2) | 多列條件查詢 | 多級樹結構 🧩 |
空間索引 | SPATIAL INDEX | 地理坐標數據(MySQL) | R樹結構 🗺? |
?? 索引操作語法
1. 創建索引
-- 基本單列索引
CREATE INDEX idx_name ON table_name (column_name);-- 唯一索引
CREATE UNIQUE INDEX uidx_email ON users (email);-- 組合索引(多列)
CREATE INDEX idx_name_dept ON employees (last_name, department);-- 全文索引(MySQL)
CREATE FULLTEXT INDEX idx_content ON articles (content);
2. 刪除索引
DROP INDEX idx_name ON table_name;
3. 查看索引
SHOW INDEX FROM table_name;
🔍 索引適用場景
推薦創建索引的列:
-
WHERE 子句頻繁使用的列
SELECT * FROM orders WHERE user_id = 100; -- 需為user_id建索引
-
JOIN 連接字段
SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- user_id和id需索引
-
排序字段(ORDER BY)
SELECT * FROM products ORDER BY price DESC; -- price建索引加速排序
-
分組字段(GROUP BY)
SELECT department, COUNT(*) FROM employees GROUP BY department; -- department建索引
?? 索引優缺點分析
? 優點:
- 極大加速 SELECT 查詢
- 加速表連接(JOIN)
- 保證數據唯一性(唯一索引)
- 優化排序和分組操作
? 缺點:
- 降低寫操作速度(INSERT/UPDATE/DELETE)
- 占用額外存儲空間
- 維護成本高(需定期優化)
五、視圖
1. 視圖是什么?
- 本質: 一個虛擬表。
- 構成: 基于一個或多個基礎表(或其他視圖)的SELECT查詢的結果集。
- 存儲: 不存儲實際數據,只存儲定義它的 SQL 查詢語句。
- 行為: 當查詢視圖時,數據庫引擎會動態執行其定義的 SELECT 語句,返回當前基礎表中的最新數據。
- 類比: 像一個保存好的查詢,或者一個窗口,透過它看到的是基礎表中數據的特定組合、過濾或聚合。
2. 為什么使用視圖?
- 簡化復雜查詢: 將復雜的 JOIN、子查詢、過濾封裝在視圖中,用戶只需查詢簡單的視圖。
- 增強數據安全性:
- 隱藏基礎表的敏感列(如薪資、密碼)。
- 通過視圖僅暴露用戶有權訪問的行和列(結合權限管理)。
- 提供邏輯數據獨立性:
- 應用程序可以基于視圖編寫,即使基礎表結構發生變化(如列名、拆分表),只需修改視圖定義,應用程序代碼可能無需改動。
- 定制化數據展現: 為不同用戶或部門提供不同的數據視角(聚合數據、特定范圍數據)。
- 簡化權限管理: 對視圖授權比直接對多個基礎表授權更方便。
3. 創建視圖 (CREATE VIEW)
CREATE VIEW view_name [(column_alias1, column_alias2, ...)]
AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[JOIN ...];
view_name
: 視圖的名稱。(column_alias1, ...)
: 可選。為視圖的列指定別名。如果省略,列名繼承 SELECT 語句中的列名或別名。AS
: 關鍵字。SELECT ...
: 定義視圖內容的查詢語句。這是視圖的核心。
示例:
CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CompanyName, ContactName, Phone
FROM Customers
WHERE Active = 1;
4. 查詢視圖 (SELECT)
SELECT * FROM ActiveCustomers; -- 查詢整個視圖
SELECT ContactName, Phone FROM ActiveCustomers WHERE CompanyName LIKE 'A%'; -- 像查詢普通表一樣使用視圖
5. 視圖的核心特點(重點理解)
- 虛擬性: 視圖不存儲數據本身,只存儲查詢定義。查詢視圖時實時計算。
- 動態性: 視圖數據隨基礎表數據實時變化。
- 派生性: 視圖數據完全來源于其定義所基于的基礎表。
- 可更新性 (有時): 并非所有視圖都可更新。能否更新取決于視圖的定義:
- 通常,基于單個表、不含 DISTINCT、GROUP BY、HAVING、聚合函數,并且包含基礎表所有非空且無默認值的列的視圖較容易更新。
- 涉及多個表的 JOIN 視圖通常不可直接更新(具體規則因數據庫系統而異,如 SQL Server 有限支持, Oracle 有 INSTEAD OF 觸發器)。
- 更新視圖 (INSERT/UPDATE/DELETE) 最終會作用到基礎表上。
圖表 1:視圖與基礎表關系
+----------------+ +----------------+ +----------------+
| Table A | | Table B | | Table C |
| (Physical) | | (Physical) | | (Physical) |
| - Col1 (PK) | | - ColX (PK) | | - ColY (PK) |
| - Col2 | | - ColY (FK) | | - ColZ |
| - Col3 | | - ColW | +----------------+
+----------------+ +----------------+^ ^| || JOIN/Filter/Projection ||-------------------------+|v+----------------+| MyView || (Virtual) || - Col1 || - Col2_Alias | <-- (Alias from TableA.Col2)| - ColZ | <-- (From TableC)| - CalcColumn | <-- (e.g., TableA.Col3 * 10)+----------------+^||+----------------+| Application || or User | --> SELECT * FROM MyView WHERE ...+----------------+
- 實線方框: 物理存儲的基礎表。
- 虛線方框: 虛擬的視圖。
- 箭頭: 視圖的數據來源于執行其定義的 SELECT 語句(包含 JOIN、過濾、計算等操作)作用于基礎表。
- 應用/用戶: 像查詢普通表一樣查詢視圖。
6. 修改視圖 (ALTER VIEW / CREATE OR REPLACE VIEW)
-
ALTER VIEW (部分數據庫如 SQL Server):
ALTER VIEW view_name [(column_alias1, ...)] AS new_select_statement;
-
CREATE OR REPLACE VIEW (更通用,如 MySQL, PostgreSQL, Oracle):
CREATE OR REPLACE VIEW view_name [(column_alias1, ...)] AS new_select_statement;
7. 刪除視圖 (DROP VIEW)
DROP VIEW [IF EXISTS] view_name;
8. 視圖的優缺點總結
- 優點: 簡化、安全、邏輯獨立、定制化。
- 缺點:
- 性能開銷: 復雜視圖查詢時,數據庫仍需執行底層復雜查詢,可能不如直接優化基礎表查詢快(視圖不是性能優化銀彈)。
- 更新限制: 并非所有視圖都可直接更新,規則有時復雜。
- 依賴管理: 視圖依賴于基礎表。如果基礎表被刪除或結構更改(如刪除視圖中引用的列),視圖會失效。
- 嵌套復雜性: 過度使用嵌套視圖(視圖基于視圖)會降低可讀性和維護性,并可能放大性能問題。
圖表 2:視圖的權限控制(概念圖)
+----------------+ +----------------+ +----------------+
| User/App | | View | | Base Tables |
| (Limited Perm) |---->| (Permission: |---->| (Full Schema) |
| | | SELECT only | | |
+----------------+ | on specific cols| +----------------+| & rows) |+----------------+
- 用戶/應用只有對視圖的 SELECT 權限。
- 視圖定義了哪些列(如隱藏了 Salary 列)和哪些行(如只顯示 Active=1)對用戶可見。
- 用戶通過視圖間接訪問基礎表,但只能看到視圖定義允許的部分。
六、SQL 訪問控制
SQL 訪問控制
SQL 訪問控制是數據庫安全的重要組成部分,主要用于管理用戶對數據庫對象的訪問權限。
一、用戶管理
作用:創建、修改和刪除數據庫用戶
關鍵字:CREATE USER、ALTER USER、DROP USER、SHOW CREATE USER
1. 用戶管理語法
sql
-- 創建用戶
CREATE USER 'username'@'host' IDENTIFIED BY 'password';-- 修改用戶密碼
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';-- 重命名用戶
RENAME USER 'old_username'@'host' TO 'new_username'@'host';-- 刪除用戶
DROP USER 'username'@'host';-- 查看用戶創建語句
SHOW CREATE USER 'username'@'host';
2. 示例
sql
-- 創建用戶testuser,只能從本地訪問
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123';-- 修改用戶密碼
ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newtest123';-- 重命名用戶
RENAME USER 'testuser'@'localhost' TO 'newuser'@'localhost';-- 刪除用戶
DROP USER 'newuser'@'localhost';
二、權限管理
作用:授予、撤銷用戶對數據庫對象的訪問權限
關鍵字:GRANT、REVOKE、SHOW GRANTS
1. 常用權限類型
權限類型 | 作用范圍 |
---|---|
SELECT | 查詢數據 |
INSERT | 插入數據 |
UPDATE | 修改數據 |
DELETE | 刪除數據 |
CREATE | 創建表、視圖等對象 |
DROP | 刪除表、視圖等對象 |
ALTER | 修改表結構 |
INDEX | 創建和刪除索引 |
ALL PRIVILEGES | 所有權限 |
GRANT OPTION | 授予他人權限的權限 |
2. 權限管理語法
sql
-- 授予權限
GRANT privilege_type ON database.table TO 'username'@'host';-- 授予權限并允許傳播
GRANT privilege_type ON database.table TO 'username'@'host' WITH GRANT OPTION;-- 撤銷權限
REVOKE privilege_type ON database.table FROM 'username'@'host';-- 撤銷所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';-- 查看用戶權限
SHOW GRANTS FOR 'username'@'host';-- 查看當前用戶權限
SHOW GRANTS;
3. 示例
-- 授予用戶對testdb數據庫所有表的SELECT權限
GRANT SELECT ON testdb.* TO 'testuser'@'localhost';-- 授予用戶對testdb數據庫中user表的INSERT和UPDATE權限
GRANT INSERT, UPDATE ON testdb.user TO 'testuser'@'localhost';-- 授予用戶所有權限并允許傳播
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' WITH GRANT OPTION;-- 撤銷用戶的DELETE權限
REVOKE DELETE ON testdb.* FROM 'testuser'@'localhost';-- 撤銷用戶的GRANT OPTION權限
REVOKE GRANT OPTION ON testdb.* FROM 'testuser'@'localhost';-- 撤銷用戶所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';-- 查看用戶權限
SHOW GRANTS FOR 'testuser'@'localhost';
三、角色管理
作用:簡化權限管理,將權限分配給角色,再將角色分配給用戶
關鍵字:CREATE ROLE、DROP ROLE、GRANT ROLE、SET DEFAULT ROLE、REVOKE ROLE
1. 角色管理語法
sql
-- 創建角色
CREATE ROLE 'role_name';-- 授予角色權限
GRANT privilege_type ON database.table TO 'role_name';-- 將角色授予用戶
GRANT 'role_name' TO 'username'@'host';-- 設置用戶默認角色
SET DEFAULT ROLE 'role_name' TO 'username'@'host';-- 設置用戶激活角色
SET ROLE 'role_name';
SET ROLE ALL; -- 激活所有角色-- 撤銷角色權限
REVOKE privilege_type ON database.table FROM 'role_name';-- 撤銷用戶角色
REVOKE 'role_name' FROM 'username'@'host';-- 刪除角色
DROP ROLE 'role_name';
2. 示例
sql
-- 創建只讀角色
CREATE ROLE 'read_only';-- 授予只讀權限
GRANT SELECT ON testdb.* TO 'read_only';-- 創建讀寫角色
CREATE ROLE 'read_write';-- 授予讀寫權限
GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'read_write';-- 將角色授予用戶
GRANT 'read_only' TO 'user1'@'localhost';
GRANT 'read_write' TO 'user2'@'localhost';-- 設置默認角色
SET DEFAULT ROLE 'read_write' TO 'user2'@'localhost';-- 撤銷角色權限
REVOKE INSERT, UPDATE, DELETE ON testdb.* FROM 'read_write';-- 撤銷用戶角色
REVOKE 'read_only' FROM 'user1'@'localhost';-- 刪除角色
DROP ROLE 'read_only';
DROP ROLE 'read_write';
四、SQL 訪問控制流程圖
下面是 SQL 訪問控制的流程示意圖:
使用注意事項
- 最小權限原則:為用戶分配完成工作所需的最小權限集
- 定期審計:定期檢查用戶權限,撤銷不再需要的權限
- 密碼安全:使用強密碼策略,定期更換密碼
- 權限繼承:角色權限變更會影響所有關聯用戶,需謹慎操作
- 權限傳播:慎用 WITH GRANT OPTION,避免權限失控
- DROP USER 注意:刪除用戶前應確認其權限已清理,數據已備份
七、嵌入式SQL
嵌入式 SQL 是將 SQL 語句直接嵌入到高級程序設計語言(如 C、Java、Python 等)中,實現數據庫操作的技術。它允許程序員在高級語言環境中直接使用 SQL 訪問數據庫,結合了高級語言的流程控制能力和 SQL 的數據處理能力。
一、嵌入式 SQL 基本概念
- 宿主語言:嵌入 SQL 的高級程序設計語言(如 C、Java)
- SQL 通信區:用于在宿主語言和 SQL 之間傳遞狀態信息
- 主變量:宿主語言中定義的變量,用于 SQL 語句中傳遞數據
- 游標:用于處理 SQL 查詢返回的多行結果
二、嵌入式 SQL 語法特點
-
語句標識:使用特殊標記區分 SQL 語句和宿主語言代碼
- C 語言中通常使用
EXEC SQL
開頭 - Java 中使用 JDBC API
- Python 中使用 DB API
- C 語言中通常使用
-
主變量使用:在 SQL 語句中使用宿主語言變量
運行
EXEC SQL SELECT name, age INTO :name, :age FROM users WHERE id = :user_id;
-
游標操作:處理多行查詢結果
運行
EXEC SQL DECLARE user_cursor CURSOR FOR SELECT id, name FROM users;EXEC SQL OPEN user_cursor;while (1) {EXEC SQL FETCH user_cursor INTO :id, :name;if (sqlca.sqlcode != 0) break;// 處理數據 }EXEC SQL CLOSE user_cursor;
三、嵌入式 SQL 工作流程
四、不同語言中的嵌入式 SQL 實現
-
C 語言中的嵌入式 SQL
#include <stdio.h> EXEC SQL INCLUDE SQLCA; // SQL通信區int main() {char username[20] = "test";int age;EXEC SQL BEGIN DECLARE SECTION;char user[20];int user_age;EXEC SQL END DECLARE SECTION;EXEC SQL CONNECT TO database@server USER username;EXEC SQL SELECT age INTO :user_age FROM users WHERE name = :username;if (sqlca.sqlcode == 0) {printf("用戶年齡: %d\n", user_age);}EXEC SQL COMMIT WORK;EXEC SQL DISCONNECT;return 0; }
-
Java 中的嵌入式 SQL(JDBC)
java
import java.sql.*;public class EmbeddedSQLExample {public static void main(String[] args) {String url = "jdbc:mysql://localhost:3306/mydb";String username = "root";String password = "password";try (Connection conn = DriverManager.getConnection(url, username, password)) {String sql = "SELECT name, age FROM users WHERE id = ?";try (PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setInt(1, 1); // 設置參數try (ResultSet rs = pstmt.executeQuery()) {if (rs.next()) {String name = rs.getString("name");int age = rs.getInt("age");System.out.printf("用戶: %s, 年齡: %d%n", name, age);}}}} catch (SQLException e) {e.printStackTrace();}} }
-
Python 中的嵌入式 SQL(SQLite 示例)
import sqlite3conn = sqlite3.connect('example.db') cursor = conn.cursor()# 執行SQL查詢 user_id = 1 cursor.execute("SELECT name, age FROM users WHERE id = ?", (user_id,))# 獲取結果 row = cursor.fetchone() if row:name, age = rowprint(f"用戶: {name}, 年齡: {age}")conn.close()
五、嵌入式 SQL 的優缺點
優點:
- 無縫集成高級語言和數據庫功能
- 利用宿主語言的強大編程能力
- 執行效率高(預處理優化)
- 便于實現復雜業務邏輯
缺點:
- 代碼可讀性較差
- 數據庫移植性受限
- 調試和維護難度較大
- 事務管理復雜
六、適用場景
- 需要高性能數據處理的應用
- 對事務處理要求嚴格的系統
- 與現有數據庫架構深度集成的應用
- 資源受限環境下的數據庫操作
嵌入式 SQL 是數據庫編程的重要技術,現代開發中更常見的是通過 ORM(對象關系映射)工具實現類似功能,但在性能敏感場景和遺留系統中仍廣泛使用。