玩转oracle实战教程个人笔记Word文档下载推荐.docx
《玩转oracle实战教程个人笔记Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《玩转oracle实战教程个人笔记Word文档下载推荐.docx（32页珍藏版）》请在冰点文库上搜索。
Dropprofilelockmacascade;
清屏操作：
clear
Disc[onnect]：
断开与当前数据库的连接
Passw[ord]：
修改密码，如果要修改其他用户密码，需要用sys/system登录
Showuser：
查看当前连接用户
Exit：
断开与数据库连接，同时会退出sql*plus
Start和@：
运行sql脚本，如：
@d:
\a.sql或者startd:
\a.sql
Edit：
编辑指定的脚本如：
editd:
Spool：
可以将sql*plus屏幕上的内容输出到知道的文件中去，
如：
sql>
spoold:
\b.sql;
然后所有的操作及结果都会输出到文件，最后输入sql>
spooloff结束
Oracle表的管理
Oracle表名和列的命名规则：
必须以字母开头
长度不能超过30个字符
不能使用oracle的保留字
只能使用如下字符A-Z，a-z，0-9，$，#等
Oracle支持的数据类型：
字符型
char：
定长，最大2000字符，对于长度变化不大的变量，查询速度很快。
如身份证id。
varchar2：
变长，最大4000字符。
节省空间。
clob（characterlargeobject）：
字符串大对象，最大4G
数字型
number可以表示整数也可以表示小数
number（5,2）表示有5位有效数字，2位小数，-999.99~999.99
number（5）表示一个5位整数，-99999~99999
日期型
date包括年月日和时分秒
timestamporacle对date数据类型的扩展
图片
blob二进制数据，可以存放图片/声音4G
创建表：
DDL数据描述语言，包括createdropalter
DML数据操作语言，包括selectinsertdeleteupdate
DCL数据控制语言，包括grantrevoke
createtablestudent（
2nonumber（10）,
3namevarchar2（10）,
4sexchar
（2）,
5birthdaydate,
6salnumber（7,2）
7）;
添加一个字段：
altertablestudentaddclassidnumber
（2）;
修改字段长度：
altertablestudentmodifynamevarchar（30）;
修改字段类型或字段名字（不能有数据）：
altertablestudentmodifynamechar（20）;
删除一个字段：
altertablestudentdropcolumnsal;
修改表的名字：
renamestudenttostud;
删除表：
droptablestudent;
插入数据：
insertintostudvalues（1,'
马杰'
'
男'
16-11月-1988'
12）;
其中日期的格式，oracle默认的是：
’dd-mon-yy’，而且月份必须有’月’字。
可以修改oracle的默认值：
altersessionsetnls_date_format='
yyyy-mm-dd'
;
然后再插入数据：
insertintostudvalues（2,'
张三'
1988-11-1'
SQL>
select*fromstud;
NONAMESEXBIRTHDAYCLASSID
----------------------------------------------
1马杰男1988-11-1612
2张三男1988-11-112
修改no字段非空：
altertablestudmodifynonotnull;
altertablestudmodifynamenotnull;
altertablestudmodifysexnotnull;
altertablestudmodifyclassidnotnull;
insertintostud（no,name,sex,birthday,classid）values（3,'
李四'
null,10）;
查询空的记录：
select*fromstudwherebirthdayisnull;
select*fromstudwherebirthdayisnull;
3李四男10
修改多列：
updatestudsetname='
李斯'
sex='
女'
birthday='
1988-11-05'
whereno='
3'
select*fromstudwhereno='
3李斯女1988-11-510
还原点的作用，在还原点里进行的操作可以rollback回去：
savepointbb;
savepointbb;
Savepointcreated
deletefromstud;
3rowsdeleted
NONAMESEXBIRTHDAYCLASSIDSAL
------------------------------------------------------
rollbacktobb;
Rollbackcomplete
SQL>
1马杰男1988-11-1612100.00
2张三男1988-11-112200.00
3李四男10300.00
关于多个还原点：
savepointa1;
Savepointcreated
updatestudsetsal=110whereno=1;
1rowupdated
savepointa2;
updatestudsetsal=220whereno=2;
1马杰男1988-11-1612110.00
2张三男1988-11-112220.00
rollbacktoa1;
1马杰男1988-11-1612100.00
如果rollbacktoa2的话，就只有第二条记录会被还原。
如果还原点内任何位置进行了commit操作，则还原点被删除：
updatestudsetsal=110whereno=1;
commit;
Commitcomplete
rollbacktoa1
ORA-01086:
从未创建保存点'
A1'
1马杰男1988-11-1612110.00
特殊的删除表数据：
truncatetablestud;
删除表中的所有记录，表结构还在，删除操作不写日志，无法找回删除的记录，但是速度很快。
可以用于数据表非常庞大的时候进行删除。
Oracle表的查询
简单查询：
查询scott的emp表，计算年工资（包括奖金）：
selectsal*12+comm*12工资,enamefromemp;
结果为，没有奖金的员工就没有年工资了，这主要是由于comm存在null值的缘故。
故修改语句为：
selectsal*12+nvl（comm,0）*12工资,enamefromemp;
使用nvl（comm,0），如果comm为null，则让comm为0。
使用like关键字：
%代表任意0个或多个字符，_代表任意单个字符。
selectenamefromempwhereenamelike'
__O%'
ENAME
----------
SCOTT
S%'
SMITH
升序（asc）和降序（desc）：
Selectename,sal,deptnofromemporderbydeptno,saldesc;
可以按照字段别名排序：
selectename,sal*12+nvl（comm,0）*12年薪fromemporderby年薪;
复杂查询：
子查询
查询最高工资的员工的姓名和工资：
selectename,salfromempwheresalin（selectmax（sal）fromemp）;
不能写成：
selectename,salfromempwheresal=max（sal）;
selectename,max（sal）fromemp;
查询比平均工资高的员工的信息：
select*fromempwheresal>
（selectavg（sal）fromemp）;
查询每个部门每种岗位的平均工资：
selectavg（sal）,max（sal）,deptno,jobfromempgroupbydeptno,job;
selectavg（sal）,max（sal）,deptno,jobfromempgroupbydeptno,job;
AVG（SAL）MAX（SAL）DEPTNOJOB
-----------------------------------
950110020CLERK
1400160030SALESMAN
2975297520MANAGER
95095030CLERK
5000500010PRESIDENT
2850285030MANAGER
1300130010CLERK
2450245010MANAGER
3000300020ANALYST
9rowsselected
显示平均工资大于2000的的部门号和它的平均工资：
selectavg（sal）,max（sal）,deptnofromempgroupbydeptnohavingavg（sal）>
2000;
总结：
1、分组函数只能出现在选择列表、having、orderby子句中
2、如果select语句中同时包含有groupby、having、orderby，那么它们的顺序是groupby、having、orderby。
3、在选择列中，如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在groupby子句中，否则就会出错。
多表查询：
多表查询的条件是：
连接条件至少不能少于“表的个数-1”
查询部门为10的部门名称，员工姓名，员工工作：
selectd.dname,e.ename,e.salfromempe,deptdwheree.deptno=d.deptnoandd.deptno=10;
查询员工的姓名，工资，员工的工资级别：
selecte.ename,e.sal,s.gradefromempe,salgradeswheree.salbetweens.losalands.hisal;
自连接：
查询员工的领导编号和名字：
selecte1.enameworker,e1.mgrbossid,e2.enamebossfromempe1,empe2wheree1.mgr=e2.empno;
selecte1.enameworker,e1.mgrbossid,e2.enamebossfromempe1,empe2wheree1.mgr=e2.empno;
WORKERBOSSIDBOSS
--------------------------
FORD7566JONES
SCOTT7566JONES
JAMES7698BLAKE
TURNER7698BLAKE
MARTIN7698BLAKE
WARD7698BLAKE
ALLEN7698BLAKE
MILLER7782CLARK
ADAMS7788SCOTT
CLARK7839KING
BLAKE7839KING
JONES7839KING
SMITH7902FORD
单行子查询：
查询和SMITH一个部门的员工：
selecte.*,d.dnamefromempe,deptdwheree.deptno=（selectdeptnofromempwhereename='
SMITH'
）ande.deptno=d.deptno;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODNAME
-------------------------------------------------------
7369SMITHCLERK79021980-12-17800.0020RESEARCH
7566JONESMANAGER78391981-4-22975.0020RESEARCH
7788SCOTTANALYST75661987-4-193000.0020RESEARCH
7876ADAMSCLERK77881987-5-231100.0020RESEARCH
7902FORDANALYST75661981-12-33000.0020RESEARCH
多行子查询：
查询大于部门30的任意员工工资的员工信息：
selectename,sal,deptnofromempwheresal>
any（selectsalfromempwheredeptno=30）;
或者是：
（selectmin（sal）fromempwheredeptno=30）;
多列子查询：
查询和SMITH相同部门并且工作相同的员工信息：
select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename='
）;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
7369SMITHCLERK79021980-12-17800.0020
7876ADAMSCLERK77881987-5-231100.0020
查询高于自己部门平均工资的员工的信息：
第1步，查询各个部门的平均工资和部门号：
selectdeptno,avg（sal）mysalfromempgroupbydeptno;
DEPTNOMYSAL
----------------
301566.66666
202175
102916.66666
第2步，把上面的查询看做为一张子表temp：
selecte.ename,e.sal,e.deptno,temp.mysalfromempe,（selectdeptno,avg（sal）mysalfromempgroupbydeptno）tempwheree.deptno=temp.deptnoande.sal>
temp.mysal;
ENAMESALDEPTNOMYSAL
-----------------------------------
ALLEN1600.00301566.66666
JONES2975.00202175
BLAKE2850.00301566.66666
SCOTT3000.00202175
KING5000.00102916.66666
FORD3000.00202175
Oralce分页一个有三种方式，下面是rownum分页方式：
1．rownum分页
（select*fromemp）
2.显示rownum[oracle分配的]
selecta1.*,rownumrnfrom（select*fromemp）a1;
3．然后筛选rownum<
=10的：
selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<
=10;
下面这条语句会出错：
rownum只能使用一次，如果要再使用，必须嵌套：
=10andrownum>
=6;
嵌套如下：
select*from（selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<
=10）wherern>
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNORN
----------------------------------------------------------------
7698BLAKEMANAGER78391981-5-12850.00306
7782CLARKMANAGER78391981-6-92450.00107
7788
升级会员 