SQL 語法面試備忘錄，建議收藏！

快速查看

◎ 查找數(shù)據(jù)查詢
◎ 修改數(shù)據(jù)查詢
◎ 報(bào)告查詢
◎ 表連接查詢
◎ 視圖查詢
◎ 修改表查詢
◎ 創(chuàng)建表查詢

查找數(shù)據(jù)查詢

SELECT

用于從數(shù)據(jù)庫(kù)中選擇數(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é)果集進(jìn)行排序

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ù)庫(kù)系統(tǒng)都支持SELECT TOP。與MySQL等價(jià)的是LIMIT子句

SELECT column_names 
FROM table_name LIMIT offset, count;

LIKE

在 WHERE 子句中用于搜索列中特定模式的運(yùn)算符

◎ %（百分號(hào)）是代表零、一個(gè)或多個(gè)字符的通配符
◎ _（下劃線）是代表單個(gè)字符的通配符

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 子句中指定多個(gè)值的運(yùn)算符

◎ 本質(zhì)上，IN 運(yùn)算符是多個(gè) OR 條件的簡(jiǎn)寫

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

運(yùn)算符選擇給定范圍內(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

別名用于為表或列分配臨時(shí)名稱

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

集合運(yùn)算符用于組合兩個(gè)或多個(gè) SELECT 語句的結(jié)果集

◎ UNION 中的每個(gè) SELECT 語句必須具有相同的列數(shù)
◎ 列必須具有相似的數(shù)據(jù)類型
◎ 每個(gè) SELECT 語句中的列也必須按相同順序排列
◎?UNION運(yùn)算符只選擇不同的值，UNION ALL將允許重復(fù)

SELECT columns_names FROM table1 
UNION 
SELECT column_name FROM table2;

INTERSECT

集合運(yùn)算符，用于返回兩個(gè) SELECT 語句共有的記錄

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

SELECT columns_names FROM table1 
INTERSECT 
SELECT column_name FROM table2;

EXCEPT

集合運(yùn)算符用于返回第一個(gè) SELECT 語句中第二個(gè) SELECT 語句中未找到的所有記錄

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

SELECT columns_names FROM table1 
EXCEPT 
SELECT column_name FROM table2;

ANY|ALL

用于檢查 WHERE 或 HAVING 子句中使用的子查詢條件的運(yùn)算符

◎ 該ANY如有子查詢值滿足條件運(yùn)算符返回true
◎ 該ALL如果所有子查詢值滿足條件運(yùn)算符返回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）一起使用的語句，用于按一列或多列對(duì)結(jié)果集進(jìn)行分組

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

HAVING

這個(gè)子句被添加到 SQL 中，因?yàn)?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;

報(bào)告查詢

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()

返回?cái)?shù)字列的平均值

SELECT AVG (column_name) 
FROM table_name WHERE condition;

SUM()

返回?cái)?shù)字列的總和

SELECT SUM (column_name) 
FROM table_name WHERE condition;

表連接查詢

INNER JOIN

返回在兩個(gè)表中具有匹配值的記錄

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

在左表或右表中匹配時(shí)返回所有記錄

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)建一個(gè)表

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