第二章-数据库表管理PPT课件.ppt

1 SQLSERVER2005课程 第二章数据库表管理讲师 冯敏琴Email echofff 2 上章回顾 数据库的作用数据库的发展过程以及当前流行的数据库数据库基本概念 数据存储方式 实体和记录 数据库和数据库表 数据冗余和数据完整性用T SQL创建数据库文件组 数据文件和日志文件收缩 移动和删除数据库 3 本章目标 创建数据库表掌握数据类型了解SQL Server命名规则设置表的主键 外键和建立表之间的关系为表增加约束SQLServer中存在五种约束 主键约束 外键约束 检查约束 默认约束和唯一性约束实施数据完整性 包括实体完整性 引用完整性和域完整性 4 表的基础知识 建表的基本步骤 确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系SQLServer中的数据类型 整型数据 intsmallint浮点数据 floatnumeric 字符数据固定长度 char 6 可变长度 varchar 10 unicode编码 nchar 10 布尔数据 真 假 bit 1 0 货币数据money 日期数据datetime 5 数据类型 数据类型是指数据所代表信息的类型 为了数据库的可移植性 建库时最好用共有的数据类型 Sqlserver2005共定义了29种数据类型 整数Bigint 263到263 1的整数 8个字节 9223372036854775808 9223372036854775807Int 231到231 1的整数 4个字节 2147483648 2147483647Smallint 215到215 1的整数 32768 32767 2个字节 Tinyint0到255的整数 1个字节 6 数据类型 字符型由字母 数字和符号组成的数据 char n 固定长度最大长度8000个字节Varchar n 可变长度最大长度8000个字节text可变长度最大长度为231 1个字节日期和时间数据类型datetime存储从1753年1月1日到9999年12月31日的日期和时间数据 如 2007 1 1316 49 45smalldatetime精确到分钟 范围从1900年1月1日到2079年6月6日 占4个字节 7 数据类型 小数Float 1 79E 308到1 79E 308的浮点数 1 24 精度占7位 用4个字节 25 53 精度占15位 用8个字节Real 3 40E 38到3 40E 38的浮点数占4个字节 精度可以达到7位精确小数decimal从 1038 1到1038 1 decimal 5 3 decimal 精度 小数位数 12 345两个都可以指定精度 p 和小数后面的位数 s 必须满足两个公式 1 p 380 s p numeric 可以带有identity关键字的列 8 数据类型 二进制数据类型 用十六进制来表示的数据 在数据常量前面加一个前缀0 x 1 binary 具有固定的长度 最大长度可以达到8kb 2 varbinary 具有不固定的长度 最大长度可以达到8kb 3 image 可以用于存储字节数超过8kb gif bmp jpeg 统一码数据类型 1 nchar n 固定长度的双字节数据类型 n的取值范围是0 4000 双字节数据的存储空间 字符数 2 2 nvarchar n 可变长度的双字节数据类型 n的取值范围是0 4000 双字节数据的存储空间 字符数 2 3 ntext n 可变长度的双字节字符存储多达230 1个双字节字符 特殊数据类型Bit由1或0组成 Cursortimestampsql variantuniqueidentifiertablexml 9 用户自定义数据类型 执行sp addtype创建别名数据类型 该数据类型可在特定数据库中出现 创建了别名数据类型之后 可以在CREATETABLE或ALTERTABLE中使用它 也可以将默认值和规则绑定到别名数据类型使用sp droptype删除别名数据类型 execsp addtypetelephone varchar 24 notnull execsp addtypefax varchar 24 null sp droptypetelephone 10 SQL Server命名规则 数据库对象的名称即为其标识符 标识符分为两类 常规标识符和分割标识符 界定标识符 只能容纳128个字符 常规标识符 1 第一个字符必须是下列字符之一 Unicode标准所定义的字母 Unicode中定义的字母包括拉丁字符a z和A Z 以及来自其他语言的字母字符 下划线 at 符号 或者 在SQLServer中 某些位于标识符开头位置的符号具有特殊意义 以 at 符号开头的标识符表示局部变量或参数 以 开头的标识符表示临时表或过程 以 开头的标识符表示全局临时对象 2 后续字符可以包括 如Unicode标准中所定义的字母 基本拉丁字符或其他国家 地区字符中的十进制数字 at 符号 美元符号 数字符号或下划线 3 标识符不能是Transact SQL保留字 SQLServer保留其保留字的大写和小写形式 4 不允许嵌入空格或其他特殊字符 分割标识符 限定标识符 包含在单引号 或者方括号 内 也就是分割标识符可以遵守标识符命名规则 也可以不遵守标识符命名规则 但是规则标识符必须遵守标识符命名规则 11 创建表 建表的语法 Createtable表名 字段1数据类型列的特征 字段2数据类型列的特征 列的特征 包括该列是是否为空 NULL 是否是标识列 自动编号 是否有默认值 是否为主键等 12 建表示例 USEstuDB 将当前数据库设置为stuDBGOCREATETABLEstuInfo 创建学员信息表 stuNameVARCHAR 20 NOTNULL 姓名 非空 必填 stuNoCHAR 6 NOTNULL 学号 非空 必填 stuAgeINTNOTNULL 年龄 INT类型默认为4个字节stuIDNUMERIC 18 0 身份证号stuSeatSMALLINTIDENTITY 1 1 座位号 自动编号stuAddressTEXT 住址 允许为空 即可选输入 GO NUMERIC 18 0 代表18位数字 小数位数为0 演示 创建学员信息表stuInfo IDENTITY 起始值 递增量 13 创建表 Createtabletable name column namedata type DEFAULTconstant expression IDENTITY seed increment NULL NOTNULL PRIMARYKEY UNIQUE REFERENCESref table ref column 1 table name新表的名称 表名称必须符合标识符规则 table name在数据库中必须是唯一的 table name最多可包含128个字符 2 column name表中列的名称 列名称必须符合标识符规则 并且在表中必须是唯一的 14 创建表 3 identity seed increament 指示新列是标识列 在为表添加新行时 SQLServer将为列提供唯一的增量值 标识列通常与primarykey约束结合使用以作为表的唯一行标识符 identity属性只能分配给int列 每个表只能创建一个标识列 标识列无法使用绑定默认值和default约束 必须同时指定种子和增量 或者不指定任何值 如果不指定任何值 默认为 1 1 seed 定义标识列的起始值 就是插入表的第一行数据的标识列的值increment 定义标识列的增量 加到所加载的上一行的标识值上的增量值 4 null notnull用于指定列中是否允许空值的关键字 从严格意义上讲 NULL不是一个约束 但可以使用指定NOTNULL相同的方法来指定 15 创建表 5 primarykey通过使用唯一的索引对特定列强制执行实体完整性的约束 对于每个表只能创建一个primarykey约束 6 unique通过使用唯一的索引提供特定列的实体完整性的约束 unique约束中的列可以为null 但每一列只允许一个NULL值 一个表可以包含多个unique约束 7 Check通过检查输入表列的数据的值来维护值域的完整性 和外键约束不同 它是通过对一个逻辑表达式的结果进行判断来对数据进行检查 8 default指定在插入操作中没有显式提供值时为该列提供的值 除了identity属性定义的列之外 default定义可应用于任何列 常量值可用作默认值 16 创建表 9 foreignkey references为列中的数据提供引用完整性的约束 主要用来维护两个表之间的一致性关系 外键的建立主要是通过将一个表中的主键 unique约束所在列包含在另一个表中 这些列就是另一个表的外键FOREIGNKEY约束如果在FOREIGNKEY约束的列中输入非NULL值 则此值必须在被引用的列中存在 否则 将返回违反外键约束的错误消息 FOREIGNKEY约束可以引用同一表中的其他列 且引用为自我引用 createtableemployee emp idintprimarykey emp namevarchar 20 manager idintforeignkeyreferencesemployee emp id 列级FOREIGNKEY约束的REFERENCES子句仅能列出一个引用列 该列必须与定义约束的列具有相同的数据类型 表级FOREIGNKEY约束的REFERENCES子句中引用列的数目必须与约束列的列表中的列数相同 每个引用列的数据类型也必须与列的列表中相应列的数据类型相同 17 修改表结构 execsp help goods 查看表的详细信息execsp rename goods orders 修改表的名称 execsp rename orders goods id ord id column 修改列的名称 修改表结构 增加列 altertableordersaddord datedatetime 修改表结构 修改列 altertableordersaltercolumnord namevarchar 100 修改表结构 删除列 altertableordersdropcolumnord date 18 删除表 删除表的语法 DROPTABLE表名 USEstuDB 将当前数据库设置为stuDB 以便在stuDB数据库中建表GOIFEXISTS SELECT FROMsysobjectsWHEREname stuInfo DROPTABLEstuInfoCREATETABLEstuInfo 创建学员信息表 GO 19 SQLServer的约束 约束的目的 确保表中数据的完整型常用的约束类型 主键约束 PrimaryKeyConstraint 要求主键列数据唯一 并且不允许为空唯一约束 UniqueConstraint 要求该列唯一 允许为空 但只能出现一个空值 检查约束 CheckConstraint 某列取值范围限制 格式限制等 如有关年龄的约束默认约束 DefaultConstraint 某列的默认值 如我们的男性学员较多 性别默认为 男 外键约束 ForeignKeyConstraint 用于两表间建立关系 需要指定引用主表的那列 20 查看约束 execsp helpconstraint goods 查看指定表的所有约束 execsp help goods 查看指定表的所有信息 execsp pkeys goods 查看指定表的主键约束 execsp fkeys provider 查看指定表的外键约束 外键属于主表 而不是从表 21 添加约束 添加约束的语法 ALTERTABLE表名ADDCONSTRAINT约束名约束类型具体的约束说明 约束名的取名规则推荐采用 约束类型 约束字段主键 PrimaryKey 约束 如PK stuNo唯一 UniqueKey 约束 如UQ stuID默认 DefaultKey 约束 如DF stuAddress检查 CheckKey 约束 如CK stuAge外键 ForeignKey 约束 如FK stuNo 22 添加约束示例 ALTERTABLEstuInfoADDCONSTRAINTPK stuNoPRIMARYKEY stuNo ALTERTABLEstuInfoADDCONSTRAINTUQ stuIDUNIQUE stuID ALTERTABLEstuInfoADDCONSTRAINTDF stuAddressDEFAULT 地址不详 FORstuAddressALTERTABLEstuInfoADDCONSTRAINTCK stuAgeCHECK stuAgeBETWEEN15AND40 ALTERTABLEstuMarksADDCONSTRAINTFK stuNoFOREIGNKEY stuNo REFERENCESstuInfo stuNo GO 演示 给学员信息表stuInfo添加约束 添加主键约束 stuNo作为主键 添加唯一约束 因为每人的身份证号全国唯一 添加默认约束 如果地址不填 默认为 地址不详 添加检查check约束 要求年龄只能在15 40岁之间 添加外键约束 主表stuInfo和从表stuMarks建立关系 关联字段为stuNo 23 删除约束 如果错误地添加了约束 我们还可以删除约束删除约束的语法 ALTERTABLE表名DROPCONSTRAINT约束名 例如 删除stuInfo表中地址默认约束ALTERTABLEstuInfoDROPCONSTRAINTDF stuAddress 24 再论数据完整性2 1 数据完整性 25 再论数据完整性2 2 数据存放在表中 数据完整性的问题大多是由于设计引起的 创建表的时候 就应当保证以后数据输入是正确的 错误的数据 不符合要求的数据不允许输入 创建表 保证数据的完整性 实施完整性约束 26 完整性包括 2 1 输入的类型是否正确 年龄必须是数字输入的格式是否正确 身份证号码必须是18位是否在允许的范围内 性别只能是 男 或者 女 是否存在重复输入 学员信息输入了两次是否符合其他特定要求 信誉值大于5的用户才能够加入会员列表 27 完整性包括 2 2 28 实体完整性 约束方法 唯一约束 主键约束 标识列 29 域完整性 约束方法 限制数据类型 检查约束 外键约束 默认值 非空约束 30 引用完整性 约束方法 外键约束 31 自定义完整性 约束方法 规则 存储过程 触发器 32 列级约束和表级约束 列级约束 只能够应用在一列上表级约束 可以应用在一个表中的多列上 如 联合主键约束altertablestuInfodetailsaddconstraintpk Class Nameprimarykey stu Name stu class altertablestuInfodetailsaddconstraintuq code addressunique stu code stu