sql 语法

Select
SELECT “栏位” FROM “表格名”;

Distinct
SELECT DISTINCT “栏位”
FROM “表格名”;

Where
SELECT “栏位”
FROM “表格名”
WHERE “条件”;
在where中条件等号是“=”，而不是“==”，即 where a=5

And/Or
SELECT “栏位”
FROM “表格名”
WHERE “简单条件”
{[AND|OR] “简单条件”}+;

In
SELECT “栏位”
FROM “表格名”
WHERE “栏位” IN (‘值1’, ‘值2’, …);

Between
SELECT “栏位”
FROM “表格名”
WHERE “栏位” BETWEEN ‘值1’ AND ‘值2’;

Like
SELECT “栏位”
FROM “表格名”
WHERE “栏位” LIKE {模式};
例：
{套式} 经常包括野卡 (wildcard). 以下是几个例子：

• ‘A_Z’: 所有以 ‘A’ 起头，另一个任何值的字原，且以 ‘Z’ 为结尾的字串。 ‘ABZ’ 和 ‘A2Z’ 都符合这一个模式，而’AKKZ’ 并不符合 (因为在 A 和 Z 之间有两个字原，而不是一个字原)。
• ‘ABC%’: 所有以 ‘ABC’ 起头的字串。举例来说，’ABCD’ 和 ‘ABCABC’ 都符合这个套式。
• ‘%XYZ’: 所有以 ‘XYZ’ 结尾的字串。举例来说，’WXYZ’ 和 ‘ZZXYZ’ 都符合这个套式。
• ‘%AN%’: 所有含有 ‘AN’ 这个套式的字串。举例来说， ‘LOS ANGELES’ 和 ‘SAN FRANCISCO’
  都符合这个套式。

Order By
SELECT “栏位”
FROM “表格名”
[WHERE “条件”]
ORDER BY “栏位” [ASC, DESC];
ASC 代表结果会以由小往大的顺序列出，而 DESC 代表结果会以由大往小的顺序列出。如果两者皆没有被写出的话，那我们就会用 ASC。

Count
SELECT COUNT(“栏位”)
FROM “表格名”;

Group By -> GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT “栏位1”, SUM(“栏位2”)
FROM “表格名”
GROUP BY “栏位1”;
例：根据顾客去计算订单价格的和
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

Having ->where无法与合计函数一起使用，单HAVING可以
SELECT “栏位1”, SUM(“栏位2”)
FROM “表格名”
GROUP BY “栏位1”
HAVING (栏位);
例：我们希望查找订单总金额少于 2000 的客户。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

Create Table
CREATE TABLE “表格名”
(“栏位 1” “栏位 1 资料种类”,
“栏位 2” “栏位 2 资料种类”,
… );
例：CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);

Drop Table
DROP TABLE “表格名”;

Truncate Table ->清除表格中所有行
TRUNCATE TABLE “表格名”;

Insert Into
INSERT INTO “表格名” (“栏位1”, “栏位2”, …)
VALUES (“值1”, “值2”, …);

Update
UPDATE “表格名”
SET “栏位1” = [新值]
WHERE “条件”;

Delete From
DELETE FROM “表格名”
WHERE “条件”;
** as ** 别名
SELECT A.id
FROM A_TABLE as A , B_TABLE as B
WHERE A.id == B.ID