SQL第3章管理数据表

1第3章管理数据表及数据完整性3.1SQLServer表的概念和数据类型3.2创建和管理数据表3.3完整性的概念和约束类型

3.4约束的创建和查看3.5删除约束、使用规则和使用默认值23.1SQLServer表的概念1.表的概念

在SQLServer数据库中，表定义为列的集合，与Excel电子表格相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。学号姓名性别出生日期系部入学时间000101张三男1980计算机2000000201李伟男1981经济管理2000010101王丽女1982数学2001关系名(表名)属性（列或字段）关系模式学生表

属性名记录或元组主键（码）33.1SQLServer表的概念2.表的设计在SQLServer创建表有如下限制：每个数据库里最多有20亿个表。每个表上最多可以创建一个聚集索引和249个非聚集索引。每个表最多可以配置1024个字段。每条记录最多可以占8060B，但不包括text字段和image字段。

4第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表53.2SQLServer2005数据类型1.二进制数据2.字符型数据类型3.Unicode字符数据4.日期时间型数据5.整数型数据6.精确数值型数据7.近似数值类型8.货币数据9.位类型数据

63.2SQLServer2005数据类型1.二进制数据功能：常用于存储图像等数据，包括长二进制数据binary、变长二进制数据varbinary和image三种。类型存储空间大小

(字节)最大取值说明Binary(n)N+4存储空间固定N∈[1,8000]默认为1输入不足n+4字节,补足后存储;超过则截断后存储.Varbinary(n)变长存储数据N∈[1,8000]默认为1输入不足n+4字节,按实际长度存储;超过则截断后存储.image231-173.2SQLServer2005数据类型2.字符型数据类型功能:用于存储汉字、英文字母、数字、标点和各种符号，必须由英文单引号括起来。类型存储空间大小(字节)最大取值说明char(n)固定长度存储字符串N∈[1,8000]默认为1输入不足n个字节,补足后存储;超过则截断后存储.varchar(n)按变长存储字符串N∈[1,8000]默认为1输入不足n字节,按实际长度存储;超过则截断后存储.text231-1个字节83.2SQLServer2005数据类型3.Unicode字符数据功能：存放Unicode字符数据.支持的字符范围更大，存储所需要的空间也更大。类型存储空间大小(字节)最大取值说明nchar(n)固定长度存储字符串N∈[1,4000]默认为1输入不足n个字节,补足后存储;超过则截断后存储.nvarchar(n)按变长存储字符串N∈[1,4000]默认为1输入不足n字节,按实际长度存储;超过则截断后存储.ntext230-1个字节93.2SQLServer2005数据类型4.日期时间型数据功能：用于存储日期和时间数据。类型存储范围datetime存储从1753年1月1日到9999年12月31日的日期和时间数据，精确度为3%秒。smalldatetime存储从1900年1月1日到2079年6月6日的日期和时间数据，精确度为分.注意:当使用日期格式数据时，在字符串中可以使用斜杠’/’,连字符’-’或句号.作为分隔符。在SQLSERVER2008中增加了date、time、datetime2、datetimeoffset类型103.2SQLServer2005数据类型5.整数型数据功能：用于存储整型数据。类型存储大小（字节）数据范围bigint8-263~263-1int4-231~231-1smallint2-215~215-1tinyint10~255113.2SQLServer2005数据类型6.精确数值型数据

功能：用于存储带有小数点且小数点后位数确定的实数。类型说明数据范围decimal(p,[s])p指定存储数据的最大位数，不含小数点，p∈[1，38]；s指定存储的小数的最大位数，s∈[0，p].默认小数位是0。-1038+1~1038-1numeric(p,[s])123.2SQLServer2005数据类型7.近似数值类型功能：用于存储浮点数。

类型说明数据范围float(n)n为精度，n∈[1，53]存放-1.79E+308~1.79E+308数值范围内的浮点数real存储大小为4个字节从-3.40E+38到3.40E+38之间的浮点数字数据133.2SQLServer2005数据类型8.货币数据类型存储大小数据范围money8个字节货币数据值介于-263与263-1之间，精确到货币单位的千分之十smallmoney4个字节货币数值介于-214,748.3648~+214.748,3647之间，精确到货币单位的千分之十143.2SQLServer2005数据类型9.位类型数据功能：位类型数据用于存储整数，只能取

1、0

或NULL，常用于逻辑数据的存取。说明：在位类型的字段中输入0和1之外的任何值，系统都会作为1来处理。如果一个表中有8个以下的位类型数据字段，则系统会用一个字节存储这些字段；如果表中有9个以上16个以下位类型数据字段，则系统会用两个字节来存储这些字段。

15第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表163.3创建数据表1.使用SSMS创建表

创建表的步骤:1）定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。

2）设置约束：设置约束是为了限制该列输入值的取值范围，以保证输入数据的正确性和一致性。

3）添加数据：表结构建立完成之后，就可以向表中输入数据。

173.3创建数据表1.使用SSMS创建表

例1:请在student数据库中建立“学生基本信息”表的结构。注:主键的字段值不能为空,且字段值在表中必须唯一字段名字段数据类型长度是否为空约束学号char8否主键姓名char10否

性别char2是

默认值‘男’出生年月datetime8是

籍贯varchar20是

家庭住址varchar60是电话char15是

所属班级char8是

183.3创建数据表2.使用T-SQL语句创建表

1）语法：CREATETABLE[database_name.[owner].|owner.]table_name({column_definition>}|[{PRIMARYKEY|UNIQUE}[,…N]])<column_definttion>::={column_name

data_type}[[DEFAULT

constraint_expression]|[IDENTITY[(seed,increment)]]][<column_constraint>][,…n]193.3创建数据表语法注释：●database_name：指定创建的表所在的数据库，在当前数据库创建表时该项可以省略。●

owner：指定表的拥有者，如果表的拥有者为当前用户，该项可以省略。●

table_name：指定新建表的名称。●

column_definttion：为表中字段的定义表达式。●

column_name：为表中的字段名。●

data_type：为字段的数据类型。●

PRIMARYKEY：为主键约束关键字。●

UNIQUE：为唯一约束关键字。●

DEFAULT：为默认约束关键字。●

IDENTITY：为自动编号标识。●

Seed：为自动标识的开始值，默认为1。●

Increment：为自动编号的步长或增量，默认为1。203.3创建数据表

例2:创建带有参照约束的学生表stud_info，学生表的表结构定义如下表所示。“学号”字段为学生表的主键。字段名字段数据类型长度是否为空约束学号char8否主键姓名char10否

性别char2是

默认值‘男’出生年月datetime8是

籍贯varchar20是

家庭住址varchar60是电话char15是

所属班级char8是

213.3创建数据表创建“学生”表的代码如下：USEStudentGO

CREATETABLEstud_info(学号char(8)PRIMARYKEY,姓名char(10)NOTNULL,性别char(2)DEFAULT‘男’,出生年月datetime,籍贯varchar(20),家庭住址varchar(60),电话char(15),所属班级char(8)）GO223.3创建数据表例3：在学生管理数据库中用T-SQL语句创建一个学生成绩表

stud_score

，表结构如下，其中包含标识列和计算列。233.3创建数据表实现的T-SQL语句:CREATETABLEstud_score(

Scoreid

intIDENTITY(1,1)PRIMARYKEY,

Stud_nochar(8)NOTNULL,MathintDEFAULT0,ChineseintDEFAULT0,ComputerintDEFAULT0,

total_scoreASMath+Chinese+computer)GO243.3创建数据表设置列的标识属性时，应注意:该列的数据类型必须是decimal,int,numeric,smallint,bigint,tinyint中的一种，才可以设置标识属性。标识列不允许为空值，也不能包含默认属性。每个表中只允许有一个标识列，并且不可以修改。设置计算列时，应注意:在计算列中不能添加如primarykey、unique、foreignkey、default等约束条件。不能对计算列进行赋值。25第3章管理数据表3.1SQLServer表的概念3.2SQLServer中的数据类型3.3创建数据表3.4管理数据表

263.4管理数据表

3.4.1修改表结构

3.4.2删除数据表

3.4.3查看数据表273.4.1修改表结构1使用SSMS修改表结构例4：修改“student”库中“学生基本信息表”表结构。283.4.1修改表结构2使用T-SQL语言修改表1）语法：ALTERTABLE

table_name

{[ALTERCOLUMN

column_name

{new_data_type[(precision[,scale])][NULL|NOTNULL]}]|ADD{[<column_definition>]}[,...n]|DROP{[CONSTRAINT]constraint_name

|COLUMN

column}[,...n]}[[DEFAULT

constant_expression][WITHVALUES]|[IDENTITY[(seed,increment)]]

293.4.1修改表结构2）语法注释：

ALTERCOLUMN：修改表列属性的子句

ADD：增加列或约束的子句

DROPCOLUMN：删除表列的子句

table_name：需要修改表的表格名称

column_name：希望增加的字段名

data_type：需要增加的字段的数据类型名

collation_name：排序规则名

303.4.1修改表结构例5：使用T-SQL代码修改学生基本信息表stud_info结构,增加字段“QQ号码”，数据类型为varchar，长度为15，可为空；增加入学时间字段，时间/日期类型，默认时间为‘2006-9-1’。USEstudentGOALTERTABLEstud_infoADDQQ号码varchar(15)GOALTERTABLEstud_infoADD入学时间datetimeDEFAULT‘2006-9-1’GO313.4.1修改表结构例6：使用T-SQL代码修改学生基本信息表stud_info结构，修改字段“家庭住址”的长度为100。实现的T-SQL语句USEstudentGOALTERTABLEstud_infoALTERCOLUMN家庭住址varchar(100)GO323.4.1修改表结构例7：使用T-SQL语句删除学生基本信息表stud_info的QQ号码字段。实现的T-SQL语句USEstudentGOALTERTABLEstud_infoDROPCOLUMNQQ号码GO

333.4管理数据表

3.4.1修改表结构

3.4.2删除数据表

3.4.3查看数据表343.4.2删除数据表1使用SSMS删除数据表2使用T-SQL语句删除数据表语法格式为：

DROPTABLEtable_name例8：使用T-SQL语句删除学生基本信息表。实现的T-SQL语句

USEstudentGODROPTABLE学生基本信息表

GO353.4管理数据表

3.4.1修改表结构

3.4.2删除数据表

3.4.3查看数据表363.4.3

查看数据表1使用SSMS对象资源管理器查看表属性信息。在对象资源管理器中，右键单击“student”数据库的学生基本信息表stud_info，从弹出的快捷菜单中选择“属性”命令。

373.4.3

查看数据表2使用T-SQL语句查看数据表

Execsp_help

table_name

例9:查看学生基本信息表stud_info

属性信息。

ExecSp_help

stud_info383.5完整性的概念完整性的概念

数据完整性指存储在数据库中的数据正确无误并且相关数据具有一致性。数据完整性可分为以下四种：1、实体完整性：要求在表中不能存在两条完全相同的记录。实现实体完整性的方法有：主键约束、唯一约束、指定IDENTITY属性。393.5完整性的概念2、域完整性：要求向表中指定列输入的数据必须具有正确的数据类型、格式及有效的数据范围。

实现域完整性的方法有：检查约束、外键约束、非空约束、规则及在建表时设置的数据类型。3、参照完整性：指作用于有关联的表通过主键和外键或主键和惟一键间的关系，使表中的键值在相关表中保持一致。

实现参照完整性的方法有：外键约束.4、用户定义的完整性：指应用领域需要遵守的约束条件。实现用户定义完整性的方法包括规则、触发器和存储过程等403.6约束的类型

1.NOTNULL（非空）约束

2.

PRIMARYKEY（主键）约束3.

UNIQUE（惟一）约束4.

CHECK（检查）约束

5.FOREIGNKEY（外健）约束

6.DEFAULT（默认）约束

413.6约束的创建使用CREATETABLE

或者ALTERTABLECREATETABLE

是在创建表时创建约束ALTERTABLE

是在一个已有的表上添加约束可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，一般此类约束都是在表创建完成后再进行添加约束.423.6约束的创建CREATETABLE

table_name

({column_name

data_type}[...n]}

[[DEFAULT

constant_expression]

]

<[CONSTRAINTconstraint_name

]

{

|[{PRIMARYKEY|UNIQUE}

[CLUSTERED|NONCLUSTERED]

|[[FOREIGNKEY]

REFERENCESref_table[(ref_column

)]

|CHECK]

(logical_expression

)}>

433.6.1

创建主键约束1.创建主键可以在建表时使用CREATETABLE

命令完成。

[示范案例1]

在student库中，建立一个民族表（民族代码，民族名称），将民族代码指定为主键。其程序清单如下：CREATETABLEnative(native_id

char(2)CONSTRAINT

pk_mzdm

PRIMARYKEY,

Native_name

varchar(30)NOTNULL）

GO443.6.1

创建主键约束2.

为已存在的表创建主键约束，其语法格式如下：

ALTERTABLE

table_name

ADDCONSTRAINT

constraint_name

PRIMARYKEY[CLUSTERED|NONCLUSTERED]

{(column[,…n])}453.6.1

创建主键约束[示范案例2]

使用T-SQL语句在学生信息管理数据库“班级”表class中，指定字段“班级代号”class_id为表的主键。

程序清单如下：

ALTERTABLEclass

ADDCONSTRAINT

pk_bjbh

PRIMARYKEY(class_id)463.6.1

创建主键约束每张表只能有一个

PRIMARYKEY约束输入的值必须是惟一的不允许空值将在指定列上创建惟一索引473.6.2创建惟一约束

为存在的表创建惟一约束，其语法格式如下：

ALTERTABLE

table_name

ADDCONSTRAINT

constraint_name

UNIQUE

[CLUSTERED|NONCLUSTERED]

{(column[,…n])}483.6.2创建惟一约束[示范案例3]

使用T-SQL语句在学生信息管理数据库student中，为民族表native中的“民族名称”native_name字段创建一个惟一约束。程序清单如下：

ALTERTABLE

native

ADDCONSTRAINT

uk_mzmz

UNIQUE(native_name)GO493.6.2创建惟一约束允许一个空值在一个表上允许多个UNIQUE

约束可在一个或者多个列上定义是通过一个惟一索引强制约束的503.6.3创建检查约束

使用SQL语句为已存在的表创建检查约束，其语法格式如下：

ALTERTABLE

table_name

ADDCONSTRAINT

constraint_name

CHECK

(logical_expression)

513.6.3创建检查约束

[示范案例4]

使用T-SQL语句在学生信息管理数据库student中，为学生成绩表score中的成绩“Sscore”字段创建一个检查约束，以保证输入的数据大于等于0而小于等于100。程序清单如下：

ALTERTABLE

score

ADDCONSTRAINT

ck_chengji

CHECK

(Sscore>=0andSscore<=100)

523.6.3创建检查约束在每次执行

INSERT

或者

UPDATE

语句的时候校验数据值。可以引用同表中的其他列,但不能引用其他表中的列。不能包含子查询。列级CHECK约束可省略名字，让系统自动生成。表达式可以用AND以及OR连接以表示复杂逻辑。CHECK约束中可使用系统函数。533.6.4

创建默认约束

使用SQL语句为已存在的表创建默认约束，其语法格式如下：

ALTERTABLE

table_name

ADDCONSTRAINT

constraint_name

DEFAULT

constant_expression

[FOR

column_name]

543.6.4

创建默认约束

[示范案例5]

使用T-SQL语句在学生信息管理数据库student中，为教师表teachers中的学历tdegree字段创建一个默认约束，其默认值为‘本科’

。程序清单如下：

ALTERTABLE

teachers

ADDCONSTRAINT

df_xueli

DEFAULT‘本科’

FOR

tdegree

553.6.4

创建默认约束DEFAULT约束创建时将检查表中的现存数据。DEFAULT约束只对INSERT语句有效。每列只能定义一个DEFAULT约束。不能和“标识”属性共同使用。为具有PRIMARYKEY或UNIQUE约束的列指定默认值是没有意义的。常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号。563.6.5创建外键约束

使用SQL语句创建外键约束的语法格式为：

ALTERTABLE

table_name

ADDCONSTRAINT

constraint_name

FOREIGNKEY

(column_name[,…])

REFERENCES

ref_table[(ref_column_name[,…])

]573.6.5创建外键约束[示范案例6]

使用T-SQL语句在学生信息管理数据库student中，为学生基本信息表stud_info中的“所属班级”字段创建一个外键约束，引用班级表class的班级代码class_id字段,从而保证输入有效的班级代码

。程序清单如下：

ALTERTABLE

stud_info

ADDCONSTRAINT

fk_bjdm

FOREIGNKEY(所属班级

)

REFERENCES

class(class_id)

583.6.5创建外键约束提供了单列或多列的引用完整性。FOREIGNKEY子句中指定的列的个数和数据类型必须和REFERENCES子句中指定的列的个数和数据类型匹配。修改数据的时候，用户必须在被FOREIGNKEY约束引用的表上具有SELECT或REFERENCES权限。若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句。59总结：使用约束的注意事项SQLServer

里的约束只是“最后防线”当给一个表添加约束的时候，SQLServer

将检查现有数据是否违反约束。建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称。名称必须惟一，且符合SQLServer

标识符的规则。603.7查看约束的定义

1.使用系统存储过程查看约束信息2.使用SSMS查看约束信息

611.使用系统存储过程查看约束信息

1）系统存储过程sp_help用来查看约束的名称、创建者、类型和创建时间，其语法格式为：

EXECsp_help

约束名称

2）如果约束存在文本信息，可以使用sp_helptext来查看，其语法格式为：

EXECsp_helptext

约束名称

例：使用系统存储过程查看学生表上的约束ck_csrq的文本信息。其程序清单如下：

EXECsp_helptext

ck_csrq

3.7查看约束的定义

622.使用SSMS查看约束信息的步骤为：

1）在SSMS中，选择要查看约束的表（如学生表），打开表设计器。

2）在表设计器中可以查看主键约束、空值约束和默认值约束。

3）在表设计器中，右击鼠标，从弹出的快捷菜单中选择“属性”命令。弹出“属性”对话框

4）在“属性”对话框中通过切换选项卡，可以查看主键约束、外键约束与CHECK约束信息。

3.7查看约束的定义

633.8删除约束

1.使用SSMS删除表约束

2.使用DROP命令删除表约束

643.8删除约束1.使用SSMS删除表约束在表设计器的窗口中，可以删除主键，去掉默认值。在表设计器的“属性”窗口中，可以通过切换选项卡，选择约束名称，单击“删除”按钮，分别删除主键约束、外键约束和CHECK约束。

653.8删除约束662.使用DROP命令删除表约束在T-SQL语言中，也可以方便的删除一个或多个约束，其语法格式为：

ALTERTABLE

table_name

DROPCONSTRAINT

constraint_name[,…n]3.8删除约束67[示范案例7]

使用T-SQL语句删除Northwind数据库中CK_Quantity约束。程序清单如下：

ALTERTABLE[orderdetails]

DROPCONSTRAINT

ck_quantity3.8删除约束68决定使用何种约束完整性类型约束类型描述域DEFAULT如果在INSERT语句中未显式提供值，则指定为列提供的值CHECK指定列中可接受的数据值REFERENCES基于其他表中的列的值，指定可接受的用于更新的数据值实体PRIMARYKEY惟一标识每一列，确保用户没有输入重复的值。同时创建一个索引以增强性能。不允许空值UNIQUE确保在非主键列中不输入重复值，并创建一个索引以增强性能。允许空值引用FOREIGNKEY定义一列或多列的值与同表或其他表中主键的值匹配CHECK基于同表中其他列的值，指定列中可接受的数据值69规则与CHECK约束的比较1、check约束比规则更简明，它可以在建表时由createtable语句将其作为表的一部分进行指定。2、规则需要单独创建，然后绑定到列上。3、在一个列上只能应用一个规则，但是却可以应用到多个check约束。4、规则的优点是：一个规则只需要定义一次就已可以多次应用，可以应用到多个表或多个列，还可以应用到用户定义的数据类型上。

3.9使用规则703.9使用规则1.创建规则的命令是CREATERULE，语法格式为：

CREATERULE

rule_name

AS

condition_expression

2.绑定规则其语法格式为:

[EXECUTE]sp_bindrule

‘规则名称’,’表名.字段名’|’

自定义数据类型名’713.9使用规则[示范案例8]

在Northwind数据库中创建规则,名称为region_