第4章-数据库查询.ppt

,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,数据库技术与应用,*,数据库技术与应用,数据库技术与应用教材编写组,第,4,章 数据库查询,查询概述,基本查询,嵌套查询,连接查询,嵌套查询、连接查询,2,问题提出,为什么要查询数据？,如何实现查询？,3,？,4.1,查询概述,4.1.1,图形界面的菜单方式,4.1.2,查询语句,SELECT,4,4.1,查询概述,在数据库应用中，最常见的操作是数据查询，它是数据库系统中最重要的功能，也是数据库其他操作（如统计、插入、删除及修改）的基础。无论是创建数据库、还是创建数据表等最终的目的都是为了使用数据，而使用数据的前提是需要从数据库中获取数据库所提供的数据信息。,5,4.1.1,图形界面的菜单方式,在,SSMS,图形界面中，通过,“,对象资源管理器,”,可以直接查询数据表中的数据。,6,4.1.2,查询语句,SELECT,数据查询语句,SELECT,的基本框架是,:,SELECT,-FROM-,WHERE,语法格式：,SELECT,select_list,/*,指定要选择的列或行及其限定*,/,INTO,new_table,/*,指定结果存入新表*,/,FROM,table_source,/*,指定数据来源的表和视图*,/,WHERE,search_condition,/*,指定查询条件*,/,GROUP BY,group_by_expression,/*,指定分组表达式*,/,HAVING,search_condition,/*,指定分组统计条件*,/,ORDER BY,order_expression,ASC|DESC,/*,指定查询结果的排序方式*,/,7,需要哪些列,从哪些表,根据什么条件,4.2,基本查询,4.2.1,简单查询,4.2.2,条件查询,4.2.3,查询结果处理,8,4.2.1,简单查询,简单查询是指,SELECT,语句只包含,SELECT,子句和,FROM,子句的操作，涉及的对象是单表中的列，即在查询过程对一张表的列进行操作。,语法格式：,SELECT ALL|DISTINCT TOP n PERCENT,select_list,FROM,table_name,其中,:,ALL,表示输出所有记录，包括重复记录。,DISTINCT,表示输出无重复结果的记录。,TOP n,指定返回查询结果的前,n,行数据。,select_list,中的选项可以是：、字段名、表达式或函数。,9,4.2.1,简单查询,查询全部列或指定列,SELECT ALL,*,FROM,st_info,SELECT,all,st_name,FROM,st_Info,消除重复行或定义列别名,SELECT,DISTINCT,St_name,FROM,st_Info,SELECT,DISTINCT,st_id,St_name,FROM,st_Info,SELECT,st_name,as,姓名,st_sex,AS,性别,FROM,st_Info,SELECT,st_name,姓名,st_sex,性别,FROM,st_Info,10,4.2.1,简单查询,限制结果集的行数,例,4.5,：对,St_info,表选择姓名、性别查询，返回结果集中前,5,行。,SELECT,top 5,st_name,AS,姓名,St_Sex,as,性别,FROM,st_info,又如：,SELECT,top 3,St_id,FROM,s_c_Info,SELECT,top 20 PERCENT,St_id,FROM,s_c_Info,/*,返回结果集中前,20%,行,*/,11,4.2.1,简单查询,计算列值,例,4.6,按,120,分计算成绩并显示,S_C_Info,表中前,5,行学生的成绩情况。,SELECT,TOP 5,St_ID,学号,C_No,课程编号,成绩,120=Score*1.2,FROM,S_C_Info,12,4.2.1,简单查询,计算列值,使用聚合函数,是对一组值执行计算并返回单一的值的函数,常用聚合函数,13,函,数,功,能,函,数,功,能,AVG(),求一列数据的平均值,MIN(),求列中的最小值,SUM(),求一列数据的和,MAX(),求列中的最大值,COUNT(*),统计查询的行数,？什么是聚合函数,4.2.1,简单查询,例,4.74.8,：分别查询,St_Info,表的学生总数和平均年龄,查询学生总数（使用,COUNT(*),）,SELECT,COUNT,(*)AS,总数,FROM,St_Info,查询学生的平均年龄（使用,AVG,）,SELECT,AVG,(YEAR(GETDATE()-,YEAR(Birthdate,),AS,平均年龄,FROM,St_info,14,4.2.2,条件查询,语法格式：,WHERE,search_condition,条件,运算符：,比较运算,逻辑运算,字符匹配运算,范围比较运算,空值比较运算,15,4.2.2,条件查询,语法格式：,WHERE,search_condition,16,4.2.2,条件查询,比较运算,例,4.12,，查询,St_Info,表中,1998,年以前出生的学生情况，要求列出学号、姓名、出生年份和所在班级。,SELECT,St_ID,St_Name,YEAR(Birthdate,),出生年份,Cl_Name,FROM,St_Info,WHERE,YEAR(Birthdate,)=80,and,Score90,17,这是一个关系表达式,这是一个逻辑表达式,4.2.2,条件查询,字符匹配运算,LIKE,关键字的语法格式：,match_ expression NOT,LIKE,pattern ESCAPE,escape_character,18,运算符,描述,示例,%,包含零个或多个字符的任意字符串,address LIKE%,公司,%,将查找地址任意位置包含公司的所有职员,_,下划线，对应任何单个字符,employee_name,LIKE _,海燕,将查找以“海燕”结尾的所有,6,个字符的名字,指定范围（如,a-f,）或集合（如,abcdef,）中的任何单个字符,employee_name,LIKE,张李王,海燕,将查找张海燕、李海燕、王海燕等,不属于指定范围或集合的任何单个字符,employee_name,LIKE,张李,海燕,将查找不姓张、李的名为海燕的职员,4.2.2,条件查询,例,4.14:,查询,st_info,表中姓“张”的男学生的信息。,SELECT*,FROM,st_info,WHERE,st_name,LIKE,张,%,AND,st_sex,=,男,其中等价的形式：,WHERE,LEFT(,st_name,1)=,张,AND,st_sex,=,男,例,4.15,：在,St_Info,表中查询学号倒数第,3,个数为,1,，倒数第,1,个数在,14,之间的学生的学号、姓名、班级信息。,SELECT,St_ID,St_Name,Cl_Name,FROM,St_Info,WHERE,St_ID,LIKE%1_1234,19,4.2.2,条件查询,例,4.16,：在,St_Info,表中，查询所有“口腔”班，名叫“小玲”的学生的学号、姓名、班级信息。,SELECT,St_ID,St_Name,Cl_Name,FROM,St_Info,WHERE,st_name,like,_,小玲,%,and,Cl_Name,like,口腔,%,例,4.17,：在,St_Info,表中，查询学生,“张好然”和“杨平娟”,的信息。要求显示学号、姓名、班级和电话号码。注意此表中是用下划线（,_,）将区号与电话号码连接的。,SELECT,St_ID,St_Name,Cl_Name,Telephone,FROM,St_Info,WHERE Telephone,like%#_%ESCAPE#,/*,定义,#,为转义字符*,/,20,4.2.2,条件查询,范围比较运算,语法格式：,expression NOT,BETWEEN,begin_expression,AND,end_expression,例,4.18,：在,St_Info,表中查询,1984,年出生的学生信息。,SELECT*,FROM,St_Info,WHERE,Birthdate,BETWEEN,1997-1-1 AND 1997-12-31,21,4.2.2,条件查询,例,4.13,：在,st_info,表中，查询年龄在,20,21,岁之间的学生信息。,SELECT*,FROM,st_info,WHERE YEAR(GETDATE()-,YEAR(Birthdate,),BETWEEN,20 AND 21,22,此句的等价形式是什么？,4.2.2,条件查询,若要查询的是列表中的数据，可以使用,IN,关键字,语法格式：,expression NOT,IN,(expression ,.n ),例,4.20,：在,st_info,表中，,“法学,1601”,、“法学,1701”,和“材料科学,1701”,班的学生信息。,SELECT *,FROM,St_Info,WHERE,Cl_Name,IN,(,法学,1601,法学,1701,材料科学,1701),23,4.2.2,条件查询,空值比较运算,例,4.21,：对,st_info,表，查询所有,Telephone,为空值的学生的信息。,SELECT*,FROM,st_info,WHERE Telephone,IS NULL,24,可以写成：,Telephone,=NULL,?,空值表示值未知。,空值不同于空白或零值。,没有两个相等的空值。,4.2.3,查询结果处理,排序输出（,ORDER BY,）,语法格式：,ORDER BY order_by_expression1ASC|DESC,order_by_expression2ASC|DESC,例,4.22,：对,st_info,表，按性别顺序列出学生的信息，性别相同的再按年龄由小到大排序。,SELECT*,FROM,st_info,ORDER BY,st_sex,BirthDate,DESC,25,4.2.3,查询结果处理,重定向输出（,INTO,）,语法格式：,INTO,new_table,例,4.23,：对,s_c_info,表，查询选修“大学计算机基础”（课程号为“,9710011”,）课程的所有学生信息，并将结果存入,newstudent,表中。,SELECT,st_id,学号,c_no,大学计算机基础,score,成绩,INTO,newstudent,FROM,s_c_info,WHERE,c_no,=9710011,26,4.2.3,查询结果处理,输出合并（,UNION,）,语法格式：,UNION ALL,例,4.24,：对,c_info,表，列出课程编号为“,9710011”,或“,9720033”,的课程名称和学分。,SELECT,c_name,c_credit,FROM,c_info,WHERE,c_no,=9710011,UNION,SELECT,c_name,c_credit,FROM,c_info,WHERE,c_no,=9720033,27,4.2.3,查询结果处理,分组统计（,GROUP BY,）与筛选（,HAVING,）,语法格式：,GROUP BY group_by_expression1,group_by_expression2,例,4.25,：对,st_info,表，分别统计男女学生人数。,SELECT,st_sex,COUNT(st_sex,)FROM,st_info,GROUP BY,st_sex,例,4.21,：对,s_c_info,表，查询平均成绩大于,80,的课程编号和平均成绩。,SELECT,c_no,AVG(score,)AS,平均成绩,FROM,s_c_info,GROUP BY,c_no,HAVING,AVG(score,)=80,28,WHERE,与,HAVING,两者的区别,?,4.2.3,查询结果处理,使用,COMPUTE,和,COMPUTE BY,子句汇总,语法格式：,COMPUTE,row_aggregate(column_name,),row_aggregate(column_name,).,BY,column_name,column_name,.,例,4.28,：,列出,St_Info,表中“材料科学,1701”,班学生的年龄及平均年龄,（即年龄的明细行和汇总行）。,SELECT,St_ID,YEAR(GETDATE,()-,YEAR(Birthdate,)AS,年龄,FROM,St_Info,WHERE,Cl_Name,=材料科学1701,ORDER BY,St_ID,COMPUTE AVG(YEAR(GETDATE()-,YEAR(Birthdate,),29,4.2.3,查询结果处理,例,4.29,：,对,St_Info,表中“材料科学,1701”,和“口腔（七）,1701”,班学生的年龄，生成分组汇总行和明细行,。,SELECT,St_ID,YEAR(GETDATE,()-,YEAR(Birthdate,)AS,年龄,FROM,St_Info,WHERE,Cl_Name,=材料科学1701 OR,Cl_Name,=口腔（七）1701,ORDER BY,Cl_Name,COMPUTE SUM(YEAR(GETDATE()-,YEAR(Birthdate,),BY,Cl_Name,30,注意：,COMPUTE,和,COMPUTE BY,区别,4.3,嵌套查询,4.3.1,单值嵌套查询,4.3.2,多值嵌套查询,31,4.3,嵌套查询,在一个,SELECT,语句的,WHERE,子句或,HAVING,子句中嵌套另一个,SELECT,语句的查询称为嵌套查询，又称子查询。,嵌套查询的类型,单值嵌套查询,多值嵌套查询,32,4.3.1,单值嵌套查询,子查询的返回结果是一个值的嵌套查询称为,单值,嵌套查询。,例,4.30,：对,student_db,数据库，查询选修“大学计算机基础”的所有学生的学号和成绩。,SELECT,st_id,score,FROM,s_c_info,WHERE,c_no,=(,SELECT,c_no,FROM,c_info,WHERE,c_name,=,大学计算机基础,),33,内查询的结果作为外查询的条件,4.3.2,多值嵌套查询,子查询的返回结果是一列值的嵌套查询称为,多值,嵌套查询。,若某个子查询的返回值不止一个，则必须在,WHERE,子句中指明如何使用这些返回值。通常使用条件运算符：,ANY,ALL,IN,34,ALL,表示大于,每一个,值；即大于最大值。,例如，,ALL(1,2,3),表示大于,3,。,ANY,表示,至少,大于一个值，即大于最小值。,例如，,ANY(1,2,3),表示大于,1,。,4.3.2,多值嵌套查询,使用,ANY,运算符,例,4.31,：,对,Student,数据库，查询选修“,9710011”,即“大学计算机基础”课程的学生的成绩比选修“,29000011”,即“体育”课程的学生的最低成绩高的学生的学号和成绩,。,SELECT,st_id,score,FROM,s_c_info,WHERE,c_no,=9710011 and score,ANY,(,SELECT score FROM,s_c_info,WHERE,c_no,=29000011,),35,4.3.2,多值嵌套查询,使用,ALL,运算符,ALL,运算符指定子查询结果集中每个值都满足比较条件时返回,TURE,，否则返回,FALSE,。,例,4.32,：,对,Student,数据库，列出选修“,29000011”,即“体育”的学生的成绩比选修“,9710011”,即“大学计算机基础”的学生的最高成绩还要高的学生的学号和成绩,。,SELECT,st_id,score,FROM,s_c_info,WHERE,c_no,=29000011 and score,ALL,(SELECT score FROM,s_c_info,WHERE,c_no,=9710011),36,4.3.2,多值嵌套查询,使用,IN,运算符,IN,是属于的意思，等价于“,=ANY”,，即等于子查询中任何一个值。,例,4.33,：,对,Student,数据库，列出选修“,29000011”,即“体育”或选修“,9710011”,即“大学计算机基础”的学生学号和成绩,。,SELECT,st_id,score,FROM,s_c_info,WHERE,c_no,IN,(SELECT,c_no,FROM,c_info,WHERE,c_name,=,大学计算机基础,OR,c_name,=,体育,),37,4.4,连接查询,4.4.1,自连接,4.4.2,内连接,4.4.3,外连接,4.4.4,交叉连接,38,4.4,连接查询,连接查询的概念,同时涉及多个表的查询称为连接查询。,可根据各个表之间的逻辑关系从两个或多个表中检索数据。,连接查询的类型,自连接,(Self join),内连接（,Inner join,）,外连接（,Outer join,）,交叉联接（,Cross join,）,39,4.4,连接查询,连接查询的建立,在,WHERE,子句中建立,在连接,FROM,子句中建立,连接字段,连接谓词中的列名称为连接字段。,连接条件中的各,连接字段类型必须是可比,的，但不必是相同的。,连接的结果,一个表中的行和与另外一个表中的行匹配连接。表中的数据决定了如何对这些行进行组合。从每一个表中选取一行，根据这些列的值是否相同，,组合方式分为一对一、多对一和多对多的关系。,40,4.4,连接查询,在,SELECT,语句的,WHERE,子句中建立连接,当需要对两个或多个表连接时，可以指定连接的列，在,WHERE,子句中给出连接条件，在,FROM,子句中指定要连接的表。,在,FROM,子句中建立连接（,推荐使用,）,在,FROM,子句中指出连接时有助于将连接操作与,WHERE,子句中的搜索条件区分开来。,FROM,子句连接的语法格式：,FROM,join_table,join_type,JOIN,join_table,ON,join_condition,41,4.4.1,自连接,自连接,(Self join),是指一个表自己与自己建立连接，也称为自身连接。,例,4.35,：,查询选修“大学计算机基础”（,9710011,）课程的成绩高于学号为“,2001160308”,学生的成绩的所有学生信息，并按成绩从高到低排列,。（,在,WHERE,子句中建立连接,）,SELECT x.*,/*,将成绩表,s_c_info,分别取别名为,x,和,y*/,FROM,s_c_info,x,s_c_info,y,WHERE,x.C_No,=9710011,And,x.Score,y.Score,And,y.St_ID,=2001160308,And,y.C_No,=9710011,ORDER BY,x.score,DESC,42,在,FROM,子句中指定要连接的表，在,WHERE,子句中给出连接条件。,4.4.2,内连接,内连接（,Inner join,）使用比较运算符进行表间某,(,些,),列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。,内连接类型,等值连接,不等值连接,43,4.4.2,内连接,等值连接,在连接条件中使用等号“,=”,运算符比较被连接列的列值，按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括其中的重复列，这种连接称为等值连接。,例,4.36,：在,Student,数据库中，查询所有选课学生的学号、所选课程的名称和成绩。（,在,WHERE,子句中建立连接,）,SELECT,x.st_id,y.c_name,x.score,FROM,S_C_Info,x,C_Info,y,WHERE,x.c_no,=,y.c_no,44,在,WHERE,子句中用“,=,”形式进行连接。,4.4.2,内连接,等值连接,例,4.38,：,在,Student,数据库中，查询学生的选课情况。要求列出选课表,s_c_info,中的所有列，学生信息表,st_info,中的学生姓名,st_name,列。,SELECT b.*,FROM,St_Info,a,INNER JOIN,S_C_Info,b,ON,a.St_ID,=,b.St_ID,SELECT,a.st_name,b.*,FROM,st_info,a,INNER JOIN,s_c_info,b,ON,a.st_id,=,b.st_id,当连接的两个表，两个连接列的值完全相同，则两个表连接相当于一对一的关系。,45,在,FROM,子句中指出连接,4.4.2,内连接,两种查询操作比较,46,连接的结果集为,1,：,1,关系,等值连接的特例自然连接,4.4.2,内连接,不等值连接,使用的运算符包括：,、,=,、,=,、,、,!,b.score,AND,a.c_no,=,b.c_no,WHERE,a.C_No,=9710011 AND,b.St_ID,=2001160308,ORDER BY,a.score,DESC,47,在,FROM,子句中指出连接,4.4.3,外连接,外连接（,Outer join,）,左外连接（,Left outer join,）,右外连接,(Right outer join),全外连接,(Full outer join),48,4.4.3,外连接,左外连接,使用,LEFT OUTER JOIN,关键字进行连接。左外连接保留了第一个表的所有行，但只包含第二个表与第一个表匹配的行。第二个表相应的空行被放入,NULL,值。,例,4.41,：,st_info,表左外连接,s_c_info,表，列出女同学的信息,SELECT,a.St_ID,St_Name,St_Sex,C_No,Score,FROM,St_Info,a,LEFT OUTER JOIN,S_C_Info,b,ON,a.St_ID,=,b.St_ID,WHERE,St_Sex,=,女,49,4.4.3,外连接,50,查询前,左外连接保留了第一个表的所有行，但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入,NULL,值。,左外连接，查询后,一对多关系,4.4.3,外连接,右外连接,右外连接使用,RIGHT OUTER JOIN,关键字进行连接。右外连接通过右向外连接引用右表的所有行。,例,4.42,：,st_info,表右外连接,s_c_info,表。,为了说明方便，先在,s_c_info,表中插入一条选课信息，此信息的学号在,st_info,表中不存在。,INSERT INTO,s_c_info,(,st_id,c_no,score,),VALUES(2001060155,29000011,100),go,SELECT,a.st_id,a.st_name,b.c_no,b.score,FROM,st_info,a,RIGHT OUTER JOIN,s_c_info,b,ON,a.st_id,=,b.st_id,51,4.4.3,外连接,52,SELECT,a.st_id,a.st_name,b.c_no,b.score,FROM,st_info,a,RIGHT OUTER JOIN,s_c_info,b,ON,a.st_id,=,b.st_id,右外连接保留了第二个表的所有行，但只包含第一个表与第二个表匹配的行。第一个表相应空行被入,NULL,值。,右外连接，查询后,查询前,一对多关系,4.4.3,外连接,全外连接,使用,FULL OUTER JOIN,关键字连接，它返回两个表的所有行。不管两个表的行是否满足连接条件，均返回查询结果集。对不满足连接条件的记录，另一个表相对应字段用,NULL,代替。,例,4.43:st_info,表全外连接,s_c_info,表,SELECT,st_info.st_id,st_name,s_c_info.c_no,score,FROM,st_info,FULL OUTER JOIN,s_c_info,ON,st_info.st_id,=,s_c_info.st_id,53,4.4.4,交叉连接,交叉连接（,Cross join,）没有,WHERE,子句,，它返回连接表中所有数据行的笛卡尔积。,笛卡尔积结果集的大小为第一个表的行数乘以第二个表的行数。,交叉连接使用关键字,CROSS JOIN,进行连接。,例,4.44:,将,st_info,表和,c_info,表进行交叉连接。,SELECT,St_Info.St_Name,c_info,.*,FROM,St_Info,CROSS JOIN,C_Info,54,本章小结,（,1,）,SELECT,查询语句,基本语法格式，子句及执行顺序。,子句的使用。,（,2,）简单查询,查询列（聚合函数）；,选择行（比较表达式、逻辑表达式、限定范围、限制检索、模糊查询）,分组与汇总,（,3,）连接查询,FROMWHERE,子句连接查询,FROM,子句的,ANSI,模式连接查询（内、外、交叉连接）,（,4,）子查询,单列单值子查询、单列多值子查询、多列多值子查询,（,5,）联合查询,并运算联合查询、差运算联合查询、交运算联合查询,55,本章思考,（,1,）在,SQL,的查询语句,SELECT,中，使用什么选项实现投影运算？什么选项实现连接运算？什么选项实现选择运算？,（,2,）一个子,SELECT,的结果作为查询的条件，即在一个,SELECT,语句的,WHERE,子句中出现另一个,SELECT,语句，这种查询称为什么查询？,（,3,）在,SELECT,语句中，定义一个区间范围的特殊运算符是什么？检查一个属性值是否属于一组值中的特殊运算符又是什么？,（,4,）在,T-SQL,语句中，与表达式,“,工资,BETWEEN 2000 AND 5000,”,功能相同的表达式如何写？,（,5,）语句,“,SELECT*FROM,成绩表,WHERE,成绩,(SELECT,avg,(,成绩,)FROM,成绩表,),”,的功能是什么？,56,本章结束,57,