ORACLE日常操作手册.docx
- 文档编号:10153321
- 上传时间:2023-02-08
- 格式:DOCX
- 页数:59
- 大小:93.50KB
ORACLE日常操作手册.docx
《ORACLE日常操作手册.docx》由会员分享,可在线阅读,更多相关《ORACLE日常操作手册.docx(59页珍藏版)》请在冰豆网上搜索。
ORACLE日常操作手册
ORACLE日常操作手册
目录
一、数据库的启动和关闭4
1.数据库的正常启动步骤4
2.数据库的正常关闭步骤4
3.几种关闭数据库方法对比4
4.数据库的启动关闭过程4
二、创建数据库用户5
1、以DBA用户登录数据库(如system,sys)5
2、用createuser语法创建用户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
在关联子查询中notexists10
Exists和notexists与in和notin的比较10
嵌套子查询实例11
多层嵌套子查询11
5、使用集合操作符12
Unionall使用实例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
尽量多使用COMMIT22
减少对表的查询22
通过内部函数提高SQL效率.23
使用表的别名(Alias)24
用EXISTS替代IN和用NOTEXISTS替代NOTIN24
用表连接替换EXISTS24
用EXISTS替换DISTINCT24
等式比较和范围比较25
不明确的索引等级25
强制索引失效26
避免在索引列上使用计算.26
自动选择索引27
避免在索引列上使用NOT27
避免在索引列上使用ISNULL和ISNOTNULL28
总是使用索引的第一个列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.数据库的正常启动步骤
●以DBA的身份登录数据库(要在oracle安装用户下执行sqlplus)
[oracle@DB1~]$sqlplus“/assysdba”
●执行启动数据库命令
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218968bytes
VariableSize88082024bytes
DatabaseBuffers188743680bytes
RedoBuffers7168000bytes
Databasemounted.
Databaseopened.
●启动和关闭监听
[oracle@DB1~]$lsnrctlstart
[oracle@DB1~]$lsnrctlstop
2.数据库的正常关闭步骤
●同样以DBA的身份登录数据库
[oracle@DB1~]$sqlplus“/assysdba”
●执行数据库关闭命令
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
3.几种关闭数据库方法对比
SHUTDOWN有四个参数:
NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。
缺省不带任何参数时表示是NORMAL。
SHUTDOWNNORMAL:
不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。
启动时不需要实例恢复,这种方法往往不能关闭数据库或等待很长时间。
SHUTDOWNTRANSACTIONAL:
不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。
启动时不需要实例恢复。
SHUTDOWNIMMEDIATE:
不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。
没有结束的事务是自动rollback的。
启动时不需要实例恢复。
最常用的方法。
SHUTDOWNABORT:
不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。
启动时自动进行实例恢复。
一般不推荐采用,只有在数据库无法关闭时使用,可能造成数据库的不一致。
4.数据库的启动关闭过程
二、创建数据库用户
1、以DBA用户登录数据库(如system,sys)
SQL>connsystem/oracle@orcl
注:
如果在本地服务器登录@orcl可以去掉
2、用createuser语法创建用户
CREATEUSERuser_nameIDENTIFIEDBYuser_passwordDefaultTablespacetbs_users;
●user_name为数据库用户的用户名
●user_password为数据库用户的密码
●tbs_users为用户使用的表空间,默认是users表空间。
例如:
CREATEUSERcmsuserIDENTIFIEDBYpasswordDefaultTablespaceusers;
3、赋表空间使用权限
alteruseruser_namequotaunlimitedonuser_tablespacequotaunlimitedonuser_tablespace;
4、给用户赋权限
GRANTconnect,resourceTOcmsuser;
●Connect用户能登录数据库的权限
●Resource用户能创建一些数据库对像的权限,表、视图,存储过程,一般是授予开发人员的
5、删除用户
DropUsercmsuserCascade;
●使用cascade参数可以删除该用户的全部objects
三、ORACL常用的数据类型
●INTEGER存储整数,整数不包括浮点数;它是一个整数数字,如:
1、10、15
●NUMBER,是以十进制格式进行存储的,它便于存储,但是在计算上,系统会自动的将它转换成为二进制进行运算的。
它的定义方式是NUMBER(P,S),P是精度,最大38位,S是刻度范围,可在-84...127间取值。
例如:
NUMBER(5,2)可以用来存储表示-999.99...999.99间的数值。
P、S可以在定义是省略,例如:
NUMBER(5)、NUMBER等;
●CHAR,描述定长的字符串,如果实际值不够定义的长度,系统将以空格填充。
它的声明方式如下CHAR(L),L为字符串长度,缺省为1,作为变量最大32767个字符,作为数据存储在ORACLE8中最大为2000。
●VARCHAR2(VARCHAR),描述变长字符串。
它的声明方式如下VARCHAR2(L),L为字符串长度,没有缺省值,作为变量最大32767个字节,作为数据存储在ORACLE8中最大为4000。
在多字节语言环境中,实际存储的字符个数可能小于L值,例如:
当语言环境为中文(SIMPLIFIEDCHINESE_CHINA.ZHS16GBK)时,一个VARCHAR2(200)的数据列可以保存200个英文字符或者100个汉字字符。
●NCHAR、NVARCHAR2,国家字符集,与环境变量NLS指定的语言集密切相关,使用方法和CHAR、VARCHAR2相同。
不过最大参数为NCHAR(2000)、NVARCHAR2(2000)
●DATE唯一的一种日期类型--,用来存储时间信息,站用7个字节(从世纪到秒)
●LOB(oracle8以前叫long)变量主要是用来存储大量数据的数据库字段,最大可以存储4G字节的内容,CLOB:
存储单字节字符数据(如英文)NCLOB:
用来存储定宽多字节字符数据(如汉字),BLOB:
用来存储无结构的二进制数据(word、pdf文档)。
四、基本的SQL语句的写法
1、rowid和rownum的区别
rowid是Oracle数据库中的每一行都有一个唯一的行标识符,称为rowid,它是一个18位数字,以64为基数,该徝包含了该行在oracle数据库中的物理位置,查询rowid如下:
SQL>Selectrowid,idFrominfobaseWhereRownum<5;
ROWIDID
---------------------------
AAAYKRAAEAAGGpcAAI1000000
AAAYKRAAEAAGGpcAAJ1000001
AAAYKRAAEAAGGpcAAK1000002
AAAYKRAAEAAGGpcAAL1000003
Rowid应用实例:
Ø删除表中重复记录
DeleteFromInfobasea
WhereRowid<(SelectMax(Rowid)FromInfobaseWhereId=a.Id);
Ø使表处于可编辑状态
使用下面的语句可以使表处于可编辑状态,可手工添加、删除、更改记录。
然后分别点击pl/sqlDev的
按
Selectt.Rowid,t.*Frominfobaset;
rownum被称为“伪数列”,是事实上不存在一个数列,它的特点是按照顺序标记,而且是逐次递加,换句话说只有存在rownum=1的记录,才有可能有rownum=2的记录。
查询如下:
SQL>SelectRownum,idFrominfobaseWhereRownum<5;
ROWNUMID
-------------------
11000000
21000001
31000002
41000003
rownum应用实例:
Ø批量删除记录
如果要删除的数据量很大,一次删除可能需要占用系统大量的内存,给数据库带来很大的压力,可以进行分步批量删除并提交,避免这种情况
createorreplaceproceduredel_data
as--创建过程并执行
begin
foriin1..1000loop
deletefromcmsuser_zbxy.infobaseWhereposterid='Servlet提交'andrownum<100;
commit;
endloop;
Enddel_data;
Ø分页查询
Select*
From(Select*FromInfobaseOrderByOriginaltimeDesc)
WhereRownum<=10;
2、delete和truncate、drop的区别
TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同:
二者均删除表中的全部行。
但TRUNCATETABLE比DELETE速度快,且使用的系统和事务日志资源少。
Drop则是删除整个表,与TRUNCATE操作类型相同,都是DDL操作(数据定义语言)
DeleteFrominfobaseId=1;
Commit;
--或
DeleteinfobaseWhereId=1;
Commit;
TruncateTableinfobase;
DropTableinfobase;
3、多表关联查询
根据连接中使用操作符的不同,连接条件可以分为两类:
●等连接:
在连接中使用等于操作符(=)
●不等连接:
在连接中使用除等号之外的操作符,如:
<、>、between等
除连接条件区分之外,连接本身也有3种不同的类型:
●内连接:
只有当连接中的列包含满足连接条件的值时才会返回一行。
这就是说,如果某一行的连接条件中的一列是空值,那么这行就不返回。
●外连接:
即使连接条件中的一列包含空值也会返回一行。
●自连接:
返回连接的同一个表中的行。
不等连接实例
Ø查询员工的工资等级
Selecte.first_name,e.title,e.salary,sg.salary_grade_id
Fromemployeese,salary_gradessg
Wheree.salaryBetweensg.low_salaryAndsg.high_salary;
--employees员工表,salary_grades工资等级表
内连接实例
Ø查询详细信息记录
Selecta.Id,b.Name,a.Title,a.Content
FromInfobasea
JoinClassbOna.Classid='('||b.Id||')';
--或
Selecta.Id,b.Name,a.Title,a.Content
FromInfobaseaClassb
Wherea.Classid='('||b.Id||')';
Ø关联更新和删除
UpdateInfobasea
Seta.Title=(SelectTitleFromInfobase_TempWhereId=a.Id);
Commit;
DeleteFromInfobasea
WhereExists(Select1FromClassbWherea.classid=b.id);
Commit;
外连接实例
Ø查询没有附件信息记录
Selecta.*
FromInfobasea
LeftJoinAttachmentbOna.Id=b.Infoid
Whereb.InfoidIsNull;
--这是典型两表相减查询,也可用notin,但是种写法效率会高些
--这是一个左外连接例子,右外连接跟左外连接一样,只是表的位置不同
自连接实例
Ø查询员工和主管之间的关系
Selectw.Last_Name||'worksfor'||m.Last_Name
FromEmployeesm,Employeesw
Wherew.Employee_Id=m.Manager_Id;
Ø递归查询
下面的语法也可以看做成一个隐含的自连接查询,它是一个字列和父列的递归查询
Select*
FromClass
StartWithParentid=0001
ConnectByPriorId=Parentid
--id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构
--Parentid=0001指定树的根从哪个节点开始
4、子查询
子查询有两种基本类型:
●单行子查询:
不向外部的SQL返回结果,或者只返回一行。
●多行子查询:
向外部的SQL返回一行或多行。
另外子查询还有三种子类型:
●多列子查询:
向外部的SQL语句返回多列。
●关联子查询:
引用外的SQL语句中的一钱或多列。
●嵌套子查询:
位于另外一个子查询中。
子查询最多可以嵌套255层。
单行子查询实例
Ø查询内容大小大于平均大小的记录
Select*
FromInfobase
WhereContentsize>(SelectAvg(Contentsize)FromInfobase);
Ø在having子句中使用子查询
检索那些平均价格低于同类产品平均价格最大值的产品的product_type_id和平均价格:
SelectProduct_Type_Id,Avg(Price)
FromProducts
GroupByProduct_Type_Id
HavingAvg(Price)<(SelectMax(Avg(Price))
FromProducts
GroupByProduct_Type_Id);
Ø在from子句中使用子查询(内联视图)
就外部查询的from子句而言,子查询的输出仅仅是另外一个数据源。
检索Productid大于100的产品
SelectProductid
From(SelectProductidFromProductWhereProductid<100);
在外部查询中从products表中检索product_id和price列,在子查询中检索一种产品已经被购买的次数:
Selecta.Product_Id,a.Price,b.Product_Count
FromProductsa,
(SelectProduct_Id,Count(Product_Id)Product_Count
FromPurchases
GroupByProduct_Id)b
Wherea.Product_Id=b.Product_Id;
Ø可能碰到的两个错误
(1)、单行查询最多返回一行
SQL>SelectProductid,Productname
2FromProduct
3WhereProductid=
4(SelectProductidFromProductWhereProductnameLike'恒泰%');
SelectProductid,Productname
ORA-01427:
单行子查询返回多于一个行
(2)、子查询不能包含orderby子句,必须在外查询中进行任何排序
多行子查询实例
Ø在多行查询中使用in操作符
检索信息表里符合classid条件的记录:
Select*
FromInfobase
WhereClassidIn
(Select'('||Id||')'FromClassWhereNameLike'营业部%')
Ø在多行子查询中使用any操作符
检查是否有任何员工的工资低于salary_grades表中任何一级的最低工资:
Selecte.Employee_Id,e.Last_Name
FromEmployeese
Wheree.Salary Ø在多行子查询中使用all操作符 检查是否有任何员工的工资高于salary_grades表中所有级别的最高工资: Selecte.Employee_Id,e.Last_Name FromEmployeese Wheree.Salary>All(Selectsg.high_salaryFromSalary_GradesSg); 多例子查询实例 Ø检索每种产品类型中价格最低的产品 Select* FromProducts Where(Product_Type_Id,Price)In (SelectProduct_Type_Id,Min(Price) FromProducts GroupByProduct_Type_Id); --上面的写法也如同下面的写法,返回结果一样 Select* FromProductsa WherePrice=(SelectMin(Price) FromProducts WhereProduct_Type_Id=a.Product_Type_Id); --注意: 这个例子是日常的开发很典型的例子,会经常用到,一定要学会应用 关联子查询实例 Ø在关联子查询中exists 检索那些负责管理其它员工的员工记录: SelectEmployee_Id,Last_Name FromEmployees OuterWhereExists (SelectEmployee_Id From
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 日常 操作手册