oracle学习浓缩笔记Word格式文档下载.docx
- 文档编号:3412725
- 上传时间:2023-05-01
- 格式:DOCX
- 页数:37
- 大小:32.65KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 学习 浓缩 笔记