SQL实战宝典：常用语句全解析

SQL 常用语句分类示例

数据查询（SELECT）

查询所有列：

SELECT * FROM employees;

查询特定列并排序：

SELECT first_name, last_name 
FROM employees 
ORDER BY hire_date DESC;

条件筛选（WHERE）：

SELECT product_name, price 
FROM products 
WHERE price > 100 AND category = 'Electronics';

数据操作（DML）

插入单条记录：

INSERT INTO customers (name, email) 
VALUES ('John Doe', 'john@example.com');

批量插入：

INSERT INTO orders (customer_id, order_date) 
VALUES (101, '2023-01-15'), (102, '2023-01-16');

更新数据：

UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Engineering';

删除数据：

DELETE FROM logs 
WHERE created_at < '2022-01-01';

表操作（DDL）

创建表：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表结构：

ALTER TABLE products 
ADD COLUMN stock_count INT DEFAULT 0;

删除表：

DROP TABLE temp_data;

高级查询

分组统计（GROUP BY）：

SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

多表连接（JOIN）：

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

子查询：

SELECT employee_name 
FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
);

常用函数

字符串函数：

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       UPPER(email) AS email_upper,
       SUBSTRING(phone, 1, 3) AS area_code
FROM contacts;

日期函数：

SELECT order_id, 
       DATEDIFF(day, order_date, shipped_date) AS processing_days,
       DATE_FORMAT(order_date, '%Y-%m') AS order_month
FROM orders;

聚合函数：

SELECT MAX(salary) AS max_salary,
       MIN(age) AS min_age,
       SUM(revenue) AS total_revenue
FROM company_stats;

事务控制

事务处理：

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT;

索引优化

创建索引：

CREATE INDEX idx_customer_name ON customers(last_name, first_name);

查看执行计划：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1005;

视图与存储过程

创建视图：

CREATE VIEW active_customers AS
SELECT customer_id, name, email 
FROM customers 
WHERE last_purchase_date > DATE_SUB(NOW(), INTERVAL 6 MONTH);

创建存储过程：

CREATE PROCEDURE update_salary(IN emp_id INT, IN increase_percent DECIMAL(5,2))
BEGIN
    UPDATE employees 
    SET salary = salary * (1 + increase_percent/100)
    WHERE employee_id = emp_id;
END;

窗口函数

排名计算：

SELECT product_id, sales_amount,
       RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank,
       DENSE_RA