SQL Cheat Sheet
SQL Cheat Sheet
查找数据的查询
SELECT: 用于从数据库中选择数据
SELECT
*FROM
table_name;
DISTINCT: 用于过滤掉重复的值并返回指定列的行
SELECT DISTINCT
column_name;
WHERE: 用于过滤记录/行
SELECT
column1, column2FROM
table_nameWHERE
condition;SELECT
*FROM
table_nameWHERE
condition1AND
condition2;SELECT
*FROM
table_nameWHERE
condition1OR
condition2;SELECT
*FROM
table_nameWHERE NOT
condition;SELECT
*FROM
table_nameWHERE
condition1AND
(condition2OR
condition3);SELECT
*FROM
table_nameWHERE EXISTS
(SELECT
column_nameFROM
table_nameWHERE
condition);
ORDER BY: 用于结果集的排序,升序(ASC)或者降序(DESC)
SELECT
*FROM
table_nameORDER BY
column;SELECT
*FROM
table_nameORDER BY
columnDESC
;SELECT
*FROM
table_nameORDER BY
column1ASC
, column2DESC
;
SELECT TOP: 用于指定从表顶部返回的记录数
SELECT TOP
number columns_namesFROM
table_nameWHERE
condition;SELECT TOP
percent columns_namesFROM
table_nameWHERE
condition;- 并非所有数据库系统都支持
SELECT TOP
。 MySQL 中是LIMIT
子句 SELECT
column_namesFROM
table_nameLIMIT
offset, count;
LIKE: 用于搜索列中的特定模式,WHERE 子句中使用的运算符
- % (percent sign) 是一个表示零个,一个或多个字符的通配符
- _ (underscore) 是一个表示单个字符通配符
SELECT
column_namesFROM
table_nameWHERE
column_nameLIKE
pattern;LIKE
‘a%’ (查找任何以“a”开头的值)LIKE
‘%a’ (查找任何以“a”结尾的值)LIKE
‘%or%’ (查找任何包含“or”的值)LIKE
‘_r%’ (查找任何第二位是“r”的值)LIKE
‘a*%*%’ (查找任何以“a”开头且长度至少为 3 的值)LIKE
‘[a-c]%’(查找任何以“a”或“b”或“c”开头的值)
IN: 用于在 WHERE 子句中指定多个值的运算符
- 本质上,IN 运算符是多个 OR 条件的简写
SELECT
column_namesFROM
table_nameWHERE
column_nameIN
(value1, value2, …);SELECT
column_namesFROM
table_nameWHERE
column_nameIN
(SELECT STATEMENT
);
BETWEEN: 用于过滤给定范围的值的运算符
SELECT
column_namesFROM
table_nameWHERE
column_nameBETWEEN
value1AND
value2;SELECT
*FROM
ProductsWHERE
(column_nameBETWEEN
value1AND
value2)AND NOT
column_name2IN
(value3, value4);SELECT
*FROM
ProductsWHERE
column_nameBETWEEN
#01/07/1999# AND #03/12/1999#;
NULL: 代表一个字段没有值
SELECT
*FROM
table_nameWHERE
column_nameIS NULL
;SELECT
*FROM
table_nameWHERE
column_nameIS NOT NULL
;
AS: 用于给表或者列分配别名
SELECT
column_nameAS
alias_nameFROM
table_name;SELECT
column_nameFROM
table_nameAS
alias_name;SELECT
column_nameAS
alias_name1, column_name2AS
alias_name2;SELECT
column_name1, column_name2 + ‘, ‘ + column_name3AS
alias_name;
UNION: 用于组合两个或者多个 SELECT 语句的结果集的运算符
- 每个 SELECT 语句必须拥有相同的列数
- 列必须拥有相似的数据类型
- 每个 SELECT 语句中的列也必须具有相同的顺序
SELECT
columns_namesFROM
table1UNION SELECT
column_nameFROM
table2;UNION
仅允许选择不同的值,UNION ALL
允许重复
ANY|ALL: 用于检查 WHERE 或 HAVING 子句中使用的子查询条件的运算符
ANY
如果任何子查询值满足条件,则返回 true。ALL
如果所有子查询值都满足条件,则返回 true。SELECT
columns_namesFROM
table1WHERE
column_name operator (ANY
|ALL
) (SELECT
column_nameFROM
table_nameWHERE
condition);
GROUP BY: 通常与聚合函数(COUNT,MAX,MIN,SUM,AVG)一起使用,用于将结果集分组为一列或多列
SELECT
column_name1, COUNT(column_name2)FROM
table_nameWHERE
conditionGROUP BY
column_name1ORDER BY
COUNT(column_name2) DESC;
HAVING: HAVING 子句指定 SELECT 语句应仅返回聚合值满足指定条件的行。它被添加到 SQL 语言中,因为 WHERE 关键字不能与聚合函数一起使用。
SELECT
COUNT
(column_name1), column_name2FROM
tableGROUP BY
column_name2HAVING
COUNT(
column_name1)
> 5;
修改数据的查询
INSERT INTO: 用于在表中插入新记录/行
INSERT INTO
table_name (column1, column2)VALUES
(value1, value2);INSERT INTO
table_nameVALUES
(value1, value2 …);
UPDATE: 用于修改表中的现有记录/行
UPDATE
table_nameSET
column1 = value1, column2 = value2WHERE
condition;UPDATE
table_nameSET
column_name = value;
DELETE: 用于删除表中的现有记录/行
DELETE FROM
table_nameWHERE
condition;DELETE
*FROM
table_name;
聚合查询
COUNT: 返回出现次数
SELECT COUNT (DISTINCT
column_name)
;
MIN() and MAX(): 返回所选列的最小/最大值
SELECT MIN (
column_names) FROM
table_nameWHERE
condition;SELECT MAX (
column_names) FROM
table_nameWHERE
condition;
AVG(): 返回数字列的平均值
SELECT AVG (
column_name) FROM
table_nameWHERE
condition;
SUM(): 返回数值列的总和
SELECT SUM (
column_name) FROM
table_nameWHERE
condition;
连接查询
INNER JOIN: 内连接,返回在两张表中具有匹配值的记录
SELECT
column_namesFROM
table1INNER JOIN
table2ON
table1.column_name=table2.column_name;SELECT
table1.column_name1, table2.column_name2, table3.column_name3FROM
((table1INNER JOIN
table2ON
relationship)INNER JOIN
table3ON
relationship);
LEFT (OUTER) JOIN: 左外连接,返回左表(table1)中的所有记录,以及右表中的匹配记录(table2)
SELECT
column_namesFROM
table1LEFT JOIN
table2ON
table1.column_name=table2.column_name;
RIGHT (OUTER) JOIN: 右外连接,返回右表(table2)中的所有记录,以及左表(table1)中匹配的记录
SELECT
column_namesFROM
table1RIGHT JOIN
table2ON
table1.column_name=table2.column_name;
FULL (OUTER) JOIN: 全外连接,全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 以 NULL 填充。
SELECT
column_namesFROM
table1FULL OUTER JOIN
table2ON
table1.column_name=table2.column_name;
Self JOIN: 自连接,表自身连接
SELECT
column_namesFROM
table1 T1, table1 T2WHERE
condition;
视图查询
CREATE: 创建视图
CREATE VIEW
view_nameAS SELECT
column1, column2FROM
table_nameWHERE
condition;
SELECT: 检索视图
SELECT
*FROM
view_name;
DROP: 删除视图
DROP VIEW
view_name;
修改表的查询
ADD: 添加字段
ALTER TABLE
table_nameADD
column_name column_definition;
MODIFY: 修改字段数据类型
ALTER TABLE
table_nameMODIFY
column_name column_type;
DROP: 删除字段
ALTER TABLE
table_nameDROP COLUMN
column_name;