1 / 89

项目 6 数 据 查 询

项目 6 数 据 查 询. 学习目的及要求 :  掌握数据查询的各种语句；  会运用企业管理器和基本的 SELECT 语句查询表中的数据；  掌握数据维护的基本方法。. 任务 2 查询语句 SELECT. 2.1 单表查询 2.2 连接查询 2.3 嵌套查询 2.4 集合查询 2.5 使用企业管理器进行数据查询与维护.

amity-meadows
Download Presentation

项目 6 数 据 查 询

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 项目6 数 据 查 询 学习目的及要求:  掌握数据查询的各种语句；  会运用企业管理器和基本的SELECT语句查询表中的数据；  掌握数据维护的基本方法。

  2. 任务2 查询语句SELECT 2.1 单表查询 2.2 连接查询 2.3 嵌套查询 2.4 集合查询 2.5 使用企业管理器进行数据查询与维护

  3. 数据库的一个重要操作就是数据查询，数据库中往往存在海量的数据，那么，怎样才能从一张又一张的二维表中，查出对我们有用的数据呢？SQL语言的主要功能之一就是数据查询，我们可以用SQL语句来实现查询。数据库的一个重要操作就是数据查询，数据库中往往存在海量的数据，那么，怎样才能从一张又一张的二维表中，查出对我们有用的数据呢？SQL语言的主要功能之一就是数据查询，我们可以用SQL语句来实现查询。 和其他数据库操作一样，我们既可以用企业管理器的一些操作来完成查询，也可以用SQL语句来实现。 首先，我们看一下SQL 语句的查询过程。

  4. 2.1 单表查询 SELECT语句的语法格式 SELECT <目标列> [INTO 〈新表名〉] [FROM 〈数据源〉] [WHERE 〈元组条件表达式〉] [GROUP BY 〈列名的列表〉] [HAVING 〈条件表达式〉] [ORDER BY 〈列名1〉 [ASC|DESC]， 〈列名2〉 [ASC|DESC]， ．．． 〈列名n〉 [ASC|DESC]]

  5. 2.1 单表查询 单表查询指的是在一个源表中查找所需的数据。因此，单表查询时，FROM子句中的<数据源> 只有一个。 下面以学生课程库为例，介绍各种查询的描述格式。学生课程库包括3个基本表，其结构为： Students(Sno，Sname，Ssex，Sage，Sdept)； Courses(Cno，Cname，Cpno，Credit)； Study(Sno，Cno，Grade)。

  6. 2.1 单表查询 1.使用SELECT子句选取字段 简单地可以说明为，按照指定的表中查询出指定的字段。使用的SELECT格式为： SELECT 〈目标列〉 FROM 〈数据源〉

  7. 2.1 单表查询 1.使用SELECT子句选取字段 （1）选择表中所有列 查询全部列，即将表中的所有列都选出来，一般有两种方法：一是在<列名表>中指定表中所有列的列名，此时目标列所列出的顺序可以和表中的顺序不同；二是将目标列用*来代替，或用<表名>.*代表指定表的所有列，此时列的显示顺序与表中的顺序相同。 【实例6.1】查询全体学生的学号、姓名、性别、年龄、所在系。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Students 等价于：SELECT * FROM Students

  8. 2.1 单表查询 1.使用SELECT子句选取字段 （2）查询指定表中的部分列 在很多情况下，用户只对表中的部分列值感兴趣，这是可以通过在SELECT子句中<列名表>来指定要查询的目标列，各个列名之间用逗号分隔，各个列的先后顺序可以与表的顺序不一致，用户可以根据需要改变列的显示顺序。 【实例6.2】查询全体学生的Sno，Sname，Sdept。 SELECT Sno，Sname，Sdept FROM Students

  9. 2.1 单表查询 1.使用SELECT子句选取字段 （3）为结果集内的列指定别名 如果某个列在SELECT子句中未经修改，列名就是默认的列标题。为增加查询结果的可读性，可以不使用在表中的列名，指定一个列标题来换掉默认的标题。 【实例6.3】查询全体学生的Sno，Sname，Sdept，将结果集中将字段名显示为中文学号，姓名，所在系。 SELECT Sno 学号，Sname AS 姓名，所在系=Sdept FROM Students

  10. 2.1 单表查询 1.使用SELECT子句选取字段 (4) 结果集为表达式 有些时候，结果集中的某些列不是表中现成的列，而是一列或多列运算后产生的结果。如果在SELECT子句中有表达式或者对某列进行了运算，那么有表达式生成的列标题就是空白。此时，如果想要为空白列提供一个列标题，可以通过对某一列指定列标题来实现。 【实例6.4】查询study表中的所有信息，并将结果集中的Grade（成绩）统一增加10分。 SELECT Sno，Cno，Grade=Grade+10 FROM Study

  11. 2.1 单表查询 1.使用SELECT子句选取字段 (5) 为结果集消除重复列 当查询的结果集中仅包含表中的部分列时，有可能出现重复记录。如果要消除结果集中的重复记录，可以在目标列前面加上DISTINCT关键字。 【实例6.5】查询students基本信息表中的Sdept，查询结果中消除重复行。 SELECT DISTINCT 所在系=sdept FROM Students

  12. 2.1 单表查询 1.使用SELECT子句选取字段 (6) 限制返回行数 用SELECT子句选取输出列时，如果在目标列前面使用TOP n子句，则在查询结果中输出前面n条记录；如果在目标列前面使用TOP n PERCENT子句，则在查询结果中输出前面占记录总数百分比为n%的记录。 【实例6.6】查询students表中的所有列，在结果集中输出前3条记录。 SELECT TOP 3 * FROM students 【实例6.7】查询students表中的所有列，在结果集中输出前10%记录。 SELECT TOP 10 PERCENT * FROM students

  13. 2.1 单表查询 2.使用INTO子句创建新表 通过在SELECT语句中使用INTO子句，可以自动创建一个新表并将查询的结果集中的记录添加到该表中。新表的列由SELECT子句中的目标列来决定。若新表的名称以“#”开头，则生成的新表为临时表。不带“#”为永久表。 【实例6.8】将students表中Sno,Sname,Ssex的查询结果作为新建的临时表Student01。 SELECT Sno,Sname,Ssex INTO student01 FROM students

  14. 2.1 单表查询 3.使用WHERE子句设置查询条件 大多数查询都不希望得到表中所有记录，而是一些满足条件的记录，这时就要用到WHERE子句。 WHERE子句中常用的查询条件包括比较、确定范围、确定集合、字符匹配、空值匹配和多重条件等，下面分别介绍它们的具体使用。

  15. 3.使用WHERE子句设置查询条件 WHERE子句确定了查询的条件，表4-1给出了组成查询条件表达式的运算符。

  16. 2.1 单表查询 3.使用WHERE子句设置查询条件 （1）比较运算符 比较运算符用于比较大小，包括：>、<、=、>=、<=、<>或!=、!>、!<，其中<>或!=表示不等于、!>表示不大于、!<表示不小于。 【实例6.9】查询Study表中成绩Grade不及格的记录。 SELECT * FROM Study WHERE Grade<60 【实例6.10】查询students表中所有女生的Sno,Sname。 SELECT Sno,Sname FROM students WHERE Ssex=’女’

  17. 2.1 单表查询 3.使用WHERE子句设置查询条件 (2) 范围运算符 在WHERE子句的<元组条件表达式>中使用谓词BETWEEN… AND或NOT BETWEEN … AND。 BETWEEN… AND——测试表达式的值包含在指定范围内； NOT BETWEEN … AND——测试表达式的值不包含在指定范围内。 【实例6.11】查询年龄在20～23岁(包括20岁和23岁)间的学生的姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Students WHERE Sage BETWEEN 20 AND 23 【实例6.12】查询年龄不在20～23岁之间的学生姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Students WHERE Sage NOT BETWEEN 20 AND 23

  18. 2.1 单表查询 3.使用WHERE子句设置查询条件 (3) 集合运算符 在WHERE子句的<元组条件表达式>中使用用谓词IN(值表)或NOT IN (值表)，(值表)是用逗号分隔的一组取值。 IN——测试表达式的值等于列表中的某一个值； NOT IN——测试表达式的值不等于列表中的任何一个值。 【实例6.13】查询管理系、数学系和计算机系学生的姓名Sname和性别Ssex。 SELECT Sname,Ssex FROM Students WHERE Sdept IN (‘管理’,’数学’,’计算机’) 【实例6.14】查询既不是管理系、数学系，也不是计算机系的学生的姓名Sname和性别Ssex。 SELECT Sname,Ssex FROM Students WHERE Sdept NOT IN (‘管理’,’数学’,’计算机’)

  19. 2.1 单表查询 3.使用WHERE子句设置查询条件 (4)字符匹配 字符匹配运算符用来判断字符型数据的值是否与指定的字符通配格式相符。在WHERE子句的<元组条件表达式>中使用谓词[NOT] LIKE ‘<匹配串>’。其中<匹配串>可以是一个有数字或字母组成的字符串，也可以是含有通配符的字符串。

  20. 条件表达式中的通配符有以下4种： 1.%：匹配包含0个或多个字符的字符串。 2.—：匹配任何单个的字符，包括汉字。 3.[ ]：排列通配符，匹配任何在范围和集合中的单个字符。例如[m-p]匹配的是m、n、o、p单个字符。 4.[^]:不在范围之内的单个字符，匹配任何不在范围之内或集合之内的单个字符，例如[^mnop]或[^m-p]匹配的是除了m、n、o、p之外的任何字符。 例如： ‘XU%’, ’[ck]ars[eo]’, ‘m[^c]%’

  21. 2.1 单表查询 3.使用WHERE子句设置查询条件 (4)字符匹配 【实例6.15】查询Students表中所有姓陈的学生的信息。 SELECT * FROM Students WHERE Sname=’刘%’ 【实例6.16】查询Students表中所有姓王且名字为两个汉字的学生的信息。 SELECT * FROM Students WHERE Sname=’王__’

  22. 2.1 单表查询 3.使用WHERE子句设置查询条件 (5) 空值运算符 空值运算符用来判断列值是否为NULL（空值），包括： ① IS NULL 列值为空； ② IS NOT NULL 列值不为空。 【实例6.17】查询Stusy表中成绩为空的记录。 SELECT * FROM Study WHERE Grade IS NULL

  23. 2.1 单表查询 3.使用WHERE子句设置查询条件 (6) 逻辑运算符 一个查询条件有时是多个简单条件的组合，逻辑运算符能够连接多个简单条件，构成一个复杂的查询条件。包括： ① AND：运算符两端同时成立时，表达式结果才成立。 ② OR：运算符两端有一个成立时，表达式结果即成立。 ③ NOT：将运算符右侧表达式的结果取反。

  24. 2.1 单表查询 3.使用WHERE子句设置查询条件 (6) 逻辑运算符 【实例6.17】查询Students表中计算机系所有男生的信息。 SELECT * FROM Students WHERE Sdept=’计算机’AND Ssex=’男’ 【实例6.18】查询数学系年龄在20岁以下的学生姓名。 SELECT Sname FROM Students WHERE Sdept =’数学’AND Sage<20

  25. 2.1 单表查询 4.使用ORDER BY子句对结果及排序 查询结果集中记录的顺序是按它们在表中的顺序进行排列的，使用ORDER BY子句可以按一个或多个属性列排序，升序ASC，降序DESC，缺省为升序。当排序列含空值时，ASC排序列为空值的元组最后显示，DESC排序列为空值的元组最先显示。 如果在ORDER BY子句中指定多个列，检索结果首先按第1列进行排序，第1列值相同值的那些数据行，再按照第2列排序，如此等等。ORDER BY要写在WHERE子句的后面。

  26. 2.1 单表查询 4.使用ORDER BY子句对结果及排序 【实例6.19】查询选修了课程代号为C01号课程的学生的学号及成绩，查询结果按分数降序排列。 SELECT Sno,Grade FROM Study WHERE Cno= ‘C01’ ORDER BY Grade DESC 【实例6.20】查询全体学生情况，结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Students ORDER BY Sdept,Sage DESC

  27. 2.1 单表查询 5.使用集合函数统计数据 在实际应用中，往往需要对表中的原始数据做一些数学处理。统计函数就是满足这些需求的最好工具。SELECT语句中的统计功能是对查询结果集进行求和、求平均值、求最大最小值等操作。统计的方法是通过集合函数和GROUP BY子句、COMPUTE子句进行组合来实现的。

  28. 集合函数是在查询结果记录的列集上进行各种统计运算，运算的结果形成一条汇总记录。集合函数是在查询结果记录的列集上进行各种统计运算，运算的结果形成一条汇总记录。

  29. 2.1 单表查询 5.使用集合函数统计数据 【实例6.21】查询学生总人数。 SELECT COUNT(*) FROM Students 【实例6.22】查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM Study 注：用DISTINCT以避免重复计算学生人数。

  30. 2.1 单表查询 5.使用集合函数统计数据 【实例6.23】计算C01号课程的学生平均成绩。 SELECT AVG(Grade) FROM Study WHERE Cno= ‘C01’ 【实例6.24】查询选修C01号课程的学生最高分数。 SELECT MAX(Grade) FROM Study WHERE Cno= ‘C01’

  31. 2.1 单表查询 6.使用GROUP BY子句 前面进行的统计都是针对整个查询结果集的，通常也会要求按照一定的条件对数据进行分组统计。GROUP BY子句就能够实现这种统计，它按照指定的列，对查询结果进行分组统计；"HAVING 条件表达式"选项是对生成的组进行筛选，只有满足HAVING短语指定条件的组才输出，HAVING短语与WHERE子句的区别是WHERE子句作用于基表或视图，从中选择满足条件的元组；HAVING短语作用于组，从中选择满足条件的组。 注意：SELECT子句中的选择列表中出现的列，或者包含在集合函数中，或者包含在GROUP BY子句中，否则，SQL Server将返回错误信息。

  32. 2.1 单表查询 6.使用GROUP BY子句 【实例6.25】统计各门课程的选课人数，输出课程代号Cno和选课人数。 SELECT 课程代号=Cno,选课人数=COUNT(Sno) FROM Study GROUP BY Cno 【实例6.26】求各系及相应的学生人数。 SELECT系名称=Sdept,学生人数=COUNT(Sno) FROM Students GROUP BY Sdept 【实例6.26】查询选修了3门以上课程的学生学号。 SELECT 学号=Sno FROM Study GROUP BY Sno HAVING COUNT(*) >3

  33. 2.1 单表查询 7.使用COMPUTE子句 COMPUTE子句的功能与GROUP BY子句类似，对记录进行分组统计。COMPUTE子句与GROUP BY子句的区别是，除显示统计结果外，还显示统计的各组数据的详细信息。语法格式如下： COMPUTE 集合函数 [BY 列名] 在使用COMPUTE子句时，必须遵守以下原则： ① 在集合函数中，不能使用DISTINCT关键字； ② COMPUTE BY子句必须与ORDER BY子句同时使用； ③ COMPUTE BY子句中BY后的列名必须与ORDER BY子句中相同，或为其子集，且二者从左到右的排列顺序必须一致； ④COMPUTE子句中不使用BY选项时，统计出来的为合计值。

  34. 2.1 单表查询 7.使用COMPUTE子句 【实例6.27】查询Students表中的所有字段列，在结果集中显示各系的学生人数和该系的所有学生记录。 SELECT * FROM Students ORDER BY Sdept COMPUTE COUNT(Sno) BY Sdept

  35. 2.2 连接查询 一个数据库的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面查询所举例子都是针对一个表进行的。在实际的数据库操作中，往往需要同时从两个或两个以上的表中查询相关数据，连接就是满足这些需求的技术。如果一个查询同时涉及两个以上的表，则称为连接查询。连接查询是关系数据库中最主要的查询。 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。连接分为内连接、外连接、交叉连接和自连接。

  36. 2.2 连接查询 1.交叉连接 交叉连接有以下两种语法格式： SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 或者 SELECT 列名列表 FROM 表名, 表名2 交叉连接的结果是两个表的笛卡儿积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。 【实例6.27】查询学生的情况以及选修课程的情况。 SELECT Students.*, Study.* FROM Students, Study

  37. 2.2 连接查询 2.内连接 内连接就是只包含满足连接条件的数据行，是将交叉连接结果集按照连接条件进行过滤的结果，也称自然连接。连接条件通常采用“主键=外键”的形式，即按一个表的主键值与另一个表的外键值相同的原则进行连接。内连接有以下两种语法格式： SELECT 列名列表 FROM 表名1 [INNER] JOIN 表名2 ON 表名1.列名=表名2.列名 或 SELECT 列名列表 FROM 表名1, 表名2 WHERE 表名1.列名=表名2.列名

  38. 2.2 连接查询 2.内连接 【实例6.28】查询每个学生的基本信息以及他/她选课的情况。 SELECT Students.*,Study.* FROM Students,Study WHERE Students.Sno = Study.Sno 【实例6.29】查询每个学生的学号、姓名、选修的课程名、成绩。 SELECT Students.Sno,Sname, Cname,Grade FROM Students,Courses,Study WHERE Students.Sno = Study.Sno AND Courses.Cno= Study.Cno

  39. 2.2 连接查询 2.内连接 【实例6.30】查询选修了C2且成绩大于90分的学生的学号、姓名、成绩。 SELECT Students.Sno,Sname, Grade FROM Students, Study WHERE Students.Sno = Study.Sno AND Cno='C2 ' AND Grade>90 这里：用AND将一个连接条件和两个行选择条件组合成为查询条件 【实例6.31】求计算机系选修课程大于等于2门课的学生的学号、姓名、平均成绩, 并按平均成绩从高到低排序。 SELECT Students.Sno, Sname, 平均成绩=AVG(Grade) FROM Students, Study WHERE Students.Sno= Study.Sno AND Sdept='计算机' GROUP BY Students.Sno HAVING COUNT(*) >= 2 ORDER BY AVG(Grade) DESC

  40. 2.2 连接查询 3.外连接 外连接根据连接时保留表中记录的侧重不同分为“左外连接”、“右外连接”和“全外连接”。 （1）左外连接 将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回左表中不符合条件的记录，并在右表的相应列中填上NULL，由于BIT类型不允许为NULL，就以0值填充。左外连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A LEFT [OUTER] JOIN 表名2 AS B ON A.列名=B.列名

  41. 2.2 连接查询 3.外连接 外连接根据连接时保留表中记录的侧重不同分为“左外连接”、“右外连接”和“全外连接”。 （2）右外连接 和左外连接类似，右外连接是将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回右表中不符合条件的记录，并在左表的相应列中填上NULL，由于BIT类型不允许为NULL，就以0值填充。右外连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A RIGHT [OUTER] JOIN 表名2 AS B ON A.列名=B.列名

  42. 2.2 连接查询 3.外连接 外连接根据连接时保留表中记录的侧重不同分为“左外连接”、“右外连接”和“全外连接”。 （2）右外连接 【实例6.31】 查询所有学生的选修情况，要求包括选修了课程的学生和没有修课的学生，显示他们的学号、姓名、课程号、成绩。 SELECT Students.Sno, Sname,Cno,Grade FROM Students,Study WHERE Students.Sno*= Study .Sno

  43. 2.2 连接查询 3.外连接 外连接根据连接时保留表中记录的侧重不同分为“左外连接”、“右外连接”和“全外连接”。 (3) 全外连接 全外连接是将左表中的所有记录分别与右表中的每条记录进行组合，结果集中除返回内部连接的记录以外，还在查询结果中返回两个表中不符合条件的记录，并在左表或右表的相应列中填上NULL，BIT类型以0值填充。全外连接的语法格式如下： SELECT 列名列表 FROM 表名1 AS A FULL [OUTER] JOIN 表名2 AS B ON A.列名=B.列名

  44. 2.2 连接查询 4.自连接 自连接就是一个表的两个副本之间的内连接。表名在FROM子句中出现两次，必须对表指定不同的别名，在SELECT子句中引用的列名也要使用表的别名进行限定。语法格式如下： SELECT 列名列表 FROM 表名 AS A,表名 AS B WHERE A.列名=B.列名

  45. 2.2 连接查询 4.自连接 【实例6.32】查询与李辉在同一个系学习的所有学生的学号和姓名。 SELECT S2.Sno, S2.Sname FROM Students S1,Students S2 WHERE S1.Sdept = S2.Sdept AND S1.Sname = '李辉' 【实例6.33】查询Students中姓名相同的学生信息。 SELECT * FROM Students S1,Students S2 WHERE S1.Sname = S2.Sname AND S1.Sno<>S2.Sno

  46. 4.3 SELECT语句中的多表连接 在实际应用中，数据查询往往会涉及到多个表，这就需要将多个表连接起来进行查询。这种连接分为交叉连接、内连接、外连接和自连接4种。交叉连接相当于作笛卡儿乘积，内连接又分为相等连接、自然连接。外连接分为左外连接、右外连接和全外连接。

  47. 例4-24：生成多表连接所要使用的“销售名单”表和“销售业绩”表，并添加数据。例4-24：生成多表连接所要使用的“销售名单”表和“销售业绩”表，并添加数据。 Select 工号,姓名 into 销售名单 From 销售人员 Create table销售业绩 (工号 int primary key , 销售额 money) Alter table 销售名单 add primary key(工号)

  48. 图4-7 连接要使用的表和数据

  49. 4.3.1 交叉连接 SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 或者 SELECT 列名列表 FROM 表名, 表名2 交叉连接的结果是两个表的笛卡儿积，在实际应用中一般是没有意义的，但在数据库的数学模式上有重要的作用。连接后该结果集的行数等于两个表的行数积，列属等于两个表的列数和。它是其他连接运算的基础。

  50. 例4-25：对新建的表“销售名单”和“销售业绩”进行交叉连接，观察连接后的结果。例4-25：对新建的表“销售名单”和“销售业绩”进行交叉连接，观察连接后的结果。 Select 销售名单.工号 as 左工号,姓名,销售业绩.工号 as 右工号, 销售额 From 销售名单CROSS JOIN销售业绩

More Related