数据库数据类型设置.ppt

第4章 数据表的创建与操作,4.1 数据类型 4.2 运算符与表达式 4.3 系统内置函数 4.4 用企业管理器创建数据表及约束对象 4.5 用T-SQL语句创建数据表及约束对象 4.6 查看表信息、输入数据、编辑和删除记录 4.7 数据表及约束对象的删除 4.8 数据库应用实例电脑器材销售管理的数据表 4.9 实训要求与习题,第4章 数据表的创建与操作,学习目的与要求 数据表是数据库中最重要的对象，数据库的全部数据都存储在不同的数据表中，因而数据表的设计成功与否是数据库设计的关键，将直接影响到数据库使用的合理有效。了解SQL Server数据库系统，掌握数据表的数据类型、运算符与表达式、常用系统函数是创建数据库必备的基础知识。数据表及约束对象的创建以及数据的输入更新是创建数据库的重要环节。通过本章学习，读者应熟悉并掌握SQL Server数据库的数据类型、表达式和常用内置函数，掌握数据表及约束对象的创建、修改、删除以及数据的输入、更新、删除等操作，独立创建电脑器材销售管理diannaoxs数据库的各个数据表。,4.1 数据类型,数据类型决定了数据的存储格式、长度、精度等属性。SQL Server为我们提供了多达26种的丰富数据类型，用户还可以自己定义数据类型（见第7章）。 4.1.1、二进制数据 SQL Server用binary、varbinary 和image三种数据类型来存储二进制数据。二进制类型可用于存储声音图像等数字类型的数据。 1、定长二进制 binary(n) 按n个字节的固定长度存放二进制数据，最大长度为8K字节，即1n8000。 若实际数据不足n个字节，则在数据尾部加0补足n个字节。 2、变长二进制 varbinary(n) 按不超过n个字节的实际长度存放二进制数据，最大长度8K字节，即1n8000。 若实际数据不足n个字节，按实际长度存储数据，不补充加0；,3、图像二进制 Image,图像二进制可存储不超过231-1个字节的二进制数据，比如文本文档、Excel图表以及图像数据（包括.GIF、.BMP、.JPEG文件）等。 注意： l 二进制数据常量不允许加引号，默认用十进制书写，如1234、1011均视为十进制常量，如果使用十六进制则必须加0 x前缀。输出显示默认采用十六进制。 l若实际数据二进制长度超过指定的n个字节，用局部变量存储时只截取二进制的前n个字节，其余二进制位舍掉。 l数据库中二进制字段的数据不能在数据表中直接输入，即“无法编辑该单元”。 l在用INSERT或UPDATE为数据表二进制字段输入、更新数据时，如果超过n字节，则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。,【例4-1】二进制数据1aa2bb3cc4共40位5个字节，可表示为0 x1aa2bb3cc4 若定义数据类型为binary(6)则数据后加0按6字节存储为：0 x1aa2bb3cc400 若定义数据类型为varbinary(6)则按实际数据存储为：0 x1aa2bb3cc4 若定义数据类型为binary(4)或varbinary(4)，则在局部变量中存储为：0 x1aa2bb3c。 存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。,4.1.2、数值型数据,SQL Server数值型数据共8种，整型4种，实型4种。 1字节型整数 TinyInt 占1个字节固定长度内存，可存储0-255范围内的任意无符号整数。 2短整型整数 smallInt 占2字节固定长度内存，最高位为符号位，可存储-3276832767（-215215-1）的任意整数。 3基本整型整数 Int 或Integer 占4字节固定长度内存，高位为符号位，可存储-2147483648-2147483647（-231-231-1）范围内的任意整数。 4长整型整数 Bigint 占8字节固定长度内存，高位为符号位，可存储-263到263-1范围内的任意整据。 注意：整型数据可以在较少字节里存储精确的整型数字，存储效率高，不可能出现小数的数据应尽量选用整数类型。,5近似值实型浮点数 real,占4字节固定长度内存，最多7位有效数字，范围从-3.40E+38到1.79E+38。 6可变精度实型浮点数 float(n) l当n的取值为1-24时，数据精度是7位有效数字，范围从-3.40E+38到1.79E+38，占4字节内存。 l 当n的取值为25-53时，精度是15位有效数字，范围从-1.79E+308到1.79E+308，占8字节内存。 l实型浮点数常量可以直接使用科学记数法的指数形式书写。,7精确小数型数据 Numeric(p,s),lp指定总位数（不含小数点），p的取值范围1p38。即最多可达38位有效数字，不使用指数的科学记数法表示，但取值范围必须在-1038到1038-1之间。 ls指定其中的小数位数，s的取值范围0sp。 l numeric型数据在数据表里可带identity关键字（自动增长字段）。 lnumeric型数据所占的存储空间随精度的不同而不同，一般说来对应关系为: p的取值范围 数据所占内存字节数 1 - 9 5 10-19 9 20-28 13 29-38 17 8精确小数型数据 Decimal(p,s) 或 Dec(p,s) 该类型数据与Numeric(p,s)类型用法相同，所不同的是Decimal(p,s)不能用于数据表的identity字段。,8精确小数型数据 Decimal(p,s) 或 Dec(p,s),该类型数据与Numeric(p,s)类型用法相同，所不同的是Decimal(p,s)不能用于数据表的identity字段。,4.1.3字符型数据,SQL Server提供了Char(n)、Varchar(n)和Text三种ASC码字符型数据。 1定长字符型 Char(n) 按n个字节的固定长度存放字符串，每个字符占用一个字节，长度范围1n8000； 若实际字符串长度小于n，则尾部填充空格按n个字节的字符串存储。 2变长字符型 Varchar(n) 按不超过n个字节的实际长度存放字符串，可指定最大长度为1n8000； 若实际字符串长度小于n，则按字串实际长度存储，不填充空格。 当存储的字符串长度不固定时，使用Varchar数据类型可以有效地节省空间。,3文本类型 Text,Text类型存储的是可变长度的字符数据类型，最大长度为231-1字节2GB的数据。当存储超过8000字节时，可选择Text数据类型。 l字符型数据采用ASC码字符集，英文符号占1个字节，汉字占2个字节。 l字符型字符串常量必须使用单引号括起来。 lChar、Varchar数据类型若不指定长度，默认为1字节。 l若实际字符串长度超过指定的n个字节，用局部变量存储时只截取字符串前n个字节，其余字符舍掉。 l字符型字符串数据可以在企业管理器中直接输入到数据表中，但输入的字符串长度超过字段指定长度时系统不接受，也不会自动截取。 l在用INSERT或UPDATE为数据表字符型字段输入、更新字符串时，如果超过n字节则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。,【例4-2】字符型字符串abcdABCD我们学习共12个字符占16字节。 若定义数据类型为char(20)则存储为： abcdABCD我们学习 若定义数据类型为varchar(20)则按实际长度存储为： abcdABCD我们学习 若定义数据类型为char(10)或varchar(10)，则在局部变量中存储为： abcdABCD我 存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。,4.1.4统一字符型数据,统一字符型也称为宽字符型，采用Unicode字符集，包括了世界上所有语言符号，一个英文符号或一个汉字都占用2个字节的内存。前127个字符为ASC码字符。 SQL Server提供了Nchar(n)、Nvarchar(n)和Ntext三种统一字符型数据。 1定长统一字符型 Nchar(n) 按n个字符的固定长度存放字符串，每个字符占用2个字节，长度范围1n4000； 若实际字符个数小于最大长度n，则尾部填充空格按n个字符存储。 2变长统一字符型 Nvarchar(n) 按不超过n个字符的实际长度存放字符串，可指定最大字符数为1n4000； 若实际字符个数小于n，则按字符串实际长度占用存储空间，不填充空格。,3统一字符文本类型 Ntext,Ntext存储的是可变长度的双字节字符数据类型，最多可以存储（230-1）/2个字符。 l统一字符型数据长度n为“字符”个数，所占字节数为2*n。 l统一字符型字符串常量必须使用单引号括起来。 lNchar、Nvarchar数据类型若不指定长度n，默认为1个字符（2个字节）。 l若实际字符串长度超过指定的n个字符，用局部变量存储时只截取字符串前n个字符，其余字符舍掉。 l统一字符型数据可以在企业管理器中直接输入到数据表中，但输入的字符个数超过字段长度时则系统不接受。 l在用INSERT或UPDATE为数据表统一字符型字段输入、更新字符串时，若超过n个字符则系统提示“将截断字符串或二进制数据”并终止命令执行，不能保存该数据。,【例4-3】字符串abcdABCD我们学习作为统一字符型共12个字符占24字节。 若定义数据类型为Nchar(14)则存储为： abcdABCD我们学习 若定义数据类型为Nvarchar(14)则按实际字符数存储为： abcdABCD我们学习 若定义数据类型为Nchar(10)或Nvarchar(10)，则在局部变量中存储为： abcdABCD我们 存储到数据表字段中时，系统提示“将截断字符串或二进制数据”不能保存该数据。,4.1.5日期/时间型数据,SQL Server提供的日期/时间数据类型可存储日期和时间的组合数据。以日期/时间类型存储日期或时间数据比字符型更简单，因为SQL Server提供了一系列专门处理日期和时间的函数来处理这类数据。若使用字符型存储日期和时间，计算机不能识别，也不能自动对这些数据按照日期和时间进行处理。 SQL Server提供了Smalldatetime和Datetime两种日期/时间的数据类型。 1短日期/时间型 Smalldatetime 占4个字节固定长度的内存，存放1900年1月1日到2079年6月6日的日期时间，可以精确到分。,2基本日期/时间型 Datetime,占8个字节固定长度的内存，存放1753年1月1日到9999年12月31日的日期时间，可以精确到千分之一秒，即0.001s。 注意： l日期时间型常量与字符串常量相同必须使用单引号括起来。 lSQL Server在用户没有指定小时以下精确的时间数据时，自动设置Datetime或Smalldatetime数据的时间为00:00:00。 l数据库中默认的日期格式为“年-月-日”，输入时可使用年/月/日或年-月-日，也可以使用月/日/年、月-日-年、日/月/年或日-月-年。 l如果使用日/月/年或日-月-年，系统不能区分时默认按“月-日-年”处理。,4.1.6货币型数据,SQL Server提供了Smallmoney和Money两种货币型数据。 1短货币型 Smallmoney 该类型占4个字节固定长度的内存，实际是由2个2字节的整数构成，前2个字节为货币值的整数部分，后2个字节为货币值的小数部分。货币值的范围从-214748.3648到+214748.3647，可以精确到万分之一货币单位。 2基本货币型 Money 该类型占8个字节固定长度的内存，由2个4字节的整数构成，前4个字节为货币值的整数部分，后4个字节表示货币值的小数部分。货币值的范围从-263到263-1，可精确到万分之一货币单位。 Money或Smallmoney类型的数值常量，应加货币符号$前缀，负数时加后缀$。 如：$222.222 , -333.333$,4.1.7位类型数据 bit,l位类型只能存放0、1和NULL(空值)，一般用于逻辑判断。 l位类型数据占1位二进制内存，如果一个数据表中有8个以下的位类型字段，系统用一个字节存储所有这些字段，超过8个不足16个用2个字节存放。 l位类型数据输入任意的非0值时，都按1处理。 SQL Server 常用数据类型见表4.1。,4.1.8其他特殊数据类型,1、Timestamp： 也称为时间戳数据类型，它提供数据库范围内的惟一值，反应数据库中数据修改的相对顺序，相当于一个单调上升的计数器。 2、Uniqueidentifier： 用于存储16字节长度的二进制数据类型，是SQL Server根据计算机网络适配器地址和CPU时钟而产生的全局惟一标识符代码（Globally Unique Identifier，简写为GUID）。 3、sql_variant： 用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据。 4、table： 用于存储对表或者视图处理后的结果集。这种新的数据类型使得用一个变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。,4.1.9图像、文本型数据的存储方式,存储文本、图像等大型数据时，可使用Text、Ntext和Image三种数据类型，这三种数据类型的数据量往往比较大，在SQL Server 7.0以前的版本中，文本和图像数据被存储在专门的页中，在数据行的相应位置只保存指向这些数据的指针。 SQL Server 2000提供了将小型文本或图像数据直接在行中存储的功能，不需要到另外的页中访问这些数据，使得读写文本和图像数据可以与读写普通字符串一样快。 使用系统存储过程sp_tableoption可以指定文本或图像数据是否在表的行中存储。 语法格式： sp_tableoption 表名,text in row,TRUE|FALSE,4.1.9图像、文本型数据的存储方式,说明： l 当指定“TRUE”选项时，允许小型文本或图像数据直接在该表的行中存储，而且还可以指定文本或图像数据大小的上限值（24-7000字节），默认上限256字节。 l当数据的大小不超过上限值而且同时数据行有足够空间时，文本和图像数据就会直接存储在行中。 l若以上两个条件有一个不满足时，行中仍只存放指向数据存储位置的指针。 【例4-4】在当前数据库中创建数据表example，字段bin_1存放TEXT类型数据，bin_2存放NTEXT类型数据，bin_3存放Image类型数据。 CREATE TABLE example(bin_1 text,bin_2 ntext,bin_3 ntext Image) Go /* 以下语句指定不大于1000字节的文本或图像数据在行中存储 */ sp_tableoption example, text in row, TRUE sp_tableoption example ,text in row, 1000 /* 以下语句指定Mytable表不在行中存储文本和图像数据 */ sp_tableoption Mytable , text in row, FALSE,4.1.10、局部变量的定义与输出,本书在第7、8章将专门详细介绍T-SQL的程序设计、批处理、局部变量、自定义类型和函数、游标、存储过程与触发器，但在前几章也许会用到批处理及局部变量的概念，为此我们先简单介绍一下有关的知识。 1、批处理 批处理是一个或多个SQL语句的集合，构成一个独立的程序模块，以GO语句为结束标志。从程序开头或从某一个GO语句开始到下一个GO语句或程序结束为一个批处理。 2、局部变量 局部变量是用户自定义的变量，用于临时存储各种类型的数据。,3、定义局部变量,语法格式： DECLARE 变量名 数据类型(长度) ，n 例如：DECLARE x int , s decimal(8.4) 注意： l局部变量必须以开头以区别字段名变量。固定长度的类型不需要指定长度。 l 局部变量只在一个批处理内有效，其生命周期从定义开始到它遇到的第一个GO语句或者到程序结束。 4、局部变量的赋值 语法格式： SET 局部变量=表达式 5、显示输出局部变量 语法格式： PRINT 局部变量或表达式,4.2 运算符与表达式,4.2.1、算术运算符与表达式 算术运算符： +(加)、-(减)、*(乘)、/(除)、%(取模求余)。,4.2.2、逻辑类运算符与逻辑值表达式,SQL逻辑值表达式都是作为判断条件使用的，取值为： l TRUE： 真，条件成立 l FALSE： 假，条件不成立 l UNKNOWN：不确定，是某个数据与NULL比较的结果 在数据库中，NULL是一个不知道或不能确定的专用数据值，它不等于数值0和字符的空格。某个数据与NULL进行比较运算的逻辑值就是UNKNOWN，因为大家对使用UNKNOWN都不习惯，所以大多数SQL版本都提供了专门的空值运算符“IS NULL”，用于判断是否是空值NULL。 例如：假设“职称”的内容为NULL，若有条件表达式： 职称=讲师 则该表达式的值不是true或false而是unknown。 SQL Server 2000提供了由7类运算符组成的逻辑值条件表达式。,1、比较运算符,(大于)、=(大于等于)、或!=(不等于)、!(不大于)、! 500 则单价大于500条件为TRUE，单价不大于500条件为FALSE 销售单价-进价=销售单价/2 则毛利润大于等于一半时条件为真,2、逻辑运算符,not (逻辑非)、 and (逻辑与)、 or (逻辑或) 逻辑条件表达式： not 逻辑值表达式 逻辑值表达式1 and 逻辑值表达式2 逻辑值表达式1 or 逻辑值表达式2 如：not单价 500 则单价不大于500条件为真，等价于：单价 =1000 则只有孙立华收到的商品中进价大于等于1000时条件才为真 如：单价=500 or 单价=1000 则单价等于500或者等于1000，只要满足一个条件就为真,2、逻辑运算符,在逻辑运算中，逻辑值UNKNOWN(不确定)可以看成是介于真假之间的中立值，既不是真也不是假，不真不假就是不确定。 如果把TRUE UNKNOWN FALSE这三者的关系理解为由高级低级，就比较好理解三者的逻辑运算结果了。 AND运算：结果取低级的 TRUE AND UNKOWN 结果UNKOWN UNKOWN AND UNKOWN 结果UNKOWN FALSE AND UNKOWN 结果FALSE OR运算：结果取高级的 TRUE OR UNKOWN 结果TRUE UNKOWN OR UNKOWN 结果UNKOWN FALSE OR UNKOWN 结果UNKOWN NOT运算：结果取相反的 NOT TRUE 结果FALSE NOT UNKOWN 结果UNKOWN NOT FALSE 结果TRUE,3、范围运算符,not between and 条件表达式：表达式 not between 起始值 and 终止值 between and用于判断表达式的值是否在某个范围内，若在指定范围内条件为真，不在指定范围内条件为假。等价于： 表达式=起始值 and 表达式终止值 即：表达式小于起始值或大于终止值条件为真 如：X between 5 and 10 则X=5且X10为假。 X not between 5 and 10 则X10条件为真，X=5且X=10为假。 注意：between所选取的数据范围包括边界值，not between则不包括边界值，not实际上就是逻辑非运算符。,4、多值列表运算符,not in ( ) 条件表达式： not 表达式 in ( 值1, 值2, , 值n ) in ( )用于判断表达式的值是否等于所给出的值之一，只要与其中任何一个值相等条件就为真，全部都不相等为假。 Not in ( )表示判断表达式的值是否全部不等于所给出的值，所有的值一个也不相等条件为真，只要有一个相等为假。 如：X in (1，3，5) 则相当于逻辑表达式：X=1 or X=3 or X=5。 如：not X in (1，3，5) 则相当于逻辑表达式：X1 and X3 and X5。,5、用于子查询的运算符,（1）列表比较运算符 ANY | ALL 列表运算符ANY与包含运算符in功能大致相同，但IN可以独立使用，而ANY必须与比较运算符配合使用；in只是比较相等（包含），ANY可进行任何比较。 列表比较的条件表达式格式： 表达式 比较运算符 ANY (子查询的一列值) 表达式 比较运算符 ALL (子查询的一列值) 该条件将表达式与子查询返回的一整列值按给定的比较运算符逐一比较： 只要有一个比较成立： ANY的结果为真(相当于或运算)。 只有全部比较都成立： ALL的结果为真(相当于与运算)。 在SQL-92标准中还可使用SOME运算符，SOME运算符与ANY 等效。 （2）记录存在逻辑运算符 not exists 逻辑运算符not exists可以检查子查询返回的结果集中是否包含有记录。若子查询结果集中包含记录，则exists为真，否则为假。 ANY | ALL与not exists的详细用法见第5章子查询。,6、空值运算符,not is null 条件表达式：表达式 not is null is null表示判断表达式的值是否等于空值，如果是空值NULL则条件为真，否则为假。 not is null表示判断表达式的值是否不等于空值，如果表达式的值不是空值NULL则条件为真。 例如：假设“职称”的内容为NULL，则： （ 职称=讲师 ）= UNKNOWN 或：“ 职称 IS NULL ”的值为真。,7、字符模糊匹配运算符,not like 条件表达式：字符串表达式 not like 通配符 其中通配符可以使用： （1）%：代表0个或多个字符的任意字符串 如：字符串表达式 like A% 则不论字符串有多少个字符，只要第一个字符（开头）是“A”，条件为真。 如：字符串表达式 like %AB 则不论字符串多少个字符，只要最后两个字符（末尾）是“AB”，条件为真。 如：字符串表达式 like %ABC% 则不论字符串多少字符，只要任意位置上有字符串“ABC”，条件为真。,7、字符模糊匹配运算符,（2）_：代表单个任意字符 如：字符串表达式 like _ _A_ 则必须是只有四个字符的字符串中第3个字符是“A”，条件为真。 如：字符串表达式 like A_BC 则必须是只有四个字符的字符串中第一个是“A”，第三、四个是“BC”，第二个不论是任意的单个字符，条件为真。 如：字符串表达式 like _AB% 则不论字符串有多少个字符，只要第二、三个字符是“AB”，条件为真。 如：字符串表达式 like %ABC_ 则不论字符串有多少个字符，只要最后一个字符前是“ABC”，条件为真。,7、字符模糊匹配运算符,（3）abcd：代表指定字符中的任何一个单字符（取所列字符之一） 如：字符串表达式 like AABC% 则只要第一个是A，第二个是ABC三个字符其中的任意一个字符，条件都为真。 如：字符串表达式 like A_a-h 则必须是只有三个字符的字符串中，只要第一个是A，第三个是a-h即abcdefgh八个字符中的任意一个，条件都为真。 （4）abc：代表不在指定字符中的任何一个单字符 如：字符串表达式 like AABC%，则不论字符串有多少字符，只要第一个是A，第二个不是ABC中的任意一个（ABC以外的任意一个单字符），条件都为真。 如：字符串表达式 like A_a-h，则必须是只有三个字符的字符串中，只要第一个是A，第三个不是abcdefgh八个字符中的任意一个（abcdefgh以外的），条件都为真。,7、字符模糊匹配运算符,（5）转义字符 由于%、_、 都被作为了通配符，如果字符串数据中有这些字符，那么如何匹配这些字符呢？假设有一个表X，其中字段abc中的值为：xyz、%xyz、x_yz、xyzw，我们要查找以%开头的字符应该如何写出表达式呢？like %能不能行？ 在SQL语句中，条件表达式一般用于where子句，我们可以在where子句之后用escape子句指定一个转义字符，把这个字符放在通配符前面，该通配符就可以作为原来的普通字符使用了。 例如：where abc like t% escape t 其中t%表示匹配第一个字符为%，之后为任意多个任意字符%的字符串，这样就可以找到字符串%xyz，转义字符t只表示它后面的第一个%字符已被转义，第二个%仍是匹配符。 注意：所有通配符都必须在LIKE子句中使用才有意义，否则被当作普通字符处理。,4.2.3、字符串连接运算符与字符串表达式,在T-SQL中，字符串连接运算符用“+”，而不能用“|”或“&”。 字符串表达式：字符串表达式1 + 字符串表达式2 字符串表达式的值仍是字符串。 如：ABC+EFG 则字符串表达式连接后的新字符串是：ABCEFG。 如：ABC+，+EFG 则连接后的新字符串是ABC，EFG。,4.3 系统内置函数,SQL的函数分为系统函数（内置函数）和用户自定义函数（见第7章）。 SQL的内置函数中有些是ANSI标准定义的，大多数SQL版本都进行了扩充，不同版本的函数名及功能会略有不同。使用内置函数可以方便快捷地执行某些操作，因此了解并掌握这些函数的用法对数据处理是非常重要的。 T-SQL提供了几百个内置函数，可分为以下几类： l数学函数 l字符串函数 l日期时间函数 l类型转换函数 l集合函数（在第5章 数据库查询SELECT语句中介绍）,4.3.1 数学函数,常用数学函数见表4.2。 说明： l函数参数x可以是数值常量、变量、字段名、数值函数或算术表达式。 lx的数据类型可以是各种数值型或货币型的，有的函数值类型与x类型相同，有的需要将x转换成float，其结果也是float类型的。 l功能说明中得到的值是函数返回值，使用函数后参数x的值不变。,4.3.2 字符串函数,常用字符串函数见表4.3。 l函数参数x一般是整型的数值常量、变量、数值函数或算术表达式。 l参数A是字符串常量、变量、字段名、字符串函数或字符串表达式。 lA的数据类型可以是各种字符型、宽字符型或二进制类型的，大部分只能处理char(n)、varchar(n)、nchar(n)、nvarchar(n)类型或者可以转换成这些类型的数据，只有少部分可以处理binary(n)、varbinary(n)、image、text、ntext类型的数据。 l功能说明中得到的字符串或子字符串是函数返回值，原字符串A的内容不变。 如：len(this is a book)的函数值为14 如：substring(欢迎使用SQL Server 2000,3,4)，从字符串的位置3（第一个字符位置为1）开始取4个字符，函数返回值为子字符串使用SQ,4.3.3 日期时间函数,常用日期时间函数见表4.4。 说明： l 函数参数x一般是整型的数值常量、变量、数值函数或算术表达式。 lD是日期时间型的常量、变量、字段名或日期时间函数。 lD的格式应该符合 SET DATEFORMAT()命令设定的格式。 l功能说明中得到的值是函数返回值，原日期时间D的内容不变。 例如： getdate() 得到当前系统的日期时间为：03 16 2006 4:35PM year(getdate() 得到系统当前日期的年份：2006 year(2006-01-02) 函数返回值为（数值或日期型都可以）：2006 dateadd(dd,20,2006-3-16)表示指定日期加20天：04 5 2006 12:00AM datediff(yy,1985-3-16,getdate()表示当前日期减指定日期的年数差：21 利用datediff()函数，我们可以根据日期求当前的年龄。,4.3.4、类型转换函数,类型转换函数见表4.5。 说明： l函数中的表达式可以是任何有效的SQL Server表达式，所指定的数据类型必须是系统的基本数据类型而不能是用户自定义的类型。 l（长度）用于需要指定长度的数据类型，不需要指定长度的类型可以省略。 lCast()函数只适用于转换后不需要指定格式的数据类型，如整数、普通字符串。 lConvert()函数可适合于任何类型，其中Style可设置转换后的格式： 将datetime或smalldatetime型日期时间转换为字符串的日期格式； 将Real或float(p)型浮点数转换为字符串的小数或指数格式； 将Smallmoney或money货币型转换为字符串的货币格式。 style参数见表4.6。不需要指定格式的类型Style可以省略。,【例4-5】根据出生日期，求出年龄并输出。若将出生年份及年龄作为一个字符串整体输出，需要将年份和年龄转换为字符串再用字符串表达式连接成一个字符串。 Print cast( year(1980-3-7) as char(4) ) + 年出生的人年龄是 + cast( datediff(yy,1980-3-7,getdate() as char(2) ) + 岁 输出结果为：1980年出生的人年龄是25岁,【例4-6】convert函数的用法。 set dateformat mdy -设置日期格式采用月日年 declare d datetime,r real,m money -定义局部变量 set d=11/20/2002 10:10:36 AM set r=268886 set m=9635225.3685 print convert(varchar(30),d,108) - 结果为：10:10:36 print convert(varchar(30),d,111) - 结果为：2002/11/20 print convert(varchar(30),d,120) - 结果：2002-11-20 10:10:36 print convert(varchar(20),r, 0) - 结果为：268886 print convert(varchar(20),r, 1) - 结果为：2.6888600e+005 print convert(varchar(22),r, 2) -结果：2.688860000000000e+005 print convert(varchar(25),m, 0) - 结果为：9635225.37 print convert(varchar(25),m, 1) - 结果为：9,635,225.37 print convert(varchar(25),m, 2) - 结果为：9635225.3685 go,4.4 用企业管理器创建数据表及约束对象,4.4.1、数据表的基本概念 数据表就是相关联的行列数据集合，是数据库中最重要的对象，整个数据库中的全部数据都是物理存储在各个数据表中的。例如电脑器材销售管理diannaoxs数据库的员工表中存放着该公司员工的数据。见表4.7。,1、字段,l数据表中的一列称为一个字段（Field），员工表共有8个字段。 l每个字段的标题名称称为列名或字段名，如“姓名”就是该列的字段名，一个数据表中的字段名必须是惟一的（满足1NF没有相同的列）。 l一个字段中存放着同一类型的数据，不同字段存放的数据类型可以不同。如“员工ID”字段存放各个员工的编号，而“部门”字段存放的是各个员工所在的部门名称。 l一个字段中所存放的数据类型、数值大小及字段长度等称为该字段的属性值。 如“姓名”字段存放的员工姓名是字符类型的数据，假定存储4个汉字，则可设置为char(8)、varchar(8)或Nchar(4)、Nvarchar(4)。而“出生日期”字段存放着员工的出生日期是日期/时间类型的数据，如果设置为Datetime类型将占据8个字节的固定空间，如果设置为Smalldatetime类型则占据4个字节的固定空间。,2记录,l数据表中的一行称为一条记录，由表中各个字段的数据项组成，是一组相关数据的集合。如员工表中的一条记录是一个员工相关数据的集合。 l每个表都有一个主键，主键字段的数据可以惟一的标识表中的一条记录。如各个员工的编号是惟一的，可将“员工ID”字段指定为主键， l设置了主键的数据表中，各条记录是惟一的（满足2NF，没有相同的行）。,4.4.2、数据表的结构,人工绘制数据表的方法步骤： 第一：按表的列数（行数）及每列存放数据的大小绘制一个合理的空表格框架。 第二：在表中填入数据。 对比人工绘制数据表的方法，在数据库中把数据表分成“表结构”和“数据”两部分，所绘制的空表格就是需要设计的“表结构”（框架）。见表4.8。 同样，在数据库中创建数据表也必须先设计出表的“结构”再输入“数据”。那么在数据库中如何设计数据表的结构（框架）呢？ 在数据库中设计表的结构就是告诉数据库系统该表中各列的属性，包括各列的列标题（字段名称）、每列中所要存放数据的类型（字段类型）、存放数据的大小或字符个数（字段长度）以及其他必要的说明（其他属性）。 按照SQL Server数据库创建表的要求我们可以也用一个表格来描述数据表的结构，见表4.9。,4.4.3、用企业管理器创建表结构,在SQL Server 2000中： l 每个数据库里最多有20亿个表。 l 每个表最多可以设置1024个字段（列）。 l每条记录最多占 8060个字节，不包括text、Ntext和image字段。,【实例练习4-1】,在企业管理器中按表4.9的结构创建电脑器材销售管理diannaoxs数据库的员工表。 1、打开企业管理器 展开企业管理器控制台根目录，找到diannaoxs数据库，右击该数据库中的表结点在快捷菜单中单击“新建表”命令，如图4-1所示，随即打开“表设计器”窗口。,【实例练习4-1】,2、在“表设计器”窗口中创建员工表结构 已输入表结构字段属性的员工表“表设计器”如图4-2所示。,【实例练习4-1】,（1）表设计器上半部分的表格，用于描述表的结构，与我们描述表结构的表4.9中前四列完全对应：设计器表格的每一行描述数据表的一个字段，四列参数分别描述该字段的“列名”“数据类型”“长度”“允许空”。 l列名(字段名)：同一表中的字段名必须惟一。可以由字母、汉字、数字和下划线组成，不能以数字开头，不能是关键字，长度不超过128个字符。 l 数据类型：是一个下拉列表框，其中包括了当前数据库的全部类型，也包括用户自定义的数据类型，可直接从中选择即可。 l长度：对需要设置数据类型长度的字段输入长度值，默认长度的类型系统自动取固定长度，不允许用户设置。 l允许空：用对钩表示允许为空值，取消对钩表示不允许为空值。默认状态勾选允许为空，单击鼠标可以切换，使允许为空的字段清除复选对钩标记。,【实例练习4-1】,（2）“表设计器”下半部分是描述字段的附加属性，当鼠标选中哪个字段（标志块中有三角标志），即可为该字段设置附加属性： l描述：输入该字段的说明性文字 l默认值：当输入记录时如果该字段没有输入数据则自动使用该默认值。默认值可以在此直接输入，也可事先单独设置默认约束对象，在这个下拉列表中选取已定义好的默认值约束对象。 l精度、小数位数：对Numeric(p,s)和Decimal(p,s)类型需在此处设置精度p和小数位数s的值。 l 标识(是/否)：选择是否将该字段设置为自动编号字段(identity，输入记录时系统根据“标识种子”和“递增量”自动产生该字段的值)。只有bigint, int, smallint等整数类型的字段，先清除“允许空”的对钩后才可以设置为该项。 l标识种子(初始值)：为数据表第一条记录的自动编号设置初始值，只有“标识”设为“是”才允许设置该项。如班级的“学号”设为自动编号字段标识列，若第一个学生的学号是200603001则可将该值设置为“标识种子”。 l标识递增量：设置自动编号字段每增加一条记录时编号的增长量。默认为1。,【实例练习4-1】,3、保存空数据表 员工表结构输入完毕后必须进行保存，单击文件菜单的“保存”或工具栏第一个“保存”按钮，在弹出的“选择名称对话框”中输入表名“员工表”（默认为TABLE1），单击“确定”关闭表设计器，“员工表”的结构即设计创建完成，在数据库中创建了一个没有数据的空员工表。如图4-3所示。,4.4.4、在企业管理器中修改表结构,若没有关闭“表设计器”可直接在设计器中反复设置修改各个字段，为各个字段设置约束。若已经关闭（创建完成）则可随时再打开要修改表的“表设计器”，对表结构进行修改并设置各种约束，也可以使用SQL语句修改表的结构和创建约束。 1、打开已有表的“表设计器” 打开企业管理器，依次展开到要修改的数据库单击“表”对象展开数据表选择要修改的数据表，右键单击，在弹出的快捷菜单中选择“设计表”命令，即可打开该表的“表设计器”。如图4-4所示。,4.4.4、在企业管理器中修改表结构,2、修改字段属性 在设计器中可以自由的修改各字段的“列名”“数据类型”“字段长度”“允许空”以及其他附加属性。 3、添加新字段 如果在最后追加一个新字段，可将光标移到（或用鼠标单击）最下面的空白行中，即可输入一个新行。 如果要在某个字段前插入一个新字段，可右击插入位置的字段，在弹出的快捷菜单中选择“插入列”，则会在该列之前出现一行空白，即可插入一个字段。如图4-5所示。,4.4.4、在企业管理器中修改表结构,4、删除字段 右击要删除的字段，在弹出的快捷菜单中选择“删除列”命令即可删除该字段。 5、移动字段顺序 单击要移动字段左方（最前端）的标志块，则出现一个“三角”标志，左键按下不松开，然后拖动该字段到所需要的位置再松开即可。 6、修改字段约束 右击要修改约束的字段，在弹出的快捷菜单中选择“属性”，即可在弹出的“属性”对话框中设置或修改该字段的约束，具体方法在下一节介绍。 7、关闭表设计器 修改完毕，单击“保存”工具按钮，保存修改后的表结构并关闭“表设计器”。,4.4.5、在企业管理器中创建表的各种约束对象,我们为表中各个字段设置的每个约束（除了主键约束外），在数据库中都对应一个约束对象，每个约束对象都有自己惟一的名称（可以自己设定，也可以使用系统默认的名称），在查看、修改、删除约束对象时必须指明约束的名称。 字段的约束可以在企业管理器中创建表结构的同时进行设置，也可以在表结构创建完成后单独添加或修改，还可以使用T-SQL语句进行设置或修改。 在企业管理器中设置表的字段约束必须在“表设计器”中进行，可以使用工具栏的主键按钮、关系按钮、索引/键按钮、约束按钮，也可以单击鼠标右键使用快捷菜单中的设置主键、索引/键、关系、CHECK约束命令，最终都要进入“属性”对话框进行设置。如图4-6所示。,4.4.5、在企业管理器中创建表的各种约束对象,1、字段“属性”对话框,单击工具栏“表和索引属性”按钮，或右键快捷菜单选“属性”命令，都会弹出表的“属性对话框”。如图4-7所示。 表的“属性”对话框有“表”“关系”“索引/键”“CHECK约束”四个选项卡。实际上单击工具栏“关系”、“索引/键”、“约束”按钮，或在右键快捷菜单中选择“关系”、“索引/键”、“CHECK约束”命令也都是直接进入“属性”对话框中对应的选项卡。他们的作用是： l 主键按钮：用于设置关键字段的主键约束。 l“表”选项卡：在“选定的表”下拉列表中选择设置约束的数据表。 l关系按钮、关系命令都会打开“关系”选项卡：用于设置外键约束。 l索引/键按钮、索引/键命令都会打开“索引/键”选项卡：用于设置惟一约束。 l约束按钮、约束命令都会打开“CHECK约束”选项卡：用于设置检查约束。,【实例练习4-2】,在企业管理器中为电脑器材销售管理diannaoxs数据库的员工表设置约束。 n “员工ID”设置主键约束、只允许5位数字的检查约束。 n“姓名”设置惟一约束以满足进货表销售表中“收货人”和“销售员”字段的外键约束。 n“性别”设置检查约束只允许输入1和0表示“男”和“女”。 n “部门”可将人数最多的部门设为默认值，如“销售科”。 若输入员工表结构时没有关闭“表设计器”可直接设置各个字段的约束，若已经关闭可重新打开员工表的“表设计器”，修改表结构并设置约束（也可用SQL语句修改表结构和创建约束）。,2、设置主键约束,选中设置主键的“员工ID”字段，直接单击工具栏“主键按钮”，或者在“员工ID”字段上右键单击，在快捷菜单中选“设置主键”命令，此时在“员工ID”字段最前端的标志块中出现一个“钥匙图标”，主键设置完毕。如图4-6所示。,3、设置惟一约束,选中设置约束的“姓名”字段，直接单击工具栏“属性”按钮，或在右键快捷菜单中选“属性”命令，最后在弹出的“属性对话框”中选择“索引/键”选项卡。,3、设置惟一约束,若单击工具栏“索引/键”按钮，或在右键快捷菜单中选择“索引/键”命令，则直接进入“属性”对话框中的“索引/键”选项卡。如图4-8所示。 （1）单击“新建”按钮； （2）在“列名”的下拉列表中选择设置惟一约束的“姓名”字段； （3）选中“创建UNIQUE”复选框和“约束”单选框； （4）在“索引名”中出现该约束的默认名称“IX_员工表”，可在此输入自己的约束名称，以便以后查阅、修改、删除时使用。 最后单击“关闭”，即完成了“姓名”字段的惟一约束设置。,4、设置外键约束,外键约束在“属性”对话框的“关系”选项卡中设置，如图4-9所示。,4、设置外键约束,（1）单击“新建”按钮； （2）在“外键表”的下拉列表中选择创建外键约束的子表，并在下面的列表框中选择要创建外键约束的字段； （3）在“主键表”的下拉列表中选择该外键所引用的父表，并在下面的列表框中选择该外键所要引用的字段。注意：外键所要引用的列必须是父表中已经设置了主键约束或惟一约束的列。 （4）在“关系名”中出现该外键约束的默认名称，可在此输入自己的约束名。 （5）在“关系”选项卡下部的复选框中根据需要可进行相应的附加选择设置： u创建中检查现存数据：表示先对子表已有数据进行检查，若不符合“外键列值必须是引用列的列值之一” ，则不允许定义外键约束；不选择该项则不对已经存在的数据进行检查，只对以后数据有效。 u级联删除相关的记录：表示可以实现级联删除，即主表被引用列的某个值删除时，子表中与该值相关的记录同时被删除。不选择该项则父表中数据删除时，子表记录不受影响。 u级联更新相关的字段：表示可以实现级联修改更新，即主表中被引用的字段值更新时子表中相关记录的字段值同时更新。 （6）单击“关闭”，即可完成外键约束设置。,5、设置CHECK检查约束,为“性别”字段设置CHECK检查约束只允许输入1和0表示“男”“女”。 检查约束就是用指定的条件（逻辑表达式）检查限制输入数据的取值范围，用于保证数据的参照完整性和域完整性性。 选择“性别”字段，打开“属性”对话框并选择“CHECK约束”选项卡。如图4-10所示。,5、设置CHECK检查约束,（1）单击“新建”按钮。 （2）在“约束表达式”中为设置检查约束的列输入约束条件表达式。如“姓别”字段设计为bit位数据类型，用1表示“男”用0表示“女”，则该字段只允许输入0和1（注意：bit字段如果输入0和1以外的值，都默认为1），则相应的条件表达式为： 性别=0 or 性别=1 （3）在“约束名”中出现默认的约束名称，可输入自己的约束名称。 （4）在“CHECK约束”选项卡下部的复选框中进行相应的附加设置。 （5）单击“关闭”，即完成了“性别”字段的检查约束设置。 注意：在创建数据表时直接为某个字段设置的CHECK约束，只对该字段有效，如果一个检查约束需要在不同表不同字段上使用，则应该单独创建“规则”对象，再将该对象绑定到需要约束的各个字段上。 对“性别”的CHECK约束也可以创建为规则对象，再绑定到性别字段上。,6、在企业管理器中创建“规则”对象,“规则”对象也是用于字段的检查约束，限制该字段输入数据的范围。其区别在于CHECK约束只对一个字段有效，而规则对象属于整个数据库，所有数据表中的字段都可以使用。 l规则是数据库对象之一，创建规则对象后还必须绑定到需要的字段上才有效。 l一个规则对象可以绑定到多个表的多个字段，还可以绑定到用户自定义的数据类型上。 l一个字段只能绑定一个规则对象，但可直接设置多个CHECK约束。 注意： uCHECK约束的表达式中可直接使用自己表中的字段，规则对象则不允许使用表中的字段，可用“局部变量”代表自己表中的任意字段。 u CHECK约束或规则对象都不允许使用其他数据表中的字段，如果需要参照其他表中的数据设置复杂约束，则必须使用触发器（见第8章）。例如销售表2006的“销售单价”只能限制在商品一览表中“参考价格”的5%范围内浮动，则不能设置检查约束或规则对象，必须使用触发器。,【实例练习4-3】,为员工表“员工ID”字段创建只允许5位数字的“规则”对象。 展开数据库diannaoxs，右键单击“规则”对象图标，或选中“规则”对象图标单击“操作”菜单选择“新建规则”命令，弹出“规则属性”对话框。如图4-11所示。,【实例练习4-3】,n在“名称”文本框中为规则对象指定一个符合命名规则的惟一名称“职工编号”。 n在“文本”文本框中输入只允许5位数字字符的条件表达式： x like 0-90-90-90-90-9 n 单击“确定”，即完成了规则对象“职工编号”的创建。 注意： u 规则的条件表达式必须是以开头的局部变量，用来表示某个字段中的数据。 u规则表达式 x like 0-90-90-90-90-9 限制输入的数据必须是数字字符，而且必须是5位，否则即违反该检查约束的规则。,【实例练习4-4】,为商品一览表的“平均进价”“参考价格”“库存量”字段、销售表2006的“金额”字段创建输入数值必须大于等于0“不能为负”的规则对象。 右键单击数据库diannaoxs中的“规则”对象图标，在弹出的快捷菜单中选择“新建规则”命令，弹出“规则属性”对话框。如图4-12所示。 n在“名称”文本框中为规则对象指定名称“不能为负”。 n在“文本”文本框中输入规则的条件表达式：x = 0 n单击“确定”，即完成了规则对象“不能为负”的创建。,7、在企业管理器中绑定和解除规则对象,可以将“规则”对象绑定到某个字段上，也可以绑定到自定义数据类型上。 例如将【实例练习4-3】创建的“职工编号”规则绑定到员工表“员工ID”字段： 展开数据库diannaoxs，单击“规则”图标，在右边的“规则”对象列表中右键单击要绑定或解除的“职工编号”规则对象，在快捷菜单中选择“属性”命令，打开“规则属性”对话框。如图4-13所示。,7、在企业管理器中绑定和解除规则对象,l 若要将规则绑定到自定义数据类型上，单击“绑定UDT”按钮，在弹出的下一个对话框中选择“自定义类型”，在“绑定”复选框打对勾选中（若还选择“仅将来”则表示对原来已经使用的类型不做检查，仅对以后使用有效）。若要解除可将“绑定”复选框中的对勾取消。 l若要将规则绑定到字段上，单击“绑定列”按钮，弹出“绑定列”对话框，如图4-14所示。,7、在企业管理器中绑定和解除规则对象,n在“表”的下拉列表中选择“员工表”； n在左