Oracle管理表

Oracle管理表

Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。

目标

• 生成一张包含完整性约束的表
• 确定表的命名规范.
• 描述当你定义列时可能用到的数据类型
• 认识自动生成的索引
• 基于一个表的内容建一个新表
• 添加和修改表列
• 添加，删除，开启，关闭约束
• 删除一张表

常用类型

数据类型 描述 VARCHAR2(size) 变长度的字符串 CHAR(size) 固定长度的字符串 NUMBER 浮点型数字 NUMBER(p,s) 数字值（p-整数位数，s-小数位数） DATE 日期和时间值 LONG 变长度的字符串，存储最大字节数可 达 2 GB RAW and LONG RAW 存储二进制数据的变长度字符串

数值型

建议数值类型都是NUMBER类型

NUMBER（precision, scale）

• precision表示数字中的有效位。如果没有指定precision的话，Oracle将使用38作为精度。
• scale表示数字小数点右边的位数，scale默认设置为0

字符型

字符型分为定常字符串和不定常字符串

• 定常字符串(效率高一些)
  
  1. CHAR
  2. NCHAR
• 不定常字符串（节约空间一些）
  
  1. VARCHAR2
  2. NVARCHAR2

数据类型 描述 CHAR 最长可以容纳8000个字符，以字节为单位,若不指定的话则系统默认的是1 NCHAR 最长可以容纳4000个字符，采用Unicode，以两字节为单位，若不指定的话则系统默认的是1 VARCHAR2 同上，若存 abc 值，VARCHAR2(20)则只占用3个字节的长度。 NVARCHAR2 同上

大对象数据类型

不常用

• RAW
  
  1. 可存储小型的二进制数据。不能转换字符集，Oracle工具不处理RAW数据。Long, Long Raw and Large Object (LOBs)
  2. CLOB and LONG：大型定常字符
  3. NCLOB： 大型定常字符，支持unicode
  4. BLOB and LONG RAW：非结构化数据
  5. BFILE：在外部文件中存储非结构化数据
  6. 注：
     
     • LONG and LONG RAW存储非结构化数据，图片、文档、图像等，现被LOB取代
     • LOB与LONG and LONG RAW不能互换。LOBs不支持LONG 类型的API接口等。

时间类型

1. DATE (不那么精确)
   在Oracle中日期型数据包括了时间信息。
2. TIMESTAMP （相当精确）
   存储精确到十亿分之一秒的日期。
   TIMESTAMP（可作主键）
   TIMESTAMP WITH TIME ZONE （不可作主键）
   TIMESTAMP WITH LOCAL TIME ZONE（可作主键），数据存储到数据库时是规范化的，查询时用户看到的是会话时区的数据。

表结构

• 随时可创建
• 无需预先决定其大小
• 可被在线修改
• 命名规则
  
  1. 必须以字母开头
  2. 1-30个字符长
  3. 只能包含A-Z, a-z, 0-9, _, $, and #
  4. 同一个用户不能拥有两个同名的对象
  5. 不能使用Oracle Server的保留字

建表:语法

PS:你必须拥有指定的权限: CREATE TABLE; 存储区域

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

例子-这里使用pl/sql developer工具演示建表

点击tables,右键，新建

输入表名，点击列选项

输入下列参数

执行生成表

相应的sql语句

-- Create tablecreate table student(  id    number not null,  name  varchar2(20),  age   number(5) default 20,  birth timestamp default sysdate);

验证-执行下面语句

insert into scott.student(id,name) values(1, '张三');commit;select * from scott.student ; 

建表-利用子查询建一个表

PS:将表的查询结果，创建新表，并将查询的纪录插入到新表中

CREATE TABLE table  [column(, column...)]  AS subquery;

执行下面语句

create table sub_studentas select t.name, t.age from scott.student t;

验证

select t.*, rowid from sub_student t ；

约束类型

常见约束类型

约束 说明 NOT NULL 该字段不为空 UNIQUE 该字段唯一 PRIMARY KEY 该字段为主键（不为空，且唯一） FOREIGN KEY 该字段为外键（约束表已存在的值） CHECK 该字段被一定的规则校验

NOT NULL约束

举例

运行-- Create tablecreate table t_not_null(  name nvarchar2(20) not null,  age  long);

正确
insert into t_not_null(name, age) values(‘zhangsan’, 12);
错误-name字段不能为空
insert into t_not_null(age) values(12);

修改约束

-- Add/modify columns alter table T_NOT_NULL modify NAME null;

UNIQUE约束

举例

运行-- Create tablecreate table T_UNIQUE(  name nvarchar2(20),  age  long);alter table T_UNIQUE  add constraint TEST_UNIQUE unique (NAME)

第一条正确
insert into T_UNIQUE(name, age) values(‘zhangsan’, 12);
第二条错误-name字段重复了
insert into T_UNIQUE(name, age) values(‘zhangsan’, 12);

删除掉约束

-- Drop primary, unique and foreign key constraints alter table T_UNIQUE  drop constraint TEST_UNIQUE cascade;

主键约束

举例

运行-- Create tablecreate table t_primary(  name nvarchar2(20),  age  long);-- Create/Recreate primary, unique and foreign key constraints alter table t_primary  add constraint test_primary primary key (NAME);

第一条正确
insert into t_primary(name, age) values(‘zhangsan’, 12);
第二条错误-name字段重复了
insert into t_primary(name, age) values(‘zhangsan’, 12);

删除掉约束

-- Drop primary, unique and foreign key constraints alter table T_PRIMARY  drop constraint TEST_PRIMARY cascade;

FOREIGN KEY 关键字

举例

运行-- Create tablecreate table T_FOREIGN(  NAME  NVARCHAR2(20),  SCORE LONG)-- Create/Recreate primary, unique and foreign key constraints alter table T_FOREIGN  add constraint test_foreign foreign key (NAME)  references t_primary (NAME) on delete cascade;

注：
**ON DELETE CASCADE
删除父表行的同时删除子表中的从属行**

第一条正确
insert into T_FOREIGN(name, score) values(‘zhangsan’, 90);
第二条错误-name字段lisi在表t_primary 没有lisi这个值
insert into T_FOREIGN(name, score) values(‘lisi’, 100);
删除掉约束

-- Drop primary, unique and foreign key constraints alter table T_FOREIGN  drop constraint TEST_FOREIGN;

修改表和约束

ALTER TABLE可以帮助你添加和修改列.添加或删除约束.开启或关闭约束.DROP TABLE 命令可以删除行和表结构.其他影响表的命令有RENAME, TRUNCATE, COMMENT.当你发布DDL命令时，系统自动执行提交.

加入一列:句法

语法

ALTER TABLE tableADD (column datatype [DEFAULT expr][NOT NULL]     [, column datatype]...);

例子，向表T_NOT_NULL添加一列，remark，类型nvarchar2（10）

-- Add/modify columns alter table T_NOT_NULL add remark nvarchar2(20);

修改一列:句法

语法

ALTER TABLE tableMODIFY  (column datatype [DEFAULT expr][NOT NULL]                [, column datatype]...);

例子，将表T_NOT_NULL修改一列，remark，改为类型nvarchar2（100）

-- Add/modify columns alter table T_NOT_NULL modify REMARK NVARCHAR2(100);

删除列:句法

语法

ALTER TABLE tableDROP column datatype;

例子，将表T_NOT_NULL删除一列，remark

-- Drop columns alter table T_NOT_NULL drop column REMARK;

修改对象名

RENAME命令修改表、视图、序列或同义词的名字

RENAME s_ord TO s_order;

例子，将T_NOT_NULL修改为T2_NOT_NULL

rename T_NOT_NULL to T2_NOT_NULL;

向表中加入注释

挺重要的，相当于对代码的说明

COMMENT ON TABLE 模式.表明.字段
IS ‘注释’

例子，为T_FOREIGN表的NAME字段加上注释

-- Add comments to the columns comment on column T_FOREIGN.NAME  is '这是姓名';