Oracle的DDL语句

本文记录Oracle学习过程中遇到的Oracle的DDL语句~

创建表空间

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

语法格式:

create tablespace spacename
datafile '/home/work/data/spacename001.dbf'
size 100m
autoextend on
next 10m;

常用参数:

  • spacename:表空间名称;
  • datafile:表空间对应的数据文件;
  • size:表空间的初始大小;
  • autoextend on:当表空间存储都占满时自动增长;
  • next:一次自动增长的大小。

用户相关操作

创建用户

语法格式:

create user username
identified by password
default tablespace spacename;

常用参数:

  • username:用户名;
  • password:用户的密码;
  • spacename:默认的表空间名称。

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

用户权限管理

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

注意:进入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 raw:原始的二进制数据;
  • blob:二进制数据,最大可达到4G;
  • bfile:存储外部文件的二进制数据,最大可达到4G;
  • rowid:行地址。

使用子查询创建表的语法格式:

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

示例代码:

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, 'oliver', 1, to_date('2018-04-06', 'yyyy-MM-dd'));

修改表

常见的修改表操作有:

  • 添加字段:alter table tablename add (columnname datatype default value, ...);
  • 修改字段:alter table tablename modify (columnname datatype default value, ...);
  • 修改列名:alter table tablename rename column old to new;

示例代码:

alter table person add(address varchar2(10));
alter table person modify(address varchar2(20));
alter table person rename column name to username;

删除表

语法格式:

drop table tablename;

约束

常见的约束类型有:

  • 主键约束:primary key;
  • 非空约束:not null;
  • 唯一约束:unique;
  • 外键约束:foreign key;
  • 检查性约束:check。

主键约束

示例代码:

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

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

注意:主键不能重复。

非空约束

示例代码:

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

唯一约束

示例代码:

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

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

检查约束

示例代码:

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

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

外键约束

示例代码:

create table orders(
	order_id number(10),
	total_price number(10,2),
	order_time date,
	constraint orders_pk_order_id 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_pk_detail_id primary key(detail_id),
	constraint order_detail_fk_order_id foreign key(order_id) references orders(order_id)
);

注意:

  • 外键一定是主表的主键;
  • 一定要先删除子表再删除主表;
  • 可以强制删除主表,但不推荐(drop table orders cascade constraint);
  • 级联删除可以在外键约束上加上on delete cascade。

标签: none