第05章SQL-Server查询处理和表数据编辑

1第5章查询处理和表数据编辑

5.1查询数据

5.2表数据编辑25.1查询数据SQL用SELECT语句进行数据查询

SELECT语句的格式SELECT[DISTINCT]<目标列表达式>[,…n]FROM<表名或视图名>[,…n][WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]SELECT语句的含义

根据WHERE条件，从FROM指定的表中找出满足条件的元组，按目标列表达式，选出属性值，形成结果表。

35.1查询数据5.1.1简单查询

5.1.2统计

5.1.3连接查询

5.1.4子查询

5.1.5联合查询

45.1.1简单查询1．最简单的查询

2．查询满足条件的元组3．对查询结果排序51．最简单的查询省略的一些可选成分，得最简单的查询命令：SELECT[DISTINCT]<目标列表达式>[,…n]FROM<表名或视图名>对一张表的某些列进行操作，功能为：（1）查询指定列

（2）查询所有列

（3）查询计算列

（4）为列起别名

（5）使用DISTINCT关键字消除重复元组

6（1）查询指定列【例5-1】查询全体学生的姓名、学号和电话号码。SELECT 姓名,学号,移动电话FROM 学生表列的输出顺序可以与表中的列顺序不同。7（2）查询所有列【例5-2】查询全体学生的详细信息SELECT *FROM 学生表用“*”表示查询表的所有列。8（3）查询计算列也可以查询由常量、变量和函数构成的表达式【例5-3】将累计学分降低10％后显示出来SELECT 姓名,累计学分,累计学分-累计学分*0.1FROM 学生表查询结果为：

姓名

累计学分

（无列名）王东民 160 144…9（4）为列起别名目的：满足用户的习惯，为计算列起名。方法：①<目标列表达式>[AS]<别名>②<别名>=<目标列表达式>【例5-4】将每个学生的累计学分降低10%后显示出来，要求查询结果表的标题用英语或字母显示。SELECT姓名ASname,累计学分Ogpa,Ngpa=累计学分-累计学分*0.1FROM 学生表查询结果如下：name Ogpa Ngpa------------------------------------王东民 160 144…当别名含有空格时要用单引号括起10（5）使用DISTINCT关键字消除重复元组无DISTINCT时，结果中可能含重复行有DISTINCT时，自动消除结果中的重复行【例5-5】查询每个院系有在读学生的专业。

SELECTSdepa,SmajorFROMStudent查询结果为：

Sdepa Smajor信息学院

计算机信息学院

计算机……结果中含重复行SELECTDISTINCT所在院系,专业FROM 学生表查询结果如下：

所在院系

专业信息学院

计算机信息学院

信息管理……结果中无重复行DISTINCT应紧跟SELECT

112．查询满足条件的元组通过在WHERE子句中指定查询条件来实现WHERE子句常用的查询条件:

查询条件运算符（

）

条件(逻辑表达式)

备注比较大小=,>,<,>=,<=,!=,<>,!>,!<op1

op2双目运算确定范围[NOT]BETWEENANDop1[NOT]BETWEENop2ANDop3三目运算确定集合[NOT]INop1[NOT]INop2双目运算字符匹配[NOT]LIKEop1[NOT]LIKEop2双目运算空值判断IS[NOT]NULLopIS[NOT]NULL单目运算组合条件NOT,AND,OR,()NOTop,op1ANDop2，op1ORop2NOT是单目,其余是双目,括号用于改变运算优先级122．查询满足条件的元组通过在WHERE子句中指定查询条件来实现WHERE子句常用的查询条件:(1)比较大小(2)确定范围(3)确定集合(4)字符匹配(5)空值判断(6)组合条件13(1)比较大小返回查询条件:op1

op2

(比较运算符

):

=,>,<,>=,<=,!=,<>,!>,!<op1

和

op2：由常量、变量、函数构成的算术/字符串表达式【例5-6】查询来自杭州的所有学生。

SELECT*FROM学生表WHERE籍贯='杭州'【例5-7】查询累计学分在160分以下的学生姓名和累计学分。SELECT姓名,累计学分ROM学生表WHERE累计学分<16014(2)确定范围查询条件:op1[NOT]BETWEENop2ANDop3

op1、op2、

op3：由常量、变量、函数构成的算术/字符串表达式。【例5-8】查询累计学分不在150和159之间的学生姓名和累计学分。

SELECT姓名,累计学分FROM学生表WHERE累计学分NOTBETWEEN150AND159【例5-9】查询姓名在’陈’和’李’之间的学生学号和姓名。SELECT学号,姓名FROM学生表WHERE姓名BETWEEN'陈'AND'李'由字符串定义的范围是根据字符内码的顺序确定的(一般按字典顺序)返回15(3)确定集合查询条件:op1[NOT]INop2

op1：由常量、变量、函数构成的算术/字符串表达式op2：集合，表示为(e1,e2,…,en)，其中e1,e2,…,en为集合的元素，它们可以是与op1同类型的常量、变量和函数构成的表达式。含义：若op1(不)是集合op2的元素,则条件为真,否则为假。【例5-10】查询来自杭州、宁波或温州的学生学号和姓名。

SELECTSno,SnameFROMStudent

WHEREScityIN('杭州','宁波','温州')返回

【例5-11】查询既不来自杭州,也不来自宁波的学号和姓名。

SELECT学号,姓名FROM学生表WHERE籍贯IN('杭州'，'宁波'，'温州')【例5-12】查询学号后两位是“09”，或者等于学号前两位或中间两位的学生学号和姓名。

SELECT学号,姓名FROM学生表WHERESUBSTRING(学号,6,2)IN(‘09’，SUBSTRING(学号,2,2)，SUBSTRING(学号,4,2))

SUBSTRING(s,p,c):取子串函数,返回字符串s中从第p个字符开始，长度为c的子串。16(4)字符匹配查询条件:s1[NOT]LIKEs2[ESCAPE’<换码字符>’]s1和s2是由常量、变量、函数构成的字符串表达式。s1称为主字符串，s2称为模式字符串。模式字符串除了包含普通字符外，还包含下列特殊字符（称为通配符）：

% 匹配任意长度的字符串（长度可以为0）_ 匹配任意一个字符[c1c2…cn] 匹配字符c1,c2,…,cn中的一个。当c1,c2,…,cn 连续时可简化为[c1-cn][^c1c2…cn] 匹配除c1,c2,…,cn外的一个字符。当c1,c2,…, cn连续时可简化为[^c1-cn]

含义：若s1(不)与s2相匹配，则条件为真，否则为假。

返回

【例5-13】查询姓名中第二个字为“鹏”的学生学号和姓名。

SELECTSno,SnameFROMStudent

WHERESnameLIKE'_鹏%'

【例5-14】查询学号长度不等于7，或者学号后6位含有非数字字符的学生学号和姓名。

SELECT学号,姓名FROM学生表

WHERE学号NOTLIKE'S[0-9][0-9][0-9][0-9][0-9][0-9]'【例5-15】查询学号最后一位既不是“1”和“3”，也不是“9”的学生学号和姓名。

SELECT学号,姓名FROM学生表

WHERE学号LIKE'%[^139]'ESCAPE短语:使模式串中的某个通配符恢复原来的含义。

【例5-16】查询课程名以“DB_”开头的课程信息。

SELECT*FROM课程表

WHERE课名LIKE'DB\_%'ESCAPE'\'17(5)空值判断查询条件:expIS[NOT]NULLexp是由常量、变量、函数构成的表达式。含义：exp的值(不)为空值，则条件为真，否则为假。【例5-17】查询没有成绩的学号和开课计划编号。

SELECT学号,开课号FROM选课表WHERE成绩ISNUL注意“IS”不能用“=”代替。

【例5-18】查询有成绩的学号和开课计划编号。SELECT学号,开课号FROM选课表WHERE成绩ISNOTNULL注意“ISNOT”不能用“!=”或“<>”代替。

返回

18(6)组合条件返回

查询条件:用NOT、AND、OR和括号将多个逻辑表达式连接起来所得的复杂逻辑表达式。括号的优先级最高，NOT次之，AND再次之，OR的优先级最低。【例5-19】查询这样的男生，他的电话号码前3位是“130”，他来自杭州或者宁波，他既不主修电子商务专业，也不主修信息管理专业。

SELECT*FROM学生表WHERE性别=‘男’ANDSUBSTRING(移动电话,1,3)=‘130’AND(籍贯='杭州'OR籍贯='宁波')ANDNOT专业IN('电子商务','信息管理')193．对查询结果排序用ORDERBY子句按照一个或多个列升序(ASC)或降序(DESC)输出查询结果，其中ASC为默认值

。语法：ORDERBY{<排序列>[ASC|DESC]}[,…n]

【例5-20】查询选修了开课计划编号为’010101’的课程的学生学号和成绩，查询结果按分数降序排列

SELECTSno,GradeFROMEnrollmentWHEREOno='010101'ORDERBYGradeDESC可以用列在SELECT子句中的顺序编号来指定排序列，上例的ORDERBY子句可改为：ORDERBY2DESC返回若需按SELECT子句中的计算列排序，则ORDERBY子句可用三种方法来表示这个计算列：1）列表达式；2）列顺序编号；3）列别名。

【例5-21】查询选修了开课计划编号为’010101’的课程的学生学号、成绩以及加了10分后的新成绩，查询结果按原成绩降序、按新成绩升序排列。

SELECT学号,成绩,成绩+10ASNew成绩FROM选课表WHERE开课号='010101'ORDERBY成绩DESC,成绩+10上例中的成绩+10也可改写为：New成绩或3。也可按SELECT子句中没有出现的列排序，此时不能用顺序编号来表示排序列。

205.1.2统计为了有效处理SQL查询结果集，SQLServer提供了一序列的统计函数，用来实现对数据集进行汇总、求平均等各种运算。本节内容包括：1．常用的统计函数2．分组查询返回

211．常用的统计函数下表列出了常用的统计函数，其中DISTINCT表示统计时要剔除重复值。函数格式函数功能COUNT([DISTINCT]*)统计元组个数COUNT([DISTINCT]<列表达式>)统计列值的个数SUM([DISTINCT]<列表达式>)计算数值型列表达式的总和AVG([DISTINCT]<列表达式>)计算数值型列表达式的平均值MAX([DISTINCT]<列表达式>)求列表达式的最大值MIN([DISTINCT]<列表达式>)求列表达式的最小值这些函数常在SELECT子句中直接作为计算列或参与计算列的运算，对数据集进行统计运算并返回结果。

221．常用的统计函数【例5-23】查询所有课本的总价格和平均价格，以及打七折后的总价格和平均价格。

SELECTSUM(定价),AVG(定价),SUM(定价*0.7),AVG(定价*0.7)FROM课程表查询结果为：

(无列名) (无列名) (无列名) (无列名)93 31 65.1 21.7关于本例有如下几条说明：

(1)语句搜索了Course表的所有行，但只返回一行结果。(2)统计函数表示的列是计算列，结果无列名，可指定别名。(3)统计列值为空的元组不参与统计计算。231．常用的统计函数若结合WHERE子句来使用统计函数，则只有满足WHERE条件的行才参与统计。

【例5-24】查询课程编号前两位数字是’02’的课程所用课本的总价格和平均价格。

SELECTSUM(定价),AVG(定价)FROM课程表WHERE课号LIKE'C02%‘在统计函数中可以用DISTINCT关键字来剔除重复值。【例5-25】查询至少选修了一门课程的学生总数。

SELECTCOUNT(DISTINCT学号)FROM选课表COUNT(*)用来统计满足条件的元组个数。【例5-26】查询课程编号前两位数字是’02’的课程总数。SELECTCOUNT(*)FROM课程表WHERE课号LIKE'C02%'返回242．分组查询返回以上关于统计函数的例子都是针对满足WHERE条件的查询结果集进行的统计。如果想先对查询结果集进行分组，然后再对每个组进行统计，就要用到GROUPBY子句了。GROUPBY子句可以将查询结果集按一列或多列取值相等的原则进行分组。含GROUPBY子句的查询称为分组查询。本节内容包括：(1)使用GROUPBY子句进行分组

(2)使用HAVING短语来筛选组

(1)使用GROUPBY子句进行分组【例5-27】查询各门课程的课程号及相应的选课人数。SELECT开课号,COUNT(学号)FROM选课表GROUPBY开课号查询结果如下：开课号 (无列名)-------------------010101 5010201 1010202 12526(1)使用GROUPBY子句进行分组分组目的：细化统计函数的作用对象。如果未对查询结果集分组，统计函数将作用于整个查询结果集，即整个查询结果集只有一个统计值。否则，统计函数将作用于每个组，即每一个组都有一个统计值。GROUPBY子句的语法：GROUPBY<分组列>[,…n]

【例5-27】查询各门课程的课程号及相应的选课人数。SELECTOno,COUNT(Sno)FROMEnrollmentGROUPBYOno本例先对Enrollment表按Ono的取值进行分组，所有具有相同Ono值的元组被分为一组，然后用COUNT函数统计每一组的学生人数。

返回两点注意：①GROUPBY中的列名只能是FROM子句所列表的列名，不能是列的别名。例如下列查询是错误的：SELECT开课号

AS开课编号,COUNT(学号)FROM选课表GROUPBY开课编号②使用GROUPBY子句后，SELECT子句的目标列表达式所涉及的列必须满足：要么在GROUPBY子句中，要么在在某个统计函数中。例如下列查询是错误的：

SELECT开课号,学号FROM选课表GROUPBY开课号因为学号既不在GROUPBY子句中，也不在统计函数中。27(2)使用HAVING短语来筛选组HAVING短语的作用：指定组筛选条件。【例5-28】查询学号前5位为’S0601’且选修了两门以上(含)课程的学生学号。SELECT学号FROM选课表WHERE学号LIKE'S0601%'GROUPBY学号HAVINGCOUNT(*)>=2WHERE子句与HAVING短语的区别

(1)作用对象不同：WHERE作用表，HAVING作用于组。(2)条件构成不同：WHERE条件不能直接包含统计函数，而HAVING条件所涉及的列必须要么在GROUPBY子句中，要么在某个统计函数中。返回285.1.3连接查询单表查询：仅涉及一个表的查询(FROM子句仅含一个表)。连接查询：涉及多个表的查询(FROM子句包含多个表)。本节内容包括：1.连接查询和单表查询的区别和联系

2.为FROM子句后的表起别名

3.使用JOIN…ON关键字4.外连接返回291.连接查询和单表查询的区别和联系区别：单表查询只涉及一张表，而连接查询涉及多张表。联系：连接查询是针对多表笛卡尔积的单表查询。连接查询的特殊性：(1)重名列加“<表名>.”前缀作为限定。(2)WHERE条件：连接条件[AND普通查询条件]。(3)涉及n张表的连接查询至少应包括n-1个连接条件。【例5-29】查询学生的基本信息及其选课信息。SELECT学生表.*,开课号,成绩FROM学生表,选课表WHERE学生表.学号=选课表.学号返回【例5-30】查询选修了开课计划编号为“010101”的课程的学生学号和姓名。

SELECT学生表.学号,姓名FROM学生表,选课表WHERE学生表.学号=选课表.学号AND开课号='010101'302.为FROM子句后的表起别名格式：FROM{<表名>[[AS]<别名>]}[,…n]目的：(1)用别名作为列的前缀，缩短涉及重名列的子句。(2)当FROM子句含多张相同的表时，必须为它们取不同的别名，在其他子句中用别名作为列的前缀。【例5-31】查询至少选修了学号为“S060110”的学生所选一门课程的学生学号和姓名。SELECTDISTINCTZ.学号,姓名FROM选课表ASX,选课表ASY,学生表ASZWHEREX.学号=‘S060110’ANDY.学号!=X.学号ANDY.开课号=X.开课号ANDY.学号=Z.学号返回313.使用JOIN…ON关键字目的：将连接条件和普通查询条件分开。格式：SELECT子句FROM<表名>{JOIN<表名>ON<连接条件>}[…n][WHERE<普通查询条件>][其他子句]【例5-32】用JOIN和ON关键字实现例5-31的查询。SELECTDISTINCTZ.学号,姓名FROM选课表XJOIN选课表YONY.学号!=X.学号ANDY.开课号=X.开课号JOIN学生表ZONY.学号=Z.学号WHEREX.学号='S060110'返回325.1.4子查询查询块:(SELECT语句),代表查询的中间结果集。子查询：将一个查询块嵌入另一个中，称嵌套查询。上层查询块称父查询，下层查询块称子查询。

用途：对子查询进行集合检查来表达查询条件。子查询检查方法：1.检查给定值是否在结果集中2.用给定值和结果集中的元素进行大小比较3.检查结果集是否为空返回331.检查给定值是否在结果集中查询条件：父查询的属性列IN(子查询)。含义：判断属性列的值是否在子查询的结果中。【例5-34】查询选修了“数据库原理”的学生学号和姓名。SELECT学号,姓名FROM学生表WHERE学号IN(SELECT学号

FROM选课表WHERE开课号IN(SELECT开课号FROM开课表WHERE课号IN(SELECT课号FROM课程表WHERE课名='数据库原理’)))嵌套查询的特点:(1)允许多层嵌套，求解顺序：由内向外。(2)对用IN或比较运算符连接的子查询，其SELECT子句只能有一个列表达式，且左边列表达式和右边SELECT中的列表达式含义要相同。返回342.用给定值和结果集中的元素进行大小比较含义：指父查询与子查询之间用比较运算符进行连接。分为单值比较和多值比较两类。(1)单值比较当子查询的结果集只包含一个值时，可用比较运算符直接连接父查询的列表达式和子查询结果集，实现其间的大小比较。返回单值的子查询可参加任何合法的表达式运算。【例5-35】查询累计学分比“胡汉民”多2分以上（含）的学生学号、姓名和累计学分。

SELECTSno,Sname,SgpaFROMStudentWHERESgpa>=(SELECTSgpaFROMStudentWHERESname='胡汉民')+2【例5-36】查询学生S060101的姓名和平均成绩

SELECT姓名,(SELECTSUM(成绩)FROM选课表

WHERE学号='S060101')FROM学生表WHERE学号='S060101'352.用给定值和结果集中的元素进行大小比较返回(2)多值比较当子查询的结果集包含多个值时，用给定值和结果集中的某个值进行的比较。此时父查询与子查询之间要用比较运算符后缀ANY或ALL进行连接。【例5-37】查询累计学分比计算机专业和信息管理专业所有学生都低的学生名单。SELECT姓名FROM学生表WHERE 专业<>'计算机'AND专业<>'信息管理'AND

累计学分<ALL(SELECT累计学分FROM学生表

WHERE专业IN('计算机','信息管理'))本例也可以用统计函数实现：SELECT姓名FROM学生表

WHERE 专业<>'计算机'AND专业<>'信息管理'AND

累计学分<(SELECTMIN(累计学分)FROM学生表 WHERE专业IN('计算机','信息管理'))ANY和ALL与统计函数的对应关系见见教材表5-8。

363.检查结果集是否为空语法：[NOT]EXISTS(子查询)EXISTS：子查询结果集不空则返回真，否则返回假NOTEXISTS：子查询结果集为空则返回真，否则返回假【例5-38】查询选修了开课计划号为010101的学生姓名。SELECT姓名FROM学生表ASSWHEREEXISTS(SELECT* FROM选课表ASEWHEREE.学号=S.学号AND开课号='010101')这类子查询具有如下特点：

(1)子查询的条件往往要引用上层查询所涉及的表。

(2)子查询的SELECT子句写成SELECT*即可。返回375.2表数据编辑表数据编辑又称数据更新，包括插入数据、修改数据和删除数据三类命令。本节内容包括：5.2.1插入数据

5.2.2修改数据

5.2.3删除数据返回385.2.1插入数据1．插入单个元组:INSERT…VALUES语句,格式为:INSERT[INTO]<表名>[(<列名>[,…n])]VALUES(<表达式>[,…n])注意：(1)未出现在列名列表中的列插入时取空值；(2)表达式数量必须和列名数量相等，表达式的数据类型必须和对应列的数据类型相兼容；(3)关系中的NOTNULL列必须出现在列名列表中；(4)若省略列名列表，则VALUES须指定所有列的值。【例5-40】将(’S060102’,’010201’)插入Enrollment表。INSERTINTO选课表(学号,开课号)VALUES('S060102','010201')返回5.2.1插入数据2．插入子查询的结果:INSERT…SELECT语句,格式为:INSERT[INTO]<表名>[(<列名>[,…n])]SELECT语句【例5-42】求各个专业学生的平均累计学分，把结果存入表中。CREATETABLE主修专业(专业CHAR(20),avgpaINT)GOINSERTINTO主修专业(专业,avgpa)SELECT专业,AVG(累计学分)FROM学生表GROUPBY专业395.2.1插入数据3．使用SELECT…INTO语句进行数据插入,格式为:SELECT<目标列>[,…n]INTO<新表名>[SELECT语句的其他子句]注意：(1)系统会自动创建一个新表，新表的结构由目标列表达式定义，然后将

SELECT语句的结果集插入这个新表；(2)当目标列是计算列时，必须为它起别名。【例5-43】用SELECT…INTO语句改写例5-42。

SELECT专业,AVG(累计学分)AS平均累计学分INTO主修专业FROM学生表GROUPBY专业40415.2.2修改数据返回1．数据修改语句：UPDATE,格式为:UPDATE<表名>SET{<列名>=<表达式>}[,…n][FROM<表名>[,…n]][WHERE<修改条件>]注意：(1)UPDATE语句用来修改指定表中满足WHERE条件的元组。修改方法是用SET子句中<表达式>的值取代相应列的值；(2)修改条件和SELECT语句中WHERE条件完全相同，它不仅可以直接使用UPDATE后面的表，也可通过引入FROM子句直接使用其他表，还可以将子查询嵌入修改条件中。5.2.2修改数据2．修改给定表的所有行若省略WHERE子句，则UPDATE将修改表的所有行。【例5-44】将所有学生的累计学分增加3分。

UPDATE学生表SET累计学分=累计学分+33．基于给定表修改某些行

如果省略FROM子句，但含有WHERE子句，则UPDATE语句将修改满足修改条件的行，但是此时的修改条件只能直接使用UPDATE后面的表所包含的列。

【例5-45】将计算机专业所有女生的籍贯改为“杭州”，累计学分增加3分。

UPDATE学生表SET累计学分=累计学分+3,籍贯='杭州'WHERE专业='计算机'AND性别='女'425.2.2修改数据4．基于其他表修改某些行如果修改条件需要使用其他表的列，就要用FROM子句将这些表引入到UPDATE语句中。【例5-46】将计算机专业所有学生的数据库原理课程的成绩增加10分。UPDATE选课表SET成绩=成绩+10FROM开课表ASO,课程表ASC,学生表ASSWHERE专业='计算机'AND课名='数据库原理'ANDC.课号=O.课号

ANDO.开课号=选课表.开课号

AND选课表.学号=S.学号435.2.2修改数据5．用子查询修改某些行UPDATE中的修改条件还可以通过嵌入子查询进行构造。【例5-47】用子查询构造例5-46的修改条件，实现相同功能。UPDATE选课表SET成绩=成绩+10FROM学生表