第4章_表数据操作wl

第4章表数据操作 本章我们学习使用T SQL语句创建和操作数据库和表 与图形用户界面方式相比 用T SQL命令方式更为灵活 本章的学习目标 操纵数据需要解决的问题使用INSERT语句插入数据使用UPDATE语句更新数据使用DELETE语句删除数据理解数据加密的方式和特点 表创建之后 只是一个空表 如何向表中添加数据 如果表中已经有了数据 不合适或不正确 怎么办 如果表中的数据不再需要了 怎么办 这些都是数据操纵问题 用户可以使用INSERT UPDATE DELETE等语句来解决这些问题 4 1概述 与创建数据库和表一样 把不直接使用T SQL语句对表数据的操作称为界面操作表数据 界面操作表数据主要在SQLServerManagementStudio中进行 在选择了 打开表 后 将进入操作所选择的表数据窗口 在此窗口中 表中的记录按行显示 每个记录占一行 在此界面中 可向表中插入记录 也可删除和修改记录 4 2界面操作表数据 4 2 1插入记录 4 2 2删除记录 4 2 3修改记录 4 3数据操纵语言 DML 数据操纵语言主要是用于操纵表 视图中数据的语句 当我们创建表对象之后 初始状态时该表是空的 没有任何数据 这时需要使用INSERT语句向表中添加数据 如何检索表中数据呢 可以使用我们前面介绍的SELECT语句 如果表中数据不正确的 那么可以使用UPDATE语句进行更新 也可以使用DELETE语句删除表中的数据 DML就是指处理数据变更的3种SQL语句 INSERT UPDATE和DELETE 对表数据的插入 修改和删除还可以通过T SQL语句来进行 与界面操作表数据相比 通过T SQL语句操作表数据更为灵活 功能更为强大 4 3命令操作表数据 4 3 1INSERT语句 INSERT语句用于向已经存在的表中插入新的数据 我们需要声明向哪个表中插入数据 向那一列插入数据 以及插入什么数据 INSERT语句的语法格式为 INSERT INTO 列1 列2 VALUES 表达式1 表达式2 INSERT语句插入一条记录insertintosu1 studentvalues 1234567890 abc 女 计算机科学 1991 10 26 500 null 例4 35 向course表插入一条记录 课程号为10 课程名为network 学分为4 任课教师为Sophie T SQL语句为 INSERTINTOcourseVALUES 10 network 4 Sophie 例4 36 向student表插入一条学生记录 其中学号为20090101 学生姓名为张静 T SQL语句为 INSERTINTOstudent sno sname VALUES 20090101 N 张静 由于只插入student表中的两列的数据 因此 使用列表需要指明所要插入的列 执行完成后 查看student表 可以看到课程已经被添加 创建books表 2020年4月5日 第12页 一般的插入数据示例 2020年4月5日 第13页 向表中插入空值 2020年4月5日 第14页 虽然DEFAULE和NULL都可以为某个列提供空值 但是这两个关键字的作用是不同的 NULL关键字仅是向允许为空的列提供空值 DEFAULT关键字则为指定的列提供一个默认值 如果列上没有定义默认值或者其他可以自动获取数据的类型 这两个关键字的作用才是相同的 注意 向表中插入部分列的数据 2020年4月5日 第16页 使用DEFAULTVALUES子句 2020年4月5日 第17页 如果表中所有的列都允许为空或者定义有默认值或者定义了其他可以获取数据的特征 可以使用DEFAULTVALUES子句向表中提供一行全是默认值的数据 使用INSERT SELECT形式 2020年4月5日 第19页 在INSERT语句中使用SELECT时 它们引用的表既可以是相同的 也可以是不同的 要插入的数据表必须已经存在 要插入数据的表必须和SELECT语句的结果集兼容 应该注意下面几点 使用SELECT INTO语句 2020年4月5日 第21页 包含了图书数据的文本文件 2020年4月5日 第22页 使用BULKINSERT语句 2020年4月5日 第23页 可以使用BULKINSERT语句按照用户指定的格式把大量数据插入到数据库的表中 这是批量加载数据的一种方式 FIELDTERMINATOR用于指定字段之间的分隔符 ROWTERMINATOR用于指定行之间的分隔符 使用insertselect语句usetestgocreatetableag snochar 10 primarykey snamevarchar 8 pjftinyint insertintoagselectstudent sno sname AVG grade fromstudent scwherestudent sno sc snogroupbystudent sno sname Insertinto语句与selectinto语句的区别 SELECTINTO语句与INSERTSELECT语句不同的是 它可以将其他数据源中的任何查询结果或导入的数据都添加到数据库的新表当中 这种方法用于从不同的数据源中找到不同的数据集 放在通过该语句一开始创建的临时表中 若用该表进行查询比在多表或多数据库查询执行起来更为容易 方便 使用SELECTINTO语句时要注意以下原则 必须保证SELECTINTO语句中指定创建的新表在数据库是唯一的 可以创建本地临时表或全局临时表 本地临时表的创建需要在表名前加一个 符号 全局临时表的创建需要在表名前加上两个 符号 临时表分为 本地临时表 仅限于当前访问者访问 创建方法去如下 createtable TableName 表结构 储存于数据库tempdb内 硬盘 当前用户断开连接 自动删除如果使用中不断开连接 且不需要该临时表请执行 droptable TableName全局临时表 所有访问用户访问 创建方法如下 createtable TableName 表结构 储存于数据库tempdb内 当所有访问用户断开连接 自动删除删除语句 droptable TableName SELECTINTO语句的语法格式如下 SELECTselect listINTOnew tableFROMtable listWHEREsearch condition例 3 5 统计每个需要补考学生的未通过课程的门数 将其保存到临时表 stuexam表 显示系名 学号 姓名 未通过门数 并按系名排序 usexscj2005goselectsdept student sno sname COUNT cno as未通过门数into stuexamfromstudent scwherestudent sno sc snoandgrade 60groupbysdept student sno sname只对当前连接是可见的selectsdept student sno sname COUNT cno as未通过门数into stuexamfromstudent scwherestudent sno sc snoandgrade 60groupbysdept student sno sname对所有连接是可见的 直到SQLServer断开连接为止 usetestgocreatetablett aint select into ttlinshifromtt 以peter身份登录 test库下的用户 通过查询可以看到select from ttlinshi以u1身份登录 test库下的用户 通过查询可以看到select from ttlinshicreatelogintomwithpassword 123456 useabcgocreateusertom以tom登录 abc库下的用户 后可以看到结果select from ttlinshi 对所有用户 任何不同库下的用户 的连接是可见的 不管是否有权限 直到SQLServer断开连接为止 ttlinshi存储在tempdb系统数据库中usetempdbselect fromsysobjects 在当前会话连接没有断开时 可以查到本地临时表 ttlinshi 在当前会话连接断开后 ttlinshi表已自动删除 所以查不到本地临时表 ttlinshi 而只看到了 ttlinshi 4 3 2UPDATE语句 如果表中的数据不正确或者已经变化 可以使用UPDATE语句更新这些不恰当的数据 插入新数据和更新已经存在的数据之间的主要差别在于需要指明要更改哪些记录 一般需要用WHERE子句来指明需要更新的记录所满足的条件 UPDATE语句的语法格式为 UPDATESET列1 列2 FROM表名 WHERE 例4 37 将course表中的课程名称为network的记录项更改为课程名称为database T SQL语句为 UPDATEcourseSETcname N database WHEREcname N network 执行完成后 使用SELECT语句查询course表可以看到 对应纪录已经被修改 三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句 修改数据 续 例4 将学生2008056101的年龄增加一岁 updatestudentsetsbirth sbirth 365 sname 王远东 wheresno 2008056101 1 修改某一个元组的值 例5 将所有学生的年龄增加1岁 updatestudentsetsbirth sbirth 365补充 为少数民族学生增加10分updatestudentsetscomegrade scomegrade 10wheresnation 1 2 修改多个元组的值 例6 将信息管理系所有学生的年龄增加1岁 updatestudentsetsbirth sbirth 365wheresdept 信息管理 2 修改多个元组的值 续 例7 将计算机科学系全体学生的成绩置零 UPDATESC2SETGrade 0WHERE 计算机科学 SELECTSdeptFROMStudent2WHEREStudent2 Sno SC2 Sno 3 带子查询的修改语句 例7 将计算机科学系全体学生的成绩置零 updatescsetgrade 0wheresnoin selectsnofromstudentwheresdept 计算机科学 3 带子查询的修改语句 4 3 3DELETE语句 使用DELETE语句可以删除表中的数据 一般地 如果在DELETE语句中没有删除条件 那么将删除表中的所有数据 需要注意的是 DELETE语句与DROP语句不同 DELETE语句删除表中的数据 但是该表依然存在 而DROP语句则删除了表 表中的数据自然也不存在了 DELETE语句的语法非常容易 只需要简单指定从哪一个表中删除了记录 如果需要 则用WHERE子句指定要删除哪些记录 格式为 DELETEFROM WHERE条件 例4 38 将student表中学生姓名为张静的记录删除 T SQL语句为 DELETEFROMstudentWHEREsname N 张静 执行完成后 使用SELECT语句查询student表可以看到 对应纪录已经被删除 DELETEFROM WHERE 功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组 删除数据 1 Delete删除三种方式删除某一个元组的值删除多个元组的值带子查询的删除语句 删除数据 续 例8 删除学号为2009015128的学生记录 DELETEFROMStudentWHERESno 2009015128 删除某一个元组的值 例9 删除2号课程的所有选课记录 DELETEFROMSC WHERECno 1002 例10 删除所有的学生选课记录 DELETEFROMSC 删除多个元组的值 例11 删除计算机科学系所有学生的选课记录 DELETEFROMSCWHERE 计算机科学 SELETESdeptFROMStudentWHEREStudent Sno SC Sno 带子查询的删除语句 2 使用trancatetable语句删除表数据语法格式 Trancatetable表名 使用delete及TRUNCATETABLE删除了指定表中的所有行 但表的结构及其列 约束 索引等保持不变 而新行标识所用的计数值重置为该列的初始值 如果想保留标识计数值 则要使用DELETE语句 TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同 二者均删除表中的全部行 但TRUNCATETABLE比DELETE速度快 且使用的系统和事务日志资源少 DELETE语句每次删除一行