11-Oracle学习_修改表结构-数据字典表

灰太狼 2021-10-30 06:08 335阅读 0赞

修改表结构  
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