有关Oracle学习总结.doc

表xyza b c1 m hj1 m hjk1 n jk2 u we2 u wert3 q s4 a ds4 a dsaa怎么删掉第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/local ORACLE相关语法及命令一、Oracle入门 理论知识： Oracle的物理组件有三个：(1)数据文件数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件， 一个数据文件只能与一个数据库关联。(2)日志文件用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。(3)控制文件控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。 Oracle的逻辑组件：表空间(TableSpace) 表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。段(Segment)段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。区(Extent)区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。数据块(Data Block) 数据块是数据库中最小的数据组织单位与管理单位，Oracle数据库中的数据存储于数据块中，取值范围2K-64K之间。模式(schema)模式是对用户所创建的数据库对象的总称，又称为用户模式。概念：内存Oracle内存结构包含以下两个内存区。1、系统全局区(SGA)实例启动时分配该内存区，是Oracle实例的一个基本组件。又称为共享全局区，它用来存储数据库信息，并由多个数据库进程共享。可分为共享池、数据缓冲区及日志缓冲区。(1)共享池是对SQL、PLSQL程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数据字典缓存组成。其中，库缓存含有最近执行的SQL、PLSQL语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。(2)数据缓冲区数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。(3)日志缓冲区日志记录数据库的所有修改信息，主要用于恢复数据。2、程序全局区(PGA)服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如果不存在就由服务器进程(DBWR)来IO数据库语法知识：创建表空间的语法如下： CREATE TABLESPACE tablespacename DATAFILE d:filename.DBF SIZE int KB|MB AUTOEXTEND OFF|ON; tablespacename 是需创建的表空间名称。DATAFILE指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。filename是表空间中数据文件的路径和名称。SIZE指定文件的大小，用K指定千字节大小，用M指定兆字节大小。AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。Oracle默认用户： 用户名：sys 默认密码：chage_on_install用来管理拥有Oracle数据字典文件 用户名：system 默认密码：manager用来管理拥有数据字典视图对象 用户名：scott默认密码：tiger示例用户，包括emp、dept等表连接Oracle:在控制台下输入 sqlplus 用户名/密码回车或sqlplusw 回车相关命令： disconn/退出当前登录 conn 用户名/密码/连接Oracle alter user 用户名 identified by 密码 /修改用户口令drop user 用户名 cascade;/删除用户 alter user 用户名 account lock;/给某个用户加锁 alter user 用户名 account unlock; /给某个用户解锁 ed回车:/打开缓冲区 /回车:/执行缓冲区中的语句 create user 用户名 identified by 密码 password expiredefault tablespace 表空间名 temporary tablespace 临时表空间名;/创建用户相关权限： 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 scott;/将特定列的更新权限授予用户scott grant 权限 on 表名 to 用户名 with grant option;/接受该权限的用户可以将此权限授予其他用户revoke select,update on 表名 from 用户名;/收回相应的权限二、SQL查询和SQL函数SQL支持如下类别的命令：数据定义语言：create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。数据操纵语言：insert(插入)、select(选择)、delete(删除)和update(更新)命令。事务控制语言：commit(提交)、savepoint(保存点)和rollback(回滚)命令。数据控制语言：grant(授予)和revoke(回收)命令。数据类型：char:长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。varchar2: 长度在1到4000个字节，输入的值是多少字节，就占用多少字节。long:长度在2GB，设置为此类型的列时，要注意：一个表中只有一列可以为long类型，long类型列不能定义为唯一约束或主键约束，不能建立索引，过程或存储过程不能接受long类型的参数。number(p,s): 其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。date:日期类型，sysdate为当前系统时间。格式为08-9月 -07。timestamp: 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。raw:此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。long raw: 此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。lob又称为大对象数据类型，最多能存储4GB的非结构化信息。包括：clob:clob代表Character LOB(字符LOB)，它能存储大量字符数据。如XML文档。 blob: blob代表Binary LOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。 bfile:bfile代表Binary File(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。伪列：rowid:select rowid,ename, 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; 下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。 示例：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 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 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其中：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是锁定的模式。表级锁的模式：1、行共享(ROW SHARE,RS)： 允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。2、行排他(ROW EXCLUSIVE,RX)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用Select.For update语句会自动应用行排他。3、共享(SHARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁即允许资源共享。但是这样极容易造成死锁。4、共享行排他(SHARE ROW EXCLUSIVE,SRX)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。5、排他(EXCLUSIVE,X)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。示例2：演示如何以共享模式锁定表。lock table dept in share mode nowait;表分区的优点：改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。注意：要分区的表不能具有Long和Long Raw数据类型的列。四种分区方法：1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。语法如下：在Create Table语句后增加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.tbsN是分区所在的表空间，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, 也可以than (to_date(2003-01-01,yyyy-mm-dd) partition e2 values less than (2000) tablespace emp2, 也可以than (to_date(2004-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 tbsN);其中：column_name是以其为基础创建散列分区的列。 number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。 tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。 part1.partN是分区的名称。 tbs1.tbsN是分区所在的表空间，TABLESPACE子句是可选项。示例4：自动分配4个散列分区， 可以使用select partition_name,HIGH_VALUE from user_tab_partitions where table_name=upper(t_emp)查询分区名create table t_emp ( empno number(4), ename varchar2(30), sal number) partition by hash (empno) partitions 4;3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。语法如下：PARTITION BY RANGE(column_name1)SUBPARTITION BY HASH(column_name2)SUBPARTITIONS number_of_partitions 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是要创建的子分区的数目。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(20020501,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_name是以其为基础创建列表分区的列。part1.partN是分区的名称。values_list是对应分区的分区键值的列表。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 (广东,福建),); 要查询表分区中的数据行：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); 在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区 边界是MAXVALUE，则应使用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、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。 语法如下： 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语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。 语法如下： ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION p