oracle马士兵笔记Word文档下载推荐.docx
《oracle马士兵笔记Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle马士兵笔记Word文档下载推荐.docx（21页珍藏版）》请在冰点文库上搜索。
从表中选出数据
1、descemp：
描述表emp（雇员）；
descdept：
描述表dept（部门）；
descsalgrade：
描述salgrade（薪水）；
.select*fromsalgrade;
标的内容全都取出来
.selectenamesal*12annual_salfromemp;
计算年薪，并命名为annual_sal。
.selectdistinctdeptnofromemp;
deptno部门编号。
删除重复的，修饰一个字段
.selectdistinctdeptno,jobfromemp;
删除重复的组合，修饰多个字段
等值判断：
where过滤条件，会过滤一些相关记录。
.select*fromempwheredeptno=10;
取出部门编号为10的
.select*fromempwhereename=‘CLARK’;
取出姓名为CLARK的记录。
.selectename,salfromempwheresal>
1500;
取出薪水值大于1500的记录。
.selectename,salfromempwheredeptno<
>
10;
取出部门编号不等于10的记录。
.selectename,sal,deptnofromempwheredeptno<
过滤掉部门编号等于10的
.selectename,salfromempwhereename>
CBA;
取出姓名大于CBA的记录。
.selectename,salfromempwheresalbetween800and1500;
.selectename,salfromempwheresal>
=800andsal<
=1500;
取出工资在800到1500的员工记录
.selectename,sal,commfromempwherecommisnull;
取出comm为空的
.selectename,sal,commfromempwherecommisnotnull;
.selectename,sal,commfromempwheresalin（800,1500,2000）;
或者
.selectename,sal,commfromempwhereenamelin（‘A’,‘B’,’C’）;
.selectename,sal,hiredatefromempwherehiredate>
‘20-2月-81’;
81年2月20号之后
.selectename,salfromempwheredeptno=10andsal>
1000;
.selectename,salfromempwheredeptno=10orsal>
.selectename,salfromempwheresalnotin（800,1500）;
.selectsysdatefromemp;
取出日期
.selectenamefromempwhereenamelike‘%ALL%’;
.selectenamefromempwhereenamelike‘_A%’;
取出第二个字母是A的姓名。
.selectenamefromempwhereenamelike‘%$%%’escape‘$’;
$是转义字符。
数据排序：
升序可以不写，默认的是升序；
降序要写明。
.select*fromdept;
按建表时的顺序排
.select*fromdeptorderbydeptnodesc;
按降序排
.selectempno,enamefromemp;
排列无序
.selectempno,enamefromemporderbyempnoasc;
按升序排
.selectempno,enamefromempwheredeptno<
10orderbyempnoasc;
过滤之后升序
.selectename,sal,deptnofromemporderbydeptnoasc;
按deptno生序排列。
.selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;
deptno升序，deptno相同的情况下再按照enamel降序排列。
1.计算数据可以用空表：
比如：
.select2*3fromdual
2.selectename,sal*12annual_salfromemp;
与selectename,sal*12"
annualsal"
fromemp;
区别，加双引号保持原大小写。
不加全变大写。
3.selectename||"
abcd"
如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：
distinct
selectdeptnofromemp;
selectdistinctdeptnofromemp;
selectdistinctdeptno,jobfromemp
去掉deptno,job两者组合的重复。
更多的项，就是这么多项的组合的不重复组合。
第六课：
Where
select*fromempwheredeptno=10;
select*fromempwheredeptno<
10;
不等于10
select*fromempwhereename='
bike'
;
selectename,salfromempwheresalbetween800and1500（>
=800and<
=1500）
空值处理:
selectename,sal,commfromempwherecommis（not）null;
selectename,sal,commfromempwhereename（not）in（'
smith'
'
king'
abc'
selectenamefromempwhereenamelike'
_A%'
_代表一个字母,%代表0个或多个字母.如果查询%
可用转义字符.\%.还可以用escape'
$'
比如:
selectenamefromempwhereenamelike'
%$a%'
escape'
第七课:
orderby
select*fromdept;
select*fromdeptorderbydeptdesc;
（默认:
asc）
selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;
第八课:
sqlfunction1:
selectename,sal*12annual_salfromemp
whereenamenotlike'
andsal>
800
orderbysaldesc;
selectlower（ename）fromemp;
selectenamefromempwherelower（ename）like'
_a%'
等同于
orenamelike'
selectsubstr（ename,2,3）fromemp;
从第二字符截,一共截三个字符.
selectchr（65）fromdual;
把数字转换成A
selectascii（'
A'
）fromdual；
结果为:
65
round四舍五入：
selectround（23.652）fromdual;
24
selectround（23.652,2）fromdual;
23.65
selectround（23.652,1）fromdual;
23.7
selectround（23.652,-1）fromdual;
20
to_char把数字或者日期转换成某种格式
selectto_char（sal,'
$99_999_999'
）fromemp;
selectto_char（sal,'
L99_999_999'
人民币符号,L:
代表本地符号
selectto_char（sal,’L00000.0000’）fromemp;
如果某一位上没有数字，则强制转换为0
这个需要掌握牢:
转换日期格式
.selecthiredatefromemp;
.selectto_char（hiredate,’YYYY-MM-DDHH:
MI:
SS’）fromemp;
selectbirthdatefromemp;
显示为:
BIRTHDATE
----------------
17-12月-80
改为:
selectto_char（birthdate,'
YYYY-MM-DDHH:
SS'
显示:
-------------------
1980-12-1712:
00:
00
.sysdate当前时间
selectto_char（sysdate,'
YYYY-MM-DDHH24:
）fromdual;
//也可以改为:
HH12
TO_CHAR（SYSDATE,'
YY
2007-02-2514:
46:
14
selectto_char（sysdate,'
YYYY-MM-DDHH12:
TO_CHAR（SYSDATE,'
2007-02-2502:
to_date函数:
selectename,birthdatefromempwherebirthdate>
to_date（'
1981-2-2012:
34:
56'
如果直接写birthdate>
'
会出现格式不匹配,因为表中的格式为:
DD-MM月-YY.
selectsalfromempwheresal>
888.88;
无错.但
$1,250,00;
会出现无效字符错误.
to_number（'
$1.250.00'
$9,999,99'
把空值改为0
selectename,sal*12+nvl（comm,0）fromemp;
nvl（comm.,0）如果comm是空值改为0。
这样可以防止comm为空时,sal*12相加也为空的情况.
第九课:
Groupfunction组函数重点重点★★★★★
max,min,avg,count,sum函数
selectmax（sal）fromemp;
selectmin（sal）fromemp;
selectavg（sal）fromemp;
平均值
selectsum（sal）fromemp;
总和
selectto_char（avg（sal）,'
99999999,99'
selectround（avg（sal）,2）fromemp;
结果:
2073.21
.selectcount（*）fromemp;
求出emp中一共有多少条记录
selectcount（*）fromempwheredeptno=10;
求出部门10里有多少人
selectcount（ename）fromempwheredeptno=10;
count某个字段,如果这个字段不为空就算一个。
.selectcount（deptno）fromemp;
selectcount（distinctdeptno）fromemp;
一共多少个部门编号
第十课:
Groupby语句：
Groupby按照什么分组:
★★★★★
需求:
现在想求,求每个部门的平均薪水.
selectavg（sal）fromempgroupbydeptno;
selectdeptnoavg（sal）fromempgroupbydeptno;
selectmax（sal）fromempgroupbydeptno,job;
selectdeptno,job,max（sal）fromempgroupbydeptno,job;
按照两个或者多个字段分组
求薪水值最高的人的名字.
selectename,max（sal）fromemp;
出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
selectenamefromempwheresal=（selectmax（sal）fromemp）;
.selectdeptno,max（sal）fromempgroupbydeptno;
Groupby语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Groupby语句中.
第十一课:
Having对分组结果筛选
Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
selectavg（sal）,deptnofromempgroupbydeptno;
分组
selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>
2000;
分组之后筛选。
查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
select*fromempwheresal>
1200groupbydeptnohavingavg（sal）>
1500orderbyavg（sal）desc;
.selectavg（sal）
.fromemp
.wheresal>
1200
.groupbydeptno
.havingavg（sal）>
1500
.orderbyavg（sal）desc;
第十二课:
字查询
谁挣的钱最多（谁:
这个人的名字,钱最多）
select语句中嵌套select语句,可以在where,from后.
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
问那些人工资,在平均工资之上.
selectename,salfromempwheresal>
（selectavg（sal）fromemp）;
查找每个部门挣钱最多的那个人的名字.
selectename,deptnofromempwheresalin（selectmax（sal）fromenamegroupbydeptno）;
出错了单行子查询返回多个行查询会多值.
应该如下:
selectmax（sal）,deptnofromempgroupbydeptno;
当成一个表.
面试题：
求部门中哪些人的薪水最高：
selectename,salfromemp
join（selectmax（sal）max_sal,deptnofromempgroupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）;
求部门平均薪水的等级：
.selectdeptno,avg_sal,gradefrom（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t
.joinsalgradeson（t.avg_salbetweens.losalands.hisal）;
求部门平均的薪水等级：
.selectdeptno,avg（grade）from
（selectdeptno,ename,gradefromempjoinsalgradeson（emp.salbetweens.losalands.hisal））t
.groupbydeptno;
雇员中有哪些人是经理人：
.selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;
不准用组函数，求薪水的最高值（面试题）：
.selectdistinctsalfromempwheresalnotin
（selectdisticte1.salfromempe1joinempe2on（e1.sal<
e2.sal））;
求平均薪水最高的部门的部门编号:
.selectdeptno,avg_salfrom（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）whereavg_sal=
（selectmax（avg_sal）from（selectavg（sal）avg_sal,deptnofromempgroupbydeptno））;
等价写法（组函数嵌套）：
最多嵌套2层
（selectmax（avg（sal））fromempgroupbydeptno））;
求平均薪水最高的部门的部门名称：
.selectdnamefromdeptwheredeptno=
（selectdeptno,avg_salfrom（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）whereavg_sal=
（selectmax（avg_sal）from（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）））;
求平均薪水的等级最低的部门的部门名称：
.selectdname,t1.deptno,grade,avg_salfrom
selectdeptno,grade,avg_salfrom
（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t
.joinsalgradeson（t.avg_salbetweens.losalands.hisal）
）t1
.joindepton（t1.deptno=dept.deptno）
.wheret1.grade=
（
.selectmin（grade）from
（
.selectdeptno,grade,avg_salfrom
（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t
.joinsalgradeson（t.avg_salbetweens.losalands.hisal）
创建视图：
.connsys/bjsxtassysdba
授权：
.grantcreatetable,createviewtoscott;
授权成功。
.createviewv$_dept_avg_sal_infoas
.selectdeptno,grade,avg_salfrom
.joinsalgradeson（t.avg_salbetweens.losalands.hisal）;
视图已创建。
从视图里取数据：
.select*fromv$_dept_avg_sal_info;
视图本身就是一张表或者说是一个子查询，视图这张表叫虚表，实际数据依然存放在实际表。
用视图解决上题：
（先建一张虚表，再在虚表中取数据）
.selectdname,t1.deptno,grade,avg_salfromv$_dept_avg_sal_infot1
.joindepton（t1.de
升级会员 