表的创建和管理(约束).doc

表的创建和管理创建了数据库，需要创建表存储数据。本章介绍：l 表结构的创建、修改、删除；l 表数据的插入、更新、删除；l 表数据的约束（主键、唯一、检查、默认、外键）一、 表的概念数据表的概念在第1章已经介绍，即：l 表由行、列构成，l 行代表一条记录，列代表记录的一个属性。l 行列的次序并不影响数据。SQL SERVER2008允许每个数据库最多20亿张表，每张表最多1024列，每行数据最多8K。二、 数据类型SQL SERVER的数据有数据类型，在创建表结构时需要确定表中每列的数据类型。SQL SERVER的数据类型有系统数据类型和用户自定义数据类型。1、 系统数据类型（常用）分类数据特性数据类型字节数表达的数据的范围等整型整数bit（位）1位0、1代表真假、开关、是否tinyint（微整型）10-255smallint（小整型）2-215215-1（-3276832767）int（整型）4-231231-1（约正负21亿）bigint（大整型）8-263263-1浮点型非精确小数，范围大，有有效位数real（单精度）4-3.4x1038-3.4x1038 （7位有效位数）float（双精度）8-3.4x10308-3.4x10308 （15位有效位数）精确小数可以精确指定小数总的位数p以及小数点后位数snumerical(p,s)-1038-1038-1。numerical列可以做IDENTITY列；decimal列不可以。decimal(p,s)货币型专门用于货币处理money4+4=8-263-263-1（约920亿亿）。精确到小数点后4位smallmoney2+2=4-214748.3648-214748.3647。精确到小数点后4位字符型字符串处理char(n)定长字符型nn=18000。未指定n，n=1。varchar(n)变长字符型实际字符数text大文本231-1个字符（约21亿字符）日期时间型日期时间datetime81753-1-19999-12-31，精确到3.33mssmalldatetime41900-1-12079-6-6，精确到1min二进制存放字节数据。image可以存放超过8K数据，适合存储图像或者任何其它文件。binary(n)定长二进制n+4n=18000。varbinary(n)变长二进制实际长度+4image变长大二进制231-1个字节（约21亿字节）其它略2、 用户自定义数据类型SQL Server用户自定义数据类型从系统类型派生，指定一个容易记忆的名称，便于统一使用某种数据类型。例如：邮政编码都是6个数字字符的号码，数据类型可以用char(6)表示。为了方便以后统一使用，可以指定一个名字zip代表char(6)这种数据类型。zip就是用户自定义数据类型。（1） 使用企业管理器管理自定义数据类型l 创建：数据库-可编程性-类型-用户自定义类型-右键-新建“用户定义数据类型”-输入名称、选择数据类型等确定后创建。l 删除：数据库-可编程性-类型-用户自定义类型-选中自定义数据类型-右键删除-删除对象窗口-确定例3-1：使用企业管理器，创建一个用户自定义数据类型telphone_code，系统数据类型为varchar(15)，允许空。（2） 使用T-SQL语法管理自定义数据类型l 创建：CREATE TYPE FROM NULL | NOT NULLl 删除：DROP TYPE 例3-1：使用T-SQL，创建一个用户自定义数据类型telphone_code，系统数据类型为varchar(15)，允许空。（使用T-SQL）CREATE TYPE telphone_code FROM varchar(15) NULLDROP TYPE telphone_code例3-2、例3-3：T-SQL创建删除自定义数据类型（参见教材、略）三、 表结构的创建、修改和删除1、 表结构的创建（1） 使用企业管理器创建表结构数据库-表右键-“新建表”-表设计器窗口。例：创建Orders表保存，提示输入表名称，确定。打开表-表右键-编辑所有行，试着输入一些数据。（2） 使用T-SQL创建表结构使用T-SQL创建表结构的简单语法未包括主键、唯一、检查、外键约束。：CREATE TABLE . .( DEFAULT IDENTITY标识列(种子标识列起始值，增量)NULL | NOT NULL ，n -多个列)ON | DEFAULT演示CREATE TABLE创建表结构。例3-4：创建Seller表的结构（非空、默认列等）例3-5：创建Orders表的结构（标识列）例3-6：创建Category表（指定文件组）2、 表结构的修改-增删列，修改列名、列数据类型等（1） 使用企业管理器修改表结构。表右键-“设计”-打开表设计窗口；修改完成，保存。（2） 使用T-SQL修改表结构ALTER TABLE ADD 增列DEFAULT IDENTITY(种子，增量)NULL | NOT NULL ，n -一次可以增加多个列，多个列以“，”分隔。| DROP COLUMN 删除列 ，n -一次也可以删除多个列| ALTER COLUMN NULL | NOT NULL例3-7：改表-Customer表增加个列Address、Zipcode、Telphone。例3-8：改表-Seller表删除个列Sex、Notes。例3-9：改表-Seller修改Address列数据类型char(60)-varchar(30)。3、 表结构的删除（1） 使用企业管理器删除表（结构）：表右键-“删除”-“确定”。（2） 使用T-SQL删除表（结构）DROP TABLE ，n -可以一次删除多张表。例3-10：删除Customer表四、 表数据的插入、修改和删除主要介绍、重点掌握T-SQL命令。创建了表结构，就可以向表中添加数据-表数据的插入；在插入了数据后可以对数据进行修改或者删除-对数据表进行数据的增删改（维护）。尽管可以用企业管理器对表的数据进行增删改等（表数据的维护）操作，实际应用中，特别是程序设计中更多地是利用T-SQL命令进行数据表数据的维护。1、 插入数据INSERT INTO (，n) VALUES( | NULL | DEFAULT ，n)注意：当向表中所有列插入数据时，列名表可以省略，但是必须保证VALUES后数据项的数量、位置、类型与表定义一致。例3-11：插入数据（标准语法）例3-12：插入数据（默认、NULL字段的处理）例3-13：插入数据（省略字段名表-数据必须和表结构完全一致）例3-14：省略字段名表，数据个数与表结构不匹配-报错。例3-15：插入数据(Identity列自动填充)提示：使用T-SQL命令的效果可以通过打开表观察。UPDATE、DELETE同。可以通过查询结果给一张表批量插入数据，语法：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、 使用企业管理器插入/修改/删除表数据。SQL SERVER2008-“编辑前n行”/“编辑所有行”。注：SQL SERVER2008无“打开表”快捷菜单，只有“编辑前n行”/“编辑所有行”“编辑所有行”就是打开表。设置：工具-选项。例子：使用企业管理器练习为Orders表增加、修改、删除数据。五、 约束CONSTRAINT约束：是对列进行限制的规则，以确保输入数据的正确。约束是实现数据完整性的主要途径。约束有5种类型：主键约束、唯一性约束、检查约束、默认约束，外键约束。1、 约束的创建-可以在建表和改表时创建（1） CREATE TABLE语句创建约束-P51建表语句的扩充CREATE TABLE ( CONSTRAINT 可以省略，系统自动给出约束名 ，n -多个列)（2） ALTER TABLE语句创建约束-P53改表语句的扩充。ALTER TABLE WITH CHECK | WITH NOCHECKWITH CHECK用增加的约束检查现有数据，若数据符合约束，则添加约束，否则添加约束失败。| ADD CONSTRAINT 2、 约束的删除-使用改表语句ALTER TABLE DROP CONSTRAINT （一） 主键约束（PRIMARY KEY）主键：用于唯一标识表记录的列或者列组合。主键不能为空，不会重复（任何两行的主键不会相同）1、 使用T-SQL创建主键约束CONSTRAINT PRIMARY KEY ( ,n) 例3-21：建表时设置主键约束例3-22：改表时实现主键约束例3-23：改表，多字段实现主键约束2、 使用企业管理器创建主键约束表右键-“设计”。选择列-右键-“设置主键”/“删除主键”，即可创建、删除主键约束。完成后“保存”表。（二） 唯一性约束（UNIQUE）唯一性约束：限制不受主键约束的列上的数据的唯一性（不重复）。唯一性约束与主键约束的区别：唯一性约束允许列为NULL。1、 使用T-SQL创建唯一性约束CONSTRAINT UNIQUE( ,n) 例3-25：建表(部门表)时创建唯一性约束例2-26：改表时创建唯一性约束2、 使用企业管理器创建唯一性约束例：使用企业管理器为Customer表添加唯一性约束。表右键-“设计”-打开表设计窗口表设计窗口上方窗格-右键-“索引/键”-打开“索引/键”对话框。l 添加按钮-添加唯一键（删除按钮-可删除唯一键）l 类型-选“唯一键”；列-选“Telphone”；名称-设置唯一性约束名。l “关闭”按钮。返回表设计窗口。l 表设计保存后有效！。（三） 检查约束（CHECK）检查约束：指定列的取值需要满足的条件。1、 使用使用T-SQL创建唯一性约束CONSTRAINT CHECK() 注：l 表达式-对列检查的条件，一般是关系表达式或者逻辑表达式。l 检查约束在约束文件夹可见。例3-27：建Student表，设置检查约束（学生年龄范围30）例3-28：改Student表，设置检查约束（生源地-直辖市）例3-29：改Seller表，设置检查约束(电话号码位数字)2、 使用企业管理器创建检查约束例：删除Student表上的检查约束，并重新创建。表右键-“设计”-打开“表设计窗口”表设计窗口上方窗格-右键-“CHECK约束”-打开“CHECK约束”对话框。l 添加按钮-添加检查约束（删除按钮-可删除检查约束）l 名称-设置检查约束名。l 表达式-设置检查表达式。l “关闭”按钮。返回表设计窗口。l 表设计保存后有效！。（四） 默认约束（DEFAULT）默认约束为列指定一个值（默认值）。当向表插入数据时，如果没有指定此列的值，那么就以此默认值作为列的取值。1、 使用使用T-SQL创建默认约束CONSTRAINT DEFAULT ( | NULL) FOR 注：默认约束在约束文件夹可见。例3-5：创建表时，设置默认约束。例3-30：修改表时，设置默认值约束。2、 使用企业管理器创建默认约束例：Orders表设置OrderDate列设置默认值为当前日期。l 表右键-“设计”-表设计窗口-选中OrderDate列l 列属性-“默认值或绑定”：getdate()删除默认，只要清除这里的数据即可l 保存表设计。（五） 外键约束（FOREIGN KEY）复习：外键：一个表的列是另外一个表的主键列。表之间通过外键相互关联。外键约束：限制一个表A的列（外键列）是另外一个表B的主键列或唯一性列。l A表外键列的取值必须是B表主键列或者唯一性列中的取值；l B表主键列或者唯一性列取值发生改变，A表外键列的值也发生改变；-更新l B表主键列或者唯一性列取值删除，A表外键列取值与该值相同的行会被删除。-删除1、 使用使用T-SQL创建外键约束CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT 更新/删除规则.例3-31：创建Orders表，设置外键约束例3-32：改表，设置外键约束2、 使用企业管理器创建外键约束例：设置外键约束，Product.CategoryID参照Category. CategoryID。预先设置Category表主键 CategoryID。（1） Product表右键-“设计”-打开表设计窗口。（2） 表设计窗口上部右键-“关系”-打开“外键关系”对话框。“添加”按钮增加一个外键约束，修改外键约束名称：FK_CategoryID。（3） “表和列规范”后省略号单击，打开“表和列”对话框选择外键约束的表和列。（4） “确定”后返回外键关系对话框，根据需要可以设置更新规则和删除规则：存在外键约束的情况下，对主键表的记录的更新和删除（插入无影响），需要检查主键表要更新的主键值是否被外键表的外键引用。l 若未引用，主键表的记录直接更新或者删除（外键表无任何改动）。l 若被引用（违反外键约束），可选的4种规则产生不同的处理结果：u 不执行任何操作（默认）-无操作-主键表的更新或者删除企图会被拒绝。u 级联-外键表随主键表级联更新或级联删除。自动，但有误删除数据的