第4章_数据查询ppt课件.ppt

数据库应用基础教程,计算机基础教学实验中心2008.1.10,第4章数据查询,本章内容,4.1SQL操作与查询语句的基本结构4.2基本查询4.3嵌套查询4.4联接查询,4.1SQL操作与查询语句的基本结构,SQL操作包括:选择、投影、联接SQL操作通过数据查询语句SELECT实现SELECT语句基本框架是SELECT-FROM-WHERE,包含输出字段、数据来源和查询条件等基本子句。SELECT语句能从数据库的一个或多个表中选择一个或多个行或列。,4.1SQL操作与查询语句的基本结构,SELECT语句的主要子句如下：SELECTselect_listINTOnew_tableFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC,4.2基本查询,4.2.1简单查询4.2.2带条件查询4.2.3查询结果处理,4.2.1简单查询,简单查询是指无条件查询SELECTALL|DISTINCTTOPnPERCENTselect_listFROMtable_name其中:(1)ALL表示输出所有记录，包括重复记录。(2)DISTINCT表示输出无重复结果的记录。(3)TOPnPERCENT指定返回查询结果的前n行数据(4)select_list是所要查询的选项的集合。可以是字段名、表达式或函数。若要输出一个表的全部字段，选项可用*表示。(5)table_name表示要查询的表。,例如，使用以下语句显示Student_db数据库的St_Info表中全部学生的信息：SELECT*FROMSt_info,例如,先在St_Info表中，添加一个姓名为“张红飞”的学生记录，然后在查询分析器中输入以下语句并执行：SELECTDISTINCTSt_nameAS姓名,YEAR(GETDATE()-YEAR(Born_date)AS年龄FROMSt_Info,注意:结果中学生姓名为“张红飞”的记录只列出了一条,由DISTINCT关键字控制。第二列为计算列，通过表达式YEAR(GETDATE()-YEAR(born_date)计算了学生的年龄。,聚合函数,聚合函数是SELECT语句中的选项。聚合函数对一组值执行计算并返回单一值聚合函数可以针对整个或几个列进行数据汇总，常用来计算SELECT语句查询结果集的统计值。,例如,分别查询St_Info表的学生总数和学生的平均年龄：SELECTCOUNT(*)AS总数FROMSt_InfoSELECTAVG(YEAR(GETDATE()-YEAR(Born_date)AS平均年龄FROMSt_info,常用聚合函数：,4.2.2带条件查询,SELECT通过WHERE子句指定查询条件：WHEREsearch_condition其中:(1)search_condition是条件表达式，指定查询的结果集合应满足的条件。(2)表达式用的比较运算符为：=(等于)、!=或(不等于)、(大于)、=(大于等于)、=18ANDYEAR(GETDATE()-YEAR(Born_date)=80,4.重定向输出(INTO),INTO子句用于把查询结果存放到一个新建的表中。语法格式：INTOnew_table其中,new_table指定新表的名称。,例4-9,统计出S_C_Info表中每个学生所修课程的平均成绩，并存放New_C表中，其列名为学号、平均分。SELECTSt_IdAS学号,AVG(Score)AS平均分INTONew_CFROMS_C_InfoGROUPBYSt_Id,查询结果集不是直接显示在查询分析器的结果窗格中，而存放在New_C表中。,5.使用COMPUTE和COMPUTEBY汇总,语法格式：COMPUTErow_aggregate(column_name),row_aggregate(colornn_name).BYcolumn_name,column_name.COMPUTE所生成的汇总值在查询结果中显示为分离的结果集,例4-10,列出St_Info表中“材料科学0601班”学生年龄的明细行和汇总行。SELECTSt_Id,St_Name,YEAR(GETDATE()-YEAR(Born_date)AS年龄FROMSt_InfoWHERECl_Name=材料科学0601班ORDERBYSt_IdCOMPUTEAVG(YEAR(GETDATE()-YEAR(Born_date),例4-11,分别列出St_Info表中“材料科学0601班”和“口腔(七)0601班”学生年龄的明细行和汇总行。SELECTSt_Id,St_Name,YEAR(GETDATE()-YEAR(Born_date)AS年龄FROMSt_InfoWHERECl_Name=材料科学0601班ORCl_Name=口腔(七)0601班ORDERBYCl_NameCOMPUTEAVG(YEAR(GETDATE()-YEAR(Born_date)BYCl_Name,4.3嵌套查询,4.3.1单值嵌套查询4.3.2多值嵌套查询,4.3嵌套查询,SELECT语句中包含SELECT语句,称为子查询SQLServer允许多层嵌套查询，即子查询中可以还可以包含子查询。嵌套查询要求服务器先处理最内层的子查询并产生一个结果，再根据该结果继续外层的查询步骤。根据子查询的返回结果将嵌套查询分为单值嵌套查询和多值嵌套查询。,4.3.1单值嵌套查询,例4-12对Student_db数据库，列出选修“大学计算机基础”课程的所有学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=(SELECTC_NoFROMC_InfoWHEREC_Name=大学计算机基础),4.3.2多值嵌套查询,子查询的返回结果是结果集的嵌套查询称为多值嵌套查询。若某个子查询的返回值不止一个，则必须在WHERE子句中指明如何使用这些返回值。通常使用条件运算符ANY、ALL、IN和EXISTS等实现。,1.使用ANY运算符,例4-13列出Student_db数据库中选修“9710011”(即“大学计算机基础”)课程的学生的成绩比选修“29000011”(“体育”)课程的学生的最低成绩高的学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=9710011ANDScoreANY(SELECTScoreFROMS_C_InfoWHEREC_No=29000011),2.使用ALL运算符,例4-14列出Student_db数据库中选修“29000011（体育）”的学生的成绩比选修“9710011（大学计算机基础）”的学生的最高成绩还要高的学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=29000011ANDScoreALL(SELECTScoreFROMS_C_InfoWHEREC_No=9710011),ANY与ALL运算符的区别是什么?,3.使用IN运算符,例4-15列出Student_db数据库中选修“体育”或“大学计算机基础”课程的学生学号和成绩SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_NoIN(SELECTC_NoFROMC_InfoWHEREC_Name=大学计算机基础ORC_Name=体育),Select*FromS_C_infoWhereC_NoIN(9710011,29000011)IN用于判断C_NO是否在集合中,思考,查询与“口腔(七)0601班”所有学生的年龄不同的学生学号、姓名和年龄。查询选修了”9710041”课程的学生和姓名,分析以下语句的正确性,Selectst_name,max(year(getdate()-year(born_date)Fromst_info,4.4联接查询,4.4.1联接类型4.4.2内联接4.4.3外联接,联接查询，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接是关系数据库模型的主要特点，也是它区别于其他类型数据库管理系统的一个标志。分为内联接、外联接和交叉联接,4.4.1联接类型,联接条件在WHERE子句中指定例4-16查询Student_db数据库中每个学生的成绩，显示姓名、课程编号和成绩。SELECTSt_Name,C_No,ScoreFROMSt_Info,S_C_InfoWHERESt_Info.St_ID=S_C_Info.St_ID,联接查询通过两种方式实现:联接条件在WHERE子句中指定联接条件也可以在FROM子句中建立,例4-17,在例4-16的基础上，要求显示学生的学号、姓名、课程名称和成绩。SELECTSt_Info.St_Id,St_Info.St_Name,C_Info.C_Name,S_C_info.ScoreFROMSt_Info,C_Info,S_C_InfoWHERESt_Info.St_Id=S_C_Info.St_IdANDS_C_Info.C_No=C_Info.C_No,结果涉及三个表：St_Info、C_Info和S_C_Info因此WHERE子句所定义的连接条件就需要两个。,4.4.1联接类型,联接条件也可以在FROM子句中建立。在T-SQL中推荐使用这种方法。,4.4.1联接类型,FROM子句联接的语法格式：FROMjoin_tablejoin_typeJOINjoin_tableONjoin_condition其中：join_table指出参与联接操作的表名join_type指出联接类型，可分为内联接(默认)、外联接和交叉联接三种类型。ONjoin_condition指出联接条件,例4-18,列出S_C_Info和C_Info表中选修“大学计算机基础”和“体育”两门课程的学生信息SELECT*FROMS_C_InfoeJOINC_InfodONe.C_No=d.C_NoWHEREd.C_NameIN(大学计算机基础,体育)注意：其中*表示列出两个表中的所有字段，e与d是该表的别名,提问,例4-17中的联接用FROM子句建立，完成操作的语句?SELECTs.St_Id,s.St_Name,c.C_Name,sc.ScoreFROMSt_InfosJOINS_C_InfoscONs.St_Id=sc.St_IdJOINC_InfocONsc.C_No=c.C_No,查询Student_db数据库中每个学生的成绩，要求显示学生的学号、姓名、课程名称和成绩。,4.4.2内联接,内联接查询列出与联接条件匹配的数据行，它使用比较运算符比较被联接列的列值。根据所使用的比较方式的不同，内联接可分为三种类型：等值联接、不等值联接和自然联接。,1等值联接,在联接条件中使用等号（=）运算符比较被联接列的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相联接，包括其中的重复列。,例4-19,列出Student_db数据库中男生的选课情况，要求列出学号、姓名、课程名称和成绩。SELECTs.St_Id,s.St_Name,c.C_Name,sc.ScoreFROMSt_InfosINNERJOINS_C_InfoscONs.St_Id=sc.St_IdINNERJOINC_InfocONsc.C_No=c.C_NoWHERE(s.St_Sex=男),相等联接返回两个表中的所有列，但只返回在联接列中具有相等值的行。SELECT*FROMSt_InfosINNERJOINS_C_InfoscONs.St_Id=sc.St_IdINNERJOINC_InfocONsc.C_No=c.C_NoWHERE(s.St_Sex=男),2.不等值联接,在联接条件中使用除=以外的其他比较运算符(、=、!b.ScoreANDa.C_No=b.C_NoWHEREb.St_Id=2001050108ANDb.C_No=9720013,3.自然联接,在联接条件中使用等于运算符(=)比较被联接列的列值，它使用选择列表方式来指出查询结果集合中所包括的列，并删除联接表中的重复列。,例4-21,实现St_Info表和S_C_Info表的自然联接。SELECTa.St_Name,b.*FROMSt_InfoaINNERJOINS_C_InfobONa.St_Id=b.St_Id自然联接通过更改选择列表消除两个相同列中的一个。,4.4.3外联接,内联接查询时，返回结果集的仅是符合查询条件(WHERE或HAVING的筛选条件)和联接条件的行。采用外联接时，返回结果集的不仅包含符合联接条件的行，而且还包括左表(左外联接)、右表(右外联接)或两个联接表(全外联接)中的所有数据行。,1使用左外联接,左外联接包括左表（出现在JOIN子句的最左边）的所有行，不包括右表中的不匹配行。,例4-22,St_Info表左外联接S_C_Info表。SELECTa.St_Id,a.St_Name,b.C_No,b.ScoreFROMSt_InfoaLEFTOUTERJOINS_C_InfobONa.St_Id=b.St_Id,2使用右外联接,右外联接包含右表（出