【sql】編寫基本的SQL SELECT語句四

不一樣的天空w發表於2017-06-02
多表查詢及建立物件訓練手冊

  第1章  多表查詢

1.1.  等連線

    透過兩個表具有相同意義的列,建立連線條件。查詢結果只顯示兩個列中的值是等值條件的行資料。表中同名列被選擇時必須新增表名字首進行修飾 否則無法確定這一列是屬於哪個表

HR@ENMOEDU> conn hr/hr

Connected.

HR@ENMOEDU>SELECT count(*)

  FROM departments d, employees t

  WHERE d.department_id = t.department_id;

 

  COUNT(*)

----------

 106

 

HR@ENMOEDU> --注:下面sql語句中 INNER 是可以省略不寫的。

HR@ENMOEDU>SELECT COUNT(*)

  FROM departments d

 INNER JOIN employees t

 ON d.department_id = t.department_id;

 

  COUNT(*)

----------

 106

 

HR@ENMOEDU>SELECT COUNT(*)

  FROM departments d

  JOIN employees t

    USING (department_id);

 

COUNT(*)

----------

106

 

HR@ENMOEDU>

 

表名字首(因為兩張表都存在欄位department_id,故報此錯誤)

HR@ENMOEDU>select department_id, employee_id, first_name, department_name

  from employees d, departments t

    where d.department_id = t.department_id;

select department_id, employee_id, first_name, department_name

       *

ERROR at line 1:

ORA-00918: column ambiguously defined

 

HR@ENMOEDU>select d.department_id, employee_id, first_name, department_name

  from employees d, departments t

 where d.department_id = t.department_id;

 

DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME           DEPARTMENT_NAME

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

           10         200 Jennifer             Administration

           20         201 Michael              Marketing

           20         202 Pat                  Marketing

           30         114 Den                  Purchasing

           30         115 Alexander            Purchasing

           30         116 Shelli               Purchasing

           30         117 Sigal                Purchasing

           30         118 Guy                  Purchasing

           30         119 Karen                Purchasing

           40         203 Susan                Human Resources

           50         120 Matthew              Shipping

 

HR@ENMOEDU>

問:下面兩個sql的結果是否相同?為什麼?相同,and是並行滿足,而where是過濾只要滿足這一個條件。

SQL1:

SELECT *

FROM departments d

JOIN employees t

ON d.department_id= t.department_id

AND d.department_name = 'SALES';

 

SQL2:

SELECT *

FROM departments d

JOIN employees t

ON d.department_id = t.department_id

WHERE d.department_name = 'SALES';

1.2.  自然連線

    自然連線只發生在兩個表中有相同名字和資料型別的列上,如果單純的列名相同而型別不同,那麼會報語法錯誤。

HR@ENMOEDU>desc departments

Name                                      Null?    Type

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

 DEPARTMENT_ID                             NOT NULL NUMBER(4)

 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)

 MANAGER_ID                                         NUMBER(6)

 LOCATION_ID                                        NUMBER(4)

 

HR@ENMOEDU>desc employees

Name                                      Null?    Type

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

 EMPLOYEE_ID                                       NOT NULL NUMBER(6)

 FIRST_NAME                                         VARCHAR2(20)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

 EMAIL                                     NOT NULL VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                                 NOT NULL DATE

 JOB_ID                                    NOT NULL VARCHAR2(10)

 SALARY                                             NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                         NUMBER(6)

 DEPARTMENT_ID                                      NUMBER(4)

 

HR@ENMOEDU> SELECT d.department_name, d.location_id, t.first_name

   FROM departments d NATURAL JOIN employees t;

 

DEPARTMENT_NAME                LOCATION_ID FIRST_NAME

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

Executive                             1700 Neena

Executive                             1700 Lex

IT                                    1400 Bruce

IT                                    1400 David

IT                                    1400 Valli

IT                                    1400 Diana

Finance                               1700 Daniel

Finance                               1700 John

Finance                               1700 Ismael

Finance                               1700 Jose Manuel

Finance                               1700 Luis

Purchasing                            1700 Alexander

Purchasing                            1700 Shelli

Purchasing                            1700 Sigal

Purchasing                            1700 Guy

Purchasing                            1700 Karen

Shipping                              1500 Laura

Shipping                              1500 Mozhe

 

HR@ENMOEDU>

1.3.  using子句

1)   using子句的用法:

HR@ENMOEDU>select d.department_name,d.location_id,t.first_name

   from departments d join employees t using (department_id);

 

DEPARTMENT_NAME                LOCATION_ID FIRST_NAME

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

Administration                        1700 Jennifer

Marketing                             1800 Pat

Marketing                             1800 Michael

Purchasing                            1700 Sigal

Purchasing                            1700 Karen

Purchasing                            1700 Shelli

Purchasing                            1700 Den

Purchasing                            1700 Alexander

Purchasing                            1700 Guy

Human Resources                       2400 Susan

Shipping                              1500 Kevin

Shipping                              1500 Jean

Shipping                              1500 Adam

Shipping                              1500 Timothy

Shipping                              1500 Ki

Shipping                              1500 Girard

 

HR@ENMOEDU>

2)   using子句的注意事項

HR@ENMOEDU> select d.department_name,d.location_id,t.first_name

    from departments d join employees t

  using (department_id)

   where department_id=10;

 

DEPARTMENT_NAME                LOCATION_ID FIRST_NAME

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

Administration                        1700 Jennifer

 

HR@ENMOEDU>select d.department_name,d.location_id,t.first_name

from departments d join employees t

  using (department_id) where d.department_id=10;

 

using (department_id) where d.department_id=10

                            *

ERROR at line 3:

ORA-25154: column part of USING clause cannot have qualifier

 

HR@ENMOEDU>

    透過面的例子可以得出,using子句中,相同名字的欄位不允許有任何限定符,否則將報ORA-25154:錯誤,此限制同樣適用於natural join(自然連線)

1.4.  不等連線

    A表中的某列資料和B表中一列或多列的關係是非等值關係,大於,小於,不等於,等條件都屬於不等連線的範疇

    就是排除完全相等條件以外的 >,<,!=, <=, >=, between and主要在於不同表之間顯示特定範圍的資訊(也可以理解成包含關係)

HR@ENMOEDU>create table job_grades (s_grade varchar2(2),low_salary number,high_salary number);

insert into job_grades (s_grade, low_salary, high_salary) values ('A', 0, 10000);

insert into job_grades (s_grade, low_salary, high_salary) values ('B', 10001, 20000);

insert into job_grades (s_grade, low_salary, high_salary) values ('C', 20001, 30000);

HR@ENMOEDU> commit;

Commit complete.

 

HR@ENMOEDU>select first_name,salary,s_grade

 from employees a,job_grades b

 where salary between low_salary and high_salary and b.s_grade='B';

 

FIRST_NAME               SALARY S_

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

Neena                     17000 B

Lex                       17000 B

Nancy                     12008 B

Den                       11000 B

John                      14000 B

 

HR@ENMOEDU>

between A and B (大於等於A並且小於等於B)

HR@ENMOEDU> with t as (

select rownum id from dual connect by rownum<=10)

select id from t where id between 2 and 8;

 

        ID

---------

         2

         3

         4

         5

         6

         7

         8

 

7 rows selected.

 

HR@ENMOEDU>

1.5.  自連線

    資料都來自一張表,所以在from句中需要對錶新增別名,新增表別名後才能合法化的引用表中的列名本質就是將一張表虛擬成了兩張表

HR@ENMOEDU>SELECT d.last_name || ' works for ' || t.last_name

FROM   employees d, employees t

WHERE  d.manager_id = t.employee_id;

 

D.LAST_NAME||'WORKSFOR'||T.LAST_NAME

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

Smith works for Cambrault

Ozer works for Cambrault

Kumar works for Cambrault

……

中間省略n

……

Hutton works for Zlotkey

Grant works for Zlotkey

Abel works for Zlotkey

 

106 rows selected.

 

HR@ENMOEDU>

1.6.  外連線

    選擇出滿足等連線條件的及其以外的記錄。(+)修飾符號用法:置在選出結果只包含等連線的列後,另一列的結果就是等值行+非等值行

1.6.1.  左外連線

在等連線的基礎上,還包含左表中所有不符合條件的資料行,並在其中的右表列填寫NULL

1)   Oracle自己的寫法(不推薦):

HR@ENMOEDU>col id for 99

HR@ENMOEDU>col name for a4

HR@ENMOEDU> with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id=t2.id(+);

 

ID NAME        ID NAME

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

2 b            2 bb

 3 c            3 cc

1 a

HR@ENMOEDU>

2)   SQL99寫法

 

HR@ENMOEDU> with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1 left outer join t2 on t1.id=t2.id;

 

ID NAME        ID NAME

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

2 b            2 bb

 3 c            3 cc

1 a

 

HR@ENMOEDU>

注:outer 可以省略

問:下面的sql結果是否相同,思考為什麼?不一樣,and是同時滿足,而where是隻滿足。

SQL1

with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id and t2.id=2;

SQL2

with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id where t2.id=2;

1.6.2.  右外連線

在等連線的基礎上,還包含右表中所有不符合條件的資料行,並在其中的左表列填寫NULL

1)   Oracle自己的寫法(不推薦):

HR@ENMOEDU> with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id(+)=t2.id;

 

ID NAME        ID NAME

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

2 b           2 bb

 3 c            3 cc

 4 dd

 

HR@ENMOEDU>

2)   SQL99寫法

HR@ENMOEDU> with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select t1.id,t1.name,t2.id,t2.name from t1 right outer join t2 on t1.id=t2.id;

 

ID NAME        ID NAME

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

2 b           2 bb

 3 c            3 cc

 4 dd

 

HR@ENMOEDU>

1.6.3.  全外連線

在等連線的基礎上,還包含兩個表中所有不符合條件的資料行,並在其中的左表、和右表列填寫NULL 

HR@ENMOEDU>col n1 for a5

HR@ENMOEDU>col n2 for a5

HR@ENMOEDU>with

t1 as (

select 1 as id,'a' as name from dual union all

select 2,'b' from dual union all

select 3,'c' from dual),

t2 as (

select 2 as id,'bb' as name from dual union all

select 3,'cc' from dual union all

select 4,'dd' from dual)

select nvl(t1.id,t2.id) as id ,t1.name as n1, t2.name as n2

from t1 full join t2 on t1.id=t2.id order by 1 asc;

 

ID N1    N2

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

1 a

2 b     bb

3 c     cc

4       dd

 

HR@ENMOEDU>

1.7.  交叉連線(笛卡爾

將兩個表的所有行進行組合,連線後的行數為兩個表的乘積數。

往往有人會說要避免笛卡爾積,上學的時候,老師也把它說的很恐怖,其實則不然,我倒是認為如若弄明白它的原理,則可合理利用它。

1)   寫法一:

HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual

            UNION ALL SELECT 'list' FROM dual),

     t2 AS (SELECT 'range' AS l FROM dual

            UNION ALL SELECT 'list' FROM dual),

     t3 AS (SELECT 'hash' AS h from dual)

SELECT r,l FROM t1 CROSS JOIN t2

UNION ALL

SELECT l,h FROM t2 CROSS JOIN t3;

 

R     L

----- -----

range range

range list

list  range

list  list

range hash

list  hash

 

6 rows selected.

 

HR@ENMOEDU>

2)   寫法二:

HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual

            UNION ALL SELECT 'list' FROM dual),

     t2 AS (SELECT 'range' AS l FROM dual

            UNION ALL SELECT 'list' FROM dual),

     t3 AS (SELECT 'hash' AS h from dual)

SELECT r,l FROM t1,t2

UNION ALL

SELECT l,h FROM t2,t3;

 

R     L

----- -----

range range

range list

list  range

list  list

range hash

list  hash

 

6 rows selected.

 

HR@ENMOEDU>


  第2章  建立和管理表

語法:

CREATE TABLE [schema.]table

        (columndatatype [DEFAULT expr][, ...]);

2.1.  建立表

HR@ENMOEDU>CREATE TABLE dept2

(department_id   NUMBER(2),

 dname   VARCHAR2(14),

 loc   VARCHAR2(13));

 

Table created.

 

HR@ENMOEDU>

2.2.  日期資料型別

TIMESTAMP       帶小數秒的日期型

INTERVAL YEAR TO MONTH  作為年和月的時間間隔儲存

INTERVAL DAY TO SECOND 作為天、小時、分和秒的時間間隔儲存(DAY, HOUR, MINUTE, SECOND

HR@ENMOEDU>create table ha (id number,x INTERVAL YEAR TO MONTH,y INTERVAL DAY TO SECOND );

 

Table created.

 

HR@ENMOEDU>

--x欄位插入:5

--y欄位插入:2

HR@ENMOEDU>insert into ha values(1,INTERVAL '5' year,INTERVAL '2' day);

 

1 row created.

 

HR@ENMOEDU>

 

--x欄位插入:10個月

--y欄位插入:2小時

HR@ENMOEDU>insert into ha values(2,INTERVAL '10' month,INTERVAL '2' hour);

 

1 row created.

 

HR@ENMOEDU>

--x欄位插入:2年零6個月

--y欄位插入:1天零12小時30分鐘1

HR@ENMOEDU>insert into ha values(3,INTERVAL '2-6' YEAR TO MONTH,INTERVAL '1 12:30:01' DAY TO SECOND);

                                           

1 row created.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>

--利用系統時間加上xy欄位來驗證這兩個資料型別

HR@ENMOEDU>select id,sysdate,sysdate+x,sysdate+y from ha;

 

 ID SYSDATE             SYSDATE+X           SYSDATE+Y

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

  1 2012-09-09 13:19:32 2017-09-09 13:19:32 2012-09-11 13:19:32

  2 2012-09-09 13:19:32 2013-07-09 13:19:32 2012-09-09 15:19:32

  3 2012-09-09 13:19:32 2015-03-09 13:19:32 2012-09-11 01:49:33

 

HR@ENMOEDU>

2.3.  用子查詢語法建立表

HR@ENMOEDU>create table dept3 as select * from departments;

 

Table created.

 

HR@ENMOEDU>

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

 

  COUNT(*)

----------

27

 

HR@ENMOEDU>desc dept3;

Name                                      Null?    Type

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

 DEPARTMENT_ID                                      NUMBER(4)

 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)

 MANAGER_ID                                         NUMBER(6)

 LOCATION_ID                                        NUMBER(4)

HR@ENMOEDU>

2.4.  修改表

1)   增加欄位

HR@ENMOEDU>alter table dept3 add (job_id number(5));

 

Table altered.

 

HR@ENMOEDU>desc dept3;

Name                                      Null?    Type

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

 DEPARTMENT_ID                                      NUMBER(4)

 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)

 MANAGER_ID                                         NUMBER(6)

 LOCATION_ID                                        NUMBER(4)

 JOB_ID                                             NUMBER(5)

HR@ENMOEDU>

2)   修改欄位大小

HR@ENMOEDU>alter table dept3 modify (job_id number(10));

 

Table altered.

 

HR@ENMOEDU>desc dept3;

Name                                      Null?    Type

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

 DEPARTMENT_ID                                      NUMBER(4)

 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)

 MANAGER_ID                                         NUMBER(6)

 LOCATION_ID                                        NUMBER(4)

 JOB_ID                                             NUMBER(10)

HR@ENMOEDU>

3)   刪除欄位

HR@ENMOEDU>alter table dept3 drop column job_id;

 

Table altered.

 

HR@ENMOEDU>desc dept3;

Name                                      Null?    Type

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

 DEPARTMENT_ID                                      NUMBER(4)

 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)

 MANAGER_ID                                         NUMBER(6)

 LOCATION_ID                                        NUMBER(4)

HR@ENMOEDU>

2.5.  欄位UNUSED

UNUSED 選項標記一個或多個未使用的列

特別注意:unused是不可逆的。

    那麼為什麼還要這麼做呢,主要還是因為,這個動作是對資料字典進行了刪除,效能非常的好,對資料庫沒什麼壓力。如果資料庫壓力很大,同時又不想要某個欄位了,那麼就可以用這種方式,等壓力小了,再做清除的處理

1)   建立測試表test(兩個欄位xy並插入一條記錄

HR@ENMOEDU>create table test(x number(1),y number(2));

 

Table created.

 

HR@ENMOEDU>insert into test values(1,2);

 

1 row created.

 

HR@ENMOEDU>commit;

 

 

Commit complete.

 

HR@ENMOEDU>

2)   將欄位y設定為unused

HR@ENMOEDU> alter table test set unused (y);

 

Table altered.

 

HR@ENMOEDU>

3)   檢視錶,y欄位已經消失

HR@ENMOEDU>select * from test;

 

X

----------

1

 

HR@ENMOEDU>

4)   檢視字典表中,表設定為unused欄位個數,由於unused是不可逆的,所以,此處只顯示欄位個數,不顯示具體的欄位

 desc user_unused_col_tabs

 Name                                      Null?    Type

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

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 COUNT                                              NUMBER

HR@ENMOEDU> select * from user_unused_col_tabs;

 

TABLE_NAME                          COUNT

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

TEST                                    1

 

HR@ENMOEDU>

5)   我們可以加入一個欄位yvarchar2型別),檢視testy欄位建立成功

HR@ENMOEDU>alter table test add(y varchar2(10));

 

Table altered.

 

HR@ENMOEDU>select * from test;

 

 X Y

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

1

 

HR@ENMOEDU>

6)   刪除設定為unused的欄位

HR@ENMOEDU>alter table test drop unused COLUMNS;

 

Table altered.

 

HR@ORA11GR2>select * from user_unused_col_tabs;

 

no rows selected

2.6.  刪除表

HR@ENMOEDU>drop table test;

 

Table dropped.

 

HR@ENMOEDU>

注:此種方式刪除後,是進入回收站的,如果不進入回收站直接刪除的話,那麼在刪除的時候加上purge子句,詳見《閃回》章節。

2.7.  改變物件的名字

1)   檢視當前使用者下所有表物件

HR@ENMOEDU>select tname from tab where tabtype='TABLE';

 

TNAME

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

EMPLOYEES

DEPARTMENTS

DEPT2

......

 

HR@ENMOEDU>

2)   重新命名DEPT2DEPT5

HR@ENMOEDU>rename DEPT2 to DEPT5;

 

Table renamed.

 

3)   再次檢視DEPT2已經不見了,已經重新命名為DEPT5

HR@ENMOEDU>select tname from tab where tabtype='TABLE';

 

TNAME

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

EMPLOYEES

DEPARTMENTS

DEPT5

......

 

HR@ENMOEDU>

2.8.  截斷表

?   TRUNCATE  TABLE 語句

-     刪除表中所有的行

-     釋放該表所使用的儲存空間,降低高水位

?   DDL操作,不能回退用 TRUNCATE 刪除的行

?   如果想刪除某幾行,那麼可以用 DELETE 語句刪除行

 

1)   建立測試表,並檢視分配的區

HR@ENMOEDU>create table tt_tab as select * from all_objects;

 

Table created.

 

HR@ENMOEDU>col SEGMENT_NAME for a12

HR@ENMOEDU>col SEGMENT_TYPE for a12

HR@ENMOEDU>col TABLESPACE_NAME for a15

HR@ENMOEDU>col EXTENT_ID for 999999999

HR@ENMOEDU>col BLOCKS for 999999

HR@ENMOEDU>set pagesize 300

HR@ENMOEDU>

select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');

 

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID    size(k)  BLOCKS

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

TT_TAB        TABLE          USERS                        0           64        8

TT_TAB        TABLE          USERS                        1           64        8

TT_TAB        TABLE          USERS                        2           64        8

TT_TAB        TABLE          USERS                        3           64        8

TT_TAB        TABLE          USERS                        4           64        8

TT_TAB        TABLE          USERS                        5           64        8

TT_TAB        TABLE          USERS                        6           64        8

TT_TAB        TABLE          USERS                        7           64        8

TT_TAB        TABLE          USERS                        8           64        8

TT_TAB        TABLE          USERS                        9           64        8

TT_TAB        TABLE          USERS                       10           64        8

TT_TAB        TABLE          USERS                       11           64        8

TT_TAB        TABLE          USERS                       12           64        8

TT_TAB        TABLE          USERS                       13           64        8

TT_TAB        TABLE          USERS                       14           64        8

TT_TAB        TABLE          USERS                       15           64        8

TT_TAB        TABLE          USERS                       16         1024      128

TT_TAB        TABLE          USERS                       17         1024      128

TT_TAB        TABLE          USERS                       18         1024      128

TT_TAB        TABLE          USERS                       19         1024      128

TT_TAB        TABLE          USERS                       20         1024      128

TT_TAB        TABLE          USERS                       21         1024      128

 

22 rows selected.

 

HR@ENMOEDU>

2)   DDLtruncate)操作後,不能回退用 TRUNCATE 刪除的行,因為是DDL操作,無需要顯示提交,當然,回滾也無濟於事。

HR@ENMOEDU>truncate table tt_tab;

 

Table truncated.

 

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

 

  COUNT(*)

----------

 

         0

 

HR@ENMOEDU>

--驗證DDL語句無法rollback

HR@ENMOEDU>rollback;

 

Rollback complete.

 

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

 

  COUNT(*)

----------

         0

 

HR@ENMOEDU>

3)   降低高水位,我們發現分配的區已經全部收回

HR@ENMOEDU>select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');

 

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID    size(k)  BLOCKS

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

TT_TAB       TABLE        USERS                    0         64       8

 

HR@ENMOEDU>

自測題:

請大家按照以上的方法自行驗證用delete方式清空表後delete from tablename,區的分配情況。


 

 

  第3章  內建約束

3.1.  NOT NULL

注:not null約束只能在列級定義。

HR@ENMOEDU>create table t_notnull

(x number not null,

y number constraint nn_tnotnull_y not null

);

 

Table created.

 

HR@ENMOEDU>select constraint_name,constraint_type,search_condition

from user_constraints

where table_name=upper('t_notnull');

 

CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION

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

SYS_C0011054    C               "X" IS NOT NULL

NN_TNOTNULL_Y   C               "Y" IS NOT NULL

 

3.2.  UNIQUE

唯一鍵約束

HR@ENMOEDU>create table student1

(sno number(5) not null,

sname varchar2(20) not null constraint uk_student_sname1 unique,

idcard varchar2(18),

createtime date default sysdate not null,

constraint uk_student_idcard1 unique(idcard)

);

Table created.

 

HR@ENMOEDU>

HR@ENMOEDU>create table student2

(sno number(5) not null,

sname varchar2(20) not null unique,

idcard varchar2(18),

createtime date default sysdate not null,

constraint uk_student_idcard2 unique(idcard)

);

 

Table created.

 

HR@ENMOEDU>create table student3

(sno number(5) not null,

sname varchar2(20) not null unique,

idcard varchar2(18),

createtime date default sysdate not null,

unique(idcard)

);

 

Table created.

 

HR@ENMOEDU>col table_name for a12

HR@ENMOEDU>col column_name for a15

HR@ENMOEDU>col constraint_name for a20

HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('student_');

 

TABLE_NAME   COLUMN_NAME     CONSTRAINT_NAME

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

STUDENT3      IDCARD            SYS_C0011070

STUDENT3      SNAME             SYS_C0011069

STUDENT3      CREATETIME        SYS_C0011068

STUDENT3      SNAME             SYS_C0011067

STUDENT3      SNO               SYS_C0011066

STUDENT2      IDCARD            UK_STUDENT_IDCARD2

STUDENT2      SNAME             SYS_C0011064

STUDENT2      CREATETIME        SYS_C0011063

STUDENT2      SNAME             SYS_C0011062

STUDENT2      SNO               SYS_C0011061

STUDENT1      IDCARD            UK_STUDENT_IDCARD1

STUDENT1      SNAME             UK_STUDENT_SNAME1

STUDENT1      CREATETIME        SYS_C0011058

STUDENT1      SNAME             SYS_C0011057

STUDENT1      SNO               SYS_C0011056

 

15 rows selected.

 

HR@ENMOEDU>

3.3.  PRIMARY KEY

主鍵約束

1)   行級定義主鍵,系統分配主鍵名稱

HR@ENMOEDU>CREATE TABLE t_pk1

(

sno NUMBER(5) primary key,

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

HR@ENMOEDU>

2)   表級定義主鍵,自定義主鍵名稱

HR@ENMOEDU>CREATE TABLE t_pk2

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE,

constraint pk_t_pk2_sno primary key(sno)

);

 

Table created.

 

HR@ENMOEDU>

3)   表級定義主鍵,系統分配主鍵名稱

HR@ENMOEDU>CREATE TABLE t_pk3

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE,

primary key(sno)

);

 

Table created.

 

HR@ENMOEDU>

4)   行級定義主鍵,自定義主鍵名稱,並且指定索引使用的表空間

HR@ENMOEDU>CREATE TABLE t_pk4

(

sno NUMBER(5) constraint pk_t_pk4_sno primary KEY using index tablespace example,

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

HR@ENMOEDU>

5)   表級定義主鍵,自定義主鍵名稱,並且指定索引使用的表空間

HR@ENMOEDU>CREATE TABLE t_pk5

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE,

constraint pk_t_pk5_sno primary key(sno) using index tablespace example

);

 

Table created.

 

HR@ENMOEDU>

6)   檢視剛剛建表的主鍵情況

HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');

 

TABLE_NAME  COLUMN_NAME    CONSTRAINT_NAME

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

T_PK1        SNO             SYS_C0011132

T_PK2        SNO             PK_T_PK2_SNO

T_PK3        SNO             SYS_C0011134

T_PK4        SNO             PK_T_PK4_SNO

T_PK5        SNO             PK_T_PK5_SNO

 

HR@ENMOEDU>

7)   檢視主鍵自動建立索引情況

HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');

 

INDEX_NAME                     UNIQUENES TABLESPACE_NAME

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

SYS_C0011132                   UNIQUE    USERS

PK_T_PK2_SNO                   UNIQUE    USERS

SYS_C0011134                   UNIQUE    USERS

PK_T_PK4_SNO                   UNIQUE    EXAMPLE

PK_T_PK5_SNO                   UNIQUE    EXAMPLE

 

HR@ENMOEDU>

8)   清理環境

HR@ENMOEDU>

drop table t_pk1 purge;

drop table t_pk2 purge;

drop table t_pk3 purge;

drop table t_pk4 purge;

drop table t_pk5 purge;

Table dropped.

Table dropped.

Table dropped.

Table dropped.

Table dropped.

 

HR@ENMOEDU>

9)   建立測試表t_pk6命令列增加主鍵,自定義主鍵名稱

HR@ENMOEDU>CREATE TABLE t_pk6

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

HR@ENMOEDU>alter table t_pk6 add constraint pk_t_pk6_sno primary key (sno);

 

Table altered.

 

HR@ENMOEDU>

10)  建立測試表t_pk7命令列增加主鍵,自定義主鍵名稱,並且指定自動建立索引的表空間

HR@ENMOEDU>CREATE TABLE t_pk7

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

 

HR@ENMOEDU>

HR@ENMOEDU>

HR@ENMOEDU>alter table t_pk7 add constraint pk_t_pk7_sno primary key (sno)using index tablespace example;

 

Table altered.

 

HR@ENMOEDU>

11)  建立測試表t_pk8命令列增加主鍵,自定義主鍵名稱,並且指定建立索引的名稱、型別及表空間

HR@ENMOEDU>CREATE TABLE t_pk8

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

HR@ENMOEDU>

HR@ENMOEDU>alter table t_pk8 add constraint pk_t_pk8_sno primary key (sno) using index (create unique index un_t_pk8_sno on t_pk8(sno) tablespace example);

 

Table altered.

 

HR@ENMOEDU>

12)  建立測試表t_pk6t_pk7t_pk8,主鍵情況

HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');

 

TABLE_NAME  COLUMN_NAME   CONSTRAINT_NAME

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

T_PK6        SNO             PK_T_PK6_SNO

T_PK7        SNO             PK_T_PK7_SNO

T_PK8        SNO             PK_T_PK8_SNO

 

HR@ENMOEDU>

13)  建立測試表t_pk6t_pk7t_pk8索引情況

HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');

 

INDEX_NAME      UNIQUENES TABLESPACE_NAME

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

PK_T_PK6_SNO    UNIQUE    USERS

PK_T_PK7_SNO    UNIQUE    EXAMPLE

UN_T_PK8_SNO    UNIQUE    EXAMPLE

 

HR@ENMOEDU>

14)  主鍵的索引並一定都是唯一索引,也可以是普通的B*tree索引

HR@ENMOEDU>CREATE TABLE t_pk9

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE

);

 

Table created.

 

HR@ENMOEDU>alter table t_pk9 add constraint pk_t_pk9_sno primary key (sno) using index (create index un_t_pk9_sno on t_pk9(sno) tablespace example);

 

Table altered.

 

HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');

 

INDEX_NAME      UNIQUENES TABLESPACE_NAME

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

PK_T_PK6_SNO    UNIQUE    USERS

PK_T_PK7_SNO    UNIQUE    EXAMPLE

UN_T_PK6_SNO    UNIQUE    EXAMPLE

UN_T_PK9_SNO    NONUNIQUE EXAMPLE

 

HR@ENMOEDU>

3.4.  FOREIGN KEY

外來鍵約束

語法:

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

      REFERENCES departments(department_id)

                 [ON DELETE {CASCADE|SET NULL}]

   FOREIGN KEY: 在表約束級別,定義子表中的列。

   REFERENCES: 確定父表中的表和列。

   ON DELETE CASCADE: 當父表中的行被刪除時,刪除子表中相依賴的行(級聯刪除)

   ON DELETE SET NULL: 當父表的值被刪除時,轉換外來鍵值為空。

 

1)   清理測試環境

HR@ENMOEDU>drop table student purge;

 

Table dropped.

 

HR@ENMOEDU>

2)   建立籍貫表,插入測試資料

HR@ENMOEDU>create table nativeplace

(

nid number(2) primary key,      --主鍵id

np_name varchar2(20) not null   --籍貫名稱

);

 

Table created.

 

HR@ENMOEDU>insert into nativeplace values(1,'heilongjiang');

 

1 row created.

 

HR@ENMOEDU>insert into nativeplace values(2,'jilin');

 

1 row created.

 

HR@ENMOEDU>insert into nativeplace values(3,'liaoning');

 

1 row created.

 

 

HR@ENMOEDU>insert into nativeplace values(4,'beijing');

 

1 row created.

 

HR@ENMOEDU>commit;

HR@ENMOEDU>select * from nativeplace;

 

       NID NP_NAME

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

         1 heilongjiang

         2 jilin

         3 liaoning

         4 beijing

 

HR@ENMOEDU>

3)   建立學生表,插入測試資料

HR@ENMOEDU>CREATE TABLE student

(

sno NUMBER(5) constraint pk_student primary KEY using index, --學生編號(主鍵)

sname VARCHAR2(20) not null,                                 --學生名稱

idcard VARCHAR2(18) not null,                                --身份證號

createtime DATE default SYSDATE not null,                  --建立時間

native_place number(2),                                      --籍貫id

constraint fk_student foreign key (native_place) REFERENCES nativeplace (nid) ON DELETE SET NULL

);

 

Table created.

 

HR@ENMOEDU>

insert into student values(1,'zhangsan','123456789123456780',default,1);

 

1 row created.

 

HR@ENMOEDU>

insert into student values(2,'lisi','123456789123456781',default,2);

 

1 row created.

 

HR@ENMOEDU>

insert into student values(3,'wangwu','123456789123456782',default,3);

 

1 row created.

 

HR@ENMOEDU>

insert into student values(4,'zhaoliu','123456789123456783',default,4);

 

1 row created.

 

HR@ENMOEDU>col sno for 999

HR@ENMOEDU>col sname for a10

HR@ENMOEDU>select * from student;

 

 SNO SNAME      IDCARD             CREATETIME          NATIVE_PLACE

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

   1 zhangsan   123456789123456780 2012-09-14 16:06:06            1

   2 lisi       123456789123456781 2012-09-14 16:06:10            2

   3 wangwu     123456789123456782 2012-09-14 16:06:13            3

   4 zhaoliu    123456789123456783 2012-09-14 16:06:16            4

 

HR@ENMOEDU>

4)   建立課程表,插入測試資料

HR@ENMOEDU>create TABLE course

(

cno NUMBER(5) CONSTRAINT pk_course PRIMARY KEY NOT NULL,  --課程編號(主鍵)

cname VARCHAR2(20) NOT NULL                               --課程名稱

);

 

Table created.

 

HR@ENMOEDU>insert into course VALUES(1,'Oracle');

 

1 row created.

 

HR@ENMOEDU>insert into course VALUES(2,'Java');

 

 

1 row created.

 

HR@ENMOEDU>insert into course VALUES(3,'C++');

 

1 row created.

 

HR@ENMOEDU>select * from course;

 

       CNO CNAME

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

         1 Oracle

         2 Java

         3 C++

 

HR@ENMOEDU>

5)   建立成績表,插入測試資料

HR@ENMOEDU>create TABLE sc

(

sno NUMBER(5),       --學生編號

cno NUMBER(5),       --課程編號

grade NUMBER(4,1),  --成績

constraint pk_sc primary key (sno,cno),

constraint fk_sc_sno foreign key (sno) REFERENCES student (sno) ON DELETE CASCADE,

constraint fk_sc_cno foreign key (cno) REFERENCES course (cno)

);

 

Table created.

 

HR@ENMOEDU>

insert into sc values(1,1,91);

insert into sc values(1,2,92);

insert into sc values(1,3,93);

insert into sc values(2,1,88);

insert into sc values(2,2,77);

insert into sc values(2,3,99);

insert into sc values(3,1,65);

insert into sc values(3,2,75);

insert into sc values(3,3,85);

insert into sc values(4,1,80);

insert into sc values(4,2,88);

insert into sc values(4,3,89);

HR@ENMOEDU>commit;

Commit complete.

 

HR@ENMOEDU>select * from sc;

 

 SNO       CNO      GRADE

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

   1          1         91

   1          2         92

   1          3         93

   2          1         88

   2          2         77

   2          3         99

   3          1         65

   3          2         75

   3          3         85

   4          1         80

   4          2         88

   4          3         89

 

12 rows selected.

 

HR@ENMOEDU>

6)   測試,子表插入父表不存在的資料的時候,報錯的效果往成績表中插入資料,學生編號不存在

HR@ENMOEDU>insert into sc values(5,1,100);

insert into sc values(5,1,100)

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_SC_SNO) violated - parent key not

found

 

HR@ENMOEDU>

7)   往成績表中插入資料,課程編號不存在

HR@ENMOEDU>insert into sc values(1,4,100);

insert into sc values(1,4,100)

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_SC_CNO) violated - parent key not

found

 

HR@ENMOEDU>

8)   開始測試,驗證set null,檢視錶nativeplace

HR@ENMOEDU>select * from nativeplace;

 

       NID NP_NAME

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

         1 heilongjiang

         2 jilin

         3 liaoning

         4 beijing

 

HR@ENMOEDU>

9)   檢視錶student

HR@ENMOEDU>select * from student;

 

 SNO SNAME      IDCARD             CREATETIME          NATIVE_PLACE

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

   1 zhangsan   123456789123456780 2012-09-14 16:06:06            1

   2 lisi       123456789123456781 2012-09-14 16:06:10            2

   3 wangwu     123456789123456782 2012-09-14 16:06:13            3

   4 zhaoliu    123456789123456783 2012-09-14 16:06:16            4

 

HR@ENMOEDU>

10)  刪除籍貫表中nid3的記錄

HR@ENMOEDU>delete nativeplace where nid=3;

 

1 row deleted.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>

11)  檢視籍貫表,nid3的記錄已經刪除

HR@ENMOEDU>select * from nativeplace;

 

       NID NP_NAME

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

         1 heilongjiang

         2 jilin

         4 beijing

 

HR@ENMOEDU>

12)  檢視學生表,此時之前籍貫為3的籍貫id已經變為null,此時,外來鍵約束set null起了作用

HR@ENMOEDU>select * from student;    

 

 SNO SNAME      IDCARD             CREATETIME          NATIVE_PLACE

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

   1 zhangsan   123456789123456780 2012-09-14 16:06:06            1

   2 lisi       123456789123456781 2012-09-14 16:06:10             2

   3 wangwu     123456789123456782 2012-09-14 16:06:13

   4 zhaoliu    123456789123456783 2012-09-14 16:06:16            4

 

 

13)  驗證級聯刪除

HR@ENMOEDU>select * from student;

 

SNO SNAME      IDCARD             CREATETIME          NATIVE_PLACE

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

   1 zhangsan   123456789123456780 2012-09-14 16:06:06            1

   2 lisi       123456789123456781 2012-09-14 16:06:10            2

   3 wangwu     123456789123456782 2012-09-14 16:06:13

   4 zhaoliu    123456789123456783 2012-09-14 16:06:16            4

 

HR@ENMOEDU>select * from sc;

 

 SNO        CNO  GRADE

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

   1          1         91

   1          2         92

   1          3         93

   2          1         88

   2          2         77

   2          3         99

   3          1         65

   3          2         75

   3          3         85

   4          1         80

   4          2         88

   4          3         89

 

12 rows selected.

 

HR@ENMOEDU>

14)  刪除學生表中,學號為1的記錄,查詢驗證,學號為1的記錄已經刪除

HR@ENMOEDU>delete student where sno=1;

 

1 row deleted.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>select * from student;

 

 SNO SNAME     IDCARD             CREATETIME          NATIVE_PLACE

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

   2 lisi       123456789123456781 2012-09-14 16:06:10            2

   3 wangwu     123456789123456782 2012-09-14 16:06:13

   4 zhaoliu    123456789123456783 2012-09-14 16:06:16            4

 

HR@ENMOEDU>

15)  檢視成績表,此時發現,學號為1的學生的成績已經全部刪除,此時外來鍵的級聯刪除起了作用

HR@ENMOEDU>select * from sc;

 

 SNO        CNO     GRADE

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

   2          1         88

   2          2         77

   2          3         99

   3          1         65

   3          2         75

   3          3         85

   4          1         80

   4          2         88

   4          3         89

 

9 rows selected.

 

HR@ENMOEDU>

16)  驗證預設約束規則,提示無法刪除

HR@ENMOEDU>delete course where cno=1;

delete course where cno=1

*

ERROR at line 1:

ORA-02292: integrity constraint (HR.FK_SC_CNO) violated - child record found

 

 

HR@ENMOEDU>

3.5.  CHECK

1)   刪除環境中原來的學生表

HR@ENMOEDU>drop table student1 purge;

 

Table dropped.

 

HR@ENMOEDU>

建立學生表,要求,建表的同時,建立兩個check,一個保證身份證內容都為小寫,一個是保證所有學生的名字都為大寫

HR@ENMOEDU>CREATE TABLE student1

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18) check (idcard=lower(idcard)),

createtime DATE,

CONSTRAINT ck_student_sname CHECK (sname=upper(sname))

) ;

 

Table created.

 

HR@ENMOEDU>

2)   插入測試資料,驗證check約束

HR@ENMOEDU>insert into student1 values(1,'AA','123a',sysdate);

 

1 row created.

 

HR@ENMOEDU>insert into student1 values(2,'aa','123a',sysdate);

insert into student1 values(2,'aa','123a',sysdate)

*

ERROR at line 1:

ORA-02290: check constraint (HR.CK_STUDENT_SNAME) violated

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>

 

問:

下面語句是否能夠執行成功?

insert into student1 values(1,null,null,sysdate);可以,null空值是不受check約束的

 

注:下面兩個建表指令碼是不被允許的。

Drop table student1 purge;

CREATE TABLE student1

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE check(createtime <sysdate)

);

 

CREATE TABLE student1

(

sno NUMBER(5),

sname VARCHAR2(18) check(sname=idcard),

idcard VARCHAR2(18),

createtime DATE

);

如下表示式在check是不被允許的

  - 涉及到 CURRVAL, NEXTVAL, LEVEL ROWNUM 偽列

  - 呼叫 SYSDATE, UID, USER USERENV 函式

  - 涉及其它行中其它值的查詢


 

 

  第4章  建立檢視

語法:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

 

OR   REPLACE

如果檢視已經存在重新建立它

FORCE

建立檢視,而不管基表是否存在

NOFORCE

只在基表存在的情況下建立檢視(這是預設值)

View

檢視的名字

Alias

為由檢視查詢選擇的表示式指定名字(別名的個數必須與由檢視選擇的表示式的個數匹配)

Subquery

是一個完整的 SELECT 語句(對於在 SELECT 列表中的欄位你可以用別名)

WITH   CHECK   OPTION

指定只有可訪問的行在檢視中才能被插入或修改

Constraint

CHECK OPTION 約束指定的名字

WITH   READ   ONLY

確保在該檢視中沒有 DML 操作被執行

 

如果基表不存在,那麼

Force 可以建立成功,但會提示編譯錯誤

Noforce 直接提示表或檢視不存在

4.1.  簡單檢視、複雜檢視區別及測試

特性

簡單檢視

複雜檢視

表的數目

一個

一個或多個

包含函式

包含資料分組

透過檢視進行DML操作

不允許

1)   建立學生表

HR@ENMOEDU>CREATE TABLE student

(

sno NUMBER(5),

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE default SYSDATE not null,

native_place number(2)

);

2)   學生表插入資料

insert into student values(1,'zhangsan','123456789123456780',default,1);

insert into student values(2,'lisi','123456789123456781',default,2);

insert into student values(3,'wangwu','123456789123456782',default,3);

insert into student values(4,'zhaoliu','123456789123456783',default,4);

3)   建立簡單檢視和複雜檢視   grant create view to scott;

簡單檢視:

HR@ENMOEDU>create view vw_student1 as select sno,sname,createtime from student;

 

View created.

 

HR@ENMOEDU>create view vw_student2 as select sno,sname,createtime,sysdate as dd from student;

 

View created.

 

複雜檢視:

HR@ENMOEDU>create view vw_student3 as select sno,sname,to_char(createtime,'yyyy-mm-dd') as ctime from student;

 

View created.

 

HR@ENMOEDU>create view vw_student4 as select native_place,count(*) as num from student group by native_place;

 

View created.

 

HR@ENMOEDU>

4)   插入資料測試

HR@ENMOEDU>insert into vw_student1 values(5,'test',sysdate);

 

1 row created.

 

HR@ENMOEDU>insert into vw_student2(sno,sname,createtime) values(6,'test1',sysdate);

 

1 row created.

HR@ENMOEDU>insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'));

insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'))

                                  *

ERROR at line 1:

ORA-01733: virtual column not allowed here

 

 

HR@ENMOEDU>insert into vw_student4 values(1,1);

insert into vw_student4 values(1,1)

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

 

 

HR@ENMOEDU>

4.2.  WITH CHECK OPTION

檢視中的check約束

1)   建表、插入測試資料

HR@ENMOEDU>drop table student purge;

 

Table dropped.

 

HR@ENMOEDU>CREATE TABLE student

(

sno NUMBER(5) constraint pk_student primary KEY using index,

sname VARCHAR2(20),

idcard VARCHAR2(18),

createtime DATE default SYSDATE,

native_place number(2)

);

 

Table created.

 

HR@ENMOEDU>

insert into student values(1,'zhangsan','123456789123456780',default,1);

insert into student values(2,'lisi','123456789123456781',default,2);

insert into student values(3,'wangwu','123456789123456782',default,3);

insert into student values(4,'zhaoliu','123456789123456783',default,2);

insert into student values(5,'zhaosi','123456789123456783',default,2);

insert into student values(6,'maba','123456789123456783',default,2);

commit;

2)   建立沒有約束的簡單檢視

HR@ENMOEDU>create or replace view vw_student7

as

select sno,sname,idcard,native_place

from student where native_place=2;

 

View created.

 

HR@ENMOEDU>

3)   建立有約束的簡單檢視with check option,即指定只有可訪問的行在檢視中才能被插入或修改

HR@ENMOEDU>create or replace view vw_student8

as

select sno,sname,idcard,native_place

from student where native_place=2

with check option;

 

View created.

 

HR@ENMOEDU>

4)   插入資料測試

HR@ENMOEDU>

insert into vw_student8 values(7,'zhu88','123456789123456783',3);

insert into vw_student8 values(7,'zhu88','123456789123456783',3)

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

 

 

HR@ENMOEDU>

insert into vw_student7 values(7,'zhu88','123456789123456783',3);

 

1 row created.

 

HR@ENMOEDU>

insert into vw_student8 values(8,'niu99','123456789123456783',2);

 

1 row created.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>

小結:

1)   當往有約束的簡單檢視中插入不符合約束的資料時,資料庫則會丟擲錯誤,當插入符合約束條件的資料,則插入正常。

2)   當往沒有約束的檢視中插入符合標準的資料時,則可以正常插入

4.3.  WITH READ ONLY

只讀檢視

HR@ENMOEDU>create or replace view vw_student9

as

select sno,sname,idcard,native_place

from student

where native_place=2

with read only;

 

View created.

 

HR@ENMOEDU>

HR@ENMOEDU>insert into vw_student9 values(9,'r_only','123456789123456783',2);

insert into vw_student9 values(9,'r_only','123456789123456783',2)

*

ERROR at line 1:

ORA-42399: cannot perform a DML operation on a read-only view


  第5章  其他資料庫物件

5.1.  序列

(一)語法:

1)   建立序列

CREATE SEQUENCE sequence

       [INCREMENT BY n]

       [START WITH n]

       [{MAXVALUE n | NOMAXVALUE}]

       [{MINVALUE n | NOMINVALUE}]

       [{CYCLE | NOCYCLE}]

       [{CACHE n | NOCACHE}];

sequence

是序列發生器的名字

INCREMENT   BY   n

指定序列號之間的間隔,在這兒 n 是一個整數  (如果該子句被省略,序列增量為 1)

START   WITH   n

指定要產生的第一個序列數  (如果該子句被省略,序列從 1開始)

MAXVALUE   n

指定序列能產生的最大值

NOMAXVALUE

對於升序序列指定 10^27 為最大值,對於降序序列指定-1為最大值  (這是預設選項) 

MINVALUE   n

指定最小序列值

NOMINVALUE

對於升序序列指定 1 為最小值,對於降序序列指定-(10^26)為最小值  (這是預設選項)

CYCLE|NOCYCLE

指定序列在達到它的最大或最小值之後,是否繼續產生 (NOCYCLE 是預設選項)

CACHE   n|NOCACHE

指定 Oracle 伺服器預先分配多少值,並且保持在記憶體中(預設情況下,Oracle 伺服器緩衝 20 個值)

2)   修改序列:

ALTER     SEQUENCE sequence

[INCREMENT   BY   n]

[{MAXVALUE   n   |   NOMAXVALUE}]

[{MINVALUE   n   |   NOMINVALUE}]

[{CYCLE   |   NOCYCLE}]

[{CACHE   n   |   NOCACHE}];

3)   刪除序列:

DROP SEQUENCE sequence;

(二)使用NEXTVALCURRVAL的規則

1)   可以使用nextvalcurrval

?   一個不是子查詢的一部分的 SELECT 語句的 SELECT 列表

?   在一個 INSERT 語句中子查詢的 SELECT 列表

?   一個 INSERT 語句中的 VALUES 子句

?   一個 UPDATE 語句的 SET 子句

2)   不允許使用nextvalcurrval

?   一個檢視的 SELECT 列表

create or replace view vw_a as select seq_test.nextval,1 as id from dual;

?   一個帶 DISTINCT 關鍵字的 SELECT 語句

?   一個帶 GROUP BYHAVING ORDER BY 子句的 SELECT 語句

?   一個在 SELECTDELETE UPDATE 語句中的子句

?   CREATE TABLE ALTER TABLE 語句中的 DEFAULT 表示式

(三)序列測試

HR@ENMOEDU>create sequence SEQ

  minvalue 1

  maxvalue 100

  start with 2

  increment by 1;

 

Sequence created.

--在沒有執行nextval的時候,無法執行currval

HR@ENMOEDU>select seq.currval from dual;

select seq.currval from dual

       *

ERROR at line 1:

ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session

 

 

HR@ENMOEDU>select seq.nextval from dual;

 

   NEXTVAL

----------

2

 

HR@ENMOEDU>select seq.nextval from dual;

 

   NEXTVAL

----------

 

         3

 

HR@ENMOEDU>

新開啟一個會話:

HR@ENMOEDU> select seq.currval from dual;

select seq1.currval from dual

       *

ERROR at line 1:

ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session

 

小結:

1)   在一個新的會話中,不能直接使用currval,必須先nextval後再currval

2)   新建的一個序列,第一次nextval的值為start with的值

(四)序列和觸發器配合應用

HR@ENMOEDU>create table t_seq(nid number(10),name varchar2(10));

 

Table created.

 

HR@ENMOEDU>create sequence seq_t_seq;

 

Sequence created.

 

HR@ENMOEDU>create trigger trg_t_seq

  before insert on t_seq

  for each row

begin

  if :new.nid is null then

    select seq_t_seq.nextval

      into :new.nid

      from dual;

  end if;

end;

/

 

Trigger created.

 

HR@ENMOEDU>insert into t_seq (name) values('a');

 

1 row created.

 

HR@ENMOEDU>insert into t_seq (name) values('b');

 

1 row created.

 

HR@ENMOEDU>

上面的insert語句,有這樣的誤解,nid不是主鍵,也非not null,插入兩條記錄很正常,那麼我們將nid修改為主鍵,如下:

HR@ENMOEDU>alter table t_seq add primary key(nid);

 

Table altered.

 

HR@ENMOEDU>insert into t_seq (name) values('c');

 

1 row created.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>

HR@ENMOEDU>select * from t_seq;

 

       NID NAME

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

         1 a

         2 b

         3 c

 

HR@ENMOEDU>

此時我們發現,主鍵欄位也是有資料的,主鍵的資料就是來源於觸發器,這個是一個比較典型的觸發器利用序列協助寫入主鍵的例子

(五)查詢序列

1)   檢視序列SEQ_T_SEQ

HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';

 

SEQUENCE_NAME  MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER

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

SEQ_T_SEQ              1 1.0000E+28            1 21

 

HR@ENMOEDU>

注:如果指定NOCACHE 選項,那麼LAST_NUMBER顯示序列中下一個有效的值,'SEQ_T_SEQ'這個序列的LAST_NUMBER顯示的就不是下一個有效值

2)   檢視建立SEQ_T_SEQ的指令碼,確認cache

HR@ENMOEDU>set long 999999

HR@ENMOEDU>select dbms_metadata.get_ddl(object_type => 'SEQUENCE',name => 'SEQ_T_SEQ') from dual;

 

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'SEQUENCE',NAME=>'SEQ_T_SEQ')

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

 

   CREATE SEQUENCE  "HR"."SEQ_T_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999

999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE

 

HR@ENMOEDU>

3)   下面舉我們建立一個NOCACHE的例子

HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';

 

SEQUENCE_NAME  MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER

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

SEQ_T_SEQ              1 1.0000E+28            1             21

 

HR@ENMOEDU>create sequence seq_nocache nocache;

 

Sequence created.

 

4)   執行一次sequence,得到的結果為1,下一個值應該是2

HR@ENMOEDU>select seq_nocache.nextval from dual;

 

   NEXTVAL

----------

         1

 

HR@ENMOEDU>

5)   檢視序列SEQ_NOCACHELAST_NUMBER,此時我們發現LAST_NUMBER值為2,也就是下一個有效值

HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,last_number from user_sequences where sequence_name='SEQ_NOCACHE';

 

SEQUENCE_NAME  MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER

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

SEQ_NOCACHE            1 1.0000E+28            1        2

 

HR@ENMOEDU>

(六)刪除序列

HR@ENMOEDU>drop sequence seq_nocache;

 

Sequence dropped.

 

HR@ENMOEDU>

5.2.  索引

(一)自動建立

建立主鍵或唯一鍵約束會在相應欄位上建立唯一索引

1)   建立測試表

HR@ENMOEDU>create table t_a_index(x number,y number);

 

Table created.

 

HR@ENMOEDU>

2)   修改表,增加主鍵主鍵的名字為pk_t_a_index

HR@ENMOEDU>alter table t_a_index add constraint pk_t_a_index primary key(x);

Table altered.

 

HR@ENMOEDU>

3)   修改表,增加唯一鍵,唯一鍵的名字為uk_t_a_index

HR@ENMOEDU>alter table t_a_index add constraint uk_t_a_index unique(y);

 

Table altered.

 

HR@ENMOEDU>

4)   檢視錶T_A_INDEX的相關索引,發現有兩個與約束名稱相同的索引

HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_A_INDEX';

 

INDEX_NAME   INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES

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

UK_T_A_INDEX NORMAL     HR          T_A_INDEX  UNIQUE

PK_T_A_INDEX NORMAL     HR          T_A_INDEX  UNIQUE

 

HR@ENMOEDU>

透過這個測試,我們瞭解到,主鍵和唯一鍵會自動建立唯一索引,索引的名稱與約束的名稱相同

(二)手工建立

在一個或多個列上建立索引,語法:

CREATE INDEX index ON table (column[, column]...);

1)   建立測試表

HR@ENMOEDU>create table t_m_index(x number,y number,z number);

 

Table created.

 

HR@ENMOEDU>

2)   x欄位上建立普通索引

HR@ENMOEDU>create index idx_t_m_index_x on t_m_index(x);

 

Index created.

 

HR@ENMOEDU>

3)   y,z欄位上建立普通複合索引

HR@ENMOEDU>create index idx_t_m_index_yz on t_m_index(y,z) tablespace users;

 

Index created.

 

HR@ENMOEDU>

4)   檢視錶T_M_INDEX相關索引

HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_M_INDEX';

 

INDEX_NAME       INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES

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

IDX_T_M_INDEX_YZ NORMAL     HR          T_M_INDEX  NONUNIQUE

IDX_T_M_INDEX_X  NORMAL     HR          T_M_INDEX  NONUNIQUE

 

HR@ENMOEDU>

注:字典表user_indexesUNIQUENES欄位

comment on column SYS.USER_INDEXES.UNIQUENESS is 'Uniqueness status of the index:  "UNIQUE",  "NONUNIQUE", or "BITMAP"';

5)   檢視T_M_INDEX相關索引及索引欄位

HR@ENMOEDU>SELECT t.index_name, t.column_name, t.column_position col_pos, d.uniqueness FROM user_indexes d, user_ind_columns t

WHERE t.index_name = d.index_name

 AND t.table_name = 'T_M_INDEX';

 

INDEX_NAME       COLUMN_NAME    COL_POS UNIQUENES

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

IDX_T_M_INDEX_X  X                     1 NONUNIQUE

IDX_T_M_INDEX_YZ Y                     1 NONUNIQUE

IDX_T_M_INDEX_YZ Z                     2 NONUNIQUE

 

HR@ENMOEDU>

5.3.  同義詞

同義詞,簡單點兒理解就是一個別名,一個對映的關係。

(一)同義詞的分類

Oracle同義詞有兩種型別,分別是公用Oracle同義詞與私有Oracle同義詞。

1)   公用Oracle同義

由一個特殊的使用者組Public所擁有。顧名思義,資料庫中所有的使用者都可以使用公用同義詞。公用同義詞往往用來標示一些比較普通的資料庫物件,這些物件往往大家都需要引用。

2)   私有Oracle同義詞(預設建立)

它是跟公用同義詞所對應,他是由建立他的使用者所有。當然,這個同義詞的建立者,可以透過授權控制其他使用者是否有權使用屬於自己的私有同義詞。

(二)語法

1)   建立同義詞

create [or replace] [public] synonym [schema.]synonym

for [schema.]object[@dblink];

2)   刪除同義詞

drop [public] synonym [schema.]synonym [force];

(三)先決條件

1)   要自己的schema下建立私有的同義詞,你必須擁有CREATE SYNONYM系統許可權

2)   要在其他schema下建立一個私有同義詞,你必須有CREATE ANY SYNONYM系統許可權

3)   建立一個公共的同義詞,你必須有CREATE PUBLIC SYNONYM系統許可權

(四)私有同義詞測試

1)   scott下想檢視hr下的employees,沒有許可權檢視,如果不想直接把employees的查詢許可權給scott,那麼可以考慮建立一個同義詞

SYS@ENMOEDU> create user scott identified by scott ;

User created.

SYS@ENMOEDU> grant connect,resource,create synonym to scott;

Grant succeeded.

SYS@ENMOEDU> conn scott/scott

Connected.

 

SCOTT@ENMOEDU> select count(*) from hr.employees;

select count(*) from hr.employees

                        *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SCOTT@ENMOEDU>

2)   連線到scott使用者下建立hr使用者下的employees的同義詞syn_employees

SCOTT@ENMOEDU> conn hr/hr

Connected.

HR@ENMOEDU> grant select on employees to scott;

 

Grant succeeded.

HR@ENMOEDU> conn scott/tiger

Connected.

SCOTT@ENMOEDU>create synonym syn_employees for hr.employees;

 

create synonym syn_employees for hr.employees

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

 

SCOTT@ENMOEDU >conn / as sysdba

Connected.

SYS@ENMOEDU >grant create any synonym to scott;

 

Grant succeeded.

 

SYS@ENMOEDU >conn scott/tiger;

Connected.

SCOTT@ENMOEDU >create synonym syn_employees for hr.employees;

 

Synonym created.

 

3)   scott使用者下查詢hr下的同義詞

HR@ENMOEDU> conn scott/tiger

Connected.

SCOTT@ENMOEDU> select count(*) from syn_employees;

 

  COUNT(*)

----------

       107

SCOTT@ENMOEDU>

(五)公共同義詞測試

1)   sys使用者下建立公共同義詞dept

SYS@ENMOEDU>create or replace public synonym dept for hr.departments;

 

Synonym created.

 

SYS@ENMOEDU>

2)   連線到scott使用者測試公共同義詞是否可以查詢

查詢結果為表或檢視不存在,之所以報這個錯誤,是因為scott沒有查詢hr.departments的許可權

SYS@ENMOEDU> conn scott/scott

Connected.

SCOTT@ENMOEDU> select * from dept;

select * from dept

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SCOTT@ENMOEDU>

3)   我們到擁有DBA許可權的system使用者可以檢視到dept同義詞

SCOTT@ENMOEDU> conn system/oracle

Connected.

 

SYSTEM@ENMOEDU>select count(*) from dept;

 

  COUNT(*)

----------

27

 

SYSTEM@ENMOEDU>

4)   我們可以將同義詞departmentsselect許可權賦予public

SYSTEM@ENMOEDU> conn hr/hr

Connected.

HR@ENMOEDU>grant select on departments to public;

 

Grant succeeded.

 

HR@ENMOEDU>

5)   我們再回到scott使用者,此時就可以查詢了

HR@ENMOEDU> conn scott/scott

Connected.

SCOTT@ENMOEDU> select count(*) from dept;

 

  COUNT(*)

----------

        27

SCOTT@ENMOEDU>

其實規則是這樣的

-     首先檢視發出命令的使用者對錶或者檢視是否存在並且有訪問許可權

-     如果表或檢視不存在,則檢查私有同義詞

-     如果私有同義詞不存在,則訪問公共同義詞

-     如果公共同義詞仍不存在,Oracle返回訊息“ORA-00942 table or view does not exist”.


 

 

   第6章  使用集合運算

6.1.  UNION

A集合和B集合的合併,去掉兩集合重複的部分並且排序

HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,20);

 

DEPARTMENT_ID FIRST_NAME

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

           10 Jennifer

           20 Michael

           20 Pat

 

HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,30);

 

DEPARTMENT_ID FIRST_NAME

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

           10 Jennifer

           30 Den

           30 Alexander

           30 Shelli

           30 Sigal

           30 Guy

           30 Karen

 

7 rows selected.

 

HR@ENMOEDU>

select department_id,first_name from employees where department_id in (10,20)

union

 select department_id,first_name from employees where department_id in (10,30);

 

DEPARTMENT_ID FIRST_NAME

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

           10 Jennifer

           20 Michael

           20 Pat

           30 Alexander

           30 Den

           30 Guy

           30 Karen

           30 Shelli

           30 Sigal

 

9 rows selected.

 

HR@ENMOEDU>

6.2.  UNION ALL

A集合和B集合的合併不去重,不排序

HR@ENMOEDU>

select department_id,first_name from employees where department_id in (10,20)

union all

 select department_id,first_name from employees where department_id in (10,30);

 

 

DEPARTMENT_ID FIRST_NAME

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

           10 Jennifer

           20 Michael

           20 Pat

10 Jennifer

           30 Den

           30 Alexander

           30 Shelli

           30 Sigal

           30 Guy

           30 Karen

 

10 rows selected.

 

HR@ENMOEDU>

6.3.  INTERSECT

兩個集合交集部分排序並去重

HR@ENMOEDU>

select department_id,first_name from employees where department_id in (10,20)

intersect

 select department_id,first_name from employees where department_id in (10,30);

 

DEPARTMENT_ID FIRST_NAME

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

           10 Jennifer

 

HR@ENMOEDU>

6.4.  MINUS

取兩個集合差集返回A集合中存在,B集合中不存在的資料

HR@ENMOEDU>

select department_id,first_name from employees where department_id in (10,20)

minus

 select department_id,first_name from employees where department_id in (10,30);

 

DEPARTMENT_ID FIRST_NAME

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

           20 Michael

           20 Pat

 

HR@ENMOEDU>

6.5.  UNION/UNION ALL注意事項

HR@ENMOEDU>create table t_union1(col number);

 

Table created.

 

HR@ENMOEDU>

HR@ENMOEDU>create table t_union2(col  number);

 

Table created.

 

HR@ENMOEDU>

思考題:

在先不考慮t_union1t_union2兩張表中的資料的情況下,請問如下兩個sql的結果是否相同相同

 

SQL1

Select 1 as type,col from t_union1

Union

Select 2,col from t_union2;

SQL2

Select 1 as type,col from t_union1

Union all

Select 2,col from t_union2;

1)   測試:

HR@ENMOEDU>insert into t_union1 values(1);

 

1 row created.

 

HR@ENMOEDU>insert into t_union1 values(1);

 

1 row created.

 

HR@ENMOEDU>insert into t_union2  values(1);

 

1 row created.

 

HR@ENMOEDU>insert into t_union2  values(2);

 

1 row created.

 

HR@ENMOEDU>commit;

 

Commit complete.

 

HR@ENMOEDU>select * from t_union1;

 

       COL

----------

         1

         1

 

HR@ENMOEDU>select * from t_union2;

 

       COL

----------

         1

         2

 

HR@ENMOEDU>select 1 as type,col from t_union1;

 

      TYPE        COL

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

         1          1

         1          1

 

HR@ORA11GR2>select 2 as type,col from t_union2;

 

      TYPE        COL

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

         2          1

         2          2

 

HR@ENMOEDU>Select 1 as type,col from t_union1

Union

Select 2,col from t_union2;

 

      TYPE        COL

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

         1          1

         2          1

         2          2

 

HR@ENMOEDU>Select 1 as type,col from t_union1

Union all

Select 2,col from t_union2;

 

      TYPE        COL

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

         1          1

         1          1

         2          1

         2          2

 

HR@ENMOEDU>

2)   小結

    透過上面的測試我們發現,雖然有常量欄位type,但是這兩個結果集還是不一樣的,因為union的去重操作不單單是兩個結果集之間的去重而是整個結果集的一個去除重複的動作(即先是兩個結果集的分別去重,然後再兩個結果集的合併,最後再去重排序),所以,我們在使用unionunion all的時候要格外注意,特別是在效能最佳化的時候,不要不假思索的把union替換成union all

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2140204/,如需轉載,請註明出處,否則將追究法律責任。

相關文章