SQL学习自我总结（八）

时间：2010-08-25 来源：ji_chunhui

常用的数据库对象有：表、视图、序列、索引、同义词。
表也可以被称为堆组织表
表最多可以有1000列表的分类
表可以分为两大类：用户表、数据字典
用户表：由用户创建和维护的表
数据字典：由oracle服务器创建和维护的表，数据字典表的owner是sys，普通用户无法直接访问，数据字典里面包含数据库信息。
因为数据字典表的基表的信息看上去不是怎么太直观，也很难理解。所以用户一般访问的是数据字典的视图。存储在数据字典中的信息包括oracle服务器用户的名字，被授予用户的权限，数据库对象名，表结构和审计信息。数据字典视图有四种：
user_ 这些视图包含关于用户所拥有的对象的信息
all_ 这些视图包含所有用户可以访问的表的信息
dba_ 这些视图时受限制的视图，它们只能被分配有dba角色的用户所访问
v$ 这些视图是动态性能的视图，包含数据库的性能，存储器和锁的信息表和列的命名规则
表命名和列命名必须以字母开始，必须是1-30个字符长度
通一个用户所拥有的对象之间不能重名，不能用oracle服务器的保留字
表和列名的大小写是不敏感的 create table语句创建表
范例：
create table jocky
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
); 用子查询语法创建表
此种方法既可以创建表还可以将子查询返回的行插入新创建的表中。
创建原则：
被创建的表要带指定的列名，并且由select语句返回的行插入到新表中
如果给出了指定的列，那么列的数目必须等于子查询的select列表的列数目
如果没有给出指定的列，表的列名应该是和子查询中的列名相同
如果不需要在创建的时候插入数据，则可以在子查询的where子句里面使条件为faise
范例：
create table jocky
as select employee_id, last_name, salary*12 annsal, hire_date
from employees
where department_id = 80; create table jocky
as select employee_id, last_name, salary*12 annsal, hire_date
from employees
where 1 = 2; 引用表
引用当前用户的表
引用其他用户的表
查看数据字典
查看用户所拥有的不同的对象类型
查看本用户所拥有的表、视图、同义词、序列
范例：
引用当前用户的表
select * from employees;
引用其他用户的表
select * from jocky.employees;(引用其他人的表需要有相关的权限)
查询数据字典表
select table_name from user_tables;
查看本用户所拥有的不同的对象类型
select distinct object_type from user_objects;
查看本用户所拥有的表、视图、同义词和序列
select * from user_catalog; 常用数据类型介绍
oracle的基本的数据类型
varchar2(size) 可变长度字符数据
char2(size) 固定长度字符数据
number 右边的数字长度
date 日期和时间值
long 最大2G的可变长度字符数据
clob 最大4G的字符数据
raw(size) 原始二进制数据(必须指定最大长度，最大长度为2000)
long raw 可变长度原始二进制数据，最大2G
blob 二进制数据，最大4G 时间数据类型
date类型：定义年份和日期值，范围为4712.1.1到9999.12.31
timestamp类型：它是date数据类型的一种扩展，它的记录比date更详细，除了date记录的部分还记录了小时、分、秒的值，以及秒的小数值
interval year to month：允许时间作为年和月的间隔被存储
interval day to second：允许时间作为天、小时、分和秒的间隔被存储 timestamp类型
timestamp是精度很高的时间数据类型，默认情况下小数秒精度为6，范围是0-9。
范例：
SQL> create table jocky
2 (ts timestamp,
3 ts2 timestamp(2),
4 ts3 timestamp with time zone,
5 ts4 timestamp with local time zone);
Table created.
SQL> insert into jocky
2 select sysdate,
3 sysdate,
4 sysdate,
5 sysdate
6 from dual;
1 row created.
SQL> select ts from jocky;
TS
------------------------------
03-AUG-10 12.05.30.000000 AM
SQL> select ts2 from jocky;
TS2
------------------------------
03-AUG-10 12.05.30.00 AM
SQL> select ts3 from jocky;
TS3
----------------------------------------
03-AUG-10 12.05.30.000000 AM +08:00
SQL> select ts4 from jocky;
TS4
------------------------------
03-AUG-10 12.05.30.000000 AM
具体含义看上面的输出就能明白了！ alter table语句
它是一条DDL语句
它可以完成：添加列、修改列、删除列
添加列：
SQL> desc jocky;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(10)
SEX                                                VARCHAR2(5)
SQL> alter table jocky add
2 (salary number,
3 address varchar2(50));
Table altered.
SQL> desc jocky;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(10)
SEX                                                VARCHAR2(5)
SALARY                                             NUMBER
ADDRESS                                            VARCHAR2(50)
修改列：
列的修改包括修改列的数据类型，大小和默认值
SQL> alter table jocky modify
2 (address varchar2(30));
Table altered.
SQL> desc jocky;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(10)
SEX                                                VARCHAR2(5)
SALARY                                             NUMBER
ADDRESS                                            VARCHAR2(30)
注意：在减少一个列的宽度时，只能是列中只包含空值或表中没有行时
      在改变(或转换char--varchar)一个列的数据类型时，只能是列中只包含了空值
范例：
SQL> insert into jocky values
2 (01,'jocky','man',10000,'jiangsu');
1 row created.
SQL> select * from jocky;                                                                                                                                            ID NAME       SEX       SALARY ADDRESS
---------- ---------- ----- ---------- ------------------------------
         1 jocky      man        10000 jiangsu
SQL> alter table jocky modify
2 (address number);
(address number)
*
ERROR at line 2:
ORA-01439: column to be modified must be empty to change datatype
删除列：
删除列的时候，该列中可以有数据，也可以没有数据。用alter table语句时一次只能删除一个列
SQL> alter table jocky drop column address;
alter table jocky drop column address
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS
删除其他用户的表中的列是可以的！
SQL> show user
USER is "SYS"
SQL> create table benbo.test as select * from dba_objects;
Table created.
SQL> alter table benbo.test drop column object_name;
Table altered.
注意：当一列从表中被删除时，该表中任何其他的被用set unused选项标记的列也会被删除。
set unused选项标记一个或多个列作为不使用的。指定该子句不会真的从表的每一行中删除目标列(即不会恢复这些列所占用的磁盘空间)，因此，set unused选项标记的执行相应时间会比执行drop子句快一些。不使用的列就好像被删除了一样的被处理，即使它们的列数据还保留在表里边，在一列已经被标记为不使用后，你就不能访问该列了。一个select *查询不会从标记为不使用的列返回数据。另外，在使用describe命令时，被标记为不使用的列的名字和类型将不再显示，并且你可以用一个与不使用列相同的名字添加一个新列到表中。
set unused信息被存储在user_unused_col_tabs字典中
drop unused columns从表中删除所有被标记为不使用的列
范例：
alter table jocky set unused (id);
alter table jocky drop unused columns; 删除表
当你删除一个表时，所有与其相关的索引也被删除。所有表中的数据都被删除，任何视图和同义词被保留但是无效。任何未决的事务被提交。
因为oracle 10g引入了回收站，因此要想彻底删除一个表得用如下命令：
drop table jocky purge; 改变对象名称
改变时你必须是对象的所有者(对象包括：表、视图、序列、同义词)
rename jocky to sunboy;
SQL> desc jocky;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
QID                                                NUMBER
MSGNUM                                             NUMBER
MSGID                                              RAW(16)
SUB                                                NUMBER
SEQNUM                                             NUMBER
RSUBS                                              AQ$_RECIPIENTS
SQL> select * from jocky;
no rows selected
SQL> rename jocky to sunboy;
Table renamed.
SQL> desc sunboy;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
QID                                                NUMBER
MSGNUM                                             NUMBER
MSGID                                              RAW(16)
SUB                                                NUMBER
SEQNUM                                             NUMBER
RSUBS                                              AQ$_RECIPIENTS 截断表
截断表的作用是删除表的所有行并释放表所占用的空间
截断表和删除数据所有行区别：delete删除所有行并不会释放表所占用的空间，truncate比起delete更快一些
同样执行这样的命令，你必须是该对象的所有者或者有delete table系统权限
范例：
truncate table jocky; 给表添加注释
可以用comment语句给一个列、表、视图或快照添加一个最多2K字节的注释
注释将被存储在数据字典中，可以通过下面的数据字典视图查看comments列：
all_col_comments
user_col_comments
all_tab_comments
user_tab_comments
范例：
comment on table jocky is 'employee information';
comment on column jocky.name is 'employee name';
去掉注释：
comment on table jocky is '';
范例：
SQL> comment on table jocky is 'employees information';
Comment created.
SQL> comment on column jocky.qid is 'employees id';
Comment created.