[Oracle数据库学习]九、创建和管理表

D6

常见的数据库对象

对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的、逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询的效率
同义词 给对象起别名

 

命名规则

表名和列名

1）必须以字母开头

2）必须在1-30个字符之间

3）必须只能包含A-Z, a-z, 0-9, _, $, #

4）必须不能和用户定义的其他对象重名

5）必须不能是Oracle的保留字

 

创建表CREATE TABLE

条件

1）存储空间

2）创建表的权限

 

语法

CREATE TABLE [schema.]table
                    (column datatype [DEFAULT expr][, ...]);

必须指明：

1）表名

2）列名、数据类型、尺寸

 

引用其他用户的表

应该使用用户名作为前缀来引用其他用户定义的表

 

DEFAULT选项

前面章节有介绍过：插入时为一个列指定默认值

 

字符串、表达式或SQL函数都是合法的；

其它列的列名和伪列都是非法的；

默认值必须满足列的数据类型定义。

 

创建表

create table departments
            (
              department_id int,
              department_name varchar2(300),
              location_id int
            );

 

查看表结构

DESCRIBE departments;

 

使用子查询创建表

使用AS subquery选项，将创建表和插入数据结合起来。

CREATE TABLE table
        [(column, column...)]
AS subquery;

 

注意：

1）指定的列和子查询中的列一一对应；

2）通过列名和默认值定义列。

CREATE TABLE copy_dept 
  AS 
    SELECT department_id, department_name 
    FROM departments;

 

Oracle数据库中的表

分类

用户定义的表

用户自己创建并维护的一组表，包含了用户所需的信息

数据字典

由Oracle Server自动创建的一组表，包含数据库的信息

 

查询数据字典

查看用户定义的表

SELECT table_name 
FROM user_tables;

查看用户定义的各种数据库对象

SELECT DISTINCT object_type 
FROM user_objects;

 

查看用户定义的表, 视图, 同义词和序列 

SELECT* 
FROM user_catalog ;

 

 

数据类型

数据类型 描述
VARCHAR2(size)  可变长字符数据
CHAR(size) 定长字符数据 
NUMBER(p,s) 可变长数值数据 
DATE 日期型数据 
LONG 可变长字符数据，最大可达到2G 
CLOB 字符数据，最大可达到4G 
RAW and LONG RAW 裸二进制数据 
BLOB 二进制数据，最大可达到4G 
BFILE 存储外部文件的二进制数据，最大可达到4G 
ROWID 行地址 

 

日期数据类型

数据类型 描述
TIMESTAMP 时间戳
INTERVAL YEAR TO MONTH 若干年月
INTERVAL DAY TO SECOND 若干天到秒

TIMESTAMP数据类型

是对DATE数据类型的扩展。

按DATE数据类型存放年、月、日、小时、分钟、秒以及微秒甚至纳秒。

TIMESTAMP数据类型的一般形式：

TIMESTAMP [(fractional_seconds_precision)]

 

TIMESTAMP WITH TIME ZONE数据

带有时区的TIMESTAMP；

时区部分按照小时和分钟显示本地时间与UTC的时差。

TIMESTAMP [(fractional_seconds_precision)] 
WITH TIME ZONE

 

INTERVAL YEAR TO MONTH数据

存放若干年和若干月的一个时间段

INTERVAL YEAR [(year_precision)] TO MONTH

示例：

INTERVAL '123-2' YEAR(3) TO MONTH
#表示间隔123年，2个月

INTERVAL '123' YEAR(3)
#表示间隔123年，0个月

INTERVAL '300' MONTH(3)
#表示间隔300个月

INTERVAL '123' YEAR
#错误，因为默认的精度是2，而123尾数是3

 

INTERVAL DAY TO SECOND数据

INTERVAL DAY TO SECOND存放若干天到若干秒的一个时间段

INTERVAL DAY [(day_precision)] 
                TO SECOND [(fractional_seconds_precision)]

示例：

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
#表示间隔4天，5小时，12分钟，10.222秒

INTERVAL '7' DAY
#表示间隔7天

INTERVAL '180' DAY(3) 
#表示间隔180天

INTERVAL '4 5:12' DAY TO MINUTE
#表示间隔4天，5小时，12分钟

INTERVAL '400 5' DAY(3) TO HOUR
#表示间隔400天，5小时

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
#表示间隔11小时，12分钟，10.2222222秒

 

更新表ALTER TABLE语句

使用ALTER TABLE语句可以：
1）追加新的列，并可以为新追加的列定义默认值

ALTER TABLE table
ADD    (column datatype [DEFAULT expr]
            [, column datatype]...);

 

2）修改现有的列

ALTER TABLE table
MODIFY    (column datatype [DEFAULT expr]
                [, column datatype]...);

 

3）删除一个列

ALTER TABLE table
DROP    (column);

 

示例：

ALTER TABLE departments
ADD(manager_id int);
ALTER TABLE departments
MODIFY(department_name VARCHAR2(30));

注：对默认值的修改只影响今后对表的修改。

ALTER TABLE departments
DROP COLUMN manager_id;

 

SET UNUSED选项

使用SET UNUSED选项，使一个或多个列被标记为不可用

ALTER TABLE table 
SET UNUSED (column);

或者

ALTER TABLE table
SET UNUSED COLUMN column;

 

使用DROP UNUSED COLUMNS选项，删除不可用的列

ALTER TABLE table
DROP UNUSED COLUMNS;

 

删除表DROP TABLE

规则

数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE语句不能回滚

DROP TABLE departments;

 

改变对象的名称RENAME

执行RENAME语句改变表、视图、序列或同义词的名称；

注意：必须是对象的所有者。

RENAME departments TO dept;

 

清空表TRUNCATE TABLE

规则

TRUNCATE TABLE语句:
1）删除表中所有的数据
2）释放表的存储空间
3）TRUNCATE语句不能回滚

TRUNCATE TABLE departments;

也可以使用DELETE语句删除数据

DELETE * FROM departments;

 

表的注释COMMENT

使用COMMENT语句给表或列添加注释。

COMMENT ON TABLE departments 
IS 'Department Information';

 

可以通过下列数据字典视图查看所添加的注释：
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

SELECT * 
FROM USER_TAB_COMMENTS;
TABLE_NAMETABLE_TYPECOMMENTS
DEPARTMENTS TABLE Department Information

 

总结：

本节主要介绍DDL语句：

1）创建表：CREATE TABLE；

2）修改表结构：ALTER TABLE；

3）删除表：DROP TABLE；

4）重命名表：RENAME；

5）删除表中的所有数据并释放其存储空间：TRUNCATE TABLE；

6）给对象加注释：COMMENT。

另外，还介绍了Oracle中的数据字典。

 

补充：常用的数据字典表：

1、 查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users; 


2、 查看当前用户的角色
SQL>select * from user_role_privs;


3、 查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;


4、 查看用户下所有的表
SQL>select * from user_tables;


5、 查看用户下所有的表的列属性
SQL>select * from USER_TAB_COLUMNS where table_name=:table_Name;


6、 显示用户信息(所属表空间)
select default_tablespace, temporary_tablespace
  from dba_users  www.2cto.com  
 where username = 'GAME';


7、 显示当前会话所具有的权限
SQL>select * from session_privs;


8、 显示指定用户所具有的系统权限
SQL>select * from dba_sys_privs where grantee='GAME';
 
9、 显示特权用户
select * from v$pwfile_users;


10、 显示用户信息(所属表空间)
select default_tablespace,temporary_tablespace 
from dba_users where username='GAME';


11、 显示用户的PROFILE
select profile from dba_users where username='GAME';
————————————————
版权声明：本文为CSDN博主「阿__龙」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/a_long_123/java/article/details/78054175

 

posted @ 2020-07-16 15:20  workingdiary  阅读(177)  评论(0)    收藏  举报