第4章数据库数据操作

数据库系统开发与应用主讲：凃云杰电子工业出版社郑阿奇主编第

四章数据库数据操作教学目标：了解：索引的概念理解：创建索引的必要性掌握：数据记录的增删改操作、索引的创建。重点难点：教学重点：数据记录的增删改操作、创建索引的方法。教学难点：用命令方式创建索引的方法、数据记录的增删改操作1．插入数据记录在“对象资源管理器”中展开“数据库jxgl”，用鼠标右键单击要进行操作的表teacher，在弹出的快捷菜单上选择“编辑前200行”菜单项，打开“表数据”窗口。在此窗口中，表中的记录将按行显示，每个记录占一行。

4.1使用SSMS操作表中的数据2．修改数据记录如果信息有变动，定位到所在的一行的相应位置进行修改，修改之后将光标移到下一行保存修改的内容。3．删除数据记录在表

的“表数据”窗口中定位需被删除的记录行，单击该行最前面的黑色箭头处选择全行，用鼠标右键单击，选择“删除(D)”菜单项。选择“删除(D)”后，将出现一个确认对话框，单击“是”按钮将删除所选择的记录行。1．使用INSERT语句输入数据

使用T-SQL的INSERTINTO语句，可以向表中输入数据。基本语法如下：INSERT[INTO]<table_name|view_name>[columnlist]VALUES(valueslist|selectstatement)4.2使用T-SQL操作表中的数据INSERT语句输入数据时应注意以下问题：⑴当向表中所有列输入数据时，columnlist子句可以缺省。若缺省该子句，数据清单中数据的顺序一定与表中列的顺序一致。⑵当要输入数据的表中包含有标识列、允许为空的列、设置默认值的列时，可以不输入该列的值。⑶当向字符型、日期型列输入数据时，要以单引号引起来。（1）向表中输入一行数据【例4-1】向表major中插入一条合法记录。USEjxglGOINSERTINTOmajorVALUES('m05','电子商务')练习：

向teacher表中输入一条元组，元组相应字段值如下：('611','张力军','女','1975-7-7','副教授','教研室主任',null,,'海拉尔学府路号','党员','zhanglijun@163.com',,'计算机科学与技术学院')（2）输入一行中的部分列【例4-2】向表teacher插入一条合法记录。代码如下：USEJXGLGOINSERTINTOteacher(tno,tname,tsex,professionaltitle)

--列名清单只列出需要输入数据的列VALUES('t09','董卫刚','男','讲师')

--输入清单中列出的部分列的数据GO（3）一次向表中输入多行记录【例4-3】向major表输入2条合法记录。USEJXGLGOINSERTINTOmajorVALUES('m06','远程技术'),('m07','传感技术')【例4-4】建立一张新表js(bhchar(4),xmchar(8))，将teacher表的所有记录的教师号和姓名插入到js中。

CREATETABLEjs(bhCHAR(4),xmCHAR(8))GOINSERTINTOjsSELECTtno,tnameFROMteacher对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREATETABLEDept_age(SdeptCHAR(15), /*系名*/Avg_agenumeric(5,2)) /*学生平均年龄*/练习第二步：插入数据

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2．使用DELETE语句删除表中的数据行表中没有意义的数据，使用DELETE语句以行为单位删除。基本语法如下：DELETE[FROM]table_name|view_nameWHEREsearch_condition【例4-5】删除表major中majorno为‘m05’的行。代码如下：DELETEFROMmajorWHEREmajorno='m05'GO练习：删除teacher表中张力军老师的记录。3．使用UPDATE语句更新表中的数据使用UPDATE语句可以更新表的一行、一行中的某些列，也可以更新表中所有行或所有行的某些列。基本语法如下：UPDATEtable_nameSETcolumn_name=express|NULL|DEFAULT[,…n][WHERElogical_express]其中：SET子句：指定要被更新的列及其新的值。WHERE子句：指定修改条件，只有满足条件的那些行的列值才被修改为SET子句指定的数据。express：新数据可以是常量或表达式。（1）更新表中所有行的某一列。【例4-6】将表student_teacher_course中所有同学的成绩提高5分。USEJXGLGOUPDATEstudent_teacher_courseSETgrade=grade+5GO（2）更新表中某些行的某一列。【例4-7】将表student_teacher_course中学号为‘2010010001’的同学所有课程的成绩提高5分。代码如下：USEJXGLGOUPDATEstudent_teacher_courseSETgrade=grade+5WHEREsno='2010010001'GO（3）更新表中多个行的多个列。【例4-8】将表major_course中专业基础课的学分提高0.5分，考试形式改为闭卷。代码如下：USEJXGLGOUPDATEmajor_courseSETcredit=credit+0.5,examinationform='闭卷'WHEREtype='专业基础'GOTRUNCATETABLE语句使用TRUNCATETABLE语句将删除指定表中的所有数据，因此也称为清除表数据语句。语法格式如下：TRUNCATETABLE<表名>说明：使用TRUNCATETABLE语句删除了指定表中的所有行，但表的结构及其列、约束、索引等保持不变，而新行标识所用的计数值重置为该列的初始值。如果想保留标识计数值，则要使用DELETE语句。TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同，二者均删除表中的全部行。但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少。设有三个关系：C（CNO，CNAME，PCNO）SC（SNO，CNO，SCORE）S（SNO，SNAME，AGE，SEX）其中：C为课程表关系，对应的属性分别是课号、课程名和预选课号；SC为学生选课表关系，对应的属性分别是学号、课号和成绩；S表示学生，它的各属性依次为学号、姓名、年龄和性别。用SQL语言写出：（1）向S表中插入一条新元组（‘2011010001’，‘张力’，19，‘男’）。（2）将学号为‘2011010001’的学生年龄改为20。（3）删除学号为‘2011010001’的学生记录。（4）建立一反映各学生总成绩的视图S_ZCJ，视图包括学号、姓名、总成绩三列。1.索引的概念

索引是对表或视图中的一列或多个列的值进行排序的一种物理结构，它由该表或视图中的一列或若干列值，以及这些列值的记录在数据表中存储位置的物理地址所组成。

（1）聚集索引（CLUSTEREDINDEX，也称聚类索引、簇集索引）。聚集索引是表数据的物理排序。

（2）非聚集索引（NONCLUSTEREDINDEX，也称非聚类索引、非簇集索引）。非聚集索引具有与表数据完全分离的存储结构。

4.3索引索引的优缺点(1)索引的优点唯一性索引可保证表中每一行数据的唯一性加快数据查询速度，尤其是数据量很大时。（最主要原因）加速表和表之间连接速度，特别是在实现数据的参照完整性方面特别有意义。在使用分组和排序子句进行数据查询时，可以显著减少分组和排序时间。通过使用索引，可以在查询过程中使用优化隐藏器，提高系统性能。(2)索引的缺点索引需要占用数据表以外的物理存储空间创建索引和维护索引要耗费时间，且这个时间随着数据量的增加而增加。当表中的数据增加、删除和修改时，索引也要动态地更改或重建，降低数据的更新速度。索引的应用原则主键列一般需要建立索引，因为通常查询和存取表都是通过主键进行的。外键列一般需要建立索引，以便保证进行表连接操作时的快速执行。经常查询的字段最好建立索引。索引中列的组合取决于哪些字段经常一起作为联合查询条件。数据量小的表、很少或从来不在查询中作为条件引用的列，只有很少取值的列，定义为text、image和bit数据类型的列不要建立索引。当更新或插入操作的性能要求比查询操作的性能要求更重要时，最好不建立索引。2．使用T-SQL语句创建索引可以使用T-SQL的CREATEINDEX语句创建索引。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])[WITH<index_option>[,...n]][ONfilegroup][;]其中：UNIQUE：指明创建的是唯一索引。CLUSTERED|NONCLUSTERED：指明创建的索引为聚集索引还是非聚集索引。系统默认创建的索引为非聚集索引。ASC|DESC：指定特定的索引列的排序方式，默认为升序（ASC）。Column：指定被索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。【例4-9】在表student上创建非聚集索引idstu，该索引基于“班级”和“出生日期”两个字段，并按照“班级”升序、“出生日期”降序创建。CreatenonclusteredindexclassbirOnstudent(classno,birthdaydesc)【例4-10】为teacher创建唯一索引，该索引基于身份证号identitycard字段CreateuniqueindexteaidOnteacher(identitycarddesc)go3．使用系统存储过程查看索引信息通过执行系统存储过程sp_helpindex或sp_help，可以查看数据表的索引信息。基本语法如下：

[EXEC]{sp_helpindex|sp_help}table_name【例4-11】查看表student中的索引信息。代码如下：sp_helpindexstudentGOsp_helpindex'student'GO4．使用系统存储过程更改索引名称使用系统存储过程sp_rename可以更改索引的名称，基本语法如下：

[EXEC]sp_rename'old_table_name','new_table_name'【例4-12】将索引classbir的名称修改为idx_class。代码如下：

sp_rename'student.classbir','student.idx_class'GO5．使用T-SQL语句删除索引当不再需要某个索引时，要及时把索引从数据库中删除掉。对因为设置PRIMARYKEY约束或UNIQUE约束时由系统创建的索引，可以通过删除约束的方法删除索引；对于用户手工创建的其他索引，可以使用DROPINDEX语句删除，基本语法如下：

DROPINDEXtable_name.index_name[,…n]【例4-13】将表teacher中的索引teaid删除。代码如下：DROPINDEXteacher.teaidGO本次课讲解了数据的增删改的操作与索引的相关操作。总结作业：字段名数据类型字段说明其他AU_IDint作者ID号主键AU_NAMEvarchar(80)作者姓名

AU_CITYvarchar(20)作者所在城市

TELvarchar(20)作者电话号码

假设有三张表：TITLES表、AUTHORS表和PUBLISHERS