修改表结构
alter
drop
一, alter
1, 字段
① 添加一个字段
SQL> alter table stu add ( addr varchar2(100) );
② 删除一个字段
SQL> alter table stu drop (addr);
③ 修改现有字段
SQL> alter table stu modify ( addr varchar2(200) );
注: 如果已有数据, 则不能损失数据的精度及大小
2, 约束
① 删除一个约束
delete from class where id = 1
ORA-02292: 违反完整约束条件 (SCOTT.STU_CLASS_FK) - 已找到子记录
SQL> alter table stu drop constraint stu_class_fk;
SQL> delete from class where id = 1;
已删除 1 行。
② 添加一个约束
SQL> alter table stu add constraint stu_class_fk foreign key (class) references class (id);
二, drop
drop table table_name;
SQL> create table emp2 as select * from emp;
表已创建。
SQL> drop table emp2;
表已删除。
三, 数据字典表
0, dict 数据字典表 的 表
TABLE_NAME 数据字典表名
COMMENTS 描述
1, user_tables
当前用户下所有表的描述
SQL> desc user_tables;
SQL> select * from dict where table_name = 'USER_TABLES';
SQL> select table_name from user_tables;
2, user_views
Description of the user's own views
SQL> desc user_views;
SQL> select * from dict where table_name = 'USER_VIEWS';
SQL> select view_name from user_views;
3, user_constraints
Constraint definitions on user's own tables
SQL> desc user_constraints
SQL> select * from dict where table_name = 'USER_CONSTRAINTS';
cat tab
SQL> desc cat
Name Null? Type
----------- --------- -----------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
SQL> desc tab
Name Null? Type
----------- --------- -----------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> select * from dict where table_name='CAT';
TABLE_NAME COMMENTS
---------- -----------------------------------
CAT Synonym for USER_CATALOG
SQL> select * from dict where table_name='USER_CATALOG';
TABLE_NAME COMMENTS
-------------------- -------------------------
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
alter
drop
一, alter
1, 字段
① 添加一个字段
SQL> alter table stu add ( addr varchar2(100) );
② 删除一个字段
SQL> alter table stu drop (addr);
③ 修改现有字段
SQL> alter table stu modify ( addr varchar2(200) );
注: 如果已有数据, 则不能损失数据的精度及大小
2, 约束
① 删除一个约束
delete from class where id = 1
ORA-02292: 违反完整约束条件 (SCOTT.STU_CLASS_FK) - 已找到子记录
SQL> alter table stu drop constraint stu_class_fk;
SQL> delete from class where id = 1;
已删除 1 行。
② 添加一个约束
SQL> alter table stu add constraint stu_class_fk foreign key (class) references class (id);
二, drop
drop table table_name;
SQL> create table emp2 as select * from emp;
表已创建。
SQL> drop table emp2;
表已删除。
三, 数据字典表
0, dict 数据字典表 的 表
TABLE_NAME 数据字典表名
COMMENTS 描述
1, user_tables
当前用户下所有表的描述
SQL> desc user_tables;
SQL> select * from dict where table_name = 'USER_TABLES';
SQL> select table_name from user_tables;
2, user_views
Description of the user's own views
SQL> desc user_views;
SQL> select * from dict where table_name = 'USER_VIEWS';
SQL> select view_name from user_views;
3, user_constraints
Constraint definitions on user's own tables
SQL> desc user_constraints
SQL> select * from dict where table_name = 'USER_CONSTRAINTS';
cat tab
SQL> desc cat
Name Null? Type
----------- --------- -----------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
SQL> desc tab
Name Null? Type
----------- --------- -----------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
SQL> select * from dict where table_name='CAT';
TABLE_NAME COMMENTS
---------- -----------------------------------
CAT Synonym for USER_CATALOG
SQL> select * from dict where table_name='USER_CATALOG';
TABLE_NAME COMMENTS
-------------------- -------------------------
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user