以下是常用的 SQL 语句分类整理,覆盖数据查询、操作、表管理和高级功能,适用于大多数关系型数据库(如 MySQL、PostgreSQL、SQL Server):
目录
一、数据查询(DQL)
1. 基础查询
-- 查询所有字段
SELECT * FROM employees;
-- 查询特定字段
SELECT first_name, last_name FROM employees;
-- 去重查询
SELECT DISTINCT department FROM employees;
2. 条件过滤(WHERE)
-- 简单条件
SELECT * FROM orders WHERE amount > 1000;
-- 多条件组合(AND/OR)
SELECT * FROM products
WHERE price BETWEEN 50 AND 100
AND category = 'Electronics';
-- 模糊匹配(LIKE)
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';
-- IN 运算符
SELECT * FROM employees
WHERE department_id IN (10, 20, 30);
3. 排序与分页
-- 排序(ASC/DESC)
SELECT * FROM products
ORDER BY price DESC, stock_quantity ASC;
-- 分页(MySQL)
SELECT * FROM orders
LIMIT 10 OFFSET 20; -- 等价于第3页(每页10条)
-- 分页(SQL Server)
SELECT * FROM orders
ORDER BY order_date
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
二、数据操作(DML)
1. 插入数据(INSERT)
-- 插入单条数据
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW());
-- 插入多条数据
INSERT INTO products (name, price)
VALUES
('Laptop', 999.99),
('Mouse', 29.99),
('Keyboard', 49.99);
2. 更新数据(UPDATE)
-- 更新单字段
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';
-- 更新多字段
UPDATE orders
SET status = 'Shipped', shipped_date = CURRENT_DATE
WHERE order_id = 1001;
3. 删除数据(DELETE)
-- 删除特定记录
DELETE FROM logs
WHERE created_at < '2023-01-01';
-- 清空表(慎用!)
TRUNCATE TABLE temp_data;
三、表管理(DDL)
1. 创建表(CREATE TABLE)
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
2. 修改表(ALTER TABLE)
-- 添加列
ALTER TABLE users
ADD COLUMN phone_number VARCHAR(15);
-- 修改列类型
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12, 2);
-- 删除列
ALTER TABLE orders
DROP COLUMN old_reference;
3. 删除表(DROP TABLE)
DROP TABLE backup_data; -- 删除表结构及数据
四、高级查询
1. 聚合函数(GROUP BY)
-- 统计每个部门的平均工资
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000; -- HAVING过滤聚合结果
2. 多表连接(JOIN)
-- 内连接
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- 左连接(包含未匹配的订单)
SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- 自连接(查找员工的上级)
SELECT e.employee_id, m.manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
3. 子查询
-- 子查询在WHERE中
SELECT product_name
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name = 'Books'
);
-- 关联子查询
SELECT e.first_name, e.salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
五、实用功能
1. 日期处理
-- 当前日期
SELECT CURRENT_DATE;
-- 日期格式化(MySQL)
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
FROM orders;
-- 日期计算(PostgreSQL)
SELECT order_date + INTERVAL '7 days' AS due_date
FROM orders;
2. 字符串处理
-- 拼接字符串(SQL Server)
SELECT first_name + ' ' + last_name AS full_name
FROM employees;
-- 截取子串(MySQL)
SELECT SUBSTRING(email, 1, 5) AS prefix
FROM users;
-- 替换文本(PostgreSQL)
SELECT REPLACE(description, 'old', 'new')
FROM products;
3. 窗口函数
-- 计算排名
SELECT
product_id,
sales,
RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM products;
-- 累计求和
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
六、性能优化技巧
索引使用
-- 创建索引 CREATE INDEX idx_customer_email ON customers(email);
执行计划分析
EXPLAIN SELECT * FROM orders WHERE status = 'Shipped';
避免全表扫描
- 对WHERE条件列添加索引
- 避免在WHERE中对字段进行函数操作(如
WHERE YEAR(date) = 2023
→ 改为范围查询)