第7章 操纵数据表中的数据.ppt

1 第7章操纵数据表中的数据 2 主要内容 7 1概述7 2数据操作7 3检索数据概述7 4使用select子句进行简单查询7 5使用where子句选择数据7 6聚合函数7 7分组查询7 8排序7 9连接查询7 10子查询技术7 11集合运算本章小结 3 7 1概述 表创建以后 往往只是一个没有数据的空表 因此 向表中输入数据可能是创建表之后首先要执行的操作 无论表中是否有数据 都可以根据需要向表中添加数据 当表中的数据不合适或者出现了错误时 可以更新表中的数据 如果表中的数据不再需要了 则可以删除这些数据 可以根据用户需求 对表中已有的数据进行数据检索 即按照用户条件进行数据查询 4 7 2数据操作 7 2 1用INSERT语句插入数据 1 插入所有列数据 2 插入部分列数据 3 插入查询结果集数据 多行数据 4 插入标识列数据7 2 2用UPDATE语句更新数据7 2 3用DELETE语句删除数据 5 7 2 1用INSERT语句插入数据 INSERT语句的基本格式 INSERT INTO table or view name column list VALUES DEFAULT NULL expression n n derived table DEFAULTVALUES 6 各参数含义如下 table or view name 要接收数据的表或视图的名称 column list 要在其中插入数据的一列或多列的列表 必须用括号将column list括起来 并且用逗号进行分隔 如果某列不在column list中 则SQLSERVER必须能够基于该列提供一个值 否则不能加载行 如果列满足下面的条件 则SQLSERVER将自动为列提供值 具有IDENTITY属性 使用下一个增量标识值 有默认值 使用列的默认值 具有timestamp数据类型 使用当前的时间戳值 可为NULL值 使用NULL值 是计算列 使用计算值 7 VALUES 引入要插入的数据值的列表 对于column list 如果已指定 或表中的每个列 都必须有一个数据值 并且必须用圆括号将值列表括起来 DEFAULT 强制数据库引擎加载为列定义的默认值 如果某列并不存在默认值 并且该列允许NULL值 则插入NULL derived table 任何有效的SELECT语句 它返回将加载到表中的数据行 DEFAULTVALUES 强制新行包含为每个列定义的默认值 8 1 插入所有列数据 例7 1 在Student表中插入一条新的学生信息 INSERTINTOStudentVALUES 8 曾玉林 男 1991 2 25 计算机 20 NULL 123456 NULL 注意 向表中插入数据时 数字数据可以直接插入 但是字符数据和日期数据要用英文单引号引起来 不然就会提示系统错误 当VALUES子句中数据值个数和顺序与表中定义的列的个数和顺序完全一致时 column list 可以省略 否则 不能省略 9 2 插入部分列 例7 2 在Student表中插入一条新的学生信息 学号为9 姓名李林 性别为男 院系为计算机 周学时为18 执行下面语句 INSERTINTOStudent studentID studentName sex speciality credithour VALUES 9 李林 男 计算机 18 注意 该语句不能写成 INSERTINTOStudentVALUES 9 李林 男 计算机 18 因为值列表的值个数与表中列的个数不一致 10 3 插入查询结果集数据 多行数据 使用INSERT INTO derived table命令可以一次插入多行数据 derived table可以是任何有效的SELECT语句 例7 3 将学生基本信息 学号 姓名 性别 插入到学生名册表stu Info中 INSERTINTOstu InfoSELECTstudentID studentName sexFROMstudent使用INSERT INTO形式插入多行数据时 需要注意下面两点 要插入的数据表必须已经存在 要插入数据的表结构必须和SELECT语句的结果集兼容 也就是说 二者的列的数量和顺序必须相同 列的数据类型必须兼容等 11 4 插入标识列数据 标识列的值由系统自动计算并保存在相应的记录中 因此不能手工插入 在使用INSERT命令向包含有标识列的表插入数据时 可直接忽略标识列 例7 4 将一条学生信息插入到表StuInfo中 表结构参考6 4 1节表6 2 INSERTINTOStuInfoVALUES 张羽 女 表StuInfo中有3列 第一列学号为标识列 插入数据时忽略不计 该列的值由系统自动生成 12 那么标识列的数据是不是永远都没有办法手工更新呢 不是的 如果想手工更新学号的值 可以通过以下方法实现 首先 使用SET命令打开INSERT IDENTIYT选项 SETIDENTITY INSERTStuInfoON然后使用INSERT命令插入数据 此时一定要显式指出更新哪些列数据 包括标识列 即列列表不能省略 命令如下 INSERTINTOStuInfo stuID stuName sex VALUES 3 汪华 男 注意 INSERT IDENTIYT选项为ON时 系统将不再自动为标识列填入值 如果想恢复自动输入 需要再次执行SETIDENTITY INSERTstudentOFF 将IDENTITY INSERT选项设置为OFF 13 7 2 2用UPDATE语句更新数据 可以使用UPDATE语句更新表中已经存在的数据 该语句既可以一次更新一行数据 也可以一次更新多行数据 UPDATE语句的基本语法如下 UPDATE TOP n PERCENT table or view nameSET column name expression DEFAULT NULL variable column expression WHERE 参数说明 TOP n PERCENT 指定将要更新的行数或行百分比 table orview name 要更新行的表或视图的名称 SET 指定要更新的列或变量名称的列表WHERE 指定条件来限定所更新的行 为要更新的行指定需满足的条件 14 当执行UPDATE语句时 如果使用了WHERE子句 则指定表中所有满足WHERE子句条件的行都将被更新 如果没有指定WHERE子句 则表中所有的行都将被更新 例7 5 将学生表Student中 李林 所属的学院由 计算机 改为 数学 UPDATEStudentSETspeciality 数学 WHEREstudentName 李林 更新数据时 每个列既可以被直接赋值 也可以通过计算得到新值 例7 6 将所有计算机系学生的选课成绩加5分 UPDATEGradeSETgrade grade 5WHEREstudentIDIN SELECTstudentIDFROMstudentWHEREspeciality 计算机 15 7 2 3用DELETE语句删除数据 当表中的数据不再需要的时候 可以将其删除 一般情况下 可以使用DELETE语句删除表中的数据 该语句可以从一个表中删除一行或多行数据 使用DELETE语句删除数据的基本语法形式如下 DELETE FROM table name WHEREsearch condition 在DELETE语句中 如果使用了WHERE子句 表示从指定的表中删除满足WHERE子句条件的数据行 如果没有使用WHERE子句 则表示删除指定表中的全部数据 16 例7 7 删除student表中姓名为 李林 的数据记录 DELETEFROMstudentWHEREstudentName 李林 如果想删除Exam表中的所有数据 直接执行 DELETEFROMExam或者DELETEExam 17 在删除表中的全部数据时 还可以使用TRUNCATETABLE语句 其格式为 TRUNCATETABLEtable nameTRUNCATETABLE语句和DELETE语句都可以将表中的数据全部删除 但是 两条语句又有不同的特点 一般情况下 当使用DELETE语句删除数据时 被删除的数据记录在日志中 而当使用TRUNCATETABLE语句删除数据时 系统将立即释放表中数据和索引所占的空间 并且这种数据变化不被记录在日志中 18 7 3检索数据概述 检索数据是使用数据库的最基本的方式 也是最重要的方式 在SQLServer中 可以使用SELECT语句执行数据检索的操作 查看表中的数据 该语句具有非常灵活的使用方式和丰富的功能 它既可以在单表上完成简单的数据查询 也可以在多表上完成复杂的连接查询和嵌套查询 19 SELECT语句的完整语法较复杂 其主要子句可归纳如下 SELECTselect list INTOnew table FROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 其中 SELECT子句指定将要查询的列名称 INTO子句用于将查询得到的结果集插入并保存到一个新表中 FROM子句指定将要查询的对象 表或视图 WHERE子句指定数据应该满足的条件 20 一般情况下 SELECT子句和FROM子句是必不可少的 WHERE子句是可选的 如果没有使用WHERE子句 那么表示无条件地查询所有的数据 如果SELECT语句中有GROUPBY子句 则查询结果将按照分组表达式的值进行分组 将该表达式的值相等的记录作为一个组 如果GROUPBY句带有HAVING短语 则只有满足指定条件的组才会输出 如果有ORDERBY子句 则结果将按照排序表达式的值进行升序或降序排列后再输出 还可以在查询之间使用UNION EXCEPT和INTERSECT运算符 以便将各个查询的结果合并或比较到一个结果集中 21 7 4使用select子句进行简单查询 SELECT子句的select list可以是表中的属性列 也可以是表达式 或者说 可以是经过计算的值 可以在SELECT关键字后面的列列表中使用各种运算符和函数 这些运算符和函数包括算术运算符 数学函数 字符串函数 日期和时间函数以及系统函数等 算术运算符 包括 和 可以用在各种数值列上 数值列的数据类型可以是INT SMALLINT TINYINT FLOAT REAL MONEY或SMALLMONEY 22 例7 8 查询当前SQLServer版本信息 SELECT version 例7 9 查询圆周率常数及其正弦 余弦值 selectPI SIN PI COS PI 在上述两个查询中 SELECT子句后分别使用了变量和函数 从查询结果可以看到 在结果集中显示了 无列名 可以通过取别名的方法为这些列重新起名字 定义别名有两种方法 一种是使用等号 一种是使用AS关键字 使用等号时 其语法形式为 新标题 列名 使用AS关键字时 其形式为 列名 AS 新标题 AS关键字可以省略 23 以 例7 9 为例 可以通过下述语句给查询结果起别名 selectPI AS圆周率常数 SIN PI AS圆周率正弦值 COS PI AS圆周率余弦值 select圆周率常数 PI 圆周率正弦值 SIN PI 圆周率余弦值 COS PI 两个语句执行结果完全一致 在SELECT子句中还可以使用列名 因为列必须属于某个表 所以此时需要从表中查询所需数据 需要同时使用FROM子句 例7 10 查询全体学生的基本情况 select fromstudent该语句中使用 代表查询表中所有列 24 例7 11 查询全体学生的学号 姓名及性别 由题目可知 要查询的学号 姓名及性别是student表的几个属性 此时只需要用列名指出想要查询的信息即可 selectstudentID studentName sexfromstudent还可以在SELECT子句中使用计算列 表达式 例7 12 查询所有学生的姓名及其年龄 selectgetdate selectyear getdate selectbirthdayfromstudentselectstudentName year getdate year birthday asagefromstudent该语句中使用了getdate 函数和year 函数 前者用于获取系统当前日期 后者用于获取指定日期的年份 25 例7 13 查询选修了课程的学生学号 分析学生一旦选课 其信息就会记录到成绩表Grade中 因此查询选修了课程的学生学号就是查询在Grade表中有多少个学生学号的问题 SQL语句如下 selectstudentIDfromGrade从查询结果中可以看到 结果集中包含了许多重复的行 这时因为默认地使用了ALL关键字 如果想去掉重复行 可以指定DISTINCT关键字 SQL语句为 selectdistinctstudentIDfromGrade 26 7 5使用where子句选择数据 7 5 1确定查询7 5 2模糊查询7 5 3带查找范围的查询 27 7 5 1确定查询 在WHERE子句中 确定查询指的是使用比较运算符 列表 合并以及取反等运算方式进行的条件查询 比较运算符是搜索条件中最常用的 用于比较大小的运算符一般包括 等于 大于 大于等于 不等于 例7 14 查询所有计算机学院的学生学号和姓名 分析 student表中保存了所有学生信息 要想查询 计算机 学院的学生信息 则学生记录需要满足院系为 计算机 这一条件 亦即 speciality 计算机 SELECTstudentID studentNameFROMStudentWHEREspeciality 计算机 28 例7 15 查询Student表中所有年龄大于19岁的学生信息 分析 首先在Student表不存在 年龄 列 但是存在 出生年月 列 因此年龄可以通过出生年月计算出来 即 当前年份 出生年份 年龄 查询语句如下 SELECT FROMStudentWHEREyear getdate year birthday 19其中 WHERE子句中的getdate 和year 均为SQLSERVER提供的日期函数 分别用于获取系统日期和指定日期的年份 其具体含义及使用方法请参看本书第8章相关介绍 29 在WHERE子句中 还可以使用逻辑运算符 AND OR和NOT 把若干个查询条件合并起来 组成较复杂的查询条件 注意 AND为与操作 OR为或操作 NOT为取反操作 三者的优先级从高到低一次为NOT AND OR 用户也可以用括号改变优先级 例7 16 查询Student表中所有男生或者年龄大于19岁的学生姓名和年龄 分析 查询的信息只需要满足 男生 年龄大于19岁 两个条件中的一个即可 可以使用OR来表示 SELECTstudentName YEAR getdate YEAR birthday asageFROMStudentWHEREsex 男 ORYEAR getdate YEAR birthday 19 30 例7 17 查询Grade表中成绩为空的学生学号 分析 成绩为空亦即没有成绩 或成绩未定 而不是成绩为0或成绩填入了空值 如空字符串 在SQL语言里 使用NULL关键字来表示未知 不确定或没有 SELECTstudentIDFROMGradeWHEREgradeISNULL注意 查询值是否为空 要使用关键字ISNULL 否定形式为ISNOTNULL 一定不能使用 NULL 或者 31 7 5 2模糊查询 通常在查询字符数据时 提供的查询条件并不是十分准确 例如 查询仅仅是包含或类似某种样式的字符 这种查询称为模糊查询 在WHERE子句中 可以使用LIKE关键字实现这种灵活的查询 LIKE关键字用于搜索与特定字符串匹配的字符数据 LIKE关键字后面可以跟一个列值的一部分而不是完整的列值 其基本语法形式为 match expression NOT LIKEpattern ESCAPEescape character 参数说明 match expression 任何有效的字符数据类型的表达式 pattern 要在match expression中搜索并且可以包括下列有效通配符 表7 1 的特定字符串 escape character 放在通配符之前用于指示通配符应当解释为常规字符而不是通配符的字符 32 表7 1LIKE子句中的通配符 33 需要强调的是 带有通配符的字符串必须使用单引号引起来 下面是一些带有通配符的示例 LIKE AB 返回以 AB 开始的任意字符串 LIKE ABC 返回以 ABC 结束的任意字符串 LIKE ABC 返回以 ABC 开始的任意字符串 LIKE AB 返回以 AB 结束的3个字符的字符串 LIKE ACE 返回以 A C E 开始的任意字符串 LIKE A Z ing 返回4个字符长的字符串 结尾是 ing 第1个字符的范围是从A到Z LIKE L a 返回以 L 开始 第2个字符不是 a 的任意字符串 下面通过例子来说明LIKE子句的使用 34 例7 18 查询所有姓王的学生的姓名 学号和性别 分析 姓 王 的学生 其姓名必须满足这样的条件 姓名第一个字为 王 而剩下的部分可以是任意字 并且可以是任意多个字 由此可见从第二个字开始应使用 通配符 SELECTstudentID studentName sexFROMStudentWHEREstudentNameLIKE 王 例7 19 查询所有不姓刘的学生姓名和学号 分析 所有不姓刘的学生信息没有办法直接查询出来 但是可以直接查询出所有姓刘的学生信息 SELECTstudentID studentName sexFROMStudentWHEREstudentNameLIKE 刘 35 再对这些信息取否定 即NOTLIKE 就可以得到所有不姓刘的学生信息 代码如下 SELECTstudentID studentName sexFROMStudentWHEREstudentNameNOTLIKE 刘 例7 20 查询姓名中第二个字为 敏 字的教师信息 分析 姓名中第二个字为 敏 显然 敏 字前面必须有且只能有一个字 所以要用到通配符 而 敏 字后面可以有字也没有没有字 是任意长度的字符串 要用到通配符 SQL语句如下 SELECT FROMTeacherWHEREteacherNameLIKE 敏 36 例7 21 查询DB Design课程的课程号和学分 分析 若要将通配符作为文字字符使用 可将通配符放在方括号中 如字符串 ABC 表示以 ABC 开始的任意字符串 SELECTcourseID credithourFROMCourseWHEREcourseNameLIKE DB Design 37 例7 21 查询DB Design课程的课程号和学分 另一种方法 对通配符字符进行 转义 以便告诉SQL分析器 字符串中的哪些符号是普通符号 不要作为通配符来处理 转义的方法是使用ESCAPE子句 并在ESCAPE子句后跟上转义字符 转义字符可自己定义 SELECTcourseID credithourFROMCourseWHEREcourseNameLIKE DB Design ESCAPE ESCAPE 表示 为转义字符 这样匹配字符串中紧跟在 后面的字符 就不再具有通配符的含义 而是转义为普通的 字符处理 38 7 5 3带查找范围的查询 谓词IN NOTIN和BETWEEN AND NOTBETWEEN AND 可以用来查找属性值在或不在指定范围内的元组 其中 BETWEEN后是范围的下限 AND后是范围的上限 例7 22 查询年龄在19 22之间的学生的姓名 年龄和所属院系 SELECTstudentName YEAR getdate YEAR birthday specialityFROMStudentWHEREYEAR getdate YEAR birthday BETWEEN19AND22此查询等价于 SELECTstudentName YEAR getdate YEAR birthday specialityFROMStudentWHEREYEAR getdate YEAR birthday IN 19 20 21 22 39 与BETWEEN AND 相对的谓词是NOTBETWEEN AND 例7 23 查询所属院系为计算机或者化学系的学生信息 分析 可以把所有学生所属院系看作一个集合 而每个学生所属的院系就是集合中的一个 匹配集合中的某个值可以使用谓词IN 代码如下 SELECT FROMStudentWHEREspecialityin 计算机 化学 该查询等价于 SELECT FROMStudentWHEREspeciality 计算机 orspeciality 化学 40 7 6聚合函数 计数 COUNT DISTINCT ALL expression 计算总和 SUM DISTINCT ALL expression 计算平均值 AVG DISTINCT ALL expression 求最大值 MAX DISTINCT ALL expression 求最小值 MIN DISTINCT ALL expression 在SELECT语句中使用聚合函数 可为表中的值创建汇总 聚合函数又称聚集函数 统计函数 它对一组值执行计算 并返回单个值 在SQLSERVER中主要提供以下几类聚合函数 41 注意 除了COUNT以外 聚合函数都会忽略空值 NULL 聚合函数只能在以下位置作为表达式使用 SELECT语句的选择列表 子查询或外部查询 HAVING子句 42 例7 24 查询学生总人数 分析 首先确定要使用的函数 统计学生人数显然要使用计数函数即COUNT函数 接下来确定COUNT计数时使用的参数 学生信息保存在student表中 学生总人数等价于student表中学生的记录个数或学号个数 因此统计总人数实际上就是统计student表中的记录数或学号个数 SQL语句如下 SELECTCOUNT FROMStudent或 SELECTCOUNT studentID FROMStudent 43 例7 25 查询选修了课程的学生人数 由于一个学生可能选择多门课程 所以在计算时要要避免一个学生重复计数 所以可以使用DISTINCT关键字对学生学号studentID进行筛选 保证同一个学号只计数一次 代码如下 SELECTCOUNT DISTINCTstudentID FROMGrade 44 例7 26 计算计算机系学生选修1号课程的平均成绩 分析 显然 要计算平均成绩需要使用AVG函数对Grade表中的成绩列grade运算 但是Grade表中保存了所有学生的1号课程成绩 我们需要从中筛选出来计算机系的相关信息 计算机 信息保存在student表中 如何把这二者联系起来呢 可以通过两张表共有的属性 studentID 来完成 首先通过student表找出所有 计算机 系学生的studentID 只要Grade表中的studentID等于其中的某个值 就表明Grade表中的这项纪录是 计算机 系学生的选课纪录 接着就可以进一步找出所有计算机系选修1号课程的纪录 进而计算平均成绩 参考SQL语句如下 SELECTAVG Grade FROMGradeWHEREcourseID 1 andstudentIDIN SELECTstudentIDfromstudentwherespeciality 计算机 45 7 7分组查询 使用分组技术可以将记录按属性分组 属性值相等的为一组 这样做的目的是为了细化统计函数的作用对象 如果未对查询结果分组 集函数将作用于整个查询结果 而对查询结果分组后 集函数将分别作用于每个组 分组的一般格式为 GROUPBY ALL group by expression n 46 例7 27 统计每个院系的学生总人数 要想统计每个院系的学生人数 需要先把院系一样的记录放到一起作为一组 然后统计改组的人数 即 按院系 speciality 分组 统计人数 SQL语句如下 SELECTCOUNT FROMStudentGROUPBYspeciality 47 例7 28 查询每门课程的选课平均成绩及选课人数 分析 要想计算每门课程的平均成绩和选课人数 需要把 课程号 相同的纪录 同一门课 作为一组来进行运算 即按课程号courseID分组 SQL语句如下 SELECTcourseID AVG grade AS平均成绩 COUNT studentID AS选课人数FROMGradeGROUPBYcourseID如果分组后还要求按一定的条件对这些组进行筛选 最终只输出满足指定条件的组 则可以使用HAVING短语指定筛选条件 48 例7 29 查询选修了2门以上课程的学生学号 分析 先用GROUPBY子句按学号studentID进行分组 再用统计函数COUNT对每一组计数 HAVING短语给出了输出结果的条件 只有满足这个条件 即元组个数 2 的组才会输出显示 SQL语句如下 SELECTstudentIDFROMGradeGROUPBYstudentIDHAVINGCOUNT 2 49 注意 GROUPBY子句的作用对象是查询的中间结果表 它按照指定的一列或多列值进行分组 值相等的为一组 因此 使用GROUPBY子句后 SELECT子句的列名列表中只能出现分组属性和聚合函数 例如 下面的语句就是不对的 因为courseID既不是分组属性 也没有包含在聚合函数中 SELECTstudentID courseIDFROMGradeGROUPBYstudentIDHAVINGCOUNT 2 50 注意 因为HAVING子句是作为GROUPBY子句的条件出现的 所以HAVING子句一般与GROUPBY子句同时出现 并在必须出现在GROUPBY子句之后 如果不使用GROUPBY子句 则HAVING的行为与WHERE子句一样 GROUPBY子句可以包含表达式 在HAVING子句中的列只返回一个值 51 例7 30 查询课程成绩有2门 或以上 超过80分的学生学号及相应 80分以上 的课程数 分析 先从Grade表中筛选出来所有80分以上的选课纪录 然后再按学生 分组 统计选修课程的门数 超过两门的学生信息即是所求 SQL语句如下 SELECTstudentID COUNT FROMGradeWHEREgrade 80GROUPBYstudentIDHAVINGCOUNT 2由此可见 HAVING短语与WHERE子句的区别在于作用对象不同 WHERE子句作用于基表或视图 从中选择满足条件的元组 HAVING短语作用于组 从中选择满足条件的组 52 7 8排序 使用ORDERBY子句可以按一个或多个属性列对数据进行排序 默认的排序方式有两种 升序和降序 分别使用关键字ASC和DESC来指定 当排序列包含空值 NULL 时 空值默认以最大值处理 基本格式如下 ORDERBYorder by expression ASC DESC n 其中 排序表达式可以是列名 表达式或者正整数 正整数表示表中列的位置 第几列 53 例7 31 查询选修了2号课程的学生学号及其成绩 并按分数降序输出结果 SELECTstudentID gradeFROMGradeWHEREcourseID 2 ORDERBYgradeDESC 54 当基于多个属性对数据进行排序时 出现在ORDERBY子句中的列的顺序是非常重要的 因为系统是按照排序列的先后进行排序的 如果第一个属性相同 则依据第二个属性排序 如果第二个属性相同 则依据第三个属性排序 依此类推 例7 32 查询全体学生信息 查询结果按学生所在系的系名升序排列 同一个系中的学生按年龄降序排列 SELECT FROMStudentORDERBYspeciality birthday在该查询中 系统先按照院系speciality升序进行排序 关键字ASC省略 然后对于院系相同的元组再按照出生年月降序 即年龄升序 进行排序 55 当在查询语句中使用了ORDERBY子句时 还经常在SELECT子句中使用TOP关键字 TOP关键字表示仅在结果集中从前向后列出指定数量的数据行 如果在使用TOP关键字的SELECT语句中没有使用排序子句 则只是随机地返回指定数量的数据行 使用TOP关键字的基本语法有两种 TOP n 从前向后返回n行数据TOP n PERCENT 按照百分比返回指定数量的数据行 56 例7 33 查询班内前5个学生的信息 SELECTTOP 5 FROMStudentORDERBYstudentID本查询先将结果集中的数据按照studentID升序排序 然后取出前5个输出显示 57 例7 34 查询程序课程成绩排名在前20 的学生学号 姓名和成绩 分析 先筛选得到课程2的选课信息并按 成绩 降序排序 然后取出前面20 的记录输出显示 本查询SQL语句如下 SELECTTOP 20 PERCENTGrade studentID GradeFROMGradeWHEREcourseID 2ORDERBYGradeDESC 58 7 9连接查询 在设计表时 为了提高表的设计质量 经常把相关的数据分散在不同的表中 但是 在实际使用时 往往需要同时从两个或两个以上表中检索数据 实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作称为连接技术 连接查询是关系数据库中最主要的查询 包括交叉连接 内部连接 外部连接三种 59 连接查询 7 9 1交叉连接7 9 2内连接7 9 3外连接 60 7 9 1交叉连接 交叉连接也称为笛卡儿乘积 它返回两个表中所有数据行的全部组合 所得结果集中的数据行数等于第一个表中的数据行数乘以第二个表中的数据行数 例子SELECT FROMStudentCROSSJOINCourse或简写为SELECT FROMStudent Course 61 在FROM子句中指定连接条件的语法格式为 FROMfirst tablejoin typesecond table ON join condition 其中 连接类型join type可以是交叉连接 CROSSJOIN 内部连接 INNERJOIN 外部连接 OUTERJOIN ON子句指出连接条件 它由被连接表中的列和比较运算符 逻辑运算符等构成 62 如果添加了WHERE子句 则只显示满足条件的行 例如 添加条件的交叉连接 SELECT FROMStudentCROSSJOINCourseWHEREspeciality 计算机 带WHERE子句的交叉连接 63 7 9 2内连接 内联接 INNERJOIN 使用比较运算符进行表间某 些 列数据的比较操作 并列出这些表中与联接条件相匹配的数据行 1 等值连接查询 2 不等值连接查询 3 自连接查询 64 1 等值连接查询 连接条件或连接谓词中的运算符为等号的连接查询 称为等值连接 例7 35 查询每个学生选修课程的情况 学生信息存放在Student表中 学生选课信息存放在Grade表中 所以本查询实际上涉及Student与Grade两个表 这两个表之间的联系是通过公共属性studentID实现的 法 一 在FROM子句中指定连接条件SELECT FROMstudentINNERJOINGradeONstudent studentID grade studentID法 二 在WHERE子句中指定连接条件SELECT FROMstudent GradeWHEREstudent studentID grade studentID 65 本例中 SELECT子句 ON子句与WHERE子句中的属性名前都加上了表名前缀 这是因为在Student表和Grade表中都有属性studentID 为了避免混淆 必须加上表名 如果属性名在参加连接的各表中是惟一的 则可以省略表名前缀 66 如果在进行等值或不等值连接时目标列不使用 而使用列名称 从而把结果集中重复的属性列去掉 就成了自然连接 例如 在 例7 35 中 查询每个学生选修课程的情况 并去掉重复列 可以使用下面的SQL语句 SELECTStudent studentID studentName birthday speciality courseID GradeFROMStudentINNERJOINGradeONStudent studentID Grade studentID 67 2 不等值连接查询 当连接条件或连接谓词中的运算符不是等号 时 将该连接称为不等值连接 这些运算符可以是 还可以使用BETWEEN AND 之类的谓词 一般情况下 不等值连接通常和等值连接一起组成复合条件 共同完成一组查询 68 2 不等值连接查询 例7 36 查询每个学生选修课程成绩大于80 的情况 学生情况存放在Student表中 学生选课情况存放在Grade表中 所以本查询实际上涉及Student与Grade两个表 这两个表之间的联系是通过公共属性studentID实现的 法 1 在FROM子句中指定连接SELECTStudent Grade FROMStudentINNERJOINGradeONStudent studentID Grade studentIDANDGrade Grade 80法 2 在FROM子句中指定连接SELECTStudent Grade FROMStudent GradewhereStudent studentID Grade studentIDANDGrade Grade 80 69 3 自连接查询 连接还可以在同一张表上进行 这种连接称为自连接 SelfJoin 相应的查询称为自连接查询 例7 37 查找课程不同成绩相同的学生的学号 课程号和成绩 不考虑同一学生成绩相同的情况 要查询不同学生课程不同成绩相同的信息 显然 要使用两张Grade表 在第一张表中保存学生的课程 成绩 在第二张表中逐条读取学生成绩记录与第一张表中的记录比较 看是否满足条件 课程不同 成绩相同 SQL语句如下 SELECTX studentID X courseID X grade Y studentID Y courseID Y gradeFROMGradeXJOINGradeYONX courseIDY courseIDANDX grade Y gradeANDX studentIDY studentID 70 分析查询结果 可以看到 结果集中的数据并不是我们最终想要的 因为里面的记录两两重复了 在自连接查询中 这是经常出现的情况 因为所有的记录都会被比较两次 要想得到最终的结果 需要加上一些限制条件对结果进一步处理 执行下面的语句 观察结果有什么变化 SELECTX studentID X courseID X grade Y studentID Y courseID Y gradeFROMGradeXJOINGradeYONX courseIDY courseIDANDX grade Y gradeANDX studentID Y studentID思考 如果需要把同一个学生不同课程 成绩相同的记录同时显示出来 语句该如何实现 71 7 9 3外连接 在内连接操作中 只有满足连接条件的元组才能作为结果输出 如在 例7 35 的结果表中没有关于5 6 7号学生的选课信息 原因在于他们没有选课 在Grade表中没有这些学生相应的记录 因此内连接后就没有这些学生的选课信息 但是有时需要以Student表为主体列出每个学生的基本情况及其选课情况 若某个学生没有选课 则只输出其基本情况信息 其选课信息为空值即可 这时可以使用外连接 OUTERJOIN 在MicrosoftSQLSERVER2008系统中 可以使用3种外连接关键字 即LEFTOUTERJOIN 或LEFTJOIN RIGHTOUTERJOIN 或RIGHTJOIN 和FULLOUTERJOIN 或FULLJOIN 72 LEFTOUTERJOIN表示左外连接 结果集中将包含左表中的所有数据和第二个连接表中满足条件的数据 RIGHTOUTERJOIN表示右外连接 结果集中将包含右表中的所有数据和第一个连接表中满足条件的数据 FULLOUTERJOIN表示全外连接 它综合了左外连接和右外连接的特点 返回两个表的所有行 对于不满足外连接条件的数据在另外一个表中的对应值以NULL填充 73 例7 38 查询所有学生选修课程的情况 包括没有选修课程的学生 本例和例7 35的不同在于 例7 35 只需输出选修了课程的学生信息 没有选修课程的学生信息不输出 而本例却必须输出全部学生信息 因此 必须使用外连接才能实现 SQL语句如下 SELECTStudent studentID Student studentName courseID gradeFROMStudentLEFTOUTERJOINGradeONStudent studentID Grade studentID在该查询语句中 使用了左外连接 所以Student表 左表 中的数据将全部输出 而Grade表 右表 中不满足查询条件的数据记录在对应的记录位置上都用NULL表示 74 本例也可以用右外连接来完成 这时只需要把Student表和Grade表的位置调换一下即可 SELECTStudent studentID Student studentName courseID gradeFROMGradeRIGHTOUTERJOINStudentONStudent studentID Grade studentID 75 7 10子查询技术 在SQL语言中 一个SELECT FROM WHERE 语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 又称为子查询 例如 SELECTstudentNameFROMStudentWHEREstudentIDIN SELECTstudentIDFROMGradeWHEREcourseID 2 76 在本例中 下层查询块 SELECTstudentIDFROMGradeWHEREcourseID 2 是嵌套在上层查询块 SELECTstudentNameFROMStudentWHEREstudentIDIN 的WHERE子句中的 上层查询块称为外层查询或者父查询 下层查询块称为内层查询或子查询 当查询语句比较复杂 不容易理解 或者一个查询依赖于另外一个查询结果时 就可以使用子查询 77 SQL语言允许多层嵌套查询 即一个子查询中还可以嵌套其他子查询 在使用子查询时 需要注意以下几点 子查询必须使用圆括号括起来 子查询中不能使用ORDERBY子句 如果父查询中使用了ORDERBY子句 则ORDERBY子句必须与TOP子句同时出现 嵌套查询一般的求解方法是由里向外 即每个子查询要在上一级查询处理之前求解 子查询的结果用于建立其父查询要使用的查找条件 78 7 10 1带IN的嵌套查询7 10 2带比较运算符的嵌套查询7 10 3带ANY或ALL的嵌套查询7 10 4带EXISTS的嵌套查询 79 7 10 1带IN的嵌套查询 在嵌套查询中 子查询的结果往往是一个集合 所以谓词IN是嵌套查询中最常使用的谓词 其主要使用方式为 WHEREexpression NOT IN subquery 需要注意的是 包含IN和NOTIN的子查询只能返回一列数据 例7 39 查询与 张三 在同一个院系学习的学生信息 先分步完成此查询 然后再构造嵌套查询 1 确定 张三 所在的系名 SELECTspecialityFROMStudentWHEREstudentName 张三 查找结果为 软件学院 80 2 查找所有在 软件学院 系学习的学生信息 SELECT FROMStudentWHEREspeciality 软件学院 然后将第 1 步查询嵌入到第 2 步查询中 构造出嵌套查询 SELECT FROMStudentWHEREspecialityIN SELECTspecialityFROMStudentWHEREstudentName 王恒 81 例7 40 查询选修了课程名为 数据库原理 的学生学号和姓名 分析 本查询涉及学号 姓名和课程名3个属性 学号和姓名存放在Student表中 课程名存放在Course表中 这两个表之间没有直接的联系 所以需要通过Grade表建立二者之间的联系 因此本查询实际上涉及3个关系 SELECTstudentID studentName 最后在Student表中取出相应学FROMStudent号的学生信息WHEREstudentIDIN SELECTstudentID 然后在Grade中找出选修了3号FROMGrade课程的学生学号为 1 2 4WHEREcourseIDIN SELECTcourseID 首先在Course表中找出 数据库FROMCourse原理 的课程号 结果为3WHEREcoursename 数据库原理 82 7 10 2带比较运算符的嵌套查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接 当用户能确切知道内层查询返回的是单值时 可以用 等比较运算符 例如 在 例7 39 中 由于一个学生只可能在一个系学习 也就是说子查询的结果是一个值 因此可以用 代替 IN 其SQL语句如下 SELECT FROMStudentWHEREspeciality SELECTspecialityFROMStudentWHEREstudentName 王恒 83 例7 39 和 例7 40 中的子查询都只执行一次 其查询结果用于父查询 子查询的条件不依赖于父查询 这类子查询称为不相关子查询 不相关子查询是较简单的一类子查询 如果子查询的查询条件依赖于父查询 这类子查询称为相关子查询 下面就是一个相关子查询的例子 例7 41 找出每个学生超过他选修课程平均成绩的课程号 分析 该查询需要用到两张表 一张是学生的选课成绩表Grade 另一张是学生的平均成绩表 存放学生的平均成绩 该表可以通过查询的结果集来构造 然后用第一张表的学生成绩跟第二张表中该学生的平均成绩做比较 即可找出满足条件的记录 84 SQL语句如下 SELECTstudentID courseIDFROMGradexWHEREgrade SELECTAVG grade FROMGradeyWHEREy studentID x studentID x是表Grade的别名 又称为元组变量 可以用来表示Grade的一个元组 内层查询是求一个学生所有选修课程平均成绩的 至于是哪个学生的平均成绩要看参数x studentID的值 而该值是与父查询相关的 因此这类查询称为相关子查询 85 7 10 3带ANY