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,);
评论