Oracle笔记.docx
- 文档编号:11793747
- 上传时间:2023-04-02
- 格式:DOCX
- 页数:19
- 大小:70.21KB
Oracle笔记.docx
《Oracle笔记.docx》由会员分享,可在线阅读,更多相关《Oracle笔记.docx(19页珍藏版)》请在冰豆网上搜索。
Oracle笔记
Oracle笔记
主编:
够潮
版本:
V.20110325
创建表空间:
createtablespacegouchao
datafile'f:
\xyc01.dbf'size50m;
用户:
创建用户:
createuserxuyongchao3identifiedbyxyc
defaulttablespacegouchao
temporarytablespacetemp;
修改用户密码:
alteruserxuyongchaoidentifiedbygouchao;
设置密码过期:
alterusergouchaopasswordexpire;
锁定用户:
alteruserxuyongchaoaccountlock;
为用户解锁:
alteruserxuyongchaoaccountunlock;
为用户授权:
(系统权限)
grantconnect,resource,createtable,createsession,createproceduretoxuyongchao;、
为用户授数据对象权限:
grantselectonscott.emptogouchao;
为用户撤销权限:
revokeconnectfromgouchao;
创建角色:
createrolestudent;
为角色授权:
grantselectanytable,connect,resourcetostudentwithadminoption;
为用户授角色:
grantstudenttoxuyongchao;
为用户撤销角色
revoketeacherfromxuyongchao;
删除角色:
droproleteacher;
在数据字典中查询用户信息,授权情况。
角色信息
查询用户信息:
select*fromdba_users;
select*fromdba_userswhereusername='XUYONGCHAO';
授权情况:
connxuyongchao/xyc
select*fromuser_sys_privs;
角色信息:
connxuyongchao/xyc
select*fromuser_role_privs;
序列
查看当前用户所创建的序列:
select*fromuser_sequences;
创建序列:
createsequenceS_userno
startwith50incrementby10
MAXvalue99cache10;
修改序列:
altersequenceS_userno
maxvalue200cache20;
删除序列:
dropsequenceS_userno
使用序列:
nextVal:
用于返回下一个序列号
Curral:
用于返回当前序列号
insertintouser(userno,username)values(S_userno.Nextval,'33');
同义词:
同义词是方案对象的别名,作用:
1)简化对象访问2)提高对象访问的安全性
建立公共同义词:
createpublicsynonympublic_empforscott.emp;
使用同义词
select*frompublic_emp;
建立私有同义词:
createsynonymprivate_empforscott.emp;
删除公共同义词:
droppublicsynonympublic_emp;
删除私有同义词
dropsynonymprivate_emp;
表:
建表:
createtableuser_list
(
user_namevarchar2(23),
user_ageint
);
查看表结构:
descuser_list;
添加字段:
altertableuser_list
add(usernoint);
修改字段:
altertableuser_list
modify(user_namevarchar2(50));
删除字段:
altertableuser_list
dropcolumnuser_age;
对表添加数据:
insertintouser_list(user_name,userno)values('gouchao',10001);
insertintouser_listvalues('xuyongchao',10003);
insertintouser_listvalues('gouchao',S_userno.Nextval);
对表修改数据:
updateuser_list
setuser_name='gouli'whereuser_name='gouchao';
对表删除数据:
deletefromuser_listwhereuserno=10001;
删除表格:
droptableuser_list;
查询:
查询所有记录:
select*fromuser_list;
selectusernofromuser_list;
selectusernoIDfromuser_list;
selectuser_list.usernofromuser_list;
selecta.usernofromuser_lista;
查询所有记录的某些字段:
selectempno,enamefromemp;
查询某些字段的不同记录:
selectdistinctjobfromemp;
单条件的查询:
=
select*fromempwherejob='CLERK';
!
=
select*fromempwherejob!
='CLERK';
>
select*fromempwheresal>1600;
<
select*fromempwheresal<1600;
>=
select*fromempwheresal>=1600;
<=
select*fromempwheresal<=1600;
in
select*fromempwheresalin(1600,3000);
notin
select*fromempwheresalnotin(1600,3000);
betweenand
select*fromempwheresalbetween1600and3000;
Like
select*fromempwherejoblike'M%';
select*fromempwherejoblike'M_';
select*fromempwherejoblike'MANAGE_';
notlike
elect*fromempwherejobnotlike'M_';
select*fromempwherejobnotlike'M%';
Isnull
select*fromempwheresalisnull;
select*fromempwherejobisnull;
Isnotnull
select*fromempwheresalisnotnull;
select*fromempwherejobisnotnull;
组合条件的查询:
and
select*fromempwherejob='CLERK'andsal<3000;
or
elect*fromempwherejob='CLERK'orsal<3000;
not
select*fromempwherenotjob='CLERK';
排序查询:
select*fromemporderbyjobasc,saldesc;
分组查询:
groupbyhaving
selectempno,ename,job,salfromempgroupbyjob,empno,ename,salhavingsal<=2000;
wheregroupby
selectempno,ename,job,salfromempwheresal<=2000groupbyjob,empno,ename,sal;
字段运算查询:
+
selectename,job,mgr+salfromemp;
-
selectename,job,mgr-salfromemp;
*
selectename,job,mgr*salfromemp;
/
selectename,job,mgr/salfromemp;
变换查询显示:
selectempno编号,job工作,sal薪水fromemp;
用SQL进行多表查询
无条件多表查询:
产生笛卡尔积
selecta.empno,a.ename,a.sal,b.deptno,b.dname,b.locfromempa,deptb;
等值多表查询
selecta.empno,a.ename,a.sal,b.deptno,b.dname,b.locfromempa,deptbwherea.deptno=b.deptno;
非等值多表查询
selecta.empno,a.ename,a.sal,b.deptno,b.dname,b.locfromempa,deptbwherea.deptno!
=b.deptnoanda.deptno=10;
用SQL进行嵌套查询
简单嵌套查询
select*fromempwheresal<=(selectsalfromempwhereename='SMITH');
select*fromempwheresal<(selectsalfromempwhereename='SMITH');
select*fromempwheresal>=(selectsalfromempwhereename='SMITH');
select*fromempwheresal>(selectsalfromempwhereename='SMITH');
带【in】的嵌套查询
select*fromempwheresalIN(selectsalfromempwhereename='WARD');
select*fromempwheresalnotIN(selectsalfromempwhereename='WARD');
带【any】的嵌套查询
select*fromempwheresal>any(selectsalfromempwherejob='MANAGER');
带【some】的嵌套查询
select*fromempwheresal=some(selectsalfromempwherejob='MANAGER');
带【all】的嵌套查询
select*fromempwheresal>all(selectsalfromempwherejob='MANAGER');
用SQL进行函数查询
【avg】函数
selectavg(mgr)平均薪水fromemp;
【count】函数
selectcount(*)总记录数fromemp;
【min】函数
selectmin(mgr)最低薪水fromemp;
【max】函数
selectmax(mgr)最高薪水fromemp;
【sum】函数
selectcount(*)总记录数fromemp;
用SQL录入数据
单行记录的录入
insertintouser_list(user_name,userno)values('gouchao',10001);
insertintouser_listvalues('xuyongchao',10003);
insertintouser_listvalues('gouchao',S_userno.Nextval);
多行记录的录入
insertintoemp(empno,ename,hiredate)
(selectempno+100,ename,hiredatefromempwhereempno>=6999);
表间数据复制
createtabletest
as
(
selectdistinctempno,ename,hiredatefromempwhereempno>=7000
);
用SQL删除数据
删除记录
deletefromuser_listwhereuserno=10001;
整表数据删除
deletefromuser_list;
truncatetabletest;
主键:
altertableuser_list
addconstraintpk_user_no
primarykey(userNo);
外键:
altertablecard_list
addconstraintfk_user_no
foreignkey(userno)
referencesuser_list(userno);
索引:
创建索引:
createindexindex_user_name
onuser_list(username);
删除索引:
dropindexindex_user_name;
视图:
查看视图:
select*fromuser_views;
创建视图:
createorreplaceviewuser_list_view
asselectuserno,usernamefromuser_list;
删除视图:
dropviewuser_list_view;
函数:
Pl/sql:
基本语法:
setServerOutputon;
declare
bookNamevarchar2(40);
begin
bookName:
='gouchao';
dbms_output.put_line(bookName);
end;
/
条件语句if
setServerOutputon;
declare
numinteger:
=-11;
begin
ifnum<0then
dbms_output.put_line('负数');
elsifnum>0then
dbms_output.put_line('正数');
else
dbms_output.put_line('0');
endif;
end;
/
分支语句case:
setServerOutputon;
declare
numinteger:
=3;
resultvarchar2(30);
begin
result:
=casenum
when1then'星期一'
when2then'星期二'
when3then'星期三'
when4then'星期四'
when5then'星期五'
when6then'星期六'
when7then'星期天'
else'数据越界'
end;
dbms_output.put_line(result);
end;
/
循环语句loop...next...end
循环语句loop...exitwhen...endloop
setServerOutputon;
declare
numinteger:
=10;
begin
loop
dbms_output.put_line(num);
num:
=num-1;
exitwhennum<=0;
endloop;
end;
/
循环语句while...loop...endloop
setServerOutputon;
declare
numinteger:
=10;
begin
while(num>0)
loop
dbms_output.put_line(num);
num:
=num-1;
endloop;
end;
/
循环语句for...in...loop...endloop
setServerOutputon;
declare
iinteger;
begin
foriin1..10
loop
dbms_output.put_line(i);
endloop;
end;
/
setServerOutputon;
declare
iinteger;
begin
foriin1..10
loop
if(i=5)then
exit;
endif;
dbms_output.put_line(i);
endloop;
end;
/
函数:
存储过程:
不带参数:
createorreplaceprocedureout_line
is
begin
dbms_output.put_line(systimestamp);
dbms_output.put_line('helloWorld');
end;
/
setServeroutputon;
execout_line;
带输入参数:
createorreplaceproceduredemo2(dnonumber,dnamevarchar2,locvarchar2)
is
begin
insertintodeptvalues(dno,dname,loc);
end;
/
execdemo2(70,'gouchao','gouchao');
commit;
select*fromdept;
带输出参数1
createorreplaceproceduredemo3(enonumber,nameoutvarchar2,salaryoutnumber)
is
begin
selectename,salintoname,salaryfromempwhereempno=eno;
exception
whenno_data_foundthen
dbms_output.put_line('该记录不存在');
end;
/
setserveroutputon;
varnamevarchar2(10);
varsalarynumber;
execdemo3(7788,:
name,:
salary);
printnamesalary;
带输出参数2
createorreplaceproceduredemo4(inumber,jnumber,sumoutnumber)
is
begin
sum:
=i+j;
end;
/
varresultnumber;
execdemo4(1,2,:
result);
printresult;
删除存储过程:
游标:
游标:
游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数
据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数
据进行各种操作,然后将操作结果写回数据表中。
游标作为一种数据类型,首先必须进行定义,其语法如下
cursor游标名isselect语句;
cursor是定义游标的关键词,select是建立游标的数据表查询命令。
setserveroutputon
declare
tempsalscott.emp.sal%type;
cursormycursoris
select*fromscott.emp
wheresal>tempsal;
cursorrecordmycursor%rowtype;
begin
tempsal:
=800;
openmycursor;
fetchmycursorintocursorrecord;
dbms_output.put_line(to_char(cursorrecord.deptno));
end;
/
触发器:
触发器是特殊的存储过程,它当指定的表中的数据发生变化时自动运行:
触发器的执行是由事件触发的,而普通存储过程是由命令调用运行的
触发器的组成:
触发事件,触发条件,触发操作
触发事件:
Insert
Update
delete
出发时间:
Before:
在指定的事件发生之前执行触发器
After:
在指定的事件发生之前后执行触发器
触发级别:
行触发:
对触发事件影响的每一行执行触发器
语句触发:
对于触发事件只能触发一次,而且不能访问受触发器影响的每一行的值。
需求:
表emp:
表emp_log:
要求:
对雇员表的操作
对日志表的操作
新增员工记录
把新员工记录同时插入到日志表中,把员工状态改为“入职”
修改员工的工资
判断日志表是否有该员工记录,若存在,比较新旧工资,把员工状态对应改为“加薪”或“减薪“,更新工资;若不存在,插入新记录,状态改为”调薪”。
员工辞职,删除该员工记录
判断日志表是否有该员工记录,若存在,把员工状态改为“辞职”;若不存在,插入该条记录,把员工状态改为“辞职。
建表:
droptableEMPcascadeconstraint;
droptableemp_logcascadeconstraint;
prompt=====创建表(EMP)=
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 笔记