数据库 第04章 创建数据类型和表课件.ppt

1、1第第4章章 创建数据类型和表创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作2创建数据类型创建数据类型系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则3.1 创建数据类型创建数据类型3系统提供的数据类型系统提供的数据类型数字数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据3.1.1 系统提供的数据类型系统提供的数据类型bigint占8个字节，值的范围为-263263-1int占4个字节，值的范围为-231231-1smallint占2个字节，值的范围为-3276832 767tinyint占1个字节，值的范围为 0255decimal(p,s)p为精

2、度，最大38；s为小数位数，0spnumeric(p,s)在SQL Server中，等价于decimal4系统提供的数据类型（续）系统提供的数据类型（续）数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值3.1.1 系统提供的数据类型系统提供的数据类型float(n)从-1.79E+308 到 1.79E+308 之间的浮点数字数据；n 为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1n53real从 3.40E+38 到 3.40E+38 之间的浮点数字数据，存储大小为 4 字节；SQL Server 中，real 的同义词为 float(24)mo

3、ney占8个字节，值的范围为 -922 337 203 685 477.580 8 +922 337 203 685 477.580 7smallmoney占4个字节，值的范围为 -214 748.3648 214 748.36475系统提供的数据类型（续）系统提供的数据类型（续）日期和时间数据字符数据和 Unicode 字符数据3.1.1 系统提供的数据类型系统提供的数据类型datetime占8个字节，表示从1753年1月1日到 9999年12月31日的日期smalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char(n)存储字符个数为 08 000var

4、char(n)存储字符个数为 08 000text存储字符个数为 02GBnchar(n)存储字符个数为 04 000nvarchar(n)存储字符个数为 04 000ntext存储字符个数为 01GB6系统提供的数据类型（续）系统提供的数据类型（续）二进制数据其他3.1.1 系统提供的数据类型系统提供的数据类型binary(n)存储字节个数 08 000varbinary(n)存储字节个数 08 000image存储字节个数 02 Gbit存储位数据cursor存储对游标的引用rowversion(timestamp)时间戳sql_variant可存储除 text、ntext、image、r

5、owversion 之外的其他类型table存储函数返回结果uniqueidentifier存储 GUID 以及 UUID7创建和删除用户定义的数据类型创建和删除用户定义的数据类型3.1.2 创建和删除用户定义的数据类型创建和删除用户定义的数据类型为什么要自定义数据类型当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型，以保证数据的一致性创建用户定义的数据类型企业管理器系统存储过程sp_sp_addtypeaddtype 类型名类型名,系统数据类型系统数据类型 ,NULL|NOT NULL,NULL|NOT NULL,拥有者拥有者

6、删除用户定义的数据类型sp_sp_droptypedroptype 类型名类型名 8创建和删除用户定义的数据类型创建和删除用户定义的数据类型(续续)3.1.2 创建和删除用户定义的数据类型创建和删除用户定义的数据类型创建用户定义的数据类型：创建用户定义的数据类型：EXEC sp_addtype city,varchar(20),NULLEXEC sp_addtype region,varchar(20),NULLEXEC sp_addtype country,varchar(40),NULL删除用户定义的数据类型：删除用户定义的数据类型：EXEC sp_droptype cityEXEC sp

7、_droptype regionEXEC sp_droptype country9选择数据类型的指导原则选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用 tinyint 数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用 decimal 数据类型可以精确地控制精度如果行的存储量 超过8 000字节，使用 text 或者 image若不大于8 000字节，可使用char、varchar或者binary数据类型对于货币数据，使用 money 数据类型不要使用类型为 float 或者 real 的列作为主键因为它们不精确，所以不适合用于比

8、较3.1.3 选择数据类型的指导原则选择数据类型的指导原则10第第4章章 创建数据类型和表创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作11创建表创建表SQL Server 在行中组织数据的方式SQL Server 组织 text、ntext 和 image 数据的方式创建和删除表添加和删除列3.2 创建表创建表12行中组织数据的方式行中组织数据的方式行首行首定长数据定长数据NBVB变长数据变长数据空值块空值块变长值块变长值块4字节数据部分数据行由行首和数据部分组成行首：四个字节，包括了数据行中每列的信息数据部分定长数据：存放定长数据类型的列的数据空值块：标示值为空的列变长值块：标

9、示值为变长数据的列的信息以及存放位置变长数据：存放变长数据类型的列的数据3.2.1 SQL Server 在行中组织数据的方式在行中组织数据的方式13text、ntext 和和 image 数据的方式数据的方式Text、ntext 和 image 一般是存储在数据行之外的因为它们一般都比较大Text、ntext 和 image 的存储结构在数据行内存放一个16字节的指针，指向一个根结构根结构组成了一棵B树的根节点，B树的叶节点指向实际存放数据的数据块将大对象数据类型的数据存储在数据行中避免了多次查找数据块位置，可提高性能用sp_tableoption来设置表的 text in row 选项查看

10、表的 text in row 选项：objectproperty 函数3.2.2 SQL Server SQL Server 组织组织 texttext、ntextntext 和和 image image 数据的方式数据的方式14text、ntext 和和 image 数据的方式（续）数据的方式（续）数据行根结构根结构中间节点中间节点中间节点中间节点块块 1块块 2块块 1块块 23.2.2 SQL Server SQL Server 组织组织 texttext、ntextntext 和和 image image 数据的方式数据的方式15创建表命名表和列：数据库名.拥有者.表名n 数据库名默认

11、为当前的工作数据库n 拥有者默认为当前用户或者数据库的拥有者指定 NULL 或者 NOT NULLn 确定列值是否可为空n 默认按照会话或者数据库的默认值设定计算列n 是一个虚的列，并不物理存放在表中n 当取列值的时候，SQL Server 根据其他列的值和一n 个公式计算出列值创建和删除表创建和删除表3.2.3 创建和删除表创建和删除表16分隔标识符命名规则：保留字，如 table、create、select 等，不能作为对象标识符不符合标识符格式规则的标识符必须使用分隔符删除表DROP TABLE 表名,n 创建和删除表（续）创建和删除表（续）3.2.3 创建和删除表创建和删除表CREAT

12、E TABLE customer(name char(30),column char(12)CREATE TABLE customer(name char(30),“column”char(12)CREATE TABLE customer(name char(30),column char(12)注：仅当注：仅当 QUOTED_IDENTIFIER 选项设置为选项设置为 ON 时，被引时，被引 用的标识符才有效用的标识符才有效SET QUOTED_IDENTIFIER ON 17添加和删除列添加和删除列3.2.4 添加和删除列添加和删除列添加列语法：ALTER TABLE 表名表名 ADD 列

13、名列名 数据类型数据类型 NULL|NOT NULLALTER TABLE XSADD 奖学金等级 tinyint NULLGOALTER TABLE XSALTER COLUMN 姓名 char(10)ALTER COLUMN 出身时间 datetimeGO18添加和删除列（续）添加和删除列（续）3.2.4 添加和删除列添加和删除列删除列语法：ALTER TABLE 表名表名 DROP COLUMN 列名列名,n不能删除以下列n 正在复制的列n 用在索引中的列n 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列n 与 DEFAULT 定义关联或绑

14、定到某一默认对象的列n 绑定到规则的列19课堂练习课堂练习 添加列添加列添加一个数值列添加列描述设置精度和小数位数设置列为标识列添加一个 GUID 列添加一个日期列添加一个字符列保存并关闭表设计器20第第4章章 创建数据类型和表创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作21生成列值生成列值使用 Identity 属性使用 NEWID 函数和 uniqueidentifier 数据类型3.3 生成列值生成列值22使用使用 Identity 属性属性使用 Identity 属性的要求每个表只能有一个标识列只用在 int、smallint、tinyint 和 decimal数据类型上

15、。若用于 decimal，小数位数必须为0标识列不能进行更新操作标识列不允许空值在查询中，可以用关键字 IDENTITYCOL 来代表一个表中的标识列使得不必指明标识列的列名，增加了灵活性3.3.1 使用使用 Identity 属性属性23使用使用 Identity 属性（续）属性（续）检索 Identity 属性的信息使用全局变量 identity 获得当前会话的所有作用当前会话的所有作用域域中的任何表最后生成的标识值 例如：select identity from Employees 返回Employees表中的标示列的最后一行的值使用函数 IDENT_CURRENT 返回任何会话和任何作

16、用域中的特定表最后生成的标识值 例如：select IDENT_CURRENT(Employees)管理 Identity 属性设置 IDENTITY_INSERT 为 ON，可在 INSERT 中为标识列显式地插入用户提供的值使用 DBCC CHECKIDENT 检查当前标识值是否越界3.3.1 使用使用 Identity 属性属性24使用使用 uniqueidentifier 数据类型数据类型作用：确保 ID 的全局惟一性uniqueidentifier 数据类型：存储 GUIDNEWID 函数：产生一个 GUID两者常常和 DEFAULT 约束配合使用 CREATE TABLE Cust

17、omer(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),CustName char(30)NOT NULL)3.3.2 使用使用 NEWID 函数和函数和 uniqueidentifier 数据类型数据类型25第第4章章 创建数据类型和表创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作26生成脚本生成脚本使用企业管理器将模式生成 Transact-SQL 脚本维护备份脚本创建或者更新数据库开发脚本建立测试或开发环境训练新员工可以生成整个数据库的一个创建脚本文件一个或多个表的若干个创建脚本文件表、索引以及存储过程等各个对象的脚本，可以

18、分别存放在不同脚本文件里3.4 生成脚本生成脚本27第第4章章 创建数据类型和表创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作28推荐操作推荐操作在在 CREATE TABLE 语句中总是指定列的特性语句中总是指定列的特性产生脚本以重建数据库和数据库对象产生脚本以重建数据库和数据库对象指定适当的数据类型和大小指定适当的数据类型和大小3.5 推荐操作推荐操作29目标创建用户定义数据类型创建表添加和删除列从数据库中生成 Transact-SQL 脚本练习 1 创建用户自定义数据类型练习 2 在 ClassNorthwind 数据库中创建表练习 3 添加和删除列练习 4 生成 Transact-SQL 脚本练习 5 在 ClassNorthwind 数据库中加载数据实验实验 创建数据类型和表创建数据类型和表30回顾回顾学习完本章后，将能够：创建和删除用户定义数据类型创建和删除用户表生成列值生成脚本