SQL数据库第5章 表数据操作和数据完整性

第5章表数据操作和数据完整性•创建数据库和表以后，需要对表中的数据进行操作，包括插入、删除和修改。有两种方法：一是通过SQLServer2005管理器，一是通过T-SQL语句。•5.1界面操作表数据•一、插入记录添加记录在表尾•二、删除记录•三、修改记录•5.2命令操作表数据（T-SQL语句）•一、通过INSERT语句插入表数据向表中插入数据可以使用INSERT语句。INSERT最基本的格式为：INSERT[into]表名(column1,column2,…)VALUES（constant1,constant2,…）该语句的功能是向指定的表中加入由VALUES指定的各列值的行例：向XSCJ数据库的表XS中插入一行：001112罗林林计算机男1/30/19800:0:040可以使用如下的T-SQL语句：•USExscj•INSERTINTOXSCJ.dbo.XS•VALUES(‘001112’,’罗林林’,‘计算机’,0,‘1/30/19800:0:0’,40,null)•Go•例：用如下语句建立表test•CREATETABLEtest•(xmchar(20)notnull,•zyvarchar(30)default(‘计算机’),•njtinyintnotnull•)•用INSERT语句向表中插入一条记录：•INSERTINTOtest(xm,nj)•values(‘王林’,3)•例：•创建表XS2•CREATETABLEXS2•(numchar(10)notnull,•namechar(10)notnull,•specialtychar(10)null•)•可用如下的INSERT语句向XS2中插入数据：•Insertintoxs2•selectxh,xm,zhy•fromxs1•Wherezhy=‘生工’•查询结果：select*fromxs2•二、使用DELETE或TRUNCATETABLE语句删除数据•1.使用delete语句删除数据•delete语句的功能是从表中删除行，其基本语法格式为：•delete[from]•{table_name׀view_name}•[wheresearch_condition]•Example:将XSCJ数据库的表XS中总学分小于39的行删除：•USEXSCJ•DELETEFROMXS•WHERE总学分39•go•2.使用TRUNCATETABLE语句删除表数据•使用该语句将删除指定表中的所有数据，语法格式为：•TRuNCATETABLEname•3.使用UPDATE语句修改数据•UPDATE语句可以用来修改表中的数据行，其最基本语法格式如下：•UPDATE{table_name׀view_name}•SETcolumn_name={expression׀DEFAULT׀NULL}[,…n]•[WHEREsearch_condition]•例：将XSCJ数据库的XS表中学号为001110的学生的备注列值改为“三好生”：•USEXSCJ•UPDATEXS•SET备注=‘三好生’•WHERE学号=‘001110’•GO•例将XS表中的所有学生的总学分都增加10个学分。•USEXSCJ•UPDATEXS•SET总学分=总学分+10•例将姓名为“王林”的同学的专业改为“材料工程”，备注改为“转专业学习”，学号改为001230•Usexscj•Updatexs•setxh=‘001230’,•zhy=‘材料工程’,•bzh=‘转专业学习’•wherexm=‘王林’5.3数据完整性和约束5.3.1数据完整性的分类数据完整性是指数据库中的数据在逻辑上的一致性和准确性.一般包括三种.1域完整性又称列完整性,指给定列的输入的有效性.可以通过定义相应的规则、约束、默认值对象等方法实现。2实体完整性又称行的完整性，要求表中有一个主键，其值不能为空且能唯一地表示对应的实体。通过索引、UNIQUE约束primarykey约束或identity属性可实现数据的完整性.3参照完整性参照完整性又称引用完整性.参照完整性保证主表(被参照表)中的数据与从表(参照表)中数据的一致性.通过定义外键(外码)于与主键(主码)之间或外键与唯一键之间的对应关系来实现参照完整性.参照完整性确保键值在所有表中一致.4.用户定义完整性•5.3.2约束的类型•1.check约束•2.default约束•3.primarykey约束•4.foreignkey约束•5.unique约束5.3.3完整性的实现一、域完整性的实现可以通过定义约束和规则等来实现域完整性1.check约束的定义与删除check约束是字段输入内容的验证规则,表示一个字段的输入内容必须满足check约束的条件,若不满足,则数据无法正常输入.1)通过SQLServerManangementStudio创建与删除check约束2)利用sql语句在创建表时创建check约束•语法格式:•createtabletable_name•(column_namedatatypenotnull/null•[defaultconstraint_expression]•[[check_name]check(logical_expression)]•[…n])•例在学生数据库中创建books,其中包括所有的约束定义•usexscj•createtablebooks•(book_idsmallint,•book_namevarchar(50)notnull,•book_descvarchar(50)notnull,•max_lvltinyintnotnullcheck(max_lvl=250)•)•Go•usexscj•createtablebooks1•(book_idsmallint,•book_namevarchar(50)notnull,•book_descvarchar(50)notnull,•max_lvltinyintnotnullconstraintcheck_mcheck(max_lvl=250)•)•Go3)利用sql语句在修改表时创建check约束altertabletable_nameaddconstraintcheck_namecheck(logical_expression)例:通过修改xscj数据库的xs_kc表,增加成绩字段的check约束usexscjaltertablexs_kcaddconstraintcj_constraintcheck(成绩=0and成绩=100)4)利用SQL语句删除check约束altertabletable_namedropconstraintcheck_name•例删除xscj数据库中的xs_kc表的成绩字段的check约束•usexscj•altertablexs_kc•dropconstraintcj_constraint•go•2.规则对象的定义、使用与删除•（1）定义规则对象•（2）将规则对象邦定到列或用户自定义数据类型•1）利用SQL命令定义规则并绑定到自定义类型或列•规则对象的定义createrulerule_nameasiocondition_expressn绑定sp_bindrule@rulename=]’rule’,[@objectname=]’object_name’[,[@futureonly=]‘futureonly_flag’]•例•创建一个规则，并绑定到表KC的课程号列，用于限制课程号的输入范围•usexscj•go•Createrulekc_rule•as@ranglike‘[1-5][0-9][0-9]’•go•Usexscj•execsp_bindrule‘kc_rule’,’kc.kch’•go•例2•创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值•usexscj•go•Createrulelist_rule•as@list1in('C语言','微机原理','离散数学')•go•Usexscj•execsp_bindrule'list_rule','kc.kcm'•go•例3•定义一个用户数据类型course_num,然后将前面定义的规则‘kc_rule’绑定到course_num上，最后定义表kc1，其课程号的数据类型为course_num。•usexscj•execsp_addtype'course_num','char(3)','notnull'•execsp_bindrule'kc_rule','course_num'•Go•usexscj•createtablekc1(kchcourse_num,•kcmchar(30)notnull,•kkxqtinyint,•xshtinyint,•xftinyint)•二、实体完整性的实现•通过选择一列或多列做主键可实现表的实体完整性。•一个表只能有一个primarykey约束,且primarykey约束中的列不能为空值.•如果要确保一个表中的非主键列不输入重复值,应在该列上定义唯一约束(unique约束).•Primarykey与unique约束的主要区别如下:•(1)一个表只能创建一个primarykey约束,但可以对不同的列创建若干个unique约束.•(2)primarykey约束列不能为空值,unique约束能为空值•(3)创建primarykey约束时,系统自动产生索引,索引的默认类型为聚集索引,创建unique约束时,系统自动创建一个unique索引,索引类型为非聚集索引.•1利用管理器定义和删除primarykey约束•2利用管理器定义和删除unique约束•3利用sql语句创建及删除primarykey约束和unique约束•1)创建表的同时创建语法格式:•createtabletable_name•(column_namedatatype•[constraintconstraint_name]•notnull•Primarykey/unique•[clustered/nonclustered]•[…n])•usexscj•createtablexs3•(xhchar(6)notnullconstraintxh_pkprimarykey,•xmchar(8)notnull,identtitycardchar(20)constraintsh_ukunique,•zhychar(10)null,xbchar(2)notnull,chshsmalldatetimenotnull,•zxftinyintnull,•bzhtextnull,•rxrqdatetimeconstraintdatedfltdefaultgetdate())•2)通过修改表创建primarykey约束和unique约束•altertabletable_name•add[constraintconstraint_name]primarykey/unique•clustered/nonclustered•(column[,…n])•如:•Altertablexs•addconstraintxs_pk•primarykeyclustered(xh)•Go•Altertablexs•addconstraintxs_ukuniquenonclustered(shfzh)•go•3)删除primarykey约束和unique约束•Altertablexs•Dropconstraintxs_pk，xs_uk•go