OCP课程11：SQL之使用DDL语句创建和管理表

stonebox

数据库对象：

                               
登录/注册后可看大图

表名和列名命名规则：

• 字母开头
• 不超过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创建表和约束
• 数据类型