SQL Cheat Sheet

基础查询

• 选择所有列：SELECT * FROM table_name;
• 选择特定列：SELECT column1, column2 FROM table_name;
• 去重记录：SELECT DISTINCT column1 FROM table_name;

条件查询

• 基本条件：SELECT * FROM table_name WHERE condition;
• 逻辑运算符：AND, OR, NOT
  • SELECT * FROM table_name WHERE condition1 AND condition2;
• 范围查询：BETWEEN, IN
  • SELECT * FROM table_name WHERE column1 BETWEEN value1 AND value2;
  • SELECT * FROM table_name WHERE column1 IN (value1, value2, value3);
• 模式匹配：LIKE
  • SELECT * FROM table_name WHERE column1 LIKE 'pattern';

聚合函数

• 计数：COUNT()
  • SELECT COUNT(column1) FROM table_name;
• 求和：SUM()
  • SELECT SUM(column1) FROM table_name;
• 平均值：AVG()
  • SELECT AVG(column1) FROM table_name;
• 最大值：MAX()
  • SELECT MAX(column1) FROM table_name;
• 最小值：MIN()
  • SELECT MIN(column1) FROM table_name;

分组与排序

• 分组：GROUP BY
  • SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
• 过滤分组：HAVING
  • SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;
• 排序：ORDER BY
  • SELECT * FROM table_name ORDER BY column1 ASC;
  • SELECT * FROM table_name ORDER BY column1 DESC;

联接（JOIN）

• 内联接：INNER JOIN
  • SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
• 左联接：LEFT JOIN
  • SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
• 右联接：RIGHT JOIN
  • SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
• 全联接：FULL OUTER JOIN
  • SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

子查询

• 嵌套查询：SELECT * FROM table_name WHERE column1 = (SELECT column1 FROM table_name2 WHERE condition);

数据操作

• 插入数据：INSERT INTO
  • INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• 更新数据：UPDATE
  • UPDATE table_name SET column1 = value1 WHERE condition;
• 删除数据：DELETE
  • DELETE FROM table_name WHERE condition;

创建和修改表

• 创建表：CREATE TABLE
  • CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
• 修改表：ALTER TABLE
  • ALTER TABLE table_name ADD column_name datatype;
  • ALTER TABLE table_name DROP COLUMN column_name;
• 删除表：DROP TABLE
  • DROP TABLE table_name;