oracle学习笔记 01 表的操作

开始学习oracle已经一周了，是时候来对一周的学习做一下总结，顺便再复习一下。

表

   1、创建一个永久表

       create table  employees(

employee_id number(6) not null,

first_name varchar2(20),

last_name varchar2(40),

email varchar2(100),

unique(email),

salary number(5,2),

jib_id number(6)

)

tablespace USER

(

pctfree 10

initrans 1

maxtrans 255

);

comment on table employees is '员工表';

comment on column employees.employee_id is '员工ID';

comment on column employees.first_name is '员工姓氏';

comment on column employees.last_name is '员工姓名';

2.创建临时表

create global temporary jobs(

job_id number(6),

job_name varchar2(20))

tablespace users(

pctfree 10

);

comment on table jobs is '职能岗位表';

comment on column job_id is '职位ID';

3.创建分区表

       create table  employees(

employee_id number(6) not null,

first_name varchar2(20),

last_name varchar2(40),

email varchar2(100),

unique(email),

salary number(5,2),

jib_id number(6),

hire_date date

)

tablespace USER

(

pctfree 10

initrans 1

maxtrans 255

);

partition by range(hire_date)

partition emp_partition1 values less than(to_date('20150202','YYYY-MM-DD')) tablespace users,

partition emp_partition1 values less than (maxvalue) tablespace users;

查看分区数据：

select * from empployees partition(emp_partition);

 

 

4.给表增加约束

alter table employees add(

 constraint emp_PK_01 primary key(employee_id),

 constraint emp_check_01 check(salary>0),

constraint emp_fore_key foreign key job_id refence jobs

);

删除约束

alter table employees  drop constraint emp_check_01;

5.更新表

alter table employees add column (max_salary number(6,2));

alter table employees drop column job_id;