89魔乐科技Oracle笔记(超经典)--李兴华

1234连接符||567图表1891011121314多表查询的基本语法查一张以上的表，就叫做多表查询例子：查询出雇员名称，部门名称和部门所在地的（一般多表查询要用别名）15统计记录数：查询emp有多少条纪录左右连接（重点）selecte.empno,e.ename,d.deptno,d.dname,d.locfromempe,deptdwheree.deptno=d.deptno;部门一共四个，这里只查询出三个，因为在雇员表中没有指定40部门的雇员，所以在消除笛卡尔乘机的时候没有条件符合40，如果喜欢40部门显示出来，就要用左右连接了。16selecte.empno,e.ename,d.deptno,d.dname,d.locfromempe,deptdwheree.deptno((++))=d.deptno;（+）在左边，表示以右边的表为准，表示右链接。40部门出来了，所以此时就用到了有连接，证明以下规律（．+．）在左表示右连接．．．．．．．．（．+．）在右表示左连接．．．．．．．．17.SQL：1999对SQL的支持（了解）范例：交叉连接（crossjoin）产生笛卡尔积select*fromemptCROSSJOINdept;查询结果产生笛卡尔积CREATETABLEEMP10ASSELECT*FROMEMPWHEREDEPTNO=10;select*fromempNATURALJOINdept;自动进行匹配范例：USING子句，直接关联的操作列select*fromempeJOINdeptdUSING(deptno)wheredeptno=30;把两张表的详细信息进行打印输出范例：ON子句自己编写连接条件select*fromempeJOINdeptdON(e.deptno=d.deptno)wheree.deptno=30;范例：左连接（左外连接）右连接（右外连接）LEFTJOINRIGHTJOIN组函数和分组统计（重点）组函数在SQL常用组函数有如下几个：COUNT()求全部记录数MAX()求最大记录数MIN()求最小记录数AVG()平均SUM()求和分组统计GROUPBYselectdeptno,COUNT(empno)fromempGROUPBYdeptno;18算出部门表的平均工资：selectAVG(sal)fromemp;算出每个部门的平均工资：Selectdeptno,AVG(sal)fromemp;之所以会出现这个错误是因为数据库不知道怎样在结果集中处理deptno列。考虑一下：这个查询既试图使用AVG聚合函数对多行记录进行操作，却又试图从每行中获得deptno列的值；这两个操作是不可能同时完成的。此时必须提供一个GROUPBY子句告诉数据库将deptno列相同的行分组在一起，然后数据库就可以将这些组中的行传递给AVG函数。警告：如果查询中包含聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在GROUPBY子句中。按部门分组，并显示部门名称，以及部门员工数selectd.dname,count(e.empno)fromdeptd,empewhered.deptno=e.deptnoGROUPBYd.dname;要求查出平均工资大于2000的部门编号和平均工资selectdeptno,AVG(sal)fromempWHEREAVG(sal)2000GROUPBYdeptno;19之所以会出现这个错误是因为WWHHEERREE子子句句只只能能用用来来对对单单行行而而不不是是行行组组进进行行过过滤滤。要过滤行组，可以使用HAVING子句。范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于￥5000。输出结果按月工资的合计升序排列1.显示全部的非销售人员：job’SALESMAN’select*fromempwherejob'salesman';2.按工作分组同时求出工资的总和Selectjob,SUM(sal)fromempWHEREjob’SALESMAN’GROUPBYjob;3.对分组条件进行限制Selectjob,SUM(sal)fromempWHEREjob’SALESMAN’GROUPBYjobHAVINGSUM(sal)5000;4.使用排序，按升序排列Selectjob,SUM(sal)sufromempWHEREjob’SALESMAN’GROUPBYjobHAVINGSUM(sal)5000orderbysu;分组的简单原则：只要一列上存在重复的内容才考虑用分组注意：分组函数可以嵌套使用，但是在组函数嵌套的时候不能再出现分组条件的查询语句范例：求出平均工资最高的部门错误代码：Selectdeptno,MAX(AVG(sal))fromempGROUPBYdeptno;SelectMAX(AVG(sal))fromempGROUPBYdeptno;(正确)20子查询范例：要求查询出比7654工资高的全部雇员信息首先：要知道7654雇员的工资是多少然后：以此查询结果为查询依据，只要其他工资大于sal，则表示符合条件首先：查询出比7654工资高的全部雇员信息select*fromempwheresal(selectsalfromempwhereempno=7654);其次：与7788工作一样Selectjobfromempwhereempno=7788所以：select*fromempwheresal(selectsalfromempwhereempno=7654)andjob=（Selectjobfromempwhereempno=7788）；;21数据库更新操作数据库的主要操作分为两种：1..数据库的查询操作SELECT2..数据库的更新操作uUPDATE，DELETE，INSERT此时为了保存原始的emp表的信息，在进行更新删除插入表前先将表复制一份CreatetablemyempASselect*fromemp;此时数据已经复制出来添加数据Insertintoemp(empno,ename,job,hiredate,sal,deptno)Values(7899,’张三’,’清洁工’,’20-2月-2000’,9000,40);使用简略写法(并不推荐)，因为现在是要添加所有字段的内容，所以可以不写上任何字段名称，只要值的数量和顺序和数据库表中的顺序一致。Insertintomyempvalues(7899,’张三’,’清洁工’，9000,40);之前插入数据的时候，日期的格式是使用了表中固定好的格式，如果现在有这样一个日期”2009-10-10”日期格式，那么现在如何把这种格式的日期插入进去呢？使用TO_DATE()函数，将一个字符串类型的数据变为DATE类型的数据。Insertintomyemp(empno,ename,job,hiredate,sal,deptno)Values(7899,’张三’,’清洁工’,TO_DATE(‘2009-07-19’,’yyyy-mm-dd’),9000,40);修改数据UPDATE表名称set要修改的字段=新值，要修改的字段=新值….;UPDATE表名称set要修改的字段=新值，要修改的字段=新值…WHERE修改条件.;22修改数据删除全部:DELETEFROM表名称局部删除:DELETEFROM表名称WHERE删除条件;事物处理范例：创建一张只包含10部分雇员的一张临时表CREATETABLEEMP10ASSELECT*FROMEMPWHEREDEPTNO=10;打开一个oracle终端，进行删除操作DELETEFROMEMP10WHERESAL=2450;显示已经删除然后再打开另外一个oracle终端，查询到sal=2450这条数据还在，证明这条数据并没有被删除，这就是oracle事务的概念。事务处理:就是保证数据操作的完整性，所有的操作要么同时成功要么同时失败。在ORACLE中对每一个连接到数据库中的窗口，都会与数据库建立一个Session。一个Session对数据库所做得修改不会马上反应到数据库的真实数据之上。是允许回滚的，当一个Session提交所有操作之后，数据库才真正做出修改。进行同样的删除操作DELETEFROMEMP10WHERESAL=2450;结果它停住了，等待第一个终端操作结束再动。----》这就是ORACLE死锁提交事务：COMMIT回滚：Roolback23ORACLE常用命令查看所有表selecttable_namefromuser_tables;显示表结构describenchar_tst（nchar_tst为表名）查询练习1.列出至少有一个员工的所有部门信息第一步:列出所有部门的员工数量Selectdeptno,count(empno)fromempgroupbydeptno;第二步:列出员工大于1的部门Selectdeptno,count(empno)fromempgroupbydeptnoHAVINGCOUNT(empno)1;第三步:通过多表关联查，把子查询做为一个查询出来selectd.*,ed.couFROMdeptd,(SELECTdeptno,COUNT(empno)couFROMempGROUPBYdeptnoHAVINGCOUNT(empno)1)edWHEREd.depno=ed.deptno;242.列出薪金比SMITH多的所有员工第一步：求出SMITH的工资SELECTsalFROMEMPWHEREENAME=’SMITH’;第二步：selectenamefromempwheresal(SELECTsalFROMEMPWHEREENAME=’SMITH’);3列出所有员工姓名及其直接上级的姓名此程序属于自身关联查询SELECTe.ename,d.enameFROMempe,empdwheree.mgr=d.empno;4.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称自身关联,查找mgr=empno的同时还要比较Hiredate第一步:SELECTe.empno,e.enameFROMempe,empmWHEREe.mgr=m.empnoANDe.hiredatem.hiredate;第二步:SELECTe1.empno,e1.ename,d.dnameFROM(SELECTe.empno,e.enameFROMempe,empmWHEREe.mgr=m.empnoANDe.hiredatem.hiredate)e1,deptdWHEREe1.deptno=d.deptno;ORA-00904invalididentifier这个错误是因为字段名写错了检查下字段名，发现e1表的查询结果没有deptno字段,所以报错了！SELECTe1.empno,e1.ename,d.dnameFROM(SELECTe.empno,e.ename,e.deptnoFROMempe,empmWHEREe.mgr=m.empnoANDe.hiredatem.hiredate)e1,deptdWHEREe1.deptno=d.deptno;25李兴华给出的标准答案是:SELECTe.empno,e.ename,d.dnameFROMempe,empm,deptdWHEREe.mgr=m.empnoANDe.hiredatem.hiredateANDe.deptno=d.deptno;5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门左右关联问题SELECTd.deptno,d.dname,e.ename,e.empnoFROMdeptd,empeWHEREd.deptno=e.deptno(+);查询结构少了40部门的信息,用连接操作SELECTd.deptno,d.dname,e.ename,e.empnoFROMdeptd,empeWHEREd.deptn