Oracle--使用DDL语句管理表、约束详解

创建表空间

表空间是Oracle数据库的逻辑单元。数据库–表空间。一个表空间可以与多个数据库文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace zhangs
datafile 'd:\zhangs.dbf'
size 100m
autoextend on
next 10m;

• zhangs：为表空间名称
• datafile：指定表空间对应的数据文件
• size：定义的是表空间的初始大小
• autoextend on：自动增长，当表空间存储都占满时，自动增长
• next：指定的时一次自动增长的大小

用户

1. 创建用户

create user acton
identified by 930205
default tablespace zhangs;

• identified by：用户的密码
• default tablespace：表空间名称

Oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

2. 用户赋权限
新创建的用户没有任何全新，登录后会提示

Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。

• CONNECT角色：
  是授予最终用户的典型权力，最基本的
  ALTER SESSION：修改会话
  CREATE CLUSTER：建立聚簇
  CREATE DATABASE LINK：建立数据库连接
  CREATE SEQUENCE：建立序列
  CREATE SESSION：建立会话
  CREATE SYNONYM：建立同义词
  CREATE VIEW：建立视图

• RESOURCE角色：
  是授予开发人员的
  CREATE CLUSTER：建立聚簇
  CREATE PROCEDURE：建立过程
  CREATE SEQUENCE：建立序列
  CREATE TABLE：建表
  CREATE TRIGGER：建立触发器
  CREATE TYPE：建立类型

• DBA角色：
  拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意表，包括删除

grant dba to acton;

进入system用户下给用户赋予dba权限，否则无法正常登录。

创建表

语法：

create table [schema.] table
	(column datatype [default expr][, ...]);

数据类型：

数据类型	描述
VARCHAR2（size）	可变长字符数据
CHAR（size）	定长字符数据
NUMBER（p，s）	可变长数值数据
DATE	日期型数据
LONG	可变长字符数据，最大可达到2G
CLOB	字符数据，最大可达到4G
RAW and LONG RAE	原始的二进制数据
BLOB	二进制数据，最大可达到4G
BFILE	存储外部文件的二进制数据，最大可达到4G
ROWID	行地址

使用子查询创建表的语法：

create table t_table
	[(column, column...)]
as subquery;

//如果只想创建表结构，不需要表中的数据，可以给一个永远为假的条件
create table t_table1 as
select * from t_table2 where 1=2;

示例：创建person表：

create table person(
       pid number(10),
       name varchar2(10),
       gender number(1) default 1,
       birthday date);

insert into person(pid,name,gender,birthday)
values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));

select * from person;

修改表
在sql中使用alter可以修改表

• 添加语法：

alter table 表名称 add(类名1 类型 [default 默认值]， 列名2 类型 [default 默认值]...)

• 修改语法：

alter table 表名称 modify(列名1 类型 [default 默认值]，列名2 类型 [default 默认值]...)

• 修改列名：

alter table 表名称 rename column 列名1 to 列名 2

示例：在person表中增加列address

alter table person add(address varchar2(10));

示例：把person表的address列的长度修改成20

alter table person modify(address varchar2(20));

删除表

drop table 表名

约束

在数据库开发中，约束是必不可少的，使用约束可以更好的保证数据库的完整性。在Oracle数据库中，约束的类型包括：

• 主键约束：Primary Key
• 非空约束：Not Null
• 唯一约束：Unique
• 外键约束：Foreign Key
• 检查性约束 Check

1. 主键约束：
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。创建一张表，把pid作为主键

create table person(
	pid number(10) primary key,
	name varchar2(10),
	gender number(1) default 1,
	birthday date);

$主键不可重复，SCOTT.SY{S}_{C}0017981是系统自动分配的约束名字$
$主键不可为空$
我们可以自己来指定主键约束的名字

create table person(
	pid number(10),
	name varchar2(10),
	gender number(1) default 1,
	birthday date,
	constraint person_pk_pid primary key(pid));

2. 非空约束：
使用非空约束，可以使指定的字段不可以为空。
示例：建立一张pid和name不可以为空的表

create table person(
	pid number(10) not null,
	name varchar2(10) not null,
	gender number(1),
	birthday date);

3. 唯一约束（unique）：
表中的一个字段的内容是唯一的
范例：建一个name是唯一的表

create table person(
	pid number(10),
	name varchar2(10) unique,
	gender number(1),
	birthday date);

唯一约束的名字也可以自定义

create table person(
	pid number(10),
	name varchar2(10),
	gender number(1),
	birthday date,
	constraint person_name_unique(name));

5.检查约束：
使用检查约束可以来约束字段值的合法范围。
示例：创建一张表性别只能是1 或 2

create table person(
	pid number(10),
	name varchar2(10),
	gender number(1) check(gender in (1, 2)));

检查约束也可以自定义

create table person(
	pid number(10),
	name varchar2(10),
	gender number(1),
	birthday date,
	constraint person_gender_ck check(gender in (1, 2)));

5. 外键约束：
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
示例：创建两张表，一张订单表，一张订单明细表，订单和明细是一对多的关系

create table orders(
	order_id number(10),
	total_price number(10, 2),
	order_time date,
	constraint orders_order_id_pk primary key(order_id));

create table order_detail(
	detail_id number(10),
	order_id number(10),
	item_name varchar2(10),
	quantity number(10),
	constraint order_detail_detail_id_pk primary key(detail_id));

insert into orders values(1, 200, to_date('2015-12-12', 'yyyy-MM-dd'));

insert into order_detail values(1, 2, 'java', 1);

向两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。

再次建表：

create table orders(
	order_id number(10),
	total_price number(10, 2),
	order_time date,
	constraint orders_order_id_pk primary key(order_id));

create table order_detail(
	detail_id number(10),
	order_id number(10),
	item_name varchar2(10),
	quantity number(10),
	constraint order_detail_detail_id_pk primary key(detail_id)
	constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id));

注意：
外键一定是主表的主键
删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题
SQL> drop table orders
drop table orders
$ORA-02449：表中的唯一/主键被外键引用$

但是可以强制删除：
drop table orders cascade constraint;
但是不建议使用

删除主表的数据可以先删除子表的关联数据，再删出表，也可以使用$级联删除$。
级联删除在外键约束上要加上on delete cascade

constraint order_detail_order_id_fk foreign key(order_id)
	references orders(order_id) on delete cascade

$这样删除主表数据的时候会把子表的关联数据一同删除$

终极示例：

 create table student
(
   sid number constraint student_pk primary key,
   sname varchar2(20) constraint student_name_notnull not null,
   gender varchar2(2) constraint student_gender check (gender in ('男','女')),
   email varchar2(40) constraint student_email_unique unique
                      constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);