《SQLServer表》PPT课件.ppt

SQL Server 表管理,SQL Server 2000 数据类型创建表修改表删除表数据完整性,SQL Server 2000 的数据类型,系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则,系统提供的数据类型,数字数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据,系统提供的数据类型（续）,数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值,系统提供的数据类型（续）,日期和时间数据字符数据和 Unicode 字符数据,系统提供的数据类型（续）,二进制数据其他,选择数据类型的指导原则,若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用 tinyint 数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用 decimal 数据类型可以精确地控制精度如果行的存储量 超过8 000字节，使用 text 或者 image若不大于8 000字节，可使用char、varchar或者binary数据类型对于货币数据，使用 money 数据类型不要使用类型为 float 或者 real 的列作为主键因为它们不精确，所以不适合用于比较,创建表,数据表在企业管理器中创建表在查询分析器中创建表,创建表,数据表 数据库表是组成关系数据库最常见的数据库对象之一。,设计表的结构：字段的名称每个字段的数据类型和宽度字段是否允许为空是否有缺省值是否为标识列是否为主键、外键,创建表,创建表,在企业管理器中创建表,在查询分析器中创建表创建表结构的语法 Create Table 表名（列名1 数据类型 列级约束1,列名2 数据类型 列级约束1,.,表级约束1.)；,创建表,创建表,举例,创建表,利用企业管理器查看表的信息利用系统存储过程查看表的信息Sp_helpSp_spaceusedSp_depends,创建表,修改表,在企业管理器中修改表在查询分析器中修改表,修改表,在企业管理器中修改表使用企业管理器对表的列进行添加、修改和删除,使用查询分析器修改表结构,修改表,ALTER TABLE ADD 约束 DROP COLUMN ALTER COLUMN 约束 ADD CONSTRAINT 约束名 DROP CONSTRAINT 约束名,修改表添加和删除列,添加列语法：ALTER TABLE 表名 ADD 列名 数据类型 NULL|NOT NULL,ALTER TABLE XSADD 奖学金等级 tinyint NULLGO,ALTER TABLE XSALTER COLUMN 姓名 char(10)ALTER COLUMN 出身时间 datetimeGO,ALTER TABLE XSALTER COLUMN 姓名 char(10)GoALTER TABLE XSALTER COLUMN 出身时间 datetimeGO,修改表添加和删除列,删除列语法：ALTER TABLE 表名 DROP COLUMN 列名,n不能删除以下列 正在复制的列 用在索引中的列 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列 与 DEFAULT 定义关联或绑定到某一默认对象的列 绑定到规则的列,修改表添加和删除列,ALTER TABLE CategoriesNew ADD Commission money null,添加,ALTER TABLE CategoriesNew DROP COLUMN Sales_date,删除,Customer_name,Sales_amount,Sales_date,Customer ID,Commission,删除表,在企业管理器中删除表在查询分析器中删除表,删除表,在企业管理器中删除表除去对象对话框,删除表,删除表语法：DROP TABLE 表名,DROP TABLE XS,数据完整性,数据完整性类型实施数据完整性的途径定义约束,数据完整性的类型,数据完整性指的是数据库中存储的数据的正确性、有效性和一致性数据安全性与完整性的区别安全性：防止用户非法使用数据库，包括恶意破坏数据和越权存取数据。完整性：防止合法用户使用数据库时向数据库中加入不合语义的数据。,数据完整性的类型,数据完整性的类型（1）,在SQL中通过PRIMARY KEY、UNIQUE或IDENTITY实现,域完整性是指一个字段的输入有效性，是否允许为空值。例：性别（男，女）,实体完整性是指保证表中所有的记录的唯一。,SC table,Student table,在SQL中通过FOREIGN KEY实现,数据完整性的类型（2）,参照完整性（引用完整性）在插入或删除数据时，维护表间数据一致性的手段。一般建立在主码（主键）与外部码（外键）之间的关系。,在SQL中通过check、default、触发器(trigger)等实现,数据完整性的类型（3）,用户定义的完整性体现实际运用的业务规则。例如：学生成绩 0100分 职工年龄 1860岁 保险金+补贴基本工资,实施数据完整性的途径,约束（constraint）规则（rule）默认值（default）标识列（identity）触发器（trigger）,约束的类型决定使用何种约束,约 束,约束的类型,约束的用途是限制输入到表中的值的范围。约束是实施数据完整性的首选方法,约束的分类,列级约束：只对一列起作用的约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名。表级约束：对表中的多列起作用的约束。表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用,分隔，定义表约束时必须指出要约束的那些列的名称。,约束的类型,列级约束有六种：主键Primary key外键foreign key 唯一 unique、检查 checck 默认default 非空/空值 not null/null,约束的类型,表级约束有四种：主键外键唯一检查,约束的类型,PRIMARY KEY 约束UNIQUE 约束DEFAULT 约束CHECK约束FOREIGN KEY 约束级联参考完整性,PRIMARY KEY 约束,PRIMARY KEY约束利用表中的一列或多列数据唯一地标识某一行数据每个表只有一个PRIMARY KEY约束PRIMARY KEY约束的值必须是唯一的不允许有空值SQL Server中最多可定义 16 列作为主键,直接在列名后增加关键字 PRIMARY KEYCREATE TABLE Student(sno char(5)PRIMARY KEY,sname varchar(20)not null,);,PRIMARY KEY 约束,创建sc表，其主码为（sno,cno）,?,CREATE TABLE sc(sno char(5)PRIMARY KEY，cno char(1)PRIMARY KEY，grade decimal(4,1);,PRIMARY KEY 约束,在CREATE TABLE语句各列定义的最后加:PRIMARY KEY()CREATE TABLE sc(sno char(5)，cno char(1)，grade decimal(4,1),PRIMARY KEY(sno,cno);,CREATE TABLE sc1(.,constraint PK_SC PRIMARY KEY(sno,cno);,约束名,删除表上已定义的主键 ALTER TABLE SC1 DROP CONSTRAINT PK_SC;在没有定义主键的表上，加上一个主键 ALTER TABLE SC ADD CONSTRAINT PK_SC primary key(sno,cno);,删除和添加主键,UNIQUE 约束,UNIQUE 约束主要被用来确保不受主键约束的列上的数据唯一性。UNIQUE约束的创建和使用指导：可以向表中的多列应用 UNIQUE 约束允许该列存在NULL 值向现有表应用 UNIQUE 约束时，会验证现有数据,UNIQUE 约束,UNIQUE 与主键约束的区别UNIQUE 约束主要用在非主键的一列或多列上限制数据惟一的情况，而主键是用于惟一标识一行数据UNIQUE 约束允许该列上存在NULL值，而主键决不允许出现这种情况可以在一个表上设置多个UNIQUE 约束，而一个表上只能设置一个主键,UNIQUE 约束,UNIQUE 约束的操作方法在创建表时定义UNIQUE约束在已经有数据但没有重复值的列或列的集合上添加UNIQUE约束修改或删除表上已定义的UNIQUE约束,在创建表时定义UNIQUE约束CREATE TABLE test1(.,tname varchar(20)unique,tmount int);CREATE TABLE test1(.,tname varchar(8)constraint UNIQ_ TNAME unique,tmount int);,创建UNIQUE 约束,列级UNIQUE约束,在创建表时定义UNIQUE约束CREATE TABLE test2(even_id int primary key,even_name char(20),even_type char(20),even_time datetime,),创建UNIQUE 约束,CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time),要求：在even_type和even_time上共同建立惟一约束,表级UNIQUE约束,向 test1表中的 tname 列添加 UNIQUE 约束 ALTER TABLE test1 ADD CONSTRAINT UNIQ_ TNAME UNIQUE(tname)删除表上已定义的UNIQUE约束ALTER TABLE test1 DROP CONSTRAINT UNIQ_ TNAME,添加和删除UNIQUE 约束,DEFAULT 约束,如果一个列的值在 插入时没有指定，DEFAULT 约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运行时的值语法：CONSTRAINT 约束名 DEFAULT 约束表达式,DEFAULT 约束,在创建表时使用default属性,CREATE TABLE userInfo(.country varchar(50)not null DEFAULT China,),为已经创建好的表添加default属性,USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName,应用 DEFAULT 约束的几种情况,CHECK 约束,CHECK约束通过检查输入表列的值来维护值域的完整性。CHECK约束通过对一个逻辑表达式的结果进行判断来对数据进行检查。age int CHECK(age=18 and age=65)可在一列上设置多个CHECK约束，一个CHECK约束可应用多个列。,CHECK 约束,CHECK约束的操作方法在创建表时定义CHECK约束在已经建立的表上添加CHECK约束修改或删除表上已定义的CHECK约束,在创建SC表时定义CHECK约束 Create Table SC(sno char(5)not null,cno char(1)not null,grade decimal(4,1)primary key(sno,cno);,创建CHECK 约束,check(grade=0 and grade=100),Create Table SC(sno char(5)not null,cno char(1)not null,grade decimal(4,1),primary key(sno,cno),check(grade=0 and grade=0 and grade=100);,创建CHECK 约束,Create Table Student(sno char(5)not null,birthdate datetime not null schooldate datetime not null,primary key(sno),);,创建CHECK 约束,表级CHECK约束,check(birthdate getdate(),check(birthdate schooldate),列级CHECK约束,删除SC表中的 CHECK 约束ALTER TABLE SC DROP CONSTRAINT chk_gmk 向SC表中添加CHECK 约束ALTER TABLE SC ADD CONSTRAINT chk_gmk check(gmark=0 and gmark=100),添加和删除CHECK 约束,CHECK 约束,应用CHECK 约束的注意事项一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。,FOREIGN KEY 约束,应用FOREIGN KEY约束主要用来维护两个表之间的一致性关系。当一行新的数据加入到表格中，或对表格中已经存在的外键上的数据进行修改时新的数据取值必须：存在于另一张表的主键上为NULL当主键所在表的数据被另一张表的外键引用时，用户无法对主键的数据进行修改或删除。,创建一个新的 employee 表，并向该表中的 dno列添加 FOREIGN KEY 约束CREATE TABLE employee(eno char(4)not null PRIMARY KEY,ename char(8)not null,sex char(2)not null,age integer,marry char(1),title char(10),dno char(2)REFERENCES department(dno);,FOREIGN KEY 约束,FOREIGN KEY约束,表employee和department的参照关系,父表,子表,FOREIGN KEY 约束,CREATE TABLE employee(eno char(4)not null PRIMARY KEY,ename varchar(8)not null,sex char(2)not null,age int,marry char(1),title char(10),dno char(2),FOREIGN KEY(dno)REFERENCES department(dno);,注意：说明dno为employee的外键时，department中的dno必须已被说明为主键。,FOREIGN KEY 约束,FOREIGN KEY约束,CREATE TABLE employee(eno char(4)not null PRIMARY KEY,ename varchar(8)not null,sex char(2)not null,age int,marry char(1),title char(10),dno char(2),CONSTRAINT FRNKEY_DNO FOREIGN KEY(dno)REFERENCES department(dno);,FOREIGN KEY 约束,FOREIGN KEY约束,删除表上已定义的外键 ALTER TABLE EMPLOYEE DROP CONSTRAINT FRNKEY_DNO;在没有定义外键的表上添加外键 ALTER TABLE EMPLOYEE ADD CONSTRAINT FRNKEY_DNO FOREIGN KEY(dno)REFERENCES department(dno);,FOREIGN KEY 约束,FOREIGN KEY 约束,FOREIGN KEY约束的默认实现策略向外键所在表（employee）插入一行新元组，如输入外键的值不满足参照完整性约束规则，则系统拒绝。修改外键所在表（employee）的元组，如所修改外键的值不满足参照完整性约束规则，则系统拒绝。修改或删除父表（department）的主键，仅当子表(employee)中没有任何元组外键值与其相同时，系统才执行删除操作，否则拒绝。,向外键所在表（employee）插入一行新元组,FOREIGN KEY 约束,修改外键所在表（employee）的元组,FOREIGN KEY 约束,删除父表（department）的主键dno,FOREIGN KEY 约束,修改父表（department）的主键dno,01,FOREIGN KEY 约束,级联引用完整性,允许对父表某元组主键值删除或更新操作，相应外键所在表的具有该外键值的元组也将被删除或更新，保证参照完整性。默认情况下，没有级联。,级联引用完整性,删除级联,级联引用完整性,更新级联,01,另见P198例及P200使用关系图管理外键约束,级联引用完整性,决定使用何种约束,使用 Identity 属性,使用 Identity 属性的要求每个表只能有一个标识列只用在 integer、numeric 和 decimal数据类型上。若用于 numeric，小数位数必须为0标识列不能进行更新操作标识列不允许空值,回 顾,掌握创建和管理数据库表的方法掌握数据完整性控制，理解表间关系和约束能根据关系图，完成数据类型以及数据库表的设计,