表的创建与管理

 

范例1：创建一张可以保存所有成员信息的表 —— member表

CREATE TABLE member  (

mid NUMBER(5) ,

name VARCHAR2(50)  DEFAULT '无名氏' ,

age NUMBER(3) ,

birthday DATE DEFAULT SYSDATE ,

note CLOB

);

 

范例2：查看当前用户（现在是c##scott登录）下的全部表

SELECT * FROM tab ;

 

范例3：查看member的表结构是否正确

DESC member ;

 

范例4：向member表中增加若干条测试数据

INSERT INTO member(mid,name,age,birthday,note)

VALUES (1,'李兴华',30,TO_DATE('1979-09-27','yyyy-mm-dd'),'总公司活动提倡者') ;

INSERT INTO member(mid,name,age,birthday,note)

VALUES (2,'董鸣楠',29,TO_DATE('1980-08-13','yyyy-mm-dd'),'积极响应者') ;

INSERT INTO member(mid,age,note)  VALUES (3,35,'活动名单提供者') ;

COMMIT ;

 

范例5：从member表中查询当前表中的记录

SELECT * FROM member ;

 

范例6：将emp表复制成myemp表

CREATE TABLE myemp

AS

SELECT * FROM emp ;

 

范例7：查询myemp表中的记录

SELECT * FROM myemp ;

范例8：要求按照emp的表结构建立一张employee表，但是不需要emp表中的任何数据，但是要求表的结构与emp表一样，但是不能存在emp表中的记录 —— 即：只复制表结构

CREATE TABLE employee

AS

SELECT * FROM emp WHERE 1=2 ;

 

范例9：查看employee的表结构

DESC employee ;

 

范例10：所有部门的统计信息单独保存到一张新的department表之中

CREATE TABLE department

AS

SELECT d.deptno deptno,d.dname dname,d.loc loc,

COUNT(e.empno) count, SUM(e.sal + NVL(e.comm,0)) sum,

ROUND(AVG(e.sal + NVL(e.comm,0)),2) avg, MAX(e.sal) max, MIN(e.sal) min

FROM dept d,emp e

WHERE d.deptno=e.deptno(+)

GROUP BY d.deptno,d.dname,d.loc

ORDER BY d.deptno ;

 

范例11：查看department表中的内容

SELECT * FROM department ;

 

范例12：将member表修改为mldnuser表

RENAME member TO mldnuser ;

 

范例13：查看scott的全部数据表

SELECT * FROM tab ;

 

范例14：截断mldnuser表

TRUNCATE TABLE mldnuser ;

 

范例15：删除myemp表

DROP TABLE myemp ;

 

范例16：查看c##scott下的全部表，以确定myemp是否已经被成功删除

SELECT * FROM tab ;

 

范例17：将employee和department表一起删除

DROP TABLE employee ;

DROP TABLE department ;

 

范例18：查看c##scott用户所有的表

SELECT * FROM tab ;

 

范例19：查看回收站中的数据

SELECT object_name,original_name,operation,type FROM recyclebin ;

 

范例20：恢复myemp表

FLASHBACK TABLE myemp TO BEFORE DROP ;

 

范例21：查询回收站中的表

SELECT object_name,original_name,operation,type FROM recyclebin ;

 

范例22：查看全部表

SELECT * FROM tab ;

 

范例23：直接删除myemp表

DROP TABLE myemp PURGE ;

 

范例24：删除之后再次查询全部数据表

SELECT * FROM tab ;

 

范例25：从回收站之中删除employee表

PURGE TABLE employee ;

 

范例26：查看回收站中的数据

SELECT object_name,original_name,operation,type FROM recyclebin ;

 

范例27：查看回收站中的数据

SELECT object_name,original_name,operation,type FROM recyclebin ;

 

范例：定义数据库创建脚本

-- 删除数据表

DROP TABLE member PURGE ;

-- 创建数据表

CREATE TABLE member (

mid NUMBER ,

name VARCHAR2(50) DEFAULT '无名氏'

) ;

-- 增加测试数据

INSERT INTO member (mid,name) VALUES (1,'李兴华') ;

INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ;

INSERT INTO member (mid,name) VALUES (3,'王月清') ;

-- 提交事务

COMMIT ;

 

范例：查看member表中的全部数据

SELECT * FROM member ;

 

 

范例：向member表中增加三个字段

ALTER TABLE member ADD (age NUMBER(3)) ;

ALTER TABLE member ADD (sex VARCHAR2(10) DEFAULT '男') ;

ALTER TABLE member ADD (photo VARCHAR2(100) DEFAULT 'nophoto.jpg') ;

 

范例：查询member表结构

DESC member ;

 

范例：查询修改之后的member表数据

SELECT * FROM member ;

 

范例：将name字段的长度修改为30，将sex字段的默认值修改为女

ALTER TABLE member MODIFY(name VARCHAR2(30)) ;

ALTER TABLE member MODIFY(sex VARCHAR2(3) DEFAULT '女') ;

 

范例：查看member表结构

DESC member ;

 

范例：删除member表中的photo和age字段

ALTER TABLE member DROP COLUMN photo ;

ALTER TABLE member DROP COLUMN age ;

 

范例：查看member表结构

DESC member ;

 

范例：将sex列设置成无用状态

ALTER TABLE member SET UNUSED(sex) ;

 

范例：将name列设置成无用状态

ALTER TABLE member SET UNUSED COLUMN name ;

 

范例：查看此时的member表结构

DESC member ;

 

范例：查看member表中的记录

SELECT * FROM member ;

 

范例：删除member表中的无用（UNUSED）列

ALTER TABLE member DROP UNUSED COLUMNS ;

 

范例：定义数据库创建脚本

-- 删除数据表

DROP TABLE member PURGE ;

-- 创建数据表

CREATE TABLE member (

mid NUMBER ,

name VARCHAR2(50) DEFAULT '无名氏' ,

age NUMBER(3) ,

birthday DATE

) ;

 

范例：查看user_tab_comments数据字典

SELECT * FROM user_tab_comments WHERE table_name='MEMBER' ;

 

范例：为member表添加注释

COMMENT ON TABLE member IS '用于记录参加活动的成员信息' ;

 

范例：查看member表的注释

SELECT * FROM user_tab_comments WHERE table_name='MEMBER' ;

 

范例：使用“user_col_comments”这个数据字典查看列的注释信息

SELECT * FROM user_col_comments WHERE table_name='MEMBER' ;

 

范例：为member表的mid添加注释信息

COMMENT ON COLUMN member.mid IS '参加活动的成员编号' ;

 

范例：查看member表中所有列的注释信息

SELECT * FROM user_col_comments  WHERE table_name='MEMBER' ;

 

范例：定义数据表

DROP TABLE mytab PURGE ;

CREATE TABLE mytab (

mid NUMBER ,

name VARCHAR2(30) ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

 

范例：察看mytab表结构

DESC mytab ;

 

范例：使用简写方式插入数据

INSERT INTO mytab VALUES(1) ;

 

范例：将name字段设置为不可见状态

ALTER TABLE mytab MODIFY (name INVISIBLE);

 

范例：察看mytab表结构

DESC mytab ;

 

范例：察看user_tab_columns数据字典

SELECT table_name,column_name,data_type,data_length,nullable FROM user_tab_columns

WHERE table_name='MYTAB';

 

范例：增加数据

INSERT INTO mytab VALUES(1) ;

 

范例：查询mytab数据表

SELECT * FROM mytab ;

 

范例：将name字段变为可见

ALTER TABLE mytab MODIFY (name VISIBLE);

 

范例：查询mytab表中的全部数据

SELECT * FROM mytab ;

 

范例：定义表是直接设置不可见字段

DROP TABLE mytab PURGE ;

CREATE TABLE mytab (

mid NUMBER ,

name VARCHAR2(30) INVISIBLE ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

 

范例：创建一个mldn_data的数据表空间

CREATE TABLESPACE mldn_data

DATAFILE  'd:\mldnds\mldn_data01.dbf' SIZE 50M ,

 'e:\mldnds\mldn_data02.dbf' SIZE 50M

AUTOEXTEND on NEXT 2M

LOGGING ;

 

范例：创建一个mldn_temp的临时表空间

CREATE TEMPORARY TABLESPACE mldn_temp

TEMPFILE 'd:\mldnds\mldn_temp01.dbf' SIZE 50M ,

 'e:\mldnds\mldn_temp02.dbf' SIZE 50M

AUTOEXTEND on NEXT 2M ;

 

范例：利用dba_tablespaces查看表空间信息

SELECT tablespace_name,block_size,extent_management,status,contents FROM dba_tablespaces ;

 

范例：利用dba_data_files数据字典查看数据文件信息

SELECT tablespace_name,file_name,bytes,autoextensible,online_status FROM dba_data_files ;

 

范例：利用dba_temp_files数据字典查看数据文件信息

SELECT tablespace_name,file_name,bytes,autoextensible FROM dba_temp_files ;

 

范例：创建数据表，指定表空间

CREATE TABLE mytab(

id NUMBER ,

title VARCHAR2(50)

) TABLESPACE mldn_data ;