数据库技术.ppt

数据库技术,第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,查询数据库表中的数据并返回符合用户查询条件的结果数据。数据查询语句是SQL语言的核心。 Select语句由多个子句构成，其语法形式为： SELECT 字段表 FROM 表名 WHERE 查询条件 GROUP BY 分组字段 HAVING 分组条件 ORDER BY 字段名 ASC/DESC 表示可选项。 在书写SELECT语句时，字母大小写意义完全相同。 语句可以写在若干行上（如上述语法格式中的描述），也可以不换行。,查询语句SELECT,4.2.1 简单查询,(1)字段表 字段表位于关键字SELECT后面，这些字段将作为查询的结果被显示。使用字段表规则： 1) 字段与字段之间用逗号分隔。 【例4-1】使用SELECT语句查询Student表中的学生姓名（StudentName）和性别（Sex）。 SELECT StudentName, Sex FROM Student,2) 可以使用通配符“ * ”表示表中的所有字段。 【例4-2】 使用SELECT语句查询 Student表中所有字段的值。 SELECT * FROM Student,4.2.1 简单查询,3) 如果字段名或表名中含有空格，则该字段名或表名必须用方括号括起。 4) 在字段表中，可以使用以下方式将原字段名以新的字段名显示 字段名 AS 新字段名,【例4-3】 显示ClassInfo表中班级代号，并将ClassCode字段改为Class Number。 SELECT ClassCode AS Class Number FROM ClassInfo,注意：字段别名Class Number含有空格，所以用方括号括起,（1）字段表,4.2.1 简单查询,5) 在字段名前可加上一些范围限制，以便进一步优化查询结果。常用的范围关键字有： TOP n ：显示前n条记录。 TOP m PERCENT ：显示前m%的记录。,【例4-4】查询Course表中的前3条记录。 SELECT TOP 3 * FROM Course,【例4-5】查询Course表中前30%的记录。 SELECT TOP 30 PERCENT * FROM Course,（1）字段表,4.2.1 简单查询,DISTINCT：若多条记录的字段值具有相同数据，只显示一条记录。 【例4-6】查询Course表中开课的学院代码AcademyCode（与所开课程无关）。 SELECT DISTINCT AcademyCode FROM Course,（1）字段表,4.2.1 简单查询,6) 为了增强查询功能，在SELECT语句中还可以使用内部聚合函数，它们被用来进行一些简单的统计或计算。常用统计函数如下表所示。,（1）字段表,4.2.1 简单查询,【例4-7】计算学生的总分、平均分、最高分、最低分及学生选课数。 SELECT Sum (Grade) as 总分, Avg (Grade) as 平均分, Max (Grade) as 最高分, Min (Grade) as 最低分, Count (StudentCode) as 课程人数 FROM Grade,（1）字段表,4.2.1 简单查询,7）使用SELECT语句复制新表。在字段名后加上INTO 表名可将查询结果复制到一张新表中。,【例4-8】将Student表中所有记录的学生姓名、出生日期两个字段复制一张新表NewTable。 SELECT StudentName, BirthDay INTO NewTable FROM Student 运行后，产生一张表名为NewTable的新表。,（1）字段表,4.2.1 简单查询,(2) FROM子句 FROM 表名1 ，表名2，表名n ,1) 在FROM子句中，表的先后次序可以任意排列。 2) FROM子句中若包含多个表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必须表示成 “.” 。,【例4-9】查询Student表和Grade表，报告StudentCode、 StudentName、 CourseCode和Grade。 SELECT StudentCode, StudentName, CourseCode, Grade FROM Student, Grade 正确语句： SELECT Student.StudentCode, StudentName, CourseCode, Grade FROM Student, Grade,4.2.1 简单查询,WHERE子句 设置查询的条件，它是一个可选的子句。在使用时，WHERE子句必须在FROM子句的后面。,WHERE子句的语法格式： WHERE 查询条件 查询条件是一个逻辑表达式。常用的运算符有：,4.2.2 条件查询,【例4-11】查询未住校的女学生的情况。 SELECT StudentCode, StudentName, Sex, LiveInDorm FROM Student WHERE Sex=女 and LiveInDorm=0,比较和逻辑运算 比较运算用于比较两个表达式的值；逻辑运算用来连接多个查询条件。,【例4-10】在Student表中查询所有女同学的姓名。 SELECT StudentCode, StudentName, Sex, LiveInDorm FROM Student WHERE Sex=女,4.2.2 条件查询,2)确定范围BETWEEN AND BETWEEN 值1 AND 值2:查询值在值1至值2之间的记录 NOT BETWEEN 值1 AND 值2:查询不在指定范围中的记录,【例4-12】在Grade表中查询课程代号（CourseCode）为“110001”、成绩（Grade）在70分到90分之间的学生代号（StudentCode）及成绩。 SELECT StudentCode, CourseCode, Grade FROM Grade WHERE CourseCode=110001 AND Grade BETWEEN 70 AND 90 此例的WHERE子句也可改为： WHERE CourseCode=110001 AND Grade= 70 AND Grade=90,【例4-13】查询Student表中不在1986年到1995年中出生的学生学号和姓名。 SELECT StudentCode, StudentName, Birthday FROM Student WHERE Birthday NOT BETWEEN 1986-01-01 AND 1995-12-12,4.2.2 条件查询,3)确定集合IN运算 使用IN运算可查询某些字段值是否包含在所列出的指定值的记录.,【例4-14】查询Student表中班号（ClassCode）为“153030301”、“153030302” 的学生。 SELECT StudentCode, StudentName, Sex, ClassCode FROM Student WHERE Classcode IN (153030301,153030302),此例的WHERE子句也可以改为： WHERE ClassCode=153030301 OR ClassCode=153030302,4.2.2 条件查询,4)字符匹配LIKE 指定字段的值是否包含在给定的字符串中，其结果是满足字符串匹配的数据记录。 通配符“_”表示任意单个字符；“%”表示包含零个或更多字符的任意字符串。,【例4-15】查询Course表中课程名称（CourseName）中包含“化学”两个字的课程代号（CourseCode）及课程名称。 SELECT CourseCode,CourseName FROM Course WHERE CourseName LIKE %化学%,4.2.2 条件查询,4.2.2 条件查询,5）空值比较运算 例4.15：对st_info表，查询所有Telephone为空值的学生的信息。 SELECT * FROM st_info WHERE Telephone IS NULL,23,可以写成： Telephone = NULL,?,空值表示值未知。 空值不同于空白或零值。 没有两个相等的空值。,4.2.3 查询结果处理,排序输出（ORDER BY） 语法格式： ORDER BY order_by_expression1ASC|DESC ,order_by_expression2ASC|DESC , 例4.16：对st_info表，按性别顺序列出学生的信息，性别相同的再按年龄由小到大排序。 SELECT * FROM st_info ORDER BY st_sex, BirthDate DESC,24,【例4-17】按成绩（Grade）升序显示Grade表中的所有数据。 SELECT * FROM Grade ORDER BY Grade,可以指定多个排序的字段。多字段排序的规则是：首先用指定的第一个字段对记录排序，然后对此字段中具有相同值的记录用指定的第二个字段进行排序，依此类推。 若无ORDER BY子句，则按原数据表的次序显示数据。,【例4-18】按姓名（StudentName）升序 班名（ClassName）降序查询学生及其班级信息。 SELECT StudentCode, StudentName, ClassName, Birthday FROM Student JOIN ClassInfo ON Student.ClassCode=ClassInfo.ClassCode ORDER BY Studentname ASC, ClassName DESC,4.2.3 查询结果处理,分组统计（GROUP BY）与筛选（HAVING） GROUP BY用于将指定字段中具有相同值的记录合并成一条记录。 语法格式： GROUP BY 分组字段 HAVING 分组条件 HAVING子句与WHERE子句的作用类似： 在使用GROUP BY完成分组后，显示满足HAVING子句中分组条件的所有记录。,【例4-19】按住校与否统计学生的平均成绩。 SELECT LiveInDorm, STR(AVG(Grade),5,2) AS 平均成绩 FROM Student JOIN Grade ON Student.StudentCode=Grade.StudentCode GROUP BY LiveInDorm,4.2.3 查询结果处理,【例4-20】统计课程代号（CourseCode）大于“110006”的各门课程的选课人数。 SELECT CourseCode, Count (StudentCode) AS 选课人数 FROM Grade GROUP BY CourseCode HAVING CourseCode 110006,4.2.3 查询结果处理,4.2.3 查询结果处理,重定向输出（INTO） 语法格式： INTO new_table 例4.21：对s_c_info表，查询选修“大学计算机基础”（课程号为“9710011”）课程的所有学生信息，并将结果存入newstudent表中。 SELECT st_id 学号, c_no 大学计算机基础, score 成绩 INTO newstudent FROM s_c_info WHERE c_no= 9710011,28,4.2.3 查询结果处理,输出合并（UNION） 语法格式： UNION ALL 例4.22：对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,29,4.3 嵌套查询,4.3.1 单值嵌套查询 4.3.2 多值嵌套查询,30,4.3 嵌套查询,在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询称为嵌套查询，又称子查询。 嵌套查询的类型 单值嵌套查询 多值嵌套查询,31,4.3.1 单值嵌套查询,子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。 例4.23：对student_db数据库，查询选修“大学计算机基础”的所有学生的学号和成绩。 SELECT st_id,score FROM s_c_info WHERE c_no=( SELECT c_no FROM c_info WHERE c_name=大学计算机基础 ),32,内查询的结果作为外查询的条件,【例4-24】查询“张三”同班同学的学号、姓名。 SELECT Student.StudentCode, Student.StudentName, Student.ClassCode FROM Student WHERE Student.ClassCode=( SELECT Student.ClassCode FROM Student WHERE Student.StudentName=张三 ),4.3.1 单值嵌套查询,4.3.2 多值嵌套查询,子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。 若某个子查询的返回值不止一个，则必须在WHERE子句中指明如何使用这些返回值。通常使用条件运算符： ANY ALL IN,34,ALL 表示大于每一个值；即大于最大值。 例如，ALL (1, 2, 3) 表示大于 3。 ANY 表示至少大于一个值，即大于最小值。 例如， ANY (1, 2, 3) 表示大于 1。,1) IN子查询 IN子查询用来判断一个给定值是否在子查询的结果集中。,【例4-25】查询选修了课程代码为“110001”的学生的学号、姓名和班级代号。,SELECT StudentCode, StudentName, ClassCode FROM Student WHERE StudentCode IN ( SELECT StudentCode FROM Grade WHERE CourseCode=110001 ),4.3.2 多值嵌套查询,2) EXISTS子查询 EXISTS子查询用于判断一个子查询的结果集是否为空，如果为空则返回TRUE，否则返回FALSE。NOT EXISTS的返回值与EXISTS相反。,【例4-26】查询选修了课程代码为“110001”的学生的学号、姓名和班级代号。用EXISTS子查询实现,SELECT StudentCode, StudentName, ClassCode FROM Student WHERE EXISTS ( SELECT * FROM Grade WHERE Student.StudentCode=Grade.StudentCode AND Grade.CourseCode=110001 ),4.3.2 多值嵌套查询,4.3.2 多值嵌套查询,使用ANY运算符 例4.27：对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 ),37,4.3.2 多值嵌套查询,使用ALL运算符 ALL运算符指定子查询结果集中每个值都满足比较条件时返回TURE，否则返回FALSE。 例4.28：对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 ),38,4.4 连接查询,4.4.1 自连接 4.4.2 内连接 4.4.3 外连接 4.4.4 交叉连接 4.4.5 多表连接嵌套,39,4.4 连接查询,连接查询的概念 同时涉及多个表的查询称为连接查询 。 可根据各个表之间的逻辑关系从两个或多个表中检索数据。 连接查询的类型 自连接(Self join) 内连接（Inner join） 外连接（Outer join） 交叉联接（Cross join）,40,4.4 连接查询,连接查询的建立 在WHERE子句中建立 在连接FROM子句中建立 连接字段 连接谓词中的列名称为连接字段。 连接条件中的各连接字段类型必须是可比的， 但不必是相同的。 连接的结果 一个表中的行和与另外一个表中的行匹配连接。表中的数据决定了如何对这些行进行组合。从每一个表中选取一行，根据这些列的值是否相同，组合方式分为一对一、多对一和多对多的关系。,41,在WHERE子句中设置查询条件。 【例4-29】查询未住校学生的选课及成绩情况 SELECT StudentName, CourseCode, Grade, LiveinDorm FROM Grade, Student WHERE LiveInDorm=0 AND Grade.StudentCode=Student.StudentCode,4.4 连接查询,在FROM子句中使用联接关键字将表与表联接在一起。 T-SQL查询嵌套在语言中使用时，多采用该方法 。语法格式： FROM 表1 联接关键字 表2 ON 表1.字段名1 表2.字段名2 其中： 表1、表2是被联接的表名； 字段名是被联接的字段。必须有相同的数据类型并包含同类数据，但不需要有相同的名称。 比较运算符：=、=、 联接关键字用于确定联接的方式，常用的有INNER JOIN （内联接）、LEFT OUTER JOIN（左外联接）、RIGHT OUTER JOIN（右外联接）、CROSS JOIN（交叉联接）；,4.4 连接查询,4.4.1 自连接,自连接(Self join)是指一个表自己与自己建立连接，也称为自身连接。 例4.30：查询选修“大学计算机基础”（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.Scorey.Score And y.St_ID=2001160308 And y.C_No=9710011 ORDER BY x.score DESC,44,在FROM子句中指定要连接的表，在WHERE子句中给出连接条件。,内连接（Inner join）使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。 内连接是系统默认的，可以将关键字INNER省略。 【例4-31】使用内联接查询未住校学生的选课及成绩情况。 SELECT StudentName, CourseCode, Grade, LiveinDorm FROM Student INNER JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0 本句的查询结果与【例4-29】结果完全相同。,4.4.2 内连接,LEFT OUTER JOIN左外联接，查询结果除了包含两张表中符合连接条件的记录外，还包含左表（写在关键字LEFT OUTER JOIN左边的表）中不符合联接条件、但符合WHERE条件的全部记录。 【例4-32】使用左外联接查询未住校学生的选课及成绩情况。 SELECT StudentName, CourseCode, Grade, LiveInDorm FROM Student LEFT OUTER JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0,本句的查询结果中有些记录的CourseCode和Grade字段的值为NULL，也就是说这些记录是不符合联接条件的（即在Grade中没有找到有相同StudentCode的记录），但由于使用了左外联接的查询方法，所以左表（Student）中的符合WHERE子句条件的全部记录显示在查询结果中。,4.4.3 外连接,RIGHT OUTER JOIN右外联接，查询结果除了包含两张表中符合联接条件的记录，还包含右表（写在关键字RIGHT OUTER JOIN右边的表）中不符合联接条件、但符合WHERE条件的全部记录。 【例4-33】使用右外联接查询未住校学生的选课及成绩情况。 SELECT StudentName, CourseCode, Grade, LiveInDorm FROM Student RIGHT OUTER JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0,本句的查询结果与使用内联接完全相同，但含义不同。结果相同是由于在表设计时，在表Student和Grade之间通过外键建立了参照完整性约束，表Grade中的所有StudentCode必须是Student中出现的值。,4.4.3 外连接,【例4-34】查询化工学院开设的课程的选修学生和考试成绩。 SELECT Course.CourseCode, Course.CourseName, StudentCode, AcademyName, Grade FROM Grade RIGHT JOIN Course ON Grade.CourseCode = Course