第章数据表的创建和管理PPT课件.ppt

第三章基本表的创建与管理 1 学习要点 表的概念表的创建 修改和删除添加 更新与删除表中的数据数据完整性的概念及实施方法 2 3 1表的概念 表是数据的集合 是用来存储数据和操作数据的逻辑结构 表是由行和列组成的 行被称为记录 是组织数据的单位 列被称为字段 每一列表示记录的一个属性 在特定表中 列名必须是唯一的 但相同的列名可以在数据库中的不同表中使用 SQLServer中的每个表中最多允许有1024列 每行最多允许有8060字节的用户数据 行和列的次序是任意的 3 3 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个字节 不足的长度会用空白补上 储存双字节字符 nchar m 中的m代表的含义是用来定义字符数据的最大长度 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 3 3表结构的建立和删除 1 表的建立表是包含数据库中所有数据的数据库对象 用来存储各种各样的信息 在SQLServer2005中 一个数据库中最多可以创建20亿个表 用户创建数据库表时 最多可以定义1024列 在同一数据库的不同表中 可以有相同的字段 但在同一个表中不允许有相同的字段 而且每个字段都要求数据类型相同 11 一般的SQL建表语句形式CREATETABLE表名 表约束 列名1数据类型 缺省值1 列约束1 列名2数据类型 缺省值2 列约束2 列名n数据类型 缺省值n 列约束n 12 例3 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表名例3 2删除教师表DROPTABLEteacher 19 3 4表结构的扩充和修改1 修改表的命令一般形式ALTERTABLE表名 ADD列名数据类型 NULL NOTNULL ALTERCOLUMN列名数据类型 NULL NOTNULL DROPCOLUMN列名 20 例3 3在student中增加住址列ALTERTABLEstudentADDaddressCHAR 50 例3 4修改student表中的sdept字段 将数据类型长度为30ALTERTABLEstudentALTERCOLUMNsdeptchar 30 例3 5把Student表中的Sdept列删除 ALTERTABLEStudentDROPCOLUMNSdept 21 2 添加主关键字ALTERTABLE表名ADD CONSTRAINT约束名 PRIMARYKEY 主键列 3 删除主关键字ALTERTABLE 表名 DROP CONSTRAINT 约束名3 补充定义外来关键字ALTERTABLE 表名 1 ADD CONSTRAINT约束名 FOREIGNKEY 外来关键字名 REFERENCES 表名 2 5 删除外来关键字ALTERTABLE 表名 DROP 外来关键字名 22 例3 6补充定义student表的主关键字 ALTERTABLEStudentADDCONSTRAINTpk studentPRIMARYKEY Sno 例3 7 删除course表中的主关键字 ALTERTABLEcourseDROPpk course例3 8 修改sc表 在sno字段上创建外键约束 ALTERTABLEscADDCONSTRAINTfk sc studentFOREIGNKEY sno REFERENCESstudent sno 23 例3 9 删除sc表中sno字段上的外键约束ALTERTABLEscDROPfk sc student例3 10 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDScomedateDATETIME例3 11 将年龄的数据类型改为半字长整数 ALTERTABLEStudentALTERCOLUMNSageSMALLINT 24 3 5数据完整性 数据完整性是指存储在数据库中的数据的一致性和准确性 数据完整性技术是衡量数据库质量好坏的重要标准 在MicrosoftSQLServer系统中 提供了一系列实现数据完整性的方法 这些方法主要包括主键约束 外键约束 非空约束 惟一约束 默认值约束 检查约束以及规则和默认对象 25 分类 实体完整性参照完整性域完整性实体完整性又称为行完整性 这里的实体是指表中的记录 一个实体就是表的一条记录 实体完整性要求在表中不能存在完全相同的记录 而且每条记录都要具有一个非空且不重复的主键值 26 参照完整性 一般指多个实体或关系表之间的关联关系 在关系数据库中用外码 Foreignkey 来实现参照完整性 域完整性也称用户定义的完整性或语义完整性 用户定义的完整性实际上就是指明关系中属性的取值范围 也就是属性的域 即限制关系中属性的取值类型及取值范围 防止属性的值与应用语义矛盾 27 约束定义了必须遵循的用于维护数据一致性和正确性的规则 是强制实现数据完整性的途径 约束分类 主键约束外键约束唯一值约束检查约束默认约束 28 1 唯一性约束 UNIQUE UNIQUE用来限制不受主键约束的列上的数据的唯一性 一个表可以放置多个UNIQUE约束语法 CONSTRAINTconstraint name UNIQUE 列名 29 例3 12为student表添加身份证号列 id 并为 id 列添加唯一值约束 ALTERTABLEstudentADDidBIGINTCONSTRAINTunq idUNIQUE注 UNIQUE约束列允许有NULL值 但最多只能有一个NULL值 否则认为重复 例3 13为course表的cname列添加唯一值约束 ALTERTABLEcourseADDCONSTRAINTunq cnameUNIQUE cname 30 2 检查约束 CHECK CHECK约束用来指定某列的可取值范围 通过限制输入值强制控制域的完整性 语法 CONSTRAINTconstraint name CHECK 检查条件 例3 14限制student表中的ssex列只能添入值为 男 或 女 ALTERTABLEstudentADDCONSTRAINTcheck ssexCHECK ssex 男 orssex 女 31 3 默认约束 DEFAULT DEFAULT约束用于给表中指定列赋予一个常量值 默认值 语法 CONSTRAINT约束名 DEFAULT默认值FOR列名例3 15为student中的sage列添加默认值 19 ALTERTABLEstudentADDCONSTRAINTdefault sageDEFAULT19FORsage 32 上机实践 独立完成实验 实验目的 本章实验主要目的是学习正确创建表 掌握修改表结构的方法 正确删除表 实验内容 自定义数据类型 分别用企业管理器和查询分析器创建表 分别用企业管理器和查询分析器修改表 分别用企业管理器和查询分析器删除表 33 实验题目 题目一 在学生数据库student中创建学生个人信息数据表studentinfo题目要求分析 studentinfo数据表中包含以下信息 学号 姓名 性别 年龄 班级 地址以及联系方式等 如下表所示 34 35 题目二 修改个人信息数据表studentinfo 完成如下操作 删除year old字段 向该表中添加一列 列名为email 数据类型为char 长度40 允许空 题目要求分析 根据题目一创建的个人信息数据表studentinfo 可以使用企业管理器和Transact SQL语句来实现 36 题目三 删除个人信息数据表studentinfo 题目要求分析 将题目一中创建的个