一、CREATE VIEW
视观表 (View) 可以被当作是虚拟表格。它跟表格的不同是,表格中有实际储存资料,而视观表是建立在表格之上的一个架构,它本身并不实际储存资料。建立一个视观表的语法如下:
CREATE VIEW "VIEW_NAME" AS "SQL 语句";
其中"SQL 语句" 可以是任何一个我们在这个教材中有提到的 SQL。来看一个例子。假设我们有以下的表格(表名=Customer):
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Address | char(50) |
City | char(50) |
Country | char(25) |
Birth_Date | datetime |
CREATE VIEW V_Customer AS SELECT First_Name, Last_Name, Country FROM Customer;
这样现在,我们就有一个叫做 V_Customer 的视观表:
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Country | char(25) |
2. 我们也可以用视观表来连接两个表格。在这个情况下,使用者就可以直接由一个视观表中找出她要的资讯,而不需要由两个不同的表格中去做一次连接的动作。假设有以下的两个表格:
Table1表格
Store_Name | Sales | Txn_Date |
Los Angeles | 1500 | 05-Jan-1999 |
San Diego | 250 | 07-Jan-1999 |
Los Angeles | 300 | 08-Jan-1999 |
Boston | 700 | 08-Jan-1999 |
Table2表格
Region_Name | Store_Name |
East | Boston |
East | New York |
West | Los Angeles |
West | San Diego |
CREATE VIEW V_REGION_SALES
AS SELECT Table2.Region_Name AS REGION, SUM(A2.Sales) AS SALES
FROM Table1, Table2
WHERE Table2.Store_Name = Table1.Store_Name
GROUP BY Table2.Region_Name;
这样我们就有一个名为 V_REGION_SALES 的视观表。这个视观表包含不同地区的销售哦。如果我们要从这个视观表中获取资料,我们就打入:
SELECT * FROM V_REGION_SALES;
结果显示如下:
REGION | SALES |
East | 700 |
West | 2050 |
当我们选不只一个栏位,且其中至少一个栏位有包含函数的运用时,我们就需要用到 GROUP BY 这个指令。在这个情况下,我们需要确定我们有 GROUP BY 所有其他的栏位;换句话说,除了有包括函数的栏位外,我们都需要将其放在 GROUP BY 的子句中。
还是以上面的表格为例:
Store_Name | Sales | Txn_Date |
Los Angeles | 1500 | 05-Jan-1999 |
San Diego | 250 | 07-Jan-1999 |
Los Angeles | 300 | 08-Jan-1999 |
Boston | 700 | 08-Jan-1999 |
如果我们的需求变成是要算出每一间店 (Store_Name) 的营业额 (Sales),那怎么办呢?在这个情况下,我们要做到两件事:第一,我们对于 Store_Name 及 Sales 这两个栏位都要选出。第二,我们需要确认所有的 Sales 都要依照各个 Store_Name 来分开算。这个语法为:
SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1";
我们输入:
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;
结果为:
Store_Name | SUM(Sales) |
Los Angeles | 1800 |
San Diego | 250 |
Boston | 700 |
三、
LEFT/RIGHT OUTER JOIN
当需要对两个表格进行操作时,经常会用到“join”命令,将两个表格链接起来。一般语法如下:
select * from table1
left outer join table2
on table1.field1 = table2.field1
where table1.field2 = "5801"
1. A left outer join B
是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例如: table1.field1 = table2.field1).B表记录不足的地方均为NULL.
2. A right outer join B
2. A right outer join B
和left outer join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3. A inner join B
3. A inner join B
很明显,这里只显示A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
(注:left/right join是left/right outer join的简写,二者一样;但是没有left/right inner join这种语法)
用法举例:
(注:left/right join是left/right outer join的简写,二者一样;但是没有left/right inner join这种语法)
用法举例:
tableA:
Field_K | Field_A |
1 | a |
3 | b |
4 | c |
Field_K | Field_A |
1 | x |
2 | y |
4 | z |
select tableA.Field_K, tableA.Field_A, tableB.Field_K, tableB.Field_B from tableA left join tableB on tableA.Field_K= tableB.Field_K
结果为:
Field_K Field_A Field_K Field_B
------- -------- -------- ----------
1 a 1 x
3 b NULL NULL
4 c 4 z
Field_K Field_A Field_K Field_B
------- ------- ------- -------
1 a 1 x
NULL NULL 2 y
4 c 4 z
------- -------- -------- ----------
1 a 1 x
3 b NULL NULL
4 c 4 z
select tableA.Field_K, tableA.Field_A, tableB.Field_K, tableB.Field_B from tableA right join tableB on tableA.Field_K=tableB.Field_K
结果为:
Field_K Field_A Field_K Field_B
------- ------- ------- -------
1 a 1 x
NULL NULL 2 y
4 c 4 z
四、CREATE INDEX
索引 (Index) 可以帮助我们从表格中快速地找到需要的资料。如果一个表格没有索引的话,资料库系统就需要将整个表格的资料读出 (这个过程叫做'table scan')。若有适当的索引存在,资料库系统就可以先由这个索引去找出需要的资料是在表格的什么地方,然后直接去那些地方抓资料。这样子速度就快多了。因此,在表格上建立索引是一件有利于系统效率的事。一个索引可以涵盖一或多个栏位。建立索引的语法如下:
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME);
现在假设我们有以下这个Customer表格:
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Address | char(50) |
City | char(50) |
Country | char(25) |
Birth_Date | datetime |
CREATE INDEX IDX_CUSTOMER_LAST_NAME ON Customer (Last_Name);
也可以为City 和 Country两栏建立索引
CREATE INDEX IDX_CUSTOMER_LOCATION ON Customer (City, Country);
索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首,例如 "IDX_" ,来避免与资料库中的其他物件混淆。注意,每个资料库会有它本身的CREATE INDEX语法,而不同资料库的语法会有不同。
更多SQL语法知识,请参考
http://www.1keydata.com/cn/sql/sql-groupby.php