SQL语句大全100句
一、基础操作
1. 创建表:CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, ...);
2. 删除表:DROP TABLE table_name;
3. 插入数据:INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
4. 删除数据:DELETE FROM table_name WHERE condition;
5. 更新数据:UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
6. 查询数据:SELECT column1, column2, ... FROM table_name WHERE condition;
7. 查询所有数据:SELECT * FROM table_name;
8. 查询并显示列名:SELECT column_name FROM table_name;
9. 排序数据:SELECT column1, column2, ... FROM table_name ORDER BY column_name ASC/DESC;
10. 限制返回的行数:SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;
二、条件查询
11. 使用WHERE子句进行条件查询:SELECT column1, column2, ... FROM table_name WHERE condition;
12. 使用AND和OR运算符组合多个条件:SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 OR condition3;
13. 使用IN关键字匹配多个值:SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);
14. 使用BETWEEN运算符进行范围查询:SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;
15. 使用LIKE运算符进行模糊查询:SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
三、聚合函数
16. 求和函数:SELECT SUM(column_name) FROM table_name;
17. 平均函数:SELECT AVG(column_name) FROM table_name;
18. 计数函数:SELECT COUNT(column_name) FROM table_name;
19. 最大值函数:SELECT MAX(column_name) FROM table_name;
20. 最小值函数:SELECT MIN(column_name) FROM table_name;
21. 分组聚合:SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
四、连接查询
22. 内连接查询:SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
23. 左连接查询:SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
24. 右连接查询:SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
25. 全外连接查询:SELECT column1, column2, ... FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
五、子查询和视图
26. 子查询:SELECT column1, column2, ... FROM (SELECT column1, column2, ... FROM table_name WHERE condition) AS subquery;
27. 创建视图:CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
28. 更新视图:UPDATE view_name SET column1 = value1, column2 = value2 WHERE condition;
29. 删除视图:DROP VIEW view_name;
30. 使用视图进行查询:SELECT column1, column2, ... FROM view_name;