数据库程序设计

数据库程序设计——SQLServer2000数据库程序设计第1章SQLServer概述第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个字节，值的范围为-263～263-1int占4个字节，值的范围为-231～231-1smallint占2个字节，值的范围为-32768～32767tinyint占1个字节，值的范围为0～255decimal[(p[,s])]p为精度，最大38；

s为小数位数，0≤s≤pnumeric[(p[,s])]在SQLServer中，等价于decimal系统提供的数据类型（续）数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值3.1.1系统提供的数据类型float[(n)]从-1.79E+308到1.79E+308之间的浮点数字数据；n

为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1≤n≤53real从3.40E+38到3.40E+38之间的浮点数字数据，存储大小为4字节；SQLServer中，real的同义词为float(24)

money占8个字节，值的范围为-922337203685477.5808～+922337203685477.5807smallmoney占4个字节，值的范围为-214748.3648～214748.3647系统提供的数据类型（续）日期和时间数据字符数据和Unicode字符数据3.1.1系统提供的数据类型datetime占8个字节，表示从1753年1月1日到9999年12月31日的日期smalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char[(n)]存储字符个数为0～8000varchar

[(n)]存储字符个数为0～8000text存储字符个数为0～2GBnchar

[(n)]存储字符个数为0～4000nvarchar[(n)]存储字符个数为0～4000ntext存储字符个数为0～1GB系统提供的数据类型（续）二进制数据其他3.1.1系统提供的数据类型binary[(n)]存储字节个数0～8000varbinary[(n)]存储字节个数0～8000image存储字节个数0～2Gbit存储位数据cursor存储对游标的引用rowversion(timestamp)时间戳sql_variant可存储除text、ntext、image、rowversion

之外的其他类型table存储函数返回结果uniqueidentifier存储GUID以及UUID创建和删除用户定义的数据类型3.1.2创建和删除用户定义的数据类型用户定义的数据类型当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型创建用户定义的数据类型企业管理器系统存储过程

sp_addtype

{类型名},[系统数据类型][,[‘NULL’|‘NOTNULL’]][,’拥有者’]删除用户定义的数据类型

sp_droptype

{‘类型名’}

选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用tinyint

数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用decimal数据类型可以精确地控制精度如果行的存储量超过8000字节，使用text或者image若不大于8000字节，可使用char、varchar或者binary数据类型对于货币数据，使用money数据类型不要使用类型为float或者real的列作为主键因为它们不精确，所以不适合用于比较3.1.3选择数据类型的指导原则第3章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作创建表SQLServer在行中组织数据的方式SQLServer组织text、ntext

和image数据的方式创建和删除表添加和删除列3.2创建表SQLServer在行中组织数据的方式行首定长数据NBVB变长数据空值块变长值块4字节数据部分数据行由行首和数据部分组成行首：四个字节，包括了数据行中每列的信息数据部分定长数据：存放定长数据类型的列的数据空值块：标示值为空的列变长值块：标示值为变长数据的列的信息以及存放位置变长数据：存放变长数据类型的列的数据3.2.1SQLServer在行中组织数据的方式SQLServer组织text、ntext

和image数据的方式Text、ntext

和image一般是存储在数据行之外的因为它们一般都比较大Text、ntext

和image的存储结构在数据行内存放一个16字节的指针，指向一个根结构根结构组成了一棵B树的根节点，B树的叶节点指向实际存放数据的数据块若数据大于32KB，在根节点和数据块之间添加中间节点将大对象数据类型的数据存储在数据行中避免了多次查找数据块位置，可提高性能设置表的textinrow选项查看表的textinrow选项：objectproperty

函数3.2.2SQLServer组织text、ntext

和image数据的方式SQLServer组织text、ntext

和image数据的方式（续）数据行text指针根结构中间节点中间节点块1块2块1块23.2.2SQLServer组织text、ntext

和image数据的方式创建表命名表和列：数据库名.拥有者.表名数据库名默认为当前的工作数据库拥有者默认为当前用户或者数据库的拥有者指定NULL或者NOTNULL

确定列值是否可为空默认按照会话或者数据库的默认值设定列的排序规则可为同一表中的各个列指定不同的排序规则计算列是一个虚的列，并不物理存放在表中当取列值的时候，SQLServer根据其他列的值和一个公

式计算出列值创建和删除表3.2.3创建和删除表分隔标识符命名规则：保留字，如table、create、select等，不能作为对象标识符不符合标识符格式规则的标识符必须使用分隔符两种分隔标识符括在括号中的标识符：用方括号分隔被引用的标识符：用双引号分隔仅当QUOTED_IDENTIFIER选项设置为ON时，被引用的标识符才有效SETQUOTED_IDENTIFIERON

删除表DROPTABLE表名[,…n]创建和删除表（续）3.2.3创建和删除表添加和删除列3.2.4添加和删除列添加列语法：ALTERTABLE表名

ADD列名

数据类型[NULL|NOTNULL]添加列时的各个选项和创建表时的列选项相同向表中添加新列时，SQLServer在列中为表中每个现有的数据行插入一个值。因此，在向表中添加列时向列添加DEFAULT定义会很有用如果新列没有DEFAULT定义，则必须指定它允许空值如果新列不允许空值，则SQLServer向其中插入空值时将返回错误添加和删除列（续）3.2.4添加和删除列删除列语法：ALTERTABLE表名DROP列名[,…n]不能删除以下列正在复制的列用在索引中的列用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY

约束中的列与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中为标识列显式地插入用户提供的值使用DBCCCHECKIDENT检查当前标识值是否越界3.3.1使用Identity属性使用NEWID函数和uniqueidentifier

数据类型作用：确保ID的全局惟一性uniqueidentifier

数据类型：存储GUIDNEWID函数：产生一个GUID两者常常和DEFAULT约束配合使用

CREATETABLECustomer(CustID

uniqueidentifierNOTNULLDEFAULTNEWID(),

CustNamechar(30)NOTNULL)3.3.2使用NEWID函数和uniqueidentifier

数据类型第3章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作生成脚本使用企业管理器将模式生成Transact-SQL脚本维护备份脚本创建或者更新数据库开发脚本建立测试或开发环境训练新员工可以生成整个数据库的一个创建脚本文件一个或多个表的若干个创建脚本文件表、索引以及存储过程等各个对象的脚本，可以分别存放在不同脚本文件里3.4生成脚本第3章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作推荐操作在CREATETABLE

语句中总是指定列的特性产生脚本以重建数据库和数据库对象指定适当的数据类型和大小3.5推荐操作目标创建用户定义数据类型创建表添加和删除列从数据库中生成Transact-SQL脚本练习1创建用户自定义数据类型练习2在ClassNorthwind

数据库中创建表练习3添加和删除列练习4生成Transact-SQL脚本练习5在ClassNorthwind

数据库中加载数据实验

创建数据类型和表回顾学习完本章后，将能够：

创建和删除用户定义数据类型创建和删除用户表生成列值生成脚本随堂练习1 下图所示是一个大学的管理数据库中的三张数据表的数据字典结构：

随堂练习1（续） 如果想要提高数据查询的速度和加少数据冗余，应当如何做？添加表Instructors，包含ID列、InstructorName列和OfficePhone列，将ID列加入到Courses表中将Classroom表与Course表的内容合并，删除Classroom表将Courses表中的主键约束设在CourseID和CourseTitle上删除ClassroomID列，将主键约束设在ClassNumber和ClassTime列上随堂练习2 在某个数据库中，为了产生一些查询信息，需要连接四五张数据表。假设该数据库在设计时做到了高度的规范化。在这种情况下应当如何提高数据查询的响应速度？在这四五张数据表上创建视图并在视图上创建索引降低数据库设计的规范化程度提高规范化程度在连接列上创建索引随堂练习3在为仓储公司开发的SQLServer2000数据库中，需要创建一张数据表存放了订单数据。其中OrderID必须是数据库自动生成的具备唯一性的数据列，CustomerID和CargoID分别是在另外的数据表Customers和Cargos中已有的数据中选取，OrderDate不能为空，下面四个选项中的SQL语句能实现创建功能？CREATETABLEOrders{

OrderID

intidentity(1,1)PRIMARYKEYNOTNULL,

OrderDate

datetimeNULL,

CustomerIDchar(5)NOTNULLFOREIGNKEYREFERENCESCustomers(CustomerID),

CargoID

intNOTNULLFOREIGNKEYREFERENCESCargos(CargoID),

ShipDate

datetimeNULL}随堂练习3（续）CREATETABLEOrders{

OrderID

intidentity(1,1)PRIMARYKEYNOTNULL,

OrderDate

datetimeNOTNULL,

CustomerIDchar(5)NOTNULLFOREIGNKEYREFERENCESCustomers(CustomerID),

CargoID

intNOTNULLFOREIGNKEYREFERENCESCargos(CargoID),

ShipDate

datetimeNULL}随堂练习3（续）CREATETABLEOrders{

OrderID

intidentity(1,1)PRIMARYKEYNOTNULL,

OrderDate

datetimeNO