SQL:查询学习笔记
SQL 查询命令
SELECT
语法
SELECT "column_name"
FROM "table_name";
返回一列
1 SELECT Username FROM Users
返回多列
1 SELECT Username, Password From Users
返回所有列
1 SELECT * FROM Users
DISTINCT
语法
SELECT DISTINCT "column_name"
FROM "table_name";
示例
1 SELECT DISTINCT Name FROM Users
WHERE
语法
SELECT "column_name"
FROM "table_name"
WHERE "condition";
示例
1 SELECT * FROM Users WHERE Username = ‘HappyFramework’
AND OR
语法
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{ [AND|OR] "simple condition"}+;
AND 示例
1 SELECT * FROM Orders Where Sales >= 1000 AND Sales <= 5000
OR 示例
1 SELECT * FROM Orders Where Sales < 1000 OR Sales > 5000
IN
语法
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...);
示例
1 SELECT * FROM Users WHERE Username IN (Happyframework, shijiucha)
BETWEEN
语法
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';
示例
1 SELECT * FROM Orders WHERE Sales BETWEEN 1000 AND 5000
LIKE
语法
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};
% 示例
1 SELECT * FROM Users WHERE Name LIKE '%光'
_ 示例
1 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]}+;
示例
1 SELECT * FROM Users ORDER BY Username ASC
带公式的示例
1 SELECT * FROM Orders ORDER BY Receipt - Refund
AVG
语法
SELECT AVG("column_name")
FROM "table_name";
示例
1 SELECT AVG(Sales) FROM Sales
COUNT
语法
SELECT COUNT("column_name")
FROM "table_name";
示例
1 SELECT COUNT(DISTINCT *) FROM Sales
MAX
语法
SELECT MAX ("column_name")
FROM "table_name";
示例
1 SELECT MAX(Sales) FROM Sales
MIN
语法
SELECT MIN ("column_name")
FROM "table_name";
示例
1 SELECT MIN(Sales) FROM Sales
SUM
语法
SELECT SUM ("column_name")
FROM "table_name";
示例
1 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";
示例
1 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);
示例
1 SELECT Customer, SUM(Sales) 2 FROM Orders 3 GROUP BY Customer 4 HAVING SUM(Sales) > 10000
ALIAS
语法
SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias";
示例
1 SELECT U.Username 用户名 FROM Users U
AS
语法
SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias";
示例
1 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";
示例
1 SELECT Categories.Name, News.Content 2 FROM News 3 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";
示例
1 SELECT Categories.Name, News.Content 2 FROM News 3 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";
示例
1 SELECT Categories.Name, News.Content 2 FROM News 3 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";
示例
1 SELECT Categories.Name, News.Content 2 FROM News 3 RIGHT OUTER JOIN Categories ON News.CategoryId = Categories.Id
CROSS JOIN
语法
SELECT "column_name"
FROM "left_table_name", "right_table_name";
示例
1 SELECT Categories.Name, News.Content 2 FROM News, Categories
UNION
语法
[SQL Statement 1]
UNION
[SQL Statement 2];
示例
1 SELECT Name FROM Users 2 UNION 3 SELECT * FROM Employees
UNION ALL
语法
[SQL Statement 1]
UNION ALL
[SQL Statement 2];
示例
1 SELECT Name FROM Users 2 UNION ALL 3 SELECT * FROM Employees
INLINE VIEW
语法
SELECT "column_name" FROM (Inline View);
示例
1 SELECT * FROM 2 (SELECT * FROM Users)
INTERSECT
语法
[SQL Statement 1]
INTERSECT
[SQL Statement 2];
示例
1 SELECT Txn_Date FROM Store_Information 2 INTERSECT 3 SELECT Txn_Date FROM Internet_Sales;
MINUS
语法
[SQL Statement 1]
INTERSECT
[SQL Statement 2];
示例
1 SELECT Txn_Date FROM Store_Information 2 MINUS 3 SELECT Txn_Date FROM Internet_Sales;
TOP
语法
SELECT TOP N [PERCENT] "column_name"
FROM "table_name";
示例
1 SELECT TOP 2 Store_Name, Sales, Txn_Date 2 FROM Store_Information 3 ORDER BY Sales DESC;
百分比示例
1 SELECT TOP 25 PERCENT Store_Name, Sales, Txn_Date 2 FROM Store_Information 3 ORDER BY Sales DESC;
SUBQUERY
语法
1 SELECT "column_name1" 2 FROM "table_name1" 3 WHERE "column_name2" [Comparison Operator] 4 (SELECT "column_name3" 5 FROM "table_name2" 6 WHERE "condition");
示例
1 SELECT SUM (Sales) FROM Store_Information 2 WHERE Store_Name IN 3 (SELECT Store_Name FROM Geography 4 WHERE Region_Name = 'West');
1 SELECT SUM (a1.Sales) FROM Store_Information a1 2 WHERE a1.Store_Name IN 3 (SELECT Store_Name FROM Geography a2 4 WHERE a2.Store_Name = a1.Store_Name);
EXISTS
语法
SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
(SELECT *
FROM "table_name2"
WHERE "condition");
示例
1 SELECT * 2 FROM Users 3 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";
示例
1 SELECT Username, CASE (UserType) 2 WHEN 1 THEN "管理员" 3 WHEN 2 THEN "员工" 4 ELSE "未知" 5 FROM Users
SEARCHED CASE
语法
SELECT CASE
WHEN "condition1" THEN "result1"
WHEN "condition2" THEN "result2"
...
[ELSE "resultN"]
END
FROM "table_name";
示例
1 SELECT Username, CASE 2 WHEN UserType = 1 THEN "管理员" 3 WHEN UserType = 2 THEN "员工" 4 ELSE "未知" 5 FROM Users