SQL创建数据类型和表

Slide Title,Body Text,Second level,Third level,Fourth level,Fifth level,数据库程序设计,SQL Server 2000,数据库程序设计,第,1,章,SQL Server,概述,第,2,章 创建和管理数据库,第,3,章 创建数据类型和表,第,4,章 实现数据完整性,第,5,章,Transact-SQL,介绍,第,6,章 使用,Transact-SQL,第,7,章 检索数据,第,8,章 数据分组与汇总,第,9,章 多表联接,第,10,章 子查询,第,11,章 修改数据,第,12,章,全文索引查询,第,13,章 规划索引,第,14,章 创建和维护索引,第,15,章 实现视图,第,16,章 实现存储过程,第,17,章 实现用户定义函数,第,18,章 实现触发器,第,19,章 多服务器编程,第,20,章 优化查询性能,第,21,章 分析查询,第,22,章 管理事务和锁,第,3,章 创建数据类型和表,创建数据类型,创建表,生成列值,生成脚本,推荐操作,创建数据类型,系统提供的数据类型,创建和删除用户定义的数据类型,选择数据类型的指导原则,3.1 创建数据类型,系统提供的数据类型,数字数据,整型数据：存储整数,小数数据：包含存储在最小有效数上的数据,3.1.1,系统提供的数据类型,bigint,占8个字节，值的范围为 -2,63,2,63,-1,int,占4个字节，值的范围为 -2,31,2,31,-1,smallint,占2个字节，值的范围为 -3276832 767,tinyint,占1个字节，值的范围为 0255,decimal (p,s),p,为精度，最大38；,s,为小数位数，0,sp,numeric (p,s),在,SQL Server,中，等价于,decimal,系统提供的数据类型（续）,数字数据（续）,近似数字数据：表示浮点数据的近似数字,货币数据：表示正的或负的货币值,3.1.1,系统提供的数据类型,float (,n,),从 -1.79,E+308,到 1.79,E+308,之间的浮点数字数据；,n,为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1,n,53,real,从 3.40,E+38,到 3.40,E+38,之间的浮点数字数据，存储大小为 4 字节；,SQL Server,中，,real,的同义词为,float(24),money,占8个字节，值的范围为 -922 337 203 685,477.580 8, +922 337 203 685,477.580 7,smallmoney,占4个字节，值的范围为 -214 748,.3648, 214 748.3647,系统提供的数据类型（续）,日期和时间数据,字符数据和,Unicode,字符数据,3.1.1,系统提供的数据类型,datetime,占8个字节，表示从1753年1月1日到 9999年12月31日的日期,smalldatetime,占4个字节，表示从1900年1月1日至2079年6月6日的日期,char (,n,),存储字符个数为 08 000,varchar (,n,),存储字符个数为 08 000,text,存储字符个数为 02,GB,nchar (,n,),存储字符个数为 04 000,nvarchar(,n,),存储字符个数为 04 000,ntext,存储字符个数为 01,GB,系统提供的数据类型（续）,二进制数据,其他,3.1.1,系统提供的数据类型,binary(,n,),存储字节个数 08 000,varbinary(,n,),存储字节个数 08 000,image,存储字节个数 02,G,bit,存储位数据,cursor,存储对游标的引用,rowversion (timestamp),时间戳,sql_variant,可存储除,text、ntext、image、rowversion,之外的其他类型,table,存储函数返回结果,uniqueidentifier,存储,GUID,以及,UUID,创建和删除用户定义的数据类型,3.1.2 创建和删除用户定义的数据类型,用户定义的数据类型,当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型,创建用户定义的数据类型,企业管理器,系统存储过程,sp_addtype,类型名,系统数据类型, ,NULL|NOT NULL ,拥有者,删除用户定义的数据类型,sp_droptype,类型名,选择数据类型的指导原则,若列值的长度相差很大，那么使用变长数据类型,例如某列存储的是人名，地址等,谨慎使用,tinyint,数据类型,虽然节省空间，但扩展性很小,对于小数数据来说，一般使用,decimal,数据类型,可以精确地控制精度,如果行的存储量 超过8 000字节，使用,text,或者,image,若不大于8 000字节，可使用,char、varchar,或者,binary,数据类型,对于货币数据，使用,money,数据类型,不要使用类型为,float,或者,real,的列作为主键,因为它们不精确，所以不适合用于比较,3.1.3,选择,数据类型的指导原则,第,3,章 创建数据类型和表,创建数据类型,创建表,生成列值,生成脚本,推荐操作,创建表,SQL Server,在行中组织数据的方式,SQL Server,组织,text,、,ntext,和,image,数据的方式,创建和删除表,添加和删除列,3.2 创建表,SQL Server,在行中组织数据的方式,行首,定长数据,NB,VB,变长数据,空值块,变长值块,4字节,数据部分,数据行由行首和数据部分组成,行首：四个字节，包括了数据行中每列的信息,数据部分,定长数据：存放定长数据类型的列的数据,空值块：标示值为空的列,变长值块：标示值为变长数据的列的信息以及存放位置,变长数据：存放变长数据类型的列的数据,3.2.1 SQL Server,在行中组织数据的方式,SQL Server,组织,text,、,ntext,和,image,数据的方式,Text、ntext,和,image,一般是存储在数据行之外的,因为它们一般都比较大,Text、ntext,和,image,的存储结构,在数据行内存放一个16字节的指针，指向一个根结构,根结构组成了一棵,B,树的根节点，,B,树的叶节点指向实际存放数据的数据块,若数据大于32,KB，,在根节点和数据块之间添加中间节点,将大对象数据类型的数据存储在数据行中,避免了多次查找数据块位置，可提高性能,设置表的,text in row,选项,查看表的,text in row,选项：,objectproperty,函数,3.2.2,SQL Server,组织,text,、,ntext,和,image,数据的方式,SQL Server,组织,text,、,ntext,和,image,数据的方式（续）,数据行,text,指针,根结构,中间节点,中间节点,块 1,块 2,块 1,块 2,3.2.2,SQL Server,组织,text,、,ntext,和,image,数据的方式,创建表,命名表和列：数据库名.拥有者.表名,数据库名默认为当前的工作数据库,拥有者默认为当前用户或者数据库的拥有者,指定,NULL,或者,NOT NULL,确定列值是否可为空,默认按照会话或者数据库的默认值设定,列的排序规则,可为同一表中的各个列指定不同的排序规则,计算列,是一个虚的列，并不物理存放在表中,当取列值的时候，,SQL Server,根据其他列的值和一个公 式计算出列值,创建和删除表,3.2.3 创建和删除表,分隔标识符,命名规则：保留字，如,table、create、select,等，不能作为对象标识符,不符合标识符格式规则的标识符必须使用分隔符,两种分隔标识符,括在括号中的标识符：用方括号分隔,被引用的标识符：用双引号分隔,仅当,QUOTED_IDENTIFIER,选项设置为,ON,时，被引用的标识符才有效,SET QUOTED_IDENTIFIER ON,删除表,DROP TABLE,表名,n,创建和删除表（续）,3.2.3 创建和删除表,添加和删除列,3.2.4 添加和删除列,添加列,语法：,ALTER TABLE,表名,ADD,列名,数据类型,NULL | NOT NULL,添加列时的各个选项和创建表时的列选项相同,向表中添加新列时，,SQL Server,在列中为表中每个现有的数据行插入一个值。因此，在向表中添加列时向列添加,DEFAULT,定义会很有用,如果新列没有,DEFAULT,定义，则必须指定它允许空值,如果新列不允许空值，则,SQL Server,向其中插入空值时将返回错误,添加和删除列（续）,3.2.4 添加和删除列,删除列,语法：,ALTER TABLE,表名,DROP,列名,n,不能删除以下列,正在复制的列,用在索引中的列,用在,CHECK、FOREIGN KEY、UNIQUE,或,PRIMARY KEY,约束中的列,与,DEFAULT,定义关联或绑定到某一默认对象的列,绑定到规则的列,课堂练习 添加列,添加一个数值列,添加列描述,设置精度和小数位数,设置列为标识列,添加一个,GUID,列,添加一个日期列,添加一个字符列,保存并关闭表设计器,第,3,章 创建数据类型和表,创建数据类型,创建表,生成列值,生成脚本,推荐操作,生成列值,使用,Identity,属性,使用,NEWID,函数和,uniqueidentifier,数据类型,3.3 生成列值,使用,Identity,属性,使用,Identity,属性的要求,每个表只能有一个标识列,只用在,integer、numeric,和,numeric(decimal),数据类型上。若用于,numeric(decimal)，,小数位数必须为0,标识列不能进行更新操作,标识列不允许空值,在查询中，可以用关键字,IDENTITYCOL,来代表一个表中的标识列,使得不必指明标识列的列名，增加了灵活性,3.3.1 使用,Identity,属性,使用,Identity,属性（续）,检索,Identity,属性的信息,使用函数,IDENT_SEED,和,IDENT_INCR,获得定义信息,使用全局变量 ,identity,获得当前会话的所有作用域中的任何表最后生成的标识值,使用函数,SCOPE_IDENTITY,返回当前会话和当前作用域中的任何表最后生成的标识值,一个作用域就是一个模块存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中,使用函数,IDENT_CURRENT,返回任何会话和任何作用域中的特定表最后生成的标识值,管理,Identity,属性,设置,IDENTITY_INSERT,为,ON，,可在,INSERT,中为标识列显式地插入用户提供的值,使用,DBCC CHECKIDENT,检查当前标识值是否越界,3.3.1 使用,Identity,属性,使用,NEWID,函数和,uniqueidentifier,数据类型,作用：确保,ID,的全局惟一性,uniqueidentifier,数据类型：存储,GUID,NEWID,函数：产生一个,GUID,两者常常和,DEFAULT,约束配合使用,CREATE TABLE Customer,(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),CustName char(30) NOT NULL),3.3.2 使用,NEWID,函数和,uniqueidentifier,数据类型,第,3,章 创建数据类型和表,创建数据类型,创建表,生成列值,生成脚本,推荐操作,生成脚本,使用企业管理器将模式生成,Transact-SQL,脚本,维护备份脚本,创建或者更新数据库开发脚本,建立测试或开发环境,训练新员工,可以生成,整个数据库的一个创建脚本文件,一个或多个表的若干个创建脚本文件,表、索引以及存储过程等各个对象的脚本，可以分别存放在不同脚本文件里,3.4 生成脚本,第,3,章 创建数据类型和表,创建数据类型,创建表,生成列值,生成脚本,推荐操作,推荐操作,在,CREATE TABLE,语句中总是指定列的特性,产生脚本以重建数据库和数据库对象,指定适当的数据类型和大小,3.5,推荐操作,目标,创建用户定义数据类型,创建表,添加和删除列,从数据库中生成,Transact-SQL,脚本,练习,1,创建用户自定义数据类型,练习,2,在,ClassNorthwind,数据库中创建表,练习,3,添加和删除列,练习,4,生成,Transact-SQL,脚本,练习,5,在,ClassNorthwind,数据库中加载数据,实验,创建数据类型和表,回顾,学习完本章后，将能够：,创建和删除用户定义数据类型,创建和删除用户表,生成列值,生成脚本,