SQL查询基础ppt课件

.,1,SQL查询基础,.,2,本章要点,SQL的基本概念和作用SQL与SQL*Plus、PL/SQL之间的关系SelectWhereOrderByGroupByHaving,.,3,概述,本节包括两部分内容。首先，介绍SQL的作用和特点，SQL技术是本章的中心内容；其次，介绍SQL与SQL*Plus、PL/SQL之间的区别和联系，以便更好地学习和理解SQL。,.,4,SQL的作用和特点,SQL是结构化查询语言(structurequerylanguage)的简称，是关系型数据库管理系统中最流行的数据查询和更新语言。用户可以使用SQL语言在数据库中执行各种操作。1986年，SQL-86，也被称为SQL-1。1992年，SQL-92，也被称为SQL-2。1999年，SQL-99，也被称为SQL-3。还存在不同的数据库管理系统厂商开发的不同类型的SQL。SQL查询语言包括了所有对数据库的操作，这些操作可以分为4个部分，即数据定义语言DDL：定义数据库的逻辑结构数据操纵语言DML：数据检索和数据更新数据控制语言DCL：授权、完整性规则事务管理语言TML：事务的定义、保存、提交commit、取消rollback,.,5,SQL与SQL*Plus、PL/SQL之间的关系,SQL*Plus是一个用于连接Oracle数据库的强大工具。使用该工具，用户可以连接服务器上的数据库，还可以执行许多操作。总结一下SQL*Plus的主要功能特点：在数据库中执行SQL语言和PL/SQL语言；更新数据库中的数据；执行数据查询；将查询结果集格式化为报表；建立、编辑、检索和执行SQL脚本；管理数据库；描述数据库中的表和PL/SQL对象；将数据从一个数据库复制到另一个数据库；向用户发送消息，并接受用户的输入。,.,6,PL/SQL,PL/SQL是Oracle系统的过程化编程语言。用户可以使用PL/SQL语言编写在数据库中执行的定制程序和过程代码。如同拥有执行SQL语言的SQL引擎一样，Oracle系统也拥有执行PL/SQL语言的PL./SQL引擎。PL/SQL程序能够存储在数据库中，以便用于用户应用程序和日常操作。这样可以使用户在Oracle数据库设计和规划的编程语言中，实现与用户数据密切相关的业务逻辑。,.,7,SQL语句的书写规则,一般情况下，在使用SQL语句(包括使用select语句)时，用户应该遵循下面的规则或约定：除非明确指定，否则SQL语句不区分大小写；SQL语句可以一行或多行的形式输入；关键字不能跨行；为了增强可读性，关键字左对齐；可以在SQL语句中使用各种制表符和伸缩符号。,.,8,select语句,数据操纵语言就是那些检索、更新表中数据的语句。这些语句包括select、insert、update、delete等4个语句。本节首先介绍select语句，后面将陆续讲述其他的数据操纵语言。在SQL语言中，最简单的语句是select语句。使用select语句可以检索数据库中的信息。具体地说，使用select语句可以完成选择、投影、连接等运算。,.,9,select基本语法,在Oracle系统中，select语句的基本语法格式如下：selectdistinct*,column_namealias,fromtablenamewheregroupbyhavingorderby,.,基本的SELECT命令,SELECT*,columnalias,.FROMtable;,SELECT识别什么列FROM识别哪个表,.,选择所有列,DEPTNODNAMELOC-10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON,SQLSELECT*2FROMdept;,.,选择指定的列,DEPTNOLOC-10NEWYORK20DALLAS30CHICAGO40BOSTON,SQLSELECTdeptno,loc2FROMdept;,.,算数表达式,对NUMBER和DATE型数据可用算数运算创建表达式,运算+-*/,描述加法减法乘法除法,.,使用算数运算,SQLSELECTename,sal,sal+3002FROMemp;,ENAMESALSAL+300-KING50005300BLAKE28503150CLARK24502750JONES29753275MARTIN12501550ALLEN16001900.14rowsselected.,.,运算的优先级,乘法和除法的优先级高于加法和减法同级运算的顺序是从左到右表达式中使用括号可强行改变优先级的运算顺序,.,运算的优先级,SQLSELECTename,sal,12*sal+1002FROMemp;,ENAMESAL12*SAL+100-KING500060100BLAKE285034300CLARK245029500JONES297535800MARTIN125015100ALLEN160019300.14rowsselected.,.,使用括号,SQLSELECTename,sal,12*(sal+100)2FROMemp;,ENAMESAL12*(SAL+100)-KING500061200BLAKE285035400CLARK245030600JONES297536900MARTIN125016200.14rowsselected.,.,定义空值,空值是指不可用，不知道，不适用的值空值不等于零或空格,SQLSELECTename,job,comm2FROMemp;,ENAMEJOBCOMM-KINGPRESIDENTBLAKEMANAGER.TURNERSALESMAN0.14rowsselected.,.,空值的运算表达式,包括空值的算法表达式等于空,SQLselectenameNAME,12*sal+comm2fromemp3WHEREename=KING;,NAME12*SAL+COMM-KING,.,20,处理NULL,当算术表达式包含NULL，其结果也是NULLSelectename,sal,comm,sal+commfromemp;使用NVL函数处理NULLNVL（expr1,expr2):如果expr1是null，则返回expr2；否则返回expr1Selectename,sal,comm,sal+nvl(comm,0)as“月收入”fromemp;使用NVL2函数处理NULLNVL2(expr1,expr2,expr3):如果expr1不是null，则返回expr2；否则返回expr3Selectename,nvl2(comm,sal+comm,sal)fromemp;,.,定义列的别名,改变列的标题头使用计算结果列的别名如果使用特殊自符,或大小写敏感,或有空格时,需加双引号,.,使用列的别名,SQLSELECTenameASname,salsalary2FROMemp;,NAMESALARY-.,SQLSELECTenameName,2sal*12AnnualSalary3FROMemp;,.,连结操作,将列或字符与其它列连结用双竖条表示(|)产生的结果列是一个字符表达式,.,使用连结操作,SQLSELECTename|jobASEmployees2FROMemp;,Employees-KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN.14rowsselected.,.,文字字符串,文字字符串是一个包括在SELECT列表中的字符,表达式,或数字日期和字符型文字字符必须用单引号扩起来每返回一条记录字符被输出一次,.,使用文字字符串,EmployeeDetails-KINGisaPRESIDENTBLAKEisaMANAGERCLARKisaMANAGERJONESisaMANAGERMARTINisaSALESMAN.14rowsselected.,SQLSELECTename|isa|job2ASEmployeeDetails3FROMemp;,.,27,检索日期列,默认日期显示格式：Selectename,hiredatefromemp;使用YYYY-MM-DD显示格式显示日期Selectename,to_char(hiredate,YYYY-MM-DD)fromemp;,.,重复记录,缺省情况下查询显示所有行,包括重行,SQLSELECTdeptno2FROMemp;,DEPTNO-10301020.14rowsselected.,.,删除重复行,使用DISTINCT关键字可删除重行,SQLSELECTDISTINCTdeptno2FROMemp;,DEPTNO-102030,.,30,使用where子句,使用select语句中的where子句可以限制数据量，实际上，select语句中的where子句可以用来指定检索数据的条件。where子句的语法格式如下：selectdistinct*,column_namealias,fromtablenamewherecondition_expression以上的where子句用来指定检索到的数据应该满足指定的条件，参数condition_expression就是用于指定条件。在where子句的条件中，可以比较各种列值、文字串、算术表达式、函数等内容。,.,使用WHERE子句,SQLSELECTename,job,deptno2FROMemp3WHEREjob=CLERK;,ENAMEJOBDEPTNO-JAMESCLERK30SMITHCLERK20ADAMSCLERK20MILLERCLERK10,.,字符串和日期,字符串和日期要用单引号扩起来字符串是大小写敏感的,日期值是格式敏感的缺省的日期格式是DD-MON-YY,SQLSELECTename,job,deptno2FROMemp3WHEREename=JAMES;,SQLSELECT*FROMempWHEREhiredate01-1月-82SQLSELECT*FROMempWHEREhiredateto_date(1982-01-01,YYYY-MM-DD),.,比较运算符,运算符=,含义等于大于大于等于小于小于等于不等于,.,使用比较运算符,SQLSELECTename,sal,comm2FROMemp3WHEREsalSELECTename,sal2FROMemp3WHEREsalBETWEEN1000AND1500;,使用BETWEEN运算符显示某一值域范围的记录,.,使用IN运算符,使用IN运算符获得匹配列表值的记录,SQLSELECTempno,ename,sal,mgr2FROMemp3WHEREmgrIN(7902,7566,7788);,EMPNOENAMESALMGR-7902FORD300075667369SMITH80079027788SCOTT300075667876ADAMS11007788,.,使用LIKE运算符,使用LIKE运算符执行通配查询查询条件可包含文字字符或数字(%)可表示零或多个字符(_)可表示一个字符,SQLSELECTename2FROMemp3WHEREenameLIKES%;,.,使用LIKE运算符,使用组合方式匹配字符Youcancombinepatternmatchingcharacters.使用ESCAPE标识符来查找带特殊符号的字符号,SQLSELECTename2FROMemp3WHEREenameLIKE_A%;,ENAME-JAMESWARD,SQLSELECTename2FROMemp3WHEREenameLIKE%a_%escapea,.,使用ISNULL运算符,查询包含空值的记录,SQLSELECTename,mgr2FROMemp3WHEREmgrISNULL;,ENAMEMGR-KING,.,逻辑运算符,.,使用AND运算符,AND需要条件都是TRUE.,SQLSELECTempno,ename,job,sal2FROMemp3WHEREsal=11004ANDjob=CLERK;,EMPNOENAMEJOBSAL-7876ADAMSCLERK11007934MILLERCLERK1300,.,使用OR运算符,OR需要条件之一是TRUE,SQLSELECTempno,ename,job,sal2FROMemp3WHEREsal=11004ORjob=CLERK;,EMPNOENAMEJOBSAL-7839KINGPRESIDENT50007698BLAKEMANAGER28507782CLARKMANAGER24507566JONESMANAGER29757654MARTINSALESMAN1250.14rowsselected.,.,使用NOT运算符,SQLSELECTename,job2FROMemp3WHEREjobNOTIN(CLERK,MANAGER,ANALYST);,ENAMEJOB-KINGPRESIDENTMARTINSALESMANALLENSALESMANTURNERSALESMANWARDSALESMAN,.,优先级规则,括号将跨越所有优先级规则,次序运算符1所有的比较运算2NOT3AND4OR,.,优先级规则,ENAMEJOBSAL-KINGPRESIDENT5000MARTINSALESMAN1250ALLENSALESMAN1600TURNERSALESMAN1500WARDSALESMAN1250,SQLSELECTename,job,sal2FROMemp3WHEREjob=SALESMAN4ORjob=PRESIDENT5ANDsal1500;,.,优先级规则,ENAMEJOBSAL-KINGPRESIDENT5000ALLENSALESMAN1600,使用扩号提高优先级,SQLSELECTename,job,sal2FROMemp3WHERE(job=SALESMAN4ORjob=PRESIDENT)5ANDsal1500;,.,48,使用orderby子句,在前面介绍的数据检索技术中，只是把数据库中的数据直接取出来。这时，在结果集中数据的排列顺序由数据的存储顺序决定。但是，这种存储顺序经常不符合实际的各种业务需要，因此需要对数据库中的数据在检索时进行排序。在select语句中，可以使用orderby子句执行数据排序操作。使用orderby子句的语法格式如下：orderbycolumn_name,expressionasc|desc,.,49,排序是昂贵的操作,从开始就考虑优化排序往往花费大量的时间及资源磁盘排序比内存排序慢14000倍,.,ORDERBY子句,使用ORDERBY子句将记录排序ASC:升序,缺省DESC:降序ORDERBY子局在SELECT指令的最后,SQLSELECTename,job,deptno,hiredate2FROMemp3ORDERBYhiredate;,ENAMEJOBDEPTNOHIREDATE-SMITHCLERK2017-DEC-80ALLENSALESMAN3020-FEB-81.14rowsselected.,.,降序排序,SQLSELECTename,job,deptno,hiredate2FROMemp3ORDERBYhiredateDESC;,ENAMEJOBDEPTNOHIREDATE-ADAMSCLERK2012-JAN-83SCOTTANALYST2009-DEC-82MILLERCLERK1023-JAN-82JAMESCLERK3003-DEC-81FORDANALYST2003-DEC-81KINGPRESIDENT1017-NOV-81MARTINSALESMAN3028-SEP-81.14rowsselected.,.,使用列的别名排序,SQLSELECTempnoa,enameb,sal*12annsal2FROMemp3ORDERBYannsal;,EMPNOENAMEANNSAL-7369SMITH96007900JAMES114007876ADAMS132007654MARTIN150007521WARD150007934MILLER156007844TURNER18000.14rowsselected.,.,多个列排序,通过ORDERBY列表的顺序来排序,你可使用不在SELECT序列上的列来排序,SQLSELECTename,deptno,sal2FROMemp3ORDERBYdeptno,salDESC;,ENAMEDEPTNOSAL-KING105000CLARK102450MILLER101300FORD203000.14rowsselected.,.,什么是分组函数,分组函数运算每一组记录,每一组返回一个结果,EMP,EMP表中工资的最高值,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,MAX(SAL)-5000,.,55,聚合函数（合计计算）,合计计算就是把一组数据作为计算的对象而执行的计算，这种计算包括求最大值、平均值、方差等。进行合计计算必须使用Oracle系统提供的合计函数。在Oracle系统中，可以使用的合计函数及其语法和功能如表6-5所示。,.,使用AVG和SUM函数,AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-1400160012505600,在数字类型数据使用AVGandSUM函数,SQLSELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp4WHEREjobLIKESALES%;,.,使用MIN和MAX函数,MINandMAX适用于任何数据类型,SQLSELECTMIN(hiredate),MAX(hiredate)2FROMemp;,MIN(HIREDMAX(HIRED-17-DEC-8012-JAN-83,.,使用COUNT函数,COUNT(*)-6,SQLSELECTCOUNT(*)2FROMemp3WHEREdeptno=30;,COUNT(*)返回表中的记录数,.,使用COUNT函数,COUNT(expr)返回非空记录数,SQLSELECTCOUNT(comm)2FROMemp3WHEREdeptno=30;,COUNT(COMM)-4,COUNT(distinctexpr)返回非重复记录数,.,分组函数和空值,分组函省略列中的空值,SQLSELECTAVG(comm)2FROMemp;,AVG(COMM)-550,.,在分组函数中使用NVL函数,NVL函数迫使分组函数包括空值,SQLSELECTAVG(NVL(comm,0)2FROMemp;,AVG(NVL(COMM,0)-157.14286,.,62,使用groupby和having子句,表中包含了许多数据，可以对这些数据按照某个指定的列进行分组，然后使用合计函数计算相应列的合计值。例如，如果需要检索最高薪水和最低薪水的雇员，并返回不同部门中最高薪水和最低薪水的雇员，那么就需要对表中的数据进行分组计算。在Oracle系统中，可以在select语句中使用groupby子句进行分组计算。使用分组计算的语法格式如下：groupbygroup_by_expressionhavinggroup_condition组函数忽略空值,可以使用NVL,NVL2,COALESCE函数处理空值结果集按groupby的列隐式按升序排列,.,数据分组,EMP,EMP表中每一个部门的平均工资,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,DEPTNOAVG(SAL)-102916.6667202175301566.6667,.,使用GROUPBY子句,所有用来分组的列在SELECT列表中不能使用分组函数,SQLSELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;,DEPTNOAVG(SAL)-102916.6667202175301566.6667,.,使用GROUPBY子句,GROUPBY列可以不在SELECT列表中,SQLSELECTAVG(sal)2FROMemp3GROUPBYdeptno;,AVG(SAL)-2916.666721751566.6667,.,多列分组,EMP,按部门统计emp表中每种职位的工资总和,DEPTNOJOBSAL-10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250,JOBSUM(SAL)-CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600,DEPTNO-101010202020303030,.,在多列上使用GROUPBY子句,SQLSELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;,DEPTNOJOBSUM(SAL)-10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900.9rowsselected.,.,使用分组函数的非法查询,如果没有GROUPBY子句SELECT列表中的不能同时出现列和合计函数,SQLSELECTdeptno,COUNT(ename)2FROMemp;,SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunction,.,使用分组函数时的非法查询,不能使用WHERE子句限定组可使用HAVING子句限定分组之后的结果,SQLSELECTdeptno,AVG(sal)2FROMemp3WHEREAVG(sal)20004GROUPBYdeptno;,WHEREAVG(sal)2000*ERRORatline3:ORA-00934:groupfunctionisnotallowedhere,.,限定组的结果,查询部门的最高工资大于$2900,EMP,DEPTNOSAL-1024501050001013002080020110020300020300020297530160030285030125030950301500301250,DEPTNOMAX(SAL)-105000203000,.,限定组的结果:HAVING子句,使用HAVING子句限定组记录被分组应用组函数匹配HAVING子句的组被显示,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,.,使用HAVING子句,SQLSELECTdeptno,max(sal)2FROMemp3GROUPBYdeptno4HAVINGmax