页数：82页文件大小：2.67 MB来源： 空间数据库与SQLppt课件
本课件系统列出SQL Server常用系统数据类型，结合学生成绩管理库的XSB、KCB、CJB三张表讲解表结构设计，再分别用界面方式和命令方式演示表与表数据的创建、修改和删除。
3.1表结构和数据类型3.2界面方式操作表3.3命令方式操作表3.4界面方式操作表数据3.5命令方式操作表数据
3.1.1表和表结构每个数据库包含了若干个表。表是SQL Server中最主要的数据库对象，它是用来存储数据的一种逻辑结构。表由行和列组成，因此也称为二维表。表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表3.1就是用来表示学生情况的一个学生表。
表3.1学生表：下表列出相关数据。
| 学号 | 姓名 | 性别 | 出生时间 | 专业 | 总学分 | 备注 |
|---|---|---|---|---|---|---|
| 081101 | 王林 | 男 | 1990-02-10 | 计算机 | 50 | — |
| 081103 | 王燕 | 女 | 1989-10-06 | 计算机 | 50 | — |
| 081108 | 林一帆 | 男 | 1989-08-05 | 计算机 | 52 | 已提前修完一门课 |
| 081202 | 王林 | 男 | 1989-01-29 | 通信工程 | 40 | 有一门课不及格，待补考 |
| 081204 | 马琳琳 | 女 | 1989-02-10 | 通信工程 | 42 | — |
下面简单介绍与表有关的几个概念：
（5）关键字。若表中记录的某一字段或字段组合能唯一标识记录，则称该字段或字段组合为候选关键字（Candidate key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary key），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字。
这里的主关键字与第1章中的主码所起的作用是相同的，都用来唯一标识记录行。例如，在学生表中，2个及其以上记录的姓名、性别、出生时间、专业、总学分和备注这6个字段的值有可能相同，但是“学号”字段的值对表中所有记录来说一定不同，即通过“学号”字段可以将表中的不同记录区分开来。所以，“学号”字段是唯一的候选关键字，学号就是主关键字。
再例如，学生成绩表记录的候选关键字是（学号，课程号）字段组合，它也是唯一的候选关键字。
列的数据类型可以是SQL Server提供的系统数据类型，也可以是用户定义的数据类型。SQL Server 2008提供了丰富的系统数据类型，现将其列于表3.2中。
表3.2系统数据类型表：下表列出相关数据。
| 数据类型 | 符号标识 |
|---|---|
| 整数型 | bigint, int, smallint, tinyint |
| 精确数值型 | decimal, numeric |
| 浮点型 | float, real |
| 货币型 | money, smallmoney |
| 位型 | bit |
| 字符型 | char, varchar、varchar(MAX) |
| Unicode字符型 | nchar, nvarchar、nvarchar(MAX) |
| 文本型 | text, ntext |
| 二进制型 | binary, varbinary、varbinary(MAX) |
| 日期时间类型 | datetime, smalldatetime, date, time, datetime2, datetimeoffset |
| 时间戳型 | timestamp |
| 图像型 | image |
| 其他 | cursor, sql_variant, table, uniqueidentifier, xml, hierarchyid |
在讨论数据类型时，使用了精度、小数位数和长度3个概念，前两个概念是针对数值型数据的，它们的含义如下。精度：指数值数据中所存储的十进制数据的总位数。小数位数：指数值数据中小数点右边可以有的数字位数的最大值。例如，数值数据3890.587的精度是7，小数位数是3。长度：指存储数据所使用的字节数。
下面分别说明常用的系统数据类型。1．整数型整数型包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。bigint：大整数，数范围为263～2631，其精度为19，小数位数为0，长度为8字节。int：整数，数范围为231～2311，其精度为10，小数位数为0，长度为4字节。
smallint：短整数，数范围为215～2151，其精度为5，小数位数为0，长度为2字节。tinyint：微短整数，数范围为0～255，长度为1字节，其精度为3，小数位数为0，长度为1字节。
2．精确数值型精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。精确数值型包括decimal和numeric两类。在SQL Server 2008中，这两种数据类型在功能上完全等价。
声明精确数值型数据的格式是numeric | decimal(p[,s])，其中，p为精度，s为小数位数，s的默认值为0。例如，指定某列为精确数值型，精度为6，小数位数为3，即decimal(6,3)，那么当向某记录的该列赋值56.342 689时，该列实际存储的是56.3427。
decimal和numeric可存储–1038+1～1038–1的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。例如，若有声明numeric(8,3)，则存储该类型数据需5字节；而若有声明numeric(22,5)，则存储该类型数据需13字节。
3．浮点型浮点型也称近似数值型。顾名思义，这种类型不能提供精确表示数据的精度，使用这种类型来存储某些数值时，可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不太高的数值量，如一些统计量。
有两种近似数值数据类型：float[(n)]和real，两者通常都使用科学计数法表示数据，即形为尾数E阶数，如5.6432E20，–2.98E10，1.287659E-9等。real：使用4字节存储数据，表数范围为–3.40E + 38～3.40E + 38，数据精度为7位有效数字。
float：float型数据的数范围为–1.79E+308～1.79E+308。定义中的n取值范围是1～53，用于指示其精度和存储大小。当n在1～24之间时，实际上将定义一个real型数据，存储长度为4字节，精度为7位有效数字。当n在25～53之间时，存储长度为8字节，精度为15位有效数字。当省略n时，代表n在25～53之间。
4．货币型SQL Server 2008提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。money：数据的数范围为263～2631，其精度为19，小数位数为4，长度为8字节。
money的数范围与bigint相同，不同的只是money型有4位小数，实际上，money就是按照整数进行运算的，只是将小数点固定在末4位。smallmoney：数范围为–231～2311，其精度为10，小数位数为4，长度为4字节。可见smallmoney与int的关系就如同money与bigint的关系一样。
当向表中插入money或smallmoney类型的值时，必须在数据前面加上货币表示符号（$），并且数据中间不能有逗号（,）；若货币值为负数，则需要在符号$的后面加上负号（-）。例如，$15 000.32，$680，$-20000.9088都是正确的货币数据表示形式。
5．位型SQL Server 2008中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为1字节。但要注意，SQL Server对表中bit类型列的存储进行了优化：如果一个表中有不多于8个的bit列，则这些列将作为1字节存储；如果表中有9～16个bit列，则这些列将作为2字节存储，更多列的情况以此类推。
当为bit类型数据赋0时，其值为0，而赋非0（如100）时，其值为1。字符串值TRUE和FALSE可以转换为以下bit值：TRUE转换为1，FALSE转换为0。
6．字符型字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、@、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如'abc'、"Abc<Cde"。SQL Server 2008字符型包括两类：固定长度（char）或可变长度（varchar）字符数据类型。
char[(n)]：定长字符数据类型，其中，n定义字符型数据的长度，n在1～8000之间，默认为1。当表中的列定义为char(n)类型时，若实际存储的串长度不足n，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。
例如，某列的数据类型为char(20)，而输入的字符串为"ahjm1922"，则存储的是字符ahjm1922和12个空格。若输入的字符个数超出了n，则超出的部分被截断。varchar[(n)]：变长字符数据类型，其中，n的规定与定长字符型char中的n完全相同，但这里n表示的是字符串可达到的最大长度。
7．Unicode字符型Unicode是统一字符编码标准，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的Unicode字符型可以存储Unicode标准字符集定义的各种字符。Unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。
nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度Unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。nchar[(n)]：nchar[(n)]为包含n个字符的固定长度Unicode字符型数据，n的值在1～4 000之间，长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。
nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1～4 000之间，默认为1。长度是所输入字符个数的两倍。
8．文本型当需要存储大量的字符数据，如较长的备注、日志信息等时，字符型数据最长8000个字符的限制可能使它们不能满足这种应用需求，此时可使用文本型数据。文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。text类型可以表示最大长度为2311个字符，其数据的存储长度为实际字符数字节。
ntext类型可表示最大长度为2301个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。
9．二进制型二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。binary [(n) ]：固定长度的n字节二进制数据。n的取值范围为1～8 000，默认为1。binary(n)数据的存储长度为n+4字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。
varbinary [(n)]：n字节变长二进制数据。n取值范围为1～8 000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度+4字节。
10．日期时间类型日期时间类型数据用于存储日期和时间信息，在SQL Server 2008以前的版本中，日期时间数据类型只有datetime和smalldatetime两种。而在SQLServer 2008中新增了4种新的日期时间数据类型，分别为date、time、datetime2和datetimeoffset。
（1）datetime：datetime类型可表示的日期范围为从1753年1月1日到9999年12月31日，精确度为0.03 s（3.33 ms或0.003 33 s），例如，1～3ms的值都表示为0 ms，4～6 ms的值都表示为4 ms。datetime类型数据长度为8字节，日期和时间分别使用4字节存储。
前4字节用于存储datetime类型数据中距1900年1月1日的天数。为正数表示日期在1900年1月1日之后，为负数则表示日期在1900年1月1日之前。后4字节用于存储datetime类型数据中距12:00（24小时制）的毫秒数。
用户给出datetime类型数据值时，日期部分和时间部分分别给出。
日期部分的表示形式常用的格式如下：2001Jan20、2001Janary20年月日200120Jan年日月Jan202001、Jan20,2001、Jan20,01月日[,]年Jan200120月年日20Jan2001、20Jan,2001日月[,]年202001Jan日年月2001表示2001年1月1日年（4位数）20010120、010120年月日01/20/01、1/20/01、01/20/2001、1/20/2001月/日/年01-20-01、1-20-01、01-20-2001、1-20-2001月-日-年01.20.01、1.20.01、01.20.2001、1.20.2001月.日.年
时间部分常用的表示格式如下：10:20、08:05时:分20:15:18、20:15:18.2时:分:秒20:15:18:200时:分:秒:毫秒10:10AM、10:时:分AM|PM
（2）smalldatetime：smalldatetime类型数据可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。即29.998 s或更低的值向下舍入为最接近的分钟，29.999 s或更高的值向上舍入为最接近的分钟。
smalldatetime类型数据的存储长度为4字节，前2字节用来存储smalldatetime类型数据中日期部分距1900年1月1日之后的天数。后2字节用来存储smalldatetime类型数据中时间部分距中午12点的分钟数。
用户输入smalldatetime类型数据的格式与datetime类型数据完全相同，只是它们的内部存储可能不相同。（3）date：date类型数据可以表示从公元元年1月1日到9999年12月31日的日期，date类型只存储日期数据，不存储时间数据，存储长度为3字节，表示形式与datetime数据类型的日期部分相同。
（4）time：time数据类型只存储时间数据，表示格式为“hh:mm:ss[.nnnnnnn]”。hh表示小时，范围为0～23。mm表示分钟，范围为0～59。ss表示秒数，范围为0～59。n是0～7位数字，范围为0～999 9999，表示秒的小数部分，即微秒数。
所以time数据类型的取值范围为00:00:00.000 000 0～23:59:59.999 999 9。time类型的存储大小为5字节。另外还可以自定义time类型微秒数的位数，例如，time(1)表示小数位数为1，默认为7。（5）datetime2：新的datetime2数据类型和datetime类型一样，也用于存储日期和时间信息。
但是datetime2类型取值范围更广，日期部分取值范围从公元元年1月1日到9999年12月31日，时间部分的取值范围为00:00:00.0000000～23:59:59.999999。另外，用户还可以自定义datetime2数据类型中微秒数的位数，例如，datetime(2)表示小数位数为2。
datetime2类型的存储大小随着微秒数的位数（精度）而改变，精度小于3时为6字节，精度为4和5时为7字节，所有其他精度则需要8字节。
（6）datetimeoffset：datetimeoffset数据类型也用于存储日期和时间信息，取值范围与datetime2类型相同。但datetimeoffset类型具有时区偏移量，此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。
datetimeoffset的格式为“YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|}hh:mm]”，其中，hh为时区偏移量中的小时数，范围为00～14，mm为时区偏移量中的额外分钟数，范围为00～59。时区偏移量中必须包含“+”（加）或“–”（减）号。
这两个符号表示是在UTC时间的基础上加上还是从中减去时区偏移量以得出本地时间。时区偏移量的有效范围为14:00～+14:00。
11．时间戳型标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。记录timestamp列的值实际上反映了系统对该记录修改的相对（相对于其他记录）顺序。一个表只能有一个timestamp列。
timestamp类型数据的值实际上是二进制格式数据，其长度为8字节。12．图像数据类型标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与2311 (2 147 483 647)字节之间。在SQL Server 2008中该类型是为了向下兼容而保留的数据类型。
微软推荐用户使用varbinary(MAX)数据类型来替代image类型。
13．其他数据类型除了上面所介绍的常用数据类型外，SQL Server 2008还提供了其他几种数据类型：cursor、sql_variant、table、uniqueidentifier、xml和hierarchyid。cursor：是游标数据类型，用于创建游标变量或定义存储过程的输出参数。
sql_variant：是一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8016字节。table：是用于存储结果集的数据类型，结果集可以供后续处理。uniqueidentifier：是唯一标识符类型。
系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。xml：是用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2 GB。hierarchyid：hierarchyid数据类型是SQL Server 2008新增加的一种长度可变的系统数据类型。可使用hierarchyid表示层次结构中的位置。
varchar、nvarchar、varbinary这三种数据类型可以使用MAX关键字，如varchar(MAX)、nvarchar(MAX)、varbinary(MAX)，加了MAX关键字的这几种数据类型最多可存放231-1字节的数据，分别用来替换text、ntext和image数据类型。
其中，“学号”列的数据是学生的学号，学号值有一定的意义，例如，“081101”中“08”表示学生的年级，“11”表示所属班级，“01”表示学生在班级中的序号，所以“学号”列的数据类型可以是6位的定长字符型数据；“姓名”列记录学生的姓名，姓名一般不超过4个中文字符，所以可以是8位定长字符型数据；
“性别”列只有“男”、“女”两种值，所以可以使用bit型数据，值1表示“男”，值0表示“女”，默认是1；“出生时间”是日期时间类型数据，列类型定为date；“专业”列为12位定长字符型数据；“总学分”列是整数型数据，值在0～160之间，列类型定为int，默认是0；
“备注”列需要存放学生的备注信息，备注信息的内容在0～500个字之间，所以应该使用varchar类型。在XSB表中，只有“学号”列能唯一标识一个学生，所以将“学号”列设为该表主键。最后设计的XSB的表结构如表3.3所示。
表3.3 XSB的表结构：下表列出相关数据。
| 列名 | 数据类型 | 长度 | 是否可空 | 默认值 | 说明 |
|---|---|---|---|---|---|
| 学号 | 定长字符型（char） | 6 | × | 无 | 主键，前2位表示年级，中间2位为班级号，后2位为序号 |
| 姓名 | 定长字符型（char） | 8 | × | 无 | — |
| 性别 | 位型（bit） | 默认值 | √ | 1 | 1:男 0:女 |
| 出生时间 | 日期型（date） | 默认值 | √ | 无 | — |
| 专业 | 定长字符型（char） | 12 | √ | 无 | — |
| 总学分 | 整数型（int） | 默认值 | √ | 0 | 0≤总学分<160 |
| 备注 | 不定长字符型（varchar） | 500 | √ | 无 | — |
参照XSB表结构的设计方法，同样可以设计出其他两个表的结构，表3.4所示是KCB的表结构，表3.5所示是CJB的表结构。
表3.4 KCB的表结构：下表列出相关数据。
| 列名 | 数据类型 | 长度 | 是否可空 | 默认值 | 说明 |
|---|---|---|---|---|---|
| 课程号 | 定长字符型（char） | 3 | × | 无 | — |
| 课程名 | 定长字符型（char） | 16 | × | 无 | — |
| 开课学期 | 整数型（tinyint） | 1 | √ | 1 | 只能为1～8 |
| 学时 | 整数型（tinyint） | 1 | √ | 0 | — |
| 学分 | 整数型（tinyint） | 1 | × | 0 | — |
表3.5 CJB的表结构：下表列出相关数据。
| 列名 | 数据类型 | 长度 | 是否可空 | 默认值 | 说明 |
|---|---|---|---|---|---|
| 学号 | 定长字符型（char） | 6 | × | 无 | 主键 |
| 课程号 | 定长字符型（char） | 3 | × | 无 | 主键 |
| 成绩 | 整数型（int） | 默认值 | √ | 0 | — |
说明：源文本仅在课程号行标注主键；按该表设计，学号与课程号共同构成联合主键，此处两行均作标注。
3.2.1创建表以下是通过对象资源管理器创建表XSB的操作步骤。第1步：启动SQL Server Management Studio，在对象资源管理器中，展开“数据库”，右击“PXSCJ”数据库菜单下的“表”选项，在弹出的快捷菜单中，选择“新建表”菜单项，打开如图3.1所示的“表设计器”窗口。图3.1“表设计器”窗口
第2步：在“表设计器”窗口中，根据已经设计好的XSB的表结构，分别输入或选择各列的名称、数据类型、是否允许为空等属性。根据需要，可以在列属性选项卡中填入相应内容。第3步：在“学号”列上右击鼠标，选择“设置主键”菜单项，选择“设置主键”选项，如图3.2所示。图3.2设置XSB表的主键
在“列属性”选项卡中的“默认值和绑定”和“说明”项中分别填写各列的默认值和说明。学生情况表结构设计完成后的结果如图3.3所示。图3.3表属性编辑完成结果
第4步：在表的各列的属性均编辑完成后，单击工具栏中的按钮（“保存”按钮），出现“选择表名”对话框。在“选择表名”对话框中输入表名“XSB”，单击“确定”按钮即可创建XSB表。在对象资源管理器中可以找到新创建的XSB表，如图3.4所示。图3.4新创建的XSB表
第5步：使用同样的方法创建课程表，名称为KCB；创建成绩表，名称为CJB。KCB表创建后的界面如图3.5所示。图3.5创建表KCB
CJB表创建后的界面如图3.6所示。图3.6创建表CJB
在SQL Server 2008中，当用户使用界面方式修改表的结构（如添加列、修改列的数据类型等）时，必须删除原来的表，再重新创建新表才能完成表的更改。如果强行更改会弹出如图3.7所示的对话框。图3.7“不允许保存更改”对话框
如果要在修改表时不出现此对话框，可以进行以下操作：启动SQL Server Management Studio，在面板中单击“工具”主菜单，选择“选项”子菜单，在出现的“选项”对话框中选择“Designers”下的“表设计器和数据库设计器”选项卡，将“阻止保存要求重新创建表的更改”复选框前的勾去掉，如图3.8所示，完成操作后单击“确定”按钮，接下来就可以对表进行更改了。
图3.8解除阻止保存的选项
1．更改表名SQL Server 2008中允许改变一个表的名字，但当表名改变后，与此相关的某些对象（如视图），以及通过表名与表相关的存储过程将无效。因此，建议一般不要更改一个已有的表名，特别是当在其上定义了视图或建立了相关的表时。图3.9修改表名
【例3.1】将
将XSB表的表名改为student。在对象资源管理器中选择需要更名的表XSB，右击鼠标，在弹出的快捷菜单上选择“重命名”菜单项，如图3.9所示，输入新的表名student，按下回车键即可更改表名。2．增加列当原来所创建的表中需要增加项目时，就要向表中增加列。例如，若在表XSB中需要登记其籍贯、获奖情况等，就要用到增加列的操作。同样，已经存在的列可能需要修改或删除。
【例3.2】向表
向表XSB中添加一个“奖学金等级”列，“奖学金等级”列的数据类型为tinyint，允许为空值。第1步：启动SQL Server Management Studio，在对象资源管理器中展开“数据库”，选择其中的“PXSCJ”数据库，在“PXSCJ”数据库中选择表“dbo.XSB”，右击鼠标，在弹出的快捷菜单上选择“设计”菜单项，打开“表设计器”窗口。第2步：在“表设计器”窗口中选择第一个空白行，输入列名“奖学金等级”，选择数据类型“tinyint”，如图3.10所示。如果要在某列之前加入新列，则可以右击该列，选择“插入列”，在空白行中填写列信息即可。图3.10增加新列
第3步：当需向表中添加的列均输入完毕后，关闭该窗口，此时将弹出一个“保存更改”对话框，单击“是”按钮，保存修改后的表（或单击面板中的按钮）。3．删除列在“表dbo.XSB设计器”窗口中选择需删除的列（如在XSB表中删除“奖学金等级”列），右击鼠标，在弹出的快捷菜单上选择“删除列”菜单项，该列即被删除。
4．修改列表中尚未有记录值时，可以修改表结构，如更改列名、列的数据类型、长度和是否允许空值等属性；但当表中有了记录后，建议不要轻易改变表结构，特别不要改变数据类型，以免产生错误。（1）具有以下特性的列不能修改：数据类型为timestamp的列；计算列；全局标识符列；
用于索引的列（但当用于索引的列为varchar、nvarchar或varbinary数据类型时，可以增加列的长度）；用于由CREATE STATISTICS生成统计的列，如需修改这样的列，则必须先用DROP STATISTICS语句删除统计；用于主键或外键约束的列；用于CHECK或UNIQUE约束的列；关联有默认值的列。
（2）当改变列的数据类型时，要求满足下列条件：原数据类型必须能够转换为新数据类型。新数据类型不能为timestamp类型。如果被修改列属性中有“标识规范”属性，则新数据类型必须是有效的“标识规范”数据类型。
【例3.3】在
在XSB表中，将“姓名”列名改为“name”，数据长度由8改为10，允许为空值。将“出生时间”列名改为“birthday”，数据类型由“date”改为“datetime”。因尚未输入记录值，所以可以改变XSB表的结构，右击需要修改的XSB表，选择“设计”菜单项进入表XSB的设计窗口，选择需要修改的列，修改相应的属性。修改完后保存。删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除。注意，不能删除系统表和外键约束所参照的表。
【例3.4】使用界面方式
使用界面方式删除表XSB。启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库→PXSCJ→表”，选择要删除的表XSB，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项。系统弹出“删除对象”窗口。单击“确定”按钮，即可删除XSB表。3.3.1创建表创建表使用CREATE TABLE语句。
语法格式：CREATE TABLE[ database_name . [ schema_name ] . | schema_name . ] table_name({<column_definition>| column_name AS computed_column_expression [PERSISTED [NOT NULL]]}[ <table_constraint> ] [ ,...n ])[ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ][ { TEXTIMAGE_ON { filegroup | "default" } ][ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ][ WITH ( <table_option> [ ,...n ] ) ][ ; ]
列的定义格式如下：<column_definition> ::=column_name data_type[ FILESTREAM ][ COLLATE collation_name ][ NULL | NOT NULL ][[ CONSTRAINT constraint_name ]DEFAULT constant_expression ]| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ][ ROWGUIDCOL ][ <column_constraint> [ ...n ] ][ SPARSE ]
在开始使用FILESTREAM之前，必须在SQL Server 2008数据库引擎实例中启用FILESTREAM，操作步骤如下。
第1步：在SQL Server配置管理器中打开SQL Server 2008数据库实例的“属性”窗口，选择“FILESTREAM”选项卡，选中“针对Transact-SQL访问启用FILESTREAM”复选框，其他的选项是针对Windows进行读/写的，可以都选中，如图3.11所示，然后单击“确定”按钮保存对FILESTREAM的设置。
图3.11启用FILESTREAM
第2步：打开SSMS，在对象资源管理器中右击已连接的服务器，选择“属性”菜单项。在“服务器属性”窗口中选择“高级”选项卡，在“文件流访问级别”选项后的下拉列表框中选择“已启用完全访问”选项，如图3.12所示。单击“确定”按钮后在SQL Server配置管理器中重启SQL Server服务，FILESTREAM在数据库实例中的设置即可完成。
图3.12设置文件流访问级别
完成以上步骤后数据库实例即启用了FILESTREAM，接下来就可以创建FILESTREAM文件组和具有FILESTREAM数据列的表了。在创建了FILESTREAM数据列后，访问的方法与访问一般varbinary(MAX)列的方式相同。NULL | NOT NULL：NULL表示列可取空值，NOT NULL表示列不可取空值。
DEFAULT constant_expression：为所在列指定默认值，默认值constant_expression必须是一个常量值、标量函数或NULL值。DEFAULT定义可适用于除定义为timestamp或带identity属性的列以外的任何列。IDENTITY：指出该列为标识符列，为该列提供一个唯一的、递增的值。
seed是标识字段的起始值，默认值为1，increment是标识增量，默认值为1。如果为IDENTITY属性指定了NOT FOR REPLICATION选项，则复制代理执行插入时，标识列中的值将不会增加。
<column_constraint>：列的完整性约束，指定主键、替代键、外键等。例如，指定该列为主键则使用PRIMARY KEY关键字。<table_constraint>：表的完整性约束，有关列的约束和表的约束将在第6章中介绍。
column_name AS computed_column_expression：用于定义计算字段，计算字段是由同一表中的其他字段通过表达式计算得到的。
其中，column_name为计算字段的列名，computed_column_expression是表其他字段的表达式，表达式可以是非计算字段的字段名、常量、函数、变量，也可以是一个或多个运算符连接的上述元素的任意组合。系统不将计算列中的数据进行物理存储，该列只是一个虚拟列。如果需要将该列的数据物理化，则需要使用PERSISTED关键字。
ON子句：filegroup | “default”指定存储表的文件组。如果指定了filegroup，则表将存储在指定的文件组中，数据库中必须存在该文件组。如果指定“default”，或者未指定ON参数，则表存储在默认文件组中。
partition_scheme_name(partition_column_ name)指定分区方案来创建分区表，partition_scheme_name是分区方案的名称，partition_column_name是表中的分区列。
TEXTIMAGE_ON {filegroup | "default"}：TEXTIMAGE_ON表示将text、ntext、image、xml、varchar(MAX)、nvarchar(MAX)、varbinary(MAX)和CLR用户定义类型的列存储在指定文件组中。如果表中没有这些类型的列，则不能使用TEXTIMAGE_ON。
如果没有指定TEXTIMAGE_ON或指定了"default"，则这些列将与表存储在同一文件组中。FILESTREAM_ON子句：filegroup | "default"指定存储FILESTREAM数据的文件组。
如果表包含FILESTREAM数据并且已分区，则必须包含FILESTREAM_ON子句并指定FILESTREAM文件组的分区方案partition_scheme_name。
【例3.5】设已经创建了数据库
设已经创建了数据库PXSCJ，现在该数据库中需创建学生情况表XSB，该表的结构见表3.3。创建表XSB的T-SQL语句如下： USE PXSCJ GO CREATE TABLE XSB(学号char(6)NOT NULL PRIMARY KEY,姓名char(8)NOT NULL,性别bitNULL DEFAULT 1,出生时间dateNULL,专业char(12)NULL,总学分intNULL,备注varchar(500) NULL)【例3.6】创建一个带计算列
创建一个带计算列的表，表中包含课程的课程号、总成绩和学习该课程的人数，以及课程的平均成绩。 CREATE TABLE PJCJ(课程号char(3) PRIMARY KEY,总成绩real NOT NULL,人数int NOT NULL,平均成绩AS总成绩/人数PERSISTED)1．使用T-SQL语句创建分区表（1）创建分区函数。创建分区函数使用CREATE PARTITION FUNCTION命令，语法格式如下。
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )AS RANGE [ LEFT | RIGHT ]FOR VALUES ( [ boundary_value [ ,...n ] ] )[ ; ]
【例3.7】对
对int类型的列创建一个名为NumberPF的分区函数，该函数把int类型的列中数据分成5个区。分为小于或等于50的区、大于50且小于或等于500的区、大于500且小于或等于1000的区、大于1000且小于或等于2000的区、大于2000的区。 CREATE PARTITION FUNCTION NumberPF(int) AS RANGE LEFT FOR VALUES(50,500,1000,2000) GO（2）创建分区方案。分区函数创建完后可以使用CREATE PARTITION SCHEME命令创建分区方案，由于在创建分区方案时需要根据分区函数的参数定义映射分区的文件组。所以需要有文件组来容纳分区数，文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。
在一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。一个分区方案只可以使用一个分区函数，而一个分区函数可以用于多个分区方案中。CREATE PARTITION SCHEME命令的语法格式如下。
CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]
【例3.8】假设文件组
假设文件组FGroup1、FGroup2、FGroup3、FGroup4、FGroup5已经在数据库PXSCJ中存在。根据例3.7中定义的分区函数创建一个分区方案，将分区函数中的5个分区分别存放在这5个文件组中。 CREATE PARTITION SCHEME NumberPSAS PARTITION NumberPFTO(FGroup1, FGroup2, FGroup3, FGroup4, FGroup5) GO（3）使用分区方案创建分区表。分区函数和分区方案创建以后就可以创建分区表了。创建分区表使用CREATE TABLE语句，只要在ON关键字后指定分区方案和分区列即可。
【例3.9】在数据库
在数据库PXSCJ中创建分区表，表中包含“编号”（值可以是1～5000）、“名称”两列，要求使用例3.8中的分区方案。 USE PXSCJCREATE TABLE sample(编号int NOT NULL PRIMARY KEY,名称char(8) NOT NULL) ON NumberPS(编号) GO2．使用图形向导方式创建分区表
【例3.10】使用图形向导方式
使用图形向导方式对PXSCJ数据库中的CJB表根据“成绩”列分区，成绩值为0～100，根据成绩值分为5个区：0～59，60～69，70～79，81～89，90～100。操作步骤如下。第1步：启动SQL Server Management Studio，在对象资源管理器中展开“数据库”，右击“PXSCJ”数据库菜单下的“表”选项，右击表“dbo.CJB”选择“存储”菜单项，单击“创建分区”子菜单项，进入分区向导窗口，单击“下一步”按钮，进入“选择分区列”界面，界面中将显示可用的分区列，选择“成绩”列，如图3.13所示。图3.13选择分区列第2步：单击“下一步”按钮，进入“选择分区函数”界面。选择“新建分区函数”选项，填写要新建的分区函数的名称，如PointsPF，如图3.14所示。当然，也可以选择现有的分区函数。图3.14选择分区函数
第3步：单击“下一步”按钮，进入“选择分区方案”界面。填写要新建的分区方案的名称，如PointsPS，如图3.15所示，单击“下一步”按钮。图3.15选择分区方案
第4步：进入“映射分区”界面，在界面中选择各个分区要映射到的文件组，如FGroup1、FGroup2等。选择单选按钮“左边界”，在“<=边界”栏指定分区的边界值，并选择一个边界值之外的额外文件组，如图3.16所示。另外，还可以单击“预计存储空间”按钮来查看各分区所需空间等信息。
图3.16选择文件组和指定边界值第5步：单击“下一步”按钮，进入“选择输出选项”界面。用户可以在其中选择“立即运行”选项，并单击“完成”按钮完成对CJB的分区。用户也可以选择“创建脚本”选项，并指定保存脚本的位置。
修改表结构可以使用ALTER TABLE语句。语法格式。
【例3.11】设已经
设已经在数据库PXSCJ中创建了表XSB，先在表XSB中增加1个新列“奖学金等级”，然后在表XSB中删除名为“奖学金等级”的列。在SSMS中新建一个查询，并输入如下脚本： USE PXSCJ GO ALTER TABLE XSBADD奖学金等级tinyint NULL GO 输入完成后执行该脚本，然后可以在对象资源管理器中展开“PXSCJ”中的表dbo.XSB的结构，查看运行结果。下面的脚本用于在表XSB中删除名为奖学金等级的列：USE PXSCJGOALTER TABLE XSBDROP COLUMN奖学金等级GO使用ALTER TABLE语句一次还可以添加多个列，中间用逗号隔开。
例如，向XSB表中添加奖学金等级1、奖学金等级2两个新列：USE PXSCJGOALTER TABLE XSBADD奖学金等级1 tinyint NULL,奖学金等级2 tinyint NULLGO
【例3.12】修改表
修改表XSB中已有列的属性：将名为“姓名”的列长度由原来的8改为10；将名为“出生时间”的列的数据类型由原来的date改为datetime。新建一个查询，在查询分析器中输入并执行如下脚本： USE PXSCJ GO ALTER TABLE XSBALTER COLUMN姓名char(10) GO ALTER TABLE XSBALTER COLUMN出生时间smalldatetime语法格式：DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]table_name [ ,...n ] [ ; ]其中，table_name是要被删除的表名。例如，要删除表XSB，使用的T-SQL语句为：USE PXSCJGODROP TABLE XSB
3.4.1插入记录用户可以自己根据需要向表中插入数据，插入的数据要符合列的约束条件，例如，不可以向非空的列插入NULL值。也可参考本书附录A中的数据样本表，本书后面用到的数据就是附录A中的样本数据。图3.17所示是插入数据后的XSB表。图3.17向表中插入记录
当表中的某些记录不再需要时，要将其删除。在对象资源管理器中删除记录的方法是：在表数据窗口中定位需删除的记录行，单击该行最前面的黑色箭头处选择全行，右击鼠标，选择“删除”菜单项，如图3.18所示。图3.18删除记录
在操作表数据的窗口中修改记录数据的方法是，先定位被修改的记录字段，然后对该字段值进行修改，修改之后将光标移到下一行即可保存修改的内容。
3.5.1插入记录插入记录使用INSERT语句。语法格式。说明如下。
（6）derived_table：这是一个由SELECT语句查询所得到的结果集。利用该参数，可把一个表中的部分数据插入到另一个表中。结果集中每行数据的字段数、字段的数据类型要与被操作的表完全一致。
使用结果集向表中插入数据时可以使用TOP (expression) [ PERCENT ]选项，这个选项可以在结果集中选择指定的行数或占指定百分比数的行插入表中。expression可以是行数或行的百分比，使用百分比时要加PERCENT关键字。有关SELECT语句的内容将在第4章中介绍。
（7）<dml_table_source>：指定插入目标表的行是INSERT、UPDATE、DELETE或MERGE语句的OUTPUT子句返回的行。（8）DEFAULT VALUES：该关键字说明向当前表中所有列均插入其默认值。此时，要求所有列均定义了默认值。
【例3.13】向PXSCJ数据库
向PXSCJ数据库的表XSB中插入如下一行数据：081101,王林, 1, 1990-02-10,计算机, 50 , NULL（假设XSB表中该行数据已被删除）INSERT INTO XSBVALUES('081101', '王林' , 1, '1990-02-10', '计算机',50, NULL)语句的运行结果如图3.19所示。图3.19使用T-SQL语句向表中插入数据【例3.14】假设上表
假设上表XSB中专业的默认值为“计算机”，备注默认值为NULL，则插入上例数据可以使用以下命令： INSERT INTO XSB (学号,姓名,性别,出生时间,总学分) VALUES('081101', '王林', 1, '1990-02-10', 50)下列命令效果相同： INSERT INTO XSBVALUES('081101', '王林', 1, '1990-02-10', DEFAULT,50, NULL);【例3.15】一次向
一次向XSB表中插入两行数据：（'091101', '王海', 1, '1991-05-10', '软件工程', 50, NULL）和（'091102', '李娜', 0, '1991-04-12', '软件工程',52, NULL）。 INSERT INTO XSB VALUES('091101', '王海', 1, '1991-05-10', '软件工程', 50, NULL), ('091102', '李娜', 0, '1991-04-12', '软件工程', 52, NULL)【例3.16】向学生管理系统涉及
向学生管理系统涉及的其他表中插入数据。向KCB表加入数据的T-SQL语句示例如下： INSERT INTO KCB VALUES('101','计算机基础',1,80,5)向CJB表加入数据的T-SQL语句示例如下： INSERT INTO CJB VALUES('081101',101,80)【例3.17】从表
从表XSB中生成计算机专业的学生表，包括学号、姓名、专业，要求新表中的数据为结果集中前5行。用CREATE语句建立表XSB1： USE PXSCJ GO CREATE TABLE XSB1(num char(6) NOT NULL PRIMARY KEY,name char(8) NOT NULL,speiality char(10) NULL)用INSERT语句向XSB1表中插入数据： INSERT TOP(5) INTO XSB1SELECT学号,姓名,专业FROM XSBWHERE专业= '计算机'使用SELECT语句从XSB1表中查询结果：SELECT *FROM XSB1执行结果如图3.20所示：图3.20执行结果
1．使用DELETE语句删除数据语法格式如下。
[ WITH <common_table_expression> [ ,...n ] ]DELETE [ TOP ( expression ) [ PERCENT ] ][ FROM ]{ table_name| view_name| rowset_function_limited[WITH ( < table_hint_limited > [ ...n ] )]}[ <OUTPUT Clause> ][ FROM <table_source> [ ,...n ] ][ WHERE { <search_condition>| { [ CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name }]}}][ OPTION ( <Query Hint> [ ,...n ] ) ][; ]
说明如下。
【例3.18】将PXSCJ数据库
将PXSCJ数据库的XSB表中总学分大于52的行删除，使用如下语句： USE PXSCJ GO DELETEFROM XSBWHERE总学分>52 GO【例3.19】将PXSCJ数据库
将PXSCJ数据库的XSB表中备注为空的行删除（实际不做操作）： DELETE FROM XSBWHERE备注IS NULL删除PXSCJ数据库的XSB表中的所有行（实际不做操作）： DELETE XSB2．使用TRUNCATE TABLE语句删除表数据使用TRUNCATE TABLE语句将删除指定表中的所有数据，因此也称为清除表数据语句。语法格式：TRUNCATE TABLE tb_name使用TRUNCATE TABLE语句删除了指定表中的所有行，但表的结构及其列、约束、索引等保持不变，而新行标识所用的计数值重置为该列的初始值。
如果要保留标识计数值，则要使用DELETE语句。TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同，二者均删除表中的全部行；但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每一行记录一项。
而TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
在T-SQL中，UPDATE语句可以用来修改表中的数据行。语法格式。据窗口，可以发现表中学号为“081101”的行的备注字段值已被修改，如图3.21所示。图3.21修改数据以后的表
【例3.21】将
将XSB表（数据以附录A中XSB表的样本数据为准）中所有学生的总学分都增加10。将姓名为“罗林琳”的同学的专业改为“软件工程”，备注改为“提前修完学分”，学号改为081261。 USE PXSCJ GO UPDATE XSBSET总学分=总学分+10 GO UPDATE XSBSET专业= '软件工程',备注= '提前修完学分',学号= '081261'WHERE姓名= '罗林琳' GO SELECT * FROM XSB执行结果如图3.22所示。图3.22修改结果
在SQL Server 2008中，新增加了一个MERGE语句，用于根据与源表连接的结果，对目标表执行插入、更新或删除操作。例如，根据一个表的数据在另一个表中插入、更新或删除有差异的行，可以对两个表进行信息同步。语法格式如下。
[ WITH <common_table_expression> [,...n] ]MERGE [ TOP ( expression ) [ PERCENT ] ][ INTO ] target_table [ [ AS ] table_alias ]USING <table_source>ON <merge_search_condition>[ WHEN MATCHED [ AND <search_condition> ]THEN { UPDATE SET <set_clause> | DELETE } ][ WHEN NOT MATCHED [ BY TARGET ] [ AND < search_condition> ]THEN INSERT [ ( column_list ) ] { VALUES ( values_list ) | DEFAULTVALUES } ][ WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ]THEN { UPDATE SET <set_clause> | DELETE }]
【例3.22】创建表
创建表a，要求表中数据与XSB表同步。创建学生表的语句如下。 USE PXSCJ GO CREATE TABLE a(学号char(6)NOT NULL PRIMARY KEY,姓名char(8)NOT NULL,性别bitNULL DEFAULT 1,出生时间dateNULL,专业char(12)NULL,总学分intNULL,备注varchar(500) NULL)进行信息同步使用如下语句。
MERGE INTO aUSING XSB ON a.学号=XSB.学号WHEN MATCHEDTHEN UPDATE SET a.姓名=XSB.姓名,a.性别=XSB.性别,a.出生时间=XSB.出生时间,a.专业=XSB.专业,a.总学分=XSB.总学分,a.备注=XSB.备注WHEN NOT MATCHEDTHEN INSERT VALUES(XSB.学号,XSB.姓名,XSB.性别,XSB.出生时间,XSB.专业,XSB.总学分,XSB.备注)WHEN NOT MATCHED BY SOURCETHEN DELETE;
下载地址：{{downurl}}
{{{a}}}
{{#b}}{{{b}}}
{{/b}} {{/title}} {{#nolog}}登陆注册
{{/nolog}} {{#djsx}}点我刷新
{{/djsx}} {{#gocz}} {{/gocz}} {{#ffqr}} {{/ffqr}}