数据表的创建和管理

数据表的创建和管理第1页/共60页第4章数据表的创建和管理第2页/共60页本章学习目标掌握SQLServer2005中的数据类型掌握如何利用SQLServer管理控制台和Transact-SQL语言创建表掌握创建、删除和修改约束的方法第3页/共60页本章学习目标掌握增加、删除和修改字段的方法掌握如何查看数据表的定义、表中数据、数据库对象之间的依赖关系掌握如何利用SQLServer管理控制台和Transact-SQL语言删除表第4页/共60页4.1数据类型数据类型决定了数据的存储格式，代表了各种不同的信息类型。SQLServer提供系统数据类型集，该类型集定义了可与SQLServer一起使用的所有数据类型。系统数据类型是SQLServer预先定义好的，可以直接使用。SQLServer2005中的数据类型分为七大类，共28种。第5页/共60页4.1.1精确数字类型精确数字类型分为整数类型、位类型、数值类型和货币类型等四类：（1）整数类型（2）位类型（3）数值类型（4）货币类型第6页/共60页4.1.2近似数字类型近似数字类型包括float和real这2类。它们用于表示浮点数值数据。第7页/共60页4.1.3日期和时间类型日期和时间类型包括datetime与smalldatetime。它们是用于表示某天的日期和时间的数据类型。当存储datetime数据类型时，默认的格式是：MMDDYYYYhh:mmA.M./PM。当插入数据或者在其他地方使用datetime类型时，需要用单引号把它括起来。第8页/共60页4.1.4字符串类型包括char、varchar和text数据类型。char[(n)]：固定长度，非Unicode字符数据，取值范围为1至8,000。varchar[(n|max)]：可变长度，非Unicode字符数据。n的取值范围为1至8,000。text：用于存储大容量文本数据。第9页/共60页4.1.5Unicode字符串类型包括nchar、nvarchar和ntext数据类型。nchar[(n)]：n个字符的固定长度的Unicode字符数据。n值必须在1到4,000之间。nvarchar[(n|max)]：可变长度Unicode字符数据。n值在1到4,000之间。Ntext：是长度可变的Unicode数据，最大长度为230-1(1,073,741,823)个字符。第10页/共60页4.1.6二进制字符串类型包括binary、varbinary和image数据类型。第11页/共60页4.1.7其他数据类型包括cursor、sql_variant、table、timestamp、uniqueidentifier、xml数据类型。第12页/共60页4.2创建数据表数据表是数据库的主要对象，用来存储各种各样的信息。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表通常都有一个主关键字（又称为主键），用于唯一地确定一条记录。在SQLServer2005中，每个数据库最多可包含20亿个表，每个表可包含1,024个字段。在同一个表中不允许有相同名称的字段。第13页/共60页4.2.1使用SQLServer管理控制台

创建数据表在SQLServer管理控制台的“对象资源管理器”中，展开指定的服务器和数据库，打开想要创建新表的数据库，右击“表”对象，并从弹出的快捷菜单中选择“新建表”选项，在窗口右部会出现新建数据表的对话框。第14页/共60页说明：在SQLServer的早期版本中，数据库用户和架构在概念上是同一对象。从SQLServer2005开始，用户和架构区分开来了，架构用作对象的容器。每个对象都属于一个数据库架构。4.2.1使用SQLServer管理控制台

创建数据表第15页/共60页在数据库中数据表名必须是唯一的，但是如果为数据表指定了不同的架构，就可以创建多个相同名称的数据表，即在不同的架构下可以存在相同名称的数据表。在使用这些数据表时，需要在数据表的名称前面加上架构的名称。4.2.1使用SQLServer管理控制台

创建数据表第16页/共60页4.2.2使用Transact-SQL语言

创建数据表CREATETABLE[database_name.[schema_name].|schema_name.]table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])第17页/共60页<column_definition>::=column_name<data_type>[NULL|NOTNULL|DEFAULTconstant_expression|IDENTITY[(seed,increment)]][<column_constraint>[...n]]4.2.2使用Transact-SQL语言

创建数据表第18页/共60页<column_constraint>::=[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED]|[FOREIGNKEY]REFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]|CHECK(logical_expression)}4.2.2使用Transact-SQL语言

创建数据表第19页/共60页<table_constraint>::=[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED](column[ASC|DESC][,...n])|FOREIGNKEY(column[,...n])REFERENCESreferenced_table_name[(ref_column[,...n])][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][NOTFORREPLICATION]|CHECK(logical_expression)}4.2.2使用Transact-SQL语言

创建数据表第20页/共60页其中各参数的说明如下：database_name：在其中创建表的数据库的名称。schema_name：新表所属架构的名称。table_name：新表的名称。表名必须遵循标识符规则。column_name：表中列的名称。4.2.2使用Transact-SQL语言

创建数据表第21页/共60页computed_column_expression：定义计算列的值的表达式。DEFAULT：如果在插入过程中没有显式地提供值，则指定为列提供的值。[ASC|DESC]：指定加入到表约束中的一列或多列的排序顺序。默认值为ASC。4.2.2使用Transact-SQL语言

创建数据表第22页/共60页例4-2-1：在数据库STUDENT中创建一个学生基本信息表（名为t_student）。4.2.2使用Transact-SQL语言

创建数据表第23页/共60页4.3创建、删除和修改约束在SQLServer2005中主要可以使用以下几种约束：非空约束（NOTNULLconstraint）主键约束（primarykeyconstraint）唯一性约束（uniqueconstraint）默认约束（DEFAULTconstraint）检查约束（checkconstraint）外键约束（foreignkeyconstraint）。第24页/共60页4.3创建、删除和修改约束约束可以分为表级约束和列级约束两种：列级约束：列级约束是行定义的一部分，只能够应用在一列上。表级约束：表级约束的定义独立于列的定义，可以应用在一个表中的多列上。第25页/共60页4.3.1非空约束空值（或NULL）不同于零(0)、空白或长度为零的字符串（如""）。出现NULL通常表示值未知或未定义。列的为空性决定表中的行是否可为该列包含空值。创建非空约束常用的操作方法有如下两种：1．使用SQLServer管理控制台创建非空约束第26页/共60页4.3.1非空约束2．使用Transact-SQL语句创建非空约束[CONSTRAINT<约束名>]NOTNULL例4-3-1：为t_student2表中的s_number字段创建非空约束。第27页/共60页4.3.2主键约束主键能够唯一地确定表中的每一条记录，主键不能取空值。主键约束可以保证实体的完整性，是最重要的一种约束。主键的添加、删除和修改操作方法有两种：SQLServer管理控制台操作法和Transact-SQL语句操作法。第28页/共60页4.3.2主键约束1．通过SQLServer管理控制台完成添加、删除和修改主键的操作2．使用Transact-SQL语句设置主键约束其语法形式如下：CONSTRAINTconstraint_namePRIMARYKEY[CLUSTERED|NONCLUSTERED]（column_name[,…n]）第29页/共60页4.3.2主键约束例4-3-2：在数据库STUDENT中创建一个t_student3表，并定义s_number为主键。增加主键后，就限定了该记录中不能插入编号相同的两个或者多个数据信息，从而保证了表中的记录信息是唯一的。第30页/共60页4.3.3唯一性约束唯一性约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。1．通过SQLServer管理控制台创建唯一性约束第31页/共60页4.3.3唯一性约束2．使用Transact-SQL语句创建唯一性约束CONSTRAINTconstraint_nameUNIQUE[CLUSTERED|NONCLUSTERED]（column_name[,…n]）例4-3-3：创建一个学生信息表t_student4，其中s_name字段具有唯一性。第32页/共60页4.3.4检查约束检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。1．使用SQLServer管理控制台创建检查约束第33页/共60页4.3.4检查约束2．使用Transact-SQL语句创建检查约束其语法形式如下：CONSTRAINTconstraint_nameCHECK[NOTFORREPLICATION]（logical_expression）第34页/共60页4.3.4检查约束其中，参数NOTFORREPLICATION用于指定在把从其他表中复制的数据插入到表中时检查约束对其不发生作用。logical_expression用于指定逻辑条件表达式，返回值为TRUE或者FALSE。第35页/共60页4.3.4检查约束例4-3-4：创建一个学生信息表t_student5，其中输入性别字段值时，只能接受“男”或者“女”，而不能接受其他数据。第36页/共60页4.3.5默认约束默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。1．使用SQLServer管理控制台创建默认约束进入设计表对话框后，选定要设置的字段后，在窗口下部的“列属性”中找到“（常规）”下的“默认值或绑定”框，在默认值栏中输入该字段的默认值，即可创建默认约束。第37页/共60页4.3.5默认约束2．使用Transact-SQL语句创建默认约束可以在createtable命令中定义列时，使用DEFAULT关键字指定默认值。例4-3-5：创建一个学生信息表t_student6，为sex字段创建默认约束“男”。第38页/共60页4.3.6外键约束外键约束主要用来维护两个表之间数据的一致性，实现数据表之间的参照完整性。创建外键约束常用的操作方法有如下两种：1．在SQLServer管理控制台中添加外键约束第39页/共60页4.3.6外键约束2．使用Transact-SQL语句设置外键约束

CONSTRAINTconstraint_nameFOREIGNKEY（column_name[,…n]）REFERENCESref_table[（ref_column[,…n]）]第40页/共60页4.3.6外键约束其中，各参数的说明如下：REFERENCES：用于指定要建立关联的表的信息。ref_table：用于指定要建立关联的表的名称。ref_column：用于指定要建立关联的表中相关列的名称。说明：外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配。第41页/共60页4.3.6外键约束例4-3-6：创建t_score表，并为t_score创建外键约束，该约束把表t_score中的学生学号（s_number）字段和表t_student中的学生学号（s_number）字段关联起来，实现t_score中的学生学号（s_number）字段的取值要参照表t_student中的学生学号（s_number）字段的数据值。第42页/共60页4.4增加、删除和修改字段当数据表的结构创建完成后，用户还可以根据实际需要随时更改表结构。用户可以增加、删除和修改字段，更改数据表名称等。在SQLServer中可以利用SQLServer管理控制台和SQL语言的altertable语句两种方法来增加、删除和修改字段。第43页/共60页4.4.1利用SQLServer管理控制台

增加、删除和修改字段在SQLServer管理控制台中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“修改”选项。则会出现显示已有的表结构的窗口。在该对话框中，可以通过鼠标操作完成增加、删除和修改字段的操作。第44页/共60页4.4.2使用Transact-SQL语言

增加、删除和修改字段ALTERTABLE[database_name.[schema_name].|schema_name.]table_name{ALTERCOLUMNcolumn_name{[type_schema_name.]type_name[({precision[,scale]|max})][NULL|NOTNULL]}|[WITH{CHECK|NOCHECK}]ADD{<column_definition>|<computed_column_definition>|<table_constraint>}[,...n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}[,...n]}第45页/共60页例4-4-1：在数据库STUDENT中创建一个t_course1表，然后在表中增加一个c_pnumber字段，删除表中的credit字段，并且修改c_name字段的数据长度。4.4.2使用Transact-SQL语言

增加、删除和修改字段第46页/共60页4.5查看数据表数据表保存在数据库中，可以随时查看数据表的有关信息。比如数据表属性、数据表结构、表中的记录以及数据表与其他数据库对象之间的依赖关系等。第47页/共60页4.5.1查看数据表属性在SQLServer管理控制台中，打开指定的服务器和数据库，右击要查看的数据表，然后从弹出的快捷菜单中选择“属性”选项。第48页/共60页4.5.2查看数据表中的数据在SQLServer管理控制台中，打开指定的数据库并展开“表”对象，右击要查看数据的数据表，并从弹出的快捷菜单中选择“打开表”选项。会出现显示表中数据的对话框。第49页/共60页4.5.3查看数据表与其他数据库

对象的依赖关系在SQLServer管理控制台中，右击要查看的数据表，从弹出的快捷菜单中选择“查看依赖关系”选项，则会出现“对象依赖关系”对话框。在该对话框中，可以通过选择不同的选项来查看依赖于此数据表的对象，也可以查看此数据表依赖的对象。第50页/共60页4.5.4利用系统存储过程查看表的信息系统存储过程sp_help可以提供指定数据库对象的信息，也可以提供系统或者用户定义的数据类型的信息，其语法形式如下：sp_help[[@objname=]name]sp_help存储过程只用于当前的数据库，其中[objname=]name子句用于指定对象的名称。如果不指定对象名称，sp_help存储过程就会列出当前数据库中的所有对象名称、对象的所有者和对象的类型。第51页/共60页4.5.4利用系统存储过程查看表的信息例4-5-1：（1）显示STUDENT数据库中所有对象的信息。（2）显示t_student数据表的信息。第52页/共60页4.6删除数据表第53页/共60页4.6.1利用SQLServer管理控制台

删除数据表在SQLServer2005的管理控制台中，展开指定的数据库，并展开其中的“表”对象，右击要删除的数据表，从弹出的快捷菜单中选择“删除”选项，会出现“删除对象”对话框。删除某个数据表之前