OCP課程11：SQL之使用DDL語句建立和管理表

資料庫物件：

表名和列名命名規則：

• 字母開頭
• 不超過30個字元
• 只能包含大小寫字母，0-9，_,$,#
• 同一使用者下物件名稱不能重複
• 不能使用保留字

 

 

1、建立表

• 必須要有建立表的許可權及儲存區域
• 需要指定表名，列名，列資料型別及長度

訪問其他使用者的表需要加上使用者名稱字首。

建立表的時候可以為欄位指定預設值，具體用法如下：

• 字變數，表示式或者SQL函式是合法的預設值
• 其他欄位名稱或者偽列是非法的預設值
• 預設值的型別要與欄位的型別一致
• 插入的時候default表示使用預設值

 

例子：建立一個入職時間表，其中入職時間欄位使用系統日期為預設值

SQL> create table hire_dates(

  2  id number(8),

  3  hire_date date default sysdate);

Table created.

 

例子：建立一個dept表，其中建立時間欄位使用系統日期為預設值

SQL> create table dept(

  2  deptno number(2),

  3  dname varchar2(14),

  4  loc varchar2(13),

  5  create_date date default sysdate);

Table created.

使用desc檢視錶的結構

SQL> desc dept;

Name                                      Null?    Type

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

DEPTNO                                             NUMBER(2)

DNAME                                              VARCHAR2(14)

LOC                                                VARCHAR2(13)

CREATE_DATE                                        DATE

 

 

2、資料型別

其中：

• 透過子查詢建立的表，long型別列不能被複制
• long型別列不能位於group by和order by子句中
• 一個表中只能有一個long型別列
• long型別列上面不能加約束
• CLOB是不能指定大小

 

日期時間資料型別

 

timestamp語法：

其中：

• timestamp包含年月日時分秒及微秒，他是date型別的一個擴充套件
• timestamp with time zone帶有時區
• timestamp with local time zone根據當前session的時區進行變化

 

interval語法：

其中：

interval year to month表示間隔多少年和月

interval day to second表示間隔多少天、小時、分鐘和秒

 

例子：建立一個藥品表，包含保質期欄位

SQL> create table drug(

  2  name varchar2(20),

  3  grantee_time interval year to month);

Table created.

檢視錶結構，可以看到精度預設為2位

SQL> desc drug

Name                                      Null?    Type

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

NAME                                               VARCHAR2(20)

GRANTEE_TIME                                       INTERVAL YEAR(2) TO MONTH

插入資料，其中'10-2'表示10年2個月

SQL> insert into drug values('aspinlin','10-2');

1 row created.

檢視到期時間

SQL> select name,sysdate,sysdate+grantee_time from drug;

NAME                 SYSDATE      SYSDATE+GRAN

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

aspinlin             27-OCT-15    27-DEC-25

 

interval舉例：

 

 

3、約束

約束是表級上的一個規則，阻止我們對錶依賴性的破壞，約束型別：

約束使用指導：

• 可以命名一個約束，也可以不指定，使用Oracle自動產生的以sys_c開頭的名字
• 既可以在建立表的時候建立約束，也可以在建立表之後再建立約束
• 既可以在表級建立約束，也可以在列級建立約束，一次只能指定一個約束
• 複合約束（包含多列）需在表級建立
• 可以透過資料字典檢視約束

 

定義約束語法：

 

建立列級約束：

 

建立表級約束：

 

例子：建立列級約束

SQL> create table emp1(

  2  employee_id number(6) constraint emp1_id_pk primary key,

  3  first_name varchar2(20));

Table created.

 

例子：建立表級約束

SQL> create table emp2(

  2  employee_id number(6),

  3  first_name varchar2(20),

  4  job_id varchar2(10) not null,

  5  constraint emp2_id_pk primary key(employee_id));

Table created.

 

（1）非空約束（not null）

• 欄位的值不能為null
• 只能在列級建立，不能在表級建立

例子：非空約束建立在表級會報錯

SQL> create table emp3(

  2  employee_id number(6),

  3  first_name varchar2(20),

  4  job_id varchar2(10) not null,

  5  constraint emp2_name_nn not null(first_name));

constraint emp2_name_nn not null(first_name))

                        *

ERROR at line 5:

ORA-00904: : invalid identifier

 

（2）唯一約束（unique）

• 欄位的值不能相同
• 但是可以為null
• 可以建立在列級，也可以建立在表級
• 唯一約束隱式建立唯一索引

例子：建立唯一約束

SQL> create table emp4(

  2  employee_id number(6) unique,

  3  email varchar2(25),

  4  constraint emp4_email_uk unique(email));

Table created.

建立了2個唯一約束，一個列級，沒有指定名字，一個表級，指定了名字。

插入null

SQL> insert into emp4 values(null,null);

1 row created.

SQL> select * from emp4;

EMPLOYEE_ID EMAIL

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

SQL> select count(*) from emp4;

  COUNT(*)

----------

         1

 

（3）主鍵約束（primary key）

• 欄位的值不能為null，也不能相同
• 一張表只能有一個主鍵約束
• 主鍵約束隱式建立唯一索引

 

（4）外來鍵約束（foreign key）

• 列級建立不需要foreign key關鍵字，表級建立必須指定，使用references關鍵字指定父表及參考列，如果父表有主鍵，則可以不指定參考列，預設為主鍵，如果父表沒有主鍵，則必須指定參考列
• 子表外來鍵的值必須存在於父表中或者為null
• 子表的外來鍵列須是父表的主鍵或唯一鍵

• 為子表的列增加外來鍵約束後，預設情況下不允許刪除或更新父表相關列值，以確儲存參照完整性
• on delete cascade允許刪除父表相關記錄，同時子表對應記錄也將被刪除
• on delete set null允許刪除父表相關記錄，同時子表對應記錄被置為null

例子：建立外來鍵

SQL> create table emp5(

  2  employee_id number(8) primary key,

  3  manager_id number(8) constraint emp5_emp5_fk references emp5(employee_id),

  4  department_id number(4),

  5  constraint emp5_dept_fk foreign key(department_id) references departments(department_id));

Table created.

建立了2個外來鍵，一個在列級建立，參考自己的主鍵，一個在表級建立，參考departments表的主鍵，由於都有主鍵，都可以不指定父表的參考列。

例子：參照完整性

先建立2個表，emp6的deptid列參考dept6的deptid列

SQL> create table dept6(

  2  deptid number constraint dept6_id_pk primary key,

  3  deptname varchar2(20));

Table created.

SQL> create table emp6(

  2  empid number,

  3  last_name varchar2(20),

  4  deptid number,

  5  constraint emp6_id_pk primary key(empid),

  6  constraint emp6_dept6_fk foreign key(deptid) references dept6);

Table created.

現在2個表沒有資料，先向表emp6插入資料

SQL> insert into emp6 values(100,'aaa',10);

insert into emp6 values(100,'aaa',10)

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP6_DEPT6_FK) violated - parent key not

found

報錯，提示違法約束完整性，沒有找到父鍵，這裡插入的deptid的值為10，但是在表dept6裡面沒有deptid為10的記錄，嚮往表dept6裡面插入一條記錄。

SQL> insert into dept6 values(10,'sales');

1 row created.

再次執行剛才插入表emp6的語句就可以了

SQL> insert into emp6 values(100,'aaa',10);

1 row created.

那麼現在來刪除父表dept6的這條記錄

SQL> delete from dept6 where deptid=10;

delete from dept6 where deptid=10

*

ERROR at line 1:

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

found

報錯，提示違法約束完整性，在子表找到相應的記錄。

先刪除約束再重新建立，增加on delete cascade關鍵字

SQL> alter table emp6 drop constraint emp6_dept6_fk;

Table altered.

SQL> alter table emp6 modify(deptid constraint emp6_dept6_fk references dept6 on delete cascade);

Table altered.

再次來刪除父表dept6的這條記錄就可以了，同時子表的這條記錄也會被刪除，先看一下刪除之前表的記錄。

SQL> select * from emp6;

     EMPID LAST_NAME                DEPTID

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

       100 aaa                          10

SQL> select * from dept6;

    DEPTID DEPTNAME

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

        10 sales

執行刪除

SQL> delete from dept6 where deptid=10;

1 row deleted.

再看一下這2張表的記錄，emp6D的記錄也被級聯刪除了

SQL> select * from emp6;

no rows selected

SQL> select *  from dept6;

no rows selected

 

（5）check約束

• 定義一個欄位必須滿足的條件
• 不允許使用如下偽列：currval,nextval,level,rownum
• 不允許使用如下函式：sysdate,uid,user,userenv
• 不允許參照其他行的值
• 可以在表級也可以在欄位級定義

例子：建立一個check約束，薪水必須大於0

SQL> create table emp7(

  2  employee_id number,

  3  salary number constraint emp7_salary_ck check(salary>0));

Table created.

插入一條薪水小於0的記錄，報錯，違法check約束

SQL> insert into emp7 values(100,-2000);

insert into emp7 values(100,-2000)

*

ERROR at line 1:

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

 

 

4、使用子查詢建立表

• 可以使用AS關鍵字加子查詢建立表並插入資料
• 表的欄位要和子查詢的欄位進行匹配，個數相等，型別相同
• 表後面定義了欄位名字，那麼子查詢裡面的表示式就不要定義別名，如果表後面沒有定義，那麼子查詢裡面的表示式就必須定義別名
• 列資料型別和非空約束將會繼承到新表，其他約束不繼承

 

例子：使用子查詢建立表，單獨使用表示式報錯

SQL> create table dept80

  2  as

  3  select employee_id,last_name,salary*12,hire_date

  4  from employees

  5  where department_id=80;

select employee_id,last_name,salary*12,hire_date

                                   *

ERROR at line 3:

ORA-00998: must name this expression with a column alias

表後面定義了欄位名字就可以了

SQL> create table dept80(empid,name,annsal,hire_date)

  2  as

  3  select employee_id,last_name,salary*12,hire_date

  4  from employees

  5  where department_id=80;

Table created.

表後面不定義欄位名字，直接在子查詢裡面定義別名也可以

SQL> drop table dept80;

Table dropped.

SQL> create table dept80

  2  as

  3  select employee_id,last_name,salary*12 annsal,hire_date

  4  from employees

  5  where department_id=80;

Table created.

 

 

5、修改表

使用alter table語句可以：

• 增加欄位
• 修改欄位型別，長度，約束
• 定義一個欄位的預設值
• 刪除一個欄位

只讀表：

• 只讀表不允許進行DML操作
• 只讀表可以進行不影響表中資料的DDL操作
• 只讀表可以對索引進行操作

SQL> alter table emp read only;

Table altered.

SQL> alter table emp read write;

Table altered.

 

 

6、刪除表

所有資料和表結構都被刪除

提交所有未完成的事務

所有的索引和約束都被刪除

drop table語句不能回滾

例子：刪除dept80表

SQL> drop table dept80;

Table dropped.

 

 

7、總結

這章主要講了

• 使用create table建立表和約束
• 資料型別