当前位置： 首页 > news >正文

100条常用SQL语句大全

news 2025/7/27 12:01:53

SQL（结构化查询语言）是管理和操作关系型数据库的标准语言。无论是数据分析师、后端开发人员还是数据库管理员，掌握SQL都是必备技能。本文将为您整理100条最常用的SQL语句，涵盖查询、插入、更新、删除等各类操作。

基础查询语句

1. 查询表中所有数据
```sql
SELECT * FROM table_name;
```

2. 查询特定列
```sql
SELECT column1, column2 FROM table_name;
```

3. 带条件的查询
```sql
SELECT * FROM table_name WHERE condition;
```

4. 查询不重复的值
```sql
SELECT DISTINCT column_name FROM table_name;
```

5. 按列排序（升序）
```sql
SELECT * FROM table_name ORDER BY column_name;
```

6. 按列排序（降序）
```sql
SELECT * FROM table_name ORDER BY column_name DESC;
```

7. 多列排序
```sql
SELECT * FROM table_name ORDER BY column1, column2 DESC;
```

8. 限制返回行数
```sql
SELECT * FROM table_name LIMIT 10;
```

9. 分页查询
```sql
SELECT * FROM table_name LIMIT 10 OFFSET 20;
```

10. 使用别名
```sql
SELECT column_name AS alias_name FROM table_name;
```

## 条件查询与运算符

11. 等于条件
```sql
SELECT * FROM table_name WHERE column_name = value;
```

12. 不等于条件
```sql
SELECT * FROM table_name WHERE column_name != value;
```

13. 大于条件
```sql
SELECT * FROM table_name WHERE column_name > value;
```

14. 小于条件
```sql
SELECT * FROM table_name WHERE column_name < value;
```

15. 大于等于条件
```sql
SELECT * FROM table_name WHERE column_name >= value;
```

16. 小于等于条件
```sql
SELECT * FROM table_name WHERE column_name <= value;
```

17. BETWEEN范围查询
```sql
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
```

18. IN操作符
```sql
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
```

19. NOT IN操作符
```sql
SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, value3);
```

20. LIKE模糊查询
```sql
SELECT * FROM table_name WHERE column_name LIKE 'pattern%';
```

21. 多条件AND
```sql
SELECT * FROM table_name WHERE condition1 AND condition2;
```

22. 多条件OR
```sql
SELECT * FROM table_name WHERE condition1 OR condition2;
```

23. NOT条件
```sql
SELECT * FROM table_name WHERE NOT condition;
```

24. IS NULL空值查询
```sql
SELECT * FROM table_name WHERE column_name IS NULL;
```

25. IS NOT NULL非空查询
```sql
SELECT * FROM table_name WHERE column_name IS NOT NULL;
```

## 聚合函数与分组

26. COUNT计数
```sql
SELECT COUNT(*) FROM table_name;
```

27. 列计数
```sql
SELECT COUNT(column_name) FROM table_name;
```

28. SUM求和
```sql
SELECT SUM(column_name) FROM table_name;
```

29. AVG平均值
```sql
SELECT AVG(column_name) FROM table_name;
```

30. MAX最大值
```sql
SELECT MAX(column_name) FROM table_name;
```

31. MIN最小值
```sql
SELECT MIN(column_name) FROM table_name;
```

32. 分组统计
```sql
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
```

33. HAVING分组后筛选
```sql
SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 10;
```

34. 多列分组
```sql
SELECT column1, column2, AVG(column3)
FROM table_name
GROUP BY column1, column2;
```

35. 分组后排序
```sql
SELECT column_name, COUNT(*) AS count
FROM table_name
GROUP BY column_name
ORDER BY count DESC;
```

## 多表连接查询

36. 内连接
```sql
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
```

37. 左连接
```sql
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
```

38. 右连接
```sql
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
```

39. 全外连接
```sql
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
```

40. 交叉连接
```sql
SELECT * FROM table1 CROSS JOIN table2;
```

41. 自连接
```sql
SELECT a.column, b.column
FROM table_name a, table_name b
WHERE a.common_field = b.common_field;
```

42. 多表连接
```sql
SELECT * 
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;
```

43. 使用USING简化连接
```sql
SELECT * FROM table1 JOIN table2 USING (common_column);
```

44. 自然连接
```sql
SELECT * FROM table1 NATURAL JOIN table2;
```

45. 连接查询带条件
```sql
SELECT * 
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE table1.column = value;
```

## 子查询与集合操作

46. WHERE子句中的子查询
```sql
SELECT * FROM table1 WHERE column IN (SELECT column FROM table2);
```

47. FROM子句中的子查询
```sql
SELECT * FROM (SELECT column FROM table_name) AS subquery;
```

48. SELECT子句中的子查询
```sql
SELECT column1, (SELECT MAX(column2) FROM table2) FROM table1;
```

49. EXISTS子查询
```sql
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
```

50. NOT EXISTS子查询
```sql
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
```

51. 并集UNION
```sql
SELECT column FROM table1 UNION SELECT column FROM table2;
```

52. 保留重复的并集UNION ALL
```sql
SELECT column FROM table1 UNION ALL SELECT column FROM table2;
```

53. 交集INTERSECT
```sql
SELECT column FROM table1 INTERSECT SELECT column FROM table2;
```

54. 差集EXCEPT/MINUS
```sql
SELECT column FROM table1 EXCEPT SELECT column FROM table2;
```

55. ANY/SOME操作符
```sql
SELECT * FROM table1 WHERE column > ANY (SELECT column FROM table2);
```

56. ALL操作符
```sql
SELECT * FROM table1 WHERE column > ALL (SELECT column FROM table2);
```

数据操作语言(DML)

57. 插入单行数据
```sql
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
```

58. 插入多行数据
```sql
INSERT INTO table_name (column1, column2) 
VALUES (value1, value2), (value3, value4), (value5, value6);
```

59. 插入查询结果
```sql
INSERT INTO table_name (column1, column2)
SELECT column1, column2 FROM another_table;
```

60. 更新数据
```sql
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
```

61. 更新多表数据
```sql
UPDATE table1, table2 
SET table1.column = value1, table2.column = value2
WHERE table1.id = table2.id;
```

62. 删除数据
```sql
DELETE FROM table_name WHERE condition;
```

63. 清空表
```sql
TRUNCATE TABLE table_name;
```

64. 合并数据(MERGE/UPSERT)
```sql
INSERT INTO table_name (id, column1, column2)
VALUES (1, 'A', 'B')
ON DUPLICATE KEY UPDATE column1 = 'A', column2 = 'B';
```

65. 条件插入
```sql
INSERT INTO table_name (column1, column2)
SELECT value1, value2 FROM dual
WHERE NOT EXISTS (SELECT 1 FROM table_name WHERE condition);
```

数据定义语言(DDL)

66. 创建数据库
```sql
CREATE DATABASE db_name;
```

67. 创建表
```sql
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
```

68. 创建表带主键
```sql
CREATE TABLE table_name (
id INT PRIMARY KEY,
name VARCHAR(100)
);
```

69. 创建表带外键
```sql
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
```

70. 添加列
```sql
ALTER TABLE table_name ADD column_name datatype;
```

71. 删除列
```sql
ALTER TABLE table_name DROP COLUMN column_name;
```

72. 修改列数据类型
```sql
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
```

73. 添加主键
```sql
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
```

74. 添加外键
```sql
ALTER TABLE table1 ADD FOREIGN KEY (column_name) REFERENCES table2(column_name);
```

75. 创建索引
```sql
CREATE INDEX index_name ON table_name (column_name);
```

76. 创建唯一索引
```sql
CREATE UNIQUE INDEX index_name ON table_name (column_name);
```

77. 删除索引
```sql
DROP INDEX index_name ON table_name;
```

78. 重命名表
```sql
ALTER TABLE old_table RENAME TO new_table;
```

79. 删除表
```sql
DROP TABLE table_name;
```

80. 删除数据库
```sql
DROP DATABASE db_name;
```

视图与存储过程

81. 创建视图
```sql
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
```

82. 使用视图
```sql
SELECT * FROM view_name;
```

83. 修改视图
```sql
ALTER VIEW view_name AS SELECT column1, column2 FROM table_name WHERE new_condition;
```

84. 删除视图
```sql
DROP VIEW view_name;
```

85. 创建存储过程
```sql
CREATE PROCEDURE procedure_name(parameters)
BEGIN
-- SQL语句
END;
```

86. 执行存储过程
```sql
CALL procedure_name(parameters);
```

87. 删除存储过程
```sql
DROP PROCEDURE procedure_name;
```

88. 创建函数
```sql
CREATE FUNCTION function_name(parameters) RETURNS datatype
BEGIN
-- 逻辑
RETURN value;
END;
```

89. 使用函数
```sql
SELECT function_name(parameters);
```

90. 删除函数
```sql
DROP FUNCTION function_name;
```

事务控制与高级特性

91. 开始事务
```sql
BEGIN TRANSACTION;
-- 或
START TRANSACTION;
```

92. 提交事务
```sql
COMMIT;
```

93. 回滚事务
```sql
ROLLBACK;
```

94. 设置保存点
```sql
SAVEPOINT savepoint_name;
```

95. 回滚到保存点
```sql
ROLLBACK TO savepoint_name;
```

96. 设置事务隔离级别
```sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

97. 递归查询(WITH RECURSIVE)
```sql
WITH RECURSIVE cte_name AS (
-- 基础查询
SELECT ... FROM ...
UNION ALL
-- 递归部分
SELECT ... FROM cte_name JOIN ...
)
SELECT * FROM cte_name;
```

98. 窗口函数
```sql
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table_name;
```

99. 公用表表达式(CTE)
```sql
WITH cte_name AS (
SELECT column1, column2 FROM table_name
)
SELECT * FROM cte_name;
```

100. 动态SQL执行
```sql
PREPARE stmt FROM 'SELECT * FROM table_name WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
```

结语

这100条SQL语句涵盖了数据库操作的方方面面，从基础查询到高级特性。掌握这些语句后，您将能够高效地处理大多数数据库操作任务。实际应用中，根据不同的数据库系统（MySQL、PostgreSQL、SQL Server、Oracle等），语法可能略有差异，但核心概念是相通的。

建议收藏本文作为SQL速查手册，在实际工作中遇到问题时快速查找解决方案。SQL技能的提升需要不断实践，建议在真实项目或练习环境中多使用这些语句，加深理解。

http://www.lryc.cn/news/599837.html

相关文章：

  • 用毫秒级视频回传打造稳定操控闭环之远程平衡控制系统技术实践
  • LE AUDIO CIS/BIS音频传输时延计算方法
  • 【神经网络概述】从感知机到深度神经网络（CNN RNN）
  • 博客多级评论展示功能实现
  • Class18卷积层的填充和步幅
  • 仙人掌cacti中的RCE案例
  • 【Python】一些PEP提案（四）：scandir、类型约束，异步asyncawait
  • win11 使用adb 获取安卓系统日志
  • 黑马点评01 - 项目介绍 短信登录
  • RAG、Function Call、MCP技术笔记
  • HTML+CSS+JS快速入门
  • Jenkins中HTML文件显示样式问题解决方案
  • uniapp使用css实现进度条带动画过渡效果
  • Elasticsearch-ik分析器
  • 轮盘赌算法
  • C语言————原码 补码 反码 （试图讲清楚版）
  • 多智能体(Multi-agent)策略模式：思维链CoT和ReAct
  • Ubuntu 环境下创建并启动一个 MediaMTX 的 systemd 服务
  • 电科金仓新一代数据库一体机：以 “云数据库 - AI 版” 破局 AI 时代，三骏守护定义行业新标杆
  • 项目管理进阶——解读软件项目管理-项目阶段复盘会（通用模板）【附全文阅读】
  • 文心4.5开源之路：从封闭到开放的力量
  • ARM-I2C硬实现
  • linux-开机启动流程
  • 编程语言Java——核心技术篇（三）异常处理详解
  • 将本地项目推送到远程github仓库
  • 学习游戏制作记录（克隆技能）7.25
  • C语言|指针的应用
  • Python 之 keyboard
  • 详解软件需求中的外部接口需求
  • 网络安全入门第一课：信息收集实战手册（3）