Oracle DBA 基础之 DDL 语句

DDL 语句
数据库对象：表、视图、序列、索引、同义词
数据库对象的命名规则：
-必须以字母开头
-不得超过30个字符
-A-Z,a-z,0-9,_,$,#
-不得与同一个schema下的其他相同类型数据库对象重名
-不得为 ORACLE 保留的关键字


------创建表
CREATE TABLE [SCHEMA].TABLE_NAME
(COLUMN DATA_TYPE [DEFAULT EXPR] [,...]);


需要指定：列名、列数据类型、和列的长度
------查看某个SCHEMA下的数据库对象
hr@PROD> select object_name,object_type from user_objects;


hr@PROD> create table t(t int,name char(19));


Table created.


hr@PROD> select object_name,object_type from user_objects where object_name = 'T';


OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
T TABLE




hr@PROD> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'T';


TABLE_NAME
------------------------------
T


属于其他用户的表不会出现在当前用户的schema中
可以使用表的owner_name作为表的前缀进行访问


hr@PROD> conn / as sysdba
Connected.
sys@PROD> insert into hr.T values(1,'xiang');


1 row created.


sys@PROD> select * from hr.T;


T NAME
---------- ---------------------------------------------
1 xiang





--------------创建表时指定 DEFUALT 选项
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CREATE_DATE DATE DEFAULT SYSDATE);


数据类型：
varchar2(size)
char(size)
number(p,s)
date
long 已作废
clob character data 最大可达 4GB
raw
long raw
blob binary data 最大可达4GB
bfile binary data 存储在外部文件（操作系统文件），最大可达 4GB
rowid 在 64 位系统上表示表中的某个行的唯一地址


timestamp Date with fractional seconds
interval year to month stored as an interval of year and months
interval day to second stored as an interval of days, hours,minutes, and seconds


hr@PROD> select rowid,id,hire_date from hire_dates;


ROWID ID HIRE_DATE
------------------ ---------- ---------
AAARUwAAEAAAAGVAAA 1 06-AUG-12








CREATE TABLE HIRE_DATES
(ID NUMBER(2),
HIRE_DATE DATE DEFAULT SYSDATE);


hr@PROD> select * from hire_dates;


no rows selected


hr@PROD> insert into hire_dates values (1,default);


1 row created.


hr@PROD> select * from hire_dates;


ID HIRE_DATE
---------- ---------
1 06-AUG-12


---------插入的时候指定列
hr@PROD> insert into hire_dates(id) values (1);


1 row created.


hr@PROD> select * from hire_dates;


ID HIRE_DATE
---------- ---------
1 06-AUG-12




---------------------Constraints 约束
Constraints enforce rules at table level
Constraints prevent the deletion of a table if there are dependencies.
Oracle 中有如下约束：


NOT NOLL 确保列中不会插入 NULL 值
UNIQUE 确保列中不会插入重复的值
PRIMARY KEY NULL+UNIQUE，每个表有且只能有一个逐渐
FOREIGN KEY 完整性约束，一个表A上的外键是另一个表B的主键，如果你要往表A插入数据，则外键列必须在表B的主键列中有值。
如果要删除表B时表A中有数据，则删除不会成功
foreign key
references
on delete cascade 删除父表中的记录时，会删除子表中的依赖的行
on delete set null 将有依赖关系的外键值转换为NULL值

无法删除一个包含已被另一个表引用为外键的主键的行。
DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID = 60，如果在 EMPLOYEES 表中存在 DEPARTMENT_ID 为 60
的记录，则该行无法被删除，可以通过在定义外键时加入 on delete cascade 和 on delete set null 来解决
此问题


CHECK 定义一个每行必须满足的条件
以下表达式不允许
-CURRVAL,NEXTVAL,LEVEL 和 ROWNUM 伪列不得定义 CHECK 约束
-SYSDATE,UID,USER,USERENV 函数不能定义 CHECK 约束
-引用其他行中的其他值的查询
例子;
....
SALARY NUMBER(2) CONSTRAINT EMP_SALARY_MIN CHECK(SALARY >0),
....



创建约束的时候应该给约束命名，否则ORACLE会自己以SYS_Cn格式命名。
可以再创建表的同时创建约束
也可以在创建表之后创建约束
可以定义列级的约束，也可以定义表级的约束
可以通过数据字典查看约束相关的信息


创建约束的语法：
CREATE TABLE [SCHEMA].TABLE_NAME
(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_ID_PK PRIMARY KEY,
FIRST_NAME VARCHAR(20),
...);

表级约束创建示例：
CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR(20),
...
JOB_ID VARCHAR2(10) NOT NULL,
CONSTRAINT EMP_ID_PK PRIMARY KEY(EMPLOYEE_ID)
);


CREATE TABLE EMP
(
EMP_ID NUMBER(6),
NAME VARCHAR(20),
EMAIL VARCHAR(50) CONSTRAINT EMP_EMAIL_UK UNIQUE
);


CREATE TABLE EMP(EMP_ID NUMBER(6) PRIMARY KEY, NAME VARCHAR(30));


获取数据库对象的创建脚本


SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP') FROM DUAL;


CREATE TABLE EMP
(COL1
COL2
...
COLn
CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID),
CONSTRAINT EMP_EMAIL_UK UNIQUE(EMAIL)
);






------------通过子查询来创建表
CREATE TABLE TABLE_NAME AS SELECT * FROM TABLE2;
通过此方法创建的表，source table 的 NOT NULL 和 CHECK 约束都将应用于新表
但 PRIMARY KEY,unique , foreign key 都将不应用于新表。
CREATE TABLE DEPT
AS
SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12，HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;


--------此语句执行会报错，因为 SALARY*12 无法识别，修改成如下即可
CREATE TABLE DEPT ( EMPLOYEE_ID,LAST_NAME,SALARY,JOIN_DATE)
AS
SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12，HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;


------------复制表结构
CREATE TABLE TABLE1 AS SELECT * FROM TABLE2 WHERE 1=2;
----条件可以为 1=2 2=3 等所有false值的表达式




------------ALTER TABLE 子句
-添加新列
-修改现有的列定义
-定义新列的默认值
-删除列
-重命名列
-将表更改为 read only 状态


示例：
ALTER TABLE EMPLOYEES READ ONLY;
ALTER TABLE EMPLOYEES WRITE;




-------------DROP TABLE 子句
DROP TABLE TABLE_NAME; ---进回收站
并未将表删除，而是将表放进 recyclebin 中。
DROP TABLE TABLE_NAME PURGE;---彻底删除

加上 PURGE 子句会彻底删除表及其所有数据。

原文链接：http://blog.youkuaiyun.com/xiangsir/article/details/8598486