oracle学习浓缩笔记Word格式文档下载.docx
《oracle学习浓缩笔记Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《oracle学习浓缩笔记Word格式文档下载.docx（37页珍藏版）》请在冰点文库上搜索。
,ename,commFROMemp;
——nvl处理null
SELECTename,salFROMempWHEREenamelike'
__O%'
——like操作符
SELECT*FROMempWHEREempnoin（7844,7839,123,456）;
——where…in（）
SELECT*FROMempORDERbydeptno,salDESC;
——orderby排序
显示平均工资低于2000的部门号和它的平均工资？
SELECTAVG（sal）,MAX（sal）,deptnoFROMempGROUPbydeptnohavingAVG（sal）<
2000;
——分组函数的应用。
SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;
——多表查询，orderby中deptno也需要加上表名
selecta1.ename||'
的上司是'
||a2.ename"
员工关系表"
fromempa1,empa2wherea1.MGR=a2.empno;
——自连接
查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
SELECT*FROMempWHEREjobIN（SELECTDISTINCTjobFROMempWHEREdeptno=10）;
显示高于自己部门平均工资的员工的信息
selectemp.*fromemp,（selectavg（sal）asal,deptnofromempgroupbydeptno）a1whereemp.sal>
a1.asalandemp.deptno=a1.deptno;
——其中，平均工资必须取别名，否则后面比较时不能用分组函数，给查询的结果定义名字为a1
select*from（selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<
=10）wherern>
=6;
——分页查询
CREATETABLEmytable（id,name,sal,job,deptno）asSELECTempno,ename,sal,job,deptnoFROMemp;
——用查询结果创建新表
合并查询：
Union并集，取消重复行
unionall并集，不取消重复行，不排序
intersect交集
minus差集，第一个集合减去第二个集合
SELECTename,sal,jobFROMempWHEREsal>
2500
MINUS
SELECTename,sal,jobFROMempWHEREjob='
MANAGER'
insertintoempvalues
（9998,
'
xiaohong'
7782,
to_date（'
1988-12-12'
）,
78.9,
55.33,
10）;
——to_date函数
insertintokkk（myId,myName,myDept）
selectempno,ename,deptnofromempwheredeptno=10;
——直接把查询结果导入表中
updateempset（job,sal,comm）=（selectjob,sal,commfromempwhereename='
SMITH'
）whereename='
——直接用查询结果更新数据
commit——提交事务
字符函数：
lower（char）：
将字符串转化为小写的格式。
upper（char）：
将字符串转化为大写的格式。
length（char）：
返回字符串的长度。
substr（char,m,n）：
m是指从第m个开始取，n代表取n个的意思，不是代表取到第n个
replace（char,search_string,replace_string）用replace_string替换search_string显示（并不修改库里内容）。
instr（char1,char2,[,n[,m]]）取子串在字符串的位置
数学函数：
round（n,[m]）该函数用于执行四舍五入
trunc（n,[m]）该函数用于截取数字。
mod（m,n）取余数
floor（n）返回小于或是等于n的最大整数（向下取整）
ceil（n）返回大于或是等于n的最小整数（向上取整）
日期函数：
sysdate:
该函数返回系统时间
对于每个员工，显示其加入公司的天数。
SQL>
selectfloor（sysdate-hiredate）"
入职天数"
enamefromemp;
add_months（d,n）增加月份
查找已经入职8个月多的员工
select*fromempwheresysdate>
=add_months（hiredate,8）;
last_day（d）：
返回指定日期所在月份的最后一天
找出各月倒数第3天受雇的所有员工。
selecthiredate,enamefromempwherelast_day（hiredate）-2=hiredate;
转换函数：
to_char
yy：
两位数字的年份2004-->
04
yyyy：
四位数字的年份
2004年
mm：
两位数字的月份8月-->
08
dd：
两位数字的天30号-->
30
hh24：
8点-->
20
hh12：
8点-->
mi、ss-->
显示分钟\秒
9：
显示数字，并忽略前面0
0：
显示数字，如位数不足，则用0补齐
.：
在指定位置显示小数点
：
在指定位置显示逗号
显示薪水的时候，把本地货币单位加在前面
selectename,to_char（hiredate,'
yyyy-mm-ddhh24:
mi:
ss'
）,to_char（sal,'
L99,999.99'
）fromemp;
显示所有12月份入职的员工
select*fromempwhereto_char（hiredate,'
mm'
）=12;
to_date
函数to_date用于将字符串转换成date类型的数据。
select*fromempwherehiredate=to_date（'
2015-01-0418:
20:
46'
'
）;
导出
导出具体的分为：
导出表，导出方案，导出数据库三种方式。
导出使用exp命令来完成的，该命令常用的选项有：
userid：
用于指定执行导出操作的用户名，口令，连接字符串
tables：
用于指定执行导出操作的表
owner：
用于指定执行导出操作的方案
full=y：
用于指定执行导出操作的数据库
inctype：
用于指定执行导出操作的增量类型
rows：
用于指定执行导出操作是否要导出表中的数据
file：
用于指定导出文件名
导出scott的表
expuserid=system/manager@myoraltables=（scott.emp）file=d:
\e2.emp
导出表的结构
expuserid=scott/tiger@accptables=（emp）file=d:
\e3.dmp
rows=n
使用直接导出方式
\e4.dmp
direct=y
导出其它方案
expuserid=system/manager@myorclowner=（system,scott）file=d:
\system.dmp
增量备份inctype=complete（好处是第一次备份后，第二次备份就快很多了）
expuserid=system/manager@myorclfull=yinctype=completefile=d:
\all.dmp
导入
imp常用的选项有
用于指定执行导入操作的用户名，口令，连接字符串
用于指定执行导入操作的表
formuser：
用于指定源用户
touser：
用于指定目标用户
用于指定导入文件名
用于指定执行导入整个文件
用于指定执行导入操作的增量类型
指定是否要导入表行（数据）
ignore：
如果表存在，则只导入数据
1.导入自己的表
impuserid=scott/tiger@myorcltables=（emp）file=d:
\xx.dmp
2.导入表到其它用户
要求该用户具有dba的权限，或是imp_full_database
impuserid=system/tiger@myorcltables=（emp）file=d:
\xx.dmptouser=scott
3.导入表的结构
只导入表的结构而不导入数据
\xx.dmp
rows=n
4.导入数据
如果对象（如比表）已经存在可以只导入表的数据
ignore=y
1．导入自身的方案
impuserid=scott/tigerfile=d:
\xxx.dmp
2．导入其它方案
要求该用户具有dba的权限
impuserid=system/managerfile=d:
\xxx.dmpfromuser=systemtouser=scott
导入数据库
在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：
impuserid=system/managerfull=yfile=d:
\xxx.dmp
如何查询一个角色包括的权限？
a.一个角色包含的系统权限
select*fromdba_sys_privswheregrantee='
角色名'
另外也可以这样查看：
select*fromrole_sys_privswhererole='
b.一个角色包含的对象权限
select*fromdba_tab_privswheregrantee='
显示当前用户可以访问的所有数据字典视图。
select*fromdictwherecommentslike'
%grant%'
显示当前数据库的全称
select*fromglobal_name;
约束
notnull（非空）
如果在列上定义了notnull，那么当插入数据时，必须为列提供数据。
unique（唯一）
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
primarykey（主键）
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。
需要说明的是：
一张表最多只能有一个主键，但是可以有多个unqiue约束。
foreignkey（外键）
用于定义主表和从表之间的关系。
外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。
商店售货系统表设计案例
现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider）；
客户customer（客户号customerId，姓名name，住在address，电邮email，性别sex，身份证cardId）；
购买purchase（客户号customerId，商品号goodsId，购买数量nums）；
请用SQL语言完成下列功能：
1.建表，在定义中要求声明：
（1）.每个表的主外键；
（2）.客户的姓名不能为空值；
（3）.单价必须大于0，购买数量必须在1到30之间；
（4）.电邮不能够重复；
（5）.客户的性别必须是男或者女，默认是男；
createtablegoods（goodsIdchar（8）primarykey,--主键
goodsNamevarchar2（30），
unitpricenumber（10,2）check（unitprice>
0）,
categoryvarchar2（8）,
providervarchar2（30）
）;
createtablecustomer（customerIdchar（8）primarykey,--主键
namevarchar2（50）notnull,
--不为空
addressvarchar2（50）,
emailvarchar2（50）unique,
sexchar
（2）default'
check（sexin（'
，'
女'
）），--一个char能存半个汉字，两位char能存一个汉字
cardIdchar（18）
createtablepurchase（customerIdchar（8）referencescustomer（customerId）,
goodsIdchar（8）referencesgoods（goodsId）,
numsnumber（10）check（numsbetween1and30）
维护
商店售货系统表设计案例
（2）
如果在建表时忘记建立必要的约束，则可以在建表后使用altertable命令为表增加约束。
但是要注意：
增加notnull约束时，需要使用modify选项，而增加其它四种约束使用add选项。
1.增加商品名也不能为空
altertablegoodsmodifygoodsNamenotnull;
2.增加身份证也不能重复
altertablecustomeraddconstraintxxxxxxunique（cardId）;
3.增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；
altertablecustomeraddconstraintyyyyyycheck（addressin（’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’））;
删除约束
当不再需要某个约束时，可以删除。
altertable表名dropconstraint约束名称；
索引
createindexemp_idx1onemp（ename,job）;
显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
selecttable_name,column_namefromuser_ind_columnswhereindex_name='
IND_ENAME'
系统权限介绍
createsession
连接数据库
createtable
建表
createview
建视图
createpublicsynonym
建同义词
createprocedure建过程、函数、包
createtrigger
建触发器
createcluster建簇
回收系统权限
revokecreatesessionfromken;
对象权限
常用的有：
alter修改
delete删除
select查询
insert添加
update修改
index索引
references引用
execute执行
希望monkey只可以修改scott.emp的表的sal字段，怎样操作？
grantupdateonemp（sal）tomonkey
10g:
grantupdate（sal）onemptomonkey
如果用户想要执行其它方案的包/过程/函数，则须有execute权限。
grantexecuteondbms_transactiontoken;
授予index权限
grantindexonscott.emptoblake;
回收对象权限
revokeselectonempfromblake
标志符号的命名规范
1）.当定义变量时，建议用v_作为前缀v_sal
2）.当定义常量时，建议用c_作为前缀c_rate
3）.当定义游标时，建议用_cursor作为后缀emp_cursor
4）.当定义例外时，建议用e_作为前缀e_error
pl/sql块
1.declare
2.
--定义变量
3.
v_ename
varchar2（5）;
4.
v_sal
number（7,2）;
5.begin
6.
--执行部分
7.
select
ename,sal
into
v_ename,v_sal
from
emp
where
empno=&
aa;
8.
--在控制台显示用户名
9.dbms_output.put_line（'
用户名是：
'
||v_ename||'
工资：
||v_sal）;
10.exception
11.
--异常处理
12.when
no_data_found
then
13.
dbms_output.put_line（'
朋友，你的编号输入有误！
）；
14.end;
15./
函数
1.--输入雇员的姓名，返回该雇员的年薪
2.create
function
annual_incomec（name
varchar2）
3.return
number
is
4.annual_salazy
sal*12+nvl（comm,
0）
annual_salazy
ename=name;
return
annual_salazy;
9.end;
10./
包
1.create
package
sp_package
procedure
update_sal（name
varchar2,
newsal
number）;
annual_income（name
varchar2）
number;
4.end;
给包sp_package实现包体
Sql代码
or
replace
body
number）
begin
5.
update
set
sal
=
newsal
升级会员 