有关Oracle学习总结

1、精品资料推荐表 xyzabc1mhj1mhjk1njk2uwe2uwert3qs4ads4adsaa怎么删掉第 1、5、8条记录？(字段 a、 b相同的几条记录只留一条，留下字段C 较短的一条)DELETE FROM LIANXIWHERE LENGTH(C) NOT IN (SELECT MIN(LENGTH(C) FROM LIANXI GROUP BY A, B) oracle 数据库常用的命令集锦今日开始研究 oracle,搜索到了一个好东东，拿出来与大家一同分享。 下面是摘抄的部分 ： local2/localORACLE 相关语法及命令一、Oracle 入门理论知识：Oracle

2、的物理组件有三个：(1) 数据文件 数据文件是用于存储数据库数据的文件，如表、索引数据。每个 Oracle 数据库有一个或多个物理数据文件， 一个数据文件只能与一个数据库关联。(2) 日志文件 用于记录对数据库进行的修改信息， 日志文件主要用于在数据库出现故障 时实施数据库恢复。(3) 控制文件 控制文件是记录数据库物理结构的二进制文件，每个 Oracle 数据库都含 有一个控制文件。Oracle 的逻辑组件：表空间 (TableSpace) 表空间是数据库最大的逻辑单位， 一个数据库至少包含一个表空间， 一个表空间包含一个或多个段等等。段(Segment) 段存在于表空间中， 分成 4 类，

3、数据段、索引段、回退段、临时段。区 (Extent) 区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段， 区只能存在于一个数据文件中。数据块 (Data Block) 数据块是数据库中最小的数据组织单位与管理单位， Oracle 数据库 中的数据存储于数据块中，取值范围 2K-64K 之间。模式 (schema) 模式是对用户所创建的数据库对象的总称，又称为用户模式。精品资料推荐概念：内存 Oracle 内存结构包含以下两个内存区。1、系统全局区 (SGA) 实例启动时分配该内存区， 是 Oracle 实例的一个基本组件。 又称为共享全局区，它用来存储数据库信息，并由多个数据库

4、进程共享。可分 为共享池、数据缓冲区及日志缓冲区。(1) 共享池是对 SQL 、PLSQL 程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数据字典缓存组成。其中，库缓存含有最近执行的SQL、PLSQL 语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。(2) 数据缓冲区 数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。(3) 日志缓冲区 日志记录数据库的所有修改信息，主要用于恢复数据。2、程序全局区 (PGA) 服务器进程启动时分配该内存区。 PGA 为非共享区， 只能单 个进程使用，当一个用户会话结束后， PGA 释放。用户进程 (

5、PGA)发送 SQL 语句到共享全局区 (SGA) ，先在共享池的库缓存中查询是否存 在所需的数据块， 如果存在就在数据字典中读取相应的数据块， 如果不存在就由服务器进程 (DBWR) 来 IO 数据库语法知识： 创建表空间的语法如下：CREATE TABLESPACE tablespacename DATAFILE d:filename.DBF SIZE int KB|MB AUTOEXTEND OFF|ON;tablespacename 是需创建的表空间名称。 DATAFILE 指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。 filename 是表空间中数据文件的路径

6、和名称。SIZE 指定文件的大小，用 K 指定千字节大小，用 M 指定兆字节大小。 AUTOEXTEND 子句用来启用或禁用数据文件的自动扩展。Oracle 默认用户：用户名： sys 默认密码： chage_on_install 用来管理拥有 Oracle 数据字典文件 用户名： system 默认密码： manager 用来管理拥有数据字典视图对象 用户名： scott 默认密码： tiger 示例用户，包括 emp、 dept 等表 连接 Oracle: 在控制台下输入 sqlplus 用户名 /密码 回车或 sqlplusw 回车 相关命令：disconn/ 退出当前登录conn 用户

7、名 / 密码 /连接 Oraclealter user 用户名 identified by 密码 /修改用户口令drop user 用户名 cascade;/删除用户alter user 用户名 account lock; / 给某个用户加锁alter user 用户名 account unlock; /给某个用户解锁 ed 回车 : /打开缓冲区/ 回车 : / 执行缓冲区中的语句create user 用户名 identified by 密码 password expiredefault tablespace 表空间名 temporary tablespace 临时表空间名 ; / 创建用

8、户 相关权限：grant connect to scott; /connect 角色将允许用户创建数据库并在数据库中创建表或 其他对象精品资料推荐grant resource to scott;/resource 角色将允许用户使用数据库中的空间grant create sequence to scott; /create sequence权限将允许用户创建序列， 此权限包含在 connect 连接角色中grant select on emp to scott; / 将 emp 表的查询权限授予用户 scottgrant update（vencode,venname） on 表名 to sco

9、tt; / 将特定列的更新权限授予用户 scott grant 权限 on 表名 to 用户名 with grant option; / 接受该权限的用户可以将此权限授予 其他用户revoke select,update on 表名 from 用户名 ; /收回相应的权限二、SQL 查询和 SQL 函数SQL 支持如下类别的命令： 数据定义语言：create（创建）、alter（更改）、drop（删除 ）和 truncate（截断）命令。数据操纵语言：insert（插入）、 select（选择）、 delete（删除）和 update（更新）命令。事务控制语言：commit（ 提交 ）、 sa

10、vepoint（保存点 ）和 rollback（ 回滚 ）命令。数据控制语言：grant（授予）和 revoke（回收 ）命令。数据类型：char: 长度在 1 到 2000 个字节，声明多少字节在内存中就占用多少字节，输入的 值小于指定的长度时用空格填充。varchar2: 长度在 1 到 4000 个字节，输入的值是多少字节，就占用多少字节。 long: 长度在 2GB ，设置为此类型的列时， 要注意： 一个表中只有一列可以为 long 类型， long 类型列不能定义为唯一约束或主键约束， 不能建立索引， 过程或存储过程不能接 受 long 类型的参数。number（p,s）: 其中 p

11、 为精度，表示数字的总位数，在1 至 38 之间。 s 为范围，表示小数点右边数字的位数，在 -84 至 127 之间。date: 日期类型， sysdate 为当前系统时间。格式为 08-9 月 -07 。 timestamp: 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数 点后 6 位，systimestamp 返回当前日期、时间。格式为 08-9 月 -07 04.08.30.000000 下午。 raw: 此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多 能存储 2000 个字节，可以建立索引。long raw: 此数据类型用于可变长度的二进制数据，

12、最多能存储2GB 。long 数据类型的所有限制对 long raw 数据类型也同样有效。lob 又称为 大对象 数据类型，最多能存储 4GB 的非结构化信息。包括： clob: clob 代表 Character LOB（ 字符 LOB） ，它能存储大量字符数据。如 XML 文档。blob: blob 代表 Binary LOB（ 二进制 LOB） ，它能存储较大的二进制对象， 如图形、 视频 剪辑和声音剪辑。bfile: bfile 代表 Binary File（ 二进制文件 ），它能够将二进制文件存储在数据库外部的操 作系统文件中。伪列： rowid: select rowid,enam

13、e, from scott.emp where empno=7900;rownum: select * from scott.emp where rownum=5; /having 对分组统计再加限制条件7、分析函数：只能出现在 select 列表或 order by 子句中。row_number: 下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。示例： select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.emp;下面对所有员工的工资按部门进行排名，即使工资相同，排名也

14、不同。示例： select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.emp;rank: 此函数计算一个值在一组值中的排位， 排位是以 1 开头的连续整数， 如果两行的序 数为 1，则没有序数 2，下行的序数为 3。下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且 排位不连续。示例： select ename,sal,comm,deptno,rank() over(partition by deptno order by

15、 sal desc,comm) 排名 from scott.emp;dense_rank: 此函数计算一个行在一组有序行中的排位，排位是以 1 开头的连续整数，具 有相同值的排位相同，并且排位是连续的。下面首先选择所有在 accounting 或 research 部门中工作的员工的部门名称、 员工姓名和工 资，然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同示例：select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名from emp e,dept d

16、where e.deptno=d.deptno;三、锁和表分区 锁定是数据库用来控制共享资源并发访问的机制。 Oracle 提供以确保在多用户环境下数据的完整性和一致性。 锁的两种级别： (只有在提交或回滚后才能释放锁定 ) (1) 行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使 用 Insert、 Update、 Delete 和 Select.For Update 等语句时， Oracle 会自动应用行级锁定。精品资料推荐Select.For Update 语法为：Select.For Update OF column_list WAIT n | NOWAIT

17、 其中： OF 子句用于指定即将更新的列，即锁定行上的特定列。WAIT 子句指定等待其他用户释放锁的秒数， 防止无限期的等待。 NOWAIT 为不等待。 示例 1：演示如何锁定 deptno 值为 10 的所有行。select * from dept where deptno=10 for update of dname,loc;(2) 表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用 来限制对表执行添加、更新和删除等修改操作。语法：LOCK TABLE IN MODE NOWAIT; 其中： table_name 是要被锁定的表的名称。lock_mode 是锁定的

18、模式。 表级锁的模式：1、行共享 (ROW SHARE,RS) ： 允许其他用户访问和锁定该表， 但是禁止排他锁锁定整 个表。2、行排他 (ROW EXCLUSIVE,RX) ：与行共享模式相同，同时禁止其他用户在此表上使 用共享锁。使用 Select.For update 语句会自动应用行排他。3、共享 (SHARE,S) ：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、 更新或删除行。多个用户可以同时在同一张表中放置共享锁即允许资源共享。但是这样极容易造成死锁。4、共享行排他 (SHARE ROW EXCLUSIVE,SRX) ： 执行比共享表锁更多的限制。防止其 他事务在表上

19、应用共享锁、共享行排他锁以及排他锁。5、排他 (EXCLUSIVE,X) ： 对表执行最大限制。除了允许其他用户查询该表的记录，排 他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。示例 2：演示如何以共享模式锁定表。lock table dept in share mode nowait; 表分区的优点：改善表的查询性能 ;表更容易管理 ;便于备份和恢复 ;提高数据安全性。 注意：要分区的表不能具有 Long 和 Long Raw 数据类型的列。四种分区方法： 1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。语法如下：在 Create Table 语句后增加

20、PARTITION BY RANGE(column_name)(PARTITION part1 VALUE LESS THAN (range1) TABLESPACE tbs1,PARTITION part2 VALUE LESS THAN (range2) TABLESPACE tbs2,PARTITION partN VALUE LESS THAN (MAXVALUE) TABLESPACE tbsN);其中： column_name 是以其为基础创建范围分区的列，特定行的该列值称为分区键。 part1.partN 是分区的名称。range1.MAXVALUE 是分区的边界值。tbs1.t

21、bsN 是分区所在的表空间， TABLESPACE 子句是可选项。精品资料推荐示例 3：create table t_emp(empno number(4),ename varchar2(30),sal number)partition by range(empno)(partition e1 values less than (1000) tablespace emp1, 也 可 以 (to_date(2003-01-01,yyyy-mm-dd)partition e2 values less than (2000) tablespace emp2, 也 可 以 (to_date(2004-

22、01-01,yyyy-mm-dd)partition e3 values less than (maxvalue) tablespace emp3);2、散列分区：语法有两种如下PARTITION BY HASH(column_name)PARTITIONS number_of_partitions STORE IN (tablespace_list);或PARTITION BY HASH(column_name)(PARTITION part1 TABLESPACE tbs1,PARTITION part2 TABLESPACE tbs2,PARTITION partN TABLESPACE

23、 tbsN);其中： column_name 是以其为基础创建散列分区的列。number_of_partitions 是散列分区的数目，使用这种方法系统会自动生成分区的名称。 tablespace_list 指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以 循环的方式分配到表空间中。part1.partN 是分区的名称。tbs1.tbsN 是分区所在的表空间， TABLESPACE 子句是可选项。示例 4：自动分配 4 个散列分区，可 以 使 用 select partition_name,HIGH_VALUE from user_tab_partitions table_nam

24、e=upper(t_emp) 查询分区名create table t_emp(empno number(4),ename varchar2(30),sal number)partition by hash (empno)partitions 4;thanthanwhere3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进精品资料推荐行分区，然后在这些分区内创建散列子分区。语法如下：PARTITION BY RANGE(column_name1)SUBPARTITION BY HASH(column_name2)SUBPARTITIONS number_of_part

25、itions STORE IN (tablespace_list)(PARTITION part1 VALUE LESS THAN (range1) TABLESPACE tbs1,PARTITION part2 VALUE LESS THAN (range2) TABLESPACE tbs2,PARTITION partN VALUE LESS THAN (MAXVALUE) TABLESPACE tbsN); 其中： column_name1 是以其为基础创建范围分区的列。column_name2 是以其为基础创建散列分区的列。 number_of_partitions 是要创建的子分区的

26、数目。 part1.partN 是分区的名称。range1.MAXVALUE 是范围分区的边界值示例 5：将雇员表先按照雇佣时间 hiredate 进行了范围分区，然后再把每个分区分为 2 个子 hash分区，此表一共是 6 个分区。create table t_emp(empno number(4),ename varchar2(30),hiredate date)partition by range (hiredate) subpartition by hash (empno) subpartitions 2(partition e1 values less than (to_date(2

27、0020501,YYYYMMDD),partition e2 values less than (to_date(20021001,YYYYMMDD),partition e3 values less than (maxvalue);4、列表分区：此分区允许用户明确地控制行到分区的映射。语法如下：PARTITION BY LIST(column_name)(PARTITION part1 VALUES (values_list1),PARTITION part2 VALUES (values_list2),PARTITION partN VALUES (DEFAULT);其中： column_

28、name 是以其为基础创建列表分区的列。part1.partN 是分区的名称。values_list 是对应分区的分区键值的列表。10精品资料推荐DEFAULT 关键字允许存储前面的分区不能存储的记录。示例 6：create table t_emp1(empno number(4),ename varchar2(30),location varchar2(30)partition by list (location)(partition e1 values ( 北京 ),partition e2 values ( 上海 ,天津 ,重庆 ),partition e3 values ( 广东 ,福

29、建 ),);要查询表分区中的数据行： select * from table_name PARTITION(p1); 注： p1 是分区名。 分区维护操作：1、添加分区： ALTER.ADD PARTITION 语句用于在现有的最后一个分区之后添加新的 分区。示例 7：演示如何将名为 E4 的新分区添加到示例 3 中创建的 t_emp 表。ALTER TABLE t_emp ADD PARTITION E4 VALUES LESS THAN (3000);在此请注意： 上例公适用于已使用特定的键值定义了最后一个分区的表。 如果要在表的 开始或中间位置添加分区，或者最高分区的分区边界是 MAXV

30、ALUE ，则应使用 SPLIT PARTITION 语句。2、删除分区：使用 ALTER TABLE.DROP PARTITION 语句。示例 8：演示了如何删除 t_emp表的 E4 分区。删除分区时，分区中的数据也随之删除。 ALTER TABLE t_emp DROP PARTITION E4;3、截断分区：使用 ALTER TABLE.TRUNCATE PARTITION语句来截断分区，只删除表分区中的所有记录。示例 9：演示了如何删除 t_emp 表中 e3 的分区的所有记录。ALTER TABLE t_emp TRUNCATE PARTITION e3;4、合并分区：可以将范围分

31、区或复合分区表的两个相邻分区连接起来。结果分区将继承 被合并的两个分区的较高上界。语法如下：ALTER TABLE table_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;示例 10：演示了如何将 e1和 e2合并成一个 e2分区。ALTER TABLE t_emp MERGE PARTITIONS e1,e2 INTO PARTITION e2;5、拆分分区：使用 SPLIT PARTITION 语句在表的开头或中间添加分区。拆分分区允许 用户将一个分区拆分为两个分区。

32、语法如下：11精品资料推荐ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION partition2);示例 11：演示了如何将 t_emp 表中的 e3 分区拆分为 e31 和 e32 两个分区。ALTER TABLE t_emp SPLIT PARTITION e3 AT (Date 2005-01-01) INTO (PARTITION e31,PARTITION e32);6、重新命名拆分后的分区 :ALTER TABLE t_emp R

33、ENAME PARTITION e31 TO P3;ALTER TABLE t_emp RENAME PARTITION e32 TO P4;可以查询字典视图 user_tab_partitions 来查看用户所创建的分区的详细信息 示例 12： select table_name,partition_name,high_value from user_tab_partitions;可以查询字典视图 dba_tab_subpartitions 来查看用户所创建的子分区的详细信息 示例 13： select table_name,partition_name,subpartition_name

34、from dba_tab_subpartitions;四、数据库对象 表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。1、同义词：私有同义词、公有同义词。 私有同义词只能被当前模式的用户访问。 私有同义词名称不可与当前模式的对象名称相同。 要在自身的模式创建私有同义词，用户必须拥有 Create Synonym 系统权限。要在其它用户模式创建私有同义词，用户必须拥 有 Create Any Synonym 系统权限。公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有 Create Public Synonym 系统权限。创建私有同义词语法：Create OR

35、 REPLACE SYNONYM schema.synonym_name FOR schema.object_name; 其中： OR REPLACE 表示在同义词存在的情况下替换该同义词。synonym_name 表示要创建的同义词的名称。 object_name 指定要为之创建同义词的对象的名称。示例 1： create synonym s_emp for scott.emp;创建公有同义词语法：Create PUBLIC SYNONYM synonym_name FOR schema.object_name; 示例 2： create public synonym emp_syn from scott.emp;可以查询字典视图 User_Syno