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

2023/9/18,主讲人：李翠琳,第4章 数据表的创建与管理,2023/9/18,2,SQL Server 2005数据库应用技术 清华大学出版社,技能目标,学会创建数据表；学会创建和修改销售管理数据库中的数据表；学会使用约束来保证数据的完整性。,2023/9/18,3,本章新单词,实体完整性 Entity Integrity域完整性 Domain Integrity 参照完整性 Referential Integrity用户定义的完整性 User-defined Integrity,2023/9/18,4,SQL Server 2005数据库应用技术 清华大学出版社,表相关的基本概念,表是组织和管理数据的基本单位。表由行和列组成的二维结构。表中的一行称为一条记录，表中的一列称为一个字段。说明：每个SQL Server数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。,2023/9/18,5,SQL Server 2005数据库应用技术 清华大学出版社,数据类型,数字数据字符数据货币数据日期和时间数据二进制字符串其他数据类型,2023/9/18,6,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-数字型,2023/9/18,7,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-货币型,2023/9/18,8,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-日期型,2023/9/18,9,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-字符型,2023/9/18,10,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-字节二进制和图像型,2023/9/18,11,SQL Server 2005数据库应用技术 清华大学出版社,数据类型-其他数据类型,2023/9/18,12,SQL Server 2005数据库应用技术 清华大学出版社,列的属性,列的为空性 没有输入的值，未知或未定义。列值可以接受空值NULL，也可以拒绝空值NOT NULL。IDENTITY属性 可以使表的列包含系统自动生成的数字，可以惟一地标识表的每一行，即表中的每行数据列上的数字均不相同。IDENTITY属性的表达格式如下。IDENTITY(s,i)其中，s(seed)表示起始值，i(increment)表示增量值，其默认值为1。,2023/9/18,13,SQL Server 2005数据库应用技术 清华大学出版社,创建销售管理数据库的数据表,使用Management Studio 任务一：在销售管理数据库中，利用Management Studio 创建客户表。使用T-SQL创建数据表任务二：在销售管理数据库中，利用 Create Table语句，创建部门表和商品表。,2023/9/18,14,SQL Server 2005数据库应用技术 清华大学出版社,创建数据表-使用Management Studio,1、在销售管理数据库中，利用Management Studio 创建客户表。步骤：（1）启动 Management Studio。（2）在【对象资源管理器】窗口中，展开【数据库】|【CompanySales】|【表】节点。右击【表】节点，从弹出快捷菜单中，选择【新建表】选项，出现表设计器。,2023/9/18,15,SQL Server 2005数据库应用技术 清华大学出版社,创建数据表-使用Management Studio（续）,（3）在表设计器中，在【列名】单元格输入字段名“CustomerID”，在同一行的【数据类型】单元格设置该字段的数据类型“int”，并在【允许空】列选择是否允许该字段为空值。如果允许，则选中复选框，如果不允许，则取消选中复选框。（4）重复（3）步骤设置“CompanyName”列、“ContactName”列、“Phone”列、“address”列和“EmailAddress”列。（5）单击【文件】|【保存】或工具栏上的按钮，在出现的对话框中输入表的名称“Customer”，新表的相关信息即会出现在对象资源管理器中。,2023/9/18,16,SQL Server 2005数据库应用技术 清华大学出版社,使用T-SQL创建数据表,使用CREATE TABLE语句创建数据库的基本语法格式如下。CREATE TABLE(NULL|NOT NULL IDENTITY(seed,increment)，n),CREATE TABLE 表名(字段1 数据类型 列的特征，字段2 数据类型 列的特征，.),列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。,2023/9/18,17,SQL Server 2005数据库应用技术 清华大学出版社,创建数据表使用CREATE TABLE语句（续）,2、在销售管理数据库中，利用 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 NULL,ProductName varchar(50)NOT NULL,Price decimal(18,2)NULL,ProductStockNumber int NULL,ProductSellNumber int NULL）,2023/9/18,18,SQL Server 2005数据库应用技术 清华大学出版社,销售管理数据库的数据表管理,查看表结构 查看数据表的属性 查看表结构查看表中数据修改数据表 使用Management Studio 使用Alter Table 语句,2023/9/18,19,(1）添加列 任务三：在部门表Department中，增加两列：部门人数列PersonNum，数据类型为整型，允许为空；办公地点列Office，数据类型为Varchar（50）,允许为空。代码如下：ALTER TABLE DepartmentADDpersonNum int NULL,Office Varchar(50)NULL（2）删除列,SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,20,任务四：在部门表Department中，删除两列：部门人数列PersonNum和办公室列Office。代码如下：ALTER TABLE DepartmentDROP COLUMN personNum,Office（3）修改列的定义任务五：在部门表Department中，将部门经理列Manager的数据类型改为Varchar（20）。代码如下：ALTER TABLE DepartmentALTER COLUMN Manager varchar(20),SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,21,（4）修改列名任务六：在部门表Department中，部门经理列Manager重命名为ManagerName。代码如下：sp_rename Department.Manager,ManagerName,COLUMN(5)删除数据表任务七：删除部门表Department。代码如下：USE CompanySalesGODROP TABLE departmentGo,SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,22,(6)重命名数据表 任务八：将商品表product重命名为newProduct，然后删除newProduct表。代码如下：USE CompanySalesGOSp_rename product,newProductGoDROP TABLE newProductGO,SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,23,SQL Server 2005数据库应用技术 清华大学出版社,数据库数据的完整性,实体完整性(Entity Integrity)约束要求表中的每一行数据都反映不同的实体，不能存在相同的数据行。域完整性(Domain Integrity)约束指给定列的输入有效性。参照完整性(Referential Integrity)约束在输入或删除数据行时，参照完整性约束用来保持表之间已定义的关系。用户自定义的完整性(User-defined Integrity)约束用来定义特定的规则。,2023/9/18,24,SQL Server 2005数据库应用技术 清华大学出版社,约束概述,约束定义 约束就是限制，定义约束就是定义可输入表或表的单个列中数据的限制条件。约束分类主关键字约束(Primary Key Constraint)惟一约束(Unique Constraint)检查约束(Check Constraint)默认约束(Default Constraint)外关键字约束(Foreign Key Constraint),实体完整性,域完整性,参照完整性,2023/9/18,25,SQL Server 2005数据库应用技术 清华大学出版社,主键约束,使用Management Studio 创建主键约束（演示设置主键）使用T-SQL语句在创建表同时主键约束在销售管理数据库中，创建部门表。代码如下：CREATE TABLE Department(DepartmentID int NOT NULL Primary KEY,DepartmentName varchar(30)NOT NULL,Manager char(8)NULL,Depart_Description varchar(50)NULL)GO,2023/9/18,26,使用T-SQL语句在一张现有表上添加主键约束在创建供应商表Provider的ProviderID上，添加主键约束。代码如下：ALTER TABLE ProviderADD Constraint PK_Provider PRIMARY KEY(ProviderID),SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,27,SQL Server 2005数据库应用技术 清华大学出版社,外键约束,使用Management Studio 创建外键约束使用Transact-SQL 语句定义外键 语法格式：CREATE TABLE(列名 数据类型 为空性 FOREIGN KEY REFERENCES ref_table(ref_column)）其中，参数说明如下。REFERENCES：参照ref_table：主键表名，要建立关联的被参照表的名称ref_column：主键列名,2023/9/18,28,【例4.21】在销售管理数据库中，创建在第一章设计的销售订单如表 4-13所示。代码如下：USE CompanySalesGOCREATE TABLE Sell_Order(SellOrderID int NOT NULL PRIMARY KEY,ProductID int NULL FOREIGN KEY REFERENCES product(ProductID),SellOrderNumber int NULL,employeeID int NULL FOREIGN KEY REFERENCES Employee(EmployeeID),CustomerID int NULL FOREIGN KEY REFERENCES customer(CustomerID),SellOrderDate smalldatetime NULL,SQL Server 2005数据库应用技术 清华大学出版社,2023/9/18,29,SQL Server 2005数据库应用技术 清华大学出版社,惟一约束,惟一约束与主键约束的区别。惟一性约束指定的列可以有NULL 属性。但主键约束所在的列则不允许；一个表中可以包含多个惟一约束，而主键约束则只能有一个；创建惟一约束创建表的同时创建UNIQUE约束在现有的表中添加惟一约束,2023/9/18,30,【例4.24】在销售管理数据库中的部门表，为部门名称列添加惟一约束，保证部门名称的不重复。创建后使用Transact-SQL 语句删除此约束。代码如下：USE CompanySalesGO ALTER TABLE departmentADD CONSTRAINT un_departName UNIQUE(departmentName)GOALTER TABLE departmentDROP CONSTRAINT un_departNameGO,2023/9/18,31,建立表间的关系,建立表间的关系实际上就是实施参照完整性约束，建立主表和子表关系。（1）在SQL Management Studio中，在设计表的时候，在Sell_Order表中的右键菜单选择“关系”，单击“添加”。（2）单击“表和列规范”最右侧的小按钮，将显示要建立关系的主键表以及主键列对话框。（3）选择主表Product，主键字段为ProductID，对应的外键表Sell_Order的字段也为ProductID。单击“确定”，主-外键关系就建立了。,2023/9/18,32,SQL Server 2005数据库应用技术 清华大学出版社,检查约束,检查约束（CHECK约束）定义 实际上是验证字段输入内容的规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以对每个列设置CHECK约束。创建检查约束使用Management studio 使用Transact-SQL 语句 语法结构：CONSTRAINT 约束名 CHECK(logical_expression),n,2023/9/18,33,【练习】在销售管理数据库中的商品表中，为了保证数据的质量，确保商品的价格为大于0的数，库存量和已销售量数据为非负数。ALTER TABLE productADD CONSTRAINT CK_PTN CHECK(price0)and(ProductStockNumber=0)and(ProductSellNumber=0),2023/9/18,34,SQL Server 2005数据库应用技术 清华大学出版社,默认值约束,默认值约束（DEFAULT）用于确保域完整性，它提供了一种为数据表中任何一列提供默认值的手段。创建默认值约束：1、使用Management studio 创建默认值 例4.28 在销售管理数据库中的员工表中，将“性别”列设定默认值“男”。2、使用Transact-SQL 语句创建默认值定义语法：CONSTRAINT 约束名 DEFAULT constant_expression FOR 列名,2023/9/18,35,【例】在销售管理数据库中的员工表中，新员工如果不到特定部门工作的话，新员工全部到“销售部”工作。代码如下：USE CompanySalesGOALTER TABLE employeeADD CONSTRAINT def_DepartID DEFAULT(1)FOR DepartmentID,2023/9/18,36,默认值和规则,默认值提问：默认值与默认值约束的区别是？,相同点：当用户向数据表中插入数据行时，如果没有为某列输入值，则由SQL Server自动为该列赋予默认值。不同点：默认值是一种数据库对象。在数据库中创建默认值对象后，可以将其绑定到多个数据表的一个或多个列应用；默认值约束只能用于约束一个 表中的列。,2023/9/18,37,创建规则,规则就是对存储在表中列或用户自定义数据类型的取值范围的规定或限制。规则与CHECK约束很相似，但CHECK约束不能直接作用于用户自定义数据类型。【例4.33】创建一个日期规则RL_Date，即一个员工的雇佣日期在1980-1-1和系统的当前日期之间。代码如下：USE CompanySalesGOCREATE RULE RL_DATE AS date=1980-1-1 and date=getdate()GO,2023/9/18,38,绑定规则,绑定规则的语法格式：sp_bindrule 规则名称，object_name【例4.34】将规则RL_Date绑定到员工表的雇佣日期列上。代码如下：USE CompanySalesGOexec sp_bindrule RL_DATE,employee.hireDate GO,2023/9/18,39,删除规则,【例4.35】删除RL_Date规则。代码如下：（1）解除绑定 USE CompanySalesGOexec sp_unbindrule employee.hireDateGO（2）删除规则USE CompanySalesGODROP RULE RL_DateGO,2023/9/18,40,上机任务,上一阶段已经建立了BBS数据库，本阶段将在上一阶段的基础上建立数据库的表结构。,用户表BBSUsers,2023/9/18,41,上机任务（续）,主贴表的结构,2023/9/18,42,跟帖表的结构,2023/9/18,43,版块表的结构,