oracle笔记Word下载.docx
《oracle笔记Word下载.docx》由会员分享，可在线阅读，更多相关《oracle笔记Word下载.docx（37页珍藏版）》请在冰点文库上搜索。
<
>
10//表示不等于10
（1）selectename,sal,deptnofromempwheresalbetween800and1500;
//筛选工资为800到1500，包括800和1500
（2）selectename,sal,deptnofromempwheresal>
=800andsal<
//与上面等同
Selectename,sal,commFromempwherecommisnull;
//找出comm为空的记录。
Selectename,sal,commfromempwherecommisnotnull;
//找到不为空的记录。
Selectename,salfromempwheresalin（800,1500,2000）;
//找到工资为800或者1500，或者2000的数据
Selectename,sal,hiredatefromempwherehiredate>
‘20-2月-81’;
//选出入职日期为81年2月20日的数据，注意日期格式。
Selectename,sal,deptnofromempwheresal>
1500anddeptno=10;
//两个条件都符合的数据
Selectename,sal,deptnofromempwheresal>
1500ordeptno=10;
//两个条件之中的一个成立即可
Selectename,sal,deptnofromempwheresalnotin（800,1000）;
//不是800和1000的数据
Selectename,sal,hiredatefromempwhereenamelike‘%ALL%’;
//找出名字含有ALL的名字，%表示一个或多个的字母。
Selectename,salhiredatefromempwhereenamelike‘A%’;
//找出名字第二个字母为A的名字，代表一个字母
如果名字里面有%号，那可用转义字符找出含有%的名字，如下语句：
Selectename,salfromempwhereenamelike‘%\%%’;
或者自己指定转义字符如下：
Selectename,salfromempwhereenamelike‘%$%%’escape‘$’;
//指定转义字符为$
Select*fromdeptorderbydeptnodesc;
//对deptno进行降序排列
默认为升序，也可以用deptnoase表示升序
Selectempno,enamefromempwheredeptno<
10orderbyempnoase;
//先过虑deptno不等于10的empno，再升序排列
Selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;
//两个字段进行排列，先deptno升序排列，然后再deptno相同的情况下，对ename进行倒序排列
单行函数
Selectlower（ename）fromemp;
//函数lower（ename）将名字都变成小写
upper（ename）//全部转化为大写
Selectsubstr（ename,1,3）fromemp;
//单行函数，截字符串子串，从第1个字母开始截，截3个子符
Selectchr（65）fromdual;
//把数字65转换成编码
Selectascii（'
A'
）fromdual;
//把编码A转换成数字
Selectround（25.32）fromdual;
//四舍五入，结果为25
Selectround（25.342,2）//四舍五入到小数点后两位，结果25.34
Selectround（25.325,-1）//四舍五入十位，结果为20
Selectto_char（sal,'
$99,999.9999'
）fromemp;
//按照一定的格式显示sal,9代表一个数子，如果sal的位数不符合格式的要求，前面缺少的不显示，后面没有的，用0表示，如800，显示为：
800.0000
Selectto_char（sal,'
L000.0000'
）fromemp;
//将sal转换为特定格式，如果不符合，前面缺少的和后面没有的都加0，L代表本地货币¥
Selectto_char（sysdate,'
YYYY--MM--DDHH:
MI:
SS'
//
显示系统的时间，按特定格式。
HH为12小时制，如果要改成24小时制，则只需把HH改成HH24
Selectename,hiredatefromempwherehiredate>
to_date（'
1981-2-2012:
362:
56'
'
YYYY-MM-DDHH24:
）;
//将1981-2-2012:
56之后的数据按照一定的格式找出来，但找出来的仍为原表的格式
Selectename,salfromempwheresal>
to_number（'
$1,250.00'
$9,999.99'
//把特定格式的数字转化为字符串，这里为大于1250的数字
对于含有空值的计算：
Selectename,sal*12+commnvl（comm,0）fromemp;
//如果comm为空值，则用0代替，如果comm不为空值，则还是comm
组函数（很多条记录作为输入，但只有一个输出）
Selectmax（sal）fromemp;
//工资最高者
Selectmin（sal）fromemp;
//工资最低者
Selectavg（sal）fromemp;
//平均工资
Selectto_char（avg（sal）,'
99999.99'
//对平均工资取小数点后两位
Selectsum（sal）fromemp;
//总共要支出的工资
Selectcount（*）fromemp;
//表中总共有多少条记录
Selectcount（*）fromempwheredeptno=10;
//deptno为10的记录数
如果count（字段），则计算出来的总数为不为null值的数,null值的不计算在内
Selectcount（dinstinctdeptno）fromemp;
//总共有多少个唯一的deptno
Selectdeptno,avg（sal）fromempgroupbydeptno;
//计算每一个部门的平均工资
Selectmax（sal）fromempgroupbydeptno,job;
//根据两个字段的组合时行分组，两个人的deptno和job都是一样的
子查询（在select语句中套select语句）：
Selectenamfromempwheresal=（selectmax（sal）fromemp）;
Selectename,max（sal）fromempgroupbydeptno;
//不能这样，因为组函数为多个输入，只有一个输出,根据deptno分组后，每一个dept里面工资最高的ename就不是唯一确定的了。
Selectdeptno,max（sal）fromempgroupbydeptno;
//这样就可以了，根据deptno分组后，每一个dept都是唯一确定的
Where语句只是对单条记录进行过滤
Selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>
2000//选择分组后部门平均工资高于2000的记录，不能用where因为where只是针对单条记录，分组后不能用where过滤，
Having,对分组进行限制
单条select语句的执行顺序
（1）selectXXXfromemp//取数据
（2）WhereXXX//过滤数据
（3）GroupbyXXX//对数据进行分组
（4）Having//过滤组
（5）OrderbyXXX//对数据进行排序
Selectename,salfromempjoin（selectdeptno,max（sal）max_salfromempgroupbydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;
//连接到另一张表，t为表的名称
自连接：
同一张表起两个别名：
Selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno
Oracle新语法1999
两表连接：
Selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）//join为连接，on为连接条件，dept表名
Selectename,dnamefromempjoindeptusing（deptno）;
//为上面作用一样，但不建议用
Selectename,gradefromempejoinsalgradeson（e.salbetweens.losalands.hisal）;
三表连接：
selectename,dname,gradefromempejoindeptdon（e.deptno=d.deptno）joinsalgradeson（e.salbetweens.losalands.hisal）whereenamenotlike'
_A%'
;
//ename,dname,grade分别为emp,dept,salgrade表中的子段
左外连接:
Selecte1.ename,e2.enamefromempe1leftjoinempe2on（e1.deptno=e2.deptno）;
//会将左边表e1有没连接的多余的显示出来
右外连接也是一样的道理。
Selectename,dnamefromemprightjoindepton（emp.deptno=dept.deptno）;
全连接：
两边多余的都显示
Selectename,dnamefromempfulljoindepton（emp.deptno=dept.deptno）;
求部门中哪些人的薪水最高：
Selectename,salfromempjoin（selectdeptno,max（sal）max_salfromempgroupbydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;
求部门中平均工资的等级：
Selectename,avg_max,gradefrom（
selectdetpno,avg（sal）avg_salfromempgroupbydeptno）t
joinsalgradeson
（t.avg_salbetweens.losalands.hisal）;
//注意avg_max是另一表的字段名avg（sal）avg_sal
求部门的平均工资等级：
Selectdeptno,avg（grade）from（selectdeptno,gradefromempjoinsalgradeson（emp.salbetweens.losalands.hisal））tgroupbydeptno;
//从另一张表上取数据
雇员中有哪些人是经理人：
Selectenamefromempwhereempnoin（selectdistinctmgrformemp）;
不用组函数，求薪水的最高值：
考虑：
将emp当成两张表，e1表中的薪水小于e2表的薪水，这样连接的时候，只会有e1表中的薪水的最大值连接不上，因为在e2表中找不到比它在大的值。
这时再用notin把最大值求出来，如下：
Selectdistinctsalfromempwheresalnotin（selecte1.salfromempe1joinempe2on（e1.sal<
e2.sal））;
求部门平均工资最高的编号：
（1）先求部门的平均工资，如下：
Selectavg（sal）,deptnofromempgroupbydeptno;
（2）将
（1）中看成一张表，从表中取最大值，如下：
Selectmax（sal）from（selectavg（sal）,deptnofromempgroupbydeptno）;
（3）将
（2）中得到的最大值作为一个数字，即过滤条件，另起别名，如下：
Selectavg_sal,deptnofrom（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）whereavg_sal=（selectmax（avg_sal）from（selectavg（sal）avg_sal,deptnofromempgroupbydeptno））;
求平均工资最高部门的名称：
由上面求出平均工资最高的部门编号，由此可以由这个编号找到部门名称，如下：
Selectdnamefromdeptwheredeptno=（
Selectdeptnofrom
（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）whereavg_sal=（selectmax（avg_sal）from（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）
）
组函数的嵌套：
（最多两层）
如上求部门平均工资最高的编号时：
（2）中的
Selectmax（sal）from（
selectavg（sal）,deptnofromempgroupbydeptno）可改为
selectmax（avg（sal））fromempgroupbydeptno;
求平均薪水等级最低的部门的名称：
（1）先求出部门的平均工资等级，如下：
Selectdeptno,avg_sal,gradefrom（
selectdeptno,avg（sal）avg_salfromempgroupbydeptno）
（avg_salbetweens.losalands.hisal）
（2）求出部门平均工资的最低等级：
Selectmin（grade）from（
selectdeptno,avg_sal,gradefrom（
selectdeptno,avg（sal）avg_salfromempgroupbydeptno）join
salgradeson
（avg_salbetweens.losalands.hisal））
（3）将
（1）看成一张表与dept表连接，注意连接条件,
此时将
（2）中求出的最低等级作为一个连接条件：
Selectdname,avg_sal,grade,t1.deptnofrom（
selectavg_sal,grade,deptnofrom（
selectavg_sal,deptnofromempgroupbydeptno）tl
joindepton
（t1.deptno=dept.deptno）
wheret1.grade=
（selectmin（grade）fromselectdeptno,avg_sal,gradefrom（
selectavg（sal）avg_sal,deptnofromempgroupbydeptno
）
（avg_salbetweens.losalands.hisal））;
注意是t1.deptno,因为是从t1表中抽出来的
创建视图（虚表）：
首先以超级管理员身份连接：
Connsys/lcmassysdba;
授权：
grantcreatetable,createviewtoscott;
再连接：
connscott/tiger
下面开始创建视图：
Createviewv$_dept_avg_infoas
Selectdeptno,avg_sal,gradefrom（
selectdeptno,avg_salfromempgroupbydeptno）tjoin
//创建视图一般以：
Createviewv$名字as.......这种形式开始
实际数据不是存在视图里面的
求比普通员工最高工资还高的经理人名称
（1）先求普通员工的最高工资，如下：
Selectmax（sal）fromempwhereempnonotin（selectdistinctmgrfromempwheremgrisnotnull）//消除最高级的那个没有mgr的情况
（2）工资还高的经理人名称
Selectenamefrmoemp
whereempnoin（
selectdistinctmgrfromempwheremgrisnotnull）
andsal>
（selectmax（sal）fromemp
whereempnonotin
（selectmgrfromempwheremgrisnotnull）
工资最高的前五个人的名字：
Selectename,salfrom（
selectename,salfromemporderbysaldesc）whererownum<
=5;
求薪水最高的第6名到第10名的雇员：
Selectename,sal,rownumrfrom（
Selectename,salfromemporderbysaldesc
）
）wherer>
=6andr<
=10;
DML语句
插入语句：
Insertintodeptvalues（40,'
myname'
'
beijing'
//insertinto,values关键字，括号为表dept的字段名，要对应
Rollback//恢复到原来的表
备份表：
Createtableemp2asselect*fromemp;
//创建表emp2和emp相同
Insert语句的三种形式：
（1）insertintodept2values（40,'
//只插入deptno,dname，第三个字段会默认为空
（2）insertintodept2（deptno,dname）values（50,'
//插入dept中的所有行，注意此时没有value
（3）insertintodpet2select*fromdept;
Rownum（伪字段）
在oracle中，rownum只能为rownum<
某位数，或rownum<
=某位数，而不能为其它的！
如不能：
selectename,empnofromempwhererownum>
10或=10或>
=10
如果想取10以后的数，解决办法：
创建别一表：
selectrownumr,enamefromemp;
再进行子查询：
Selectename,from（selectrownumr,enamefromemp）wherer>
更新语句：
update
Updatedept2setsal=sal*2,ename=ename||'
dd'
wheredeptno=10
删除语句：
delete
Deletefromdept2
//删除表dept2
创建表时，删除时可用：
Droptable表名
事务（Transaction）
事务:
要么同进完成，要么同进不完成
一个transaction起始于一
升级会员 