第4章数据库数据操作

1、数据库系统开发与应用数据库系统开发与应用 主讲：凃云杰主讲：凃云杰 电子工业出版社电子工业出版社 郑阿奇郑阿奇 主编主编 第第 四章四章 数据库数据操作数据库数据操作 教学目标： 了解：索引的概念 理解：创建索引的必要性 掌握：数据记录的增删改操作、索引的创建。 重点难点： 教学重点：数据记录的增删改操作、创建索引的方法。 教学难点：用命令方式创建索引的方法、数据记录的增 删改操作 1 1插入数据记录插入数据记录 在“对象资源管理器”中展开“数据库jxgl” ，用鼠标右键单击要进行操作的表teacher，在弹 出的快捷菜单上选择“编辑前200行”菜单项， 打开“表数据”窗口。在此窗口中，表中的

2、记录 将按行显示，每个记录占一行。 4.1 4.1 使用使用SSMSSSMS操作表中的数据操作表中的数据 2 2修改数据记录修改数据记录 如果信息有变动，定位到所在的一行的相应位 置进行修改，修改之后将光标移到下一行保存修 改的内容。 3 3删除数据记录删除数据记录 在表 的“表数据”窗口中定位需被删除的记录 行，单击该行最前面的黑色箭头处选择全行，用 鼠标右键单击，选择“删除(D)”菜单项。选择“ 删除(D)”后，将出现一个确认对话框，单击“是 ”按钮将删除所选择的记录行。 1 1使用使用INSERTINSERT语句输入数据语句输入数据 使用T-SQL的INSERT INTO语句，可以向表中

3、输入数据。基本语法 如下： INSERT INTO column list VALUES ( values list | select statement) 4.2 使用使用T-SQL操作表中的数据操作表中的数据 INSERT语句输入数据时应注意以下问题： 当向表中所有列输入数据时，column list子句可以缺省。 若缺省该子句，数据清单中数据的顺序一定与表中列的顺 序一致。 当要输入数据的表中包含有标识列、允许为空的列、设置 默认值的列时，可以不输入该列的值。 当向字符型、日期型列输入数据时，要以单引号引起来。 （1）向表中输入一行数据 【例4-1】向表major中插入一条合法记录。 U

4、SE jxgl GO INSERT INTO major VALUES(m05,电子商务) 练习：练习： 向teacher表中输入一条元组，元组相应字段值如下： (611,张力军,女,1975-7-7,副教授,教研室主任, null海拉尔学府路号,党员,计算机科学与技术学院) （2）输入一行中的部分列 【例4-2】向表teacher插入一条合法记录。代码如下： USE JXGL GO INSERT INTO teacher (tno,tname, tsex,professionaltitle) -列名清单只列出需要输入数据的列

5、VALUES(t09,董卫刚, 男, 讲师) -输入清单中列出的部分列的数据 GO （3）一次向表中输入多行记录 【例4-3】 向major表输入2条合法记录。 USE JXGL GO INSERT INTO major VALUES(m06,远程技术) ,(m07,传感技术) 【例4-4】建立一张新表js(bh char(4),xm char(8)，将 teacher表的所有记录的教师号和姓名插入到js中。 CREATE TABLE js (bh CHAR(4), xm CHAR(8) GO INSERT INTO js SELECT tno,tname FROM teacher 对每一个系

6、，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15), /* 系名系名*/ Avg_age numeric(5,2) /*学生平均年龄学生平均年龄*/ 练习练习 第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept； 2使用DELETE语句删除表中的数据行 表中没有意义的数据，使用DELETE 语句以行为单位删除。基本语 法如下： DELETE FROM table_name |

7、view_name WHERE search_condition 【例4-5】删除表major中majorno为m05的行。 代码如下： DELETE FROM major WHERE majorno=m05 GO 练习：删除teacher表中张力军老师的记录。 3使用UPDATE语句更新表中的数据 使用UPDATE语句可以更新表的一行、一行中的某些列，也可以更 新表中所有行或所有行的某些列。基本语法如下： UPDATE table_name SET column_name=express | NULL | DEFAULT ,n WHERE logical_express 其中：其中： SET

8、子句：指定要被更新的列及其新的值。 WHERE子句：指定修改条件，只有满足条件的那些行的列 值才被修改为SET子句指定的数据。 express：新数据可以是常量或表达式。 （1）更新表中所有行的某一列。 【例4-6】将表student_teacher_course中所有同学的 成绩提高5分。 USE JXGL GO UPDATE student_teacher_course SET grade=grade+5 GO （2）更新表中某些行的某一列。 【例4-7】将表student_teacher_course中学号为 2010010001的同学所有课程的成绩提高5分。代 码如下： USE JXG

9、L GO UPDATE student_teacher_course SET grade=grade+5 WHERE sno=2010010001 GO （3）更新表中多个行的多个列。 【例4-8】将表major_course中专业基础课的学分提 高0.5分，考试形式改为闭卷。代码如下： USE JXGL GO UPDATE major_course SET credit= credit+0.5,examinationform=闭卷 WHERE type=专业基础 GO TRUNCATE TABLE语句语句 使用TRUNCATE TABLE语句将删除指定表中的所有数据，因此也 称为清除表数据语

10、句。语法格式如下： v TRUNCATE TABLE 说明：使用TRUNCATE TABLE语句删除了指定表中的所有行，但表的 结构及其列、约束、索引等保持不变，而新行标识所用的计数值重置为 该列的初始值。如果想保留标识计数值，则要使用DELETE语句。 v TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同， 二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且 使用的系统和事务日志资源少。 设有三个关系： C（CNO，CNAME，PCNO） SC（SNO，CNO，SCORE） S（SNO，SNAME，AGE，SEX） 其中：C为课程表关

11、系，对应的属性分别是课号、课程名和预选课号； SC为学生选课表关系，对应的属性分别是学号、课号和成绩；S表示学 生，它的各属性依次为学号、姓名、年龄和性别。 用SQL语言写出： （1）向S表中插入一条新元组（2011010001，张力，19， 男）。 （2）将学号为2011010001的学生年龄改为20。 （3）删除学号为2011010001的学生记录。 （4）建立一反映各学生总成绩的视图S_ZCJ，视图包括学号、姓名 、总成绩三列。 1.索引的概念 索引是对表或视图中的一列或多个列的值进行排序的一种物理结 构，它由该表或视图中的一列或若干列值，以及这些列值的记录在数 据表中存储位置的物理地址

12、所组成。 （1）聚集索引（CLUSTERED INDEX，也称聚类索引、簇集索引）。 聚集索引是表数据的物理排序。 （2）非聚集索引（NONCLUSTERED INDEX，也称非聚类索引、非簇 集索引）。非聚集索引具有与表数据完全分离的存储结构。 4.3索引索引 索引的优缺点索引的优缺点 (1)索引的优点 v 唯一性索引可保证表中每一行数据的唯一性 v 加快数据查询速度，尤其是数据量很大时。（最主要原因） v 加速表和表之间连接速度，特别是在实现数据的参照完整性方面特别有意义 。 v 在使用分组和排序子句进行数据查询时，可以显著减少分组和排序时间。 v 通过使用索引，可以在查询过程中使用优化隐

13、藏器，提高系统性能。 (2)索引的缺点 v 索引需要占用数据表以外的物理存储空间 v 创建索引和维护索引要耗费时间，且这个时间随着数据量的增加而增加。 v 当表中的数据增加、删除和修改时，索引也要动态地更改或重建，降低数 据的更新速度。 索引的应用原则索引的应用原则 v 主键列一般需要建立索引，因为通常查询和存取表都是通过主键进行 的。 v 外键列一般需要建立索引，以便保证进行表连接操作时的快速执行。 v 经常查询的字段最好建立索引。索引中列的组合取决于哪些字段经常 一起作为联合查询条件。 v 数据量小的表、很少或从来不在查询中作为条件引用的列，只有很 少取值的列，定义为text、image和

14、bit数据类型的列不要建立索引。 v 当更新或插入操作的性能要求比查询操作的性能要求更重要时，最好 不建立索引。 2使用使用T-SQL语句创建索引语句创建索引 可以使用T-SQL的CREATE INDEX语句创建索引。 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITH ,.n ON filegroup ; 其中： v UNIQUE：指明创建的是唯一索引。 v CLUSTERED | NONCLUSTERED：指明创建的索引为聚集索引还是非聚

15、集 索引。系统默认创建的索引为非聚集索引。 v ASC | DESC：指定特定的索引列的排序方式，默认为升序（ASC）。 v Column：指定被索引的列。如果使用两个或两个以上的列组成一个索引，则 称为复合索引。 【例4-9】在表student上创建非聚集索引idstu，该索引基于 “班级”和“出生日期”两个字段，并按照“班级”升序 、“出生日期”降序创建。 Create nonclustered index classbir On student(classno,birthday desc) 【例4-10】为teacher创建唯一索引，该索引基于身份证号 identitycard字段 Cr

16、eate unique index teaid On teacher(identitycard desc) go 3使用系统存储过程查看索引信息使用系统存储过程查看索引信息 通过执行系统存储过程sp_helpindex或sp_help，可以查 看数据 表的索引信息。基本语法如下： EXEC sp_helpindex | sp_help table_name 【例4-11】查看表student中的索引信息。代码如下： sp_helpindex student GO sp_helpindex student GO 4使用系统存储过程更改索引名称使用系统存储过程更改索引名称 使用系统存储过程sp_r

17、ename可以更改索引的名称，基本 语法如下： EXEC sp_rename old_table_ name, new_table_ name 【例4-12】将索引classbir的名称修改为idx_class。代码如下： sp_rename student.classbir, student.idx_class GO 5使用使用T-SQL语句删除索引语句删除索引 当不再需要某个索引时，要及时把索引从数据库中删除 掉。对因为设置PRIMARY KEY约束或UNIQUE约束时由 系统创建的索引，可以通过删除约束的方法删除索引； 对于用户手工创建的其他索引，可以使用DROP INDEX 语句删除，

18、基本语法如下： DROP INDEX table_name.index_name ,n 【例4-13】将表teacher中的索引teaid删除。代码如下： DROP INDEX teacher. teaid GO 本次课讲解了数据的增删改的操作与索引的相关操 作。 总结总结 作业：作业： 字段名字段名数据类型数据类型字段说明字段说明其他其他 AU_IDint作者作者ID号号主键主键 AU_NAMEvarchar(80)作者姓名作者姓名 AU_CITY varchar(20)作者所在城市作者所在城市 TELvarchar(20)作者电话号码作者电话号码 假设有三张表：假设有三张表：TITLES表、表、AUTHORS表和表和PUBLISHERS 表，表， 分别图书信息、作者信息和出版社