oracle_sql 基础 （3）

+++++++++++++++++++++++++++++++++
创建和管理表

数据库对象及表介绍
常用的数据库对象有：表、视图、序列、索引、同义词。
表：也可以被称为堆组织表（后面的章节中会讲到其他组织形式的表）
表由多列组成，至少一列，最多可以有1000列。

表的分类
用户表：由用户创建和维护的表
数据字典：由Oracle 服务器创建和维护的表
全部数据字典表的所有者是用户SYS。数据字典表的基表很少被用户访问，因为其中的信息不容易理解，因此，用户一般是访问数据字典视图，因为视图中的信息是以容易理解的格式表示的。
存储在数据字典中的信息包括Oracle服务器用户的名字，被授予用户的权限，数据库对象名，表结构和审计信息。
有四种数据字典视图，每一种有一个特定的前缀来反映其不同的目的。
USER_，这些视图包含关于用户所拥有的对象的信息。
ALL_，这些视图包含所有用户可访问的表(对象表和相关的表) 的信息。
DBA_，这些视图是受限制的视图，它们只能被分配有DBA 角色的用户所访问。
V$，这些视图是动态执行的视图，包含数据库服务器的性能、存储器和锁的信息。

表和列的命名规则
表命名和列命名：必须以字母开始，必须是1–30 个字符长度，只能包含A–Z, a–z, 0–9, _, $, 和#，同一个用户所拥有的对象之间不能重名，不能用Oracle 服务器的保留字。
使用描述性的名字为表和其他数据库对象命名。
表和列名是大小写不敏感的。eMPloyees 或 eMpLOYEES是算做同

用SQL的CREATE TABLE语句创建表以存储数据，该语句是数据定义语言(DDL) 语句之一，其它的(DDL) 语句将在后面讲述。
DDL语句是SQL语句的一个子集，用来创建、修改或删除 数据库的结构。这些语句会立即作用于数据库，并且他们还将信息记录在数据字典中。
要执行CREATE TABLE脚本，需要满足下面的条件：
CREATE TABLE权限，一个存储区域，指定表名和列名，指定列数据类型和列的大小。
因为创建表是一个DDL语句，当该语句执行时将会发生一个自动提交。

用子查询语法创建表
使用子查询创建表：
CREATE TABLE table[(column, column...)]
AS subquery;
此种形式被简称为CTAS。
可以在创建表的同时为表插入多行

DEFAULT选项
一个列可以用DEFAULT选项给予一个默认值，列该选项防止插入时输入空值到列中。默认值可以是文字、表达式或SQL函数，例如用SYSDATE和USER。
但默认值不能是另一个列的名字或伪列，例如NEXTVAL或CURRVAL。默认表达式必须与列的数据类型相匹配。

引用表
引用当前用户的表
引用其他用户的表
查看数据字典
查看用户所拥有的不同的对象类型
查看本用户所拥有的表、视图、同义词、序列
查看本用户所拥有的表、视图、同义词和序列：
SELECT* FROM user_catalog ;

数据类型介绍
最为常用的数据类型有三种，字符型、数值型和日期型。
VARCHAR2、CHAR2可以定义字符型数据
NUMBER定义整数或小数的数值型数据
DATE定义日期型数据
数据类型                        说明
VARCHAR2(size)                    可变长度字符数据（必须指定最大字符数：最小字符数是 1；最大字符数是 4000）
CHAR [(size)]                    固定长度字符数据，长度的大小以字节为单位(默认和最小字符数为 1；最大字符数为 2000)
NUMBER [(p,s)]                    数字，精度为p，小数为s (p是小数数字的总长度，s是小数点右边的数字长度；p的范围从1到38，s的范围从-84到127)
DATE                            日期和时间值，从公元前4712.1.1到公元9999.12.31
LONG                            最大2G的可变长度字符数据
CLOB                            最大4G的字符数据
RAW(size)                        原始二进制数据 (必须指定最大长度，最大长度为 2000)
LONG RAW                        可变长度原始二进制数据，最大2G
BLOB                            二进制数据，最大4G
BFILE                            二进制数据存储在一个外部文件中；最大到4G
ROWID                            十六进制串，表示行在所在的表中唯一的行地址。该数据类型主要用于返回ROWID伪列

在用子查询创建表时，LONG列不会被复制。LONG 列不能包括在GROUP BY或ORDER BY子句中。在每个表中只能有一个LONG列。
在LONG列上不能定义约束。通常用情况下使用CLOB列而不是LONG列。

时间数据类型
DATE类型，时间范围：从公元前4712.1.1到公元9999.12.31
TIMESTAMP类型：精度更高的日期时间型
INTERVAL类型：间隔型，又分为两种
- INTERVAL YEAR TO MONTH
- INTERVAL DAY TO SECOND

TIMESTAMP类型
它是精度更高的时间期数据，使用示例：
Timestamp ：缺省时小数秒精度为6
TIMESTAMP(n) ：指定小数秒精度为7
Timestamp WITH TIME Zone
Timestamp WITH LOCAL Time Zone

Create Table ts_test
(dt Date,
ts Timestamp,
ts1 TIMESTAMP(7),--精度7指示小数秒的精度，如果不指定，小数秒的默认精度是6。
ts2 Timestamp WITH TIME Zone,
ts3 Timestamp WITH LOCAL Time Zone);
Insert Into Ts_Test Select Sysdate, Sysdate, Sysdate, Sysdate, Sysdate From Dual;
Select * From Ts_Test
TIMESTAMP WITH TIME ZONE是TIMESTAMP的一个变量，它对TIMESTAMP值进行一个时区转换，在本地时间和UTC 之间，小时和分钟的时区转换是不同的。
UTC代表协调世界时—以前的格林尼治标准时间。如果两个TIMESTAMP WITH TIME ZONE在UTC中代表同一时刻，它们的值被认为是相同的，而不管存储在数据中的TIME ZONE偏移。
因为TIMESTAMP WITH TIME ZONE也可以存储时区信息，它特别适合记录那些必须组合或协调地理区域的日期信息。
例如，
TIMESTAMP '1999-04-15 8:00:00 -8:00'
与
TIMESTAMP '1999-04-15 11:00:00 -5:00'
是相同的。
美国西部标准时间 8:00 a.m. 和东部标准时间11:00 a.m. 是相同的。
该时间也可以被指定为：
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
注：小数秒精度指定SECOND日期时间字段的小数部分数字的数目，其范围是0到9，默认是6。
TIMESTAMP WITH LOCAL TIME数据类型，TIMESTAMP WITH LOCAL TIME ZONE 是TIMESTAMP的另一个变量，
它对TIMESTAMP值进行一个时区转换，存储在数据库中的数据被格式化为数据库时区，时区的转换不被作为列数据的一部分存储；
Oracle 以本地会话时区返回数据，TIMESTAMP WITH LOCAL TIME ZONE数据类型被如下指定：TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE。
不像TIMESTAMP WITH TIME ZONE，你可以指定TIMESTAMP WITH LOCAL TIME ZONE类型作为一个主键或唯一键的一部分。
在本地时间和UTC之间的时区转换(小时或分钟) 是不同的，对于TIMESTAMP WITH LOCAL TIME ZONE是非文字的。
注：小数秒精度指定SECOND日期时间字段的小数部分数字的数目，其范围是0到9，默认是6。
TIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序，在其中你可以用客户系统的时区显示日期和时间。

INTERVAL YEAR TO MONTH
使用语法如下：
INTERVAL YEAR [(year_precision)] TO MONTH
主要用于求日期n年、m月后的日期是那一天
是普通Data日期型的补充
INTERVAL YEAR TO MONTH用年和月日期时间字段存储一段时间。
用INTERVAL YEAR TO MONTH表示两个日期时间值的差，该差值只有年和月的部分。例如，你可能用该值设置一个往后120个月的提醒日期，或检查是否从某个特定的日期后6 月已过去。
指定 INTERVAL YEAR TO MONTH 如下语法：
INTERVAL YEAR [(year_precision)] TO MONTH
year_precision 是在YEAR日期时间字段中数字的数目，年精度的默认值是2。
例如：
CREATE TABLE te1
(loan_duration INTERVAL YEAR (3) TO MONTH);
INSERT INTO te1 (loan_duration)
VALUES (INTERVAL '120' MONTH(3));
SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy')
FROM te1;

INTERVAL DAY TO SECOND
使用语法：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
主要用于求日期n天、m小时后的准确时间
间隔型数据无法直接查看，必须和一个日期进行运行后才有效。
INTERVAL DAY TO SECOND根据天、小时、分和秒存储一段时间。用INTERVAL DAY TO SECOND来表示两个日期时间值精确的差。
例如，你可能用该值设置一个往后36个小时的提醒，或记录一个赛跑的开始和结束之间的时间。为了表示很长的时间跨度，包括很多年，用很高的精度，你可以用一个很大的值表示天的一部分。
指定 INTERVAL DAY TO SECOND 如下：
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
day_precision 是在DAY日期时间字段中数字的数目，可接受的值的范围是0到9，默认是2。
fractional_seconds_precision 是在SECOND日期时间字段中数字的数目，可接受的值的范围是0到9，默认是6。
CREATE TABLE te2
(day_duration INTERVAL DAY (3) TO SECOND);
INSERT INTO te2 (day_duration)
VALUES (INTERVAL '180' DAY(3));
SELECT sysdate + day_duration "Half Year"
FROM te2;

ALTER TABLE语句
ALTER TABLE语句是经常使用的修改表定义的语句
它是一条DDL语句
它可以完成如下操作：
- 添加列
- 修改列
- 删除列
可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。
你可以改变数据类型，但仅在列中只包含空值时。
你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到CHAR 数据类型仅当列中只包含空值时，或者你不改变列的大小时。
对默认值的改变仅影响以后插入的列。例如：
ALTER Table dept80 Modify (last_name VARCHAR2(30));
用ALTER TABLE语句，一次只能有一列被删除。表被修改后必须至少保留一列。一旦一列被删除，它不能再恢复。当一列从表中被删除时，该表中任何其他的被用SET UNUSED选项标记列也被删除。
SET UNUSED信息被存储在USER_UNUSED_COL_TABS字典视图中。
DROP UNUSED COLUMNS选项
DROP UNUSED COLUMNS从表中删除当前所有被标记为不使用的列，当你想要从表中的不使用列回收额外的磁盘空间时你可以用该语句，如果表中不包含不使用列，该语句不返回错误。
Alter Table dept80 Set Unused (last_name);
Alter Table dept80 Drop Unused COLUMNS;

删除表
删除表的语法：DROP TABLE 表名
表的索引将被随之删除
表相关的视图和同义词被保留但都处于无效状态
所有的数据从表中删除。任何视图和同义词被保留但无效。任何未决的事务被提交。只有表的创建者或具有DROP ANY TABLE权限的用户才能删除表。
由于引入了回收站，因此要彻底删除一个表需要加PURGE关键字。

改变对象名称
执行RENAME语句，改变一个表、视图、序列或同义词，你必须是对象的所有者。DDL语句包括RENAME语句，该语句被用于改变表、视图序列或同义词的名字。

截断表
截断表的语法：TRUNCATE TABLE 表名
截断表的作用是删除表的所有行并释放表所占用的空间
截断表和删除数据所有行区别就是，使用Delete
DELETE语句也可以从表中删除所有的行，但它不能释放存储空间。TRUNCATE命令更快一些，用TRUNCATE语句删除行比用DELETE语句删除同样的行快一些，
原因如下：TRUNCATE语句是数据定义(DDL) 语句。并且不产生回滚信息。截断一个表不触发表的删除触发器。
如果表是一个引用完整性约束的父表，你不能截断该表，在发布TRUNCATE语句之前禁用约束。例如：
TRUNCATE TABLE detail_dept;

给表添加注释
可以使用COMMENT给表、列、视图等对象添加注释
可以通过如下视图查看添加到表或列的注释：
- ALL_COL_COMMENTS
- USER_COL_COMMENTS
COMMENT语句给一个列、表、视图或快照添加一个最多2K字节的注释。注释被存储在数据字典中，并且可以通过下面的数据字典视图查看COMMENTS列：
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
语法：
COMMENT ON TABLE table | COLUMN table.column IS 'text';
table 是表的名字column 是表中列的名字text 是注释的文本。你可以用设置注释为空串('') 的办法从数据库中删除一个注释：
COMMENT ON TABLE employees IS '';
COMMENT ON TABLE employees IS 'Employee Information';