第4章_数据查询PPT课件

精选,1,第4章数据查询,精选,2,本章内容,4.1SQL操作与查询语句的基本结构4.2基本查询4.3嵌套查询4.4联接查询小结,精选,3,4.1SQL操作与查询语句的基本结构,SQL操作是通过关系运算实现的，基本的关系运算有3种:选择操作：用来限制表中与给定条件相匹配的行；投影操作：用来限制结果集的列数，可以垂直地选择单列或列集作为输出结果；连接操作：用来将一个表与另一个表中列值匹配的行连接起来，通常用一个表中的主键与另一个表中的外键匹配。,精选,4,4.1SQL操作与查询语句的基本结构,数据查询语句SELECT的基本框架是:SELECT-FROM-WHERE即：SELECTFROMWHERE,需要哪些列,从哪些表,根据什么条件,精选,5,4.1SQL操作与查询语句的基本结构,SELECT语句的主要子句如下：SELECTselect_listINTOnew_tableFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC,精选,6,4.2基本查询,4.2.1简单查询简单查询是指无条件查询，其语法格式如下：SELECTALL|DISTINCTTOPnPERCENTFROMtable_name其中:ALL表示输出所有记录，包括重复记录。DISTINCT表示输出无重复结果的记录。TOPn指定返回查询结果的前n行数据。select_list中的选项可以是：、字段名、表达式或函数。,如果还指定了PERCENT，则只从结果集中输出前百分之n行,例如：查询St_Info表中全部学生的信息：SELECT*FROMSt_info,精选,7,4.2基本查询(简单查询举例),又如：SELECTallst_nameFROMSt_InfoSELECTDISTINCTSt_nameFROMSt_InfoSELECTDISTINCTst_id,St_nameFROMSt_InfoSELECTst_nameas姓名,st_sexAS性别FROMSt_InfoSELECTst_name姓名,st_sex性别FROMSt_InfoSELECTtop3St_idFROMS_c_InfoSELECTtop20PERCENTSt_idFROMS_c_Info,ALL表示所有记录,精选,8,4.2基本查询,在SELECT语句中选项，不仅可以是字段名和表达式，也可以是函数。如：SELECTSt_nameAS姓名,YEAR(GETDATE()-YEAR(Born_date)AS年龄FROMSt_Info,返回当前日期函数,返回日期年份函数,函数嵌套,精选,9,4.2基本查询,聚合函数（一种特殊函数）对一组行中的某个列执行计算，并返回单个值。经常与SELECT语句的GROUPBY子句一起使用。用来计算SELECT语句查询结果集的统计值。常用的聚合函数有：,该函数计算符合查询限制条件的总行数。并且不使用有关任何特定列的信息。,精选,10,4.2基本查询,聚合函数使用举例例如，分别查询St_Info表的学生总数和学生的平均年龄查询学生总数（使用COUNT(*)）SELECTCOUNT(*)AS总数FROMSt_Info查询学生的平均年龄（使用AVG）SELECTAVG(YEAR(GETDATE()-YEAR(Born_date)AS平均年龄FROMSt_info,精选,11,4.2基本查询,4.2.2带条件查询语法格式如下：WHEREsearch_condition例如，列出St_Info表中年龄在20岁以上的学生记录。,指定查询结果集合应满足的条件,精选,12,4.2基本查询,LIKE的通配符SQL提供了LIKE子句来进行模糊搜索。,精选,13,4.2基本查询,例4-1：列出St_Info表中姓“张”的男学生的信息。SELECT*FROMSt_InfoWHERESt_Sex=男ANDSt_NameLIKE张%或SELECT*FROMSt_InfoWHERESt_Sex=男ANDLEFT(St_Name,1)=张,返回从字符串左边开始指定个数的字符,精选,14,4.2基本查询,WHERE子句中的条件运算符,精选,15,4.2基本查询,例4-2：列出St_Info表中年龄在18到19之间的学生名单。SELECT*FROMSt_InfoWHEREYEAR(GETDATE()-YEAR(Born_date)BETWEEN18AND19此语句中的WHERE子句等价于以下形式：WHEREYEAR(GETDATE()-YEAR(Born_date)=18ANDYEAR(GETDATE()-YEAR(Born_date)=80,精选,25,4.2基本查询,4.重定向输出(INTO)INTO子句用于把查询结果存放到一个新建的表中，语法格式：INTOnew_table,new_table指定了新建表的名称。,精选,26,4.2基本查询,例4-9：统计出S_C_Info表中每个学生所修课程的平均成绩，并存放New_C表中，其列名为学号、平均分。SELECTSt_IdAS学号,AVG(Score)AS平均分INTONew_CFROMS_C_InfoGROUPBYSt_Id查询结果集不是直接显示在查询分析器的结果窗格中，而存放在New_C表中。,精选,27,4.2基本查询,5.使用COMPUTE和COMPUTEBY子句汇总语法格式：COMPUTEAVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM(expression),.nBYexpression,.nCOMPUTE子句使用的行聚合函数：,指定要执行的聚合函数,精选,28,4.2基本查询,例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),精选,29,4.2基本查询,例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,精选,30,4.2基本查询,注意：COMPUTE子句中指定的列必须是SELECT子句中已有的。COMPUTEBY必须与ORDERBY子句一起使用，且COMPUTEBY中指定的列必须与ORDERBY子句中指定的列相同，且排列顺序也必须相同。COMPUTE子句中不能使用text或image数据类型，不能与SELECTINTO子句一起使用。DISTINCT关键字不能与聚合函数一起使用。COMPUTE和GROUPBY之间的区别是：GROUPBY生成单个结果集；COMPUTE生成多种结果集，一种结果集按照分类要求包含每个组的明细行，另一种结果集在分类基础上进行聚合运算。,精选,31,4.3嵌套查询,嵌套查询(子查询)在一个SELECT语句的WHERE子句或HAVING子句中嵌套另一个SELECT语句的查询称为嵌套查询，又称子查询。即在一个外层查询中包含有另一个内层查询。其中外层查询称为主查询，内层查询称为子查询。嵌套查询分为:单值嵌套查询多值嵌套查询,精选,32,4.3嵌套查询,4.3.1单值嵌套查询子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。例4-12：对Student_db数据库，列出选修了“大学计算机基础”的所有学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=(SELECTC_NoFROMC_InfoWHEREC_Name=大学计算机基础),精选,33,4.3嵌套查询,4.3.2多值嵌套查询子查询的返回结果是结果集的嵌套查询称为多值嵌套查询。若某个子查询的返回值不止一个，则必须在WHERE子句中指明如何使用这些返回值。通常使用条件运算符：ANYALLINEXISTS等实现。,ALL表示大于每一个值；换句话说，大于最大值。例如，ALL(1,2,3)表示大于3。ANY表示至少大于一个值，也就是大于最小值。因此ANY(1,2,3)表示大于1。,精选,34,逻辑运算符,精选,35,4.3嵌套查询,使用ANY运算符例4-13：列出Student_db数据库中选修“9710011”（即“大学计算机基础”）课程的学生的成绩比选修“29000011”（“体育”）课程的学生的最低成绩高的学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=9710011ANDScoreANY(SELECTScoreFROMS_C_InfoWHEREC_No=29000011),满足子查询中任意一个值的记录。,精选,36,SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=9710011ANDScoreANY(SELECTScoreFROMS_C_InfoWHEREC_No=29000011)goSELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=9710011go(SELECTScoreFROMS_C_InfoWHEREC_No=29000011),精选,37,4.3嵌套查询,2.使用ALL运算符例4-14：列出Student_db数据库中选修“29000011（体育）”的学生的成绩比选修“9710011（大学计算机基础）”的学生的最高成绩还要高的学生的学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_No=29000011ANDScoreALL(SELECTScoreFROMS_C_InfoWHEREC_No=9710011),满足子查询中所有值的记录,精选,38,4.3嵌套查询,3.使用IN运算符例4-15：列出Student_db数据库中选修“体育”或选修“大学计算机基础”课程的学生学号和成绩。SELECTSt_Id,ScoreFROMS_C_InfoWHEREC_NoIN(SELECTC_NoFROMC_InfoWHEREC_Name=大学计算机基础ORC_Name=体育),字段内容是结果集合或者子查询中的内容,精选,39,4.4联接查询,4.4.1联接类型4.4.2内联接4.4.3外联接,精选,40,4.4联接查询,联接概述一个查询同时涉及两个或两个以上的表，则称之为联接查询。联接查询是关系数据库中最主要的查询。联接查询包括内联接、外联接和交叉联接等。联接查询可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接建立可以在SELECT语句的WHERE子句中建立。,精选,41,4.4联接查询,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例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,精选,42,4.4联接查询,联接在FROM子句中建立在FROM子句中指出联接时有助于将联接操作与WHERE子句中的搜索条件区分开来。在Transact-SQL中推荐使用这种方法。FROM子句建立联接的语法格式是：FROMjoin_tablejoin_typeJOINjoin_tableONjoin_condition,联接表,联接条件,被联接表,联接类型,精选,43,4.4联接查询,例4-18：列出S_C_Info和C_Info表中选修“大学计算机基础”和“体育”两门课程的学生信息。SELECT*FROMS_C_InfoJOINC_InfoONS_C_Info.C_No=C_Info.C_NoWHEREC_NameIN(大学计算机基础,体育),SELECT*FROMS_C_InfoeJOINC_InfodONe.C_No=d.C_NoWHEREd.C_NameIN(大学计算机基础,体育),精选,44,4.4联接查询,例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,精选,45,4.4联接查询,4.4.2内联接(Innerjoin)内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。内联接（典型的联接运算，使用像=或之类的比较运算符）的三种类型：等值联接不等值联接自然联接,精选,46,4.4联接查询,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=男),精选,47,4.4联接查询,2不等值联接在联接条件中使用除等于运算符以外的其他比较运算符比较被联接的列的列值。这些运算符包括：、=、!。例4-20：列出Student_db数据库中选修“9720013”课程的成绩大于学号为“2001050108”的该门课程成绩的学生的学号及成绩。SELECTa.St_Id,a.ScoreFROMS_C_InfoaINNERJOINS_C_InfobONa.Scoreb.ScoreANDa.C_No=b.C_NoWHEREb.St_Id=2001050108ANDb.C_No=9720013,精选,48,4.4联接查询,3自然联接在联接条件中使用等于(=)运算符比较被联接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除联接表中的重复列。例4-21：实现St_Info表和S_C_Info表的自然联接。SELECTa.St_Name,b.*FROMSt_InfoaINNERJOINS_C_InfobONa.St_Id=b.St_Id,精选,49,4.4联接查询,等值联接与自然联接的区别等值联接其查询结果中列出被连接表中的所有列，包括其中的重复列。自然联接使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。-使用等值连接SELECT*FROMSt_InfoaINNERJOINS_C_InfobONa.St_Id=b.St_Idgo-使用自然连接，在选择列表中删除st_info和S_C_Info表中重复列st_id.SELECTa.St_Name,b.*FROMSt_InfoaINNERJOINS_C_InfobONa.St_Id=b.St_Id,精选,50,4.4联接查询,4.4.3外联接外联接查询：返回查询结果集合中的不仅包含符合联接条件的行（返回FROM子句中提到的至少一个表的所有行）;还包括左表(左外联接时);或右表(右外联接时);或两个联接表(全外联接)中的所有数据行。分类：左外联接:LEFTOUTERJOIN或LEFTJOIN右外联接:RIGHTOUTERJOIN或RIGHTJOIN全外联接:FULLOUTERJOIN,精选,51,4.4联接查询,1使用左外联接左外联接包括左表（出现在JOIN子句的最左边）的所有行，不包括右表中的不匹配行。例4-22：St_Inf