1 / 131

项目三 公司管理数据系统的表的创建和修改

项目三 公司管理数据系统的表的创建和修改. 终极目标：会创建公司管理数据库系统用户数据表，并能根据需要实现表的修改和删除操作. 工作任务. 在 SQL Server 2000 数据库管理系统下，在 companyinfo 数据库中完成公司管理数据库系统各数据表的创建，并能根据实际需要进行数据表的修改、删除及数据的相关操作。. 模块 1 公司管理数据系统数据表的建立. 教学目标： 1 ．会用企业管理器和 Transact—SQL 语句创建数据表 2 ．会用企业管理器和 Transact—SQL 语句创建外键约束以实现数据表间的关联. 工作任务（ 1 ）.

sylvie
Download Presentation

项目三 公司管理数据系统的表的创建和修改

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 项目三 公司管理数据系统的表的创建和修改 终极目标：会创建公司管理数据库系统用户数据表，并能根据需要实现表的修改和删除操作

  2. 工作任务 • 在SQL Server 2000数据库管理系统下，在companyinfo数据库中完成公司管理数据库系统各数据表的创建，并能根据实际需要进行数据表的修改、删除及数据的相关操作。

  3. 模块1 公司管理数据系统数据表的建立 • 教学目标： • 1．会用企业管理器和Transact—SQL语句创建数据表 • 2．会用企业管理器和Transact—SQL语句创建外键约束以实现数据表间的关联

  4. 工作任务（1） • 1、创建名称为“product”的产品信息表，该表结构如表3-1所示，要求利用企业管理器和Transact—SQL语句两种方法实现。 • 表3-1 product表

  5. 一、 系统数据类型 • 1. 整型数据类型 • 2. 浮点数据类型 • 3. 字符数据类型 • 4. 日期和时间数据类型 • 5. 文本和图形数据类型 6. 货币数据类型 7. 位数据类型 8. 二进制数据类型 9. 特殊数据类型 10. 新增数据类型 数据类型就是定义每一列所能存放的数据值和数据格式。

  6. 1. 整型数据类型 • 整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。 • int（integer）：可以存储从-231 ~231-1（-2147483648~（2147483647）范围之间的所有正负整数。 • Smallint：可以存储从-213 ~213-1（-32768~32767）范围之间的所有正负整数 。2个字节 • Tinyint：可以存储从0到255范围之间的所有正整数。 • Bigint：存放从-263到263-1范围内的整型数据。以bigint数据类型存储的每个值占用8个字节，共64位，其中63位用于存储数字，1位用于表示正负。

  7. 2. 浮点数据类型 • 浮点数据包括按二进制技术系统所能提供的最大精度保留的数据。浮点数值的数据在SQL Server中采用只入不舍的方式进行存储 。 • Real：精确到第7位精确位数，范围从-3.40E-38~ 3.40E+38 ，占用4个字节的存储空间。 • Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308，占用8个字节的存储空间。 • 语法： • float[n] ,n 为数据精度1~7定义real,8~15为float

  8. 小数数据类型 • Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。

  9. 例： • 存储数值1829.3455比存储5.20需要更加多字节。 • 声明格式 ： • numeric(p,[s]) | decimal(p,[s]) • 其中p为精度，s为小数位数，s的默认值为0. • 例：decimal(6,3) 若向该列赋值65.4542345则实际存储的是65.454

  10. 3. 字符数据类型 • SQL Server提供了3类字符数据类型，分别是Char、Varchar和Text。在这3类数据类型中，最常用的Char和Varchar两类。字符数据类型可以用来存储各种字母、数字符号和特殊符号。

  11. Char • 利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。 • 格式：char[(n)] • n表示所有字符所占的空间，默认值为1。 • 如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。 • 如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。 • 在使用字符型常量为字符数据类型赋值时，必须使用单引号（‘’）将字符型常量括起来。

  12. Varchar • Varchar数据类型来存储最长可以达到8000字符的变长字符。与Char 数据类型不同，Varchar数据类型的存储空间随存储在表列中的每一个数据的字符数的不同而变化。 • 格式：Varchar[(n)] • n表示所有字符所占的空间，1~8000。 • 例:定义表列为Varchar(20)，那么存储在该列的数据最多可以长达20个字节。但是在数据没有达到20个字节时并不会在多余的字节上填充空格。 • 当存储在列中的数据的值大小经常变化时，使用Varchar数据类型可以有效地节省空间。 但是char比Varchar的处理速度快。

  13. Text • 当要存储的字符型数据非常庞大以至于8000字节完全不够用时，Char和Varchar数据类型都失去了作用。这时应该选择Text数据类型。 • Text：用于存储大量文本数据，其容量理论上为1到231-1（21，4748，3647）个字节，但实际应用时要根据硬盘的存储空间而定。 • Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。

  14. 4. 日期和时间数据类型 • 代表日期和一天内的时间的日期和时间数据类型。 • Datetime：用于存储日期和时间的结合体 。从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。 • Smalldatetime：从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据精确到分钟。 • SQL Server在用户没有指定小时以上精度的数据时，会自动设置Datetime和Smalldatetime数据的时间为00:00:00。

  15. 输入日期和时间数据 • 常用的日期表示格式： • 月/日/年：5/1//2003、05/01/03 • 月-日-年：5-1-2003、05-01-03 • 月.日.年：5.1.2003、05.01.03 • 常用的时间表示格式： • 时：分 08：05 • 时：分：秒 08：05：25 • 时：分 ：秒：毫秒 08：05：25：23 • 时：分 AM | PM 08：05 AM

  16. 5. 货币数据类型 • Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213683477.3808到922337213683477.3808，精度为货币单位的万分之一。 • Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。

  17. 7. 位数据类型 • Bit数据相当于其他语言的逻辑型数据。他存储0和1,长度为1字节。

  18. 8. 二进制数据类型 • binary [ ( n ) ]：固定长度的 n 个字节二进制数据。N 必须从 1 到 8,000。存储空间大小为 n+4 字节。 • varbinary [ ( n ) ]：n个字节变长二进制数据。n 必须从 1 到 8,000。存储空间大小为实际输入数据长度 +4 个字节，而不是 n 个字节。输入的数据长度可能为 0 字节。

  19. 9. 特殊数据类型 • Timestamp：亦称时间戳数据类型，数据库范围的唯一数字，每次更新行时也进行更新。timestamp 这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。存储大小为 8 字节。 • 一个表只能有一个 timestamp 列。每次插入或更新包含 timestamp 列的行时，timestamp 列中的值均会更新。

  20. 10. 新增数据类型 （1） • Bigint：用于存储从-263（-9，223，372，036，834，773，807）到263-1（9，223，372，036，834，773，807）之间的所有正负整数。 • sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据。sql_variant 类型的列可以包含不同数据类型的行。例如，定义为 sql_variant 的列可以存储 int、binary 和 char 值。不能使用 sql_variant 存储的值类型只有 text、ntext、image、timestamp 和 sql_variant。 • sql_variant 的最大长度可达 8016 字节。

  21. 10. 新增数据类型 （2） • table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

  22. 11.用户自定义数据类型（1） • 用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。 • 例：SQL Server 实例包括一个名为 sysname 的用户定义数据类型。sysname 在功能上与 nvarchar(128) 等效。

  23. 创建用户定义的数据类型时提供参数： • 名称 • 新数据类型所依据的系统数据类型 • 为空性（数据类型是否允许空值） • 如果为空性未 明确定义，系统将依据数据库或连接的 ANSI Null 默认设置进行指派。

  24. 创建数据类型的方法： • 1. 使用企业管理器（Enterprise Manager）创建用户自定义数据类型。 • 2. 利用系统存储过程创建用户自定义数据类型 。 • 系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径。 • 语法： • sp_addtype [@typename=] type, • [@phystype=] system_data_type • [, [@nulltype=] ‘null_type’]

  25. 【例1】创建一个用户定义的数据类型ssn，其基于的系统数据类型是变长为11的字符，不允许空。 命令如下： • Exec sp_addtype ssn, 'varchar(11)', 'Not Null' • 【例2】创建一个用户定义的数据类型 birthday，其基于的系统数据类型是 datetime，允许空。命令如下： • Exec sp_addtype birthday,datetime,'Null'

  26. 删除用户自定义的数据类型 • 1. 使用企业管理器（Enterprise Manager）删除用户自定义数据类型。 • 2. 利用T-SQL删除用户自定义数据类型 。 • 系统存储过程sp_droptype为用户提供了T_SQL语句创建自定义数据类型的途径。 • 语法： • sp_droptype [@typename=] type

  27. 【例3】删除自定义的生日数据类型。 • exec sp_droptype birthday • 其运行结果如下： • （1 row(s) affected） • （0 row(s) affected） • Type has been dropped.

  28. 【例4】假定用户已定义了一个数据类型ssn，则删除该数据类型的命令为：【例4】假定用户已定义了一个数据类型ssn，则删除该数据类型的命令为： • Exec sp_droptype 'ssn' • 注意：当表中的列还正在使用用户定义的数据类型时，或者在其上面还绑定有默认或者规则时，这种用户定义的数据类型不能删除。

  29. (二) 表的概念（1） • 表是数据存储的地方，在SQL Server中所有的数据存储都基于表，表是数据库中最重要的部分。表由行和列组成。表中的一行称为一条记录，表中的一列称为一个字段。

  30. （二) 表的概念（2） • 每个SQL Server数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。行数和表的大小只受服务器中可用存储空间的限制。

  31. （二) 表的概念（3） • SQL Server 2000的数据表可分为用户表和系统表两种类型。用户表是数据库用户根据自己的设计创建的表，用于存放用户的数据，系统表则是SQL Server 2000为实现数据库维护而创建的表，任何用户都不应该直接修改系统表，否则会导致数据库无法使用。

  32. （三）数据表的创建1. 利用企业管理器创建表 • 步骤如下： • （1）在树形目录中找到要建表的数据库，展开该数据库。 • （2）选择表，单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，表设计器如图所示。 • （3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行以下设置，其中前三项是必须在建表时给出的，它们是：列名、数据类型、长度。 • （4）定义好所有列后，单击工具栏上的保存按扭，表就创建完成了。

  33. 表设计器3-1

  34. 表设计器3-2 • 数据类型：数据类型是一个下拉列表框，其中包括了所有的系统数据类型和数据库中的用户自定义数据类型。 • 长度：如果选择的数据类型需要长度，则指定长度。 • 允许空：单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。

  35. 表设计器3-3 • 表设计器的下半部分是特定列的详细属性，包括是否是标识列、是否使用默认值等。 • 描述：定义字段的说明信息。 • 默认值：定义字段的默认值。如果在输入记录时没有指定该字段的值，便将该字段设置为默认值。 • 标识：定义是否允许字段自动编号，其值可以为“否”、或“是（不用于复制）”。都表示允许字段自动编号，只是后者在通过复制方法添加数据时不自动编号。 • 标识种子：定义字段自动编号的初值，默认值为1。 • 标识递增量：定义字段自动编号的递增量，默认值为1。 • 公式：定义计算字段的表达式。在定义表达式时，字段名称应使用[ ]括起来，例如，[a] + [b] + 10，其中a，b分别为字段名称。 • 排序规则：定义字段的排序规则。

  36. 工作任务（1） • 1、创建名称为“product”的产品信息表，该表结构如表3-1所示，要求利用企业管理器和Transact—SQL语句两种方法实现。 • 表3-1 product表

  37. 方法一、利用企业管理器实现 • 步骤如下： • 1.打开SQL Server企业管理器。 • 2.展开companyinfo数据库节点，用鼠标右击“表”对象，在打开的快捷菜单中执行【新建表】命令。 • 3.在打开的“表设计器”对话框中。定义完成各字段后。 • 说明：当创建表时，必须指定表名、列名以及数据类型。同一个表中，列名不能重复，但是，同一个数据库中不同表的列名称可以相同。并必须为每个列都指定数据类型。

  38. 方法二 利用create命令创建表 用“CREATE TABLE表名”来创建表，表中列的定义必须有括号括起来。一个表最多有1024列。 语法： • CREATE TABLE table_name ( • { < column_definition > /*列的定义*/ • | column_name AS computed_column_expression /*定义计算列*/ • | < table_constraint > } /*指定表的约束*/ • [ ON { filegroup | DEFAULT } ] /*指定存储表的文件组*/ • [ TEXTIMAGE_ON { filegroup | DEFAULT }] • /*指定存储text,ntext和image类型数据的文件组*/

  39. 说明： • < column_definition > ::= • { column_name data_type } /*指定列明、类型*/ • [[DEFAULT constant_expression ] /*指定默认值*/ • |[IDENTITY [(seed,increment) NOT FOR REPLICATION ] ] ] ] /*指定默认值*/ • [ < column_constraint > ] [ ...n ] • /*指定列的约束*/

  40. 各参数说明2-1： • database_name：用于指定在其中创建表的数据库名称。 • owner：用于指定新建表的所有者的用户名 。 • table_name：用于指定新建的表的名称。 • column_name：用于指定新建表的列的名称。 • computed_column_expression：用于指定计算列的列值的表达式。 • ON {filegroup | DEFAULT}：用于指定存储表的文件组名。 • TEXTIMAGE_ON：用于指定 text、ntext 和 image 列的数据存储的文件组。 • data_type：用于指定列的数据类型 。 • DEFAULT：用于指定列的缺省值。

  41. 各参数说明2-2： • constant_expression：用于指定列的缺省值的常量表达式 。 • IDENTITY：用于指定列为标识列。 • Seed：用于指定标识列的初始值。 • Increment：用于指定标识列的增量值。 • NOT FOR REPLICATION：用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用，即不足的生成列值，使得复制的数据行保持原来的列值。 • column_constraint和table_constraint：用于指定列约束和表约束。

  42. 方法二： 利用Transact-SQL语句创建实现 • 操作步骤： • 1．在打开 “查询分析器”窗口。 • 2．连接数据库：在“查询分析器”窗口的工具栏的下拉列表中，选中companyinfo数据库。 • 3．在查询分析器的查询窗口中输入如下： • CREATE TABLE product( • 产品ID int primary key， • 产品名 char(10) not null， • 类别ID int， • 单价 money, • 库存量 int • )

  43. （二）按照任务2要求创建companyinfo数据库的其它数据表。（二）按照任务2要求创建companyinfo数据库的其它数据表。 • 1．创建表名为“p_order”的产品订单表的命令如下： • CREATE TABLE p_order( • 订单ID int primary key, • 产品ID int, • 数量 int, • 雇员ID int, • 客户ID int, • 订货日期 datetime • ) • GO

  44. 2．创建表名为“employee”的产品信息表的命令如下：2．创建表名为“employee”的产品信息表的命令如下： • CREATE TABLE employee( • 雇员ID int primary key, • 姓名 varchar(8), • 性别 char(2), • 出生日期 datetime, • 雇佣日期 datetime, • 特长 varchar(20), • 薪水 money • ) • GO

  45. 3．创建名为“customer”的客户表和名为“category”的类别表的命令如下：3．创建名为“customer”的客户表和名为“category”的类别表的命令如下： • CREATE TABLE customer ( • /* 创建客户表customer*/ • 客户ID int primary key, • 公司名称 varchar(30) , • 联系人姓名 varchar(8), • 联系方式 varchar(12), • 地址 varchar(30), • 邮编 char(6) • ) • GO

  46. 创建类别表 • CREATE TABLE category ( • /*创建类别表category*/ • 类别ID int primary key, • 类别名 varchar(10) , • 说明 varchar(16) • ) • GO

  47. （三）按照任务3的要求，创建名称为DIAGRAM_p_order_product的数据库关系图，从而实现p-order表和product表间的关联。（三）按照任务3的要求，创建名称为DIAGRAM_p_order_product的数据库关系图，从而实现p-order表和product表间的关联。

  48. 数据约束 • 约束是在创建表时，可以对某一列或多个列的组合设置限制条件，让SQL Server帮助检查该列的输入值是否符合要求。当用户从客户端传送数据到SQL Server服务器，系统也会检查数据是否符合要求，如果不符合要求将拒绝接受。

  49. 约束的实现： • 设置主键（PRIMARY KEY）约束、唯一（UNIQUE）约束、外键（FOREIGN KEY）约束、缺省（DEFAULT）约束和检查（CHECK）约束等。 • 注意： • 对于BYTE和TEXT列不可以指定主键约束、唯一约束和外键约束，但可以用检查约束来检查其值是否为NULL。

  50. 约束的创建 • 约束作为表的一部分，可以在创建表的同时创建约束，也可以在建立表之后追加定义或删除定义。在一个表中可以定义多个约束，甚至可以在一个列上定义多个约束。

More Related