SQL基础全面指南：从CRUD操作到高级特性实战

前言

SQL（结构化查询语言）是与关系型数据库交互的标准语言，无论是数据分析师、后端开发人员还是全栈工程师，掌握SQL都是必备技能。本文将系统介绍MySQL和PostgreSQL两大主流数据库的CRUD操作，深入解析索引优化策略，详细讲解事务特性，并展示视图的实际应用场景，帮助您构建扎实的数据库基础。

一、MySQL与PostgreSQL的CRUD操作

1.1 数据库创建与选择

MySQL:

CREATE DATABASE shop;
USE shop;

PostgreSQL:

CREATE DATABASE shop;
\c shop;  -- 连接数据库

1.2 表创建与数据类型对比

MySQL创建表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

PostgreSQL创建表：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 自增序列
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

数据类型差异：

用途	MySQL	PostgreSQL
自增主键	AUTO_INCREMENT	SERIAL
字符串	VARCHAR	VARCHAR/TEXT
时间戳	TIMESTAMP	TIMESTAMPTZ
JSON支持	JSON类型	JSON/JSONB

1.3 CRUD操作详解

1.3.1 插入数据（Create）

批量插入：

-- MySQL/PostgreSQL通用
INSERT INTO users (username, email, age)
VALUES 
    ('user1', 'user1@example.com', 25),
    ('user2', 'user2@example.com', 30);

PostgreSQL特有（RETURNING子句）：

INSERT INTO users (username, email, age)
VALUES ('user3', 'user3@example.com', 28)
RETURNING id, username;  -- 返回插入的数据

1.3.2 查询数据（Read）

基础查询：

SELECT * FROM users WHERE age > 25 ORDER BY created_at DESC;

分页查询差异：

-- MySQL分页
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 第3页，每页10条

-- PostgreSQL分页
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或者
SELECT * FROM users OFFSET 20 FETCH NEXT 10 ROWS ONLY;

JSON查询（PostgreSQL更强大）：

-- 假设有json_data字段存储JSON
SELECT json_data->>'property' FROM table_name;

1.3.3 更新数据（Update）

UPDATE users 
SET email = 'new@example.com', age = age + 1 
WHERE username = 'user1';

1.3.4 删除数据（Delete）

DELETE FROM users WHERE age < 20;

清空表差异：

-- MySQL
TRUNCATE TABLE logs;

-- PostgreSQL
TRUNCATE TABLE logs RESTART IDENTITY;  -- 同时重置序列

二、索引优化艺术

2.1 索引类型对比

索引类型	MySQL支持	PostgreSQL支持
B-Tree索引	✅	✅
哈希索引	✅(Memory引擎)	✅
全文索引	✅	✅(更强大)
空间索引	✅	✅
部分索引	❌	✅
函数索引	❌	✅

2.2 创建索引示例

普通索引：

-- 通用语法
CREATE INDEX idx_users_email ON users(email);

PostgreSQL部分索引：

-- 只为活跃用户创建索引
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

MySQL全文索引：

ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content);

2.3 索引优化策略

选择高区分度列：

-- 不好的索引
CREATE INDEX idx_gender ON users(gender);  -- 只有'M','F'两种值

-- 好的索引
CREATE INDEX idx_username ON users(username);

复合索引顺序：

-- 最左前缀原则
CREATE INDEX idx_name_age ON users(last_name, first_name, age);
-- 能优化 WHERE last_name=? AND first_name=?
-- 但不能优化 WHERE first_name=? AND age=?

EXPLAIN分析：

EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';

 PostgreSQL更强大的分析工具：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM large_table WHERE category = 'books';

三、事务与ACID特性

3.1 事务基本语法

MySQL事务：

START TRANSACTION;
-- SQL语句
COMMIT;  -- 或 ROLLBACK;

PostgreSQL事务：

BEGIN;
-- SQL语句
COMMIT;  -- 或 ROLLBACK;

3.2 隔离级别对比

隔离级别	MySQL默认	PostgreSQL默认
READ UNCOMMITTED	✅	❌
READ COMMITTED	✅	✅
REPEATABLE READ	✅(默认)	✅
SERIALIZABLE	✅	✅

设置隔离级别：

-- MySQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

3.3 实际应用案例

银行转账事务：

BEGIN;
-- 检查账户余额
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;

-- 转账操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 记录交易
INSERT INTO transactions (from_user, to_user, amount) 
VALUES (1, 2, 100);

COMMIT;

PostgreSQL特有功能（SAVEPOINT）：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
SAVEPOINT step1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果出错可以回滚到保存点
ROLLBACK TO SAVEPOINT step1;
COMMIT;

四、视图的高级应用

4.1 创建视图

基本视图：

-- 通用语法
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE is_active = true AND deleted_at IS NULL;

PostgreSQL物化视图：

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY month
WITH DATA;

REFRESH MATERIALIZED VIEW monthly_sales;  -- 手动刷新

4.2 视图更新限制

可更新视图条件：

1. 来自单一基表

2. 包含基表主键

3. 不包含GROUP BY/DISTINCT/聚合函数

示例：

-- 可更新视图
CREATE VIEW user_emails AS
SELECT id, username, email FROM users;

-- 可以执行
UPDATE user_emails SET email = 'new@example.com' WHERE id = 1;

4.3 视图应用场景

简化复杂查询：

CREATE VIEW order_details AS
SELECT 
    o.id, o.order_date, o.total_amount,
    u.username, u.email,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, u.username, u.email;

数据安全：

-- 只暴露必要字段
CREATE VIEW public_profiles AS
SELECT id, username, avatar FROM users;

跨数据库兼容：

-- 使用视图统一不同数据库的表结构差异
CREATE VIEW unified_products AS
SELECT 
    id, 
    name AS product_name,
    price AS unit_price
FROM products;  -- 适配不同数据库的实际表结构

五、性能优化实战

5.1 MySQL优化技巧

索引优化：

-- 使用覆盖索引
SELECT username, email FROM users WHERE age > 25;
-- 创建索引 (age, username, email) 可以完全通过索引获取数据

查询缓存（MySQL 8.0+已移除）：

-- 旧版本MySQL可以使用
SELECT SQL_CACHE * FROM products WHERE category = 'electronics';

5.2 PostgreSQL优化技巧

CTE优化：

WITH recent_orders AS (
    SELECT * FROM orders 
    WHERE order_date > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_orders JOIN users ON recent_orders.user_id = users.id;

并行查询：

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT * FROM large_table WHERE complex_condition;

5.3 通用优化建议

避免SELECT ：

-- 不好
SELECT * FROM users;

-- 好
SELECT id, username, email FROM users;

合理使用JOIN：

-- 使用EXISTS代替IN
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM inventory i 
    WHERE i.product_id = p.id AND i.quantity > 0
);

分页优化：

-- 传统分页问题
SELECT * FROM large_table ORDER BY id LIMIT 10 OFFSET 10000;  -- 慢

-- 优化方案
SELECT * FROM large_table WHERE id > 10000 ORDER BY id LIMIT 10;

结语

SQL作为数据处理的核心语言，其深度掌握对开发者至关重要。本文涵盖了MySQL和PostgreSQL的CRUD操作、索引优化策略、事务控制以及视图应用等核心内容，为您构建了坚实的SQL基础。

进一步学习建议：

1. 学习窗口函数（Window Functions）进行高级分析

2. 探索存储过程和触发器

3. 了解数据库分区策略

4. 研究不同数据库的复制与高可用方案

如果您在SQL实践中遇到任何问题，欢迎在评论区留言讨论。觉得本文有帮助的话，请点赞收藏支持！