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

100条常用SQL语句大全

news 2025/7/26 5:05:05

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.xdnf.cn/news/1181719.html

相关文章：

  • C++20协程异步
  • 论文Review Registration TEASER | TRO | MIT出品，点云配准经典BenchMark | 硬核的数学长文
  • 蜘蛛强引的原理与百度SEO的关系
  • Qt（资源库和按钮组）
  • SpringBoot+AI+Web3实战指南
  • pytest官方Tutorial所有示例详解（一）
  • 洛谷刷题7.24
  • 优选算法：移动零
  • 计算机网络知识点总结 （2）
  • go语言基础教程：1. Go 下载安装和设置
  • Java网络编程入门：从基础原理到实践（二）
  • 算法第三十八天：动态规划part06（第九章）
  • uboot FPGA调试环境搭建
  • io_uring：Linux异步I/O的革命性突破
  • 星慈光编程虫2号小车讲解第四篇--触摸按键
  • 平时遇到的错误码及场景？404？400？502？都是什么场景下什么含义，该怎么做 ？
  • vue3核心语法
  • （进阶向）Python第十四期OpenCv图像预处理方法[2]
  • 跨境支付入门～国际支付结算（稳定币）
  • 深度分析Java多线程机制
  • AI实践：Pydantic
  • Spring之SSM整合流程详解（Spring+SpringMVC+MyBatis）
  • 【Linux】常用命令（一）
  • 洛谷P1512 伊甸园日历游戏
  • SQL基础⑫ | 视图篇
  • C++ - 仿 RabbitMQ 实现消息队列--服务端核心模块实现（三）
  • 基于深度学习的图像分类：使用MobileNet实现高效分类
  • Python进阶第三方库之Matplotlib
  • 深度学习（鱼书）day01--感知机
  • LeetCode 23：合并 K 个升序链表