在数据库管理与开发中，SQL（Structured Query Language）作为一门用于管理和操作关系型数据库的语言，是每一位开发者和数据库管理员不可或缺的工具。掌握一些经典的SQL语句不仅能够提高工作效率，还能帮助我们更好地理解和优化数据库的运行逻辑。本文将整理一系列常用且实用的经典SQL语句，以供参考。
数据库查询基础
1. SELECT语句
SELECT语句是最常用的SQL命令之一，用于从数据库中检索数据。
```sql
SELECT column1, column2 FROM table_name WHERE condition;
```
示例：
```sql
SELECT name, age FROM users WHERE active = 1;
```
2. ORDER BY排序
ORDER BY关键字用于对结果集进行排序。
```sql
SELECT FROM table_name ORDER BY column_name ASC/DESC;
```
示例：
```sql
SELECT FROM products ORDER BY price DESC;
```
3. DISTINCT去重
DISTINCT关键字用于返回唯一不同的值。
```sql
SELECT DISTINCT column_name FROM table_name;
```
示例：
```sql
SELECT DISTINCT category FROM products;
```
数据更新与删除
4. UPDATE语句
UPDATE语句用于修改表中的现有记录。
```sql
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
```
示例：
```sql
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
```
5. DELETE语句
DELETE语句用于删除表中的记录。
```sql
DELETE FROM table_name WHERE condition;
```
示例：
```sql
DELETE FROM logs WHERE created_at < '2023-01-01';
```
聚合函数与分组
6. COUNT统计行数
COUNT函数用于计算符合条件的行数。
```sql
SELECT COUNT() FROM table_name WHERE condition;
```
示例：
```sql
SELECT COUNT() FROM orders WHERE status = 'completed';
```
7. GROUP BY分组
GROUP BY关键字用于将数据分组并应用聚合函数。
```sql
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;
```
示例：
```sql
SELECT category, SUM(price) FROM products GROUP BY category;
```
8. HAVING过滤分组结果
HAVING子句用于过滤GROUP BY后的结果。
```sql
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;
```
示例：
```sql
SELECT category, COUNT() FROM products GROUP BY category HAVING COUNT() > 10;
```
多表联结
9. INNER JOIN内联结
INNER JOIN用于返回两个表中匹配的记录。
```sql
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
```
示例：
```sql
SELECT u.name, o.order_date FROM users u INNER JOIN orders o ON u.id = o.user_id;
```
10. LEFT JOIN左联结
LEFT JOIN用于返回左表中的所有记录以及右表中匹配的记录。
```sql
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
```
示例：
```sql
SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
```
其他高级技巧
11. UNION合并结果集
UNION用于合并两个或多个SELECT语句的结果集。
```sql
SELECT column_name FROM table1 UNION SELECT column_name FROM table2;
```
示例：
```sql
SELECT product_name FROM products UNION SELECT category FROM categories;
```
12. LIMIT限制结果数量
LIMIT关键字用于限制返回的记录数量。
```sql
SELECT column_name FROM table_name LIMIT number OFFSET offset;
```
示例：
```sql
SELECT FROM users LIMIT 10 OFFSET 20;
```
结语
以上列举的SQL语句涵盖了数据库操作的各个方面，从基本的数据查询到复杂的多表联结和聚合运算。熟练掌握这些经典语句，不仅能提升你的SQL技能，还能为你的项目开发提供坚实的基础。希望本文能对你有所帮助！
