semovy

---------------------------------------------------表操作-------------------------------------------------------

1．表的創(chuàng)建：

create table table_name(column_name datatype[,column_name datatype])

2．表數(shù)據(jù)的插入：

insert into table_name values(column_instance)

3．更新數(shù)據(jù)：

update table_name set column_instance=? where column_name=?

4．查詢數(shù)據(jù)：

select * from table_name where condition1 group by column_name1... having.... order by column_name[desc,[asc]]

5．刪除數(shù)據(jù)：

delete from table_name where column_name1=?

6．在表空間上建立表：

create table tablename(column_name,datatype)tablespace tablespace_name

7．oracle中提供5種約束：

Check，not null，unique，primary key，foreign key

(a)check約束

Create table policies(policy_id number ,holder_name varchar2(40),gender varchar2 (1) constraint chk_gender check (gender in ('M','F'),marital_status varchar2 (1),
date_of_birth date ,constraint chk_marital check (marital_status in('S','M','D','W')));

(b)not null約束

Create table policies(policy_id number ,holder_name varchar2 (40) not null,gender varchar2(1),marital_status varchar2(1),date_of_birth date not null);　　

對(duì)于not null的alter table語(yǔ)句與其他約束稍微有點(diǎn)不同。Alter table policies modify holder_name not null

(c)unique約束

create table  emp(eid  int ,ename  varchar2(10),constraint uq unique(eid,ename))

create  table  emp (eid  int ,ename  varchar(10))

alter table emp  add  constraint pk  primary key(eid,ename);

8。

(a)添加新列

alter table table_name add column_name datatype [constraint][constraint_name]

(b) 添加新列并指定默認(rèn)值

alter table table_name add column_name datatype default expression

alter table table_name drop column column_name--刪除列

alter table table_name modify column_name datatype--修改類屬性

alter table table_name disable constraint constraint_name--禁用約束

alter table table_name enable constraint constraint_name--啟用約束

alter table table_name add constraint constraint_name constraint_type(column_name)--添加約束

alter table table_name drop constraint constraint_name [cascade]--刪除約束

--組合主健（可以用多個(gè)列組合成一個(gè)主健）