SQL 语法全面教程：从入门到精通

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。本教程涵盖 基础查询、高级操作、性能优化 等内容，适用于 MySQL、PostgreSQL、SQL Server 等主流数据库。

1. SQL 基础语法

(1) 数据库操作

-- 创建数据库
CREATE DATABASE db_name;

-- 删除数据库
DROP DATABASE db_name;

-- 使用数据库
USE db_name;  -- MySQL
\c db_name    -- PostgreSQL

(2) 表操作

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

-- 删除表
DROP TABLE users;

-- 修改表结构
ALTER TABLE users ADD COLUMN gender CHAR(1);
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

(3) 基本CRUD操作

-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

-- 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 20;

-- 更新数据
UPDATE users SET age = 25 WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

2. 高级查询

(1) 条件查询

-- 比较运算符
SELECT * FROM products WHERE price > 100;

-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND gender = 'M';

-- IN/NOT IN
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- BETWEEN
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- LIKE 模糊查询
SELECT * FROM users WHERE name LIKE '张%';  -- 张开头
SELECT * FROM users WHERE name LIKE '%三%'; -- 包含"三"

(2) 排序与分页

-- 排序
SELECT * FROM products ORDER BY price DESC;  -- 降序
SELECT * FROM users ORDER BY name ASC;       -- 升序

-- 分页 (不同数据库语法不同)
-- MySQL
SELECT * FROM products LIMIT 10 OFFSET 20;  -- 第3页，每页10条

-- PostgreSQL/SQL Server
SELECT * FROM products OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(3) 聚合函数

SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(price) AS max_price,
    MIN(price) AS min_price,
    SUM(quantity) AS total_quantity
FROM users;

(4) 分组查询

-- 按部门统计平均工资
SELECT 
    department, 
    AVG(salary) AS avg_salary,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;  -- HAVING对分组结果过滤

3. 多表操作

(1) 连接查询

-- 内连接
SELECT o.order_id, u.name, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 左连接
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接
SELECT u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 全连接
SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

(2) 子查询

-- WHERE子句中的子查询
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子句中的子查询
SELECT dept.name, emp_count.count
FROM departments dept
JOIN (
    SELECT department_id, COUNT(*) AS count
    FROM employees
    GROUP BY department_id
) emp_count ON dept.id = emp_count.department_id;

(3) 集合操作

-- 并集
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- 交集
SELECT id FROM table1
INTERSECT
SELECT id FROM table2;

-- 差集
SELECT id FROM table1
EXCEPT
SELECT id FROM table2;

4. 数据完整性约束

(1) 主键与外键

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

(2) 其他约束

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category VARCHAR(50) DEFAULT 'Other',
    UNIQUE (product_name)
);

5. 高级功能

(1) 视图

-- 创建视图
CREATE VIEW high_value_customers AS
SELECT * FROM customers WHERE total_purchases > 10000;

-- 使用视图
SELECT * FROM high_value_customers;

(2) 存储过程

-- MySQL存储过程
DELIMITER //
CREATE PROCEDURE update_salary(IN emp_id INT, IN increase_amount DECIMAL(10,2))
BEGIN
    UPDATE employees 
    SET salary = salary + increase_amount
    WHERE id = emp_id;
END //
DELIMITER ;

-- 调用
CALL update_salary(101, 500);

(3) 触发器

CREATE TRIGGER update_inventory
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

6. 性能优化技巧

1. 索引优化

   CREATE INDEX idx_name ON users(name);
   CREATE INDEX idx_email ON users(email);

1. 查询优化

   -- 避免SELECT *
   SELECT id, name FROM users;

   -- 使用EXISTS代替IN
   SELECT * FROM orders o
   WHERE EXISTS (
       SELECT 1 FROM customers c 
       WHERE c.id = o.customer_id AND c.status = 'VIP'
   );

1. 分析执行计划

   EXPLAIN SELECT * FROM users WHERE name LIKE '张%';

7. 不同数据库的SQL差异

8. 学习资源推荐

1. 在线练习平台

   • SQLZoo
   • LeetCode数据库题目

2. 免费教程

   • W3Schools SQL教程
   • SQLBolt

3. 进阶书籍

   • 《SQL必知必会》
   • 《高性能MySQL》

掌握SQL是数据相关工作的基础技能，建议通过实际项目练习巩固知识。如果需要特定数据库的深入教程，可以告诉我您使用的数据库类型（MySQL/PostgreSQL等），我可以提供更针对性的内容！