数据表的创建及管理

1、 数据表的数据表的 创建和管理创建和管理1 1、数据类型、数据类型 使用使用SQL ServerSQL Server创建数据库中的表时，要对表创建数据库中的表时，要对表中的每一列定义一种数据类型，数据类型决定了表中的每一列定义一种数据类型，数据类型决定了表中的某一列可以存放什么数据。中的某一列可以存放什么数据。 除了定义表需要指定数据类型外，使用视图、除了定义表需要指定数据类型外，使用视图、存储过程、变量、函数等都需要用到数据类型。存储过程、变量、函数等都需要用到数据类型。 SQL ServerSQL Server提供了丰富的系统定义的数据类型，提供了丰富的系统定义的数据类型，用户还可以在此基

2、础上自己定义数据类型用户还可以在此基础上自己定义数据类型 1.1 1.1 整型数据类型整型数据类型1. bigint1. bigint类型类型 存储大小存储大小: 8: 8个字节，取值范围个字节，取值范围: -2: -263632 26363-1-12. int2. int类型类型 存储大小存储大小: 4: 4个字节，取值范围个字节，取值范围: -2: -23131223131-1-13. smallint3. smallint类型类型 存储大小存储大小: 2: 2个字节，取值范围个字节，取值范围: -2: -215152 21515-1-14. tinyint4. tinyint类型类型 存

3、储大小存储大小: 1: 1个字节，取值范围个字节，取值范围: 0: 0255255 1.2 1.2 定点数据类型定点数据类型 定点数据类型用于表示定点实数，包括定点数据类型用于表示定点实数，包括numericnumeric和和decimaldecimal类型。类型。 NumericNumeric等价于等价于decimaldecimal。格式：格式： decimal(p, s)decimal(p, s) numeric(p, s) numeric(p, s)说明说明: : p: p: 表示精度，指定小数点左边和右边十进制数字的最大表示精度，指定小数点左边和右边十进制数字的最大位数，取值在位数，取

4、值在1 1到到3838之间，缺省值为之间，缺省值为1818； s: s: 指定小数点右边十进数的最大位数，取值在指定小数点右边十进数的最大位数，取值在0 0到到p p之间，之间，缺省值为缺省值为0 0。 表示范围表示范围: -10: -103838+1+110103838-1-1 精度精度: decimal: decimal和和numericnumeric类型数据的存储字节数与精度类型数据的存储字节数与精度p p有关。具体如下表有关。具体如下表: : 精度p存储字节数195101992028132938171.3 1.3 浮点数据类型浮点数据类型 浮点数据类型采用科学计数法存储十进制小数，浮点

5、数据类型采用科学计数法存储十进制小数，包括包括realreal和和floatfloat数据类型。数据类型。尾数位数尾数位数n精度精度存储字节数存储字节数1247位位42553或省略或省略15位位8n 表示范围表示范围: -1.79E+3081.79E+3081. float1. float类型类型格式格式: : float(n)float(n)说明说明: : n: n: 科学记数法尾数的位数，具体如下表：科学记数法尾数的位数，具体如下表：2. real2. real类型类型 存储大小存储大小: 4: 4个字节个字节 取值范围取值范围: -3.40E+38: -3.40E+383.40E+383

6、.40E+38 精度精度: 7: 7位位 在在SQL ServerSQL Server中，中，realreal的同义词为的同义词为float(24)float(24)。 分为分为UnicodeUnicode字符数据类型和非字符数据类型和非UnicodeUnicode字符数据类型字符数据类型. .非非UnicodeUnicode字符数据类型字符数据类型: : 允许使用由特定字符集定义的字符。允许使用由特定字符集定义的字符。字符集在安装字符集在安装SQL ServerSQL Server时选择，不能更改。时选择，不能更改。非非UnicodeUnicode字符数据类型字符数据类型: char: ch

7、ar、varcharvarchar和和texttext。 UnicodeUnicode字符数据类型字符数据类型: : 可存储由可存储由UnicodeUnicode标准定义的任何字符，标准定义的任何字符，包含由不同字符集定义的所有字符。需要相当于非包含由不同字符集定义的所有字符。需要相当于非UnicodeUnicode数据数据类型两倍的存储空间。类型两倍的存储空间。UnicodeUnicode字符数据数据类型包括字符数据数据类型包括: nchar: nchar、nvarcharnvarchar和和ntextntext。对。对于存储来源于多种字符集的字符的列，可采用这些数据类型。于存储来源于多种字

8、符集的字符的列，可采用这些数据类型。 分为固定长度和可变长度字符数据类型。分为固定长度和可变长度字符数据类型。1.4 字符型字符型charchar类型类型格式格式: : char(n)char(n)功能功能: : 定义长度为定义长度为n n个字节的个字节的固定长度固定长度非非UnicodeUnicode字符数据，字符数据，每个字符占一个字节。每个字符占一个字节。说明说明: : n:18000 n:18000 存储大小存储大小: n: n个字节（个字节（n n个字符）个字符）2. varchar2. varchar类型类型格式格式: : varchar(n)varchar(n)功能功能: : 定

9、义长度定义长度最多最多为为n n个字节的个字节的可变长度可变长度非非UnicodeUnicode字符数字符数据据, ,每个字符占一个字节。每个字符占一个字节。说明说明: : n: 18000 n: 18000。 存储大小存储大小: : 输入字符的实际长度。长度可为零输入字符的实际长度。长度可为零3. nchar3. nchar类型类型格式格式: : nchar(n)nchar(n)功能功能: : 定义包含定义包含n n个字符的个字符的固定长度固定长度UnicodeUnicode字符数据字符数据说明说明: : n:1-4000 n:1-4000 存储大小存储大小: 2n: 2n个字节个字节4.

10、nvarchar4. nvarchar类型类型格式格式: : nvarchar(n)nvarchar(n)功能功能: :定义包含最多定义包含最多n n个字符的个字符的可变长度可变长度UnicodeUnicode字符数据字符数据说明说明: : n: 1-4000 n: 1-4000。 存储大小存储大小: :所输入的字符实际个数的两倍。长度可以为零。所输入的字符实际个数的两倍。长度可以为零。省略省略n n：在数据定义或变量定义语句中，在数据定义或变量定义语句中，n n的缺省长度为的缺省长度为1 1。在在CASTCAST函数中，函数中，n n的缺省长度为的缺省长度为3030。1.5 1.5 日期和时

11、间数据类型日期和时间数据类型 日期和时间数据类型用于存储日期和时间的结合体。包日期和时间数据类型用于存储日期和时间的结合体。包括括datetimedatetime和和smalldatetimesmalldatetime两种类型。两种类型。1. datetime1. datetime类型类型 存储大小存储大小: 8: 8个字节个字节 表示范围表示范围:1753:1753年年1 1月月1 1日零时日零时-9999-9999年年1212月月3131日日2323时时5959分分5959秒。秒。 例例: : 01/01/98 23:59:59 2000-5-29 12:30:4801/01/98 23:

12、59:59 2000-5-29 12:30:482. smalldatetime2. smalldatetime 存储大小存储大小: 4: 4个字节个字节 表示范围表示范围: 1900: 1900年年1 1月月1 1日日-2079-2079年年6 6月月6 6日。日。 例例: : 2000/05/08 12:35 2000-05-29 12:352000/05/08 12:35 2000-05-29 12:35 2000-05-29 2000-05-291.6 图形（IMAGE）数据类型 imageimage数据类型用于存储可变长度二进制数据，其长度数据类型用于存储可变长度二进制数据，其长度界

13、于界于0 0到到2 23131-1-1个字节之间。个字节之间。1.7 1.7 货币数据类型货币数据类型 货币数据类型包括货币数据类型包括moneymoney和和smallmoneysmallmoney数据类型。数据类型。货币数据存储的精确度为四位小数。货币数据存储的精确度为四位小数。1. money1. money类型类型 存储大小存储大小: 8: 8个字节个字节 表示范围表示范围: -922,337,203,685,477.5808- : -922,337,203,685,477.5808- +922,337,203,685,477.5807+922,337,203,685,477.5807

14、2. smallmoney2. smallmoney类型类型 存储大小存储大小: 4: 4个字节个字节 表示范围表示范围: -214,748.3648-214,748.3647: -214,748.3648-214,748.36471.8 1.8 位（位（BITBIT）数据类型）数据类型 bitbit数据类型的取值只有数据类型的取值只有0 0和和1 1，如果一个表中有，如果一个表中有不多于不多于8 8个的个的bitbit列，这些列将作为一个字节存储。列，这些列将作为一个字节存储。如果表中有如果表中有9 9到到1616个个bitbit列，这些列将作为两个字节列，这些列将作为两个字节存储。更多列的

15、情况依此类推。存储。更多列的情况依此类推。1.9 1.9 二进制数据类型二进制数据类型二进制数据类型又可以分为二进制数据类型又可以分为binarybinary和和varbinaryvarbinary类型。类型。1. binary类型类型 格式格式: binary(n) 功能功能: 定义固定长度的定义固定长度的n个字节二进制数据，当输个字节二进制数据，当输入的二进制数据长度小于入的二进制数据长度小于n时，余下部分填充时，余下部分填充0。 说明说明: n: 18000 存储大小存储大小: n+4个字节个字节2 2. . varbinaryvarbinary类型类型格式格式: : varbinary

16、(n)varbinary(n)功能功能: : 定义定义n n个字节可变长度二进制数据。个字节可变长度二进制数据。说明说明: : n n: : 1 1-80008000存储大小存储大小: : 为实际输入数据长度加为实际输入数据长度加4 4个字节，而不是个字节，而不是n n个字节。输入的数据长度可能为个字节。输入的数据长度可能为0 0字节。字节。如果在数据定义或变量定义语句中使用时没有指定如果在数据定义或变量定义语句中使用时没有指定n n，则默认长度则默认长度n n为为1 1。如果在。如果在CASTCAST函数中使用时没有指定函数中使用时没有指定n n，则默认长度则默认长度n n为为3030。1.

17、10 1.10 其他数据类型其他数据类型1 1. . timestamptimestamp类型类型( (时间戳数据类型时间戳数据类型) ) 存储大小存储大小: : 8 8个字节。个字节。 时间戳类型的数据用于提供数据库范围内的惟一值，反时间戳类型的数据用于提供数据库范围内的惟一值，反映数据库中数据修改的相对顺序，相当于一个单调上升的计映数据库中数据修改的相对顺序，相当于一个单调上升的计数器。当表中的某列定义为数器。当表中的某列定义为timestamptimestamp类型时，在对表中某类型时，在对表中某行进行修改或添加行时，相应行进行修改或添加行时，相应timestamptimestamp类型

18、列的值会自动类型列的值会自动被更新。被更新。2 2. . uniqueidentifieruniqueidentifier类型类型 用于存储一个用于存储一个1616字节长的二进制数据，它是字节长的二进制数据，它是SQL SQL ServerServer根据计算机网络适配器和根据计算机网络适配器和CPUCPU时钟产生的惟时钟产生的惟一标识符（一标识符（Globally Unique IdentifierGlobally Unique Identifier，GUIDGUID），），该数字可以通过调用该数字可以通过调用SQL ServerSQL Server的的NEWIDNEWID函数获得。函数获得

19、。 GUIDGUID是一个唯一的二进制数字，世界上的任何是一个唯一的二进制数字，世界上的任何两台计算机都不会生成重复的两台计算机都不会生成重复的GUIDGUID值。值。GUIDGUID主要用主要用于在拥有多个节点、多台计算机的网络中，分配必于在拥有多个节点、多台计算机的网络中，分配必须具有唯一性的标识符。须具有唯一性的标识符。3 3. . sql_variantsql_variant类型类型 用于存储除用于存储除texttext、ntextntext、imageimage、timestamptimestamp和和sql_variantsql_variant外的其它任何合法的数据。外的其它任何合

20、法的数据。4 4. . tabletable类型类型 用于存储对表或者视图处理后的结果集。这种用于存储对表或者视图处理后的结果集。这种新的数据类型使得用变量就可以存储一个表，从而新的数据类型使得用变量就可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。使函数或过程返回查询结果更加方便、快捷。5. cursor5. cursor类型类型 cursorcursor类型是变量或存储过程类型是变量或存储过程 的的OUTPUT OUTPUT 参数参数的一种数据类型，这些参数包含对游标的引用。的一种数据类型，这些参数包含对游标的引用。2 2、创建表、创建表数据库中的表的概念数据库中的表的概念SQ

21、L ServerSQL Server的数据类型的数据类型数据类别数据类别长度长度精度精度小数位数小数位数空值：没有输入的值，未知或未定义空值：没有输入的值，未知或未定义避免用空值直接参与运算避免用空值直接参与运算尽量不要允许用空值（用默认值解决没有输入的问题）尽量不要允许用空值（用默认值解决没有输入的问题）u表的概念表的概念 表是数据库的基本单位，它是一个二维表，表由行和表是数据库的基本单位，它是一个二维表，表由行和列组成。每行代表惟一的一条记录，而每列代表记录的列组成。每行代表惟一的一条记录，而每列代表记录的一个域。一个域。u在设计数据库时，应在充分进行项目需求分析调查的前提下，分析在设计数

22、据库时，应在充分进行项目需求分析调查的前提下，分析数据库中要有哪些表，每个表中应有哪些列。而具体设计表时一般数据库中要有哪些表，每个表中应有哪些列。而具体设计表时一般应考虑：应考虑：表中有哪些列，列的名字，每一列的数据类型和长度，表中有哪些列，列的名字，每一列的数据类型和长度，列是否允许空值。列是否允许空值。表中哪些列需要定义主键，哪个列需要定义外键、唯一表中哪些列需要定义主键，哪个列需要定义外键、唯一键、标识列。键、标识列。表中哪些列的数据有效范围需要限定。表中哪些列的数据有效范围需要限定。需要对哪些列提高查询速度。需要对哪些列提高查询速度。数据类型数据类型 数据类型是数据库对象的一个属性，

23、数据类型是数据库对象的一个属性，SQL ServerSQL Server提供提供了一系列系统定义的数据类型，也可让用户根据需要了一系列系统定义的数据类型，也可让用户根据需要在系统数据类型的基础上创建用户定义的数据类型。在系统数据类型的基础上创建用户定义的数据类型。数据类型实际上包括如下数据类型实际上包括如下 几个属性：几个属性：（1 1）数据类别，如字符型、整数型、数字型等。）数据类别，如字符型、整数型、数字型等。（2 2）存储的数据值的长度或大小。）存储的数据值的长度或大小。（3 3）数值的精度。）数值的精度。（4 4）数值的小数位数。）数值的小数位数。1、使用Microsoft SQL S

24、erver Management Microsoft SQL Server Management StudioStudio创建图书类别表和用户表一、创建表一、创建表在在SQL Server 2008SQL Server 2008中，提供了两种方法来创建数中，提供了两种方法来创建数据表：据表：u利用利用Microsoft SQL Server Management Microsoft SQL Server Management StudioStudio。u利用利用T SQLT SQL语句。语句。定义标识列定义标识列 所谓标识列，是指字段的值由所谓标识列，是指字段的值由SQL Server 200

25、8自自动提供。动提供。定义标识列须同时指定种子和增量，或者两者定义标识列须同时指定种子和增量，或者两者都不指定；如果两者都未指定，则取默认值为（都不指定；如果两者都未指定，则取默认值为（1，1），），即种子值（第一个即种子值（第一个1）为）为1，增量值（后一个，增量值（后一个1）为）为1。 种子是添加表中的第一个记录所使用的值，增量是种子是添加表中的第一个记录所使用的值，增量是每次添加记录时，在上次增加记录时该字段值的基础上每次添加记录时，在上次增加记录时该字段值的基础上增加的幅度增加的幅度。例如，设种子、增量值为（。例如，设种子、增量值为（3，2），若上），若上次添加记录时字段的值为次添加记

26、录时字段的值为7，则下次添加记录时，字段，则下次添加记录时，字段的值为的值为9。在一个表中，只能定义一个字段为标识列。在一个表中，只能定义一个字段为标识列。2、使用Transact-SQL语句创建表。创建表命令基本语法如下： CREATE TABLE table_nameCREATE TABLE table_name ( ( col_name column_properties, col_name column_properties, ) )其中：Table_name 表的名称。col_name 列的名称。Column_properties列的属性。创建订单表USE USE 在线书店在线书店G

27、OGO-创建等级表创建等级表CREATE TABLE CREATE TABLE 订单订单( ( 订单编号订单编号 int NOT NULL,int NOT NULL, 用户账号用户账号 varchar (20) NOT NULL,varchar (20) NOT NULL, 订单时间订单时间 smalldatetime NOT NULL ,smalldatetime NOT NULL , 订单状态订单状态 varchar (10) NOT NULL ,varchar (10) NOT NULL , 总金额总金额 float NOT NULL float NOT NULL ) )n-创建等级表创

28、建等级表CREATE TABLE CREATE TABLE 等级等级( ( 等级编号等级编号 tinyint NOT NULL ,tinyint NOT NULL , 消费金额下限消费金额下限 int NULL ,int NULL , 消费金额上限消费金额上限 int NULL ,int NULL , 等级名称等级名称 varchar (10) NULL , varchar (10) NULL , 折扣折扣 numeric (3,1) NULL numeric (3,1) NULL ) )GOGO2. 2. 修改表结构修改表结构 利用ALTER TABLE命令修改表结构ALTER TABLE

29、table_name ALTER COLUMN column_name new_data_type ( precision ,scale ) null | not null | ADD | ,n | DROP COLUMN column_name ,n 例：给订单表添加新列例：给订单表添加新列ALTER TABLE 订单ADD 发货状态 char (6) null例：把订单表中的订单状态列的长度修改为例：把订单表中的订单状态列的长度修改为20。ALTER TABLE 订单ALTER COLUMN 订单状态 varchar (20) not null 3 3、重新命名表、重新命名表（1 1）使用

30、企业管理器重新命名表）使用企业管理器重新命名表 例4：使用企业管理器将在线书店数据库中的“等级”表重新命名为“用户等级”.（2 2）使用）使用TRANSACT-SQLTRANSACT-SQL语句重新命名表语句重新命名表 EXEC sp_rename EXEC sp_rename 原表名原表名 , , 新表名新表名 例例5 5：在线书店数据库中的“用户等级”表重新命名为“等级”.USE USE 在线书店在线书店GoGoEXEC sp_renameEXEC sp_rename 用户等级用户等级, , 等级等级 GOGO4、删除表n（1）使用企业管理器删除表例6：使用企业管理器删除在线书店数据库中的

31、表等级。n（2）使用TRANSACT-SQL语句删除表DROP TABLE table_name例7：使用TRANSACT-SQL语句删除在线书店数据库的表等级.USE USE 在线书店在线书店GoGoDROP TABLEDROP TABLE 等级等级GOGO表数据的操作表数据的操作添添 加加 数数 据据在SQL Server中，可以使用INSERT命令向指定的表或视图添加数据,语法如下。命令格式：INSERT INTO table_name | view_name (column_name ,.n ) VALUES( DEFAULT | NULL | expression ,.n )| de

32、rived_table 命令说明：（1）INSERT INTO ：指定要向表中插入数据，INTO可以省略。（2）table_name | view_name：表示要插入数据的表或视图的名称。（3）(column_name ,.n)：表示要插入数据的表或视图的列名清单。（4）VALUES ：该关键字指定要插入数据的列表清单。（5） DEFAULT | NULL | expression ,.n：该数据列表清单中包括默认值、空值和表达式的数量、次序和数据类型必须与列清单中指定列的定义相匹配。如果在VALUES清单中按表中定义的列的顺序提供每列的值，则可以省略列清单。例：向图书类别表中添加两个元组。

33、例：向图书类别表中添加两个元组。INSERT 图书类别VALUES (1,官场)INSERT 图书类别VALUES(2,科幻)更更 新新 数数 据据 在在SQL ServerSQL Server中中，可以使用可以使用UPDATEUPDATE命令在指定的表或命令在指定的表或视图中更新现有的数据视图中更新现有的数据，语法如下。语法如下。命令格式：UPDATE table_nameSET column_name = expression | DEFAULT | NULL | variable = expression | variable = column_name = expression ,.n

34、 FROM ,.n WHERE 命令说明：（1）UPDATE table_name：指定需要更新的表的名称为：指定需要更新的表的名称为table_name所表示的名称。所表示的名称。（2）SET：该子句表示对指定的列或变量名称赋予新值。：该子句表示对指定的列或变量名称赋予新值。（3）column_name = expression | DEFAULT | NULL：将：将变 量 、 字 符 、 表 达 式 的 值 ， 或 默 认 值 ， 或 空 值 替 换变 量 、 字 符 、 表 达 式 的 值 ， 或 默 认 值 ， 或 空 值 替 换column_name所指定列的现有值。不能修改标识列数据。所指定列的现有值。不能修改标识列数据。（4）variable = expression：指定将变量、字符、表达式：指定将变量、字符、表达式的值赋予一个已经声明的局部变量。的值赋予一个已经声明的局部变量。（5）variable = column_name = expression：指定将变：指定将变量、字符、表达式的值同时赋予一列