创建与管理表

创建与管理表
以上几次总结中我主要介绍了Oracle的查询操作及组函数、group by字句 having子句等基本内容这一节我将继续介绍Oracle表的一些基本概念及使用方式，希望能够对学习数据库同学有所帮助，以后我将会继续总结oracle的一些知识，总结的知识内容自认为比较全面、实用，希望大家能够有所收益。下面我们来学习oracle的用户、模式和表。
Oracle是通过“模式”来组织和管理这些数据库对象的。Oracle系统中的模式是一系列逻辑数据结构或对象的集合，一个模式只能够被一个数据库用户所拥有，并且模式名称与该用户的名称相同。

    数据库中对象包括：表、视图、索引、序列、同义词等。
 表：基本的数据存储的集合,由行与列组成
 视图：从表中抽出的逻辑上相关的数据集合。
 序列: 提供有规律的数值
 索引：提高查询效率
 同义词：给对象起的别名。

Oracle数据库中的表
1、 用户自定义表
表名和列名的命名规范:
 必须以字母开头
 必须在 1–30 个字符之间
 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
 必须不能和用户定义的其他对象重名
 必须不能是Oracle 的保留字

创建表必须具备的条件：
 必须具备:
CREATE TABLE权限
存储空间
 必须指定:
表名
列名, 数据类型, 尺寸

创建表的语法
      CREATE TABLE [schema.]table
    (column datatype [DEFAULT expr][, ...]);

数据类型如下：

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


创建表的分析：
       在数据库中必须遵循一定的规范 这些规范称为范式.目前关系数据库有6种方式,数据库只需要满足3NF即可,需要使用范式对数据库中表进行处理.

下面我以学生信息为例分析：
   学号,学生姓名,出生日期,学生性别,所属班级,所属院系,系主任姓名,课程编号,成绩.
分析：
   学号：sid          数据类型 varchar2
   学生姓名：sname
   出生日期：sbirth    date
   学生性别:ssex      
   所属班级:sclass
   所属院系：sdep
   系主任姓名:dname
   课程编号:cid
   成绩 grade         number(5,2)
创建学生信息表
根据如上信息可以创建存储的表 学生信息表  包含医以上的全部信息,但非规范。

分析设计过程：
       必须满足3NF范式 但前提必须满足1，2NF
1、 属性不可再分(1NF) ==一对多关系
学生信息表：Sid，sname,sbirth,ssex,sclass,sdep,dname
成绩表： sid cid grade
2、 (前提必须满足1NF )表中的所有非主属性都完全依赖于任一候选键。
Sid->Sname
sid->sbirth
sid->ssex
sid->sclass
sdep->dname
--其他普通属性不应当存在部分依赖于主要属性->此普通属性应当被分离到新的表中去
s(sid,sname,sbirth,ssex,sclass,sdep)
d(sdep,dname)
g(sid,cid,grade)


3、 （前提满足2NF）表中的每一个非主属性都不传递依赖于任何候选键。

s(sid,sname,sbirth,ssex,sclass,sdep)
d(sdep,dname)
g(sid,cid,grade)

在满足2NF后 分析：
Sid->sclass
Sclass->sdep
Sid->sdep
s(sid,sname,sbirth,ssex,sclass)
cd(sclass,sdep)
d(sdep,dname)
g(sid,cid,grade)

2、 Oracle数据字典
2.1查询用户自定义表
         //查询用户定义的表的名称
SQL> select table_name from user_tables;
//查询用户自定表的详细特征
SQL> select * from user_tables
//等效于
SQL> select * from tabs;

2.2查询用户定义的各种数据库对象
SQL> select distinct object_type from user_objects;
OBJECT_TYPE
-------------------
INDEX PARTITION
SEQUENCE
QUEUE
TABLE PARTITION
PROCEDURE
LOB
PACKAGE
PACKAGE BODY
TRIGGER
SYNONYM
TABLE
INDEX
VIEW
TYPE

14 rows selected
2.3 查看用户定义的表、视图、序列和同义词
SQL> select * from user_catalog;

TABLE_NAME              TABLE_TYPE
------------------------------ -----------
USESS                       TABLE
ABC                          TABLE
DEPT                        TABLE
EMP                         TABLE
SALGRADE                 TABLE
PRODUCT_SEQ             SEQUENCE
ORDERS_SEQ               SEQUENCE
ORDERITEM_SEQ          SEQUENCE
USERS                       TABLE
PRODUCT                   TABLE
ORDERS                     TABLE
ORDERITEM                TABLE
TMP                         TABLE
PAGES                       TABLE
STUDENT                   TABLE
COURSE                     TABLE
SC                            TABLE
TEST                         TABLE

18 rows selected
用户自定表详解
使用 ALTER TABLE 语句可以:
 追加新的列
 修改现有的列
 为新追加的列定义默认值
 删除一个列
使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.
追加
ALTER TABLE table
ADD    (column datatype [DEFAULT expr]
   [, column datatype]...);
修改
ALTER TABLE table
MODIFY    (column datatype [DEFAULT expr]
   [, column datatype]...);

删除
ALTER TABLE table
DROP column   (column_name);

修改列的名称
ALTER TABLE table_name rename column old_column_name
to new_column_name


案例：
//采用子查询创建customer表 表的结构域users表完全一致
SQL> create table customer as select * from users;
Table created
SQL> commit; //事务提交
Commit complete
//为customer表添加新的列 email 类型varchar2(20).
SQL> alter table customer add email varchar2(20);//追加
Table altered
SQL> commit;
SQL> desc customer;
Name Type       Nullable Default Comments
-------- ------------ -------- ------- --------
USERNAME VARCHAR2(20) Y                       
PASSWORD VARCHAR2(20)                          
NAME VARCHAR2(20)                          
ADDRESS VARCHAR2(20) Y                       
ZIP     NUMBER(6) Y                       
EMAIL VARCHAR2(20) Y   
//修改某个字段的类型
SQL> alter table customer modify email number(4);//修改

Table altered

SQL> commit;

Commit complete

SQL> desc customer;
Name Type       Nullable Default Comments
-------- ------------ -------- ------- --------
USERNAME VARCHAR2(20) Y                       
PASSWORD VARCHAR2(20)                          
NAME VARCHAR2(20)                          
ADDRESS VARCHAR2(20) Y                       
ZIP     NUMBER(6) Y                       
EMAIL NUMBER(4) Y     
//删除某个字段
SQL> alter table customer drop column email;
Table altered
SQL> commit;
Commit complete
SQL> select * from customer;
//修改表的某个字段的名称
SQL> alter table customer rename column address to addr;
Table altered
SQL> commit;

删除表
 数据和结构都被删除
 所有正在运行的相关事物被提交
 所有相关索引被删除
 DROP TABLE 语句不能回滚
修改对象的名称
 执行RENAME语句改变表, 视图, 序列, 或同义词的名称
 必须是对象的拥有者
//修改对象的名称操作
SQL> rename customer to customers;
清空表
 TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
 TRUNCATE语句不能回滚
 可以使用 DELETE 语句删除数据
//清空表的操作
SQL> truncate table customers;

总结：您已经学会如何使用DDL语句创建, 修改, 删除, 和重命名表.
语句           描述
CREATE TABLE 创建表
ALTER TABLE 修改表结构
DROP TABLE 删除表
RENAME      重命名表
TRUNCATE   删除表中的所有数据，并释放存储空间


Oracle数据库中的视图
视图是从一个表或多个表中导出的虚表,它与常规表不同之处在于它并不存储数据—它只是在一个存储的查询中定义.视图同真实表一样,也包含一系列带有名称的列和数据。

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
视图向用户提供基表数据的另一种表现形式

视图分为简单视图和复杂视图

简单视图：
所谓简单的视图是只引用一个表,并且它只从基础表中检索数据,不允许修改其中的数据；
复杂的视图是引用多个表,视图越复杂、视图内再查询的表达式就越多越复杂.
创建视图的语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

注意：在创建视图时应先赋予创建视图的权限，create view或create any view权限
//切换到管理员然后赋予权限（两种赋予权限的方法）
SQL> grant create any view to dxl;
SQL> grant create view to dxl;

为什么使用视图？
控制数据访问:提供了一种安全机制,通过视图显示数据时,屏蔽用户不应该知道的数据.
简化查询:select语句构造了一个复杂的查询语句,每次使用必须都得调用它,根据复杂查询的语句创建一个视图,来简化查询。

//创建视图时在子查询中给列定义别名
SQL> create view usersview as select username name,password pass from users;
//查询视图
SQL> select * from usersview;
//检索视图的定义
SQL> select * from user_views;

复杂视图：

是通过多个表的连接查询获得的，又称为连接视图
//创建复杂视图（创建两个表的视图 dept和emp表）
//需要查询出两个表的所有字段（使用内连接查询）然后再根据整合后的字段创建视图
SQL> create view empdept as (select e.*,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno);

修改视图CREATE  OR  REPLACE  VIEW 子句
SQL> create or replace view usersview as(select * from users);
//创建带有函数,group by子句的视图
SQL> create or replace view gde as ( select deptno,count(*) cou  from emp group by deptno);
//创建连接视图 带有函数 group by子句 order by 子句
SQL> create view gdeo as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno order by e.deptno;
//在上面的基础上带有having子句
SQL> create view gdeoh as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno having count(*)>3 order by e.deptno;
SQL> create or replace view gdea as(select deptno,count(*) cou from emp group by deptno having count(*)>3 order by deptno);//这个是错误的  不能加括号 语法错误


删除视图的操作
    Drop view 视图名;

Oracle数据库中的序列
序列: 是oacle提供的用于产生一系列唯一数字的数据库对象。
 自动提供唯一的数值
 共享对象
 主要用于提供主键值
 将序列值装入内存可以提高访问效率
创建序列：
要有创建序列的权限 create sequence 或 create any sequence
创建序列的语法
CREATE SEQUENCE sequence  //创建序列名称
       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue
       [{MAXVALUE n | NOMAXVALUE}] //最大值
       [{MINVALUE n | NOMINVALUE}] //最小值
       [{CYCLE | NOCYCLE}] //循环/不循环
       [{CACHE n | NOCACHE}];//分配并存入到内存中

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

SQL> create sequence seq increment by 2 start with 1 maxvalue 10 minvalue 1 nocycle  nocache;
Sequence created

修改序列
修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
修改序列语法：
alter SEQUENCE sequence  //创建序列名称
       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue
       [{MAXVALUE n | NOMAXVALUE}] //最大值
       [{MINVALUE n | NOMINVALUE}] //最小值
       [{CYCLE | NOCYCLE}] //循环/不循环
       [{CACHE n | NOCACHE}];//分配并存入到内存中
修改序列的注意事项：
 必须是序列的拥有者或对序列有 ALTER 权限
 只有将来的序列值会被改变
 改变序列的初始值只能通过删除序列之后重建序列的方法实现
删除序列
 使用DROP SEQUENCE 语句删除序列
 删除之后，序列不能再次被引用
SQL> alter sequence seq increment by 2  maxvalue 5 nocycle  nocache;
Sequence altered
Oracle数据库中的同义词
同义词是表、索引、视图等模式的一个别名。同义词只在Oracle数据库字典中保存其定义描述，因此同义词并不占用任何实际的存储空间。
Oracle中同义词分为两种类型：共有同义词和私有同义词。
共有同义词在数据库中所有的用户都可以使用
私有同义词由创建它的用户所拥有
语法格式：
  CREATE [PUBLIC] SYNONYM synonym_name FOR schema_oject
//创建同义词
SQL> create public synonym merch_info for emp;
Synonym created
//删除同义词
SQL> drop public  synonym merch_info;
Synonym dropped
//若定义为公有同义词 删除时必须添加关键词public
SQL> drop  synonym merch_info;
drop  synonym merch_info
ORA-01434: 要删除的专用同义词不存在
//查看同义词的信息
SQL> desc dba_synonyms;
Name         Type          Nullable Default Comments                                                
------------ ------------- -------- ------- --------------------------------------------------------
OWNER        VARCHAR2(30)                   Username of the owner of the synonym                    
SYNONYM_NAME VARCHAR2(30)                   Name of the synonym                                     
TABLE_OWNER  VARCHAR2(30)  Y                Owner of the object referenced by the synonym           
TABLE_NAME   VARCHAR2(30)                   Name of the object referenced by the synonym            
DB_LINK      VARCHAR2(128) Y                Name of the database link referenced in a remote synonym
以上是除索引外的所有数据库对象，索引将在下一篇文章中详细讲解，请查看下篇文章…