数据表的创建与管理PPT课件.ppt

第四章基本表的创建与管理 1 学习要点 表的概念表的创建 修改和删除添加 更新与删除表中的数据数据完整性的概念及实施方法 2 表是数据的集合 是用来存储数据和操作数据的逻辑结构 表是由行和列组成的 行被称为记录 是组织数据的单位 列被称为字段 每一列表示记录的一个属性 在特定表中 列名必须是唯一的 但相同的列名可以在数据库中的不同表中使用 SQLServer中的每个表中最多允许有1024列 每行最多允许有8060字节的用户数据 行和列的次序是任意的 4 1表的概念 3 4 2数据类型 第一大类 整数数据bit bit数据类型代表0 1或NULL 就是表true false 占用1byte int 以4个字节来存储正负数 可存储范围为 2147483648至2147483647 smallint 以2个字节来存储正负数 存储范围为 32768至32767 tinyint 是最小的整数类型 仅用1字节 范围 0至255bigint 以8个字节存储 是SQLServer2000引入的全新的数据类型 可以存储范围 263到263 1 4 第二大类 精确数值数据numeric 表示的数字可以达到38位 存储数据时所用的字节数目会随着使用权用位数的多少变化 格式写为numeric p s 例如 numeric 10 4 表示共有10位数 其中整数6位 小数4位 decimal 和numeric类似 区别 numeric类型的列可以带有IDENTITY关键字 唯一标识符 5 第三大类 近似浮点数值数据float 用8个字节来存储数据 最多可为53位 范围为 1 79E 308至1 79E 308 real 位数为24 用4个字节 数字范围 3 04E 38至3 04E 38第四大类 日期时间数据datatime 表示时间范围可以表示从1753 1 1至9999 12 31 时间可以表示到3 33 1000秒 使用8个字节 smalldatetime 表示时间范围可以表示从1900 1 1至2079 12 31 使用4个字节 6 第五大类 字符串数据char n 长度是设定的 最短为1字节 最长为8000个字节 不足的长度会用空白补上 例如 char 10 Varchar n 变长字符型数据 最短为1字节 最长为8000个字节 尾部的空白会去掉 text 长专门用于存储数量庞大的变长字符数据 最长可以存放2G 231 1个字符 的数据 7 第六大类 Unincode字符串数据nchar 长度是设定的 最短为1字节 最长为4000个字节 不足的长度会用空白补上 储存一个字符需要2个字节 nvarchar 可变长度 最短为1字节 最长为4000个字节 尾部的空白会去掉 储存一个字符需要2个字节 ntext 可变长度 最短为1字节 最长为230 1个unicode数据 尾部的空白会去掉 储存一个字符需要2个字节 8 第七大类 货币数据类型money 记录金额范围为 92233720368577 5808至92233720368577 5807 需要8个字节 smallmoney 记录金额范围为 214748 3648至214748 36487 需要4个字节 第八大类 标记数据timestamp 该数据类型在每一个表中是唯一的 当表中的一个记录更改时 该记录的timestamp字段会自动更新 9 第九大类 二进制码字符串数据binary 固定长度的二进制码字符串字段 最短为1 最长为8000 varbinary 与binary差异为数据尾部是00时 varbinary会将其去掉image 为可变长度的二进制码字符串 最长2G 10 4 3表结构的建立和删除 1 表的建立表是包含数据库中所有数据的数据库对象 用来存储各种各样的信息 在SQLServer2000中 一个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 在同一数据库的不同表中 可以有相同的字段 但在同一个表中不允许有相同的字段 而且每个字段都要求数据类型相同 11 一般的SQL建表语句形式CREATETABLE表名 表约束 列名1数据类型 缺省值1 列约束1 列名2数据类型 缺省值2 列约束2 列名n数据类型 缺省值n 列约束n 12 例4 1创建三张表 表结构如下 Student表结构 13 Course表结构 14 Sc表结构 15 CREATETABLEStudent SnoCHAR 7 PRIMARYKEY SnameCHAR 10 NOTNULL SsexCHAR 2 DEFAULT 男 SageTINYINT SdeptCHAR 20 16 CREATETABLECourse CnoCHAR 10 PRIMARYKEY CnameCHAR 20 NOTNULL CcreditTINYINTDEFAULT3 SemesterTINYINT 17 CREATETABLESc SnoCHAR 7 CnoCHAR 10 GradeFLOAT PRIMARYKEY Sno Cno FOREIGNKEY Sno REFERENCESStudent Sno FOREIGNKEY Cno REFERENCESCourse Cno 18 2 表的删除删除表的命令一般形式DROPTABLE表名例4 2删除教师表DROPTABLEteacher 19 4 4表结构的扩充和修改1 修改表的命令一般形式ALTERTABLE表名 ADD列名数据类型 NULL NOTNULL ALTERCOLUMN列名数据类型NULL NOTNULL DROPCOLUMN列名 20 例4 3在student中增加住址列ALTERTABLEstudentADDaddressCHAR 50 例4 4修改student表中的sdept字段 将数据类型长度为30ALTERTABLEstudentALTERCOLUMNsdeptchar 30 例4 5把Student表中的Sdept列删除 ALTERTABLEStudentDROPCOLUMNSdept 21 2 添加主关键字ALTERTABLE表名ADD CONSTRAINT约束名 PRIMARYKEY 主键列 3 删除主关键字ALTERTABLE 表名 DROP CONSTRAINT 约束名4 补充定义外来关键字ALTERTABLE 表名 1 ADD CONSTRAINT约束名 FOREIGNKEY 外来关键字名 REFERENCES 表名 2 5 删除外来关键字ALTERTABLE 表名 DROP 外来关键字名 22 例4 6补充定义student表的主关键字 ALTERTABLEStudentADDCONSTRAINTpk studentPRIMARYKEY Sno 例4 7 删除course表中的主关键字 ALTERTABLEcourseDROPpk course例4 8 修改sc表 在sno字段上创建外键约束 ALTERTABLEscADDCONSTRAINTfk sc studentFOREIGNKEY sno REFERENCESstudent sno 23 例4 9 删除sc表中sno字段上的外键约束ALTERTABLEscDROPfk sc student例4 10 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDScomedateDATETIME例4 11 将年龄的数据类型改为半字长整数 ALTERTABLEStudentALTERCOLUMNSageSMALLINT 24 4 5约束 约束定义了必须遵循的用于维护数据一致性和正确性的规则 是强制实现数据完整性的途径 约束分类 主键约束外键约束唯一值约束检查约束默认约束 25 主键约束 表中常有一列或列的组合 其值能惟一标识表中的每一行 这样的一列或列的组合称为表的主键 PRIMARYKEY 通过主键可以强制表的实体完整性 定义了主键约束的列具有以下特点 每个表只能定义一个主键 主键值不可为空 NULL 主键值不可重复 若主键是由多列组成时 某一列上的值可以重复 但多列的组合值必须是惟一的 26 外键约束 一个数据库中可能包含多个表 可以通过外键 FOREIGNKEY 使这些表关联起来 外键是由表中的一列或多列组成的 如在A表中有一个字段的取值只能是B表中某字段的取值之一 则在A表该字段上创建外键约束 A表称为从表 B表称为主表 定义外键约束的列具有以下特点 外键的取值可以为空 NULL 外键的取值可以重复 但必须是它所引用列 在主表中 的取值之一 引用列必须是定义了主键约束或惟一约束的列 27 唯一性约束 UNIQUE UNIQUE用来限制不受主键约束的列上的数据的唯一性 一个表可以放置多个UNIQUE约束语法 CONSTRAINTconstraint name UNIQUE 列名 28 例3 12为student表添加身份证号列 id 并为 id 列添加唯一值约束 ALTERTABLEstudentADDidBIGINTCONSTRAINTunq idUNIQUE注 UNIQUE约束列允许有NULL值 但最多只能有一个NULL值 否则认为重复 例3 13为course表的cname列添加唯一值约束 ALTERTABLEcourseADDCONSTRAINTunq cnameUNIQUE cname 29 检查约束 CHECK CHECK约束用来指定某列的可取值范围 通过限制输入值强制控制域的完整性 语法 CONSTRAINTconstraint name CHECK 检查条件 例3 14限制student表中的ssex列只能添入值为 男 或 女 ALTERTABLEstudentADDCONSTRAINTcheck ssexCHECK ssex 男 orssex 女 30 默认约束 DEFAULT DEFAULT约束用于给表中指定列赋予一个常量值 默认值 语法 CONSTRAINT约束名 DEFAULT默认值FOR列名例3 15为student中的sage列添加默认值 19 ALTERTABLEstudentADDCONSTRAINTdefault sageDEFAULT19FORsage 31 4 6实现数据完整性 数据完整性是指数据的正确性 一致性和安全性 它是衡量数据库中数据质量好坏的重要标准 三种完整性类型 实体完整性参照完整性域完整性 32 域完整性 域完整性又称为列完整性 用以指定列的数据输入是否具有正确的数据类型 格式以及有效的数据范围 33 实体完整性 实体完整性又称为行完整性 这里的实体是指表中的记录 一个实体就是表的一条记录 实体完整性要求在表中不能存在完全相同的记录 而且每条记录都要具有一个非空且不重复的主键值 34 参照完整性 参照完整性又称为引用完整性 参照完整性保证主表 被参照表 中的数据与从表 参照表 中数据的一致性 在SQLServer2000中 参照完整性的是通过定义外键与主键之间或外键与惟一键之间的对应关系实现的 35 参照完整性 如果定义了两个表之间的参照完整性 则要求如下 从表不能引用不存在的键值 如果主表中的键值更改了 那么在整个数据库中 对从表中该键值的所有引用要进行一致的更改 如果要删除主表中的某一记录 应先删除从表中与该记录匹配的相关记录 36 参照完整性示意图 37 4 7数据操纵 1 插入数据1 用values子句向表中插入数据INSERT语句形式为 INSERTINTO表名 列名1 列名2 VALUES 常量1 常量2 38 例1将新生记录 95011 陈平 男 信息系 17岁 插入到Student表中 INSERTINTOStudentVALUES 95011 陈平 男 信息系 17 例2在SC表中插入一新纪录 成绩和修课类别暂缺 INSERTINTOSC Sno Cno VALUES 95011 C01 注 此时必须列出列名SC表中的其他列必须允许为空 39 2 修改数据1 用SET子句修改表中数据UPDATE语句格式为 UPDATE表名SET列名1 表达式1 WHERE更新条件 例3 将所有学生的年龄加1 UPDATEStudentSETSage Sage 1例4 将学生95001的年龄改为23岁 UPDATEStudentSETSage 23WHERESno 95001 40 3 删除数据删除语句的一般格式为 DELETEFROM表名WHERE删除条件例5 删除所有学生的选课记录 DELETEFROMSC例6 删除所有不及格学生的修课记录 DELETEFROMSCWHEREGrade 60 41 上机实践 独立完成实验 实验目的 本章实验主要目的是学习正确创建表 掌握修改表结构的方法 正确删除表 实验内容 自定义数据类型 分别用企业管理器和查询分析器创建表 分别用企业管理器和查询分析器修改表 分别用企业管理器和查询分析器删除表 42 实验题目 题目一 创建学生个人信息数据表studentinfo题目要求分析 在第一章实验中创建的学生信息数据库student中创建数据表studentinfo 数据表中包含以下信息 学号 姓名 性别