泰州职业技术学院：《数据库技术(SQL Server 2000)》电子教案二

泰州职业技术学院电子工程系 课程授课教案 课程名称数据库技术( SQLServer2000) 主讲教师 蔡伯峰 03网商 授课班级 《数据库原理与 SQL Server2000教程》 使用教材 北京工业大学出版社,苏啸编著 理论 3 课程学分6周学时 实践 3

1 泰州职业技术学院电子工程系 课程授课教案 课程名称 数据库技术（SQL Server 2000） 主讲教师 蔡 伯 峰 授课班级 0 3 网 商 使用教材 《数据库原理与 SQL Server2000 教程》 北京工业大学出版社，苏啸编著 课程学分 6 周学时 理论 3 实践 3

授课题目(章、节) 第十讲 SQL Server2000数据类型 学时_3授课时间 周3第3~5节第10)授课 主要参考书 《 SQL Server数据库原理及应用教程》,清华大学出版社 教学目的与要求 1、了解各个数据类型的使用场合 2、能根据问题的需要选择合适的数据类型并熟练运用。 教学重点、难点 1、根据问题的需要选择合适的数据类型 大体内容 教学方法时间安排 Transact-SQL概述 面授 SQL Server支持的数据类型 面授 用户定义的数据类型 面授 教研室审阅意见 教研室主任签名 年月日

2 授课题目（章、节） 第十讲 SQL Server2000 数据类型 学时 3 授课时间 周 3 第 3～5 节 第( 10 )次授课 主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社 教学目的与要求： 1、了解各个数据类型的使用场合； 2、能根据问题的需要选择合适的数据类型并熟练运用。 教学重点、难点： 1、根据问题的需要选择合适的数据类型 大体内容 教学方法 时间安排 Transact-SQL 概述 面授 SQL Server 支持的数据类型 面授 用户定义的数据类型 面授 教研室审阅意见： 教研室主任签名： 年 月 日

教学过程(基本内容) 辅助手段备注 时间分配 一、 Transact-SQL概述 SQL(结构化数据查询语言, Structure Query Language),是关系型 数据库( RDBMS)的应用语言 Transact-SQL是在包含了大多数 ANSI SQL89和 ANSI SQL92功能 的基础上对 ANSI SQL92的扩展,对语法作了一些精简,增强了可编程和 灵活性,它是非标准的SQL,是基于商业应用的SQL,用于编写具有实 际意义的CS(客户/服务器)模式下的数据库应用程序,要想移植到其 它平台上只需修改少量语法即可。它是 Server2000的核心组件之一。 尽管 SQL Server2000提供了使用方便的图形化用户界面,但各种功 能的实现基础是 Transact-SQL语言,只有 Transact-SQL可以直接和数据 库引擎进行交互。 1. Transact-SQL的特点 体化的特点。 Transact-SL语言集数据定义语言、数据操纵 语言、数据控制语言和附加语言元素(包括变量、运算符、函 数、流程控制语句和注释等)为一体 两种使用方式,统一的语法结构。两种使用方式即联机交互式 和嵌入高级语言的使用方式 高度非过程化。 Transact-SQL语言一次处理一个/多个记录 所有的SQL语句接受集合作为输入,返回集合作为输出,并允 许一条S吼L语句的结果作为另一条SQL语句的输入。另外, Transact-SQL语言不要求用户指定对数据的存放方法,所有的 Transact-SQL语句使用查询优化器,用以指定数据以最快速度 存取的手段 类似于人的思维习惯,容易理解和掌握 2.SQL语句的构成及提交 构成:任何一条 Transact-SQL语句至少需要包含一个命令动词,即 个明确动作含义的动词。SQL语句中的命令动词是一种对SQL服务器 有特定意义的关键字,而其它关键字则是在SQL语句中引入的其它限制 条件。 提交:用户通过查询分析器或应用程序向服务器提交 Transact-SQL

3 教学过程(基本内容) 辅助手段 备注 时间分配 一、Transact-SQL 概述 SQL（结构化数据查询语言，Structure Query Language），是关系型 数据库（RDBMS）的应用语言。 Transact-SQL 是在包含了大多数 ANSI SQL 89 和 ANSI SQL 92 功能 的基础上对 ANSI SQL92 的扩展，对语法作了一些精简，增强了可编程和 灵活性，它是非标准的 SQL，是基于商业应用的 SQL，用于编写具有实 际意义的 C/S（客户/服务器）模式下的数据库应用程序，要想移植到其 它平台上只需修改少量语法即可。它是 Server 2000 的核心组件之一。 尽管SQL Server 2000提供了使用方便的图形化用户界面，但各种功 能的实现基础是Transact-SQL语言，只有Transact-SQL可以直接和数据 库引擎进行交互。 1．Transact-SQL 的特点 ⚫ 一体化的特点。Transact-SQL语言集数据定义语言、数据操纵 语言、数据控制语言和附加语言元素（包括变量、运算符、函 数、流程控制语句和注释等）为一体。 ⚫ 两种使用方式，统一的语法结构。两种使用方式即联机交互式 和嵌入高级语言的使用方式。 ⚫ 高度非过程化。Transact－SQL语言一次处理一个/多个记录， 所有的 SQL语句接受集合作为输入，返回集合作为输出，并允 许一条 SQL语句的结果作为另一条 SQL语句的输入。另外， Transact-SQL语言不要求用户指定对数据的存放方法，所有的 Transact-SQL语句使用查询优化器，用以指定数据以最快速度 存取的手段。 ⚫ 类似于人的思维习惯，容易理解和掌握。 2．SQL 语句的构成及提交 构成：任何一条 Transact-SQL 语句至少需要包含一个命令动词，即 一个明确动作含义的动词。SQL 语句中的命令动词是一种对 SQL 服务器 有特定意义的关键字，而其它关键字则是在 SQL 语句中引入的其它限制 条件。 提交：用户通过查询分析器或应用程序向服务器提交 Transact-SQL

语句时均采用批处理的形式(在书写批处理语句时,需要使用GO语句 作为批处理命令的结束标志。当编译器读取到GO语句时,它会把Go 句前面所有的语句当作一个批处理,并将这些句打包发送给服务器。 GO语句本身并不是 Transact-SQL语句的组成部分,它只是一个用于表 示批处理结束的前端指令 3.服务器处理SQL语句的过程 当服务器收到一条SQL语句后,将依次进行 1)分析2)优化3)编译4)执行 二、 SQL Server支持的数据类型(26种,参见“数据表的创建与维护”) 在SQL中,数据类型通常指字段、存储过程参数和局部变量的 数据特征 数据类型通常由SQL系统定义,称为系统数据类型。 SQL Server 也支持用户定义的数据类型(并非真正的用户定义,而是对现 有类型的一种具体化描述)。 (一)、字符型 字符型(由单引号括起)用来存储非统一编码型或统一编码的数据。 分为四种:定长char及 nchar、可变长 varchar及 nvarchar 1.字符型字段的长度 定义字符型字段时,必须规定它的最大长度,以表明它所能存储的 最大字符数 选择char或 varchar类型的原则字段中字符长度是否固定。 ■对char,若实际长度不足,则以空格填充到尾部 可变长度的字符结构需要额外的存储开销(主要是开辟一 字节用于指明究竞多长) 尽管 varchar需要额外的存储开销,但能够删除字段尾部的 空格 2.关于截断字符串 向表中插入字符串时,如果实际长度大于字段的定义长度,则系统

4 语句时均采用批处理的形式（在书写批处理语句时，需要使用 GO 语句 作为批处理命令的结束标志。当编译器读取到 GO 语句时，它会把 GO 语句前面所有的语句当作一个批处理，并将这些语句打包发送给服务器。 GO 语句本身并不是 Transact-SQL 语句的组成部分，它只是一个用于表 示批处理结束的前端指令）。 3．服务器处理 SQL 语句的过程 当服务器收到一条 SQL 语句后，将依次进行： 1）分析 2）优化 3）编译 4）执行 二、SQL Server 支持的数据类型（26 种，参见“数据表的创建与维护”） ⚫ 在 SQL 中，数据类型通常指字段、存储过程参数和局部变量的 数据特征。 ⚫ 数据类型通常由 SQL 系统定义，称为系统数据类型。SQL Server 也支持用户定义的数据类型（并非真正的用户定义，而是对现 有类型的一种具体化描述）。 （一）、字符型 字符型（由单引号括起）用来存储非统一编码型或统一编码的数据。 分为四种：定长 char 及 nchar、可变长 varchar 及 nvarchar。 1．字符型字段的长度 定义字符型字段时，必须规定它的最大长度，以表明它所能存储的 最大字符数。 ◼ 选择 char 或 varchar 类型的原则：字段中字符长度是否固定。 ◼ 对 char，若实际长度不足，则以空格填充到尾部。 ◼ 可变长度的字符结构需要额外的存储开销（主要是开辟一 字节用于指明究竟多长） ◼ 尽管 varchar 需要额外的存储开销，但能够删除字段尾部的 空格 2．关于截断字符串 向表中插入字符串时，如果实际长度大于字段的定义长度，则系统

将报告错误信息,且不向表中插入该记录。 (二)、十六进制型(也即二进制型) 有两种:固定长度 binary、可变长度 varbinary。用于存储十六进制数 据或十六进制字符串 十六进制数据由09、A_F或af组成:由0x开头,其中每 两个字符为一组,共同构成一个字节,不需加引号。若输入的 十六进制数据位数为单数,则在最高位加0变成双数,以便按 整字节存放,再在尾部补0。 输入十六进制型数据只能通过SQL的 insert进行,在企业管理 器中不能向表中直接输入数据 (三)、文本型和图像型 即:text、 ntext、 Image text/ ntext字符型,用来存储大量的非统一编码/统一编码型字 符数据。其容量理论上为1到231-1个字节,但实际应用时要 根据硬盘的存储空间而定。 对text、 ntext和mage字段的任何更新操作,都会引起对这些字 段的初始化操作, SQL Server为它们分配至少2kB的存储空间 故应慎用更新操作 在 SQL Server2000中,小的text、 ntext可以直接存储在记录中 大的text、 ntext或 Image值被存储在页集合中。每个表只有一个保存 text、 ntext和 Image数据的页集合,text、 ntext和 Image数据可以保存 在同一页中。在 sysindexes中 indid=255的记录保存了这个页集合的起始 位置。当text、 ntext或 Image值被存储在页集合中时,在记录的相应字 段中将存放一个16位的指针来代替text、 ntext或 Image的信息,该指针 指向页集合 例 create table text exl(PId int not null, name char( 8), text field text) insert text exl(PID, text field) values( 101, null) insert text exI(PID, text field) values103我们胜利了!)

5 将报告错误信息，且不向表中插入该记录。 （二）、十六进制型（也即二进制型） 有两种：固定长度 binary、可变长度 varbinary。用于存储十六进制数 据或十六进制字符串。 ⚫ 十六进制数据由 0—9、A—F 或 a—f 组成：由 0x 开头，其中每 两个字符为一组，共同构成一个字节，不需加引号。若输入的 十六进制数据位数为单数，则在最高位加 0 变成双数，以便按 整字节存放，再在尾部补 0。 ⚫ 输入十六进制型数据只能通过 SQL 的 insert 进行，在企业管理 器中不能向表中直接输入数据。 （三）、文本型和图像型 即：text、ntext、image ⚫ text/ntext 字符型，用来存储大量的非统一编码/统一编码型字 符数据。其容量理论上为 1 到 2 31-1 个字节，但实际应用时要 根据硬盘的存储空间而定。 ⚫ 对 text、ntext 和 image 字段的任何更新操作，都会引起对这些字 段的初始化操作，SQL Server 为它们分配至少 2kB 的存储空间， 故应慎用更新操作。 在 SQL Server 2000中，小的text、ntext可以直接存储在记录中， 大的text、ntext或image值被存储在页集合中。每个表只有一个保存 text、ntext和image数据的页集合，text、ntext和 image数据可以保存 在同一页中。在sysindexes中indid=255的记录保存了这个页集合的起始 位置。当text、ntext或image值被存储在页集合中时，在记录的相应字 段中将存放一个16位的指针来代替text、ntext或image的信息，该指针 指向页集合。 例 1： create table text_ex1(PID int not null,name char(8),text_field text) insert text_ex1(PID,text_field) values(101,null) insert text_ex1(pid) values(102) insert text_ex1(PID,text_field) values(103,'我们胜利了！')

insert text exI(PID, text field) values(104 replicate(我们己取得了雅典 奥运会的的全面胜利!,100) select from text exI 例2: create table text ex2(PId int not null, name char(8), image field lage) insert text ex2(PID, image field) values(104, 0x4ab5435bf556d) select from text ex2 (四)、时间戳型 如果在表中定义了时间戳型的字段,则无论何时添加记录、修改记 录,系统都会向该字段中加入一个计数器值或修改计数器值。 时间戳字段的值为二进制格式的数据,与系统时间没有关系, 它表示对该记录的操作顺序。占8 Bytes 个表中只能有一个时间戳字段。在数据库中,该值保持唯 时间戳字段的值由系统自动确定,不允许用户修改或设置。用 Insert插入数据时,除该字段外的字段需列出,不能全部省略。 时间戳字段可以用于防止多个用户同时修改表的同一记录 例 create table timestamp ex( PID int, name char(8), tis timestamp) insert timestamp ex(PID, name) values(101, cbf1") insert timestamp ex(PID, name) values( 102, cbf2") select from timestamp ex 例2: update timestamp ex

6 insert text_ex1(PID,text_field) values(104,replicate('我们已取得了雅典 奥运会的的全面胜利！',100)) go select * from text_ex1 例 2： create table text_ex2(PID int not null,name char(8),image_field image) insert text_ex2(PID,image_field) values(104,0x4ab5435bf556d) go select * from text_ex2 （四）、时间戳型 如果在表中定义了时间戳型的字段，则无论何时添加记录、修改记 录，系统都会向该字段中加入一个计数器值或修改计数器值。 ⚫ 时间戳字段的值为二进制格式的数据，与系统时间没有关系， 它表示对该记录的操作顺序。占 8Bytes。 ⚫ 一个表中只能有一个时间戳字段。在数据库中，该值保持唯一。 ⚫ 时间戳字段的值由系统自动确定，不允许用户修改或设置。用 insert 插入数据时，除该字段外的字段需列出，不能全部省略。 ⚫ 时间戳字段可以用于防止多个用户同时修改表的同一记录。 例 1： create table timestamp_ex(PID int, name char(8), tis timestamp) insert timestamp_ex(PID,name) values(101,'cbf1') insert timestamp_ex(PID,name) values(102,'cbf2') select * from timestamp_ex 例 2： update timestamp_ex

set name=cbf22 here tis=0x000010此处tis的值应为表中某个记录中的值 update timestamp ex et name=cbf22 where PID=102 and sequal(tis, OX00000000000001F5 此处“PID=102”不能少,因为 sequal要求首先定位记录 (五)、日期时间型 datetime和 smalldatetime的区别: 项目 datetime Smalldatetime 最小值 1753-1-1 900-1-1 最大值 9999-12-31 2079-6-6 精度 I min 占用空间 8Bytes 4Bytes datetime类型的数据以字符串的形式传递给服务器,服务器负责进行 转换和合法性验证。 服务器默认的日期格式为mm/ddy(mdy格式,见“区域”设置) 可以使用 SET Dateformat [ ymd语句来设置日期格式,如mdy、ymd、dmy、 ydm、myd、dym。年月日之间可以使用/、-和分隔 datetime的值四舍五入为3ms, smalldatetime的值四舍五入为 lmin。 对于没有时间的数据,默认时间为00:00:00:对于没有日期 的时间值,默认日期为1900年1月1日(不论 datetime还是 smalldatetime),但没有日期的时间毫无意义 所有小于50的年份被认为是21世纪,>=50的两位年份被认为 是20世纪。最好写4位数 例1:演示多种日期的输入格式,并显示其结果

7 set name='cbf22' where tis=0x0000000000000192 --此处 tis 的值应为表中某个记录中的值 或： update timestamp_ex set name='cbf22' where PID=102 and tsequal(tis,0x00000000000001F5) –此处“PID=102”不能少，因为 tsequal 要求首先定位记录。 （五）、日期时间型 datetime 和 smalldatetime 的区别： 项目 datetime Smalldatetime 最小值 1753-1-1 1900-1-1 最大值 9999-12-31 2079-6-6 精度 3ms 1min 占用空间 8Bytes 4Bytes datetime 类型的数据以字符串的形式传递给服务器，服务器负责进行 转换和合法性验证。 服务器默认的日期格式为 mm/dd/yy（mdy 格式，见“区域”设置）。 可以使用 SET Dateformat [ymd]语句来设置日期格式，如 mdy、ymd、dmy、 ydm、myd、dym。年月日之间可以使用/、-和.分隔。 ⚫ datetime 的值四舍五入为 3ms，smalldatetime 的值四舍五入为 1min。 ⚫ 对于没有时间的数据，默认时间为 00：00：00；对于没有日期 的时间值，默认日期为 1900 年 1 月 1 日(不论 datetime 还是 smalldatetime)，但没有日期的时间毫无意义。 ⚫ 所有小于 50 的年份被认为是 21 世纪，>=50 的两位年份被认为 是 20 世纪。最好写 4 位数。 例 1：演示多种日期的输入格式，并显示其结果

Set dateformat mdy create table date ex(PId int not null date field datetime) insert date ex values(101, 2003-8-30) insert date ex values( 102, 2003/8/30) insert date ex values( 103, 2003.8.30) insert date ex values( 1033, 8.30.2003") insert date ex values( 10333, 8.2003.30) -4位年份写全时,不受mdy限制,此时可等价使用mdy、 myd、ymd insert date ex values(104, 2003-8-30 20: 30: 21) insert date ex values(105,2003-8-3020:30:21.765) insert date ex values(106, 20: 30: 21) insert date ex valuest(107,8-30-48)-因日期格式为mdy insert date ex values( 108, 8-30-56 20: 30: 21) select from date ex 例2根据日期(不含时间)进行查询。当日期字符串中不含时间时, 默认时间为00:00:00。 例3:根据日期(含时间)进行查询。 例4:根据时间(不含日期)进行查询。当日期中不含有年月日信息 时,默认为1900年1月1日。 例5:表示某一天的日期数据: date field>>=200349 )'and date field =2003-4-923:59:59999 (六)、位型 bit是SQL中支持的逻辑数据类型,用于存储0或1。 bit字段通常用于表示 ON/OFF、 TRUE/FALSE。用作状态标志 位、活动帐户指示器、项目使用标志等

8 Set dateformat mdy create table date_ex(PID int not null ,date_field datetime) insert date_ex values(101,'2003-8-30') insert date_ex values(102,'2003/8/30') insert date_ex values(103,'2003.8.30') insert date_ex values(1033,'8.30.2003') insert date_ex values(10333,'8.2003.30') --当 4 位年份写全时，不受 mdy 限制，此时可等价使用 mdy、 myd、ymd insert date_ex values(104,'2003-8-30 20:30:21') insert date_ex values(105,'2003-8-30 20:30:21.765') insert date_ex values(106,'20:30:21') insert date_ex values(107,'8-30-48') --因日期格式为 mdy insert date_ex values(108,'8-30-56 20:30:21') select * from date_ex 例 2：根据日期（不含时间）进行查询。当日期字符串中不含时间时， 默认时间为 00：00：00。 例 3：根据日期（含时间）进行查询。 例 4：根据时间（不含日期）进行查询。当日期中不含有年月日信息 时，默认为 1900 年 1 月 1 日。 例 5：表示某一天的日期数据：date_field>=’2003-4-9’ and date_field <=’2003-4-9 23:59:59.999’ （六）、位型 bit 是 SQL 中支持的逻辑数据类型，用于存储 0 或 1。 ⚫ bit 字段通常用于表示 ON/OFF、TRUE/FALSE。用作状态标志 位、活动帐户指示器、项目使用标志等

bit类型字段的长度为 I Byte数据,如果在表中包含多个bt型字 段,则它们可以共用同一字节 bit字段不要使用空值,因为毫无意义 例 create table bit ex(编号char(9) not null性别bit婚否 bit not null, 党员否bit) /*1-男0-女* insert bit ex values( 200308001, 1,0,0) insert bit ex values(200308002, 0, 1, 1) SELECt FRom bit ex (七)、数值数据类型 整数类int、 smallint、 tinyint、 bigint 浮点类 float、real ●精确数值 numerIc、 decimal 货币类 money、 1.整数类型 用于存储精确的整数数值,其存储结构效率高,并且在本地进行处 理,在各种平台上处理速度均较快,使用较多。四种整数类型的比较: 类型 最小值 最大值 占用空间 Bigint 263-1 8Bytes 231(约21亿)|23-1(约21亿)|4Byte 215(约-3万) smallint 215-1(约3万)2 Bytes 255 例 create table int ex(编号 int identity((1,3),姓名char(8),工资 tinyint) insert int ex values张三,1200,28) ex valuest(张四980,33) insert int ex values张五,1800,24)

9 ⚫ bit 类型字段的长度为 1Byte 数据，如果在表中包含多个 bit 型字 段，则它们可以共用同一字节。 ⚫ bit 字段不要使用空值，因为毫无意义。 例： create table bit_ex(编号 char(9) not null,性别 bit,婚否 bit not null , 党员否 bit) /*1-男 0-女*/ insert bit_ex values('200308001',1,0,0) insert bit_ex values('200308002',0,1,1) SELECT * FROM bit_ex （七）、数值数据类型 ⚫ 整数类 int、smallint、tinyint、bigint ⚫ 浮点类 float、real ⚫ 精确数值 numeric、decimal ⚫ 货币类 money、smallmoney 1．整数类型 用于存储精确的整数数值，其存储结构效率高，并且在本地进行处 理，在各种平台上处理速度均较快，使用较多。四种整数类型的比较： 类型 最小值 最大值 占用空间 Bigint -2 63 2 63 -1 8Bytes Int -2 31（约-21 亿） 2 31 -1（约 21 亿） 4Bytes smallint -2 1 5（约-3 万） 2 15 -1（约 3 万） 2Bytes tinyint 0 255 1Bytes 例 1： create table int_ex (编号 int identity(1,3) , 姓名 char(8), 工资 smallint,年龄 tinyint) insert int_ex values('张三',1200,28) insert int_ex values('张四',980,33) insert int_ex values('张五',1800,24)

elect from int 关键字 identit为自动增量字段,类型可为整型、精确数值型(小数 位数必须为0),它用于标识该字段的值自动增加(初始种子值和增量值 会四舍五入成整数): create table int exI(编号 numeric identity(2.3,4.6),姓名char(8) 工资 smallint龄 tinyint)-初始种子值:2增量值:5 也可用 decimal insert int ex valuest(张三,1200,28) insert int ex values(张四",980,33 nsert int ex values张五,1800,24) select from int ex 2.近似值类型(浮点类型) 类型最小值 最大值 精度 占用空间 foat|-1.7E+308+1.79E+30815位(均精确)8 Bytes real-3.40E+38+340E+38|7位(均精确)|4 Bytes E+308代表10308,称为阶数,179称为尾数。在SQL中,foat real通常采用科学计数法表示。 精度,即数据的总长度,不包括符号和小数点。 用于科学计算或统计方面,因为其数值往往很大,但无需绝对 的精度 例 create table real ex(编号 int identity((2,4),姓名char(8),工资rel) insert real ex values(张三,1200.82673)-注意7位精度的处理 insert real ex values'张四,980) insert real ex values(张五’,1800.95) select from real ex 3.精确数值类型

10 select * from int_ex 关键字 identity 为自动增量字段，类型可为整型、精确数值型（小数 位数必须为 0），它用于标识该字段的值自动增加（初始种子值和增量值 会四舍五入成整数）： create table int_ex1(编号 numeric identity(2.3,4.6) , 姓名 char(8), 工资 smallint,年龄 tinyint) --初始种子值：2 增量值：5； 也可用 decimal insert int_ex values('张三',1200,28) insert int_ex values('张四',980,33) insert int_ex values('张五',1800,24) select * from int_ex1 2．近似值类型（浮点类型） 类型 最小值 最大值 精度 占用空间 float -1.79E+308 +1.79E+308 15 位(均精确) 8Bytes real -3.40E+38 +3.40E+38 7 位(均精确) 4Bytes ⚫ E+308 代表 10308，称为阶数，1.79 称为尾数。在 SQL 中，float、 real 通常采用科学计数法表示。 ⚫ 精度，即数据的总长度，不包括符号和小数点。 ⚫ 用于科学计算或统计方面，因为其数值往往很大，但无需绝对 的精度。 例： create table real_ex(编号 int identity(2,4) , 姓名 char(8), 工资 real) insert real_ex values('张三',1200.82673) --注意 7 位精度的处理 insert real_ex values('张四',980) insert real_ex values('张五',1800.95) select * from real_ex 3．精确数值类型