Microsoft Word - 第5章.doc

Transcription

1 第 5 章 数据的基本操作 通过第 4 章表的基本操作, 用户明确了创建表的目的是为了利用表存储和管理数据 本章将首先介绍关系运算的基础知识, 然后在第 4 章建立的如图 5-1 所示的 学生选课管理信息系统 的 student 数据库用户表的基础上讲述数据的基本操作 数据的操作主要包括数据库表中数据的增加 修改 删除和查询操作 查询是数据操作的重点, 是用户必须重点掌握的数据操作技术 图 5-1 数据库表结构图 5.1 关系运算 1.2 节介绍了关系数据库的相关概念, 关系数据库应用数学方法来处理数据库中的数据, 有的人把该处理过程称为关系操作, 更多的人则称为关系运算 关系运算是关系数据模型的理论基础, 由高度抽象的数学语言来表达, 这些语言与具体的数据库管理系统中实现的实际语言并不完全相同, 但它们能用于评估实际系统中查询语言的能力高低 关系运算包含关系代数和关系演算两个部分, 关系演算以离散数学中的谓词演算为基础, 此处限于篇幅, 重点讨论关系数据结构的形式化定义和关系代数两个基本内容

2 5.1.1 关系数据结构的形式化定义 关系模型的数据结构非常单一, 就是关系, 它由关系数据结构 关系操作集合和关系完整性约束三部分组成 关系模型中常用的关系操作有查询 (Query) 插入(Insert) 删除(Delete) 修改(Update) 操作 查询操作又可分为选择 (Select) 投影(Project) 连接(Join) 除(Divide) 并 (Union) 交(Intersection) 差(Except) 和笛卡儿积 (Cartesian Product) 等 关系模型的完整性规则是对关系的某种约束条件 有三类完整性约束 : 实体完整性 (Entity Integrity) 参照完整性(Referential Integrity) 和用户定义的完整性 (User-defined Integrity), 将在第 7 章详细介绍 如何理解关系? 从逻辑上看可以把关系理解为一张二维表, 表是用来保持数据库所要描述数据的逻辑数据结构, 而非物理结构 下面用集合代数来定义二维表的关系 定义 1 域 (Domain) 是一组具有相同数据类型的值的集合 例如, 学生性别域是 { 男, 女 }, 学生百分制成绩的域是 0~100 的整数集合 定义 2 给定一组域 D 1,D 2,,D n, 则 D 1,D 2,,D n 的笛卡儿积定义为 : D D D = {( d, d,, d ) d D, i = 1,2,, n } 1 2 n 1 2 n i i 其中, 每一个元素 (d 1,d 2,,d n ) 称为一个 n 元组 (n-tuple), 简称元组 (Tuple), 元素中每一个值 d i 称为一个分量 (Component) 例如, 给出两个域, 学生姓名域 D 1 ={ 张斌, 周红瑜 } 和专业名称域 D 2 ={ 软件工程, 信息管理, 经济管理 }, 则 D 1 和 D 2 的笛卡儿积为 : D D ={( 张斌, 软件工程 ),( 张斌, 信息管理 ),( 张斌, 经济管理 ),( 周红瑜, 软件工程 ), 1 2 ( 周红瑜, 信息管理 ),( 周红瑜, 经济管理 )} 表示学生姓名和专业名的所有可能组合 其中 ( 张斌, 软件工程 ) ( 周红瑜, 信息管理 ) 等都是元组 张斌 周红瑜 软件工程 经济管理等都是分量 若 D i (i=1,2,,n) 为有限集, 其基数 (Cardinal number) 为 m i (i=1,2,,n)( 基数是一个表中除属性行外的行的总数 ), 则 D 1 D 2 D n 的基数为 : n 1 2 n = i= 1 m m m m 上例中 D 1 D 2 一共有 2 3=6 个元组 定义 3 关系 (Relation) D 1 D 2 D n 的子集叫作在域 D 1,D 2,,D n 上的关系, 表示为 R(D 1,D 2,,D n ) R 表示关系的名字,n 为关系的目或度 (Degree), 当 n=1 时, 称该关系为单元关系或一元关系 (Unary relation); 当 n=2 时, 称该关系为二元关系 (Binary relation) 关系是笛卡儿积的有限子集, 关系也是二维表, 表的每行对应一个元组, 每列对应一个域 由于域可以相同, 如学生学号和身份证号有可能都为整数域, 为了加以区分, 必须为每列起一个名字, 称为属性 (Attribute) n 目关系必有 n 个属性 图 5-2 给出了用表描述关系的一般格式 值得注意的是, 基本关系具有以下 4 个特点 : (1) 关系 ( 表 ) 可以看成是由行和列交叉组成的二维表 同列具有相同的域, 即每一列中的各个分量属于同一数据类型 不同的列可以有相同的域 (2) 表中任意两行 ( 元组 ) 不能完全相同 i 133 第 5 章 数据的基本操作

3 数据库原理与技术 (SQL Server 2012) 134 图 5-2 用表描述关系的一般格式 (3) 行的顺序可以任意交换, 列的顺序也可任意交换 (4) 各分量必须是原子值, 即每一个分量不可再分解, 这是由于关系模式要求关系必须满足一定的规范条件, 这也是把规范化的关系称为范式的原因 通常数据库中不允许出现非原子分量, 即数据表中含有可再分解的 表, 俗称 表中表 现象 关系代数 关系代数是允许从给定关系集合中构造新关系的运算符全集, 它是一种抽象的查询语言, 是关系数据操纵语言 (DML) 的一种传统表达方式 关系代数以集合代数为基础发展而来, 以关系为运算对象, 其运算结果仍为关系 关系代数用到的运算符称为关系运算符, 包括传统集合运算符 针对数据库表进行操作的专用运算符以及算术比较和逻辑运算符, 如表 5-1 所示 对关系的每种运算都解决面向数据库的一个询问, 用数据库的术语简称为查询或检索 表 5-1 关系代数运算符 集合运算符专用关系运算符符号含义符号含义符号含义 并 (Union) σ 交 (Intersection) F 差 (Difference) A=B 广义笛卡儿积 (ECP) 逻辑运算符 选择 (Selection) F 连接 (Formula Join) 等值连接 (Equijoin) 半连接 (Semijoin) 比较运算符 π 投影 (Projection) AθB θ 连接 (Theta Join) 自然连接 (Natural Join) 除 (Division) 符号含义符号含义符号含义 逻辑非 (NOT) > 大于 < 小于 逻辑与 (AND) 大于或等于 小于或等于 逻辑或 (OR) = 等于 <> 不等于 1. 传统集合运算 传统集合运算把关系看成元组的集合, 其运算从 水平 方向即行的角度来进行 设关系 R 和关系 S 具有相同的度 n( 即两个关系都有 n 个属性 ), 且相应属性取自同一个域, 记 t 为元组变量, 定义并 交 差运算如下 :

4 1) 并 R S = { t t R t S}, 并运算是将两个关系中的所有元组构成一个新关系, 结果应该消除重复的元组 例 5.1 表 5-2(a) 和表 5-2(b) 所示的两个关系 : 开设 C 程序设计课程教师情况和 Java 程序设计课程教师情况, 执行并操作得到如表 5-2(c) 所示结果, 即开设计算机程序设计课程的教师情况 表 5-2(a) C_teachers 工 号 姓 名 性 别 所属系部 T107 何英 女 计算机系 T207 王宁 男 计算机系 T306 李杰 男 数学系 表 5-2(b) Java_teachers 工 号 姓 名 性 别 所属系部 T211 张学杰 男 信息工程系 T107 何英女计算机系 表 5-2(c) C_teachers Java_teachers 工 号 姓 名 性 别 所属系部 T107 何英 女 计算机系 T207 王宁 男 计算机系 T306 李杰 男 数学系 T211 张学杰 男 信息工程系 2) 交 R S = { t t R t S}, 交运算得到的关系由既属于 R 又属于 S 的元组组成 上例中的 C_teachers Java_teachers, 得到如表 5-3 所示结果, 即同时开设两门课程的教师情况 表 5-3 C_teachers Java_teachers 工号姓名性别所属系部 T107 何英女计算机系 3) 差 R S = { t t R t S}, 差运算得到的关系由属于 R 而不属于 S 的所有元组组成 在差运算中顺序非常重要, R S S R, 上例中 C_teachers -Java_teachers 表示只开设了 C 程序设计的教师情况, 而 Java_teachers - C_teachers 则表示只开设了 Java 程序设计的教师情况, 分别如表 5-4(a) (b) 所示 表 5-4(a) C_teachers - Java_teachers 工 号 姓 名 性 别 所属系部 T207 王宁 男 计算机系 T306 李杰 男 数学系 表 5-4(b) Java_teachers - C_teachers 工号姓名性别所属系部 T211 张学杰男信息工程系 135 第 5 章 数据的基本操作

5 数据库原理与技术 (SQL Server 2012) 136 4) 广义笛卡儿积 在 节中已给出了笛卡儿积的定义, 记作 : R S = { t t t R t S} r s r s 笛卡儿积运算得到的关系, 其度是 R 和 S 的度之和, 基数是 R 和 S 的基数之积 值得注意的是, 笛卡儿积运算得到的结果可能没有任何意义, 而且计算代价较大 有时也把笛卡儿积运算叫作交叉连接或非限制连接 例 5.2 以 学生 表 ( 见表 5-5) 和 专业 表 ( 见表 5-6) 为例说明笛卡儿积的运算过程, 两表产生的结果集如表 5-7 所示 学号 (SNO) 姓名 (Name) 表 5-5 学生 表 (Student) 性别 (Sex) 系部代码 (Dept_NO) 专业代码 (Spec_NO) 张斌男 周红瑜女 贾凌云男 向雪林女 学号 (SNO) 专业代码 (Spec_NO) 表 5-6 专业 表 (Specialty) 专业名称 (Spec) 系部代码 (Dept_NO) 0101 软件工程 信息管理 经济管理 会计 02 表 5-7 交叉连接的结果表 姓名性别系部代码专业代码 (Name)(Sex)(Dept_NO)(Spec_NO) 专业代码 专业名称 *(Sp_NO) (Spec) 系部代码 *(De_NO) 张斌男 软件工程 周红瑜女 软件工程 贾凌云男 软件工程 向雪林女 软件工程 张斌男 信息管理 周红瑜女 信息管理 贾凌云男 信息管理 向雪林女 信息管理 张斌男 经济管理 周红瑜女 经济管理 贾凌云男 经济管理 向雪林女 经济管理 张斌男 会计 周红瑜女 会计 贾凌云男 会计 向雪林女 会计 02

6 以上执行过程是 : 把 学生 表 ( 共有 5 个属性列 ) 中的每一条记录取出 ( 共有 4 条记录 ), 与 专业 表 ( 共有 3 个属性列 ) 中的第一条记录连接, 形成如表 5-7 所示的前 4 条记录 ; 同样地, 再取出 学生 表中的每一条记录, 与 专业 表中的第二条至第四条记录分别连接, 从而形成后 12 条记录, 一共形成了 4( 来自 学生 表 ) 4( 来自 专业 表 )=16 条记录, 即 16 个元组, 同时, 该笛卡儿积有 8 个属性列 =5( 来自 学生 表 )+3( 来自 专业 表 ) 在表 5-7 中加 * 的 专业代码 和 系部代码 列是为了区别来自表 5-5 和表 5-6 具有相同名称的列 关系也满足集合运算的若干定律, 设关系 R S 和 Q 具有相同的度 n, 且相应属性取自同一个域, 则有 : (1) 结合律 R S Q= ( R S) Q= R ( S Q) R S Q= ( R S) Q= R ( S Q) (2) 交换律 R S = S R; R S = S R (3) 分配律 R ( S Q) = ( R S) ( R Q) R ( S Q) = ( R S) ( R Q) (4) 吸收律 R ( S R) = R; R ( S R) = R (5) 关系的交可以用差来表示 R S = R ( R S) 2. 专用关系运算 专用关系运算包括选择 投影 连接 除等 1) 选择 σ ( R ) = { t F t R F ( t ) = 'true'}, 在关系 R 中选择符合条件 F 的元组, 也就是从关系 R 中选取使逻辑表达式 F 为真的元组,F 由逻辑运算符连接各种算术表达式组成 选择运算是根据某些条件对关系做水平分割, 目的是检索一个特定的列中一个给定值的元组或元组集合的所有可能信息 例如, 表 5-5 表 5-6 分别列出了学生和专业情况, 现在要查询经济管理专业 ( 已知专业代码为 0201) 的全体学生, 用关系代数表示为 σ (Student) Spec _ NO = '0201' 或 σ (Student) 5 = '0201', 其中下标 5 是 Spec_NO 的属性序号 结果如表 5-8 所示 学号 (SNO) 表 5-8 经济管理专业学生的查询结果 ( 选择运算 ) 姓名 (Name) 性别 (Sex) 系部代码 (Dept_NO) 专业代码 (Spec_NO) 贾凌云男 若要列出所有女同学的基本情况则表示为 σ (Student) Sex = ' 女 ' 或 σ (Student) 3 = ' 女 ', 其中下标 3 是 Sex 的属性序号 查询结果如表 5-9 所示 学号 (SNO) 姓名 (Name) 表 5-9 全体女生的查询结果 ( 选择运算 ) 性别 (Sex) 系部代码 (Dept_NO) 专业代码 (Spec_NO) 周红瑜女 向雪林女 第 5 章 数据的基本操作

7 数据库原理与技术 (SQL Server 2012) 138 2) 投影 π ( R ) = X { t ( X ) t R }, 其中 X={A 1,A 2,,A k } 是关系 R 属性的子集, 它是先删除在 X 中没有指明的列, 然后删除一些重复的元组 ( 因为取消了某些属性列后, 就可能出现重复行 ), 而得到的一个新的关系, 即运算结果中的一个元组 j 的记录是从关系 R 的元组 j 选择记录统计 t j (A 1 ),t j (A 2 ),,t j (A k ) 而形成的 投影运算是对一个关系做垂直分割, 目的是研究某个特定列或者某几个列存在的不同值 例如在表 5-5 中查询学生学号和姓名, 用关系代数表示为 π (Student) SNO,Name 或 π (Student) 1,2, 其中下标 1 和 2 分别是 SNO 和 Name 的属性序号 查询结果如表 5-10 所示 表 5-10 全体学生的学号 姓名的查询结果 ( 投影运算 ) 学号姓名 (SNO) (Name) 张斌 周红瑜 贾凌云 向雪林 若要列出所有系部代码, 则应查询表 5-6, 表示为 π (Specialty) Dept _ NO 或 π (Specialty) 3, 其中下标 3 是 Dept_NO 的属性序号 结果如表 5-11 所示 3) 连接 表 5-11 系部代码的查询结果 ( 投影运算 ) 系部代码 (Dept_NO) 连接运算将两个关系连在一起, 形成一个新的关系 通常连接也称为 θ 连接, 它是从两个关系的笛卡儿积中选取属性值满足某一条件的元组, 即从 R 和 S 的广义笛卡儿积 R S 中选取 (R 关系 ) 在 A 属性上的值与 (S 关系 ) 在 B 属性上的值满足比较关系 θ 的元组, 记为 : R S= t t t R t S t [ A] θt [ B] A θ B { } r s r s r s 其中,A 和 B 分别为 R 和 S 上度数相等且可比的属性组,θ 为比较运算符 θ 连接一般不直接被关系数据库厂商支持, 它可以被模拟成选择和投影运算 当 θ 为 = 时, 称该连接为等值连接, 它是从 R 和 S 的广义笛卡儿积 R S 中选取 A B 属性值相等的元组, 记为 : R S= t t t R t S t [ A] = t [ B] A= B { } r s r s r s 当要求等值连接得到的结果中去掉重复的属性列时, 就产生了一种特殊的等值连接, 叫自然连接 自然连接继承了 等值连接两个关系中进行比较的分量必须是相同的属性组 这个规则 一般的连接运算是从行的角度进行, 而自然连接还需要消除重复列, 同时从行和列的角度进行运算 自然连接记为 :

8 R S= { t t t R t S t [ B] = t [ B] } r s r s r s 此外, 还有一种 F 连接, 从 R 和 S 的广义笛卡儿积 R S 中选取属性间满足某一公式 F 的元组,F 是形如 F 1 F 2 F i F n 的公式, 每个 F i 等价于 Aθ B 的形式 图 5-3 描述了各种连接的层次关系 图 5-3 各种连接的层次关系 例 5.3 表 5-12~ 表 5-14 分别是 学生成绩 表 成绩等级 表和 选课情况 表 学号 (SNO) 表 5-12 学生成绩 表 (SCG) 课程号 (CNO) 成绩 (Grade) S001 Math01 88 S002 Math02 90 S201 Math01 78 S202 Eng18 69 S301 Math02 83 S302 Math01 64 成绩边界 (G) 表 5-13 成绩等级 表 (GL) 等级 (Level) 90 A 80 B 课程号 (CNO) 表 5-14 选课情况 表 (CS) 课程名称 (Cname) 选课学生学号 (SNO) 选课学生姓名 (Sname) Math01 高等数学 S001 张斌 Math02 离散数学 S002 周红瑜 Math01 高等数学 S201 贾凌云 Math02 离散数学 S301 黄丽 Math01 高等数学 S302 杨素梅 首先, 要求列出获得成绩等级的学生情况 ( 包括其成绩等级 所修课程等信息 ) 用 139 第 5 章 数据的基本操作

9 数据库原理与技术 (SQL Server 2012) 关系代数表示为 SCG GL, 这是一个 θ 连接运算, 等价于执行 σ Grade (SCG GL) 操 Grade G 作 结果如表 5-15 所示 G 140 学号 (SNO) 表 5-15 课程号 (CNO) 获得成绩等级的学生情况 (SCGL) 成绩 (Grade) 成绩边界 (G) 等级 (Level) S001 Math B S002 Math A S301 Math B 根据表 5-15, 列出与表 5-14 中相匹配的学生情况 ( 包括课程名称 学生姓名等信息 ) 用关系代数表示为 SCGL CS, 这是一个自然连接运算, 等价于执行 π ( (SCGL CS)) 或 结果如表 5-16 所示 学号 (SNO) SCGL.SNO,Sname,SCGL.CNO,Cname,Grade,G,Level σ SCGL.SNO= CS.SNO π ( (SCGL CS)) SCGL.SNO,Sname,SCGL.CNO,Cname,Grade,G,Level σ SCGL.CNO= CS.CNO 学生姓名 (Sname) 表 5-16 课程号 (CNO) 获得成绩等级的学生详细情况 课程名称 (Cname) 成绩 (Grade) 成绩边界 (G) 等级 (Level) S001 张斌 Math01 高等数学 B S002 周红瑜 Math02 离散数学 A S301 黄丽 Math02 离散数学 B 4) 除在介绍除运算之前先给出像集的定义 关系 R(X,Z) 中 X 和 Z 为属性组, 当 tx [ ] = x时, x 在 R 中的像集定义为 : Zx= {[ t Z] t R t[ X] = x} 表示 R 中属性组 X 上值为 x 的诸元组在属性组 Z 上分量的集合 例如表 5-17 所示的关系 R, 其中属性列 A 可以取三个值 { a1, a2, a 3}, 则有 :a 1 的像集为 {( b1, c5),( b4, c 5)},a 2 的像集为 {( b2, c1),( b6, c 2)},a 3 的像集为 {( b3, c 4)} 表 5-17 关系 R A B C a 1 b 1 c 5 a 2 b 2 c 1 a 1 b 4 c 5 a 3 b 3 c 4 a 2 b 6 c 2 由此给出除运算定义 : 关系 R(X,Y) 和 S(Y,Z), 其中 X Y Z 为属性组 R 中的 Y 与 S 中的 Y 必须出自同一个域, 则 RXY (, ) SYZ (, ) = QX ( ), 新关系 Q 是 R 满足下列条件的元组在 X 属性组上的投影, 即元组在 X 上分量值 x 的像集 Y x 包含 S 在 Y 上投影的集合 记作 :

10 R S = { tr[ X] tr R πy( S) Yx}, 其中 Y x 是 x 在 R 上的像集,x=t r [X] 除运算的定义很复杂, 用来回答这样的问题 : 一个表的哪些元组包含在另一个表的某特定列的所有值, 其具体计算过程是 : (1) H =π1,2,3,, r s( R) (2) J = ( H S) R (3) K =π1,2,3,, r s( J) (4) R S = H K 另给出关系 S 如表 5-18 所示 1 表 5-18 关系 S U B C u 1 b 1 c 5 u 2 b 4 c 5 显然 π, ( S ) = {( b 1, c BC 5),( b 4, c 5)}, 只有 a 1 的像集 ( BC, ) a 1 包含了 π BC, ( S ), 因此, R S = { a }, 如表 5-19 所示 表 5-19 R S 的结果 A a 1 例 5.4 给出 学生学习情况 表 ( 见表 5-20) 和 主干课程成绩等级 表 ( 见表 5-21), 查询主干课程成绩优秀 ( 等级 A) 的学生情况 ( 学号 姓名和专业 ) 学号 (SNO) 学生姓名 (Sname) 表 5-20 学生学习情况 表 (SCG) 专业名称 (Spec) 课程名称 (Cname) 成绩等级 (Glevel) S001 张小斌通信工程计算机网络 A S001 张小斌通信工程数理方程 B S001 张小斌通信工程数据结构 A S002 周念计算机科学与技术计算机网络 A S002 周念计算机科学与技术数据结构 A S301 黄丽丽计算机科学与技术数据结构 A S201 贾云飞电子信息工程计算机网络 B S201 贾云飞电子信息工程数理方程 A S201 贾云飞电子信息工程数据结构 A 课程名称 (Cname) 数据结构 计算机网络 表 5-21 主干课程成绩等级 表 (CL) 等级 (Level) 本例是一个典型的除运算例子, 即 SCG CL, 显然, 只有 张小斌 和 周念 两人 A A 141 第 5 章 数据的基本操作

11 数据库原理与技术 (SQL Server 2012) 满足修读主干课程及相应成绩等级的条件, 结果如表 5-22 所示 142 学号 (SNO) 表 5-22 主干课程成绩优秀的学生情况 ( SCG CL ) 学生姓名 (Sname) 专业名称 (Spec) S001 张小斌通信工程 S002 周念计算机科学 关系代数的等价变换规则 关系代数表达式的满足一些等价变换规则 设 E1 和 E2 是关系代数表达式,F 是连接条件,L 是属性集, 则有 : ( 1 ) 连接交换律 笛卡儿积交换律 : E 1 E 2 E 2 E 1, E1 E2 E2 E 1, E1 E2 E2 E1 (2) 投影串联 πl 1 ( πl 2 ( ( πln( E)) )) π L 1 ( E), 其中 L1,L2,,Ln 为属性集, 且 L1 L2 Ln (3) 选择串联 σf 1 ( σf 2 ( E)) σf 1 F 2 ( E), 其中 F1 F2= F2 F1, 则又有选择的交换律 : σf 1 ( σf 2 ( E)) σf 2 ( σf 1 ( E)) (4) 选择对集合并的分配律 : σf( E1 E2) σf( E1) σf( E2),E1 和 E2 具有相同的属性名, 或 E1 和 E2 表达的关系的属性有对应性 (5) 选择对集合差的分配律 : σf( E1 E2) σf( E1) σf( E2),E1 和 E2 的属性有对应性 (6) 投影对集合并的分配律 : πl( E1 E2) πl( E1) πl( E2),E1 和 E2 的属性有对应性 (7) 投影对笛卡儿积的分配律 : π L 1 L 2 ( E1 E2) π L 1 ( E1) πl 2 ( E2),L1 是 E1 的属性集,L2 是 E2 的属性集 (8) 选择和投影操作的交换律 : πl( σf( E)) σf( π L( E)),F 只涉及 L 中的属性, 若 F 涉及非 L 中的属性 L', 那么就有 πl( σf( E)) πl( σ F( πl L ( E)) ) (9) 选择对自然连接的分配律 : σ ( 1 2) ( 1) ( 2) F E E σf E σf E,F 只涉及 E1 和 E2 的公共属性 (10) 选择与连接操作的结合律 : ( 1 2 ) F E E E1 E2 σ F1 E1 E2 E1 E2 其余规则请读者总结, 此处不再详述 关系代数表达式应用实例 F σ, ( ) F F F2 F1 F2 并 差 笛卡儿积 投影和选择是关系代数最基本的操作, 构成了关系运算的最小完备集 已经证明关系代数 安全的关系演算 ( 对关系演算施加了安全约束条件 ) 在关系的表达和操作能力上是等价的 我们可以用关系代数表达式表示各种数据查询操作, 其执行的一般过程如图 5-4 所示 需要注意的是, 当查询涉及否定或全部 包含值时, 下述流程就不能完全胜任了, 要用到差运算或除运算

12 图 5-4 关系代数表达式的一般执行流程 例 5.5 设教学管理数据库中有三个关系, 带下画线的属性为主键 : 学生关系 Student(SNO,Sname,Age,Sex,Sdept) 课程关系 Course(CNO,Cname,Cdept) 学习关系 SC(SNO,CNO,Grade) (1) 查询电子工程系全体学生的学号 姓名和性别 属于单表查询, 关系代数表达式为 π SNO,Sname,Sex ( σ Sdept = ' 电子工程系 '(Student)) (2) 查询学习课程号为 Math02 的学生学号与姓名 属于两表连接查询, 关系代数表达式为 π ( ) SNO Sname σ CNO='Math02' Student SC ( ), (3) 查询选修课程名为 离散数学 的学生学号与姓名 属于三表连接查询, 关系代数表达式为 π ( ) SNO Sname σcname=' ' Student SC Course ( ), 离散数学 (4) 至少选修课程号为 Math01 和 Math02 的学生学号 涉及多条件查询, 关系代数表达式为 π SNO ( σ SC1.SNO = SC2.SNO SC1.CNO = 'Math01' SC2.CNO = 'Math02'(SC1 SC2)), 其中,SC1 和 SC2 是关系 SC 的别名, 这里 (SC1 SC2) 表示关系 SC 自身进行笛卡儿积运算, 而选择表达式则表示同一个学生既选修了 Math01 课程又选修了 Math02 课程 (5) 查询没有选修 Math02 课程的学生学号与姓名 这里用全体学生的学号与姓名集合同选修了 Math02 课程的学生学号与姓名集合进行差运算 关系代数表达式为 π ( ) ( ( ) SNO, Sname Student πsno, Sname σ CNO='Math02' Student SC ) (6) 查询选修了所有课程 ( 号 ) 的学生学号 先求学生选课情况 π (SC) SNO,CNO, 再求开设的全部课程 π (Course) CNO, 选修了全部课程的学生学号用除运算表示为 π SNO,CNO (SC) π CNO (Course) 除了能够正确写出符合查询要求的关系代数表达式外, 还应该考虑表达式的优化问题, 即系统应该以什么样的操作顺序, 才能兼顾时间 空间和效率三者 有一些优化策略, 比如, 在表达式中尽可能早地执行选择运算 ; 把笛卡儿积和其后的选择操作合并成 F 连接 143 第 5 章 数据的基本操作

13 数据库原理与技术 (SQL Server 2012) 144 运算 ; 应对在一个表达式中多次出现的某个子表达式预处理, 即预先计算好结果保存起来等等 例如上题中的 π ( ) SNO, Sname σ Cname=' 离散数学 ' Student SC Course, 可以利用选择对 ( ) 自然连接的分配律, 把选择运算移至关系 Course 前面, 得到 : π Student SC σ ( Course) ( ( )) SNO, Sname Cname=' 离散数学 ' 在每个操作后, 应做投影运算, 挑选完后操作中需要的属性, 去掉不用的属性值, 减少中间的数据量, 最终得到一个比较优化的表达式 : π ( ) ( ( ) ( ( ) SNO, Sname ( πsno, Sname Student πsno πsno, CNO SC πcno σ Cname=' 离散数学 ' Course ))) 5.2 单表查询 数据库存在的意义在于将数据组织在一起, 以方便查询 查询 的含义就是用来描述从数据库中获取数据和操纵数据的过程 本节主要涉及单个数据表中信息的查询问题 SQL 语言中最主要 最核心的部分是查询功能 查询语言用来对已经存在于数据库的数据按照特定的组合 条件表达式或者一定次序进行检索 其基本格式是由 SELECT 子句 FROM 子句和 WHERE 子句组成的 SQL 查询语句 : SELECT < 列名表 > FROM < 表或视图名 > WHERE < 查询限定条件 > 也就是说,SELECT 指定了要查看的列 ( 字段 ),FROM 指定这些数据的来源 ( 表或者视图 ),WHERE 则指定了要查询哪些记录 注意 : 在 T-SQL 语言中,SELECT 子句除了进行查询外, 其他的很多功能也都离不开 SELECT 子句, 例如, 创建视图是利用查询语句来完成的 ; 插入数据时, 在很多情况下是从另外一个表或者多个表中选择符合条件的数据 所以查询语句是掌握 T-SQL 语言的关键 完整的 SELECT 语句的基本语法格式 虽然 SELECT 语句的完整语法较复杂, 但是其主要的语法格式可归纳如下 : SELECT select_list [INTO new_table_name] FROM table_list [WHERE search_conditions] [GROUP BY group_by_expression] [HAVING search_conditions] [ORDER BY order_expression [ASC DESC]] 其中, 带有中括号的子句是可选择的, 大写的单词表示 SQL 的关键字, 而小写的单词或词组表示表或视图名称或给定的条件 以上语法格式的详细说明如下 : SELECT select_list 描述结果集的列, 它是一个由逗号分隔的表达式列表 每个表达

14 式通常是从中获取数据的源表或视图的列的引用, 但也可能是其他表达式, 例如常量或 T-SQL 函数 在选择列表中使用 * 表达式指定返回源表中的所有列 [INTO new_table_name] 用于指定使用结果集来创建一个新表,new_table_name 是新表的名称 FROM table_list 包含从中检索到结果集数据来创建的表的列表, 也就是结果集数据来源于哪些表或视图,FROM 子句还可包含连接的定义 [WHERE search_conditions] 中的 WHERE 子句是一个筛选, 它定义了源表中的行要满足 SELECT 语句的要求所必须达到的条件 只有符合条件的行才向结果集提供数据, 不符合条件的行中的数据不会被使用 GROUP BY group_by_expression 中 GROUP BY 子句根据 group_by_expression 列中的值将结果集分成组 HAVING search_conditions 中 HAVING 子句是应用于结果集的附加筛选 从逻辑上讲,HAVING 子句从中间结果集对行进行筛选, 这些中间结果集是用 SELECT 语句中的 FROM WHERE 或 GROUP BY 子句创建的 HAVING 子句通常与 GROUP BY 子句一起使用, 尽管 HAVING 子句前面不必有 GROUP BY 子句 ORDER BY order_expression [ASC DESC] 中 ORDER BY 子句定义结果集中的行排列的顺序 order_expression 指定组成排序列表的结果集的列 ASC 和 DESC 关键字用于指定行是按升序还是按降序排序 选择表中的若干列 选择表中的全部列或部分列就是表的投影运算 这种运算可以通过 SELECT 子句给出的字段列表来实现 字段列表中的列可以是表中的列, 也可以是表达式列 所谓表达式列, 就是多个列运算后产生的列或者是利用函数计算后所得的列 1. 输出表中的所有列 将表中的所有字段都在 结果 窗格中列出来, 可以有两种方法 : 一种是将所有的字段名在 SELECT 关键字后列出来 ; 另一种是在 SELECT 语句后使用一个 * 例 5.6 查询 学生 表中全体学生的记录 代码如下 : SELECT * FROM 学生 在查询编辑器中输入并执行上述代码, 将返回学生表中的全部列, 如图 5-5 所示 2. 输出表中部分列如果在 结果 窗格中列出表中的部分列, 可以将要显示的字段名在 SELECT 关键字后依次列出来, 列名与列名之间用英文逗号隔开, 字段的顺序可以根据需要来指定 例 5.7 查询全体教师的教师编号 姓名和职称信息 145 第 5 章 数据的基本操作

15 数据库原理与技术 (SQL Server 2012) 146 图 5-5 查询 学生 表的全部字段 代码如下 : SELECT 教师编号, 姓名, 职称 FROM 教师 在查询编辑器中输入并执行上述代码, 在 结果 窗格中将只有 教师编号 姓名 和 职称 三个字段, 如图 5-6 所示 图 5-6 查询全体教师的编号 姓名和职称

16 3. 为 查询结果 窗格内的列指定别名有时, 查询结果 窗格中的列不是表中现成的列, 而是通过表中的一个或多个列计算出来的, 这时, 需要为这个计算列指定一个列名, 同时该表达式将显示在字段列表中 格式如下 : SELCET 表达式 AS 列别名 FROM 数据源 例 5.8 查询 教师 表中全体教师的姓名及年龄 代码如下 : SELECT 姓名,YEAR(GETDATE())-YEAR( 出生日期 ) AS 年龄 FROM 教师 其中, YEAR(GETDATE())-YEAR( 出生日期 ) 是表达式, 其含义是取得系统当前日期中的年份减去 出生日期 字段中的年份, 就是教师的当前年龄 年龄 是表达式别名 将上述代码在查询编辑器中输入并执行, 返回结果如图 5-7 所示 图 5-7 带有别名的查询 选择表中的若干记录 选择表中的若干记录这就是表的选择运算 这种运算可以通过增加一些谓词 ( 例如 WHERE 子句 ) 等来实现 147 第 5 章 数据的基本操作

17 数据库原理与技术 (SQL Server 2012) 消除取值重复的行两个本来并不相同的记录, 当投影到指定的某些列上后, 可能变成相同的行 如果要去掉结果集中重复的行, 可以在字段列表前面加上 DISTINCT 关键字 例 5.9 查询选修了课程的学生学号 代码如下 : SELECT 学号 FROM 课程注册 上述代码执行结果如图 5-8 所示, 选课的学生号有重复, 共有 36 条记录 下面的代码就去掉了重复的学号, 仅有 7 条记录, 执行结果如图 5-9 所示 SELECT DISTINCT 学号 FROM 课程注册 图 5-8 未去掉重复学号的查询 2. 限制返回行数如果一个表中有上万条记录, 而用户只想查看记录的样式和内容, 这就没有必要显示全部的记录 如果要限制返回的行数, 可以在字段列表之前使用 TOP n 关键字, 则查询结果只显示表中前面的 n 条记录, 如果在字段列表之前使用 TOP n PERCENT 关键字, 则查询结果只显示前面 n% 条记录

18 图 5-9 去掉了重复学号的查询 例 5.10 查询 课程注册 表中的前三条记录的信息 代码如下 : SELECT TOP 3 * FROM 课程注册 在查询编辑器中输入并执行上述代码, 执行结果如图 5-10 所示 图 5-10 显示前三条记录 查询满足条件的元组 如果只希望得到表中满足特定条件的一些记录, 可以在查询语句中使用 WHERE 子句 第 5 章 数据的基本操作

19 数据库原理与技术 (SQL Server 2012) 使用 WHERE 子句的条件如表 5-23 所示 150 表 5-23 常用的查询条件查询条件运算符含义 比较 确定范围 = > < >= <=!= <>!>; NOT+ 上述运算符 BETWEEN AND NOT BETWEEN AND 比较大小 判断值是否在范围内 确定集合 IN NOT IN 判断值是否为列表中的值 字符匹配 LIKE NOT LIKE 判断值是否与指定的字符通配格式相符 空值 IS NULL NOT IS NULL 判断值是否为空 多重条件 AND OR NOT 用于多重条件判断 1) 比较大小 比较运算符是比较两个表达式大小的运算符, 各运算符的含义是 =( 等于 ) >( 大于 ) <( 小于 ) >=( 大于或等于 ) <=( 小于或等于 ) <>( 不等于 )!=( 不等于 )!<( 不小于 )!>( 不大于 ) 逻辑运算符 NOT 可以与比较运算符同用, 对条件求非 例 5.11 查询 课程注册 表成绩大于等于 50 分的记录 代码如下 : SELECT * FROM 课程注册 WHERE 成绩 >=50 将上述代码在查询编辑器中输入并执行, 结果如图 5-11 所示 图 5-11 查询成绩大于等于 50 分的记录

20 2) 确定范围范围运算符 BETWEEN AND 和 NOT BETWEEN AND 可以查找属性值在 ( 或不在 ) 指定的范围内的记录 其中,BETWEEN 后是范围的下限 ( 即低值 ),AND 后是范围的上限 ( 即高值 ) 语法格式如下: 列表达式 [NOT] BETWEEN 起始值 AND 终止值 例 5.12 查询出生日期在 年的学生姓名 学号和出生日期 代码如下 : SELECT 姓名, 学号, 出生日期 FROM 学生 WHERE year( 出生日期 ) BETWEEN 1992 AND 1995 上述代码的含义是 : 如果返回出生日期的年份大于等于 1992 且小于等于 1995, 则该记录会在 结果 窗格中显示 在查询编辑器中输入并执行上述代码, 执行结果如图 5-12 所示 图 5-12 范围查找 3) 确定集合确定集合运算符 IN 和 NOT IN 可以用来查找属性值属于 ( 或不属于 ) 指定集合的记录, 运算符的语法格式如下 : 列表达式 [NOT] IN( 列值 1, 列值 2, 列值 3, ) 151 第 5 章 数据的基本操作

21 数据库原理与技术 (SQL Server 2012) 152 例 5.13 查询计算机系 ( 系部代码是 01) 经济管理系( 系部代码是 02) 的班级名称与班级编号 代码如下 : SELECT 班级代码, 班级名称 FROM 班级 WHERE 系部代码 IN('01','02') 将上述代码在查询编辑器中输入并执行, 结果如图 5-13 所示 图 5-13 确定集合查询 4) 字符匹配在实际的应用中, 用户有时候不能给出精确的查询条件 因此, 经常需要根据一些不确定的信息来查询 T-SQL 语言提供了字符匹配运算符 LIKE 进行字符串的匹配运算, 实现这类模糊查询 其一般语法格式如下 : [NOT] LIKE '< 匹配串 >' [ESCAPE '< 换码字符 >'] 其含义是查找指定的属性列值与 < 匹配串 > 相匹配的记录 < 匹配串 > 可以是一个完整的字符串, 也可以含有通配符 % 和 _, 其中通配符包括如下四种 (1)%( 百分号 ), 代表任意长度的字符串 ( 长度可以是 0) 的字符串 例如,a%b 表示以 a 开头 以 b 结尾的任意长度的字符串 例如,acb adxyzb ab 等都满足该匹配串 (2)_( 下画线 ), 代表任意单个字符 例如,a_b 表示以 a 开头, 以 b 结尾的长度为 3

22 的任意字符串 如 afb 等 (3)[] 表示中括号里列出的任意一个字符 例如 A[BCDE], 表示第一个字符是 A, 第二个字符为 B C D E 中的任意一个 也可以是字符范围, 例如 A[B-E] 同 A[BCDE] 的含义相同 (4)[^] 表示不在中括号里列出的任意一个字符 例 5.14 查询 学生 表中姓 周 的学生的信息 代码如下 : SELECT * FROM 学生 WHERE 姓名 LIKE ' 周 %' 通配符字符串 ' 周 %' 的含义是第一个汉字是 周 的字符串 将上述代码在查询编辑器中输入并执行, 执行结果如图 5-14 所示 图 5-14 模糊查询 如果用户要查询的字符串本身就含有 % 或 _, 这时就需要使用 ESCAPE'< 换码字符 >' 短语对通配符进行转义了 例 5.15 有一门课程的名称是 Photoshop CC_2014, 查询它的课程号和课程名 代码如下 : 153 第 5 章 数据的基本操作

23 数据库原理与技术 (SQL Server 2012) 154 INSERT INTO 课程 ( 课程号, 课程名, 学分 ) VALUES('0008','Photoshop CC_2014','4') SELECT 课程号, 课程名 FROM 课程 WHERE 课程名 LIKE 'Photoshop CC/_2014' ESCAPE '/' ESCAPE'/' 短语表示 / 是换码字符, 这样匹配串中紧跟在 / 之后的字符 _ 不再具有通配符的含义, 转义为普通的 _ 字符 本例中的 INSERT 语句是向 课程 表插入一条新记录, 以便完成后面的查找任务 INSERT 语句将在后续章节中详细讲解 将上述代码在查询编辑器中输入并执行, 结果如图 5-15 所示 图 5-15 使用 ESCAPE'< 换码字符 >' 短语对通配符 _ 进行转义 5) 涉及空值的查询一般情况下, 表的每一列都有其存在的意义, 但有时某些列可能暂时没有确定的值, 这时用户可以不输入该列的值, 那么这列的值为 NULL NULL 与 0 或空格是不一样的 空值运算符 IS NULL 用来判断指定的列值是否为空 语法格式如下 : 列表达式 [NOT] IS NULL 例 5.16 查询 教师 表中备注字段为空的教师信息 代码如下 :

24 SELECT * FROM 教师 WHERE 备注 IS NULL 这里的 IS 运算符不能用 = 代替 将上述代码在查询编辑器中输入并执行, 执行结果如图 5-16 所示 图 5-16 查询空值 6) 多重条件查询用户可以使用逻辑运算符 AND OR NOT 连接多个查询条件, 实现多重条件查询 逻辑运算符使用格式如下 : [NOT] 逻辑表达式 AND OR [NOT] 逻辑表达式 例 5.17 查询 课程注册 表中课程号为 0001 成绩在 80~89 分 ( 不含 89 分 ) 的学生的学号 成绩 代码如下 : SELECT 学号, 成绩 FROM 课程注册 WHERE 课程号 ='0001' AND 成绩 >=80 AND 成绩 < 第 5 章 数据的基本操作

25 数据库原理与技术 (SQL Server 2012) 将上述代码在查询编辑器中输入并执行, 结果如图 5-17 所示 156 图 5-17 多重条件查询 对查询的结果排序 可以使用 ORDER BY 子句对查询结果按照一个或多个属性列的升序 (ASC) 或降序 (DESC) 排列, 默认为升序 如果不使用 ORDER BY 子句, 则结果集按照记录在表中的顺序排列 ORDER BY 子句的语法格式如下 : ORDER BY { 列名 [ASC DESC]}[, n] 当按多列排序时, 先按前面的列排序, 如果值相同再按后面的列排序 例 5.18 查询选修了 0001 号课程的学生的学号, 并按成绩降序排列 代码如下 : SELECT 学号, 成绩 FROM 课程注册 WHERE 课程号 ='0001' ORDER BY 成绩 DESC 将上述代码在查询编辑器中输入并执行, 结果如图 5-18 所示 例 5.19 查询全体学生信息, 查询结果按所在班级代码降序排列, 同一个班的按照学号升序排列

26 图 5-18 将查询结果降序排序 代码如下 : SELECT * FROM 学生 ORDER BY 班级代码 DESC, 学号 ASC 将上述代码在查询编辑器中输入并执行, 结果如图 5-19 所示 图 5-19 组合排序 157 第 5 章数据的基本操作

27 数据库原理与技术 (SQL Server 2012) 对数据进行统计 158 需要对结果集进行统计, 例如求和 平均值 最大值 最小值 个数等, 这些统计可以通过集合函数 COMPUTE 子句 GROUP BY 子句来实现 1. 使用集合函数为了进一步方便用户, 增强检索功能,SQL Server 提供了许多集合函数, 主要有 : (1)COUNT( [ DISTINCT ALL ] * ) 统计记录个数 (2)COUNT( [ DISTINCT ALL ] < 列名 > ) 统计一列中值的个数 (3)SUM( [ DISTINCT ALL ] < 列名 > ) 计算一列值的总和 ( 此列必须是数值型 ) (4)AVG( [ DISTINCT ALL ] < 列名 > ) 计算一列值的平均值 ( 此列必须是数值型 ) (5)MAX( [ DISTINCT ALL ] < 列名 > ) 求一列值中的最大值 (6)MIN( [ DISTINCT ALL ] < 列名 > ) 求一列值中的最小值 在 SELECT 子句中, 集合函数用来对结果集记录进行统计计算 DISTINCT 是去掉指定列中的重复信息的意思,ALL 是不取消重复, 默认是 ALL 例 5.20 查询 教师 表中的教师总数 代码如下 : SELECT COUNT(*) AS 教师总数 FROM 教师 将上述代码在查询编辑器中输入并执行, 结果如图 5-20 所示 图 5-20 统计记录总数 例 5.21 查询 课程注册 表中学生的成绩平均分 代码如下 :

28 SELECT AVG ( 成绩 ) AS 平均分 FROM 课程注册 将上述代码在查询编辑器中输入并执行, 结果如图 5-21 所示 图 5-21 求学生成绩的平均分 2. 对结果进行分组 GROUP BY 子句将查询结果集按某一列或多列值分组, 分组列值相等的为一组, 并对每一组进行统计 对查询结果集分组的目的是为了细化集合函数的作用对象 GROUP BY 子句的语法格式为 : GROUP BY 列名 [HAVING 筛选条件表达式 ] 其中 : BY 列名 是按列名指定的字段进行分组, 将该字段值相同的记录组成一组, 对每一组记录进行汇总计算并生成一条记录 HAVING 筛选条件表达式 表示对生成的组筛选后再对满足条件的组进行统计 SELECT 子句的列名必须是 GROUP BY 子句已有的列名或是计算列 例 5.22 查询 课程注册 表中课程选课人数 4 人以上的各个课程号和相应的选课人数 代码如下 : 159 第 5 章 数据的基本操作

29 数据库原理与技术 (SQL Server 2012) 160 SELECT 课程号,COUNT(*) AS 选课人数 FROM 课程注册 GROUP BY 课程号 HAVING COUNT(*)>=4 将上述代码在查询编辑器中输入并执行, 结果如图 5-22 所示 HAVING 与 WHERE 子句的区别在于作用的对象不同 HAVING 作用于组, 选择满足条件的组 ;WHERE 子句作用于表, 选择满足条件的记录 图 5-22 分组统计 3. 使用 COMPUTE 子句 COMPUTE 子句对查询结果集中的所有记录进行汇总统计, 并显示所有参加汇总记录的详细信息 使用语法格式如下 : COMPUTE 集合函数 [BY 列名 ] 其中 : 集合函数, 例如 SUM() AVG() COUNT() 等 BY 列名 按指定 列名 字段进行分组计算, 并显示被统计记录的详细信息 BY 选项必须与 ORDER BY 子句一起使用 COMPUTE BY 子句之前要使用 ORDER BY 子句, 原因是必须先按分类字段排序之后才能使用 COMPUTE BY 子句进行分类汇总 COMPUTE BY 与 GROUP BY 子句的区别在于 : 前者既显示统计记录又显示详细记录, 后者仅显示分组统计的汇总记录 注意 :SQL Server 2012 废弃了 COMPUTE 和 COMPUTE BY 功能, 此处不再举例赘述

30 5.2.6 用查询结果生成新表 在实际的应用系统中, 有时需要将查询结果保存成一个表, 这个功能可以通过 SELECT 语句中的 INTO 子句实现 INTO 子句语法格式如下 : INTO 新表名 其中 : 新表名是被创建的新表, 查询的结果集中的记录将添加到此表中 新表的字段由结果集中的字段列表决定 如果表名前加 # 则创建的表为临时表 用户必须拥有该数据库中建表的权限 INTO 子句不能与 COMPUTE 子句一起使用 例 5.23 创建 课程注册 表的一个副本 代码如下 : SELECT * INTO 课程注册副本 FROM 课程注册 SELECT * FROM 课程注册副本 将上述代码在查询编辑器中输入并执行, 结果如图 5-23 所示 图 5-23 生成新表 例 5.24 创建一个空的 教师 表的副本 代码如下 : 161 第 5 章 数据的基本操作

31 数据库原理与技术 (SQL Server 2012) 162 SELECT * INTO 教师副本 FROM 教师 WHERE 1=2 上述代码中 WHERE 子句的条件永远为 假, 所以不会在创建的表中添加记录 在查询编辑器中输入并执行上述代码, 用户可以查看到新建的表, 但表中没有添加任何记录, 如图 5-24 所示 图 5-24 创建教师空表副本 集合查询 集合操作主要包括并操作 UNION 交操作 INTERSECT 和差操作 EXPECT 参加集合操作的各结果的列数量和对应的数据类型必须相同 使用 UNION 语句可以将多个查询结果集合并为一个结果集, 也就是集合的合并操作 UNION 子句的语法格式如下 : SELECT 语句 {UNION SELECT 语句 }[, n]

32 其中 : 参加 UNION 操作的各结果集的列数必须相同, 对应的数据类型也必须相同 系统将自动去掉并集的重复记录, 如果要保留重复记录需使用 UNION ALL 操作符 最后结果集的列名来自第一个 SELECT 语句 例 5.25 查询 课程注册 表中选修了 0001 课程或者选修了 0002 课程的学生, 也就是选修了课程 0001 的学生集合与选修了课程 0002 的学生集合的并集, 且按课程号升序排列 代码如下 : SELECT * FROM 课程注册 WHERE 课程号 ='0001' UNION SELECT * FROM 课程注册 WHERE 课程号 ='0002' ORDER BY 课程号 ASC 将上述代码在查询编辑器中输入并执行, 可得到如图 5-25 所示的结果 图 5-25 查询结果的并操作 163 第 5 章数据的基本操作

33 数据库原理与技术 (SQL Server 2012) 164 使用 INTERSECT 语句和 EXCEPT 语句的语法结构与 UNION 相似, 此处不再赘述 例 5.26 查询 课程注册 表中既选修了 0001 课程又选修了 0002 课程的学生学号, 即选修了课程 0001 的学生集合与选修了课程 0002 的学生集合的交集, 结果按学号升序排列 图 5-26 显示了查询结果 代码如下 : SELECT 学号 FROM 课程注册 WHERE 课程号 ='0001' INTERSECT SELECT 学号 FROM 课程注册 WHERE 课程号 ='0002' ORDER BY 学号 ASC 图 5-26 查询结果的交操作 例 5.27 查询 课程注册 表中选修了 0001 课程而未选修 0005 课程的学生学号, 即选修了课程 0001 的学生集合与选修了课程 0005 的学生集合的差集, 结果按学号升序排列 如图 5-27 给出了查询结果 代码如下 : SELECT 学号 FROM 课程注册 WHERE 课程号 ='0001' EXCEPT

34 SELECT 学号 FROM 课程注册 WHERE 课程号 ='0005' ORDER BY 学号 ASC 图 5-27 查询结果的差操作 5.3 连接查询 5.2 节所述查询是单表查询 若一个查询同时涉及两个或两个以上的表, 则称为连接查询 连接查询是关系数据库中最主要的查询, 包括等值与非等值查询 自然连接查询 自身连接查询 外连接查询和复合条件连接查询等 交叉连接查询 交叉连接又称非限制连接, 也叫广义笛卡儿积, 交叉连接的执行过程已在 5.1 节做了深入讨论 现给出其语法格式 : SELECT 列表列名 FROM 表名 1 CROSS JOIN 表名 2 其中,CROSS JOIN 为交叉表连接关键字 例 5.28 使用示例中的 学生 表 专业 表, 实现交叉查询 代码如下 : SELECT 学号, 姓名, 性别, 学生. 系部代码, 学生. 专业代码, 专业. 专业代码, 专业名称, 专业. 系部代码 165 第 5 章 数据的基本操作

35 数据库原理与技术 (SQL Server 2012) FROM 学生 CROSS JOIN 专业 166 在查询分析器中输入并执行上述代码, 结果如图 5-28 所示 图 5-28 交叉连接的执行结果 在例 5.28 的查询语句中, 由于 学号 姓名 性别 和 专业名称 列在 学生 表 专业 表中是唯一的, 因此引用时可去掉表名前缀 而 系部代码 专业代码 在两个表中都出现了, 引用时必须加上表名前缀 注意 : 多表查询时, 如果要引用不同表中的同名属性, 则在属性名前加表名, 即用 表名. 属性名 的形式表示, 以便区分 等值与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词, 其一般格式为 : [< 表名 1>.]< 列名 1> < 比较运算符 > [< 表名 2>.]< 列名 2> 其中, 比较运算符主要是 = > < >= <=!=( 或 <>) 当比较运算符为 = 时, 称为等值连接 使用除等号外的其他运算符的称非等值连接 与比较运算符一起组成连接条件的列名称为连接字段 连接字段的类型必须是可比的, 但名字不必相同 在例 5.28 中, 如果使用等值连接, 其过程如下 : 把 学生 表中的每一条记录取出, 与 专业 表中的第一条记录比较, 如果 专业代码 列值相等 ( 连接条件 ), 则连接形成第一条记录, 否则不连接 ; 同样地, 再取出 学生 表中的每一条记录, 与 专业 表中

36 的第二条 第三条 第四条 比较, 若 专业代码 列值相等, 则分别连接 ; 否则不连接 这样的操作, 要进行到 专业 表中的全部记录都处理完毕为止 通过以上描述, 可得出结论 : 等值连接的过程类似于交叉连接, 不过, 它只将满足连接条件的记录连接到结果集中 其语法格式为 : SELECT 列表列名 FROM 表名 1 [INNER] JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名其中,INNER 是连接类型可选关键字, 表示内连接, 可以省略 ON 表名 1. 列名 = 表名 2. 列名 是等值连接的连接条件 例 5.29 用等值连接方法连接 学生 表和 专业 表, 观察通过 专业代码 连接后的结果与交叉连接的结果有何区别 代码如下 : SELECT 学号, 姓名, 性别, 学生. 系部代码, 学生. 专业代码, 专业. 专业代码, 专业名称, 专业. 系部代码 FROM 学生 INNER JOIN 专业 ON 学生. 专业代码 = 专业. 专业代码 在查询分析器中输入并执行上述代码, 结果如图 5-29 所示 图 5-29 等值连接的执行结果从结果中可以发现, 只有满足连接条件的记录才被连接到结果集中, 结果集是两个表的交集 在如图 5-29 所示的图中, 系部代码 专业代码 列有重复 在等值连接中, 把 167 第 5 章 数据的基本操作

37 数据库原理与技术 (SQL Server 2012) 168 目标列中重复的属性列删除, 称为自然连接 例 5.30 自然连接 学生 表和 专业 表 代码如下 : SELECT 学号, 姓名, 性别, 学生. 系部代码, 专业. 专业代码, 专业名称 FROM 学生 JOIN 专业 ON 学生. 专业代码 = 专业. 专业代码 在查询分析器中输入并执行上述代码, 结果如图 5-30 所示 例 5-30 中 系部代码 列和 专业代码 列在两表中都出现过, 只需引用一个即可, 但引用时必须加上相应的表名前缀 图 5-30 自然连接的执行结果 自身连接查询 连接操作既可在多表之间进行, 也可以是一个表与其自己进行连接, 称为表的自身连接 使用自身连接时, 必须为表指定两个别名, 以示区别 例 5.31 使用 教师任课 表, 查询至少为两个专业开设课程的教师编号和专业代码 代码如下 : SELECT first. 教师编号,second. 专业代码 FROM 教师任课 AS first JOIN 教师任课 AS second

38 ON first. 教师编号 =second. 教师编号 AND first. 专业代码!=second. 专业代码 在查询分析器中输入并执行上述代码, 结果如图 5-31 所示 图 5-31 自身连接的执行结果 外连接查询 外连接的结果集不但包含满足连接条件的行, 还包括相应表中的所有行, 也就是说, 即使某些行不满足连接条件, 但仍需要输出该行记录 外连接包括三种 : 左外连接 右外连接和完全外连接 1. 左外连接左外连接 (Left Outer Join) 是指结果表中除了包含满足连接条件的记录外, 还包含左表中不满足连接条件的记录 注意 : 左表中不满足条件的记录与右表记录连接时, 右表的相应列上填充 NULL 值 左外连接的语法格式为 : SELECT 列表列名 FROM 表名 1 LEFT [OUTER] JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名 其中,OUTER 关键字可省略 例 5.32 将 学生 表左外连接 成绩 表 代码如下 : 169 第 5 章 数据的基本操作

39 数据库原理与技术 (SQL Server 2012) 170 SELECT 学生. 学号, 学生. 姓名, 性别, 系部代码, 语文, 数学, 英语, 美术, 自然, 体育, 音乐 FROM 学生 LEFT OUTER JOIN 成绩 ON 学生. 学号 = 成绩. 学号 在查询分析器中输入并执行上述代码, 结果如图 5-32 所示 其含义是 : 以 学生 表为主体列出每个学生的基本情况及其课程成绩, 而无论该生是否有该门课程的成绩, 若没有该门课成绩, 则在相应位置填空值 (Null), 这就避免了在连接时舍弃成绩全为空的学生基本信息, 即保留图中贾凌云 周红瑜 李晨 周春梅 张雪琪 李艾一 6 名学生的基本信息 图 5-32 左外连接的执行结果 2. 右外连接右外连接 (Right Outer Join) 是指结果表中除了包含满足连接条件的记录外, 还包含右表中不满足连接条件的记录 注意 : 右表中不满足条件的记录与左表记录连接时, 左表的相应列上填充 NULL 值 右外连接的语法格式为 : SELECT 列表列名 FROM 表名 1 RIGHT [OUTER] JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名其中,OUTER 关键字可省略 例 5.33 将 学生 表右外连接 成绩 表 代码如下 :

40 SELECT 学生. 学号, 学生. 姓名, 性别, 系部代码, 语文, 数学, 英语, 美术, 自然, 体育, 音乐 FROM 学生 RIGHT OUTER JOIN 成绩 ON 学生. 学号 = 成绩. 学号 输入并执行上述代码的结果如图 5-33 所示 我们发现在连接时舍弃了成绩全为空的学生基本信息, 即删除了图 5-32 中贾凌云 周红瑜 李晨 周春梅 张雪琪 李艾一的基本信息, 表明该右外连接是以 成绩 表为主体列出每个学生的基本情况及其课程成绩的 图 5-33 右外连接的执行结果 3. 完全外连接同理, 完全外连接 (Full Outer Join) 是指结果表中除了包含满足连接条件的记录外, 还包含两个表中不满足连接条件的记录 注意 : 左 ( 右 ) 表中不满足条件的记录与右 ( 左 ) 表记录连接时, 右 ( 左 ) 表的相应列上填充 NULL 值 完全外连接的语法格式为 : SELECT 列表列名 FROM 表名 1 FULL [OUTER] JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名 其中,OUTER 关键字可省略 复合连接条件查询 以上各个连接查询中,ON 连接条件表达式只有一个条件, 允许 ON 连接表达式有多 171 第 5 章 数据的基本操作

41 数据库原理与技术 (SQL Server 2012) 172 个连接条件, 称为复合条件连接, 或多表连接 实际上, 在例 5-31 中已经给出了多表连接的应用 这里再举一例 例 5.34 使用 学生 表 课程 表和 课程注册 表, 查询成绩在 70~80 分 ( 含 70 分和 80 分 ) 的学生学号 姓名 专业代码, 选修课的课程号 课程名称以及对应的成绩 代码如下 : SELECT S. 学号,S. 姓名,S. 专业代码,C. 课程号,CN. 课程名,C. 成绩 FROM 学生 AS S JOIN 课程注册 AS C ON S. 学号 =C. 学号 AND C. 成绩 >=70 AND C. 成绩 <=80 JOIN 课程 AS CN ON C. 课程号 =CN. 课程号 在查询分析器中输入并执行上述代码, 结果如图 5-34 所示 图 5-34 复合连接条件的执行结果 用 WHERE 子句改写例 5.34, 可简化代码如下, 其执行结果与图 5-34 一致 : SELECT S. 学号,S. 姓名,S. 专业代码,C. 课程号,CN. 课程名,C. 成绩 FROM 学生 AS S, 课程注册 AS C, 课程 AS CN WHERE S. 学号 =C. 学号 AND C. 课程号 =CN. 课程号 AND C. 成绩 >=70 AND C. 成绩 <=80

42 5.4 子查询 SQL 语言作为一门超高级语言, 继承了其他计算机语言的主要特征, 例如, 将要讲述的嵌套查询就类似于程序语言中的循环嵌套 通常把一个 SELECT-FROM-WHERE 语句组称为一个查询块 将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语条件中的查询称为嵌套查询 (Nested Query) 例如: SELECT 姓名 FROM 学生 WHERE 学号 IN ( SELECT 学号 FROM 课程注册 WHERE 教师编号 =' ' ) 括号内的查询块作为括号外 WHERE 子句的条件嵌入 SQL 语句中 我们把括号内的查询块称为子查询或内层查询, 与之相对的概念就是父查询或外层查询, 即包含子查询的查询块 SQL 语言允许多层嵌套查询, 但需要注意的是, 子查询的 SELECT 语句中不能使用 ORDER BY 子句,ORDER BY 子句只能对最终查询结果进行排序, 也不能包括 COMPUTE 或 FOR BROWSE 子句 SQL Server 2012 对嵌套查询的求解顺序是先内后外 即每个子查询在上一级查询处理之前求解, 子查询的结果用于建立父查询的查找条件 有了嵌套查询, 可以用多个简单的查询构造复杂查询 ( 嵌套不能超过 32 层 ), 提高 SQL 语言的表达能力, 以这样的方式来构造查询程序, 层次清晰, 易于实现, 这正是 SQL 中 结构化 (structured) 的内涵所在 某些嵌套查询可用连接运算替代, 某些则不能 到底采用哪种方法, 用户可根据实际情况判断 带有 IN 谓词的子查询 在嵌套查询中, 子查询的结果通常是一个集合 IN 是嵌套查询中使用最频繁的谓词 其处理过程是 : 父查询通过 IN 谓词将父查询中的一个表达式与子查询返回的结果集进行比较, 如果表达式的值等于子查询结果集中的某个值, 父查询中的条件表达式返回真 (TRUE), 否则返回假 (FALSE) 还可以在 IN 前加上关键字 NOT, 其功能与 IN 相反 例 5.35 使用 学生 表 课程 表和 课程注册 表, 查询选修了课程名为 高等数学 或 计算机导论 的学生的学号和姓名 代码如下 : 173 第 5 章 数据的基本操作

43 数据库原理与技术 (SQL Server 2012) 174 SELECT 学号, 姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 课程注册 WHERE 课程号 IN (SELECT 课程号 FROM 课程 WHERE 课程名 =' 高等数学 ' OR 课程名 =' 计算机导论 ' ) ) 例 5.35 涉及三个属性 : 学号 姓名和课程名 学号和姓名存放在 学生 表中, 课程名存放在 课程 表中, 两个表通过 课程注册 表建立联系, 所以本例涉及三个关系 ( 如上面标号所示 ): (1) 在 课程 表中找到 高等数学 或 计算机导论 两课程的课程号, 结果为 0002 或 0003 (2) 在 课程注册 表中找出选修了 (1) 中课程的学生学号, 结果为 (3) 在 学生 表中取出 (2) 中的学号和对应的姓名 在查询分析器中输入并执行上述代码, 结果如图 5-35 所示 图 5-35 带有 IN 运算符的子查询的执行结果

44 例 5.35 同样可用连接查询实现, 代码如下, 执行结果与图 5-35 一致 : SELECT DISTINCT 学生. 学号, 姓名 FROM 学生, 课程注册, 课程 WHERE 学生. 学号 = 课程注册. 学号 AND 课程注册. 课程号 = 课程. 课程号 AND ( 课程名 =' 高等数学 ' OR 课程名 =' 计算机导论 ') 带有比较运算符的子查询 节示例中子查询的查询条件不依赖于父查询, 这类子查询称为不相关子查询 ; 反之, 则称为相关子查询 (Correlated Subquery) 父查询与子查询之间通过比较运算符连接, 便形成了带有比较运算符的子查询 其处理过程是 : 父查询通过诸如 = > < >= <=!= 或 <> 等比较运算符将父查询中的一个表达式与子查询返回的结果 ( 单值 ) 进行比较, 如果表达式的值与子查询结果相比为真, 那么, 父查询中的条件表达式返回真 (TRUE), 否则返回假 (FALSE) 要强调的是, 带有 IN 运算符的子查询返回的结果是集合, 而带有比较运算符的子查询返回的结果是单值, 而且用户在查询开始时就知晓 内层查询返回的是单值 这一事实 在书写带比较运算符的子查询时, 注意子查询一定要跟在比较运算符之后 特殊地, 若 IN 的子查询结果集为单值, 则 = 符号和 IN 可以互换, 如图 5-36 所示 图 5-36 查询与 王钢 ( 含王钢本人 ) 同在一个系的教师基本信息 175 第 5 章数据的基本操作

45 数据库原理与技术 (SQL Server 2012) 例 5.36 使用 教师 表, 查询与 王钢 同在一个系的教师基本信息 代码如下 : 176 SELECT 教师编号, 姓名, 性别, 学历, 职务, 职称 FROM 教师 WHERE 系部代码 = (SELECT 系部代码 FROM 教师 WHERE 姓名 =' 王钢 ' ) 在查询分析器中输入并执行上述代码, 结果如图 5-36 所示, 结果集中包括 王钢 本人的情况, 若要去掉 王钢 本人的情况, 则代码改写为图 5-37 即可 图 5-37 查询与 王钢 ( 不含王钢本人 ) 同在一个系的教师基本信息 例 5.37 找出每个学生超过他所修课程平均成绩的课程号 代码如下 : SELECT 学号, 课程号 FROM 课程注册 AS x WHERE 成绩 >= (SELECT AVG( 成绩 ) FROM 课程注册 AS y WHERE y. 学号 =x. 学号

46 ) 其中,x 和 y 都是课程注册表的别名 子查询是求解一个学生所有课程的平均成绩, 至于是哪名学生的平均成绩要看 x. 学号的值, 而该值是与父查询相关的, 这就是相关子查询 上述语句的执行过程是 : (1) 从外层查询中取出 课程注册 表的一个元组, 将其学号 ( 如取出学号 ) 的值传送给子查询, 即 : SELECT AVG( 成绩 ) FROM 课程注册 AS y WHERE y. 学号 =' ' (2) 执行子查询, 得到近似结果 78(( ) 5 78), 用该值代替子查询, 得到父查询, 即 : SELECT 学号, 课程号 FROM 课程注册 AS x WHERE 成绩 >=78 (3) 执行父查询, 得到 ( , 0001) /*0001 号课程成绩 87 分 */ ( , 0005) /*0004 号课程成绩 90 分 */ 接着, 父查询取出下一个元组, 重复上述 (1)~(3) 步, 直到外层所有元组处理完毕, 结果如图 5-38 所示 图 5-38 查询每个学生超过他所修课程平均成绩的课程号 177 第 5 章数据的基本操作

47 数据库原理与技术 (SQL Server 2012) 带有 ANY 或 ALL 谓词的子查询 178 子查询返回单值时可以使用比较运算符, 但返回多值时则使用 ANY 或 ALL 谓词, 还必须同时使用比较运算符, 其语义如表 5-24 所示 表 5-24 ANY 或 ALL 谓词与比较运算符连用的语义表 运算符 语 义 运算符 语 义 >ANY 大于子查询结果中的某个值 >ALL 大于子查询结果中的所有值 <ANY 小于子查询结果中的某个值 <ALL 小于子查询结果中的所有值 >=ANY 大于或等于子查询结果中的某个值 >=ALL 大于或等于子查询结果中的所有值 <=ANY 小于或等于子查询结果中的某个值 <=ALL 小于或等于子查询结果中的所有值 =ANY 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值!=ANY 或!=ALL 或不等于子查询结果中的某个值 <> ANY <>ALL 不等于子查询结果中的所有值 带有 ANY 或 ALL 谓词的子查询, 其处理过程是 : 父查询通过 ANY 或 ALL 谓词将父查询中的一个表达式与子查询返回结果集中的某个值进行比较, 如果表达式的值与子查询结果相比为真, 那么, 父查询中的条件表达式返回真 (TRUE), 否则返回假 (FALSE) ANY 或 ALL 谓词与聚集函数 IN 谓词的等价转换关系如表 5-25 所示 表 5-25 ANY 或 ALL 谓词与聚集函数 IN 谓词的等价关系 = <> 或!= < 或 <= > 或 >= ANY IN < 或 <=MAX > 或 >=MIN ALL NOT IN < 或 <=MIN > 或 >=MAX 例 5.38 使用 学生 表和 系部 表, 查询其他系中比 计算机系 某一学生年龄小的学生信息 代码如下 : SELECT 学号, 姓名, 性别, 出生日期, 系部代码 FROM 学生 WHERE 系部代码 <>(SELECT 系部代码 FROM 系部 WHERE 系部名称 = ' 计算机系 ' ) AND 出生日期 >ANY (SELECT 出生日期 FROM 学生 WHERE 系部代码 = (SELECT 系部代码 FROM 系部 WHERE 系部名称 =' 计算机系 ') )

48 ORDER BY 出生日期 在查询分析器中输入并执行上述代码, 结果如图 5-39 所示 注意 : 在例 5.38 中, 做 >ANY 运算的并不是学生年龄, 而是学生的出生日期 ( 年龄越小, 表示出生日期的数值越大 ), 因此, 用 >ANY 运算符 也可用聚集函数 YEAR() GETDATE() 先将出生日期计算转换为年龄 ( 若采用年龄参与比较运算, 则 >ANY 应改写为 <ANY ), 即 YEAR(GETDATE())-YEAR( 出生日期 ) /* 用系统当前日期中的年份减去学生出生日期中的年份, 得到学生年龄 */ 此外, 本例还用到比较运算符 = 的子查询, 通过 系部名称 查找对应的 系部代码 AND 前面的表达式是为了去除计算机系系内的学生信息 最后, 要求结果按出生日期升序排列 图 5-39 查询其他系中比 计算机系 某一学生年龄小的学生信息 将例 5.38 改为查询其他系中比 计算机系 所有学生年龄都小的学生信息 只需把 >ANY 修改为 >ALL 即可, 请读者自己实现代码并验证结果 例 5.38 使用 MAX() 函数来实现 : 先用子查询找出计算机系中学生的最大年龄 ( 年龄计算方法如前所述 ), 接着在父查询中查找所有非计算机系且年龄小于该 最大年龄 的学生信息 179 第 5 章 数据的基本操作

49 数据库原理与技术 (SQL Server 2012) 代码如下 : 180 SELECT 学号, 姓名, 性别, 出生日期, 系部代码 FROM 学生 WHERE 系部代码 <>(SELECT 系部代码 FROM 系部 WHERE 系部名称 = ' 计算机系 ' ) AND YEAR(GETDATE())-YEAR( 出生日期 ) < (SELECT MAX(YEAR(GETDATE())-YEAR( 出生日期 )) AS 年龄 From 学生 WHERE 系部代码 =(SELECT 系部代码 FROM 系部 WHERE 系部名称 =' 计算机系 ') ) 带有 EXISTS 谓词的子查询 EXISTS 是存在量词, 使用 EXISTS 谓词的子查询不返回任何数据, 此时, 若子查询结果非空 ( 即至少存在一条记录 ), 则父查询的 WHERE 子句返回真 (TRUE), 否则返回假 (FALSE) 由 EXISTS 引出的子查询, 其目标列通常都用 *, 原因在于该查询只返回逻辑值, 给出列名毫无意义 正是因为 EXISTS 的这个用途, 其查询效率不一定比不相关查询低, 有时是一种高效的查询方法 前面所讲的子查询, 其查询条件不依赖于父查询, 并且每个子查询都只执行一次, 我们称之为不相关子查询 与此相对的概念是相关子查询, 即查询条件依赖于父查询中的某个值, 鉴于这种相关性 (relativity), 必须反复求值, 供父查询使用 其处理过程是 : 取出父查询表中的第一条记录, 根据它与子查询相关的属性值处理子查询, 若子查询的 WHERE 子句返回真值, 则把该条记录放入结果表中 ; 然后再取父表的第二条记录 ; 重复以上过程, 直至父查询表全部处理完毕为止 与 EXISTS 运算符相对的是 NOT EXISTS, 使用 NOT EXISTS 后, 若子查询结果为空, 则父查询的 WHERE 子句返回真 (TRUE), 否则返回假 (FALSE) 例 5.39 用 EXISTS 谓词改写例 5.36, 即查询与 王钢 同在一个系的教师基本信息 代码如下 : SELECT 教师编号, 姓名, 性别, 学历, 职务, 职称 FROM 教师 AS T1 WHERE EXISTS

50 (SELECT * FROM 教师 AS T2 WHERE T2. 系部代码 =T1. 系部代码 AND T2. 姓名 =' 王钢 ' ) 在查询分析器中输入并执行上述代码, 结果与例 5.36 一致, 如图 5-40 所示 从本例中可以看出所有带 IN 谓词 比较运算符 ANY 或 ALL 谓词的子查询都能使用带 EXISTS 运算符的子查询等价替换 图 5-40 用 EXISTS 谓词改写例 5.36 例 5.40 使用 学生 表和 课程注册 表, 查询所有选修 计算机导论 课学生的学号和姓名 代码如下 : SELECT 学号, 姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 课程注册 WHERE 学号 = 学生. 学号 AND 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 =' 计算机导论 ' ) ) 181 第 5 章 数据的基本操作

51 数据库原理与技术 (SQL Server 2012) 在查询分析器中输入并执行上述代码, 结果如图 5-41 所示 182 图 5-41 用 EXISTS 谓词查询所有选修了 计算机导论 课的学生学号和姓名 例 5.41 查询选修了全部课程的学生学号和姓名 由于 SQL 语言中没有描述 全部 量词 (For all), 我们将该查询转译为 查询这样的学生 : 没有一门课程是他不选修的 代码如下 : SELECT 学号, 姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 课程注册 WHERE 学号 = 学生. 学号 AND 课程号 = 课程. 课程号 ) ) 在查询分析器中输入并执行上述代码, 结果如图 5-42 所示

52 图 5-42 用 NOT EXISTS 实现全称量词的查询 5.3 节 5.4 节涉及的运算符基本上是二元运算符, 即用这些运算符来 组合 两个或两个以上的关系 ( 即表 ) 学习这两节时, 要注意公共属性集合的问题 : 它是第一个关系与第二个关系 ( 与第三个关系,, 与第 n 个关系 ) 相联系的中间环节, 尽管这些公共属性可能在各个关系上具有不同的名称, 但是它们必须具有相同的域和含义, 只要掌握了它们的 内涵, 并结合 5.2 节简单查询的知识, 就能写出结构规范 运行高效的 SQL 多表查询语句 5.5 数据的添加 修改和删除 SQL Server 数据库的新表建好后, 表中并不包含任何记录, 要想实现数据的存储, 必须向表中添加数据 同样要实现表的良好管理, 则需要经常修改表中的数据 本节主要介绍数据的添加 修改和删除 在数据的基本操作中, 常用到 T-SQL 语句, 首先应掌握如表 5-26 所示的 SQL 语句的语法规则 表 5-26 SQL 语句的语法规则规则含义大写 T-SQL 关键字 斜体 T-SQL 语法中用户提供的参数 ( 竖线 ) 分隔括号或大括号内的语法项目 只能选择一个项目 [ ]( 中括号 ) 可选语法项目, 不必输入中括号 { }( 大括号 ) 必选语法项目, 不要输入大括号 [, n] [ n] 加粗 < 标签 >::= 表示前面的项可重复 n 次 每一项由英文逗号分隔 表示前面的项可重复 n 次 每一项由空格分隔 数据库名 表名 列名 索引名 存储过程 实用工具 数据类型名以及必须按所显示的原样输入的文本语法块的名称 此规则用于对可在语句中多个位置使用的过长语法或语法单元部分进行分组和标记 适合使用语法块的每个位置由括在尖括号内的标签表示 :< 标签 > 183 第 5 章 数据的基本操作

53 数据库原理与技术 (SQL Server 2012) 数据的添加 184 向表中添加数据可以使用 INSERT 语句 INSERT 语句的语法格式如下 : INSERT [INTO] table_name [column_list] VALUES (data_values) 其中, 各项参数的含义如下 : [INTO] 是一个可选关键字, 可以将它用在 INSERT 和目标表之间 table_name 是要添加数据的表名或 table 变量名称 [column_list] 是要添加数据的字段名称或字段列表, 必须用中括号将 column_list 括起来, 并且用逗号进行分隔 若没有指定字段列表, 则指全部字段 VALUES(data_values) 用于引入添加记录的字段值 必须与 column_list 相对应 也就是说, 每一个字段必须对应一个字段值, 且必须用小括号将字段值列表括起来 如果 VALUES 列表中的值与表中列的顺序不相同, 或者未包含表中所有列的值, 那么必须使用 column_list 明确地指定存储每个传入值的列 1. 最简单的 INSERT 语句 例 5.42 在结构如图 5-43 所示的 专业 表中添加一行记录 : 在计算机系部中添加一个电子商务专业 代码如下 : 图 5-43 专业 表结构 INSERT 专业 ( 专业代码, 专业名称, 系部代码 ) VALUES ('0103',' 电子商务 ','01') 在查询编辑器中输入上述代码, 单击 按钮, 运行结果如图 5-44 和图 5-45 所示 图 5-44 简单添加数据语句图 5-45 查看运行结果 注意 :VALUES 列表中的表达式的数量必须匹配列表中的列数, 表达式的数据类型应与列的数据类型相兼容

54 2. 省略清单的 INSERT 语句 例 5.43 在结构如图 5-46 所示的 班级 表中添加 15 级电子商务 001 班 代码如下 : INSERT 班级 VALUES (' ','15 级电子商务 001 班 ','0103','01',NULL) 在查询编辑器中输入上述代码并执行, 即可在 班级 表中增加如图 5-47 所示的值为 ' ','15 级电子商务 001 班 ', '0103', '01', NULL 的记录 注意 : 此种方法省略了字段清单, 用户必须按照这些列在表中定义的顺序提供每一个列的值, 建议在输入数据时最好使用列清单 图 5-46 班级 表结构图 5-47 执行添加语句后的结果 3. 省略 VALUES 清单的 INSERT 语句在 T-SQL 语言中, 有一种简单的插入多行的方法 这种方法是使用 SELECT 语句查询出的结果代替 VALUES 子句 这种方法的语法结构如下 : INSERT [INTO] table_name (column_name[, n]) SELECT column_name[, n] FROM table_name WHERE search_conditions 其中, 各项参数的含义如下 : (1)search_conditions 查询条件 (2)INSERT 表和 SELECT 表的结果集的列数 列序 数据类型必须一致 例 5.44 创建 课程 表的一个副本 课程 1 表, 将 课程 表的全部数据添加到 课程 1 表中 代码如下 : 185 第 5 章 数据的基本操作

55 数据库原理与技术 (SQL Server 2012) 186 CREATE table 课程 1 ( 课程号 char(4) NOT NULL, 课程名 char(20) NOT NULL, 学分 smallint NULL) INSERT INTO 课程 1 ( 课程号, 课程名, 学分 ) SELECT 课程号, 课程名, 学分 FROM 课程 将上述代码在查询编辑器中运行, 用户可以看到在 课程 1 中增加了 7 行数据, 如图 5-48 所示 图 5-48 增加多行数据语句执行结果 4. 向学生选课系统各表中添加数据根据需要, 向学生选课系统的各表中添加数据, 在查询编辑器中分别执行下列代码 (1) 向 系部 表中添加如图 5-49 所示的 4 条记录 代码如下 : INSERT 系部 ( 系部代码, 系部名称, 系主任 ) VALUES ('01',' 计算机系 ',' 徐才智 ') (a) 系部 表结构 图 5-49 (b) 系部 表中增加 4 条记录后的执行结果 表结构及增加 4 条记录后的执行结果

56 (2) 向 专业 表添加如图 5-50 所示的 8 条记录 代码如下 : INSERT 专业 ( 专业代码, 专业名称, 系部代码 ) VALUES ('0101',' 软件工程 ','01') (a) 专业 表结构 图 5-50 (b) 专业 表中增加 8 条记录后的执行结果 表结构及增加 8 条记录后的执行结果 (3) 向 班级 表添加如图 5-51 所示的 5 条记录 代码如下 : INSERT 班级 ( 班级代码, 班级名称, 专业代码, 系部代码, 备注 ) VALUES (' ','14 级软件工程 001 班 ','0101','01',NULL) 187 (a) 班级 表结构 图 5-51 (b) 班级 表中增加 5 条记录后的执行结果 表结构及增加 5 条记录后的执行结果 第 5 章 数据的基本操作

57 数据库原理与技术 (SQL Server 2012) (4) 向 学生 表添加如图 5-52 所示的 10 条数据记录 代码如下 : 188 INSERT 学生 VALUES (' ',' 张斌 ',' 男 ',' ',' ',' ', '01','0101') (a) 学生 表结构 (b) 学生 表中增加 10 条记录后的执行结果 图 5-52 表结构及增加 10 条记录后的执行结果 (5) 向 课程 表添加如图 5-53 所示的 7 条数据记录 代码如下 : INSERT 课程 ( 课程号, 课程名, 学分 ) VALUES ('0001',' 大学英语 ','6') (a) 课程 表结构 图 5-53 (b) 课程 表中增加 7 条记录后的执行结果 表结构及增加 7 条记录后的执行结果 (6) 向 教学计划 表添加如图 5-54 所示的 42 条数据记录 代码如下 :

58 INSERT 教学计划 ( 课程号, 专业代码, 专业学级, 课程类型, 开课学期, 学分 ) VALUES ('0001','0101','2014',' 公共必修 ','1','6') (a) 教学计划 表结构 (b) 教学计划 表中增加 42 条记录后的部分执行结果 图 5-54 表结构及增加 42 条记录后的执行结果 (7) 向 教师 表添加如图 5-55 所示的 5 条数据记录 代码如下 : INSERT 教师 ( 教师编号, 姓名, 性别, 出生日期, 学历, 职务, 职称, 系部代码, 专业, 备注 ) VALUES (' ',' 张学杰 ',' 男 ',' ',' 硕士 ',' 主任 ',' 教授 ','01', ' 计算机 ',NULL) (a) 教师 表结构 图 5-55 (b) 教师 表中增加 5 条记录后的执行结果 表结构及增加 5 条记录后的执行结果 189 第 5 章 数据的基本操作

59 数据库原理与技术 (SQL Server 2012) (8) 向 教师任课 表添加如图 5-56 所示的 42 条数据记录 代码如下 : 190 INSERT 教师任课 ( 教师编号, 课程号, 专业学级, 专业代码, 学年, 学期, 学生数 ) VALUES(' ','0002','2014','0101','2014',2,0) (a) 教师任课 表结构 (b) 教师任课 表中增加 42 条记录后的部分执行结果 图 5-56 表结构及增加 42 条记录后的执行结果 (9) 利用 学生 表 教师任课 表 教学计划 表向 课程注册 表添加如图 5-57 所示的 36 条数据记录 ( 注意, 若学生完成该门课程学习, 还需手动修改成绩 学分列的值或使用触发器来自动修改学分列的值, 具体内容参见 10.2 节 ) 代码如下: INSERT 课程注册 ( 学号, 课程号, 教师编号, 专业代码, 专业学级, 选课类型, 学期, 学年, 成绩, 学分 ) SELECT DISTINCT 学生. 学号, 教师任课. 课程号, 教师任课. 教师编号, 学生. 专业代码, 教师任课. 专业学级, 教学计划. 课程类型, 教师任课. 学期, 教师任课. 学年,0,0 FROM 学生, 教师任课, 教学计划 WHERE 教师任课. 专业学级 =YEAR( 学生. 入学时间 ) AND 教师任课. 专业代码 = 学生. 专业代码 AND 教师任课. 专业代码 = 教学计划. 专业代码 AND 教师任课. 课程号 = 教学计划. 课程号 AND 教师任课. 专业学级 = 教学计划. 专业学级

60 (a) 课程注册 表结构 (b) 课程注册 表中增加 36 条记录后的部分执行结果 图 5-57 表结构及增加 36 条记录后的执行结果 数据的修改 在数据输入过程中, 可能会出现输入错误, 或是因为时间变化而需要更新数据, 这都需要修改数据 修改表中的数据可以使用 SQL Server Management Studio 中的图形界面进行修改, 即右击某数据表图标, 在弹出的快捷菜单中选择 编辑前 200 行 命令, 在打开的 表数据窗口 中进行修改 这里主要介绍 T-SQL 的 UPDATE 语句实现修改的方法, UPDATE 的语法格式如下 : UPDATE table_name SET {column_name={expression DEFAULT NULL}}[, n] [FROM{<table_source>}[, n]] [WHERE<search_condition>] <table_source>::=table_name[[as]table_alias][ WITH(<table_hint>[, n])] 其中 : table_name 是需要更新的表的名称 SET 是指定要更新的列或变量名称的列表 column_name 是含有要更改数据的列的名称 {expression DEFAULT NULL) 是列值表达式 <table_source> 是修改数据来源表 注意 : 当没有 WHERE 子句指定修改条件时, 则表中所有记录的指定列都被修改 若修改的数据来自另一个表时, 则需要 FROM 子句语句指定一个表 例 5.45 将 教学计划 表中专业代码为 0101 的 开课学期 的值改为第二学期 代码如下 : UPDATE 教学计划 SET 开课学期 =2 191 第 5 章 数据的基本操作

61 数据库原理与技术 (SQL Server 2012) WHERE 专业代码 ='0101' 192 在查询编辑器中输入并执行上述代码后, 用户可以通过 SQL Server Management Studio 查看修改的结果, 这里如果没有使用 WHERE 子句, 则对表中所有记录的 开课学期 进行修改 例 5.46 更新 课程注册 表中学生成绩及格的课程的 学分 值 代码如下 : UPDATE 课程注册 SET 学分 =(SELECT 学分 FROM 课程 WHERE 课程号 = 课程注册. 课程号 ) WHERE 成绩 >=60 修改 课程注册 表中成绩及格记录的 学分 值需要利用 课程 表中的 学分 字段值, 所以需要使用 FROM 子句 在查询编辑器中输入并执行上述代码后, 可以查看结果以检验执行情况 这里只对表中一列数据进行修改, 如要修改多个列, 列与列之间要用英文逗号隔开 例 5.47 利用 课程注册 表更新 教师任课 表中 学生数 的值 代码如下 : UPDATE 教师任课 SET 学生数 =(SELECT COUNT(*) FROM 课程注册 WHERE 专业代码 = 教师任课. 专业代码 AND 教师任课. 专业学级 = 课程注册. 专业学级 AND 教师任课. 教师编号 = 课程注册. 教师编号 AND 教师任课. 课程号 = 课程注册. 课程号 AND 教师任课. 专业学级 = 课程注册. 专业学级 ) 修改 教师任课 表中 学生数 字段时, 需要通过 课程注册 表统计出选课的学生人数, 所以查询语句中使用了集合函数 COUNT 在查询编辑器中输入并执行上述代码后, 可以查看结果以检验执行情况, 如图 5-58 所示 图 5-58 更新学生数后的 教师任课 表部分记录

62 5.5.3 数据的删除 随着系统的运行, 表中可能产生一些无用的数据, 这些数据不仅占用空间, 而且还影响查询的速度, 所以应该及时删除 删除数据可以使用 DELETE 语句和 TRUNCATE TABLE 语句 1. 使用 DELETE 语句删除数据从表中删除数据, 最常用的是 DELETE 语句 DELETE 语句的语法格式如下 : DELETE table_name[from{<table_source>}[, n]] [WHERE {<search_condition>}]<table_source>::=table_name[[as] table_alias][, n]] 其中 : table_name 是要从其中删除数据的表的名称 FROM <table_source> 为指定附加的 FROM 子句 WHERE 指定用于限制删除行数的条件 如果没有提供 WHERE 子句, 则 DELETE 删除表中的所有行 <search_condition> 指定删除行的限定条件 对搜索条件中可以包含的谓词数量没有限制 table_name[[as] table_alias] 是为删除操作提供标准的表名 例 5.48 删除 课程注册 表中的所有记录 代码如下 : DELETE 课程注册 此例中没有使用 WHERE 语句指定删除的条件, 将删除课程注册表中的所有记录, 只剩下表格的定义 例 5.49 删除 教师 表中没有姓名的记录 代码如下 : DELETE 教师 WHERE 姓名 IS NULL 例 5.50 删除 课程注册 表中姓名为 张斌 的课程号为 0001 的选课信息 代码如下 : 193 第 5 章 数据的基本操作

63 数据库原理与技术 (SQL Server 2012) 194 DELETE 课程注册 WHERE 课程注册. 课程号 ='0001' AND 学号 =(SELECT 学号 FROM 学生 WHERE 姓名 LIKE ' 张斌 ') 删除 课程注册 表中的数据时, 用到了 学生 表里的 姓名 字段值 张斌, 所以使用了 FROM 子句 在查询编辑器中输入并执行上述代码 用户可以使用 SQL Server Management Studio 检查代码执行结果 用户在操作数据库时, 要小心使用 DELETE 语句, 因为数据会从数据库中永久地被删除 2. 使用 TRUNCATE TABLE 清空表格使用 TRUNCATE TABLE 语句删除所有记录的语法格式为 : TRUNCATE TABLE table_name 其中 : TRUNCATE TABLE 为关键字 table_name 为要删除所有记录的表名 使用 TRUNCATE TABLE 语句清空表格要比 DELETE 语句快,TRUNCATE TABLE 是不记录日志的操作, 它将释放表的数据和索引所占据的所有空间以及所有为全部索引分配的页, 删除的数据是不可恢复的 而 DELETE 语句则不同, 它在删除每一行记录时都要把删除操作记录在日志中 删除操作记录在日志中, 可以通过事务回滚来恢复删除的数据 用 TRUNCATE TABLE 和 DELETE 语句都可以删除所有的记录, 但是表结构还存在, 而 DROP TABLE 是删除表结构和所有记录, 并释放表所占用的空间 例 5.51 用 TRUNCATE TABLE 语句清空 课程注册 表 代码如下 : TRUNCATE TABLE 课程注册 5.6 应用举例 1. 添加学生课程信息 (1) 自动添加学生必修课 假设现在是 2014 学年的第一个学期, 将学生该学期的必修课程 ( 即公共必修和专业必修 ) 自动添加到 课程注册 表中, 正常选课时选课类型设置为空 代码如下 : INSERT INTO 课程注册 ( 学号, 课程号, 教师编号, 专业代码, 专业学级, 选课类型, 学期, 学年, 成绩, 学分 ) SELECT DISTINCT A. 学号,B. 课程号,C. 教师编号,A. 专业代码,B. 专业学级,' ',C. 学期,C.

64 学年,0,0 FROM 学生 AS A JOIN 教学计划 AS B ON A. 专业代码 =B. 专业代码 AND B. 专业学级 =YEAR(A. 入学时间 ) JOIN 教师任课 AS C ON B. 专业代码 =C. 专业代码 AND B. 专业学级 =C. 专业学级 AND B. 课程号 =C. 课程号 WHERE C. 学年 ='2014' AND C. 学期 =1 AND (B. 课程类型 =' 公共必修 ' OR B. 课程类型 =' 专业必修 ') (2) 将学生未取得学分的必修课自动添加到 课程注册 表中, 且选课类型设置为 重修 代码如下: INSERT INTO 课程注册 ( 学号, 课程号, 教师编号, 专业代码, 专业学级, 选课类型, 学期, 学年, 成绩, 学分 ) SELECT DISTINCT A. 学号,C. 课程号,C. 教师编号,B. 专业代码,B. 专业学级,' 重修 ',C. 学期, C. 学年,0,0 FROM 课程注册 AS A JOIN 教学计划 AS B ON A. 专业代码 =B. 专业代码 AND A. 课程号 =B. 课程号 AND A. 专业学级 =B. 专业学级 JOIN 教师任课 AS C ON B. 专业代码 =C. 专业代码 AND B. 课程号 =C. 课程号 AND B. 专业学级 =C. 专业学级 WHERE A. 成绩 <60 AND (B. 课程类型 =' 公共必修 ' OR B. 课程类型 =' 专业必修 ') 2. 查询学生课程成绩 (1) 查询所有学生各门课程成绩 代码如下 : SELECT A. 学号,A. 姓名,C. 课程名,B. 成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A. 学号 =B. 学号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 ORDER BY A. 学号 (2) 查询某个学生的各门必修课成绩, 假设该学生的学号为 代码如下: SELECT DISTINCT A. 学号,A. 姓名,C. 课程名,B. 成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A. 学号 =B. 学号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 JOIN 教学计划 AS D ON C. 课程号 =D. 课程号 WHERE A. 学号 =' ' AND (D. 课程类型 =' 公共必修 ' OR D. 课程类型 =' 专业必修 ') 195 第 5 章 数据的基本操作

65 数据库原理与技术 (SQL Server 2012) (3) 查询学生所有必修课的平均分 代码如下 : 196 SELECT A. 学号,AVG(B. 成绩 )AS 平均分 FROM 学生 AS A JOIN 课程注册 AS B ON A. 学号 =B. 学号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 JOIN 教学计划 AS D ON C. 课程号 =D. 课程号 WHERE D. 课程类型 =' 专业必修 ' OR D. 课程类型 =' 公共必修 ' GROUP BY A. 学号 (4) 查询学生的已获得学分的成绩 代码如下 : SELECT A. 学号,A. 姓名,C. 课程名,B. 成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A. 学号 =B. 学号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 WHERE B. 成绩 >=60 ORDER BY A. 学号 (5) 查询学生的总学分 代码如下 : SELECT A. 学号,SUM(B. 学分 )AS 总学分 FROM 学生 AS A JOIN 课程注册 AS B ON A. 学号 =B. 学号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 GROUP BY A. 学号 3. 查询教师授课信息 (1) 查询所有教师授课的课程号和课程名 代码如下 : SELECT DISTINCT A. 教师编号,A. 姓名,C. 课程名,C. 课程号 FROM 教师 AS A JOIN 教师任课 AS B ON A. 教师编号 =B. 教师编号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 ORDER BY A. 教师编号

66 (2) 查询某学年某学期所有教师的具体授课信息 假设需要查询 2014 学年第一学期教师的授课信息, 应注意在同一个学期 同一个教师可能会给不同专业的学生授课, 所以要按学生的专业代码和专业名称分别列出 代码如下 : SELECT A. 教师编号,A. 姓名,C. 课程号,C. 课程名,B. 专业学级,D. 专业名称 FROM 教师 AS A JOIN 教师任课 AS B ON A. 教师编号 =B. 教师编号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 JOIN 专业 AS D ON B. 专业代码 =D. 专业代码 WHERE B. 学年 ='2014' AND B. 学期 =1 ORDER BY A. 教师编号,B. 专业学级 (3) 查询某个教师的具体授课信息和选课的学生人数 假设该教师的姓名为 周红梅 代码如下 : SELECT A. 教师编号,A. 姓名,C. 课程号,C. 课程名,B. 专业学级,D. 专业名称,B. 学生数 FROM 教师 AS A JOIN 教师任课 AS B ON A. 教师编号 =B. 教师编号 JOIN 课程 AS C ON B. 课程号 =C. 课程号 JOIN 专业 AS D ON B. 专业代码 =D. 专业代码 WHERE A. 姓名 =' 周红梅 ' ORDER BY B. 专业学级 练习题 1. 关系与普通表格 文件有何区别? 2. 两个关系做并 交 差 笛卡儿积 选择运算, 最后得到的关系的基数是什么? 3. 假设一个关系实例的度为 7, 基数为 15, 那么该关系有多少属性? 关系中目前有多少不同的行? 4. 如何用连接运算模拟一个选择运算? 5. 等值连接和自然连接的区别和联系是什么? 6. 设有三个关系 : 学生关系 Student(SNO,Sname,Age,Sex,Sdept) 课程关系 Course(CNO,Cname,Cdept,Tname) 学习关系 SC(SNO,CNO,Grade) 其中 Tname 表示选修某门课程的学生姓名 试用关系代数表达式表示下列查询语句 : (1) 查询 王红梅 老师所讲授课程的课程号与课程名称 ; 197 第 5 章 数据的基本操作

67 数据库原理与技术 (SQL Server 2012) 198 (2) 查询年龄大于 23 岁的男学生的学号与姓名 ; (3) 查询学号为 S074 的学生所选修课程的课程名称和任课教师姓名 ; (4) 查询至少选修 李艳 老师所讲授课程中一门课的女学生姓名 ; (5) 查询 王刚 同学没有选修的课程 ( 号 ); (6) 查询至少选修两门课程的学生学号 ; (7) 查询全部学生都选修的课程的课程号和课程名称 ; (8) 查询选修课程包含 刘大林 老师所讲授课程的学生学号 7. 某汽车品牌生产数据库中包括供应商 零件 工程项目和供应情况四个关系模式 : 供应商 ( 供应商代码, 供应商名, 状态, 所在城市 ) 零件 ( 零件代号, 零件名, 颜色, 重量 ) 工程项目 ( 项目代码, 项目名, 项目所在地 ) 供应情况 ( 供应商代码, 零件代号, 项目代码, 供应数量 ) 其中, 工程项目表中的供应数量指某供应商供应某种汽配零件给某汽车生产工程项目的数量 现给出下列数据 : 供应商 表 供应商代码 供应商名 状 态 所在城市 S1 三菱 20 东京 S2 天顺 10 济南 S3 旺特 30 青岛 S4 滕势 20 深圳 S5 金辉高科 30 佛山 零件 表 零件代号 零件名 颜 色 重 量 P1 发动机 红色 102 P2 气门导管 黄色 3 P3 锂电池 银色 100 P4 水泵 蓝色 30 P5 电器仪表 白色 8 P6 压缩机 绿色 45 工程项目 表 项目代码 项目名 项目所在地 J1 新能源轿车 A1 北京 J2 新能源轿车 A52 长春 J3 油电混合越野车 S3 天津 J4 油电混合越野车 S5 天津 J5 1.6L 轿车 TIDA 昆明 J6 1.4T 轿车 K10 上海 J7 新能源大巴 南京

68 供应情况 表 供应商代码 零件代号 项目代码 数 量 S1 P1 J1 210 S1 P1 J3 105 S1 P1 J4 700 S1 P2 J2 105 S2 P3 J1 435 S2 P3 J2 200 S2 P3 J4 501 S2 P3 J5 400 S2 P5 J1 420 S2 P5 J2 100 S3 P1 J1 201 S3 P3 J1 202 S4 P5 J1 104 S4 P6 J3 303 S4 P6 J4 200 S5 P2 J4 100 S5 P3 J1 201 S5 P6 J2 208 S5 P6 J4 500 用 SQL 语句建立以上四张表, 并完成如下查询 : (1) 求供应工程 J2 零件的供应商代码 ; (2) 求供应工程 J2 零件 P5 的供应商代码 ; (3) 求供应工程 J1 零件为红色的供应商代码 ; (4) 求没有使用济南供应商生产的红色零件的项目代号 ; (5) 求至少用了供应商 S1 所供应的全部零件的项目代号 ; (6) 求工程项目 J3 使用的各种零件的名称及其数量 ; (7) 求深圳厂商供应的所有零件代号 ; (8) 求使用佛山产的零件的工程名称 ; (9) 求没有使用东京产的零件的工程代码 ; (10) 把全部红色零件的颜色改成棕色 ; (11) 把 S5 供给 J4 的零件全部改为由 S3 供应 ; (12) 从 供应商 表中删除 S2 的记录, 并从 供应情况 表中删除相应记录 ; (13) 把 (S2,J6,P4,700) 插入 供应情况 表 199 第 5 章数据的基本操作