第5章 SQLSERVER 数据表课件.ppt

SQLSERVER 数据表本章的学习目标： 掌握创建数据库的技能 熟悉数据表的基本特点和类型 掌握用SQL Server Management Studio和Transact-SQL语言创建数据表的技能 掌握用SQL Server Management Studio和Transact-SQL语言修改数据表结构的技能 掌握用SQL Server Management Studio和Transact-SQL语言操作数据表中数据的相关技能 理解什么是数据的完整性 掌握约束的创建、查看及删除相关技能5.1 数据表创建数据表创建 在Microsoft SQL Server 2008中，创建数据表有两种方式：一是用该数据库管理系统自带的SQL Server Management Studio可视化地创建数据表，二是使用Transact-SQL语言直接创建数据表。5.1 数据表创建数据表创建 5.1.1 使用使用SQL Server Management Studio创创建数据表建数据表 5.1.2 使用使用Transact-SQL语言创建数据表语言创建数据表5.1.1 使用使用SQL Server Management Studio创建创建数据表数据表1 打开Microsoft SQL Server 20082 创建数据库3创建数据表表结构设置主键5.1.2 使用使用Transact-SQL语言语言创建数据表创建数据表 使用Transact-SQL语言创建数据表时，主要是用到CREATE TABLE语句。CREATE TABLE 数据表名字（字段名称1 (对字段的定义部分)字段名称2 (对字段的定义部分) .字段名称n (对字段的定义部分) ）；1客房类型表的创建CREATE TABLE 客房类型( 类型编号 char(4) CONSTRAINT pk_lxbh PRIMARY KEY NOT NULL , 名称 varchar(10) UNIQUE NOT NULL , 面积 float , 床位 int , 价格 smallmoney NOT NULL, 空调 bit , 电视 bit , 卫生间 bit) 2客房信息表的创建CREATE TABLE 客房信息( 客房号 char(6) CONSTRAINT pk_kfbh PRIMARY KEY NOT NULL , 类型编号 char(4) CONSTRAINT fk_kfxxlxdm REFERENCES 客房类型(类型编号) NOT NULL , 位置 varchar(20) , 描述 varchar(50) ) 3客户信息表的创建CREATE TABLE 客户信息 ( 身份证号 varchar(18) CONSTRAINT pk_sfzh PRIMARY KEY NOT NULL , 姓名 varchar(12) NOT NULL , 电话 varchar(15) NOT NULL ) 4客房业务表的创建CREATE TABLE 客房业务 ( 客房号 char(6) CONSTRAINT fk_kfhyw REFERENCES 客房信息(客房号) NOT NULL , 入住时间 datetime NOT NULL , 身份证号 varchar(18) CONSTRAINT fk_sfzhyw REFERENCES 客户信息(身份证号) NOT NULL,客户姓名 varchar(12) NOT NULL ,客户电话 varchar(15) NOT NULL ,备注 nVarchar(50) NULL ) 5.2 修改表结构 在Microsoft SQL Server 2008中，修改表结构有两种方式：一是用该数据库管理系统自带的SQL Server Management Studio可视化地修改表结构，二是使用Transact-SQL语言直接修改表结构。5.2 修改表结构 5.2.1 使用SQL Server Management Studio修改表结构 5.2.2 使用Transact-SQL语言修改表结构5.2.1 使用SQL Server Management Studio修改表结构1打开表设计界面打开表设计界面2 修改表结构修改表结构5.2.2 使用使用Transact-SQL语言语言修改表结构修改表结构1 使用使用ALTER语句添加字段语句添加字段 对数据表进行字段添加的语法如下：ALTER TABLE 数据表名 ADD 字段名 字段类型 是否为空 【例5-7】向客房类型表中添加一个字段【备注】，类型为nchar，长度为10。USE hotelGOALTER TABLE 客房类型 ADD 备注 nchar(10) NULLGO执行后的查询结果2 使用使用ALTER语句修改字段类型语句修改字段类型 对数据表进行字段类型修改的语法如下：ALTER TABLE table_name ALTER COLUMN column_name new_type_name 【例5-8】客房类型的【备注】属性列的字段类型为nchar10，现在把它修改为ntext类型。具体的Transact-SQL语句如下所示：USE hotelGOALTER TABLE 客房类型 ALTER COLUMN 备注 ntext GO执行后的结果3 使用使用ALTER语句删除字段语句删除字段 对数据表进行字段删除的语法如下：ALTER TABLE 数据表名 DROP COLUMN 字段名 【例5-9】对客房类型表删除刚才添加的字段【备注】。具体的Transact-SQL语句如下所示：USE hotelGOALTER TABLE 客房类型 DROP COLUMN 备注 GO执行后的结果4删除数据表操作介绍删除数据表操作介绍 删除表就是将表中数据和表的结构从数据库中永久性地去除。表被删除之后，就不能再恢复该表的定义。删除表可以使用DROP TABLE语句来完成，该语句的语法形式如下：DROP TABLE table_name 可以使用DROP TABLE语句一次性地删除多个表，表之间使用逗号分开。5.3 表数据的操作 所谓表数据的操作，就是对存储在数据表中的数据的相关操作。 在Microsoft SQL Server 2008中，对表数据操作有两种方式：一是用该数据库管理系统自带的SQL Server Management Studio可视化地对数据表操作，二是使用Transact-SQL语言直接操作表数据。5.3 表数据的操作 5.3.1 使用使用SQL Server Management Studio 插插入、更新和删除表数据入、更新和删除表数据 5.3.2 使用Transact-SQL语言插入、更新和删除表数据5.3.1 使用使用SQL Server Management Studio 插插入、更新和删除表数据入、更新和删除表数据1 打开表数据编辑界面打开表数据编辑界面2 对表数据进行编辑对表数据进行编辑5.3.2 使用使用Transact-SQL语言插入语言插入、更新和删除表数据、更新和删除表数据 虽然使用SQL Server Management Studio可以很方便地进行数据的插入、更新和删除等操作，但是对于编程人员来说，使用最多的还是通过Transact-SQL语言来完成这些操作。从另一方面来说，当数据量很庞大后，使用SQL Server Management Studio对数据进行操作也不方便，更多时候需要使用Transact-SQL语言对数据进行批量操作。本小节讲详细介绍如何使用Transact-SQL语言进行数据的插入、更新和删除操作（以节创建的客房类型数据表为操作对象）。1插入数据插入数据 一般地，使用INSERT语句一次只能插入一行数据。INSERT语句的基本语法形式如下：INSERT INTO table_or_view_name (column_list) VALUES (expression) 【例5-11】在客房类型表中插入一行数据，类型编号为“P004”,名称为“普通三人间”，面积为“45”，床位为“3”，价格为“550”，空调为“1”，电视为“1”，卫生间为“0”。具体的Transact-SQL语句如下所示：INSERT INTO hotel.dbo.客房类型 (类型编号,名称 ,面积 ,床位 ,价格 ,空调 ,电视 ,卫生间) VALUES (P004 ,普通三人间 ,45 ,3 ,550 ,1 ,1 ,0)GO查看新添加的数据2更新数据更新数据 可以使用UPDATE语句更新表中已经存在的数据。UPDATE语句既可以一次更新一行数据，也可以一次更新许多行，甚至可以一次更新表中的全部数据行。UPDATE语句的基本语法形式如下：UPDATE table_or_view_name SET column_name = expression, WHERE search_condition （1）不带不带where子句的子句的UPDATE语句语句 【例5-13】由于物价上涨，现在对酒店所有类型房间的价格统一上调50元。具体的Transact-SQL语句如下所示：USE hotelGOUPDATE 客房类型 SET 价格 = 价格+50GO执行结果（2）带带where子句的子句的UPDATE语句语句 【例5-14】由于物价上涨，现在对酒店的豪华商务房的价格统一上调50元。具体的Transact-SQL语句如下所示：USE hotelGOUPDATE 客房类型 SET 价格 = 价格+50 WHERE 名称=豪华商务房GO执行结果3删除数据删除数据 当表中的数据不再需要时，可以将其删除。一般情况下，使用DELETE语句删除数据。DELETE语句可以从一个表中删除一行或多行数据。 删除数据的DELEE语句的基本语法形式如下：DELETE FROM table_or_name WHERE search_condition （1）带带where子句的子句的DELETE语句语句 【例5-15】删除客房类型数据表中【面积】=30平方米的房间类型数据。具体的Transact-SQL语句如下所示：USE hotelGODELETE FROM 客房类型 WHERE 面积=30GO删除失败，因为有外键关系应先删除外键数据执行成功查看结果（2）不带不带where子句的子句的DELETE语句语句5.4 数据表的约束数据表的约束 数据完整性就是指存储在数据库中的数据的一致性和准确性。在评价数据库的设计时，数据完整性的设计是数据库设计好坏的一项重要指标。在Microsoft SQL Server 2008系统中，有3种数据完整性类型，即域完整性、实体完整性和引用完整性。5.4 数据表的约束数据表的约束 5.4.1 约束的概念和类型约束的概念和类型 5.4.2 约束的管理约束的管理5.4.1 约束的概念和类型约束的概念和类型完整性类型约 束 类 型描 述域完整性DEFAULT在使用INSERT语句插入数据时，如果某个列的值没有明确提供，则将定义的缺省值插入到该列中CHECK指定某一个列中的可保存值的范围实体完整性主键每一行的唯一标识符，确保用户不能输入冗余值和确保创建索引，提高性能，不允许空值UNIQUE防止出现冗余值，并且确保创建索引，提高性能，允许空值引用完整性外键定义一列或者几列，其值与本表或者另外一个表的主键值匹配约束使用注意事项（1）不必删除表，就可以直接创建、修改和删除约束的定义。（2）应该在应用程序中增加错误检查机制，测试数据是否与约束相冲突。（3）当在表上增加约束时，SQL Server系统将检查表中的数据是否与约束冲突。5.4.2 约束的管理约束的管理 本节详细研究各种DEFAULT、CHECK、主键、UNIQUE、外键等约束的特点、创建方法、修改等内容。1 DEFAULT约束约束 DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新纪录。在创建表时和创建表后，都可以对表中的列使用DEFAULT 约束。创建表时添加DEFAULT约束【例5-18】对于客房类型数据表，创建的时候可以把【电视】字段默认为“1”，这样插入数据的时候就可以不用输入这个属性列的值。Transact-SQL代码如下所示：CREATE TABLE 客房类型( 类型编号 char(4) CONSTRAINT pk_lxbh PRIMARY KEY NOT NULL , 名称 varchar(10) UNIQUE NOT NULL , 面积 float , 床位 int , 价格 smallmoney NOT NULL, 空调 bit , 电视 bit DEFAULT 1, 卫生间 bit ) 创建表后添加DEFAULT约束 对于已经创建完成的数据表，也可以对某些属性列添加默认值。具体Transact-SQL代码如下所示：ALTER TABLE 客房类型ADD CONSTRAINT col_b_defDEFAULT 1 FOR 电视 ;2 CHECK约束约束 CHECK约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。 CHECK约束的两种基本语法形式如下：CONSTRAINT constraint_name CHECK (logical_expression)CHECK (logical_expression)创建表时添加CHECK约束【例5-19】在数据表房间类型的【名称】列中可以创建一个CHECK约束，指定其取值范围是“单人间”、“双人间”、“三人间”。这样，当向【名称】列输入数据时，要么输入数据这三个数据之一，而不能输入其他数据。具体Transact-SQL代码如下所示： CREATE TABLE 客房类型( 类型编号 char(4) CONSTRAINT pk_lxbh1 PRIMARY KEY NOT NULL , 名称 varchar(10) UNIQUE NOT NULL CHECK(名称=单人间 or 名称=双人间 or 名称=三人间), 面积 float , 床位 int , 价格 smallmoney NOT NULL, 空调 bit , 电视 bit, 卫生间 bit)创建表后添加CHECK约束 对于已经创建完成的数据表，也可以对某些属性列添加CHECK约束。具体Transact-SQL代码如下所示：ALTER TABLE 客房类型ADD CONSTRAINT col_b_checkCHECK(名称=单人间 or 名称=双人间 or 名称=三人间),3 主键约束主键约束 主键约束在表中定义一个主键值，这是唯一确定表中每一行数据的标识符。在所有的约束类型中，主键约束是最重要的一种约束类型，也是使用最广泛的约束类型。 定义主键约束的4种基本语法形式如下所示：CONSTRAINT constraint_name PRIMARY KEYPRIMARY KEYCONSTRAINT constraint_name PRIMARY KEY (column_list)PRIMARY KEY (column_list)创建表后添加主键约束 【例5-21】一般在创建数据表的时候就会创建数据表的主键（参考本章小节客房类型表的创建过程）。如果在创建数据表的时候没有添加主键，则可以在后期使用Alter 语句对数据表进行主键设置，具体Transact-SQL代码如下所示：ALTER TABLE 客房类型ADD CONSTRAINT pk_TypeID PRIMARY KEY (类型编号)使用主键约束的注意事项 （1）每一个表最多只能定义一个主键约束。 （2）主键列所输入的值必须是唯一的。如果主键约束由两个或两个以上的列组成，那么这些列的组合必须是唯一的。 （3）主键列不允许空值。 （4）主键约束在指定的列上创建了一个唯一性索引。该唯一性索引既可以是聚集索引，也可以是非聚集索引。在默认情况下创建的是聚集索引。如果表中已经有聚集索引，那么在创建主键约束之前，要么把已有的聚集索引删除，要么指定所创建的索引是非聚集索引。 （5）可以在定义主键约束时添加级联操作选项。4 UNIQUE约束约束 UNIQUE约束指定表中某一个列或多个列不能有相同的两行或两行以上的数据存在。这种约束通过实现唯一性索引来强制实体完整性。 UNIQUE约束的4种基本语法形式如下所示：CONSTRAINT constraint_name UNIQUEUNIQUECONSTRAINT constraint_name UNIQUE (column_list)UNIQUE (column_list)使用UNIQUE约束的注意事项（1）UNIQUE约束所在的列允许空值，但是主键约束所在的列不允许空值。（2）一个表中可以有多个UNIQUE约束。（3）可以把UNIQUE约束放在一个或者多个列上，这些列或列的组合必须有唯一的值。但是，UNIQUE约束所在的列并不是表的主键列。（4）UNIQUE约束强制在指定的列上创建一个唯一性索引。在默认情况下，是创建唯一性的非聚集索引。但是，在定义UNIQUE约束时也可以指定所创建的索引是聚集索引。5 外键约束外键约束 外键约束强制引用完整性, 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。外键约束定义一个或多个列，这些列可以引用同一个表或另外一个表中的主键约束列或UNIQUE约束列。创建表时添加外键约束 【例5-22】在创建客房信息数据表的时候，把客房类型数据表中的【类型编号】作为其外键，代码如下： CREATE TABLE 客房信息( 客房号 char(6) CONSTRAINT pk_kfbh PRIMARY KEY NOT NULL , 类型编号 char(4) CONSTRAINT fk_kfxxlxdm REFERENCES 客房类型(类型编号) NOT NULL , 位置 varchar(20) , 描述 varchar(50) ) 创建表后添加外键约束 也可以在创建数据表后，在添加相应外键，具体Transact-SQL代码如下： ALTER TABLE 客房信息ADD CONSTRAINT pk_kfbhFOREIGN KEY (类型编号)REFERENCES 客房类型(类型编号)使用外键约束的注意事项（1）外键约束提供了单列引用完整性和多列引用完整性。在FOREIGN KEY子句中的列的数量和数据类型必须和REFERENCES子句中的列的数量和数据类型匹配。（2）不像主键约束或唯一性约束，外键约束不能自动创建索引。然而，如果在数据库中经常使用连接查询，那么，为了加快连接查询的速度，提高连接查询的性能，用户应该在外键约束列上手工创建索引。（3）当用户修改外键约束所在表中的数据时，该用户必须拥有外键约束所参考表的SELECT权限或REFERENCES权限。（4）在定义外键约束时，如果引用同一个表中的列时，只能使用REFERENCES子句，不能使用FOREIGN KEY子句。例如，在如图11-13的示例中创建了两个约束。一个约束是主键约束，用来确保雇员代号的唯一性；另外一个约束是外键约束，用来确保经理代号是一个有效的雇员代号。注意，在这个外键约束中，引用了同一个表的主键列。本章小结 本章主要讲述数据表和数据表中的数据创建、修改等操作。对于这些操作，既可以通过可视化的SQL Server Management Studio来完成，也可以通过执行Transact-SQL语句来实现。首先，通过实例介绍了数据表的创建过程，包括创建数据表时候外键关系的添加、删除等；然后，介绍了如何修改数据表的结构，包括添加、修改及删除某些字段；接着，通过实例介绍了数据表中数据的创建、修改及删除等操作；最后，重点介绍了数据表中的约束，包括约束的概念和类型，及各种约束的详细介绍。本章是数据库操作学习中的核心章节，需要重点理解学习。