跳到主要内容

SQL 知识速查表

本页面汇总了 SQL 数据库查询中最常用的语法和知识点，方便快速查阅。

基础操作​

创建数据库和表​

CREATE DATABASE dbname;
USE dbname;

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

数据类型​

类型说明
INT/INTEGER整数
BIGINT大整数
DECIMAL(p,s)精确数值
FLOAT/REAL浮点数
VARCHAR(n)可变长度字符串
CHAR(n)固定长度字符串
TEXT长文本
DATE日期
TIME时间
DATETIME/TIMESTAMP日期时间
BOOLEAN布尔值

查询操作​

SELECT​

SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;           -- 查询所有列
SELECT DISTINCT column FROM table; -- 去重
SELECT column AS alias FROM table; -- 别名

WHERE 条件​

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name = 'Tom';
SELECT * FROM users WHERE age >= 18 AND age <= 30;
SELECT * FROM users WHERE name LIKE 'T%';  -- 模糊匹配
SELECT * FROM users WHERE age IN (18, 20, 22);  -- 范围
SELECT * FROM users WHERE age BETWEEN 18 AND 30;  -- 区间

运算符​

=       -- 等于
<>      -- 不等于
>       -- 大于
<       -- 小于
>=      -- 大于等于
<=      -- 小于等于
AND     -- 逻辑与
OR      -- 逻辑或
NOT     -- 逻辑非
IS NULL -- 为空
IS NOT NULL -- 不为空

排序和分页​

SELECT * FROM users ORDER BY age ASC;           -- 升序
SELECT * FROM users ORDER BY age DESC;          -- 降序
SELECT * FROM users ORDER BY age DESC, name ASC;  -- 多条件

-- 分页（不同数据库语法可能不同）
SELECT * FROM users LIMIT 10 OFFSET 0;  -- MySQL/PostgreSQL
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;  -- SQL Server/Oracle
SELECT * FROM users WHERE ROWNUM <= 10;  -- Oracle

聚合函数​

函数说明
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
SELECT COUNT(*) FROM users;
SELECT SUM(price) FROM orders;
SELECT AVG(age) FROM users;
SELECT MAX(price), MIN(price) FROM products;

-- 分组聚合
SELECT department, COUNT(*) FROM employees GROUP BY department;
SELECT category, SUM(sales) FROM orders GROUP BY category;
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

连接（JOIN）​

-- 内连接
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- 左连接
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- 右连接
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- 全连接（MySQL 不支持，可用 UNION 实现）
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- 自连接
SELECT a.name, b.name AS manager FROM employees a 
JOIN employees b ON a.manager_id = b.id;

子查询​

-- 在 WHERE 中使用子查询（标量子查询）
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- 在 WHERE 中使用 IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

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

-- 在 FROM 中使用子查询（派生表）
SELECT * FROM (SELECT * FROM users WHERE age > 20) AS young_users;

-- 在 SELECT 中使用子查询
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- ANY / ALL
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'IT');
SELECT * FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department);

窗口函数​

-- 基本语法
function_name() OVER (
    PARTITION BY 列名      -- 分组（可选）
    ORDER BY 列名          -- 排序（可选）
    frame_clause          -- 窗口框架（可选）
)

-- ROW_NUMBER：行号
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

-- RANK：排名（有间隙）
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS ranking FROM employees;

-- DENSE_RANK：排名（无间隙）
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking FROM employees;

-- 分区内排名
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG / LEAD：前后行值
SELECT date, revenue,
       LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
       LEAD(revenue) OVER (ORDER BY date) AS next_revenue
FROM daily_sales;

-- 聚合窗口函数
SELECT name, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

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

-- 移动平均
SELECT date, price,
       AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3
FROM stock_prices;

-- NTILE：分桶
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

-- FIRST_VALUE / LAST_VALUE
SELECT name, department, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_max,
       LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC 
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_min
FROM employees;

公用表表达式（CTE）​

-- 基本 CTE
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 50000
)
SELECT * FROM high_earners;

-- 多个 CTE
WITH 
active_users AS (SELECT * FROM users WHERE status = 'active'),
user_orders AS (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id)
SELECT u.name, COALESCE(o.cnt, 0) AS order_count
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- 递归 CTE
WITH RECURSIVE org_tree AS (
    -- 锚点：顶级员工
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下属
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level;

-- 递归 CTE：生成序列
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

-- CTE 用于数据修改
WITH deleted_users AS (
    SELECT id FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
)
DELETE FROM users WHERE id IN (SELECT id FROM deleted_users);

数据操作​

INSERT 插入​

INSERT INTO users (name, age) VALUES ('Tom', 20);
INSERT INTO users (name, age) VALUES ('Jerry', 18), ('Bob', 22);
INSERT INTO users SET name='Tom', age=20;

UPDATE 更新​

UPDATE users SET age = 21 WHERE id = 1;
UPDATE users SET age = age + 1 WHERE name = 'Tom';
UPDATE users SET name = 'Tom', age = 20 WHERE id = 1;

DELETE 删除​

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;
DELETE FROM users;  -- 删除所有（谨慎！）

约束​

-- 主键
PRIMARY KEY
PRIMARY KEY (id)

-- 唯一约束
UNIQUE (email)

-- 非空约束
NOT NULL

-- 默认值
DEFAULT 18

-- 外键
FOREIGN KEY (user_id) REFERENCES users(id)

-- 检查约束
CHECK (age >= 0 AND age <= 150)

常用函数​

字符串函数​

SELECT LENGTH('hello');           -- 长度
SELECT UPPER('hello');            -- 转大写
SELECT LOWER('HELLO');            -- 转小写
SELECT SUBSTRING('hello', 1, 3);  -- 截取
SELECT TRIM('  hello  ');         -- 去除空白
SELECT CONCAT('hello', 'world'); -- 拼接
SELECT REPLACE('hello', 'l', 'r');-- 替换

日期函数​

SELECT NOW();                      -- 当前时间
SELECT CURDATE();                  -- 当前日期
SELECT DATE('2024-01-01');         -- 提取日期
SELECT YEAR('2024-01-01');         -- 提取年
SELECT MONTH('2024-01-01');        -- 提取月
SELECT DAY('2024-01-01');          -- 提取日
SELECT DATEDIFF('2024-01-01', '2023-01-01');  -- 日期差

条件函数​

-- IF 函数
SELECT IF(age > 18, '成年', '未成年') FROM users;

-- CASE 语句
SELECT CASE 
    WHEN age < 18 THEN '未成年'
    WHEN age < 30 THEN '青年'
    ELSE '中年'
END FROM users;

-- COALESCE 返回第一个非空值
SELECT COALESCE(email, '未设置') FROM users;

索引​

-- 创建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

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

视图​

-- 创建视图
CREATE VIEW user_orders AS
SELECT u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 使用视图
SELECT * FROM user_orders;

-- 删除视图
DROP VIEW user_orders;

事务​

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 提交

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;  -- 回滚

常见数据库​

数据库特点
MySQL最流行的开源数据库
PostgreSQL功能最强大的开源数据库
SQL ServerMicrosoft 企业级数据库
OracleOracle 公司企业级数据库
SQLite轻量级嵌入式数据库