組函式和子查詢訓練任務

1.1.  建立測試表

1)   建立表

HR@ENMOEDU>create table t_group

(id number(2) primary key

,name varchar2(10)

,age number(2)

,classno number(2));

2)   插入測試資料

insert into t_group values(1,'zhangsan',25,3);

insert into t_group values(2,'lisi',26,3);

insert into t_group values(3,'wangwu',30,1);

insert into t_group values(4,null,15,1);

insert into t_group values(5,'zhangsan',25,2);

insert into t_group values(6,null,null,null);

commit;

3)   檢視效果

HR@ENMOEDU>select * from t_group;

 

        ID NAME              AGE    CLASSNO

-------- ---------- ---------- ----------

         1 zhangsan           25          3

         2 lisi               26          3

         3 wangwu             30          1

         4                     15          1

         5 zhangsan         25          2

         6

 

6 rows selected.

 

HR@ENMOEDU>

1.2.  聚集

1.2.1.  COUNT

語法：COUNT({ * | [ DISTINCT | ALL ] expr })

1)   統計表中的資料量

HR@ENMOEDU>select count(*) from t_group;

 

  COUNT(*)

----------

         6

 

HR@ENMOEDU>

2)   統計name欄位不為空的記錄數

--方法一：使用where子句過濾name為null的記錄

HR@ENMOEDU>select count(*) from t_group where name is not null;

 

  COUNT(*)

----------

         4

 

HR@ENMOEDU>

--方法二：count函式中，寫入name欄位的名稱進行統計，結果一樣是正確的，原因是，count(name)這種方式統計記錄數是不統計name為null的記錄的

HR@ENMOEDU>select count(name) from t_group;

 

COUNT(NAME)

-----------

          4

 

HR@ENMOEDU>

1.2.2.  SUM

語法：SUM([ DISTINCT | ALL ] expr)

1)   統計age欄位值的總和。注：null值是不參與運算的

HR@ENMOEDU>select sum(age) from t_group;

 

 

  SUM(AGE)

----------

       121

 

HR@ENMOEDU>

2)   統計age欄位透過distinct去重後的總和（即：先去重，後求和），null同樣不參與運算

HR@ENMOEDU>select sum(distinct age) from t_group;

 

SUM(DISTINCTAGE)

----------------

              96

 

HR@ENMOEDU>

1.2.3.  MIN、MAX

語法：MIN([ DISTINCT] expr)

MAX([ DISTINCT] expr)

expr為一個字元型、日期型或數值型別，如果expr為字元型，則根據ASCII來判斷大小

1)   檢視age最大值

HR@ENMOEDU>select max(age) from t_group;

 

  MAX(AGE)

----------

        30

 

HR@ENMOEDU>

2)   驗證字元型是透過ASCII來判斷大小的

透過目測我們就能發現ASCII最大的name為zhangsan，max(name)的結果，與我們目測分析的相同，也就是驗證了字元型是根據ASCII來判斷大小的

HR@ENMOEDU>select id,name,ascii(name) from t_group;

 

        ID NAME       ASCII(NAME)

-------- ---------- -----------

         1 zhangsan           122

         2 lisi               108

 

         3 wangwu            119

         4

         5 zhangsan           122

         6

 

6 rows selected.

 

HR@ENMOEDU>select max(name) from t_group;

 

MAX(NAME)

----------

zhangsan

 

HR@ENMOEDU>

3)   思考題

MAX組函式中使用了distinct是否有意義？無意義

4)   自測題：

ü   建立測試表

SYS@ENMOEDU>create table t_objects as select * from dba_objects;

 

Table created.

 

SYS@ENMOEDU>create index idx_t_objects on t_objects(object_id);

 

Index created.

 

SYS@ENMOEDU>

ü   請大家自行驗證如下SQL：

select min(object_id) from t_objects;

select max(object_id) from t_objects;

select max(object_id),min(object_id) from t_objects;

ü   提示：透過執行時間和執行計劃來觀察他們在效率上的區別。

開啟顯示執行時間的方法：

SYS@ENMOEDU>set timing on

開啟顯示執行計劃的方法：

SYS@ENMOEDU>set autot trace exp

1.2.4.  AVG

語法：AVG([ DISTINCT] expr)

求平均值

1)   統計age欄位值的平均值

HR@ENMOEDU>select avg(age) from t_group;

 

  AVG(AGE)

----------

 24.2

 

HR@ENMOEDU>

2)   統計去重以後的age欄位值的平均值

HR@ENMOEDU>select avg(distinct age) from t_group;

 

AVG(DISTINCTAGE)

----------------

 24

 

HR@ENMOEDU>

3)   小結

我們會發現，兩個結果是不同的，為什麼？因為distinct去重以後，做平均時分母的值就變了，導致結果的不一致。

1.3.  分組

1.3.1.  Group by 子句

    在資料庫查詢中，分組是一個非常重要的應用。分組是指將資料表中所有記錄中，以某個或者某些列為標準，劃分為一組。

    進行分組查詢應該使用group by子句。group by子句指定分組標準，並將資料來源按照該標準進行劃分，然後迴圈處理每組資料。

HR@ENMOEDU>

SELECT classno, SUM(age) AS sum_age,

       COUNT(DISTINCT NAME) AS cnt,

       AVG(age) AS avg_age,

       MIN(age) AS min_age,

 MAX(age) AS max_age

  FROM t_group

 GROUP BY classno;

 

   CLASSNO    SUM_AGE        CNT    AVG_AGE    MIN_AGE    MAX_AGE

---------- ---------- ---------- ---------- ---------- ----------

0

 1         45          1       22.5         15        30

2         25          1         25         25         25

 3         51          2       25.5         25         26

 

問：CNT欄位為什麼有一個0？

答：是因為CNT欄位統計的是name欄位的數量，因為按照CLASSNO進行了分組，CLASSNO為null的那一組（行）的name也為null，由於null是不被count所統計的，所以該值為0.

 

另外，我們還要從這個結果中看出，無論是sum、avg、min還是max，當所統計的值都為null的時候，那麼則直接返回null。

1.3.2.  Having 子句

    where子句可以過濾from子句所指定的資料來源，但是對於group by子句所產生的分組無效。為了將分組按照一定條件進行過濾，應該使用having子句。

HR@ENMOEDU>

SELECT classno, SUM(age) AS sum_age,

       COUNT(DISTINCT NAME) AS cnt,

       AVG(age) AS avg_age,

       MIN(age) AS min_age,

       MAX(age) AS max_age

  FROM t_group

 GROUP BY classno

HAVING COUNT(DISTINCT NAME)>=1;

 

   CLASSNO    SUM_AGE        CNT    AVG_AGE    MIN_AGE    MAX_AGE

---------- ---------- ---------- ---------- ---------- ----------

 1         45          1       22.5         15        30

2         25          1         25       25         25

 3         51          2       25.5         25         26

 

HR@ENMOEDU>

 

 

第2章  子查詢

     子查詢是指在查詢語句的內部嵌入查詢（也稱巢狀查詢），以獲得臨時的結果集。 Oracle總是自動最佳化帶有子查詢的查詢語句。如果子查詢中的資料來源與父查詢中的資料可以實現連線操作，那麼將轉化為連線操作；否則，將首先執行子查詢，然後執行父查詢

 

    子查詢指嵌入在其他SQL中的select語句,也稱巢狀查詢.

    按照子查詢返回結果,可將子查詢分為:

        單行單列

        單行多列

        多行單列

        多行多列

    特點:

        1.優先執行子查詢,主查詢再使用子查詢的結果

        2.子查詢返回的列數和型別要匹配

        3.子查詢要用括號括起來

        4.子查詢返回多行要用多行關係運算子

2.1.  查詢條件中的子查詢

HR@ENMOEDU>select last_name from employees where salary > (select salary from employees where last_name='Bloom');

 

LAST_NAME

-------------------------

Hartstein

Higgins

King

Kochhar

De Haan

Greenberg

Raphaely

Russell

Partners

Errazuriz

Cambrault

Zlotkey

Vishney

 

Ozer

Abel

 

15 rows selected.

 

HR@ENMOEDU>

2.2.  建表語句中的子查詢

利用CTAS方式建立表tmp_user_objects（where 1=2 這種不等於的條件只建立的表結構），這種方式就是建表語句中的子查詢

HR@ENMOEDU>create table tmp_user_objects as select * from user_objects where 1=2;

 

Table created.

 

HR@ENMOEDU>select count(*) from tmp_user_objects;

 

  COUNT(*)

----------

         0

2.3.  插入語句中的子查詢

HR@ENMOEDU>insert into tmp_user_objects select * from user_objects where object_type='TABLE';

 

5 rows created.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>