第09章数据库查询

1 173 数据库原理与应用教程 SQLServer 第9章数据库查询 2 173 所谓查询 就是对已经存在于数据库中的数据按特定的组合 条件或次序进行检索 查询功能是数据库最基本也是最重要的功能 3 173 9 1基本查询使用T SQL语言实现查询SELECT语句的语法如下 SELECTselect list INTOnew table name FROMtable list WHEREsearch conditions GROUPBYgroup by list HAVINGsearch conditions ORDERBYorder list ASC DESC 4 173 9 1基本查询其中各参数的含义如下 select list描述结果集的列 它指定了结果集中要包含的列的名称 是一个逗号分隔的表达式列表 INTOnew table name指定使用结果集来创建新表 new table name指定新表的名称 FROMtable list指定要从中检索数据的表名或视图名 5 173 WHEREsearch conditionsWHERE子句是一个筛选条件 它定义了源表中的行要满足SELECT语句的要求所必须达到的条件 GROUPBYgroup by listGROUPBY子句根据group by list列中的值将结果集分成组 6 173 HAVINGsearch conditionsHAVING子句是应用于结果集的附加筛选 用来向使用GROUPBY子句的查询中添加数据过滤准则 ORDERBYorder list ASC DESC ORDERBY子句定义了结果集中行的排序顺序 7 173 9 1 1简单查询1 投影查询通过SELECT语句的项组成结果表的列 选择列的查询格式 SELECT ALL DISTINCT TOPn PERCENT WITHTIES 选择当前表或视图的所有列 table name view name table alias 选择指定的表或视图的所有列 colume name expression IDENTITYCOL ROWGUIDCOL AS column alias 选择指定的列 column alias expression 选择指定列并更改列标题 n 8 173 1 投影查询参数含义 ALL 指定显示所有记录 包括重复行 ALL是默认设置 DISTINCT 指定显示所有记录 但不包括重复行 TOPn PERCENT 指定从查询结果中返回前n行 9 173 select list 指定返回结果中的列 如果有多个列 用逗号分隔 表示所有列 column name 指定是要返回的列名 expression 是列名 常量 函数以及由运算符连接的列名 常量和函数的任意组合 或者是子查询 10 173 1 选择一个表中指定的列使用SELECT语句选择一个表中的某些列 各列名之间要以逗号分隔 例9 1 查询 教学库 数据库 学生 表的姓名 性别和专业USE教学库SELECT姓名 性别 专业FROM学生查询结果如图所示 11 173 例9 2 查询 教学库 数据库 课程 表中所有纪录USE教学库SELECT FROM课程用 表示表中所有的列 按用户创建表格时声明列的顺序来显示所有的列 12 173 例9 3 查询 教学库 数据库 学生 表的专业名称 去掉重复行 USE教学库SELECTDISTINCT专业FROM学生 13 173 例9 4 查询 教学库 数据库 课程 表前三行信息 USE教学库SELECTtop3 FROM课程查询结果如图所示 14 173 例9 5 查询 教学库 数据库 课程 表前50 USE教学库SELECTtop50percent FROM课程查询结果如图所示 15 173 2 修改查询结果中的列标题T SQL提供了在SELECT语句中操作列名的方法 用户可以根据实际需要对查询数据的列标题进行修改 或者为没有标题的列加上临时的标题 常用的方式 在列表达式后面给出列名 用 来连接列表达式 用AS关键字来连接列表达式和指定的列名 注意列别名的使用范围 列别名只在定义的语句中有效 16 173 例9 6 查询 学生 表中同学的学号 姓名 结果中各列的标题分别指定为number name USE教学库SELECT学号ASnumber 姓名ASnameFROM学生 17 173 或 USE教学库SELECTnumber 学生号 name 姓名FROM学生 18 173 或 USE教学库SELECT学生号number 姓名nameFROM学生 19 173 3 计算列值在进行数据查询时 经常需要对查询到的数据进行再次计算处理 T SQL允许直接在SELECT语句中使用计算列 计算列并不存在于表格所存储的数据中 它是通过对某些列的数据进行演算得来的结果 20 173 例9 7 查询 教学库 数据库 课程 表 按120分计算成绩显示 USE教学库SELECT学生号 课程号 成绩150 成绩 1 50FROM选课查询结果如图所示 21 173 2 选择查询一般查询都不是针对全表所有行的查询 只是从整个表中选出满足指定条件的内容 这就要用到WHERE子句 选择行的基本语法 SELECTSELECT LISTFROMTABLE LISTWHERESEARCH CONDITIONS 22 173 2 选择查询其中 SEARCH CONDITIONS为选择查询结果的条件 SQLServer支持比较 范围 列表 字符串匹配等选择方法 WHERE子句中的条件表达式包括关系表达式和逻辑表达式两种 SQLServer对WHERE子句中的查询条件的数目没有限制 23 173 2 选择查询 使用关系表达式比较运算符用于比较两个表达式值 共有9个 分别是 等于 大于 大于等于 不等于 不等于 不大于 24 173 2 选择查询比较运算的格式为 expression expression其中expression是除text ntext和image外类型的表达式 25 173 例9 8 查询 教学库 数据库 选课 表中成绩大于等于60的学生的学号 课程号和成绩 USE教学库SELECT FROM选课WHERE成绩 60 26 173 2 选择查询 使用逻辑表达式NOT 非 对表达式的否定 AND 与 连接多个条件 所有的条件都成立时为真 OR 或 连接多个条件 只要有一个条件成立就为真 27 173 例9 9 查询 教学库 数据库 学生 表中专业为 电子 且性别为 男 的学生信息 USE教学库SELECT FROM学生WHERE专业 电子 and性别 男 查询结果如图所示 28 173 29 173 例9 10 查询 教学库 数据库 学生 表中专业为 电子 或性别为 男 的学生信息 USE教学库SELECT FROM学生WHERE专业 电子 or性别 男 查询结果如图所示 30 173 31 173 2 选择查询 使用BETWEEN关键字使用BETWEEN关键字可以更方便地限制查询数据的范围 语法格式为 表达式 NOT BETWEEN表达式1AND表达式2使用BETWEEN表达式进行查询的效果完全可以用含有 和和 的逻辑表达式来代替 32 173 2 选择查询 使用IN关键字同BETWEEN关键字一样 IN的引入也是为了更方便地限制检索数据的范围语法格式为 表达式 NOT IN 表达式1 表达式2 表达式n 33 173 2 选择查询 使用like关键字LIKE关键字搜索与指定模式匹配的字符串 日期或时间值 模式包含要搜索的字符串 字符串中可包含4种通配符的任意组合 搜索条件中可用的通配符如下表所示 34 173 35 173 例9 14 通配符的示例LIKE AB 返回以 AB 开始的任意字符串 LIKE Ab 返回以 Ab 开始的任意字符串 LIKE abc 返回以 abc 结束的任意字符串 LIKE abc 返回包含 abc 的任意字符串 36 173 LIKE ab 返回以 ab 结束的三个字符的字符串 LIKE ACK 返回以 A C 或 K 开始的任意字符串 LIKE A T ing 返回四个字符的字符串 结尾是 ing 首字符的范围从A到T LIKE M c 返回以 M 开始且第二个字符不是 c 的任意长度的字符串 37 173 例9 15 从 学生 表中查询所有姓张的学生信息 程序如下 USE教学库SELECT FROM学生WHERE姓名like 张 查询结果如图所示 38 173 2 选择查询 ISNULL 是否为空 查询在WHERE子句中不能使用比较运算符对空值进行判断 只能使用空值表达式来判断某个表达式是否为空值 如下所示 表达式ISNULL或表达式ISNOTNULL 39 173 例9 16 从 学生 表中查询所有学生姓名字段不为空的的学生信息 USE教学库SELECT FROM学生WHERE姓名ISNOTNULL 40 173 2 选择查询 复合条件查询在WHERE子句中可以使用逻辑运算符把若干个搜索条件合并起来 组成复杂的复合搜索条件 这些逻辑运算符包括AND OR和NOT AND运算符表示只有在所有条件都为真时 才返回真 OR运算符表示只要有一个条件为真时 就可以返回真 NOT运算符取相反 当在一个WHERE子句中 同时包含多个逻辑运算符时 其优先级从高到低依次是NOT AND OR 41 173 例9 17 从 学生 表中查询所有计算机专业的女学生或电子专业的学生信息 USE教学库SELECT FROM学生WHERE性别 女 AND 专业 计算机 OR专业 电子 查询结果如图所示 42 173 43 173 3 聚合函数SQLServer提供了一系列聚合函数 这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录 通过使用这些函数可以实现数据集合的汇总或是求平均值等各种运算 Transact SQL提供聚合函数如下表所示 44 173 45 173 3 聚合函数在SELECT子句中可以使用聚合函数进行运算 运算结果作为新列出现在结果集中 在聚合运算的表达式中 可以包括列名 常量以及由算术运算符连接起来的函数 46 173 例9 18 在教学库中查询数据库 选课 表中成绩的平均值 平均值显示列标题为 平均成绩 USE教学库SELECTavg 成绩 as平均成绩FROM选课查询结果如图所示 47 173 48 173 例9 19 从数据库 教学库 的学生表中查询专业的种类 相同的按一种计算 USE教学库SELECTcount distinct专业 AS专业种类数FROM学生查询结果如图所示 49 173 50 173 3 聚合函数说明 在T SQL中 允许与统计函数如count sum 和avg 一起使用DISTINCT关键字来处理列或表达式中不同的值 在MicrosoftSQLServer2005系统中 一般情况下可以在3个地方使用聚合函数 即SELECT子句 COMPUTE子句和HAVING子句 51 173 9 1 2分组查询使用聚合函数返回的是所有行数据的统计结果 如果需要按某一列数据的值进行分类 在分类的基础上再进行查询 就要使用GROUPBY子句了 分组技术是指使用GROUPBY子句完成分组操作的技术 52 173 9 1 2分组查询GROUPBY子句的语法结构 GROUPBY ALL group by expression n WITH CUBE ROLLUP 其中的参数说明如下 ALL 包含所有的组和结果 甚至包含那些不满足WHERE子句指定搜索条件的组和结果 如果指定了ALL 组中不满足搜索条件的空值也将作为一个组 53 173 group by expression 执行分组的表达式 可以是列或引用列的非聚合表达式 CUBE 除了返回由GROUPBY子句指定的列外 还返回按组统计的行 返回的结果先按分组的第一个条件列排序显示 再按第二个条件列排序显示 以此类推统计行包括了GROUPBY子句指定的列的各种组合的数据统计 更改列分组的顺序会影响在结果集内生成的行数 ROLLUP选项只返回最高层的分组列即第一个分组列的统计数据 54 173 1 普通 简单 分组如果在GROUPBY子句中没有使用CUBE或ROLLUP关键字 那么表示这种分组技术是简单分组技术 例9 20 在教学库中对 学生 表中的记录按性别进行分组 查询相应的人数 USE教学库SELECT性别 count 性别 人数FROM学生GROUPBY性别查询结果如图所示 55 173 注意 指定GROUPBY时 选择列表中任意非聚合表达式内的所有列都应包含在GROUPBY列表中 不能使用别名列 或者GROUPBY表达式必须与选择列表表达式完全匹配 56 173 2 带HAVING子句的普通分组查询当完成数据结果的查询和统计后 可以使用HAVING关键字来对查询和统计的结果进行进一步的筛选 如果不使用GROUPBY子句 则HAVING的行为与WHERE子句一样 57 173 例9 21 在 选课 表中查询选修了两门及以上课程的学生学号和选课数 USE教学库SELECT学生号 COUNT 课程号 选修课程数FROM选课GROUPBY学生号HAVINGCOUNT 课程号 2查询结果如图所示 58 173 59 173 3 WITHCUBE的使用 CUBE指定在结果集内不仅包含由GROUPBY提供的行 还包含汇总行 GROUPBY汇总行针对每个可能的组和子组组合在结果集内返回 GROUPBY汇总行在结果中显示为NULL 但用来表示所有值 使用GROUPING函数可确定结果集内的空值是否为GROUPBY汇总值 结果集内的汇总行数取决于GROUPBY子句内包含的列数 GROUPBY子句中的每个操作数 列 绑定在分组NULL下 并且分组适用于所有其他操作数 列 由于CUBE返回每个可能的组和子组组合 因此不论在列分组时指定使用什么顺序 行数都相同 60 173 例9 22 在教学库中查询选课表 求被选修的各门课程的平均成绩和选修该课程的人数 USE教学库SELECT课程号 AVG 成绩 AS 平均成绩 COUNT 学生号 AS 选修人数 FROM选课GROUPBY课程号WITHCUBE查询结果如图所示 61 173 62 173 例9 23 在教学库中查询学生表 统计各专业男生 女生人数及学生总人数 标志汇总行 USE教学库SELECT专业 性别 COUNT AS 人数 FROM学生GROUPBY专业 性别WITHCUBE查询结果如图所示 63 173 64 173 4 WITHROLLUP的使用ROLLUP指定在结果集内不仅包含由GROUPBY提供的行 还包含汇总行 按层次结构顺序 从组内的最低级别到最高级别汇总组 组的层次结构取决于列分组时指定使用的顺序 更改列分组的顺序会影响在结果集内生成的行数 使用CUBE或ROLLUP时 不支持区分聚合 如AVG DISTINCTcolumn name COUNT DISTINCTcolumn name 和SUM DISTINCTcolumn name 65 173 例9 24 在教学库中查询选课表和学生表 统计每个专业每门课程的平均成绩 每门课程的总平均成绩和所有课程的总平均成绩 USE教学库SELECT课程号 专业 AVG 成绩 AS 平均成绩 FROM选课 学生WHERE学生 学生号 选课 学生号GROUPBY课程号 专业WITHROLLUP查询结果如图所示 66 173 67 173 9 1 3数据汇总当在SELECT子句中出现聚合函数时 结果集中的数据全是聚合值 没有明细值 这是使用SELECT子句计算聚合值的缺点 能否解决这种问题呢 答案是肯定的 解决问题的方法就是使用COMPUTE子句 在SELECT语句中使用COMPUTE子句时 查询结果由两个部分组成 前一部分就是未用COMPUTE子句时产生的结果集 后一部分只有一行 是由COMPUTE子句产生附加的汇总数据 出现在整个结果集的末尾COMPUTE子句有两种形式 一种形式是不带BY子句 另一种形式是带BY子句 68 173 9 1 3数据汇总COMPUTE子句的语法格式 COMPUTE 聚合函数名 expression n BYexpression n 说明 AVG COUNT MAX MIN SUM这些函数均会忽略NULL值 且DISTINCT选项不能在此使用expression 指定需要统计的列的名称 此列必须包含于SELECT列表中且不能用别名 COMPUTE子句中也不能使用TEXTNTEXT和IMAGE数据类型 BYexpression 在查询结果中生成分类统计的行 如果使用此选项则必须同时使用ORDERBY子句 expression是对应的ORDERBY子句中的order by expression的子集或全集 69 173 例9 25 在教学库中查找专业为电子的学生的学号 姓名 性别 并产生一个学生总人数行 USE教学库goSELECT学生号 姓名 性别FROM学生WHERE专业 电子 COMPUTECOUNT 学生号 查询结果如图所示 70 173 71 173 例9 26 在教学库中求每个同学的成绩总和USE教学库SELECT学生号课程号成绩FROM选课orderby学生号COMPUTEsum 成绩 by学生号查询结果如图所示 72 173 73 173 9 1 4连接查询以上的查询操作都是从一个表中检索数据 在实际应用中 经常需要同时从两个表或两个以上表中检索数据 并且每一个表中的数据往往作为一个单独的列出现在结果集中 实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术 或者说连接技术是指对两个表或两个以上表中数据执行乘积运算的技术 在MicrosoftSQLServer2005系统中 这种连接操作又可以细分为交叉连接 内连接 外连接等 74 173 1 内连接内连接把两个表中的数据连接生成第三个表 在这个第三个表中仅包含那些满足连接条件的数据行 在内连接中 使用INNERJOIN连接运算符 并且使用ON关键字指定连接条件 内连接是一种常用的连接方式 如果在JOIN关键字前面没有明确指定连接类型 那么默认的连接类型是内连接 75 173 内连接的语法格式如下 SELECT列FROM表1INNERJOIN表2ON连接条件或SELECT列FROM表1 表2WHERE连接条件连接条件格式为 76 173 例9 27 从数据库 教学库 中查询每个学生的姓名 课程号和成绩 USE教学库SELECT学生 姓名 选课 课程号 选课 成绩FROM学生INNERJOIN选课ON学生 学生号 选课 学生号也可以利用下面的程序来实现 UES教学库SELECT学生 姓名 选课 课程号 选课 成绩FROM学生 选课WHERE学生 学生号 选课 学生号 77 173 注意 在列名不同时 列名前可以不加表名 但有时也会加上表名 以增强可读性 78 173 例9 28 从数据库 教学库 中查询电子专业的学生所选课程的平均分 课程号 显示 平均分 use教学库SELECTb 课程号 avg b 成绩 as平均分FROM学生aINNERJOIN选课bONa 学生号 b 学生号anda 专业 电子 groupbyb 课程号查询结果如图所示 79 173 为了简化输入 可以在查询中使用别名 以缩写表名 在form子句中为表定义一个临时别名 在查询中引用 80 173 2 自连接连接操作不仅可以在不同的表上进行 而且在同一张表内可以进行自身连接 即将同一个表的不同行连接起来 自连接可以看作一张表的两个副本之间的连接 在自连接中 必须为表指定两个别名 使之在逻辑上成为两张表 81 173 例9 29 从数据库 教学库 中的 选课 表中查询选学C001课程的成绩高于0303001号学生成绩的所有学生的记录 并按成绩降序排列 use教学库SELECTa 学生号 a 课程号 a 成绩FROM选课aINNERJOIN选课bONa 课程号 C001 anda 成绩 b 成绩andb 学生号 0303001 andb 课程号 C001 Orderbya 成绩desc 82 173 或利用下面的程序代码实现 use教学库SELECTa 学生号 a 课程号 a 成绩FROM选课a 选课bwherea 课程号 C001 anda 成绩 b 成绩andb 学生号 0303001 andb 课程号 C001 Orderbya 成绩desc查询结果如图所示 83 173 84 173 3 外连接在外连接中 不仅包括那些满足条件的数据 而且某些表不满足条件的数据也会显示在结果集中 也就是说 外连接只限制其中一个表的数据行 而不限制另外一个表中的数据 这种连接形式在许多情况下是非常有用的 例如在连锁超市统计报表时 不仅要统计那些有销售量的超市和商品 而且还要统计那些没有销售量的超市和商品 需要注意的是 外连接只能用于两个表中 85 173 3 外连接 1 外连接的分类在MicrosoftSQLServer2005系统中 可以使用的3种外连接关键字 即LEFTOUTERJOIN RIGHTOUTERJOIN和FULLOUTERJOIN 左外连接是对连接条件中左边的表不加限制 右外连接是对右边的表不加限制 全外连接对两个表都不加限制 所有两个表中的行都会包括在结果集中 86 173 2 外连接的语法 左外连接SELECT列FROM表1LEFT OUTER JOIN表2ON表1 列1 表2 列2右外连接SELECTselect listFROM表1RIGHT OUTER JOIN表2ON表1 列1 表2 列2全外连接SELECTselect listFROM表1FULL OUTER JOIN表2ON表1 列1 表2 列2 87 173 例9 30 在教学库中查询每个学生及其选修课程的成绩情况 含未选课的学生信息 USE教学库GOSELECT学生 选课 课程号 选课 成绩FROM学生LEFTJOIN选课ON学生 学生号 选课 学生号查询结果如图所示 88 173 89 173 例9 31 在教学库中查询每个学生及其选修课程的情况 含未选课的学生信息及未被选修的课程信息 USE教学库SELECT课程 选课 成绩 学生 姓名 学生 学生号FROM课程fullJOIN选课ON课程 课程号 选课 课程号FULLJOIN学生ON学生 学生号 选课 学生号查询结果如图所示 90 173 91 173 例9 32 在教学库中查询成绩在75分以上的学生的学号 姓名 选修课的课程号 课程名 成绩 USE教学库SELECTC 课程号 C 课程名 A 学生号 A 姓名 B 成绩FROM学生ASAJOIN选课ASBONA 学生号 B 学生号ANDB 成绩 75JOIN课程asConB 课程号 C 课程号GO查询结果如图所示 92 173 93 173 4 交叉连接交叉连接也被称为笛卡尔乘积 返回两个表的乘积 在检索结果集中 包含了所连接的两个表中所有行的全部组合 例如 如果对A表和B表执行交叉连接 A表中有5行数据 B表中有12行数据 那么结果集中可以有60行数据 交叉连接使用CROSSJOIN关键字来创建 实际上 交叉连接的使用是比较少的 但是交叉连接是理解外连接和内连接的基础 94 173 交叉连接语法格式如下 SELECT列FROM表1CROSSJOIN表2 例9 33 在教学库中查询所有学生可能的选课情况 use教学库selecta b 课程号 b 成绩from学生aCROSSJOIN选课b查询结果如图所示 95 173 96 173 9 1 5子查询SELECT语句可以嵌套在其他许多语句中 这些语句包括SELECT INSERT UPDATE及DELETE等 这些嵌套的SELECT语句被称为子查询 当一个查询依赖于另外一个查询结果时 那么可以使用子查询 在某些查询中 查询语句比较复杂不容易理解 因此为了把这些复杂的查询语句分解成多个比较简单的查询语句形式时常使用子查询方式 使用子查询方式完成查询操作的技术是子查询技术 子查询可以分为无关自查询和相关子查询 97 173 1 无关子查询无关子查询的执行不依赖于外部查询 无关子查询在外部查询之前执行 然后返回数据供外部查询使用 无关子查询中不包含对于外部查询的任何引用 98 173 例9 34 在教学库中查询与 沈艳 在同一个专业学习的学生的学号 姓名和专业 USE教学库SELECT学生号 姓名 专业FROM学生WHERE专业 SELECT专业FROM学生WHERE姓名 沈艳 查询结果如图所示 99 173 100 173 1 无关子查询例9 34可以用自连接来实现 程序如下 USE教学库SELECTa 学生号 a 姓名 a 专业FROM学生a 学生bWHEREa 专业 b 专业ANDb 姓名 沈艳 101 173 1 无关子查询需要特别指出的是 子查询的SELECT语句不能使用ORDERBY子句 ORDERBY子句只能对最终查询结果排序 当用户能确切知道内层查询返回的是单值时 可以用 等比较运算符 102 173 例9 35 在教学库中查询选修了编号为 C001 的课程的学生姓名和所在专业 use教学库goSELECT姓名 专业FROM学生WHERE学生号IN SELECT学生号FROM选课WHERE课程号 C001 103 173 2 相关子查询在相关子查询中 子查询的执行依赖于外部查询 多数情况下是子查询的WHERE子句中引用了外部查询的表 相关子查询的执行过程与嵌套子查询完全不同 嵌套子查询中子查询只执行一次 而相关子查询中的子查询需要重复地执行 104 173 2 相关子查询相关子查询的执行过程 1 子查询为外部查询的每一行执行一次 外部查询将子查询引用的列的值传给子查询 2 如果子查询的任何行与其匹配 外部查询就返回结果行 3 再回到第一步 直到处理完外部表的每一行 105 173 2 相关子查询 使用子查询进行比较测试使用子查询进行比较测试时 通过等于 不等于 小于 小于或等于 等比较运算符 将一个表达式的值与子查询返回的单值进行比较 如果比较运算的结果为TRUE 则比较测试也返回TRUE 106 173 例9 36 在教学库中查询成绩比该课的平均成绩低的学生的学号 课程号 成绩 USE教学库GOSELECT学生号 课程号 成绩FROM选课aWHEREdegree SELECTavg 成绩 FROM选课bWHEREa 课程号 b 课程号 107 173 2 相关子查询 带有EXISTS的子查询使用子查询进行存在性测试时 通过逻辑运算符EXISTS或NOTEXISTS 检查子查询所返回的结果集是否有行存在 使用逻辑运算符EXISTS时 如果在子查询的结果集内包含有一行或多行 则存在性测试返回TRUE 如果该结果集内不包含任何行 则存在性测试返回FALSE 在EXISTS前面加上NOT时 将对存在性测试结果取反 带有EXISTS谓词的子查询不返回任何数据 只产生逻辑真值 TRUE 或逻辑假值 FALSE 108 173 例9 37 在教学库中查询所有选修了 E002 课程的学生姓名 分析 本查询涉及学生和选课关系 我们可以在学生表中依次取每个元组的学生号值 用此值去检查选课关系 若选课表中存在这样的元组 其学生号值等于学生表中学生号的值 并且其课程号 E002 则取此学生的姓名送入结果关系 109 173 USE教学库GOSELECT姓名FROM学生WHEREEXISTS SELECT FROM选课WHERE学生号 学生 学生号AND课程号 E002 110 173 2 相关子查询由EXISTS引出的子查询 其目标属性列表达式一般用 表示 因为带EXISTS的子查询只返回真值或假值 给出列名无实际意义 若内层子查询结果非空 则外层的WHERE子句条件为真 TRUE 否则为假 FALSE 111 173 9 1 6联合查询联合查询是指将两个或两个以上的SELECT语句通过UNION运算符连接起来的查询 联合查询可以将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询中所有查询的全部行 使用UNION组合两个查询的结果集的两个基本规则是 所有查询中的列数和列的顺序必须相同 数据类型必须兼容 112 173 9 1 6联合查询其语法格式如下 UNION ALL UNION ALL n 其中的参数说明如下 是参与查询的SELECT语句 ALL 在结果中包含所有的行 包括重复行 如果没有指定 则删除重复行 113 173 例9 38 查询选修了课程 C001 或者课程 E002 的学生的姓名 USE教学库GOSELECT姓名FROM选课 学生WHERE课程号 C001 and选课 学生号 学生 学生号UNIONSELECT姓名FROM选课 学生WHERE课程号 E002 and选课 学生号 学生 学生号 114 173 115 173 9 1 7对查询结果排序在使用SELECT语句时 排序是一种常见的操作 排序是指按照指定的列或其他表达式对结果集进行排列顺序的方式 SELECT语句中的ORDERBY子句负责完成排序操作 如果在SELECT中同时指定了TOP ORDERBY无效 116 173 9 1 7对查询结果排序其语法格式如下 ORDERBY order by expression ASC DESC n 其中的参数说明如下 order by expression 指定要排序的列 可以指定多个列 在ORDERBY子句中不能使用ntext text和image列 ASC表示升序 DESC表示降序 默认情况下是升序 117 173 例9 39 查询 教学库 数据库中 学生 表中女学生的姓名和专业 并按姓名升序排列 USE教学库SELECT姓名 专业FROM学生WHERE性别 女ORDERBY姓名ASC 118 173 例9 40 查询 选课 表中学生的成绩和学生号 并按成绩将序排列 USE教学库SELECT学生号 成绩FROM选课ORDERBY成绩DESC 119 173 例9 41 查询 选课 表中学生的成绩和学生号 并按成绩 降序 排列 若成绩相同按学生号 升序 排列 USE教学库SELECT学生号 成绩FROM选课ORDERBY成绩DESC 学生号ASC 120 173 例9 42 使用TOP关键字USE教学库SELECTTOP2课程名 学分FROM课程ORDERBY学分DESC 121 173 9 1 8存储查询结果通过在SELECT语句中使用INTO子句 可以创建一个新表并将查询结果中的行添加到该表中 用户在执行一个带有INTO子句的SELECT语句时 必须拥有在目标数据库上创建表的权限 SELECT INTO不能与COMPUTE子句一起使用 122 173 9 1 8存储查询结果1 SELECT INTO语句的使用语法格式如下 SELECTselect listINTOnew tableFROMtable source WHEREsearch condition 其中 new table为要新建的表的名称 新表中包含的列由SELECT子句中选择列表的内容来决定 新表中包含的行数则由WHERE子句指定的搜索条件来决定 123 173 例9 43 在教学库中将查询的学生姓名 学生号 选修课程名 成绩 专业的相关数据放在表 成绩单 中 USE教学库SELECT姓名 学生 学生号 课程名 成绩into成绩单from学生 选课 课程where学生 学生号 选课 学生号and课程 课程号 选课 课程号goselect from成绩单 124 173 125 173 2 INSERT语句中使用SELECT子句在INSERT语句中使用SELECT子句可以将一个或多个表或视图中的值添加到另一个表中 使用SELECT子句还可以同时插入多行 126 173 INSERT语句中使用SELECT子句的语法形式为 INSERT INTO table name column list SELECTselect listFROMtable name WHEREsearch condition 127 173 例9 44 在教学库中创建 选课 表的一个副本 成绩 表 将 选课 表中成绩大于80的数据添加到成绩表中 并显示表中内容 USE教学库CREATETABLE成绩 学生号char 7 课程号char 4 成绩INT goINSERTINTO成绩 学生号 课程号 成绩 SELECT FROM选课WHERE成绩 80 128 173 129 173 注意 不要把SELECT子查询写在圆括号中 INSERT语句中的列名列表应当放在圆括号中 而且不使用VALUES关键字 如果来源表与目标表结构完全相同 则可以省略INSERT语句中的列名列表 SELECT子查询中的列列表必须与INSERT语句中的列列表相匹配 如果没有在INSERT语句中给出列列表 SELECT子查询中的列列表必须与目标表中的列相匹配 130 173 9 2使用控制流语句Transact SQL提供称为控制流的特殊关键字 用于控制Transact SQL语句 语句块和存储过程的执行流 与所有的计算机编程语言一样 Transact SQL也提供了用于编写过程性代码的语法结构 可用来进行顺序 分支 循环 存储过程等程序设计 编写结构化的模块代码 从而提高编程语言的处理能力 131 173 9 2 1SET语句声明一个局部变量后 该变量将被初始化为NULL 使用SET语句将一个不是NULL的值赋给声明的变量 给变量赋值的SET语句返回单值 在初始化多个变量时 为每个局部变量使用单独的SET语句 其语法格式为 SET local variable expressionession 例9 45 声明变量 并用SET给变量赋值 DECLARE myvarchar 20 SET myvar Thisisatest SELECT myvar GO 132 173 9 2 2BEGIN END语句BEGIN END语句能够将多个Transact SQL语句组合成一个语句块 并将它们视为一个单元处理 其语法格式如下 BEGIN sql statement statement block END其中 参数 sql statement statement block 为任何有效的T SQL语句或以语句块定义的语句分组 133 173 9 2 3IF ELSE语句使用IF ELSE语句 可以有条件地执行语句 在程序中如果要对给定的条件进行判定 当条件为真或假时分别执行不同的T SQL语句 可用IF ELSE语句实现 语法格式如下 IFBoolean expressionession 条件表达式 可含有SELECT语句 sql statement statement block 条件表达式为真时执行 语句块使用BEGIN END ELSE sql statement statement block 条件表达式为假时执行 语句块使用BEGIN END 其中条件表达式的值必须是逻辑值 ELSE子句是可选的 如果条件表达式中含有SELECT语句 必须用圆括号将SELECT语句括起来 134 173 9 2 3IF ELSE语句 例9 46 如果 C001 号课的平均成绩高于80分 则显示 平均成绩还不错 否则显示 平均成绩一般 USE教学库GOIF SELECTAVG 成绩 FROM选课WHERE课程号 C001 80PRINT C001号课的平均成绩还不错 ELSEPRINT C001号课的平均成绩一般 本例执行结果为 C001号课的平均成绩还不错 135 173 9 2 3IF ELSE语句 例9 47 输出0101001号学生的平均成绩 如果该学生没有选课 则显示相应的提示信息 USE教学库GOIFEXISTS SELECT FROM选课WHERE学号 0101001 SELECTAVG 成绩 as 0101001号学生的平均分 FROM选课WHERE学号 0101001 ELSEPRINT 没有0101001号学生或0101001号学生没选课 136 173 9 2 4CASE语句使用CASE语句可以进行多个分支的选择 CASE具有以下两种格式 简单CASE格式 将某个表达式与一组简单表达式进行比较 以确定结果 搜索CASE格式 计算一组布尔表达式 以确定结果 1 简单CASE的语法格式 CASEinput expressionWHENwhen expressionTHENresult expression n ELSEelse result expression END 137 173 9 2 4CASE语句 2 搜索CASE的语法格式 CASEWHENBoolean expressionTHENresult expression n ELSEelse result expression END 138 173 9 2 4CASE语句其中的参数说明如下 input expression 是使用简单CASE格式时所计算的表达式 Input expression是任何有效的MicrosoftSQLServer表达式 WHENwhen expression 使用简单CASE格式时input expression所比较的简单表达式 When expression是任意有效的SQLServer表达式 Input expression和每个when expression的数据类型必须相同 或者是隐性转换 139 173 9 2 4CASE语句 THENresult expression当input expression when expression取值为TRUE 或者Boolean expression取值为TRUE时返回的表达式 resultexpression是任意有效的SQLServer表达式 n占位符 表明可以使用多个WHENwhen expressionTHENresult expression子句或WHENBoolean expressionTHENresult expression子句 140 173 9 2 4CASE语句 ELSEelse result expression 当比较运算取值不为TRUE时返回的表达式 如果省略此参数并且比较运算取值不为TRUE CASE将返回NULL值 Else result expression是任意有效的SQLServer表达式 Else result expression和所有result expression的数据类型必须相同 或者必须是隐性转换 WHENBoolean expression 使用CASE搜索格式时所计算的布尔表达式 Boolean expression是任意有效的布尔表达式 141 173 9 2 4CASE语句 例9 47 以简单CASE格式查询所有学生的专业情况 包括学生号 姓名和专业的英文名 USE教学库SELECT学生号 姓名 CASE专业WHEN 计算机 THEN computer WHEN 通信 THEN communication WHEN 电子 THEN electronic ENDAS专业FROM学生 142 173 9 2 4CASE语句 例9 48 以简单CASE格式查询所有学生的专业情况 包括学生号 姓名和专业的英文名 USE教学库SELECT学生号 姓名 CASE专业WHEN 计算机 THEN computer WHEN 通信 THEN communication WHEN 电子 THEN electronic ENDAS专业FROM学生 143 173 9 2 4CASE语句 例9 49 以搜索CASE格式查询所有学生的考试等级 包括学生号 课程号和成绩级别 a b c d e USE教学库SELECT学生号 课程号 CASEWHEN成绩 90then a WHEN成绩 80then b WHEN成绩 70then c WHEN成绩 60then d WHEN成绩 60then e ENDAS成绩级别FROM选课 144 173 9 2 5WHILE语句如果需要重复执行程序中的一部分语句 可使用WHILE循环语句实现 WHILE语句通过布尔表达式来设置一个条件 当这个条件成立时 重复执行一个语句或语句块 重复执行的部分称为循环体 可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行 145 173 9 2 5WHILE语句语法格式为 WHILEBoolean expressionession 条件表达式 sql statement1 statement block1 BREAK sql statement2 statement block2 CONTINUE sql statement3 statement block3 T SQL语句序列构成的循环体 其中 BREAK命令的功能是让程序跳出包含它的最内层循环 而CONTINUE命令可以让程序跳过CONTINUE之后的语句回到WHILE循环的第一行命令 146 173 9 2 5WHILE语句 例9 50 显示字符串 China 中每个字符和其ASCII值 DECLARE positionint stringchar 5 SET position 1SET string China WHILE position DATALENGTH string BEGINSELECTSUBSTRING string position 1 ASCII SUBSTRING string position 1 SET position position 1END 147 173 9 2 5WHILE语句 例9 51 求1到100的累加和 当和超过1000时停止累加 显示累加和以及累加到的位置 DECLARE iint aintSET i 1SET a 0WHILE i 1000BREAKSET i i 1ENDSELECT aAS a iAS i 148 173 9 2 6GOTO语句GOTO语句可以实现无条件的跳转 语法格式为 GOTOlable lable为要跳转到的语句标号 其中 标号是GOTO的目标 它仅标识了跳转的目标 标号不隔离其前后的语句 执行标号前面语句的用户将跳过标号并执行标号后的语句 除非标号前面的语句本身是控制流语句 如RETURN 这种情况才会发生 149 173 9 2 6GOTO语句 例9 52 用GOTO实现循环 求1到100的和 DECLARE sint iintSET i 0SET s 0my loop SET s s iSET i i 1IF i 100GOTOmy loopPRINT 1 2 100 CAST saschar 25 150 173 9 2 6GOTO语句 例9 53 输出108号学生的平均成绩 若该学生没选课 则显示相应的提示信息 用GOTO语句实