SQL 語法面試備忘錄，建議收藏！
來? 源：數(shù)據(jù)STUDIO/作? 者：云朵君
在數(shù)據(jù)科學(xué)這個越來越卷的行當(dāng)，找工作面試必然難以駕馭。而它的多學(xué)科領(lǐng)域性質(zhì)決定了你需要翻閱大量材料才能感覺準(zhǔn)備充分，而這很可能會讓你不知所措，無從下手。
在這里，小編總結(jié)了MySQL的大多數(shù)查詢語法，并將其寫成備忘錄的形式，希望這可以幫助小伙伴輕松應(yīng)對數(shù)據(jù)科學(xué)面試。這里強烈建議你收藏，在面試前可以快速找出以臨時抱佛腳。
目錄
查找數(shù)據(jù)查詢
修改數(shù)據(jù)查詢
報告查詢
表連接查詢
視圖查詢
修改表查詢
創(chuàng)建表查詢
查找數(shù)據(jù)查詢
SELECT
用于從數(shù)據(jù)庫中選擇數(shù)據(jù)
SELECT * FROM table_name;
DISTINCT
過濾掉重復(fù)值并返回指定列的行
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
用于按升序或降序?qū)Y(jié)果集進行排序
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
用于指定從表頂返回的記錄數(shù)
SELECT TOP number columns_names
FROM table_name WHERE condition;
SELECT TOP percent columns_names
FROM table_name WHERE condition;
不是所有的數(shù)據(jù)庫系統(tǒng)都支持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”結(jié)尾的值）
LIKE?'%or%'（查找任何位置有“or”的值）
LIKE?'[ac]%'（查找以“a”、“b”或“c”開頭的任何值）
IN
允許您在 WHERE 子句中指定多個值的運算符
本質(zhì)上，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
運算符選擇給定范圍內(nèi)的值
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 語句的結(jié)果集
UNION 中的每個 SELECT 語句必須具有相同的列數(shù)
列必須具有相似的數(shù)據(jù)類型
每個 SELECT 語句中的列也必須按相同順序排列
UNION運算符只選擇不同的值，UNION ALL將允許重復(fù)
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
經(jīng)常與聚合函數(shù)（COUNT、MAX、MIN、SUM、AVG）一起使用的語句，用于按一列或多列對結(jié)果集進行分組
SELECT column_name1, COUNT(column_name2)
FROM table_name
WHERE condition
GROUP BY column_name1
ORDER BY COUNT(column_name2) DESC;
HAVING
這個子句被添加到 SQL 中，因為 WHERE 關(guān)鍵字不能與聚合函數(shù)一起使用
SELECT COUNT(column_name1), column_name2
FROM table
GROUP BY column_name2
HAVING COUNT(column_name1) > 10;
數(shù)據(jù)修改查詢
INSERT INTO
用于在表中插入新記錄/行
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name VALUES (value1, value2 …);
UPDATE
用于修改表中現(xiàn)有記錄
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
UPDATE table_name SET column_name = value;
DELETE
用于刪除表中現(xiàn)有的記錄/行
DELETE FROM table_name WHERE condition;
DELETE * FROM table_name;
報告查詢
COUNT
返回出現(xiàn)次數(shù)
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()
返回數(shù)字列的平均值
SELECT AVG (column_name)
FROM table_name WHERE condition;
SUM()
返回數(shù)字列的總和
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：創(chuàng)建視圖
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：更改列的數(shù)據(jù)類型
ALTER TABLE table_name MODIFY column_name column_type;
DROP：刪除一列
ALTER TABLE table_name DROP COLUMN column_name;
創(chuàng)建表查詢
CREATE：創(chuàng)建一個表
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype,);
-END-
掃描/識別下方二維碼
回復(fù)【S4】即可免費領(lǐng)取SQL面試題庫
