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=NYWHEREstudio_id=11rowupdated.,UPDATE语句示例1,可以看到，在SET子句中，将city和state字段都进行了更改。WHERE子句表明只有studioID为1的行才能被更新。在编写只对表中某一行产生影响的UPDATE语句时，在WHERE子句中使用主关键字来确保只有一行受到改变的影响往往是一个好办法。如果忽略UPDATE语句中的WHERE子句，那么在更新表中的所有行都将受到该语句的影响。,UPDATE语句示例2,用UPDATE来更改表中所有行的语句,UPDATEStudiosSETstate=AK2rowsupdated.,SELECT*FROMStudiosSTUDIO_IDNAMECITYSTATE1GiantNewYorkAK2MPMBurbankAK,DELETE语句,DELETE语句也可以用来将记录从表中删除。DEILETE语句的结构非常简单：DELETEFROMtableWHEREcondition,DELETE语句,可选的WHERE子句可用来限制DELETE语句删除的行数。如果忽略WHERE子句，表中所有的行都会被删除。通过使用WHERE子句，可以指定要想删除每行所必须满足的条件。,DELETE语句示例,删除Studios表中所有的行,DELETEFROMStudiosWHEREstate=AK2rowsdeleted.,检索数据SELECT语句,SELECT语句由大量子句组成，其中的一些是可选项。本节我们讨论SELECT语句的基本子句。下面是简单的SELECT语句的句法：SELECTselect_listFROMtableWHEREcondition,指定要检索的列,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语句中。,SELECTHelloWorld!,2+5,enameFROMEMPHELLOWORLD!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分配别名,SELECTexprASalias，expr2ASalias，FROMtableWHEREcondition,AS对表达式重新命名示例,使用AS对表达式重新命名,SELECTename,salASSALARY,sal*2ASDOUBLESALARYFROMEMPENAMESALARYDOUBLESALARY-SMITH8001600ALLEN16003200WARD12502500JONES29755950,AS对表达式重新命名小结,别名如果是单个单词(字母中间没有空格)命名，可以加双引号也可以不加，例如：SALARY和SALARY是相同的。但是，如果是由多个单词组成的（单词之间有空格）必须加双引号，例如：DOUBLESALARY。,对查询结果排序,关系数据库的一个特性是：表中列与行的顺序不重要。行与列都不需要访问，数据库也不按顺序来处理它们。这是一个使它们区别于平面文件和电子表的特性。这就意味着随便按照哪种顺序从数据库中检索记录都很简单。,对查询结果排序,使用ORDERBY子句的SELECT语句的句法遵循下面这种格式：SELECTselect_listFROMtableWHEREconditmonORDERBYcolumn|alias|positionASC|DESC),对查询结果排序示例1,使用ORDERBY子句按照sal列升序排列,SELECTename,salASSALARYFROMEMPWHEREsal3000ORDERBYsalASCENAMESALARY-SMITH800JAMES950ADAMS1100,对查询结果排序示例2,使用ORDERBY子句按照sal列升序排列,SELECTename,salASSALARYFROMEMPWHEREsal3000ORDERBYsalDESCENAMESALARY-JONES2975BLAKE2850CLARK2450,Where子句概述,WHERE子句用来决定哪些行应当包括在查询结果中。WHERE子句可以作为筛选程序，将那些不合标准的行删除，保留那些合乎标准的行。当然，重要的一点是要记住：WHERE子句不仅仅和SELECT语句一起使用，它还可以用来选择UPDATE和DELETE语句影响的行。不管使用上述3条语句中的哪一条，WHERE子句的用法都是相同的。,比较的运算符,SQl标准支持的比较运算符：,比较的运算符总结,需要注意的一件事，不仅对于“=”，而且对于所有运算符来说都应注意的是：在检验数字数据时，不必使用单引号标记来包围被比较的数据。,比较的运算符示例1,不加单引号标记检索,SELECTename,salFROMEMPwheresal=950ENAMESAL-JAMES950,比较的运算符示例2,加单引号标记检索,SELECTename,salFROMEMPwheresal=950ENAMESAL-JAMES950,逻辑运算符,SQL提供3种逻辑运算符，允许将多个表达式包括在WHERE子句中。实际上，其中的两个专门用来将多个表达式包括在WHERE子句中，另外一个用来对表达式(或表达式的集合)的结果求反。这3个运算符是：ANDORNOT,逻辑运算符,逻辑运算符示例1,AND运算符：,SELECTempno,ename,sal,jobFROMempWHEREjob=SALESMANANDsal3000EMPNOENAMESALJOB-7499ALLEN1600SALESMAN7521WARD1250SALESMAN7654MARTIN1250SALESMAN7844TURNER1500SALESMAN,逻辑运算符示例2,OR运算符：,SELECTempno,ename,sal,jobFROMempWHEREjob=SALESMANORsal3000EMPNOENAMESALJOB-7369SMITH800CLERK7499ALLEN1600SALESMAN7521WARD1250SALESMAN7566JONES2975MANAGER7934MILLER1300CLERK,逻辑运算符示例1,NOT运算符：,SELECTempno,ename,sal,jobFROMempWHERENOTjob=SALESMANANDsal3000EMPNOENAMESALJOB-7369SMITH800CLERK7566JONES2975MANAGER7698BLAKE2850MANAGER7782CLARK2450MANAGER7876ADAMS1100CLERK,运算符先后顺序,SQL支持的布尔运算符和数学运算符，按照从高到低的运算顺序排列:,IN子句,SQL语言中还有另外一些子句可以用来简化查询(否则查询将使用多个布尔运算符)。这些子句的一个例子是IN子句，它取代了多个OR运算符，用来检查一组值中是否有一个出现在具体的某列中，IN运算符可以决定被检验的值是否等于某组值中的一个。,IN子句,IN子句的结构：SELECTselect_listFROMtableWHEREcolumnNOTIN(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_listFROMtableWHEREcolumnNOTBETWEENlower_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运算符可以这样使用：FROMtableWHEREcolumnNOTLIKEpattern,使用LIKE小结,SQL标准对于模式匹配表达式提供了两种通配符：%(它与任意的字符集合都匹配，包括不包含字符的字符集合)和_(它与任意单个字符相匹配)。程序清单4.22中的查询将会返回所有以“M”开头的人名的数据。,使用LIKE示例1,使用LIKE子句的查询,SELECTempno,ename,sal,jobFROMempWHEREenameLIKEM%EMPNOENAMESALJOB-7654MARTIN1250SALESMAN7934MILLER1300CLERK,DISTINCT选择惟一值,雇员表emp中包含了相同工种（job），如果需要一个关于数据库中所有工种的简单的清单，那么类似于程序下面的查询将不起作用。程序清单4.25：,SELECTjobFROMempJOB-CLERKSALESMANSALESMANMANAGER,DISTINCT选择惟一值,可以看到，所有的工种都列在清单中，但有的工种多次重复出现。SQL提供了DISTINCT运算符，可以将程序清单中的重复值去除。一般来说，DISTINCT运算符的使用方法如下：SELECTDISTINCTselect_listFROMtableWHERexpressionORDERBYexpression,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)FROMtableWHEREcondition,聚集函数,在使用这些函数时，通常选择清单只由单独的一列(将要计算其中的集合值)和所返回的单独的一“行”数据组成。下面我们介绍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_listFROMtableWHEREconditionGROUPBYgroup_by_listORDERBYorder_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)(SELECTAVG(sal)FROMemp)DNAME-ACCOUNTINGRESEARCHSALES,嵌套子查询,表连接,连接(join)就是将多个表中的数据结合在一起的查询。连接的分类：内连接(naturaljoin)外连接(outerjoin)左外连接右外连接全外连接,内连接,内连接（naturaljoin）也称自然连接,内连接有以下两种语法格式：,第一种格式：SELECTcolumn_listFROMtable1，tableWHEREcondition第二种格式：SELECTcolumn_listFROMtable1INNERJOINtable2ONtable1.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,内连接实例,外连接,左外连接,左外连接就是将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除返回内部连接的记录以外，还在查询结果中显示出左表中不