第4章数据表的创建与管理

《第4章数据表的创建与管理》由会员分享，可在线阅读，更多相关《第4章数据表的创建与管理（43页珍藏版）》请在金锄头文库上搜索。

1、第第4章章 数据表的创建与管理数据表的创建与管理主讲人：李翠琳主讲人：李翠琳 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社技能目标技能目标学会创建数据表；学会创建数据表；学会创建和修改销售管理数据库中的数据表；学会创建和修改销售管理数据库中的数据表；学会使用约束来保证数据的完整性。学会使用约束来保证数据的完整性。 2 2本章新单词本章新单词实体完整性实体完整性 Entity Integrity域完整性域完整性 Domain Integrity 参照完整性参照完整性 Referential Integrity用户定义的完整性用户定义的完整性 User

2、- defined Integrity3 3 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社表相关的基本概念表相关的基本概念 表是组织和管理数据的基本单位。表是组织和管理数据的基本单位。表由行和列组成的二维结构。表由行和列组成的二维结构。表中的一行称为一条记录，表中的一列称为一个表中的一行称为一条记录，表中的一列称为一个字段。字段。说明：每个SQLServer数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。4 4 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数

3、据类型 数字数据数字数据字符数据字符数据货币数据货币数据日期和时间数据日期和时间数据二进制字符串二进制字符串其他数据类型其他数据类型 5 5 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-数字型数字型 数据类型数据类型说说 明明bigint -263 (-1.8E19 ) 263-1 (1.8E19 )的整型数int -231 (-2 147 483 648) 231-1 (2 147 483 647)的整型数smallint -215 (-32 768) 215-1 (32 767)的整型数tinyint 0 255的整型数floa

4、t 浮点数数据，从1.79E +308 到 1.79E+308 real浮点精度数字数据，从3.40E+38 到 3.40E+38 bit整数数据，值为1或0numeric(p, s)固定精度和小数的数字数据，取值范围从-1038+1 到 10381。p 变量指定精度，取值范围从1到38。s变量指定小数位数，取值范围从0到p6 6 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-货币型货币型 数据类型数据类型范范 围围money-922,337,203,685,477.5808 到 922,337,203,685,477.5807sma

5、llmoney -214,748.3648 到 214,748.36477 7 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-日期型日期型 数据类型数据类型范范 围围精确度精确度datetime1753年1月 1日到 9999年12月313.33毫秒smalldatetime 1900年1月1日到 2079年6月6日1分钟8 8 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-字符型字符型 数据类型数据类型说说 明明char (n) 固定长度的字符数据，长度为n个字节，n 的取

6、值范围为 1 8 000 varchar (n) 可变长度的字符数据，长度为n个字节，n 的取值范围为 18 000 nchar (n) 固定长度的Unicode 字符数据。n 值在 14 000 之间 nvarchar (n) 可变长度的Unicode 字符数据。n 值在 14 000 之间Text变长度字符数据，最多达到2 147 483 647 字节nText变长度的Unicode字符数据最多可达1 073 741 823 个字符9 9 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-字节二进制和图像型字节二进制和图像型 数据类型

7、数据类型说说 明明binary (n)长度为 n 字节的固定长度二进制数据，其中 n 是从1到8000的值varbinary (n) 可变长度二进制数据。n 可以取从1到8,000的值Image变长度二进制数据 最长为2147 483 647字节1010 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-其他数据类型其他数据类型 数据类型数据类型说说 明明UniqueIdentifier唯一标识数字存储为 16 字节的二进制值TimeStamp当插入或者修改行时，自动生成的惟一的二进制数字的数据类型Cursor允许在存储过程中创建游标变量

8、，游标允许一次一行地处理数据，这个数据类型不能用作表中的列数据类sql_variant可包含除text、ntex、timage 和 timestamp 之外的其他任何数据类型Table一种特殊的数据类型，用于存储结果集以进行后续处理 XML存储 XML 数据的数据类型。可以在列中或者 xml 类型的变量中存储 xml 实例1111 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社列的属性列的属性 列的为空性列的为空性 没有输入的值，未知或未定义。列值可以接受空值没有输入的值，未知或未定义。列值可以接受空值NULL，也可以拒绝空值，也可以拒绝空值NOT N

9、ULL 。IDENTITY属性属性 可以使表的列包含系统自动生成的数字，可以惟一地标可以使表的列包含系统自动生成的数字，可以惟一地标识表的每一行，即表中的每行数据列上的数字均不相同。识表的每一行，即表中的每行数据列上的数字均不相同。 IDENTITY属性的表达格式如下。属性的表达格式如下。 IDENTITY (s, i) 其中，其中，s (seed) 表示起始值，表示起始值，i(increment) 表示增表示增量值，其默认值为量值，其默认值为1。1212 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建销售管理数据库的数据表创建销售管理数据库的数据

10、表 使用使用Management Studio 任务一：在销售管理数据库中，利用任务一：在销售管理数据库中，利用Management Studio 创建客户表。创建客户表。使用使用T-SQL创建数据表创建数据表任务二：在销售管理数据库中，利用任务二：在销售管理数据库中，利用 Create Table语句，语句，创建部门表和商品表。创建部门表和商品表。1313 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建数据表创建数据表-使用使用Management Studio 1、在销售管理数据库中，利用、在销售管理数据库中，利用Management Stud

11、io 创建客户表。创建客户表。步骤：步骤：（1）启动ManagementStudio。（2）在【对象资源管理器】窗口中，展开【数据库】|【CompanySales】|【表】节点。右击【表】节点，从弹出快捷菜单中，选择【新建表】选项，出现表设计器。1414 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建数据表创建数据表-使用使用Management Studio（续）（续）（3）在表设计器中，在【列名】单元格输入字段名“CustomerID”，在同一行的【数据类型】单元格设置该字段的数据类型“int”，并在【允许空】列选择是否允许该字段为空值。如果允

12、许，则选中复选框，如果不允许，则取消选中复选框。（4）重复（3）步骤设置“CompanyName”列、“ContactName”列、“Phone”列、“address”列和“EmailAddress”列。（5）单击【文件】|【保存】或工具栏上的按钮，在出现的对话框中输入表的名称“Customer”，新表的相关信息即会出现在对象资源管理器中。1515 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社使用使用T-SQL创建数据表创建数据表使用使用CREATE TABLE语句创建数据库的基本语法格式如下。语句创建数据库的基本语法格式如下。CREATETABLE

13、(NULL|NOTNULLIDENTITY(seed,increment)，n)CREATE TABLE 表名表名 ( 字段字段1 数据类型数据类型 列的特征，列的特征， 字段字段2 数据类型数据类型 列的特征，列的特征， .)列的特征：列的特征：包括该列是是否为空（包括该列是是否为空（NULL）、是否是标识列（自动编号）、是）、是否是标识列（自动编号）、是否有默认值、是否为主键等。否有默认值、是否为主键等。 1616 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建数据表创建数据表使用使用CREATE TABLE语句（续）语句（续）2、在销售管理数

14、据库中，利用、在销售管理数据库中，利用 Create Table语句，创建部门表和商品表。语句，创建部门表和商品表。USE CompanySalesGOCREATE TABLE Department - -创建部门表创建部门表 ( DepartmentID int NOT NULL, DepartmentName varchar (30) NOT NULL, Manager char(8) NULL, Depart_Description varchar (50) NULL )GOCREATE TABLE product - -创建商品表创建商品表( ProductID int NOT NUL

15、L,ProductName varchar(50) NOT NULL, Price decimal(18, 2) NULL, ProductStockNumber int NULL,ProductSellNumber int NULL）1717 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社销售管理数据库的数据表管理销售管理数据库的数据表管理 查看表结构查看表结构 查看数据表的属性查看数据表的属性 查看表结构查看表结构查看表中数据查看表中数据修改数据表修改数据表 使用使用Management Studio Management Studio 使用使用A

16、lter Table Alter Table 语句语句1818(1）添加列 任务三任务三：在部门表：在部门表DepartmentDepartment中，增加两列：中，增加两列：部门人数列部门人数列PersonNumPersonNum，数据类型为整型，允，数据类型为整型，允许为空；办公地点列许为空；办公地点列OfficeOffice，数据类型为，数据类型为VarcharVarchar（5050）, ,允许为空。允许为空。代码如下：代码如下：ALTER TABLE DepartmentALTER TABLE DepartmentADDADDpersonNumpersonNum intint NUL

17、L, NULL,Office Varchar(50) NULLOffice Varchar(50) NULL（2）删除列 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社1919任务四：任务四：在部门表在部门表Department中，删除两列：部门人数列中，删除两列：部门人数列PersonNum和办公室列和办公室列Office。代码如下：代码如下：ALTER TABLE DepartmentDROP COLUMN personNum ,Office（3）修改列的定义）修改列的定义任务五任务五：在部门表：在部门表Department中，将部门经理列中，将部

18、门经理列Manager的数据类的数据类型改为型改为Varchar（20）。）。代码如下：代码如下：ALTER TABLE DepartmentALTER COLUMN Manager varchar(20) SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2020（4）修改列名修改列名任务六任务六：在部门表：在部门表Department中，部门经理列中，部门经理列Manager重命名为重命名为ManagerName。代码如下：代码如下：sp_rename Department.Manager,ManagerName,COLUMN (5)删除数据表删除数据

19、表任务七任务七：删除部门表：删除部门表Department。代码如下：代码如下：USE CompanySalesGODROP TABLE departmentGo SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2121(6)重命名数据表重命名数据表 任务八任务八：将商品表：将商品表product重命名为重命名为newProduct，然后，然后删除删除newProduct表。表。代码如下：代码如下：USE CompanySalesGOSp_rename product,newProductGoDROP TABLE newProductGO SQL Ser

20、ver 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2222 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据库数据的完整性数据库数据的完整性 实体完整性实体完整性(Entity Integrity)约束约束要求表中的每一行数据都反映不同的实体，不能存在相同的数据行。要求表中的每一行数据都反映不同的实体，不能存在相同的数据行。域完整性域完整性(Domain Integrity )约束约束指给定列的输入有效性。指给定列的输入有效性。参照完整性参照完整性(Referential Integrity)约束约束在输入或删除数据行时，参照

21、完整性约束用来保持表之间已定义的在输入或删除数据行时，参照完整性约束用来保持表之间已定义的关系。关系。用户自定义的完整性用户自定义的完整性(User - defined Integrity)约束约束用来定义特定的规则。用来定义特定的规则。2323 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社约束概述约束概述 约束定义约束定义 约束就是限制，定义约束就是定义可输入表或表的单个列中约束就是限制，定义约束就是定义可输入表或表的单个列中数据的限制条件。数据的限制条件。约束分类约束分类主关键字约束(PrimaryKeyConstraint)惟一约束(Uniqu

22、eConstraint)检查约束(CheckConstraint)默认约束(DefaultConstraint)外关键字约束(ForeignKeyConstraint)实体完整性域完整性参照完整性2424 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社主键约束主键约束 使用使用Management Studio 创建主键约束创建主键约束 （演示设置（演示设置主键）主键）使用使用T-SQL语句在创建表同时主键约束语句在创建表同时主键约束在销售管理数据库中，创建部门表。在销售管理数据库中，创建部门表。代码如下：代码如下：CREATE TABLE Depar

23、tment( DepartmentID int NOT NULL Primary KEY, DepartmentName varchar (30) NOT NULL, Manager char(8) NULL, Depart_Description varchar (50) NULL )GO2525使用使用T-SQL语句在一张现有表上添加主键约束语句在一张现有表上添加主键约束在创建供应商表在创建供应商表Provider的的ProviderID上，添加主键约上，添加主键约束。束。代码如下：代码如下：ALTER TABLE ProviderADD Constraint PK_Provider PR

24、IMARY KEY(ProviderID) SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2626 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社外键约束外键约束 使用使用Management Studio 创建外键约束创建外键约束使用使用Transact-SQL 语句定义外键语句定义外键 语法格式：语法格式： CREATE TABLE (列名列名 数据类型数据类型 为空性为空性 FOREIGN KEY REFERENCES ref_table (ref_column) ） 其中，参数说明如下。其中，参数说明如

25、下。REFERENCES：参照ref_table：主键表名，要建立关联的被参照表的名称ref_column：主键列名2727【例例4.21】在销售管理数据库中，创建在第一章设计的销售订单如在销售管理数据库中，创建在第一章设计的销售订单如表表 4- 13所示。所示。代码如下：代码如下：USE CompanySalesGOCREATE TABLE Sell_Order( SellOrderID int NOT NULL PRIMARY KEY, ProductID int NULL FOREIGN KEY REFERENCES product(ProductID), SellOrderNumber

26、 int NULL, employeeID int NULL FOREIGN KEY REFERENCES Employee(EmployeeID), CustomerID int NULL FOREIGN KEY REFERENCES customer(CustomerID), SellOrderDate smalldatetime NULL SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2828 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社惟一约束惟一约束 惟一约束与主键约束的区别惟一约束与主键约束的区别

27、 。惟一性约束指定的列可以有惟一性约束指定的列可以有NULL 属性。但属性。但主键约束所在的列则不允许；主键约束所在的列则不允许；一个表中可以包含多个惟一约束，而主键约一个表中可以包含多个惟一约束，而主键约束则只能有一个；束则只能有一个；创建惟一约束创建惟一约束创建表的同时创建创建表的同时创建UNIQUE约束约束在现有的表中添加惟一约束在现有的表中添加惟一约束2929【例例4.24】在销售管理数据库中的部门表，为部门名称在销售管理数据库中的部门表，为部门名称列添加惟一约束，保证部门名称的不重复。创建后使列添加惟一约束，保证部门名称的不重复。创建后使用用Transact-SQL 语句删除此约束。

28、语句删除此约束。 代码如下：代码如下：USE CompanySalesGO ALTER TABLE departmentADD CONSTRAINT un_departName UNIQUE (departmentName)GOALTER TABLE departmentDROP CONSTRAINT un_departNameGO3030建立表间的关系建立表间的关系建立表间的关系实际上就是实施参照完整性约束，建立表间的关系实际上就是实施参照完整性约束，建立主表和子表关系。建立主表和子表关系。（1）在）在SQL Management Studio中，在设计表的时中，在设计表的时候，在候，在Se

29、ll_Order表中的右键菜单选择表中的右键菜单选择“关系关系”，单击，单击“添加添加”。（2）单击）单击“表和列规范表和列规范”最右侧的小按钮，将显示要建立最右侧的小按钮，将显示要建立关系的主键表以及主键列对话框。关系的主键表以及主键列对话框。（3）选择主表）选择主表Product，主键字段为，主键字段为ProductID，对应，对应的外键表的外键表Sell_Order的字段也为的字段也为ProductID。单击。单击“确定确定”，主，主-外键关系就建立了外键关系就建立了。3131 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社检查约束检查约束检查约

30、束（检查约束（CHECK约束）定义约束）定义实际上是验证字段输入内容的规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以对每个列设置CHECK约束。 创建检查约束创建检查约束使用Managementstudio使用Transact-SQL语句语法结构：CONSTRAINT约束名CHECK(logical_expression),n3232【练习练习】在销售管理数据库中的商品表中，为了保在销售管理数据库中的商品表中，为了保证数据的质量，确保商品的价格为大于证数据的质量，确保商品的价格为大于0的数，的数，库存量和已销售量数据为非负数。库存量和已销售量数据为

31、非负数。ALTER TABLE productADD CONSTRAINT CK_PTN CHECK (price0) and (ProductStockNumber=0) and (ProductSellNumber=0)3333 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社默认值约束默认值约束 默认值约束（默认值约束（DEFAULT）用于确保域完整性，它提供了）用于确保域完整性，它提供了一种为数据表中任何一列提供默认值的手段。一种为数据表中任何一列提供默认值的手段。 创建默认值约束：创建默认值约束：1、使用、使用Management studio

32、 创建默认值创建默认值 例例4.28 在销售管理数据库中的员工表中，将在销售管理数据库中的员工表中，将“性别性别”列设列设定默认值定默认值“男男”。2、使用、使用Transact-SQL 语句创建默认值定义语句创建默认值定义语法：语法：CONSTRAINT约束名DEFAULTconstant_expressionFOR列名3434【例例】在销售管理数据库中的员工表中，新员工如果不到在销售管理数据库中的员工表中，新员工如果不到特定部门工作的话，新员工全部到特定部门工作的话，新员工全部到“销售部销售部”工作。工作。代码如下：代码如下：USE CompanySalesGOALTER TABLE em

33、ployeeADD CONSTRAINT def_DepartID DEFAULT (1) FOR DepartmentID3535默认值和规则默认值和规则默认值默认值提问：提问：默认值与默认值约束的区别是？默认值与默认值约束的区别是？相同点：当用户向数据表中插入数据行时，如果没有为某列输入值，则由SQLServer自动为该列赋予默认值。不同点：默认值是一种数据库对象。在数据库中创建默认值对象后，可以将其绑定到多个数据表的一个或多个列应用；默认值约束只能用于约束一个表中的列。3636创建规则创建规则规则规则就是对存储在表中列或用户自定义数据类型的取值范围的规定或限就是对存储在表中列或用户自定义

34、数据类型的取值范围的规定或限制。规则与制。规则与CHECK约束很相似，但约束很相似，但CHECK约束不能直接作用于用约束不能直接作用于用户自定义数据类型。户自定义数据类型。【例例4.33】 创建创建一个日期一个日期规则规则RL_Date，即一个员工，即一个员工的雇佣日期在的雇佣日期在1980-1-1和系统的当前日期之间。和系统的当前日期之间。代码如下：代码如下：USE CompanySalesGOCREATE RULE RL_DATE AS date=1980-1-1 and date=getdate()GO3737绑定规则绑定规则绑定规则绑定规则的语法格式：的语法格式：sp_bindrule

35、 规则名称规则名称，object_name【例例4.34】 将规则将规则RL_Date绑定到员工表的雇绑定到员工表的雇佣日期列上。佣日期列上。代码如下：代码如下：USE CompanySalesGOexec sp_bindrule RL_DATE ,employee.hireDate GO3838删除规则删除规则【例例4.35】 删除删除RL_Date规则。规则。代码如下：代码如下：（1）解除绑定）解除绑定 USE CompanySalesGOexec sp_unbindrule employee.hireDateGO（2）删除规则）删除规则USE CompanySalesGODROP RUL

36、E RL_DateGO3939上机任务上机任务上一阶段已经建立了上一阶段已经建立了BBS数据库，本阶段将在上一阶段的数据库，本阶段将在上一阶段的基础上建立数据库的表结构。基础上建立数据库的表结构。列名列名数据类型数据类型长度长度主键是否允主键是否允许为空许为空默认值默认值描述描述UIDint不允许不允许标识列，自标识列，自动增长动增长UNamevarchar32不允许不允许用户名用户名UPasswordvarchar16不允许不允许888888密码密码Upointint4允许允许20用户积分用户积分UEmailvarchar32允许允许PP.com电子邮件地电子邮件地址址用户表用户表BBSUs

37、ers4040上机任务（续）上机任务（续）列名数据类型长度主键是否允主键是否允许为空许为空默认值描述TIDint不允许标识列，自标识列，自动增长动增长TNumbervarchar32不允许帖子编号TUIDint不允许发帖人IDTSIDint不允许版块IDTTopicvarchar255不允许标题信息TContentsnText不允许帖子正文TTtimedatetime允许发帖时间主贴表的结构主贴表的结构4141跟帖表的结构列名数据类型长度主键是否允许为空默认值描述RIDint不允许标识列，自标识列，自动增长动增长RNumbervarchar32不允许帖子编号RTIDint不允许主贴IDRSIDint不允许版块IDRUIDint不允许回帖人IDRTopicvarchar255不允许标题信息RContentsnText不允许跟帖的正文4242版块表的结构列名数据类型长度主键是否允许为空默认值描述SIDint不允许标识列，自标识列，自动增长动增长SNamevarchar32不允许版块名称SMasterIDint不允许版主用户IDSClickCountint允许点击率STopicCountint允许发帖数4343