SQL 基础查询

→ 返回 数据库基础

关系型数据库用 SQL（Structured Query Language）操作数据。本文汇总日常开发最常用的 DDL / DML / 查询 写法，以标准 SQL 为主，示例兼容 MySQL / PostgreSQL。MySQL 客户端与运维命令见 MySQL 基础命令；多表 JOIN、子查询进阶见 联表与查询。

---

SQL 语句分类

类型	全称	作用	示例
DDL	Data Definition Language	定义库表结构	CREATE、ALTER、DROP
DML	Data Manipulation Language	增删改查数据	INSERT、UPDATE、DELETE、SELECT
DCL	Data Control Language	权限控制	GRANT、REVOKE
TCL	Transaction Control Language	事务控制	BEGIN、COMMIT、ROLLBACK

---

库与表（DDL 速查）

-- 建库
CREATE DATABASE app_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;
 
USE app_db;
 
-- 建表
CREATE TABLE users (
    id         BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(64)  NOT NULL,
    email      VARCHAR(128) NOT NULL,
    status     TINYINT      NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email (email),
    KEY idx_status (status)
);
 
-- 改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
ALTER TABLE users ADD INDEX idx_phone (phone);
ALTER TABLE users DROP COLUMN phone;
 
-- 删表
DROP TABLE IF EXISTS tmp_logs;

表设计原则见 关系模型与范式；索引设计见 索引。

---

SELECT 基础

基本结构

SELECT 列1, 列2, ...
FROM   表名
WHERE  条件
GROUP BY 分组列
HAVING 分组后条件
ORDER BY 排序列 [ASC | DESC]
LIMIT  行数 [OFFSET 偏移];

执行顺序（逻辑上）：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

常用查询

-- 查指定列
SELECT id, name, email FROM users WHERE status = 1;
 
-- 查全部列（生产环境避免 SELECT *，除非调试）
SELECT * FROM users WHERE id = 1001;
 
-- 排序 + 分页（第 1 页，每页 20 条）
SELECT id, name FROM users
WHERE status = 1
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
 
-- 深分页优化：用游标代替大 OFFSET
SELECT id, name FROM users
WHERE status = 1 AND id > 10000
ORDER BY id
LIMIT 20;

---

WHERE 条件

比较与逻辑

-- 比较
SELECT * FROM orders WHERE amount >= 100 AND amount < 500;
SELECT * FROM users WHERE status IN (1, 2, 3);
SELECT * FROM users WHERE id BETWEEN 100 AND 200;
 
-- 逻辑组合
SELECT * FROM orders
WHERE status = 'paid'
  AND (amount > 1000 OR user_id IN (1, 2, 3));
 
-- NULL 判断（= NULL 永远为假，必须用 IS NULL）
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

模糊匹配

-- LIKE：% 任意长度，_ 单个字符
SELECT * FROM users WHERE name LIKE '张%';      -- 前缀匹配，可走索引
SELECT * FROM users WHERE name LIKE '%三';      -- 后缀，通常不走索引
SELECT * FROM users WHERE email LIKE '%@gmail.com';
 
-- 正则（MySQL）
SELECT * FROM users WHERE name REGEXP '^张[0-9]+$';

时间范围

-- 推荐左闭右开，避免边界漏/重
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at <  '2025-01-01 00:00:00';

---

INSERT / UPDATE / DELETE

插入

-- 单行
INSERT INTO users (name, email, status)
VALUES ('张三', 'zhang@example.com', 1);
 
-- 多行
INSERT INTO users (name, email, status) VALUES
  ('李四', 'li@example.com', 1),
  ('王五', 'wang@example.com', 1);
 
-- 插入或忽略重复（MySQL）
INSERT IGNORE INTO users (email, name) VALUES ('zhang@example.com', '张三');
 
-- 存在则更新（MySQL UPSERT）
INSERT INTO users (email, name, status)
VALUES ('zhang@example.com', '张三', 1)
ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status);

更新

UPDATE users SET status = 0 WHERE id = 1001;
 
UPDATE orders
SET status = 'cancelled', updated_at = NOW()
WHERE id = 5001 AND status = 'pending';
 
-- 务必带 WHERE，否则全表更新

删除

DELETE FROM users WHERE id = 1001;
 
-- 清空表（DDL，不可回滚，重置自增）
TRUNCATE TABLE logs_2023;

---

聚合与分组

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT user_id) FROM orders;
 
-- 分组统计
SELECT status, COUNT(*) AS cnt
FROM users
GROUP BY status;
 
-- HAVING：过滤分组结果（WHERE 过滤行，HAVING 过滤组）
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING SUM(amount) > 1000;
 
-- 常用聚合函数
SELECT
  COUNT(*)   AS row_cnt,
  SUM(amount) AS total,
  AVG(amount) AS avg_amt,
  MAX(amount) AS max_amt,
  MIN(amount) AS min_amt
FROM orders
WHERE status = 'paid';

---

JOIN 联表（基础）

假设 orders.user_id 关联 users.id：

-- INNER JOIN：只返回两表都匹配的行
SELECT o.id, o.amount, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';
 
-- LEFT JOIN：左表全保留，右表无匹配则 NULL
SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
 
-- 多表
SELECT o.id, u.name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'paid';

JOIN 类型	语义
INNER JOIN	交集，只保留匹配行
LEFT JOIN	左表全保留
RIGHT JOIN	右表全保留
CROSS JOIN	笛卡尔积，慎用

注意：LEFT JOIN 后对右表列的过滤应写在 ON 里，写在 WHERE 里会把 LEFT JOIN 变成 INNER JOIN 语义。

---

子查询（基础）

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE status = 'paid');
 
-- EXISTS（存在性判断，常比 IN 高效）
SELECT u.*
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);
 
-- 标量子查询（返回单值）
SELECT id, name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt
FROM users u;

---

常用函数

字符串

SELECT CONCAT(name, ' <', email, '>') AS label FROM users;
SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS local_part FROM users;
SELECT LENGTH(name), UPPER(name), LOWER(email) FROM users;

数值

SELECT ROUND(amount, 2), CEIL(amount), FLOOR(amount) FROM orders;
SELECT MOD(id, 10) AS bucket FROM users;

日期时间

SELECT NOW(), CURDATE(), DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM orders;
SELECT DATEDIFF('2025-01-01', created_at) FROM orders;

条件表达式

-- CASE WHEN
SELECT id,
  CASE status
    WHEN 1 THEN '正常'
    WHEN 0 THEN '禁用'
    ELSE '未知'
  END AS status_label
FROM users;
 
-- IF（MySQL）
SELECT IF(amount >= 1000, '大额', '普通') AS level FROM orders;
 
-- COALESCE：取第一个非 NULL
SELECT COALESCE(phone, email, '无联系方式') FROM users;

---

事务控制

START TRANSACTION;   -- 或 BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT;              -- 提交
-- ROLLBACK;         -- 回滚

事务原理、隔离级别见 数据库事务；并发控制见 MVCC、锁机制。

---

实用技巧

场景	写法
去重	SELECT DISTINCT user_id FROM orders;
合并结果集	SELECT id, name FROM users_a UNION SELECT id, name FROM users_b;
限制更新行数	UPDATE orders SET ... WHERE ... LIMIT 100;（MySQL）
批量插入来自查询	INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2023-01-01';
查看执行计划	EXPLAIN SELECT ...;（见 SQL 优化）

---

常见坑

问题	原因	解决
UPDATE/DELETE 误伤全表	忘记写 WHERE	先 SELECT 确认，再更新；生产环境开 safe update
NULL 比较失效	col = NULL 永远为假	用 IS NULL / IS NOT NULL
LIKE '%xxx' 不走索引	前缀通配符导致全表扫描	前缀匹配或搜索引擎
隐式类型转换	字符串列与数字比较	保持类型一致，避免索引失效
大事务	一次更新/删除百万行	分批处理，缩短持锁时间

---

相关

• MySQL 基础命令 — 客户端、DDL、运维命令
• 联表与查询 — JOIN 进阶、CTE、EXISTS 优化
• 开窗函数 — ROW_NUMBER、RANK 等
• 索引 — 查询如何走索引
• SQL 优化 — EXPLAIN、慢查询优化