oracle实验21：建立简单的表，并对表进行简单的DDL操作
DDL和DML语句
SQL语言包括四种主要程序设计语言类别的语句：
- DDL数据定义语言(Data Definition Language)
- DML数据操作语言(Data Manipulation Language)
- DCL数据控制语言(Data Control Language)
- TCL事务控制语言（Transaction Control Language）
数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。一个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等。
数据操纵语言DML（Data Manipulation Language），使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。DML包括：INSERT插入、UPDATE更新、DELETE删除。
注意，select语句属于DQL(Data Query Language)。
表的基本操作
- 表有名称
- 表由行和列组成。
- 表是存放数据的最基本对象。
- 堆表：我们将一般的表叫做head table（堆表），其含义为杂乱无章的存储数据，堆表是数据库的重要组织形式。他有别于索引组织表和cluster表。
表的名称规则
- 标准的ASCII码可以描述
- 字母开头
- 30个字母内
- 不能是保留字
- 可以包含大小写字母，数字，_，$，#
- 不能和所属用户的其他对象重名。
- 注：不要使用汉字做表和列的名称。
数据字典
SQL> conn scott/tiger
已连接。
SQL> col object_name for a20
SQL> col object_type for a20
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SALGRADE TABLE
BONUS TABLE
PK_EMP INDEX
EMP TABLE
DEPT TABLE
PK_DEPT INDEX
已选择6行。
user_objects当前用户所拥有的所有对象。不包含自己建立的public对象。
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1
T3
已选择6行。
user_tables当前用户所拥有的表，有一切权利。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
tab当前用户所拥有的表和视图，显示较简洁。
实验21：建立简单的表，并对表进行简单的DDL操作
1.create table语句建立表
- 指明表的名称
- 指明列的名称
- 列的数据类型
- 列的宽度
- 是否有默认值
常见的数据类型
-
- char(n)定长
- varchar2(n)变长，最大到4000
- number
- date
- long
- lob
- raw
创建表
SQL> create table t1(name char(8),salary number(5) default 0,content char(4 char), hiredate date);
表已创建。
描述表结构
SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME CHAR(8)
SALARY NUMBER(5)
CONTENT CHAR(4 CHAR)
HIREDATE DATE
验证表
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1
SQL> col data_length for a20
SQL> col data_type for a20
SQL> col column_name for a20
SQL> col table_name for a20
SQL> select table_name,column_name,data_type,data_length
from user_tab_columns where table_name='T1';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH
-------------------- -------------------- -------------------- -----------
T1 NAME CHAR ##########
T1 SALARY NUMBER ##########
T1 CONTENT CHAR ##########
T1 HIREDATE DATE ##########
2.在现有表的基础上建立表
SQL> create table t2
as select ename name,sal salary from emp;
表已创建。
创建t2时就插入了子查询中的数据。
可以用别名改变列的名称。
SQL> select * from t2;
NAME SALARY
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
FORD 3000
MILLER 1300
已选择12行。
只创建表结构，不插入数据，可以加一个假条件。
SQL> create table t3(c1,c2,c3) as
select ename,empno,sal from emp where 9=1;
表已创建。
SQL> select * from t3;
未选定行
3.修改表结构
如果列为null，可以随便修改列的类型和宽度。
SQL> alter table t1 modify(name char(4));
表已更改。
SQL> desc t1
名称 是否为空? 类型
------------------------------- -------- ----------------------------
NAME CHAR(4)
SALARY NUMBER(5)
CONTENT CHAR(4 CHAR)
HIREDATE DATE
如果有数据，修改会受限制，但不会破坏数据。
SQL> alter table t2 modify(name char(4));
alter table t2 modify(name char(4))
*
第 1 行出现错误:
ORA-01441: 无法减小列长度, 因为一些值过大
如果不改变数据类型，只改变宽度的话，加大是可以的。
SQL> alter table t2 modify(name char(8));
表已更改。
SQL> desc t2
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME CHAR(8)
SALARY NUMBER(7,2)
4.修改表的名称
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T2 TABLE
T1 TABLE
T3 TABLE
已选择7行。
重命名
SQL> rename t1 to t_1;
表已重命名。
验证
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T2 TABLE
T3 TABLE
T_1 TABLE
已选择7行。
5.修改列的名称
SQL> alter table t3 rename column c1 to name;
表已更改。
SQL> desc t3
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
C2 NUMBER(4)
C3 NUMBER(7,2)
6.表注释
SQL> comment on table emp is 'employee table';
注释已创建。
SQL> select comments from user_tab_comments where table_name='EMP';
COMMENTS
--------------------------------------------------------------------------------
employee table
7.列注释
SQL> COMMENT ON COLUMN EMP.SAL IS '员工工资';
注释已创建。
SQL> select comments from user_col_comments where table_name='EMP' AND COLUMN_NAME='SAL';
COMMENTS
--------------------------------------------------------------------------------
员工工资
8.丢弃表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T2 TABLE
T3 TABLE
T_1 TABLE
已选择7行。
删除
SQL> drop table t2;
表已删除。
验证
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T3 TABLE
T_1 TABLE
BIN$YZUBV4KDQdaHbfgXefa6Eg==$0 TABLE
已选择7行。
实际上并没有真的删除，只是改了名称。
显示回收站的信息
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$YZUBV4KDQdaHbfgXefa6Eg==$0 TABLE 2013-06-09:10:20:01
SQL> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME
------------------------------ ------------- --------- ----- ---------------------
BIN$YZUBV4KDQdaHbfgXefa6Eg==$0 T2 DROP TABLE 2013-06-09:10:20:01
9.还原回收站
将回收站的表还原
SQL> flashback table t2 to before drop;
闪回完成。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
T3 TABLE
T_1 TABLE
T2 TABLE
已选择7行。
还原表的同时修改表名
SQL> flashback table t2 to before drop rename to t_2;
闪回完成。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$w+zMkBr9SyeBdhobxWWgow==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T1 TABLE
T3 TABLE
T_2 TABLE
已选择8行。
10.清空回收站
清空回收站内指定的表
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$w+zMkBr9SyeBdhobxWWgow==$0 TABLE 2013-06-27:23:48:51
T_2 BIN$thCQdwB1RtmwVqD6O7k2KQ==$0 TABLE 2013-07-01:21:18:37
SQL> purge table t2;
表已清除。
SQL> show recyclebin --验证已清除
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T_2 BIN$thCQdwB1RtmwVqD6O7k2KQ==$0 TABLE 2013-07-01:21:18:37
清除当前用户的回收站。不会影响其他用户。
SQL> purge recyclebin ;
回收站已清空。
彻底删除表。绕过回收站，彻底删除表。
SQL> create table t2
as select ename name,sal salary from emp;
表已创建。
SQL> drop table t2 purge;
表已删除。
SQL> show recyclebin
--回收站无数据。
知识点
- 建立表
- 修改表
- 注释表
- 该名称
- 丢弃表
- 恢复丢弃的表
- 初步认识数据字典
