SQL學習（二）

1.  SQL中的集合函數

名     稱	參數類型	結果類型	描述
COUNT  SUM　  AVG　  MAX  MIN	任意（可以是*）   數值型   數值型   字元型或數值型   字元型或數值型	數值型   數值型　   數值型   同參數的內容一樣   同參數的內容一樣	出現次數    參數的和    參數的平均值    最大值    最小值

註： 在WHERE子句的比較操作中不能出現集合函數，除非它們是出現在子查詢的挑選清單中

例： select cid from customers

              where discnt < (select max(discnt) from customers);

2. SQL中的分組

     group by ... having ...通用形式

   例：當某個代理商所訂購的某樣品產品的總量超過1000時，列印出所有滿足條件的產品和代理商的ID及這個總量

          select pid ,aid, sum(qty) as TOTAL from orders

                 group by pid,aid

                 having sum(qty)>1000;

執行過程：

•          首先對from子句中的所有表做笛卡爾積
•          接著刪除不滿足WHERE子句的行
•          然後根據GROUP BY子句對剩餘的進行分組
•          最後求出挑選清單中運算式的值
•          HAVING子句緊跟在GROUP BY之後但先於對挑選清單的運算式的計算

註： HAVING一般情況不會單獨出現，一般和GROUP BY一起出現，如果省略了GROUP BY子句，那麼HAVING子句將把

       整個結果當做一個組來使用

       

1.ODER BY

   order by cid desc  對列cid的值從大到小排列，如果省略desc則從小到大排列

2.CAST

    cast以一種類型的值為參數並把它顯示的轉化為另一種類型

   cast (substring(cid from 2 for 3) as integer)

   cast (o.qty as char(10))

3. CASE

  通用形式：case

                      when search_conditionl then resulet1

                      when search_condition2 then resulet2

                      ...

                     else resul(N+1)

               end

例： case when city > 'Al1' then city else 'Atlanta' end

4.BETWEEN謂詞

   expr1 [NOT] BETWEEN expr2 and expr3

   其含義（不考慮NOT）實際上就是：expr2<=expr1 and expr1<=expr3

用between謂詞比and連結資料表達式效率高

5.LIKE謂詞

   colname [NOT] LIKE  val [escape val]

第一個val代表模式串，也可以是一個程式變數

下面列出包括萬用字元在內的所有可用在模式串中的特殊字元：

模式串中的字元	含義
底線（_）  百分比符號(%)  逸出字元  所有其他字元	任意單個字元的萬用字元   包含零個或多個字元的任意序列的萬用字元   用在需要按字面含義引用的字元之前   代表他們自己

例：檢索cname值以字母"A"打頭的顧客的所有資訊

 select * from customers where cname like 'A%';

如果要在模式串中按字面意思來引用_,%則用逸出字元(escape)\

例：檢索cname值以“Tip_”打頭並且後面跟著任意個字元的顧客的cid值

select * from customers where cname like 'Tip\_%' escape '\';

6. JOIN

   1)   inter jion

select cname,city,latiude,longitude

       from customers c join  cities x on c.city =x.cityname;

    2)  OUTER JOIN

      以下2個表S和T

         S                                                          T

C               A                                           A          B

c1             a1                                          a1         b1

c3             a3                                          a2         b2

c4             a4                                          a3         b3

做select  * from S full outer  join t using(A)

結果為：

C      A         B

c1     a1       b1

c3     a3       b3

c4     a4       null

null    a2       b2

也可以用以下形式實現：

select * from S left join using(A) union select * from S right jion T using (A);

1.  INSERT

 通用形式：

 INSERT INTO tablename [colname {,colname...}]

              {VALUES (expr ｜NULL ｜ Subquery)}

例：1. insert into orders (ordno,month)

                    values (1107.'aug');

     2.  insert  into swcusts

                  select * from customers 

                             where city in ('dallas','austin');

2.  UPDATE

    例：1.update agents set percent=1.1*percent where city='New York';

          2.update customers set discnt=1.1*discnt where cid in

             (select cid from orders  group by cid having  sum(dollars)>1000);

3.  DELETE

     例：1.delete from agents where city ='New York';

           2. delete  from agents where in

                      (select aid from orders group by adi having sum(dollars)<600);

1.  SQL中的集合函數

名     稱	參數類型	結果類型	描述
COUNT  SUM　  AVG　  MAX  MIN	任意（可以是*）   數值型   數值型   字元型或數值型   字元型或數值型	數值型   數值型　   數值型   同參數的內容一樣   同參數的內容一樣	出現次數    參數的和    參數的平均值    最大值    最小值

註： 在WHERE子句的比較操作中不能出現集合函數，除非它們是出現在子查詢的挑選清單中

例： select cid from customers

              where discnt < (select max(discnt) from customers);

2. SQL中的分組

     group by ... having ...通用形式

   例：當某個代理商所訂購的某樣品產品的總量超過1000時，列印出所有滿足條件的產品和代理商的ID及這個總量

          select pid ,aid, sum(qty) as TOTAL from orders

                 group by pid,aid

                 having sum(qty)>1000;

執行過程：

•          首先對from子句中的所有表做笛卡爾積
•          接著刪除不滿足WHERE子句的行
•          然後根據GROUP BY子句對剩餘的進行分組
•          最後求出挑選清單中運算式的值
•          HAVING子句緊跟在GROUP BY之後但先於對挑選清單的運算式的計算

註： HAVING一般情況不會單獨出現，一般和GROUP BY一起出現，如果省略了GROUP BY子句，那麼HAVING子句將把

       整個結果當做一個組來使用

       

1.ODER BY

   order by cid desc  對列cid的值從大到小排列，如果省略desc則從小到大排列

2.CAST

    cast以一種類型的值為參數並把它顯示的轉化為另一種類型

   cast (substring(cid from 2 for 3) as integer)

   cast (o.qty as char(10))

3. CASE

  通用形式：case

                      when search_conditionl then resulet1

                      when search_condition2 then resulet2

                      ...

                     else resul(N+1)

               end

例： case when city > 'Al1' then city else 'Atlanta' end

4.BETWEEN謂詞

   expr1 [NOT] BETWEEN expr2 and expr3

   其含義（不考慮NOT）實際上就是：expr2<=expr1 and expr1<=expr3

用between謂詞比and連結資料表達式效率高

5.LIKE謂詞

   colname [NOT] LIKE  val [escape val]

第一個val代表模式串，也可以是一個程式變數

下面列出包括萬用字元在內的所有可用在模式串中的特殊字元：

模式串中的字元	含義
底線（_）  百分比符號(%)  逸出字元  所有其他字元	任意單個字元的萬用字元   包含零個或多個字元的任意序列的萬用字元   用在需要按字面含義引用的字元之前   代表他們自己

例：檢索cname值以字母"A"打頭的顧客的所有資訊

 select * from customers where cname like 'A%';

如果要在模式串中按字面意思來引用_,%則用逸出字元(escape)\

例：檢索cname值以“Tip_”打頭並且後面跟著任意個字元的顧客的cid值

select * from customers where cname like 'Tip\_%' escape '\';

6. JOIN

   1)   inter jion

select cname,city,latiude,longitude

       from customers c join  cities x on c.city =x.cityname;

    2)  OUTER JOIN

      以下2個表S和T

         S                                                          T

C               A                                           A          B

c1             a1                                          a1         b1

c3             a3                                          a2         b2

c4             a4                                          a3         b3

做select  * from S full outer  join t using(A)

結果為：

C      A         B

c1     a1       b1

c3     a3       b3

c4     a4       null

null    a2       b2

也可以用以下形式實現：

select * from S left join using(A) union select * from S right jion T using (A);

1.  INSERT

 通用形式：

 INSERT INTO tablename [colname {,colname...}]

              {VALUES (expr ｜NULL ｜ Subquery)}

例：1. insert into orders (ordno,month)

                    values (1107.'aug');

     2.  insert  into swcusts

                  select * from customers 

                             where city in ('dallas','austin');

2.  UPDATE

    例：1.update agents set percent=1.1*percent where city='New York';

          2.update customers set discnt=1.1*discnt where cid in

             (select cid from orders  group by cid having  sum(dollars)>1000);

3.  DELETE

     例：1.delete from agents where city ='New York';

           2. delete  from agents where in

                      (select aid from orders group by adi having sum(dollars)<600);

1.ODER BY

   order by cid desc  對列cid的值從大到小排列，如果省略desc則從小到大排列

2.CAST

    cast以一種類型的值為參數並把它顯示的轉化為另一種類型

   cast (substring(cid from 2 for 3) as integer)

   cast (o.qty as char(10))

3. CASE

  通用形式：case

                      when search_conditionl then resulet1

                      when search_condition2 then resulet2

                      ...

                     else resul(N+1)

               end

例： case when city > 'Al1' then city else 'Atlanta' end

4.BETWEEN謂詞

   expr1 [NOT] BETWEEN expr2 and expr3

   其含義（不考慮NOT）實際上就是：expr2<=expr1 and expr1<=expr3

用between謂詞比and連結資料表達式效率高

5.LIKE謂詞

   colname [NOT] LIKE  val [escape val]

第一個val代表模式串，也可以是一個程式變數

下面列出包括萬用字元在內的所有可用在模式串中的特殊字元：

模式串中的字元	含義
底線（_）  百分比符號(%)  逸出字元  所有其他字元	任意單個字元的萬用字元   包含零個或多個字元的任意序列的萬用字元   用在需要按字面含義引用的字元之前   代表他們自己

例：檢索cname值以字母"A"打頭的顧客的所有資訊

 select * from customers where cname like 'A%';

如果要在模式串中按字面意思來引用_,%則用逸出字元(escape)\

例：檢索cname值以“Tip_”打頭並且後面跟著任意個字元的顧客的cid值

select * from customers where cname like 'Tip\_%' escape '\';

6. JOIN

   1)   inter jion

select cname,city,latiude,longitude

       from customers c join  cities x on c.city =x.cityname;

    2)  OUTER JOIN

      以下2個表S和T

         S                                                          T

C               A                                           A          B

c1             a1                                          a1         b1

c3             a3                                          a2         b2

c4             a4                                          a3         b3

做select  * from S full outer  join t using(A)

結果為：

C      A         B

c1     a1       b1

c3     a3       b3

c4     a4       null

null    a2       b2

也可以用以下形式實現：

select * from S left join using(A) union select * from S right jion T using (A);

1.  INSERT

 通用形式：

 INSERT INTO tablename [colname {,colname...}]

              {VALUES (expr ｜NULL ｜ Subquery)}

例：1. insert into orders (ordno,month)

                    values (1107.'aug');

     2.  insert  into swcusts

                  select * from customers 

                             where city in ('dallas','austin');

2.  UPDATE

    例：1.update agents set percent=1.1*percent where city='New York';

          2.update customers set discnt=1.1*discnt where cid in

             (select cid from orders  group by cid having  sum(dollars)>1000);

3.  DELETE

     例：1.delete from agents where city ='New York';

           2. delete  from agents where in

                      (select aid from orders group by adi having sum(dollars)<600);