create table userinfo_f2 (id varchar2(10) primary key,username varchar2(20),typeid_new varchar2(10), constraint fk_typeid_new foreign key (typeid_new) references typeinfo(typeid));创建表时设置外键约束

 create table typeinfo_f (id varchar2(10) primary key, username varchar2(20),typeid_new varchar2(10) references typeinfo(typeid));创建表时设置主键约束

insert into typeinfo values(1,1);添加主表约束

insert into typeinfo_f (id, typeid_new)values(1,1);添加从表约束

alter table userinfo rename constraint pk_id to new_pk_id;更改主键约束名称

alter table userinfo disable constraint new_pk_id;禁用主键约束

alter table userinfo drop constraint new_pk_id;删除约束

alter table userinfo_p drop primary key;通过主键名删除主键约束 

alter table userinfo add constraint pk_id primary key(id);修改表时添加主键约束

create table userinfo_p (id number(6,0) primary key, username varchar2(20),userpwd varchar2(20));创建表时设置主键约束

create table userinfo_p1 (id number(6,0) ,username varchar2(20), userpwd varchar2(20), constraint pk_id_username primary key (id, username));  创建表时设置联合主键约束

desc user_constraints查询数据字典

select constraint_name from user_constraints where table_name= 'USERINFO_P';在数据字典中查找主键名字

create table userinfo_1 (id number(6,0), username varchar2(20) not null, userpwd varchar2(20) not null );创建表时添加非空约束

alter table userinfo modify username varchar2(20) not null;在修改表时添加非空约束

alter table userinfo modify username varchar2(20) null;在修改表时去除非空约束

delete from testdel;无条件删除表中数据

delete from userinfo where username = 'yanyal'有条件删除表中数据

update userinfo set userpwd = ' 11111';无条件更新表中某个字段的值

update userinfo set userpwd = '123456' where username = 'yyl';有条件更新表中字段的数值

create table userinfo_new as select * from userinfo;复制表中全部数据

create table userinfo_new1 as select id, username from userinfo;复制insert into userinfo_new(id,username) select id, username from userinfo;修改时复制表数据

insert into userinfo values (1,'yyl','123','xxx@126.com',sysdate); 向表中添加数据

select *from userinfo;查询表中所有的数据

insert into userinfo (id, username,userpwd) values(2,'yanyal','123');向表中添加某些字段的数据值

select username,userpwd from userinfo;查询某些字段的数据值

create table userinfo1 (id number(6,0), regdate date default sysdate);创建表时为字段添加默认值

insert into userinfo1(id) values(1);修改表时为字段添加默认值

alter table userinfo modify email default 'wu';修改某些字段的默认数据值

truncate table new_userinfo;删除表数据

drop table new_userinfo;删除表结构

alter table userinfo rename column email to new_email;修改字段名

alter table userinfo drop column remarks;删除字段，但在sys用户下不能删除

alter table userinfo modify userpwd number(6,0);修改字段

alter table userinfo add remarks varchar2(500);增加字段