创建数据类型与表ppt课件

1、第第3章章 创建数据类型和表创建数据类型和表n创建数据类型创建数据类型n创建表创建表n生成列值生成列值n生成脚本生成脚本n推荐操作推荐操作创建数据类型创建数据类型n系统提供的数据类型系统提供的数据类型n创建和删除用户定义的数据类型创建和删除用户定义的数据类型n选择数据类型的指导原则选择数据类型的指导原则3.1 创建数据类型创建数据类型系统提供的数据类型系统提供的数据类型n数字数据数字数据n整型数据：存储整数整型数据：存储整数n小数数据：包含存储在最小有效数上的数据小数数据：包含存储在最小有效数上的数据3.1.1 系统提供的数据类型系统提供的数据类型bigintbigint占8个字节，值的范围为

2、 -263263-1intint占4个字节，值的范围为 -231231-1smallintsmallint占2个字节，值的范围为 -3276832 767tinyinttinyint占1个字节，值的范围为 0255decimal (p,s)decimal (p,s)p为精度，最大38；s为小数位数，0spnumeric (p,s)numeric (p,s)在SQL Server中，等价于decimal系统提供的数据类型续）系统提供的数据类型续）n数字数据续）数字数据续）n近似数字数据：表示浮点数据的近似数字近似数字数据：表示浮点数据的近似数字n货币数据：表示正的或负的货币值货币数据：表示正的或

3、负的货币值3.1.1 系统提供的数据类型系统提供的数据类型float (float (n n)从 -1.79E+308 到 1.79E+308 之间的浮点数字数据；n 为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1n53realreal从 3.40E+38 到 3.40E+38 之间的浮点数字数据，存储大小为 4 字节；SQL Server 中，real 的同义词为 float(24) moneymoney占8个字节，值的范围为 -922 337 203 685 477.580 8 +922 337 203 685 477.580 7smallmoneysmallmoney占4个

4、字节，值的范围为 -214 748.3648 214 748.3647系统提供的数据类型续）系统提供的数据类型续）n日期和时间数据日期和时间数据n字符数据和字符数据和 Unicode 字符数据字符数据3.1.1 系统提供的数据类型系统提供的数据类型datetimedatetime占8个字节，表示从1753年1月1日到 9999年12月31日的日期smalldatetimesmalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char (char (n n)存储字符个数为 08 000varchar (varchar (n n)存储字符个数为 08 000tex

5、ttext存储字符个数为 02GBnchar (nchar (n n)存储字符个数为 04 000nvarchar(nvarchar(n n)存储字符个数为 04 000ntextntext存储字符个数为 01GB系统提供的数据类型续）系统提供的数据类型续）n二进制数据二进制数据n其他其他3.1.1 系统提供的数据类型系统提供的数据类型binary(binary(n n) ) 存储字节个数 08 000varbinary(varbinary(n n) ) 存储字节个数 08 000imageimage存储字节个数 02 Gbitbit存储位数据cursorcursor存储对游标的引用rowve

6、rsion (timestamp)rowversion (timestamp)时间戳(特点演示)sql_variantsql_variant可存储除 text、ntext、image、rowversion 之外的其他类型tabletable存储函数返回结果uniqueidentifieruniqueidentifier存储 GUID 以及 UUID创建和删除用户定义的数据类型创建和删除用户定义的数据类型3.1.2 创建和删除用户定义的数据类型创建和删除用户定义的数据类型n用户定义的数据类型用户定义的数据类型n当多个表的列中要存储同样类型的数据，且想确保当多个表的列中要存储同样类型的数据，且想确

7、保这些列具有完全相同的数据类型、长度和为空性时这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型，可使用用户定义数据类型n创建用户定义的数据类型创建用户定义的数据类型n企业管理器企业管理器n系统存储过程系统存储过程nsp_addtype 类型名类型名,系统数据类型系统数据类型 ,NULL|NOT NULL ,拥有者拥有者n删除用户定义的数据类型删除用户定义的数据类型nsp_droptype 类型名类型名 选择数据类型的指导原则选择数据类型的指导原则n若列值的长度相差很大，字符一般使用变长数据类型，数字和若列值的长度相差很大，字符一般使用变长数据类型，数字和日期类型考虑实际情况

8、日期类型考虑实际情况n例如某列存储的是人名，地址等例如某列存储的是人名，地址等n谨慎使用谨慎使用 tinyint 数据类型数据类型0-255）n虽然节省空间，但扩展性很小虽然节省空间，但扩展性很小n对于小数数据来说，一般使用对于小数数据来说，一般使用 decimal 数据类型数据类型n可以精确地控制精度可以精确地控制精度(1-38)n如果行的存储量如果行的存储量 超过超过8 000字节，运用字节，运用 text 或者或者 imagen若不大于若不大于8 000字节，可使用字节，可使用char、varchar或者或者binary数据类型数据类型n对于货币数据，运用对于货币数据，运用 money

9、数据类型数据类型n不要使用类型为不要使用类型为 float 或者或者 real 的列作为主键的列作为主键n因为它们不精确，所以不适合用于比较因为它们不精确，所以不适合用于比较3.1.3 选择数据类型的指导原则选择数据类型的指导原则创建表创建表nSQL Server 在行中组织数据的方式在行中组织数据的方式nSQL Server 组织组织 text、ntext 和和 image 数据的方式数据的方式n创建和删除表创建和删除表n添加和删除列添加和删除列3.2 创建表创建表SQL Server 在行中组织数据的方式在行中组织数据的方式行首行首定长数据定长数据NBVB变长数据变长数据空值块空值块变长值

10、块变长值块4字节数据部分n数据行由行首和数据部分组成数据行由行首和数据部分组成n行首：四个字节，包括了数据行中每列的信息行首：四个字节，包括了数据行中每列的信息n数据部分数据部分n定长数据：存放定长数据类型的列的数据定长数据：存放定长数据类型的列的数据n空值块：标示值为空的列空值块：标示值为空的列n变长值块：标示值为变长数据的列的信息以及存放变长值块：标示值为变长数据的列的信息以及存放位置位置n变长数据：存放变长数据类型的列的数据变长数据：存放变长数据类型的列的数据3.2.1 SQL Server 在行中组织数据的方式在行中组织数据的方式SQL Server 组织组织 text、ntext 和

11、和 image 数据的方式数据的方式nText、ntext 和和 image 一般是存储在数据行之外的一般是存储在数据行之外的n因为它们一般都比较大因为它们一般都比较大nText、ntext 和和 image 的存储结构的存储结构(建表时类型后长度建表时类型后长度是是16)n在数据行内存放一个在数据行内存放一个16字节的指针，指向一个根结字节的指针，指向一个根结构构n根结构组成了一棵根结构组成了一棵B树的根节点，树的根节点，B树的叶节点指向树的叶节点指向实际存放数据的数据块实际存放数据的数据块n若数据大于若数据大于32 KB，在根节点和数据块之间添加中间，在根节点和数据块之间添加中间节点节点n

12、将大对象数据类型的数据存储在数据行中将大对象数据类型的数据存储在数据行中n避免了多次查找数据块位置，可提高性能避免了多次查找数据块位置，可提高性能n设置表的设置表的 text in row 选项选项n查看表的查看表的 text in row 选项：选项：objectproperty 函数函数3.2.2 SQL Server 组织组织 text、ntext 和和 image 数据的方式数据的方式sp_tableoption TableNamePattern = table , OptionName = option_name , OptionValue = valueSQL Server 组织组

13、织 text、ntext 和和 image 数据的方式续）数据的方式续）数据行根结构根结构中间节点中间节点中间节点中间节点块块 1块块 2块块 1块块 23.2.2 SQL Server 组织组织 text、ntext 和和 image 数据的方式数据的方式n创建表创建表n命名表和列：数据库名命名表和列：数据库名.拥有者拥有者.表名表名n 数据库名默认为当前的工作数据库数据库名默认为当前的工作数据库n 拥有者默认为当前用户或者数据库的拥有者拥有者默认为当前用户或者数据库的拥有者n指定合适的数据类型和长度指定合适的数据类型和长度n指定指定 NULL 或者或者 NOT NULLn 确定列值是否可为

14、空确定列值是否可为空n 默认按照会话或者数据库的默认值设定默认按照会话或者数据库的默认值设定n列的排序规则列的排序规则n 可为同一表中的各个列指定不同的排序规则可为同一表中的各个列指定不同的排序规则n计算列计算列n 是一个虚的列，并不物理存放在表中是一个虚的列，并不物理存放在表中n 当取列值的时候，当取列值的时候，SQL Server 根据其他列的值和一根据其他列的值和一个公个公 式计算出列值式计算出列值创建和删除表创建和删除表3.2.3 创建和删除表创建和删除表create table a(a int,b int,c as a+b )insert into a(a,b) values(1,1

15、)select * from aCreate table 语法语法CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE DEFAULT constant

16、_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUIDCOL .n create table Produce(Pro_id int,Name char(10),Price decimal,qty int,cost as Price*qty)n := CONSTRAINT constraint_name NULL | NOT NULL | PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegrou

17、p | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ) n := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ASC | DESC ,.n ) WIT

18、H FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions )n分隔标识符分隔标识符n命名规则：保留字，如命名规则：保留字，如 table、create、select

19、等，等，不能作为对象标识符不能作为对象标识符n不符合标识符格式规则的标识符必须使用分隔符不符合标识符格式规则的标识符必须使用分隔符n两种分隔标识符两种分隔标识符n 括在括号中的标识符：用方括号分隔括在括号中的标识符：用方括号分隔n 被引用的标识符：用双引号分隔被引用的标识符：用双引号分隔n仅当仅当 QUOTED_IDENTIFIER 选项设置为选项设置为 ON 时，被引时，被引用的标识符才有效用的标识符才有效nSET QUOTED_IDENTIFIER ON n删除表删除表nDROP TABLE 表名表名 ,n 创建和删除表续）创建和删除表续）3.2.3 创建和删除表创建和删除表修改表语法修改

20、表语法ALTER TABLE table ALTER 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 CONSTRAI

21、NT ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n 添加和删除列添加和删除列3.2.4 添加和删除列添加和删除列n添加列添加列n语法：语法：ALTER TABLE 表名表名 ADD 列名列名 数据类型数据类型 NULL | NOT NULLn添加列时的各个选项和创建表时的列选项相同添加列时的各个选项和创建表时的列选项相同n向表中添加新列时，向表中添加新列时，SQL Server 在列中为表中每个现在列中为表中每个现有的数据行插入一个值。因此，在向表中添加列时有的数据行插入一个值。因此，在向

22、表中添加列时向列添加向列添加 DEFAULT 定义会很有用定义会很有用n如果新列没有如果新列没有 DEFAULT 定义，则必须指定它允许空定义，则必须指定它允许空值值n如果新列不允许空值，那么如果新列不允许空值，那么 SQL Server 向其中插入空向其中插入空值时将返回错误值时将返回错误添加和删除列续）添加和删除列续）3.2.4 添加和删除列添加和删除列n删除列删除列n语法：语法：ALTER TABLE 表名表名 DROP 列名列名 ,nn不能删除以下列不能删除以下列n 正在复制的列正在复制的列n 用在索引中的列用在索引中的列n 用在用在 CHECK、FOREIGN KEY、UNIQUE

23、或或 PRIMARY KEY 约束中的列约束中的列n 与与 DEFAULT 定义关联或绑定到某一默认对象的列定义关联或绑定到某一默认对象的列n 绑定到规则的列绑定到规则的列课堂练习课堂练习 添加列添加列n添加一个数值列添加一个数值列n添加列描述添加列描述n设置精度和小数位数设置精度和小数位数n设置列为标识列设置列为标识列n添加一个添加一个 GUID 列列n添加一个日期列添加一个日期列n添加一个字符列添加一个字符列n保存并关闭表设计器保存并关闭表设计器生成列值生成列值n运用运用 Identity 属性属性n运用运用 NEWID 函数和函数和 uniqueidentifier 数据类型数据类型3.

24、3 生成列值生成列值运用运用 Identity 属性属性n运用运用 Identity 属性的要求属性的要求n每个表只能有一个标识列每个表只能有一个标识列n只用在只用在 integer、numeric 和和 numeric(decimal)数据类型数据类型上。若用于上。若用于 numeric(decimal)，小数位数必须为，小数位数必须为0n标识列不能进行更新操作标识列不能进行更新操作n标识列不允许空值标识列不允许空值n在查询中，可以用关键字在查询中，可以用关键字 IDENTITYCOL 来代表一个来代表一个表中的标识列表中的标识列n使得不必指明标识列的列名，增加了灵活性使得不必指明标识列的列

25、名，增加了灵活性3.3.1 运用运用 Identity 属性属性运用运用 Identity 属性续）属性续）n检索检索 Identity 属性的信息属性的信息n使用函数使用函数 IDENT_SEED 和和 IDENT_INCR 获得定义信息获得定义信息n使用全局变量使用全局变量 identity 获得当前会话的所有作用域中的任何表最获得当前会话的所有作用域中的任何表最后生成的标识值后生成的标识值n使用函数使用函数 SCOPE_IDENTITY 返回当前会话和当前作用域中的任何返回当前会话和当前作用域中的任何表最后生成的标识值表最后生成的标识值n 一个作用域就是一个模块一个作用域就是一个模块存储

26、过程、触发器、函数或批处存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中中，则它们位于相同的作用域中n使用函数使用函数 IDENT_CURRENT 返回任何会话和任何作用域中的特定返回任何会话和任何作用域中的特定表最后生成的标识值表最后生成的标识值n管理管理 Identity 属性属性n设置设置 IDENTITY_INSERT 为为 ON，可在，可在 INSERT 中为标识列显式地中为标识列显式地插入用户提供的值插入用户提供的值n运用运用 DBCC CHECKIDENT 检查当前标识值是否越界检查当前标识值是否越界3.3.1 运用运用 Identity 属性属性运用运用 NEWID 函数和函数和 uniqueidentifier 数据类型数据类型n作用：确保作用：确保 ID 的全局惟一性的全局惟一性nuniqueidentifier 数据类型：存储数据类型：存储 GUIDnNEWID 函数：产生一个函数：产生一个 GUIDn两者常常和两者常常和 DEFAULT 约束配合使用