数据库查询技术ppt.ppt

《数据库查询技术ppt.ppt》由会员分享，可在线阅读，更多相关《数据库查询技术ppt.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第4 4章章 查询技术查询技术SELECT 主要子句SELECTALL|DISTINCTTOPexpressionPERCENTWITHTIESINTOnew_tableFROM,.nWHEREGROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUPHAVINGORDERBYorder_expressionASC|DESCCOMPUTEAVG|COUNT|MAX|MIN|SUM(expression),.nBYexpression,.nSELECT 主要子句参数说明如下：vSELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、

2、列表、变量等。vINTO子句用于指定所要生成的新表的名称。vFROM子句用于指定要查询的表或者视图，最多可以指定14个表或者视图，用逗号相互隔开。vWHERE子句用来限定查询的范围和条件。vGROUPBY子句是分组查询子句。vHAVING子句用于指定分组子句的条件。vGROUPBY子句、HAVING子句和集合函数一起可以实现对每个组生成一行和一个汇总值。vORDERBY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。vASC表示升序排列，DESC表示降序排列。vCOMPUTE子句使用集合函数在查询的结果集中生成汇总行。vCOMPUTEBY子句用于增加

3、各列汇总行。4.1 基本SELECT语句4.1.1投影查询4.1.2条件查询4.1.1 投影查询v最基本的SELECT语句仅有两个部分：要返回的列，和这些列源于的表。也就是说查询均为不使用WHERE子句的无条件查询，也称作投影查询。v例4-1查询全体学生的学号、姓名和年龄。程序清单如下：SELECTSNO,SN,AGEFROMSv例4-2查询学生的全部信息。程序清单如下：SELECT*FROMSv注意：用*表示表的全部列名，而不必逐一列出。4.1.1 投影查询v例4-3查询选修了课程的学生号。程序清单如下：SELECTDISTINCTSNOFROMSC注意：应用DISTINCT消除查询结果以某

4、列为依据的重复行。上例中，sc表中相同学号（SNO）的纪录只保留第一行，余下的具有相同学号的记录将从查询结果中清除。也就是每个同学保留一条选课纪录。v另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字，如下例。v例4-4查询全体学生的姓名、学号和年龄。程序清单如下：SELECTSNNAME,SNO,AGEFROMSv注意：NAME为SN的别名，这里我们改变了列的显示顺序。4.1.2 条件查询v当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：列名；比较运算符；列名、常数。v条件查询又可分为以下几方面内容

5、：1、比较大小和确定范围；2、部分匹配查询；3、空值查询；4、查询的排序4.1.2 条件查询1、比较大小和确定范围v例4-5查询选修课程号为C1的学生的学号和成绩。程序清单如下：SELECTSNO,SCOREFROMSCWHERECNO=C1v例4-4查询成绩高于85分的学生的学号、课程号和成绩。程序清单如下：SELECTSNO,CNO,SCOREFROMSCWHERESCORE854.1.2 条件查询1、比较大小和确定范围v当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括

6、号改变优先级。v例4-7查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。程序清单如下：SELECTSNO，CNO，SCOREFROMSCWHERE（CNO=C1ORCNO=C2）ANDSCORE=85SQL语句中也有一个特殊的BETWEEN运算符，用于检查某个值是否在两个值之间（包括等于两端的值）。v例4-8查询工资在1000至1500之间的教师的教师号、姓名及职称。程序清单如下：SELECTTNO,TN,PROFFROMTWHERESALBETWEEN1000AND1500v上面SQL语句等价于以下语句：SELECTTNO,TN,PROFFROMTWHERESAL=1000

7、ANDSAL=15004.1.2 条件查询1、比较大小和确定范围v注意：在SELECT语句中可利用“IN”操作来查询属性值属于指定集合的元组。利用“NOTIN”可以查询指定集合外的元组。如下面两个例子。v例4-10查询选修C1或C2的学生的学号、课程号和成绩。程序清单如下：SELECTSNO,CNO,SCOREFROMSCWHERECNOIN(C1,C2)v此语句也可以使用逻辑运算符“OR”实现。相应的程序清单如下：SELECTSNO,CNO,SCOREFROMSCWHERECNO=C1ORCNO=C24.1.2 条件查询2、部分匹配查询v当不知道完全精确的値时，用户还可以使用LIKE或NOT

8、LIKE进行部分匹配查询（也称模糊查询）。LIKE运算使我们可以使用通配符来执行基本的模式匹配。v使用LIKE运算符的一般格式为：LIKE字符串常量的字符可以包含如表4-2所示的通配符。通配符 说明_表示任意单个字符%表示任意长度的字符串 与特定范围（例如，a-f）或特定集（例如，abcdef）中的任意单字符匹配。与特定范围（例如，a-f）或特定集（例如，abcdef）之外的任意单字符匹配。表4-2通配符及说明 4.1.2 条件查询2、部分匹配查询v例4-12查询所有姓张的教师的教师号和姓名。程序清单如下：SELECTTNO,TNFROMTWHERETNLIKE张%v例4-13查询姓名中第二个

9、汉字是“力”的教师号和姓名。程序清单如下：SELECTTNO,TNFROMTWHERETNLIKE_力%4.1.2 条件查询3、空值查询v某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。v例4-15查询没有考试成绩的学生的学号和相应的课程号。程序清单如下：SELECTSNO,CNOFROMSCWHERESCOREISNULLv注意：这里的空值条件为ISNULL，不能写成SCORE=NULL。4.1.2 条件查

10、询4查询的排序v当需要对查询结果排序时，应该在SELECT语句中使用ORDERBY子句。ORDERBY子句包括了一个或多个用于指定排序顺序的列名，排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。ORDERBY子句必须出现在其他子句之后。vORDERBY子句支持使用多列。可以使用以逗号分隔的多个列作为排序依据：查询结果将先按指定的第一列进行排序，然后再按指定的下一列进行排序。v例4-14查询选修C1的学生学号和成绩，并按成绩降序排列。程序清单如下：SELECTSNO,SCOREFROMSCWHERECNO=C1ORDERBYSCOREDESC4.2 分组查询4.2.1聚合函数和GR

11、OUPBY子句4.2.2GROUPBY和WHERE子句、HAVING子句4.2.1 聚合函数和GROUP BY子句GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用GROUPBY子句，则只为SELECT语句报告一个聚合值。常用的聚合函数，如表4-3所示。函数名称MINMAXSUMAVGCOUNTCOUNT(*)功能求一列中的最小值求一列中的最大值按列计算值的总和按列计算平均值按列值计个数返回表中的所用行数表4-3常用的聚合函数 4.2.1 聚合函数和GROUP BY子句v例4-18通过查询求学号为S1学生的

12、总分和平均分。程序清单如下：SELECTSUM(SCORE)ASTotalScore,AVG(SCORE)ASAveScoreFROMSCWHERESNO=S1v注意：函数SUM和AVG只能对数值型字段进行计算。v例4-19通过查询求选修C1号课程的最高分、最低分及之间相差的分数程序清单如下：SELECTMAX(SCORE)ASMaxScore,MIN(SCORE)ASMinScore,MAX(SCORE)-MIN(SCORE)ASDiffFROMSCWHERE(CNO=C1)4.2.1 聚合函数和GROUP BY子句v例4-20通过查询求管理系学生的总数。程序清单如下：SELECTCOUNT

13、(SNO)FROMSWHEREDEPT=管理v例4-21通过查询求学校中共有多少个系。程序清单如下：SELECTCOUNT(DISTINCTDEPT)ASDeptNumFROMSv注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。v例4-22统计有成绩的学生的人数。程序清单如下：SELECTCOUNT(SCORE)FROMSCv注意：上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。4.2.1 聚合函数和GROUP BY子句v例4-23利用特殊函数COUNT(*)求工商管理学院学生的总数程序清单如下：SE

14、LECTCOUNT(*)FROMSWHEREDEPT=工商v注意：上例中，COUNT（*）用来统计元组的个数。此函数不消除重复行，也不允许使用DISTINCT关键字。v在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。如下例所示。v例4-24查询每位学生的学号及其选课的门数。程序清单如下：SELECTSNO,COUNT(*)ASC_NUMFROMSCGROUPBYSNOvGROUPBY子句按CNO的值分组，所有具有相同CNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。v例4-25统计各年度出生的雇员人数程序清单如下。Useadventurework

15、sSELECTDATEPART(year,birthdate)ASYear,COUNT(*)ASNumberOfemployeesFROMhumanresources.employeeGROUPBYDATEPART(year,birthdate)4.2.2GROUP BY 和 WHERE 子句、HAVING 子句v可以在包含GROUPBY子句的查询中使用WHERE子句。在完成任何分组之前，将消除不符合WHERE子句中的条件的行。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。v例4-24在分组查询中使用WHERE条件，查询工商学院的学生学号及平均成绩。程序清单如下：SELECT

16、sno,AVG(score)ASAverageScoreFROMscWHEREsno=(selectsnofromswheredept=工商)GROUPBYsnoORDERBYsnoGOv当在一个SQL查询中同时使用WHERE子句，GROUPBY 子句和HAVING子句时，其顺序是WHEREGROUPBYHAVING。vWHERE与HAVING子句的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后，但GROUPBY子句可没有HAVING子句。4.2.2GROUP BY 和 WHERE

17、子句、HAVING 子句v例4-27在分组查询中使用HAVING条件，查询平均成绩大于85的学生学号及平均成绩。程序清单如下：SELECTsno,AVG(score)ASAverageScoreFROMscGROUPBYsnoHAVINGAVG(score)85GOv注意：如果HAVING中包含多个条件，那么这些条件将通过AND、OR或NOT组合在一起4.2.2GROUP BY 和 WHERE 子句、HAVING 子句v例4-28查询选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。程序清单如下：SELECTSNO,SUM(SCORE)ASTotalScoreFR

18、OMSCWHERESCORE=60GROUPBYSNOHAVINGCOUNT(*)=3ORDERBYSUM(SCORE)DESC比较比较 GROUP BY 与与 COMPUTECOMPUTE和GROUPBY之间的区别汇总如下：vGROUPBY生成单个结果集。结果集依据分组根据聚合函数进行统计显示。vCOMPUTE生成多个结果集。一种结果集包含每个组的明细行。另一种结果集包含组的子聚合，或SELECT语句的总聚合。聚合函数在COMPUTE子句中指定，而不是在选择列表中指定。v第一个示例使用COMPUTE子句计算不同类型烹调书籍的价格总和。第二个示例只使用GROUPBY生成相同的汇总信息。v例一:

19、vSELECTcourseid,scorevFROMcourseselectvorderbycourseidvCOMPUTESUM(score)bycourseidv例二vSELECTcourseid,sum（score）vFROMcourseselectvGroupbycouresidvorderbycourseid4.3 连接查询数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。4.3.1等值连接与非等值连接4.3.2自

20、身连接v表的连接方法有两种：方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。方法2：利用关键字JOIN进行连接。4.3.1 等值连接与非等值连接v连接条件的一般格式为：.v其中，比较运算符主要有：、！。当比较运算符为“”时，称为等值连接，其他情况为非等值连接。v例4-29查询张飞同学所选修的课程。SELECTS.SNO,SN,CNOFROMS,SCWHERE(S.SNO=SC.SNO)AND(SN=张飞)引用列名TNO时要加上表名前缀，是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果

21、列名是唯一的，比如TN，就不必须加前缀。上面的操作是将T表中的TNO和TC表中的TNO相等的行连接，同时选取TN为“刘伟“的行，然后再在TN，CNO列上投影，这是连接、选取和投影的操作组合。v方法2：SELECTT.TNO,TN,CNOFROMTINNERJOINTCONT.TNO=TC.TNOANDT.TN=刘伟v方法3：SELECTR2.TNO,R2.TN,R1.CNOFROM(SELECTTNO,CNOFROMTC)ASR1INNERJOIN(SELECTTNO,TNFROMTWHERETN=刘伟)ASR2ONR1.TNO=R2.TNO4.3.1等值连接与非等值连接v例4-30查询所有选

22、课学生的学号、姓名、选课名称及成绩。程序清单如下：SELECTS.SNO,SN,CN,SCOREFROMS,C,SCWHERES.SNO=SC.SNOANDSC.CNO=C.CNOv注意：本例涉及三个表，WHERE子句中有两个连接条件。当有两个以上的表进行连接时，称为多表连接。4.3.2 自身连接v当一个表与其自已进行连接操作时，称为表的自身连接。要查询的内容均在同一表中，可以将表分别取两个别名，一个是X，一个是Y。将X，Y中满足查询条件的行连接起来。这实际上是同一表的自身连接。v例4-31查询所有比李明工资高的教师姓名、性别、工资和李明的工资。程序清单如下：SELECTX.TN,X.SALA

23、SSAL_a,Y.SALASSAL_bFROMTASX,TASYWHEREX.SALY.SALANDY.TN=李明v方法2：SELECTX.TN,X.SAL,Y.SALFROMTASXINNERJOINTASYONX.SALY.SALANDY.TN=李明v方法3：SELECTR1.TN,R1.SAL,R2.SALFROM(SELECTTN,SALFROMT)ASR1INNERJOIN(SELECTSALFROMTWHERETN=李明)ASR2ONR1.SALR2.SALv子查询v在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的

24、语句称为父查询或外部查询。v嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。v子查询的嵌套层次最多可达到255层，以层层嵌套的方式构造查询充分体现了SQL“结构化”的特点。v嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。4.4子查询4.4.1返回一个值的子查询4.4.2返回一组值的子查询4.4.1返回一个值的子查询v当子查询的返回值只有一个时，可以使用比较运算符（=,=,比较运算符为例，ALL表示大于每一个值。换句话说，它表示大于最大值。例如，ALL(1,2,3)表示大于3。ANY表示至少大于一个值，即大于最小值。因此ANY(1,2,

25、3)表示大于1。v若要使带有ALL的子查询中的行满足外部查询中指定的条件，引入子查询的列中的值必须大于子查询返回的值列表中的每个值。v同样，ANY表示要使某一行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于子查询返回的值列表中的一个值。例4-34查询讲授课程号为C5的教师姓名。程序清单如下：SELECTTNFROMTWHERETNO=ANY(SELECTTNOFROMTCWHERECNO=C5)v先执行子查询，找到讲授课程号为先执行子查询，找到讲授课程号为C5C5的教师号，为一组值构的教师号，为一组值构成的集合成的集合(T2,T3,T5)(T2,T3,T5)；v再执行父查询，其中再

26、执行父查询，其中ANYANY的含义为任意一个，查询教师号为的含义为任意一个，查询教师号为T2T2、T3T3、T5T5的教师的姓名。的教师的姓名。v该例也可以使用前面所讲的连接操作连接操作来实现：SELECTTNFROMT,TCWHERET.TNO=TC.TNOANDTC.CNO=C5v可见，对于同一查询可使用子子查查询询和连连接接两种方法来解决，可根据习惯任意选用。v例 查询其他系中比工商院老师中最低工资高的教师的姓名和工资。SELECTTN,SALFROMTWHERESALANY(SELECTSALFROMTWHEREDEPT=工商院)ANDDEPT!=工商院/*注意：此行是父查询中的条件*

27、/v先执行子查询，找到工商院中所有教师的工资集合(1500,900)；v再执行父查询，查询所有不是工商院且工资高于1500或900的教师姓名和工资。v此查询也可以写成：SELECTTN,SALFROMTWHERESAL(SELECTMIN(SAL)FROMTWHEREDEPT=工商院)ANDDEPT!=工商院v先执行子查询，利用库函数MIN找到工商院中所有教师的最低工资900；v再执行父查询，查询所有不是工商院且工资高于900的教师。4.4.2 返回一组值的子查询2.使用IN可以使用IN代替“=ANY”。例4-34查询讲授课程号为C5的教师姓名程序清单如下：SELECTTNFROMTWHERE

28、TNOIN(SELECTTNOFROMTCWHERECNO=C5)4.4.2 返回一组值的子查询3.使用ALLALL的含义为全部。例4-37查询其他系中比电力系所有教师工资都高的教师的姓名和工资。程序清单如下：SELECTTN,SALFROMTWHERESALALL(SELECTSALFROMTWHEREDEPT=电力)ANDDEPT!=电力4.4.2 返回一组值的子查询4.使用EXISTSEXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。v例4-38查询讲授课程号为C5的教师姓名程序清单如下：SELECTTNFROMTWHEREEXISTS(SELECT*FROMTCWHERETNO=T.TNOANDCNO=C5)