数据库技术.

Presentation on theme: "数据库技术."— Presentation transcript:

1 数据库技术

2 查询概述 基本查询 嵌套查询 连接查询 嵌套查询、连接查询
第4章 数据库查询 查询概述 基本查询 嵌套查询 连接查询 嵌套查询、连接查询

3 ？ 问题提出 为什么要查询数据？ 如何实现查询？

4 4.1 查询概述 4.1.1 图形界面的菜单方式 4.1.2 查询语句SELECT

5 4.1 查询概述 在数据库应用中，最常见的操作是数据查询，它是数据库系统中最重要的功能，也是数据库其他操作（如统计、插入、删除及修改）的基础。无论是创建数据库、还是创建数据表等最终的目的都是为了使用数据，而使用数据的前提是需要从数据库中获取数据库所提供的数据信息。

6 4.1.1 图形界面的菜单方式 在SSMS图形界面中，通过“对象资源管理器”可以直接查询数据表中的数据。

7 4.1.2 查询语句SELECT SELECT-FROM-WHERE 语法格式： 数据查询语句SELECT的基本框架是:
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 ]] /*指定查询结果的排序方式*/ 需要哪些列 从哪些表 根据什么条件

8 4.2 基本查询 4.2.1 简单查询 条件查询 查询结果处理

9 4.2.1 简单查询 查询语句SELECT 查询数据库表中的数据并返回符合用户查询条件的结果数据。数据查询语句是SQL语言的核心。
FROM 表名 [WHERE 查询条件] [GROUP BY 分组字段 [HAVING 分组条件]] [ORDER BY 字段名 [ASC/DESC]] [ ]表示可选项。 在书写SELECT语句时，字母大小写意义完全相同。 语句可以写在若干行上（如上述语法格式中的描述），也可以不换行。

10 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

11 4.2.1 简单查询 （1）字段表 3) 如果字段名或表名中含有空格，则该字段名或表名必须用方括号括起。
4) 在字段表中，可以使用以下方式将原字段名以新的字段名显示 字段名 AS 新字段名 【例4-3】 显示ClassInfo表中班级代号，并将ClassCode字段改为Class Number。 SELECT ClassCode AS [Class Number] FROM ClassInfo 注意：字段别名[Class Number]含有空格，所以用方括号括起

12 4.2.1 简单查询 （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

13 4.2.1 简单查询 （1）字段表 DISTINCT：若多条记录的字段值具有相同数据，只显示一条记录。
【例4-6】查询Course表中开课的学院代码AcademyCode（与所开课程无关）。 SELECT DISTINCT AcademyCode FROM Course

14 4.2.1 简单查询 （1）字段表 6) 为了增强查询功能，在SELECT语句中还可以使用内部聚合函数，它们被用来进行一些简单的统计或计算。常用统计函数如下表所示。 函数名 函数功能 AVG 计算某一字段的平均值（此字段的值必须是数值型） COUNT 统计某一字段的个数 MAX 查找某一字段的最大值 MIN 查找某一字段的最小值 SUM 计算某一字段的总和（此字段的值必须是数值型）

15 4.2.1 简单查询 （1）字段表 【例4-7】计算学生的总分、平均分、最高分、最低分及学生选课数。
SELECT Sum (Grade) as 总分, Avg (Grade) as 平均分, Max (Grade) as 最高分, Min (Grade) as 最低分, Count (StudentCode) as 课程人数 FROM Grade

16 7）使用SELECT语句复制新表。在字段名后加上INTO 表名可将查询结果复制到一张新表中。
4.2.1 简单查询 （1）字段表 7）使用SELECT语句复制新表。在字段名后加上INTO 表名可将查询结果复制到一张新表中。 【例4-8】将Student表中所有记录的学生姓名、出生日期两个字段复制一张新表NewTable。 SELECT StudentName, BirthDay INTO NewTable FROM Student 运行后，产生一张表名为NewTable的新表。

17 4.2.1 简单查询 (2) FROM子句 FROM 表名1[ ，表名2，……，表名n ]
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

18 4.2.2 条件查询 WHERE子句 设置查询的条件，它是一个可选的子句。在使用时，WHERE子句必须在FROM子句的后面。
查询条件是一个逻辑表达式。常用的运算符有： 查询条件 谓词 比较 >、>=、<、<=、=(等于)、<>(不等于) 确定范围 BETWEEN …AND、NOT BETWEEN… AND 确定集合 IN、NOT IN 字符匹配 LIKE、NOT LIKE 空值 IS NULL、IS NOT NULL 否定 NOT 逻辑运算 AND、OR

19 4.2.2 条件查询 比较和逻辑运算 比较运算用于比较两个表达式的值；逻辑运算用来连接多个查询条件。
【例4-10】在Student表中查询所有女同学的姓名。 SELECT StudentCode, StudentName, Sex, LiveInDorm FROM Student WHERE Sex='女' 【例4-11】查询未住校的女学生的情况。 SELECT StudentCode, StudentName, Sex, LiveInDorm FROM Student WHERE Sex='女' and LiveInDorm=0

20 4.2.2 条件查询 2)确定范围BETWEEN … AND BETWEEN 值1 AND 值2:查询值在值1至值2之间的记录
NOT BETWEEN 值1 AND 值2:查询不在指定范围中的记录 【例4-13】查询Student表中不在1986年到1995年中出生的学生学号和姓名。 SELECT StudentCode, StudentName, Birthday FROM Student WHERE Birthday NOT BETWEEN ' ' AND ' ' 【例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子句也可改为： AND Grade>= 70 AND Grade<=90

21 4.2.2 条件查询 3)确定集合IN运算 使用IN运算可查询某些字段值是否包含在所列出的指定值的记录.
【例4-14】查询Student表中班号（ClassCode）为“ ”、“ ” 的学生。 SELECT StudentCode, StudentName, Sex, ClassCode FROM Student WHERE Classcode IN (' ',' ') 此例的WHERE子句也可以改为： WHERE ClassCode=' ' OR ClassCode=' '

22 4.2.2 条件查询 4)字符匹配LIKE 指定字段的值是否包含在给定的字符串中，其结果是满足字符串匹配的数据记录。
通配符“_”表示任意单个字符；“%”表示包含零个或更多字符的任意字符串。 【例4-15】查询Course表中课程名称（CourseName）中包含“化学”两个字的课程代号（CourseCode）及课程名称。 SELECT CourseCode,CourseName FROM Course WHERE CourseName LIKE '%化学%'

23 ? 4.2.2 条件查询 5）空值比较运算 例4.15：对st_info表，查询所有Telephone为空值的学生的信息。 SELECT *
FROM st_info WHERE Telephone IS NULL 空值表示值未知。 空值不同于空白或零值。 没有两个相等的空值。 可以写成： Telephone = NULL ?

24 4.2.3 查询结果处理 排序输出（ORDER BY） 语法格式：
ORDER BY order_by_expression1[ASC|DESC] [,order_by_expression2[ASC|DESC]] [,…]] 例4.16：对st_info表，按性别顺序列出学生的信息，性别相 同的再按年龄由小到大排序。 SELECT * FROM st_info ORDER BY st_sex, BirthDate DESC

25 4.2.3 查询结果处理 【例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

26 4.2.3 查询结果处理 分组统计（GROUP BY）与筛选（HAVING）
语法格式： 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

27 4.2.3 查询结果处理 【例4-20】统计课程代号（CourseCode）大于“110006”的各门课程的选课人数。
SELECT CourseCode, Count (StudentCode) AS 选课人数 FROM Grade GROUP BY CourseCode HAVING CourseCode > '110006'

28 4.2.3 查询结果处理 重定向输出（INTO） 语法格式： INTO new_table
例4.21：对s_c_info表，查询选修“大学计算机基础”（课程 号为“ ”）课程的所有学生信息，并将结果存入 newstudent表中。 SELECT st_id 学号, c_no 大学计算机基础, score 成绩 INTO newstudent FROM s_c_info WHERE c_no= ' '

29 4.2.3 查询结果处理 输出合并（UNION） 语法格式： [UNION [ALL] <SELECT语句>]
例4.22：对c_info表，列出课程编号为“ ”或 “ ”的课程名称和学分。 SELECT c_name,c_credit FROM c_info WHERE c_no=' ' UNION FROM c_info WHERE c_no=' '

30 4.3 嵌套查询 4.3.1 单值嵌套查询 多值嵌套查询

31 4.3 嵌套查询 在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询称为嵌套查询，又称子查询。 嵌套查询的类型 单值嵌套查询 多值嵌套查询

32 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='大学计算机基础‘ ) 内查询的结果作为外查询的条件

33 4.3.1 单值嵌套查询 【例4-24】查询“张三”同班同学的学号、姓名。
SELECT Student.StudentCode, Student.StudentName, Student.ClassCode FROM Student WHERE Student.ClassCode=( SELECT Student.ClassCode WHERE Student.StudentName='张三' )

34 4.3.2 多值嵌套查询 子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。
若某个子查询的返回值不止一个，则必须在WHERE子句中指明如何使用这些返回值。通常使用条件运算符： ANY ALL IN >ALL 表示大于每一个值；即大于最大值。 例如，>ALL (1, 2, 3) 表示大于 3。 >ANY 表示至少大于一个值，即大于最小值。 例如， >ANY (1, 2, 3) 表示大于 1。

35 4.3.2 多值嵌套查询 1) IN子查询 IN子查询用来判断一个给定值是否在子查询的结果集中。
【例4-25】查询选修了课程代码为“110001”的学生的学号、姓名和班级代号。 SELECT StudentCode, StudentName, ClassCode FROM Student WHERE StudentCode IN ( SELECT StudentCode FROM Grade WHERE CourseCode='110001' )

36 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' )

37 4.3.2 多值嵌套查询 使用ANY运算符 例4.27：对Student数据库，查询选修“ ”即“大学计算机基础”课程的学生的成绩比选修“ ”即“体育”课程的学生的最低成绩高的学生的学号和成绩 。 SELECT st_id,score FROM s_c_info WHERE c_no=' ' and score >ANY ( SELECT score FROM s_c_info WHERE c_no=' ' )

38 4.3.2 多值嵌套查询 使用ALL运算符 ALL运算符指定子查询结果集中每个值都满足比较条件时返回TURE，否则返回FALSE。
例4.28：对Student数据库，列出选修“ ”即“体育”的学生的成绩比选修“ ”即“大学计算机基础”的学生的最高成绩还要高的学生的学号和成绩 。 SELECT st_id, score FROM s_c_info WHERE c_no=' ' and score > ALL( SELECT score FROM s_c_info WHERE c_no=' ' )

39 4.4 连接查询 4.4.1 自连接 内连接 外连接 交叉连接 多表连接嵌套

40 4.4 连接查询 连接查询的概念 同时涉及多个表的查询称为连接查询 。 可根据各个表之间的逻辑关系从两个或多个表中检索数据。 连接查询的类型
自连接(Self join) 内连接（Inner join） 外连接（Outer join） 交叉联接（Cross join）

41 4.4 连接查询 连接查询的建立 在WHERE子句中建立 在连接FROM子句中建立 连接字段 连接谓词中的列名称为连接字段。
连接条件中的各连接字段类型必须是可比的， 但不必是相同的。 连接的结果 一个表中的行和与另外一个表中的行匹配连接。表中的数据决定了如何对这些行进行组合。从每一个表中选取一行，根据这些列的值是否相同，组合方式分为一对一、多对一和多对多的关系。

42 4.4 连接查询 ①在WHERE子句中设置查询条件。 【例4-29】查询未住校学生的选课及成绩情况
SELECT StudentName, CourseCode, Grade, LiveinDorm FROM Grade, Student WHERE LiveInDorm=0 AND Grade.StudentCode=Student.StudentCode

43 4.4 连接查询 ②在FROM子句中使用联接关键字将表与表联接在一起。 T-SQL查询嵌套在语言中使用时，多采用该方法 。语法格式：
ON 表1.字段名1 <比较运算符> 表2.字段名2 其中： 表1、表2是被联接的表名； 字段名是被联接的字段。必须有相同的数据类型并包含同类数据，但不需要有相同的名称。 比较运算符：=、<、>、<=、>=、<> 联接关键字用于确定联接的方式，常用的有INNER JOIN （内联接）、LEFT OUTER JOIN（左外联接）、RIGHT OUTER JOIN（右外联接）、CROSS JOIN（交叉联接）；

44 4.4.1 自连接 自连接(Self join)是指一个表自己与自己建立连接，也称为自身连接。
例4.30：查询选修“大学计算机基础”（ ）课程的成绩高于学号为“ ”学生的成绩的所有学生信息，并按成绩从高到低排列。（在WHERE子句中建立连接） SELECT x.* /*将成绩表s_c_info 分别取别名为x和y*/ FROM s_c_info x , s_c_info y WHERE x.C_No=' ' And x.Score>y.Score And y.St_ID=' ' And y.C_No=' ' ORDER BY x.score DESC 在FROM子句中指定要连接的表，在WHERE子句中给出连接条件。

45 4.4.2 内连接 内连接（Inner join）使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。
【例4-31】使用内联接查询未住校学生的选课及成绩情况。 SELECT StudentName, CourseCode, Grade, LiveinDorm FROM Student INNER JOIN Grade ON Student.StudentCode = Grade.StudentCode WHERE LiveInDorm=0 本句的查询结果与【例4-29】结果完全相同。

46 4.4.3 外连接 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子句条件的全部记录显示在查询结果中。

47 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中出现的值。

48 4.4.3 外连接 【例4-34】查询化工学院开设的课程的选修学生和考试成绩。
SELECT Course.CourseCode, Course.CourseName, StudentCode, AcademyName, Grade FROM Grade RIGHT JOIN Course ON Grade.CourseCode = Course.CourseCode JOIN Academy ON Course.AcademyCode=Academy.AcademyCode WHERE AcademyName='化工学院'

49 4.4.4 交叉连接 CROSS JOIN（交叉联接）：没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积。笛卡尔积结果集的大小为第一个表的行数乘以第二个表的行数。交叉连接使用关键字CROSS JOIN进行连接。 【例4-35】使用交叉联接列出所有学生需选的所有课程情况。 SELECT StudentName, CourseCode FROM Student CROSS JOIN Course

50 4.4.5 多表连接嵌套 多表联接嵌套：在多表查询时，所涉及的数据表在3张以上，形成联接嵌套。嵌套的格式如下： FROM 表1
JOIN 表2 ON 表1.字段1 <比较运算符> 表2.字段1 JOIN 表3 ON 表2.字段2 <比较运算符> 表3.字段2 … [JOIN 表n ON 表n-1.字段n <比较运算符> 表n.字段n]

51 4.4.5 多表连接嵌套 【例4-36】查询班号是“051011”的学生姓名、所选课程名称和成绩。 ①使用WHERE子句设置查询条件：
SELECT StudentName, CourseName, Grade FROM Student, Course,Grade WHERE Course.CourseCode = Grade.CourseCode AND Student.StudentCode = Grade.StudentCode AND ClassCode=' ' ②使用联接关键字的语句： SELECT StudentName, CourseName, Grade FROM Student JOIN Grade ON Student.StudentCode = Grade.StudentCode JOIN Course ON Grade.CourseCode = Course.CourseCode WHERE ClassCode=' '

52 本章小结 （1）SELECT 查询语句 基本语法格式，子句及执行顺序。 子句的使用。 （2）简单查询 查询列（聚合函数）；
选择行（比较表达式、逻辑表达式、限定范围、限制检索、模糊查询） 分组与汇总 （3）连接查询 FROM…WHERE 子句连接查询 FROM子句的ANSI模式连接查询（内、外、交叉连接） （4）子查询 单列单值子查询、单列多值子查询、多列多值子查询 （5）联合查询 并运算联合查询、差运算联合查询、交运算联合查询 ANSI模式(美国国家标准学会)