第3讲 管理表

1、第第 3讲讲 管理表管理表哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院本本讲讲主要主要内内容容表的基础知识用SSMS管理表 用T-SQL语言管理表 常用工具简介 123 数据库创建后，数据库创建后， 就可以创建表来存储数就可以创建表来存储数据，通常是存储在永久表中据，通常是存储在永久表中. 不过不过, 用户也可用户也可以建立临时表以建立临时表. 表会一直存储在数据库文件表会一直存储在数据库文件内内, 直到被删除为止直到被删除为止. 只要用户拥有适当权只要用户拥有适当权限限, 就可以使用它们就可以使用它们. 一. 表的基础知识表的基础知识 表是由行和列组成的表是由行和列组成的. 创建表的过程主要

2、创建表的过程主要就是就是定义表的列定义表的列的过程的过程. 与表有关的两个常用与表有关的两个常用概念是：概念是：字段字段和和记录记录. 其中记录是表的一行，其中记录是表的一行，是表所塑造的单一对象的实体是表所塑造的单一对象的实体. 而字段是表中而字段是表中的列，描述一条记录要有若干个列的列，描述一条记录要有若干个列. 列代表列代表对象的属性对象的属性(例如例如, 姓名、住址等姓名、住址等). 在在 SQL Server 中，每个数据库中最多可中，每个数据库中最多可以创建以创建200万个表，用户创建数据库表时，最万个表，用户创建数据库表时，最多可以定义多可以定义 1024 列，也就是可以定义列，

3、也就是可以定义1024个个字段字段. 但是很少有哪个设计良好的关系数据库但是很少有哪个设计良好的关系数据库表会包含表会包含 25 个以上的列，它们绝大多数都只个以上的列，它们绝大多数都只包含少数的列包含少数的列 . 每个列有自己的属性每个列有自己的属性, 常用且重要的三常用且重要的三个属性是个属性是: 列名列名、数据类型数据类型和和约束约束. SQL Server 系统内的对象在命名时可以使系统内的对象在命名时可以使用中文用中文, 但要尽量避免这样做，以免引发工具但要尽量避免这样做，以免引发工具程序或前端应用程序的无谓错误程序或前端应用程序的无谓错误. 对象的命名必须符合系统的对象的命名必须符

4、合系统的命名规则命名规则. 比比如如名称名称标识符含字母在标识符含字母在1128个个(临时表的名称临时表的名称在在116个字符内个字符内), 特殊字符的名称要放到分隔符特殊字符的名称要放到分隔符或或”内等等内等等.命名规则命名规则 数据类型数据类型是数据的是数据的格式格式, 表定义中的每个表定义中的每个列必须有数据类型列必须有数据类型, 数据操作中数据操作中, SQL Server自动进行数据检验自动进行数据检验. 常用数据类型主要有常用数据类型主要有: 整数型整数型: intint, , smallintsmallint, , bigintbigint 浮点型浮点型: numeric, :

5、numeric, float(nfloat(n), real), real 字符型字符型: : char(nchar(n), ), varchar(nvarchar(n), ), nchar(nnchar(n) ) 日期时间型日期时间型: : smalldatetimesmalldatetime, date, time, date, time数据类型数据类型 SQL Server SQL Server 提供了两种方法创建数据库提供了两种方法创建数据库表表. . 第一种方法是通过图形化界面创建即使用第一种方法是通过图形化界面创建即使用“SQL Server Management StudioSQ

6、L Server Management Studio”(”(简记为简记为 SSMSSSMS ) )创建表，它提供了一个前端的、填充创建表，它提供了一个前端的、填充式的表设计器以简化表的设计工作；另一种方式的表设计器以简化表的设计工作；另一种方法是利用法是利用 T-SQLT-SQL语句中的语句中的 CREATE 命令创建命令创建表表. .二二. . 用用 SSMS 管理表管理表 在在 SSMSSSMS中中, , 展开要建表的数据库，比如展开要建表的数据库，比如 pubs,pubs,右键单击右键单击“表表”节点节点, , 在弹出的菜单中选在弹出的菜单中选择择“新建表新建表”, ”, 打开表设计器打

7、开表设计器, ,如下图如下图. . 注意在下方列属性视图中设置主键注意在下方列属性视图中设置主键, ,非空非空, ,标识列标识列, ,默认值默认值. . 创建表创建表 例例 建立表建立表 student 存放学生的信息存放学生的信息. id charchar (7), name charchar(10), sex charchar(2), birthday smallsmalldatetimedatetime, birth_place varcharvarchar(50), class charchar(10) 练习练习 - - 用用 T-SQLT-SQL 命令命令 CREATE 创建表创建表

8、. . CREATE TABLE studentstudent (id (id charchar(7),(7), name name charchar(10),(10), sex sex charchar(2),(2), birthday birthday smalldatetimesmalldatetime, , birth_placebirth_place varcharvarchar(50),(50), class class charchar(10) (10) ) )三三. . 用用T-SQL语言语言 管理表管理表哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 修改表修改表 (1) (1

9、)启动启动 SSMS SSMS 后后, , 展开展开 SQL Server SQL Server 实例和数实例和数据库据库. . (2) (2)右击右击要修改的表，然后从弹出的快捷菜单中要修改的表，然后从弹出的快捷菜单中选择选择“设计设计”命令命令. . (3)(3)在在“表设计器表设计器”中，可以新增列、删除列和中，可以新增列、删除列和修改列的名称、数据类型、长度、是否允许为空等修改列的名称、数据类型、长度、是否允许为空等. . (4)(4)当完成修改表的操作后，单击工具栏上的当完成修改表的操作后，单击工具栏上的“保存保存”按钮按钮. .利用利用T-SQL语句修改数据表语句修改数据表修改数据

10、表的语法格式如下：修改数据表的语法格式如下：ALTER TABLE ALTER TABLE table_nametable_name ALTER COLUMN ALTER COLUMN column_namecolumn_name new data type ( precision new data type ( precision ，scale ) NULL scale ) NULL | NOT NULL| NOT NULL | ADD | ADD ，n n | DROP CONSTRAINT | DROP CONSTRAINT constraint_nameconstraint_name

11、| COLUMN | COLUMN column_namecolumn_name ，n n 哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 删除表删除表 (1) (1)启动启动 SSMS SSMS 后后, ,展开展开 SQL Server SQL Server 实例和数实例和数据库据库. . (2) (2)右击右击要修改的表，然后从弹出的快捷菜单中要修改的表，然后从弹出的快捷菜单中选择选择“删除删除”，打开，打开“删除对象删除对象”对话框对话框. . (3)(3)在在“删除对象删除对象”对话框中，显示出删除对象对话框中，显示出删除对象的属性信息，单击的属性信息，单击“确定确定”按钮按钮. 注意注

12、意: 删除表只能够删除用户表，不能够删除系删除表只能够删除用户表，不能够删除系统表统表. 删除表一旦操作完成，表中数据也一并被删删除表一旦操作完成，表中数据也一并被删除，而且是无法恢复的除，而且是无法恢复的.v 利用利用T-SQLT-SQL语句删除数据表语句删除数据表删除数据表的语法格式如下：删除数据表的语法格式如下： DROP TABLE DROP TABLE table_nametable_name ，n n 其中，其中，table_nametable_name为所要删除的表的名称。为所要删除的表的名称。 删除表只能够删除用户表，不能够删除系统表。删除表只能够删除用户表，不能够删除系统表。

13、 完整性与约束完整性与约束 完整性完整性 数据完整性是数据库设计方面一个非常重要的问题，数据完整性是数据库设计方面一个非常重要的问题，数据完整性代表数据的正确性、一致性与可靠性，实施完整数据完整性代表数据的正确性、一致性与可靠性，实施完整性的目的在于确保数据的质量。性的目的在于确保数据的质量。 在在SQL Server中，根据数据完整性措施所作用的数据库中，根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性分类为对象和范围不同，可以将数据完整性分类为实体完整性实体完整性、域域完整性完整性和和参照完整性参照完整性等。等。v1 1实体完整性实体完整性 实体完整性把数据表中的每行看作一

14、个实体，它要实体完整性把数据表中的每行看作一个实体，它要求所有行都具有唯一标识。在求所有行都具有唯一标识。在SQL ServerSQL Server中，可以通过中，可以通过建立建立PRIMARY KEYPRIMARY KEY约束、约束、UNIQUEUNIQUE约束、唯一索引，以及约束、唯一索引，以及列列IDENTITYIDENTITY属性等措施来实施实体完整性。属性等措施来实施实体完整性。v2 2域完整性域完整性 域完整性要求数据表中指定列的数据具有正确的数域完整性要求数据表中指定列的数据具有正确的数据类型、格式和有效的数据范围。域完整性通过默认值据类型、格式和有效的数据范围。域完整性通过默认

15、值、FOREIGN KEYFOREIGN KEY、CHECKCHECK等约束，以及默认、规则等数据等约束，以及默认、规则等数据库对象来实现。库对象来实现。v3 3参照完整性参照完整性 参照完整性维持被参照表和参照表之间的数据参照完整性维持被参照表和参照表之间的数据一致性。在一致性。在SQL ServerSQL Server中，参照完整性通过主键与中，参照完整性通过主键与外键或唯一键与外键之间的关系来实现，通过建立外键或唯一键与外键之间的关系来实现，通过建立FOREIGN KEYFOREIGN KEY约束来实施。在被参照表中，当其主键约束来实施。在被参照表中，当其主键值被其他表所参照时，该行不能

16、被删除，也不允许值被其他表所参照时，该行不能被删除，也不允许改变。在参照表中，不允许参照不存在的主键值。改变。在参照表中，不允许参照不存在的主键值。 约束约束语句是表定义语句的重要组成部分语句是表定义语句的重要组成部分. 通过约束机制通过约束机制, 可使数据库引擎强制控制数据可使数据库引擎强制控制数据内容的完整性内容的完整性. 约束语句可以在列或表两个级别完成约束语句可以在列或表两个级别完成.例如例如, 将将 PRIMARY KEY 约束可以作用在列上约束可以作用在列上, 若若 PRIMARY KEY 约束结合多个字段约束结合多个字段, 则只能在则只能在表级别建立表级别建立. 使用时使用时,

17、约束优先于约束优先于 DML 触发过程、规则触发过程、规则和默认和默认.约束约束 not null 非空约束非空约束 - 不允许空值不允许空值 primary key 主键约束主键约束 - 识别表的记录识别表的记录 - 主键值唯一主键值唯一,非空非空 check 检查约束检查约束 - 限制列值范围限制列值范围 - 操作数据时操作数据时, 系统自动验证数据系统自动验证数据 unique 唯一性约束唯一性约束 - 值唯一值唯一 - 该约束可建在表上该约束可建在表上, 可用于有空值的列可用于有空值的列 default 默认值约束默认值约束 - 默认的值默认的值 - 常用的默认值常用的默认值 getd

18、ate(),newid() foreign key 外键约束外键约束 - 该键值参照另一表键值该键值参照另一表键值 - 另一表另一表(引用表引用表)的值必须存在唯一的值必须存在唯一 约束的种类约束的种类PK 客户编号客户编号 公司名称公司名称 联系人联系人 联系人称呼联系人称呼 地址地址 城市城市 行政区行政区 邮政编码邮政编码 国家地区国家地区 电话电话 传真传真 订单编号订单编号FK 客户编号客户编号 员工编号员工编号 订单日期订单日期 要货日期要货日期 送货日期送货日期 送货方式送货方式 运费运费 收货人收货人 送货地址送货地址 送货城市送货城市哈尔哈尔滨师滨师范大范大学学恒星恒星学学院

19、院 使用表设计器创建使用表设计器创建 Primary Key 约束约束 在表设计器中，选择需要设置主键的列在表设计器中，选择需要设置主键的列 (如需要如需要设置多个列为主键，则选中所有需要设置为主键的设置多个列为主键，则选中所有需要设置为主键的所有列所有列)，单击鼠标，单击鼠标右键右键，然后从弹出的快捷菜单，然后从弹出的快捷菜单中选择中选择“设置主键设置主键”命令，完成主键设置，这时主命令，完成主键设置，这时主键列的左边会显示键列的左边会显示“黄色钥匙黄色钥匙”图标启动图标启动. 如何修改和删除如何修改和删除 Primary Key 约束呢约束呢?建立约束建立约束使用使用T-SQL语句创建语句

20、创建PRIMARY KEY约束约束创建主键约束的语法形式如下：创建主键约束的语法形式如下： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( column_name , n )其中，其中，CLUSTERED | NONCLUSTERED表示所创建表示所创建的的UNIQUE约束是聚集索引还是非聚集索引，默认为约束是聚集索引还是非聚集索引，默认为CLUSTERED聚集索引聚集索引。哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 使用表设计器创建使用表设计器创建 Unique 约束约束 在表设计器中可以创建、修改和删除在

21、表设计器中可以创建、修改和删除Unique 约束约束. 要点要点: 选中列选中列, 按按右键右键, 选选“索引索引/键键(I)”. 如何创建如何创建 Check 约束呢约束呢?建立约束建立约束v 使用使用T-SQL语句创建语句创建UNIQUE约束约束创建唯一性约束的语法形式如下：创建唯一性约束的语法形式如下： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED 其中，其中，CLUSTERED | NONCLUSTERED表示所创建的表示所创建的UNIQUE约束是聚集索引还是非聚集索引，默认为约束是聚集索引还是非聚集索引，默认为N

22、ONCLUSTERED非聚集索引。非聚集索引。哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 CHECK约束约束CHECK约束用于限制输入到一列或多列的值的范围，从约束用于限制输入到一列或多列的值的范围，从逻辑表达式判断数据的有效性，也就是一个列的输入内容逻辑表达式判断数据的有效性，也就是一个列的输入内容必须满足必须满足CHECK约束的条件，否则，数据无法正常输入，约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。从而强制数据的域完整性。建立约束建立约束使用使用T-SQL语句创建语句创建CHECK约束约束创建检查约束的语法形式如下：创建检查约束的语法形式如下： CONSTRAINT c

23、onstraint_name CHECK ( check_expression )其中，其中，check_expression 为检查表达式。为检查表达式。 使用表设计器创建使用表设计器创建DEFAULT约束约束在表设计器中可以创建、修改和删除在表设计器中可以创建、修改和删除DEFAULT约束。约束。其操作步骤如下：其操作步骤如下：在表设计器中，选择需要设置在表设计器中，选择需要设置DEFAULT值的列，在下面值的列，在下面“列属性列属性”的的“默认值或绑定默认值或绑定”栏中输入默认值，然后单栏中输入默认值，然后单击工具栏中的击工具栏中的“保存保存”按钮，即完成按钮，即完成DEFAULT约束的

24、创约束的创建。建。 使用使用T-SQL语句创建语句创建DEFAULT约束约束创建默认值约束的语法形式如下：创建默认值约束的语法形式如下： CONSTRAINT constraint_name DEFAULT constraint_expression 其中，其中，constraint _expression 为默认值。为默认值。哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 使用表设计器建立使用表设计器建立 Foreign Key 约束约束 外键外键 ( Foreign Key ) 用于建立和加强两个表用于建立和加强两个表( 主表与从表主表与从表) 的一列或多列数据之间的链接，当数的一列或多列数

25、据之间的链接，当数据添加、修改或删除时，通过外键约束保证它们之据添加、修改或删除时，通过外键约束保证它们之间数据的一致性间数据的一致性. 定义表之间的参照完整性是先定义主表的主键，定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束再对从表定义外键约束. Foreign Key 约束要求约束要求列中的每个值在所引用的表中对应的列中都存在列中的每个值在所引用的表中对应的列中都存在.建立约束建立约束哈尔哈尔滨师滨师范大范大学学恒星恒星学学院院 在表设计器的空白处单击鼠标在表设计器的空白处单击鼠标右键右键选择选择“关系关系”，弹出弹出“外键关系外键关系”对话框对话框. . 单击单击“添加添

26、加”按钮可以添加外键，单击按钮可以添加外键，单击“表和列表和列规范规范” ” 右侧的按钮，弹出右侧的按钮，弹出 “ “表和列表和列”对话框，选择对话框，选择相应的主键表和外键表及对应的主键和外键，单击相应的主键表和外键表及对应的主键和外键，单击“确定确定”按钮返回按钮返回“外键关系外键关系”对话框，在对话框，在“标识标识”的名称栏中填写外键名称即可创建外键约束的名称栏中填写外键名称即可创建外键约束. . 要删除要删除外键，只要在该对话框左侧栏中选择对应的外键名称，外键，只要在该对话框左侧栏中选择对应的外键名称，单击单击“删除删除”按钮即可按钮即可. .建立约束建立约束v 使用使用T-SQL语句

27、创建语句创建FOREIGN KEY约束约束创建外键约束的语法形式如下：创建外键约束的语法形式如下： CONSTRAINT constraint_name FOREIGN KEYFOREIGN KEY（ column_namecolumn_name , n , n ） / /* *定义外键定义外键* */ / REFERENCES REFERENCES referenced_table_namereferenced_table_name ( (ref_columnref_column, n , n ) ) , n , n 参数说明如下。参数说明如下。referenced_table_name是是

28、FOREIGN KEY约束引用的表的名约束引用的表的名称。称。column_name是是FOREIGN KEY约束所引用的表中的某列。约束所引用的表中的某列。 - - 创建表时给出列的约束创建表时给出列的约束. . CREATE TABLE student1 ( id char(7) primary key clustered, name char(10) not null, sex char(2) check (sex in(F,M), birthday smalldatetime, birth_place varchar(50) null, class char(10) )USE pubs

29、CREATE TABLE dbo.客户客户 ( 客户编号客户编号 int primary key, 客户名称客户名称 char(50) not Null, 住址住址 char(50), 生日生日 smalldatetime, 电话电话 int, 建立日期建立日期 smalldatetime default GetDate(), constraint chk_phone check (电话电话 like (010)0-90-90-90-9 0-90-90-90-9), constraint chk_id check(客户编号客户编号 between 0 and 10000) ) 例题例题- 下列

30、代码测试下列代码测试“建立日期建立日期”字段是否输入默认值字段是否输入默认值.USE pubsINSERT 客户客户 ( 客户编号客户编号, 客户名称客户名称, 生日生日 ) VALUES( 1001, Candy , 1980/04/22 )GO SELECT * FROM 客户客户CREATE TABLE 产品产品 ( 产品编号产品编号 int primary key, 产品名称产品名称 varchar(30) ; CREATE TABLE 订单订单 ( 订单编号订单编号 int, 产品编号产品编号 int foreign key references 产品产品 (产品编号产品编号), 订

31、单数量订单数量 int , 订单日期订单日期 datetime default Getdate() ); 下面的范例使用订单表与产品表来说明如何用代码下面的范例使用订单表与产品表来说明如何用代码建立建立”外键外键”. 首先首先, 产品表有产品表有”主键主键”, 才可用订单才可用订单表的表的”产品编号产品编号”字段为参考来建立字段为参考来建立”外键外键”. “外键外键” 不一定要参考另一个表的不一定要参考另一个表的 ”主键主键”, 也可以定义为参考其他表中也可以定义为参考其他表中 unique 条件约束的条件约束的列列. ”外键外键” 可包含可包含 Null 值值. 同时同时, 复合复合”外键外

32、键”的任何列包含了的任何列包含了 Null 值值, 将会略过数据验证工将会略过数据验证工作作. 若要确保输入的数据都符合若要确保输入的数据都符合”外键外键”要求要求, 可对所有参与的列指定非空可对所有参与的列指定非空. 例题例题 在执行创建产品信息表的操作时，指在执行创建产品信息表的操作时，指定产品编号为主键值，并且创建一个聚集索引定产品编号为主键值，并且创建一个聚集索引. create table create table productsproducts ( id char(10) not null, name char(20) not null, price money default

33、0.01, quantity smallint null constraint pk_id primary key clustered (id) )增加、删除和修改列增加、删除和修改列 例题例题 在雇员信息表中增加、删除和修改字段的在雇员信息表中增加、删除和修改字段的 SQL 语句的程序清单如下：语句的程序清单如下： CREATE TABLE employees (id char(8) primary key, name char(20) not null, department char(20) null, memo char(30) null, age int null) ALTER TA

34、BLE employees ADD salary int null DROP column age ALTER column memo varchar(200) null 若要修改表的定义并在现有的表中新增列若要修改表的定义并在现有的表中新增列,操作必须在该列允许操作必须在该列允许 Null 值或包含值或包含 Default 约约束的条件下进行束的条件下进行. 由于新的列被加入表中由于新的列被加入表中, 数据库引擎会针数据库引擎会针对表中的每条现有数据将值插入该列内对表中的每条现有数据将值插入该列内. 因此因此,当列被加入表中时当列被加入表中时, 替该列加入替该列加入 Default 定义定义

35、会很有用或者指定该列允许会很有用或者指定该列允许 Null 值值. 不满足这不满足这些条件些条件, 将返回错误信息将返回错误信息.如果需要增加非空列呢如果需要增加非空列呢? ?增加新列的限制增加新列的限制 用户可以删除现有表中的列用户可以删除现有表中的列, 但具有下列但具有下列特性的列将无法删除特性的列将无法删除. - 用于索引的列用于索引的列. - 用于用于 CHECK、FOREIGN KEY、 UNIQUE 或或 PRIMARY KEY 条件约束的列条件约束的列. - 与与 Default 定义建立关联定义建立关联, 或关联至默或关联至默认对象的列认对象的列. - 关联至规则的列关联至规则的列. - 注册成全文检索支持的列注册成全文检索支持的列. - 作为表的全文检索索引键的列作为表