ORACLE相关语法与命令

1、表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数数据库常用的的命令集锦今日开始研究ooraclee,搜索到了了一个好东东东，拿出来与与大家一同分分享。下面是是摘抄的部分分 ：loocal22/loccal ORACCLE相关语语法及命令一一、Ora

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

3、空空间，一个表表空间包含一一个或多个段段等等。段段(Segmment)段段存在于表空空间中，分成成4类，数据据段、索引段段、回退段、临临时段。区(Extennt)区是磁磁盘空间分配配最小单位，由由连续的数据据块组成，一一个或多个区区构成段，区区只能存在于于一个数据文文件中。数数据块(Daata Bllock) 数据块是数数据库中最小小的数据组织织单位与管理理单位，Orracle数数据库中的数数据存储于数数据块中，取取值范围2KK-64K之之间。模式式(scheema)模式是对用用户所创建的的数据库对象象的总称，又又称为用户模模式。概概念：内存Orracle内内存结构包含含以下两个内内存区。1、

4、系系统全局区(SGA)实例启动时时分配该内存存区，是Orracle实实例的一个基基本组件。又称为为共享全局区区，它用来存存储数据库信信息，并由多多个数据库进进程共享。可可分为共享池池、数据缓冲冲区及日志缓缓冲区。(1)共共享池是对SQLL、PLSSQL程序进进行语法分析析、编译、执执行的内存区区域。共享池池由库缓存和和数据字典缓缓存组成。其其中，库缓存存含有最近执执行的SQLL、PLSSQL语句的的分析码和执执行计划；数数据字典缓存存含有从数据据字典中得到到的表、索引引、列定义和和权限等信息息。(2)数据缓冲区区数据缓冲冲区用于存储储从磁盘数据据文件中读入入的数据，所所有用户共享享。(3)日志

5、缓冲区区日志记录录数据库的所所有修改信息息，主要用于于恢复数据。2、程序全局区(PGA)服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如果不存在就由服务器进程(DBWR)来IO数据库语法知识：创建表空间的语法如下： CREATE TABLESPACE tablespacename DATAFILE d:filename.DBF SIZE int KB|MB AUTOEXTEND OFF|ON; tab

6、lespacename 是需创建的表空间名称。DATAFILE指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。filename是表空间中数据文件的路径和名称。SIZE指定文件的大小，用K指定千字节大小，用M指定兆字节大小。AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。Oracle默认用户： 用户名：sys 默认密码：chage_on_install用来管理拥有Oracle数据字典文件 用户名：system 默认密码：manager用来管理拥有数据字典视图对象 用户名：scott默认密码：tiger示例用户，包括emp、dept等表连接Oracle:在控制台下输入

7、 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 ta

8、blespace 表空间名 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(v

9、encode,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

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

11、，表示小数点右边数字的位数，在-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又称为大对象数据类

12、型，最多能存储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;/ha

13、ving对分组统计再加限制条件 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 des

14、c) 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开头的连续整数，具有相同值的排位相同，并且排位是连续的

15、。 下面首先选择所有在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提供以确保在多用户环境下数据的完整性和一致性。锁的两种级别：(只有在提交或回滚后才能释

16、放锁定)(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=

17、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、共享(S

18、HARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁即允许资源共享。但是这样极容易造成死锁。4、共享行排他(SHARE ROW EXCLUSIVE,SRX)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。5、排他(EXCLUSIVE,X)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。示例2：演示如何以共享模式锁定表。lock table dept in share mode nowait;表分区的优点：改善表的查询性能;

19、表更容易管理;便于备份和恢复;提高数据安全性。注意：要分区的表不能具有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 THA

20、N (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 e

21、mp1, 也可以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)

22、;或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是分

23、区所在的表空间，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、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散

24、列子分区。语法如下：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) TABL

25、ESPACE 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 (hi

26、redate) 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

27、 (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)

28、 ( 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 (30

29、00); 在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区 边界是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的

30、分区的所有记录。 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;

31、5、拆分分区：使用SPLIT PARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。 语法如下： 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,P

32、ARTITION e32); 6、重新命名拆分后的分区: ALTER TABLE t_emp RENAME 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

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

34、onym系统权限。创建私有同义词语法：Create OR 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 sy

35、nonym emp_syn from scott.emp;可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息删除同义词语法：Drop Synonyms synonym_name; 删除公有同义词加上一个Public此命令只删除同义词，不会删除对应的表。2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。创建序列语法如下：Create SEQUENCE sequence_name START WITH integer INCREMENT BY integer MAXVALUE integer|NOMAXVALUE MINVALUE integer|NOMINVALUE CYCLE|NOCYCLE CACHE interger|NOCACHE;其中：START WITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。INCREMENT BY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。MAXVALUE指定序列可以生成的最大值。NOMAXVA