ORACLE_OCP之SQL_DDL（数据定义语言）-CSDN博客

本文链接：https://blog.csdn.net/XiaoHG_CSDN/article/details/111460789

ORACLE_OCP之SQL_DDL（数据定义语言）

一、数据对象

二、CREATE TABLE 语句

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

//栗子：创建一个名为dept的表
CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE);

//确认表的创建
DESCRIBE dept
名称空值? 类型
.---------- — ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
CREATE_DATE DATE

三、数据类型

在这里插入图片描述
几种日期时间数据类型：

默认选项
- 在CREATE表中为列指定默认值
… hire_date DATE DEFAULT SYSDATE, …
- 文字值，表达式或SQL函数是合法值。
- 另一列的名称或伪列是非法值。
- 默认数据类型必须与列数据类型匹配。

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

四、约束

约束在表级别执行规则。
约束确保数据库的一致性和完整性。
以下约束类型有效:
- 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));
  - 外键约束：关键字
    - 外键：在表约束级别上定义子表中的列
    - 参考：标识父表中的表和列
    - ON DELETE CASCADE：删除父表中的行时，删除子表中的相关行
    - ON DELETE SET NULL：将相关外键值转换为null
- CHECK 检查
  - 它定义了每一行必须满足的条件。
  - 它不能引用其他表中的列。
  …, salary NUMBER(2)
  CONSTRAINT emp_salary_min
  CHECK (salary > 0),…

约束准则：

您可以命名约束，或者Oracle服务器使用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),
…);

表级约束的例子：

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));

CREATE TABLE: 例子

CREATE TABLE teach_emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
photo BLOB,
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES
departments(department_id));

五、CTAS语句

使用子查询创建表
- 通过组合CREATE TABLE语句和AS子查询选项来创建表并插入行
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 table
ADD (column datatype [DEFAULT expr]
[, column datatype]…);
- 栗子：（新列成为最后一列）
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
修改现有的列定义
- 语法：
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]…);
- 可以更改列的数据类型，大小和默认值，栗子：
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
为新列定义默认值（更改默认值仅影响表的插入）
删除列
- 语法：
ALTER TABLE table
DROP (column [, column] …);
- 使用DROP COLUMN子句从表中删除不再需要的列，栗子：
ALTER TABLE dept80
DROP (job_id);
UNUSED 操作
- 可以使用SET UNUSED选项将一列或多列标记为未使用。
- 可以使用DROP UNUSED COLUMNS选项删除标记为未使用的列。
- 可以指定ONLINE关键字来指示在将一列或多列标记为UNUSED时允许对表进行DML操作。
- 设置 UNUSED 操作
  - 语法：
  ALTER TABLE <table_name>
  SET UNUSED(<column_name> [ , <column_name>]);
  OR
  ALTER TABLE <table_name>
  SET UNUSED COLUMN <column_name> [ , <column_name>];
- 删除 UNUSED 操作
  - DROP UNUSED COLUMNS从表中删除当前标记为未使用的所有列。当您要从表中未使用的列中回收额外的磁盘空间时，可以使用此语句。如果该表不包含未使用的列，则该语句返回没有错误。
  - 语法：
  ALTER TABLE <table_name>
  DROP UNUSED COLUMNS;
重命名列
将表更改为只读状态
- 可以使用ALTER TABLE语法:
  
  ALTER TABLE employees READ ONLY;
  – perform table maintenance and then
  – return table back to read/write mode
  ALTER TABLE employees READ WRITE;
- 将表置于只读模式，这可以防止在表维护期间更改DDL或DML
- 将表放回读/写模式