马老师oracle 学习日记.docx

马老师oracle 学习日记.docx

《马老师oracle 学习日记.docx》由会员分享，可在线阅读，更多相关《马老师oracle 学习日记.docx（72页珍藏版）》请在冰豆网上搜索。

马老师oracle学习日记

:

马老师的pq/sql学习笔记

--sqlstructuredquerylanguage

--DML--DataManipulationLanguage--数据操作语言

queryinformation（SELECT）,

addnewrows（INSERT）,

modifyexistingrows（UPDATE）,

deleteexistingrows（DELETE）,

performaconditionalupdateorinsertoperation（MERGE）,

seeanexecutionplanofSQL（EXPLAINPLAN）,

andlockatabletorestrictaccess（LOCKTABLE）.

--DDL--DataDefinitionLanguage--数据定义语言

create,modify,drop,orrenameobjects（CREATE,ALTER,DROP,RENAME）,

removeallrowsfromadatabaseobjectwithoutdroppingthestructure（TRUNCATE）,

manageaccessprivileges（GRANT,REVOKE）,

auditdatabaseuse（AUDIT,NOAUDIT）

andaddadescriptionaboutanobjecttothedictionary（COMMENT）.

--TransactionControl事务控制语句

savethechanges（COMMIT）

ordiscardthechanges（ROLLBACK）madebyDMLstatements.

Alsoincludedinthetransaction-controlstatementsarestatementstosetapointormarkerinthetransactionforpossiblerollback（SAVEPOINT）

andtodefinethepropertiesforthetransaction（SETTRANSACTION）.

Usedtomanagethepropertiesofthedatabase.

Thereisonlyonestatementinthiscategory（ALTERSYSTEM）.

--DCL--DataControLanguage--与开发关系不是很密切,用于权限的分配与回收

grant,revoke,datacontrol

--SessionControl

controlthesessionproperties（ALTERSESSION）

andtoenable/disableroles（SETROLE）.

--SystemControl

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

select的用法

--每个员工的所有信息

select*fromemp;

--每个人的部门编号，姓名，薪水

selectempno,ename,salfromemp;

--每个人的年薪

selectename,sal*12fromemp;

--计算2*3的值

select2*3fromemp;

--计算2*3的值（dual）

select2*3fromdual;

--得到当前时间

selectsysdatefromdual;

--可以给列起别名,比如求每个人的年薪

selectename,sal*12annual_salfromemp;

--如果别名中有空格，需要用双引号

selectename,sal*12"annualsal"fromemp;

--如果没有内容，则为空

selectename,sal,commfromemp;/*null*/

--当空字段参与计算，则结果是null

--例如：

计算每个人的全年的收入包括月薪和年终奖

selectename,sal*12+commfromemp;

--可以将多个字符串拼在一起。

比如：

求每个人的薪水，格式为smith-sal-123

selectename||'-'||sal||'-'||commfromemp;

--如果字符串中有单引号，需要用另外一个单引号转义，比如：

这样一个字符串：

he'sfriend

selectename||'he''sfriend'fromemp;

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

--distinct关键词的用法

--求有哪些个部门

selectdeptnofromemp;

selectdistinctdeptnofromemp;

--可以用来修饰多个字段

--求有哪些个部门和job的组合

selectdeptno,jobfromemp;

selectdistinctdeptno,jobfromemp;

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

where关键词的用法

--可以是数值类型的等值判断。

比如：

求10这个部门的所有员工

select*fromempwheredeptno=10;

--可以是字符串类型的等值判断。

比如：

求叫KING的这个人的信息

select*fromempwhereename='KING';

--也可以是不等值判断。

比如：

求薪水小于2000的员工信息

select*fromempwheresal>2000;

--字符串也可以做不等值判断，比如：

求所有ename大于'CBA'的员工信息。

selectenamefromempwhereename>'CBA';

--求部门不是10的部门

select*fromempwheredeptno<>10;

--求薪水在800和1500之间的员工信息

select*fromempwheresalbetween800and1500;

--也可以写成

select*fromempwheresal>=800andsal<=1500;

/*这样写则不可以

   --select*fromempwhere800<=sal<=1500;

*/

--where...in..的用法。

比如：

求薪水是800或者1500或正2000的员工信息

selectename,empno,salfromempwheresalin（800,1500,2000）;

--相当于写成这样

selectename,empno,salfromempwheresal=800orsal=1500orsal=2000;

--再比如求姓名是KING,SMITH,AA的员工信息

selectename,empno,salfromempwhereenamein（'KING','SMITH','AA'）;

--求入职时间在20-2月-81之后的员工信息

selectename,hiredatefromempwherehiredate>'20-2月-81';

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

--andornot的用法

--求薪水大于1000或者部门在10这个部门的员工信息

select*fromempwheresal>1000ordeptno=10;

--求薪水不是800或者不是1500或者不是3000的员工信息

select*fromempwheresal<>800andsal<>1500andsal<>3000;

--也可以这样来写

select*fromempwheresalnotin（800,1500,3000）;

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

--like的用法

--求名字中包含ALL这三个字符的员工信息

selectenamefromempwhereenamelike'%ALL%';

--求名字中的第二个字母是A的员工

selectenamefromempwhereenamelike'_A%';

--特殊字符需要转义。

比如：

求员工中包含特殊字符%的员工信息

selectenamefromempwhereenamelike'%\%%'escape'\';

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

--null的用法

--求没有年终奖的员工

selectenamefromempwherecommisnull;

--求有年终奖的员工

selectenamefromempwherecommisnotnull;

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

--orderby的用法

--员工信息按照姓名正序排列

selectename,salfromemporderbyenameasc;--ascent

--员工信息按照倒叙排列

selectename,salfromemporderbyenamedesc;--descent

--也可以是多个字段组合排列。

例如：

员工信息按照部门正序排列，并且按照姓名倒叙排列

selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;

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

--function的用法

--把所有姓名变成小写

selectlower（ename）fromemp;

--把所有姓名变成大写

selectupper（ename）fromemp;

--求所有人名中包含'a'的员工信息不区分大小写

selectenamefromempwherelower（ename）like'%a%';

--截取子字符串，比如求Hello的一部分

selectsubstr（'Hello',2）fromdual;

--求Hello的一部分，并指明长度

selectsubstr（'Hello',2,3）fromdual;

--求ascii码对应的字符

selectchr（65）fromdual;

--求字符对应的ascii码

selectascii（'中'）fromdual;

--四舍五入

selectround（23.652）fromdual;

--四舍五入小数点后面多少位

selectround（23.652,1）fromdual;

--四舍五入小数点前面多少位

selectround（23.652,-1）fromdual;

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

--important!

日期转换函数

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

--将当前日期转换成1981-03-1212:

00:

00这种形式的字符串

selectto_char（sysdate,'YYYY-MM-DDHH24:

MI:

SS'）fromdual;

--将1981-03-1212:

00:

00字符串转换成日期

selectto_date（'1981-03-1212:

00:

00','YYYY-MM-DDHH24:

MI:

SS'）fromdual;

--将每个人的薪水转换成固定格式的字符串

selectto_char（sal,'L00,000.9999'）fromemp;

--将固定格式的字符串转换成数值

selectto_number（'$1,250.00','$9,999.99'）fromdual;

--null当null参与计算时候，需要要nvl这个函数

selectename,sal*12+commfromemp;

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

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

--groupfunction组函数

--求所有人的薪水的总和，平均值，最大值，最小值

selectsum（sal）,avg（sal）,max（sal）,min（sal）fromemp;

--求总的行数

selectcount（*）fromemp;

--求总的行树，（可以指定具体的字段）但如果字段有null值的时候需要小心使用

selectcount（comm）fromemp;

--也可以过滤掉重复的行之后统计行数

selectcount（distinctdeptno）fromemp;

--可以指明按照哪个字段进行分组.比如；分部门统计最高薪水

selectdeptno,max（sal）fromempgroupbydeptno;

--也可以按照多个字段来分组统计，比如：

分部门和岗位，统计最高薪水和行数

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

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

--重要：

出现在select列表中的字段，如果没有在组函数中，那么必须出现在groupby子句中。

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

--selectename,deptno,max（sal）fromempgroupbydeptno;

--selectename,max（sal）fromemp;

--求薪水最高的员工姓名

selectmax（sal）fromemp;

selectename,salfromempwheresal=5000;

selectenamefromempwheresal=（selectmax（sal）fromemp）;

--having从句的用法

--求平均薪水是2000以上的部门

selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000;

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

--总结一下select语法

select

from

where

groupby

having

orderby

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

--执行顺序veryimportant!

--首先执行where语句将原有记录过滤；

--第二执行groupby进行分组；

--第三执行having过滤分组；

--然后将select中的字段值选出来；

--最后执行orderby进行排序；

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

/*

按照部门分组统计，求最高薪水，平均薪水

只有薪水是1200以上的才参与统计

并且分组结果中只包括平均薪水在1500以上的部门

而且按照平均薪水倒叙排列

*/

selectmax（sal）,avg（sal）,deptno

fromemp

wheresal>1200

groupbydeptno

havingavg（sal）>1500

orderbyavg（sal）desc;

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

/*

把雇员按部门分组，

求最高薪水，部门号，

过滤掉名字中第二个字母是'A'的，

要求分组后的平均薪水>1500，

按照部门编号倒序排列

*/

selectdeptno,max（sal）

fromemp

whereenamenotlike'_A%'

groupbydeptno

havingavg（sal）>1500

orderbydeptnodesc;

/*veryveryimportant!

*/

selectename,deptnofromemp;

selectdeptno,dnamefromdept;

----员工姓名以及员工所在部门的名字同时显示出来

selectename,dnamefromemp,dept;

selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;

--要求每位雇员的薪水等级

selectename,sal,gradefromemp,salgradewhereemp.sal>=salgrade.losalandemp.sal<=salgrade.hisal;

selectename,sal,gradefromempe,salgradeswheree.salbetweens.losalands.hisal;

--求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时

selectename,dname,grade

fromempe,deptd,salgrades

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

andjob='PRESIDENT';

--求每位员工的姓名，及其上级经理的姓名

selectempno,ename,mgrfromemp;

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

--新语法

--在SQL1992的语法规则中，语句过滤的条件和表连接的条件都被放在了where子句中，当条件过多时，容易造成混淆，

--SQL1999修正了这个缺点，将连接条件和数据过滤条件区分开来，

--交叉连接

--结果会产生这两张表的笛卡尔乘积

selectename,dnamefromempcrossjoindept;--innerjoin

--要用deptno作为等值连接条件，我们可以这样写

selectename,dnamefromempjoindeptusing（deptno）;

--相当于

selectename,dnamefromempjoindeptonemp.deptno=dept.deptno;

--也可以写成这样

selectename,dnamefromempjoindepton（emp.deptno=dept..deptno）;

--也可以用于非等值连接

--求每位雇员的薪水等级

selectename,sal,gradefromemp

joinsalgradeon（emp.sal>=salgrade.losalandemp.sal<=salgrade.hisal）;

--多个join,where组合使用

--（求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时）

selectename,dname,gradefromempe

joindeptdon（e.deptno=d.deptno）

joinsalgradeson（e.salbetweens.losalands.hisal）

wherejob='PRESIDENT';

--外连接--取出表中连接不到一起的多余的数据

--没有全内连接，没有右内连接

--其中outer也可以省略，简写为leftjoin,rightjoin,fulljoin

--leftinnerjoin可以缩写成innerjoin也可以缩写成join，意思是左内。

--updateempsetdeptno=nullwhereename='SMITH';

--commit;

--左内

selectdname,enamefromempleftinnerjoindeptusing（deptno）

--左外连接

selectename,dnamefromempleftouterjoindeptusing（deptno）

--右外连接

selectename,dnamefromemprightouterjoindeptusing（deptno）

--全外连接

s