表的创建和管理约束.docx
《表的创建和管理约束.docx》由会员分享，可在线阅读，更多相关《表的创建和管理约束.docx（16页珍藏版）》请在冰豆网上搜索。
表的创建和管理约束
表的创建和管理
创建了数据库，需要创建表存储数据。
本章介绍：
●表结构的创建、修改、删除；
●表数据的插入、更新、删除；
●表数据的约束（主键、唯一、检查、默认、外键）
一、表的概念
数据表的概念在第1章已经介绍，即：
●表由行、列构成，
●行代表一条记录，列代表记录的一个属性。
●行列的次序并不影响数据。
SQLSERVER2008允许每个数据库最多20亿张表，每张表最多1024列，每行数据最多8K。
二、数据类型
SQLSERVER的数据有数据类型，在创建表结构时需要确定表中每列的数据类型。
SQLSERVER的数据类型有系统数据类型和用户自定义数据类型。
1、系统数据类型（常用）
分类
数据特性
数据类型
字节数
表达的数据的范围等
整型
整数
bit（位）
1位
0、1代表真假、开关、是否
tinyint（微整型）
1
0-255
smallint（小整型）
2
-215~215-1（-32768~32767）
int（整型）
4
-231~231-1（约正负21亿）
bigint（大整型）
8
-263~263-1
浮点型
非精确小数，范围大，有有效位数
real（单精度）
4
-3.4x1038~-3.4x1038（7位有效位数）
float（双精度）
8
-3.4x10308~-3.4x10308（15位有效位数）
精确小数
可以精确指定小数总的位数p以及小数点后位数s
numerical（p,s）
-1038~-1038-1。
numerical列可以做IDENTITY列；decimal列不可以。
decimal（p,s）
货币型
专门用于货币处理
money
4+4=8
-263~-263-1（约920亿亿）。
精确到小数点后4位
smallmoney
2+2=4
-214748.3648~-214748.3647。
精确到小数点后4位
字符型
字符串处理
char（n）定长字符型
n
n=1~8000。
未指定n，n=1。
varchar（n）变长字符型
实际字符数
text大文本
231-1个字符（约21亿字符）
日期时间型
日期时间
datetime
8
1753-1-1~9999-12-31，精确到3.33ms
smalldatetime
4
1900-1-1~2079-6-6，精确到1min
二进制
存放字节数据。
image可以存放超过8K数据，适合存储图像或者任何其它文件。
binary（n）定长二进制
n+4
n=1~8000。
varbinary（n）变长二进制
实际长度+4
image变长大二进制
231-1个字节（约21亿字节）
其它
略
2、用户自定义数据类型
SQLServer用户自定义数据类型从系统类型派生，指定一个容易记忆的名称，便于统一使用某种数据类型。
例如：
邮政编码都是6个数字字符的号码，数据类型可以用char（6）表示。
为了方便以后统一使用，可以指定一个名字zip代表char（6）这种数据类型。
zip就是用户自定义数据类型。
（1）使用企业管理器管理自定义数据类型
●创建：
数据库-可编程性-类型-用户自定义类型-右键-新建“用户定义数据类型”-输入名称、选择数据类型等确定后创建。
●删除：
数据库-可编程性-类型-用户自定义类型-选中自定义数据类型-右键删除-删除对象窗口-确定
例3-1：
使用企业管理器，创建一个用户自定义数据类型telphone_code，系统数据类型为varchar（15），允许空。
（2）使用T-SQL语法管理自定义数据类型
●创建：
CREATETYPE<类型名>FROM<系统类型>[NULL|NOTNULL]
●删除：
DROPTYPE<类型名>
例3-1：
使用T-SQL，创建一个用户自定义数据类型telphone_code，系统数据类型为varchar（15），允许空。
（使用T-SQL）
CREATETYPEtelphone_codeFROMvarchar（15）NULL
DROPTYPEtelphone_code
例3-2、例3-3：
T-SQL创建删除自定义数据类型（参见教材、略）
三、
表结构的创建、修改和删除
1、表结构的创建
（1）使用企业管理器创建表结构
数据库-表右键-“新建表”-表设计器窗口。
例：
创建Orders表
保存，提示输入表名称，确定。
打开表-表右键-编辑所有行，试着输入一些数据。
（2）使用T-SQL创建表结构
使用T-SQL创建表结构的简单语法：
CREATETABLE[<数据库名>.][<架构名>.]<表名>
（
<列名><数据类型>[DEFAULT<默认值>][IDENTITY（种子，增量）][NULL|NOTNULL]
[，…n]--多个列
）
[ON<文件组>|DEFAULT]
演示CREATETABLE创建表结构。
例3-4：
创建Seller表的结构（非空、默认列等）
例3-5：
创建Orders表的结构（标识列）
例3-6：
创建Category表（指定文件组）
2、表结构的修改-增删列，修改列名、列数据类型等
（1）使用企业管理器修改表结构。
表右键-“设计”-打开表设计窗口；修改完成，保存。
（2）使用T-SQL修改表结构
ALTERTABLE<表名>
ADD<列名><列数据类型>[DEFAULT<默认值>][IDENTITY（种子，增量）][NULL|NOTNULL][，…n]--一次可以增加多个列，多个列以“，”分隔。
|DROPCOLUMN<列名>[，…n]--一次也可以删除多个列
|ALTERCOLUMN<列名><新数据类型>[NULL|NOTNULL]
例3-7：
改表-Customer表增加个列Address、Zipcode、Telphone。
例3-8：
改表-Seller表删除个列Sex、Notes。
例3-9：
改表-Seller修改Address列数据类型char（60）->varchar（30）。
3、表结构的删除
（1）使用企业管理器删除表（结构）：
表右键-“删除”-“确定”。
（2）使用T-SQL删除表（结构）
DROPTABLE<表名>[，…n]--可以一次删除多张表。
例3-10：
删除Customer表
四、表数据的插入、修改和删除
创建了表结构，就可以向表中添加数据-表数据的插入；在插入了数据后可以对数据进行修改或者删除-对数据表进行数据的增删改（维护）。
尽管可以用企业管理器对表的数据进行增删改等（表数据的维护）操作，实际应用中，特别是程序设计中更多地是利用T-SQL命令进行数据表数据的维护。
1、插入数据
INSERT[INTO]<表名>[（<列名>[，……n]）]
VALUES（<值>|NULL|DEFAULT[，……n]）
注意：
当向表中所有列插入数据时，列名表可以省略，但是必须保证VALUES后数据项的数量、位置、类型与表定义一致。
例3-11：
插入数据（标准语法）
例3-12：
插入数据（默认、NULL字段的处理）
例3-13：
插入数据（省略字段名表-数据必须和表结构完全一致）
例3-14：
省略字段名表，数据个数与表结构不匹配-报错。
例3-15：
插入数据（Identity列自动填充）
可以通过查询结果给一张表批量插入数据，语法：
INSERT[INTO]<目标表名>[（<列名>[，……n]）]
SELECT<列名>[，……n]）FROM<源表名>
[WHERE<查询条件>]
例3-16：
插入数据（用查询数据作为插入的数据）
2、修改数据
UPDATE<表名>SET<列名>=<值>[，…n][WHERE<查询条件>]
例3-17：
修改数据-一次修改一行
例3-18：
修改数据（补充地址）-一次修改一行
例3-19：
修改数据-一次修改多行（分类号为，调味品）
3、删除数据
DELETE[FROM]<表名>[WHERE<条件>]
例3-20：
删除数据
4、使用企业管理器插入/修改/删除表数据。
SQLSERVER2008-“编辑前n行”/“编辑所有行”。
注：
SQLSERVER2008无“打开表”快捷菜单，只有“编辑前n行”/“编辑所有行”。
设置：
工具-选项。
例子：
使用企业管理器练习为Orders表增加、修改、删除数据。
五、约束CONSTRAINT
约束：
是对列进行限制的规则，以确保输入数据的正确。
约束是实现数据完整性的主要途径。
约束有5种类型：
①主键约束、②唯一性约束、③检查约束、④默认约束，⑤外键约束。
1、约束的创建-可以在建表和改表时创建
（1）CREATETABLE语句创建约束-P51建表语句的扩充
CREATETABLE<表名>
（
<列名><数据类型>[[CONSTRAINT<约束名>][<约束类型>]]
[，…n]--多个列
）
（2）ALTERTABLE语句创建约束-P53改表语句的扩充。
ALTERTABLE<表名>
……
[WITHCHECK|WITHNOCHECK]
|ADD[CONSTRAINT<约束名>]<约束类型>
2、约束的删除-使用改表语句
ALTERTABLE<表名>
DROPCONSTRAINT<约束名>
（一）主键约束（PRIMARYKEY）
主键：
用于唯一标识表记录的列或者列组合。
主键不能为空，不会重复（任何两行的主键不会相同）
1、使用T-SQL创建主键约束
[CONSTRAINT<约束名>]PRIMARYKEY（<列名>[,……n]）
例3-21：
建表时设置主键约束
例3-22：
改表时实现主键约束
例3-23：
改表，多字段实现主键约束
2、使用企业管理器创建主键约束
表右键-“设计”。
选择列-右键-“设置主键”/“删除主键”，即可创建、删除主键约束。
完成后“保存”表。
（二）唯一性约束（UNIQUE）
唯一性约束：
限制不受主键约束的列上的数据的唯一性（不重复）。
唯一性约束与主键约束的区别：
唯一性约束允许列为NULL。
1、使用T-SQL创建唯一性约束
[CONSTRAINT<约束名>]UNIQUE（<列名>[,……n]）
例3-25：
建表（部门表）时创建唯一性约束
例2-26：
改表时创建唯一性约束
2、使用企业管理器创建唯一性约束
例：
使用企业管理器为Customer表添加唯一性约束。
表右键-“设计”-打开表设计窗口
表设计窗口上方窗格-右键-“索引/键”-打开“索引/键”对话框。
●添加按钮-添加唯一键（删除按钮-可删除唯一键）
●类型-选“唯一键”；列-选“Telphone”；名称-设置唯一性约束名。
●“关闭”按钮。
返回表设计窗口。
●表设计保存后有效！
！
！
。
（三）检查约束（CHECK）
检查约束：
指定列的取值需要满足的条件。
1、使用使用T-SQL创建唯一性约束
[CONSTRAINT<约束名>]CHECK（<表达式>）
注：
●表达式-对列检查的条件，一般是关系表达式或者逻辑表达式。
●检查约束在约束文件夹可见。
例3-27：
建Student表，设置检查约束（学生年龄范围~30）
例3-28：
改Student表，设置检查约束（生源地-直辖市）
例3-29：
改Seller表，设置检查约束（电话号码位数字）
2、使用企业管理器创建检查约束
例：
删除Student表上的检查约束，并重新创建。
表右键-“设计”-打开“表设计窗口”
表设计窗口上方窗格-右键-“CHECK约束”-打开“CHECK约束”对话框。
●添加按钮-添加检查约束（删除按钮-可删除检查约束）
●名称-设置检查约束名。
●表达式-设置检查表达式。
●“关闭”按钮。
返回表设计窗口。
●表设计保存后有效！
！
！
。
（四）默认约束（DEFAULT）
默认约束为列指定一个值（默认值）。
当向表插入数据时，如果没有指定此列的值，那么就以此默认值作为列的取值。
1、使用使用T-SQL创建默认约束
[CONSTRAINT<约束名>]DEFAULT（<表达式值>|NULL）FOR<列名>
注：
默认约束在约束文件夹可见。
例3-5：
创建表时，设置默认约束。
例3-30：
修改表时，设置默认值约束。
2、使用企业管理器创建默认约束
例：
Orders表设置OrderDate列设置默认值为当前日期。
●表右键-“设计”-表设计窗口-选中OrderDate列
●列属性-“默认值或绑定”：
getdate（）
●保存表设计。
（五）外键约束（FOREIGNKEY）
复习：
外键：
一个表的列是另外一个表的主键列。
表之间通过外键相互关联。
外键约束：
限制一个表A的列（外键列）是另外一个表B的主键列或唯一性列。
●A表外键列的取值必须是B表主键列或者唯一性列中的取值；
●B表主键列或者唯一性列取值发生改变，A表外键列的值也发生改变；--更新
●B表主键列或者唯一性列取值删除，A表外键列取值与该值相同的行会被删除。
--删除
1、使用使用T-SQL创建外键约束
[CONSTRAINT<约束名>]FOREIGNKEY（<列名>）REFERENCES<表名>（<列名>）
[ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]
[ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]
例3-31：
创建Orders表，设置外键约束
例3-32：
改表，设置外键约束
2、使用企业管理器创建外键约束
例：
设置外键约束，Product.CategoryID参照Category.CategoryID。
预先设置Category表主键CategoryID。
（1）Product表右键-“设计”-打开表设计窗口。
（2）表设计窗口上部右键-“关系”-打开“外键关系”对话框。
“添加”按钮增加一个外键约束，修改外键约束名称：
FK_CategoryID。
（3）“表和列规范”后省略号单击，打开“表和列”对话框选择外键约束的表和列。
（4）“确定”后返回外键关系对话框，根据需要可以设置更新规则和删除规则：
存在外键约束的情况下，对主键表的记录的更新和删除（插入无影响），需要检查主键表要更新的主键值是否被外键表的外键引用。
●若未引用，主键表的记录直接更新或者删除（外键表无任何改动）。
●若被引用（违反外键约束），可选的4种规则产生不同的处理结果：
◆不执行任何操作（默认）-无操作-主键表的更新或者删除企图会被拒绝。
◆级联-外键表随主键表级联更新或级联删除。
◆设置NULL-外键表外键列设置为NULL（相当于未设置，以后可以设置）。
◆设置默认值-外键表外键列设置为某个特定值。
这里我们设置为“级联”。
关闭对话框。
（5）保存表设计
（6）测试：
主键表Category表中将CategoryID=3（点心）修改为9，观察外键表Product表CategoryID原来为3的全部自动修改为9。
同样还可以做删除测试。
六、实现数据完整性（略）
1、实体完整性-通过主键约束实现。
2、域完整性-通过检查约束实现。
3、参照完整性-通过外键约束实现。
4、用户定义的完整性-一般由用户通过程序控制。
升级会员 