100条常用SQL语句

以下是整理后的100条常用SQL语句，按功能分类并补充了典型场景示例，便于快速查阅与实践：

• 全表查询SELECT * FROM employees;
• 指定列查询SELECT name, salary FROM employees;
• 条件查询SELECT * FROM employees WHERE department = 'IT';
• 模糊查询SELECT * FROM products WHERE name LIKE 'Laptop%';
• 排序查询SELECT * FROM orders ORDER BY order_date DESC;
• 限制行数SELECT * FROM logs LIMIT 100;
• 去重查询SELECT DISTINCT city FROM customers;
• 多条件组合SELECT * FROM employees WHERE salary > 5000 AND department = 'HR';
• 范围查询SELECT * FROM products WHERE price BETWEEN 100 AND 500;
• 空值判断SELECT * FROM users WHERE email IS NULL;
• 非空值判断SELECT * FROM users WHERE phone IS NOT NULL;
• 分页查询SELECT * FROM articles ORDER BY publish_date LIMIT 10 OFFSET 20;
• 字段别名SELECT name AS employee_name FROM employees;
• 表别名SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id;
• 常量列SELECT name, 'Active' AS status FROM users;

• 计数SELECT COUNT(*) FROM orders;
• 分组统计SELECT department, COUNT(*) FROM employees GROUP BY department;
• 条件分组SELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id HAVING SUM(quantity) > 10;
• 总和计算SELECT SUM(salary) FROM employees;
• 平均值SELECT AVG(rating) FROM reviews;
• 最大值SELECT MAX(price) FROM products;
• 最小值SELECT MIN(stock) FROM inventory;
• 多聚合函数SELECT MIN(price), MAX(price), AVG(price) FROM products;
• 分组拼接字符串SELECT department, GROUP_CONCAT(name) FROM employees GROUP BY department;
• 唯一值计数SELECT COUNT(DISTINCT city) FROM customers;

• 插入单行INSERT INTO customers (name, email) VALUES ('John', 'john@example.com');
• 插入多行INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20);
• 更新数据UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
• 条件更新UPDATE orders SET status = 'Shipped' WHERE order_date < '2023-01-01';
• 删除数据DELETE FROM temp_users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
• 批量删除DELETE FROM logs WHERE level = 'DEBUG';
• 复制表数据INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < '2022-01-01';
• 条件插入INSERT INTO vip_customers SELECT * FROM customers WHERE total_purchases > 1000;
• 更新或插入（UPSERT）INSERT INTO settings (key, value) VALUES ('theme', 'dark') ON DUPLICATE KEY UPDATE value = 'dark';
• 清空表TRUNCATE TABLE temp_data;

• 创建表CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE);
• 删除表DROP TABLE old_logs;
• 添加列ALTER TABLE products ADD COLUMN description TEXT;
• 修改列类型ALTER TABLE orders MODIFY COLUMN order_date DATETIME;
• 删除列ALTER TABLE customers DROP COLUMN phone;
• 重命名表ALTER TABLE customer_backup RENAME TO archive_customers;
• 添加约束ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id);
• 删除约束ALTER TABLE employees DROP FOREIGN KEY fk_dept;
• 创建自增列ALTER TABLE transactions ADD COLUMN transaction_id INT AUTO_INCREMENT PRIMARY KEY;
• 复制表结构CREATE TABLE new_products LIKE products;

• 创建索引CREATE INDEX idx_email ON users(email);
• 复合索引CREATE INDEX idx_name_age ON customers(last_name, age);
• 删除索引DROP INDEX idx_email ON users;
• 创建视图CREATE VIEW active_users AS SELECT * FROM users WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);
• 更新视图CREATE OR REPLACE VIEW vip_customers AS SELECT * FROM customers WHERE tier = 'Gold';
• 删除视图DROP VIEW active_users;
• 索引视图（部分数据库支持）CREATE MATERIALIZED VIEW sales_summary ASSELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id;
• 检查视图定义SHOW CREATE VIEW active_users;

• 内连接SELECT o.order_id, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id;
• 左连接SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
• 右连接SELECT p.name, s.name FROM products p RIGHT JOIN suppliers s ON p.supplier_id = s.id;
• 全连接（MySQL需用UNION模拟）SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.idUNIONSELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id WHERE table1.id IS NULL;
• 交叉连接SELECT * FROM colors CROSS JOIN sizes;
• 自然连接SELECT * FROM employees NATURAL JOIN departments;
• 自连接SELECT e1.name AS employee, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;
• 多表连接SELECT o.order_id, c.name, p.name FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id;

• IN子查询SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 5);
• EXISTS子查询SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);
• NOT EXISTS子查询SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
• 相关子查询SELECT name, (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS avg_dept_salary FROM employees e1;
• UNION合并结果SELECT name FROM customers WHERE country = 'US' UNION SELECT name FROM suppliers WHERE country = 'US';
• UNION ALL保留重复SELECT product_id FROM order_items_2022 UNION ALL SELECT product_id FROM order_items_2023;
• INTERSECT交集（MySQL需用INNER JOIN模拟）SELECT product_id FROM order_items_2022 o1INNER JOIN order_items_2023 o2 ON o1.product_id = o2.product_id;
• EXCEPT差集（MySQL需用LEFT JOIN模拟）SELECT product_id FROM order_items_2022 o1LEFT JOIN order_items_2023 o2 ON o1.product_id = o2.product_idWHERE o2.product_id IS NULL;
• 子查询作为派生表SELECT d.name, dept_counts.count FROM departments d JOIN (SELECT dept_id, COUNT(*) AS count FROM employees GROUP BY dept_id) dept_counts ON d.id = dept_counts.dept_id;
• CTE（公用表表达式）WITH dept_stats AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id)SELECT d.name, ds.avg_salary FROM departments d JOIN dept_stats ds ON d.id = ds.dept_id;

• 当前时间SELECT NOW();
• 当前日期SELECT CURDATE();
• 日期加法SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS expected_ship_date FROM orders;
• 日期减法SELECT DATEDIFF(NOW(), hire_date) AS days_employed FROM employees;
• 格式化日期SELECT DATE_FORMAT(order_date, '%Y-%m-%d %H:%i') FROM orders;
• 提取日期部分SELECT YEAR(order_date), MONTH(order_date) FROM orders GROUP BY YEAR(order_date), MONTH(order_date);
• 时间戳转换SELECT FROM_UNIXTIME(1672531200) AS human_readable_date;
• 时区转换SELECT CONVERT_TZ(order_date, '+00:00', '+08:00') AS beijing_time FROM orders;

• 字符串连接SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
• 字符串长度SELECT LENGTH(password) FROM users;
• 子字符串提取SELECT SUBSTRING(product_code, 1, 3) AS category_code FROM products;
• 字符串替换SELECT REPLACE(description, 'old', 'new') FROM products;
• 大小写转换SELECT UPPER(country), LOWER(city) FROM customers;
• 去除空格SELECT TRIM(BOTH FROM phone) FROM customers;
• 字符串比较SELECT * FROM products WHERE STRCMP(name, 'Laptop') = 0;
• 正则匹配SELECT * FROM emails WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';

• CASE条件判断SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS gradeFROM students;
• IF函数SELECT name, IF(is_active, 'Active', 'Inactive') AS status FROM users;
• COALESCE处理NULLSELECT name, COALESCE(phone, 'N/A') AS contact FROM customers;
• NULLIF避免除零错误SELECT amount / NULLIF(quantity, 0) AS unit_price FROM order_items;
• ROLLUP分组汇总SELECT region, city, SUM(sales) FROM sales_data GROUP BY region, city WITH ROLLUP;
• 窗口函数（排名）SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;
• 窗口函数（累计和）SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue FROM daily_sales;
• JSON处理（MySQL 5.7+）SELECT JSON_EXTRACT(user_data, '$.address.city') AS city FROM users;
• 存储过程CREATE PROCEDURE update_salaries(IN dept_id INT, IN percentage DECIMAL(5,2))BEGIN UPDATE employees SET salary = salary * (1 + percentage/100) WHERE department_id = dept_id;END;
• 触发器CREATE TRIGGER log_employee_updateBEFORE UPDATE ON employeesFOR EACH ROWINSERT INTO employee_audit (employee_id, old_salary, new_salary, change_date)VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

• EXPLAIN分析查询EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
• 强制使用索引SELECT * FROM products FORCE INDEX (idx_category) WHERE category_id = 5;
• 临时表优化CREATE TEMPORARY TABLE temp_results AS SELECT * FROM large_table WHERE condition;
• 查询缓存控制SET SESSION query_cache_type = OFF;
• 慢查询日志SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;

• 显示数据库版本SELECT VERSION();
• 显示当前用户SELECT CURRENT_USER();
• 显示所有数据库SHOW DATABASES;
• 显示表结构DESCRIBE employees;
• 显示进程列表SHOW PROCESSLIST;
• 显示表状态SHOW TABLE STATUS LIKE 'orders%';
• 显示索引信息SHOW INDEX FROM customers;
• 显示变量设置SHOW VARIABLES LIKE 'max_connections';

• 数据库备份mysqldump -u root -p mydatabase > backup.sql
• 恢复数据库mysql -u root -p mydatabase < backup.sql
• 备份特定表mysqldump -u root -p mydatabase customers orders > tables_backup.sql
• 压缩备份mysqldump -u root -p mydatabase | gzip > backup.sql.gz

• 创建用户CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
• 授予权限GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';
• 撤销权限REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';
• 刷新权限FLUSH PRIVILEGES;

以上语句覆盖了SQL开发的核心场景，可根据实际数据库类型（MySQL、PostgreSQL、SQL Server等）调整语法细节。建议结合具体业务需求进行实践验证。