数据库之表操作.doc

4.5.3允许空值约束 表操作目 录4.1设计表4设计表时需要确定的项目.41.基本数据类型4（1）数据类型4（2）数据类型的一些注意事项62.别名数据类型8（1）创建别名数据类型8（2）使用别名数据库。9（3）删除用户自定义类型。93.目录视图94.2 使用SSMS表101.创建表102.修改表103.查看表104.删除表104.3使用T-SQL管理表111.创建表112.修改表12（1）添加列12（2）修改列13（3）删除表13（4）重命名表143.查看表144.删除表144.4记录操作154.4.1使用SSMS进行记录操作154.4.2使用T-SQL进行记录操作151.使用T-SQL插入记录15(1)插入所有列15(2)插入指定列.152.用T-SOL修改记录16(1)修改单条记录.16(2)修改多条记录17(3)修改所有记录并指定多项修改.173.使用T-SQL删除记录17（1）删除指定记录。18（2）删除所有记录。184.5 SQL Sever 2005 中的数据库完整性204.5.1数据库完整性概述201.实体完整性202.域完整性203.引用完整性204.用户完整性214.5.2列约束和表约束214.5.3允许空值约束22234.1设计表设计表时需要确定的项目.(1) 表的名称.(2) 表中每一列的名称.(3) 表中每一列的数据类型和长度.(4) 表中的列中是否允许空值、是否唯一、是否要进行默认设置或添加用户定义约束。(5) 表中的关系，即确定哪些列是主键，哪些是外键。(6) 表中需要的索引的类型和需要建立索引的列。1. 基本数据类型（1）数据类型数据类型范围存储精确数字Bigint-922337203685477580892233720368547758078字节Int-214748364821474836474字节Smallint-32 786327852字节Tinyint02551字节Bit0、1、null不定DecimalNumeric-1038+11038-1517字节Money-922 337 203 685 477.5808922 337 203 685 477.58078字节Smallmoney-214 748.3648214 748.36474字节近似数值Float-1.79E+308-2.23E-308、0以及2.23E-3081.79E+30848字节Real-3.40E+38-1.18E-38、0、1.18E-383.40E+384字节日期和时间Datetime1753年1月1日9999年12月31日（精确到3.33毫秒）8字节Smalldatetime1900年1月1日2079年6月6日（精确到1分钟）4字节字符串（2）数据类型的一些注意事项l Bit类型.bit列为8bit或更少时作为1个字节储存.如果为9bit16bit,则这些列作为2个字节存储,以此类推.l Char与Varchar类型.如果列数据项的大小一致,则使用char.如果列数据项的大小差异相当大,则使用varchar.如果数据项大小相差很大,而且大小可能超过8000字节,请使用varchar(max)(231-1)l Binary与Varbinary类型.如果列数据项的大小一致,则使用binary.如果列数据项的大小差异相当大，则使用varbinary。当列数据条目超出8000字节时，请使用varbinary（max）。l 二进制数据类型。二进制数据由十六进制表示（十进制数245等于十六进制数F5）。l Image类型。Image数据列可以用来储存超过8kb的可变长度的二进制数据，如Microsoft word文档、Microsoft Excel电子表格、包含位图的图像、图形交换格式（gif0文件和联合图形专家组（JPEG）文件。l Text类型。Text数据类型的列可用于储存大于8kb的ASCII字符。例如，由于HTML文档均由ASCII字符组成且一般长于8kb，所以用浏览器查看之前应在SQL Server中存储在text列中。l Nchar、nvarchar和ntext类型。字符列宽度的定义不超过所储存的字符数据可能的最大长度，如果要在SQL Server中存储国际化字符数据，请使用nchar、nvarchar和ntext数据类型。l Unicode数据类型。Unicode数据类型需要相当于非unicode数据类型两倍的存储空间。l Numeric与decimal类型。在SQL Server中，numeric数据类型等价于decimal数据类型，如果数值超过货币数据范围，则可使用decimal数据类型代替。l Timestamp类型。Timestamp用于表示Sql Server在一行上的活动顺序，按二进制格式以递增的数字来表示。当表中的行发生变动时，用从DBTS的函数获得的当前数据库的时间戳值来更新时间戳。Timestamp数据与插入或修改数据的日期和时间无关。若要自动记录表中数据更改的时间，使用datetime或smalldatetime数据类型记录时间或触发器。l Uniqueidentifier类型。Uniqueidentifier以一个16位的十六进制数表示全局唯一标识符（GUID）。当需要在多行中唯一标识莫一行时可使用GUID，例如，可使用unique_identifiler数据类型定义一个客户标识代码列，已编辑公司来自多个国家/地区的总的客户名录。l Table类型。Table数据类型只能用于定义table类型的局部变量或用户定义函数的返回值。2.别名数据类型（1）创建别名数据类型Create type 类型名From 基本数据类型【null | not null】参考含义：l 类型名：别名数据类型或用户定义类型的名称。类型名称必须符合标识符的规则。l 基本数据类型：别名数据类型所基于的数据类型（decimal、int等）由SQl Server提供。l Null | not null：是否可以为空。例子Use webshopGoCreate type mydataFrom varchar(10) not null【提示】l 在SQL Server 2005中建议不使用储存过程sp_addtype创建别名数据库类型，也不使用sp_droptype删除别名数据类型。l 在SQL Server 2005中有别名数据类型和用户自定义数据类型之分，后者使用create assembly语句创建。l 如果别名数据类型是在model数据库中创建的，他将作用于所有用户定义的新数据库中；如果别名数据类型是在用户定义的数据库中创建的，则该数据类型只作用于此用户定义的数据库。（2）使用别名数据库。（3）删除用户自定义类型。使用drop type可以从systypes中删除用户定义的数据库类型，其基本语句格式如下：Drop type 类型名参考含义：类型是指已经定义好的别名数据类型的名称。例子：Use WebShopGoDrop type mydata3.目录视图4.2 使用SSMS表1.创建表【提示】l 尽可能地在创建表时正确的输入猎德信息。l 同一数据库中，列名不能相同。2.修改表3.查看表4.删除表【提示】l 数据库中的表删除后不能恢复。l 如果要删除的表与其他的标有依赖关系，则该表不能被删除。4.3使用T-SQL管理表1.创建表使用T-Sql语句创建表的操作的基本语句如下:Create table （【列级完整性约束条件】【列级完整性约束条件】。）例子：Use WebShopGoCreate table goods（G_ID char（6），G_name varchar(50),T_ID char(2),G_price float,G_Discount float,G_number smallint,G_ProduceDate datetime,g-Image varchar(100)，g_status varchar（10），g_Description varcharvarchar(1000)）【提示】l 表是数据库的组成对象，在进行创建表的操作之前，先要通过命令USE WebShop打开要操作的数据库。l 用户在选择表和列名称时不要使用SQL语言中的保留关键词，如select、create和insert等。l 在这里没有考虑表中的约束情况。2.修改表修改表的基本语句格式如下：Alter tableAlter column Add完整性约束Drop（1）添加列Alter table goods add g_Producer varchar(20)【提示】l 在alter table语句中使用add关键字增加列。l 不论表中是否已有数据，新增加的列一律为空值，且新增加的一列位于表结构的末尾。（2）修改列Alter table goods alter column g_ProduceDate char（10）【提示】l 在alter table 语句使用alter column关键字修改的数据类型或宽度。l 在“对象资源管理器”中展开表节点中的指定表节点后再展开【列】节点可以查看指定表中列的信息。（3）删除表用Alter table 语句删除列,可用drop column关键字.【例子】Alter table goods drop column g_Produce【提示】l 在使用alter table时，每次只能添加或删除一列。l 在添加列时，不需要带关键字column；在删除列时，在列名前要带上关键字column，因为在默认情况下，认为是删除约束。l 在添加列时，需要带数据类型和长度；在删除列时，不需要带数据类型和长度，只需指定列名。l 如果在该列定义了约束，在修改列时会进行限制，如果确实需要修改该列，线必须删除该列上的约束，然后再进行修改。（4）重命名表使用存储过程sp_rename可以更改当前数据库中表的名称，存储过程sp_rename基本语句格式如下：Sp_rename当前表名，【新表名】3.查看表Sp_help 【表名】4.删除表Drop table如：drop table good4.4记录操作4.4.1使用SSMS进行记录操作4.4.2使用T-SQL进行记录操作1.使用T-SQL插入记录使用Insert into语句可以向表中添加记录或者创建追加查询,插入单个记录的基本语句格式如下:Insert into ，Values (,)(1)插入所有列。Insert into goods values （020003，爱国者MP3-1G，2，128,0.8,20，2007-08-01，pImage/020003.gif,热点,容量G）(2)插入指定列.Insert into goods (g_ID,g_Name,t_ID,g_Discount,g_Number,g_ProduceDate,g_Status)Values (040002,杉杉西服，04，1288,0.9,20，2007-08-01，热点)【提示】l Insert 语句中的into可以省略。l 如果某些属性列在表名后的列名表没有出现，则新纪录在这些列上将取空值。但是要注意的是，在表定义时说明了not null的属性列不能去空值，否则系统会出现错误提示。l 如果没有指明任何列名，则新插入的记录必须在每个属性列上均有值。l 字符数据类型必须“”将其引起来。l 常量的顺序必须与指定的列名顺序保持一致。2.用T-SOL修改记录使用Update语句可以按照某个条件修改特定表中的字段值，其基本语句格式如下：UpdateSet =,=from where (1)修改单条记录.Update goodsSet g_Status=热点Where g_name=劲霸西服(2)修改多条记录Update goods Set g_Image=Images/+g_ImageWhere g_Image is not null【提示】l 如果不指定条件，则会修改所有的记录。l 如果加上条件is not null就可以保证对已有图片的商品进行修改。(3)修改所有记录并指定多项修改.Update goodsSet g_discount=0.8，g_ProduceDate=2007-07-01【提示】l 如果要修改多列,则在set语句后用”,”分隔各修改子句.l 这类一般在进行数据初始化时使用.l 修改记录时可以通过约束和触发器实现数据完整性.3.使用T-SQL删除记录使用Delete语句可以删除表中的记录,其基本语句如下:DeleteFrom【where】Delete 语句的功能是从表中删除满足where子句条件的所有记录。如果省略where子句，表示删除表中全部记录，但表的定义仍存在。也就是说，delete语句删除的是表中的数据，而不是关于表的定义。（1）删除指定记录。Delete From goodsWhere g_ID=040002Delete操作也是一次只能操作一个表，因此同样会遇到update操作中提到的数据不一致问题。比如商品号为040002商品被删除后，包含该商品的订单信息也应同时删除。【提示】l 如果是外键约束，则可以先将外键表中对应的记录删除，然后再删除主键表中的记录。l 记录删除后不能被恢复。（2）删除所有记录。Delete From goods该语句使goods成为空表，它删除了goods的所有记录。删除表中所有记录也可以使用truncate table语句来完成。Truncate table goods【提示】l Delete删除操作被当做是系统事务，删除操作可以被撤销。l Truncate table 则不是，删除操作不能被撤销。4.5 SQL Sever 2005 中的数据库完整性4.5.1数据库完整性概述数据库的完整性是指数据的准确性和一致性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作而提出的。数据完整性主要分为四类：实体完整性、域完整性、引用完整性和用户定义完整性。在SQL Server 2005 中可以通过空值约束、默认值定义、check约束、primary key约束、foreign key约束和unique约束等来实施数据库完整性。1.实体完整性实体完整性规定表的每一行在表中是唯一的.实体表中定义的索引、unique约束、primary key 约束和identity属性就是实体完整性的体现。2.域完整性域完整性是指数据库表中的列必须满足某种特定的数据类型或约束，其中约束又包括取值范围和精度等规定。表中的check约束、foreign key约束、default定义、not null和规则都属于域完整性的范畴。3.引用完整性引用完整性是指两个表的主关键字和外关键字的数据应对应一致。它确保了有主关键字的表中对应其他表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据库中扩散。引用完整性是创建在外关键字和主关键字之间或外关键字和唯一关键字之间的关系上的。引用完整性时，SQL Server将阻止用户执行下列操作。（1） 在主表中没有关联的记录时，将记录添加或更改到相关表中。（2） 更改主表中的值时，这会导致相关表中生成孤立记录。（3） 从主表中删除记录，但仍存在与该记录匹配的相关记录。Foreign key和check约束都属于引用完整性。4.用户完整性用户定义完