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_salaryFROM employeesGROUP BY department;

多表连接（JOIN）：

SELECT o.order_id, c.customer_name, p.product_nameFROM orders oJOIN customers c ON o.customer_id = c.customer_idJOIN order_items oi ON o.order_id = oi.order_idJOIN 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_codeFROM contacts;

日期函数：

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

聚合函数：

SELECT MAX(salary) AS max_salary, MIN(age) AS min_age, SUM(revenue) AS total_revenueFROM 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 ASSELECT 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