ITPub博客

首页 > 数据库 > Oracle > OCP课程11:SQL之使用DDL语句创建和管理表

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

原创 Oracle 作者:stonebox1122 时间:2015-12-03 12:59:47 0 删除 编辑

数据库对象:

clipboard

表名和列名命名规则:

  • 字母开头
  • 不超过30个字符
  • 只能包含大小写字母,0-9,_,$,#
  • 同一用户下对象名称不能重复
  • 不能使用保留字

 

 

1、创建表

clipboard[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、数据类型

clipboard[2]

其中:

  • 通过子查询创建的表,long类型列不能被复制
  • long类型列不能位于group by和order by子句中
  • 一个表中只能有一个long类型列
  • long类型列上面不能加约束
  • CLOB是不能指定大小

 

日期时间数据类型

clipboard[3]

 

timestamp语法:

clipboard[4]

其中:

  • timestamp包含年月日时分秒及微秒,他是date类型的一个扩展
  • timestamp with time zone带有时区
  • timestamp with local time zone根据当前session的时区进行变化

 

interval语法:

clipboard[5]

clipboard[6]

其中:

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举例:

clipboard[7]

 

 

3、约束

约束是表级上的一个规则,阻止我们对表依赖性的破坏,约束类型:

clipboard[8]

约束使用指导:

  • 可以命名一个约束,也可以不指定,使用Oracle自动产生的以sys_c开头的名字
  • 既可以在创建表的时候创建约束,也可以在创建表之后再创建约束
  • 既可以在表级创建约束,也可以在列级创建约束,一次只能指定一个约束
  • 复合约束(包含多列)需在表级创建
  • 可以通过数据字典查看约束

 

定义约束语法:

clipboard[9]

 

创建列级约束:

clipboard[10]

 

创建表级约束:

clipboard[11]

 

例子:创建列级约束

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创建表和约束
  • 数据类型

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-1852379/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-13

  • 博文量
    204
  • 访问量
    1078709