ORACLE日常操作手册

1、ORACLE日常操作手册目录一、数据库的启动和关闭.41. 数据库的正常启动步骤.42. 数据库的正常关闭步骤.43. 几种关闭数据库方法对比.44. 数据库的启动关闭过程.4二、创建数据库用户.51、 以DBA用户登录数据库（如system,sys）.52、 用create user语法创建用户.53、 赋表空间使用权限.54、 给用户赋权限.55、 删除用户.5三、ORACL常用的数据类型.5四、基本的SQL语句的写法.61、 rowid和rownum的区别.6 删除表中重复记录.6 使表处于可编辑状态.6 批量删除记录.7 分页查询.72、 delete和truncate 、drop的区

2、别.73、 多表关联查询.7不等连接实例.7 查询员工的工资等级.7内连接实例.8 查询详细信息记录.8 关联更新和删除.8外连接实例.8 查询没有附件信息记录.8自连接实例.8 查询员工和主管之间的关系.8 递归查询.84、 子查询.8单行子查询实例.9 查询内容大小大于平均大小的记录.9 在having子句中使用子查询.9 在from子句中使用子查询（内联视图）.9 可能碰到的两个错误.9多行子查询实例.9 在多行查询中使用in操作符.9 在多行子查询中使用any操作符.10 在多行子查询中使用all操作符.10多例子查询实例.10 检索每种产品类型中价格最低的产品.10关联子查询实例.1

3、0 在关联子查询中exists10 在关联子查询中not exists10 Exists和not exists与in和not in的比较.10嵌套子查询实例.11 多层嵌套子查询.115、 使用集合操作符.12 Union all使用实例.12 Union使用实例.12 Intersect使用实例.13 Minus使用实例.136、 Decode函数和Case表达式的比较.13 Decode函数使用实例.13 Case表达式使用实例.147、 其它.15五、日期和时间的存储与处理.151、 常用的几个日期函数说明.152、 常用的日期计算实例.15 取得当前日期是本月的第几周.15 取得当前日

4、期是一个星期中的第几天,注意星期日是第一天.15 取当前日期是星期几中文显示:16 如果一个表在一个date类型的字段上面建立了索引，如何使用.16 得到当前的日期.16 得到当天凌晨0点0分0秒的日期.16 得到这天的最后一秒.16 得到小时的具体数值.16 得到明天凌晨0点0分0秒的日期.17 本月一日的日期.17 得到下月一日的日期.17 返回当前月的最后一天.17 得到一年的每一天.17 如何在给现有的日期加上2年.18 判断某一日子所在年分是否为润年.18 判断两年后是否为润年.18 得到日期的季度.18六、SQL语句的优化写法.181、 oracle访问Table的方式.182、

5、创建索引.19 创建普通索引实例.19 创建全文索引实例.19 创建主建.203、 SQL优化实例及问题.20 使用like操作符的问题.20 选择最有效率的表名顺序(只在基于规则的优化器中有效)20 WHERE子句中的连接顺序.21 SELECT子句中避免使用*21 减少访问数据库的次数.21 尽量多使用COMMIT.22 减少对表的查询.22 通过内部函数提高SQL效率.23 使用表的别名(Alias)24 用EXISTS替代IN和用NOT EXISTS替代NOT IN.24 用表连接替换EXISTS.24 用EXISTS替换DISTINCT.24 等式比较和范围比较.25 不明确的索引等

6、级.25 强制索引失效.26 避免在索引列上使用计算.26 自动选择索引.27 避免在索引列上使用NOT.27 避免在索引列上使用IS NULL和IS NOT NULL.28 总是使用索引的第一个列.28七、常见的数据库管理和优化配置.291、 数据库的备份.29 导出/导入(Export/Import)29 rman备份实例.302、 数据库的参数配置及性能调整.31 如何增加ORACLE连接数.31 关于内存参数的调整.31 32bit 和 64bit 的问题.32 Linux上shmmax参数的设置及含义.32 解决CPU高度消耗(100%)的数据库问.333、 存储管理.35 创建表空

7、间.36 管理表空间.36 管理数据文件.36 查看表空间的使用情况.37本文档约定：1、 文中的数据库主要用到了公司cms、pms库结构2、 所有SQL都实际的测试通过，放心使用。3、 如果有再需要了解的部分以后可以再做补充。一、 数据库的启动和关闭1. 数据库的正常启动步骤l 以DBA的身份登录数据库（要在oracle安装用户下执行sqlplus）oracleDB1 $sqlplus “/as sysdba”l 执行启动数据库命令SQLstartupORACLE instance started.Total System Global Area 285212672 bytesFixed S

8、ize 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.l 启动和关闭监听oracleDB1 $lsnrctl startoracleDB1 $lsnrctl stop2. 数据库的正常关闭步骤l 同样以DBA的身份登录数据库oracleDB1 $sqlplus “/as sysdba”l 执行数据库关闭命令SQLshutdown immediate;Database closed

9、.Database dismounted.ORACLE instance shut down.3. 几种关闭数据库方法对比SHUTDOWN有四个参数：NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。缺省不带任何参数时表示是NORMAL。SHUTDOWN NORMAL：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复，这种方法往往不能关闭数据库或等待很长时间。SHUTDOWN TRANSACTIONAL：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。SHUTDOWN IMMED

10、IATE：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。最常用的方法。SHUTDOWN ABORT：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。一般不推荐采用，只有在数据库无法关闭时使用，可能造成数据库的不一致。4. 数据库的启动关闭过程二、 创建数据库用户1、 以DBA用户登录数据库（如system,sys）SQLconn system/oracleorcl注：如果在本地服务器登录orcl可以去掉2、 用create user语法创建用户CRE

11、ATE USER user_name IDENTIFIED BY user_passwordDefaultTablespace tbs_users;l user_name为数据库用户的用户名l user_password为数据库用户的密码l tbs_users为用户使用的表空间，默认是users表空间。例如：CREATE USER cmsuser IDENTIFIED BY passwordDefaultTablespace users;3、 赋表空间使用权限alter user user_name quota unlimited on user_tablespace quota unlimi

12、ted on user_tablespace;4、 给用户赋权限GRANT connect, resource TO cmsuser;l Connect用户能登录数据库的权限l Resource用户能创建一些数据库对像的权限，表、视图，存储过程，一般是授予开发人员的5、 删除用户DropUser cmsuser Cascade;l 使用cascade参数可以删除该用户的全部objects三、 ORACL常用的数据类型l INTEGER存储整数，整数不包括浮点数;它是一个整数数字，如：1、10、15l NUMBER，是以十进制格式进行存储的，它便于存储，但是在计算上，系统会自动的将它转换成为二进

13、制进行运算的。它的定义方式是NUMBER(P，S)，P是精度，最大38位，S是刻度范围，可在-84.127间取值。例如：NUMBER(5，2)可以用来存储表示-999.99.999.99间的数值。P、S可以在定义是省略，例如：NUMBER(5)、NUMBER等;l CHAR，描述定长的字符串，如果实际值不够定义的长度，系统将以空格填充。它的声明方式如下CHAR(L)，L为字符串长度，缺省为1，作为变量最大32767个字符，作为数据存储在ORACLE8中最大为2000。l VARCHAR2(VARCHAR)，描述变长字符串。它的声明方式如下VARCHAR2(L)，L为字符串长度，没有缺省值，作为

14、变量最大32767个字节，作为数据存储在ORACLE8中最大为4000。在多字节语言环境中，实际存储的字符个数可能小于L值，例如：当语言环境为中文(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)时，一个VARCHAR2(200)的数据列可以保存200个英文字符或者100个汉字字符。l NCHAR、NVARCHAR2，国家字符集，与环境变量NLS指定的语言集密切相关，使用方法和CHAR、VARCHAR2相同。不过最大参数为NCHAR(2000)、NVARCHAR2(2000)l DATE唯一的一种日期类型-，用来存储时间信息，站用7个字节(从世纪到秒)l LOB（oracl

15、e8以前叫long）变量主要是用来存储大量数据的数据库字段，最大可以存储4G字节的内容，CLOB：存储单字节字符数据（如英文）NCLOB：用来存储定宽多字节字符数据（如汉字），BLOB：用来存储无结构的二进制数据（word、pdf文档）。四、 基本的SQL语句的写法1、 rowiddelete和rownum的区别rowid是Oracle数据库中的每一行都有一个唯一的行标识符，称为rowid,它是一个18位数字，以64为基数，该徝包含了该行在oracle数据库中的物理位置，查询rowid如下：SQL Select rowid,id From infobase Where Rownum 5;ROW

16、ID ID AAAYKRAAEAAGGpcAAI 1000000AAAYKRAAEAAGGpcAAJ 1000001AAAYKRAAEAAGGpcAAK 1000002AAAYKRAAEAAGGpcAAL 1000003Rowid应用实例： 删除表中重复记录DeleteFrom Infobase aWhere Rowid Select Rownum,id From infobase Where Rownum 5;ROWNUM ID 1 10000002 10000013 10000024 1000003rownum应用实例： 批量删除记录如果要删除的数据量很大，一次删除可能需要占用系统大量的内

17、存，给数据库带来很大的压力，可以进行分步批量删除并提交，避免这种情况createor replace procedure del_dataas -创建过程并执行beginfor i in 1.1000loopdelete from cmsuser_base Where posterid=Servlet提交and rownum 100;commit;end loop;End del_data; 分页查询Select *From (Select * From InfobaseOrder By OriginaltimeDesc)Where Rownum = 10;2、 delete和truncate

18、、drop的区别TRUNCATE TABLE 在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE比 DELETE速度快，且使用的系统和事务日志资源少。Drop 则是删除整个表，与TRUNCATE操作类型相同，都是DDL操作（数据定义语言）DeleteFrom infobase Id = 1;Commit;-或Delete infobase Where Id = 1;Commit;TruncateTable infobase;DropTable infobase;3、 多表关联查询根据连接中使用操作符的不同，连接条件可以分为两类：l 等连

19、接：在连接中使用等于操作符(=)l 不等连接：在连接中使用除等号之外的操作符，如：、between等除连接条件区分之外，连接本身也有3种不同的类型：l 内连接：只有当连接中的列包含满足连接条件的值时才会返回一行。这就是说，如果某一行的连接条件中的一列是空值，那么这行就不返回。l 外连接：即使连接条件中的一列包含空值也会返回一行。l 自连接：返回连接的同一个表中的行。不等连接实例 查询员工的工资等级Select e.first_name,e.title,e.salary,sg.salary_grade_idFrom employees e,salary_grades sgWhere e.sala

20、ry Between sg.low_salary And sg.high_salary;-employees员工表，salary_grades工资等级表内连接实例 查询详细信息记录Select a.Id, b.Name, a.Title, a.ContentFrom Infobase aJoin Class b On a.Classid = ( | b.Id | );-或Select a.Id, b.Name, a.Title, a.ContentFrom Infobase a Class bWhere a.Classid = ( | b.Id |); 关联更新和删除Update Infoba

21、se aSet a.Title = (Select Title From Infobase_TempWhere Id = a.Id);Commit;Delete From Infobase aWhere Exists ( Select 1 From Class bWhere a.classid = b.id);Commit;外连接实例 查询没有附件信息记录Select a.*From Infobase aLeft Join Attachment b On a.Id = b.InfoidWhere b.Infoid Is Null;-这是典型两表相减查询，也可用not in，但是种写法效率会高些

22、-这是一个左外连接例子，右外连接跟左外连接一样，只是表的位置不同自连接实例 查询员工和主管之间的关系Select w.Last_Name | works for | m.Last_NameFrom Employees m, Employees wWhere w.Employee_Id = m.Manager_Id; 递归查询下面的语法也可以看做成一个隐含的自连接查询，它是一个字列和父列的递归查询Select *From ClassStart With Parentid = 0001Connect By Prior Id = Parentid-id,parentid那么通过表示每一条记录的par

23、ent是谁，就可以形成一个树状结构-Parentid = 0001指定树的根从哪个节点开始4、 子查询子查询有两种基本类型：l 单行子查询：不向外部的SQL返回结果，或者只返回一行。l 多行子查询：向外部的SQL返回一行或多行。另外子查询还有三种子类型：l 多列子查询：向外部的SQL语句返回多列。l 关联子查询：引用外的SQL语句中的一钱或多列。l 嵌套子查询：位于另外一个子查询中。子查询最多可以嵌套255层。单行子查询实例 查询内容大小大于平均大小的记录Select *From InfobaseWhere Contentsize (Select Avg(Contentsize) From I

24、nfobase); 在having子句中使用子查询检索那些平均价格低于同类产品平均价格最大值的产品的product_type_id和平均价格：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) (Select Max(Avg(Price)From ProductsGroup By Product_Type_Id); 在from子句中使用子查询（内联视图）就外部查询的from子句而言，子查询的输出仅仅是另外一个数据源。检索Productid大于100的产品Select Pr

25、oductidFrom (Select Productid From ProductWhere Productid Select Productid, Productname2 From Product3 Where Productid =4 (Select Productid From Product Where Productname Like 恒泰%);Select Productid, ProductnameORA-01427:单行子查询返回多于一个行(2)、子查询不能包含order by子句，必须在外查询中进行任何排序多行子查询实例 在多行查询中使用in操作符检索信息表里符合clas

26、sid条件的记录：Select *From InfobaseWhere Classid In(Select ( |Id |)From Class Where Name Like营业部%) 在多行子查询中使用any操作符检查是否有任何员工的工资低于salary_grades表中任何一级的最低工资：Select e.Employee_Id, e.Last_NameFrom Employees eWhere e.Salary All (Select sg.high_salaryFrom Salary_Grades Sg);多例子查询实例 检索每种产品类型中价格最低的产品Select *From Pr

27、oductsWhere (Product_Type_Id, Price) In(Select Product_Type_Id, Min(Price)From ProductsGroup By Product_Type_Id);-上面的写法也如同下面的写法，返回结果一样Select *From Products aWhere Price = (Select Min(Price)From ProductsWhere Product_Type_Id = a.Product_Type_Id);-注意：这个例子是日常的开发很典型的例子，会经常用到，一定要学会应用关联子查询实例 在关联子查询中exists

28、检索那些负责管理其它员工的员工记录：Select Employee_Id, Last_NameFrom EmployeesOuter Where Exists(Select Employee_IdFrom Employees InnerInner Where Inner.Manager_Id = Outer.Employee_Id); 在关联子查询中not exists检索从未购买过的产品Select Product_Id,NameFrom Products aWhere Not Exists (Select 1 From Purchases Where Product_Id = a.Prod

29、uct_Id)- 子句的1是个虚拟列，没有意义，改成其它值也可以 Exists和not exists与in和not in的比较Exists与in不同，Exists只检查行的存在性，而in则要检查实际值的存在性。通常来讲，Exists的性能要比in要高一些，因此应该尽可能地使用Exists，而不用in。在编写使用Not Exists和Not in的查询时必须要谨慎。当一个值列表包含一个空值时，Not Exists就返回true,而Not in 则返回false。考虑下面这个例子：本例使用了Not Exists，检索那些在products表中没有任何产品的产品类型：Select Product_T

30、ype_Id,NameFrom Product_Types aWhere Not Exists(Select 1From ProductsWhere Product_Type_Id = a.Product_Type_Id);PRODUCT_TYPE_ID NAME 5 Magazine注意上面这个例子返回了一行记录。下面这个例子使用Not in重写了上面这个例子，而此时没有返回任何行：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select Product_Type_IdFrom Pr

31、oducts);PRODUCT_TYPE_ID NAME 这所以没有返回行，就是因为子查询返回Product_Type_Id值的列表，其中包含一个空值。而产品#12的Product_Type_Id是空值。因此外部查询中的Not in操作符返回false,因此没有任何行。这个问题可以使用Nvl()函数将空值转换成一个值解决。下面的例子中，Nvl()函数将空值的Product_Type_Id转换成0：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select nvl(Product_Type

32、_Id,0)From Products);PRODUCT_TYPE_ID NAME 5 Magazine这次返回了我们要得到的那行记录。嵌套子查询实例 多层嵌套子查询在子查询内部可以嵌套其它子查询，嵌套层次最多为255。在编写时应该尽量少使用嵌套子查询技术，因为使用表连接时，查询性能会更高。下面的例子包含了一个嵌套子查询，子查询包含了另外一个子查询，而它自己又被包含在一个外部查询中：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) = 1)Group By Produc

33、t_Type_Id);这个查询包含了3个查询：一个嵌套子查询、一个子查询和一个外部查询。可以由里到外自己逐步分析，得到运行结果。5、 使用集合操作符集合操作符可以将两个或多个查询返回的行组合起来，当使用集合操作符的时候，必须牢记下列的限制条件：所有查询所返回的列数以及列的类型必须匹配，列名可以不同。集合操作符主要有：l Union all 返回各个查询检索出的所有行，包括重复的行。l Union 返回各个查询检索出的所有行，不包括重复的行。l Intersect 返回两个查询共有行。l Minus 返回第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录。 Union all使用实例

34、Union all 返回各个查询检索出的所有行，包括重复的行SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007)1000000 (000100010002)1000001 (000200020003000100010002)可以使用o

35、rder by子句根据两个查询中的列的位置对列进行排序。SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_tempOrder By 1;ID CLASSID 1000000 (000100010002)1000000 (000100010002)1000001 (000200020003000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (000100010004000300

36、07) Union使用实例返回各个查询检索出的所有行，不包括重复的行。因为Union查询时要有排重操作，所以Union all要比Union操作效率要高一些。SelectId,classidFrom infobase Where Rownum 5UnionSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007) Intersect使用实例In

37、tersect 返回两个查询共有行只检索出那些infobase与infobase_temp共有的行SelectId,classidFrom infobase Where Rownum 5intersectSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002) Minus使用实例Minus 返回第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录.下例是从infobase返回的行中减去从infobase_temp中返回的行，然后返回剩

38、余的行：SelectId,classidFrom infobase Where Rownum Select decode(1,1,2,3) From dual;DECODE(1,1,2,3)2因为对1与1进行比较，由于两者相等，所以返回2（否则返回3）Decode通常在写SQL时与dual表结合给变量赋值。下面这个例子对more_products中的available列进行比较。如果available等于Y，返回字符串Product is available,否则返回字符串Product is not available:Select Prd_Id,Available,Decode(Avail

39、able,Y,Product is available,Product is not available)From More_Products;PRD_ID AVAILABLE DECODE(AVAILABLE,Y,PRODUCTI 1 Y Product is available2 Y Product is available3 N Product is not available4 N Product is not available5 Y Product is available可以向Decode()传递多个搜索和结果参数，如下例：Select Product_Id,Product_Ty

40、pe_Id,Decode(Product_Type_Id, 1,Book,2, Video,3,Dvd,CD)From Products;如果Product_Type_Id=1，返回Book如果Product_Type_Id=2，返回Video如果Product_Type_Id=3，返回Dvd如果Product_Type_Id等于其它值，返回CD Case表达式使用实例case允许if-then-else类型的逻辑处理，而不需要使用pl/sql。Case的工作方式与Decode()类似，通常我们在有较少的判断时使用decode，因为条件多话会看着很混乱；所以尽量使用case，它与ANSI兼容。

41、有两种类型的case表达式：l 简单case表达式，使用表达式确定返回值。l 搜索case表达式，使用条件确定返回值。使用简单表达式例子：Select Product_Id,Product_Type_Id,Case Product_Type_IdWhen 1Then BookWhen 2Then VideoWhen 3Then DvdElse CDEndFrom Products;PRODUCT_ID PRODUCT_TYPE_ID CASEPRODUCT_TYPE_IDWHEN1THENB 1 1 Book2 1 Book3 2 Video4 2 Video5 2 Video6 2 Vide

42、o7 3 Dvd8 3 Dvd9 4 CD使用搜索case表达式Select Product_Id,Product_Type_Id,CaseWhen Product_Type_Id = 1Then BookWhen Product_Type_Id = 2 Then VideoWhen Product_Type_Id = 3 Then DvdElse CDEndFrom Products;返回结果中上面是一样的7、 其它五、 日期和时间的存储与处理1、 常用的几个日期函数说明l MONTHS_BETWEEN两日期相差多少月l ADD_MONTHS 加月份到日期l NEXT_DAY 指定日期的下一

43、天l LAST_DAY 一个月中的最后一天l ROUND Round日期l TRUNC Truncate日期l TO_CHAR(x,format)函数用于将时间值转换为字符串，该函数还可以提供一个可选的参数format来说明x的格式。如：MONTH DDD,YYYYl TO_DATE(x,format)将字符串x转换成date类型。2、 常用的日期计算实例下面是几个关于日期方面的SQL实例 取得当前日期是本月的第几周SQL select to_char(sysdate,YYYYMMDD W HH24:MI:SS) from dual;TO_CHAR(SYSDATE,YYYYMMDDWHH242

44、0090202 1 18:00:43SQL select to_char(sysdate,W) from dual;TO_CHAR(SYSDATE,W)1 取得当前日期是一个星期中的第几天,注意星期日是第一天SQL select sysdate,to_char(sysdate,D) from dual;SYSDATE TO_CHAR(SYSDATE,D) 2009-2-2 18 2select to_char(sysdate,yyyy) from dual; -年select to_char(sysdate,Q from dual; -季select to_char(sysdate,mm) f

45、rom dual; -月select to_char(sysdate,dd) from dual; -日ddd年中的第几天WW年中的第几个星期W该月中第几个星期D周中的星期几hh小时(12)hh24小时(24)Mi分ss秒 取当前日期是星期几中文显示:SQL select to_char(sysdate,day) from dual;TO_CHAR(SYSDATE,DAY)星期四 如果一个表在一个date类型的字段上面建立了索引，如何使用alter session set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 得到当前的日期select sysdate f

46、rom dual;SYSDATE2009-2-2 18 得到当天凌晨0点0分0秒的日期select trunc(sysdate) from dual;TRUNC(SYSDATE)2009-2-2 得到这天的最后一秒select trunc(sysdate) + 0.99999 from dual;TRUNC(SYSDATE)+0.999992009-2-2 23:59:59 得到小时的具体数值select trunc(sysdate) + 1/24 from dual;TRUNC(SYSDATE)+1/242009-2-2 1:00:00select trunc(sysdate) + 7/24

47、 from dual;TRUNC(SYSDATE)+7/242009-2-2 7:00:00 得到明天凌晨0点0分0秒的日期select trunc(sysdate+1) from dual;TRUNC(SYSDATE+1)2009-2-3 本月一日的日期select trunc(sysdate,mm) from dual;TRUNC(SYSDATE,MM)2009-2-1 得到下月一日的日期select trunc(add_months(sysdate,1),mm) from dual;TRUNC(ADD_MONTHS(SYSDATE,1),M2009-3-1 返回当前月的最后一天SQL s

48、elect last_day(sysdate) from dual;LAST_DAY(SYSDATE)2009-2-28 18:11:3SQL select last_day(trunc(sysdate) from dual;LAST_DAY(TRUNC(SYSDATE)2009-2-28SQL select trunc(last_day(sysdate) from dual;TRUNC(LAST_DAY(SYSDATE)2009-2-28SQL select trunc(add_months(sysdate,1),mm) - 1 from dual;TRUNC(ADD_MONTHS(SYSD

49、ATE,1),M2009-2-28 得到一年的每一天Select Trunc(Sysdate, yyyy) + Rn - 1 Date0From (Select Rownum Rn From All_Objects Where Rownum SELECT TO_CHAR(SYSDATE,DDD) FROM DUAL;TO_CHAR(SYSDATE,DDD)033 如何在给现有的日期加上2年SQL select add_months(sysdate,24) from dual;ADD_MONTHS(SYSDATE,24)2011-2-2 18:15:56 判断某一日子所在年分是否为润年SQL s

50、elect decode(to_char(last_day(trunc(sysdate,y)+31),dd),29,闰年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 判断两年后是否为润年SQL select decode(to_char(last_day(trunc(add_months(sysdate,24),y)+31),dd),29,闰年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 得到日期的季度SQL select ceil(to_number(to_char(sysdate,mm)/3

51、) from dual;CEIL(TO_NUMBER(TO_CHAR(SYSDATE1SQL select to_char(sysdate, Q) from dual;TO_CHAR(SYSDATE,Q)1六、 SQL语句的优化写法1、 oracle访问Table的方式ORACLE 采用两种访问表中记录的方式:l 全表扫描全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.l 通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采用索引(I

52、NDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.2、 创建索引索引是表的一个概念部分,用来提高检索数据的效率.通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.除了那些LONG或LONG RAW、LOB数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使

53、用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。大多数情况下,优化器通过WHERE子句访问INDEX.定期的重构索引是有必要的.ALTER INDEX REBUILD 创建普通索引实例CreateIndex infobase_titleOn infobase(title);-创建唯一

54、索引Createunique Index infobase_keyOn infobase (Id); 创建全文索引实例-创建全文索引CREATE INDEX infobase_content ON infobase(content)INDEXTYPE IS CTXSYS.CONTEXT;-在全文索引进行检索SELECT * FROM infobase WHERE CONTAINS (content,first) 0;-创建同步全文索引过程create or replace procedure sync_contentisbeginexecute immediatealter index inf

55、obase_content rebuild online | parameters ( sync ) ;execute immediatealter index infobase_content rebuild online | parameters ( optimize full maxtime unlimited ) ;end sync_content;/-创建作业执行同步过程variable n number;begindbms_job.submit(:n,sync_content;,sysdate,sysdate+1/48);commit;end;/ 创建主建AlterTable in

56、fobaseAdd Constraints infobase_key Primary Key(Id);-表上创建主建相当于在列上的建了一个唯一的索引3、 SQL优化实例及问题 使用like操作符的问题在WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用.如：like %标题。这一点一定要注意。因为在我们开发的过程中经常遇到这样的问题，like %标题%会扫描全表，会给数据库的性能带来很大的压力。要尽可能避免这种写法，如果有必要可以用全文索引代替。如下面的例子：selectcount(*)from infobasewhere classid in

57、(0001000300030001),(0001000300030002)and (category like %600755% or category like%600976%);-可用全文索引代替select count(*) from infobasewhere classid in (0001000300030001),(0001000300030002)and (CONTAINS (category, 600755) 0or CONTAINS (category, 600976) 0 );但like 标题%这种写法会使用索引 选择最有效率的表名顺序(只在基于规则的优化器中有效)ORA

58、CLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如:表 TAB1 16,384条记录表 TAB2 1条记录选择TAB2作为基础表 (最好的方法)select count(*) from tab

59、1,tab2执行时间0.96秒选择TAB2作为基础表 (不佳的方法)select count(*) from tab2,tab1执行时间26.09秒如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表.例如:EMP表描述了LOCATION表和CATEGORY表的交集.SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN将比下列SQL更

60、有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000 WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:第二个SQL要比第一个SQL查询效率高：SELECT *FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 2