1、第3章 表的创建,学习要点： 表的概念 表的创建、修改和删除 添加、更新与删除表中的数据 数据完整性的概念及实施方法,3.1 表的概念 3.2 数据类型 3.3 表结构的创建、修改和删除 3.4 向表中插入、修改和删除数据 3.5 约束 3.6 实现数据完整性 3.7 使用企业管理器查看表格,3.1 表的概念,数据库中包含一个或多个表。 表是数据的集合，是用来存储数据和操作数据的逻辑结构。 表是由行和列组成的，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性。 在特定表中，列名必须是唯一的，但相同的列名可以在数据库中的不同表中使用。 SQL Server中的每个表中最多允
2、许有1024列，每行最多允许有8060字节的用户数据。行和列的次序是任意的。,3.2 数据类型,数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。 3.2.1 系统数据类型1二进制数据类型二进制数据是一些用十六进制表示的数据。二进制数据类型包括三种：binary、varbinary和image。2整数数据类型整数数据类型有五种：bit、int、bigint、smallint、tinyint。,3浮点数据类型浮点数据类型包括float和real两种类型，用于存储范围非常大的数字。4精确小数数据类型精确小数数据类型包括decimal和numeric两种，可以精确指定小数点两边的总位数和小
3、数点右边的位数。5货币数据类型货币数据类型专门用于货币数据处理，包括money和smallmoney。 6日期/时间数据类型日期/时间数据类型可以存储日期和时间的组合数据，包括datetime和smalldatetime两种数据类型。,7字符数据类型字符型数据是由字母、数字和符号组合而成。字符数据类型又可分为3种：char(n)、varchar(n)、text。8unicode数据类型unicode数据类型用于存储要用两个字节才能存储的双字节字符，例如汉字、日文或韩文等。unicode数据类型包括nchar、nvarchar和ntext。9sql_variant数据类型sql_variant数
4、据类型可以应用在列、参数、变量和函数返回值中，以sql_variant为数据类型的数据可以存储除text、ntext、image和sql_variant数据类型以外的各种数据。,3.2.2 用户自定义数据类型用户可以使用企业管理器或T-SQL语句来创建用户自定义数据类型，一旦创建成功，用户可以象使用系统数据类型一样使用自定义数据类型。1创建用户自定义数据类型（1）使用企业管理器创建用户自定义数据类型 （2）使用T-SQL语句创建用户自定义数据类型 语法形式为： sp_addtype type_name, system_type ,NULL | NOT NULL | IDENTITY ,type
5、_name：是用户自定义数据类型的名字。 system_type：是用户自定义数据类型所基于的系统数据类型名。 NULL | NOT NULL | IDENTITY：用来表示用户自定义数据类型的性质，分别为允许为空值、不允许为空值、具有标识例性质。例3-1 使用T-SQL语句创建两个自定义数据类型。第一个自定义的数据类型telephone_code，数据长度15，可变长字符型，允许为空；第二个自定义数据类型zip，定长字符型，长度为6，不允许为空。,USE sales GO EXEC sp_addtype telephone_code,varchar(15),NULL EXEC sp_addt
6、ype zip,char(6),NOT NULL,2查看用户自定义数据类型 语法形式为：sp_help type_name 例3-2 查看telephone_code的特征。 EXEC sp_help telephone_code 3删除用户自定义数据类型 语法形式为：sp_droptype type_name 例3-3 删除在例3-1中定义的数据类型telephone_code。 EXEC sp_droptype telephone_code,注意：只能删除已经定义但未被使用的用户自定义数据类型，正在被表或其他数据库对象使用的用户自定义数据类型不能被删除。,3.3 表结构的创建、修改和删除,
7、3.3.1 表结构的创建1用企业管理器表结构 2使用T-SQL语句创建表结构 CREATE TABLE table_name ( column_name1 data_type DEFAULTconstant_expression IDENTITY ( SEED, INCREMENT ) NULL | NOT NULL ,n )ON filegroup | DEFAULT ,table_name：是表的名字。 column_name1：表中列的名字。列名在表中必须唯一，列的命名必须遵循数据库对象的命名规则。 data_type：列的数据类型和宽度。 DEFAULT constant_expres
8、sion：指定所定义的列的默认值，默认值由常量表达式确定。 IDENTITY：定义该列是一个标识列。 SEED：标识列的起始值，即插入表的第一行数据的标识列的值。 INCREMENT：标识列的增值，即插入表的最近一行相对于前一行标识列数据值的增量。,NULL | NOT NULL：指出该列是否允许为空，默认为NULL。 ON filegroup | DEFAULT：指定在哪个文件组上创建表。DEFAULT表示将表存储在默认文件组中。,例3-4 为sales数据库创建一个销售人员表Salers，它包含SaleID、姓名、性别、出生日期、雇用日期、地址、电话和备注字段，其中SaleID、姓名这两列
9、不允许为空。,CREATE TABLE salers (SaleID char(3) NOT NULL,SaleName char(8) NOT NULL,Sex char(2),Birthday datetime,HireDate datetime,Address char(60),Telephone char(13),Note char(200) ),例3-5 为sales数据库创建订单表Orders，包括OrderID、CustomerID、SaleID和OrderDate字段，其中OrderID为标识列，起始值为10248，增值为1；CustomerID和SaleID字段不允许为空值；
10、OrderDate字段的默认值为当前日期。,CREATE TABLE Orders (OrderID int IDENTITY(10248,1),CustomerID char(3) NOT NULL,SaleID char(3) NOT NULL,OrderDate datetime DEFAULT getdate() ),例3-6 为sales数据库在文件组USER1上创建类别Categories表。 CREATE TABLE Categories (CategoryID int NOT NULL,CategoryName nvarchar(15),Description nvarchar
11、(200) ) ON USER1,注意：文件组USER1必须已经定义。在例3-4和例3-5中，没有使用ON关键字指出文件组，则表存储在缺省的文件组中。,3.3.2 表结构的修改 修改的操作包括：增加或删除列、修改列的名称、数据类型、数据长度、改变表的名称等。1使用企业管理器修改表结构 2使用T-SQL语句修改表结构 语法形式为： ALTER TABLE table_name ADD column_name date_type DEFAULT contant_expressionIDENTITY(SEED,INCREMENT)NULL | NOT NULL | DROP COLUMN colum
12、n_name | ALTER COLUMN column_name new_datetype NULL | NOT NULL ,例3-7 sales数据库中的Customers表包含CustomerID、ConpanyName和ConnectName三个字段，现为该表添加地址、邮政编码和电话号码字段。 EXEC sp_addtype telephone_code,varchar(15),NULL EXEC sp_addtype zip,char(6),NOT NULL GO ALTER TABLE Customers ADD Address char(40),ZipCode zip,Telep
13、hone telephone_code 在该列中，添加的ZipCode和Telephone字段的数据类型为用户自定义数据类型。,例3-8 将表salers中的Sex列删除 ALTER TABLE salers DROP COLUMN Sex 例3-9 将salers表中的Address字段的长度改为30，并且不能为空。 ALTER TABLE salers ALTER COLUMN Address varchar(30) NOT NULL,注意：只能修改列的数据类型，以及列值是否为空。,3.3.3 表结构的删除1使用企业管理器删除表格 2使用的DROP TABLE命令删除表格 其语法形式为：
14、DROP TABLE table_name1,n 例3-10 删除当前数据库中一个名为example1的表 DROP TABLE example1,3.4 向表中插入、修改和删除数据,3.4.1 插入数据向表中插入数据使用的T-SQL语句是INSERT INTO语句，其语法形式为： INSERT INTO table_name ( column_name ,n ) VALUES ( expression | NULL | DEFAULT ,n )table_name：要插入数据的表名。column_name：要插入数据的列名。expression：与column_name相对应的字段的值，字符
15、型和日期型值插入时要加单引号。,例3-11 向categories表中添加三行数据。 INSERT INTO Categories(CategoryID,CategoryName,Description) VALUES(1,饮料,软饮料、咖啡、茶、啤酒和淡啤酒)INSERT INTO Categories(CategoryID,CategoryName,Description) VALUES(2,调味品,香甜可口的果酱、调料、酱汁和调味品)INSERT INTO Categories(CategoryID,CategoryName,Description) VALUES(3,点心,甜点、糖和面
16、包),例3-12 向Salers表中插入一行数据，其中Sex字段使用默认值为男，HireDate等字段均取空值。,在插入数据时，对于允许为空的列可使用NULL插入空值；对于具有默认值的列可使用DEFAULT插入默认值 。,INSERT INTO Salers (SaleID,SaleName,Sex,Birthday,HireDate,Address,Telephone,Notes) VALUES(s11,赵宇飞,DEFAULT,1974-07-25,NULL,NULL,NULL,NULL) 或 INSERT INTO Salers(SaleID,SaleName,Birthday) VALU
17、ES(s11,赵宇飞,1974-07-25),例3-13 在例3-11和例3-12中，由于是对表中所有列插入数据，则可省略列名表，写成如下形式： INSERT INTO Categories VALUES(1,饮料,软饮料、咖啡、茶、啤酒和淡啤酒)INSERT INTO Salers VALUES(s11,赵宇飞,DEFAULT,1974-07-25,NULL,NULL,NULL,NULL),注意：当向表中所有列都插入新数据时，可以省略列名表，但必须保证VALUES后的各数据项位置同表定义时的顺序一致，否则系统会报错。,例3-14 向OrderDetails表中插入一行数据。 INSERT I
18、NTO OrderDetails VALUES(10254,P01003) OrderDetails表中有三列数据，而插入的数据只有两列，这是系统会报错。 正确形式为：INSERT INTO OrderDetailsVALUES(10254,P01003,NULL) 或 INSERT INTO OrderDetails (OrderID,ProductID)VALUES(10254,P01003) 在后一种形式中，系统将Quantity字段的值自动设为空值。,例3-15 向Orders表中插入一行数据。Orders表的定义如例3-5。 INSERT INTO Orders (CustomerI
19、D,SaleID) Values(c01,s11)在例3-5的定义中Orders包括四个字段，其中OrderID是标识列，OrderDate是带有默认值的列，因此用户在插入数据时不必提供这两列的值，SQL Server自动填充。,注意：具有IDENTITY属性的列，其值由系统给出，用户不必向表中插入数据。,使用INSERT INTO语句一次只能插入一行数据，若想一次插入多行数据，则需在INSERT INTO语句中加入查询子句SELECT，通过SELECT子句从其它表中选出符合条件的数据，再将其插入到指定的表中。语法形式如下： INSERT INTO dest_table_name ( colu
20、mn_name ,n ) SELECT column_name ,n FROM source_table_name WHERE search_conditions 功能：先从source_table_name表中找出符合条件的所有数据，从中选择所需要的列，将其插入到dest_table_name中。,例3-16 创建employee表，包含三个字段EmployeeID、EmployeeName和Address。将Salers表中的女销售人员的数据插入到employee表中。,注意： 要插入数据的表dest_table_name必须是已经存在的，不能向不存在的表中插入数据。 要插入数据的表des
21、t_table_name中的列和SELECT子句中的列的数量、顺序必须相同，列的数据类型也要相同。,CREATE TABLE Employee (EmployeeID char(3),EmployeeName char(8),Address char(60) GOINSERT INTO Employee SELECT SaleID,SaleName,Address FROM Salers WHERE Sex=女,3.4.2 修改数据 其语法形式为： UPDATE table_name SET column_name=expression ,n WHERE search_conditions t
22、able_name：要更新数据的表名。column_name：要更新数据的列名。expression：更新后的数据值。search_conditions：更新条件，只对表中满足该条件的记录进行更新。,例3-17 将Products表中啤酒的价格改为4元。 UPDATE Products SET Price=4 WHERE ProductName=啤酒 例3-18 将例3-12插入的一行数据中赵宇飞的地址改为东直门外大街108号，电话改为（010）60486658。 UPDATE Salers SET Address=东直门外大街108号,Telephone=(010)60486658 WHER
23、E SaleID=s11,例3-19 将Products表中CategoryID为2的所有产品的价格下调10。 UPDATE Products SET Price=Price*(1-0.1) WHERE CategoryID=2,注意：使用UPDATE语句可以一次修改一行数据，也可以一次修改多行数据，甚至时整张表的数据。但是无论那种修改，都要求修改前后的数据类型和数据个数相同。,3.4.3 删除数据 删除表中数据使用的是DELETE语句，其语法形式为： DELETE FROM table_name WHERE search_conditions 功能：删除表中符合search_conditio
24、ns的数据；缺省WHERE子句时，表示删除表中所有数据。 例3-20 将例3-12插入的数据从salers表中删除。 DELETE FROM Salers WHERE SaleID=s11,3.4.4 在企业管理器下插入、更新、删除表中数据在企业管理器下同样可以完成插入、更新和删除数据的操作。只需进入企业管理器，选中要进行插入、更新或删除数据操作的表，单击右键，弹出的快捷菜单，将鼠标指向“打开表”命令，选择级联子菜单中的“所有行”命令，系统会弹出数据记录窗口，在该窗口中就可完成插入、更新和删除操作。,3.5 约束,约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的主要
25、途径。约束有5种类型，包括：主键约束、唯一性约束、检查约束、默认约束、外键约束（参照约束）。约束可以在两个层次上实施： 列级：用户定义的约束只对表中的一列起作用； 表级：用户定义的约束对表中的多列起作用。,1约束的创建、修改（1）使用CREATE TABLE语句创建约束使用CREATE TABLE语句创建约束是在创建表时定义约束，约束是表格定义的一部分。其语法形式为： CREATE TABLE table_name ( column_name data_type CONSTRAINT constraint_name constraint_type ,n )constraint_name：要创建
26、的约束的名字。若缺省约束名，则SQL Server会自动为约束提供一个名字。constraint_type：要创建的约束类型。,（2）使用ALTER TABLE语句创建约束在已有的表上创建、修改约束可以使用ALTER TABLE命令。其语法形式为： ALTER TABLE table_name WITH CHECK | WITH NOCHECK ADD CONSTRAINT constraint_name constraint_typeWITH CHECK | WITH NOCHECK：新加入的约束对表中现有的数据是否进行检查。2约束的删除语法形式为：ALTER TABLE table_nam
27、eDROP CONSTRAINT constraint_name,3.5.1 主键约束（PRIMARY KEY）主键用于唯一地标识表中每一条记录。我们可以定义表中的一列或多列为主键，则主键列上没有两行具有相同值，也不能为空值。 1使用T-SQL语句创建主键约束创建主键约束的语法形式： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( col_name ,n),例3-21 创建Orders表，包括OrderID、CustomerID、SaleID和OrderDate四个字段，其中OrderID字段设为主键。 CRE
28、ATE TABLE Orders (OrderID int CONSTRAINT pk_orderid PRIMARY KEY,CustomerID char(3),SaleID char(3),OrderDate datetime) 或 CREATE TABLE Orders( OrderID int PRIMARY KEY,CustomerID char(3),SaleID char(3),OrderDate datetime),例3-21 已有Salers表，包含SaleID、SaleName、Sex等字段，将该表中SaleID字段设为主键。,注意：即使在创建主键约束时带有WITH NO
29、CHECK选项，系统总要对现存数据进行检查，若现有数据在该列上出现重复或空值，SQL Server会提示错误信息，并拒绝执行创建主键约束操作。,ALTER TABLE SalersADD CONSTRAINT pk_saleid PRIMARY KEY(Saleid),例3-22 OrderDetails表包含OrderID、ProductID和Quantity三个字段，在OrderID和ProductID上创建主键。,ALTER TABLE OrderDetails ADD CONSTRAINT pk_order_product PRIMARY KEY(orderid,productid),
30、主键约束定义在不止一列上时，一列中的值可以重复，但主键约束定义中的所有列的组合值必须唯一。,例3-23 删除例3-21中创建的主键约束。 ALTER TABLE Salers DROP CONSTRAINT pk_saleid2使用企业管理器创建主键约束 在企业管理器下也可创建、修改、删除主键约束。只需进入企业管理器，选中需要添加主键约束的表，右键单击，在系统弹出的快捷菜单中选择“设计表”命令，在弹出的窗口中设置、取消主键即可。,3.5.2 唯一性约束（UNIQUE）唯一性（UNIQUE）约束用来限制不受主键约束的列上的数据的唯一性，即表中任意两行在指定列上都不允许有相同的值。一个表上可以放置
31、多个UNIQUE约束。唯一性约束和主键约束的区别： 唯一性约束允许在该列上存在NULL值，而主键约束限制更严格，不但不允许有重复，而且也不允许有空值。 在创建唯一性约束和主键约束时可以创建簇索引和非簇索引，但在缺省情况下主键约束产生簇索引，而唯一性约束产生非簇索引。,1使用T-SQL语句创建唯一约束 创建唯一性约束的语法形式为： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED ( col_name ,n) 例3-24 创建表department，包含dep_id、dep_name以及dep_head三个字段，并在dep_na
32、me字段上创建唯一性约束。 CREATE TABLE department (dep_id int PRIMARY KEY,dep_name char(20) CONSTRAINT unq_depname UNIQUE,dep_head char(5) ),例3-25 在salers表的Telephone字段建立唯一性约束。 ALTER TABLE Salers ADD CONSTRAINT unq_telephone UNIQUE(Telephone)2使用企业管理器创建唯一性约束3.5.3 检查约束（CHECK）CHECK约束用来指定某列的可取值的范围。它通过限制输入到列中的值来强制域的完
33、整性。我们可以在单列上定义多个CHECK约束，以它们定义的顺序来求值。1使用T-SQL语句创建检查约束语法形式为： CONSTRAINT constraint_name CHECK (expression),例3-26 创建学生表s，包含sid（学号）、sname（姓名）、sage（年龄）以及scity（城市）四个字段，并在sage字段创建一个CHECK约束，使得sage的值在1830岁之间。,CREATE TABLE s (sid int PRIMARY KEY,sname char(20),sage int CONSTRAINT check_age CHECK (sage=18 AND s
34、age=30),scity char(10) ),该语句还可写成如下形式： CREATE TABLE s (sid int PRIMARY KEY,sname char(20),sage int CONSTRAINT check_age CHECK (sage BETWEEN 18 AND 30),scity char(10) ),例3-27 修改学生表s，在scity字段创建一个CHECK约束，以限制只能输入有效的城市。,ALTER TABLE s WITH NOCHECK ADD CONSTRAINT check_city CHECK (scity IN(北京,上海,天津,重庆),例3-2
35、8 修改Salers表，在Telephone字段创建一个CHECK约束，使得该字段的值的格式为（0-90-90-9）0-90-90-90-90-90-90-90-9。,ALTER TABLE Salers ADD CONSTRAINT check_telephone CHECK (Telephone LIKE (0-90-90-9)0-90-90-90-90-90-90-90-9),注意：不能在具有IDENTITY属性的列上设置CHECK约束,2使用企业管理器创建检查约束,3.5.4 默认约束（DEFAULT）DEFAULT约束用于给表中指定列赋予一个常量值（默认值），当向该表插入数据时，如果
36、用户没有明确给出该列的值，SQL Server自动为该列输入默认值。每列只能有一个DEFAULT约束。1使用T-SQL语句创建默认约束创建默认约束的语法形式为： CONSTRAINT constraint_name DEFAULT (expression | NULL) FOR column_name,例3-29 修改Orders表，在OrderDate字段创建DEFAULT约束，将当前日期设为默认值，当未给Orders表的订货日期提供值时，取当前日期插入。 ALTER TABLE Orders ADD CONSTRAINT default_date DEFAULT getdate() FOR
37、 OrderDate 注意： 不能在具有IDENTITY属性的列上设置DEFAULT约束 DEFAULT约束只能用于INSERT语句。 如果对一个已经有数据的表添加DEFAULT约束，原来的数据不会得到默认值。2使用企业管理器创建默认约束,3.5.5 外键约束（FOREIGN KEY）外键约束用于与其他表（称为参照表）中的列（称为参照列）建立连接。外键的建立是通过将参照表中的主键所在列或具有唯一性约束的列包含在另一个表中，这些列就是另一个表的外键。当参照表中的参照列更新后，外键列也会自动更新，从而保证两个表之间的一致性关系 。,1使用T-SQL语句创建外键约束 创建外键约束的语法形式为： CO
38、NSTRAINT constraint_name FOREIGN KEY (col_name1,n) REFERENCES table_name(column_name1,n) 其中： col_name1,n：是要实施外键约束的列。 table_name：是参照表表名。column_name1,n：是参照表中的参照列,例3-30 若sales数据库中包含salers表和customers表。其中salers表包含SaleID、SaleName等字段，SaleID为主键；customers表包含CustomerID、Company等字段，CustomerID为主键。现创建Orders表，包含Or
39、derID、CustomerID、SaleID和OrderDate四个字段，CustomerID、SaleID为外键。 CREATE TABLE Orders (Orderid int PRIMARY KEY,CustomerID char(3) REFERENCES Customers(CustomerID),SaleID char(3) CONSTRAINT fk_saleid REFERENCES Salers(SaleID),OrderDate datetime DEFAULT getdate() ),例3-31 修改OrderDetails表，在OrderID字段上创建外键约束。 A
40、LTER TABLE OrderDetails ADD CONSTRAINT fk_orderid FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),注意：当将外键约束添加到一个已有数据的列上时，默认情况下，SQL Server将会自动检查表中已有数据，以确保所有的数据和主键保持一致，或者为NULL。但我们也可以根据实际情况的需要，设置SQL Server不对现有数据进行外键约束的检查，如下列所示：,2使用企业管理器创建外键约束,ALTER TABLE OrderDetails WITH NOCHECK ADD CONSTRAINT fk_ord
41、erid FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),3.6 实现数据完整性,数据完整性是指数据的正确性、一致性和安全性，是衡量数据库中数据质量好坏的重要标准。当用户用INSERT、DELETE或UPDATE语句修改数据库内容时，数据的完整性就可能会遭到破坏。为了解决这些问题，保证数据的完整性，SQL Server提供了实施数据完整性的方法包括约束、缺省、规则等。在SQL Server数据库中，数据完整性大致可划分为以下三种类型：实体完整性（Entity Integrity） 、域完整性（Domain Integrity） 、参照完整性（R
42、eferential Integrity） 。,1实体完整性（Entity Integrity）实体完整性指的是表中的每一行都能由称为主键的属性列来唯一标识，且不存在重复的数据行。2域完整性（Domain Integrity）域完整性指的是限制向表中输入的值的范围，保证给定列的输入有效性。它可以通过限制数据类型、值域或数据格式来实现。3参照完整性（Referential Integrity）参照完整性也叫引用完整性。指的是当一个表引用了另一个表中的某些数据时，要防止非法的数据更新，以保持表格间数据的一致性。可通过外键约束来实现 。,3.7 使用企业管理器查看表格,操作步骤如下：（1）进入企业管
43、理器，选中要查看的表格，单击鼠标右键，在弹出的快捷菜单中选择“打开表”，在其子菜单中有三个命令“返回所有行”、“返回首行”和“查询”。（2）选择“返回所有行”命令，将表中所有记录都显示在“数据记录”窗口中。（3）选择“返回首行”命令，在“行数”对话框中输入要显示的最大行数，单击“确定”按钮，就可在“数据记录”窗口中显示表中前指定行记录。,（4）选择“查询”命令，系统会弹出如下窗口 。在该窗口中，用户可输入查询条件，显示指定记录。,小结：本章详细介绍了如何创建、修改以及删除表格的方法，并对数据完整性的概念和实施数据完整性的方法做了简单讲解。学习完本章要求学生熟练掌握： 创建、修改、删除表结构的方法 插入、更新和删除表中数据的方法 使用约束实施数据完整性,
