数据表的创建与管理幻灯片.ppt

数据表的数据表的创建与管理建与管理1/7/2023主讲人：李翠琳主讲人：李翠琳第1页，共43页，编辑于2022年，星期六 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社技能目标技能目标学会创建数据表；学会创建数据表；学会创建和修改销售管理数据库中的数据表；学会创建和修改销售管理数据库中的数据表；学会使用约束来保证数据的完整性。学会使用约束来保证数据的完整性。第2页，共43页，编辑于2022年，星期六2023/1/72023/1/72 2本章新单词本章新单词实体完整性实体完整性 Entity Integrity域完整性域完整性 Domain Integrity 参照完整性参照完整性 Referential Integrity用户定义的完整性用户定义的完整性 User-defined Integrity第3页，共43页，编辑于2022年，星期六2023/1/72023/1/73 3 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社表相关的基本概念表相关的基本概念 表是组织和管理数据的基本单位。表是组织和管理数据的基本单位。表由行和列组成的二维结构。表由行和列组成的二维结构。表中的一行称为一条记录，表中的一列称为一个字表中的一行称为一条记录，表中的一列称为一个字段。段。说明：每个SQLServer数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。第4页，共43页，编辑于2022年，星期六2023/1/72023/1/74 4 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型 数字数据数字数据字符数据字符数据货币数据货币数据日期和时间数据日期和时间数据二进制字符串二进制字符串其他数据类型其他数据类型 第5页，共43页，编辑于2022年，星期六2023/1/72023/1/75 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的整型数float 浮点数数据，从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到p第6页，共43页，编辑于2022年，星期六2023/1/72023/1/76 6 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-货币型货币型 数据类型范 围money-922,337,203,685,477.5808 到 922,337,203,685,477.5807smallmoney-214,748.3648 到 214,748.3647第7页，共43页，编辑于2022年，星期六2023/1/72023/1/77 7 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-日期型日期型 数据类型范 围精确度datetime1753年1月 1日到 9999年12月313.33毫秒smalldatetime1900年1月1日到 2079年6月6日1分钟第8页，共43页，编辑于2022年，星期六2023/1/72023/1/78 8 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-字符型字符型 数据类型说 明char (n)固定长度的字符数据，长度为n个字节，n 的取值范围为 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页，共43页，编辑于2022年，星期六2023/1/72023/1/79 9 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-字节二进制和图像型字节二进制和图像型 数据类型说 明binary(n)长度为 n 字节的固定长度二进制数据，其中 n 是从1到8000的值varbinary(n)可变长度二进制数据。n 可以取从1到8,000的值Image变长度二进制数据 最长为2147 483 647字节第10页，共43页，编辑于2022年，星期六2023/1/72023/1/71010 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据类型数据类型-其他数据类型其他数据类型 数据类型说 明UniqueIdentifier唯一标识数字存储为 16 字节的二进制值TimeStamp当插入或者修改行时，自动生成的惟一的二进制数字的数据类型Cursor允许在存储过程中创建游标变量，游标允许一次一行地处理数据，这个数据类型不能用作表中的列数据类sql_variant可包含除text、ntex、timage 和 timestamp 之外的其他任何数据类型Table一种特殊的数据类型，用于存储结果集以进行后续处理 XML存储 XML 数据的数据类型。可以在列中或者 xml 类型的变量中存储 xml 实例第11页，共43页，编辑于2022年，星期六2023/1/72023/1/71111 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社列的属性列的属性 列的为空性列的为空性 没有输入的值，未知或未定义。列值可以接受空值没有输入的值，未知或未定义。列值可以接受空值NULL，也可，也可以拒绝空值以拒绝空值NOT NULL。IDENTITY属性属性 可以使表的列包含系统自动生成的数字，可以惟一地标识可以使表的列包含系统自动生成的数字，可以惟一地标识表的每一行，即表中的每行数据列上的数字均不相同。表的每一行，即表中的每行数据列上的数字均不相同。IDENTITY属性的表达格式如下。属性的表达格式如下。IDENTITY(s,i)其中，其中，s(seed)表示起始值，表示起始值，i(increment)表示增量值，表示增量值，其默认值为其默认值为1。第12页，共43页，编辑于2022年，星期六2023/1/72023/1/71212 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建销售管理数据库的数据表创建销售管理数据库的数据表 使用使用Management Studio 任务一：在销售管理数据库中，利用任务一：在销售管理数据库中，利用Management Studio 创创建客户表。建客户表。使用使用T-SQL创建数据表创建数据表任务二：在销售管理数据库中，利用任务二：在销售管理数据库中，利用 Create Table语句，创语句，创建部门表和商品表。建部门表和商品表。第13页，共43页，编辑于2022年，星期六2023/1/72023/1/71313 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建数据表创建数据表-使用使用Management Studio 1、在销售管理数据库中，利用、在销售管理数据库中，利用Management Studio 创建客户表。创建客户表。步骤：步骤：（1）启动ManagementStudio。（2）在【对象资源管理器】窗口中，展开【数据库】|【CompanySales】|【表】节点。右击【表】节点，从弹出快捷菜单中，选择【新建表】选项，出现表设计器。第14页，共43页，编辑于2022年，星期六2023/1/72023/1/71414 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建数据表创建数据表-使用使用Management Studio（续）（续）（3）在表设计器中，在【列名】单元格输入字段名“CustomerID”，在同一行的【数据类型】单元格设置该字段的数据类型“int”，并在【允许空】列选择是否允许该字段为空值。如果允许，则选中复选框，如果不允许，则取消选中复选框。（4）重复（3）步骤设置“CompanyName”列、“ContactName”列、“Phone”列、“address”列和“EmailAddress”列。（5）单击【文件】|【保存】或工具栏上的按钮，在出现的对话框中输入表的名称“Customer”，新表的相关信息即会出现在对象资源管理器中。第15页，共43页，编辑于2022年，星期六2023/1/72023/1/71515 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社使用使用T-SQL创建数据表创建数据表使用使用CREATE TABLE语句创建数据库的基本语法格式如下。语句创建数据库的基本语法格式如下。CREATETABLE(NULL|NOTNULLIDENTITY(seed,increment)，n)CREATE TABLE 表名表名(字段字段1 数据类型数据类型 列的特征，列的特征，字段字段2 数据类型数据类型 列的特征，列的特征，.)列的特征：列的特征：包括该列是是否为空（包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、）、是否是标识列（自动编号）、是否有默认值、是否为主键等。是否为主键等。第16页，共43页，编辑于2022年，星期六2023/1/72023/1/71616 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）第17页，共43页，编辑于2022年，星期六2023/1/72023/1/71717 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社销售管理数据库的数据表管理销售管理数据库的数据表管理 查看表结构查看表结构 查看数据表的属性 查看表结构查看表中数据修改数据表修改数据表 使用Management Studio 使用Alter Table 语句第18页，共43页，编辑于2022年，星期六2023/1/72023/1/71818(1）添加列 任务三：在部门表Department中，增加两列：部门人数列PersonNum，数据类型为整型，允许为空；办公地点列Office，数据类型为Varchar（50）,允许为空。代码如下：ALTER TABLE DepartmentADDpersonNum int NULL,Office Varchar(50)NULL（2）删除列 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第19页，共43页，编辑于2022年，星期六2023/1/72023/1/71919任务四：任务四：在部门表在部门表Department中，删除两列：部门人数列中，删除两列：部门人数列PersonNum和办和办公室列公室列Office。代码如下：代码如下：ALTER TABLE DepartmentDROP COLUMN personNum,Office（3）修改列的定义）修改列的定义任务五任务五：在部门表：在部门表Department中，将部门经理列中，将部门经理列Manager的数据类型改为的数据类型改为Varchar（20）。）。代码如下：代码如下：ALTER TABLE DepartmentALTER COLUMN Manager varchar(20)SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第20页，共43页，编辑于2022年，星期六2023/1/72023/1/72020（4）修改列名修改列名任务六任务六：在部门表：在部门表Department中，部门经理列中，部门经理列Manager重命名为重命名为ManagerName。代码如下：代码如下：sp_rename Department.Manager,ManagerName,COLUMN(5)删除数据表删除数据表任务七任务七：删除部门表：删除部门表Department。代码如下：代码如下：USE CompanySalesGODROP TABLE departmentGo SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第21页，共43页，编辑于2022年，星期六2023/1/72023/1/72121(6)重命名数据表重命名数据表 任务八任务八：将商品表：将商品表product重命名为重命名为newProduct，然后删除，然后删除newProduct表。表。代码如下：代码如下：USE CompanySalesGOSp_rename product,newProductGoDROP TABLE newProductGO SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第22页，共43页，编辑于2022年，星期六2023/1/72023/1/72222 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社数据库数据的完整性数据库数据的完整性 实体完整性实体完整性(Entity Integrity)约束约束要求表中的每一行数据都反映不同的实体，不能存在相同的数据行。要求表中的每一行数据都反映不同的实体，不能存在相同的数据行。域完整性域完整性(Domain Integrity)约束约束指给定列的输入有效性。指给定列的输入有效性。参照完整性参照完整性(Referential Integrity)约束约束在输入或删除数据行时，参照完整性约束用来保持表之间已定义的在输入或删除数据行时，参照完整性约束用来保持表之间已定义的关系。关系。用户自定义的完整性用户自定义的完整性(User-defined Integrity)约束约束用来定义特定的规则。用来定义特定的规则。第23页，共43页，编辑于2022年，星期六2023/1/72023/1/72323 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社约束概述约束概述 约束定义约束定义 约束就是限制，定义约束就是定义可输入表或表的单个列中数据的约束就是限制，定义约束就是定义可输入表或表的单个列中数据的限制条件。限制条件。约束分类约束分类主关键字约束(PrimaryKeyConstraint)惟一约束(UniqueConstraint)检查约束(CheckConstraint)默认约束(DefaultConstraint)外关键字约束(ForeignKeyConstraint)实体完整性域完整性参照完整性第24页，共43页，编辑于2022年，星期六2023/1/72023/1/72424 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第25页，共43页，编辑于2022年，星期六2023/1/72023/1/72525使用使用T-SQL语句在一张现有表上添加主键约束语句在一张现有表上添加主键约束在创建供应商表在创建供应商表Provider的的ProviderID上，添加主键约束。上，添加主键约束。代码如下：代码如下：ALTER TABLE ProviderADD Constraint PK_Provider PRIMARY KEY(ProviderID)SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第26页，共43页，编辑于2022年，星期六2023/1/72023/1/72626 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社外键约束外键约束 使用使用Management Studio 创建外键约束创建外键约束使用使用Transact-SQL 语句定义外键语句定义外键 语法格式：语法格式：CREATE TABLE (列名列名 数据类型数据类型 为空性为空性 FOREIGN KEY REFERENCES ref_table(ref_column)）其中，参数说明如下。其中，参数说明如下。REFERENCES：参照ref_table：主键表名，要建立关联的被参照表的名称ref_column：主键列名第27页，共43页，编辑于2022年，星期六2023/1/72023/1/72727【例例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数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第28页，共43页，编辑于2022年，星期六2023/1/72023/1/72828 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社惟一约束惟一约束 惟一约束与主键约束的区别惟一约束与主键约束的区别。惟一性约束指定的列可以有NULL属性。但主键约束所在的列则不允许；一个表中可以包含多个惟一约束，而主键约束则只能有一个；创建惟一约束创建惟一约束创建表的同时创建UNIQUE约束在现有的表中添加惟一约束第29页，共43页，编辑于2022年，星期六2023/1/72023/1/72929【例4.24】在销售管理数据库中的部门表，为部门名称列添加惟一约束，保证部门名称的不重复。创建后使用Transact-SQL语句删除此约束。代码如下：USECompanySalesGOALTERTABLEdepartmentADDCONSTRAINTun_departNameUNIQUE(departmentName)GOALTERTABLEdepartmentDROPCONSTRAINTun_departNameGO第30页，共43页，编辑于2022年，星期六2023/1/72023/1/73030建立表间的关系建立表间的关系建立表间的关系实际上就是实施参照完整性约束，建立表间的关系实际上就是实施参照完整性约束，建立主表和子表关系。建立主表和子表关系。（1）在）在SQL Management Studio中，在设计表的时候，在中，在设计表的时候，在Sell_Order表中的右键菜单选择表中的右键菜单选择“关系关系”，单击，单击“添加添加”。（2）单击）单击“表和列规范表和列规范”最右侧的小按钮，将显示要建立关系的主最右侧的小按钮，将显示要建立关系的主键表以及主键列对话框。键表以及主键列对话框。（3）选择主表）选择主表Product，主键字段为，主键字段为ProductID，对应的外键表，对应的外键表Sell_Order的字段也为的字段也为ProductID。单击。单击“确定确定”，主，主-外外键关系就建立了键关系就建立了。第31页，共43页，编辑于2022年，星期六2023/1/72023/1/73131 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社检查约束检查约束检查约束（检查约束（CHECK约束）定义约束）定义实际上是验证字段输入内容的规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以对每个列设置CHECK约束。创建检查约束创建检查约束使用Managementstudio使用Transact-SQL语句语法结构：CONSTRAINT约束名CHECK(logical_expression),n第32页，共43页，编辑于2022年，星期六2023/1/72023/1/73232【练习练习】在销售管理数据库中的商品表中，为了保在销售管理数据库中的商品表中，为了保证数据的质量，确保商品的价格为大于证数据的质量，确保商品的价格为大于0的数，库的数，库存量和已销售量数据为非负数。存量和已销售量数据为非负数。ALTER TABLE productADD CONSTRAINT CK_PTN CHECK(price0)and(ProductStockNumber=0)and (ProductSellNumber=0)第33页，共43页，编辑于2022年，星期六2023/1/72023/1/73333 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社默认值约束默认值约束 默认值约束（默认值约束（DEFAULT）用于确保域完整性，它提供了一种为数）用于确保域完整性，它提供了一种为数据表中任何一列提供默认值的手段。据表中任何一列提供默认值的手段。创建默认值约束：创建默认值约束：1、使用、使用Management studio 创建默认值创建默认值 例例4.28 在销售管理数据库中的员工表中，将在销售管理数据库中的员工表中，将“性别性别”列设定默列设定默认值认值“男男”。2、使用、使用Transact-SQL 语句创建默认值定义语句创建默认值定义语法：语法：CONSTRAINT约束名DEFAULTconstant_expressionFOR列名第34页，共43页，编辑于2022年，星期六2023/1/72023/1/73434【例例】在销售管理数据库中的员工表中，新员工如果不到特在销售管理数据库中的员工表中，新员工如果不到特定部门工作的话，新员工全部到定部门工作的话，新员工全部到“销售部销售部”工作。工作。代码如下：代码如下：USE CompanySalesGOALTER TABLE employeeADD CONSTRAINT def_DepartID DEFAULT(1)FOR DepartmentID第35页，共43页，编辑于2022年，星期六2023/1/72023/1/73535默认值和规则默认值和规则默认值默认值提问：提问：默认值与默认值约束的区别是？默认值与默认值约束的区别是？相同点：当用户向数据表中插入数据行时，如果没有为某列输入值，则由SQLServer自动为该列赋予默认值。不同点：默认值是一种数据库对象。在数据库中创建默认值对象后，可以将其绑定到多个数据表的一个或多个列应用；默认值约束只能用于约束一个表中的列。第36页，共43页，编辑于2022年，星期六2023/1/72023/1/73636创建规则创建规则规则规则就是对存储在表中列或用户自定义数据类型的取值范围的规定或限制。规则与就是对存储在表中列或用户自定义数据类型的取值范围的规定或限制。规则与CHECK约束很相似，但约束很相似，但CHECK约束不能直接作用于用户自定义数据类型。约束不能直接作用于用户自定义数据类型。【例例4.33】创建创建一个日期一个日期规则规则RL_Date，即一个员工的雇佣，即一个员工的雇佣日期在日期在1980-1-1和系统的当前日期之间。和系统的当前日期之间。代码如下：代码如下：USE CompanySalesGOCREATE RULE RL_DATE AS date=1980-1-1 and date=getdate()GO第37页，共43页，编辑于2022年，星期六2023/1/72023/1/73737绑定规则绑定规则绑定规则绑定规则的语法格式：的语法格式：sp_bindrule 规则名称规则名称，object_name【例例4.34】将规则将规则RL_Date绑定到员工表的雇绑定到员工表的雇佣日期列上。佣日期列上。代码如下：代码如下：USE CompanySalesGOexec sp_bindrule RL_DATE,employee.hireDate GO第38页，共43页，编辑于2022年，星期六2023/1/72023/1/73838删除规则删除规则【例例4.35】删除删除RL_Date规则。规则。代码如下：代码如下：（1）解除绑定）解除绑定 USE CompanySalesGOexec sp_unbindrule employee.hireDateGO（2）删除规则）删除规则USE CompanySalesGODROP RULE RL_DateGO第39页，共43页，编辑于2022年，星期六2023/1/72023/1/73939上机任务上机任务上一阶段已经建立了上一阶段已经建立了BBS数据库，本阶段将在上一阶段的数据库，本阶段将在上一阶段的基础上建立数据库的表结构。基础上建立数据库的表结构。列名数据类型长度主键是否允许为空默认值描述UIDint不允许标识列，自动增长UNamevarchar32不允许用户名UPasswordvarchar16不允许888888密码Upointint4允许20用户积分UEmailvarchar32允许PP.com电子邮件地址用户表BBSUsers第40页，共43页，编辑于2022年，星期六2023/1/72023/1/74040上机任务（续）上机任务（续）列名数据类型长度主键是否允许为空默认值描述TIDint不允许标识列，自动增长TNumbervarchar32不允许帖子编号TUIDint不允许发帖人IDTSIDint不允许版块IDTTopicvarchar255不允许标题信息TContentsnText不允许帖子正文TTtimedatetime允许发帖时间主贴表的结构第41页，共43页，编辑于2022年，星期六2023/1/72023/1/74141跟帖表的结构列名数据类型长度主键是否允许为空默认值描述RIDint不允许标识列，自动增长RNumbervarchar32不允许帖子编号RTIDint不允许主贴IDRSIDint不允许版块IDRUIDint不允许回帖人IDRTopicvarchar255不允许标题信息RContentsnText不允许跟帖的正文第42页，共43页，编辑于2022年，星期六2023/1/72023/1/74242版块表的结构列名数据类型长度主键是否允许为空默认值描述SIDint不允许标识列，自动增长SNamevarchar32不允许版块名称SMasterIDint不允许版主用户IDSClickCountint允许点击率STopicCountint允许发帖数第43页，共43页，编辑于2022年，星期六2023/1/72023/1/74343