《知识点SQLServer表管理》由会员分享，可在线阅读，更多相关《知识点SQLServer表管理（75页珍藏版）》请在金锄头文库上搜索。
1、知识点SQLServer表管理SQLServer表管理表管理SQL Server 2000 数据类型创建表修改表删除表数据完整性SQLServer2000的数据的数据类型型系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则系系统提供的数据提供的数据类型型数字数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据bigint占8个字节，值的范围为 -263263-1int占4个字节，值的范围为 -231231-1smallint占2个字节，值的范围为 -3276832 767tinyint占1个字节，值的范围为 0255decimal (p,s)p为精度，最大38；s为小
2、数位数，0spnumeric (p,s)在SQL Server中，等价于decimal系系统提供的数据提供的数据类型（型（续）数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值float (n)从 -1.79E+308 到 1.79E+308 之间的浮点数字数据；n 为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1n53real从 3.40E+38 到 3.40E+38 之间的浮点数字数据，存储大小为 4 字节；SQL Server 中，real 的同义词为 float(24) money占8个字节，值的范围为 -922 337 203 685 477
3、.580 8 +922 337 203 685 477.580 7smallmoney占4个字节，值的范围为 -214 748.3648 214 748.3647系系统提供的数据提供的数据类型（型（续）日期和时间数据字符数据和 Unicode 字符数据datetime占8个字节，表示从1753年1月1日到 9999年12月31日的日期smalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char (n)存储字符个数为 08 000varchar (n)存储字符个数为 08 000text存储字符个数为 02GBnchar (n)存储字符个数为 04 000nv
4、archar(n)存储字符个数为 04 000ntext存储字符个数为 01GB系系统提供的数据提供的数据类型（型（续）二进制数据其他binary(n) 存储字节个数 08 000varbinary(n) 存储字节个数 08 000image存储字节个数 02 Gbit存储位数据cursor存储对游标的引用rowversion (timestamp)时间戳sql_variant可存储除 text、ntext、image、rowversion 之外的其他类型table存储函数返回结果uniqueidentifier存储 GUID 以及 UUID选择数据数据类型的指型的指导原原则若列值的长度相差很
5、大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用 tinyint 数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用 decimal 数据类型可以精确地控制精度如果行的存储量 超过8 000字节，使用 text 或者 image若不大于8 000字节，可使用char、varchar或者binary数据类型对于货币数据，使用 money 数据类型不要使用类型为 float 或者 real 的列作为主键因为它们不精确，所以不适合用于比较创建表建表数据表在企业管理器中创建表在查询分析器中创建表创建表建表数据表 数据库表是组成关系数据库最常见的数据库对象之一。 部件表ID颜色重量
6、AB123Blue10.5CD456Red8.0EF789Green9.25GH012Yellow8.0IJ341Blue1.0设计表的表的结构：构：字段的名称每个字段的数据类型和宽度字段是否允许为空是否有缺省值是否为标识列是否为主键、外键创建表建表创建表建表在企业管理器中创建表在查询分析器中创建表创建表建表结构的构的语法法CreateTable表名表名（列名列名1数据数据类型型列列级约束束1,列名列名2数据数据类型型列列级约束束1,.,表表级约束束1.)；创建表建表创建表建表Column nameColumn nameData typeData typeNULL or NULL or NOT
7、 NULLNOT NULLCREATE TABLE dbo.Categories(CategoryID CategoryName Description Picture int nvarchar(15)ntextimageNOT NULL,NOT NULL,NULL,NULL)举例创建表建表列名列名数据数据类型型长度度是否允是否允许为空空值默默认值说明明sidsnamesexbirthday必须小于今天IDCard必须惟一phoneemailPhone与email不能同时为空char6无主键varchar 20 无varchar2男男，女smalldatetime4无varchar50无var
8、char20无varchar20无利用企利用企业管理器管理器查看表的信息看表的信息利用系利用系统存存储过程程查看表的信息看表的信息Sp_helpSp_spaceusedSp_depends创建表建表修改表修改表在企业管理器中修改表在查询分析器中修改表修改表修改表在企业管理器中修改表使用企业管理器对表的列进行添加、修改和删除使用使用查询分析器修改表分析器修改表结构构修改表修改表ALTER TABLE ADD 约束束 DROP COLUMN ALTER COLUMN 约束束 ADD CONSTRAINT 约束名束名 DROP CONSTRAINT 约束名束名修改表修改表添加和添加和删除列除列添加列
9、语法：ALTER TABLE 表名表名 ADD 列名列名 数据数据类型型 NULL | NOT NULLALTER TABLE XSADD 奖学金等级 tinyint NULLGOALTER TABLE XSALTERCOLUMN 姓名 char(10)ALTERCOLUMN 出身时间 datetimeGOALTER TABLE XSALTERCOLUMN 姓名 char(10)GoALTER TABLE XSALTERCOLUMN 出身时间 datetimeGO修改表修改表添加和添加和删除列除列删除列语法：ALTER TABLE 表名表名 DROP COLUMN 列名列名 ,n不能删除以下列
10、n 正在复制的列n 用在索引中的列n 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列n 与 DEFAULT 定义关联或绑定到某一默认对象的列n 绑定到规则的列修改表修改表添加和添加和删除列除列ALTER TABLE CategoriesNew ADD Commission money null添加添加ALTER TABLE CategoriesNew DROP COLUMN Sales_date删除除Customer_name Sales_amount Sales_dateCustomerIDCommission删除表除表在企业管理器中删除表在查
11、询分析器中删除表删除表除表t在企业管理器中删除表l除去对象对话框删除表除表删除表语法：DROP TABLE 表名表名 DROP TABLE XS数据完整性数据完整性数据完整性类型实施数据完整性的途径定义约束数据完整性的数据完整性的类型型数据完整性数据完整性指的是数据库中存储的数据的正确性、有效性和正确性、有效性和一致性一致性数据安全性与完整性的区数据安全性与完整性的区别安全性：防止用户非法使用数据库，包括恶意破坏数据和越权存取数据。完整性：防止合法用户使用数据库时向数据库中加入不合语义的数据。数据完整性的数据完整性的类型型域完整性域完整性(列列)实体完整性体完整性 （行）（行）参照完整性参照完
12、整性(表之表之间)数据完整性的数据完整性的类型（型（1）在在SQL中通中通过PRIMARYKEY、UNIQUE或或IDENTITY实现域完整性域完整性是指一个字段的是指一个字段的输入有效性，是否允入有效性，是否允许为空空值。例：性例：性别（男，女）（男，女）实体完整性体完整性是指保是指保证表中所有的表中所有的记录的唯一。的唯一。SC tablegradecnosno9570888063121239500195001950109501095010外外码Student table主主码在在SQL中通中通过FOREIGNKEY实现李敏勇 刘晨 王敏 张立950019500295003 95010Sn
13、ame sno数据完整性的数据完整性的类型（型（2）参照完整性（引用完整性）参照完整性（引用完整性）在插入或在插入或删除数据除数据时，维护表表间数据一致性的手段。一般数据一致性的手段。一般建立在主建立在主码（主（主键）与外部）与外部码（外（外键）之）之间的关系。的关系。在在SQL中通中通过check、default、触触发器器(trigger)等等实现数据完整性的数据完整性的类型（型（3）用用户定定义的完整性的完整性体体现实际运用的运用的业务规则。例如：学生成例如：学生成绩0100分分职工年工年龄1860岁保保险金金+补贴基本工基本工资实施数据完整性的途径施数据完整性的途径约束（束（const
14、raint）规则（rule）默默认值（default）标识列（列（identity）触触发器（器（trigger）约束的类型决定使用何种约束约束束约束的束的类型型约束的用途是限制输入到表中的值的范围。约束是实施数据完整性的首选方法约约束的分束的分类类列列级约束：只束：只对一列起作用的一列起作用的约束。束。列列约束是束是对某一个特定列的某一个特定列的约束，包含在列定束，包含在列定义中，直接中，直接跟在跟在该列的其他定列的其他定义之后，用空格分隔，不必指定列名。之后，用空格分隔，不必指定列名。表表级约束：束：对表中的多列起作用的表中的多列起作用的约束。束。 表表约束与列定束与列定义相互独立，不包括
15、在列定相互独立，不包括在列定义中，通常用中，通常用于于对多个列一起多个列一起进行行约束，与列定束，与列定义用用,分隔，定分隔，定义表表约束束时必必须指出要指出要约束的那些列的名称。束的那些列的名称。约束的束的类型型 列级约束有六种：u主键Primary keyu外键foreign key u唯一 unique、u检查 checck u默认default u非空/空值 not null/ null约束的束的类型型表级约束有四种：l主键l外键l唯一l检查 约束的束的类型型PRIMARY KEY 约束UNIQUE 约束DEFAULT 约束CHECK约束FOREIGN KEY 约束级联参考完整性PRI
16、MARYKEY约束束PRIMARY KEY约束利用表中的一列或多列数据唯一地标识某一行数据每个表只有一个PRIMARY KEY约束PRIMARY KEY约束的值必须是唯一的不允许有空值SQL Server中最多可定义 16 列作为主键n直接在列名后增加关直接在列名后增加关键字字 PRIMARYKEYCREATE TABLE Student( sno char(5) PRIMARY KEY, sname varchar(20) not null, ); PRIMARYKEY约束束创建建sc表，其主表，其主码为（sno,cno）? CREATE TABLE sc (sno char(5) PRIM
17、ARY KEY， cno char(1) PRIMARY KEY， grade decimal(4,1) );PRIMARYKEY约束束n在在CREATE TABLE语句各列定句各列定义的最后加的最后加: :PRIMARY KEY() CREATE TABLE sc (sno char(5) ， cno char(1) ， grade decimal(4,1), PRIMARY KEY(sno,cno);n CREATE TABLE sc1 ( ., constraint PK_SC PRIMARY KEY(sno,cno);约束名束名n删除表上已定除表上已定义的主的主键ALTERTABLES
18、C1DROPCONSTRAINTPK_SC;n在没有定在没有定义主主键的表上，加上一个主的表上，加上一个主键ALTERTABLESCADDCONSTRAINTPK_SCprimarykey(sno,cno);删除和添加主除和添加主键UNIQUE约束束UNIQUE 约束主要被用来确保不受主键约束的列上的数据唯一性。UNIQUE约束的创建和使用指导：可以向表中的多列应用 UNIQUE 约束允许该列存在NULL 值向现有表应用 UNIQUE 约束时，会验证现有数据UNIQUE约束束UNIQUE 与主键约束的区别UNIQUE 约束主要用在非主键的一列或多列上限制数据惟一的情况，而主键是用于惟一标识一行
19、数据UNIQUE 约束允许该列上存在NULL值，而主键决不允许出现这种情况可以在一个表上设置多个UNIQUE 约束，而一个表上只能设置一个主键UNIQUE约束束UNIQUE 约束的操作方法在创建表时定义UNIQUE约束在已经有数据但没有重复值的列或列的集合上添加UNIQUE约束修改或删除表上已定义的UNIQUE约束n在在创建表建表时定定义UNIQUE约束束lCREATE TABLE test1 ( . , tname varchar(20) unique, tmount int );lCREATE TABLE test1 (. , tname varchar(8) constraint UNI
20、Q_ TNAME unique, tmount int );创建建UNIQUE约束束列列级UNIQUEUNIQUE约束束n在在创建表建表时定定义UNIQUE约束束lCREATE 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)CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)要求
21、：要求：在在even_type和和even_time上共同上共同建立惟一建立惟一约束束表表级UNIQUEUNIQUE约束束n向向test1表中的表中的tname列添加列添加UNIQUE约束束lALTERTABLEtest1ADDCONSTRAINTUNIQ_TNAMEUNIQUE(tname)n删除表上已定除表上已定义的的UNIQUE约束束lALTERTABLEtest1DROPCONSTRAINTUNIQ_TNAME添加和添加和删除除UNIQUE约束束DEFAULT约束束如果一个列的值在 插入时没有指定，DEFAULT 约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运
22、行时的值语法：法：CONSTRAINT 约束名束名 DEFAULT 约束表达式束表达式 DEFAULT约束束l在创建表时使用default属性CREATE TABLE userInfo( . country varchar(50) not null DEFAULT China , )l为已经创建好的表添加default属性USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName 应用 DEFAULT 约束的几种情况CHECK约束束CHECK约束通过检查输入
23、表列的值来维护值域的完整性。CHECK约束通过对一个逻辑表达式的结果进行判断来对数据进行检查。age int CHECK(age=18 and age=0andgrade=0andgrade=0andgrade=100);创建建CHECK约束束lCreate Table Student ( sno char(5) not null, birthdate datetime not null schooldate datetime not null , primary key (sno), );创建建CHECK约束束表表级CHECK约束束check(birthdategetdate三三),chec
24、k(birthdate=0andgmark=100)添加和添加和删除除CHECK约束束CHECK约束束应用CHECK 约束的注意事项一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。FOREIGNKEY约束束应用FOREIGN KEY约束主要用来维护两个表之间的一致性关系。当一行新的数据加入到表格中，或对表格中已经存在的外键上的数据进行修改时新的数据取值必须：存在于另一张表的主键上为NULL当主键所在表的数据被另一张表的外键引用时，用户无法对主键的数据进行修改或删除。n创建一个新的建
25、一个新的employee表，并向表，并向该表中的表中的dno列添加列添加FOREIGNKEY约束束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);FOREIGNKEY约束束FOREIGN KEY约束束表表employee和和department的参照关的参照关系系父表父表子表子
26、表FOREIGNKEY约束束 CREATETABLEemployee(enochar(4)notnullPRIMARYKEY, enamevarchar(8)notnull, sexchar(2)notnull, ageint, marrychar(1), titlechar(10), dnochar(2), FOREIGNKEY(dno)REFERENCESdepartment(dno);注意：注意：说明明dno为employee的外的外键时，department中的中的dno必必须已被已被说明明为主主键。FOREIGNKEY约束束FOREIGN KEY约束束CREATETABLEemplo
27、yee(enochar(4)notnullPRIMARYKEY,enamevarchar(8)notnull,sexchar(2)notnull,ageint,marrychar(1),titlechar(10),dnochar(2),CONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno);FOREIGNKEY约束束FOREIGN KEY约束束n删除表上已定除表上已定义的外的外键ALTERTABLEEMPLOYEEDROPCONSTRAINTFRNKEY_DNO;n在没有定在没有定义外外键的表上添加外的表上添加外键ALTERTA
28、BLEEMPLOYEEADDCONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno);FOREIGNKEY约束束FOREIGNKEY 约束束FOREIGN KEY约束的默认实现策略向外向外键所在表（所在表（employeeemployee）插入一行新元插入一行新元组，如，如输入外入外键的的值不不满足参照完整性足参照完整性约束束规则，则系系统拒拒绝。修改外修改外键所在表（所在表（employeeemployee）的元的元组，如所修改，如所修改外外键的的值不不满足参照完整性足参照完整性约束束规则，则系系统拒拒绝。修改或修改或删除父表（
29、除父表（departmentdepartment）的主的主键，仅当子当子表表( (employee)employee)中没有任何元中没有任何元组外外键值与其相同与其相同时，系系统才才执行行删除操作，否除操作，否则拒拒绝。向外向外键所在表（所在表（employee）插入一行新元插入一行新元组FOREIGNKEY 约束束修改外修改外键所在表（所在表（employee）的元的元组FOREIGNKEY 约束束删除父表（除父表（department）的主的主键dnoFOREIGNKEY 约束束修改父表（修改父表（department）的主的主键dno01FOREIGNKEY 约束束级联引用完整性引用完整
30、性允允许对父表某元父表某元组主主键值删除或更新除或更新操作，相操作，相应外外键所所在表的具有在表的具有该外外键值的元的元组也将被也将被删除或更新，保除或更新，保证参参照完整性。照完整性。默默认情况下，没有情况下，没有级联。级联引用完整性引用完整性删除除级联级联引用完整性引用完整性更新更新级联01另另见P198P198例及例及P200P200使用关系使用关系图管理外管理外键约束束级联引用完整性引用完整性决定使用何种决定使用何种约束束完整性完整性类型型约束束类型型描述描述域域DEFAULTDEFAULT如果在如果在INSERTINSERT语句中未句中未显式提供式提供值，则指定指定为列提供的列提供的
31、值CHECKCHECK指定列中可接受的数据指定列中可接受的数据值REFERENTIALREFERENTIAL( (通常使用外通常使用外键) )基于其他表中的列的基于其他表中的列的值，指定可接受的，指定可接受的用于更新的数据用于更新的数据值实体体PRIMARY KEYPRIMARY KEY惟一惟一标识每一列，确保用每一列，确保用户没有没有输入重入重复的复的值。同。同时创建一个索引以增建一个索引以增强性能。性能。不允不允许空空值UNIQUEUNIQUE确保在非主确保在非主键列中不列中不输入重复入重复值，并，并创建一个索引以增建一个索引以增强性能。允性能。允许空空值引用引用FOREIGN KEYFO
32、REIGN KEY定定义一列或多列的一列或多列的值与同表或其他表中与同表或其他表中主主键的的值匹配匹配CHECKCHECK基于同表中其他列的基于同表中其他列的值，指定列中可接，指定列中可接受的数据受的数据值使用使用Identity属性属性使用使用 Identity Identity 属性的要求属性的要求每个表只能有一个每个表只能有一个标识列列只用在只用在 integerinteger、numeric numeric 和和 decimaldecimal数据数据类型上。若用于型上。若用于 numericnumeric，小数位数必小数位数必须为0 0标识列不能列不能进行更新操作行更新操作标识列不允列不允许空空值回回顾掌握创建和管理数据库表的方法掌握数据完整性控制，理解表间关系和约束能根据关系图，完成数据类型以及数据库表的设计
