第5章数据表管理

第5章

SQLServer数据表的管理

本章内容5.1数据表的创建和管理5.2表数据的管理5.3数据完整性管理

5.1数据表的创建和管理

5.1.1使用企业管理创建数据表 5.1.2使用Transact-SQL语言创建数据表 步骤：明确在针对哪个数据库操作。对该数据库应如何规划，设定几个数据表每个数据表各存储什么信息？表中需要设置哪些字段？字段的名字及数据类型？哪些字段确定为主键？哪些字段不可以为空？是否使用约束或索引？在何处用？5.1数据表的创建和管理使用查询分析器在数据库Student_db中创建Student学生信息表，要求包含S_NO（学号）、NAME（姓名）、AGE（年龄）、SEX（性别）信息，其中学号不能为空。（1）启动SQLServer服务器，运行查询分析器，并连接到数据库。（2）在查询窗口中输入创建表的语句。创建数据表Student的语句如下：（3）单击工具栏上的“分析查询”按钮，若结果窗口无错误信息，再单击“执行查询”按钮，将返回“命令已成功完成”的信息。

5.1数据表的创建和管理

5.1.3使用企业管理器对数据表进行管理5.1.4使用Transact-SQL语言对数据表进行管理5.1数据表的创建和管理5.1.3使用企业管理器对数据表进行管理使用企业管理器查看数据表的相关设置使用企业管理器更改数据表名称使用企业管理器删除数据表5.1数据表的创建和管理5.1.4使用T-SQL语言对数据表进行重命名Execsp_rename

old_table_name,new_table_name如将图书库中的“读者”表更名为“学生”表Execsp_name‘读者’,’学生’5.1数据表的创建和管理5.1.5使用T-SQL语言对数据表字段进行管理

语法格式：

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

]|[ADDcolumn_name

data_base]|[DROPCOLUMNcolumn_name

]|}

参数说明： （1）table：要更改的数据表的名称是要更改的表的名称。如果表不在当前数据库中或者不属于当前用户所拥有，可以显式指定数据库和所有者。 （2）ALTER/ADD/DROPCOLUMN：指定要更改的列。 （3）column_name：要更改、添加的列的名称。修改表字段的数据类型

AlTERTABLE<表名>ALTERCOLUMN<列名称><新数据类型>注意：数据类型为text、image、ntext

的列，或者属于primarykey的列不能修改。新的数据类型应与原数据类型兼容

举例：1、修改student库中sc表里的grade属性列的数据类型，由原来的int改为smallintusestudentALTERTABLEscaltercolumngradeSMALLINT或者：ALTERTABLE

student.sc

altercolumngradeSMALLINT2、在”学生”表中，修改“住址”一列，数据类型为varchar(50）

ALTERTABLE

学生altercolumn

住址varchar(50）知识回顾建立表结构Createtable<表名>(字段名数据类型约束,…….,)修改表名字Execsp_rename

old_table_name,new_table_name修改字段数据类型AlTERTABLE<表名>ALTERCOLUMN<列名称><新数据类型>更改字段名Execsp_rename‘表名.[<原列名>]’,’<新列名>’,’column’

举例：3、修改读者表中字段，把“备注”字段名更名为“附言”Use图书execsp_rename‘读者.[备注]’,‘附言','column'

增加表字段（一定要包含字段名和数据类型，也可增加一些相应约束条件）AlTERTABLE<表名>ADD<新列名称><新数据类型>注意：约束条件不能有noy

null,primarykey等。

举例：2、在student库中couse表里,新增一列id_xuanke，数据类型为5个字符，而且取值唯一ALTERTABLE

student.couse

add

id_xuankechar(5)unique删除表字段AlTERTABLE<表名>dropcolumn<列名称>

举例：3、在student库中couse表里,删除列id_xuankeALTERTABLE

student.couse

drop

columnid_xuanke

练习:在读者表中，增加一个年龄字段，然后删除地址字段，并修改备注字段的数据类型为varchar(200).Altertable图书.读者add年龄intAltertable图书.读者dropcolumn地址

Altertable图书.读者altercolumn备注varchar(200)5.1数据表的创建和管理5.1.6删除数据表可使用DROPTABLE语句 语法格式：

DROPTABLEtable_name

参数说明：

table：要删除的数据表的名称。例：删除当前数据库中的Student_1学生信息表。删除该表的语句如下：DropTABLEStudent_1注意：（1）DROPTABLE不能用于删除由FOREIGNKEY约束引用的表。必须先删除引用的FOREIGNKEY约束或引用的表。（2）在系统表上不能使用DROPTABLE语句。

(3)数据、表上的索引都删除。表上的视图往往仍然保留，但无法引用实例例：在同一语句中指定多个表对它们进行删除。假设有一stu数据库，此数据库中存在book表和temp2表，同时删除这两张表的语句如下：DROPTABLEbook,temp2例：删除指定数据库中的表。假设stu

数据库内有temp1表，可以在任何数据库内执行如下语句，完成删除temp1表的操作。删除该表的语句如下：DROPTABLEstu.dbo.temp1默认情况下，将DROPTABLE权限授予表所有者，该权限不可转让。然而，dbo可以通过在DROPTABLE内指定所有者除去任何对象

5.2表数据的管理

5.2.1使用企业管理管理表数据使用表设计器浏览、添加、修改、删除数据

5.2表数据的管理

5.2.2使用Transact-SQL语言创建数据表 表数据的添加表数据的修改表数据的删除5.2表数据的管理5.2.2使用Transact-SQL语言管理表数据表数据的添加，可以使用INSERTINTO语句，语法格式：INSERT[INTO]table_or_view[(column_list)]

{values({default|null|expression})|select…from…}作用:可以一次追加一行数据，也可以从另外的表和查询中追加数据参数说明：[INTO]：一个可选的关键字，可以将它用在INSERT和目标表之间。table_or_view：要输入数据的表名或视图名。[(column_list)]：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list

括起来，并且用逗号进行分隔。如果没有指定column_list，表或者视图中的所有列都将接收数据

有两种方法指定数据值：用VALUES子句为一行指定数据值：用SELECT子查询为一行或多行指定数据值。datavalues：作为一行或者多行要插入数值的列表。所提供的数据值必须与列的列表匹配。数据值的数目必须与列数相同，每个数据值的数据类型、精度和小数位数也必须与相应的列匹配5.2表数据的管理例5-2-1：假设数据库Student_db中已经创建好了课程信息数据表C_Info，试将新课程记录（9720044，网络技术与应用，选修，3）添加到课程信息数据表C_Info中。添加新课程记录的语句如下：INSERTINTOC_InfoVALUES('9720044','网络技术与应用','选修',3,null)

5-2-1：如何使用values将一行新的记录追加到表中的所有字段5.2表数据的管理例5-2-2：在C_Info表中添加一条新记录，课程类别和课程说明暂缺。根据题意知，要求添加3个字段的数据，使用如下语句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)VALUES('9720045','Web开发技术',2)注意：使用INSERT语句一次只能为表插入一行数据。增加多行使用多条insert如果INSERT语句违反约束或规则，或者它有与列的数据类型不兼容的值，那么该语句就会失败，并且SQLServer将显示错误信息。此时必须列出列名（因为提供的值的个数与表中列的个数不一致）。未列出的列中将自动填入缺省值，如果没有设置缺省值则填入NULL

C_Info中的课程类别和课程说明列必须允许为NULL，因为系统实际插入的数据为：(‘9720045’,‘Web开发技术’,default,2,default)。5-2-2：如何使用values将一行新的记录追加到表中的部分字段练习2：给“读者”表中增加一条记录，内容为：张华，借书证号为220100usetsglINSERTINTO读者(姓名,借书证号)VALUES('王芳','210900’)练习1：给“读者”表中增加一条记录，内容为：王芳，借书证号为210900，女，19岁，专业，系别不详。usetsglINSERTINTO读者VALUES('210900','王芳','女',19,null,default)或者：usetsglINSERTINTO读者(借书证号,姓名,性别,年龄)VALUES('210900','王芳','女',19)5.2表数据的管理例5-2-3：假设数据库Student_db中已经创建好了课程信息数据表C_Info，试将新课程记录（9720044，网络技术与应用，选修，3）添加到课程信息数据表C_Info中。添加新课程记录的语句如下：INSERTINTOC_Infoselect'9720044','网络技术与应用','选修',3,null

5-2-3：如何使用select将一行或多行记录追加到表中的所有字段5.2表数据的管理例5-2-4：在C_Info表中添加一条新记录，课程类别和课程说明暂缺。根据题意知，要求添加3个字段的数据，使用如下语句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)select'9720045','Web开发技术',25-2-4：如何使用select将一行新的记录追加到表中的部分字段5.2表数据的管理例5-2-5：在student数据库中新建一个结构与sc表相同的表we,并且将sc表中的记录都追加到新表中新建表语句：createtablewe(we_idnvarchar(10),we_coid

int,we_grade

smallint,primarykey(we_id,we_coid))追加记录语句：insertintoweselect*fromsc如果只追加主键字段insertintowe（we_id,we_coid)select学号,课号fromsc5-2-5：如何使用select将另外表中的一行或者多行记录追加到本表中的部分或者全部字段5.2表数据的管理例5-2-6：把pubs数据库中authors表中的author_id,au_fname追加到student数据库中的stu表下的学号、姓名字段，使用如下语句：insertstudent.dbo.stu(学号,姓名)selectau_id,au_fnamefrompubs.dbo.authors5-2-6：如何使用select将另外数据库中某表的一行或者多行记录追加到本表中的部分或者全部字段注意：学号、姓名字段的数据类型应与追加记录的字段author_id,au_fname数据类型一致或兼容学号、姓名字段的数据类型的长度应小于或者等于追加记录的字段author_id,au_fname数据类型的长度。Stu表中其他为追加的字段必须允许为NULL5.2表数据的管理5.2.7使用Transact-SQL语言管理表数据表数据的删除，可以使用DELECT语句，基本语法格式：DELECT[FROM]table_namewhere…….作用:根据where设定条件删除一行或者多行数据例1：删除新建的we表中的所有记录Deletefromwe注意：删除的只是表的记录而不是表结构，表本身还存在。删除表droptablewe例2删除stu表中系别为空的所有记录

deletefromstuwhere系别isnull例3删除stu表中系别计算机、自动化的所有记录

deletefromstuwhere系别in(‘计算机’,’自动化’)或者：deletefromstuwhere系别=‘计算机’or系别=‘自动化’

知识回顾1、对数据库结构的修改？2、对数据表结构的修改？3、对数据表数据的修改？增加：insertinto….values(…..)select…from…where…

删除：deletefrom….where…..修改？5.2表数据的管理5.2.8使用Transact-SQL语言管理表数据表数据的修改，可以使用update语句，基本语法格式：Updatetable_nameset{column_name=….}where…….作用:根据where设定条件修改一个字段或者多个字段的数据例1、在pub数据库中将publishers表中的所有出版社都将总部迁移到乔治亚(Georgia)州的亚特兰大(Atlanta)使用以下UPDATE语句：UsepubsgoUPDATEpublishersSETcity='Atlanta',state='Georgia'例2、在student数据库中将stu

表中的所有备注字段数据为空的记录都修改为“新生”使用语句：UsestudentgoUPDATEstuSET备注=‘新生‘where备注isnull例3、在tsgl数据库中将图书表中的所有单价上涨一倍使用语句：UsetsglgoUPDATE图书SET单价=单价*1.1例4将student数据库中的scourse表中课程号为‘2001’的学时增加2UsestudentgoUPDATEscourseSET学时=学时+2where课号=2001小结（续）

5.3数据完整性管理

数据库是一种共享资源。因此，在数据库的使用过程中保证数据的安全、可靠、正确、可用就成为非常重要的问题。数据库的完整性保护可以保证数据的正确性和一致性。5.3.1数据完整性概述定义：数据的完整性是指数据库中数据的正确性、有效性和一致性。正确性：是指数据的合法性，例如数值型数据只能包含数字不能包含字母；有效性：是指数据是否处在所定义域的有效范围之内；一致性：是指同一事实的两个数据应该一致，不一致即是不相容的。

5.3数据完整性管理

满足完整性要求的数据应具有以下特点：数据类型准确无误数据的值满足范围设置同一表格之间数据不存在冲突多个表格数据之间不存在冲突例如：在一个数据库中有两个表，人事表和财务表。人事表记录了本单位的员工信息，财务表记录了本单位员工的借款情况。如果某个员工有借款，那么他不能从人事表中删除，这就是一种数据完整性约束。设计数据库时考虑数据的完整性是设计的一个重要内容

5.3数据完整性管理

5.3.2数据完整性的类型实体完整性：规定表的每一行在表中是唯一的实体如：Unique,primarykey约束域完整性：数据库表中的列必须满足某种特定的数据类型或约束如：default,notnull约束引用（参照）完整性：指两个表的主关键字和外关键字的数据应对应一致如：外键约束用户定义完整性：针对某个特定关系数据库的约束条件

存储在数据库中的所有数据值均应保持正确的状态。如果数据库中存储有不正确的数据值，则该数据库称为已丧失数据完整性。

5.3数据完整性管理

5.3.3实现数据库中的数据完整性

PRIMARYKEY约束主键

FOREIGNKEY约束外键

UNIQUE约束唯一

CHECK约束约束变量取值范围

notnull非空约束

DEFAULT（默认值）约束

使用企业管理器创建数据完整性1、主键约束2、唯一性约束3、检查性约束（check）举例：在stu表中设定系别只能为自动化或者计算机，输入：系in(‘自动化’，‘计算机’）使用企业管理器创建数据完整性4、外键约束外键的设置主要是通过将一个表中主键所含的列包含到另一个表中，这个列就是另一个表的外键。企业管理器创建外键即建立表间关系使用企业管理器创建数据完整性5、缺省约束6、notnull约束使用T-SQL创建数据完整性CONSTRAINT【约束名】约束类型约束对象

例1，为student表中的姓名列设notnull约束，国家列设置缺省值'中国',

CREATETABLEstudent(namechar(20)NOTNULL,countrychar(30)DEFAULT'中国')CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)constraintwee1DEFAULT'中国’)注意：不能写成CREATETABLEstudent1(namechar(20)NOTNULL,countrychar(30)，constraintwee1DEFAULT'中国’forcountry)例2建立主键、非空、检查、缺省约束createtablestudent2(stu_nochar(6)primarykey,

stu_namechar(10)notnull,

stu_sexchar(2)check(stu_sex='男'orstu_sex='女'),/*约束此列的值只能输入“男”或“女”值*/

stu_nationchar(10)default'汉族',)或者：createtablestudent32(stu_nochar(6)constraint约束1primarykey,

stu_namechar(10)notnull,

stu_sexchar(2)constraint约束2check(stu_sex='男'orstu_sex='女'),

stu_nationchar(10)constraint约束3default'汉族')或者：createtablestudent32(stu_nochar(6),

stu_namechar(10)notnull,

stu_sexchar(2),

stu_nationchar(10)constraint约束3default'汉',constraint约束1primarykey(stu_no),constraint约束2check(stu_sexin('男','女')))但是若建立好数据表之后，想要再往列增加约束或修改约束，则格式根据约束的不同各有不同：createtableabc

(s_nochar(10),s_namechar(10),s_sexchar(2),

s_nationchar(16))/*以上为建立一个没有任何约束的新数据表*/altertableabc

altercolumns_nochar(10)notnullgo/*以上为给表的s_no列加上notnull约束*/altertableabcaddprimarykey(s_no)go/*以上为给表的s_no列加上primarykey约束*/

altertableabcaddcheck(s_sex=''男''ors_sex=''女'')/*给x_sex列加check约束*/goaltertableabc

adddefault'汉族'fors_nation

/*加default约束时格式和其他的有所不同*/go检查表中都有哪些约束execsp_helpconst