--删除表结构
drop table company_event
--创建表
create table company_event
(
event_name varchar2(50),
event_date date default sysdate,--默认值
event_number number(5) default 0
)
--插入记录
insert into company_event(event_number,event_name,event_date) values(22,'aa',sysdate);--sysdte默认当前时间
insert into company_event(event_number,event_name,event_date) values(11,'bb',date '2008-12-28');
--查看表记录信息
select * from company_event
create table newsinfo
(
newsid number(5) primary key, --主键
newstile varchar2(100) not null,--非空
newscontent clob,
newsdate date default sysdate--默认
)
drop table newsinfo
create table newsinfo
(
newsid number(5),
newstile varchar2(100),
newscontent clob,
newsdate date default sysdate,
constraint newsinfo_newsid_pk primary key(newsid) --增加主键约束
)
--增加列
alter table newsinfo
add classid number(3)
alter table newsinfo
add constraint newsinfo_newsid_pk primary key(newsid); --通过alter table增加主键约束
--增加唯一性约束
alter table newsinfo
add constraint newsinfo_newstile_unique unique(newstile);
select * from newsinfo
--查询表上有哪些约束
select * from user_constraints where table_name=upper('newsclass')
select * from user_cons_columns where table_name=('NEWSINFO')
insert into newsinfo(newsid,newstile) values(111,null)
drop table students
create table students
(
student_id number(3),
student_name varchar2(50),
gender varchar2(6),
stuage number(3)
)
--增加检查约束
alter table students
add constraint ck_gender check(gender in ('male','female'))
--增加检查约束
alter table students
add constraint ck_stuage check(stuage between 10 and 50)
--
insert into students(student_id,student_name,gender,stuage) values(11,'abc','male',80)
--信息分类表
create table newsclass
(
classid number primary key,
classname varchar2(50),
classdate date default sysdate
)
select * from newsinfo
delete from newsinfo
--外键约束
alter table newsinfo
add constraint newsinfo_classId_fk foreign key(classid) references newsclass(classid)
select * from newsclass
--检查外键约束
insert into newsclass(classid,classname) values(10,' 公告')
insert into newsinfo(newsid,newstile,classid) values(188,'新闻2',20)--插入不成功
--使约束失效,同时使相关外键约束失效
alter table newsclass
disable constraint SYS_C005494 cascade
--create table as select 方式创建表
create table empemp_copy1
as
select * from scott.emp where 1=2
select * from empemp_copy
--删除列
alter table empemp_copy
drop column comm
--将列变为不用的列
alter table empemp_copy
set unused column sal
--增加列
alter table empemp_copy
add newcolumn varchar2(50)
--修改列的信息
alter table empemp_copy
modify (newcolumn date default sysdate)
--修改列的名称
alter table empemp_copy
rename column newcolumn to datecolumn
--修改表的名称
alter table empemp_copy rename to newemp
--删除表
drop table newemp
select * from newemp
--查询表的列信息
select * from user_tab_columns where table_name=upper('empemp_copy')