Oracle中创建和管理表详解

数据库  /  管理员 发布于 6年前   331

SQL> /*
SQL> 对于表的操作: 创建表，修改表(添加新的列，改变当前某些列，删除列),删除表
SQL> 创建表: create table(需要create table的权限)
SQL> 修改表: alter table tablename add/modify/drop
SQL> 删除表：drop table tablename
SQL> */
SQL> show user;
USER 为 "SCOTT"
SQL> --访问hr用户下的表
SQL> select * from hr.employees;
select * from hr.employees
                 *
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> --测试defaul值
SQL> create table test1
  2  (tid number,
  3   tname varchar(20),
  4   hiredate date default sysdate);
表已创建。
SQL> insert into test1(tid,tname) values(1,'Mary');
已创建 1 行。
SQL> select * from test1;
       TID TNAME                HIREDATE                                                                               
---------- -------------------- --------------                                                                         
         1 Mary                 12-6月 -11                                                                             
SQL> --rowid rownum都是伪列
SQL> select rowid,rownum,empno from emp;
ROWID                  ROWNUM      EMPNO                                                                               
------------------ ---------- ----------                                                                               
AAANA2AAEAAAAAsAAT          1       1122                                                                               
AAANA2AAEAAAAAsAAO          2       1234                                                                               
AAANA2AAEAAAAAsAAP          3       1235                                                                               
AAANA2AAEAAAAAsAAQ          4       2222                                                                               
AAANA2AAEAAAAAsAAR          5       2345                                                                               
AAANA2AAEAAAAAsAAS          6       2346                                                                               
AAANA2AAEAAAAAsAAA          7       7369                                                                               
AAANA2AAEAAAAAsAAB          8       7499                                                                               
AAANA2AAEAAAAAsAAC          9       7521                                                                               
AAANA2AAEAAAAAsAAD         10       7566                                                                               
AAANA2AAEAAAAAsAAE         11       7654                                                                               
ROWID                  ROWNUM      EMPNO                                                                               
------------------ ---------- ----------                                                                               
AAANA2AAEAAAAAsAAF         12       7698                                                                               
AAANA2AAEAAAAAsAAG         13       7782                                                                               
AAANA2AAEAAAAAsAAH         14       7788                                                                               
AAANA2AAEAAAAAsAAI         15       7839                                                                               
AAANA2AAEAAAAAsAAJ         16       7844                                                                               
AAANA2AAEAAAAAsAAK         17       7876                                                                               
AAANA2AAEAAAAAsAAL         18       7900                                                                               
AAANA2AAEAAAAAsAAM         19       7902                                                                               
AAANA2AAEAAAAAsAAN         20       7934                                                                               
已选择20行。
SQL> --rowid:oracle维护一个地址，该地址指向了该行在硬盘上实际存储的位置
SQL> --关于varchar2和char
SQL> create table testchar
  2  ( c char(5),
  3    v varchar(5));
表已创建。
SQL> insert into testchar values('a','b');
已创建 1 行。
SQL> select * from testchar;
C     V                                                                                                                
----- -----                                                                                                            
a     b                                                                                                                
SQL> select concat(c,'#'),concat(v,'#') from testchar;
CONCAT CONCAT                                                                                                          
------ ------                                                                                                          
a    # b#                                                                                                              
SQL> --添加新列
SQL> alter table testchar
  2  add  hiredate date;
表已更改。
SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(5)
 V                                                                          VARCHAR2(5)
 HIREDATE                                                                   DATE
SQL> --修改表
SQL> alter table testchar
  2  modify c char(10);
表已更改。
SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(10)
 V                                                                          VARCHAR2(5)
 HIREDATE                                                                   DATE
SQL> --删除列
SQL> alter table testchar
  2  drop hiredate;
drop hiredate
     *
第 2 行出现错误:
ORA-00905: 缺失关键字
SQL> ed
已写入 file afiedt.buf
  1  alter table testchar
  2* drop column hiredate
SQL> /
表已更改。
SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(10)
 V                                                                          VARCHAR2(5)
SQL> host cls
SQL> --删除表
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID                                                                      
------------------------------ ------- ----------                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP10                          TABLE                                                                                   
EMP101                         TABLE                                                                                   
TEST1                          TABLE                                                                                   
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE         &nb