100个SQL常用语句，建议收藏~

以下为100个常用SQL语句的分类整理，涵盖数据查询、插入、更新、删除、表操作、索引管理、视图操作、事务控制、权限管理等多个场景：

• 全表查询SELECT * FROM table_name;
• 指定列查询SELECT column1, column2 FROM table_name;
• 条件查询（WHERE）SELECT * FROM table_name WHERE condition;
• 模糊查询（LIKE）SELECT * FROM table_name WHERE column LIKE '%pattern%';
• 范围查询（BETWEEN）SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;
• 多条件组合（AND/OR）SELECT * FROM table_name WHERE condition1 AND condition2;
• 去重查询（DISTINCT）SELECT DISTINCT column FROM table_name;
• 排序查询（ORDER BY）SELECT * FROM table_name ORDER BY column ASC/DESC;
• 分组查询（GROUP BY）SELECT column, COUNT(*) FROM table_name GROUP BY column;
• 分组后筛选（HAVING）SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 1;

• 插入单行数据INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• 插入多行数据INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4);
• 从其他表插入数据INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM source_table;
• 更新数据（UPDATE）UPDATE table_name SET column1 = value1 WHERE condition;
• 批量更新UPDATE table_name SET column1 = CASE WHEN condition1 THEN value1 ELSE value2 END;
• 删除数据（DELETE）DELETE FROM table_name WHERE condition;
• 清空表（TRUNCATE）TRUNCATE TABLE table_name;

• 创建表CREATE TABLE table_name (column1 datatype, column2 datatype);
• 创建带约束的表CREATE TABLE table_name (column1 INT PRIMARY KEY, column2 VARCHAR(50) NOT NULL);
• 修改表结构（添加列）ALTER TABLE table_name ADD column_name datatype;
• 修改表结构（删除列）ALTER TABLE table_name DROP COLUMN column_name;
• 修改列数据类型ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
• 重命名表ALTER TABLE old_name RENAME TO new_name;
• 删除表DROP TABLE table_name;

• 创建索引CREATE INDEX index_name ON table_name (column_name);
• 创建唯一索引CREATE UNIQUE INDEX index_name ON table_name (column_name);
• 删除索引DROP INDEX index_name ON table_name;

• 创建视图CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;
• 修改视图CREATE OR REPLACE VIEW view_name AS SELECT * FROM new_table;
• 删除视图DROP VIEW view_name;

• 内连接（INNER JOIN）SELECT a.*, b.* FROM table1 a INNER JOIN table2 b ON a.id = b.id;
• 左外连接（LEFT JOIN）SELECT a.*, b.* FROM table1 a LEFT JOIN table2 b ON a.id = b.id;
• 右外连接（RIGHT JOIN）SELECT a.*, b.* FROM table1 a RIGHT JOIN table2 b ON a.id = b.id;
• 全外连接（FULL JOIN）SELECT a.*, b.* FROM table1 a FULL JOIN table2 b ON a.id = b.id;
• 交叉连接（CROSS JOIN）SELECT a.*, b.* FROM table1 a CROSS JOIN table2 b;
• 自连接（SELF JOIN）SELECT a.*, b.* FROM table a JOIN table b ON a.manager_id = b.id;

• WHERE子句中的子查询SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);
• FROM子句中的子查询SELECT a.* FROM (SELECT * FROM table1 WHERE condition) a;
• SELECT子句中的子查询SELECT column1, (SELECT COUNT(*) FROM table2 WHERE table2.id = table1.id) AS count FROM table1;

• 开启事务BEGIN TRANSACTION;
• 提交事务COMMIT;
• 回滚事务ROLLBACK;
• 设置保存点SAVEPOINT savepoint_name;
• 回滚到保存点ROLLBACK TO savepoint_name;

• 创建用户CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• 授予权限GRANT SELECT, INSERT ON database.table TO 'username'@'host';
• 撤销权限REVOKE SELECT, INSERT ON database.table FROM 'username'@'host';
• 删除用户DROP USER 'username'@'host';

• 分页查询（LIMIT/OFFSET）SELECT * FROM table_name LIMIT 10 OFFSET 20;
• 聚合函数（COUNT/SUM/AVG/MAX/MIN）SELECT COUNT(*), SUM(column1), AVG(column2) FROM table_name;
• 日期函数SELECT NOW(), CURDATE(), CURTIME();
• 字符串函数（CONCAT/SUBSTRING/TRIM）SELECT CONCAT(column1, column2), SUBSTRING(column1, 1, 5) FROM table_name;
• 条件表达式（CASE WHEN）SELECT column1, CASE WHEN column2 > 10 THEN 'High' ELSE 'Low' END FROM table_name;
• 存储过程CREATE PROCEDURE procedure_name() BEGIN SELECT * FROM table_name; END;
• 触发器CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW SET NEW.column1 = 'value';

• 创建数据库CREATE DATABASE database_name;
• 选择数据库USE database_name;
• 删除数据库DROP DATABASE database_name;
• 备份数据库（MySQL示例）mysqldump -u username -p database_name > backup.sql;
• 恢复数据库（MySQL示例）mysql -u username -p database_name < backup.sql;

• 分析查询执行计划EXPLAIN SELECT * FROM table_name WHERE condition;
• 强制使用索引SELECT * FROM table_name FORCE INDEX (index_name) WHERE condition;
• 优化表（MySQL）OPTIMIZE TABLE table_name;

• 空值处理（IS NULL/IS NOT NULL）SELECT * FROM table_name WHERE column1 IS NULL;
• 默认值处理（COALESCE）SELECT COALESCE(column1, 'default_value') FROM table_name;
• 生成序列（AUTO_INCREMENT/SEQUENCE）-- MySQLCREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY);-- OracleCREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
• 临时表CREATE TEMPORARY TABLE temp_table (column1 INT, column2 VARCHAR(50));

以上语句覆盖了SQL的核心功能，实际使用时需根据具体数据库（MySQL、Oracle、SQL Server等）调整语法细节。建议结合实际业务场景练习，逐步掌握复杂查询和优化技巧。