数据查询

null第五章 数据查询第五章 数据查询 数据查询功能是SQL语言的核心功能，是数据库应用中使用最多的操作,是ＳＱＬ语言的灵魂。SELECT语句的作用是让数据库服务器根据客户端的请求查找出用户所需要的信息资料---数据集合，并按用户规定的格式整理成都市“结果集”返回给客户端. null查询语句有灵活的使用方式和丰富多彩的功能。正确、高效率地数据查询，有几条最基本的原则。 null第一，用户必须清楚查询所需的数据源。 第二，对于基本表的结构要十分清楚，这是实现高效率查询的要素。 第三，对于查询语句的语法结构要很熟练。越是复杂的查询，语句越灵活多样。 第四，对系统执行查询的基本过程有正确的了解，并对结果的状态有基本的估计，以便判断查询结果的可靠性。5.1查询语句的基本结构5.1查询语句的基本结构5.1.1查询语句的结构和语法 查询语句的语句格式和语法规格可描述如下： SELECT 查询结果列表 FROM 数据源 [ WHERE 条件表达式 ] [ GROUP BY 分组依据的列名 [ HAVING 组提取条件 ] ] [ ORDER BY 排序依据的列名 ]nullSelect 语句中各子句的顺序： Select->查询结果列->[from]->[where]->[group by->[having]]->[order by] From 用于指定数据源： 单表查询简单格式：from 表名 多表查询时的格式：from 表名列表及其连接方式null说明： SELECT <目标列名序列> --结果需要哪些列 FROM <数据源> -- 数据来自于哪些表 [WHERE <检索条件表达式>]--根据什么条件 [GROUP BY <分组依据列>] [HAVING <组提取条件>] [ORDER BY <排序依据列>] 使用select 语句进行无数据源检索使用select 语句进行无数据源检索无数据源检索就是查询输出不在数据表中的数据．一般用来输出常量或变量的值（相当于print或其他语言的输出语句). SELECT是用于显示查询结果集的语句，所以输出常量或变量值时，是在网格窗口用的形式输出．5.2 简单查询5.2 简单查询简单查询（选择表中若干列） 5.2.1 查询指定的列 在很多情况下，用户可能只对表中的一部分属性列感兴趣，这时可通过在SELECT子句的<目标列名序列>中指定要查询的列来实现。 例5-2. 查询全体学生的学号与姓名 SELECT Sno，Sname FROM Student 简单查询简单查询5.2.2要查询表中的全部列 例5-4．查询全体学生的所有字段值 SELECT Sno，Sname，Ssex, Birth, Depart,Addr FROM Student 等价于：SELECT * FROM Student 思考：执行如下语句的结果是什么？ SELECT Sname， Depart, Ssex, Birth, Sno FROM Student简单查询简单查询5.2.3 查询结果是经过计算的列值 SELECT子句中的<目标列名序列>可以是表 中存在的属性列，也可以是表达式、常量或者函数。 例5-5．查询全体学生的姓名及其实际年龄 SELECT Sname,2008-DATEPART(yy,Birth) FROM Studentnull 如果将上述语句中计算实际年龄的表达式修改一下，或许更有通用性。其SQL 语句如下。 SELECT Sname, Birth DATEDIFF(yy,Birth,getdate()) FROM Student简单查询简单查询5.2.4 结果中插入常量 例5-6查询全体学生的姓名、出生年份，并在其出生年份前加入‘出生年份是’的字符串。可以用以下语句实现。 SELECT Sname,’的实际年龄是’, 2008-DATEPART(yy,Birth) FROM Student简单查询简单查询5.2.5 改变结果中的列标题 经过计算的列、函数的列和常量列的显示结果都没有列标题，通过指定列的别名可以改变查询结果的列标题。 改变列标题的语法格式为： 列名 别名 | 表达式 [ AS ] 列标题 或 列标题=列名|表达式 null例如，将例5-6 中的结果列名列表部分加入别名并简化，写成如下语句： SELECT 学生姓名=Sname，2008- DATEPART(yy,Birth) 实际年龄 FROM Student简单查询 简单查询 5.2.6 指定查询结果集中记录的显示范围有三个选项 All:显示查询结果集的全部记录（默认值） ｜distinct 对查询结果集过滤重复行 ｜top n[percent] 显示查询结果集开头的n[%]个记录distinctdistinct本来在数据库表中不存在取值全都相同的元组，但在进行了对列的选择后，在查询结果中就有可能出现取值完全相同的行了。 例6．在修课表中查询有哪些学生修 了课程，要求列出学生的学号。 SELECT Sno FROM SC 在这个结果中有许多重复的行。 nullSQL 中的DISTINCT关键字可以去掉结果表中的重复行。 SELECT DISTINCT Sno FROM SC 则执行结果为 ：5.3条件查询 5.3条件查询 查询满足条件的元组 查询满足条件的元组是通过WHERE子句实现 的。WHERE子句常用的查询条件如表5-1所示 null数据查询功能 数据查询功能 简单查询 （1）比较大小 例7．查询计算机系全体学生的姓名。已知计算机系的编号为g001. SELECT Sname FROM Student WHERE depart=‘g001’ 结果为 ：数据查询功能 数据查询功能 例5-9 查询年龄在23岁以下的学生姓名与年龄（假定当今年是2008）。 SELECT Sname，2008-DATEPART(yy,Birth) 年龄 FROM Student WHERE 2008-DATEPART(yy,Birth)<23 null例5-10 查询各课考试成绩不及格的学号、课程号、成绩。 SELECT Sno，Cno，Grade FROM SC WHERE Grade<60null2）确定范围 BETWEEN…AND和NOT BETWEEN…AND是一个逻辑运算符，可以用来查找属性值在或不在指定范围内的元组。 BETWEEN…AND…的格式为： 列名 | 表达式 [ NOT ] BETWEEN 下限值 AND 上限值nullBETWEEN…AND一般用于对数值型数据进行比较。列名或表达式的类型要与下限值或上限值的类型相同。 “BETWEEN 下限值 AND 上限值”的含义是：如果列或表达式的值在下限值和上限值范围内，则结果为True，表明此记录符合查询条件。 “NOT BETWEEN 下限值 AND 上限值”的含义正好相反：如果列或表达式的值在下限值和上限值范围内，则结果为False，表明此记录不符合查询条件。null SELECT Sname，Depart，2007-DATEPART(yy,Birth) FROM Student WHERE 2007-DATEPART(yy,Birth) BETWEEN 20 AND 22 此句等价于： SELECT Sname，Depart，2007-DATEPART(yy,Birth) FROM Student WHERE 2007-DATEPART(yy,Birth)>=20 AND 2007-DATEPART(yy,Birth)<22 nullnull例5-12查询年龄不在20~22之间的学生姓名、系编号和实际年龄。 SELECT Sname，Depart， 2007-DATEPART(yy,Birth) FROM Student WHERE 2007-DATEPART(yy,Birth) NOT BETWEEN 20 AND 22 null（3）确定集合 IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组。使用IN的格式为： 列名 [ NOT ] IN （常量1, 常量2, … 常量n） IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录； NOT IN的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录； null例5-13 查询系编号为‘g022’（美术系）、‘g005’（数学系）的学生姓名、性别。 SELECT Sname,Ssex, Depart FROM Student WHERE Depart IN ('g022','g005') 上述语句等价于下面的语句： SELECT Sname，Ssex FROM Student WHERE Depart=‘g005’ OR Depart=‘g022’ null （4）字符匹配 LIKE用于查找指定列名与匹配串常量匹配的元组。通配符用于表示任意的字符或字符串。在LIKE运算符前边也可以使用NOT运算符，表示对结果取反。 LIKE运算符的一般形式为： 列名 [NOT ] LIKE <匹配串> nullLIKE运算符的一般形式为： 列名 [NOT ] LIKE <匹配串> 匹配串中可包含如下四种通配符： _：匹配任意一个字符； %：匹配0个或多个字符； [ ]：匹配[ ]中的任意一个字符； [^ ]：不匹配[ ]中的任意一个字符。null（4）字符匹配 例5-15 查询姓‘张’的学生的所有信息。 SELECT * FROM Student WHERE Sname LIKE '张%' 例5-16 查询姓‘牛’姓‘马’的学生的所有信息。 SELECT * FROM Student WHERE Sname LIKE '[牛马]% 'null例5-17 查询不姓牛、马、刘、李、张、王的学生的所有信息。 SELECT * FROM Student WHERE Sname LIKE ' [^牛马刘李张王]% ‘ 例5-18 查询姓名中第二个字为‘小’或‘晓’的学生的所有信息。 SELECT * FROM Student WHERE Sname LIKE ' _[小晓]% ' null 例5-19查询2006年入学的全部学生名单 (假定学号左边的4位数字是入学年份)。 方法一：使用like作字符串匹配 SELECT * FROM Student WHERE Sno LIKE ‘2006%’ 方法二：使用字符串函数 SELECT * FROM Student WHERE LEFT(Sno,4)=‘2006’数据查询功能 数据查询功能 （5）涉及空值的查询 空值（NULL）在数据库中有特殊的含义，它表示不确定的值。判断某个值是否为NULL值，不能使用普通的比较运算符（=、!=等），而只能使用专门的判断NULL值的子句来完成。 判断取值为空的语句格式为：列名IS NULL 判断取值不为空的语句格式为：列名 IS NOT NULL null例5-20．查询无考试成绩的学生的学号和相应的课程号。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL 例5-21．查询所有有考试成绩的学生的学号和课程号。 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL 数据查询功能 数据查询功能 （6）多重条件查询 在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。用AND连接的条件表示必须全部满足所有的条件的结果才为True，用OR连接的条件表示只要满足其中一个条件结果即为True。 null例5-22 查询信管系（g002）、计算机系（g001）中年龄在19~23岁之间的女学生姓名、性别和年龄。 SELECT Sname,Ssex,2008- DATEPART(yy,Birth) 年龄 FROM Student WHERE (2008- DATEPART(yy,Birth) BETWEEN 19 AND 23) AND (Depart='g001' OR Depart='g002') AND Ssex='女'null 5.4 对查询结果进行排序 排序子句的格式为： ORDER BY <列名> [ASC | DESC ] [ ，… n ] 其中<列名>为排序的依据列，可以是列名或列的别名。ASC表示对列进行升序排序，DESC表示对列进行降序排序。如果没有指定排序方式，则默认的排序方式为升序排序。 null 如果在ORDER BY子句中使用多个列进行排序，则这些列在该子句中出现的顺序决定了对结果集进行排序的方式。当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进行排序，…，依此类推。null例5-24 按学生出生日期降序排序。 SELECT * FROM Student ORDER BY Birth DESC 例5-25 查询选修了'C05'课的学生学号、成绩，结果从高分到低分排列。 SELECT Sno,Grade FROM SC WHERE Cno='C05' ORDER BY Grade DESC null使用计算函数汇总数据 计算函数也称为集合函数或聚合函数、聚集函数，其作用是对一组值进行计算并返回一个单值。SQL提供的计算函数有： COUNT( *)：统计表中元组个数； COUNT(<列名>)：统计本列列值个数； SUM(<列名>)：计算列值总和（必须是数值型列）； nullAVG(<列名>)：计算列值平均值（必须是数值型列） MAX(<列名>)：求列值最大值； MIN(<列名>)：求列值最小值。 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。 null例5-27．统计学生总人数。 SELECT COUNT（*） FROM Student 例5-28．统计选修了课程的学生的人数。 SELECT COUNT （DISTINCT Sno） FROM SC 例5-29 计算20061102号学生的考试总成绩之和。 SELECT SUM(Grade) FROM SC WHERE Sno = '20061102' 例5-30．计算‘C01’号课程学生的考试平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno='C01'null例5-30．查询选修了‘C01’号课程的学生的最高分和最低分。 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno=‘C01' 注释:计算函数不能出现在WHERE子句中。 例：查询年龄最大的学生的姓名， 如下写法是错误的： SELECT Sname FROM Student WHERE Birth= MAX(Birth)null 对查询结果进行分组计算 有时我们需要对数据进行分组，然后再对每个组进行计算，而不是对全表进行计算。这时就需要用到分组子句：GROUP BY 。GROUP BY可将计算控制在组一级。分组的目的是细化计算函数的作用对象。在一个查询语句中，可以使用任意多个列进行分组。 null 使用GROUP BY时，如果在SELECT的查询列表中包含计算函数，则是针对每个组计算出一个汇总值，从而实现对查询结果的分组统计。 分组语句跟在WHERE子句的后边，它的一般形式为： GROUP BY <分组依据列> [，… n ] [HAVING <组提取条件>]null使用GROUP BY 例5-32．统计每门课程的选课人数，列出课程号和人数。 SELECT Cno as 课程号, COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno null例5-33．查询每名学生的选课门数和平均成绩。 SELECT Sno 学号, COUNT(*) 选课门数, AVG(Grade) 平均成绩 FROM SC GROUP BY Snonull使用HAVING HAVING子句用于对分组后的结果再进行过滤，它的功能有点像WHERE子句，但它用于组而不是对单个记录。在HAVING子句中可以使用计算函数，但在WHERE子句中则不能。HAVING通常与GROUP BY子句一起使用。 null例5-34 查询选修了5门课程及以上的学号、选课门数、平均成绩。 SELECT Sno,COUNT(Cno) 选课门数,AVG(Grade)平均成绩 FROM SC GROUP BY Sno HAVING COUNT(*)>=55.5多表连接查询5.5多表连接查询内连接 在非ANSI的实现中，是在WHERE子句中指定表连接条件，在ANSI SQL-92中，连接是在JOIN子句中指定。这些连接方式分别被称为theta方式和ANSI方式。我们这里介绍的是ANSI方式的连接。 内连接的格式为： FROM 表1 [ INNER ] JOIN 表2 ON <连接条件> null 在连接条件中指明两个表按什么条件进行连接，连接条件中的比较运算符称为连接谓词。连接条件的一般格式为： [<表名1.>][<列名1>] <比较运算符> [<表名2.>][<列名2>] 注意：两个表的连接列必须是可比较的，即必须是语义相同的列，否则比较将是无意义的。 当比较运算符为等号（＝）时，称为等值连接，使用其他运算符的连接称为非等值连接。null多表连接查询 例5-36．查询每个学生及其修课的情况。只选取学号、姓名、课程号和成绩。 SELECT SC.Sno，Sname，Cno，Grade FROM Student INNER JOIN SC ON Student.Sno=SC.Sno null例5-37 查询系编号g001（计算机系）和g005（数学系）各学生的选课情况和每门课的成绩。将每个系的名单排在一起。 SELECT Sname,Cno,Grade FROM Student INNER JOIN SC ON Student.Sno=SC.Sno WHERE Depart='g001' OR Depart='g005' ORDER BY Departnull多表连接查询 可以为表名取别名，其格式为： <源表名> [ AS ] <表别名> 例如：使用别名时例5-37可写为： SELECT Sname,Cno,Grade FROM Student S JOIN SC ON S.Sno=SC.Sno WHERE Depart='g001' OR Depart='g005' ORDER BY Departnull例5-39．查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。 SELECT S.Sname,C.Cname,SC.Grade FROM Student S JOIN SC ON S.Sno=SC.Sno JOIN Course C ON C.Cno=SC.Cno WHERE S.Depart='g001' AND C.Cname='VB' 注释：这是三表连接查询null自连接 这是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。 使用自连接时必须为两个表取别名，使之在逻辑上成为两张表。 例5-40 查询与学生冯巩在同一个系学习的学生情况。 不用自连接，用简单查询也可以实现。作两次查询. nullSELECT Depart FROM Student where Sname=’冯巩’ 查到冯巩所在的系，为g004（表演系）。再通过语句 SELECT Sname,Depart FROM Student where Depart=’g004’ nullSELECT S2.Sname,S2.Depart FROM Student S1 JOIN Student S2 ON S1.Depart=S2.Depart WHERE S1.Sname='冯巩' [AND S2.Sname<>'冯巩‘]数据查询功能 数据查询功能 多表连接查询 外连接 在内连接操作中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出那些不满足连接条件的元组的信息，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。nullANSI方式的外连接的语法格式为： FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON <连接条件> theta方式的外连接的语法格式为： 左外连接：FROM 表1 , 表2 WHERE [表1.]列名(+) ＝ [表2.]列名 右外连接：FROM 表1 , 表2 WHERE [表1.]列名 ＝ [表2.]列名(+) SQL Server支持ANSI方式的外连接，Oracle支持theta方式的外连接。null例5-41查询每个学生的情况和选课情况，即使有些学生没有选修任何课程，也要列出他的信息。 SELECT S.Sno,Sname,Cno,Grade FROM Student S LEFT JOIN SC ON S.Sno=SC.Sno 如果用右外连接，同样可以实现（留给大家思考，实践，验证）。 null5.6子查询 在SQL语言中，一个SELECT－FROM－WHERE语句称为一个查询块。 如果一个SELECT语句是嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中，则称之为子查询或内层查询；而包含子查询的语句则称为主查询或外层查询。子查询要写在圆括号中。 子查询语句可以出现在允许使用表达式的任何地方，但通常情况下一般是用在外层查询的WHERE子句或HAVING子句中，与比较运算符或逻辑运算符一起构成查询条件。1、使用子查询进行比较测试1、使用子查询进行比较测试 通过比较运算符（=、<>、<、>、<=、<=），将一个表达式的值与子查询返回的值进行比较。 注意：使用子查询进行比较测试时，要求子查询语句必须是返回单值的查询语句。 null例5-44．查询选修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。 SELECT Sno , Grade FROM SC WHERE Cno ='c02' and Grade > ( SELECT AVG(Grade) from SC WHERE Cno ='c02')null2、使用子查询进行基于集合的测试 使用子查询进行基于集合的测试时，通过运算符IN或NOT IN，将一个表达式的值与子查询返回的结果集进行比较。 使用IN运算符时,如果该表达式的值与集合中的某个值相等,则此测试为True；如果该表达式与集合中所有的值均不相等，则返回False。null例5-44 查询选修了C02号课程且成绩比此课程的平均成绩高的学生学号、成绩。 SELECT Sno , Grade FROM SC WHERE Cno = 'C02' AND Grade > (Select AVG(Grade) from SC Where Cno = 'C02' ) null 例5-47．查询选修了“数据库基础”课程的学生的学号、姓名。 方法一：多表连接查询，语句如下： SELECT S.Sno, Sname FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = '数据库基础'null方法二：嵌套查询，语句如下： SELECT Sno, Sname FROM Student WHERE Sno IN ( Select Sno From SC Where Cno = (SELECT Cno FROM Course WHERE Cname = '数据库基础') ) 3、使用子查询进行存在性测试3、使用子查询进行存在性测试使用子查询进行存在性测试时，一般使用EXISTS谓词。带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。 例5-48 查询选修了课程‘C20’的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'C20')null注意： 1．带EXISTS谓词的查询是先执行外层查询，然后再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。 2．由于EXISTS的子查询只能返回真或假值，因此在子查询中指定列名是没有意义的。所以在有EXISTS的子查询中，其目标列名序列通常都用‘*’。 数据查询功能 数据查询功能 子查询 上述查询语句的处理过程为： （1）找外层表Student表的第一行，根据其Sno值处理内层查询； （2）用外层的值执行内层查询，如果有符合条件的数据，则EXISTS返回真值，否则返回假值。如果EXISTS返回为真，则外层结果中的当前行数据为符合条件的结果；否则，是不符合条件的结果； （3）顺序处理外层表Student表中的第2、3、 … 行数据，直到处理完所有行。