下载尚学堂马士兵oracle笔记53节完整版.docx

资源描述

下载尚学堂马士兵oracle笔记53节完整版.docx

《下载尚学堂马士兵oracle笔记53节完整版.docx》由会员分享，可在线阅读，更多相关《下载尚学堂马士兵oracle笔记53节完整版.docx（33页珍藏版）》请在冰豆网上搜索。

下载尚学堂马士兵oracle笔记53节完整版.docx

下载尚学堂马士兵oracle笔记53节完整版

尚学堂马士兵老师oracle笔记（2008-10-3010:

17:

39）

第一课：

客户端

1.SqlPlus（客户端），命令行直接输入：

sqlplus，然后按提示输入用户名，密码。

2.从开始程序运行:

sqlplus，是图形版的sqlplus.

3.http:

//localhost:

5560/isqlplus

Toad：

管理，PlSqlDeveloper:

第二课：

更改用户（cmd）

1.sqlplussys/密码assysdba

2.alteruserscottaccountunlock;（解锁账号）

第三课：

tablestructure

（系统自带的表有emp、salgrade、dept、bonus、dual）

1.描述某一张表：

desc表名

2.select*from表名

第四课：

select语句：

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'）;

模糊查询like：

%代表任意数量的任意字符

_代表一个任意字符

selectenamefromempwhereenamelike'_A%';

如果要查询含有%的，要用转义字符\

转义字符可以自定义：

escape'自定义的转义字符'比如:

selectenamefromempwhereenamelike'%$a%'escape'$';

第七课:

orderby

select*fromdept;

默认按升序（asc）排列，要按降序（desc）用如下语句：

select*fromdeptorderbydeptnodesc;

selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;

第八课:

sqlfunction1:

selectename,sal*12annual_salfromemp

whereenamenotlike'_A%'andsal>800

orderbysaldesc;

selectlower（ename）fromemp;

selectenamefromemp

wherelower（ename）like'_a%';等同于

selectenamefromempwhereenamelike'_a%'orenamelike'_A%';

selectsubstr（ename,2,3）fromemp;从第二个字符开始截,一共截三个字符.

selectchr（65）fromdual结果为:

selectascii（'a'）fromdual结果为:

selectround（23.652,1）fromdual;结果为:

23.7（第二个参数为指定四舍五入到哪位数）

selectround（23.652,-1）fromdual;20

selectto_char（sal,'$99,999.999'）fromemp;（用9就可以在没有数字的地方不显示，如果用0的话一定会用0填充满）

selectto_char（sal,'L99,999.999'）fromemp;L:

代表本地符号

这个需要掌握牢:

selecthiredatefromemp;

显示为:

BIRTHDATE

----------------

17-12月-80

----------------

改为:

selectto_char（hiredate,'YYYY-MM-DDHH:

MI:

SS'）fromemp;

显示:

BIRTHDATE

-------------------

1980-12-1712:

00:

-------------------

selectto_char（sysdate,'YYYY-MM-DDHH24:

MI:

SS'）fromdual;//也可以改为:

HH12

TO_CHAR（SYSDATE,'YY

-------------------

2007-02-2514:

46:

to_date函数:

selectename,hiredatefromempwherehiredate>to_date（'1981-2-2012:

34:

56','YYYY-MM-DDHH24:

MI:

SS'）;

如果直接写birthdate>'1981-2-2012:

34:

56'会出现格式不匹配,因为表中的格式为:

DD-MM月-YY.

selectsalfromempwheresal>888.88无错.但

selectsalfromempwheresal>$1,250,00;

会出现无效字符错误.

改为:

selectsalfromempwheresal>to_number（'$1.250.00','$9,999,99'）;

把空值改为0

selectename,sal*12+nvl（comm,0）fromemp;

作用：

把comm为空的地方用0代替，这样可以防止comm为空时,sal*12相加也为空的情况.

第九课:

Groupfunction组函数（即从多行中得到一个输出）

牢记组函数：

max（）:

返回一个数字列或计算列的最大值

min（）:

返回一个数字列或计算列的最小值

avg（）:

返回一个数字列或计算列的平均值

sum（）:

返回一个数字列或计算列总和

count（）:

返回找到的记录数

selectto_char（avg（sal）,'99999999,99'）fromemp;

selectround（avg（sal）,2）fromemp;

结果:

2073.21

selectcount（*）fromempwheredeptno=10;

selectcount（ename）fromempwheredeptno=10;count某个字段,如果这个字段不为空就算一个.

selectcount（distinctdeptno）fromemp;

selectsum（sal）fromemp;

第十课:

Groupby语句

注意：

count（）是计数不是空值的数量

需求:

现在想求每个部门的平均薪水.

selectavg（sal）fromempgroupbydeptno;

selectdeptno,avg（sal）fromempgroupbydeptno;

selectdeptno,job,max（sal）fromempgroupbydeptno,job;

求薪水值最高的人的名字.

selectename,max（sal）fromemp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.

应如下求:

selectenamefromempwheresal=（selectmax（sal）fromemp）;

Groupby语句应注意,

出现在select中的字段,如果没出现在组函数中,必须出现在Groupby语句中.

第十一课:

Having对分组结果筛选

Where是对单条纪录进行筛选,Having是对分组结果进行筛选.

selectavg（sal）,deptnofromemp

groupbydeptno

havingavg（sal）>2000;

查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.

selectavg（sal）fromemp

wheresal>1200

groupbydeptno

havingavg（sal）>1500

orderbyavg（sal）desc;

第十二课:

子查询

谁挣的钱最多（谁:

这个人的名字, 钱最多）

select语句中嵌套select语句,可以在where,from后.

问那些人工资,在平均工资之上.

selectename,salfromempwheresal>（selectavg（sal）fromemp）;

查找每个部门挣钱最多的那个人的名字.

selectename,deptnofromempwheresalin（selectmax（sal）fromempgroupbydeptno）查询会多值.

应该如下:

把select max（sal）,deptnofromempgroupbydeptno;当成一个表.语句如下:

selectename,salfromempjoin（select max（sal）max_sal,deptnofromempgroup

bydeptno）ton（emp.sal=t.max_salandemp.deptno=t.deptno）;

每个部门的平均薪水的等级.

分析:

首先求平均薪水（当成表）,把平均薪水和另外一张表连接.

第十四课:

self_table_connection（自连接）

把某个人的名字以及他的经理人的名字求出来（经理人及这个人在表中同处一行）

分析:

首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.

selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno.

empno编号和MGR都是编号.

第十15课:

SQL1999_table_connections

selectename,dname,gradefromempe,deptd,salgrades

wheree.deptno=d.deptnoande.salbetweens.losalands.hisaland

job<>'CLERK'

有没有办法把过滤条件和连接条件分开来?

出于这样考虑,Sql1999标准推出来了.有许多人用的还是

旧的语法,所以得看懂这种语句.

selectename,dnamefromemp,dept;（旧标准）.

selectename,dnamefromempcrossjoindept;（1999标准）

selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno（旧）

selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;1999标准.没有Where语句.

selectename,dnamefromempjoindeptusing（deptno）;等同上句,但不推荐使用.

selectename,gradefromempejoinsalgradeson（e.salbetweens.losalands.hisal）;

join连接语句,on过滤条件。

连接，条件一眼分开。

如果用Where语句较长时，连接语句和过滤语句混在一起。

三张表连接：

slectename,dname,gradefrom

empejoindeptdon（e.deptno=d.deptno）

joinsalgradeson（e.salbetweens.losalands.hisal）

whereenamenotlike'_A%';

把每张表连接条件不混在一起，然后数据过滤条件全部区分开来。

读起来更清晰，更容易懂一点。

selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.emptno）;

左外连接：

会把左边这张表多余数据显示出来。

selecte1.ename,e2,enamefromempe1leftjoinempe2on（e1.mgr=e2.empno）;left后可加outer

右外连接：

把右边这张表多余数据显示出来。

selectename,dnamefromemperightouterjoindeptdon（e.deptno=d.deptno）;outer可以取掉。

全外连接：

即把左边多余数据，也把右边多余数据拿出来，

selectename,dnamefromempefulljoindeptdon（e.deptno=d.deptno）;

PS：

所谓的“外”连接，即把多余的数据显示出来。

Outer关键字可以省略

16-23

求部门中哪些人的薪水最高：

selectename,salfromemp

join（selectmax（sal）max_sal,deptnofromempgroupbydeptno）t

on（emp.sal=t.max_salandemp.deptno=t.deptno）

A.求部门平均薪水的等级。

selectdeptno,avg_sal,gradefrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

joinsalgradeson（t.avg_salbetweens.losalands.hisal）

B.求部门平均薪水的等级

selectdeptno,avg（grade）from

（selectdeptno,ename,gradefromempjoinsalgradeson

（emp.salbetweens.losalands.hisal））t

groupbydeptno

C.哪些人是经理

selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;

更高效率的写法：

selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;

D.不准用组函数，求薪水的最高值（面试题）

selectdistinctsalfromempwheresalnotin（

selectdistincte1.salfromempe1joinempe2on（e1.sal

E.平均薪水最高的部门编号

selectdeptno,avg_salfrom