SQL學習（一）

一  建表：

CREATE TABLE tablename(colname datatype [NOT NULL]

                                     {,colname datatype [NOT NULL]...}

                                     [,PRIMARY KEY (colname {,colname...})];

註： ｜,[] ,{}, ...在sql裡不會出現，所以用其標記

二    簡單的select語句：

select distinct ordno,x.cid,x.aid,x.pid,

                     .40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）as heji

                      from orders as x,customers as c,agents as a,products as p

                       where c.cid=x.cid and a.aid= x.aid and p.pid=x.aid;

x,c,a,p分別為orders,customers,agents,products的別名，關鍵字as可省略

ordno,cid, aid, pid ,heji為列名

heji 前面的as不可省略，他定義.40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）顯示為heji

distinct確保檢索後的每一行是唯一的

找出至少被兩個顧客訂購的產品的pid值

select distinct x1.pid

           from orders x1,orders x2

           where x1.pid=x2.pid and x1.pid<x2.pid;,

1.  IN謂詞

            IN謂詞的通用運算式：expr in (Subquery)｜expr in (val {,val...})

           例： 檢索由住在Duluth的顧客和住在New York的代理商組成的所有訂貨記錄的ordno值

                 select ordno from orders

                           where (cid,aid) in

                            (select cid,aid from customers c,agents a where c.city='Duluth' and a.city='New York');

     NOT IN謂詞為若且唯若求解出的expr值不在子查詢返回的集合中[expr NOT IN (Subquery)]

2. 量化比較謂詞

       通用形式 expr θ{SOME｜ANY｜ALL} (Subquery) where θ is some operator in the set {<,<=,=,<>,>,>=}

該形式中的SOME與ANY含義相同，θ包含：{<,<=,=,<>,>,>=}  <>是不等於

         例：找出傭金百分率最小的代理商的aid值

          select aid from agents where percent <=all (select percent from agents);

註：謂詞=SOME與謂詞IN具有完全相同的效果

      謂詞<>all與NOT IN等價

      謂詞<>SOME不與 NOT IN等價

3. 謂詞EXISTS

       通用形式 [NOT] EXISTS (Subquery)  註：EXISTS (Subquery) 為真若且唯若子查詢返回一個非空集合

          例：求出訂購了產品P01又訂購了產品P07的顧客的cid值

             select distinct cid from orders x

                       where pid='p01' and exists

                        (select * from orders where cid=x.cid and pid='p07');

       NOT EXISTS 查詢效果可用 NOT IN和等價謂詞<>ALL替代

4. SQL查詢會有很多等價形式

      例：檢索訂購了產品p01的顧客所在的city名這一請求的運算式，有四種主要的Select語句運算式

       select distinct city from customers where cid in

               (select cid from orders where pid='p01');

       select distinct city from customers where cid =any

               (select cid from orders where pid='p01');

       select distinct city from customers c where exists

                 (select * from oredrs o where c.cid=o.cid and o.pid='p01');

       select  distinct city from customers c, orders o

                 where c.cid=o.cid and o.pid='p01';

1. UNION 運算子

    實質就是關係運算中的並

   例：建立一個包含了顧客所在的或者代理商所在的或者兩者接在的城市的名單

          select city from customers

                   union select city from agents;

當設計到的子查詢項目大於或等於3時可用（）

      (select city from customers

                union select city from agents)

                union all select city from products;

2. 除法： SQL "FOR ALL..."條件

         如果我們所面臨的查詢要求被檢索的對象集合必須符合某個帶有“所有”這類關鍵詞的條件，我們就按照下列步驟進行

            1） 為要檢索的對象命名並考慮如何用英文來表述要檢索的候選對象的一個反例。在該反例中，前面提到的“所有”

                   對象中有至少一個對象不符合規定的條件。

            2） 建立Select語句的搜尋條件以選出步驟1所建立的所有反例。（步驟1和2必定會引用來自外部Select語句的對象，

                   所以我們要在如何用這些外部對象所在的表來引用他們這一問題上表現出一定的靈活性）

            3）  建立包含步驟2所建立的語句的搜尋條件，說明不存在上面定義的那種反例。這裡將涉及到NOT EXISTS謂詞

            4）  用步驟3的搜尋條件來建立最終的Select語句，檢索所期望的對象。

     上面的步驟序列通常會產生如下所示的嵌套子查詢對：

            select ... where not exists (select ... where  not exists (select ... ));

      例：找出具有下列性質的顧客的cid值：如果顧客c006訂購了某種產品，那要檢索的顧客也訂購了該產品。

          此題可以改寫成：找出訂購了所有被顧客c006訂購的產品的cid值。

         步驟1：我們稱c.cid是符合題意的顧客並用英文構造反例

                 "There is a product ordered by customer c006 that is not ordered by c.cid."

         步驟2：把英文表述成搜尋條件，我們將被c006訂購的產品命名為p.pid

                 cond1: p.pid in (select pid from orders x where x.cid='c006')

                                   and not exists (select * from orders y

                                           where y.pid=p.pid and y.cid=c.cid )

         步驟3：j建立表示這種反例不存在的條件：

                   cond2: not exists (select p.pid from prducts p

                                     where p.pid in (select pid from orders x

                                            where x.cid='c006')and

                                           not exists (select * from orders y

                                                 where y.pid=p.pid and y.cid=c.cid))

           步驟4：建立最終的Select

                           select cid from customers c

                                   where not exists (select p.pid from prducts p

                                     where p.pid in (select pid from orders x

                                            where x.cid='c006')and

                                           not exists (select * from orders y

                                                 where y.pid=p.pid and y.cid=c.cid));

           該select的一個變體是：

                              select cid from customers c

                                  where not exists  (select z.pid from oredrs z

                                        where z.cid='c006' and

                                         not exists (select * from orders y

                                         where y.pid=z.pid and y.cid = c.cid));

3.表別名用法

       例：檢索對同一產品至少訂購了兩次的所有顧客的名字。

       select cname from (select o.cid as spcid from orders o,oreders x where o.cid=x.cid

                and o.pid=x.pid and o.ordno<>x.o.ordon)y, customers c

                 where y.spcid=c.cid;

該查詢中為為子查詢的結果指定了別名y，並為子查詢所檢索的列提供了別名spcid

1. UNION 運算子

    實質就是關係運算中的並

   例：建立一個包含了顧客所在的或者代理商所在的或者兩者接在的城市的名單

          select city from customers

                   union select city from agents;

當設計到的子查詢項目大於或等於3時可用（）

      (select city from customers

                union select city from agents)

                union all select city from products;

2. 除法： SQL "FOR ALL..."條件

         如果我們所面臨的查詢要求被檢索的對象集合必須符合某個帶有“所有”這類關鍵詞的條件，我們就按照下列步驟進行

            1） 為要檢索的對象命名並考慮如何用英文來表述要檢索的候選對象的一個反例。在該反例中，前面提到的“所有”

                   對象中有至少一個對象不符合規定的條件。

            2） 建立Select語句的搜尋條件以選出步驟1所建立的所有反例。（步驟1和2必定會引用來自外部Select語句的對象，

                   所以我們要在如何用這些外部對象所在的表來引用他們這一問題上表現出一定的靈活性）

            3）  建立包含步驟2所建立的語句的搜尋條件，說明不存在上面定義的那種反例。這裡將涉及到NOT EXISTS謂詞

            4）  用步驟3的搜尋條件來建立最終的Select語句，檢索所期望的對象。

     上面的步驟序列通常會產生如下所示的嵌套子查詢對：

            select ... where not exists (select ... where  not exists (select ... ));

      例：找出具有下列性質的顧客的cid值：如果顧客c006訂購了某種產品，那要檢索的顧客也訂購了該產品。

          此題可以改寫成：找出訂購了所有被顧客c006訂購的產品的cid值。

         步驟1：我們稱c.cid是符合題意的顧客並用英文構造反例

                 "There is a product ordered by customer c006 that is not ordered by c.cid."

         步驟2：把英文表述成搜尋條件，我們將被c006訂購的產品命名為p.pid

                 cond1: p.pid in (select pid from orders x where x.cid='c006')

                                   and not exists (select * from orders y

                                           where y.pid=p.pid and y.cid=c.cid )

         步驟3：j建立表示這種反例不存在的條件：

                   cond2: not exists (select p.pid from prducts p

                                     where p.pid in (select pid from orders x

                                            where x.cid='c006')and

                                           not exists (select * from orders y

                                                 where y.pid=p.pid and y.cid=c.cid))

           步驟4：建立最終的Select

                           select cid from customers c

                                   where not exists (select p.pid from prducts p

                                     where p.pid in (select pid from orders x

                                            where x.cid='c006')and

                                           not exists (select * from orders y

                                                 where y.pid=p.pid and y.cid=c.cid));

           該select的一個變體是：

                              select cid from customers c

                                  where not exists  (select z.pid from oredrs z

                                        where z.cid='c006' and

                                         not exists (select * from orders y

                                         where y.pid=z.pid and y.cid = c.cid));

3.表別名用法

       例：檢索對同一產品至少訂購了兩次的所有顧客的名字。

       select cname from (select o.cid as spcid from orders o,oreders x where o.cid=x.cid

                and o.pid=x.pid and o.ordno<>x.o.ordon)y, customers c

                 where y.spcid=c.cid;

該查詢中為為子查詢的結果指定了別名y，並為子查詢所檢索的列提供了別名spcid