数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目5 数据查询

项目5数据查询《数据库应用基础（SQLServer2019）》项目目标掌握常用查询工具的用法理解SELECT语句的语法格式掌握SELECT语句的用法掌握操作结果集的方法掌握创建和使用子查询的方法本章目录5.1理解SELECT语句5.2使用SELECT定义选择列表5.3使用FROM指定表源5.4使用WHERE筛选数据5.5使用ORDERBY对数据排序5.6使用GROUPBY对数据分组5.7操作查询结果集5.8使用子查询理解SELECT语句5.1任务5.1理解SELECT语句SELECT语句可以通过SSMS集成环境或sqlcmd实用工具来执行，也可以通过各种编程语言来执行。借助于该语句对SQLServer数据库发出选择查询，可以从数据库中获取所需要的数据。选择查询可以包含要返回的列、要选择的行、放置行的顺序以及如何对行进行筛选和分组的规范。通过本任务将学习和理解SELECT语句的组成并掌握常用查询工具的用法。任务目标理解SELECT语句的基本组成掌握查询编辑器的使用方法掌握sqlcmd实用工具的使用方法5.1.1SELECT语句的基本组成（1/2）SELECT语句的基本组成SELECT<选择列表>[INTO<新表>]FROM<表源>[WHERE<搜索条件>][GROUPBY<分组表达式>][HAVING<搜索条件>][ORDERBY<排序表达式>[ASC|DESC]]5.1.1SELECT语句的基本组成（2/2）各子句功能说明SELECT：指定返回的列，最多4096个表达式；可以是列引用、常量或函数SELECT...INTO：创建新表并插入查询结果行FROM：指定数据来源（表、视图、表变量、派生表），最多256个表源；可包含联接规范WHERE：筛选返回的行，只有满足条件的行才被采用GROUPBY：将结果划分成行组，通常配合聚合函数使用HAVING：指定行组或聚合的搜索条件；常与GROUPBY配合使用ORDERBY：对结果集排序；ASC升序（默认），DESC降序；可使用列名、别名或位置编号注意：上述子句必须按照指定顺序编写5.1.2常用查询工具（1/3）SQL编辑器SQL编辑器功能输入Transact-SQL语句，可保存为.sql脚本文件执行：按F5，或单击“执行”按钮；选中部分仅执行选中部分语法检查：单击“分析”按钮或按Ctrl+F5（不执行）获取帮助：选择关键字按F1SQL编辑器工具栏按钮按钮功能连接连接到服务器更改连接切换到其他服务器可用数据库切换同一服务器的数据库执行（F5）执行脚本取消执行查询Alt+Break，取消请求分析（Ctrl+F5）检查语法5.1.2常用查询工具（2/3）sqlcmd实用工具sqlcmd功能命令提示符下交互式执行Transact-SQL语句和脚本语句存储在缓冲区，遇到GO命令时作为批处理执行常用命令选项选项说明-U登录ID指定用户登录ID-P密码指定用户密码（区分大小写）-S服务器指定SQLServer实例名称-E使用信任连接（Windows身份验证），默认选项-d数据库名称指定初始数据库-q"查询语句"执行查询，保持sqlcmd运行-Q"查询语句"执行查询，sqlcmd退出-i输入文件指定输入脚本文件-o输出文件指定输出文件5.1.2常用查询工具（2/3）连接示例sqlcmd-S<计算机名>::Windows身份验证，默认实例sqlcmd-S<计算机名>\<实例名>::Windows身份验证，命名实例sqlcmd-UMyLogin-S<计算机名>\<实例名>::SQLServer身份验证sqlcmd-q"SELECT*FROM班级"::执行查询，保持运行sqlcmd-Q"SELECT*FROM班级"-ooutput.txt::执行查询，退出并保存输出例5.1：使用sqlcmd查询班级表命令提示符>sqlcmd1>USE教务管理;2>GO1>SELECT*FROM班级;2>GO1>QUIT使用SELECT定义选择列表5.2任务5.2使用SELECT定义选择列表（1/7）SELECT语句可以通过SSMS集成环境或sqlcmd实用工具来执行，也可以通过各种编程语言来执行。借助于该语句对SQLServer数据库发出选择查询，可以从数据库中获取所需要的数据。选择查询可以包含要返回的列、要选择的行、放置行的顺序以及如何对行进行筛选和分组的规范。通过本任务将学习和理解SELECT语句的组成并掌握常用查询工具的用法。任务目标理解SELECT语句的基本组成掌握查询编辑器的使用方法掌握sqlcmd实用工具的使用方法任务5.2使用SELECT定义选择列表（2/7）5.2.1选择所有列：SELECT*返回源表或视图中所有列，列顺序与创建时相同表结构更改（添加、删除、重命名列）会自动反映例5.2：

SELECT*FROM课程;5.2.2选择部分列：列出列名，逗号分隔若表名或列名含空格，需用方括号括起多表查询中引用同名列时，需加表名前缀（如学生.学号）例5.3：SELECT学号,姓名,性别,班级编号FROM学生;任务5.2使用SELECT定义选择列表（3/7）5.2.3选择特殊列$IDENTITY：引用标识符列$ROWGUID：引用具有ROWGUIDCOL属性的列多表查询时需加表名前缀（如Table1.$IDENTITY）例5.4：SELECT$IDENTITY,姓名,性别,入职时间FROM教师;5.2.4设置结果集列名称（别名）AS子句（推荐）：列名AS列别名或派生列表达式AS派生列名称等号语法（兼容旧版）：列别名=列名或派生列名称=派生列表达式例5.5：SELECTLoginIDAS网络账户,GenderAS性别FROMHumanResources.Employee任务5.2使用SELECT定义选择列表（4/7）5.2.5在选择列表中进行计算（派生列）可包含算术运算符（+、-、*、/、%）可包含函数（日期函数、转换函数等）可包含子查询支持的数值类型：int、smallint、tinyint、decimal、numeric、float、real、money、smallmoney模运算：仅适用于int、smallint、tinyint日期运算：可对date、datetime、smalldatetime进行加减例5.6：计算学生年龄SELECT学号,姓名,DATEDIFF(yy,出生日期,GETDATE())AS年龄FROM学生;DATEDIFF(yy,startdate,enddate)：计算两个日期间的年数任务5.2使用SELECT定义选择列表（5/7）5.2.6从结果集中消除重复行ALL（默认）：保留重复行DISTINCT：仅返回唯一行，空值视为相等，只返回一个NULL例5.7：对比效果--包含重复SELECT课程编号FROM成绩;--消除重复SELECTDISTINCT课程编号FROM成绩;任务5.2使用SELECT定义选择列表（6/7）5.2.7使用TOP限制结果集行数语法：

TOP(表达式)[PERCENT][WITHTIES]表达式：指定返回的行数或百分比PERCENT：按百分比返回WITHTIES：包含与ORDERBY最后一个值相同的所有行（可能超出指定数量）常与ORDERBY配合使用，从排序后的结果集中选取前n行例5.8：--返回前8行SELECTTOP(8)学号,姓名,性别FROM学生;任务5.2使用SELECT定义选择列表（7/7）5.2.8没有FROM子句的SELECT语句当选择列表仅包含常量、变量、算术表达式、系统函数、系统变量时，可省略FROM例5.9：显示系统信息SELECT'欢迎您使用SQLServer2019'AS欢迎信息,GETDATE()AS现在时间,@@SERVERNAMEAS'SQLServer服务器名称',@@VERSIONAS'SQLServer版本号';GETDATE()：当前系统日期时间@@SERVERNAME：本地服务器名称@@VERSION：SQLServer版本信息使用FROM指定表源5.3任务5.3使用FROM指定表源在SELECT语句中，FROM子句用于指定选择查询的数据来源。如果在SELECT语句中不需要访问表中的列，则不必使用FROM子句。如果要使用SELCET语句从表或视图中检索数据，则必须使用FROM子句。FROM子句指定一个用逗号分隔的表名称、视图名称和JOIN子句的列表，使用它可以列出选择列表和WHERE子句中所引用的列所在的表和视图，也可以使用AS子句为表和视图的名称指定别名。使用FROM子句可以指定一个或多个表或视图，并且可以在两个或多个表或视图之间的创建各种类型的联接。通过本任务将学习和掌握如何使用FROM子句为选择查询指定表源。任务目标掌握使用内部联接的方法掌握使用外部联接的方法掌握使用交叉联接的方法5.3.1.使用内部联接内部联接（INNERJOIN）最常用的联接类型，使用比较运算符对联接列中的值进行比较仅返回两个表中满足联接条件的行，丢弃不匹配的行语法：

FROM表1[AS]别名1[INNER]JOIN表2[AS]别名2ON<搜索条件> INNER关键字可省略例5.10：从学生表、课程表、成绩表检索学生成绩SELECTsc.学号,姓名,课程名称,成绩FROM成绩ASscINNERJOIN学生ASstONsc.学号=st.学号INNERJOIN课程AScoONsc.课程编号=co.课程编号;5.3.2使用外部联接外部联接（OUTERJOIN）返回至少一个表中的所有行，不满足条件的行在另一个表对应列显示NULL左外部联接（LEFTOUTERJOIN）：结果集中包括左表所有行，右表不匹配的行输出NULL右外部联接（RIGHTOUTERJOIN）：结果集中包括右表所有行，左表不匹配的行输出NULL完全外部联接（FULLOUTERJOIN）：结果集中包括左表或右表中不满足联接条件的行，对应另一表输出NULL语法：

FROM表1LEFT|RIGHT|FULL[OUTER]JOIN表2ON<搜索条件>例5.11：右外部联接：列出课程表所有行SELECT学号,课程名称,成绩FROM成绩ASsRIGHTJOIN课程AScONs.课程编号=c.课程编号;5.3.3使用交叉联接交叉联接（CROSSJOIN）产生两个表的笛卡尔积结果集行数=左表行数×右表行数不使用WHERE子句时返回所有可能的行组合语法：

FROM表1CROSSJOIN表2例5.12：从学生表和课程表检索数据SELECT姓名,课程名称FROM学生CROSSJOIN课程;学生表72行，课程表30行→结果集72×30=2160行使用WHERE筛选数据5.4任务5.4使用WHERE筛选数据（1/6）在实际应用中，数据库中往往存储着大量的数据，在某个特定的用途中并非总是要使用表中的全部数据，更常见的是使用满足给定条件的那一部分数据，这就需要对查询返回的行进行筛选和限制。通过在SELECT语句中使用WHERE子句可以设置对行的筛选条件，从而保证查询集中仅仅包含所需要的行，而将不需要的行排除在结果集之外。通过本任务将学习和掌握使用WHERE子句筛选数据的方法。任务目标理解WHERE子句的语法格式掌握比较搜索条件的使用方法掌握范围搜索条件的使用方法掌握列表搜索条件的方法掌握搜索条件的模式匹配方法掌握逻辑运算符的使用方法任务5.4使用WHERE筛选数据5.4.1WHERE子句的语法格式WHERE子句语法

SELECT...FROM...WHERE<搜索条件>搜索条件为TRUE的行包含在结果集中，FALSE或UNKNOWN的行被舍弃5.4.2.使用比较搜索条件比较运算符：=、>、<、>=、<=、<>、!>、!<、!=字符串比较受排序规则控制，非Unicode数据忽略尾随空格例5.13：检索学历为“研究生”的教师SELECT教师编号,姓名,性别,学历,入职时间FROM教师WHERE学历='研究生';5.4.3使用范围搜索条件语法：

测试表达式[NOT]BETWEEN起始值AND终止值包含边界值（≥起始值且≤终止值）排除性范围使用>和<例5.14：检索成绩在86~93之间的记录SELECTst.学号,姓名,课程名称,成绩FROM成绩ASscINNERJOIN学生ASstONst.学号=sc.学号INNERJOIN课程AScoONco.课程编号=sc.课程编号WHERE成绩BETWEEN86AND93;5.4.4使用列表搜索条件语法：

测试表达式[NOT]IN(子查询|表达式列表)匹配列表中的任意值即返回TRUE例5.15：检索“张、王、李、赵”四个姓氏的学生SELECT学号,姓名,性别,出生日期,班级编号FROM学生WHERESUBSTRING(姓名,1,1)IN('张','王','李','赵');SUBSTRING(姓名,1,1)取出姓氏5.4.5搜索条件中的模式匹配语法：

匹配表达式[NOT]LIKE模式[ESCAPE转义字符]通配符：% 任意长度字符串（含零个字符）_ 任意单个字符[] 指定范围内的单个字符（如[a-f]）[^] 不在指定范围内的单个字符例5.16：检索“赵、钱、孙、李”四个姓氏的学生SELECT学号,姓名,性别,出生日期,班级编号FROM学生WHERE姓名LIKE'[赵钱孙李]%';转义通配符：使用ESCAPE定义转义符，如搜索10%：LIKE'%10/%%'ESCAPE'/'5.4.6使用逻辑运算符AND：两个条件都为TRUE时返回TRUEOR：任一条件为TRUE时返回TRUENOT：反转条件结果优先级：NOT>AND>OR；括号可强制改变顺序例5.17：检索信息工程系和电子工程系的女教师SELECT教师编号,姓名,性别,学历,系部FROM教师WHERE性别='女'AND(系部='信息工程系'OR系部='电子工程系');使用ORDERBY对数据排序5.5任务5.5使用ORDERBY对数据排序ORDERBY子句用于设置查询结果集的排列顺序。通过在SELECT语句中添加ORDERBY子句，可以使结果集中的行按照一个或多个列的值进行排列，排序方向可以是升序（即从小到大）或降序（即从大到小）。除非指定了ORDERBY子句，否则结果集中的行返回顺序是不能保证的。通过本任务将学习和掌握使用ORDERBY对数据排序的方法。任务目标掌握使用ORDERBY对数据排序的方法掌握使用TOP...WITHTIES返回附加行的方法5.5.1使用ORDERBY实现数据排序（1/2）ORDERBY语法ORDERBY排序表达式[COLLATE排序名称][ASC|DESC][,...]排序表达式列名、列别名，或表示列在选择列表中位置的序号（非负整数）可指定多个排序列，以逗号分隔，按顺序定义排序优先级排序列可以不包含在选择列表中，但ntext、text、image类型不能用于排序排序方向ASC：升序（默认），从小到大DESC：降序，从大到小空值被视为最低可能值排序规则可使用COLLATE子句指定排序规则，覆盖列默认规则5.5.1使用ORDERBY实现数据排序（2/2）例5.18：按成绩降序、姓名升序显示“平面设计”课程成绩SELECTst.学号,姓名,课程名称,成绩FROM成绩ASscINNERJOIN学生ASstONst.学号=sc.学号INNERJOIN课程AScoONco.课程编号=sc.课程编号WHEREco.课程名称='平面设计'ORDERBYsc.成绩DESC,st.姓名;5.5.2使用TOP...WITHTIES返回附加行（1/3）TOP子句回顾TOP(n)[PERCENT]：返回前n行或前n%行与ORDERBY配合时，返回排序后的前n行WITHTIES选项功能：在TOP返回的结果基础上，

包含与最后一行ORDERBY列值相同的所有行必须与TOP一起使用，且必须配合ORDERBY适用于需要保留并列结果的应用场景5.5.2使用TOP...WITHTIES返回附加行（2/3）例5.19：“信息技术基础”课程成绩前三名，并包含并列第三名仅TOP3SELECTTOP3st.学号,姓名,课程名称,sc.成绩FROM成绩ASscINNERJOIN学生ASstONst.学号=sc.学号INNERJOIN课程AScoONco.课程编号=sc.课程编号WHEREco.课程名称='信息技术基础'ORDERBYsc.成绩DESC;→只返回前三行，若有并列第三名，只取一行5.5.2使用TOP...WITHTIES返回附加行（3/3）例5.19：“信息技术基础”课程成绩前三名，并包含并列第三名TOP3WITHTIESSELECTTOP3WITHTIESst.学号,姓名,课程名称,sc.成绩FROM成绩ASscINNERJOIN学生ASstONst.学号=sc.学号INNERJOIN课程AScoONco.课程编号=sc.课程编号WHEREco.课程名称='信息技术基础'ORDERBYsc.成绩DESC;→返回所有与第三名成绩相同的行（即并列第三名）使用GROUPBY对数据分组5.6任务5.6使用GROUPBY对数据分组在SELECT语句中使用GROUPBY子句可以将查询结果分成几组，通常是为了在每个组上执行一个或多个聚合函数，SELECT语句针对每个组返回一行。GROUPBY子句指定将结果集中的行分成若干个组来输出，每个组中的行在指定的列中具有相同的值。在一个查询语句中，可以使用多个列对结果集内的行进行分组，选择列表中的每个输出列必须在GROUPBY子句中出现或者用在某个聚合函数中。当使用GROUPBY子句时，还可以使用WHERE子句在分组操作之前对数据进行筛选，或者使用HAVING在分组操作之后对数据进行筛选。通过本任务将学习和掌握使用ROUPBY子句对结果集进行分组处理。任务目标理解GROUPBY子句的组成掌握在分组操作中应用搜索条件的方法掌握使用聚合函数汇总数据的方法掌握使用CUBE和ROLLUP汇总数据的方法5.6.1使用GROUPBY子句对查询结果分组GROUPBY语法

GROUPBY[ALL]分组表达式[,...][WITH{CUBE|ROLLUP}]将结果集按指定列分组，每组返回一行选择列表中非聚合表达式必须出现在GROUPBY中ALL：包含不满足WHERE条件的组（不能与CUBE/ROLLUP共用）例5.20：统计各班人数SELECT班级编号,COUNT(学号)AS班级人数FROM学生GROUPBY班级编号;5.6.2.在分组操作应用搜索条件WHERE与HAVING的区别WHERE：分组之前筛选行，不能包含聚合函数HAVING：分组之后筛选组，可以包含聚合函数例5.21：查询平均分>85的男生记录SELECTsc.学号,姓名,AVG(成绩)AS平均分FROM成绩scINNERJOIN学生stONst.学号=sc.学号WHEREst.性别='男'GROUPBYsc.学号,st.姓名HAVINGAVG(成绩)>85ORDERBYAVG(成绩)DESC;5.6.3使用聚合函数汇总数据常用聚合函数AVG：平均值，忽略NULLCOUNT：行数，COUNT(*)包括NULL行MAX/MIN：最大/最小值，忽略NULLSUM：总和，忽略NULLGROUPING：判断CUBE/ROLLUP生成的空值例5.22：各班成绩统计SELECTst.班级编号,COUNT(DISTINCTsc.学号)AS总人数,AVG(sc.成绩)AS平均分,MAX(sc.成绩)AS最高分,MIN(sc.成绩)AS最低分FROM成绩scINNERJOIN学生stONst.学号=sc.学号GROUPBYst.班级编号ORDERBYAVG(sc.成绩)DESC;5.6.4使用CUBE和ROLLUP汇总数据（1/2）WITHCUBE生成所有可能维度组合的汇总行结果集包含各维度值的所有组合的聚合汇总行对应列显示NULL，表示“所有值”例5.23：每个学生、每个班级的平均分及总平均分SELECT班级编号,st.学号,AVG(sc.成绩)AS平均分FROM成绩scINNERJOIN学生stONst.学号=sc.学号GROUPBYst.班级编号,st.学号WITHCUBE;生成四类组：(班级,学号)、(班级,ALL)、(ALL,学号)、(ALL,ALL)5.6.4使用CUBE和ROLLUP汇总数据（2/2）WITHROLLUP生成层次结构的汇总行按分组表达式顺序，从右向左逐级汇总适合生成小计和总计报表两者的区别ROLLUP生成层次结构，CUBE生成所有组合例5.24：每个学生、每个班级的平均分及班级总平均分SELECT班级编号,st.学号,AVG(sc.成绩)AS平均分FROM成绩scINNERJOIN学生stONst.学号=sc.学号GROUPBYst.班级编号,st.学号WITHROLLUP;按(班级编号,学号)顺序汇总，生成：具体学生→班级小计→总计操作查询结果集5.7任务5.7操作查询结果集在SQLServer中，可以对SELECT语句返回的结果集进行多种操作。例如，使用UNION将两个结果集组合在一起，通过公用表表达式来使用临时结果集，使用PIVOT对表值表达式进行操作以获得另一个表以及将结果集保存到表中等等。通过本任务将学习和掌握对查询结果集进行各种操作的方法。任务目标掌握使用UNION组合结果集的方法掌握公用表表达式的使用方法掌握PIVOT运算符的使用方法掌握使用表保存结果集的方法5.7.1使用UNION组合结果集语法

SELECT语句UNION[ALL]SELECT语句准则各SELECT语句选择列表中的表达式数目必须相同对应列的数据类型必须兼容（可隐式转换）结果集列名取自第一个SELECT语句ALL：包含重复行；省略则自动去重例5.25：检索信息工程系的教师和学生信息SELECT'教师'AS角色,姓名,性别,系部FROM教师WHERE系部='信息工程系'UNIONSELECT'学生',姓名,性别,系部FROM学生sINNERJOIN班级cONs.班级编号=c.班级编号WHERE系部='信息工程系';5.7.2.使用公用表表达式（1/2）公用表表达式（CTE）定义在查询执行范围内的临时结果集，不在数据库中存储可自引用，可在同一查询中引用多次语法WITH表达式名称[(列名称[,...])]AS(查询定义)SELECT...列名称列表可选（查询定义中列名唯一时）5.7.2.使用公用表表达式（2/2）例5.26：查询学生课程成绩（教师姓名、班级、学生姓名、课程、成绩）WITHCTE(教师编号,班级编号,学生姓名,课程名称,成绩)AS(SELECT教师编号,st.班级编号,st.姓名,co.课程名称,sc.成绩

FROM成绩scINNERJOIN学生stONst.学号=sc.学号

INNERJOIN课程coONco.课程编号=sc.课程编号

INNERJOIN教学安排sdONsd.课程编号=sc.课程编号ANDsd.班级编号=st.班级编号)SELECTt.姓名AS教师姓名,c.班级编号,c.学生姓名,c.课程名称,c.成绩FROMCTEcINNERJOIN教师tONt.教师编号=c.教师编号ORDERBYc.教师编号,c.成绩DESC;5.7.3使用PIVOT运算符（1/2）功能将行值转换为列，生成交叉表查询语法SELECT*FROM表源PIVOT(聚合函数(值列)FOR透视列IN(列值列表))表别名处理过程对除透视列和值列外的组合列进行GROUPBY为透视列中每个指定值生成一列对每个子组应用聚合函数计算值列5.7.3使用PIVOT运算符（2/2）例5.27：电子工程系学生课程成绩交叉表WITHcte(学号,姓名,课程,成绩)AS(SELECTst.学号,st.姓名,co.课程名称,sc.成绩

FROM成绩scINNERJOIN学生stONst.学号=sc.学号

INNERJOIN课程coONco.课程编号=sc.课程编号

INNERJOIN班级clONcl.班级编号=st.班级编号

WHEREcl.系部='电子工程系')SELECT*FROMctePIVOT(SUM(成绩)FOR课程IN([数学],[语文],[英语],[信息技术基础]))pvt;5.7.4将结果集保存到表中SELECTINTO语法

SELECT...INTO新表FROM...在默认文件组中创建新表，并将查询结果插入新表可创建为永久表或临时表（以#或##开头）需要拥有目标数据库的CREATETABLE权限例5.28：查询信息工程系学生信息并保存到新表SELECTs.*INTO信息工程系学生FROM学生sINNERJOIN班级cONs.班级编号=c.班级编号WHEREc.系部='信息工程系';SELECT*FROM信息工程系学生;--查看新表数据使用子查询5.8任务5.8使用子查询子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。子查询也称为内部查询或内部选择，包含子查询的语句称为外部查询或外部选择。任何允许使用表达式的地方都可以使用子查询，一个子查询也可以嵌套在另外一个子查询中。为了与外层查询有所区别，总是把子查询写在一对圆括号中。通过本任务将学习和掌握创建和应用子查询的方法。任务目标掌握使用子查询进行集成员测试的方法掌握使用子查询进行比较测试的方法掌握使用子查询进行存在性测试的方法掌握使用子查询替代表达式的