时间：2021-07-01 10:21:17 帮助过：17人阅读
ORACLE日常操作手册
目录
一、......数据库的启动和关闭...4
1. 数据库的正常启动步骤...4
2. 数据库的正常关闭步骤...4
3. 几种关闭数据库方法对比...4
4. 数据库的启动关闭过程...4
二、......创建数据库用户...5
1、 以DBA用户登录数据库（如system,sys）...5
2、 用create user语法创建用户...5
3、 赋表空间使用权限...5
4、 给用户赋权限...5
5、 删除用户...5
三、......ORACL常用的数据类型...5
四、......基本的SQL语句的写法...6
1、 rowid和rownum的区别...6
? 删除表中重复记录...6
? 使表处于可编辑状态...6
? 批量删除记录...7
? 分页查询...7
2、 delete和truncate 、drop的区别...7
3、 多表关联查询...7
不等连接实例...7
? 查询员工的工资等级...7
内连接实例...8
? 查询详细信息记录...8
? 关联更新和删除...8
外连接实例...8
? 查询没有附件信息记录...8
自连接实例...8
? 查询员工和主管之间的关系...8
? 递归查询...8
4、 子查询...8
单行子查询实例...9
? 查询内容大小大于平均大小的记录...9
? 在having子句中使用子查询...9
? 在from子句中使用子查询（内联视图）...9
? 可能碰到的两个错误...9
多行子查询实例...9
? 在多行查询中使用in操作符...9
? 在多行子查询中使用any操作符...10
? 在多行子查询中使用all操作符...10
多例子查询实例...10
? 检索每种产品类型中价格最低的产品...10
关联子查询实例...10
? 在关联子查询中exists10
? 在关联子查询中not exists10
? Exists和not exists与in和not in的比较...10
嵌套子查询实例...11
? 多层嵌套子查询...11
5、 使用集合操作符...12
? Union all使用实例...12
? Union使用实例...12
? Intersect使用实例...13
? Minus使用实例...13
6、 Decode函数和Case表达式的比较...13
? Decode函数使用实例...13
? Case表达式使用实例...14
7、 其它...15
五、......日期和时间的存储与处理...15
1、 常用的几个日期函数说明...15
2、 常用的日期计算实例...15
? 取得当前日期是本月的第几周...15
? 取得当前日期是一个星期中的第几天,注意星期日是第一天...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语句的优化写法...18
1、 oracle访问Table的方式...18
2、 创建索引...19
? 创建普通索引实例...19
? 创建全文索引实例...19
? 创建主建...20
3、 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
? 不明确的索引等级...25
? 强制索引失效...26
? 避免在索引列上使用计算．...26
? 自动选择索引...27
? 避免在索引列上使用NOT.27
? 避免在索引列上使用IS NULL和IS NOT NULL.28
? 总是使用索引的第一个列...28
七、......常见的数据库管理和优化配置...29
1、 数据库的备份...29
? 导出/导入(Export/Import)29
? rman备份实例...30
2、 数据库的参数配置及性能调整...31
? 如何增加ORACLE连接数...31
? 关于内存参数的调整...31
? 32bit 和 64bit 的问题...32
? Linux上shmmax参数的设置及含义...32
? 解决CPU高度消耗(100%)的数据库问...33
3、 存储管理...35
? 创建表空间...36
? 管理表空间...36
? 管理数据文件...36
? 查看表空间的使用情况...37
本文档约定：
1、 文中的数据库主要用到了公司cms、pms库结构
2、 所有SQL都实际的测试通过，放心使用。
3、 如果有再需要了解的部分以后可以再做补充。
一、 数据库的启动和关闭
1. 数据库的正常启动步骤
l 以DBA的身份登录数据库（要在oracle安装用户下执行sqlplus）
[oracle@DB1 ~]$sqlplus “/as sysdba”
l 执行启动数据库命令
SQL>startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
l 启动和关闭监听
[oracle@DB1 ~]$lsnrctl start
[oracle@DB1 ~]$lsnrctl stop
2. 数据库的正常关闭步骤
l 同样以DBA的身份登录数据库
[oracle@DB1 ~]$sqlplus “/as sysdba”
l 执行数据库关闭命令
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3. 几种关闭数据库方法对比
SHUTDOWN有四个参数：NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。缺省不带任何参数时表示是NORMAL。
SHUTDOWN NORMAL：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复，这种方法往往不能关闭数据库或等待很长时间。
SHUTDOWN TRANSACTIONAL：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN IMMEDIATE：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。最常用的方法。
SHUTDOWN ABORT：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。一般不推荐采用，只有在数据库无法关闭时使用，可能造成数据库的不一致。
4. 数据库的启动关闭过程
二、 创建数据库用户
1、 以DBA用户登录数据库（如system,sys）
SQL>conn system/oracle@orcl
注：如果在本地服务器登录@orcl可以去掉
2、 用create user语法创建用户
CREATE 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 unlimited 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、15
l NUMBER，是以十进制格式进行存储的，它便于存储，但是在计算上，系统会自动的将它转换成为二进制进行运算的。它的定义方式是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为字符串长度，没有缺省值，作为变量最大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（oracle8以前叫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;
ROWID ID
------------------ ---------
AAAYKRAAEAAGGpcAAI 1000000
AAAYKRAAEAAGGpcAAJ 1000001
AAAYKRAAEAAGGpcAAK 1000002
AAAYKRAAEAAGGpcAAL 1000003
Rowid应用实例：
? 删除表中重复记录
DeleteFrom Infobase a
Where Rowid < (Select Max(Rowid)From Infobase Where Id = a.Id);
? 使表处于可编辑状态
使用下面的语句可以使表处于可编辑状态，可手工添加、删除、更改记录。然后分别点击pl/sql Dev的按
Select t.Rowid,t.*From infobase t;
rownum 被称为“伪数列”，是事实上不存在一个数列，它的特点是按照顺序标记，而且是逐次递加，换句话说只有存在rownum=1的记录，才有可能有rownum=2的记录。查询如下：
SQL> Select Rownum,id From infobase Where Rownum < 5;
ROWNUM ID
---------- ---------
1 1000000
2 1000001
3 1000002
4 1000003
rownum应用实例：
? 批量删除记录
如果要删除的数据量很大，一次删除可能需要占用系统大量的内存，给数据库带来很大的压力，可以进行分步批量删除并提交，避免这种情况
createor replace procedure del_data
as --创建过程并执行
begin
for i in 1..1000loop
delete from cmsuser_zbxy.infobase Where posterid=‘Servlet提交‘and rownum < 100;
commit;
end loop;
End del_data;
? 分页查询
Select *
From (Select * From InfobaseOrder By Originaltime Desc)
Where Rownum <= 10;
2、 delete和truncate、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 等连接：在连接中使用等于操作符(=)
l 不等连接：在连接中使用除等号之外的操作符，如：<、>、between等
除连接条件区分之外，连接本身也有3种不同的类型：
l 内连接：只有当连接中的列包含满足连接条件的值时才会返回一行。这就是说，如果某一行的连接条件中的一列是空值，那么这行就不返回。
l 外连接：即使连接条件中的一列包含空值也会返回一行。
l 自连接：返回连接的同一个表中的行。
不等连接实例
? 查询员工的工资等级
Select e.first_name,e.title,e.salary,sg.salary_grade_id
From employees e,salary_grades sg
Where e.salary Between sg.low_salary And sg.high_salary;
--employees员工表，salary_grades工资等级表
内连接实例
? 查询详细信息记录
Select a.Id, b.Name, a.Title, a.Content
From Infobase a
Join Class b On a.Classid = ‘(‘ || b.Id || ‘)‘;
--或
Select a.Id, b.Name, a.Title, a.Content
From Infobase a Class b
Where a.Classid = ‘(‘ || b.Id ||‘)‘;
? 关联更新和删除
Update Infobase a
Set a.Title = (Select Title From Infobase_TempWhere Id = a.Id);
Commit;
Delete From Infobase a
Where Exists ( Select 1 From Class bWhere a.classid = b.id);
Commit;
外连接实例
? 查询没有附件信息记录
Select a.*
From Infobase a
Left Join Attachment b On a.Id = b.Infoid
Where b.Infoid Is Null;
--这是典型两表相减查询，也可用not in，但是种写法效率会高些
--这是一个左外连接例子，右外连接跟左外连接一样，只是表的位置不同
自连接实例
? 查询员工和主管之间的关系
Select w.Last_Name ||‘ works for ‘ || m.Last_Name
From Employees m, Employees w
Where w.Employee_Id = m.Manager_Id;
? 递归查询
下面的语法也可以看做成一个隐含的自连接查询，它是一个字列和父列的递归查询
Select *
From Class
Start With Parentid = 0001
Connect By Prior Id = Parentid
--id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构
--Parentid = 0001指定树的根从哪个节点开始
4、 子查询
子查询有两种基本类型：
l 单行子查询：不向外部的SQL返回结果，或者只返回一行。
l 多行子查询：向外部的SQL返回一行或多行。
另外子查询还有三种子类型：
l 多列子查询：向外部的SQL语句返回多列。
l 关联子查询：引用外的SQL语句中的一钱或多列。
l 嵌套子查询：位于另外一个子查询中。子查询最多可以嵌套255层。
单行子查询实例
? 查询内容大小大于平均大小的记录
Select *
From Infobase
Where Contentsize > (Select Avg(Contentsize) From Infobase);
? 在having子句中使用子查询
检索那些平均价格低于同类产品平均价格最大值的产品的product_type_id和平均价格：
Select Product_Type_Id,Avg(Price)
From Products
Group By Product_Type_Id
Having Avg(Price) < (Select Max(Avg(Price))
From Products
Group By Product_Type_Id);
? 在from子句中使用子查询（内联视图）
就外部查询的from子句而言，子查询的输出仅仅是另外一个数据源。
检索Productid大于100的产品
Select Productid
From (Select Productid From ProductWhere Productid < 100);
在外部查询中从products表中检索product_id和price列，在子查询中检索一种产品已经被购买的次数：
Select a.Product_Id, a.Price, b.Product_Count
From Products a,
(Select Product_Id, Count(Product_Id) Product_Count
From Purchases
Group By Product_Id) b
Where a.Product_Id = b.Product_Id;
? 可能碰到的两个错误
(1)、单行查询最多返回一行
SQL> Select Productid, Productname
2 From Product
3 Where Productid =
4 (Select Productid From Product Where Productname Like ‘恒泰%‘);
Select Productid, Productname
ORA-01427:单行子查询返回多于一个行
(2)、子查询不能包含order by子句，必须在外查询中进行任何排序
多行子查询实例
? 在多行查询中使用in操作符
检索信息表里符合classid条件的记录：
Select *
From Infobase
Where Classid In
(Select ‘(‘ ||Id || ‘)‘From Class Where Name Like ‘营业部%‘)
? 在多行子查询中使用any操作符
检查是否有任何员工的工资低于salary_grades表中任何一级的最低工资：
Select e.Employee_Id, e.Last_Name
From Employees e
Where e.Salary < Any (Select Sg.Low_SalaryFrom Salary_Grades Sg);
? 在多行子查询中使用all操作符
检查是否有任何员工的工资高于salary_grades表中所有级别的最高工资：
Select e.Employee_Id, e.Last_Name
From Employees e
Where e.Salary > All (Select sg.high_salaryFrom Salary_Grades Sg);
多例子查询实例
? 检索每种产品类型中价格最低的产品
Select *
From Products
Where (Product_Type_Id, Price) In
(Select Product_Type_Id, Min(Price)
From Products
Group By Product_Type_Id);
--上面的写法也如同下面的写法，返回结果一样
Select *
From Products a
Where Price = (Select Min(Price)
From Products
Where Product_Type_Id = a.Product_Type_Id);
--注意：这个例子是日常的开发很典型的例子，会经常用到，一定要学会应用
关联子查询实例
? 在关联子查询中exists
检索那些负责管理其它员工的员工记录：
Select Employee_Id, Last_Name
From Employees
Outer Where Exists
(Select Employee_Id
From Employees Inner
Inner Where Inner.Manager_Id = Outer.Employee_Id);
? 在关联子查询中not exists
检索从未购买过的产品
Select Product_Id,Name
From Products a
Where Not Exists (Select 1 From Purchases Where Product_Id = a.Product_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_Type_Id,Name
From Product_Types a
Where Not Exists
(Select 1From Products Where Product_Type_Id = a.Product_Type_Id);
PRODUCT_TYPE_ID NAME
---------------------------------- ----------
5 Magazine
注意上面这个例子返回了一行记录。下面这个例子使用Not in重写了上面这个例子，而此时没有返回任何行：
Select Product_Type_Id,Name
From Product_Types a
Where Product_Type_Id Not In (Select Product_Type_IdFrom Products);
PRODUCT_TYPE_ID NAME
---------------------------------- ----------
这所以没有返回行，就是因为子查询返回Product_Type_Id值的列表，其中包含一个空值。而产品#12的Product_Type_Id是空值。因此外部查询中的Not in操作符返回false,因此没有任何行。这个问题可以使用Nvl()函数将空值转换成一个值解决。
下面的例子中，Nvl()函数将空值的Product_Type_Id转换成0：
Select Product_Type_Id,Name
From Product_Types a
Where Product_Type_Id Not In (Select nvl(Product_Type_Id,0)From Products);
PRODUCT_TYPE_ID NAME
---------------------------------- ----------
5 Magazine
这次返回了我们要得到的那行记录。
嵌套子查询实例
? 多层嵌套子查询
在子查询内部可以嵌套其它子查询，嵌套层次最多为255。在编写时应该尽量少使用嵌套子查询技术，因为使用表连接时，查询性能会更高。下面的例子包含了一个嵌套子查询，子查询包含了另外一个子查询，而它自己又被包含在一个外部查询中：
Select Product_Type_Id,Avg(Price)
From Products
Group By Product_Type_Id
Having Avg(Price) < (Select Max(Avg(Price))
From Products
Where Product_Type_Id In
(Select Product_Id
From Purchases
Where Quantity >= 1)
Group By Product_Type_Id);
这个查询包含了3个查询：一个嵌套子查询、一个子查询和一个外部查询。可以由里到外自己逐步分析，得到运行结果。
5、 使用集合操作符
集合操作符可以将两个或多个查询返回的行组合起来，当使用集合操作符的时候，必须牢记下列的限制条件：所有查询所返回的列数以及列的类型必须匹配，列名可以不同。
集合操作符主要有：
l Union all 返回各个查询检索出的所有行，包括重复的行。
l Union 返回各个查询检索出的所有行，不包括重复的行。
l Intersect 返回两个查询共有行。
l Minus 返回第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录。
? Union all使用实例
Union all 返回各个查询检索出的所有行，包括重复的行
SelectId,classid From infobase Where Rownum <5
Union All
Select Id,classid From infobase_temp;
ID CLASSID
---------- --------------------------------------------------------------------------------
1000000 (000100010002)
1000001 (000200020003000100010002)
1000002 (000100010003)
1000005 (00010001000400030007)
1000000 (000100010002)
1000001 (000200020003000100010002)
可以使用order by子句根据两个查询中的列的位置对列进行排序。
SelectId,classid From infobase Where Rownum <5
Union All
Select Id,classid From infobase_temp
Order By 1;
ID CLASSID
---------- --------------------------------------------------------------------------------
1000000 (000100010002)
1000000 (000100010002)
1000001 (000200020003000100010002)
1000001 (000200020003000100010002)
1000002 (000100010003)
1000005 (00010001000400030007)
? Union使用实例
返回各个查询检索出的所有行，不包括重复的行。因为Union查询时要有排重操作，所以Union all要比Union操作效率要高一些。
SelectId,classid From infobase Where Rownum <5
Union
Select Id,classid From infobase_temp;
ID CLASSID
---------- --------------------------------------------------------------------------------
1000000 (000100010002)
1000001 (000200020003000100010002)
1000002 (000100010003)
1000005 (00010001000400030007)
? Intersect使用实例
Intersect 返回两个查询共有行
只检索出那些infobase与infobase_temp共有的行
SelectId,classid From infobase Where Rownum <5
intersect
Select Id,classid From infobase_temp;
ID CLASSID
---------- --------------------------------------------------------------------------------
1000000 (000100010002)
1000001 (000200020003000100010002)
? Minus使用实例
Minus 返回第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录.
下例是从infobase返回的行中减去从infobase_temp中返回的行，然后返回剩余的行：
SelectId,classid From infobase Where Rownum <5
Minus
Select Id,classid From infobase_temp;
ID CLASSID
---------- --------------------------------------------------------------------------------
1000002 (000100010003)
1000005 (00010001000400030007)
6、 Decode函数和Case表达式的比较
? Decode函数使用实例
Decode(value,search_value,result,default_value)对value与search_value进行比较，如果两个值相等，Decode()返回result，否则返回default_value。Decode()允许if-then-else类型的逻辑处理，而不需要使用pl/sql。
下面是个简单的例子：
SQL> 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(Available,
‘Y‘,
‘Product is available‘,
‘Product is not available‘)
From More_Products;
PRD_ID AVAILABLE DECODE(AVAILABLE,‘Y‘,‘PRODUCTI
--------------------------------------- --------- ------------------------------
1 Y Product is available
2 Y Product is available
3 N Product is not available
4 N Product is not available
5 Y Product is available
可以向Decode()传递多个搜索和结果参数，如下例：
Select Product_Id,
Product_Type_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兼容。
有两种类型的case表达式：
l 简单case表达式，使用表达式确定返回值。
l 搜索case表达式，使用条件确定返回值。
使用简单表达式例子：
Select Product_Id,
Product_Type_Id,
Case Product_Type_Id
When 1Then ‘Book‘
When 2Then ‘Video‘
When 3Then ‘Dvd‘
Else ‘CD‘
End
From Products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPRODUCT_TYPE_IDWHEN1THEN‘B
--------- -------------------------------- ------------------------------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 Dvd
8 3 Dvd
9 4 CD
使用搜索case表达式
Select Product_Id,
Product_Type_Id,
Case
When Product_Type_Id = 1Then ‘Book‘
When Product_Type_Id = 2 Then ‘Video‘
When Product_Type_Id = 3 Then ‘Dvd‘
Else ‘CD‘
End
From Products;
返回结果中上面是一样的
7、 其它
五、 日期和时间的存储与处理
1、 常用的几个日期函数说明
l MONTHS_BETWEEN两日期相差多少月
l ADD_MONTHS 加月份到日期
l NEXT_DAY 指定日期的下一天
l LAST_DAY 一个月中的最后一天
l ROUND Round日期
l TRUNC Truncate日期
l TO_CHAR(x[,format])函数用于将时间值转换为字符串，该函数还可以提供一个可选的参数format来说明x的格式。如：MONTH DDD,YYYY
l TO_DATE(x[,format])将字符串x转换成date类型。
2、 常用的日期计算实例
下面是几个关于日期方面的SQL实例
? 取得当前日期是本月的第几周
SQL> select to_char(sysdate,‘YYYYMMDD W HH24:MI:SS‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDWHH24
------------------------------
20090202 1 18:00:43
SQL> 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 2
select to_char(sysdate,‘yyyy‘) from dual; --年
select to_char(sysdate,‘Q‘ from dual; --季
select to_char(sysdate,‘mm‘) from 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 from dual;
SYSDATE
-----------
2009-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.99999
----------------------
2009-2-2 23:59:59
? 得到小时的具体数值
select trunc(sysdate) + 1/24 from dual;
TRUNC(SYSDATE)+1/24
-------------------
2009-2-2 1:00:00
select trunc(sysdate) + 7/24 from dual;
TRUNC(SYSDATE)+7/24
-------------------
2009-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),‘M
------------------------------
2009-3-1
? 返回当前月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2009-2-28 18:11:3
SQL> select last_day(trunc(sysdate)) from dual;
LAST_DAY(TRUNC(SYSDATE))
------------------------
2009-2-28
SQL> select trunc(last_day(sysdate)) from dual;
TRUNC(LAST_DAY(SYSDATE))
------------------------
2009-2-28
SQL> select trunc(add_months(sysdate,1),‘mm‘) - 1 from dual;
TRUNC(ADD_MONTHS(SYSDATE,1),‘M
------------------------------
2009-2-28
? 得到一年的每一天
Select Trunc(Sysdate, ‘yyyy‘) + Rn - 1 Date0
From (Select Rownum Rn From All_Objects Where Rownum < 366);
DATE0
-----------
2009-1-1
2009-1-2
2009-1-3
……
今天是今年的第N天
SQL> 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> select 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) from dual;
CEIL(TO_NUMBER(TO_CHAR(SYSDATE
------------------------------
1
SQL> 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采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
2、 创建索引
索引是表的一个概念部分,用来提高检索数据的效率.通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.除了那些LONG或LONG RAW、LOB数据类型,你可以索引几乎所有的列. 通常,在大型表中使用索引特别有效. 当然,你也会发现,在扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来
存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改.这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。
大多数情况下,优化器通过WHERE子句访问INDEX.
定期的重构索引是有必要的.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
? 创建普通索引实例
CreateIndex infobase_title On infobase(title);
--创建唯一索引
Createunique Index infobase_key On 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_content
is
begin
execute immediate
‘alter index infobase_content rebuild online‘ ||
‘ parameters ( ‘‘sync‘‘ )‘ ;
execute immediate
‘alter index infobase_content rebuild online‘ ||
‘ parameters ( ‘‘optimize full maxtime unlimited‘‘ )‘ ;
end sync_content;
/
--创建作业执行同步过程
variable n number;
begin
dbms_job.submit(:n,‘sync_content;‘,sysdate,
‘sysdate+1/48‘);
commit;
end;
/
? 创建主建
AlterTable infobase
Add Constraints infobase_key Primary Key(Id);
--表上创建主建相当于在列上的建了一个唯一的索引
3、 SQL优化实例及问题
? 使用like操作符的问题
在WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用.如：like ‘%标题’。
这一点一定要注意。因为在我们开发的过程中经常遇到这样的问题，like ‘%标题%’会扫描全表，会给数据库的性能带来很大的压力。要尽可能避免这种写法，如果有必要可以用全文索引代替。如下面的例子：
selectcount(*) from infobase
where classid in (‘(0001000300030001)‘,‘(0001000300030002)‘)
and (category like ‘%600755%‘ or category like‘%600976%‘);
--可用全文索引代替
select count(*) from infobase
where classid in (‘(0001000300030001)‘,‘(0001000300030002)‘)
and (CONTAINS (category, ‘600755‘) > 0or CONTAINS (category, ‘600976‘) > 0 );
但like ‘标题%’这种写法会使用索引
? 选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
表 TAB1 16,384条记录
表 TAB2 1条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2
执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1
执行时间26.09秒
如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
将比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
? WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:
第二个SQL要比第一个SQL查询效率高：
SELECT *
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER‘
AND 25 < (SELECT COUNT(*)