SQL：查询学习笔记

SQL 查询命令

SELECT

语法

SELECT "column_name"

FROM "table_name";

返回一列

SELECT Username FROM Users

返回多列

SELECT Username, Password From Users

返回所有列

SELECT * FROM Users

DISTINCT

语法

SELECT DISTINCT "column_name"

FROM "table_name";

示例

SELECT DISTINCT Name FROM Users

WHERE

语法

SELECT "column_name"
FROM "table_name"
WHERE "condition";

示例

SELECT * FROM Users WHERE Username = ‘HappyFramework’

AND OR

语法

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{ [AND|OR] "simple condition"}+;

AND 示例

SELECT * FROM Orders Where Sales >= 1000 AND Sales <= 5000

OR 示例

SELECT * FROM Orders Where Sales < 1000 OR Sales > 5000

IN

语法

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...);

示例

SELECT * FROM Users WHERE Username IN (Happyframework, shijiucha)

BETWEEN

语法

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';

示例

SELECT * FROM Orders WHERE Sales BETWEEN 1000 AND 5000

LIKE

语法

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};

% 示例

SELECT * FROM Users WHERE Name LIKE '%光'

_ 示例

SELECT * FROM Users WHERE Name LIKE '段光_'

ORDER BY

语法

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC] {, "column_name" [ASC, DESC]}+;

示例

SELECT * FROM Users ORDER BY Username ASC

带公式的示例

SELECT * FROM Orders ORDER BY Receipt - Refund

AVG

语法

SELECT AVG("column_name")
FROM "table_name";

示例

SELECT AVG(Sales) FROM Sales

COUNT

语法

SELECT COUNT("column_name")
FROM "table_name";

示例

SELECT COUNT(DISTINCT *) FROM Sales

MAX

语法

SELECT MAX ("column_name")
FROM "table_name";

示例

SELECT MAX(Sales) FROM Sales

MIN

语法

SELECT MIN ("column_name")
FROM "table_name";

示例

SELECT MIN(Sales) FROM Sales

SUM

语法

SELECT SUM ("column_name")
FROM "table_name";

示例

SELECT SUM(Sales) FROM Sales

GROUP BY

语法

SELECT "column_name1", "column_name2", ... "column_nameN", Function("column_nameN+1")
FROM "table_name"
GROUP BY "column_name1", "column_name2", ... "column_nameN";

示例

SELECT Customer, SUM(Sales) FROM Orders GROUP BY Customer

Having

语法

SELECT ["column_name1"], Function("column_name2")
FROM "table_name"
[GROUP BY "column_name1"]
HAVING (arithmetic function condition);

示例

SELECT Customer, SUM(Sales) 
FROM Orders 
GROUP BY Customer 
HAVING SUM(Sales) > 10000

ALIAS

语法

SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias";

示例

SELECT U.Username 用户名 FROM Users U

AS

语法

SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias";

示例

SELECT U.Username AS 用户名 FROM Users AS U

INNER JOIN

语法

SELECT "column_name"
FROM "left_table_name"
INNER JOIN "right_table_name" ON "left_table_name"."left_column" = "right_table_name"."right_column";

示例

SELECT Categories.Name, News.Content
FROM News
INNER JOIN Categories ON News.CategoryId = Categories.Id

OUTER JOIN

语法

SELECT "column_name"
FROM "left_table_name"
OUTER JOIN "right_table_name" ON "left_table_name"."left_column" = "right_table_name"."right_column";

示例

SELECT Categories.Name, News.Content
FROM News
OUTER JOIN Categories ON News.CategoryId = Categories.Id

LEFT OUTER JOIN

语法

SELECT "column_name"
FROM "left_table_name"
LEFT OUTER JOIN "right_table_name" ON "left_table_name"."left_column" = "right_table_name"."right_column";

示例

SELECT Categories.Name, News.Content
FROM News
LEFT OUTER JOIN Categories ON News.CategoryId = Categories.Id

RIGHT OUTER JOIN

语法

SELECT "column_name"
FROM "left_table_name"
RIGHT OUTER JOIN "right_table_name" ON "left_table_name"."left_column" = "right_table_name"."right_column";

示例

SELECT Categories.Name, News.Content
FROM News
RIGHT OUTER JOIN Categories ON News.CategoryId = Categories.Id

CROSS JOIN

语法

SELECT "column_name"
FROM "left_table_name", "right_table_name";

示例

SELECT Categories.Name, News.Content
FROM News, Categories

UNION

语法

[SQL Statement 1]
UNION
[SQL Statement 2];

示例

SELECT Name FROM Users
UNION
SELECT * FROM Employees

UNION ALL

语法

[SQL Statement 1]
UNION ALL
[SQL Statement 2];

示例

SELECT Name FROM Users
UNION ALL
SELECT * FROM Employees

INLINE VIEW

语法

SELECT "column_name" FROM (Inline View);

示例

SELECT * FROM
(SELECT * FROM Users)

INTERSECT

语法

[SQL Statement 1]
INTERSECT
[SQL Statement 2];

示例

SELECT Txn_Date FROM Store_Information
INTERSECT
SELECT Txn_Date FROM Internet_Sales;

MINUS

语法

[SQL Statement 1]
INTERSECT
[SQL Statement 2];

示例

SELECT Txn_Date FROM Store_Information
MINUS
SELECT Txn_Date FROM Internet_Sales;

TOP

语法

SELECT TOP N [PERCENT] "column_name"
FROM "table_name";

示例

SELECT TOP 2 Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;

百分比示例

SELECT TOP 25 PERCENT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;

SUBQUERY

语法

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE "condition");

示例

SELECT SUM (Sales) FROM Store_Information
WHERE Store_Name IN
(SELECT Store_Name FROM Geography
WHERE Region_Name = 'West');

SELECT SUM (a1.Sales) FROM Store_Information a1
WHERE a1.Store_Name IN
(SELECT Store_Name FROM Geography a2
WHERE a2.Store_Name = a1.Store_Name);

EXISTS

语法

SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
(SELECT *
FROM "table_name2"
WHERE "condition");

示例

SELECT * 
FROM Users
WHERE EXISTS (SELECT * FROM UserRoleRelations WHERE Users.Id = UserRoleRelations.UserId)

SIMPLE CASE

语法

SELECT CASE ("column_name")
WHEN "value1" THEN "result1"
WHEN "value2" THEN "result2"
...
[ELSE "resultN"]
END
FROM "table_name";

示例

SELECT Username, CASE (UserType)
WHEN 1 THEN "管理员"
WHEN 2 THEN "员工"
ELSE "未知"
FROM Users

SEARCHED CASE

语法

SELECT CASE
WHEN "condition1" THEN "result1"
WHEN "condition2" THEN "result2"
...
[ELSE "resultN"]
END
FROM "table_name";

示例

SELECT Username, CASE
WHEN UserType = 1 THEN "管理员"
WHEN UserType = 2 THEN "员工"
ELSE "未知"
FROM Users