第4章-SQLSERVER-数据库的操作(查询语句)

四数据查询与视图

4.1SELECT语句概述

4.2单表查询

4.3多表查询

4.4嵌套查询

4.5视图 SQL语言最主要的功能是数据库查询，它是数据库其他操作（如统计、插入、修改、删除）的基础，也是DBMS的核心功能之一。使用SQL语句将要连接的表、查询所需的字段、筛选记录的条件、记录分组的依据、排序的方式以及查询结果的显示方式，写在一条SQL语句中，就可以从表或视图中迅速、方便地检索数据，具有十分灵活的使用方式和丰富的功能。4.1SELECT语句概述

SELECT

select_list[INTO

new_table]FROM

table_source

[WHERE

search_condition][GROUP

BY

group_by_expression][HAVING

search_condition][ORDER

BY

order_expression[ASC|DESC]]

<select_list>::={*|{table_name|view_name|table_alias}.* |{column_name|expression}[[AS]column_alias] |column_alias=expression}[,...n]

将查询结果存入新表new_table中。指定查询条件。指定将查询结果进行分组。

与GROUPBY联合使用，指定分组条件。

指定查询结果按order_expression进行排序。

指定在FROM子句内返回表或视图的所有列。指定查询的列名。指定列名、常量、函数以及由运算符连接的列名、常量和函数的任意组合，或者是子查询。

指定查询结果集内列名的别名。

整个SELECT语句的含义是：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的记录，再按照SELECT子句中的select_list，选出记录中的列值、或计算值、或汇总值形成的查询结果。

SELECT语句既可以完成简单的单表查询，也可以完成复杂的多表查询和嵌套查询。4.2单表查询——选择表中若干列如果用户只对表中部分列感兴趣，查询时可不使用WHERE子句，选择表中的全部列或部分列，也称作投影查询。基本语法为：SELECT[ALL|DISTINCT][TOPn[PERCENT]]<select_list>FROMtable_source

参数说明：ALL：默认设置，指定在结果集中可以显示重复行。DISTINCT：指定在结果集中去除重复行，只显示唯一行。TOPn[PERCENT]：指定只从查询结果集中输出前n行或前百分之n行。【例4.1】查询全体学生的学号、姓名和年龄。SELECTSNO,SNAME,SAGEFROMXS查询指定列4.2单表查询——选择表中若干列【例4.2】查询全体学生的情况。SELECTSNO,SNAME,SDEPT,SSEX,SAGE,SHEIGHT,SCPM,SRMKFROMXS或SELECT*FROMXS

查询全部列4.2单表查询——选择表中若干列【例4.3】查询全体学生的姓名及出生年份。（假设当前年份为2009年）。SELECTSNAME,2009-SAGEFROMXS

查询结果中包含计算项4.2单表查询——选择表中若干列【例4.4】查询全体学生的姓名，并将姓和名分别显示。SELECTSNAME,SUBSTRING(SNAME,1,1),SUBSTRING(SNAME,2,2)FROMXS查询结果中包含计算项4.2单表查询——选择表中若干列【例4.5】对例4.4中的查询结果指定列标题。查询结果中的列标题显示SELECTSNAMEAS学生姓名,SUBSTRING(SNAME,1,1)AS姓,SUBSTRING(SNAME,2,2)AS名FROMXS或SELECT学生姓名=SNAME,姓=SUBSTRING(SNAME,1,1),名=SUBSTRING(SNAME,2,2)FROMXS4.2单表查询——选择表中若干列【例4.6】对比以下两条查询语句的执行结果，理解DISTINCT的作用。查询结果中消除重复行①SELECTSDEPT,SSEXFROMXS②SELECTDISTINCTSDEPT,SSEXFROMXS

4.2单表查询——选择表中若干列【例4.7】查询XS表中前6个学生的情况。限制结果集的返回行数①SELECTTOP6*FROMXS

②SELECTTOP60PERCENT*FROMXS

4.2单表查询——选择表中若干列【例4.8】查询xs_kc表中学生成绩，对其成绩按以下规则进行替换：成绩90~100为“优秀”，80~89为“良好”，70~79为“中等”，60~69为“及格”，<60为“不及格”。

替换显示结果的内容SELECT学号=SNO,课程号=CNO,成绩=SCORE,等级=CASE

WHENSCORE<60THEN'不及格'

WHENSCORE>=60ANDSCORE<=69THEN'及格'

WHENSCORE>=70ANDSCORE<=79THEN'中等'

WHENSCORE>=80ANDSCORE<=89THEN'良好'

ELSE'优秀'ENDFROMXS_KC

4.2单表查询——选择表中若干列查询时使用WHERE子句，选择表中满足条件的若干行，也称作选择查询。基本语法为：SELECTselect_listFROMtable_sourceWHEREsearch_condition

查询条件运算符比较=、>、<、>=、<=、<>或!=等；确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件AND,OR4.2单表查询——选择表中若干行【例4.9】查询选修课程号为‘C01’的学生的学号和成绩。SELECTSNO,SCOREFROMXS_KCWHERECNO='C01'

比较运算符【例4.10】查询成绩高于85分的学生的学号、课程号和成绩。

SELECTSNO,CNO,SCOREFROMXS_KCWHERESCORE>854.2单表查询——选择表中若干行【例4.11】查询年龄在20~23岁（含20岁和23岁）之间的学生姓名、系别。SELECTSNAME,SDEPTFROMXSWHERESAGEBETWEEN20AND23确定范围4.2单表查询——选择表中若干行【例4.12】查询选修C01或C02的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMXS_KCWHERECNOIN('C01','C02')确定集合4.2单表查询——选择表中若干行【例4.13】查询所有姓张的学生的学号和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'张%'字符匹配【例4.14】查询姓名中第二个汉字是“一”的学生的学号和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'_一%'4.2单表查询——选择表中若干行【例4.15】查询没有备注信息的学生情况。SELECT*FROMXSWHERESRMKISNULL涉及空值4.2单表查询——选择表中若干行【例4.16】查询电力专业所有男生的情况。SELECT*FROMXSWHERESDEPT='电力'ANDSSEX='男'多重条件【例4.17】查询选修C01或C02且分数大于85分的学生的学号及成绩。SELECTSNO,SCOREFROMXS_KCWHERE(CNO='C01'ORCNO='C02')ANDSCORE>854.2单表查询——选择表中若干行4.2单表查询——聚集函数

计算诸如平均值和总和的函数被称为聚集函数。使用聚集函数时，系统对整个表或表的某个组中的列进行汇总、计算，然后为它创建相应字段的单个的值。在SELECT语句中可以单独使用聚集函数，也可以与语句GROUPBY联合使用，列的数据类型决定了在该列上可以使用的聚集函数类型。

函数作用数据类型COUNT统计列中元组的个数任意类型MIN求一列值中的最小值除bit以外的数据类型MAX求一列值中的最大值SUM计算一列值的总和只能用于数值型字段，如int、decimal、float、money等AVG计算一列值的平均值【例4.18】查询电力系学生的总人数。SELECTCOUNT(*)FROMXSWHERESDEPT='电力'COUNT函数【例4.19】查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSNO)FROMXS_KC4.2单表查询——聚集函数

【例4.20】查询课程号为‘C01’的学生成绩的最高分和最低分。SELECTMAX(SCORE)最高分,MIN(SCORE)最低分FROMXS_KCWHERECNO='C01'MAX函数和MIN函数【例4.21】查询课程号为‘C01’的学生成绩的总分和平均分。

SELECTSUM(SCORE)总分,AVG(SCORE)平均分FROMXS_KCWHERECNO='C01'SUM函数和AVG函数4.2单表查询——聚集函数

4.2单表查询——查询结果分组

在SELECT语句中使用GROUPBY子句可以将查询结果分组，并返回行的汇总信息，即对于GROUPBY子句中定义的每个组，各返回一个结果。此时，SELECT子句中的列名必须为分组列或列函数。基本语法为：

[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]其中，WITHCUBE或WITHROLLUP用于在查询结果中附加汇总结果。

【例4.22】查询各系学生人数。SELECTSDEPT系别,COUNT(*)人数FROMXSGROUPBYSDEPTGROUPBY基本用法4.2单表查询——查询结果分组

【例4.23】查询各门课程的平均成绩和选修人数。SELECTCNO课程号,AVG(SCORE)平均成绩,COUNT(*)选修人数FROMXS_KCGROUPBYCNOGROUPBY基本用法4.2单表查询——查询结果分组

【例4.24】查询各系男生人数、女生人数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXGROUPBY基本用法4.2单表查询——查询结果分组

【例4.25】查询各系男生人数、女生人数、各系人数和总人数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXWITHROLLUP使用ROLLUP和CUBE子句4.2单表查询——查询结果分组

【例4.26】除例4.25的查询要求外，还需汇总男生总数和女生总数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXWITHCUBE使用ROLLUP和CUBE子句4.2单表查询——查询结果分组

【例4.27】查询平均成绩80分以上的学生学号和平均成绩。

SELECTSNO,AVG(SCORE)AS平均成绩FROMXS_KCGROUPBYSNOHAVINGAVG(SCORE)>=80使用HAVING子句4.2单表查询——查询结果分组

【例4.28】查询全体学生的情况，查询结果按所在系的系号升序排列，同系学生按年龄降序排列。SELECT*FROMXSORDERBYSDEPT,SAGEDESC【例4.29】查询学生的学号和平均分，且按平均分降序排列。

SELECTSNO,AVG(SCORE)AS平均成绩FROMXS_KCGROUPBYSNOORDERBY平均成绩

DESC4.2单表查询——查询结果分组

【例4.30】查询电力系学生的学号、姓名、年龄，并产生一个学生总人数行。

SELECTSNO,SNAME,SAGEFROMXSWHERESDEPT='电力'COMPUTECOUNT(SNO)4.2单表查询——查询结果分组

【例4.31】将学生按专业名排序，并汇总各专业人数和平均身高。SELECTSDEPT,SNO,SNAME,SAGE,SHEIGHTFROMXSORDERBYSDEPTCOMPUTECOUNT(SNO),AVG(SHEIGHT)BYSDEPT4.2单表查询——查询结果分组

4.2单表查询——查询结果生成新表

【例4.32】由XS表创建“电力系学生”表，包括学号、姓名、年龄、身高。

SELECTSNO,SNAME,SAGE,SHEIGHTINTO电力系学生FROMXSWHERESDEPT='电力'4.2单表查询——联合查询

【例4.33】将XS表中数学系学生和“电力系学生”表数据合并。SELECTSNO,SNAME,SAGE,SHEIGHTFROMXSWHERESDEPT='数学'UNIONSELECT*FROM电力系学生

4.3多表查询实际应用中，数据往往需要同时在相关联的多个表中得到。例如，需要查询电力系所有学生的成绩。多表查询是指同时涉及两个以上的表的查询，又称为连接查询。连接查询是关系数据库中最主要的查询。包括内连接、外连接和自连接。

内连接连接查询中用来连接多个表的条件称为连接条件，连接的表名之间用逗号隔开。内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。其一般格式为：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>也可使用[INNER]JOINON子句实现，其一般格式为：FROM<表名1>[INNER]JOIN<表名2>[ON<条件表达式>]内连接【例4.33】查询每个学生的情况及选修课程的情况。SELECTXS.*,XS_KC.*FROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOSELECTXS.*,XS_KC.*FROMXSJOINXS_KCONXS.SNO=XS_KC.SNO4.3多表查询内连接【例4.34】查询选修了“电磁场”课程且成绩在85分以上的学生学号、姓名、课程名和成绩。SELECTXS.SNO,SNAME,CNAME,SCOREFROMXS,KC,XS_KCWHEREXS.SNO=XS_KC.SNOANDKC.CNO=XS_KC.CNOANDCNAME='电磁场'ANDSCORE>=85SELECTXS.SNO,SNAME,CNAME,SCOREFROMXSJOINXS_KCONXS.SNO=XS_KC.SNOJOINKCONKC.CNO=XS_KC.CNOWHERECNAME='电磁场'ANDSCORE>=854.3多表查询外连接外连接返回FROM子句中提到的至少一个表或视图的所有行，包括左外连接、右外连接和全外连接。其一般格式为：FROM<表名1>LEFT|RIGHT|FULL[OUTER]JOIN<表名2>[ON<条件表达式>]左外连接的结果集包括LEFTOUTER子句中指定的左表的所有行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为NULL。右外连接是左外连接的反向连接，返回右表的所有行，如果右表的某行在左表中没有匹配行，则将为左表返回NULL。全外连接是左外连接和右外连接的并集。

4.3多表查询外连接【例4.37】查询课程被选修的情况，要求包含学号、课程号、课程名，同时还需显示没人选修的课程。SELECTXS_KC.SNO,KC.CNO,CNAMEFROMXS_KCRIGHTJOINKCONKC.CNO=XS_KC.CNO4.3多表查询自连接将一个表与它自身进行连接，称为自连接。使用自连接时需为表指定两个别名，且对列的引用都要用别名限定。

【例4.38】查询与“赵一”同一专业的学生姓名。SELECTDISTINCTA.SNAMEFROMXSAJOINXSBONA.SDEPT=B.SDEPTANDB.SNAME='赵一'4.3多表查询在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询。外层查询称为父查询（或主查询），内层查询称为子查询（或从查询），先处理子查询，再处理父查询。子查询可以嵌套多层，子查询的结果集又成为父查询的条件。4.4嵌套查询4.4嵌套查询简单嵌套查询【例4.39】查询课程号为‘C01’且成绩高于学号为‘010’的所有学生的学号和成绩。SELECTSNO,SCOREFROMXS_KCWHERECNO='C01'ANDSCORE> (SELECTSCOREFROMXS_KC WHERECNO='C01'ANDSNO='010')【例4.40】查询课程号为‘C01’且成绩在平均分以上的学生的学号和姓名。SELECTXS.SNO,SNAMEFROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOANDCNO='C01'ANDSCORE>(SELECTAVG(SCORE)FROMXS_KC WHERECNO='C01')4.4嵌套查询带IN谓词的嵌套查询【例4.41】查询选修了课程的学生情况。SELECT*FROMXSWHERESNOIN(SELECTDISTINCTSNOFROMXS_KC)【例4.42】查询所有课程中不及格的学生的学号、姓名和所在院系。SELECTSNO,SNAME,SDEPTFROMXSWHERESNOIN(SELECTDISTINCTSNOFROMXS_KC WHERESCORE<60)4.5视图视图是从一个或多个表或视图中导出的表，是一个“虚”表，其结构和数据建立在对基本表的查询基础之上。和基本表一样，视图也包括多个被定义的列和多个数据行，但视图中的数据并不以视图结构存储在数据库中，而是存储在视图所引用的表中，并且在引用视图时动态生成。对视图的操作与对表的操作一样，可以对其进行查询、修改(有一定的限制)、删除。对视图中的数据进行修改时，相应的基本表的数据也会发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。4.5视图4.5使用T-SQL管理视图创建视图，语法格式如下：CREATEVIEWview_name

ASselect_statement

注意：在SELECT语句中，不能使用COMPUTE、COMPUTEBY等语句，不能使用INTO关键字，不能使用临时表。

【例4.47】使用T-SQL语句创建视图。CREATEVIEWVIEW2ASSELECTXS.SNO,XS.SNAME,XS.SDEPT,XS_KC.CNO,XS_KC.SCOREFROMXS,XS_KCWHEREXS.SNO=XS_KC.SNOANDXS.SDEPT='电力'ANDXS_KC.SCORE>=804.5使用T-SQL管理视图修改视图，语法格式如下：ALTERVIEW

view_nameASselect_statement删除视图，语法格式如下：

DROPVIEW

view_