第4讲 管理表.ppt

《第4讲 管理表.ppt》由会员分享，可在线阅读，更多相关《第4讲 管理表.ppt（34页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

3、er自动进行数据检验.常用数据类型主要有:整数型:int,smallint,bigint 浮点型:numeric,flo at(n),r eal字符型:char(n),v ar char(n),nchar(n)日期时间型:smalldat etime,dat e,time数据类型 约束语句是表定义语句的重要组成部分.通过约束机制,可使数据库引擎强制控制数据内容的完整性.约束语句可以在列或表两个级别完成.例如,将 primary key 约束可以作用在列上,若 primary key 约束结合多个字段,则只能在表级别建立.使用时,约束优先于 DML 触发过程、规则和默认.约束 not null

4、非空约束-不允许空值 primary key 主键约束-识别表的记录-主键值唯一,非空 check 检查约束-限制列值范围-操作数据时,系统自动验证数据 unique 唯一性约束-值唯一-该约束可建在表上,可用于有空值的列 default 默认值约束-默认的值-常用的默认值 getdate(),newid()foreign key 外键约束-该键值参照另一表键值-另一表(主表)的值必须存在唯一 约束的种类PK 客户编号 公司名称 联系人 联系人称呼 地址 城市 行政区 邮政编码 国家地区 电话 传真 订单编号FK 客户编号 员工编号 订单日期 要货日期 送货日期 送货方式 运费 收货人 送货地

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

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

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

8、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 student(id char(7),name char(10),sex char(2),birthday smalldatetime,birth_place varchar(50),clas

10、s 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)null,class char(10)USE pubsCREATE TABLE dbo.客户(客户编号 int primary key,客户名称 char(50)not Null,住址 char(50),生日 smalldat

11、etime,电话 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)例题-下列代码测试“建立日期”字段是否输入默认值.USE pubsINSERT 客户(客户编号,客户名称,生日)V ALUES(1001,Candy,1980/04/22)GO SELECT*FROM 客户CREATE TABLE 产品(产品编号 int primary

12、key,产品名称 varchar(30);CREATE TABLE 订单(订单编号 int,产品编号 int foreigh key references 产品(产品编号)on delete no action,订单数量 int，订单日期 datetime default Getdate();下面的范例使用订单表与产品表来说明如何用代码建立”外键”.首先,产品表有”主键”,才可用订单表的”产品编号”字段为参考来建立”外键”.“外键”不一定要参考另一个表的”主键”,也可以定义为参考其他表中 unique 条件约束的列.”外键”可包含 null 值.同时,复合”外键”的任何列包含了 null 值,

13、将会略过数据验证工作.若要确保输入的数据都符合”外键”要求,可对所有参与的列指定非空.例题 在执行创建产品信息表的操作时，指定产品编号为主键值，并且创建一个聚集索引.create table products(id char(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)增加、删除和修改列 例题 在雇员信息表中增加、删除和修改字段的

14、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 TABLE employees ADD salary int null DROP column age ALTER column memo varchar(200)null 若要修改表的定义并在现有的表中新增列,操作必须在该列允许 Null 值或包含 Default 约束的条件下进行.由于新的列被加入表中,数据库引

15、擎会针对表中的每条现有数据将值插入该列内.因此,当列被加入表中时,替该列加入 Default 定义会很有用或者指定该列允许 Null 值.不满足这些条件,将返回错误信息.如果需要增加非空列呢?增加新列的限制 用户可以删除现有表中的列,但具有下列特性的列将无法删除.-用于索引的列.-用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 条件约束的列.-与 Default 定义建立关联,或关联至默认对象的列.-关联至规则的列.-注册成全文检索支持的列.-作为表的全文检索索引键的列.删除列的限制USE pubsALTER TABLE 客户 ADD 更新日期 Dateti

16、me default GetDate(),身份证号码 varchar(20)CONSTRAINT 身份证号码 UNIQUE GOALTER TABLE 客户 ALTER COLUMN 客户名称 nvarchar(30),身份证号码 varchar(18)GOALTER TABLE 客户 drop COLUMN 生日例题 如果需要修改约束呢?查看表格的定义 查看表格中的数据 查看表格与其它数据库对象的依赖关系 利用系统存储过程 sp_help 查看表的信息 例如 sp_help titles sp_helpdb pubs exec sp_helpdb 演示 查看表 DROP TABLE 语句可以

17、删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象.其语法形式如下：DROP TABLE table_name,table_name 使用 DROP TABLE 语法时,若要删除的表包含“外键”,则须先删除表的“外键”,或是删除”外键”所关联的表.例如,若要删除前面建立的订单及产品表时,应先删除订单表,然后再删除产品表.否则会出现错误.删除表SELECT au_fname,phone FROM pubs.dbo.authorsWHERE phone like 415%SELECT CompanyName,Address,City,PostalCode FROM Northwi

18、nd.dbo.CustomersWHERE PostalCode like A-EA-Z_ 第2讲 课后练习参考答案SELECT au_fname,phone FROM pubs.dbo.authorsWHERE au_fname like Ala%SELECT LastName,FirstNameFROM Northwind.dbo.EmployeesWHERE FirstName like _ancySELECT CustomerID,ContactName FROM Northwind.dbo.CustomersWHERE CustomerID like B%AND ContactName like F-L%SELECT ProductID,ProductNumer,Name,Color,StandardCostFROM AdventureWorks2008.Production.ProductWHERE ProductNumer like BK-%AND Color=Silver AND NOT StandardCost 400