OracleSQL实用基础教程.ppt

王忠海2020/4/25,OracleSQL实用基础教程,SQL概述,历史SQL:StruceuredQueryLanguage1974年，由Boyce和Chamber提出1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为Sequel,SQL概述,标准化有关组织ANSI(AmericanNaturalStandardInstitute)ISO(InternationalOrganizationforStandardization)有关标准SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-99正在讨论中的新的标准，将增加对面向对象模型的支持,SQL概述,特点一体化集DDL，DML，DCL于一体单一的结构-关系，带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径两种使用方式，统一的语法结构SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）语言简洁，易学易用,SQL概述,1SQL命令基础,准备工作,用SQLPLUS来学习SQL连接到SQLPLUS，创建学习用的用户和数据SCOTT用户在ORACLE805中默认已经创建，在8i和9i中需要手动运行rdbmsadminscott.sql。是ORACLE安装的主目录，在SQLPLUS中可以用?来代替。例如：SQLconnect/assysdbaConnected.SQL?rdbmsadminscott.sqlSQLconnectscott/tigerConnected.,基本的SELECT命令,SELECT命令用于从数据库中获得想要的信息。语法：SELECT*,columnalias,FROMtable;一个最简单的查询语句至少要包括SELECT子句和FROM子句：SELECT后面指定要选择的列FROM后面指定从哪些表或视图中获取数据,SQL语句基本规则,SQL命令是大小写不敏感SQL命令可写成一行或多行一个关键字不能跨多行或缩写子句通常位于独立行，以便编辑，并易读空格和缩进使程序易读关键字大写，其他小写,使用SELECT*可显示所有的列,SQLSELECT*FROMdept;DEPTNODNAMELOC-10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON,选择指定的列,我们可以在SELECT后面指定要选择的列。,SQLSELECTdeptno,locFROMdept;DEPTNOLOC-10NEWYORK20DALLAS30CHICAGO40BOSTON,在SQL*PLUS中查看表具有哪些列,在SQLPLUS中，用DESCTABLENAME命令可以查看表具有的列以及类型等,SQLdescdept名称是否为空?类型-DEPTNONUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13),从数据字典中获取表的列信息,SQLSELECTTABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLEFROMUSER_TAB_COLUMNSWHERETABLE_NAME=DEPT;TABLE_NAMECOLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLE-DEPTDEPTNONUMBER22YDEPTDNAMEVARCHAR214YDEPTLOCVARCHAR213Y,使用算术运算,可以对数字类型的字段进行算术运算。运算的先后顺序是先乘除，后加减，括号优先。上面例子中，计算的是一年的报酬，月工资乘以12个月，再加上100,SQLSELECTename,sal,12*sal+100FROMemp;ENAMESAL12*SAL+100-KING500060100BLAKE285034300CLERK245029500JONES29753580014rowsselected。,关于空值(NULL),空值是指不可用，不知道，不适用的值空值不等于零也不等于空格对空值进行的任何运算仍然为空值,SQLSELECTenameNAME,12*salcommFROMempWHEREenameKING;NAME12*SAL+COMM-KING,定义列的别名,当显示查询结果时，SQL*PLUS通常使列名作为列头。在很多情况下，列名并非是此列的清晰描述。因此我们就可使用列的别名作为列头，。缺省情况下，列的别名是大写的。如果区别大小写，可加双引号，如有特殊字符如$、#也必须使用双引号将其括起来。上面的例子中的AS可以省略,SQLSELECTenameASname,salsalaryFROMemp;NAMESALARY-,SQLSELECTenameName,sal*12AnnualSalaryFROMemp;NAMEAnnualSalary-,列连接操作,使用双竖条”|”操作符，可将列和运算表达式常量连起来显示，形成一个输出显示,SQLSELECTENAME|ssalaryis|salEmployeesSalaryfromemp;EmployeesSalary-SMITHssalaryis800ALLENssalaryis1600WARDssalaryis1250JONESssalaryis2975.已选择14行。,去除重复记录,默认情况下，显示所有行，包括重复记录。如果想去掉重复记录，可以在DISTINCT关键字。如果在DISTINCT后面指定了多个列，则DISTINCT将对所有被选择的列有效，其结果是不同的列的组合。,SQLSELECTdeptnoFROMemp;DEPTNO-10301014rowsselected。,SQLSELECTDISTINCTdeptnoFROMemp;DEPTNO-102030,SQL*PLUS程序介绍,SQL*PLUS是是Oracle自带的与Oracle交互的一个工具。你可在SQL*PLUS中做以下操作：执行SQL命令来修改、查询、增加、删除数据库中的数据格式化、计算、存储、数据于一定格式的报告中产生用于存储SQL命令的脚本，以便以后执行SQL*PLUS专用命令可被分为如下类别：环境：影响通常的SQL命令格式化：格式化查询结果文件处理：存储、调用、运行脚本文件编辑：修改SQL缓存中的SQL命令显示列的定义,登陆到SQL*PLUS,在命令行提示符下输入SQLPLUS/NOLOG然后在SQL提示符下输入CONNECTUSERNAME/PASSWORDDATABASE,C:DocumentsandSettingsAdministratorSQLPLUS/NOLOGSQL*Plus:Release9.2.0.4.0-Productionon星期四5月2409:51:422007Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnectsystem/managertlgaxz已连接。,SQL*PLUS的编辑命令,SQL*PLUS的命令每次只能键入一行，且不能存于SQL缓存中APPENDtext将text加到当前行的后面CHANGE/old/new将当前行的old改为newCHANGE/text/从当前行中删除textCLEARBUFFER从缓存中删除所有的行DEL删除当前行LIST：列出SQL缓存中的所有行LISTn：列出一行(由n指出列出的行)RUN：显示并运行SQL缓存中的SQL语句,SQL*PLUS的编辑命令(续),GETfilename.ext：把文件的内容写到SQL缓存filename.ext：运行文件EDIT：调用编辑器编辑当前缓存中的内容EDITfilename.ext：调用编辑器编辑存的文件SPOOLfilename.ext：将查询结果存于文件中SPOOLOFF结束结果内容输出EXIT：退出SQL*PLUS,2限定和排序数据,目的,限制某一查询所取记录排序查询结果,使用选择限定记录,在上面的例子中，假定你想要显示部门10的所有员工，这种方式是基于WHERE子句的SQL命令。,使用where子句限定返回的记录,WHERE子句在FROM子句后面Condition:由列名表达式，常量和比较操作符组成。,SELECTDISTINCT*,columnalias,FROMtableWHEREcondition(s);,使用WHERE语句,上面例子中返回job=CLERK的所有员工的name,job和deptno注意：字符的大小写是敏感的。,SQLSELECTename,job,deptnoFROMempWHEREjob=CLERK;ENAMEJOBDEPTNO-KINGCLERK30BLAKECLERK20TURNERCLERK1014rowsselected。,字符串和日期,在WHERE子句中的字符串和日期必须用单引号括起来，所有的字符是大小写敏感的。Oracle存储日期是以内定的格式存放，它们代表世纪、年、月、日、小时、分钟和秒，缺省显示的日期格式是DD-MON-YY，也可能是其他格式。为了避免日期字段查询条件不同格式下可能造成的错误，通常用TO_DATE函数来进行转换。,日期类型查询条件举例,上面第二个例子使用了TO_DATE函数，就不再受日期格式的影响了。否则同样的查询，同样的表数据，不同的系统日期格式会导致结果不正确。,SQLSELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=03-DEC-81;ENAMEHIREDATE-JAMES03-DEC-81FORD03-DEC-81SQLSELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=TO_DATE(19811203,YYYYMMDD);ENAMEHIREDATE-JAMES03-DEC-81FORD03-DEC-81,常用比较运算符,使用BETWEEN,SQLSELECTename,salFROMempWHEREsalBETWEEN1000AND1500;ENAMESAL-KING1250BLAKE1500CLERK1250JONES1300,注意：BETWEEN后面要先写低值，后写高值,使用IN运算符,SQLSELECTempno,ename,sal,mgrFROMempWHEREmgrIN(7902,7566,7788);EMPNOENAMESALMGR-7902KING125075667369BLAKE150079027788CLARK125075667876JONES13007788,使用LIKE运算符,使用LIKE运算符执行通配查询查询条件可包含文字字符或数字%可表示零或多个字符_可表示一个字符,SQLSELECTenameFROMempWHEREenameLIKES%;ENAME-SMITHSCOTT,用LIKE和ESCAPE来查找包含特殊字符的数据,例如如果想查找表EMP中ENAME包含下划线_的数据，就需要用到ESCAPE选项，否则查询结果不准确。ESCAPE后面单引号内只能有一个字符，表示前面的LIKE条件中这个字符后面的第一个字符当作普通字符处理,SQLSELECTEMPNO,ENAMEFROMEMPWHEREENAMELIKE%_%ESCAPE;EMPNOENAME-9999FOR_TEST,使用ISNULL,查询包含空值的记录,SQLSELECTename,mgrFROMempWHEREmgrISNULL;ENAMEMGRKING,逻辑运算符,优先级次序：1所有的比较运算2NOT3AND4OR括号将跨越所有优先级,使用AND运算符,AND需要条件都满足,SQLSELECTempno,ename,job,salFROMempWHEREsal=1100ANDjob=CLERK；EMPNOENAMEJOBSAL-7369BLAKECLERK13007788CLARKCLERK1250,使用OR运算符,OR需要满足条件之一即可,SQLSELECTempno,ename,job,salFROMempWHEREsal=1100ORjob=CLERK；EMPNOENAMEJOBSAL-7369BLAKECLERK13007788CLARKCLERK12507839KINGPERSIDENT50007645MARTINMANAGER1050,使用NOT运算符,SQLSELECTename,jobFROMempWHEREjobNOTIN(CLERK,MANAGER,ANALYST);ENAMEJOB-KINGPERSIDENTMARTINSALESMANWARDSALESMAN,ORDERBY语句,在缺省情况下，查询返回的结果是没被排序的。使用ORDERBY子可将记录排序。ORDERBY子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省为ASC,SELECTexprFROMtableWHEREconditionORDERBYcolumn,exprASC|DESC,降序排列,SQLselectename,hiredatefromemporderbyhiredatedesc;ENAMEHIREDATE-FOR_TESTADAMS12-JAN-83SCOTT09-DEC-82MILLER23-JAN-82JAMES03-DEC-81SMITH17-DEC-8015rowsselected.,使用列别名排序,SQLselectempno,ename,sal*12annualfromemporderbyannual;EMPNOENAMEANNUAL-7369SMITH96007900JAMES114007876ADAMS132007521WARD150007654MARTIN150007934MILLER156007844TURNER1800015rowsselected.,按照多个列排序,SQLSELECTename,sal,deptnoFROMEMPORDERBYdeptno,salDESC;ENAMESALDEPTNO-KING500010CLARK245010MILLER130010SCOTT300020FORD300020JONES297520ADAMS110020.15rowsselected.,3单行函数,SQL函数,SQL函数有两种不同的SQL函数单行函数多行函数单行函数这些函数仅作用于单行记录，并对每行记录返回一个值，有许多不同类型的单行函数，常用的类型有：字符函数数字函数日期函数转换函数多行函数这些函数作用于记录组，每组记录返回一个结果。,单行函数,单行函数单行函数操作数据项，它们接收一个或多个参数，并对查询出的每一条记录返回一个值。参数可以是：用户提供的常量一个列名一个表达式单行函数的特性它们作用于查询的每一条记录每条记录返回一个结果它们可返回一个不同于它所参照的数据类型它们可嵌入到SELECT，WHERE和ORDERBY子句。,字符串函数,字符函数被分为：大小写转换函数字符处理函数LOWER(column|expression):将字符转换为小写UPPER(column|expression):将字符转换不大写INITCAP(column|expression):将每一个单词的第一个字母大写其它小写CONCAT(columnexpression):返回第一个串接上第二个串,它的作用和|运算是相同的SUBSTR(columnexpression,):返回从字母m开始,有n个字符长的字符串。LENGTH(columnexpression):返回字符串长度INSTR(column1expression.mn):返回字符串中字符的位置LPAD(column1cxpression,n,string):在字符串前填补字符,使其长度达到n。RPAD(column1cxpression,n,string):在字符串后填补字符,使其长度达到n。,字符串函数举例,上面的例子将ename列的首字母大写，其余字母小写,SQLSELECTINITCAP(ename)FROMemp;INITCAP(EN-SmithAllenWard,字符串处理函数举例,SQLSELECTENAME,SUBSTR(ENAME,1,3)SUBSTR,RPAD(ENAME,10,*)RPAD,LENGTH(ENAME)LENGTHFROMEMP;ENAMESUBSTRRPADLENGTH-SMITHSMISMITH*5ALLENALLALLEN*5WARDWARWARD*4JONESJONJONES*5MARTINMARMARTIN*6,数字函数,数字函数接收数字输入返回数字值ROUND(column|expression,n):返回舍入到小数点右边n位的值TRUNC(column|expression,n):返回截断到n位的值MOD(m,n):返回m和n相除后的余数,使用ROUND函数,上面的例子分别显示45.923到小数点后两位，个位，十位,SQLSELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)-45.924650,使用TRUNC函数,显示45.923到小数点后两位，个位，十位,SQLSELECTTRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROMDUAL;TRUNC(45.923,2)TRUNC(45.923,0)TRUNC(45.923,-1)-45.924540,使用MOD函数,这个例子计算工资除以奖金后的余数,SQLSELECTename,sal,comm,MOD(sal,comm)FROMempWHEREjob=SALESMAN;ENAMESALCOMMMOD(SAL,COMM)-ALLEN1600300100WARD1250500250MARTIN125014001250TURNER150001500,使用日期函数,Oracle的日期函数Oracle使用内部的数字化格式存储日期，它们代表世纪、年、月、日、小时、分钟和秒.缺省显示的日期格式为DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日SYSDATESYSDATE是一个返回当前日期和时间的日期函数DUALDUAL是一个SYS用户所拥有的表，所有的用户都可以访问。它包括一个列DUMMY和一条记录值为X。例子：显示当前的日期SQLSELECTSYSDATEFROMSYS.DUAL;SYSDATE-24-MAY-07,日期运算,日期+数字=日期加天数日期-数字=日期减天数日期-日期=数字两日期间的天数日期+number/24=日期加小时注意：两个日期类型字段不能相加,日期运算举例,SQLSELECTENAME,SYSDATE-HIREDATEFROMEMP;ENAMESYSDATE-HIREDATE-SMITH9654.54954ALLEN9589.54954WARD9587.54954JONES9548.54954MARTIN9369.54954,常见的日期函数,日期函数使用举例,SQLSELECTSYSDATE,ADD_MONTHS(SYSDATE,12),LAST_DAY(SYSDATE),NEXT_DAY(SYSDATE,FRIDAY)FROMDUAL;SYSDATEADD_MONTHSLAST_DAY(SNEXT_DAY(S-2007/05/242008/05/242007/05/312007/05/25,转换函数,转换函数用于数据类型之间的转换。SQL尽可能地自动进行转换，它会隐含地调用转换函数。但是你无法对隐含调用中使用的格式指定符进行控制，并且这会使得你的代码很难理解。因此使用显式转换函数而不依赖于隐式转换是一个很好的程序设计风格。,转换函数,Oracle提供了3个转换函数TO_CHAR(number|date,fmt):将数字或日期按格式转换成字符TO_NUMBER(char):将字符串转换成数字，此字符串必须是数字TO_DATE(CHAR,fmt)：将字符串按指定的格式转换成日期,使用TO_CHAR操作日期函数举例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小时，MI代表分钟，SS代表秒。,SQLSELECTSYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DDHH24:MI:SS)DETAILDATEFROMDUAL;SYSDATEDETAILDATE-24-MAY-072007/05/2413:47:32,使用TO_DATE函数举例,上面的例子，如果不用TO_DATE进行转换，会怎么样呢？,SQLSELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=TO_DATE(19820101,YYYYMMDD);ENAMEHIREDATE-SCOTT09-DEC-82ADAMS12-JAN-83MILLER23-JAN-82,SQLSELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=19820101;SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=19820101*ERRORatline1:ORA-01861:literaldoesnotmatchformatstring,因为19820101不是采用默认日期格式写的，Oracle无法进行隐含转换，导致报错。因此可以看出，对于可能发生转换的语句，应该显式指定转换,NVL函数,将空值转换为实际的值数据格式可以是日期，字符，数字数据类型必须匹配,NVL(comm,0)：如果comm为空，则转换为0NVL(hiredate,01-JAN-97)：如果hiredate为空，则转换为01-JAN-97NVL(job,NoJobYet)：如果job为空，则转换为NoJobYet,使用NVL,SQLSELECTename,comm,sal,sal+300,(sal*12)+NVL(comm,0)FROMemp;ENAMECOMMSALSAL+300(SAL*12)+NVL(COMM,0)-SMITH80011009600ALLEN3001600190019500WARD5001250155015500JONES2975327535700MARTIN14001250155016400BLAKE2850315034200CLARK2450275029400SCOTT3000330036000,4从多个表中选择数据,从多个表中获取数据,有时候你需要从多个表中获得数据。在上面的例子中，报告显示的数据取自两个表。EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中为了生成上面的报告，你需要将表EMP和DEPT连起来，从两个中获取数据。,定义连接,当从两个以上的表中获取数据时，就要使用连接条件。一个表中的记录可以根据两个表的相同列和另一个表的记录相连接。两表中的相同列一般是主键和外键列。为了能显示两个或多个表中的数据，在WHERE子句中需要设简单的连接条件。语法如下：table.column指定取数据的表和它的列table.column1=table2.column2将表连接起来的条件当写一个有连接的SELECT命令时，为了避免同样的不同表具有同样的列名，应该在列前加表的名字或者表的别名。当在表中有相同的列名时，并且这个列作为显示内容或者查询条件，必须在列名前加表名或表的别名作为前缀。如果要将n个表连起来，你必须指定n-1个连接条件。因此连接4个表需要有3个连接条件。如果你的表有组合主键，此规则可能不适用，此时多一条记录需要多个列唯一标识。,笛卡尔结果,笛卡尔结果笛卡尔结果形成于：-连接条件被省略-连接条件无效-第一个表的所有记录连接到第二个表的所有记录一个笛卡尔结果趋于产生一个巨大的记录数，通常没有意义。为了避免笛卡尔结果我们要在WHERE子句中使用有效连接,笛卡尔结果举例,SELECT*FROMEMP,DEPT;,连接类型,有两种主要的连接条件等值连接非等值连接其它的连接方式包括多连接自连接定置运算符,什么是等值连接,就是两个表连接的WHERE条件是一个表的列等于另外一个表的列。通常情况下，这种连接是主键和外键的连接。,使用等值连接获取记录举例,因为DEPTNO列在EMP和DEPT中都存在，因此需要在这个列前面加上表名，否则Oracle认为有歧义，语句无法执行,SQLSELECTENAME,EMP.DEPTNO,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO;ENAMEDEPTNODNAME-MILLER10ACCOUNTINGKING10ACCOUNTINGCLARK10ACCOUNTINGFORD20RESEARCHADAMS20RESEARCHSCOTT20RESEARCH,额外的条件,除了连接条件，可能还有额外的查询条件。例如，显示员工King的员工号、各字、部门号和部门位置，这时在WHERE子句中需要设定一个额外的条件。,SQLSELECTempno,ename,emp.deptno,LocFROMemp,deptWHEREemp.deptno=dept.deptnoANDINITCAP(ename)=King;EMPNOENAMEDEPTNOLOC-7839KING10NEWYORK,使用表的别名,表的别名使用表名限定列名可能会很浪费时间，尤其是当表名特别长，这时你可以使用表的别名。使用表的别名会减少程序代码，因此占用较少的内存。注意，表的别名是在FROM子句中指定的。表别名规则：表的别名最长为30个字符，但通常以短字符为佳表的别名最好有一定的含义表的别名只在当前的SELECT语句有效如果在FROM子句定义了表的别名，在SELECT子句中必须用它来替代表名。,SQLSELECTempno,ename,e.deptno,LocFROMempe,deptdWHEREe.deptno=d.deptnoANDINITCAP(ename)=King;EMPNOENAMEDEPTNOLOC-7839KING10NEWYORK,非等值连接,在EMP表和SALGRADE表中，没有直接的对应列，它们之间的关系是EMP的SAL列的值在SALGRADE表的LOSAL和HISAL列之间，它们是不等值连接。,非等值连接举例,SQLSELECTe.sal,e.ename,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;SALENAMEGRADE-5000KING53000SCOTT43000FORD42975JONES42850BLAKE42450CLARK41600ALLEN31500TURNER31300MILLER2.,外连接,使用外部连接，返回连接两边有一边为NULL的记录外连接运算符是加号(+)外连接运算符(+)可以加在左边,也可以加在右边，但不能两边同时加外连接有(+)的一边表示这边的值要么等于另外一边，要么为NULL从9i开始，SQL支持ANSISQL，也就是支持LEFTOUTERJOIN、RIGHTOUTERJOIN和FULLOUTERJOIN,SQLSELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;,SQLSELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column=table2.column(+);,外连接举例1：,这个例子(+)在e.deptno这边，意味着e.deptno可以是NULL,SQLSELECTename,e.deptnoE.DEPTNO,d.deptnoD.DEPTNO,d.dnameFROMEMPE,DEPTDWHEREE.DEPTNO(+)=D.DEPTNO;ENAMEE.DEPTNOD.DEPTNODNAME-SMITH2020RESEARCHALLEN3030SALES.FORD2020RESEARCHMILLER1010ACCOUNTING40OPERATIONS15rowsselected.,外连接举例2,这个例子(+)在d.deptno这边，意味着d.deptno可以是NULL,SQLSELECTename,e.deptnoE.DEPTNO,d.deptnoD.DEPTNO,d.dnameFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNO(+);ENAMEE.DEPTNOD.DEPTNODNAME-MILLER1010ACCOUNTINGKING1010ACCOUNTING.ALLEN3030SALESwzh90FOR_TEST9016rowsselected.,外连接举例3,从9i开始，可以用ANSISQL语法来写外连接，这样也提供了一个以前的(+)不能实现的功能：全外连接,SQLSELECTE.ENAME,E.DEPTNOE.DEPTNO,D.DEPTNOD.DEPTNO,D.DNAMEFROMEMPEFULLOUTERJOINDEPTDON(E.DEPTNO=D.DEPTNO);ENAMEE.DEPTNOD.DEPTNODNAME-MILLER1010ACCOUNTINGKING1010ACCOUNTING.WARD3030SALESALLEN3030SALESwzh90FOR_TEST9040OPERATIONS17rowsselected.,表的自连接,有的时候，需要对表进行自连接。例如上图所示，EMP表中的MGR列的代表员工的经理的员工编号，所以要想显示出每个员工的经理就要对EMP进行自连接，用MGR=EMPNO。实际上，自连接只是等连接（等外连接）的一个特例，同一张表用不同的别名，区别成了不同的表。,表自连接举例,上面这个例子相当一个等外连接。如果员工有经理，则显示出员工为谁工作（打工者），否则显示员工为自己干活（老板）,SQLSELECTE.ENAME|worksfor|NVL(M.ENAME,himself)RELATIONSFROMEMPE,EMPMWHEREE.MGR=M.EMPNO(+);RELATIONS-FORDworksforJONES.JONESworksforKINGSMITHworksforFORDKINGworksforhimself14rowsselected.,5使用分组函数,什么是分组函数,和单行函数不同，分组函数作用于一组记录，每一组返回一个结果。这些组可能是整个表，也可能是由GROUPBY子句将表分成的多个组。,主要的分组函数,COUNT(*/DISTINCE/ALLexpr)返回记录数，这里expr赋非空值，*表示所有被选择的记录，包括重复记录和空值。MAX(DISTNCT/ALLexpr)：表达式的最大值，忽略空值MIN(DISTNCT/ALLexpr)：表达式的最小值，忽略空值AVG(DISTNCT/ALLexpr)：平均值，忽略空值STDDEVDISTINCT/ALLX)：返回标准差，忽略空值SUM(DIXNTICT/ALLn)：求和，忽略空值VARIANCE(DISTINCT/ALLX)：返回统计方差这些分组函数中,COUNT是不计算NULL值的，其它函数忽略NULL值。,使用分组函数举例1：,SQLSELECTSUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL)FROMEMP;SUM(SAL)MAX(SAL)MIN(SAL)AVG(SAL)-2902550008002073.21429,使用分组函数举例2：,SQLSELECTCOUNT(*)FROMEMP;COUNT(*)-16SQLSELECTCOUNT(EMPNO)FROMEMP;COUNT(EMPNO)-16SQLSELECTCOUNT(MGR)FROMEMP;COUNT(MGR)-13,产生数据组：GROUPBY子句,GROUPBY子句使用GROUPBY子句将一个表分成许多小组，并对每一个小组返回一个计算值。Group_by_expression：指定按什么列分组规则：在SELECT子句中，如果使用分组函数，不能对GROUPBY子句中指定的列使用分组函数。使用WHERE子句，可预先排除某些记录在GROUPBY子句中必须有表中的列在GROUPBY子句中不能使用列的别名缺省情况下在GROUPBY子句中的列以升序排，你可以使用orderby子句改变它。,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;,使用GROUPBY举例,注意，不是分组的列不能出现在SELECT后面。否则会提示：ORA-00979:notaGROUPBYexpression。组函数也不能出现在WHERE子句中，否则会提示ORA-00934:groupfunctionisnotallowedhere,SQLSELECTDEPTNO,SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO;DEPTNOSUM(SAL)MAX(SAL)MIN(SAL)AVG(SAL)-108750500013002916.6666720108753000800217530940028509501566.66667,多列分组举例,有时你可能需要在组中再分组，上面例子中显示每个部门中不同头衔的工资和与平均工资。此时EMP表首先以部门分组，然后按头衔分组,SQLSELECTDEPTNO,JOB,SUM(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO,JOB;DEPTNOJOBSUM(SAL)AVG(SAL)-10CLERK1300130010MANAGER2450245010PRESIDENT5000500020CLERK190095020ANALYST6000300020MANAGER2975297530CLERK95095030MANAGER2850285030SALESMAN56001400,使用HAVING子句限定分组函数结果值,前面我们讲过，分组函数不能写在WHERE子句中，如果要对分组函数结果值进行限定，可以用HAVING子句,SQLSELECTDEPTNO,JOB,SUM(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO,JOBHAVINGAVG(SAL)2000;DEPTNOJOBSUM(SAL)AVG(SAL)-10MANAGER2450245010PRESIDENT5000500020ANALYST6000300020MANAGER2975297530MANAGER28502850,练习,练习分组函数的使用练习GROUPBY子句练习HAVING子句掌握分组函数中易犯的错误,6子查询,使用子查询解决问题,假设想知道谁的工资高于Jones。为了解决这个问题，必须执行两个查询：第一个查询查到了Jones的工资，第二个查询查找高于这个工资的人。可以将这两个查询组合起来，将一个查询放在另一个中来解决此问题。一个内部的查询或子查询返回一个值，此值被外部查询或主查询使用。使用子查询等价于执行两个顺序查询。第一个查询的结果作为第二个查询检索的值。,子查询语法,子查询在主查询前执行一次主查询使用子查询的结果,SELECTselect_listFROMtablenameWHEREexpr_operator(SELECTselect_listFROMtable);,子查询举例,SQLselectename,salfromempwheresal(selectsalfromempwhereename=JONES);ENAMESAL-SCOTT3000KING5000FORD3000,子查询类别,单行子查询：内部SELECT命令返回一条记录多行子查询：内部SELECT命令返回多条记录多列子查询：内部SELECT命令返回多个数据列,子查询使用规则,子查询要用括号括起来将子查询放在比较运算符的右边子查询中不要加ORDERBY子句对单行子查询使用单行运算符（如=,selectename,salfromempwheresal=(selectsalfromemp);selectename,salfromempwheresal=(selectsalfromemp)*ERRORatline1:ORA-01427:single-rowsubqueryreturnsmorethanonerow,子查询容易犯的错误2：,另一个常见的错误是内层查询没有返回记录,SQLselectename,salfromempwheresal=(selectsalfromempwhereename=NOTHING);norowsselected,多行子查询举例,查找个部门工资最高的员工,SQLSELECTENAME,DEPTNO,SALFROMEMPWHERESALIN(SELECTMAX(SAL)FROMEMPGROUPBYDEPTNO);ENAMEDEPTNOSAL-KING105000FORD203000SCOTT203000BLAKE302850,在多行子查询中使用ANY,ANY运算子查询返回每个值。上面例子中返回工资低于任何CLERK的员工，并且他不是CLERK。CLERK工资最高值为$1300，上面的例子返回工资低于$1300，且非CLERK的员工，”ANY”意味着大于最小值，”=ANY”等于IN,SQLSELECTename,deptno,job,sal2FROMempWHEREsalCLERK;ENAMEDEPTNOJOBSAL-WARD30SALESMAN1250MARTIN30SALESMAN1250,7操作数据,课程目标,在这一节课里，你将学会如何往表中插入记录，如何修改和删除表中的记录。另外，你还将学会如何用COMMIT和ROLLBACK语句控制事务。,数据操作语言(DML),当你想在数据库中增加、修改或删除数据时，你就要执行DML语句。由一组DML语句组成的逻辑工作单元叫做一个事务。DML语句可以SQL*PLUS或SQL*DBA中直接执行。也可以在象Developer/2000、TOAD这样的工具中执行，还可以在SQL预编译程序中执行。数据操作语言主要有INSERT,UPDATE和DELETE,INSERT语句,table：表名column：列名value：列的值通过INSERT语句在表中增加一条新记录。如果省略表后面的列，那么要在VALUES后面按照表的顺序指定所有的列的值,INSERTINTOtable(column,column)VALUES(value，value)；,INSERT举例:,如果表中的列没有在VALUES中列出来，则系统分配给这些列默认值。没有默认值则为空。如果该列不能为空，则插入语句不成功。如果违反约束条件，插入语句同样不能成功。,SQLINSERTINTOdept(deptno,dname,loc)VALUES(50,DEVELOPMENT,BEIJING);1rowcreated.,从其它表中拷贝数据,SQLINSERTINTOMANAGERS(empno,ename,sal,hiredate)SELECTempno,ename,sal,hiredateFROMempWHEREjob=MANAGER;3rowscreated.,UPDATE语句,用UPDATE语句可以一次修改一条或者多条记录。如果没有WHERE条件，则所有的SET后面的列的值都会被更新。,UPDATEtableSETcolumn=value,column=valueWHEREcondition;,UPDATE举例,给EMP表中部门编号是30的员工工资增加20%,SQLUPDATEEMPSETSAL=SAL*1.2WHEREDEPTNO=30;6rowsupdated.,DELETE语句,用DELETE语句从表中删除数据。如果不加WHERE条件，那么所有的记录都将被删除。,DELETEFROMtableWHEREcondition;,从表中删除指定的记录,SQLDELETEFROMDEPTWHEREDNAME=DEVELOPMENT;1rowdeleted.,提交所作的修改,用INSERT、UPDATE和DELETE语句对数据进行改动后，数据并没有真正的保存。这些改动的数据别的会话中看不到。为了真的保存，需要执行COMMIT命令。,一些隐式的COMMIT,如果在SQL*PLUS中执行了DML语句，然后虽然没有执行COMMIT，但是如果存在如下情形之一，Oralce会执行一个隐式的COMMIT，一定要注意：1.正常退出SQL*PLUS2.执行了一条DDL语句，例如创建了一个表,取消所做的修改,可以用ROLLBACK命令取消所做的修改,隐式的ROLLBACK,如果修改数据后，没有COMMIT，也没有提交，那么下列情况下Oracle会取消所做的修改：1.系统崩溃2.程序异常退出,DML语句应该注意的问题,一定要加合适的WHERE条件，避免错误修改数据执行DML后因该尽快的显式执行COMMIT或者ROLLBACK，因为被改动的数据别的用户是不能修改的，长时间不结束事务会增加死锁的可能性,COMMIT举例,SQLUPDATEempSETdeptno=10WHEREdeptno=7782;1rowupdated.,SQLCOMMIT;Commitcomplete.,ROLLBACK举例,SQLSELECTCOUNT(*)FROMEMP;COUNT(*)-16SQLDELETEFROMEMP;已删除