第三讲管理表

第三讲管理表第3讲管理表哈尔滨师范大学恒星学院本讲主要内容表的基础知识用SSMS管理表用T-SQL语言管理表常用工具简介123数据库创建后，就可以创建表来存储数据，通常是存储在永久表中.不过,用户也可以建立临时表.表会一直存储在数据库文件内,直到被删除为止.只要用户拥有适当权限,就可以使用它们.一.

表的基础知识

表是由行和列组成的.创建表的过程主要就是定义表的列的过程.与表有关的两个常用概念是：字段和记录.其中记录是表的一行，是表所塑造的单一对象的实体.而字段是表中的列，描述一条记录要有若干个列.列代表对象的属性(例如,姓名、住址等).

在SQLServer中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段.

但是很少有哪个设计良好的关系数据库表会包含25个以上的列，它们绝大多数都只包含少数的列.

每个列有自己的属性,常用且重要的三个属性是:列名、数据类型和约束.

SQLServer系统内的对象在命名时可以使用中文,但要尽量避免这样做，以免引发工具程序或前端应用程序的无谓错误.对象的命名必须符合系统的命名规则.比如名称标识符含字母在1~128个(临时表的名称在116个字符内),特殊字符的名称要放到分隔符[]或””内等等.命名规则数据类型是数据的格式,表定义中的每个列必须有数据类型,数据操作中,SQLServer自动进行数据检验.

常用数据类型主要有:

整数型:int,smallint,bigint

浮点型:numeric,float(n),real

字符型:char(n),varchar(n),nchar(n)

日期时间型:smalldatetime,date,time数据类型SQLServer提供了两种方法创建数据库表.第一种方法是通过图形化界面创建即使用“SQLServerManagementStudio”(简记为SSMS)创建表，它提供了一个前端的、填充式的表设计器以简化表的设计工作；另一种方法是利用T-SQL语句中的CREATE

命令创建表.二.用SSMS管理表在SSMS中,展开要建表的数据库，比如pubs,右键单击“表”节点,在弹出的菜单中选择“新建表”,打开表设计器,如下图.

注意在下方列属性视图中设置主键,非空,标识列,默认值.

创建表

例建立表student存放学生的信息.idchar(7),namechar(10),

sexchar(2),birthdaysmalldatetime,birth_placevarchar(50),classchar(10)练习

---用T-SQL命令CREATE创建表.

CREATETABLEstudent(idchar(7),namechar(10),

sexchar(2),birthdaysmalldatetime,birth_placevarchar(50),classchar(10))三.用T-SQL语言

管理表哈尔滨师范大学恒星学院

修改表(1)启动SSMS后,展开SQLServer实例和数据库.(2)右击要修改的表，然后从弹出的快捷菜单中选择“设计”命令.

(3)在“表设计器”中，可以新增列、删除列和修改列的名称、数据类型、长度、是否允许为空等.

(4)当完成修改表的操作后，单击工具栏上的“保存”按钮.利用T-SQL语句修改数据表

修改数据表的语法格式如下：ALTERTABLEtable_name{ALTERCOLUMNcolumn_namenewdatatype[(precision[，scale])][NULL|NOTNULL]|ADD{[<column_definition>][，．．．n]}|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}[，．．．n]}哈尔滨师范大学恒星学院

删除表(1)启动SSMS后,展开SQLServer实例和数据库.(2)右击要修改的表，然后从弹出的快捷菜单中选择“删除”，打开“删除对象”对话框.

(3)在“删除对象”对话框中，显示出删除对象的属性信息，单击“确定”按钮.

注意:删除表只能够删除用户表，不能够删除系统表.删除表一旦操作完成，表中数据也一并被删除，而且是无法恢复的.利用T-SQL语句删除数据表

删除数据表的语法格式如下：

DROPTABLEtable_name[，．．．n]

其中，table_name为所要删除的表的名称。

删除表只能够删除用户表，不能够删除系统表。

完整性与约束

完整性

数据完整性是数据库设计方面一个非常重要的问题，数据完整性代表数据的正确性、一致性与可靠性，实施完整性的目的在于确保数据的质量。

在SQLServer中，根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性分类为实体完整性、域完整性和参照完整性等。

1．实体完整性

实体完整性把数据表中的每行看作一个实体，它要求所有行都具有唯一标识。在SQLServer中，可以通过建立PRIMARYKEY约束、UNIQUE约束、唯一索引，以及列IDENTITY属性等措施来实施实体完整性。2．域完整性

域完整性要求数据表中指定列的数据具有正确的数据类型、格式和有效的数据范围。域完整性通过默认值、FOREIGNKEY、CHECK等约束，以及默认、规则等数据库对象来实现。3．参照完整性

参照完整性维持被参照表和参照表之间的数据一致性。在SQLServer中，参照完整性通过主键与外键或唯一键与外键之间的关系来实现，通过建立FOREIGNKEY约束来实施。在被参照表中，当其主键值被其他表所参照时，该行不能被删除，也不允许改变。在参照表中，不允许参照不存在的主键值。

约束语句是表定义语句的重要组成部分.通过约束机制,可使数据库引擎强制控制数据内容的完整性.

约束语句可以在列或表两个级别完成.例如,将PRIMARYKEY约束可以作用在列上,若PRIMARYKEY

约束结合多个字段,则只能在表级别建立.

使用时,约束优先于DML

触发过程、规则和默认.约束

●

not

null非空约束---不允许空值●primarykey主键约束---识别表的记录

---主键值唯一,非空●

check检查约束---限制列值范围---操作数据时,系统自动验证数据

●

unique唯一性约束---值唯一

---该约束可建在表上,可用于有空值的列●

default默认值约束---默认的值

---常用的默认值getdate(),newid()●foreignkey外键约束---该键值参照另一表键值

---另一表(引用表)的值必须存在唯一

约束的种类PK

客户编号公司名称联系人联系人称呼地址城市行政区邮政编码国家地区电话传真

订单编号FK

客户编号员工编号订单日期要货日期送货日期送货方式运费收货人送货地址送货城市哈尔滨师范大学恒星学院使用表设计器创建PrimaryKey约束在表设计器中，选择需要设置主键的列(如需要设置多个列为主键，则选中所有需要设置为主键的所有列)，单击鼠标右键，然后从弹出的快捷菜单中选择“设置主键”命令，完成主键设置，这时主键列的左边会显示“黄色钥匙”图标启动.如何修改和删除PrimaryKey约束呢?

建立约束使用T-SQL语句创建PRIMARYKEY约束

创建主键约束的语法形式如下：

[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]

(column_name[,…n])

其中，CLUSTERED|NONCLUSTERED表示所创建的UNIQUE约束是聚集索引还是非聚集索引，默认为CLUSTERED聚集索引。哈尔滨师范大学恒星学院

使用表设计器创建Unique

约束

在表设计器中可以创建、修改和删除Unique

约束.

要点:选中列,按右键,选“索引/键(I)”.

如何创建Check约束呢?建立约束使用T-SQL语句创建UNIQUE约束

创建唯一性约束的语法形式如下：

[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]

其中，CLUSTERED|NONCLUSTERED表示所创建的UNIQUE约束是聚集索引还是非聚集索引，默认为NONCLUSTERED非聚集索引。哈尔滨师范大学恒星学院

CHECK约束

CHECK约束用于限制输入到一列或多列的值的范围，从逻辑表达式判断数据的有效性，也就是一个列的输入内容必须满足CHECK约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。建立约束使用T-SQL语句创建CHECK约束

创建检查约束的语法形式如下：

[CONSTRAINTconstraint_name]CHECK(check_expression)

其中，check_expression为检查表达式。使用表设计器创建DEFAULT约束

在表设计器中可以创建、修改和删除DEFAULT约束。其操作步骤如下：

在表设计器中，选择需要设置DEFAULT值的列，在下面“列属性”的“默认值或绑定”栏中输入默认值，然后单击工具栏中的“保存”按钮，即完成DEFAULT约束的创建。

使用T-SQL语句创建DEFAULT约束

创建默认值约束的语法形式如下：

[CONSTRAINTconstraint_name]DEFAULTconstraint_expression

其中，constraint_expression为默认值。哈尔滨师范大学恒星学院

使用表设计器建立ForeignKey约束

外键(ForeignKey)用于建立和加强两个表(主表与从表)的一列或多列数据之间的链接，当数据添加、修改或删除时，通过外键约束保证它们之间数据的一致性.

定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束.ForeignKey约束要求列中的每个值在所引用的表中对应的列中都存在.建立约束哈尔滨师范大学恒星学院在表设计器的空白处单击鼠标右键选择“关系”，弹出“外键关系”对话框.

单击“添加”按钮可以添加外键，单击“表和列规范”右侧的按钮，弹出“表和列”对话框，选择相应的主键表和外键表及对应的主键和外键，单击“确定”按钮返回“外键关系”对话框，在“标识”的名称栏中填写外键名称即可创建外键约束.要删除外键，只要在该对话框左侧栏中选择对应的外键名称，单击“删除”按钮即可.建立约束使用T-SQL语句创建FOREIGNKEY约束

创建外键约束的语法形式如下：

[CONSTRAINTconstraint_name]{FOREIGNKEY（column_name[,…n]）/*定义外键*/REFERENCESreferenced_table_name[(ref_column[,…n])]}[,…n]

参数说明如下。

referenced_table_name是FOREIGNKEY约束引用的表的名称。

column_name是FOREIGNKEY约束所引用的表中的某列。

---创建表时给出列的约束.

CREATETABLEstudent1

(idchar(7)primarykeyclustered,namechar(10)notnull,sexchar(2)check(sexin('F','M')),birthdaysmalldatetime,birth_placevarchar(50)null,classchar(10))USEpubsCREATETABLEdbo.客户(客户编号intprimarykey,客户名称char(50)notNull,住址char(50),生日smalldatetime,int,建立日期smalldatetimedefaultGetDate(),constraintchk_phonecheck(like'(010)[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),constraintchk_idcheck(客户编号between0and10000))例题---下列代码测试“建立日期”字段是否输入默认值.USEpubsINSERT客户(客户编号,客户名称,生日)VALUES(1001,'Candy','1980/04/22')GOSELECT*FROM客户CREATETABLE产品

(产品编号intprimarykey,

产品名称varchar(30));CREATETABLE订单

(订单编号int,

产品编号int

foreignkeyreferences产品(产品编号),

订单数量int,

订单日期datetimedefaultGetdate());

下面的范例使用订单表与产品表来说明如何用代码建立”外键”.首先,产品表有”主键”,才可用订单表的”产品编号”字段为参考来建立”外键”.“外键”不一定要参考另一个表的”主键”,也可以定义为参考其他表中unique条件约束的列.”外键”可包含Null值.同时,复合”外键”的任何列包含了Null值,将会略过数据验证工作.若要确保输入的数据都符合”外键”要求,可对所有参与的列指定非空.

例题在执行创建产品信息表的操作时，指定产品编号为主键值，并且创建一个聚集索引.

createtableproducts(idchar(10)notnull,namechar(20)notnull,pricemoneydefault0.01,quantitysmallintnullconstraintpk_idprimarykeyclustered(id)

)增加、删除和修改列

例题在雇员信息表中增加、删除和修改字段的SQL语句的程序清单如下：

CREATETABLEemployees(idchar(8)primarykey,namechar(20)notnull,departmentchar(20)null,memochar(30)null,ageintnull)ALTERTABLEemployees

ADDsalaryintnull

DROPcolumnageALTERcolumnmemovarchar(200)null

若要修改表的定义并在现有的表中新增列,操作必须在该列允许Null值或包含Default约束的条件下进行.

由于新的列被加入表中,数据库引擎会针对表中的每条现有数据将值插入该列内.因此,当列被加入表中时,替该列加入Default定义会很有用或者指定该列允许Null值.不满足这些条件,将返回错误信息.如果需要增加非空列呢?增加新列的限制

用户可以删除现有表中的列,但具有下列特性的列将无法删除.---用于索引的列.---用于CHECK、FOREIGNKEY、

UNIQUE或PRIMARYKEY条件约束的列.---与Default定义建立关联,或关联至默认对象的列.---关联至规则的列.---注册成全文检索支持的列.---作为表的全文检索索引键的列.删除列的限制●查看表格的定义

●查看表格中的数据●查看表格与其它数据库对象的依赖关系●利用系统存储过程sp_help查看表的信息例如

sp_help

titles

sp_helpdb

pubs

execsp_helpdb

查看