数据库 第04章 创建数据类型和表
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
3.1.1 系统提供的数据类型
int
smallint tinyint
占4个字节，值的范围为 -231～231-1
占2个字节，值的范围为 -32768～32 767 占1个字节，值的范围为 0～255
小数数据：包含存储在最小有效数上的数据
decimal [(p[,s])] numeric [(p[,s])] p为精度，最大38； s为小数位数，0≤s≤p 在SQL Server中，等价于decimal
text
nchar [(n)]
存储字符个数为 0～2GB
存储字符个数为 0～4 000
nvarchar[(n)] 存储字符个数为 0～4 000
ntext
存储字符个数为 0～1GB
Copyright@2008 5
系统提供的数据类型（续）
3.1.1 系统提供的数据类型
二进制数据
binary[(n)] varbinary[(n)] image 存储字节个数 0～8 000 存储字节个数 0～8 000 存储字节个数 0～2 G
删除表
DROP TABLE 表名 [,…n]
Copyright@2008 16
添加和删除列
3.2.4 添加和删除列
添加列
语法：ALTER TABLE 表名
ADD 列名 数据类型 [NULL | NOT NULL]
ALTER TABLE XS ADD 奖学金等级 tinyint GO
NULL
ALTER TABLE XS ALTER COLUMN 姓名 char(10) ALTER COLUMN 出身时间 datetime GO
作用：确保 ID 的全局惟一性 uniqueidentifier 数据类型：存储 GUID NEWID 函数：产生一个 GUID 两者常常和 DEFAULT 约束配合使用
CREATE TABLE Customer (CustID uniqueidentifier NOT NULL DEFAULT NEWID(), CustName char(30) NOT NULL)
行首 定长数据 NB VB 变长数据
4字节
空值块
变长值块
Copyright@2008 12
text、ntext 和 image 数据的方式
3.2.2 SQL Server 组织 text、ntext 和 image 数据的方式
Text、ntext 和 image 一般是存储在数据行之外的 因为它们一般都比较大 Text、ntext 和 image 的存储结构 在数据行内存放一个16字节的指针，指向一个 根结构 根结构组成了一棵B树的根节点，B树的叶节点 指向实际存放数据的数据块 将大对象数据类型的数据存储在数据行中 避免了多次查找数据块位置，可提高性能 用sp_tableoption来设置表的 text in row 选项 查看表的 text in row 选项：objectproperty 函数
Copyright@2008
24
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008
1
创建数据类型
3.1 创建数据类型
系统提供的数据类型 创建和删除用户定义的数据类型 选择数据类型的指导原则
Copyright@2008
2
系统提供的数据类型
数字数据
整型数据：存储整数
bigint 占8个字节，值的范围为 -263～263-1
3.2.3 创建和删除表
数据库名默认为当前的工作数据库 拥有者默认为当前用户或者数据库的拥有者
指定 NULL 或者 NOT NULL
确定列值是否可为空 默认按照会话或者数据库的默认值设定
计算列
是一个虚的列，并不物理存放在表中 当取列值的时候，SQL Server 根据其他列的值和一 个公式计算出列值
Copyright@2008 13
text、ntext 和 image 数据的方式（续）
数据行 3.2.2 SQL Server 组织 text、ntext 和 image 数据的方式
text 指针
根结构
中间节点
中间节点
Hale Waihona Puke Baidu块1
块2
块1
块2
Copyright@2008
14
创建和删除表
创建表 命名表和列：数据库名.拥有者.表名
Copyright@2008
11
行中组织数据的方式
3.2.1 SQL Server 在行中组织数据的方式
数据行由行首和数据部分组成
行首：四个字节，包括了数据行中每列的信息 数据部分
定长数据：存放定长数据类型的列的数据 空值块：标示值为空的列 变长值块：标示值为变长数据的列的信息以及存放位置 变长数据：存放变长数据类型的列的数据 数据部分
Copyright@2008
15
创建和删除表（续）
3.2.3 创建和删除表
分隔标识符
命名规则：保留字，如 table、create、select 等， 不能作为对象标识符 不符合标识符格式规则的标识符必须使用分隔符
CREATE 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
系统提供的数据类型（续）
日期和时间数据
datetime
3.1.1 系统提供的数据类型
占8个字节，表示从1753年1月1日到 9999年12月31日的日期
smalldatetime 占4个字节，表示从1900年1月1日至 2079年6月6日的日期
字符数据和 Unicode 字符数据
char [(n)] 存储字符个数为 0～8 000 varchar [(n)] 存储字符个数为 0～8 000
删除用户定义的数据类型
sp_droptype {‘类型名’}
Copyright@2008 7
创建和删除用户定义的数据类型(续)
3.1.2 创建和删除用户定义的数据类型
创建用户定义的数据类型：
EXEC sp_addtype city, ‘varchar(20)’, ‘NULL’ EXEC sp_addtype region, ‘varchar(20)’, ‘NULL’ EXEC sp_addtype country, ‘varchar(40)’, ‘NULL’
real
货币数据：表示正的或负的货币值
money 占8个字节，值的范围为 -922 337 203 685 477.580 8 ～ +922 337 203 685 477.580 7 占4个字节，值的范围为 -214 748.3648 ～ 214 748.3647
Copyright@2008 4
smallmoney
Copyright@2008
18
课堂练习 添加列
添加一个数值列
添加列描述 设置精度和小数位数
设置列为标识列 添加一个 GUID 列 添加一个日期列 添加一个字符列 保存并关闭表设计器
Copyright@2008 19
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008 6
创建和删除用户定义的数据类型
3.1.2 创建和删除用户定义的数据类型
为什么要自定义数据类型
当多个表的列中要存储同样类型的数据，且想确保这 些列具有完全相同的数据类型、长度和为空性时，可 使用用户定义数据类型，以保证数据的一致性
创建用户定义的数据类型
企业管理器 系统存储过程 sp_addtype {类型名},[‘系统数据类型 ’] [,[‘NULL’|‘NOT NULL’]] [,’拥有者’]
在查询中，可以用关键字 IDENTITYCOL 来代 表一个表中的标识列
使得不必指明标识列的列名，增加了灵活性
Copyright@2008
22
使用 Identity 属性（续）
3.3.1 使用 Identity 属性
检索 Identity 属性的信息
使用全局变量 @@identity 获得当前会话的所有作用 域中的任何表最后生成的标识值 例如：select @@identity from Employees 返回Employees表中的标示列的最后一行的值 使用函数 IDENT_CURRENT 返回任何会话和任何作用 域中的特定表最后生成的标识值 例如：select IDENT_CURRENT( ‘Employees’ )
删除用户定义的数据类型：
EXEC sp_droptype city EXEC sp_droptype region EXEC sp_droptype country
Copyright@2008
8
选择数据类型的指导原则
3.1.3 选择数据类型的指导原则
若列值的长度相差很大，那么使用变长数据类型 例如某列存储的是人名，地址等 谨慎使用 tinyint 数据类型 虽然节省空间，但扩展性很小 对于小数数据来说，一般使用 decimal 数据类型 可以精确地控制精度 如果行的存储量 超过8 000字节，使用 text 或者 image 若不大于8 000字节，可使用char、varchar或者 binary数据类型 对于货币数据，使用 money 数据类型 不要使用类型为 float 或者 real 的列作为主键 因为它们不精确，所以不适合用于比较
Copyright@2008
17
添加和删除列（续）
3.2.4 添加和删除列
删除列
语法：ALTER TABLE 表名 DROP COLUMN 列名 [,…n] 不能删除以下列
正在复制的列 用在索引中的列 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列 与 DEFAULT 定义关联或绑定到某一默认对象的列 绑定到规则的列
其他
bit cursor rowversion (timestamp) sql_variant table 存储位数据 存储对游标的引用 时间戳 可存储除 text、ntext、image、 rowversion 之外的其他类型 存储函数返回结果
uniqueidentifier
存储 GUID 以及 UUID
Copyright@2008 9
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008
10
创建表
3.2 创建表
SQL Server 在行中组织数据的方式 SQL Server 组织 text、ntext 和 image 数 据的方式 创建和删除表 添加和删除列
Copyright@2008
3
系统提供的数据类型（续）
数字数据（续）
float [(n)] 3.1.1 系统提供的数据类型
近似数字数据：表示浮点数据的近似数字
从 -1.79E+308 到 1.79E+308 之间的浮点数字数据；n 为用于存储科学记数法尾数的位数，同时指示其精度和 存储大小，1≤n≤53 从 3.40E+38 到 3.40E+38 之间的浮点数字数据，存储 大小为 4 字节；SQL Server 中，real 的同义词为 float(24)
Copyright@2008
20
生成列值
3.3 生成列值
使用 Identity 属性 使用 NEWID 函数和 uniqueidentifier 数 据类型
Copyright@2008
21
使用 Identity 属性
3.3.1 使用 Identity 属性
使用 Identity 属性的要求
每个表只能有一个标识列 只用在 int、smallint、tinyint 和 decimal数据 类型上。若用于 decimal，小数位数必须为0 标识列不能进行更新操作 标识列不允许空值
管理 Identity 属性
设置 IDENTITY_INSERT 为 ON，可在 INSERT 中为标 识列显式地插入用户提供的值 使用 DBCC CHECKIDENT 检查当前标识值是否越界
Copyright@2008 23
使用 uniqueidentifier 数据类型
3.3.2 使用 NEWID 函数和 uniqueidentifier 数据类型
int
smallint tinyint
占4个字节，值的范围为 -231～231-1
占2个字节，值的范围为 -32768～32 767 占1个字节，值的范围为 0～255
小数数据：包含存储在最小有效数上的数据
decimal [(p[,s])] numeric [(p[,s])] p为精度，最大38； s为小数位数，0≤s≤p 在SQL Server中，等价于decimal
text
nchar [(n)]
存储字符个数为 0～2GB
存储字符个数为 0～4 000
nvarchar[(n)] 存储字符个数为 0～4 000
ntext
存储字符个数为 0～1GB
Copyright@2008 5
系统提供的数据类型（续）
3.1.1 系统提供的数据类型
二进制数据
binary[(n)] varbinary[(n)] image 存储字节个数 0～8 000 存储字节个数 0～8 000 存储字节个数 0～2 G
删除表
DROP TABLE 表名 [,…n]
Copyright@2008 16
添加和删除列
3.2.4 添加和删除列
添加列
语法：ALTER TABLE 表名
ADD 列名 数据类型 [NULL | NOT NULL]
ALTER TABLE XS ADD 奖学金等级 tinyint GO
NULL
ALTER TABLE XS ALTER COLUMN 姓名 char(10) ALTER COLUMN 出身时间 datetime GO
作用：确保 ID 的全局惟一性 uniqueidentifier 数据类型：存储 GUID NEWID 函数：产生一个 GUID 两者常常和 DEFAULT 约束配合使用
CREATE TABLE Customer (CustID uniqueidentifier NOT NULL DEFAULT NEWID(), CustName char(30) NOT NULL)
行首 定长数据 NB VB 变长数据
4字节
空值块
变长值块
Copyright@2008 12
text、ntext 和 image 数据的方式
3.2.2 SQL Server 组织 text、ntext 和 image 数据的方式
Text、ntext 和 image 一般是存储在数据行之外的 因为它们一般都比较大 Text、ntext 和 image 的存储结构 在数据行内存放一个16字节的指针，指向一个 根结构 根结构组成了一棵B树的根节点，B树的叶节点 指向实际存放数据的数据块 将大对象数据类型的数据存储在数据行中 避免了多次查找数据块位置，可提高性能 用sp_tableoption来设置表的 text in row 选项 查看表的 text in row 选项：objectproperty 函数
Copyright@2008
24
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008
1
创建数据类型
3.1 创建数据类型
系统提供的数据类型 创建和删除用户定义的数据类型 选择数据类型的指导原则
Copyright@2008
2
系统提供的数据类型
数字数据
整型数据：存储整数
bigint 占8个字节，值的范围为 -263～263-1
3.2.3 创建和删除表
数据库名默认为当前的工作数据库 拥有者默认为当前用户或者数据库的拥有者
指定 NULL 或者 NOT NULL
确定列值是否可为空 默认按照会话或者数据库的默认值设定
计算列
是一个虚的列，并不物理存放在表中 当取列值的时候，SQL Server 根据其他列的值和一 个公式计算出列值
Copyright@2008 13
text、ntext 和 image 数据的方式（续）
数据行 3.2.2 SQL Server 组织 text、ntext 和 image 数据的方式
text 指针
根结构
中间节点
中间节点
Hale Waihona Puke Baidu块1
块2
块1
块2
Copyright@2008
14
创建和删除表
创建表 命名表和列：数据库名.拥有者.表名
Copyright@2008
11
行中组织数据的方式
3.2.1 SQL Server 在行中组织数据的方式
数据行由行首和数据部分组成
行首：四个字节，包括了数据行中每列的信息 数据部分
定长数据：存放定长数据类型的列的数据 空值块：标示值为空的列 变长值块：标示值为变长数据的列的信息以及存放位置 变长数据：存放变长数据类型的列的数据 数据部分
Copyright@2008
15
创建和删除表（续）
3.2.3 创建和删除表
分隔标识符
命名规则：保留字，如 table、create、select 等， 不能作为对象标识符 不符合标识符格式规则的标识符必须使用分隔符
CREATE 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
系统提供的数据类型（续）
日期和时间数据
datetime
3.1.1 系统提供的数据类型
占8个字节，表示从1753年1月1日到 9999年12月31日的日期
smalldatetime 占4个字节，表示从1900年1月1日至 2079年6月6日的日期
字符数据和 Unicode 字符数据
char [(n)] 存储字符个数为 0～8 000 varchar [(n)] 存储字符个数为 0～8 000
删除用户定义的数据类型
sp_droptype {‘类型名’}
Copyright@2008 7
创建和删除用户定义的数据类型(续)
3.1.2 创建和删除用户定义的数据类型
创建用户定义的数据类型：
EXEC sp_addtype city, ‘varchar(20)’, ‘NULL’ EXEC sp_addtype region, ‘varchar(20)’, ‘NULL’ EXEC sp_addtype country, ‘varchar(40)’, ‘NULL’
real
货币数据：表示正的或负的货币值
money 占8个字节，值的范围为 -922 337 203 685 477.580 8 ～ +922 337 203 685 477.580 7 占4个字节，值的范围为 -214 748.3648 ～ 214 748.3647
Copyright@2008 4
smallmoney
Copyright@2008
18
课堂练习 添加列
添加一个数值列
添加列描述 设置精度和小数位数
设置列为标识列 添加一个 GUID 列 添加一个日期列 添加一个字符列 保存并关闭表设计器
Copyright@2008 19
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008 6
创建和删除用户定义的数据类型
3.1.2 创建和删除用户定义的数据类型
为什么要自定义数据类型
当多个表的列中要存储同样类型的数据，且想确保这 些列具有完全相同的数据类型、长度和为空性时，可 使用用户定义数据类型，以保证数据的一致性
创建用户定义的数据类型
企业管理器 系统存储过程 sp_addtype {类型名},[‘系统数据类型 ’] [,[‘NULL’|‘NOT NULL’]] [,’拥有者’]
在查询中，可以用关键字 IDENTITYCOL 来代 表一个表中的标识列
使得不必指明标识列的列名，增加了灵活性
Copyright@2008
22
使用 Identity 属性（续）
3.3.1 使用 Identity 属性
检索 Identity 属性的信息
使用全局变量 @@identity 获得当前会话的所有作用 域中的任何表最后生成的标识值 例如：select @@identity from Employees 返回Employees表中的标示列的最后一行的值 使用函数 IDENT_CURRENT 返回任何会话和任何作用 域中的特定表最后生成的标识值 例如：select IDENT_CURRENT( ‘Employees’ )
删除用户定义的数据类型：
EXEC sp_droptype city EXEC sp_droptype region EXEC sp_droptype country
Copyright@2008
8
选择数据类型的指导原则
3.1.3 选择数据类型的指导原则
若列值的长度相差很大，那么使用变长数据类型 例如某列存储的是人名，地址等 谨慎使用 tinyint 数据类型 虽然节省空间，但扩展性很小 对于小数数据来说，一般使用 decimal 数据类型 可以精确地控制精度 如果行的存储量 超过8 000字节，使用 text 或者 image 若不大于8 000字节，可使用char、varchar或者 binary数据类型 对于货币数据，使用 money 数据类型 不要使用类型为 float 或者 real 的列作为主键 因为它们不精确，所以不适合用于比较
Copyright@2008
17
添加和删除列（续）
3.2.4 添加和删除列
删除列
语法：ALTER TABLE 表名 DROP COLUMN 列名 [,…n] 不能删除以下列
正在复制的列 用在索引中的列 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列 与 DEFAULT 定义关联或绑定到某一默认对象的列 绑定到规则的列
其他
bit cursor rowversion (timestamp) sql_variant table 存储位数据 存储对游标的引用 时间戳 可存储除 text、ntext、image、 rowversion 之外的其他类型 存储函数返回结果
uniqueidentifier
存储 GUID 以及 UUID
Copyright@2008 9
第4章 创建数据类型和表
创建数据类型 创建表 生成列值 生成脚本 推荐操作
Copyright@2008
10
创建表
3.2 创建表
SQL Server 在行中组织数据的方式 SQL Server 组织 text、ntext 和 image 数 据的方式 创建和删除表 添加和删除列
Copyright@2008
3
系统提供的数据类型（续）
数字数据（续）
float [(n)] 3.1.1 系统提供的数据类型
近似数字数据：表示浮点数据的近似数字
从 -1.79E+308 到 1.79E+308 之间的浮点数字数据；n 为用于存储科学记数法尾数的位数，同时指示其精度和 存储大小，1≤n≤53 从 3.40E+38 到 3.40E+38 之间的浮点数字数据，存储 大小为 4 字节；SQL Server 中，real 的同义词为 float(24)
Copyright@2008
20
生成列值
3.3 生成列值
使用 Identity 属性 使用 NEWID 函数和 uniqueidentifier 数 据类型
Copyright@2008
21
使用 Identity 属性
3.3.1 使用 Identity 属性
使用 Identity 属性的要求
每个表只能有一个标识列 只用在 int、smallint、tinyint 和 decimal数据 类型上。若用于 decimal，小数位数必须为0 标识列不能进行更新操作 标识列不允许空值
管理 Identity 属性
设置 IDENTITY_INSERT 为 ON，可在 INSERT 中为标 识列显式地插入用户提供的值 使用 DBCC CHECKIDENT 检查当前标识值是否越界
Copyright@2008 23
使用 uniqueidentifier 数据类型
3.3.2 使用 NEWID 函数和 uniqueidentifier 数据类型