《管理数据库表》PPT课件.ppt

《《管理数据库表》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《管理数据库表》PPT课件.ppt（52页珍藏版）》请在三一办公上搜索。

1、-SQL Server 2000,第三章 管理数据库表,管理数据库表,表是由行和列组成的。创建表的过程主要就是定义表的列的过程列的属性：表的列名在同一个表中具有惟一性，同一列的数据属于同一种数据类型。除了用列名和数据类型来指定列的属性外，还可以定义其它属性：NULL或NOT NULL属性和IDENTITY(自动增长)属性。!一个列不能同时具有NULL 属性和IDENTITY 属性只能二者选其一!一个表至少有一列但最多不超过1024 个列!每个数据库中最多可以创建200 万个表,管理数据库表,1、NULL或NOT NULL 如果表的某一列被指定具有NULL属性，那么就允许在插入数据时省略该列的值

2、。反之如果表的某一列被指定具有NOT NULL属性，那么就不允许在没有指定列缺省值的情况下插入省略该列值的数据行。在SQL Server中列的缺省属性是NOT NULL，要设置缺省属性为NULL或NOT NULL，可以在Enterprise Manager中修改数据库属性选项中的允许空为真或假,管理数据库表,2、IDENTITY IDENTITY属性可以使表的列包含系统自动生成的数字，这种数字在表中可以惟一标识表的每一行，即表中的每一行数据，在指定为IDENTITY属性的列上的数字均不相同。指定了IDENTITY属性的列称为IDENTITY列，当用IDENTITY属性定义一个列时，可以指定一个

3、初始值和一个增量。插入数据到含有IDENTITY列的表中时，初始值在插入第一行数据时，使用以后就由SQL Server根据上一次使用的IDENTITY值加上增量，得到新的IDENTITY值。如果不指定初始值和增量值，则其缺省值均为1。IDENTITY属性适用于INT、SMALLINT、TINYINT、DECIMAL、P,0、NUMERIC、P,0数据类型的列,管理数据库表,用CREATE TABLE命令创建表,CREATE TABLE database_name.owner.|owner.table_name(|column_name AS computed_column_expression

4、|,.n)ON filegroup|DEFAULT TEXTIMAGE_ON filegroup|DEFAULT:=column_name data_type DEFAULT constant_expression|IDENTITY(seed,increment)NOT FOR REPLICATION ROWGUIDCOL COLLATE.n,用CREATE TABLE命令创建表,例：创建一个商品信息表create table mydb.dbo.products3(p_id smallint identity(1000,1),/*商品序列号自动增长*/p_name char(10)not nu

5、ll,price money default 0.01,/*商品单价缺省值为0.01 元*/quantity smallint null,/*商品的数据*/sumvalue as price*quantity/*商品总价值=单价*数量*/)on primary,用Enterprise Manager创建表,用Enterprise Manager创建表,输入表名后单击OK 按钮即会将表保存到数据库中,局部临时表的表名前要使用符号#全局临时表的表名前要使用符号#以便与一般的表相区别！由于SQL Server会自动在其后面添加一个系统产生的12 位的数字后缀，因此临时表的表名最长只能指定116 个字

6、符,自定义数据类型,除了使用系统提供的数据类型外用户还可以根据需要用自定义的数据类型来定义表的列或声明变量用Enterprise Manager 创建用户自定义数据类型用存储过程Sp_addtype 创建用户自定义数据类型,用Enterprise Manager 创建自定义数据类型,用Enterprise Manager 创建用户自定义数据类型的方法是在Enterprise Manager中选择要创建用户自定义类型的数据库在数据库对象User Defined Data Types上单击右键从开始菜单中选择New User Defined Data Type选项,用存储过程Sp_addtype创

7、建自定义数据类型,sp_addtype typename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name 例如：定义生日数据类型exec sp_addtype birthday,datetime,not null定义地址数据类型exec sp_addtype address,varchar(100),not null定义身份证号码数据类型exec sp_addtype cardid,char(18),not null,删除用户自定义数据类型,可以在Enterprise Manager 中选择用户自定义类型后从

8、快捷菜单中选择Delete选项将其删除。可以使用系统存储过程Sp_droptype将其删除其语法如下：sp_droptype typename=typeexec sp_droptype cardid,用Enterprise Manager修改表,在Enterprise Manager中选择要进行改动的表，单击右键从快捷菜单中选择“设计表”选项，则会出现如下图的修改表结构对话框。可以在对话框中修改列的数据类型名称等属性或添加删除列。也可以指定表的主关键字约束。,用Enterprise Manager修改表,用ALTER TABLE命令修改,ALTER TABLE table_name ALTER

9、 COLUMN column_name new_data_type(precision,scale)COLLATE NULL|NOT NULL|ADD|DROP ROWGUIDCOL|ADD|column_name AS computed_column_expression,.n|WITH CHECK|WITH NOCHECK ADD,.n|DROP CONSTRAINT constraint_name|COLUMN column,.n|CHECK|NOCHECK CONSTRAINT ALL|constraint_name,.n NULL|NOT NULL 等等 请大家参考帮助,用ALTER

10、 TABLE命令修改,例：向已存的表中添加列。ALTER TABLE cj ADD java decimal(5,2)例：将已有的表中的列删除。ALTER TABLE cj DROP COLUMN java例：修改表中已有的列的数据类型。alter table cj ALTER COLUMN china nvarchar(5),查看表,查看表的属性在Enterprise Manager 中用右键单击要查看属性的表从快捷菜单中选择属性查看表中的数据在Enterprise Manager 中用右键单击要查看属性的表从快捷菜单中选择Open Table用系统存储过程Sp_help 查看表的信息用系统

11、存储过程查看表的约束用Sp_helpconstraint 存储过程查看约束用Sp_pkeys 存储过程查看主关键字约束用Sp_fkeys 存储过程查看外关键字约束,表的输入,输入单行：Insert into 表名（列名，。，列名）Values(列值，。，列值)例：insert into table_a(col_a,col_b,col_c)Values(2,william,2000 01 12),用DROP TABLE命令删除,DROP TABLE命令可以删除一个表和表中的数据，及其与表有关的所有索引、触发器、约束许可对象。与表相关的视图和存储过程，需要用DROP VIEW和DROP PROCE

12、DURE命令来删除。DROP TABLE 命令的语法如下：DROP TABLE table_name!不能删除系统表Delete命令可以用来清空表但是保留表结构 Delete table_name,数据完整性,数据完整性Data Integrity是指数据的精确性Accuracy和可靠性Reliability。它是应防止数据库中存在不符合语义规定的数据，和防止因错误信息的输入输出造成无效操作，或错误信息而提出的。数据完整性分为四类实体完整性Entity Integrity域完整性Domain Integrity参照完整性Referential Integrity用户定义的完整性User-def

13、inedIntegrity,数据完整性类型,数据完整性,实体完整性Entity Integrity实体完整性规定表的每一行在表中是惟一的.实体表中定义的UNIQUE PRIMARY KEY（唯一的主键约束）和IDENTITY（自动增长）约束就是实体完整性的体现，主要是确定行的唯一性域完整性Domain Integrity域完整性是指数据库表中的列必须满足某种特定的数据类型或约束.其中约束又包括取值范围精度等规定表中的CHECK FOREIGN KEY（外键）约束和DEFAULT NOT NULL（默认不为空）定义都属于域完整性的范畴参照完整性Referential Integrity参照完整性

14、是指两个表的主关键字和外关键字的数据应对应一致它确保了有主关键字的表中对应其它表的外关键字的行存在.即保证了表之间的数据的一致性表现为：禁止在从表中插入包含主表中不存在的关键字的数据行禁止修改外关键字而不修改主关键字列禁止删除在从表中的有对应记录的主表记录,用户定义的完整性,不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件。其中最主要的是规则Rule、缺省值Default、约束Constraint和触发器Trigger 下面将介绍约束、规则和缺省值,决定什么时候使用哪种类型的约束,创建和使用约束,约束Constrain

15、t是Microsoft SQL Server提供的自动保持数据库完整性的一种方法。定义了可输入表或表的单个列中的数据的限制条件。在SQL Server中有5 种约束：主关键字约束 Primary Key Constraint外关键字约束 Foreign Key Constraint惟一性约束 Unique Constraint检查约束 Check Constraint缺省约束 Default Constraint,主关键字约束,PRIMARY KEY 约束标识列或列集，这些列或列集的值唯一标识表中的行。在一个表中，不能有两行包含相同的主键值。不能在主键内的任何列中输入 NULL 值。在数据库中

16、 NULL 是特殊值，代表不同于空白和 0 值的未知值。建议使用一个小的整数列作为主键。每个表都应有一个主键。一个表中可以有一个以上的列组合，这些组合能唯一标识表中的行，每个组合就是一个候选键。语法：ALTER TABLE Table_nameAdd CONSTRAINT constraint_namePRIMARY KEY CLUSTERED|NONCLUSTEREDcolumn_name1,column_name2,column_name16 例：alter table student add constraint fk primary key(name,age),主关键字约束,例：CRE

17、ATE TABLE part_sample(part_nmbr int PRIMARY KEY,part_name char(30),part_weight decimal(6,2),part_color char(15)例：create table products(p_id char(8)not null,p_name char(10)not null,price money default 0.01,quantity smallint null,constraint pk_p_id primary key(p_id,p_name)on primary,外关键字约束,外关键字约束定义了表之

18、间的关系。当一个表中的一个列或多个列的组合，和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字。这样当在定义主关键字约束的表中更新列值时，其它表中有与之相关联的外关键字约束的表中的外关键字列，也将被相应地做相同的更新。与主关键字相同不能使用一个定义为TEXT或IMAGE数据类型的列，创建外关键字。外关键字最多由16个列组成，定义外关键字约束的语法如下ALTER TABLE Table_nameAdd CONSTRAINT constraint_nameFOREIGN KEY column_name1,column_name2,REFERENCES ref_table ref

19、_column1,ref_column2,ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION NOT FOR REPLICATION!临时表不能指定外关键字约束,外关键字约束,例：create table orders(order_id char(8),p_id char(8),p_name char(10),constraint pk_order_id primary key(order_id),foreign key(p_id,p_name)references products(p_id,p_name)on primary,惟一

20、性约束,惟一性约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值。惟一性约束指定的列可以有NULL属性，由于主关键字值是具有惟一性的，因此主关键字列不能再设定惟一性约束。惟一性约束最多由16个列组成。定义惟一性约束的语法如下：ALTER TABLE Table_nameAdd CONSTRAINT constraint_nameUNIQUE CLUSTERED|NONCLUSTEREDcolumn_name1,column_name2,column_name16,惟一性约束,例：定义一个员工信息表其中员工的身份证号具有惟一性create table employees(emp_

21、id char(8),emp_name char(10),emp_cardid char(18),constraint pk_emp_id primary key(emp_id),constraint uk_emp_cardid unique(emp_cardid)on primary,检查约束,检查约束对输入列或整个表中的值设置检查条件，以限制输入值。保证数据库的数据完整性，可以对每个列设置符合检查定义。检查约束的语法如下Alter table table_nameAdd CONSTRAINT constraint_nameCHECK NOT FOR REPLICATION(logical_

22、expression),检查约束,例：创建一个订货表其中定货量必须不小于10create table orders(order_id char(8),p_id char(8),p_name char(10),quantity smallint,constraint pk_order_id primary key(order_id),constraint chk_quantity check(quantity=10),)on primary,缺省约束,缺省约束通过定义列的缺省值，或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。SQL Server推荐使用缺省约束而不使用定义缺省值的方式，来

23、指定列的缺省值。定义缺省约束的语法如下Alter table table_nameAdd CONSTRAINT constraint_nameDEFAULT constant_expression FOR column_name例：constraint de_order_quantity default 100 for order_quantity,级联参考完整性,使对现有数据的约束检验失效,运用CHECK和FOREIGN KEY约束在添加新约束时使用WITH NOCHECK选项在现有的数据不改变的情况下使用在添加约束前可以改变现在的数据,USE NorthwindALTER TABLE db

24、o.Employees WITH NOCHECK ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY(ReportsTo)REFERENCES dbo.Employees(EmployeeID),使在载入新数据时的约束检验失效,运用于CHECK和FOREIGN KEY在以下情况下使用：数据符合约束载入不符合约束的新数据,USE NorthwindALTER TABLE dbo.Employees NOCHECK CONSTRAINT FK_Employees_Employees,规则,规则Rule 就是数据库中对存储在表的列或用户自定义数据类型中

25、的值的规定和限制。规则是单独存储的、独立的数据库对象。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响CHECK 约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个 CHECK 约束。CHECK 约束作为 CREATE TABLE 语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。规则和约束可以同时使用,创建、查看规则,用CREATE RULE 命令创建规则CREATE RULE rule_name AS condition_expression例：创建雇佣日期规则hire_date_rulecreate ru

26、le hire_date_ruleas hire_date=1980-01-01 and hire_date=getdate(),用Enterprise Manager 创建规则,在Enterprise Manager 中选择数据库对象Rules 单击右键从快捷菜单中选择New Rule,用Enterprise Manager 查看规则,在Enterprise Manager 中选择Rules 对象即可从右边的任务板中看到规则的大部分信息用存储过程Sp_helptext 查看规则sp_helptext objname=name例：查看规则hire_date_ruleexec sp_helpte

27、xt hire_date_rule,规则的绑定与松绑,创建规则后规则仅仅只是一个存在于数据库中的对象并未发生作用，需要将规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。用存储过程Sp_bindrule 绑定规则 sp_bindrule rulename=rule,objname=object_name,futureonly例：EXEC sp_binderule rule1,student.age 用存储过程Sp_unbindrule 解除规则的绑定 sp_unbindrule objname=object_name,futureonly例：EXEC sp_unbindrule

28、student.age,用Enterprise Manager 管理规则的绑定,在Enterprise Manager 中选择规则，单击右键从快捷菜单中选择“属性”选项，规则属性对话框中的“绑定列”按钮,删除规则,可以在Enterprise Manager 中选择规则，单击右键从快捷菜单中选择Delete选项，删除规则。也可使用DROP RULE 命令删除当前数据库中的一个或多个规则。其语法如下：DROP RULE rule_name,.n!在删除一个规则前必须先将与其绑定的对象解除绑定,缺省值,缺省值Default是往用户输入记录时没有指定具体数据的列中自动插入的数据。缺省值对象与ALTER

29、 TABLE或CREATE TABLE命令操作表时用DEFAULT选项指定的缺省值功能相似。但缺省值对象可以用于多个列或用户自定义数据类型.缺省值对象可以用于多个列或用户自定义数据类型。它的管理与应用同规则有许多相似之处表的一列或一个用户自定义数据类型也只能与一个缺省值相绑定,创建缺省值,用CREATE DEFAULT 命令创建缺省值CREATE DEFAULT default_name AS constant_expression例：创建生日缺省值birthday_defa create default birthday_defa as 1978-1-1用Enterprise Manager

30、 创建缺省值在Enterprise Manager 中选择数据库对象默认单击右键从快捷菜单中选择“新建默认”选项,创建缺省值,有两种使用默认值的方法：在 CREATE TABLE 中使用 DEFAULT 关键字创建默认定义，将常量表达式指派为列的默认值。这是首选的标准方法，也是定义默认值的更简明的方法。使用 CREATE DEFAULT 语句创建默认对象，然后使用 sp_bindefault 系统存储过程将它绑定到列上。,创建缺省值,例：USE pubs GO CREATE TABLE test_defaults(keycol smallint,process_id smallint DEFA

31、ULT SPID,-当前用户进程的服务器进程标识符 date_ins datetime DEFAULT getdate(),-当前系统的时间 mathcol smallint DEFAULT 10*2,-默认值 definition char1 char(3),char2 char(3)DEFAULT xyz)-默认值 GO,查看缺省值,用Enterprise Manager 查看缺省值在Enterprise Manager 中选择“默认”即可从右边的任务板中看到缺省值的大部分信息用存储过程Sp_helptext 查看缺省值使用Sp_helptext 存储过程可以查看缺省值的细节,缺省值的绑定

32、与松绑,用Enterprise Manager 管理缺省值的绑定在Enterprise Manager中选择要进行绑定设置的缺省值单击右键，从快捷菜单中选择“属性”选项，单击“绑定列”用存储过程Sp_bindefault 绑定缺省值sp_bindefault defname=default,objname=object_name,futureonly用存储过程Sp_unbindefault 解除缺省值的绑定Sp_unbindefault objname=object_name,futureonly,删除缺省值,可以在Enterprise Manager中选择缺省值单击右键从快捷菜单中选择“删除”选项删除缺省值也可以使用DROP DEFAULT 命令删除当前数据库中的一个或多个缺省值其语法如下：DROP DEFAULT default_name,.n,