SQL 语法面试备忘录，建议收藏！
来 源：数据STUDIO/作 者：云朵君
在数据科学这个越来越卷的行当，找工作面试必然难以驾驭。而它的多学科领域性质决定了你需要翻阅大量材料才能感觉准备充分，而这很可能会让你不知所措，无从下手。
在这里，小编总结了MySQL的大多数查询语法，并将其写成备忘录的形式，希望这可以帮助小伙伴轻松应对数据科学面试。这里强烈建议你收藏，在面试前可以快速找出以临时抱佛脚。
目录
查找数据查询
修改数据查询
报告查询
表连接查询
视图查询
修改表查询
创建表查询
查找数据查询
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,);
-END-
扫描/识别下方二维码
回复【S4】即可免费领取SQL面试题库
