SQL-第08章-数据检索.ppt

第8章数据检索,8.1SELECT语句8.2使用SELECT语句进行简单查询8.3使用T-SQL语句进行高级查询8.4用户定义函数在数据检索中的应用8.5数据查询的优化8.6分布式查询简介,返回目录,2,8.1SELECT语句,SELECT语句的基本语法SELECTALL|DISTINCTcolumn_listINTOnew_table_nameFROMtable_listWHEREsearch_conditionGROUPBYgroup_by_listHAVINGsearch_conditionORDERBYorder_listASC|DESCSELECT语句中各子句的说明SELECT：此关键字用于从数据库中检索数据。ALL|DISTINCT：ALL指定在结果集中可以包含重复行，ALL是默认设置；关键字DISTINCT指定SELECT语句的检索结果不包含重复的行。,3,column_list：描述进入结果集的列，它是由逗号分隔的表达式的列表。每个列表中表达式通常是对从中获取数据的源表或视图的列的引用，但也可能是其它表达式，例如常量或Transact-SQL函数。如果select_list使用*，表明指定返回源表中的所有列。INTOnew_table_name：指定查询到的结果集存放到一个新表中。new_table_name为指定新表的名称。FROMtable_list：用于指定产生检索结果集的源表的列表。这些源表包括：SQLServer的本地服务器中的基表、本地SQLServer中的视图、链接表。SQLServer将一个视图引用内部解析为针对组成视图的一个或多个基表的引用，链接表是从SQLServer进行访问的OLEDB数据源中的表，这种访问方式称为分布式查询。有关视图的概念将在第九章中介绍。,4,WHEREsearch_condition：用于指定检索的条件，它定义了源表中的行数据进入结果集所要满足的条件，只有满足条件的行才能出现在结果集中。GROUPBYgroup_by_list：GROUPBY子句根据group_by_list列中的值将结果集分成组。HAVINGsearch_condition：HAVING子句是应用于结果集的附加筛选。从逻辑上讲，HAVING子句从中间结果集对行进行筛选，这些中间结果集是用SELECT语句中的FROM、WHERE或GROUPBY子句创建的。HAVING子句通常与GROUPBY子句一起使用，尽管HAVING子句前面不必有GROUPBY子句。ORDERBYorder_listASC|DESC：ORDERBY子句定义结果集中的行排列的顺序。order_list指定依据哪些列来进行排序。ASC和DESC关键字用于指定结果集是按升序还是按降序排序，DESC降序排序，ASC升序排序。ORDERBY是一个重要的子句，要想获得有序的查询结果，必须使用ORDERBY子句，因为关系理论规定表中的数据行是没有次序的。,5,在使用SELECT语句时应注意如下几点：必须按照正确的顺序指定SELECT语句中的子句。对数据库对象的每个引用必须具有唯一性。在系统中，可能有多个数据库对象带有相同的名称。例如，User1和User2可能都创建了一个名为Table_0的表。在引用表Table_0时，为了区分引用的是User1的Table_0表，还是引用的是User2的Table_0，就必须对引用名称有所限定，如：User1.Table_0，User2.Table_0。在执行SELECT语句时，对象所驻留的数据库不一定总是当前数据库。若要确保总是使用正确的对象，则不论当前数据库是如何设置的，均应使用数据库和所有者来限定对象名称，如：JWGL.dbo.student。在FROM子句中所指定的表或视图可能有相同的列名，外键很可能和相关主键同名。加上对象名称来限定列名可解决重复列名称的问题，如：department.department_no、teacher.department_no。,本章首页,6,8.2.1最基本的SQL查询语句8.2.2改变列标题的显示8.2.3使用WHERE子句的查询8.2.4TOP和DISTINCT关键字8.2.5使用ORDERBY子句对结果进行排序8.2.6计算列的使用8.2.7基于多个检索条件的查询,8.2使用SELECT语句进行简单查询,本章首页,7,8.2.1最基本的SQL查询语句,SQL语言中最主要、最核心的部分是它的查询功能。查询语言用来对已经存在于数据库中的数据按照特定的组合、条件表达式或次序进行检索。8.2.1最基本的SQL查询语句格式：SELECT*|column_name,.nFROMtable_name功能：从指定表中查询所有信息或指定列的信息。【例8-1】从teacher表中分别检索出教师的所有信息及教师、教师姓名信息。USEjwglGOSELECT*FROMstudentSELECTteacher_id,teacher_nameFROMteacher注意：在SELECT后的列名的顺序决定了显示结果中的列序在查找多列内容时，用,将各字段分开,本节首页,8,在缺省情况下，执行上面的SQL语句后，查询结果中显示的列标题是列名。可以在SELECT语句中用列标题=列名或列标题AS列名来改变列标题的显示。【例8-2】从teacher表中分别检索出教师的教师号、教师姓名信息并分别加上“教师”、“教师号”的标题信息。USEjwglGOSELECT教师号=teacher_id,教师姓名=teacher_nameFROMteacherSELECTteacher_idAS教师号,teacher_nameAS教师姓名FROMteacher,8.2.2改变列标题的显示,本节首页,9,大部分查询都不是针对表中所有行的查询，而是从整个表中选出符合条件的信息，要实现这样的查询就要用到WHERE子句。1.WHERE子句的语法形式WHERE子句的语法形式如下：SELECTselctc_listFROMtable_listWHEREselect_condition其中SQLServer支持的搜索条件如下：比较：=、=、范围：BETWEENAND（在某个范围内）、NOTBETWEENAND（不在某个范围内）,8.2.3使用WHERE子句的查询,10,列表：IN（在某个列表中）、NOTIN（不在某个列表中）字符串匹配：LIKE（和指定字符串匹配）、NOTLIKE（和指定字符串不匹配）空值判断：ISNULL（为空）、ISNOTNULL（不为空）组合条件：AND（与）、OR（或）取反：NOT要注意的是，应该避免使用否定条件，查询优化器不能识别否定条件。2.基于比较条件的WHERE子句使用基于比较条件的WHERE子句对表中数据进行查询，系统在执行这种条件查询时，逐行地对表中的数据进行比较，检查它们是否满足条件。,11,如果满足条件，则取出该行，如果不满足条件则不取该行。使用WHERE子句时，若该列为字符型，需要使用单引号将字符串括起来，而且应该注意单引号内的字符串要区分大小写形式。【例8-3】从book表中检索出价格小于15元的书本信息。USEjwglGOSELECT*FROMbookWHEREprice=15ANDprice、=、。search_condition：表中行数据进入结果集所应满足的条件。2.进行连接查询的要点：一般而言，基于主键和外键指定查询条件，连接条件可使用“主键=外键”。如果一个表有复合关键字，在连接表时，必须引用整个关键字。应尽可能限制连接语句中表的数目，连接的表越多，查询处理的时间越长。对于连接表的两个列应有相同或类似的数据类型。不要使用空值作为连接条件，因为空值计算不会和其它任何值相等。,27,【例8-13】从student及student_course两个表中检索学生的学号、姓名、学习课程号及课程成绩。USEjwglGOselectstudent.student_id,student.student_name,student_course.course_id,student_course.gradefromstudent,student_courseWHEREstudent.student_id=student_course.student_id,28,【例8-14】从student、course及student_course三个表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩。USEjwglGOSELECTstudent.student_id,student.student_name,student_course.course_id,course.course_name,student_course.gradefromstudent,student_course,courseWHEREstudent.student_id=student_course.student_idANDcourse.course_id=student_course.course_id,29,3.使用别名方法一:usejwglselects.student_id,s.student_name,s_c.course_id,s_c.gradefromstudents,student_courses_cWHEREs.student_id=s_c.student_id方法二:selects.student_id,s.student_name,s_c.course_id,s_c.gradefromstudentASs,student_courseASs_cWHEREs.student_id=s_c.student_id,本节首页,30,8.3.2使用UNION子句,UNION子句的作用是把两个或多个SELECT语句查询的结果组合成一个结果集。UNION子句的语法形式如下：Select_statementUNIONALLSelect_statementn使用UNION时，请注意以下4点：UNION中从源表选择的所有列表必须具有相同列数、相似数据类型和相同的列序。列名来自第一个SELECT语句。如果希望整个结果集以特定的顺序出现，则UNION中应使用ORDERBY子句来指定对结果集的排序顺序。在合并结果时，将从结果集中删除重复行。若使用ALL，结果集中包含所有的行。,31,【例8-15】用UNION子句将student表中学生的学号、姓名及teacher表中教师号、教师姓名组合在一个结果集中。USEjwglGOSELECTstudent_id,student_nameFROMstudentUNIONSELECTteacher_id,teacher_nameFROMteacher,本节首页,32,8.3.3使用GROUPBY子句,使用GROUPBY子句进行数据检索可得到数据分类的汇总统计、平均值或其它统计信息。1.GROUPBY子句的语法形式GROUPBY子句的语法形式如下：SELECTcolumn_name_listFROMtable_nameWHEREsearch_conditionGROUPBYALLaggregate_expressionnHAVINGsearch_condition,33,其中：aggregate_expression：分组表达式search_condition：对分组汇总后数据进入结果集的筛选条件在使用GROUPBY子句时，注意以下几点：SQLServer为每个定义的组产生一个列值，每个组只返回一行，不返回详细信息。如果包括WHERE子句，SQLServer只分组汇总满足WHERE条件的行。在包含GROUPBY子句的查询语句中，SELECT子句后的所有字段列表，除集合函数外，都应包含在GROUPBY子句中，否则将出错。GROUPBY子句的列表中最多只能有8060个字节。不要在含有空值的列上使用GROUPBY子句，因为空值将作为一个组来处理。如果GROUPBY子句使用ALL关键字，WHERE子句将不起作用。HAVING子句排除不满足条件的组。,34,2.不带HAVING的GROUPBY子句的用法GROUPBY子句是按列或表达式分组汇总，为每组产生一个值，一般和集合函数一起使用。【例8-16】用GROUPBY句汇总出student_course表中学生的学号及总成绩。USEjwglGOSELECT学号=student_id,总成绩=sum(grade)FROMstudent_courseGROUPBYstudent_id,35,3.带HAVING的GROUPBY子句的用法可以用HAVING子句对分组汇总后进入结果集的各组进行限制。HAVING子句是针对GROUPBY子句的，没有GROUPBY子句时使用HAVING子句是没有意义的。【例8-17】用GROUPBY句汇总出student_course表中总分大于450分的学生的学号及总成绩。USEjwglGOSELECT学号=student_id,总成绩=sum(grade)FROMstudent_courseGROUPBYstudent_idHAVINGsum(grade)450,本节首页,36,8.3.4使用COMPUTE和COMPUTEBY子句,使用COMPUTE和COMPUTEBY就既能浏览数据又看到统计的结果。COMPUTEBY子句的语法形式如下：COMPUTErow_aggregate(column_name)nBYcolumn_name_list【例8-18】用COMPUTE子句汇总出student_course表中每个学生的学号及总成绩。USEjwglGOSELECT学号=student_id,成绩=gradeFROMstudent_courseORDERBYstudent_idCOMPUTESUM(grade),37,COMPUTE类似于总计。如在COMPUTE后加上BY关键字，则查询的结果为带具体内容的分类统计。【例8-19】用COMPUTEBY子句按学号汇总出student_course表中每个学生的的学号及总成绩。USEjwglGOSELECT学号=student_id,成绩=gradeFROMstudent_courseORDERBYstudent_idCOMPUTESUM(grade)BYstudent_id,38,值得注意的是，在使用COMPUTE和COMPUTEBY时，有如下限制:DISTINCT不允许同集合函数一起用，不能包含text、ntext、image数据类型。COMPUTE子句中的列必须在SELECT后面的选择列表中。SELECTINTO不与COMPUTE子句一起使用。若使用了COMPUTEBY，则必须使用ORDERBY。COMPUTEBY后出现的列必须与ORDERBY后出现的列相同，或者是它的子集。它必须具有相同的从左到右顺序并且以相同的表达式开头，不能跳过任何表达式。,本节首页,39,8.3.5嵌套查询,如果先通过一个查询查出一个结果集，再在这个结果集中进行查询的话就是嵌套查询。嵌套查询是用一条SELECT语句作为另一条SELECT语句的一部分。外层的SELECT语句叫外部查询，内层的SELECT语句叫内部查询（或子查询）。嵌套查询的执行流程是，首先执行内部查询，它查询出来的数据并不被显示出来，而是传递给外层SELECT语句，作为该SELECT语句的查询条件使用。子查询可以多层嵌套。1.使用IN或NOTIN关键字单值子查询是指子查询只返回一行数据。多值子查询是指子查询返回的不是一行而是一组行数据。前者可以用“=”、IN或NOTIN和其外部查询相联系，后者则必须使用IN或NOTIN和其外部查询相联系。IN表示属于的关系，即是否在所选数据集合之中。NOTIN则表示不属于集合或不是集合的成员。,40,【例8-20】查询出“g99402”班所有男生的学号、课程号及相应的成绩。USEjwglGOSELECTstudent_course.student_id,student_course.course_id,student_course.gradeFROMstudent_courseWHEREstudent_idIN(SELECTstudent_idFROMstudentWHEREclass_id=g99402ANDsex=1),41,2.使用EXSISTS或NOTEXSISTS关键字EXISTS关键字用来确定数据是否在查询列表中存在。EXISTS表示一个子查询至少返回一行时条件成立。和使用IN关键字不同的是，IN连接的是表中的列，而EXISTS连接的是表和表，通常不需要特别指出列名，可以直接使用*。由于EXISTS连接的是表，所以，子查询中必须加入表与表之间的连接条件。【例8-21】使用EXSISTS关键字查询出“g99403”班学生的学号、课程号及相应的成绩。USEjwglGOSELECTstudent_course.student_id,student_course.course_id,student_course.gradeFROMstudent_courseWHEREEXISTS(SELECT*FROMstudentWHEREstudent_course.student_id=student.student_idANDstudent.class_id=g99403),42,3.使用嵌套子查询的几点说明：首先对子查询(内部查询)求值。外部查询依赖于子查询的求值结果。子查询必须被括在圆括号内。以比较操作符引导的子查询的选择列表只能包括一个表达式或列名。否则SQLServer会报错。,本节首页,43,8.3.6在查询的基础上创建新表,SELECTINTO的作用是，在查询的基础上创建新表。若建临时表，必须在表前设置#（局部临时表）或#（全局临时表）。新表的行和列是来自查询结果，临时表是创建在tempdb数据库上。【例8-22】创建#temp_grade的临时表，该临时表有两个列：student_id,grade，要求学生的成绩grade大于95分。然后，再从临时表#temp_grade查询数据。USEjwglGOSELECTstudent_id,gradeinto#temp_gradeFROMstudent_courseWHEREgrade=95USEtempdbGOSELECT*FROM#temp_grade,44,也可以用SELECT语句创建一个永久表。永久表是创建在基表所在的数据库里。【例8-23】创建grade_table的永久表，该临时表有两个列：student_id,grade.要求学生的成绩grade大于95分。USEjwglGOSELECTstudent_id,gradeintograde_tableFROMstudent_courseWHEREgrade95,本节首页,45,8.4用户自定义函数在数据检索中的应用,8.4.1用户自定义函数的定义及分类用户自定义函数最多可以有1024个输入参数并返回一个简单的数值。可使用CREATEFUNCTION语句创建、使用ALTERFUNCTION语句修改、使用DROPFUNCTION语句除去用户定义函数。每个完全合法的用户定义函数名必须唯一。分类:标量型用户自定义函数、表值函数；表值函数又分为内联表值用户自定义函数、多语句表值用户自定义函数。,46,8.4.2标量值型用户自定义函数的定义、使用及删除标量值型用户自定义函数的函数体被封装在以BEGIN语句开始，END语句结束的范围内。在BEGIN.END块中定义的函数主体包含返回该值的Transact-SQL语句系列。【例8-24】定义一个标量值型用户自定义函数，按出生年月计算年龄，然后从学生情况表student中检索出含有年龄的学生信息。CREATEFUNCTIONjs_old(vardatedatetime,curdatedatetime)RETURNStinyintASBEGINRETURNdatediff(yy,vardate,curdate)END,47,【例8-25】从学生情况表student中检索出含有年龄的学生信息。usejwglgoselectstudent_idas学号,student_nameas姓名,class_idas班级,dbo.js_old(birth,getdate()as年龄fromstudent【例8-26】使用SQL语句删除用户自定义函数js_old。dropfunctionjs_old,48,8.4.3表值用户自定义函数的定义、使用及删除1.内联表值用户自定义函数的定义、使用及删除内联表值用户自定义函数返回一个Table型数据，对内联表值用户自定义函数而言，返回的结果只是一个SELECT语句所返回的一系列表值的结果集。【例8-27】定义一个内联自定义函数，用班级号作参数从学生情况表student中检索出含有学号、学生姓名的学生信息。CREATEFUNCTIONtable_call(class_Parameternvarchar(30)RETURNStableASRETURN(SELECTstudent_id,student_nameFROMstudentWHEREclass_id=class_Parameter),49,【例8-28】以班级号“g99403”作参数从学生情况表student中检索出含有学号、学生姓名的学生信息。SELECT*FROMtable_call(g99403)内联表值用户自定义函数遵从以下规则：1）RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置。2）function_body不由BEGIN和END分隔。3）RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。直接表值自定义用户函数中使用的SELECT语句受到与视图中使用的SELECT语句相同的限制。,50,2.多语句表值用户自定义函数的定义、使用及删除对于多语句表值函数，在BEGIN.END块中定义的函数主体包含TRANSACT-SQL语句，这些语句可生成行并将行插入将返回的表中。【例8-29】定义一个自定义函数，用班级号作参数，然后从学生情况表student中检索出含有学号、学生姓名、性别及选课信息。CREATEFUNCTIONmuti_table_call(name_classchar(6)RETURNSreturntableTABLE(student_idchar(8),student_namenvarchar(8),sexbit,class_idchar(8),course_idchar(10),51,ASBEGININSERTreturntableSELECTs.student_id,s.student_name,s.sex,s.class_id,s_c.course_idFROMstudentASSINNERJOINstudent_courseass_cONs.student_id=s_c.student_idands.class_id=name_classRETURNEND,52,在返回table的多语句用户定义函数中：1）RETURNS子句为函数返回的表定义局部返回变量名。RETURNS子句还定义表的格式。局部返回变量名的作用域位于函数内。2）函数主体中的Transact-SQL语句生成行并将其插入RETURNS子句所定义的返回变量。3）当执行RETURN语句时，插入变量的行以函数的表格格式输出形式返回。RETURN语句不能有参数。4）函数中返回table的Transact-SQL语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的table。【例8-29】以班级号“g99402”作参数从学生情况表student和student_course中检索出该班学生选修课的信息SELECT*FROMmuti_table_call(g99402),本章首页,53,8.5数据查询的优化,1.提高SQL语句的可读性多使用别名、括号、回车，将查询的各个组成部分简单化或分隔开来。例如：（方法一）selectstudent.student_id,student.student_name,student_course.course_id,student_course.gradefromstudent,student_courseWHEREstud