SQL 基礎之DDL語句創建和管理表（十四）

數據庫對象

表名和列名注意事項:

• 必須以字母開頭

• 必須在 1–30 個字符之間

• 必須只能包含 A–Z, a–z, 0–9, _, $, 和 #

• 必須不能和用戶定義的其他對象重名

• 必須不能是Oracle的保留字

• 表的列的數量最多1000個

CREATE TABLE  語句

必須具有：

– CREATE TABLE 權限

– 存儲空間

必須指定：

– 表名

– 字段名稱，字段數據類型和字段大小

語法：

CREATE TABLE [schema.]table_name (column datatype [DEFAULT expr][, ...]);

引用其他用戶的表

• 其他用戶定義的表不在當前用戶的方案中

• 應該使用用戶名作為前綴，引用其他用戶定義的對象

DEFAULT  選項

在插入的過程中，為列指定一個默認值。

... hire_date DATE DEFAULT SYSDATE, ...

字符串, 算數表達式, 或SQL 函數都是合法的

其它列的列名和 偽列 是非法的

默認值必須滿足列的數據類型定義

例如：CREATE TABLE hire_dates  (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);

創建表

create table dept

(deptno number(2),

dname varchar2(14),

loc varchar2(13),

create_date date default sysdate);

確認創建是否成功：

desc dept

 Name   Null?    Type

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

 DEPTNO    NUMBER(2)

 DNAME    VARCHAR2(14)

 LOC    VARCHAR2(13)

 CREATE_DATE    DATE

數據類型

日期數據類型

包含的約束

• 約束為表級的強制規則。

• 如果表有依賴關系，約束能防止表的刪除。

• 下面是有效的約束類型：

       – NOT NULL

       – UNIQUE

       – PRIMARY KEY

       – FOREIGN KEY

       – CHECK

約束準則

•  如果不指定約束名 Oracle server 自動按照 SYS_Cn 的格式指定約束名

•  在什么時候創建約束:

– 建表的同時

– 建表之后

•  可以在表級或列級定義約束

•  可以通過數據字典視圖查看約束

定義約束

 語法：

create table [schema.]table

(column datatype [default expr]

[column_constraint],

...

[table_constraint][,...]);

列級約束語法

column [CONSTRAINT constraint_name] constraint_type,

表級約束語法：

column,...

[CONSTRAINT constraint_name] constraint_type

(column, ...),

列級約束示例

create table employees(

employee_id number(6)

constraint emp_emp_id_pk primary key,

first_name varchar2(20),

...);

表級約束語法：

column,...

[constraint constraint_name] constraint_type

(column, ...),

列級約束示例：

create table employees(

employee_id number(6)

constraint emp_emp_id_pk primary key,

first_name varchar2(20),

...);

表級約束示例：

create table employees(

employee_id number(6),

first_name varchar2(20),

...

job_id varchar2(10) not null,

constraint emp_emp_id_pk

primary key (employee_id));

NOT NULL 約束

確保該列不允許空值：

UNIQUE 約束

可以定義在表級或者列級：

create table employees(

employee_id number(6),

last_name varchar2(25) not null,

email varchar2(25),

salary number(8,2),

commission_pct number(2,2),

hire_date date not null,

...

constraint emp_email_uk unique(email));

PRIMARY KEY  約束

FOREIGN KEY  約束

可以定義在表級或者列級：

create table employees(

employee_id number(6),

last_name varchar2(25) not null,

email varchar2(25),

salary number(8,2),

commission_pct number(2,2),

hire_date date not null,

...

department_id number(4),

constraint emp_dept_fk foreign key (department_id)

references departments(department_id),

constraint emp_email_uk unique(email));

FOREIGN KEY約束：關鍵字

•  FOREIGN KEY：在表級指定子表中的列

•  REFERENCES：標識在父表中的列

•  ON DELETE CASCADE：當父表中的列被刪除時，子表中相對應的列也被刪除

•  ON DELETE SET NULL：子表中相應的列置空

CHECK 約束

定義每一行必須滿足的條件

以下的表達式是不允許的:

– 出現CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 偽列

– 使用 SYSDATE, UID, USER, 和 USERENV 函數

– 在查詢中涉及到其它行的值

CREATE TABLE:  示例

create table employees

( employee_id number(6)

constraint emp_employee_id primary key

, first_name varchar2(20)

, last_name varchar2(25)

constraint emp_last_name_nn not null

, email varchar2(25)

constraint emp_email_nn not null

constraint emp_email_uk unique

, phone_number varchar2(20)

, hire_date date

constraint emp_hire_date_nn not null

, job_id varchar2(10)

constraint emp_job_nn not null

, salary number(8,2)

constraint emp_salary_ck check (salary>0)

, commission_pct number(2,2)

, manager_id number(6)

constraint emp_manager_fk references

employees (employee_id)

, department_id number(4)

constraint emp_dept_fk references

departments (department_id));

違反約束

update employees

set department_id = 55

where department_id = 110;

主鍵行被另一張表外鍵引用，那么您不能刪除主鍵行

使用子查詢創建表

 使用 CREATE TABLE 語句和 AS subquery 選項，將創建表和插入數據結合起來完成。

CREATE TABLE table  [(column, column...)]  AS subquery;

指定的列和子查詢中的列要一一對應。

通過列名和默認值定義列。

create table dept80

as

select employee_id, last_name,

salary*12 annsal,

hire_date

from employees

where department_id = 80;

ALTER TABLE 語句

使用 ALTER TABLE 語句您可以：

•  添加一個新列

•  修改現有的列定義

•  新的列定義默認值

•  刪除一列

•  重命名列

•  將表更改為只讀狀態

  
  

Read-Only 表

使用 ALTER TABLE 將表改為只讀模式：

 表修改過程中阻止DDL和DML的更改

 將表改回到 讀/寫 模式

alter table employees read only;

-- perform table maintenance and then

-- return table back to read/write mode

alter table employees read write;

刪除表

•  表移動到回收站

•  使用 PURGE 子句可徹底刪除表

•  表上依賴的對象失效同時刪除對象權限

drop table dept;