oracle数据库对象–表和视图

表与视图是oracle数据库对象中最基本的也是最主要的两个对象，在开发应用中使用的也是最多的。该篇主要针对这两点对表和视图的一些操作(DDL,DML)进行介绍。

1.表

表是作为oracle数据库存储数据的一种数据结构，就相当于我们在java中用集合list，或者数组存储我们的数据一样，表是一种二维结构，有行 有列，我们把相同类型的数据归为一列，比如每个人都有姓名，我们就把姓名归为一列，年龄归为一列，而行对应着每个人的数据，比如第一行是小红的姓名和年龄，第二行是小强的姓名和年龄。通过创建表然后向表中插入数据，最终实现对信息的存储。 

下面将介绍一下对表操作的一些sql语句:



（1）、创建表

1.第一种方式直接创建

create table table_name(
字段名1 字段类型 【约束】，
字段名2 字段类型 【约束】，
字段名3 字段类型 【约束】--其中【】代表该项可写可不写
)
--eg创建用户表，要求字段有用户编号，姓名，性别，年龄；其中编号为主键，姓名不能为空。
create table users(
uid number(10) primary key,
name varchar2(10) not null,
sex varchar2(6) check sex in('男'，'女')，
age number(3)
);
--其中约束也是数据对象之一，下篇再对其做详细介绍
2.通过复制的方式创建，也就是我们想要常见的表结构已经存在，我们可以拷贝原表
create table users_2 
as
select *from users ;
--如果我们只想拷贝表结构而不拷贝表数据可以在后面加上where条件 where 1=2;
--这样我们就创建了一个新表user_2,其结构和表users表一模一样，当然我们也可以选择users表中的某些字段进行拷贝。

--注意点：1.表名必须以字母开头，当然还有一些其他注意事项，我们想如果你们不是刻意的话很难涉及到的，就不说了。
--2.在数据类型上我们了解几个orace常用的数据类型如 数字使用的是number了（size）,所有的数字都可以用number表示的，
--所以小数也不例外。字符我们通常用的是varchar2(size),在mysql用的就是varchar(size),时间用date ,二进制文件
--如图片数据用blob.其他数据类型则根据需要再去了解。

（2）、删除表



--删除表就较为简单，记得语法结构就行。
第一种删除表数据，保留表结构
(1)delete from users;
--DELETE FROM TABLE_NAME;
--这种方法会删除表中所有数据，但是会保留表的结构，但是不会释放表之前所占有的存储空间，可以执行回滚操作。
--将所有已经删除的数据进行恢复

(2)truncate table users;
--TRUNCATE TABLE TABLE_NAME;
--这种方法也可以删除表数据，保留表的结构，但是这里比delete方法更彻底一点在于，其会将表的存储空间设为初始值，且
--操作之后无法回滚。

第二种删除表数据同时删除表结构
drop table users;
--DROP TABLE TABLE_NAME
--该方法将彻底的对表进行删除，包括表数据和表结构，且不能回滚，然后回收之前占有的所有内存空间


（3）、修改表结构

前面介绍了表的创建和删除，下面将介绍当我们创建表之后，如何对表的结构进行修改。
--添加一列(用户表添加身高一项，类型为number类型)
alter table users add (uheight number(3));
--ALTER TABLE TABLE_NAME ADD(COL_NAME TYPE);

--删除一列（删除用户表的身高一项）
alter table users drop column uheight;
--ALTER TABLE TABLE_NAME DROP COLUMN COL_NAME

--修改一列，将name 字段大小变为20
alter table users modify (name varchar2(20));
--ALTER TABLE TABLE_NAME MODIFY(COL_NAME TYPE);

--重名命一列，将name重命名为username
alter table users rename column name to username;
--ALTER TABLE TABLE_NAME RENAME COLUMN OLD_NAME TO NEW_NAME;

--修改表users名称为vipusers
rename users to vipusers;
--RENAME OLD_TABLENAME TO NEW_TABLENAME;


以上就介绍了表的一些DDL操作，下面将介绍我们在项目中应用最多的DML语句，即数据的增删改查，这里就简单介绍一些最基本的DML操作,后文应该可能还会有关于复杂一点的DML操作，主要也就是查询那块。

（4）、基本DML语句结构

---------------------------------------------添加--------------------------------------------------
--向users表中添加一条数据
insert into users values(1001,'黄小州','男',18);
--INSERT INTO TABLE_ANME VALUES(VALUE1,VALUE2,VALUE3,VALUE4,...)

--向users表中只插入编号，姓名，性别；
insert into users(uid,name,sex) values(1002,'蠢小蛙','男');
--INSERT INTO TABLE_NAME(COL_NAME1,COL_NAME2,COL_NAME3,COL_NAME4) VALUES(VALUE1,VALUE2,VALUE3,VALUE4)
-------------------------------------------查询--------------------------------------------
SELECT COL_NAME1,COL_NAME2,COL_NAME3 FROM TABLE_NAME WHERE ....
--查询users表所有数据
select * from users;

--查询users表编号为1001的用户的信息
select * from users where uid=1001;
--查询users表用户的姓名和性别
select name,sex from users ;

查询是我们在开发中使用最多的地方，这里只是写了最简单的查询语句后文将推出更复杂的一些查询，包括多表连接查询，
单行行数，分组函数，高级子查询等等。
--注意，以后在复杂的一些条件查询中要遵守的一个约定就是 where语句要在 from 语句后面；
-------------------------------------------修改------------------------------------------
UPDATE TABLE_NAME SET COL_NAME = ? [WHERE  .....]

--修改users表的编号为1001的年龄为12；
update users set age = 12 where uid = 1001;

------------------------------------------删除------------------------------------
DELETE FROM TABLE_NAME WHERE COL_NAME = ?
--删除users表所有数据
delete from users ;
--删除users表中姓名为黄福州的字段
delete from users where name ='黄福州'；



2.视图

首先我们需要了解为什么会有视图的存在，它存在的意义在哪里，之前就提过表和视图是数据库基本对象中的两个很重要的对象。可见视图的地位也是非常重要的。 

1.为什么要用视图？ 

答：举个例子，对于公司人员信息表上都有他们的薪资情况，财务再根据数据库里薪资来给每个人发放工资，所以工资这一栏是非常重要的，所以为了保证运转条理性，我们一般不能让数据库管理员看到这一栏的，毕竟管理员也是员工，对于这些敏感的信息，可能他看不到对大家都好。那么就衍生了该如何解决这个问题呢，有的人会说创建一个新的员工表，里面没有员工工资一栏，但是这个方法可是非常不明智的，因为当我们把原来的数据修改之后岂不是还要再新表上还要做一次修改，而且在新表上做了修改还要更新到主表，中间就会出现很多问题的，此时利用视图则是最合理的一个方法。 

视图是从已存在表上抽出逻辑相关的数据集合，其本身和表的区别不大，都是对数据一种存储，只不过我们可以在已有表的基础上抽取一部分我们想要的数据。 

2.我们修改视图之后会更新到基表中吗？ 

答：这个是肯定的，视图的意义就是对基表中的数据进行一部分提取后然后提供给其他人操作的，如果不能更新基表，那和新建一个表就毫无区别了，同时对于基表中数据的改变也能立马更新视图。

（1）、创建视图



视图的创建和备份一个表十分类似，就是改个关键字，从table改为view
CREATE VIEW VIEW_NAME
AS
SELECT ... FROM ...TABLE_NAME WHERE ...[WITH READ ONLY]

eg:
create view users_view
as 
select uid,name,sex from users;
如果期望对视图只能查看不能进行任何操作，则只需要在后面追加WITH READ ONLY
--从创建语句来看，视图也可以理解成对select语句的一个存储。


（2）、操作视图

因为视图只是对基表数据进行一次提取存储，所以本质上和表区别不大，所以在DML语句上也没有太大区别， 

所以这里就不做详细介绍了，就是和表操作一样的，insert ,update ,delete,select,都相同。但是，很多时候视图是比我们的表要复杂了， 

比如在基表中有基本工资和奖金两个字段，而我们在新建的视图中就创建了一个新的字段用来接收这两个字段的和，总工资。对于这种视图我们就不能使用update,和insert,以及delete语句来操作视图了，因为这在逻辑上也会造成很多说不通。 

列举一下最简单的视图操作语句：



--查询视图
SELECT * FROM VIEW_NAME [WHERE...] 

--修改视图
1.UPDATE VIEW_NAME SET COL_NAME  = ? [WHERE ...]
2.CREATE OR REPLACE VIEW_NAME AS
SELECT COL_NAME1 别名，COL_NAME2 别名2,COL_NAME3 别名3 FROM TABLE_NAME [WHERE]

--删除视图
1.彻底删除
DROP VIEW VIEW_NAME;
2.删除数据
DELETE FROM VIEW_NAME [WHERE ...]


视图注意点： 

当视图定义中包含以下元素之一时不能使insert: 

1.组函数； 

2.GROUP BY 子句； 

3.DISTINCT 关键字； 

4.ROWNUM 伪列； 

5.列的定义为表达式； 

6.表中非空的列在视图定义中未包括。

当视图定义中包含以下元素之一时不能使用update: 

1.组函数； 

2.GROUP BY子句; 

3.DISTINCT 关键字; 

4.ROWNUM 伪列; 

5.列的定义为表达式.

当视图定义中包含以下元素之一时不能使用delete: 

1.组函数; 

2.GROUP BY 子句; 

3.DISTINCT 关键字; 

4.ROWNUM 伪列.

表和视图的基本操作就介绍到这里了，这里是最基本的操作，重在了解语法结构，在真正生产过程中，对表和视图的操作远比这些复杂，下文将介绍对表查询中的复杂部分，包括单行函数和多行函数，高级子查询等。

  
  视图与索引:

  是两个完全不同的对象，但是有一点是相同的，它们都是与一个表或数据库相关联，尽管每一个对象只能与一个特定的表相关联，但它们还是通过对数据的预排序和预定义显著地提高了表的工作性能。

 
 
   一:索引：索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需的数据。当进行数据检索时，系统先搜索索引，从中找到数据的指针，再直接通过指针从表中取数据！

   (1）占用存储空间

   (2）在表中执行insert、delete、update操作时，将有额外的操作来维护索引

   (3）过多的索引起反作用


   索引：类似目录，会让你的查询更快速，更高效，适用于访问大型数据库。

 

   索引的分类

   有3种索引类型：聚集索引、非聚集索引和唯一索引。

   如果表中存在聚集索引，则非聚集索引使用聚集索引来加快数据查询。
   <1>.聚集索引

   聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。可以在表的任何列或列的组合上建立索引，实际应用中一般定义成主键约束的列建立聚集索引。

   <2>.非聚集索引

   非聚集索引不会对表和视图进行物理排序。如果表中不存在聚集索引，则表示未排序的。在表或视图中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。

   <3>.唯一索引

   唯一索引不允许两行具有相同的索引值。只要列中数据是唯一的，就可在同一个表上创建一个唯一的聚集索引。如果必须实施唯一性以确保数据的完整性，则应在列上创建UNIQUE或PRIMARY KEY约束，而不要创建唯一索引。
    
 
   索引优点：查询快

   1:一般是作用于where子句所给出的条件相匹配的行

      一是在关联操作中与其他数据表所匹配的行。

   2:对于使用mix() 和max()函数的查询的列

   3:经常使用order by 和group by的列

   4:索引可以加快查询速度，不用扫描整个表


   索引缺点

   (1)索引虽然加快查询的速度，但是会降低写入操作，比如插入，修改，删除数据

   (2)索引要占据磁盘空间，索引越多占据空间越大

   (3)占用存储空间

   (4)在表中执行insert、delete、update操作时，将有额外的操作来维护索引

   (5)过多的索引起反作用
    建立索引的原则:

    创建索引虽然可以提高查询速度，但是它是牺牲一定的系统性能。因此，在创建时，哪些列适合创建索引，哪些列不适合创建索引，需要进行判断，具体以下原则:

    

    <1>.有主键的数据列要建立索引。因为主键可以加速定位到表中的某一行。

    <2>.有外键的数据列要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接。

    <3>.对于经常查询的数据列最好建立索引。

        A.对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间。

        B.经常用在WHERE子句中的数据列，将索引建立在WHERE子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间。
    

   

   

   4，全文索引

   创建索引，默认是asc排序，使用btree

   create  index  index_name   on  tbl_name （column  asc{default}|desc） 

   如果相关表已经使用Memory引擎建立，则使用hash索引

   create index  index_name  using btree|hash   on   on  tbl_name （column  asc)


   散列索引对于“=”和“<=>”操作进行匹配时速度极快，但是对于范围查找和比较查找比较慢
   唯一索引

   create  unique  index  index_name  on tbl_name  （column）
   

   注意：(1）如果给表中某字段加了 主键约束 或是 唯一约束 ，这时会自动帮你 建立对应这个字段的 唯一索引

         (2）索引不要轻易的建立
   
 
   二:视图:就是一张虚拟表，视图就是一个子查询！实际当中的数据依然存在实际的表里面，只不过取的时候是根据这个视图（也就是这个子查询）从实际当中的表里面取出来.

   

   视图由视图名和视图定义两个部分组成。视图是从一个或多个表导出来的表，它实际上是一个查询结果，视图的名字和视图对应的查询存储在数据字典中。

    

   视图：将查询语句压缩，使大部分查询语句放在服务端，而客户端只输入你要查询的信息，而不用写出大量的查询代码，这其中也是一个封装的过程。
   优点:简化查询缺点：你建视图建多了，如果这表结构突然改了，那么你视图也要跟着改！增加了你维护的支出！所以建的view越多，维护起来越麻烦.
   创建视图语法

   create view  <view name>[<column list>]   as   <table expression>  [with [cassaded | local ] check option]

   创建视图

   create  view  view_name  as  select  column1,column2  from tbl_name  where   xxx>xx

   创建视图时指定列

   create  view  view1 (va1,va2,va3)  as  select  va1,va2,va3 from tbl  where  va1<num;

    
   三、视图的作用

   1、提高了用户安全功能

   2、可以进行单位换算

   3、创建一个新的虚拟表格式

   4、简单的结构化复合查询


   创建视图的限制：

   在创建视图时，还要注意试图必须满足以下几点限制：

   <1>.不能将规则或者DEFAULT定义关联于视图。

   <2>.定义视图的查询中不能含有ORDER BY\COMPURER\COMPUTER BY 子句和INTO关键字

   <3>.如果视图中某一列是一个算术表达式、构造函数或者常数，而且视图中两个或者更多的不同列拥有一个相同的名字(这种情况通常是因为在视图的定义中有一个连接，而且这两个或者多个来自不同表的列拥有相同的名字)，此时，用户需要为视图的每一列指定列的名称。
 
   
 
 
 

这三个视图都是存在于SQL Server的每个数据库中。

在SQL Server 2000中，它们都是系统表，而不是视图。

关于两个版本中系统表和系统的视图的对应关系，参考：http://technet.microsoft.com/zh-cn/library/ms187997.aspx
 

sys.all_objects：显示所有架构范围内的用户定义对象和系统对象

，参考 http://technet.microsoft.com/zh-cn/library/ms178618.aspx

sys.sysobjects,sys.objects：用户在数据库中创建的每个对象(例如约束、默认值、日志、规则以及存储过程)都对应一行，

参考http://technet.microsoft.com/zh-cn/library/ms177596.aspx


sys.objects 不显示 DDL 触发器，因为它们不是架构范围内的对象。所有触发器（包括 DML 和 DDL）均位于 sys.triggers 中。sys.triggers 支持对各种触发器应用混合名称范围规

则。


在自己的数据库中测试发现，sys.objects，sys.sysobjects视图的内容是完全一致的。

这三个视图的结构比较类似，其中饱含type，id等属性。其中type的取值代表了各种不同的对象类型。对于触发器或者约束对象，parent_obj属性表示父表的id。

Name, ID相关的系统函数：

    SCHEMA_NAME ( [ schema_id ] )：返回与架构 ID 关联的架构名称。

    OBJECT_NAME ( object_id [, database_id ] )：返回架构范围内对象的数据库对象名称。

    OBJECT_ID (object_name [,'object_type' ] )返回架构范围内对象的数据库对象标识号，比包括触发器等；
若要返回 DDL 触发器的对象标识号，请使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'。

 
列名
          

            数据类型
          

            说明
          

name



            sysname
          


对象名称。


object_id



            int
          


对象标识号。在数据库中是唯一的。
 


principal_id



            int
          


如果不是架构所有者，则为单个所有者的 ID。默认情况下，架构包含的对象由架构所有者拥有。不过，通过使用 ALTER AUTHORIZATION 语句更改所有权可以指定备用所有者。
如果没有备用的单个所有者，则为 NULL。
如果对象类型为下列类型之一，则为 NULL：
C = CHECK 约束
D = DEFAULT（约束或独立）
F = FOREIGN KEY 约束
PK = PRIMARY KEY 约束
R = 规则（旧式，独立）
TA = 程序集（CLR 集成）触发器
TR = SQL 触发器
UQ = UNIQUE 约束


schema_id



            int
          


包含该对象的架构的 ID。
始终包含在 sys 或 INFORMATION_SCHEMA 架构中的架构范围内的系统对象。


parent_object_id



            int
          


此对象所属对象的 ID。
0 = 不是子对象。


type



            char(2)
          


对象类型：
AF = 聚合函数 (CLR)
C = CHECK 约束
D = DEFAULT（约束或独立）
F = FOREIGN KEY 约束
FN = SQL 标量函数
FS = 程序集 (CLR) 标量函数
FT = 程序集 (CLR) 表值函数
IF = SQL 内联表值函数
IT = 内部表
P = SQL 存储过程
PC = 程序集 (CLR) 存储过程
PG = 计划指南
PK = PRIMARY KEY 约束
R = 规则（旧式，独立）
RF = 复制筛选过程
S = 系统基表
SN = 同义词
SQ = 服务队列
TA = 程序集 (CLR) DML 触发器
TF = SQL 表值函数
TR = SQL DML 触发器
U = 表（用户定义类型）
UQ = UNIQUE 约束
V = 视图
X = 扩展存储过程


type_desc



            nvarchar(60)
          


对对象类型的说明：
AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE


create_date



            datetime
          


对象的创建日期。


modify_date



            datetime
          


上次使用 ALTER 语句修改对象的日期。如果对象为表或视图，则创建或修改表或视图的聚集索引时，modify_date 也会随之更改。


is_ms_shipped



            bit
          


对象由内部 SQL Server 组件创建。


is_published



            bit
          


对象为发布对象。


is_schema_published



            bit
          


仅发布对象的架构。



				
 备注
			

				
可将 OBJECT_ID
、OBJECT_NAME
 和 OBJECTPROPERTY
() 内置函数应用于 sys.objects 中显示的对象。
此视图有一个具有相同架构的版本，名为 sys.system_objects
，该版本显示系统对象。还有另一个同时显示系统对象和用户对象的视图，名为 sys.all_objects
。所有这三个目录视图的结构都相同。
在此版本的 SQL Server 中，扩展索引（例如 XML 索引或空间索引）将视为 sys.objects 中的内部表（type = IT，type_desc = INTERNAL_TABLE）。对于扩展索引：

          name 是索引表的内部名称。
        

          parent_object_id 是基表的 object_id。
        

          is_ms_shipped、is_published 和 is_schema_published 列设置为 0。
        




				
 权限
			

				

        在 SQL Server 2005 及更高版本中，目录视图中仅显示用户拥有的安全对象的元数据，或用户对其拥有某些权限的安全对象的元数据。有关详细信息，请参阅元数据可见性配置
。




				
 示例
			

				
A. 返回在最近 N 天内修改过的所有对象

运行以下查询之前，请使用有效值替换 <database_name>
 和 <n_days>
。


复制代码



USE <database_name>;

GO

SELECT name AS object_name 

  ,SCHEMA_NAME(schema_id) AS schema_name

  ,type_desc

  ,create_date

  ,modify_date

FROM sys.objects

WHERE modify_date > GETDATE() - <n_days>

ORDER BY modify_date;

GO




B. 返回指定存储过程或函数的参数

运行以下查询之前，请使用有效名称替换 <database_name>
 和 <schema_name.object_name>
。


复制代码



USE <database_name>;

GO

SELECT SCHEMA_NAME(schema_id) AS schema_name

    ,o.name AS object_name

    ,o.type_desc

    ,p.parameter_id

    ,p.name AS parameter_name

    ,TYPE_NAME(p.user_type_id) AS parameter_type

    ,p.max_length

    ,p.precision

    ,p.scale

    ,p.is_output

FROM sys.objects AS o

INNER JOIN sys.parameters AS p ON o.object_id = p.object_id

WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')

ORDER BY schema_name, o.object_name, p.parameter_id;

GO




C. 返回数据库中的所有用户定义函数

运行以下查询之前，请使用有效数据库名称替换 <database_name>
。







USE <database_name>;

GO

SELECT name AS function_name 

  ,SCHEMA_NAME(schema_id) AS schema_name

  ,type_desc

  ,create_date

  ,modify_date

FROM sys.objects

WHERE type_desc LIKE '%FUNCTION%';

GO




D. 返回架构中每个对象的所有者。

运行以下查询之前，请使用有效名称替换所有的 <database_name>
 和 <schema_name>
。


USE <database_name>;

GO

SELECT 'OBJECT' AS entity_type

    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name

    ,name 

FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'

UNION 

SELECT 'TYPE' AS entity_type

    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name

    ,name 

FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 

UNION

SELECT 'XML SCHEMA COLLECTION' AS entity_type 

    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name

    ,xsc.name 

FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s

    ON s.schema_id = xsc.schema_id

WHERE s.name = '<schema_name>';

GO