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

第5章数据查询本章导读

所谓数据查询就是用户发出一个数据请求，按照指定的条件，从数据库的相关表中找到满足条件的信息的过程。数据查询涉及两个方面，一是用户指定查询条件，二是系统进行处理并把查询结果返回给用户。SQLServer2005中的数据查询是基于SQL语言来实现的，本章将介绍在SQLServer2005中使用SQL语言对数据进行查询的各种操作。本章目录5.1

SQL概述5.2单表查询5.3连接查询5.4

高级查询本章小结5.1

SQL概述

5.1.1SQL语言简介5.1.2SQL的特点5.1.3SQL的查询功能5.1

SQL概述

SQL是StructuredQueryLanguage（结构化查询语言）的缩写，它是一个综合的、功能极强又简捷易学的语言，集数据查询、数据操纵、数据定义和数据控制于一体，这使它被用户和业界接受，成为国际标准。5.1.1SQL语言简介

SQL是用于对存放在计算机数据库中的数据进行组织、管理和检索的一种工具。用户想要检索数据库中的数据时，就通过SQL语言发出请求，接着DBMS（数据库管理系统。譬如：SQLServer、Oracle）对该

SQL请求进行处理并检索所要求的数据，最后将其返回给用户，此过程被称作为数据查询，这也就是数据查询语言这一名称的由来。表5-1SQL语言动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REMOVE查询是SQL语言的重要组成部分，但不是全部，SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，功能极强。5.1.2SQL的特点SQL是一种一体化的语言2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方式5.1.3SQL的查询功能SQL语言的核心功能是查询，故查询命令也称为SELECT命令，其基本形式由SELECT-FROM-WHERE查询块组成，多个查询可以嵌套执行。SELECT查询语句的目标是从数据库中检索满足条件的记录，查询语句并不会改变数据库中的数据，它只是检索数据，查询的结果仍是一个表。

SELECT语句:其主要功能是实现数据源数据的筛选、投影和连接操作，并能够完成筛选字段重命名、对数据源数据组合、分类汇总、排序等具体操作，具有非常强大的数据查询功能。SELECT语句的一般格式：

SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...FROM<表名或视图名>[,<表名或视图名>]...[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];【例5-1】查询students表中姓名为“王芳”的stu_name字段和class_name字段。selectstu_name，class_namefromstudentsWherestu_name=‘王芳’5.2单表查询5.2.1指定列查询5.2.2指定记录行查询5.2.3函数的使用5.2.4对查询结果排序5.2.5对查询结果分组5.2单表查询5.2.1指定列查询1．查询所有列查询所有列是指将表中的所有属性列都选出来，可有两种方法。一是在SELECT关键字后面列出所有列名。若列的显示顺序与其在基表中的顺序相同，也可以简单地将“目标列表达式”指定为星号“*”。【例5-2】查询所有学生信息。select*fromstudents或selectstu_no,stu_name,stu_age,stu_spec,stu_starttime,class_namefromstudents2．查询指定列当用户只对表中的一部分属性列感兴趣时，可选择表中的部分列，查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。【例5-3】查询所有学生的学号，姓名，年龄，班级selectstu_no,stu_name,stu_age,stu_specfromstudents或改变列显示顺序selectstu_name,stu_no,stu_spec,stu_agefromstudents3.查询经过计算的值SELECT子句的“目标列表达式”不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。【例5-4】查全体学生的姓名及其出生年份。selectstu_name,2010-stu_agefromstudentsselectstu_name,'出生年份：',2010-stu_agefromstudents4．指定列别名除添加注释列为指定列进行解释和说明外，用户还可以通过指定别名的方式来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。指定列别名的方法是在列的后面（用空格分开）指定相应列的别名，其语法格式有以下3种。（1）列名别名（2）列名AS别名（3）别名=列名【例5-5】将上例结果列分别用“姓名”和“出生年月”表示。（1）selectstu_name姓名,2010-stu_age出生年份fromstudents（2）selectstu_nameas姓名,2010-stu_ageas出生年份fromstudents（3）select姓名=stu_name,出生年份=2010-stu_agefromstudents5.2.2指定记录行查询1．简单的条件查询简单的条件查询即只设定一个条件的查询。查询条件谓词比较=，>，<，>=，<=，!=<>，!>，！<，NOT+含上述比较运算符的条件表达式确定范围BETWEEN

AND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOT

NULL多重条件AND，OR表5-2WHERE子句常用的查询条件【例5-6】查询所有软件工程班学生的信息select*fromstudentswherestu_spec='软件工程‘【例5-7】在students表中查询所有年龄在20岁以下的学生姓名及其年龄，查询列名用中文表示。selectstu_nameas姓名,stu_ageas年龄fromstudentswherestu_age<202．复合条件查询当查询条件不只一个时，可使用逻辑运算符AND或OR进行联接，当运算结果为真时，相应记录就加入结果集。如果AND和OR这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但可以通过使用括号改变优先级。【例5-8】在学生表students中查询学习软件工程专业且年龄在19岁以上（包括19岁）的学生学号、姓名、年龄、专业。selectstu_no学号,stu_name姓名,stu_age年龄,stu_spec专业fromstudentswherestu_spec='软件工程'andstu_age>=19【例5-9】在teachers表中查询职称为讲师的男教师或职称为助教的女教师信息。select*fromteacherswhere(teach_sex='男'andteach_tech='讲师')or(teach_sex='女'andteach_tech='助教‘)3．指定范围查询

范围查询指查找属性值在（或不在）指定范围内的记录行。实现方法如下：[NOT]BETWEEN……AND……意为“在…和…之间”，这个查询条件等价于“x>=表达式1ANDx<=表达式2”【例5-10】在学生表students中查询所有年龄在18-19岁之间的学生的信息。usexjglselect*fromstudentswherestu_agebetween18and19【例5-11】在学生表students中查询所有年龄不在18至20岁之间的学生的信息。select*fromstudentswherestu_agenotbetween18and204.指定集合查询

集合查询用来查找属性值属于（或不属于）指定集合的元组。通过谓词[NOT]IN来实现。具体格式如下：字段表达式[NOT]IN（集合元素列表）【例5-12】在学生表students中查询所有软件工程专业和计算机应用专业的学生的信息。select*fromstudentswherestu_specin('软件工程','计算机应用')【例5-13】在学生表students中查询所有不学软件工程专业和计算机应用专业的学生的信息select*fromstudentswherestu_specnotin(‘软件工程’,’计算机应用’)5．字符匹配查询谓词LIKE可以用来进行字符的匹配查询，它判断列值是否与指定的字符串格式相匹配，可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。其一般语句格式如下：[NOT]LIKE匹配串该语句的功能是查找指定的属性列值与“匹配串”相匹配的记录。“匹配串”中的搜索模式，可以使用下列有效SQLServer的通配符。%（百分号）：表示从0~n个任意字符。_（下划线）：表示单个的任意字符。[]（封闭方括号）：表示方括号中列出的任意一个字符。[^]：任意一个没有在方括号里列出的字符。匹配结果为逻辑类型，如果为逻辑真值则将对应的记录加入结果集中。【例5-14】在学生表students中查询所有姓唐的学生的信息。select*fromstudentswherestu_namelike'唐%‘【例5-15】在学生表students中查询所有不姓唐，且名字只有二个汉字的学生的信息。select*fromstudentswherestu_namelike'唐_‘【例5-16】在学生表students中查询所有学号以‘T’开头，第三位学号是7或8的学生的信息。select*fromstudentswherestu_nolike'T_[7,8]%'【例5-17】在学生表students中查询所有学号第三位取7~8，第4位不是2~3的学生的信息。select*fromstudentswherestu_nolike'__[7,8]_[^2-3]%'6．空值判断符谓词ISNULL和ISNOTNULL可用来查询空值和非空值，其中NULL表示的是空值，不是0，也不是空字符串，而是指不存在或不确定的值。注意“IS”不能用等号“=”代替。【例5-18】在选课表xcourses中查询缺考学生的学号、课程号、成绩。selectstu_no学号,cour_no课程号,xcour_score课程成绩fromxcourseswherexcour_scoreisnull7．删除重复行

SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。【例5-19】在学生表students中查询学生表中的各个系名。selectstu_spec系名fromstudents删除重复行selectdistinctstu_spec系名fromstudents8．取N行若只想显示查询结果集的前部分记录，可利用TOP子句限制返到结果集中的行数，其基本格式如下：TOPn[PERCENT]其中可选项“PERCENT”用于返回的结果集记录的百分比，如未指定PERCENT，n就是返回的行数，如指定了PERCENT，n就是返回的结果集行的百分比。【例5-20】在学生表students中查询前三位学生的信息。selecttop3*fromstudents【例5-21】在学生表students中查询前20%记录的学生的信息。selecttop20percent*fromstudents5.2.3函数的使用为了进一步方便用户，增强检索功能，SQL提供了许多集函数

，主要包括：COUNT（[DISTINCT|ALL]*）：统计元组个数COUNT（[DISTINCT|ALL]<列名>）：统计一列中值的个数SUM（[DISTINCT|ALL]<列名>）：计算一列值的总和（此列必须是数值型）AVG（[DISTINCT|ALL]<列名>）：计算一列值的平均值（此列必须是数值型）MAX（[DISTINCT|ALL]<列名>）：求一列值中的最大值MIN（[DISTINCT|ALL]<列名>）：求一列值中的最小值【例5-22】在学生表students中查询学软件工程学生的平均年龄。selectavg(stu_age)平均年龄fromstudentswherestu_spec='软件工程'【例5-23】在选课表xcourses中查询选修了课程的学生的人数。selectcount(distinctstu_no)as学生人数fromxcourses【例5-24】在选课表xcourses中查询学习C01课程的学生的最高分数。selectmax(xcour_score)as最高分fromxcourseswherecour_no='C01’5.2.4对查询结果排序如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是记录在表中的先后顺序）输出查询结果。用户也可以用ORDERBY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。ORDERBY子句的语法格式为：ORDERBY列名[ASC|DESC][,…n]排序规则如下：（1）中英文字符按其ASCII码大小进行比较。（2）数值型数据根据其数值大小进行比较。（3）日期型数据按年、月、日的数值大小进行比较。（4）逻辑型数据false小于true。若有多个属性列进行排序，则先按第一个属性列排序，若该属性列中有相同的内容，则按第二个属性列进行排序，依次类推。【例5-25】在选课表xcourses中查询学号为“T07001”学生的课程号及成绩，并将其成绩从低到高排序。selectstu_no学号,cour_no课程号,xcour_score成绩fromxcourseswherestu_no='T07001'orderbyxcour_score【例5-26】在学生表students中查询所有男生的学号、姓名、性别、年龄，要求按年龄降序排列，如年龄相同则按学号升序排列。selectstu_no学号,stu_name姓名,stu_sex性别,stu_age年龄fromstudentswherestu_sex='男'orderbystu_agedesc,stu_no5.2.5对查询结果分组GROUPBY子句的语法格式如下：GROUPBY列名[,…n][HAVING分组条件]可按一列或多列分组，如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。【例5-27】在学生表students中查询软件工程各个班级学生的平均年龄。selectclass_name'班级',avg(stu_age)'平均年龄'fromstudentswherestu_spec='软件工程'groupbyclass_name【例5-28】在学生表students中查询各个系、各个班学生的平均年龄。selectstu_spec院系,class_name班级,avg(stu_age)平均年龄fromstudentsgroupbystu_spec,class_name【例5-29】在选课表xcourses中查询平均成绩大于75的学生的学号及平均成绩，要求计算平均成绩时只将几个成绩计算在内，并按成绩从高到低排序。selectstu_no学号,avg(xcour_score)平均成绩fromxcourseswherexcour_score>=60groupbystu_nohavingavg(xcour_score)>75orderby平均成绩5.3连接查询5.3.1等值与非等值连接查询5.3.2自身连接5.3.3外连接5.3.4交叉连接5.3.5关系图5.3连接查询5.3.1等值与非等值连接查询用来连接两个表的条件称为连接运算符，通过连接运算符可以实现多个表查询，其一般格式为：[表名1.]列名1比较运算符[表名2.]列名2其中比较运算符主要有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=（不等于）。【例5-30】在学籍管理数据库xjgl中查询学生的学号、姓名和成绩信息。usexjglselectstudents.stu_no学号,stu_name姓名,xcour_score成绩fromstudentsjoinxcoursesonstudents.stu_no=xcourses.stu_no或selectstudents.stu_no学号,stu_name姓名,xcour_score成绩fromstudents,xcourseswherestudents.stu_no=xcourses.stu_no【例5-31】在学籍管理数据库xjgl中查询成绩在80分以上的学生的学号、姓名，所选课程的课程号，课程名和成绩信息。selectstudents.stu_no学号,stu_name姓名,xcourses.cour_no课程号,cour_name课程名,xcour_score成绩fromstudentsjoinxcoursesonstudents.stu_no=xcourses.stu_nojoincoursesoncourses.cour_no=xcourses.cour_nowherexcour_score>=80或selectstudents.stu_no学号,stu_name姓名,xcourses.cour_no课程号,cour_name课程名,xcour_score成绩fromstudents,xcourses,courseswherexcour_score>=80andstudents.stu_no=xcourses.stu_noandcourses.cour_no=xcourses.cour_no5.3.2自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。【例5-32】在学籍管理数据库xjgl中查询学分不低于操作系统学分的课程名及学分信息。并按学分的降序排列。selecty.cour_name课程名,y.cour_credit学分fromcoursesx,coursesywherex.cour_name='操作系统'andx.cour_credit<y.cour_creditorderbyy.cour_creditdesc5.3.3外连接采用外连接时，返回到查询结果集合中的不仅包含符合连接条件的记录行，而且还包括左表(左外连接时)、右表(右外连接时)或两个连接表(全外连接)中的所有记录行。1.左外连接其语法格式如下：SELECT列FROM表1LEFT[OUTER]JOIN表2ON表1.列1=表2.列2【例5-33】在学籍管理数据库xjgl中查询所有学生的选课信息，输出学号、姓名、课程号和成绩4项。包括不选任何课程的学生。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsleftjoinxcoursesonstudents.stu_no=xcourses.stu_no2.右外连接右外连接是对连接条件中右边的表不加限制，即连接条件右边的表中的数据会全部显示出来，其语法格式如下：完成语句如下：SELECT列FROM表1RIGHT[OUTER]JOIN表2ON表1.列1=表2.列2【例5-34】在学籍管理数据库xjgl中查询所有学生的选课信息，输出学号、姓名、课程号和成绩4项。包括有成绩但没有学生信息的学生。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsrightjoinxcoursesonstudents.stu_no=xcourses.stu_no3.全外连接全外连接是对两个表都不加限制，即所有两个表中的记录行会全部包括在结果集中，不管是否匹配，其语法格式如下：SELECT列FROM表1FULL[OUTER]JOIN表2ON表1.列1=表2.列2【例5-35】在学籍管理数据库xjgl中查询所有学生的选课信息，输出学号、姓名、课程号和成绩4项。selectstudents.stu_no,students.stu_name,xcourses.cour_no,xcourses.xcour_scorefromstudentsfulljoinxcoursesonstudents.stu_no=xcourses.stu_no5.3.4交叉连接交叉连接不带WHERE子句，其语句格式如下：SELECT列FROM表1CROSSJOIN表2或

SELECT列FROM表1,表2【例5-36】在学籍管理数据库xjgl中对teachers表和teach表进行交叉连接。select*fromteacherscrossjointeach或select*fromteachers,teach5.3.5关系图SQLServer2005中的关系是表之间的连接，用一个表中的外键引用另一个表中的主键，要了解多个表之间都存在哪些内在联系，可使用SSMS的数据库关系图。5.4

高级查询5.4.1嵌套查询5.4.2联合查询5.4.3在SSMS中实现查询5.4.1嵌套查询1．带有IN谓词的子查询——指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。【例5-38】在学籍管理数据库xjgl中查询与“唐薇”在同一个专业学习的学生。第①步：selectstu_specfromstudentswherestu_name='唐薇‘第②步：selectstu_no,stu_name,stu_specfromstudentswherestu_spec='计算机应用'或selectstu_no,stu_name,stu_specfromstudentswherestu_specin(selectstu_specfromstudentswherestu_name='唐薇')【例5-39】在学籍管理数据库xjgl中查询选修了操作系统的学生的学号、姓名及性别。selectstu_no,stu_name,stu_sexfromstudentswherestu_noin(selectstu_nofromxcourseswherecour_no=(selectcour_nofromcourseswherecour_name='操作系统'))【例5-40】在学籍管理数据库xjgl中查询非讲师职称的教师所教课程的课程号及所用教材。selectcour_no,teach_bookfromteachwhereteach_nonotin(selectteach_nofromteacherswhereteach_tech='讲师')5.4.1嵌套查询2.带有比较运算符的子查询——父查询与子查询之间用比较运算符进行连接。【例5-38】在学籍管理数据库xjgl中查询与“唐薇”在同一个专业学习的学生。selectstu_no,stu_name,stu_specfromstudentswherestu_spec=(selectstu_specfromstudentswherestu_name='唐薇')5.4.1嵌套查询3．带有ANY或ALL谓词的子查询比较运算符及其语义见课本表5-3【例5-41】在学籍管理数据库xjgl中查询除了软件工程的其他专业的，并且年龄不大于软件工程专业最小年龄的学生信息。selectstu_no,stu_name,stu_spec,stu_agefromstudentswherestu_age<=all(selectstu_agefromstudentswherestu_spec='软件工程')andstu_spec!='软件工程'或selectstu_no,stu_name,stu_spec,stu_agefromstudentswherestu_age<=(selectmin(stu_age)fromstudentswherestu_spec='软件工程')andstu_spec!='软件工程'5.4.1嵌套查询4．带有EXISTS谓词的子查询——判断子查询结果集合中是否有数据行返回。它的返回值为true或false，不产生实际值。【例5-42】在学籍管理数据库xjgl中查询选修了课程C02的学生学号、姓名和性别。selectstu_no,stu_name,stu_sex