表结构操作
--查询用户scott下所有的表名
select table_name from all_tables where owner='SCOTT';
--查询表EMP所有字段的信息
select * from all_tab_columns where table_name='EMP' ;
--表中的索引列
select * from sys.all_ind_columns where table_name='EMP';
--表中的所有约束
select * from all_constraints where table_name='EMP';
--如果表存在的话,将表进行删除
begin
execute immediate 'drop table emp1';
exception when others then
null;
end;
select table_name ,comments from dictionary where table_name like '%TABLE%';
1.添加字段
alter table student add age number(4);
2.修改字段类型
alter table student modify age number(10);
3.删除字段.
Alter table student drop column age;
4.清空表里面的数据
Truncate table student;
5.删除表.
Drop table student.
6.重命名表
Rename student to student1;
7.将字段名称进行修改.
Alter table student rename column age to age2;
DML
表间数据的拷贝
insert into table1(c1,c2) select cc1,cc2 from table2;
约束
1.非空约束 NOT NULL
2.主键约束 PRIMARY KEY
3.唯一约束,空值除外 UNIQUE
4.条件约束 CHECK (CHECK (age BETWEEN 0 AND 150)
5.外键 FOREIGN KEY 外键
pid number references person(pid) on delete cascade
constraint book_pid_fk foreign key(pid) references person(pid)
drop table book;
drop table person;
create table person
(
pid number,
name varchar(30) not null,
tel varchar(50),
age number
);
create table book
(
bid number,
name varchar(50),
pid number
);
--给person表的pid设为主键
ALTER TABLE person ADD constraint person_pid_pk PRIMARY KEY(pid);
--给book表的bid字段设为主键
alter table book add constraint book_bid_pk primary key(bid);
--为person表中的tel添加唯一约束
alter table person add constraint person_tel_uk unique(tel);
--为person表中的age添加一个检查约束
alter table person add constraint person_age_ck CHECK(age BETWEEN 0 and 150);
--给book添加一个外键约束,并且带级联删除.
alter table book add constraint book_pid_fk FOREIGN KEY(pid) references person(pid) on delete cascade;
--给一个字段添加一个非空
alter table person modify aa not null;
--删除约束
alter table book drop constraint book_bid_pk;
--启用约束
alter table book enable constraint book_bid_pk;
--禁用约束
alter table book disable constraint book_bid_pk;
--查询student表的所有约束名及类型
select constraint_name, constraint_type from all_constraints where table_name = 'STUDENT'
