1.常见的数据类型
char(n) 定长
varchar2(n) 变长，最大到4000
number(p,s)
long
lob
raw
2.创建一个简单的表
scott@TEST>create table t1( 2 name char(8), 3 salary number(5) default 0, 4 content char (4 char), 5 hiredate date ); Table created. scott@TEST>desc t1; //描述表结构 Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- NAME CHAR(8) SALARY NUMBER(5) CONTENT CHAR(4 CHAR) HIREDATE DATE
在现有的表基础上创建一个新的表。也就是复制现有的表结构和数据创建一个新的表空间
scott@TEST>create table t2 as 2 select ename,job,sal from emp; Table created. scott@TEST>desc t2; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- ENAME VARCHAR2(10) JOB VARCHAR2(9) SAL NUMBER(7,2) scott@TEST>select * from t2; ENAME JOB SAL -------------------- ------------------ ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 14 rows selected.
如果不想要数据，只建立表结构，则在最后写一条不成立的where子句即可
scott@TEST>create table t3 as 2 select ename,job,sal from emp where 1=2; Table created. scott@TEST>desc t3; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- ENAME VARCHAR2(10) JOB VARCHAR2(9) SAL NUMBER(7,2) scott@TEST>select * from t3; no rows selected
3.修改表结构
1）如果列为null，可以随便修改列的类型和宽度
如果有数据，修改会受到限制。但不会破坏数据
如果不改变数据类型，只改变宽度的话加大是可以的。注意 只能增大 不能变小
scott@TEST>alter table t1 modify(name char(4)); Table altered. scott@TEST>desc t1; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- NAME CHAR(4) SALARY NUMBER(5) CONTENT CHAR(4 CHAR) HIREDATE DATE
2）修改表的名称
scott@TEST>rename t1 to t_1; Table renamed. scott@TEST>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE T2 TABLE T3 TABLE T_1 TABLE 7 rows selected.
3）修改列的名称（10g以上才可以）
scott@TEST>alter table t3 rename column ename to c1; Table altered. scott@TEST>desc t3; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- C1 VARCHAR2(10) JOB VARCHAR2(9) SAL NUMBER(7,2)
4）表注释
注意：在查看表注释的时候 表名称不能小写
scott@TEST>comment on table emp is 'employee table'; Comment created. scott@TEST>select comments from user_tab_comments where table_name='EMP'; COMMENTS ------------------------------------------------------------------------------------------------------------------------ employee table
5）列注释
注意：在查看列注释的时候，列名称不能小写
scott@TEST>comment on column emp.sal is 'money'; Comment created. scott@TEST>select comments from user_col_comments 2 where table_name='EMP' AND column_name='SAL'; COMMENTS ------------------------------------------------------------------------------------------------------------------------ money
6）丢弃表
被丢弃的表并没有被删除，只是改了个名称。
scott@TEST>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE T2 TABLE T3 TABLE T_1 TABLE 7 rows selected. scott@TEST> scott@TEST> scott@TEST>drop table t2; Table dropped. scott@TEST>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- BIN$TkFwIgLVUOTgU0MKqMBNZw==$0 TABLE BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE T3 TABLE T_1 TABLE 7 rows selected.
显示回收站的信息
scott@TEST>show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T2 BIN$TkFwIgLVUOTgU0MKqMBNZw==$0 TABLE 2017-04-28:17:05:49 scott@TEST>select * from user_recyclebin; OBJECT_NAME ------------------------------------------------------------ ORIGINAL_NAME OPERATION ---------------------------------------------------------------- ------------------ TYPE TS_NAME -------------------------------------------------- ------------------------------------------------------------ CREATETIME DROPTIME DROPSCN -------------------------------------- -------------------------------------- ---------- PARTITION_NAME CAN_UN CAN_PU RELATED BASE_OBJECT PURGE_OBJECT ---------------------------------------------------------------- ------ ------ ---------- ----------- ------------ SPACE ---------- BIN$TkFwIgLVUOTgU0MKqMBNZw==$0 T2 DROP TABLE USERS 2017-04-28:15:16:19 2017-04-28:17:05:49 1331779 YES YES 88703 88703 88703 8
将回收站的表还原
scott@TEST>flashback table t2 to before drop; Flashback complete. scott@TEST>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE T2 TABLE T3 TABLE T_1 TABLE 7 rows selected.
将回收站的表还原的同时更改名字
scott@TEST>flashback table t2 to before drop rename to tt2; Flashback complete. scott@TEST>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------ -------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE T3 TABLE TT2 TABLE T_1 TABLE 7 rows selected.
清空回收站内指定的表
scott@TEST>create table t4 as select * from emp; Table created. scott@TEST>drop table t4; Table dropped. scott@TEST>purge table t4; Table purged.
清空当前用户的回收站，不会影响其它用户的回收站
scott@TEST>purge recyclebin;
Recyclebin purged.
绕过回收站，直接彻底删除一张表
scott@TEST>drop table t4 purge;
Table dropped.