oracle sql 分区查询语句_Oracle数据库常用操作命令

一、启动和关闭Oracle数据库数据库启动

以SYSDBA身份登录

数据库启动命令：STARTUP 【启动选项】

数据库启动三个阶段：

启动Oracle实例(非安装阶段)

由实例安装数据库(安装阶段)

打开数据库(打开阶段)

数据库的关闭

以SYSDBA身份登录

数据库关闭命令：SHUTDOWN 【启动选项】

数据库关闭三个阶段：

关闭数据库

卸载数据库

关闭Oravle实例

二、启动、关闭Oracle监听进程

监听器lsnrctl：提供数据库访问，默认端口1521

为了使客户端用户能连接到Oracle实例，要启动监听

1.启动监听

启动(START)监听是Oracle用户在操作系统下执行的命令，可以直接在LSNRCTL后加参数，也可以在该命令提示符后在进行操作。

2.关闭监听

注意：先启动监听，后启动数据库。

三、表空间创建表空间

参数解释：

tablespacename：表空间名称

DATAFILE：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔

filename：表空间中数据文件的路径和名称

SIZE：指定文件的大小，用K指定千字节大小，用M指定兆字节大小

AUTOEXTEND：用来启用或禁用数据文件的自动扩展

举例：

表空间的管理

(1)调整表空间的大小。当表空间已满的情况下，可以通过ALTER语句来调整表空间的大小。

方法一：更改数据文件的大小，并指明数据文件的存放路径，通过使用RESIZE关键字，用于指定调整后的表空间的大小

方法二：向表空间内添加数据文件。为表空间添加一个新的数据文件

(2)改变表空间的读写状态。

ALTER  TABLESPACE  表空间名  READ   WHITE ;      --使表空间可读写

ALTER  TABLESPACE  表空间名  READ   ONLY ;       --使表空间只读

(3)删除表空间，可以通过DROP语句来删除表空间，再加上表空间的名字即可。

DROP  TABLESPACE  表空间名 [INCLUDING  CONTENTS] ;

其中INCLUDING  CONTENTS是可选项。如果删除仍包含数据的表空间，需要加上该选项。

四、用户管理创建用户

举例：

修改用户的密码

ALTER  USER  用户名  IDENTIFIED  BY  密码；

删除用户。使用DROP USER命令可以删除用户，当用户拥有模式对象时则无法删除用户，而必须使用CASCADE选项以删除用户及用户模式对象。

DROP  USER  用户名  CASCADE；

创建用户前的准备工作：

1)选择用户名和密码

2)识别用户需用于存储对象的表空间

3)决定每个表空间的限额

4)分配缺省表空间和临时表空间

5)创建用户

6)向用户授予权限和角色

五、数据库权限管理系统权限

系统权限是指在数据库中执行某种系统级别的操作，或者针对某一类对象执行某种操作的权利

常用的系统权限如下：

CREATE SESSION：连接到数据库

CREATE TABLE：创建表

CREATE VIEW：创建视图

CREATE SEQUENCE：创建序列

对象权限

对象权限是指针对某个特定模式对象执行操作的权利，只能针对模式对象来设置管理对象权限，包括：表、视图、序列、存储过程等。

Oracle数据用户有两种途径获得权限：

(1)直接向用户授予权限

(2)将权限授予给角色，再将角色授予给一个或多个用户。使用角色能够更加方便和高效地对权限进行管理，所以数据库管理员通常使用角色向用户授予权限，而不是直接向用户授予权限。

Oracle中常用系统预定义角色如下：

(1)CONNECT：拥有连接数据库的权限

(2)RESOURCE：拥有创建表、触发器、过程等权限

(3)DBA：数据库管理员角色，拥有管理数据库的最高权限

新建的用户必须授予一定的权限才能进行相关数据库操作。授权通过GRANT语句，取消授权则通过REVOKE语句。

授予权限语法格式如下：

GRANT   权限 | 角色   TO    用户名；

撤销权限语法格式如下：

REVOKE     权限  |  角色    FROM    用户名；

六、事务控制

COMMIT：提交事务，即把事务中对数据库的修改进行永久保存。

ROLLBACK：回滚事务，即取消对数据库所做的任何修改

自动提交：  set  autocommit on

关闭自动提交： set autocommit off

七、索引

索引是oracle的一个对象，是与表关联的可选结构，提供了一种快速访问数据的途径，提高了数据库检索性能。索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需要的数据。就像书的目录，可以通过目录快速查找所需信息，无需阅读整本书。

1、索引的特点

适当地使用索引可以提高查询速度

可以对表的一列或多列建立索引

建立索引的数量没有限制

索引需要磁盘存储，可以指定表空间，由oracle自动维护

索引对用户透明，检索时是否使用索引由oracle自身决定

Oracle数据库管理系统在访问数据时使用以下三种方式：

n全表扫描

n通过ROWID(行地址，快速访问表的一行)

n使用索引

当没有索引或者不选择使用索引时就用全表扫描的方式

2.索引的分类

(1)创建普通索引(B树索引)

参数解释：

index_name：创建索引的名称

tablename：为之创建索引的表名

columnname:在其上创建索引的列名列表，可以基于多列创建索引，列之间用逗号分隔

tablespace:为索引指定表空间

(2)创建唯一索引

保证定义索引的列中没有任何两行有重复值。唯一索引中的索引关键字只能指向表中的一行。

(3)创建反向键索引

与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值，使得索引的修改平均分布到整个索引树上。主要应用于所多个实例同时访问一个数据库的场景中。

(4)创建位图索引

优点：相对于B树索引而言，基于位图索引列的查询可以减少响应时间。

相比其他索引技术，位图索引占用空间明显减少。

(5)其他索引

组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接。

基于函数的索引：需要创建的索引需要使用表中一列或多列的函数或表达式，也可以将基于函数的索引创建为B树索引或位图索引

3、创建索引的原则

频繁搜索的列可以作为索引列

经常排序，分组的列可以作为索引

经常用作连接的列(主键/外键)可以作为索引

将索引放在一个单独的表空间中，不要放在有回退段、临时段和表的表空间中

对于大型索引而言，考虑使用NOLOGIN子句创建大型索引。

根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

4.查看索引

和索引有关的数据字典：

USER_INDEXS：用户创建的索引信息

USER_IND_COLUMNS：与索引相关的表列信息

5.维护索引

(1)重建索引

索引需要维护，如果建立了索引的表中有大量的删除和插入操作，会使得索引很大，因为删除操作后，删除值的索引空间不能被自动重新使用，对于大表和DML操作很频繁的表，索引的维护是很重要的。Oracle提供了rebuild指令来重建索引。使索引空间可以重用删除值所占用的空间，使索引更加紧凑。

(2)合并索引碎片

合并索引碎片可以释放部分磁盘空间，是索引维护的一种重要方式，也是维护磁盘空间的方式，类似于磁盘碎片整理，把不用的空间释放出来再利用。

(3)重命名索引

(4)删除索引

八、视图

视图是一个虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典里，视图中的数据是一个或多个实际表中获得的。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

1、视图的优点：

1)提供了另外一种级别的表安全性

2)隐藏的数据的复杂性：一个视图可能是用多表连接定义的，但用户不需要知道多表连接的语句也可以查询数据。

3)简化的用户的SQL命令：查询视图的时候不需要写出复杂的查询语句，只需要查询视图名称即可。

4)隔离基表结构的改变：视图创建好了之后，如果修改了表的结构，也不会影响视图的。

5)通过重命名列，从另一个角度提供数据：例如在销售系统中，每日下班前要对当日数据进行汇总，在销售人员眼中，该汇总表成为日销售统计表，在财务人眼中，该销售表成为销售日报表

2.创建视图

参数解释：

OR REPLACE:如果视图已存在，此选项将重新创建该视图。

FORCE:如果使用此关键字，则无论基表是否存在，都将创建视图。

NOFORCE：这是默认值，如果使用此关键字，则仅当基表存在时才创建视图。

VIEW_NAME：要创建视图的名称

Alias：指定由视图的查询所选择的表达式或列的别名。别名的数目必须与视图所选择的表达式的数目相匹配。

Select_statement:select语句

WITH CHECK OPTION :此选项指定只能插入或更新视图可以访问的行。术语constraint表示为CHECK OPTION约束指定的名称。

WITH READ ONLY：此选项保证不能在此视图上执行任何修改操作。

3、DML语句和复杂视图

DML语句是指用于修改数据的insert、delete和update语句。因为视图是一个虚拟的表，所以这些语句也可以与视图一同使用。一般情况下不通过视图修改数据，而是直接修改基表，因为这样条例更清晰。在视图上使用DML语句有如下限制(相对于表)。

1)DML语句只能修改视图中的一个基表。

2)如果过记录的修改违反了基表的约束条件，则将无法更新视图。

3)如果创建的视图包含连接运算符，DISTINCT运算符、集合运算符、聚合函数和groupby子句，则将无法更新视图。

4)如果创建的视图包含伪列表达式，则将无法更新视图。

简单视图基于单个基表，不包括函数和分组函数，那么可以在此视图中进行insert、update、delete操作，这些操作实际上在基表中插入、更新和删除行。

复杂视图从多个表提取数据，包括函数分组函数。复杂视图不一定能进行DML操作。

4.查询视图

select    view_name   from    user_views;

5.删除视图

drop  view   view_name;

6.物化视图

含义：

就是具有物理存储的特殊视图，占据物理空间，就像表一样

是远程数据的本地副本，或者用来生成基于数据表求和的汇总表

物化视图中两个重要概念：查询重写和物化视图同步

(1)查询重写：对sql语句进行重写，当用户使用sql语句对基表进行查询时，如果已经建立了基于这些表的物化视图，oracle将自动计算和使用物化视图来完成查询，在某些情况下可以节约查询时间，减少系统i/o。Oracle将这种查询优化技术成为查询重写。参数QUERY_REWRITE_ENABLED决定是否使用重写查询，该参数为布尔型。在创建物化视图需要用ENABLE_QUERY REWRITE来启动查询重写功能。通过SHOW指令可以查看该参数的值。

(2)物化视图的同步：

物化视图是基于表创建的，所以当基表变化时，需要同步数据以更新物化视图中的数据，这样保持物化视图中的数据和基表的数据一致性。Oracle提供了两种物化视图的刷新方式，决定何时进行刷新，即ON COMMIT方式和ON DEMAND方式。

ON COMMIT方式：指物化视图在对基表的DML操作事务提交的同时进行刷新。

ON DEMAND方式：指物化视图在用户需要的时候进行更新，可以手工通过DBMS_MVIEW.REFRESH等方式来进行刷新，也可以通过JOB定时进行刷新。

选择刷新方式之后，还需要选择一种刷新类型，刷新类型指定刷新时基表与物化视图如何实现数据的同步，oracle提供了以下4种刷新类型。

COMPLETE:对整个物化视图进行完全的刷新。

FAST：采用增量刷新，只刷新自上次刷新后进行的修改。

FORCE:oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE方式。

NEVER:物化视图不进行任何刷新。

默认值是FORCE刷新类型。

创建物化视图

(1)授予权限，具备创建物化视图的权限、QUERY  REWRITE的权限，以及对创建物化视图所涉及的表的访问权限和创建表的权限。

通过SCOTT用户来演示

(2)创建物化视图日志

(3)创建物化视图语句

其中：

bulid immediate：该参数的含义是立即创建物化视图，也可以选择build  deffered，该参数说明在物化视图定以后不会立即执行，而是延迟执行，在使用该视图时再创建。

Reffesh fast：刷新数据的类型选择FAST类型。

ON COMMIT：在基表有更新时提交后立即更新物化视图。

ENABLE QUERY REWRITE:启动查询重写功能。在创建物化视图时明确说明启用查询重写功能。

As：定义后面的查询语句。

查询体：物化视图的查询内容，该sql语句的查询结果集输出到物化视图中，保存在由oracle自动创建的表中。

(4)删除物化视图

drop  materialized  view  view_name;

九、序列

序列是用来生成唯一、连续的整数数据库对象。序列通常用来自动生成主键或唯一键的值。序列可以按升序排列，也可以按降序排列。

1.创建序列

参数解释：

START WITH:指定要生成的第一个序列号，对于升序序列，其默认值为序列的最小值，对于降序序列，其默认值为序列的最大值。

INCREMENT BY：用于指定序列号之间的间隔，默认值为1，如果n为正值，则生成的序列将按升序排序，如果n为负值，则生成的序列按降序排列。

MAXVALUE:指定序列可以生成的最大值

NOMAXVALUE:如果指定了NOMAXVALUE，oracle将升序序列的最大值设为1027，将降序序列的最大值设为-1。

MINVALUE:指定序列最小值。MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE。

NOMINVALUE:如果指定了NOMINVALUE，oracle将升序序列的最小值设为1，或将降序列的值设置为-1026。

CYCLE：指定序列在达到最大值或最小值后，将继续从头开始生成值。

NOCYCLE：指定序列在达到最大值或最小值后，将不在继续生成值。

CHCHE：使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快地访问序列号，当用完缓存中的所有序列号时，oracle将生成另一组数值，并将其保留在缓存中。

NOCACHE:使用NOCACHE选项，则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，oracle将默认缓存20个序列号。

2.访问序列

创建了序列之后，可以通过NEXTVAL和CURRVAL伪列来访问该序列的值。可以从伪列中选择值。但是不能操纵他们的值。

NETXVAL：创建序列后第一次使用NEXTVAL时，将返回该序列的初始值。以后再引用NETXVAL时，将使用INCREMENT BY子句的值来增加序列值，并返回这个新值。

CURRVAL:返回序列的当前值，即最后一次引用NEXTVAL时返回的值

举例：

创建序列

创建表

插入数据

查看数据

查看序列的当前值

Currval返回序列的当前值，即最后一次引用NEXTVAL时返回的值

测试currval

重启实例之后再次写入数据发现从40开始，因为按创建序列的要求，每次会拿30个序列号放到缓存中，实例重启后，缓存中的序列就会消失

3.更改序列

Alter sequence命令用于修改序列的定义。如果要进行下列操作，则会修改序列。

设置或删除MINVALUE或MAXVALUE

修改增量值

修改缓存中的序列号的数目

不能修改序列的START WITH参数

4.删除序列

十、同义词

同义词是对象的一个别名，不占用任何的实际存储空间，只在oracle的数据字典中保存其定义描述，在使用同义词时，oracle会将其翻译为对应对象的名称。

1.用途：

简化SQL语句

隐藏对象的名称和所有者

为分布式数据库的远程对象提供位置透明性

提供对数据库对象的公共访问

创建数据库链接的语法：

CREATE DATABASE LINK 数据库链接名 CONNECT TO user名 IDENTIFIED BY 口令 USING ‘Oracle链接串’；

2.同义词的分类

(1)私有同义词

私有同义词只能被当前模式的用户访问，私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，用户必须拥有create synonym系统权限。要在其他用户模式创建私有同义词，用户必须拥有create any  synonym系统权限。

语法：

CREATE [OR REPLACE] SYNONYM  [schema.]synonym_name  FOR  [schema.]object_name ;

参数解释：

[OR REPLACE]:在同义词存在的情况下替换该同义词

synonym_name：要创建同义词的名称

object_name:指定要为之创建同义词的对象的名称

(2)共有同义词

公有同义词被所有的数据库访问。公有同义词可以隐藏基表的身份，并降低sql语句的复杂性。要创建公有公有同义词，用户必须拥有create public SYNOYM的系统权限。

语法：

CREATE   [OR REPLACE]     PUBLIC    SYNONYM    [schema.]synonym_name    FOR    [schema.]object_name ;

(3)删除同义词

DROP [PUBLIC] SYNONYM  [schema.]synonym_name;

十一、分区表

1.含义：

Oracle允许把一个表重的所有行分成几个部分，并将它们存储在不通的表空间，分成的每一部分成为一个分区，被分区的表成为分区表。

对于包含大量数据的表来说，分区很有用，优点有以下几点：

1)改善表的查询性能。在对表进行分区后，用户执行sql查询时可以只访问表中的特定分区而非整个表。

2)表更容易管理。因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。

3)便于备份和恢复。可以独立地备份和恢复每个分区。

4)提高数据安全性。将不同的分区分布在不同的磁盘，可以减少所有分区的数据同时损坏的可能性。

符合一下条件的表可以建成分区表：

1)数据量大于2GB。

2)已有的数据和新添加的数据有明显的界限划分。

表分区对用户是透明的，及应用程序可以不知道表已被分区，在更新和查询分区表时当做普通表来操作，但oracle优化程序知道表已被分区。

2.分区表的分类

Oracle提供的分区方法有范围分区、列表分区、散列分区、复合分区、间隔分区和虚拟列分区等。

3.举例

(1)创建表并且分区，以age分区

(2)向表中插入数据

(3)查询P100分区的数据

查询P200分区的数据

(4)如果向表中插入以下记录，会提示插入的分区关键字未映射到任何分区

(5)按范围分区是，如果某些记录暂时无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中

(6)再次插入以下数据

(7)查询

(8)查看所有分区的命令

一般创建范围分区时都会将最后一个分区设置为maxvalue，将其他数据落入此分区，一旦需要时可以利用拆分分区的技术将需要的数据从最后一个分区分离出入，单独形成一个分区，如果没有创建最大的分区，插入的数据查出范围就会报错。如果插入的数据是分区键上的值，则该数据落入下一个分区。