SQL语句大全 珍藏版2019-02-28

目 录 (01) SELECT .................................. ............... .......................................2 查找 SELECT "栏位名" FROM "表格名" (02) DISTINCT................................. ............... .....................................2 不同值 SELECT DISTINCT "栏位名" FROM "表格名" (03) WHERE...................................... ............... ....................................2 条件 SELECT "栏位名" FROM "表格名" WHERE "条件" (04) AND OR ...................................... ............... ..................................3 条件并和或 SELECT "栏位名" FROM "表格名" WHERE "简单条件" {[AND|OR] "简单条件"} (05) IN .............................................. ............... ..................................3 包含 SELECT "栏位名" FROM "表格名" WHERE "栏位名" IN ('值一', '值二 ', ...) (06) BETWEEN.............................. ............... ........................................4 范围包含 SELECT "栏位名" FROM " 表格名" WHERE "栏位名" BETWEEN '值一' AND '值二' (07) LIKE....................................... ............... .......................................4 通配符包含 SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {套 式} -- 支持通配符‘_’ 单个字符 '%' 任意字符 (08) ORDER BY............................... ............... ......................................5 排序 SELECT "栏位名" FROM "表格名" [WHERE "条件"] ORDER BY "栏位 名" [ASC, DESC] -- ASC 小到大 DESC 大到小 (09) 函数........................................ ............... ......................................5 函数 AVG (平均) COUNT (计数) MAX (最大值) MIN (最小值) SUM (总合) SELECT "函数名"("栏位名") FROM "表格名" (10) COUNT .................................... .............. ......................................6 计 数 SELECT COUNT(store_name) FROM Store_Information WHERE store_name is not NULL -- 统计非空 SELECT COUNT(DISTINCT store_name) FROM Store_Information -- 统计多 少个不同 (11) Group By .................................. .............. .....................................6 字段分组 SELECT "栏位 1", SUM("栏位 2") FROM "表格名" GROUP BY " 栏位 1" (12) HAVING...................................... .............. ....................................7 函数条件定位 SELECT "栏位 1", SUM("栏位 2") FROM "表格名" GROUP BY "栏位 1" HAVING (函数条件) (13) ALIAS........................................... .............. ..................................7 别名 SELECT "表格别名"."栏位 1" "栏位别名" FROM "表格名" "表格别名" (14) 连接................................................ ..............................................8 SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name (15) 外部连接........................................... ............................................9 SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name (16) Subquery .............................. .............. .........................................9 嵌套 SELECT "栏位 1" FROM "表格" WHERE "栏位 2" [比较运算素] (SELECT "栏位 1" FROM "表格" WHERE (17) UNION.................................... ............... ......................................10 合并不重复结果 [SQL 语句 1] UNION [SQL 语句 2] (18) UNION ALL....................................... .............. ............................ 11 合并所有结果 [SQL 语句 1] UNION ALL [SQL 语句 2] (19) INTERSECT..................................................... ............... ............. 11 查找相同值 [SQL 语句 1] INTERSECT [SQL 语句 2] (20) MINUS............................ ............... ..............................................12 显示第一个语句中不在第二个语句中的项 [SQL 语句 1] MINUS [SQL 语句 2] (21) Concatenate................................... ............... ...............................12 结果相加（串联） MySQL/Oracle: SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston'; SQL Server: SELECT region_name + ' ' + store_name FROM Geography WHERE store_name = 'Boston'; (22) Substring ...................................................... ............... ...............13 取字符 SUBSTR(str,pos) SUBSTR(str,pos,len) (23) TRIM ...... .............. .....................................................................14 去空 SELECT TRIM(' Sample '); TRIM()首尾, RTRIM()首, LTRIM()尾 (24) Create Table ........... .............. .....................................................14 建立表格 CREATE TABLE "表格名"("栏位 1" "栏位 1 资料种类","栏位 2" "栏位 2 资料种类",... ) (25) Create View............................. .............. ......................................15 建立表格视观表 CREATE VIEW "VIEW_NAME" AS "SQL 语句" (26) Create Index........................................... ............... ......................16 建立索引 CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME) (27) Alter Table.. .............. ..................................................................16 修改表 ALTER TABLE "table_name"[改变方式] -- ADD 增加;DROP 删 除;CHANGE 更名;MODIFY 更改类型 (28) 主键.......................... ..................................................................18 ALTER TABLE Customer ADD PRIMARY KEY (SID) (29) 外来主键....................................... ............ ..................................18 CREATE TABLE ORDERS(Order_ID integer,Order_Date date,Customer_SID integer,Amount double,Primary Key (Order_ID),Foreign Key (Customer_SID) references CUSTOMER(SID)); (30) Drop Table................................................. ............... ...................19 删除表 DROP TABLE "表格名" (31) Truncate Table ................. ............... ............................................20 清除表内容 TRUNCATE TABLE "表格名" (32) Insert Into....................................... ............... .............................20 插入内容 INSERT INTO "表格名" ("栏位 1", "栏位 2", ...) VALUES ("值 1", "值 2", ...) (33) Update ........................ ................ ................................................20 修改内容 UPDATE "表格名" SET "栏位 1" = [新值] WHERE {条件} (34) Delete ......................................... .............. .................................21 DELETE FROM "表格名" WHERE {条件}