SQL 速查表

基本查询

SELECT name, age FROM users;
SELECT * FROM users WHERE age > 18;
SELECT DISTINCT city FROM users;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;   -- 分页

WHERE 条件

WHERE age = 25
WHERE age BETWEEN 18 AND 30
WHERE name LIKE '张%'              -- 以"张"开头
WHERE name LIKE '%明'              -- 以"明"结尾
WHERE name LIKE '%小%'             -- 包含"小"
WHERE city IN ('北京', '上海')
WHERE email IS NULL
WHERE email IS NOT NULL
WHERE age > 18 AND city = '北京'
WHERE age > 30 OR city = '上海'
WHERE NOT (age < 18)

聚合函数

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;
SELECT AVG(age) FROM users;
SELECT SUM(salary) FROM employees;
SELECT MAX(age), MIN(age) FROM users;

-- GROUP BY
SELECT city, COUNT(*) as cnt
FROM users
GROUP BY city
HAVING cnt > 5;

JOIN

-- INNER JOIN（交集）
SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN（左表全保留）
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN（右表全保留）
SELECT * FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- FULL OUTER JOIN（全保留）
SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

窗口函数

-- 排名
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rn,
    RANK() OVER (ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as drank
FROM employees;

-- 分区排名
SELECT name, dept, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
FROM employees;

-- 累计
SELECT date, amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

-- 前后行
SELECT name, salary,
    LAG(salary, 1) OVER (ORDER BY salary) as prev_salary,
    LEAD(salary, 1) OVER (ORDER BY salary) as next_salary
FROM employees;

子查询

-- WHERE 子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- FROM 子查询
SELECT * FROM (SELECT city, COUNT(*) c FROM users GROUP BY city) t
WHERE c > 3;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

数据操作 (DML)

INSERT INTO users (name, age) VALUES ('小明', 25);
INSERT INTO users (name, age) VALUES ('小红', 22), ('小刚', 28);

UPDATE users SET age = 26 WHERE name = '小明';
UPDATE users SET age = age + 1;      -- 所有人加一岁

DELETE FROM users WHERE id = 5;
DELETE FROM users;                   -- ❌ 删除所有行！
TRUNCATE TABLE users;                -- 快速清空

表操作 (DDL)

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;
DROP TABLE users;

索引

CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_multi ON users(name, age);   -- 复合索引

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '小明';

-- 删除索引
DROP INDEX idx_name ON users;