MySQL和Oracle差异

create table t_user(

   fid number(4),

   fname varchar2(40)

);

create table t_book(

   bid number(4),

   bfid number(4),

   bname varchar2(40)

);

–更改表名

MySQL：

   alter table t_user rename to t_u;

Oracle:

   rename t_user to t_u;

   或者alter table t_user rename to t_u;

–创建非空约束

crate table t_user(

  fid number(4) not null,

  fname varchar2(40)

);

alter table t_user modify fid not null;

–创建唯一约束

create table t_user(

   fid number(4) unique,

   fname varchar2(40)

);

create table t_user(

   fid number(4),

   fname varchar2(40),

   constraint uk_id unique(fid)

);

alter table t_user modify fid unique;

alter table t_user add constraint uk_id unique(fid); –此处不能加key

–创建主键约束

create table t_user(

   fid number(4) primary key,

   fname varchar2(40)

);

create table t_user(

   fid number(4),

   fname varchar2(40),

   constraint pk_id primary key(fid)

);

alter table t_user modify fid primary key;

alter table t_user add constraint pk_id primary key(fid);

–创建外键约束，级联更新、级联删除

create table t_book(

   bid number(4),

   bfid number(4),

   bname varchar2(40),

   constraint fk_bfid foreign key(bfid) references t_user(fid) 

              on update set null/cascade

              on delete set null/cascade

);

alter table t_book add constraint fk_bfid foreign key(bfid) references t_user(fid)

  on update set null/cascade

  on delete set null/cascade;

create table t_book(

   bid number(4),

   bfid number(4),

   bname varchar2(40),

   foreign key(bfid) references t_user(fid)

           on update set null/cascade

           on delete set null/cascade

);

–创建默认约束、检查点约束

create table t_user(

   fid number(4),

   fname varchar2(40),

   fsex varchar2(1) default 'M' check(fsex in('M','F'))

);

alter table t_user modify fsex varchar2(1) default 1 check(fsex in(1,0));

–删除约束、禁用约束、启用约束

MySQL:

alter table t_user drop primary key;

alter table t_book drop foreign key fk_bfid;

Oracle:

alter table t_book drop constraint uk_id/pk_id/fk_bfid;

alter table t_user drop unique(fid);–删除唯一约束

alter table t_book disable constraint uk_id/pk_id/fk_bfid;

alter table t_book enable constraint uk_id/pk_id/fk_bfid;

–创建索引

create [unique] index idx_id on t_user(fid);

–重建索引

MySQL:

  无

Oracle:

  alter index idx_id rebuild;

–删除索引

MySQL:

  alter table t_user drop index idx_id;

Oracle:

  drop index idx_id

–创建视图

create view v_user as select * from t_user;

–删除视图

drop view v_user

–重命名视图

MySQl:

  无

Oracel:

   rename v_user to v_u;

–创建只读视图

MySQL:

  create view v_user as select * from t_user readonly;

Oracle:

  create view v_user as select * from t_user readonly;

  create view v_user as select * from t_user with read only;

create table t_user(

   fid int(4) primary key,

   fname varchar(40)

);

create table t_book(

   bid int(4),

   bfid int(40),

   bname varchar(40)

);

insert into t_user values(1,’张三’);

insert into t_book values(1,1,’JavaWeb’);

alter table t_book add constraint fk_bfid foreign key(bfid) references t_user(fid);

–MySQL中删除外键：

–创建外键注意事项：

–从表的外键必须是主表的主键