SQL语句

这篇文章主要记录SQL的一些使用技巧和常见问题解决方案。

SQL语句详解

基础语句

SELECT 查询数据

基本语法：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

查询所有列：

SELECT * FROM employees;

条件查询：

SELECT * FROM employees WHERE salary > 5000;

排序：

SELECT * FROM employees ORDER BY salary DESC;

分页查询：

-- MySQL
SELECT * FROM employees LIMIT 10 OFFSET 20;
-- SQL Server
SELECT * FROM employees ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- Oracle
SELECT * FROM employees WHERE ROWNUM BETWEEN 21 AND 30;

INSERT 插入数据

基本语法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

插入多行：

INSERT INTO employees (name, department, salary)
VALUES 
('张三', '研发部', 8000),
('李四', '市场部', 7500),
('王五', '财务部', 9000);

从其他表插入数据：

INSERT INTO employees_backup
SELECT * FROM employees WHERE department = '研发部';

UPDATE 更新数据

基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

更新示例：

UPDATE employees
SET salary = salary * 1.1
WHERE department = '研发部';

DELETE 删除数据

基本语法：

DELETE FROM table_name WHERE condition;

删除示例：

DELETE FROM employees WHERE last_login_date < '2019-01-01';

CREATE TABLE 创建表

基本语法：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

创建表示例：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    CONSTRAINT uk_emp_name UNIQUE (name)
);

ALTER TABLE 修改表

添加列：

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

修改列：

ALTER TABLE employees MODIFY COLUMN department VARCHAR(100);
-- SQL Server
ALTER TABLE employees ALTER COLUMN department VARCHAR(100);

删除列：

ALTER TABLE employees DROP COLUMN email;

高级查询

聚合函数

常用聚合函数：

-- 计数
SELECT COUNT(*) FROM employees;
-- 求和
SELECT SUM(salary) FROM employees;
-- 平均值
SELECT AVG(salary) FROM employees;
-- 最大值
SELECT MAX(salary) FROM employees;
-- 最小值
SELECT MIN(salary) FROM employees;

分组统计：

SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

连接查询

内连接：

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

左连接：

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

右连接：

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

全连接：

-- MySQL不支持FULL JOIN，可以通过UNION实现
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

子查询

WHERE子句中的子查询：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

FROM子句中的子查询：

SELECT dept_name, avg_salary
FROM (
    SELECT department as dept_name, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 8000;

EXISTS子查询：

SELECT name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id AND e.salary > 10000
);

常用函数

字符串函数：

-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- 截取子串
SELECT SUBSTRING(name, 1, 3) FROM employees;
-- 转换大小写
SELECT UPPER(name), LOWER(email) FROM employees;
-- 替换
SELECT REPLACE(description, '旧文本', '新文本') FROM products;

日期函数：

-- 当前日期
SELECT CURRENT_DATE();
-- 当前时间戳
SELECT CURRENT_TIMESTAMP();
-- 日期加减
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;
-- 日期差
SELECT DATEDIFF(CURRENT_DATE(), hire_date) AS days_employed FROM employees;
-- 日期格式化
SELECT DATE_FORMAT(hire_date, '%Y年%m月%d日') FROM employees;

条件函数：

-- CASE表达式
SELECT 
    name,
    CASE 
        WHEN salary < 5000 THEN '低'
        WHEN salary BETWEEN 5000 AND 10000 THEN '中'
        ELSE '高'
    END AS salary_level
FROM employees;

-- IF函数
SELECT name, IF(active = 1, '在职', '离职') AS status FROM employees;

窗口函数

排名函数：

-- ROW_NUMBER 唯一排名
SELECT name, salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK 并列排名，跳过重复的名次
SELECT name, salary,
    RANK() OVER(ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK 并列排名，不跳过名次
SELECT name, salary,
    DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
FROM employees;

分析函数：

-- 累计求和
SELECT name, salary,
    SUM(salary) OVER(PARTITION BY department ORDER BY hire_date) AS running_total
FROM employees;

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

事务控制

事务基本操作：

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;
-- 或回滚事务
ROLLBACK;

事务隔离级别：

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

索引操作

创建索引：

-- 普通索引
CREATE INDEX idx_name ON employees(name);

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

-- 复合索引
CREATE INDEX idx_dept_name ON employees(department, name);

删除索引：

DROP INDEX idx_name ON employees;

视图操作

创建视图：

CREATE VIEW employee_details AS
SELECT e.id, e.name, d.department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;

修改视图：

ALTER VIEW employee_details AS
SELECT e.id, e.name, d.department_name, e.salary, e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id;

删除视图：

DROP VIEW employee_details;

性能优化技巧

查询优化

1. 使用具体的列名代替*

-- 不推荐
SELECT * FROM employees;
-- 推荐
SELECT id, name, department, salary FROM employees;

2. 使用EXPLAIN分析查询

EXPLAIN SELECT * FROM employees WHERE department = '研发部';

3. 合理使用索引

-- 对经常用于WHERE、ORDER BY、JOIN的列创建索引
CREATE INDEX idx_department ON employees(department);

4. 避免在索引列上使用函数

-- 不推荐（无法使用索引）
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
-- 推荐
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

5. 使用LIMIT限制结果集大小

SELECT * FROM logs ORDER BY created_at DESC LIMIT 1000;

数据库设计优化

1. 合理使用范式

   • 第一范式：每列都是原子的
   • 第二范式：非主键列完全依赖主键
   • 第三范式：非主键列之间没有传递依赖

2. 适当反范式化

   • 为提高查询性能，可以适当增加冗余字段
   • 但需要保证数据一致性

3. 使用合适的数据类型

   • 使用最小满足需求的数据类型
   • 例如：使用TINYINT(1)而非CHAR(1)存储布尔值

常见问题解决方案

处理NULL值

NULL比较：

-- 正确方式（使用IS NULL或IS NOT NULL）
SELECT * FROM employees WHERE email IS NULL;
-- 错误方式（无法查到结果）
SELECT * FROM employees WHERE email = NULL;

COALESCE函数处理NULL：

-- 返回第一个非NULL的值
SELECT name, COALESCE(phone, email, '无联系方式') AS contact
FROM customers;

处理重复数据

查找重复记录：

SELECT name, COUNT(*) as count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

删除重复记录（保留ID最小的一条）：

-- MySQL
DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.id > e2.id AND e1.name = e2.name;

大批量数据操作

分批处理数据：

-- 分批插入
INSERT INTO target_table SELECT * FROM source_table LIMIT 10000 OFFSET 0;
INSERT INTO target_table SELECT * FROM source_table LIMIT 10000 OFFSET 10000;
-- 以此类推

禁用/启用索引（适用于大量数据导入）：

-- MySQL
ALTER TABLE employees DISABLE KEYS;
-- 大量数据导入操作
ALTER TABLE employees ENABLE KEYS;

使用批量操作：

-- 批量插入
INSERT INTO employees (name, department, salary) VALUES 
('张三', '研发部', 8000),
('李四', '市场部', 7500),
('王五', '财务部', 9000);

不同数据库的SQL差异

MySQL特有语法

AUTO_INCREMENT：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

LIMIT子句：

SELECT * FROM employees LIMIT 10;

Oracle特有语法

序列：

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

INSERT INTO employees (id, name)
VALUES (emp_seq.NEXTVAL, '张三');

ROWNUM分页：

SELECT * FROM (
    SELECT a.*, ROWNUM rn
    FROM (SELECT * FROM employees ORDER BY id) a
    WHERE ROWNUM <= 30
) WHERE rn > 20;

SQL Server特有语法

IDENTITY：

CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);

TOP子句：

SELECT TOP 10 * FROM employees ORDER BY salary DESC;

总结

SQL是一门强大的语言，掌握了上述技巧和解决方案，可以更高效地处理数据库相关工作。根据具体场景选择合适的SQL语句和优化技巧，能够大幅提升数据库的性能和开发效率。