Oracle、MySQL 和 PostgreSQL 中通用的100条 SQL 语法

第一部分

1. 创建数据库：CREATE DATABASE dbname;
2. 删除数据库：DROP DATABASE dbname;
3. 创建表：CREATE TABLE tablename (column1 datatype, column2 datatype, ...);
4. 删除表：DROP TABLE tablename;
5. 插入记录：INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
6. 更新记录：UPDATE tablename SET column1 = value1, column2 = value2, ... WHERE condition;
7. 删除记录：DELETE FROM tablename WHERE condition;
8. 选择记录：SELECT column1, column2, ... FROM tablename WHERE condition;
9. 选择所有记录：SELECT * FROM tablename;
10. 统计记录数：SELECT COUNT(*) FROM tablename;

11. 求和：SELECT SUM(column) FROM tablename;
12. 求平均值：SELECT AVG(column) FROM tablename;
13. 求最大值：SELECT MAX(column) FROM tablename;
14. 求最小值：SELECT MIN(column) FROM tablename;
15. 筛选唯一值：SELECT DISTINCT column FROM tablename;
16. 连接表：SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;
17. 左连接：SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
18. 右连接：SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
19. 内连接：SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
20. 自连接：SELECT * FROM table1 t1 JOIN table1 t2 ON t1.column = t2.column;

21. 条件运算符：AND、OR、NOT；
22. 比较运算符：=、<>, <, >, <=, >=；
23. 模糊匹配：LIKE、NOT LIKE；
24. 区间查询：BETWEEN x AND y；
25. 空值判断：IS NULL、IS NOT NULL；
26. 正则表达式：REGEXP_LIKE；
27. 分组统计：SELECT column, COUNT(*) FROM tablename GROUP BY column;
28. 分组筛选：SELECT column, COUNT(*) FROM tablename GROUP BY column HAVING COUNT(*) > 1;
29. 排序：SELECT * FROM tablename ORDER BY column ASC|DESC;
30. 限制结果集：SELECT * FROM tablename LIMIT n;

31. 偏移结果集：SELECT * FROM tablename OFFSET n;
32. 子查询：SELECT column FROM tablename WHERE column IN (SELECT column FROM tablename2 WHERE condition);
33. EXISTS 子查询：SELECT column FROM tablename WHERE EXISTS (SELECT column FROM tablename2 WHERE condition);
34. ANY 子查询：SELECT column FROM tablename WHERE column > ANY (SELECT column FROM tablename2 WHERE condition);
35. ALL 子查询：SELECT column FROM tablename WHERE column > ALL (SELECT column FROM tablename2 WHERE condition);
36. CASE 表达式：SELECT column1, CASE WHEN condition THEN value1 ELSE value2 END AS column2 FROM tablename;
37. 聚合函数：AVG、COUNT、MAX、MIN、SUM；
38. 窗口函数：RANK、DENSE_RANK、ROW_NUMBER、LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTILE、PERCENT_RANK；
39. GROUPING 函数：SELECT column1, column2, GROUPING(column1) FROM tablename GROUP BY ROLLUP

40. UNION 运算符：SELECT column1 FROM tablename1 UNION SELECT column1 FROM tablename2;
41. INTERSECT 运算符：SELECT column1 FROM tablename1 INTERSECT SELECT column1 FROM tablename2;
42. EXCEPT 运算符：SELECT column1 FROM tablename1 EXCEPT SELECT column1 FROM tablename2;
43. WITH 子句：WITH cte_name (column1, column2, ...) AS (SELECT column1, column2, ... FROM tablename) SELECT * FROM cte_name;
44. 联合查询：SELECT column1 FROM tablename1 UNION ALL SELECT column1 FROM tablename2;
45. EXISTS 关键字：SELECT column1 FROM tablename1 WHERE EXISTS (SELECT column1 FROM tablename2 WHERE condition);
46. 插入多条记录：INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...;
47. 创建索引：CREATE INDEX idx_name ON tablename (column1, column2, ...);
48. 删除索引：DROP INDEX idx_name;
49. 修改表结构：ALTER TABLE tablename ADD column datatype, ALTER TABLE tablename DROP column;
50. 创建视图：CREATE VIEW viewname AS SELECT column1, column2, ... FROM tablename WHERE condition;

第二部分

1. 创建用户：CREATE USER username IDENTIFIED BY password;
2. 授权：GRANT privileges ON object TO user;
3. 撤销权限：REVOKE privileges ON object FROM user;
4. 修改密码：ALTER USER username IDENTIFIED BY password;
5. 删除用户：DROP USER username;
6. 外键约束：ALTER TABLE tablename ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES tablename2 (column);
7. 唯一约束：ALTER TABLE tablename ADD CONSTRAINT uk_name UNIQUE (column);
8. 主键约束：ALTER TABLE tablename ADD CONSTRAINT pk_name PRIMARY KEY (column);
9. 默认值约束：ALTER TABLE tablename ALTER COLUMN column SET DEFAULT value;
10. 删除约束：ALTER TABLE tablename DROP CONSTRAINT constraint_name;

11. 事务处理：BEGIN; SQL statements; COMMIT; or BEGIN; SQL statements; ROLLBACK;
12. 触发器：CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON tablename FOR EACH ROW SQL statements;
13. 删除触发器：DROP TRIGGER trigger_name;
14. 时间戳：SELECT CURRENT_TIMESTAMP;
15. 格式化日期：SELECT TO_CHAR(date, 'format') FROM tablename;
16. 日期运算：SELECT ADD_MONTHS(date, n) FROM tablename;
17. 联合查询中表别名：SELECT column1 FROM tablename1 AS t1 JOIN tablename2 AS t2 ON t1.column = t2.column;
18. 分页：SELECT * FROM tablename OFFSET n ROWS FETCH NEXT m ROWS ONLY;
19. 并行查询：SELECT /*+PARALLEL(tablename, degree)*/ column1 FROM tablename;
20. 字符串连接：SELECT CONCAT(string1, string2) FROM tablename;

21. 字符串长度：SELECT LENGTH(column) FROM tablename;
22. 字符串替换：SELECT REPLACE(column, oldstring, newstring) FROM tablename;
23. 大小写转换：SELECT UPPER(column), LOWER(column) FROM tablename;
24. 数学运算：SELECT column1 + column2, column1 - column2, column1 * column2, column1 / column2 FROM tablename;
25. 取整函数：CEIL, FLOOR, ROUND；
26. 空值处理函数：COALESCE, NVL, IFNULL；
27. 时间运算：DATEDIFF, DATEADD；
28. 正则表达式替换：REGEXP_REPLACE；
29. 集合函数：GROUP_CONCAT；
30. 窗口函数：OVER(PARTITION BY column ORDER BY column);

31. 分析函数：RANK(), DENSE_RANK(), ROW_NUMBER(), LAG(), LEAD();
32. 嵌套函数：SELECT AVG(SUM(column)) FROM tablename GROUP BY column;
33. 查询时间范围：SELECT * FROM tablename WHERE date_column BETWEEN start_date AND end_date;
34. IN 子查询：SELECT * FROM tablename WHERE column IN (SELECT column FROM tablename2 WHERE condition);
35. EXISTS 子查询：SELECT * FROM tablename WHERE EXISTS (SELECT column FROM tablename2 WHERE condition);
36. COUNT(*) 与 COUNT(column)：SELECT COUNT(*) FROM tablename 和 SELECT COUNT(column) FROM tablename;
37. 对查询结果排序：SELECT column1 FROM tablename ORDER BY column1 DESC, column2 ASC;
38. 分组计算平均值：SELECT column1, AVG(column2) FROM tablename GROUP BY column1;

39. 分组计算数量：SELECT column1, COUNT(*) FROM tablename GROUP BY column1;
40. 分组计算最大值和最小值：SELECT column1, MAX(column2), MIN(column2) FROM tablename GROUP BY column1;
41. HAVING 子句：SELECT column1, AVG(column2) FROM tablename GROUP BY column1 HAVING AVG(column2) > value;
42. 自连接：SELECT t1.column1, t2.column2 FROM tablename AS t1 JOIN tablename AS t2 ON t1.column = t2.column;
43. 子查询作为列：SELECT column1, (SELECT column2 FROM tablename WHERE condition) FROM tablename2;
44. 子查询作为表：SELECT * FROM (SELECT column1 FROM tablename WHERE condition) AS t1;
45. CASE 表达式：SELECT column1, CASE WHEN column2 = value1 THEN result1 WHEN column2 = value2 THEN result2 ELSE result3 END AS new_column FROM tablename;
46. NULL 值处理：SELECT COALESCE(column1, column2) FROM tablename;
47. 连接多个表：SELECT column1 FROM tablename1 JOIN tablename2 ON condition1 JOIN tablename3 ON condition2;
48. 自增长字段：SELECT * FROM tablename WHERE column1 = LAST_INSERT_ID();
49. 子查询中的聚合函数：SELECT column1, (SELECT AVG(column2) FROM tablename2 WHERE tablename2.column1 = tablename.column1) FROM tablename;
50. 取反操作符：SELECT column1 FROM tablename WHERE NOT condition;

注意：

尽管这些语法可以在 Oracle、MySQL 和 PostgreSQL 中使用，但在某些情况下会有细微差异。因此，在编写 SQL 查询时，最好参考相应的文档以确保查询在特定数据库中正常工作。