第5章数据查询 sql 语言应用

1、第第5章章 数据查询数据查询本章导读 所谓数据查询就是用户发出一个数据请求，按照指定的条件，从数据库的相关表中找到满足条件的信息的过程。数据查询涉及两个方面，一是用户指定查询条件，二是一是用户指定查询条件，二是系统进行处理并把查询结果返回给用户。系统进行处理并把查询结果返回给用户。SQL Server 2005中的数据查询是基于SQL语言来实现的，本章将介绍在SQL Server 2005中使用SQL语言对数据进行查询的各种操作。本章目录 5.1 SQL概述概述 5.2 单表查询单表查询 5.3 连接查询连接查询 5.4 高级查询高级查询 本章小结本章小结5.1 SQL概述概述 5.1.1 S

2、QL语言简介语言简介 5.1.2 SQL的特点的特点 5.1.3 SQL的查询功能的查询功能5.1 SQL概述概述 SQL是Structured Query Language（结构化查询语言）的缩写，它是一个综合的、功能极强又简捷易学的语言，集数据查询、数据操纵、数据定义和数据控制于一体，这使它被用户和业界接受，成为国际标准。5.1.1 SQL语言简介语言简介 SQL是用于对存放在计算机数据库中的数据是用于对存放在计算机数据库中的数据进行组织、管理和检索的一种工具。进行组织、管理和检索的一种工具。用户想要检索数据库中的数据时，就通过SQL语言发出请求，接着 DBMS （数据库管理系统。譬如：S

3、QL Server、Oracle）对该 SQL 请求进行处理并检索所要求的数据，最后将其返回给用户，此过程被称作为数据查询，这也就是数据查询语言这一名称的由来。表5-1 SQL语言动词SQL功能功能动动 词词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REMOVE 查询是SQL语言的重要组成部分，但不是全部，SQL语言集数据查询、数据操纵、数据定义和数据 控制功能于一体，功能极强。5.1.2 SQL的特点的特点SQL是一种一体化的语言2. 高度非过程化3. 面向集合的操作方式4. 以同一种语法结构提供两种使用

4、方式5.1.3 SQL的查询功能的查询功能 SQL语言的核心功能是查询，故查询命令也称为SELECT命令，其基本形式由SELECT-FROM-WHERE查询块组成，多个查询可以嵌套执行。 SELECT查询语句的目标是从数据库中检索满足条件的记录，查询语句并不会改变数据库中的数据，它只是检索数据，查询的结果仍是一个表。 SELECT语句:其主要功能是实现数据源数据的筛选、投影和连接操作，并能够完成筛选字段重命名、对数据源数据组合、分类汇总、排序等具体操作，具有非常强大的数据查询功能。SELECTSELECT语句的一般格式：语句的一般格式： SELECT ALL|DISTINCT SELECT A

5、LL|DISTINCT ,. . FROM FROM , . . WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC|DESC;2 ASC|DESC;【例5-1】查询students表中姓名为“王芳”的stu_name字段和class_name字段。select stu_name，class_namefrom studentsWhere stu_name=王芳 5.2 单表查询单表查询 5.2.1 指定列查询指定列查询 5.2.2 指定记录行查询指定记录行查询 5.2.3 函数的使用函数的使用 5.2.4 对查询结

6、果排序对查询结果排序 5.2.5 对查询结果分组对查询结果分组5.2 单表查询单表查询 5.2.1 指定列查询指定列查询 1查询所有列 查询所有列是指将表中的所有属性列都选出来，可有两种方法。一是在SELECT关键字后面列出所有列名。若列的显示顺序与其在基表中的顺序相同，也可以简单地将“目标列表达式”指定为星号“*”。 【例5-2】查询所有学生信息。select * from students或select stu_no,stu_name,stu_age,stu_spec,stu_starttime,class_namefrom students2查询指定列 当用户只对表中的一部分属性列感兴趣

7、时，可选择表中的部分列，查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。【例5-3】查询所有学生的学号，姓名，年龄，班级select stu_no,stu_name,stu_age,stu_specfrom students或改变列显示顺序select stu_name,stu_no, stu_spec,stu_agefrom students3. 查询经过计算的值 SELECT子句的“目标列表达式”不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 【例5-4】查全体学生的姓名及其出生年份。select stu_name,2010

8、-stu_agefrom students select stu_name,出生年份：,2010-stu_agefrom students4指定列别名 除添加注释列为指定列进行解释和说明外，用户还可以通过指定别名的方式来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。指定列别名的方法是在列的后面（用空格分开）指定相应列的别名，其语法格式有以下3种。（1）列名 别名 （2）列名 AS 别名（3）别名=列名【例5-5】将上例结果列分别用“姓名”和“出生年月”表示。（1）select stu_name 姓名,2010-stu_age 出生年份from students（

9、2）select stu_name as 姓名,2010-stu_age as 出生年份from students（3）select 姓名=stu_name ,出生年份=2010-stu_age from students5.2.2 指定记录行查询指定记录行查询1简单的条件查询简单的条件查询即只设定一个条件的查询。查询条件查询条件谓词谓词比较比较=，=，=，!=，!，！，！，NOT+含上述比较含上述比较运算符的条件表达式运算符的条件表达式确定范围确定范围BETWEEN AND，NOT BETWEEN AND确定集合确定集合IN，NOT IN字符匹配字符匹配LIKE，NOT LIKE空值空值IS

10、 NULL，IS NOT NULL多重条件多重条件AND，OR表5-2 WHERE子句常用的查询条件【例例5-6】查询所有软件工程班学生的信息查询所有软件工程班学生的信息select *from studentswhere stu_spec =软件工程软件工程【例例5-7】在在students表中查询所有年龄在表中查询所有年龄在20岁以下的岁以下的学生姓名及其年龄，查询列名用中文表示。学生姓名及其年龄，查询列名用中文表示。select stu_name as 姓名姓名,stu_age as 年龄年龄from studentswhere stu_age=19【例5-9】在teachers表中查询

11、职称为讲师的男教师或职称为助教的女教师信息。select *from teacherswhere (teach_sex=男 and teach_tech=讲师) or (teach_sex=女 and teach_tech=助教)3指定范围查询 范围查询指查找属性值在（或不在）指定范围内的记录行。实现方法范围查询指查找属性值在（或不在）指定范围内的记录行。实现方法如下：如下：NOT BETWEENAND意为意为“在在和和之间之间”，这个查询条件等价于，这个查询条件等价于“x=表达式表达式1 AND x=表达表达式式2”【例例5-10】在学生表在学生表students中查询所有年龄在中查询所有年

12、龄在18-19岁之间的学生岁之间的学生的信息。的信息。use xjglselect *from studentswhere stu_age between 18 and 19【例例5-11】在学生表在学生表students中查询所有年龄不在中查询所有年龄不在18至至20岁之间的岁之间的学生的信息。学生的信息。select *from studentswhere stu_age not between 18 and 204. 指定集合查询 集合查询用来查找属性值属于（或不属于）指定集集合查询用来查找属性值属于（或不属于）指定集合的元组。通过谓词合的元组。通过谓词NOT IN来实现。具体格式如下：

13、来实现。具体格式如下： 字段表达式字段表达式 NOTIN （集合元素列表）（集合元素列表）【例例5-12】在学生表在学生表students中查询所有软件工程专业和计算机应中查询所有软件工程专业和计算机应用专业的学生的信息。用专业的学生的信息。select *from studentswhere stu_spec in (软件工程软件工程,计算机应用计算机应用)【例例5-13】在学生表在学生表students中查询所有不学软件工程专业和计算中查询所有不学软件工程专业和计算机应用专业的学生的信息机应用专业的学生的信息select *from studentswhere stu_spec not i

14、n(软件工程软件工程,计算机应用计算机应用)5字符匹配查询 谓词LIKE可以用来进行字符的匹配查询，它判断列值是否与指定的字符串格式相匹配，可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。其一般语句格式如下：NOTLIKE 匹配串 该语句的功能是查找指定的属性列值与“匹配串”相匹配的记录。“匹配串”中的搜索模式，可以使用下列有效 SQL Server 的通配符。%（百分号）： 表示从0n个任意字符。_（下划线）：表示单个的任意字符。 （封闭方括号）：表示方括号中列出的任意一个字符。：任意一个没有在方括号里列出的字符。匹配结果为逻辑类

15、型，如果为逻辑真值则将对应的记录加入结果集中。【例例5-14】在学生表在学生表students中查询所有姓唐的学生的信息。中查询所有姓唐的学生的信息。select *from studentswhere stu_name like 唐唐%【例例5-15】在学生表在学生表students中查询所有不姓唐，且名字只有二个汉字中查询所有不姓唐，且名字只有二个汉字的学生的信息。的学生的信息。select *from studentswhere stu_name like 唐唐_【例例5-16】在学生表在学生表students中查询所有学号以中查询所有学号以T开头，第三位学号开头，第三位学号是是7或或8

16、的学生的信息。的学生的信息。select *from studentswhere stu_no like T_7,8%【例例5-17】在学生表在学生表students中查询所有学号第三位取中查询所有学号第三位取78，第，第4位不是位不是23的学生的信息。的学生的信息。select *from studentswhere stu_no like _7,8_2-3%6空值判断符 谓词IS NULL和IS NOT NULL可用来查询空值和非空值，其中NULL表示的是空值，不是0，也不是空字符串，而是指不存在或不确定的值。注意“IS”不能用等号“=”代替。【例例5-18】在选课表在选课表xcourse

17、s中查询缺考学生的学号、课中查询缺考学生的学号、课程号、成绩。程号、成绩。select stu_no 学号,cour_no 课程号,xcour_score 课程成绩from xcourseswhere xcour_score is null7删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。【例例5-19】在学生表在学生表students中查询学生表中的各个系名。中查询学生表中的各个系名。select stu_spec 系名fro

18、m students删除重复行删除重复行select distinct stu_spec 系名from students8取N行 若只想显示查询结果集的前部分记录，可利用若只想显示查询结果集的前部分记录，可利用TOP子句限制返子句限制返到结果集中的行数，其基本格式如下：到结果集中的行数，其基本格式如下：TOP n PERCENT 其中可选项其中可选项“PERCENT”用于返回的结果集记录的百分比，如用于返回的结果集记录的百分比，如未指定未指定PERCENT，n就是返回的行数，如指定了就是返回的行数，如指定了PERCENT，n就就是返回的结果集行的百分比。是返回的结果集行的百分比。【例例5-20

19、】在学生表在学生表students中查询前三位学生的信息。中查询前三位学生的信息。select top 3 * from students【例例5-21】在学生表在学生表students中查询前中查询前20%记录的学生的记录的学生的信息。信息。select top 20 percent * from students5.2.3 函数的使用函数的使用为了进一步方便用户，增强检索功能，为了进一步方便用户，增强检索功能，SQL提供了许多集函数提供了许多集函数 ，主要包括：主要包括： COUNT（DISTINCT|ALL *）：统计元组个数）：统计元组个数 COUNT（DISTINCT|ALL ）：统

20、计一列中值的个数）：统计一列中值的个数 SUM（DISTINCT|ALL ）：计算一列值的总和（此列必）：计算一列值的总和（此列必须是数值型）须是数值型） AVG（DISTINCT|ALL ）：计算一列值的平均值）：计算一列值的平均值（此列必须是数值型）（此列必须是数值型） MAX（DISTINCT|ALL ）：求一列值中的最大值）：求一列值中的最大值 MIN（DISTINCT|ALL ）：求一列值中的最小值）：求一列值中的最小值 【例例5-22】在学生表在学生表students中查询学软件工程学生的中查询学软件工程学生的平均年龄。平均年龄。select avg(stu_age) 平均年龄fr

21、om studentswhere stu_spec=软件工程【例例5-23】在选课表在选课表xcourses中查询选修了课程的学生中查询选修了课程的学生的人数。的人数。select count(distinct stu_no)as 学生人数from xcourses【例例5-24】在选课表在选课表xcourses中查询学习中查询学习C01课程的学课程的学生的最高分数。生的最高分数。select max(xcour_score)as 最高分from xcourseswhere cour_no=C015.2.4 对查询结果排序对查询结果排序 如果没有指定查询结果的显示顺序，如果没有指定查询结果的显

22、示顺序，DBMS将按其最方便的顺将按其最方便的顺序（通常是记录在表中的先后顺序）输出查询结果。用户也可以序（通常是记录在表中的先后顺序）输出查询结果。用户也可以用用ORDER BY子句指定按照一个或多个属性列的升序（子句指定按照一个或多个属性列的升序（ASC）或降）或降序（序（DESC）重新排列查询结果，其中升序）重新排列查询结果，其中升序ASC为缺省值。为缺省值。ORDER BY子句的语法格式为：子句的语法格式为：ORDER BY 列名列名 ASC|DESC ,n排序规则如下：（1）中英文字符按其ASCII码大小进行比较。（2）数值型数据根据其数值大小进行比较。（3）日期型数据按年、月、日的

23、数值大小进行比较。（4）逻辑型数据false小于true。若有多个属性列进行排序，则先按第一个属性列排序，若该属性列中有相同的内容，则按第二个属性列进行排序，依次类推。【例例5-25】在选课表在选课表xcourses中查询学号为中查询学号为“T07001”学生的课程号及成绩，并将其成绩从低到高排序。学生的课程号及成绩，并将其成绩从低到高排序。select stu_no 学号,cour_no 课程号,xcour_score 成绩from xcourseswhere stu_no=T07001order by xcour_score【例例5-26】在学生表在学生表students中查询所有男生的学

24、号、中查询所有男生的学号、姓名、性别、年龄，要求按年龄降序排列，如年龄相姓名、性别、年龄，要求按年龄降序排列，如年龄相同则按学号升序排列。同则按学号升序排列。select stu_no 学号,stu_name 姓名,stu_sex 性别,stu_age 年龄from studentswhere stu_sex=男order by stu_age desc,stu_no5.2.5 对查询结果分组对查询结果分组GROUP BY子句的语法格式如下：GROUP BY 列名 ,nHAVING 分组条件可按一列或多列分组，如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用

25、HAVING短语指定筛选条件。【例例5-27】在学生表在学生表students中查询软件工程各个班级学生的平中查询软件工程各个班级学生的平均年龄。均年龄。select class_name 班级,avg(stu_age)平均年龄from studentswhere stu_spec=软件工程group by class_name【例例5-28】在学生表在学生表students中查询各个系、各个班学生的平均中查询各个系、各个班学生的平均年龄。年龄。select stu_spec 院系,class_name 班级,avg(stu_age)平均年龄from studentsgroup by stu_

26、spec,class_name【例例5-29】在选课表在选课表xcourses中查询平均成绩大于中查询平均成绩大于75的学生的学的学生的学号及平均成绩，要求计算平均成绩时只将几个成绩计算在内，并号及平均成绩，要求计算平均成绩时只将几个成绩计算在内，并按成绩从高到低排序。按成绩从高到低排序。select stu_no 学号,avg(xcour_score)平均成绩from xcourseswhere xcour_score =60group by stu_nohaving avg(xcour_score)75order by 平均成绩5.3 连接查询连接查询 5.3.1 等值与非等值连接查询等值

27、与非等值连接查询 5.3.2 自身连接自身连接 5.3.3 外连接外连接 5.3.4 交叉连接交叉连接 5.3.5 关系图关系图5.3 连接查询连接查询5.3.1 等值与非等值连接查询等值与非等值连接查询用来连接两个表的条件称为连接运算符，通过连接运算符可以实现多个表查询，其一般格式为：表名表名1.列名列名1 比较运算符比较运算符 表名表名2.列名列名2其中比较运算符主要有：=（等于）、（大于）、=（大于等于）、=80或或select students.stu_no 学号,stu_name 姓名,xcourses.cour_no 课程号,cour_name 课程名,xcour_score 成绩

28、from students , xcourses,courseswhere xcour_score=80 and students.stu_no=xcourses.stu_noand courses.cour_no=xcourses.cour_no5.3.2 自身连接自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。【例例5-32】在学籍管理数据库在学籍管理数据库xjgl中查询学分不低于操作系统学分的课程中查询学分不低于操作系统学分的课程名及学分信息。并按学分的降序排列。名及学分信息。并按学分的降序排列。select y.cour_name

29、课程名,y.cour_credit 学分from courses x, courses ywhere x.cour_name=操作系统 and x.cour_credity.cour_creditorder by y.cour_credit desc5.3.3 外连接外连接采用外连接时，返回到查询结果集合中的不仅包含符合连接条件的记录行，而且还包括左表(左外连接时)、右表(右外连接时)或两个连接表(全外连接)中的所有记录行。1. 左外连接其语法格式如下：SELECT 列 FROM 表1 LEFTOUTERJOIN 表2 ON 表1.列1=表2.列2 【例例5-33】在学籍管理数据库在学籍管理数

30、据库xjgl中查询所有学生的选课信息，输出学号、中查询所有学生的选课信息，输出学号、姓名、课程号和成绩姓名、课程号和成绩4项。包括不选任何课程的学生。项。包括不选任何课程的学生。select students.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefrom students left join xcourseson students.stu_no=xcourses.stu_no2. 右外连接右外连接是对连接条件中右边的表不加限制，即连接条件右边的表中的数据会全部显示出来，其语法格式如下： 完成语句如下：SEL

31、ECT 列FROM 表1 RIGHTOUTERJOIN 表2ON 表1.列1=表2.列2【例例5-34】在学籍管理数据库在学籍管理数据库xjgl中查询所有学生的选课信息，输出学号、中查询所有学生的选课信息，输出学号、姓名、课程号和成绩姓名、课程号和成绩4项。包括有成绩但没有学生信息的学生。项。包括有成绩但没有学生信息的学生。select students.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefrom students right join xcourseson students.stu_no=xcourse

32、s.stu_no3. 全外连接全外连接是对两个表都不加限制，即所有两个表中的记录行会全部包括在结果集中，不管是否匹配，其语法格式如下： SELECT 列FROM 表1 FULLOUTER JOIN 表2ON 表1.列1=表2.列2【例例5-35】在学籍管理数据库在学籍管理数据库xjgl中查询所有学生的选课信息，输出学号、中查询所有学生的选课信息，输出学号、姓名、课程号和成绩姓名、课程号和成绩4项。项。select students.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefrom students full j

33、oin xcourseson students.stu_no=xcourses.stu_no5.3.4 交叉连接交叉连接交叉连接不带WHERE 子句，其语句格式如下：SELECT 列 FROM 表1 CROSS JOIN 表2或 SELECT 列 FROM 表1, 表2【例例5-36】在学籍管理数据库在学籍管理数据库xjgl中对中对teachers表和表和teach表进行交叉连接。表进行交叉连接。select *from teachers cross join teach或或select *from teachers ,teach5.3.5 关系图关系图 SQL Server 2005中的关系

34、是表之间的连接，用一个表中的外键引用另一个表中的主键，要了解多个表之间都存在哪些内在联系，可使用SSMS的数据库关系图。5.4 高级查询高级查询 5.4.1 嵌套查询嵌套查询 5.4.2 联合查询联合查询 5.4.3 在在SSMS中实现查询中实现查询5.4.1 嵌套查询嵌套查询1带有IN谓词的子查询指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。 【例例5-38】在学籍管理数据库在学籍管理数据库xjgl中查询与中查询与“唐薇唐薇”在同一个专业学习的在同一个专业学习的学生。学生。第步：第步： select stu_specfrom studentswhere stu_n

35、ame=唐薇第步：第步： select stu_no,stu_name,stu_specfrom studentswhere stu_spec=计算机应用或或select stu_no,stu_name,stu_specfrom studentswhere stu_spec in (select stu_spec from students where stu_name=唐薇)【例例5-39】在学籍管理数据库在学籍管理数据库xjgl中查询选修了操作系统的学生的学号、中查询选修了操作系统的学生的学号、姓名及性别。姓名及性别。select stu_no,stu_name,stu_sexfrom s

36、tudentswhere stu_no in (select stu_no from xcourses where cour_no=(select cour_no from courses where cour_name=操作系统)【例例5-40】在学籍管理数据库在学籍管理数据库xjgl中查询非讲师职称的教师所教课程的中查询非讲师职称的教师所教课程的课程号及所用教材。课程号及所用教材。select cour_no,teach_bookfrom teachwhere teach_nonot in(select teach_no from teachers where teach_tech=讲师)

37、5.4.1 嵌套查询嵌套查询2. 带有比较运算符的子查询 父查询与子查询之间用比较运算符进行连接。【例例5-38】在学籍管理数据库在学籍管理数据库xjgl中查询与中查询与“唐薇唐薇”在同一个专业学习的在同一个专业学习的学生。学生。select stu_no,stu_name,stu_specfrom studentswhere stu_spec = (select stu_spec from students where stu_name=唐薇)5.4.1 嵌套查询嵌套查询3带有ANY或ALL谓词的子查询比较运算符及其语义见课本表5-3【例例5-41】在学籍管理数据库在学籍管理数据库xjgl中查询除了软件工程的其他专业的，并中查询除了软件工程的其他专业的，并且年龄不大于软件工程专业最小年龄的学生信息。且年龄不大于软件工程专业最小年龄的学生信息。select