第7章表数据操作

第7章数据表和表数据7.1表的创建、修改和删除一、SQLserver支持的数据类型

1）整数型：bigint,int,smallint,tinyint2)精确数值型：整数部分+小数部分，分为：decimal和numeric两类

3）浮点型:foat和real两类

4）货币型：money和smallmoney5)位型：相当于其他语言中的逻辑型数据，只存储0和16)字符型：varchar和char7)unicode字符型8）文本型：text和ntext9）二进制型10）日期时间类型：datetime和smalldatetime2.创建表首先要设计表（1）空值(null)概念空值表示通常表示未知、不可用或将以后添加的数据。表的关键字不允许为空值。（2）学生情况表的创建表结构二、表表是SQLserver中最主要的数据库对象，它是用来存储和操作数据的一种逻辑结构，由行和列组成。

1表结构组成表的各列的名称及数据类型

2记录表中的一行

3字段构成记录的每个数据项

4关键字表中记录的某一字段或字段组合能唯一表示记录，该字段或字段组合成为关键字。通过管理器创建表XS的操作步骤

三、使用CREATETABLE创建表示例：设已经创建数据库XSCJ，现在该数据库中需创建学生情况表XS，

Kc(kch,kcm)xs_kc(xh,kch,chj)Xs(xh,xm,zhy,xb,chsh,zxf,bzh)

usexscjcreatetablexs(学号char(6)notnull,

姓名char(8)notnull,

专业名char(10)null,

性别char(2)default‘男’notnull,

出生时间smalldatetimenotnull,

总学分tinyintnull,

备注textnull)goCreatetable语句的语法：

createtable表名（{column_namedatatype|identity|notnull|null}）例：Createtablecard(卡号decimal(6,0)identity(10000,1)

姓名char(8)notnull,

专业名char（10）null)

createtable完整的语法结构：

createtable[database_name.[schema_name]|[schema_name.]table_name(<column_definition>|column_nameascomputed_column_expression|<table_constraint>)xscj..xs四、使用ALTERTABLE修改表Altertable语句的基本语法结构

ALTERTABLEtable_name{[altercolumncolumn_name{new_datatime_type[(precision[,scale])][notnull|null]]}|add{[<column_definition>]}[,…n]|drop{[constraint]constraint_name|columncolumn_name}[,…n]1修改已有列的属性：ALTERCOLUMN列名2向表中增加新列：ADD3删除表中列：DROPCOLUMN例在表XS中增加一个新列—奖学金等级列

usexscjaltertablexsadd

奖学金等级tinyintnullgo例：在XS表中删除名为奖学金等级的列

usexscjaltertablexsdropcolumn奖学金等级

go例：修改表XS中已有列的属性：将名为“姓名”的列长度由原来的8该为10，将“出生时间”的列的数据类型由原来的smalldatetime改为datetime.usexscjaltertablexsaltercolumn姓名char(10)altertablexsaltercolumn出生时间datetimego六、使用DROPTABLE删除表

droptable表名（3）列的identity属性对任何表都可创建包含系统所生成序号值的一个标识列，该序号值唯一标识表中的一行，可以作为键值。每个表只能有一列作为标识列，int,numeric,smallint,biginORtinyint,定义标识列可指定其种子（起始）值和增量值，建立数据库library,在其中创建card表，表结构如所示3修改表通过管理器和T-SQL语言两种方法来进行：更改表名，增加列，删除列，修改已有列的属性4删除表创建数据库和表以后，需要对表中的数据进行操作，包括插入、删除和修改。有两种方法：一是通过SQLServer2005管理器，一是通过T-SQL语句。7.2界面操作表数据一、插入记录添加记录在表尾二、删除记录三、修改记录7.3命令操作表数据（T-SQL语句）一、通过INSERT语句插入表数据向表中插入数据可以使用INSERT语句。INSERT最基本的格式为：

INSERT[into]表名(column1,column2,…)VALUES（constant1,constant2,…）该语句的功能是向指定的表中加入由VALUES指定的各列值的行例：向XSCJ数据库的表XS中插入一行：

001112罗林林计算机01/30/19800:0:040可以使用如下的T-SQL语句：

USExscjINSERTINTOXSCJ.dbo.XS()VALUES(‘001112’,’罗林林’,‘计算机’,0,‘1/30/19800:0:0’,40,null)Go

例：用如下语句建立表testCREATETABLEtest(xmchar(20)notnull,zyvarchar(30)default(‘计算机’),njtinyintnotnull)用INSERT语句向表中插入一条记录：

INSERTINTOtest(xm,nj)values(‘

王林’,3)例：创建表XS2CREATETABLEXS2(numchar(10)notnull,namechar(10)notnull,specialtychar(10)null)可用如下的INSERT语句向XS2中插入数据：Insertintoxs2selectxh,xm,zhyfromxs1Wherezhy=‘生工’查询结果：select*fromxs2二、使用DELETE或TRUNCATETABLE语句删除数据1.使用delete语句删除数据

delete语句的功能是从表中删除行，其基本语法格式为：

delete[from]{table_name׀view_name}[where<search_condition>]Example:将XSCJ数据库的表XS中总学分小于39的行删除：

USEXSCJDELETEFROMXSWHERE总学分<39go2.使用TRUNCATETABLE语句删除表数据使用该语句将删除指定表中的所有数据，语法格式为：

TRuNCATETABLEname

3.使用UPDATE语句修改数据UPDATE语句可以用来修改表中的数据行，其最基本语法格式如下：UPDATE{table_name׀view_name}SETcolumn_name={expression׀DEFAULT׀NULL}[,…n][WHERE<search_condition>]例：将XSCJ数据库的XS表中学号为001110的学生的备注列值改为“三好生”：

USEXSCJUPDATEXSSET备注=‘三好生’

WHERE 学号=‘001110’GO例将XS表中的所有学生的总学分都增加10个学分。

USEXSCJUPDATEXSSET总学分=总学分+10例将姓名为“王林”的同学的专业改为“材料工程”，备注改为“转专业学习”，学号改为001230UsexscjUpdatexssetxh=‘001230’,zhy=‘材料工程’,bzh=‘转专业学习’

wherexm=‘王林’

7.4数据完整性和约束7.4.1数据完整性的分类数据完整性是指数据库中的数据在逻辑上的一致性和准确性.一般包括四种.1域完整性又称列完整性,指给定列的输入的有效性.可以通过定义相应的规则、约束、默认值对象等方法实现。2实体完整性又称行的完整性，要求表中有一个键，其值不能为空且能唯一地表示对应的实体。通过索引、UNIQUE约束primarykey约束或identity属性可实现数据的完整性.3参照完整性参照完整性又称引用完整性.参照完整性保证主表(被参照表)中的数据与从表(参照表)中数据的一致性.通过定义外键(外码)与主键(主码)之间或外键与唯一键之间的对应关系来实现参照完整性.参照完整性确保键值在所有表中一致.Xs(xh,xm,nl,zhy),kc(kch,kcm),cj(xh,kch,cj)4.用户定义完整性7.4.2约束的类型1.check约束2.default约束3.primarykey约束4.foreignkey约束5.unique约束7.4.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,其中包括所有的约束定义

usexscjcreatetablebooks(book_idsmallint,book_namevarchar(50)notnull,book_descvarchar(50)notnull,max_lvltinyintnotnullcheck(max_lvl<=250))Gousexscjcreatetablebooks1(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约束

usexscjaltertablexs_kcdropconstraintcj_constraintgo2.规则对象的定义、使用与删除（1）定义规则对象（2）将规则对象邦定到列或用户自定义数据类型

1）利用SQL命令定义规则并绑定到自定义类型或列规则对象的定义

createrulerule_nameascondition_expression绑定

sp_bindrule@rulename=]’rule’,[@objectname=]’object_name’[,[@futureonly=]‘futureonly_flag’]例创建一个规则，并绑定到表KC的课程号列，用于限制课程号的输入范围

usexscjgoCreaterulekc_ruleas@ranglike‘[1-5][0-9][0-9]’goUsexscjexecsp_bindrule‘kc_rule’,’kc.kch’go例2创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值

usexscjgoCreaterulelist_ruleas@list1in('C语言','微机原理','离散数学')goUsexscjexecsp_bindrule'list_rule','kc.kcm'go例3定义一个用户数据类型course_num,然后将前面定义的规则‘kc_rule’绑定到course_num上，最后定义表kc1，其课程号的数据类型为course_num。

usexscjexecsp_addtype'course_num','char(3)','notnull'execsp_bindrule'kc_rule','course_num'Gousexscjcreatetablekc1(kchcourse_num,kcmchar(30)notnull,kkxqtinyint,xshtinyint,xftinyint)usexscjifexists(selectnamefromsysobjectswherename='kc_rule'andtype='r')beginexecsp_unbindrule'kc.kch'droprulekc_ruleendgo二、实体完整性的实现通过选择一列或多列做主键可实现表的实体完整性。一个表只能有一个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]notnullPrimarykey/unique[clustered/nonclustered][…n])usexscjcreatetablexs3(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_nameadd[constraintconstraint_name]primarykey/uniqueclustered/nonclustered(column[,…n])如:Altertablexsaddconstraintxs_pkprimarykeyclustered(xh)GoAltertablexsaddconstraintxs_ukuniquenonclustered(shfzh)go3)删除primarykey约束和unique约束AltertablexsDropconstraintxs_pk，xs_ukgo三、参照完整性的实现

对两个相关联的表进行数据插入和删除时,通过参照完整性保证它们之间数据的一致性如果两个表之间定义了参照完整性,则:(1)从表不能引用不存在的键值(2)如果主表中的键值更改了,那么整个数据库中,对从表中该键值的所有引用要进行一致的更改.(3)如果主表中没有相关联的记录,则不能将记录添加到从表(4)如果要删除某一记录,应先删除从表中与该记录相匹配的记录.

利用foreignkey定义从表的外码,primarykey约束和unique约束定义主表中的主码或唯一码(不允许为空),可实现主表和从表之间的参照完整性。

定义表间参照关系:先定义主表的主码(或唯一码),再对从表定义外码约束.1利用企业管理器定义表间的参照关系

2利用企业管理上删除表间的参照关系

3利用SQL命令定义表间的参照关系

1）创建表的同时定义外码约束createtabletable_name(column_namedatatype[foreignkey]referencesref_table(ref_column)[…n])例在xscj数据库中创建xs4表,xs4.xh为主键,然后定义从表xs_kc1.xh为外码

usexscjcreatetablexs4(xhchar(6)notnullconstraintxh_pkprimarykey,…..)GoCreatetablexs_kc1(xhchar(6)notnullforeignkeyreferencesxs4(xh),

…)Go例：usexscjaltertablexs_kc1addconstraintkc_fkforeignkey(kch)referenceskc(kch)go

2)通过修改表定义外码约束

altertabletable_nameadd[constraintconstraint_name]foreignkey(column[,…n])referencesref_table(ref_column[,…n])3)利用SQL命令删除表间的参照关系删除参照关系，实际是删除从表的外码即可

altertabletable_namedropconstraintconstraint_namego7.4默认值约束及默认值对象一个字段默认值的建立可通过如下两种方式实现：（1）在定义和修改表时，定义默认值约束（2）先定义默认对象，然后将该对象绑定到表的相应字段一在表中定义及删除默认值约束1.默认值约束的定义通过SQL命令的执行，在定义或修改表时，可定义一个字段的默认值约束。默认值约束定义的一般格式：

createtabletable_name(columnnamedatatypenotnull|null[defaultconstraint_expression][…n])例：在定义表时定义一个字段的默认值Usexscjcreatetablexs1(xhchar(6)notnull,xmchar(8)notnull,zhychar(10)null,xbbitnotnull,birthdatesmalldatetimenotnull,zxftinyintnull，

rxrqdatetimedefaultgetdate())go例2下例的功能与上例相同，但在定义默认值的同时指定了约束名

usexscjcreatetablexs1(xhchar(6)notnull,xmchar(8)notnull,zhychar(10)null,xbbitnotnull,birthdatesmalldatetimenotnull,zxftinyintnullrxrqdatetimeCONSTRAINTdatedfltdefaultgetdate())Go例3在修改表定义一个字段的默认值约束。

usexscjaltertablexs1addadddatesmalldatetimenullconstraintadddatedfltdefaultgetdate()withvaluesWithvalues仅用在对表添加新字段的情况下。若使用了withvalues，则将为表中各现有行添加的字段提供默认值：如果没有使用withvalues那么每一行的新列中都将为null值。2默认值约束的删除若已知一个默认值约束的约束名，通过SQL命令删除例3删除上例中定义的默认值约束

usexscjaltertablexs1dropconstraintadddatedfltgo二、默认值对象的定义、使用和删除通过SQL语句定义和绑定default默认值对象1）通过sql命令定义default默认值对象语法如下：

createdefaultdefault_nameasconstraint_expr