跳到主要内容

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);

-- 在 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;

数据操作​

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轻量级嵌入式数据库