《表中数据的操作精选文档.ppt》由会员分享，可在线阅读，更多相关《表中数据的操作精选文档.ppt（67页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、表中数据的操作本讲稿第一页，共六十七页本章学习目标本章学习目标 在企业管理器中操作表中的数据使用使用SELECT语句查询数据语句查询数据使用INSERT语句插入数据使用UPDATE语句更新数据使用DELETE语句删除数据本讲稿第二页，共六十七页本课核心知识点本课核心知识点SELECT语句语句INSERT语句UPDATE语句DELETE语句本讲稿第三页，共六十七页本课重点难点本课重点难点SELECT语句的基本结构及其各子句的用法多表查询（连接查询、嵌套查询）多表查询（连接查询、嵌套查询）使用INSERT、UPDATE、DELETE语句插入、修改和删除表中数据嵌套查询 本讲稿第四页，共六十七页第第
2、5章章 表中数据的操作表中数据的操作 本讲稿第五页，共六十七页5.1 在企业管理器中操作表中的数据在企业管理器中操作表中的数据 表中数据的操作包括数据的查询查询、插入插入、更新更新和删除删除。本讲稿第六页，共六十七页在企业管理器中，打开指定的服务器和数据库选项，右击所要操作的表，选择“打开表”选项，选择“查询”选项，出现查询数据对话框。在对话框中设置各种查询条件，还可以直接输入Transact-SQL中的查询语句，单击工具栏中的执行按钮将执行指定的查询，并显示查询结果。本讲稿第七页，共六十七页打开指定的服务器和数据库选项，右击所要操作的表，从弹出的快捷菜单中选择“打开表”选项，选择“返回所有行
3、”或“返回首行”选项，出现显示数据对话框。本讲稿第八页，共六十七页将光标定位到最后一条记录后面，输入数据就可以完成插入操作；将光标定位到某条记录，改变某个数据的值就可以完成更新操作；右击某条记录，从弹出的快捷菜单中选择“删除”选项可以完成记录的删除操作。如果对表定义了约束，则在执行插入、更新、删除操作时不能违反约束，否则系统会提示。本讲稿第九页，共六十七页5.2 使用使用SELECT语句查询数据语句查询数据【问题5-1】要求按所发帖子的总点击数由高到低，列出所发帖子的总点击数超过1000的论坛用户的编号、昵称和帖子总点击数。如何使用SQL语句完成这个查询任务？SELECT语句的功能就是从数据库
4、中检索出符合用户需求的语句的功能就是从数据库中检索出符合用户需求的数据。数据。本讲稿第十页，共六十七页语法格式：语法格式：SELECT字段列表INTO新表名FROM要查询的表WHERE查询条件GROUPBY作为分组依据的表达式HAVING对分组结果进行的筛选条件ORDERBY排序依据的字段ASC|DESCSELECT语句至少要包含语句至少要包含SELECT和和FROM两个子句，其它用两个子句，其它用括起的短语都是可选的。括起的短语都是可选的。本讲稿第十一页，共六十七页5.2.1 使用使用SELECT子句子句 SELECT子句语法形式如下：（1）SELECT参数字段列表（每个字段用，隔开）其中，
5、参数若是distinct，表示对于某个字段值有重复的记录，则查询结果中只保留一条；参数若是TOPN，则代表只保留满足条件的前N条记录；字段列表中的字段若都是同一张表的，则直接写字段名即可，若涉及到多个表，则形式为：表名.字段名；（2）SELECT*代表查询表中的所有字段（3）SELECT聚集函数（字段名）本讲稿第十二页，共六十七页在expression中可以使用行聚合函数（又称统计函数），SQL Server中常用的聚合函数如表。本讲稿第十三页，共六十七页【例5-1】假设Section表中的数据如表所示。本讲稿第十四页，共六十七页（1）查询表中的所有记录。SELECT *FROM Sectio
6、n（2）查询所有版块的名称（SName），点击率（SClickCount）和帖子数量（STopicCount）。SELECT Sname,SClickCount,STopicCount FROM Section查询结果如下：SName SClickCount STopicCount-【逍遥体苑】50 1【相约同行】100 1【游记攻略】120 0（所影响的行数为 3 行）本讲稿第十五页，共六十七页（3）查询所有版块的SName（别名为版块名称），SMasterID（别名为版主编号）和SClickCount（别名为点击率）。SELECT 版块名称=Sname,SMasterID AS 版主编号,
7、SClickCount 点击率FROM Section说明：说明：在上例中使用了更改列标题（定义别名）的3种方法：列别名=列名列名 AS 列别名列名 列别名注意：注意：列别名的使用范围：列别名只在定义的语句中有效。本讲稿第十六页，共六十七页（4）查询前2条记录。SELECT TOP 2 *FROM Section（5）查询所有版块的帖子数量（STopicCount），去掉重复值。SELECT DISTINCT STopicCount FROM Section（6）统计所有版块的帖子总数。SELECT SUM(STopicCount)FROM Section本讲稿第十七页，共六十七页5.2.2
8、使用使用INTO子句子句 INTO子句用于创建新表并将查询结果插入新表中。语法格式：INTO 新表 其中的参数“新表”用于指定所要生成的新表的名称。新创建表的列由select_list指定。本讲稿第十八页，共六十七页【例5-2】创建一个只有版块编号、版块名称和点击率的新表New_Section_，其列定义和表中数据与Section表相同。SELECT SID,Sname,SClickCount INTO New_Section FROM Section运行结果如下：（所影响的行数为 3 行）执行select*from New_Section，返回结果为：SID Sname SClickCoun
9、t-1 【逍遥体苑】502 【相约同行】1003 【游记攻略】120（所影响的行数为 3 行）。本讲稿第十九页，共六十七页5.2.3 使用使用FROM子句子句 FROM子句用于指定要查询的表。语法形式如下：FROM ,.n.：指定查询所用的表、视图、派生表或联接表。本讲稿第二十页，共六十七页【例5-3】假设Users表中的数据如下图所示。（1）查询所有版块的版块编号、版块名称、版主的编号、姓名以及电子邮箱。USE bbsDBGOSELECT SID,SName,UID,UName,UEmail FROM Section,UsersWHERE Users.UID=Section.SmasterI
10、DGO本讲稿第二十一页，共六十七页（2）使用内联接INNER JOIN 完成（1）的功能。SELECT SID,SName,UID,UName,UEmail FROM Section INNER JOIN UsersON UID=SmasterID本讲稿第二十二页，共六十七页5.2.4 使用使用WHERE子句子句 WHERE子句是条件子句，用于限定查询的内容。语法格式：语法格式：WHERE 查询的条件表达式查询的条件表达式查询的条件表达式:=NOT|()AND|OR NOT|(),.n 参数说明：参数说明：search_condition：查询的条件表达式本讲稿第二十三页，共六十七页1.比较表
11、达式使用比较表达式的一般形式为：表达式表达式 比较运算符比较运算符 表达式表达式表达式：可以是列名、常量、函数、变量、标量子查询，或者是由运算符或子查询连接的列名、常量和函数的任意组合。还可以包含 CASE 函数。比较运算符:=（等于）（大于）=（对于等于）（小于）=（小于等于）（不等于）!（不大于）!=10本讲稿第二十五页，共六十七页2逻辑表达式在Transact-SQL中可以使用的逻辑运算符有3个：NOT（逻辑反）、AND（逻辑与）、OR（逻辑或）逻辑运算符的优先顺序是 NOT、AND、OR。在比较表达式和逻辑表达式中有3种可能的取值：TRUE、FALSE或UNKNOWN。【例5-5】查询
12、表Section中点击率不低于100，并且帖子数量不为0的版块。SELECT*FROM Section WHERE SClickCount=100 AND STopicCount0本讲稿第二十六页，共六十七页3BETWEEN关键字使用BETWEEN关键字可以限定查寻范围，其语法形式如下：test_expression NOT BETWEEN begin_expression AND end_expression 参数说明：test_expression：被测试的表达式。begin_expression：指定取值范围的上限。end_expression：指定取值范围的下限。【例5-6】查询表Se
13、ction中点击率在50和100之间的版块。SELECT*FROM Section WHERE SClickCount BETWEEN 50 AND 100本讲稿第二十七页，共六十七页4IN关键字使用IN关键字可以测试给定的值是否与子查询或列表中的值相匹配。语法格式：test_expression NOT IN (subquery|expression ,.n )参数说明：test_expression：任何有效的SQL Server表达式。subquery：包含某列结果集的子查询。expression,.n：一个表达式列表，用来测试是否匹配。【例5-7】从Users表中查询生日不在3月、5月
14、和7月的论坛用户信息。SELECT*FROM Users WHERE MONTH(UBirthday)NOT IN(3,5,7)本讲稿第二十八页，共六十七页5LIKE关键字LIKE关键字用于将所给字符串与指定的的模式匹配。语法格式：match_expression NOT LIKE pattern参数说明：match_expression：任何字符串数据类型的有效 SQL Server 表达式。Pattern：指定match_expression 中的搜索模式，可以包含下列有效 SQL Server 通配符：%：可匹配任意类型和长度的字符串。_（下划线）：可匹配任何单个字符。本讲稿第二十九页，
15、共六十七页【例5-8】在Pubs数据库的 authors 表中查找所有区号为 415 的电话号码。USE pubsSELECT phoneFROM authorsWHERE phone LIKE 415%ORDER by au_lnameGO本讲稿第三十页，共六十七页【例5-9】在Pubs数据库的 authors 表中查找名字为 Cheryl 或 Sheryl 的作者。USE pubsSELECT au_lname,au_fname,phoneFROM authorsWHERE au_fname LIKE CSherylORDER BY au_lname ASC,au_fname ASCGO查
16、询结果如图5所示。本讲稿第三十一页，共六十七页【例5-10】在Pubs数据库的 authors 表中查找姓为 Carson、Carsen、Karson 或 Karsen 的作者。USE pubsSELECT au_lname,au_fname,phoneFROM authorsWHERE au_lname LIKE CKarseonORDER BY au_lname ASC,au_fname ASCGO查询结果如图所示。本讲稿第三十二页，共六十七页6NULL关键字 在WHERE子句中不能使用比较运算符对空值进行判断，只能使用IS NULL来确定一个给定的表达式是否为 NULL。其语法形式如下：
17、表达式 IS NOT NULL本讲稿第三十三页，共六十七页【例5-11】在Pubs数据库的 authors 表中查找所有预付款少于$5,000 或者预付款未知（或为 NULL）的书，返回它们的书号及预付款。USE pubsGOSELECT title_id,advanceFROM titlesWHERE advance 40000GO查询结果如图。本讲稿第三十八页，共六十七页5.2.7 使用使用ORDER BY子句子句 ORDER BY子句用于根据一个列或者多个列来排序查询结果。语法形式如下：ORDER BY 排序表达式 ASC|DESC ,.n 参数说明：排序表达式：指定要排序的列，可以是多
18、个字段，用“，”隔开。ASC：指定按递增顺序的值进行排序。DESC：指定按递减顺序的值进行排序。说明：说明：空值被视为最低的可能值。本讲稿第三十九页，共六十七页【例5-14】将发帖表Topic中的记录按点击率由低到高排序。USE bbsDBGOSELECT*FROM Topic ORDER BY TClickCount GO本讲稿第四十页，共六十七页5.2.8 使用使用COMPUTE子句子句 COMPUTE子句用于生成统计结果，放在查询结果的最后。语法格式：语法格式：COMPUTE 聚集函数（列名）对于SELECT查询的返回结果，计算指定的某个聚集函数的值。本讲稿第四十一页，共六十七页【例5-
19、15】查询Section表中所有版块的点击率及平均点击率。USE bbsDBSELECT SID,SName,SClickCount FROM Section COMPUTE AVG(SClickCount)GO查询结果如图 本讲稿第四十二页，共六十七页5.2.9 使用联合查询使用联合查询 联合查询是指将两个或两个以上的SELECT语句通过UNION运算符连接起来的查询，联合查询可以将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中所有查询的全部行。使用使用UNION组合两个查询结果集的两个基本规则是：组合两个查询结果集的两个基本规则是：所有查询中的列数和列的顺序必须相同。数据类型
20、必须兼容。本讲稿第四十三页，共六十七页语法格式：语法格式：查询语句1UNIONALL查询语句2参数说明：参数说明：ALL：在结果中包含所有的行，包括重复行。如果没有指定，则删除重复行。本讲稿第四十四页，共六十七页【例5-16】查询发帖表Topic和跟帖表Reply中所有帖子的发帖人编号和主题。USE bbsDBGOSELECT TUID AS 发帖人编号,TTopic AS 主题 FROM TopicUNION SELECT RUID,RTopic FROM ReplyGO查询结果如图。本讲稿第四十五页，共六十七页5.2.10 使用嵌套查询使用嵌套查询 在SQL语言中，一个SELECT-FRO
21、M-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询称为嵌套查询嵌套查询。嵌套查询中上层的查询块称为外层查询或父查询外层查询或父查询，下层查询块称为内层查询或子查询内层查询或子查询。SQL语言允许多层嵌套。嵌套查询主要用于复杂的查询中。在某些嵌套查询中WHERE之后还可以使用ANY和ALL两个关键字。本讲稿第四十六页，共六十七页【例5-17】查询年龄最小的论坛用户的用户编号、昵称、出生日期和电子邮箱。USE bbsDBGOSELECT UID,UName,UBirthday,UEmail FROM Users WHERE UBirth
22、day=ALL(SELECT UBirthday FROM Users)查询结果如图本讲稿第四十七页，共六十七页【例5-18】查询除年龄最小的论坛用户外的所有用户的编号、昵称、出生日期和电子邮箱。USE bbsDBGOSELECT UID,UName,UBirthday,UEmail FROM Users WHERE UBirthday ANY(SELECT UBirthday FROM Users)查询结果如图 本讲稿第四十八页，共六十七页【例5-19】SELECT语句综合应用：关于【问题5-1】的解答。SELECT UID,Uame,SUM(TClickCount)as TClickCou
23、nt_TotalFROM Users,TopicWHERE Users.UID=Topic.TUIDGROUP BY UID,UName HAVING SUM(TClickCount)1000ORDER BY TClickCount_Total DESC查询结果如图 本讲稿第四十九页，共六十七页5.3 使用使用INSERT语句插入数据语句插入数据 【问题5-2】在Pubs数据库中，从编号为1389的出版社新进一本business类书，书号为BU9876,书名为Creating Web Pages,价格为29.99元，如何使用SQL语句将此书加入Pubs数据库的图书表Titles中？可以使用可以
24、使用INSERT语句向表或视图中添加一行或多行数据。语句向表或视图中添加一行或多行数据。本讲稿第五十页，共六十七页语法格式：语法格式：INSERT INTO 表名表名（要插入数据的字段列表）（要插入数据的字段列表）VALUES(每每个字段相应的值个字段相应的值)说明：说明：l字段列表中字段的个数与值的个数必须相等；l要插入的字段值的类型必须与表定义相同；l字段列表如果是表中全部字段时，可以省略；本讲稿第五十一页，共六十七页【例5-20】将一行添加到 Pubs数据库的titles 表中，以指定书名、类型、出版商及价格的值：USE PubsGOINSERT INTO titles(title_id
25、,title,type,pub_id,price)VALUES(BU9876,Creating Web Pages,business,1389,29.99)本讲稿第五十二页，共六十七页5.4 使用使用UPDATE语句更新数据语句更新数据 【问题5-3】如何使用SQL语句将Pubs数据库的titles 表中书号为BU9876的书名修改为“网页制作”？可可以以使使用用UPDATE语语句句修修改改表表中中特特定定记记录录或或字字段段的的数据。数据。本讲稿第五十三页，共六十七页语法格式：语法格式：UPDATE 表名SET 列名1=表达式1，列名2=表达式2，WHERE 查询表达式。参数说明：参数说明：
26、WHERE：指定条件来限定所更新的行。若无WHERE子句，将会修改表中的每行数据。本讲稿第五十四页，共六十七页【例5-21】修改Pubs数据库的titles 表中书号为BU9876的书名为“网页制作”。USE PubsGOUPDATE titlesSET title=网页制作WHERE title_id=BU9876本讲稿第五十五页，共六十七页5.5 使用使用DELETE语句删除数据语句删除数据DELETE语句用于删除表中记录。本讲稿第五十六页，共六十七页5.5.1 使用使用DELETE语句语句语法格式：语法格式：DELETE FROM 表名表名 WHERE 查询表达式查询表达式 参数说明：参
27、数说明：WHERE：指定条件来限定所删除的行。若无WHERE子句，将删除表中所有行。本讲稿第五十七页，共六十七页【例5-22】删除pubs数据库titles表中书号为BU9876的记录。USE pubsGODELETE FROM titlesWHERE title_id=BU9876【例5-23】删除pubs数据库authors表中 au_lname 是 McBadden 的所有行。USE pubsGODELETE FROM authorsWHERE au_lname=McBadden本讲稿第五十八页，共六十七页5.5.2 使用使用TRUNCATE语句语句清除表中的所有数据、只留下表的定义，可
28、以使用TRUNCATE语句。与DELETE语句相比，TRUNCATE通常速度快，因为TRUNCATE是不记录日志的操作。语法格式：TRUNCATE TABLE name 参数name指定要删除全部行的表的名称。【例5-24】清空跟帖表Reply中的数据。TRUNCATE TABLE Reply本讲稿第五十九页，共六十七页5.6 综合实例综合实例【实例说明】对在4.7中创建的COLLEGE数据库，完成以下对表中数据的操作：（1）查询“06182”班总分在300分以上的学生的学号，姓名和总成绩，结果按总成绩由高到低排序。（设学号字段S_number的前5位为班号）。（2）根据Student表产生一
29、个名为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）
30、使用SELECT语句进行查询操作,需要用到GROUP BY、HAVING、ORDER BY等子句。将查询结果存入新表，还需要使用INTO子句。（2）插入单条记录，若提供的字段值去不全，需要列出各字段值所对应的字段名，注意要一一对应。（3）成批插入记录的数据来源为一个查询。（4）可以成批更新和删除符合条件的记录。本讲稿第六十二页，共六十七页【关键代码（或技术）】（1）SELECT Student.S_number,S_name AS 姓名,SUM(Score)AS 总分FROM Student,ScoreWHERE Student.S_number=Score.S_number and LEFT
31、(Student.S_number,5)=06182GROUP BY Student.S_number,S_nameHAVING SUM(Score)=100ORDER BY SUM(Score)（2）SELECT*INTO Student_1 FROM Student WHERE Polity=党员（3）INSERT INTO Student_1(S_number,S_name,Sex,Birthdate,polity)VALUES(0618209,刘斯玉,女,1987-10-14,群众)（4）INSERT INTO Student_1 SELECT *FROM Student WHERE
32、性别=女（5）UPDATE Score SET Score=Score+10 WHERE Score60（6）DELETE FROM Student_1 WHERE Polity=群众本讲稿第六十三页，共六十七页5.7 上机实训上机实训实训目的1掌握使用企业管理器操作表中数据的方法；2掌握使用SELECT语句查询数据的方法；3掌握使用INSERT语句插入数据的方法；4掌握使用UPDATE语句更新数据的方法；5掌握使用DELETE语句删除数据的方法。本讲稿第六十四页，共六十七页实训要求1所有任务都在查询分析器中使用SQL语句实现。2注意观察各条SQL语句的执行结果，验证与题目要求是否相符。3将所
33、有SQL语句以.sql文件形式保存。本讲稿第六十五页，共六十七页实训内容SQL Server 2000自带了两个实例数据库，供学习者参考之用。其中一个是pubs，它存储了一个图书出版公司的基本情况，为了不改变原来的数据（其实改变也没有关系，只是要养成良好的习惯），要求大家首先将pubs下的工种情况表jobs复制为jobs2，然后直接对jobs2进行操作。本讲稿第六十六页，共六十七页本章小结本章小结在SQL Server2000中，既可以使用企业管理器对表中数据进行查询、添加、修改或删除操作，也可以在查询分析器中通过执行SELECT、INSERT、UPDATE和DELETE等SQL语句完成这些操作。SELECT语句可以精确地对数据库进行查找；使用INSERT语句向表中添加新数据行；使用UPDATE语句修改表中已有的数据；使用DELETE语句删除表中已有的数据，可以使用TRUNCATE语句清表中的所有数据。本讲稿第六十七页，共六十七页