表的建立与维护

1. 模块三 表的建立与维护

2. 教学目的： 掌握数据库在SQL SERVER 2000中的建立的基础上，再掌握数 据库中表的建立和维护

3. 教学流程 表的建立与维护 课题一 表结构在 企业管理 器中建立 课题二 表结构在查 询分析器中 的建立 课题三 在查询分析 器中删除表 结构

4. 教学流程 分析表格的列 的构成，引导 表的结构，表 的数据类型 任务一 课题一 表结构 在企业 管理器 中建立 用企业管理器 建立STU表 任务二 用企业管理器 删除STU表 任务三 返回

5. 教学流程 课题二 表结构 在查询 分析器 中的建 立 用SQL语句 建立STU表 任务一 返回

6. 教学流程 向表STU中增加一个字段， ZP即照片字段，为image类型 任务一 课题三 在查询 分析器 中删除 表结构 任务二 将表STU中字段XM的宽度变 为10，原宽度为8 任务三 将表STU中增加的字段ZD1 删除 任务四 将表STU中增加的字段ZC的 字段名改为ZHUCE，类型和 宽度不变 返回

7. 学生基本情况表 学号 姓名 性别 出生日期 班级编号 注册 总成绩 名次 041022021001 曹静 女 041022021 041022031001 杨余 女 041022031 041022031002 兰飞 男 041022031 041022041001 陈宏 男 041022041 041022041005 文琴 女 041022041 041022051001 邓茗双 女 041022051 041022051002 李林蔚 女 041022051 041022071007 王龙 男 041022071 041022071008 李智 男 041022071 041022071013 刘初阳 男 041022071 041022061001 赵晓燕 女 041022061 041022061002 甘露 女 041022061 041022061004 姜黎明 男 041022061 课题一表结构在企业管理器中建立 任务一：分析表格的列构成，引导表的结构，表的数据类型 一、分析表格 返回

8. 项目 表格（现实世界） 表（计算机世界） 名称 表格名（学生基本信息表） 表名（STU） 列 表格列（学号、姓名….） 字段（XH、XM….） 行 表格行（表格的内容） 记录（表的内容） 该表格的标题是“学生基本信息表”，由专题一知道该表格对应数据库中的一张表，表名为“STU”，表格的每一列在表中称为“字段”，表格的行在表中称为“记录”。一张表格与数据库中的表有如下的对对应关系。 表格的列说明了这个表格的组成关系，表的字段形成了表的结构。表格的列有宽度限制，有不同的数据类型，如“学号”为12个宽度的数字字符数据，“出生日期”为日期型的数据，“总成绩”为带1位小数位的实数，“名次”为正整数。那么表的字段有哪些数据类型呢？ 在MS SQL SERVER 2000中字段的数据类型有：数值型、字符串型、时间日期型等几大类。

9. 1、数值型 • 数值型的数据类型用于表示数字，一般常用的格式为：numeric(n,d)这里n是精度，表示小数点左边和右边的十进制最大的个数，这里不包括小数点。d是小数位数，表示在小数点右边的小数部分的位数。这是数据库中最通用的表示数值的形式，还有一种格式与它相同，格式为： • decimal(n,d) • 例如，表示职工工资可以使用numeric(10,2)数据类型，单位为人民币元，这表示小数部分为2位，也就是表示到分；它可以表示的最大范围是99999999.99，这已经达到了九千九百多万了，显然在现实生活中一个职工的月工资不会达到那么多，则可以根据实际情况使用numeric(8,2)或numeric(7,2)。 • 有时候可能只需要一个整数，例如，考试成绩是百分制的，则可以使用numeric(3,0)，这样在存储上比较浪费，所以数据库还设置了一些常用的数值类型，如下所示： • int表示整数，使用四个字节存储； • smallint表示整数，使用二个字节存储； • Real 表示浮点数； • Float表示浮点数； • Money表示币值，小数点后有四位小数数字，代表货币或现金值的货币数据类型，其数据值介于-263 (-922,337,203,685,477.5808) 与263 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十。存储大小为8 个字节。

10.     smallmoney表示币值，小数点后有四位小数数字，代表货币或现金值的货币数据类型。其数据值介于-214,748.3648 与+214.748,3647 之间，精确到货币单位的千分之十。存储大小为4 个字节。 所以工资的最好表示数据类型是smallmoney。 2、字符串型 现在的数据库系统都支持两种类型的串：字符串、二进制串。字符串就是可以显示的串，一般表示文本内容。二进制串是以计算机系统内部格式表示的数据对象，它需要相关软件进行解释和处理，例如图像、声音、视频等多媒体信息。 我们这里主要介绍字符串。一般字符串的表示格式为： char(n) 它表示一个字符串，n是字符串中字符的个数。例如我们要存储职工姓名，因为一般我们的名字最多三个汉字，当然现在四个汉字的姓名也比较多，要把这些情况包括进去最好的格式为： char(8) 这里注意每一个汉字占两个英文字符串，所示要使用8。 一般来说char(8)定义了存储时要存储八个字节，即使存储两个汉字（四个字节），那么剩下的四个字节也要使用空格填满。为了解决存储空间的问题，数据库系统又引入了下列字符类型： varchar(n) 它表示一个变长的字符串。与char相比，varchar类型的数值按照实际长度进行存储。例如，使用varchar(8)表示职工姓名，如果现在存储职工“张三”，则按照实际数据的长度（两个汉字，即4个字节）来存储。如果使用char(8)，则要存储8个字节。 Text定义了一个最多可以为2GB的定长字符，常用于存储大量文本块。数据库系统一般把这种数据类型作为多媒体信息单独处理。SQL Server还提供了nchar、nvarchar、ntext，希望同学们参考有关资料。

11. 这里我们要强调的是：对于数据库用户来讲，要掌握char(n)的使用，理解char(n)与varchar(n)之间的差异，这两者在表示能力上和内部存储上都有差异。我们在初学数据库阶段，只要考虑正确性，不需要太多考虑性能。就目前而言，使用哪种方式都可以。但是在数据库系统性能低的情况下，就需要考虑究竟使用哪种方式效率会更高。text与char(n)的差异是表示范围上，char(n)最多可以表示8000个字符，text最多可以表示2GB的字节。这里我们要强调的是：对于数据库用户来讲，要掌握char(n)的使用，理解char(n)与varchar(n)之间的差异，这两者在表示能力上和内部存储上都有差异。我们在初学数据库阶段，只要考虑正确性，不需要太多考虑性能。就目前而言，使用哪种方式都可以。但是在数据库系统性能低的情况下，就需要考虑究竟使用哪种方式效率会更高。text与char(n)的差异是表示范围上，char(n)最多可以表示8000个字符，text最多可以表示2GB的字节。 • 3、日期时间型 • 对于日期和时间类型，不同数据库系统有不同的处理方式。SQL Server支持下列的日期和时间类型： • datetime 以八个字节的整数形式存储日期和时间值。从1753 年1 月1 日至9999 年12 月31 日的日期。 • Smalldatetime以四个字节的整数形式存储日期和时间值。使用smalldatetime 数据类型存储从1900 年1 月1 日至2079 年6 月6 日的日期。 • 在Transact-SQL语言中使用两个单引号或者双引号括起来的字符串来表示时间日期。一般建议使用单引号。 • 例1.3，SQL Server 可以识别下列格式（在单引号' 中表示的字符）所表示的日期和时间： • '1998-2-15'、'1998/12/07' 、'19981207'、'19981207 14:30:00'

12. 序号 字段名 类型 宽度 中文说明 1 XH CHAR 12 学号（主键） 2 XM CHAR 8 姓名 3 XB CHAR 2 性别 4 CSRQ DATETIME 8 出生日期 5 BJBH VHAR 9 班级编号 6 ZC CHAR 1 注册标志0—未注册 1—已注册 7 ZCJ NUMERIC 6,1 总成绩 8 MC INT 4 名次 二、表的设计 根据上面对SQL数据类型的介绍，对上述示例表格设计表为： 1、表名：STU 2、字段：

13. 3、常用表示法： 表名 类型 宽度 字段名 小数位

14. 任务2 用企业管理器建立STU表 在企业管理器中找到STUCJ数据库，点右键选“新建表”进入表建立界面： 返回

15. 在新建表中输入相应的字段、字段类型、宽度，其界面与ACCESS界面类似。在新建表中输入相应的字段、字段类型、宽度，其界面与ACCESS界面类似。

16. 然后保存，在对话框中输入表名：“STU”，点“确定”，表STU就建好了。然后保存，在对话框中输入表名：“STU”，点“确定”，表STU就建好了。 新建表STU

17. 实训1：根据实例表格，设计出表BJ（班级表）、KC（课程表）、XK （选课表），并用企业管理器建立这些表。

18. 任务3 用企业管理器删除STU表 进入企业管理器，选中STU表，点工具栏的“X”，进入确定界面进行确定 返回

19. 点击该健 表STU被删除 实训2：在企业管理器中，将BJ、KC、XK三个表删除。

20. 课题二在查询分析器中建立表结构 任务用SQL语句建立STU表 建立语句为：CREATE TABLE命令 use stucj go create table stu ( xh char(12) primary key not null, xm char(8) , xb char(2), csrq datetime, bjbh char(9), zc char(1), zcj numeric(6,1), mc int ) 返回

21. 运行界面： 查询分析器 打开数据库 表名 字段定义 执行结果

22. 建立BJ的SQL命令： Create table bj (bjbh char(9) not null primary key ,bjmc varchar(30),bjrs int 由此可以得到建立表的一般SQL命令格式： CREATE TABLE简洁语法如下： CREATE TABLE <表名>( <列名称> <列类型> <列说明>…， [ constraint <约束名称> <约束条件>] ) 其中： <表名> 是新建表的名称。 <列名称> 是表中列的名称。 <列类型> 是列的数据类型，它既可以是SQL Server系统数据类型， 也可以是用户定义的数据类型。 <列说明> 说明列长度、列的默认值、主键等有关该列的约束条件。 <约束名称> 是表中所建立约束的名称，它在数据库中是唯一的。 <约束条件> 是约束条件的具体内容。 实训1：用SQL语句建立课程（KC）表、选课（XK）表。

23. 课题三在查询分析器中修改表结构 目的：让学生掌握如何使用SQL命令修改表的结构。 在一张表建立之后，如果发现有不妥之处，则可以更改表的定义。更改表定义的操作有两种方式：使用【企业管理器】和SQL语句。由于使用【企业管理器】修改表定义与使用【企业管理器】建立新表操作非常类似，这里不再赘述。 任务1 向表STU中增加一个字段，ZP即照片字段，为image类型。 返回

24. 学号 姓名 性别 出生日期 班级编号 注册 总成绩 名次 照片 041022021001 曹静 女 041022021 041022031001 杨余 女 041022031 041022031002 兰飞 男 041022031 041022041001 陈宏 男 041022041 041022041005 文琴 女 041022041 041022051001 邓茗双 女 041022051 041022051002 李林蔚 女 041022051 041022071007 王龙 男 041022071 041022071008 李智 男 041022071 041022071013 刘初阳 男 041022071 041022061001 赵晓燕 女 041022061 041022061002 甘露 女 041022061 041022061004 姜黎明 男 041022061 任务说明： 增加1列“照片” 学生基本情况表

25. SQL命令为： Alter table stu add zp image 从运行结果来看命令已执行，表STU中就增加了一个字段（ZP image）

26. 命令的关键在： ALTER TABLE：修改表结构，命令项 STU：为修改的对象（表），可变项 ADD：增加列，命令项 ZP Image ：增加的列，可变项。 【加强】给STU表增加一个字段ZD1 char(8) Alter Table STU ADD ZD1 char(8) 实训1：给STU表增加字段（ZD2 int）和字段（JL text）简历（文本型） 实训2：给BJ班增加字段XB char(6)，给KC表增加字段KCXZ char(5)

27. 学号 姓名 性别 出生日期 班级编号 注册 总成绩 名次 照片 041022021001 曹静 女 041022021 041022031001 杨余 女 041022031 041022031002 兰飞 男 041022031 041022041001 陈宏 男 041022041 041022041005 文琴 女 041022041 041022051001 邓茗双 女 041022051 041022051002 李林蔚 女 041022051 任务2 将表STU中字段XM的宽度变为10，原宽度为8 任务说明：姓名字段（XM）原来的宽度为8个字符，如果现在有位同学的姓名是“玛丽亚莱西”，共10个安符超过了8个，该列的宽度不能适应问题，在处理表格时我们将其拉宽，在SQL SERVER中如何处理呢？ 学生基本情况表 将该列变宽实适应数据 返回

28. 用命令：Alter table stu alter column xm char(10)，将XM字段重新定义为CHAR（10）。看运行情况： 未执行命令前 为char(8)

29. 执行命令后 变为char(10)了 命令

30. 实训1：将学号字段（XH）宽度由12 位增加到16位，将姓名字段（XM） 宽度由10位变为8位。 实训2：将注册字段（ZC）由字符型（Char）改为整型（Int）。

31. 学号 姓名 性别 出生日期 班级编号 注册 总成绩 名次 ZD1 041022021001 曹静 女 041022021 041022031001 杨余 女 041022031 041022031002 兰飞 男 041022031 041022041001 陈宏 男 041022041 041022041005 文琴 女 041022041 041022051001 邓茗双 女 041022051 041022051002 李林蔚 女 041022051 041022071007 王龙 男 041022071 041022071008 李智 男 041022071 任务3 将表STU中增加的字段ZD1删除 将该列删除 任务说明： 如果表中的一列在表格中不适合，我们就将其删除，在表STU中如何删除一个字段ZD1呢？ 返回

32. 未删除前 删除前，ZD1

33. 删除后 删除命令 ZD1没了

34. 在SQL中删除一个字段的命令为： Alter table STU drop column zd1 其中 Alter table ：修改表的命令，不变项。 STU： 被修改的表，可变项 Drop column：删除列，命令，不变项。 ZD1 ：被删除的列，可变项。 实训1：将STU表中的ZP字段删除。 实训2：将BJ班字段XB char(6)，KC表字段KCXZ char(5)删除

35. 任务4 将表STU中增加的字段ZC的字段名改为 ZHUCE，类型和宽度不变 如果我们要用SQL命令更改表的字段名，用一个系统存储过程SP_RENAME来实现，要实现将ZC字段改为ZHUCE用： SP_RENAME ‘stu.zc’,’ZHUCE’,’COLUMN’ 其中： SP_RENAME：SQL命令，不变项 stu.zc：STU表的ZC字段，可变项 ZHUCE：新字段名，可变项 COLUMN：命令项，不变项，表示改的是列“column” 返回

36. 执行前 ZC字段

37. 执行命令后 SQL命令 ZHUCE了

38. 实训1：将STU表的JL字段名改为“JIANLI”。 实训2：用SQL命令将STU表删除。