SQL 语法面试备忘录，建议收藏！

快速查看

◎ 查找数据查询
◎ 修改数据查询
◎ 报告查询
◎ 表连接查询
◎ 视图查询
◎ 修改表查询
◎ 创建表查询

查找数据查询

SELECT

用于从数据库中选择数据

SELECT * FROM table_name;

DISTINCT

过滤掉重复值并返回指定列的行

SELECT DISTINCT column_name;

WHERE

用于过滤记录/行

SELECT column1, column2 FROM table_name WHERE condition;
SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM table_name WHERE NOT condition;
SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);
SELECT * FROM table_name WHERE EXISTS 
	(SELECT column_name FROM table_name WHERE condition);

ORDER BY

用于按升序或降序对结果集进行排序

SELECT * FROM table_name ORDER BY column;
SELECT * FROM table_name ORDER BY column DESC;
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

SELECT TOP

用于指定从表顶返回的记录数

SELECT TOP number columns_names 
FROM table_name WHERE condition;

SELECT TOP percent columns_names 
FROM table_name WHERE condition;

不是所有的数据库系统都支持SELECT TOP。与MySQL等价的是LIMIT子句

SELECT column_names 
FROM table_name LIMIT offset, count;

LIKE

在 WHERE 子句中用于搜索列中特定模式的运算符

◎ %（百分号）是代表零、一个或多个字符的通配符
◎ _（下划线）是代表单个字符的通配符

SELECT column_names 
FROM table_name 
WHERE column_name 
LIKE pattern;

LIKE 'a%'（查找任何以“a”开头的值）
LIKE '%a'（查找任何以“a”结尾的值）
LIKE '%or%'（查找任何位置有“or”的值）
LIKE '[ac]%'（查找以“a”、“b”或“c”开头的任何值）

IN

允许您在 WHERE 子句中指定多个值的运算符

◎ 本质上，IN 运算符是多个 OR 条件的简写

SELECT column_names 
FROM table_name 
WHERE column_name IN (value1, value2, …);

SELECT column_names 
FROM table_name 
WHERE column_name IN (SELECT STATEMENT);

BETWEEN

运算符选择给定范围内的值

SELECT column_names 
FROM table_name 
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products 
WHERE (column_name BETWEEN value1 AND value2) 
AND NOT column_name2 IN (value3, value4);

SELECT * FROM Products
WHERE column_name 
BETWEEN #01/07/1999# AND #03/12/1999# ;

NULL

字段中没有值的值

SELECT * FROM table_name 
WHERE column_name IS NULL;
		
SELECT * FROM table_name 
WHERE column_name IS NOT NULL;

AS

别名用于为表或列分配临时名称

SELECT column_name AS alias_name FROM table_name;
SELECT column_name FROM table_name AS alias_name;
SELECT column_name AS alias_name1, column_name2 AS alias_name2;
SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;

UNION

集合运算符用于组合两个或多个 SELECT 语句的结果集

◎ UNION 中的每个 SELECT 语句必须具有相同的列数
◎ 列必须具有相似的数据类型
◎ 每个 SELECT 语句中的列也必须按相同顺序排列
◎ UNION运算符只选择不同的值，UNION ALL将允许重复

SELECT columns_names FROM table1 
UNION 
SELECT column_name FROM table2;

INTERSECT

集合运算符，用于返回两个 SELECT 语句共有的记录

◎ 一般使用和上面UNION一样的方式

SELECT columns_names FROM table1 
INTERSECT 
SELECT column_name FROM table2;

EXCEPT

集合运算符用于返回第一个 SELECT 语句中第二个 SELECT 语句中未找到的所有记录

◎ 一般使用和上面UNION一样的方式

SELECT columns_names FROM table1 
EXCEPT 
SELECT column_name FROM table2;

ANY|ALL

用于检查 WHERE 或 HAVING 子句中使用的子查询条件的运算符

◎ 该ANY如有子查询值满足条件运算符返回true
◎ 该ALL如果所有子查询值满足条件运算符返回true

SELECT columns_names 
FROM table1 
WHERE column_name operator 
(ANY|ALL) 
(SELECT column_name 
 		FROM table_name 
 		WHERE condition);

GROUP BY

经常与聚合函数（COUNT、MAX、MIN、SUM、AVG）一起使用的语句，用于按一列或多列对结果集进行分组

SELECT column_name1, COUNT(column_name2) 
FROM table_name 
WHERE condition 
GROUP BY column_name1 
ORDER BY COUNT(column_name2) DESC;

HAVING

这个子句被添加到 SQL 中，因为 WHERE 关键字不能与聚合函数一起使用

SELECT COUNT(column_name1), column_name2 
FROM table 
GROUP BY column_name2 
HAVING COUNT(column_name1) > 10;

数据修改查询

INSERT INTO

用于在表中插入新记录/行

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name VALUES (value1, value2 …);

UPDATE

用于修改表中现有记录

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
UPDATE table_name SET column_name = value;

DELETE

用于删除表中现有的记录/行

DELETE FROM table_name WHERE condition;
DELETE * FROM table_name;

报告查询

COUNT

返回出现次数

SELECT COUNT (DISTINCT column_name);

MIN() 和 MAX()

返回所选列的最小/最大值

SELECT MIN (column_names) 
FROM table_name WHERE condition;

SELECT MAX (column_names) 
FROM table_name WHERE condition;

AVG()

返回数字列的平均值

SELECT AVG (column_name) 
FROM table_name WHERE condition;

SUM()

返回数字列的总和

SELECT SUM (column_name) 
FROM table_name WHERE condition;

表连接查询

INNER JOIN

返回在两个表中具有匹配值的记录

SELECT column_names FROM table1 
INNER JOIN table2 ON table1.column_name=table2.column_name;

SELECT table1.column_name1, table2.column_name2, table3.column_name3 
FROM ((table1 INNER JOIN table2 ON relationship) 
        INNER JOIN table3 ON relationship);

LEFT (OUTER) JOIN

返回左表（table1）中的所有记录，以及右表（table2）中匹配的记录

SELECT column_names FROM table1 
LEFT JOIN table2 ON table1.column_name=table2.column_name;

RIGHT (OUTER) JOIN

返回右表（table2）中的所有记录，以及左表（table1）中匹配的记录

SELECT column_names FROM table1 
RIGHT JOIN table2 ON table1.column_name=table2.column_name;

FULL (OUTER) JOIN

在左表或右表中匹配时返回所有记录

SELECT column_names FROM table1 
FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

自连接

普通连接，表与表自身的连接

SELECT column_names 
FROM table1 T1, table1 T2 
WHERE condition;

查看查询

CREATE：创建视图

CREATE VIEW view_name AS SELECT column1, column2 
FROM table_name WHERE condition;

SELECT：检索视图

SELECT * FROM view_name;

DROP：删除视图

DROP VIEW view_name;

修改表查询

ADD：添加一列

ALTER TABLE table_name ADD column_name column_definition;

MODIFY：更改列的数据类型

ALTER TABLE table_name MODIFY column_name column_type;

DROP：删除一列

ALTER TABLE table_name DROP COLUMN column_name;

创建表查询

CREATE：创建一个表

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype,);