第4讲 管理表.ppt

1、第 4 讲 管理表,哈尔滨师范大学恒星学院,本讲主要内容,表的基础知识,用SSMS管理表,用T-SQL语言管理表,常用工具简介,1,2,3,数据库创建后， 就可以创建表来存储数 据，通常是存储在永久表中. 不过, 用户也可 以建立临时表. 表会一直存储在数据库文件 内, 直到被删除为止. 只要用户拥有适当权 限, 就可以使用它们.,一. 表的基础知识,表是由行和列组成的. 创建表的过程主要 就是定义表的列的过程. 与表有关的两个常用 概念是：字段和记录. 其中记录是表的一行， 是表所塑造的单一对象的实体. 而字段是表中 的列，描述一条记录要有若干个列. 列代表 对象的属性(例如, 姓名、住址等

2、).,在 SQL Server 中，每个数据库中最多可 以创建200万个表，用户创建数据库表时，最 多可以定义 1024 列，也就是可以定义1024个 字段. 但是很少有哪个设计良好的关系数据库 表会包含 25 个以上的列，它们绝大多数都只 包含少数的列 . 每个列有自己的属性, 常用且重要的三 个属性是: 列名、数据类型和约束.,SQL Server 系统内的对象在命名时可以使 用中文, 但要尽量避免这样做，以免引发工具 程序或前端应用程序的无谓错误. 对象的命名必须符合系统的命名规则. 比如名称标识符含字母在1128个(临时表的名称在116个字符内), 特殊字符的名称要放到分隔符或”内等等

3、.,命名规则,数据类型是数据的格式, 表定义中的每个 列必须有数据类型, 数据操作中, SQL Server 自动进行数据检验. 常用数据类型主要有: 整数型: int, smallint, bigint 浮点型: numeric, float(n), real 字符型: char(n), varchar(n), nchar(n) 日期时间型: smalldatetime, date, time,数据类型,约束语句是表定义语句的重要组成部分. 通过约束机制, 可使数据库引擎强制控制数据内容的完整性. 约束语句可以在列或表两个级别完成.例如, 将 primary key 约束可以作用在列上, 若

4、 primary key 约束结合多个字段, 则只能在表级别建立. 使用时, 约束优先于 DML 触发过程、规则和默认.,约束, not null 非空约束 - 不允许空值 primary key 主键约束 - 识别表的记录 - 主键值唯一,非空 check 检查约束 - 限制列值范围 - 操作数据时, 系统自动验证数据 unique 唯一性约束 - 值唯一 - 该约束可建在表上, 可用于有空值的列 default 默认值约束 - 默认的值 - 常用的默认值 getdate(),newid() foreign key 外键约束 - 该键值参照另一表键值 - 另一表(主表)的值必须存在唯一,约束

5、的种类,SQL Server 提供了两种方法创建数据库 表. 第一种方法是通过图形化界面创建即使用 “SQL Server Management Studio”(简记为 SSMS )创建表，它提供了一个前端的、填充 式的表设计器以简化表的设计工作；另一种方 法是利用 T-SQL语句中的 CREATE 命令创建 表.,二. 用 SSMS 管理表,在 SSMS中, 展开要建表的数据库，比如 pubs,右键单击“表”节点, 在弹出的菜单中选 择“新建表”, 打开表设计器,如下图. 注意在下方列属性视图中设置主键,非空, 标识列,默认值.,创建表,哈尔滨师范大学恒星学院,修改表,(1)启动 SSMS

6、后, 展开 SQL Server 实例和数 据库. (2)右击要修改的表，然后从弹出的快捷菜单中 选择“设计”命令. (3)在“表设计器”中，可以新增列、删除列和 修改列的名称、数据类型、长度、是否允许为空等. (4)当完成修改表的操作后，单击工具栏上的 “保存”按钮.,哈尔滨师范大学恒星学院,删除表,(1)启动 SSMS 后,展开 SQL Server 实例和数 据库. (2)右击要修改的表，然后从弹出的快捷菜单中 选择“删除”，打开“删除对象”对话框. (3)在“删除对象”对话框中，显示出删除对象 的属性信息，单击“确定”按钮. 注意: 删除表只能够删除用户表，不能够删除系 统表. 删除表

7、一旦操作完成，表中数据也一并被删除， 而且是无法恢复的.,哈尔滨师范大学恒星学院,使用表设计器创建 Primary Key 约束 在表设计器中，选择需要设置主键的列 (如需要 设置多个列为主键，则选中所有需要设置为主键的 所有列)，单击鼠标右键，然后从弹出的快捷菜单 中选择“设置主键”命令，完成主键设置，这时主 键列的左边会显示“黄色钥匙”图标启动. 如何修改和删除 Primary Key 约束呢?,建立约束,哈尔滨师范大学恒星学院,使用表设计器创建 Unique 约束 在表设计器中可以创建、修改和删除 Unique 约束. 要点: 选中列, 按右键, 选“索引/键(I)”. 如何创建 Che

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

9、表和列”对话框，选择 相应的主键表和外键表及对应的主键和外键，单击 “确定”按钮返回“外键关系”对话框，在“标识” 的名称栏中填写外键名称即可创建外键约束. 要删除 外键，只要在该对话框左侧栏中选择对应的外键名称， 单击“删除”按钮即可.,建立约束,例 建立表 student 存放学生的信息. id char (7), name char(10), sex char(2), birthday smalldatetime, birth_place varchar(50), class char(10),练习,- 用 T-SQL 命令 CREATE 创建表. CREATE TABLE studen

10、t (id char(7), name char(10), sex char(2), birthday smalldatetime, birth_place varchar(50), class char(10) ),三. 用T-SQL语言 管理表,- 创建表时给出列的约束. 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

11、) null, class char(10) ),USE pubs 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) ),例

12、题,- 下列代码测试“建立日期”字段是否输入默认值. USE pubs INSERT 客户 ( 客户编号, 客户名称, 生日 ) VALUES( 1001, Candy , 1980/04/22 ) GO SELECT * FROM 客户,CREATE TABLE 产品 ( 产品编号 int primary key, 产品名称 varchar(30) ; CREATE TABLE 订单 ( 订单编号 int, 产品编号 int foreigh key references 产品 (产品编号) on delete no action, 订单数量 int， 订单日期 datetime defaul

13、t Getdate() );,下面的范例使用订单表与产品表来说明如何用代码 建立”外键”. 首先, 产品表有”主键”, 才可用订单表的”产品编号”字段为参考来建立”外键”.,“外键” 不一定要参考另一个表的 ”主键”, 也可以定义为参考其他表中 unique 条件约束的 列. ”外键” 可包含 null 值. 同时, 复合”外键” 的任何列包含了 null 值, 将会略过数据验证工 作. 若要确保输入的数据都符合”外键”要求, 可对所有参与的列指定非空.,例题 在执行创建产品信息表的操作时，指 定产品编号为主键值，并且创建一个聚集索引. create table products ( id c

14、har(10) not null, name char(20) not null, price money default 0.01, quantity smallint null constraint pk_id primary key clustered (id) constraint ix_name unique (name) ),增加、删除和修改列,例题 在雇员信息表中增加、删除和修改字段的 SQL 语句的程序清单如下： CREATE TABLE employees (id char(8) primary key, name char(20) not null, department

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

16、件, 将返回错误信息.,如果需要增加非空列呢?,增加新列的限制,用户可以删除现有表中的列, 但具有下列 特性的列将无法删除. - 用于索引的列. - 用于 CHECK、FOREIGN KEY、 UNIQUE 或 PRIMARY KEY 条件约束的列. - 与 Default 定义建立关联, 或关联至默 认对象的列. - 关联至规则的列. - 注册成全文检索支持的列. - 作为表的全文检索索引键的列.,删除列的限制,USE pubs ALTER TABLE 客户 ADD 更新日期 Datetime default GetDate(), 身份证号码 varchar(20) CONSTRAINT 身

17、份证号码 UNIQUE GO ALTER TABLE 客户 ALTER COLUMN 客户名称 nvarchar(30), 身份证号码 varchar(18) GO ALTER TABLE 客户 drop COLUMN 生日,例题,如果需要修改约束呢?, 查看表格的定义 查看表格中的数据 查看表格与其它数据库对象的依赖关系 利用系统存储过程 sp_help 查看表的信息 例如 sp_help titles sp_helpdb pubs exec sp_helpdb 演示,查看表,DROP TABLE 语句可以删除一个表和表中 的数据及其与表有关的所有索引、触发器、 约束、许可对象. 其语法形式

18、如下： DROP TABLE table_name, ,table_name 使用 DROP TABLE 语法时, 若要删除的表 包含 “外键”, 则须先删除表的 “外键”, 或是 删除”外键” 所关联的表. 例如, 若要删除前面建立的订单及产品表 时, 应先删除订单表, 然后再删除产品表. 否 则会出现错误.,删除表,SELECT au_fname , phone FROM pubs.dbo.authors WHERE phone like 415%,SELECT CompanyName, Address, City, PostalCode FROM Northwind.dbo.Customers WHERE PostalCode like A-EA-Z_,第2讲 课后练习参考答案,SELECT au_fname , phone FROM pubs.dbo.authors WHERE au_fname like Ala%,SELECT LastName, FirstName FROM Northwind.dbo. Employees WHERE FirstName like _ancy,SEL