SQL Server 数据库基础及应用-4

4.1数据类型数据类型是数据的一种属性，表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然，不同的程序语言都具有不同的特点，所定义的数据类型的类别和名称都或多或少有些不同。列的数据类型可以是SQLServer2012提供的系统数据类型，也可以是用户自定义的数据类型,SQLServer提供了丰富的系统数据类型，见表4一1.下一页返回4.1数据类型

在讨论数据类型时，使用了精度、长度、小数位数3个概念，精度、小数位数是针对数值型数据的。他们的含义是:1)精度:是数值数据中所存储的十进制数据的总位数。2)长度:是存储数据所使用的字节数。3)小数位数:是数值数据中小数点右边数字的位数。

例如:数值数据12345.678的精度是8，小数位数是3.

下面分别说明表4一1中所列的系统数据类型。上一页下一页返回4.1数据类型1.整型整数包括bigint,int,smallint,tinyint等4类。下面分别列出了这四类整数的精度、长度、取值范围。bigint型数据的存储大小为8个字节，共64位。其中63位用于表示数值的大小，1位用于表示符号。bigint型数据可以存储的数值范围是一263一263一1，即一9223372036854775808-9223372036854775807.上一页下一页返回4.1数据类型int型数据的存储大小为4个字节，共32位。其中31位用于表示数值的大小，1位用于表示符号。int型数据存储的数值范围是一231一231一1，即一2147483648一2147483647.smallint型数据的存储大小为2个字节，共16位。其中巧位表示数值的大小，1位用于表示符号。smallint型数据存储的数值范围是一215

--215一1，即一32768一32767.

tinyint型数据的存储大小只有1个字节，共8位，全部用于表示数值的大小，由于没有符号位，所以tinyint型的数据只能表示正整数。tinyint型数据存储的数值范围是。即0-255.上一页下一页返回4.1数据类型2精确数值型decimal数据类型和numeric数据类型的功能完全一样，它们都可以提供小数所需要的实际存储空间，两者唯一的区别在于，decimal不能用于带identity关键字的列，用户可以用2一17个字节来存储数据，取值范围是一1038+1一1038一1.decimal型数据和numeric型数据的定义格式为decimal和numeric，其中P表示可供存储的值的总位数(不包括小数点),s表示小数点后的位数。例如:decimal(12,5)表示共有12位数，其中整数7位，小数5位。上一页下一页返回4.1数据类型3货币数据类型货币数据类型用于存储货币或现金值，包括money型和smallmoney型。在使用货币数据类型时，应在数据前加上货币符号。money型是一个有4位小数的decimal值，其取值范围是一263(-9223372036854775808)-263一1(+9223372036854775807)，精确到货币单位的1%。存储大小为8个字节。smallmoney型货币数据值介于一234一+234一1，精确到货币单位的千分之十。存储大小为4个字节.上一页下一页返回4.1数据类型4.近似数值类型近似数值类型包括real型、float型。real型数据的存储大小为4个字节，储范围为一3.40E+38~3.40E+38.float型的数据存储大小为8个字节储范围为一1.79E+308一1.79E+3080可精确到小数点后第7位数字。这种数据类型的数据存，可精确到小数点后第巧位数字。这种数据类型的数据存.上一页下一页返回4.1数据类型5.日期和时间数据类型日期和时数据类型包括datetime型和smalldatetime型。datetime型是用于存储日期和时间的结合体的数据类型。它可以存储从1753年1月1日零时起到9999年12月31日这个范围内的日期时间。datetime型长度为8个字节，日期和时间分别用4个字节存储。smalldatetime型与datetime型相似，但其存储的日期时范围较小，从1900年1月1日到2079年12月31日。Smalldatetime型长度为4个字节，日期和时间分别用2个字节存储。上一页下一页返回4.1数据类型6.字符型字符型是使用最多的数据类型，它可以用来存储各种字母、数字符号、特殊符号等。一般情况下，使用字符型数据时，须在数据的前后加上单引号或双引号。字符数据类型包括char型,nchar型。char型是固定长度的字符数据类型，在存储时每个字符和符号占用一个字节的存储空间。其定义形式为char(n)，其中n表示所有字符所占的存储空间，取值为1一8000。若输入的数据字符数小于n定义的范围，则系统自动在其后添加空格来填满设定好的空间;若输入的数据字符数超过n定义的范围，则系统自动截掉超出部分。上一页下一页返回4.1数据类型7.Unicode字符数据型nchar型是固定长度的Unicode字符数据类型，由于Unicode标准规定在存储时每个字符和符号占用2个字节的存储空间，因此nchar型的数据比char型数据多占用一倍的存储空间。其定义形式为nchar}(n)，其中n表示所有字符所占的存储空间，取值为1一4000，即可容纳4000个Unicode字符。nvarchar型是可变长度的Unicode字符数据类型，其定义形式为nvarchar(n)。由于它采用了Unicode标准字符集，因此n的取值范围是1}4000onvarchar型的其他特‘I生与varcha:类型相似。上一页下一页返回4.1数据类型8.文本和图形数据类型文本和图形数据类型是用于存储大量的非Unicode和Unicode文本数据以及二进制数据的可变长度数据类型，包括text型}ntext型和image型。

text型是用于存储大量非Unicod。文本数据的可变长度数据类型，其容量理论上为230-1(2147483647)个字节。在实际应用时需要视硬盘的存储空间而定。ntext型是用于存储大量Unicode文本数据的可变长度数据类型，其理论容量为23。一1(1073741823)个字节。上一页下一页返回4.1数据类型9.二进制数据类型二进制数据类型用于存储二进制数据，包括binary型和、arbinary型。10.位型hlt数据类型只占用1个字节的存储空间，其值为0和1。只要输入的值为非0，系统都会当作1处理。另外，hlt型不能定义为null值。上一页下一页返回4.1数据类型11时间戳型

timestamp数据类型提供数据库范围内的唯一值，它相当于binary(8)或varbinary(8)，但当它所定义的列在进行更新或插入数据行操作时，此列的值会自动更新。每个数据库表中只能有一个timestamp型数据列。12.特殊数据型SQLServe:还提供了几种数据类型:Cursor,sql-variant,table,Uniqueidentifiero上一页下一页返回4.1数据类型创建及删除用户定义数据类型当创建用户定义的数据类型时，必须提供3个属性:数据类型的名称、所基于的系统数据类型和数据类型的可空性。1.使用MicrosoftSQLScrvcrManagcmcntStudio创建用户定义数据类型1)展开要创建用户定义数据类型的数据库，如图4一1所示2)单击“可编程性”、“类型”、“用户定义数据类型”，单击鼠标右键，在弹出的快捷菜单中选择“新建用户定义数据类型”，弹出“新建用户定义数据类型”对话框，如图4-2所示。上一页下一页返回4.1数据类型3)输入新建数据类型的名称，并在“数据类型”列表中选择系统数据类型。4)在“长度”栏中键入此数据类型可存储的最大数据长度。5)选择是否允许该数据类型接受空值。若允许，则勾选“允许NULL值”复选框。6)在“默认值”和“规则”中可以选择一个默认值或规则，并将其绑定在用户定义数据类型上。上一页下一页返回4.1数据类型2.使用transact-SQL命令创建用户定义数据类型创建用户定义的数据类型可以使用transact-sql语句。系统存储过程sp_addtype可以用来创建用户定义的数据类型。其语法形式如下:spaddtype【@typename」type,【@phystype一]systemdatatype【;【@nulltype]’nulltype’][;【@owner]’ownername’]上一页下一页返回4.1数据类型例4一1.在查询窗口创建两个用户定义的数据类型telephone和faxo执行创建过程，并返回结果，如图4一3所示。3.使用MicrosoftSQLScrvcrManagcmcntStudio删除用户定义数据类型1)在“对象资源管理器”中依次单击“可编程性”、“类型”、“用户定义数据类型”，如图4-4所示。2)右击选中要删除的用户定义数据类型，在弹出的快捷菜单中，选择“删除”命令。弹出“删除对象”对话框，选中要删除的数据类型，单击“确定”按钮，如图4-5所示。上一页下一页返回4.1数据类型4使用transact-SQL命令删除用户定义数据类型删除用户定义的数据类型可以使用s}droptype语句，其语法形式如下spdroptype【@typename=]’type’【例4-2」删除用户定义的数据类型telephone和fax.在查询窗口执行删除过程，并返回结果，如图4-6所示。上一页返回4.2表的创建教材案例数据库中所用表的结构及数据见表4-2~表4-9表是数据库中存放用户数据的对象。它包含一组固定的列，表中的列描述该表所跟踪的实体的属性，每个列都有一个名字和若干个属性。在使用工具或命令方式创建表之前，先要设计表，即确定表的名字、所包含各列的列名、列的数据类型、长度和是否为空等。空值(NULL):空值表示未知、不可用或在以后添加的数据。若一列允许为空值，则向表中输入记录值时可不为该列给出具体值;若一列不允许为空值，则在输入时必须给出具体值。1.使用MicrosoftSQLServerManagementStudio创建表[例4一3]建表，student表结构见表4一l0.下一页返回4.2表的创建

下面以在xscj数据库中创建student表为例，说明通过SQLServer的界面方式创建表的操作过程。

以下是创建student表的操作步骤:1)启动MicrosoftSQLServerManagementStudio，展开@建立的xscj数据库。2)右击“表”，单击“新建表”，如图4一7所示。3)在左窗格的编辑窗口中(图4-8)分别输入各列的名称、数据类型、长度、是否为空值等属‘胜。4)输入完各列属性后，单击工具栏上的“保存”按钮，在弹出的“选择名称”对话框中输入表的名称“student"(图4一9)，然后单击“确定”按钮保存该表。上一页返回4.3表的修改

2.使用transact-SQL命令【例4一4】用transact-SQL语句}i}建student表，表结构见表4一l0。在查询窗口执行创建表过程，并返回结果，如图4一10所示。

数据表创建之后，有时需要对表的结构、约束等属性进行修改。表的修改可以通过界面方式和t-SQL语句两种方法进行。对一个@经存在的表可以进行如下修改:1)修改表名。2)增加、删除列。3)修改@有列的属性。例如列的名称、长度、数据类型、是否允许空值等。下一页返回4.3表的修改1.使用MicrosoftSQLServerManagementStudio修改表SQLServer中允许修改一个表的名字，但当表名改变后，与此相关的某些对象如视图，以及通过表名与表相关的存储过程将无效。因此，一般不要改变一个@有的表名，特别是在其上@定义了视图或建立了相关链接的表的时候。[例4-5]将、scj数据库中的student表改名为ts.操作步骤如下:1)展开xscj数据库，单击“表”，在student表上单击右键，在弹出的快捷菜单中选择“重命名”，如图4一11所示.2)在表名位置输入新表名“ts"，按下回车键确定。上一页下一页返回4.3表的修改2.增加列当原来所创建的表需要增加项目时，就要向表中增加列。【例4-6J在student表中增加“S_teXt”列(备注)，数据类型为teXt型，允许为空值。操作步骤如下:1)在“对象资源管理器”中展开需要进行操作的表，在student表上单击鼠标右键，在弹出的快捷菜单中选择“设计”，如图4一12所示。2)在设计表窗口单击空白行，输入列名“S_text，选择数据类型}text并选择“允许Null值”如图4一13所示。3)用此方法还可向表中添加多个列，当需要添加的列输入完毕后，单击工具栏上的“保存”按钮，保存修改后的表。上一页下一页返回4.3表的修改3删除列在SQLServer中，被删除的列是不可恢复的，所以执行删除列命令时必须谨慎操作。除此以外，删除列前，必须保证基于该列的所有索引和约束都@被删除。【例4-7】删除student表中的“S一teXt”列。操作步骤如下:1)展开xscj数据库中的表，右击student表，在弹出的快捷菜单中选择“设计”命令，如图4一12所示。2)在设计表窗口，右击“S_text”列，选择“删除列”命令(图4一14)，该列即被删除.3)单击工具栏上的“保存”按钮，保存修改后的表。上一页下一页返回4.3表的修改4.修改@有列的属性在表中还没有数据记录时，可以修改表的结构，如更改列的名称、长度、数据类型、是否允许空值等属性。但当表中@有数据记录时，建议不要轻易修改表的结构，以免产生错误。下列类型的列不能被修改:1)具有text~ntext~image~timestamp数据类型的列。2)计算列。3)全局标识符列。4)复制列。上一页下一页返回4.3表的修改5)用于索引的列(若用于索引的列为varchar,nvarchar或varbinary数据类型时，可以增加列的长度)。6)用于主键约束、外键约束、CHECK约束或UNIQUE约束的列。7)绑定默认对象的列。[例4-8]在student表中将“s_birth”列的列名改为“s_birthday"，数据类型由、malldatetime改为datetime操作步骤如下上一页下一页返回4.3表的修改1)展开xscj数据库中的表，右击student表，在弹出的快捷菜单中选择“设计”命令，如图4一12所示。2)在设计表窗口，单击需要修改的列("s_birth”列)，修改相应属性，如图4一15所示。3)当修改完相应列的属性后，单击工具栏上的“保存”按钮，保存修改后的表。上一页下一页返回4.3表的修改5.更改当前数据库中用户创建对象(如表、列)的名称[例4-9]将student表重命名为ts.在查询窗口执行修改表过程，并返回结果，如图4一16所示。【例4-10}将is表中“sbirth”列的列名改为“s_birthday".在查询窗口执行修改表过程，并返回结果，如图4一17所示。上一页下一页返回4.3表的修改6.使用transact-SQL命令修改表【例4-11】在xscj数据库中对student表进行如下修改:1)在is表中，增加“Stext”列，数据类型为text型，允许为空值。在查询窗口执行增加列过程，并返回结果，如图4一18所示。2)在t、表中，删除“Stext”列。在查询窗口执行删除列过程，并返回结果，如图4一19所示3)在is表中将“sbirthday”列的数据类型由smalldatetime改为datetime在查询窗口执行修改表过程，并返回结果，如图4-20所示。上一页返回4.4表的删除当数据库中某些表不再需要时，可以将表删除。删除表时，表的定义、表中所有的数据以及表的索引、触发器、约束等均被永久删除。1.使用MicrosoftSQLScrvcrManagcmcntStudio删除表【例4-12」删除xscj数据库中的is表。操作步骤如下:1)展开xscj数据库，在is表上单击鼠标右键，在弹出的快捷菜单中选择“删除”，如图4-21所示。2)弹出“删除对象”窗口(图4-22)，单击“确定”按钮即可。下一页返回4.4表的删除公使用transact-SQL命令

transact-SQL中对表进行修改的语句是DROPtABLE，基本语法格式如下DROPtABLEtable参数解释如下:nametah1Pname是要删除的表名。【例4-13}删除xscj数据库中的is表。在查询窗口执行删除表过程，并返回结果，如图4-23所示。上一页返回4.5表的数据操作创建数据库和表后，就可以对表中的数据进行操作，包括数据的插入、修改和删除。本节将讨论如何通过界面方式和transact-SQL语句两种方法操作表数据。1.使用MicrosoftSQLServerManagementStudio插人表数据SQLServer可以通过界面方式，直接在表中输入数据。【例4一14]在Student表中输入表4一11所列数据。下一页返回4.5表的数据操作

操作步骤如下:1)依次展开xscj数据库，展开表，在student表上单击鼠标右键，在弹出的快捷菜单中选择“编辑前200行”，如图4-24所示。2)进入表数据窗口，依次输入数据，每输完一行，光标会自动跳到下一行，如图4-25所示。上一页下一页返回4.5表的数据操作2使用transact-SQL命令【例4一15】在student表中插入如下记录:会计系会计电算化11-1班10601113013张田田男1993-10-16在查询窗口执行向表中插入数据过程，并返回结果，如图4-26所示.查看数据插入情况，如图4-27所示。【例4一16】在student表中插入如下记录:会计系10601113014王朔1994一01一06在查询窗口执行向表中插入数据过程，并返回结果，如图4-28所示。查看数据插入情况，如图4-29所示。上一页下一页返回4.5表的数据操作4.5.2修改表中数据1.使用MicrosoftSQLServerManagementStudio修改表中数据在表数据窗口中，先定位要修改的数据记录，然后对该数据进行编辑修改。【例4一17】将student表中第三条数据的“S-SPX”字段的值改为“女”，如图4一30所示【例4一18]在student表中，将“计算机科学与技术系”改为“计科系”，将“卢望”的“s_birth"改为“1993一08一26”。在查询窗口执行修改数据程序，并返回结果，如图4-31所示。查看数据修改情况，如图4一32所示。上一页下一页返回4.5表的数据操作4.5.3删除表中数据当某些表中的数据不再需要时，可以将他们删除。1.使用MicrosoftSQLServerManagementStudio【例4一19】删除student表中第二条记录。1)在表数据窗口中，先选中要删除的整行数据记录，选择“删除”命令，如图4一33所示。删除表中数据然后单击鼠标右键，在弹出的快捷菜单中2)选择“删除”后，将弹出图4-34所示的警告对话框，若单击“是”按钮则删除该行记录，若单击“否”则取消删除操作。上一页下一页返回4.5表的数据操作2.使用transact-SQL命令【例4-20】删除student表中“s_name”为“龙轩婷”的记录。在查询窗口执行删除数据程序，并返回结果，如图4-35所示。3.使用tRUNCAtEtABLE语句删除表中数据tRUNCAtEtABLE语句删除数据表内容的语法是tRUNCAtEtABLEtablename参数解释如下:table_name:是要删除其全部行的表的名称。上一页下一页返回4.5表的数据操作

下面是对truncate语句在SQLServer中的用法和原理的说明:tRUNCAtEtABLE在功能上与不带WHERE子句的DELEtE语句相同，二者均删除表中的二部行。但tRUNCAtEtABLE比DELEtE速度快，且使用的系统和事务日志资源少。DELEtE语句每次删除一行，并在事务日志中为所删除的每行记录一项。t