测试数据
SQL> conn cjc/****** SQL> create table t1(id int,name varchar2(10)); /* mysql:create table t1(id int,name char(10)); */ SQL> insert into t1 values(1,'cjc'); SQL> commit;
新增列
Oracle语法:
alter table t1 add (col1 char(10),col2 number(2));
MySQL语法:
alter table t1 add (col1 char(10),col2 int); alter table t1 add column (col1 char(10),col2 int);
新增列、默认值、非空
alter table t1 add(col3 int default 5 not null);
修改列类型
Oracle语法:
alter table t1 modify(col1 char(15));
MySQL语法:
alter table t1 modify col1 char(15);
Oracle语法:
修改列类型、默认值
alter table t1 modify(col2 number(3) default 10);
MySQL语法:
alter table t1 modify col2 int default 10;
添加约束
添加检查约束
Oracle、MySQL语法
ALTER TABLE t1 ADD CONSTRAINT chk_t1_col3 CHECK (col3 >=3);
添加主键约束
alter table t1 add constraint pk_t1_id primary key(id);
添加外键约束
---create table t2 as select * from t1; ---alter table t2 add constraint pk_t2_id primary key(id); alter table t1 add constraint fk_t1_id foreign key(id) references t2(id);
查询约束
Oracle语法:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='T1';
MySQL语法:
show create table t1\G; select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA='cjc';
重命名列
Oracle语法:
ALTER TABLE t1 RENAME COLUMN col3 TO col5;
MySQL语法:
alter table t1 change col3 col5 int;
添加备注
Oracle语法:
添加表备注
COMMENT ON TABLE t1 is 'The Test table t1';
添加列备注
comment on column t1.id is 'The table t1 column id';
MySQL语法:
添加表备注
ALTER TABLE t1 COMMENT='The Test table t1';
添加列备注
ALTER table t1 MODIFY id int COMMENT 'The table t1 column id';
查询备注
Oracle语法:
select * from user_tab_comments where table_name='T1';
查询列备注
select * from user_col_comments where table_name='T1';
MySQL语法:
show create table t1\G; SHOW FULL COLUMNS FROM t1;
列备注
SHOW FULL COLUMNS FROM t1;
###chenjuchao 20230102 17:30###