SQL中的数据处理语言.ppt

实用数据库技术 第4章SQL中的数据处理语言 学习内容 创建 更改及删除数据事务检索数据Where子句聚集查询结果子查询表连接 概述 本章我们将学习了SQL中的数据处理语句 语句 也叫数据处理语言 DML 包括 创建数据的INSERT语句 更改数据的UPDATE语句 删除数据的DELETE语句及检索数据的SELECT INSERT语句 语法INSERTINTOtable dame column list VALUES value list table name是表名称 记录将要添加到该表中 INSERT语句示例1 Studios表的INSERT语句 INSERTINTOStudiosVALUES 1 Giant LosAngeles CA INSERT语句示例2 Studios表中的列分别是 studio id name city和state 因为这些值都按照上述顺序包括在VALUES子句中 所以INSERT语句可以正常工作 INSERTINTOStudios city state name studio id VALUES Burbank CA MPM 2 INSERT语句小结 在多数数据库中 表中的列都按照它们创建的顺序出现 当使用CREATETABLE创建新的表时 列的顺序将保持为它们在原始语句中指定的顺序 UPDATE语句 UPDATE语句用来对表中现有的行作改动 UPDATE语句的结构如下 UPDATEtableSETcolumn value WHEREcondition UPDATE语句 UPDATE语句用来对表中现有的行作改动 UPDATE语句的结构如下 UPDATEtableSETcolumn value WHEREcondition UPDATE语句 UPDATE语句有3个部分 第一 必须指定要更新哪一个表 该语句的第二部分是SET子句 应当指定其中要更新的列和要插入的值 最后 WHERE子句可以用来指定表中哪些行将要更新 UPDATE语句示例1 更改某工作室的城市和州的UPDATE语句 UPDATEStudiosSETcity NewYork state NY WHEREstudio id 11rowupdated UPDATE语句示例1 可以看到 在SET子句中 将city和state字段都进行了更改 WHERE子句表明只有studioID为1的行才能被更新 在编写只对表中某一行产生影响的UPDATE语句时 在WHERE子句中使用主关键字来确保只有一行受到改变的影响往往是一个好办法 如果忽略UPDATE语句中的WHERE子句 那么在更新表中的所有行都将受到该语句的影响 UPDATE语句示例2 用UPDATE来更改表中所有行的语句 UPDATEStudiosSETstate AK 2rowsupdated SELECT FROMStudiosSTUDIO IDNAMECITYSTATE1GiantNewYorkAK2MPMBurbankAK DELETE语句 DELETE语句也可以用来将记录从表中删除 DEILETE语句的结构非常简单 DELETEFROMtable WHEREcondition DELETE语句 可选的WHERE子句可用来限制DELETE语句删除的行数 如果忽略WHERE子句 表中所有的行都会被删除 通过使用WHERE子句 可以指定要想删除每行所必须满足的条件 DELETE语句示例 删除Studios表中所有的行 DELETEFROMStudiosWHEREstate AK 2rowsdeleted 检索数据 SELECT语句 SELECT语句由大量子句组成 其中的一些是可选项 本节我们讨论SELECT语句的基本子句 下面是简单的SELECT语句的句法 SELECTselect listFROMtable WHEREcondition 指定要检索的列 SELECT语句包括了一个WHERE子句 这是一个可选元素 它限制了查询返回的那些行 检索DEPT表中所有行的SELECT语句 指定要检索的列示例1 检索DEPT表中所有行的SELECT语句 SELECTdeptno dnameFROMDEPTDEPTNODNAME 10ACCOUNTING20RESEARCH30SALES40OPERATIONS 指定要检索的列示例2 检索DEPT表中所有行的SELECT语句 SELECTdname deptnoFROMDEPTDNAMEDEPTNO ACCOUNTING10RESEARCH20SALES30OPERATIONS40 使用 来选定所有的列 因此不必像下面这样输入 SELECTdeptno dname locFROMDEPT只需要这样输入 SELECT FROMDEPT 使用 来选定所有的列总结 使用SELECT 的查询结果按照列最初输入CREATETABLE语句中的顺序来列出所有的列 在选定的数据上完成计算 利用SQL可以轻松地在数据库中的数据上完成计算并将计算结果包括在查询输出中 要实现上述操作 只需要用包含数字 列名称或二者的数学表达式来代替希望选定的列的清单中的标准输入即可 将表达式包括在SELECT语句中 数字 数学表达式和字符串全都可以包括在SELECT语句中 SELECT HelloWorld 2 5 enameFROMEMP HELLOWORLD 2 5ENAME HelloWorld 7SMITHHelloWorld 7ALLENHelloWorld 7WARDHelloWorld 7JONES 包含了数学表达式的查询 在表达式中使用列名称 可以将列名称作为所使用的表达式的组成部分包括在SELECT语句中 SELECTename sal sal 1 5FROMEMPENAMESALSAL 1 5 SMITH8001200ALLEN16002400WARD12501875JONES29754462 5 包括用某列中的数据所进行的计算的查询 SQL支持的算术运算符 SQL中可用的各种算术运算符号 SQL支持的算术运算符小结 在SQL中 括号的优先权最高 其次是乘除 再其次是加减 乘除具有相同的优先权 加减具有相同的优先权 因此 乘除或加减都可以用在同一表达式中 具有相同优先权的运算符按从左到右的顺序来计算 对Name列和表达式使用AS 可以使用AS关键字来为查询中的列和表达式分配名称 这些名称既可以用来改善查询输出的外观 也可以用来为一般情况下名称所不能引用的表达式分配名称 列和表达式使用AS分配别名 SELECTexpr ASalias expr2 ASalias FROMtable WHEREcondition AS对表达式重新命名示例 使用AS对表达式重新命名 SELECTename salASSALARY sal 2AS DOUBLESALARY FROMEMPENAMESALARYDOUBLESALARY SMITH8001600ALLEN16003200WARD12502500JONES29755950 AS对表达式重新命名小结 别名如果是单个单词 字母中间没有空格 命名 可以加双引号也可以不加 例如 SALARY和 SALARY 是相同的 但是 如果是由多个单词组成的 单词之间有空格 必须加双引号 例如 DOUBLESALARY 对查询结果排序 关系数据库的一个特性是 表中列与行的顺序不重要 行与列都不需要访问 数据库也不按顺序来处理它们 这是一个使它们区别于平面文件和电子表的特性 这就意味着随便按照哪种顺序从数据库中检索记录都很简单 对查询结果排序 使用ORDERBY子句的SELECT语句的句法遵循下面这种格式 SELECTselect listFROMtable WHEREconditmon ORDERBY column alias position ASC DESC 对查询结果排序示例1 使用ORDERBY子句按照sal列升序排列 SELECTename salASSALARYFROMEMPWHEREsal 3000ORDERBYsalASCENAMESALARY SMITH800JAMES950ADAMS1100 对查询结果排序示例2 使用ORDERBY子句按照sal列升序排列 SELECTename salASSALARYFROMEMPWHEREsal 3000ORDERBYsalDESCENAMESALARY JONES2975BLAKE2850CLARK2450 Where子句概述 WHERE子句用来决定哪些行应当包括在查询结果中 WHERE子句可以作为筛选程序 将那些不合标准的行删除 保留那些合乎标准的行 当然 重要的一点是要记住 WHERE子句不仅仅和SELECT语句一起使用 它还可以用来选择UPDATE和DELETE语句影响的行 不管使用上述3条语句中的哪一条 WHERE子句的用法都是相同的 比较的运算符 SQl标准支持的比较运算符 比较的运算符总结 需要注意的一件事 不仅对于 而且对于所有运算符来说都应注意的是 在检验数字数据时 不必使用单引号标记来包围被比较的数据 比较的运算符示例1 不加单引号标记检索 SELECTename salFROMEMPwheresal 950ENAMESAL JAMES950 比较的运算符示例2 加单引号标记检索 SELECTename salFROMEMPwheresal 950 ENAMESAL JAMES950 逻辑运算符 SQL提供3种逻辑运算符 允许将多个表达式包括在WHERE子句中 实际上 其中的两个专门用来将多个表达式包括在WHERE子句中 另外一个用来对表达式 或表达式的集合 的结果求反 这3个运算符是 ANDORNOT 逻辑运算符 逻辑运算符示例1 AND运算符 SELECTempno ename sal jobFROMempWHEREjob SALESMAN ANDsal 3000EMPNOENAMESALJOB 7499ALLEN1600SALESMAN7521WARD1250SALESMAN7654MARTIN1250SALESMAN7844TURNER1500SALESMAN 逻辑运算符示例2 OR运算符 SELECTempno ename sal jobFROMempWHEREjob SALESMAN ORsal 3000EMPNOENAMESALJOB 7369SMITH800CLERK7499ALLEN1600SALESMAN7521WARD1250SALESMAN7566JONES2975MANAGER7934MILLER1300CLERK 逻辑运算符示例1 NOT运算符 SELECTempno ename sal jobFROMempWHERENOTjob SALESMAN ANDsal 3000EMPNOENAMESALJOB 7369SMITH800CLERK7566JONES2975MANAGER7698BLAKE2850MANAGER7782CLARK2450MANAGER7876ADAMS1100CLERK 运算符先后顺序 SQL支持的布尔运算符和数学运算符 按照从高到低的运算顺序排列 IN子句 SQL语言中还有另外一些子句可以用来简化查询 否则查询将使用多个布尔运算符 这些子句的一个例子是IN子句 它取代了多个OR运算符 用来检查一组值中是否有一个出现在具体的某列中 IN运算符可以决定被检验的值是否等于某组值中的一个 IN子句 IN子句的结构 SELECTselect listFROMtableWHEREcolumn NOT IN value list IN子句总结 经过检验可以看出 有的值与所提供的清单中的某个值相匹配 IN可以选定这些值所在的所有行 有的值与清单中任何一个值都不匹配 而NOTIN则可以选定这些值所在的所有行 IN子句示例1 使用IN子句的查询 SELECTempno ename sal jobFROMempWHEREjobIN CLERK MANAGER SALESMAN EMPNOENAMESALJOB 7369SMITH800CLERK7499ALLEN1600SALESMAN7521WARD1250SALESMAN7566JONES2975MANAGER IN子句示例2 使用NOTIN子句的查询 SELECTempno ename sal jobFROMempWHEREjobNOTIN CLERK MANAGER SALESMAN EMPNOENAMESALJOB 7788SCOTT3000ANALYST7839KING5000PRESIDENT7902FORD3000ANALYST BETWEEN子句 BETWEEN子句和它的对立面 NOTBETWEEN 可以用来查看一个值是否在某个具体的值域中 下面是一个范例 表明怎样来编写BETWEEN子句 SELECTselect listFROMtableWHEREcolumn NOT BETWEENlower valueANDupper value BETWEEN子句小结 BETWEEN子句被包括在内了 用来指定检验范围的上下限也包括在值域中 NOTBETWEEN用来决定一个值是否超出指定的值域之外 NOTBETWEEN与BETWEEN正相反 它把值域上下限看做包括在值域之内 如果被检验的值等于置于上下线值中的一个的话 表达式就不能判定为 真 BETWEEN子句示例1 使用BETWEEN子句的查询 SELECTempno ename sal jobFROMempWHEREsalBETWEEN2000AND3000EMPNOENAMESALJOB 7566JONES2975MANAGER7698BLAKE2850MANAGER7782CLARK2450MANAGER7788SCOTT3000ANALYST BETWEEN子句示例2 使用BETWEEN子句的查询 SELECTempno ename sal jobFROMempWHEREsalNOTBETWEEN2000AND3000EMPNOENAMESALJOB 7369SMITH800CLERK7499ALLEN1600SALESMAN7521WARD1250SALESMAN7654MARTIN1250SALESMAN 使用LIKE来匹配字符串各部分 LIKE子句可用来创建与字符串模式相匹配的简单表达式 在需要查找具有某些相同内容的字符串或者已知字符串的某一部分 但不知道整个字符串时 可以用它来解决问题 LIKE子句也可以用NOT运算符来求反 使用LIKE来匹配字符串各部分 LIKE运算符可以这样使用 FROMtableWHEREcolumn NOT LIKE pattern 使用LIKE小结 SQL标准对于模式匹配表达式提供了两种通配符 它与任意的字符集合都匹配 包括不包含字符的字符集合 和 它与任意单个字符相匹配 程序清单4 22中的查询将会返回所有以 M 开头的人名的数据 使用LIKE示例1 使用LIKE子句的查询 SELECTempno ename sal jobFROMempWHEREenameLIKE M EMPNOENAMESALJOB 7654MARTIN1250SALESMAN7934MILLER1300CLERK DISTINCT选择惟一值 雇员表emp中包含了相同工种 job 如果需要一个关于数据库中所有工种的简单的清单 那么类似于程序下面的查询将不起作用 程序清单4 25 SELECTjobFROMempJOB CLERKSALESMANSALESMANMANAGER DISTINCT选择惟一值 可以看到 所有的工种都列在清单中 但有的工种多次重复出现 SQL提供了DISTINCT运算符 可以将程序清单中的重复值去除 一般来说 DISTINCT运算符的使用方法如下 SELECT DISTINCT select listFROMtable WHERexpression ORDERBYexpression DISTINCT选择惟一值 使用DISTINCT运算符 删除了的重复值 只返回工种每一个值的一次出现 程序清单4 26 SELECTDISTINCTjobFROMempJOB ANALYSTCLERKMANAGERPRESIDENTSALESMAN DISTINCT示例 如果程序清单中提供了多列 这些列中值的每一种单独的组合都将被选定 程序清单4 25中包含了数据库中所有的工资和工种数据 程序清单4 26中包含了使用DISTINCT删除的重复的工作工资和工种的清单 DISTINCT示例 检索数据库中所有的工资和工种 SELECTsal jobFROMempSALJOB 1250SALESMAN1250SALESMAN1500SALESMAN1100CLERK1100CLERK DISTINCT示例 检索数据库中所有的工资和工种 SELECTDISTINCTsal jobFROMempSALJOB 1100CLERK1250SALESMAN1500SALESMAN 聚集函数 有许多的聚集函数可以在SQL中使用 它们不是要在数据库中的单个字段内容上操作 而是在查询返回的一组值上操作 举例来说 它们允许计算表中与具体标准相匹配的行的数目 也允许你计算表某一列的值的平均数 聚集函数 这些函数可以处理表中所有的行 用WHERE语句选择的一个表中行的子集或用GROUPBY子句组织的一组所选数据 聚集函数的使用方法如下 SELECTfunction column FROMtable WHEREcondition 聚集函数 在使用这些函数时 通常选择清单只由单独的一列 将要计算其中的集合值 和所返回的单独的一 行 数据组成 下面我们介绍COUNT SUM AVG MIN和MAX函数 COUNT 函数 COUNT 计算查询返回的行的数目 程序清单4 27计算员工人数 SELECTCOUNT FROMempCOUNT 14 SUM函数和AVG函数 SUM函数仅仅用来叠加行中所有的值并返回结果 AVG 函数用来计算值的平均数 在统计数据中即算术平均值 SUM 函数和AVG 只能处理包含数字数据的列 SUM函数和AVG函数示例1 使用SUM 叠加一列中的值 SELECTSUM sal FROMempSUM SAL 28825 SUM函数和AVG函数示例2 使用AVG 叠加一列中的值 SELECTAVG sal FROMempAVG SAL 2058 92857 SUM函数和AVG函数小结 在用SUM 函数或AVG 函数计算含有空值的列时 空值是被忽略的 使用AVG 函数时 空值不包括在总数或值的数目 值的总数将除以这个数目 中 MIN 和MAX 函数 MIN 函数用来确定一组值中最小值 MAX 函数用来确定一组值中最大值 这两个函数可以用在任何数据类型中 在某一列中可以得到其他值时 MIN 函数和MAX 函数将不返回空值 空值作为未知对待 因而不能认为它大于或小于其他任何值 MIN 和MAX 函数示例1 MIN 函数使用范例 它将要查找员工表 emp 中工资最低的员工工资 SELECTMIN sal FROMempMIN SAL 800 MIN 和MAX 函数示例2 MAX 函数使用范例 它将要查找员工表 emp 中工资最高的员工工资 SELECTMAX sal FROMempMAX SAL 5000 GROUPBY子句 GROUPBY子句根据数据库中每一列的内容对查询结果分类 从这方面来说 它与DISTINCT有一些相似 因为它为你打算用来对数据进行分组的列中的独一无二的值都返回一行 然而与DISTINCT不同的是 如果在表中其他列里使用聚集函数 GROUPBY将根据分组后的列的值对各行的组合使用这个功能 GROUPBY子句 使用GROUPBY子句的句法如下 SELECTselect listFROMtable WHEREcondition GROUPBYgroup by list ORDERBYorder by list GROUPBY子句示例1 用来计算emp表中的所有工资的SUM 函数 SELECTSUM sal FROMempSUM SAL 28825 GROUPBY子句示例2 用来计算emp表中的每一个部门的工资合计的SUM 函数 SELECTdeptno SUM sal FROMempGROUPBYdeptnoDEPTNOSUM SAL 1085502010875309400 GROUPBY子句小结 GROUPBY子句可以和任意的聚集函数一起使用 COUNT SUM AVG MIN 和MAX 都能产生预期的效果 使用HAVING筛选查询结果 当使用GROUPBY语句时 SQL提供了另外一种筛选查询结果的方法 与WHERE子句在查询结果分组之前筛选查询结果不同的是 HAVING子句在对查询结果分组之后对结果进行筛选 HAVING示例 假设你希望查找所有的部门工资合计总不到10000部门 程序清单4 34所示 SELECTdeptno SUM sal FROMempGROUPBYdeptnoHAVINGSUM sal 10000DEPTNOSUM SAL 108550309400 HAVING示例1 假设你希望查找所有的部门工资合计总不到10000部门 程序清单4 34所示 SELECTdeptno SUM sal FROMempGROUPBYdeptnoHAVINGSUM sal 10000DEPTNOSUM SAL 108550309400 HAVING示例2 使用HAVING GROUPBY和ORDERBY子句的语句 SELECTdeptno SUM sal FROMempGROUPBYdeptnoHAVINGSUM sal 10000ORDERBYdeptnoDESCDEPTNOSUM SAL 309400108550 HAVING小结 可以看到 选择清单和HAVING子句中都使用了程序清单4 34中的聚集函数 一个常见的错误是在HAVING子句中单独使用列名 但是这样将会产生错误 选择清单和HAVING子句中表达式必须一致 没有在HAVING子句中出现的列名根本不能在选择清单中使用 HAVING子句出现在ORDERBY子句之前 但是在GROUPBY子句之后 子查询概念 有时 在SQL查询中实现具体目标的最简单方法就是在另一条查询中使用某条查询的输出 这种嵌套式查询作为子查询来引用 它们可以用在WHERE子句中帮助筛选数据 当数据存储在需要用在另一条查询某个位置处的具体表中时 可以使用子查询 它们基本上与嵌套相似 正如可以在另一个函数调用中嵌套函数调用一样 也可以在其他查询内部嵌套查询 子查询概念 从最简单的意义来看 子查询 subquery 是嵌套在另一个SQL语句中并提供嵌套它的语句所使用的数据的SELECT语句 子查询概念 IN表达式中的子查询 SELECTempno enameFROMempWHEREdeptnoIN SELECTdeptnoFROMdeptWHEREdname SALES EMPNOENAME 7499ALLEN7521WARD7654MARTIN7698BLAKE7844TURNER7900JAMES 外查询 子查询 子查询概念小结 子查询有两种类型 相关和无关 相关子查询要求在执行以前由外部查询返回的数据 子查询随后使用从外部查询接收到的数据来执行 由子查询返回的数据反向插入外部查询中进行比较 无关子查询在外部查询之前进行判定 由子查询返回的数据被外部查询使用 无关子查询比相关子查询简单 相关子查询本书不做介绍 返回值的清单的子查询 子查询可以用在两个不同的上下文中 即需要单个值的上下文和需要值的清单的上下文 在需要值的清单时 子查询可以返回任意数量的值 包括0 而且在查询中运行正常 返回值的清单的子查询 通常使用关键字IN NOTIN EXISTS NOTEXISTS ANY和ALL来进行子查询 返回值的清单的子查询示例 NOTIN表达式中的子查询 SELECTempno enameFROMempWHEREdeptnoNOTIN SELECTdeptnoFROMdeptWHEREdname SALES EMPNOENAME 7369SMITH7566JONES7782CLARK7788SCOTT 小结 用在WHERE子句中的子查询必须始终从单列中返回值 在于查询的选择清单中指定多列通常会产生错误 下面的写法错误的 SELECTempno enameFROMempWHEREdeptnoIN SELECTdeptno dnameFROMdeptWHEREdname SALES 返回单个值的子查询 使用聚集函数的子查询总是返回单个值 只要不使用GROUPBY子句 因此 通过使用聚集函数 可以使用伴随子查询的标准的比较运算符 返回单个值的子查询示例 查询使用AVG 聚集函数来查找所有工资超过平均工资的员工 SELECTempno enameFROMempWHEREsal SELECTAVG sal FROMemp EMPNOENAME 7566JONES7698BLAKE7782CLARK 嵌套子查询 子查询提供了无尽的嵌套 正如子查询可以嵌套在标准查询中一样 它也可以嵌套在另一个子查询中 对于所提供的嵌套来说 惟一的限制就是性能 随着对子查询一层接一层地嵌套 查询的性能也会不断下降 嵌套子查询示例 查出超出了平均工资的员工所在的部门 程序清单4 39使用了一个嵌套在另一个子查询中的子查询来解决这个问题 嵌套子查询示例 SELECTdnameFROMdeptWHEREdeptnoIN SELECTdeptnoFROMempWHEREsal SELECTAVG sal FROMemp DNAME ACCOUNTINGRESEARCHSALES 嵌套子查询 表连接 连接 join 就是将多个表中的数据结合在一起的查询 连接的分类 内连接 naturaljoin 外连接 outerjoin 左外连接右外连接全外连接 内连接 内连接 naturaljoin 也称自然连接 内连接有以下两种语法格式 第一种格式 SELECTcolumn listFROMtable1 table WHEREcondition 第二种格式 SELECTcolumn listFROMtable1 INNER JOINtable2ONtable1 column1 table2 column1 内连接实例 员工表 emp 数据部门表 dept 数据 内连接实例 代码清单 SELECTdept deptno dept dname emp empno emp enameFROMdept empWHEREdept deptno emp deptno或者SELECTdept deptno dept dname emp empno emp enameFROMdeptJOINempONdept deptno emp deptno 内连接实例 外连接 左外连接 左外连接就是将左表的所有记录分别与右表的每一条记录进行连接组合 结果集中除返回内部连接的记录以外 还在查询结果中显示出左表中不符合条件的记录并在右表的相应列中填上NULL值 由于bit类型不允许为NULL 就以0值填充 左外连接的语法格式为 SELECTcolumn listFROMtable1LEFT OUTER JOINtable2ONtable1 column1 table2 column1 左外连接实例 程序清单 SELECTdept deptno dept dname emp empno emp enameFROMdeptLEFTJOINempONdept deptno emp deptno 左外连接实例 右外连接 右外连接就是将左表的所有记录分别与右表的每一条记录进行连接组合 结果集中除返回内部连接的记录以外 还在查询结果中显示出右表中不符合条件的记录并在左表的相应列中填上NULL值 右外连接的语法格式为 SELECTcolumn listFROMtable1RIGHT OUTER JOINtable2ONtable1 column1 table2 column1 右外连接实例 程序清单 SELECTdept deptno dept dname emp empno emp enameFROMdeptRIGHTJOINempONdept deptno emp deptno 右外连接实例 表连接总结 有实际意义的表连接 要有连接条件的 而且连接条件之间是可以兼容的 程序清单4 44有连接条件 查出的数据为14条记录 字段 deptno 在Dept表中是主键 在Emp表中是外键 换句话说 表中的数据之所以有意义是因为它只包含两张表中相互有关系的记录 而纯粹由笛卡尔积组成的表包含了许多额外的 无意义的记录如程序清单4 4所示 程序清单4 44EMP表和DEPT表有连接条件SELECTDept deptno Dept dname Emp empno Emp enameFROMDept EmpWHEREDept deptno Emp deptnoDEPTNODNAMEEMPNOENAME 20RESEARCH7369SMITH 10ACCOUNTING7934MILLER已选择14行 程序清单4 45EMP表和DEPT表无连接条件SELECTDept deptno Dept dname Emp empno Emp enameFROMDept EmpDEPTNODNAMEEMPNOENAME 10ACCOUNTING7369SMITH20RESEARCH7369SMITH 30SALES7934MILLER已选择56行