《表中数据的操作》PPT课件.pptVIP

数据库基础与应用 第5章表中数据的操作 本章学习目标 在企业管理器中操作表中的数据使用SELECT语句查询数据使用INSERT语句插入数据使用UPDATE语句更新数据使用DELETE语句删除数据 本课核心知识点 SELECT语句INSERT语句UPDATE语句DELETE语句 本课重点难点 SELECT语句的基本结构及其各子句的用法多表查询 连接查询 嵌套查询 使用INSERT UPDATE DELETE语句插入 修改和删除表中数据嵌套查询 第5章表中数据的操作 5 1在企业管理器中操作表中的数据 表中数据的操作包括数据的查询 插入 更新和删除 在企业管理器中 打开指定的服务器和数据库选项 右击所要操作的表 选择 打开表 选项 选择 查询 选项 出现查询数据对话框 在对话框中设置各种查询条件 还可以直接输入Transact SQL中的查询语句 单击工具栏中的执行按钮将执行指定的查询 并显示查询结果 打开指定的服务器和数据库选项 右击所要操作的表 从弹出的快捷菜单中选择 打开表 选项 选择 返回所有行 或 返回首行 选项 出现显示数据对话框 将光标定位到最后一条记录后面 输入数据就可以完成插入操作 将光标定位到某条记录 改变某个数据的值就可以完成更新操作 右击某条记录 从弹出的快捷菜单中选择 删除 选项可以完成记录的删除操作 如果对表定义了约束 则在执行插入 更新 删除操作时不能违反约束 否则系统会提示 5 2使用SELECT语句查询数据 问题5 1 要求按所发帖子的总点击数由高到低 列出所发帖子的总点击数超过1000的论坛用户的编号 昵称和帖子总点击数 如何使用SQL语句完成这个查询任务 SELECT语句的功能就是从数据库中检索出符合用户需求的数据 语法格式 SELECT字段列表 INTO新表名 FROM要查询的表 WHERE查询条件 GROUPBY作为分组依据的表达式 HAVING对分组结果进行的筛选条件 ORDERBY排序依据的字段 ASC DESC SELECT语句至少要包含SELECT和FROM两个子句 其它用 括起的短语都是可选的 5 2 1使用SELECT子句 SELECT子句语法形式如下 1 SELECT 参数 字段列表 每个字段用 隔开 其中 参数若是distinct 表示对于某个字段值有重复的记录 则查询结果中只保留一条 参数若是TOPN 则代表只保留满足条件的前N条记录 字段列表中的字段若都是同一张表的 则直接写字段名即可 若涉及到多个表 则形式为 表名 字段名 2 SELECT 代表查询表中的所有字段 3 SELECT聚集函数 字段名 在expression中可以使用行聚合函数 又称统计函数 SQLServer中常用的聚合函数如表 例5 1 假设Section表中的数据如表所示 1 查询表中的所有记录 SELECT FROMSection 2 查询所有版块的名称 SName 点击率 SClickCount 和帖子数量 STopicCount SELECTSname SClickCount STopicCountFROMSection查询结果如下 SNameSClickCountSTopicCount 逍遥体苑 501 相约同行 1001 游记攻略 1200 所影响的行数为3行 3 查询所有版块的SName 别名为版块名称 SMasterID 别名为版主编号 和SClickCount 别名为点击率 SELECT版块名称 Sname SMasterIDAS版主编号 SClickCount点击率FROMSection说明 在上例中使用了更改列标题 定义别名 的3种方法 列别名 列名列名AS列别名列名列别名注意 列别名的使用范围 列别名只在定义的语句中有效 4 查询前2条记录 SELECTTOP2 FROMSection 5 查询所有版块的帖子数量 STopicCount 去掉重复值 SELECTDISTINCTSTopicCountFROMSection 6 统计所有版块的帖子总数 SELECTSUM STopicCount FROMSection 5 2 2使用INTO子句 INTO子句用于创建新表并将查询结果插入新表中 语法格式 INTO新表 其中的参数 新表 用于指定所要生成的新表的名称 新创建表的列由select list指定 例5 2 创建一个只有版块编号 版块名称和点击率的新表New Section 其列定义和表中数据与Section表相同 SELECTSID Sname SClickCountINTONew SectionFROMSection运行结果如下 所影响的行数为3行 执行select fromNew Section 返回结果为 SIDSnameSClickCount 1 逍遥体苑 502 相约同行 1003 游记攻略 120 所影响的行数为3行 5 2 3使用FROM子句 FROM子句用于指定要查询的表 语法形式如下 FROM n 指定查询所用的表 视图 派生表或联接表 例5 3 假设Users表中的数据如下图所示 1 查询所有版块的版块编号 版块名称 版主的编号 姓名以及电子邮箱 USEbbsDBGOSELECTSID SName UID UName UEmailFROMSection UsersWHEREUsers UID Section SmasterIDGO 2 使用内联接INNERJOIN完成 1 的功能 SELECTSID SName UID UName UEmailFROMSectionINNERJOINUsersONUID SmasterID 5 2 4使用WHERE子句 WHERE子句是条件子句 用于限定查询的内容 语法格式 WHERE查询的条件表达式查询的条件表达式 NOT AND OR NOT n 参数说明 search condition 查询的条件表达式 1 比较表达式使用比较表达式的一般形式为 表达式比较运算符表达式表达式 可以是列名 常量 函数 变量 标量子查询 或者是由运算符或子查询连接的列名 常量和函数的任意组合 还可以包含CASE函数 比较运算符 等于 大于 对于等于 不等于 不大于 不小于 不等于 例5 4 查询表Section中帖子数量不小于10的版块 SELECT FROMSectionWHERESTopicCount 10 2 逻辑表达式在Transact SQL中可以使用的逻辑运算符有3个 NOT 逻辑反 AND 逻辑与 OR 逻辑或 逻辑运算符的优先顺序是NOT AND OR 在比较表达式和逻辑表达式中有3种可能的取值 TRUE FALSE或UNKNOWN 例5 5 查询表Section中点击率不低于100 并且帖子数量不为0的版块 SELECT FROMSectionWHERESClickCount 100ANDSTopicCount0 3 BETWEEN关键字使用BETWEEN关键字可以限定查寻范围 其语法形式如下 test expression NOT BETWEENbegin expressionANDend expression参数说明 test expression 被测试的表达式 begin expression 指定取值范围的上限 end expression 指定取值范围的下限 例5 6 查询表Section中点击率在50和100之间的版块 SELECT FROMSectionWHERESClickCountBETWEEN50AND100 4 IN关键字使用IN关键字可以测试给定的值是否与子查询或列表中的值相匹配 语法格式 test expression NOT IN subquery expression n 参数说明 test expression 任何有效的SQLServer表达式 subquery 包含某列结果集的子查询 expression n 一个表达式列表 用来测试是否匹配 例5 7 从Users表中查询生日不在3月 5月和7月的论坛用户信息 SELECT FROMUsersWHEREMONTH UBirthday NOTIN 3 5 7 5 LIKE关键字LIKE关键字用于将所给字符串与指定的的模式匹配 语法格式 match expression NOT LIKEpattern参数说明 match expression 任何字符串数据类型的有效SQLServer表达式 Pattern 指定match expression中的搜索模式 可以包含下列有效SQLServer通配符 可匹配任意类型和长度的字符串 下划线 可匹配任何单个字符 例5 8 在Pubs数据库的authors表中查找所有区号为415的电话号码 USEpubsSELECTphoneFROMauthorsWHEREphoneLIKE 415 ORDERbyau lnameGO 例5 9 在Pubs数据库的authors表中查找名字为Cheryl或Sheryl的作者 USEpubsSELECTau lname au fname phoneFROMauthorsWHEREau fnameLIKE CS heryl ORDERBYau lnameASC au fnameASCGO查询结果如图5所示 例5 10 在Pubs数据库的authors表中查找姓为Carson Carsen Karson或Karsen的作者 USEpubsSELECTau lname au fname phoneFROMauthorsWHEREau lnameLIKE CK ars eo n ORDERBYau lnameASC au fnameASCGO查询结果如图所示 6 NULL关键字在WHERE子句中不能使用比较运算符对空值进行判断 只能使用ISNULL来确定一个给定的表达式是否为NULL 其语法形式如下 表达式IS NOT NULL 例5 11 在Pubs数据库的authors表中查找所有预付款少于 5 000或者预付款未知 或为NULL 的书 返回它们的书号及预付款 USEpubsGOSELECTtitle id advanceFROMtitlesWHEREadvance 5000ORadvanceISNULLGO查询结果如下 5 2 5使用GROUPBY子句 GROUPBY子句用来对查询结果分组 语法格式 GROUPBY分组表达式注意 指定GROUPBY时 SELECT列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中 例5 12 统计论坛库中Users表中的男 女用户人数 USEbbsDBGOSELECTUSex COUNT 人数FROMUsersGROUPBYUSexGO查询结果如图 5 2 6使用HAVING子句 HAVING子句指定分组或聚合的搜索条件 HAVING必须与GROUPBY子句一起使用 如果不使用GROUPBY子句 不能使用HAVING子句 语法格式 HAVING筛选条件 筛选条件 指定组或聚合应满足的搜索条件 例5 13 在Pubs数据库的titles表中查询截止到目前的销售额超过 40 000的出版商 USEpubsGOSELECTpub id total SUM ytd sales FROMtitlesGROUPBYpub idHAVINGSUM ytd sales 40000GO查询结果如图 5 2 7使用ORDERBY子句 ORDERBY子句用于根据一个列或者多个列来排序查询结果 语法形式如下 ORDERBY排序表达式 ASC DESC n 参数说明 排序表达式 指定要排序的列 可以是多个字段 用 隔开 ASC 指定按递增顺序的值进行排序 DESC 指定按递减顺序的值进行排序 说明 空值被视为最低的可能值 例5 14 将发帖表Topic中的记录按点击率由低到高排序 USEbbsDBGOSELECT FROMTopicORDERBYTClickCountGO 5 2 8使用COMPUTE子句 COMPUTE子句用于生成统计结果 放在查询结果的最后 语法格式 COMPUTE聚集函数 列名 对于SELECT查询的返回结果 计算指定的某个聚集函数的值 例5 15 查询Section表中所有版块的点击率及平均点击率 USEbbsDBSELECTSID SName SClickCountFROMSectionCOMPUTEAVG SClickCount GO查询结果如图 5 2 9使用联合查询 联合查询是指将两个或两个以上的SELECT语句通过UNION运算符连接起来的查询 联合查询可以将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询中所有查询的全部行 使用UNION组合两个查询结果集的两个基本规则是 所有查询中的列数和列的顺序必须相同 数据类型必须兼容 语法格式 查询语句1UNION ALL 查询语句2参数说明 ALL 在结果中包含所有的行 包括重复行 如果没有指定 则删除重复行 例5 16 查询发帖表Topic和跟帖表Reply中所有帖子的发帖人编号和主题 USEbbsDBGOSELECTTUIDAS发帖人编号 TTopicAS主题FROMTopicUNIONSELECTRUID RTopicFROMReplyGO查询结果如图 5 2 10使用嵌套查询 在SQL语言中 一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询 嵌套查询中上层的查询块称为外层查询或父查询 下层查询块称为内层查询或子查询 SQL语言允许多层嵌套 嵌套查询主要用于复杂的查询中 在某些嵌套查询中WHERE之后还可以使用ANY和ALL两个关键字 例5 17 查询年龄最小的论坛用户的用户编号 昵称 出生日期和电子邮箱 USEbbsDBGOSELECTUID UName UBirthday UEmailFROMUsersWHEREUBirthday ALL SELECTUBirthdayFROMUsers 查询结果如图 例5 18 查询除年龄最小的论坛用户外的所有用户的编号 昵称 出生日期和电子邮箱 USEbbsDBGOSELECTUID UName UBirthday UEmailFROMUsersWHEREUBirthday ANY SELECTUBirthdayFROMUsers 查询结果如图 例5 19 SELECT语句综合应用 关于 问题5 1 的解答 SELECTUID U ame SUM TClickCount asTClickCount TotalFROMUsers TopicWHEREUsers UID Topic TUIDGROUPBYUID UNameHAVINGSUM TClickCount 1000ORDERBYTClickCount TotalDESC查询结果如图 5 3使用INSERT语句插入数据 问题5 2 在Pubs数据库中 从编号为1389的出版社新进一本business类书 书号为BU9876 书名为CreatingWebPages 价格为29 99元 如何使用SQL语句将此书加入Pubs数据库的图书表Titles中 可以使用INSERT语句向表或视图中添加一行或多行数据 语法格式 INSERTINTO表名 要插入数据的字段列表 VALUES 每个字段相应的值 说明 字段列表中字段的个数与值的个数必须相等 要插入的字段值的类型必须与表定义相同 字段列表如果是表中全部字段时 可以省略 例5 20 将一行添加到Pubs数据库的titles表中 以指定书名 类型 出版商及价格的值 USEPubsGOINSERTINTOtitles title id title type pub id price VALUES BU9876 CreatingWebPages business 1389 29 99 5 4使用UPDATE语句更新数据 问题5 3 如何使用SQL语句将Pubs数据库的titles表中书号为 BU9876 的书名修改为 网页制作 可以使用UPDATE语句修改表中特定记录或字段的数据 语法格式 UPDATE表名SET列名1 表达式1 列名2 表达式2 WHERE查询表达式 参数说明 WHERE 指定条件来限定所更新的行 若无WHERE子句 将会修改表中的每行数据 例5 21 修改Pubs数据库的titles表中书号为 BU9876 的书名为 网页制作 USEPubsGOUPDATEtitlesSETtitle 网页制作 WHEREtitle id BU9876 5 5使用DELETE语句删除数据 DELETE语句用于删除表中记录 5 5 1使用DELETE语句 语法格式 DELETEFROM表名 WHERE查询表达式 参数说明 WHERE 指定条件来限定所删除的行 若无WHERE子句 将删除表中所有行 例5 22 删除pubs数据库titles表中书号为 BU9876 的记录 USEpubsGODELETEFROMtitlesWHEREtitle id BU9876 例5 23 删除pubs数据库authors表中au lname是McBadden的所有行 USEpubsGODELETEFROMauthorsWHEREau lname McBadden 5 5 2使用TRUNCATE语句 清除表中的所有数据 只留下表的定义 可以使用TRUNCATE语句 与DELETE语句相比 TRUNCATE通常速度快 因为TRUNCATE是不记录日志的操作 语法格式 TRUNCATETABLEname参数name指定要删除全部行的表的名称 例5 24 清空跟帖表Reply中的数据 TRUNCATETABLEReply 5 6综合实例 实例说明 对在4 7中创建的COLLEGE数据库 完成以下对表中数据的操作 1 查询 06182 班总分在300分以上的学生的学号 姓名和总成绩 结果按总成绩由高到低排序 设学号字段S number的前5位为班号 2 根据Student表产生一个名为Student 1的新表 其内容仅包括党员同学 3 向Student 1表插入单条记录 情况如表所示 4 批量插入记录 将表Student表中所有女同学加入到Student 1中 5 将Score表中分数低于60分的在原来的基础上加10分 6 将Student 2表中的所有政治面目为群众的同学删除 实现技术分析 1 使用SELECT语句完成查询操作 2 使用INSERT语句完成向表中添加新记录的插入操作 3 使用UPDATE语句完成表中数据的修改操作 4 使用DELETE语句完成表中数据的删除操作 功能实现 1 使用SELECT语句进行查询操作 需要用到GROUPBY HAVING ORDERBY等子句 将查询结果存入新表 还需要使用INTO子句 2 插入单条记录 若提供的字段值去不全 需要列出各字段值所对应的字段名 注意要一一对应 3 成批插入记录的数据来源为一个查询 4 可以成批更新和删除符合条件的记录 关键代码 或技术 1 SELECTStudent S number S nameAS姓名 SUM Score AS总分FROMStudent ScoreWHEREStudent S number Score S numberandLEFT Student S number 5 0