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语句的基本语法格式如下 select distinct column name alias fromtablenamewheregroupbyhavingorderby 基本的SELECT命令 SELECT column alias FROMtable SELECT识别什么列FROM识别哪个表 10 选择所有列 DEPTNODNAMELOC 10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON SQL SELECT 2FROMdept 11 选择指定的列 DEPTNOLOC 10NEWYORK20DALLAS30CHICAGO40BOSTON SQL SELECTdeptno loc2FROMdept 12 算数表达式 对NUMBER和DATE型数据可用算数运算创建表达式 运算 描述加法减法乘法除法 13 14 运算的优先级 乘法和除法的优先级高于加法和减法同级运算的顺序是从左到右表达式中使用括号可强行改变优先级的运算顺序 15 运算的优先级 SQL SELECTename sal 12 sal 1002FROMemp ENAMESAL12 SAL 100 KING500060100BLAKE285034300CLARK245029500JONES297535800MARTIN125015100ALLEN160019300 14rowsselected 16 使用括号 SQL SELECTename sal 12 sal 100 2FROMemp ENAMESAL12 SAL 100 KING500061200BLAKE285035400CLARK245030600JONES297536900MARTIN125016200 14rowsselected 17 定义空值 空值是指不可用 不知道 不适用的值空值不等于零或空格 SQL SELECTename job comm2FROMemp ENAMEJOBCOMM KINGPRESIDENTBLAKEMANAGER TURNERSALESMAN0 14rowsselected 18 空值的运算表达式 包括空值的算法表达式等于空 SQL selectenameNAME 12 sal comm2fromemp3WHEREename KING NAME12 SAL COMM KING 19 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 定义列的别名 改变列的标题头使用计算结果列的别名如果使用特殊自符 或大小写敏感 或有空格时 需加双引号 21 使用列的别名 SQL SELECTenameASname salsalary2FROMemp NAMESALARY SQL SELECTename Name 2sal 12 AnnualSalary 3FROMemp 22 连结操作 将列或字符与其它列连结用双竖条表示 产生的结果列是一个字符表达式 23 使用连结操作 SQL SELECTename jobAS Employees 2FROMemp Employees KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN 14rowsselected 24 文字字符串 文字字符串是一个包括在SELECT列表中的字符 表达式 或数字日期和字符型文字字符必须用单引号扩起来每返回一条记录字符被输出一次 25 使用文字字符串 EmployeeDetails KINGisaPRESIDENTBLAKEisaMANAGERCLARKisaMANAGERJONESisaMANAGERMARTINisaSALESMAN 14rowsselected SQL SELECTename isa job2AS EmployeeDetails 3FROMemp 26 27 检索日期列 默认日期显示格式 Selectename hiredatefromemp 使用YYYY MM DD显示格式显示日期Selectename to char hiredate YYYY MM DD fromemp 重复记录 缺省情况下查询显示所有行 包括重行 SQL SELECTdeptno2FROMemp DEPTNO 10301020 14rowsselected 28 删除重复行 使用DISTINCT关键字可删除重行 SQL SELECTDISTINCTdeptno2FROMemp DEPTNO 102030 29 30 使用where子句 使用select语句中的where子句可以限制数据量 实际上 select语句中的where子句可以用来指定检索数据的条件 where子句的语法格式如下 select distinct column name alias fromtablenamewherecondition expression以上的where子句用来指定检索到的数据应该满足指定的条件 参数condition expression就是用于指定条件 在where子句的条件中 可以比较各种列值 文字串 算术表达式 函数等内容 使用WHERE子句 SQL SELECTename job deptno2FROMemp3WHEREjob CLERK ENAMEJOBDEPTNO JAMESCLERK30SMITHCLERK20ADAMSCLERK20MILLERCLERK10 31 字符串和日期 字符串和日期要用单引号扩起来字符串是大小写敏感的 日期值是格式敏感的缺省的日期格式是 DD MON YY SQL SELECTename job deptno2FROMemp3WHEREename JAMES SQL SELECT FROMempWHEREhiredate 01 1月 82 SQL SELECT FROMempWHEREhiredate to date 1982 01 01 YYYY MM DD 32 比较运算符 运算符 含义等于大于大于等于小于小于等于不等于 33 使用比较运算符 SQL SELECTename sal comm2FROMemp3WHEREsal comm ENAMESALCOMM MARTIN12501400 34 其它的比较运算符 运算符BETWEEN AND IN list LIKEISNULL 含义在两值之间 包含 匹配列出的值匹配一个字符模式是空值 35 使用BETWEEN运算符 ENAMESAL MARTIN1250TURNER1500WARD1250ADAMS1100MILLER1300 SQL SELECTename sal2FROMemp3WHEREsalBETWEEN1000AND1500 使用BETWEEN运算符显示某一值域范围的记录 36 使用IN运算符 使用IN运算符获得匹配列表值的记录 SQL SELECTempno ename sal mgr2FROMemp3WHEREmgrIN 7902 7566 7788 EMPNOENAMESALMGR 7902FORD300075667369SMITH80079027788SCOTT300075667876ADAMS11007788 37 使用LIKE运算符 使用LIKE运算符执行通配查询查询条件可包含文字字符或数字 可表示零或多个字符 可表示一个字符 SQL SELECTename2FROMemp3WHEREenameLIKE S 38 使用LIKE运算符 使用组合方式匹配字符Youcancombinepatternmatchingcharacters 使用ESCAPE标识符来查找带特殊符号的字符号 SQL SELECTename2FROMemp3WHEREenameLIKE A ENAME JAMESWARD SQL SELECTename2FROMemp3WHEREenameLIKE a escape a 39 使用ISNULL运算符 查询包含空值的记录 SQL SELECTename mgr2FROMemp3WHEREmgrISNULL ENAMEMGR KING 40 逻辑运算符 41 使用AND运算符 AND需要条件都是TRUE SQL SELECTempno ename job sal2FROMemp3WHEREsal 11004ANDjob CLERK EMPNOENAMEJOBSAL 7876ADAMSCLERK11007934MILLERCLERK1300 42 使用OR运算符 OR需要条件之一是TRUE SQL SELECTempno ename job sal2FROMemp3WHEREsal 11004ORjob CLERK EMPNOENAMEJOBSAL 7839KINGPRESIDENT50007698BLAKEMANAGER28507782CLARKMANAGER24507566JONESMANAGER29757654MARTINSALESMAN1250 14rowsselected 43 使用NOT运算符 SQL SELECTename job2FROMemp3WHEREjobNOTIN CLERK MANAGER ANALYST ENAMEJOB KINGPRESIDENTMARTINSALESMANALLENSALESMANTURNERSALESMANWARDSALESMAN 44 优先级规则 括号将跨越所有优先级规则 次序运算符1所有的比较运算2NOT3AND4OR 45 优先级规则 ENAMEJOBSAL KINGPRESIDENT5000MARTINSALESMAN1250ALLENSALESMAN1600TURNERSALESMAN1500WARDSALESMAN1250 SQL SELECTename job sal2FROMemp3WHEREjob SALESMAN 4ORjob PRESIDENT 5ANDsal 1500 46 优先级规则 ENAMEJOBSAL KINGPRESIDENT5000ALLENSALESMAN1600 使用扩号提高优先级 SQL SELECTename job sal2FROMemp3WHERE job SALESMAN 4ORjob PRESIDENT 5ANDsal 1500 47 48 使用orderby子句 在前面介绍的数据检索技术中 只是把数据库中的数据直接取出来 这时 在结果集中数据的排列顺序由数据的存储顺序决定 但是 这种存储顺序经常不符合实际的各种业务需要 因此需要对数据库中的数据在检索时进行排序 在select语句中 可以使用orderby子句执行数据排序操作 使用orderby子句的语法格式如下 orderby column name expression asc desc 49 排序是昂贵的操作 从开始就考虑优化排序往往花费大量的时间及资源磁盘排序比内存排序慢14000倍 ORDERBY子句 使用ORDERBY子句将记录排序ASC 升序 缺省DESC 降序ORDERBY子局在SELECT指令的最后 SQL SELECTename job deptno hiredate2FROMemp3ORDERBYhiredate ENAMEJOBDEPTNOHIREDATE SMITHCLERK2017 DEC 80ALLENSALESMAN3020 FEB 81 14rowsselected 50 降序排序 SQL SELECTename job deptno hiredate2FROMemp3ORDERBYhiredateDESC ENAMEJOBDEPTNOHIREDATE ADAMSCLERK2012 JAN 83SCOTTANALYST2009 DEC 82MILLERCLERK1023 JAN 82JAMESCLERK3003 DEC 81FORDANALYST2003 DEC 81KINGPRESIDENT1017 NOV 81MARTINSALESMAN3028 SEP 81 14rowsselected 51 使用列的别名排序 SQL SELECTempnoa enameb sal 12annsal2FROMemp3ORDERBYannsal EMPNOENAMEANNSAL 7369SMITH96007900JAMES114007876ADAMS132007654MARTIN150007521WARD150007934MILLER156007844TURNER18000 14rowsselected 52 多个列排序 通过ORDERBY列表的顺序来排序 你可使用不在SELECT序列上的列来排序 SQL SELECTename deptno sal2FROMemp3ORDERBYdeptno salDESC ENAMEDEPTNOSAL KING105000CLARK102450MILLER101300FORD203000 14rowsselected 53 什么是分组函数 分组函数运算每一组记录 每一组返回一个结果 EMP EMP表中工资的最高值 DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 MAX SAL 5000 54 55 聚合函数 合计计算 合计计算就是把一组数据作为计算的对象而执行的计算 这种计算包括求最大值 平均值 方差等 进行合计计算必须使用Oracle系统提供的合计函数 在Oracle系统中 可以使用的合计函数及其语法和功能如表6 5所示 使用AVG和SUM函数 AVG SAL MAX SAL MIN SAL SUM SAL 1400160012505600 在数字类型数据使用AVGandSUM函数 SQL SELECTAVG sal MAX sal 2MIN sal SUM sal 3FROMemp4WHEREjobLIKE SALES 56 使用MIN和MAX函数 MINandMAX适用于任何数据类型 SQL SELECTMIN hiredate MAX hiredate 2FROMemp MIN HIREDMAX HIRED 17 DEC 8012 JAN 83 57 使用COUNT函数 COUNT 6 SQL SELECTCOUNT 2FROMemp3WHEREdeptno 30 COUNT 返回表中的记录数 58 使用COUNT函数 COUNT expr 返回非空记录数 SQL SELECTCOUNT comm 2FROMemp3WHEREdeptno 30 COUNT COMM 4 COUNT distinctexpr 返回非重复记录数 59 分组函数和空值 分组函省略列中的空值 SQL SELECTAVG comm 2FROMemp AVG COMM 550 60 在分组函数中使用NVL函数 NVL函数迫使分组函数包括空值 SQL SELECTAVG NVL comm 0 2FROMemp AVG NVL COMM 0 157 14286 61 62 使用groupby和having子句 表中包含了许多数据 可以对这些数据按照某个指定的列进行分组 然后使用合计函数计算相应列的合计值 例如 如果需要检索最高薪水和最低薪水的雇员 并返回不同部门中最高薪水和最低薪水的雇员 那么就需要对表中的数据进行分组计算 在Oracle系统中 可以在select语句中使用groupby子句进行分组计算 使用分组计算的语法格式如下 groupbygroup by expressionhavinggroup condition组函数忽略空值 可以使用NVL NVL2 COALESCE函数处理空值结果集按groupby的列隐式按升序排列 数据分组 EMP EMP表中每一个部门的平均工资 DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 DEPTNOAVG SAL 102916 6667202175301566 6667 63 使用GROUPBY子句 所有用来分组的列在SELECT列表中不能使用分组函数 SQL SELECTdeptno AVG sal 2FROMemp3GROUPBYdeptno DEPTNOAVG SAL 102916 6667202175301566 6667 64 使用GROUPBY子句 GROUPBY列可以不在SELECT列表中 SQL SELECTAVG sal 2FROMemp3GROUPBYdeptno AVG SAL 2916 666721751566 6667 65 多列分组 EMP 按部门统计emp表中每种职位的工资总和 DEPTNOJOBSAL 10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250 JOBSUM SAL CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600 DEPTNO 101010202020303030 66 在多列上使用GROUPBY子句 SQL SELECTdeptno job sum sal 2FROMemp3GROUPBYdeptno job DEPTNOJOBSUM SAL 10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900 9rowsselected 67 使用分组函数的非法查询 如果没有GROUPBY子句SELECT列表中的不能同时出现列和合计函数 SQL SELECTdeptno COUNT ename 2FROMemp SELECTdeptno COUNT ename ERRORatline1 ORA 00937 notasingle groupgroupfunction 68 使用分组函数时的非法查询 不能使用WHERE子句限定组可使用HAVING子句限定分组之后的结果 SQL SELECTdeptno AVG sal 2FROMemp3WHEREAVG sal 20004GROUPBYdeptno WHEREAVG sal 2000 ERRORatline3 ORA 00934 groupfunctionisnotallowedhere 69 限定组的结果 查询部门的最高工资大于 2900 EMP DEPTNOSAL 1024501050001013002080020110020300020300020297530160030285030125030950301500301250 DEPTNOMAX SAL 105000203000 70 限定组的结果 HAVING子句 使用HAVING子句限定组记录被分组应用组函数匹配HAVING子句的组被显示 SELECTcolumn group functionFROMtable WHEREcondition GROUPBYgroup by expression HAVINGgroup condition ORDERBYcolumn 71 使用HAVING子句 SQL SELECTdeptno max sal 2FROMemp3GROUPBYdept