第5章 表 的 管 理 表是SQL Server实例中全部数据的容器，关系数据库中的所有数据都存储在表中，是数据库中最重要的部分。表也是数据库所有其他对象的基础，管理数据库首先必须合理、有效地管理表。本章首先介绍SQL Server中表的相关概念，然后介绍表的设计、创建、修改和删除以及如何对表中的数据进行添加、修改和删除。 5.1SQL Server表概述 在SQL Server 2012中，一个数据库最多可以存储20亿个表。表是数据库的一种对象，由行和列组成，在数据库中，表的每一列表示数据库表的一个属性，每个表最多可以有1024列，表的每一行表示一条记录，是对实体完整性的描述，每行最多可以存储8060字节内容，表的行数及总大小仅受可用存储空间的限制。在设计数据库时，要根据数据库逻辑结构设计的要求，确定需要什么样的表、各表中都有哪些数据、表的各列的数据类型及列宽、哪些列允许空值、哪里需要索引、哪些列是主键、哪些是外键以及是否要使用和何时使用约束、默认设置或规则等。 5.1.1数据类型简介 在SQL Server中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型： 整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。 SQL Server提供系统数据类型集，该类型集定义了可与SQL Server一起使用的所有数据类型。用户还可以使用TransactSQL或Microsoft .NET Framework定义自己的数据类型。别名数据类型基于系统提供的数据类型。 SQL Server 2012支持的数据类型可以归纳为字符数据、二进制数据、日期和时间数据、逻辑数据、数字数据和其他数据类型。 1. 字符数据类型 1) 字符型数据类型 字符型数据类型用于存储汉字、英文字母、数字符号和其他各种符号。输入字符型数据时要用单引号(')或双引号(")将字符括起来。字符型数据有定长字符型(char)、变长字符型(varchar)和文本型(text)3种。 char数据类型的定义形式为char［(n)］，n的取值为1~8000，即最多可存储8000个字符。指定的字符取决于安装SQL Server时所指定的字符集，通常采用ANSI字符集。在用char(n)数据类型对列进行说明时，指示列长度为n。在数据定义或变量声明语句中如果没有指定n，则系统默认长度为1。对于超过列长度的输入将被截断，若输入字符的长度短于指定字符长度时 则用空格填满。 varchar数据类型的定义形式为varchar［(n|max)］，n的取值为1~8000。max指示最大存储大小是231-1字节(2GB)。varchar数据类型的结构与char数据类型一致，它们的主要区别是当输入varchar字符长度小于n时不用空格来填满，按输入字符的实际长度存储。若输入的数据超过n个字符，则截断后存储。varchar类型所需存储空间要比char数据小一些，但varchar列的存取速度比char列要慢一些。 text数据类型用于存储数据量庞大而变长的字符文本数据。text列的长度可变，最多可包含231-1个字符。用户要求表中的某列能存储255个字符以上的数据，可使用text数据类型。text数据类型不能用作变量或存储过程的参数。 第 5 章 表 的 管 理 数据库技术与应用——SQL Server 2012(微课版) 2) Unicode字符数据类型 SQL Server允许使用多国语言，采用Unicode标准字符集。为此SQL Server提供多字节的字符数据类型： nchar(n)、nvarchar(n|max)和ntext。 Unicode字符串的格式与普通字符串相似，但Unicode数据中的每个字符都使用两字节进行存储。Unicode字符串常量的前面有一个大写N(N代表SQL92标准中的国际语言——National Language)。例如，'Michael'是字符串常量，而N'Michael'则是Unicode常量。类似地，Unicode字符串的几种类型也是在普通字符串的类型名前增加了一个字母n来标识的。 nchar可存放Unicode字符的固定长度字符类型，n最大长度为4000个字符。 nvarchar可存放Unicode字符的可变长度字符类型，n最大长度为4000个字符。max指示最大存储大小是231-1字节。 ntext可存放Unicode字符的文本类型，其最大长度为230-1个字符。存储大小是所输入字符串长度的两倍(以字节为单位)。 nchar、nvarchar和ntext的用法分别与char、varchar和text相同，只是Unicode支持的字符范围更大，存储Unicode字符需要一些额外开销空间。由于这些额外开销和增加的空间，应该避免使用Unicode列，除非确实有需要使用它们的业务或语言需求。 text数据类型用于在数据页内外存储大型字符数据。与text数据类型相比，更好的选择是使用varchar(max)类型，因为将获得更好的性能。另外，text和ntext数据类型在SQL Server的一些未来版本中将不可用，因此最好使用varchar(max)和nvarchar(max)，而不是text和ntext数据类型。 SQL Server 2012的字符数据类型如表51所示。 表51SQL Server 2012的字符数据类型 数 据 类 型数据范围描述存 储 空 间 char(n)n为1~8000字符n字节 nchar(n)n为1~4000 Unicode字符n字节的两倍 varchar(n)n为1~8000字符2×字符数＋2字节额外开销 nvarchar(n)n为1~4000字符2×字符数＋2字节额外开销 续表 数 据 类 型数据范围描述存 储 空 间 varchar(max)最多为231-1字节2×字符数＋2字节额外开销 nvarchar(max) 最多为231-1个Unicode字符2×字符数＋2字节额外开销 text最多为231-1个字符每字符1字节 ntext最多为230-1个Unicode字符每字符2字节 2. 二进制数据类型 SQL Server二进制数据类型用于存储二进制数或字符串。与字符数据类型相似，在列中插入二进制数据时，用引号标识，或用0x开头的两个十六进制数构成一字节。SQL Server有3种有效二进制数据类型，即定长二进制类型binary、变长二进制类型varbinary和大块二进制类型image。 binary数据类型的定义形式为binary［(n)］，n的取值为1~8000，若不指定n，则n默认为1。binary数据用于存储二进制字符，例如程序代码和图像数据。 varbinary［(n|max)］数据类型与binary数据类型基本相同，但通过存储输入数据的实际长度而节省存储空间，但存取速度比binary类型要慢。n的取值范围为1~8000。除非数据长度超过8KB，一般宜用varbinary类型来存储二进制数据，建议列宽的定义不超过所存储的二进制数据可能的最大长度。max指示最大存储大小是231-1字节。 image数据类型与text数据类型类似，可存储1~231-1字节的二进制数据。image数据类型存储的是二进制数据而不是文本字符，不能用作变量或存储过程的参数。image数据列可以用来存储超过8KB的可变长度的二进制数据，如Word文档、Excel电子表格、图像或MP3等其他文件。image数据类型可在数据页外部存储最多2GB的文件。image数据类型的首选替代数据类型是varbinary(max)，其性能通常比image数据类型好。 表52列出了二进制数据类型，对其做了简单描述，并说明了要求的存储空间。 表52二进制数据类型 数 据 类 型数据范围描述存 储 空 间 binary(n)n为1~8000十六进制数字n字节 image最多为231-1字节每字符1字节 varbinary(n)n为1~8000十六进制数字每字符1字节＋2字节额外开销 varbinary(max) 最多为231-1字节每字符1字节＋2字节额外开销 3. 日期和时间数据类型 日期和时间数据类型用于存储日期和时间数据。SQL Server 2012支持多种日期和时间数据类型： datetime、datetime2、dateoffset、smalldatetime、date和time。 datetime数据类型存储两个长度为4字节的整数： 日期和时间。datetime数据类型有许多格式，可被SQL Server的内置日期函数操作。 datetime2数据类型是datetime数据类型的扩展，有着更广的日期范围。时间总是用时、分钟、秒形式来存储的。可以定义末尾带有可变参数的datetime2数据类型，如datetime2(3)。这个表达式中的3表示存储时秒的小数精度为3位，或0.999。有效值为0~9，默认值为3。 datetimeoffset数据类型和datetime2数据类型一样，带有时区偏移量。该时区偏移量最大为＋/-14小时，包含了UTC (Universal Time Coordinated，协调世界时)偏移量，因此可以合理化不同时区捕捉的时间。 smalldatetime数据类型只需4字节的存储空间，时间值是按小时和分钟来存储的。插入数据时，日期和时间值以字符串形式传给服务器。 date数据类型只存储日期，而time数据类型只存储时间。它也支持time(n)声明，因此可以控制小数秒的粒度。与datetime2和datetimeoffset一样，n可为0~7。 表53列出了日期和时间数据类型，对其进行简单描述，并说明了要求的存储空间。 表53日期和时间数据类型 数 据 类 型数据范围描述存 储 空 间 Date00010101~ 99991231 3字节 Datetime1753年1月1日~9999年12月31日，时间范围为00∶00∶00~23∶59∶59.9978字节 Datetime2(n)00010101~ 99991231，n为0~7，指定小数秒6~8字节 Datetimeoffset(n)00010101~ 99991231日，n为0~7，指定小数秒＋/-偏移量默认10字节 SmalldateTime1900年1月1日~2079年6月6日，精确到1分钟4字节 Time(n)小时∶分钟∶秒.9999999，n为0~7，指定小数秒5字节 4. 逻辑数据类型 SQL Server的逻辑数据类型也称为位(bit)数据类型，适用于判断真/假的场合，长度为一字节。位数据类型取值为1、0或NULL。非0的数据被当成1处理，位列不允许建立索引，多个位列可以占用同一字节。如果一个表有不多于8个的位列，SQL Server将这些列合在一起用一字节存储。如果表中有9~16个位列，这些列将作为两字节存储。更多列的情况以此类推。字符串值TRUE和FALSE可转换为bit值，TRUE将转换为1，FALSE将转换为0。 5. 数字数据类型 SQL Server提供了多种方法存储数值，SQL Server的数字数据大致可分为4种基本类型。 1) 整数数据类型 有4种整数数据类型： tinyint、smallint、int和bigint，用于存储不同范围的值。整数可以用较少的字节存储较大的精确数字，考虑到其高效的存储机制，只要有可能，对数值列应尽量使用整数。SQL Server 2012的整数类型如表54所示。 表54SQL Server 2012的整数数据类型 数 据 类 型数据范围描述存储空间/B tinyint0~255的整数1 smallint-215~215-1的整数2 int-231~231-1的整数4 bigint-263~263-1的整数8 2) 近似数值数据类型 近似数值数据类型包括float和real类型。它们用于表示浮点数据。但是，由于它们是近似的，因此不能精确地表示所有值。 float(n)中的n是用于存储该数尾数的位数(以科学记数法表示)。SQL Server对此只使用两个值。如果指定位于1~24， SQL Server就使用24。如果指定位于25~53，SQL Server就使用53。当指定float()时(括号中为空)，默认为53。real的同义词为float(24)。 表55列出了近似数值数据类型，对其进行简单描述，并说明了要求的存储空间。 表55近似数值数据类型 数 据 类 型数据范围描述存 储 空 间 float［(n)］ -1.79×10308~-2.23×10-308， 0，2.23×10-308~1.79×10308n为1~24时，4字节 n为25~53时，8字节 real() -3.40×1038~-1.18×10-38， 0，1.18×10-38~3.40×10384字节 3) 精确数值数据类型 精确数值数据类型用于存储有小数点且小数点后位数确定的实数。SQL Server支持两种精确的数值数据类型： decimal和numeric。这两种数据类型在功能上等价，定义格式如下： decimal［(p［, s］)］ numeric［(p［, s］)］ 其中，p指定精度，即小数点左边和右边可以存储的十进制数字的最大个数。s指定小数位数，即小数点右边可以存储的十进制数字的最大个数。精确数值数据类型如表56所示。 表56精确数值数据类型 数 据 类 型数据范围描述存 储 空 间 numeric(p,s)或decimal(p,s)-1038＋1~1038-1的数值最多17字节 4) 货币数据类型 除了decimal和numeric类型适用于货币数据的处理外，SQL Server还专门提供了两种货币数据类型： money和smallmoney，如表57所示。 输入货币数据时必须在货币数据前加$符号，如果未提供该符号，其值被当成浮点数，可能会损失值的精度，甚至被拒绝。在显示货币值时，数值的小数部分仅保留2位有效位。 表57货币数据类型 数 据 类 型数据范围描述存储空间/B money-922337203685477.5808~ 922337203685477.58078 smallmoney-214748.3648~214748.36474 6. 其他数据类型 除了以上基本数据类型外，SQL Server 2012还支持其他一些数据类型，如表58所示。 表58SQL Server 2012还支持的其他数据类型 数 据 类 型数据范围描述存 储 空 间 cursor包含一个对游标的引用和可以用作变量或存储过程OUTPUT参数不适用 hierarchyid包含一个对层次结构中位置的引用1~892字节＋2字节的额外开销 sql_variant可能包含任何系统数据类型的值，除了text、ntext、image、timestamp、xml、varchar(max)、nvarchar(max)、varbinary (max)、sql_variant、geography、geometry、hierarchyid、datetimeoffset以及用户定义的数据类型。最大为8000字节数据＋16字节8016字节 table用于存储结果集以进行后续处理。主要用于返回表值函数的结果集，也可用于函数、存储过程和批处理中取决于表定义和存储的行数 timestamp or rowversionrowversion对于每个表来说是唯一的、自动存储的值。通常用于版本戳，该值在插入和每次更新时自动改变。timestamp数据类型为rowversion数据类型的同义词。在DDL语句中，尽量使用rowversion8字节 uniqueidentifier可以包含全局唯一标识符(Globally Unique Identifier，GUID)。guid值可以从Newid()函数获得。这个函数返回的值对所有计算机来说是唯一的16字节 xml存储XML数据的数据类型。可以在列中或者xml类型的变量中存储XML实例最多2GB 5.1.2空值和默认值 当用户往表中插入一行而未对其中的某列指定值时，该列将出现空值(NULL)。空值不同于空白(空字符串)或数值零，通常表示未填写、未知(Unknown)、不可用或将在以后添加的数据。例如，某公司的某份销售订单在初下单时，是无法确定货物的发货日期(send_date)和到货日期(arrival_date)的，故该订单信息在进入数据库时，send_date和arrival_date不能填写，系统将用空值标识该订单记录的这两列。 因为每个空值均为未知，所以没有两个空值是相等的，比较两个空值或将空值与其他任何数值相比均返回未知。空值会对查询命令或统计函数产生影响。实际应用中，应尽量少使用空值，或对查询和数据修改语句进行规划，使空值的影响降到最小。可以使用查询或数据修改语句消除空值或将空值转换成其他值，也可以使用列的默认值约束来避免一些空值。 可通过以下方法在列中插入空值： 在INSERT或UPDATE语句中显式声明NULL，或不使此列进入INSERT语句，或使用ALTER TABLE语句在现有表中新添一列。若要判断某列中的值是否为空值，可以使用关键字IS NULL或IS NOT NULL。 默认值是指表中数据的默认取值，默认值对象是数据库的对象不依附于具体的表对象，即默认值对象的作用范围是整个数据库。 5.1.3约束 约束定义了关于列中允许值的规则，SQL Server通过限制列中数据、行中数据和表之间数据来保证数据的完整性。SQL Server 2012支持非空值约束、默认约束、唯一性约束、主键约束、外键约束等多种约束。 (1) 非空值约束(Not Null)限制数据列不接受NULL值，即当对表进行插入(INSERT)操作时，非空值约束的列必须给出确定的值。例如，如果employee表的employee_name(员工姓名)列定义为非空约束，则当录入员工信息时，必须提供员工的姓名。 (2) 默认约束(Default)为数据列定义一个默认值，输入数据时若没有为该列提供值，则将所定义的默认值提供给该列。默认值可以是常量，也可以是表达式。例如，为employee表的hire_date(雇用日期)列定义默认约束表达式“GetDate()”(获取当前日期)，将使数据库服务器在用户没有输入时为该列填上默认值，即当天的日期。 (3) 唯一性约束(Unique)限制约束的列，在表的范围内，不允许有两行包含相同的非空值(可以出现多个空值)。 (4) 主键约束(Primary Key)标识列(或列集)，这些列(或列集)的值唯一标识表中的行。在一个表中，不能有两行包含相同的主键值，主键的值不能为NULL。例如，employee表的employee_id列可以选作主键。 每个表都应有一个主键，建议使用一个整数列作为主键。实际应用中，有些表中的数据不便于提供主键列。在SQL Server中，通常可以另外建一列并使之成为一个易于使用的主键列。 (5) 外键约束(Foreign Key)也称为外部关键字约束，根据从另一个表中某列(通常是主键列)获得的数据集合来进行有效值判定。这时，被约束列所在的表称为外键表，提供数据的表称为主键表或引用表，提供数据的列称为引用列，所提供的数据称为键值。外键常用来标识表与表之间的关系。 例如，Sales数据库中的employee表、sell_order表之间存在一种逻辑联系，即sell_order(销售订单)表的employee_id(员工编号)列的值必须是employee表employee_id列中多个值当中的一个，因为签订销售订单的人必须是当前公司员工，因此，在sell_order表上应建立外键约束FK_sell_order_employee来限制sell_order表的employee_id列的值必须来自employee表的employee_id列。 关于约束的详细情况将在第8章中进行介绍。 5.2表的创建与维护 创建表就是定义一个新表的结构以及它与其他表之间的关系。表的维护是指在数据库中创建表以后，对表进行修改、删除等操作。修改表是指更改表结构或表间关系，而删除表是指从数据库中去除该表的表结构、表间关系和表中所有数据。所谓表结构指的是构成表的列、各列的定义(列名、数据类型、数据精度、列上的约束等)和表上的约束。 5.2.1使用SQL Server管理平台对表进行操作 在SQL Server管理平台中，表的操作可以可视化完成。管理平台中可以对单个表进行设计，也可以对同一数据库的多个表进行设计，并生成一个或多个关系图，以显示数据库中的部分或全部表、列、键和表间关系。 1. 使用SQL Server管理平台创建和修改表 视频讲解 在SQL Server管理平台中创建数据表的最常用方法是直接输入字段法。其创建数据表的一般步骤如下： (1) 打开“对象资源管理器”窗格，打开需要创建表的数据库Sales，在“表”上右击，在弹出的快捷菜单中选择“新建表”命令，打开表设计器对话框，如图51所示。 图51表的创建 (2) 如果要创建employee表，在该对话框中，输入雇员表的列名，选择每列的数据类型，设置各列是否允许为空，如图51所示。列名在一个表中的唯一性是由SQL Server强制实现的。每一列都有一个唯一的数据类型，数据类型确定列的精度和长度，可以根据实际的需要进行选择。列允许为空值时将显示“√”，表示该列可以不包含任何数据，空值既不是0，也不是空字符，而是表示未知，如果不允许列包含空值，则必须为该列提供具体的数据。 (3) 输入完成后，单击工具栏中的“保存”按钮，打开“选择名称”对话框，如图52所示。输入新建表的名称后，单击“确定”按钮，则创建了一个表。 (4) 若要修改该表，展开“数据库”结点，在需要修改的表上右击，在弹出的快捷菜单中选择“修改”命令，可重新在打开表设计器中进行上述操作。 2. 使用SQL Server管理平台设计数据库关系 图52“选择名称”对话框 在SQL Server管理平台设计器以图形方式显示部分或全部数据库结构，这种图形被称为数据库关系图。关系图可用来创建和修改表、列、关系、键、索引和约束。可创建一个或更多的关系图，以显示数据库中的部分或全部表、列、键和关系。 在管理平台中，展开要操作的数据库，选择“数据库关系图”选项，然后右击，在弹出的快捷菜单中选择“新建数据库关系图”命令，如图53所示。在弹出的对话框中选择要建立关系的表后，则会弹出数据库关系图设计器窗口。 图53关系图(仅显示表中列名) 图53是Sales数据库中一个简单而典型的关系图。在该关系图中，可以看到表department与表employee (仅显示了列名)由一条连接线联系起来了，这就是这两个表之间的关系，当鼠标移到该连线上时，出现提示框显示该关系的名称信息FK_employee_department。 在关系图的空白处右击，在弹出的快捷菜单中可以选择新建表或添加数据库中已定义(但未出现在关系图中)的表。关系图中将出现与表设计器上半窗格同样的网格，用以定义新表中各列的基本属性。在该表的级联快捷菜单中选择“属性”选项，可创建或定义该表的关系、键、索引和约束或修改当前列的附加特性。 可以切换表视图以显示表的完整列定义，这样，可以直接在关系图中对表结构进行修改。 在关系图的某个表上右击，在弹出的快捷菜单中，可以选择从关系图或从数据库中删除该表。 3. 在SQL Server管理平台中删除表 当某个表不再使用时，就可以将其删除以释放数据库空间。表被删除后，它的结构定义、数据、全文索引、约束和索引都永久地从数据库中删除。表上的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动删除。 在管理平台中可以很方便地删除数据库中已有的表。其操作方法如下： （1） 在管理平台中右击要删除的表，在弹出的快捷菜单中选择“删除”命令，则会弹出如图54所示的“删除对象”对话框，单击“确定”按钮即可删除表。 图54“删除对象”对话框 （2） 单击“显示依赖关系”按钮即会出现如图55所示的对话框，它可以分别列出表所依靠的对象和依赖于表的对象，当有对象依赖关系时就不能删除表。 图55表的依赖关系对话框 5.2.2使用TransactSQL语句创建表 设计完数据库后就可创建数据库中将存储数据的表。在TransactSQL中，创建表可使用CREATE TABLE语句，其语法格式如下： CREATE TABLE ［database_name. ［schema_name］.|schema_name.］ table_name ［AS FileTable］ ( { |} ［］ ［,...，n］ ) ［ON { partition_scheme_name ( partition_column_name )|filegroup|"default" }］ ［{ TEXTIMAGE_ON { filegroup|"default" }］ 各选项的含义如下： (1) database_name： 要在其中创建表的数据库名称，必须是现有数据库的名称，默认为当前数据库。 (2) schema_name： 新表所属架构的名称。 (3) table_name： 新表的名称。表名必须遵循有关标识符的规则。table_name最多可包含128个字符，本地临时表名(以单个数字符号(#)为前缀的名称)不能超过116个字符。 (4) AS FileTable： 将新表创建为FileTable。FileTable具有固定架构，无须指定列。在SQL Server中FileTable是一种专用的用户表，可以将文件和文档存储在FileTable的表中。 (5) column_definition： 表示数据列的语法结构，其语法格式如下。 ::= column_name ［type_schema_name.］ type_name ［COLLATE collation_name］ ［NULL|NOT NULL］ ［［CONSTRAINT constraint_name］ DEFAULT constant_expression］ |［IDENTITY ［( seed,increment )］ ［NOT FOR REPLICATION］］ ［ROWGUIDCOL］ ［ ［，...，n］］ ［SPARSE］ 其中选项的含义如下。 ① column_name ［type_schema_name.］ type_name： 指定列名和存储在该列的数据类型。  column_name： 表中列的名称。列名称必须遵循标识符的规则，且在表中必须唯一。column_name最多可以有128个字符。对于使用timestamp数据类型创建的列，可以省略column_name。如果未指定column_name，则timestamp列的名称默认为timestamp。  ［type_schema_name.］ type_name： 指定列的数据类型以及该列所属的架构。 ② COLLATE collation_name： 指定该列的排序规则。 ③ NULL |NOT NULL： 确定列中是否允许使用空值。 ④ ［CONSTRAINT constraint_name］ DEFAULT constant_expression： 定义约束。各选项含义如下。  CONSTRAINT： 可选关键字，表示PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY或CHECK约束定义的开始。  constraint_name： 约束的名称。约束名称必须在表所属的架构中唯一。  DEFAULT constant_expression： 用一个常量表达式设置该列的默认约束。 ⑤ IDENTITY ［(seed，increment)］： 设置该列为标识列，并由seed和increment分别指定种子和增量(默认都为1)。 ⑥ NOT FOR REPLICATION： 指定列的IDENTITY列的属性，在把从其他表中复制的数据插入到表中时不发生作用。 ⑦ ROWGUIDCOL： 指定该列为全局唯一标识符列。 ⑧ ： 定义在该列上的列约束。取NULL或NOT NULL时，指定是否在该列上设置非空约束。 ⑨ ［SPARSE］： 指示列为稀疏列。稀疏列已针对NULL值进行了存储优化。不能将稀疏列指定为NOT NULL。 (6) computed_column_definition： 某计算列的列定义，定义计算列的值的表达式。计算列并不是物理地存储在表中的虚拟列，除非此列标记为PERSISTED。该列由同一表中的其他列通过表达式计算得到。表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能是子查询，也不能包含别名数据类型。 在使用计算列时，应注意如下几点： ① 计算列不能作为INSERT或UPDATE语句的目标。 ② 计算列不能用作DEFAULT或FOREIGN KEY约束定义，也不能与NOT NULL约束定义一起使用。 ③ 如果计算列由具有确定性的表达式定义，并且索引列中允许计算结果的数据类型，则可将该列用作索引中的键列，或用作PRIMARY KEY或UNIQUE约束的一部分。 (7) table_constraint： 表示对数据表的约束进行设置。 (8) partition_scheme_name ( partition_column_name ) |filegroup |"default"： 指定存储表的分区架构或文件组。各选项含义如下 。  partition_scheme_name： 分区架构的名称，该分区架构定义要将已分区表的分区映射到的文件组。  partition_column_name： 表示分区策略依据的列。  filegroup： 表将存储在指定的文件组中。  "default"： 表存储在默认文件组中。 (9) TEXTIMAGE_ON { filegroup |"default"}： 指示text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max)和CLR(Common Language Runtime)用户定义类型的列存储在指定文件组的关键字。如果表中没有较大值的列，则不允许使用TEXTIMAGE_ON。如果指定了，则不能指定TEXTIMAGE_ON。如果指定了"default"，或者未指定TEXTIMAGE_ON，则较大值的列存储在默认文件组中。 【例51】简单的表定义。 USE Sales CREATE TABLE employee ( employee_id char(4)NOT NULL, employee_name varchar(20)NOT NULL, sex char(2)NOT NULL, birth_date date NOT NULL, hire_date date NOT NULL, address varchar(50), telephone varchar(12), wages money, department_id char(4), resume text ) 本例使用USE语句打开Sales数据库，使之成为当前数据库，然后在当前数据库创建了employee表，所有者为当前用户。 employee表共有10列，使用char(n)、varchar(n)、date、money和text共5种数据类型，并设置其中5列采用了非空约束，address、telephone和wages、department_id、resume列默认指定允许为空。 因为未指定employee表的所在文件组，该表将被放置在默认文件组中。 【例52】为表指定文件组。 CREATE TABLE Sales.dbo.supplier (supplier_id char(6)NOT NULL, supplier_namevarchar(50)NOT NULL, linkman_namevarchar(20), addressvarchar(50), telephone varchar(12) NOT NULL )ON ［PRIMARY］ 本例在Sales数据库创建表supplier，所有者为dbo。该表被显式地放置在PRIMARY文件组中。 【例53】对计算列使用表达式。 CREATE TABLE salary (姓名 varchar(10), 基本工资 money, 奖金 money, 总计 AS 基本工资+奖金) 本例创建了salary表，定义了3个数值列，其中“总计”为计算列，其值由表中另外两列的数据从表达式“基本工资+奖金”计算而来。 【例54】定义表autouser自动获取用户名称。 CREATE TABLE autouser (编号 int identity(1,1)NOT NULL, 用户代码 varchar(18), 登录时间 AS Getdate(), 用户名 AS User_name() ) 本例创建了表autouser，该表“登录时间”和“用户名”列的信息可以分别通过函数Getdate()和User_name()自动获取。当表插入数据时，只需添加“用户代码”数据，其他列的值都自动产生。图56为表autouser插入数据行时表中数据情况。 图56表autouser插入数据行时表中数据情况 【例55】创建临时表。 CREATE TABLE #students (学号 varchar(8), 姓名 varchar(10), 性别 varchar(2), 班级 varchar(10) ) 在实际应用中，经常会用到临时表来暂存储数据。SQL Server中使用代码创建临时表，需要在表名前加“#”或“##”符号。其中“#”表示本地临时表，在当前数据库内使用，“##”表示全局临时表，可在所有数据库内使用。临时表存储在tempdb中。 当用户与SQL Server实例断开连接后，将自动删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与SQL Server实例断开连接后，全局临时表将自动被删除。 5.2.3使用TransactSQL语句修改表 在创建数据表之后，经常需要对原先的某些定义进行一定的修改，例如添加、修改、删除列以及添加、删除各种约束。但列的某些数据类型、NULL值或IDENTITY属性不能直接进行修改。SQL Server使用ALTER TABLE语句对数据表的结构进行重新定义。 SQL Server2012的ALTER TABLE语句提供了丰富的参数项，这里列出常用语法结构。其语法格式如下： ALTER TABLE ［database_name. ［schema_name］.|schema_name.］ table_name { ALTER COLUMN column_name { ［type_schema_name.］type_name［({precision ［,scale］|max| xml_schema_collection } )］ ［COLLATE collation_name］ ［NULL|NOT NULL］ ［SPARSE］ | {ADD|DROP }{ ROWGUIDCOL|PERSISTED|NOT FOR REPLICATION|SPARSE } }|［WITH { CHECK|NOCHECK }］ | ADD {| || } ［,...，n］ | DROP { ［CONSTRAINT］ constraint_name ［WITH ( ［,...，n］ )］|COLUMN column_name } ［,...，n］ |［WITH { CHECK|NOCHECK }］ { CHECK|NOCHECK } CONSTRAINT { ALL|constraint_name ［,...，n］ } |{ ENABLE|DISABLE } TRIGGER { ALL|trigger_name ［,...，n］ } } 各选项的含义如下： (1) schema_name： 更改表所属架构的名称。 (2) table_name： 指定要修改的表名。 (3) ALTER COLUMN column_name： 指定表中要更改的列名为column_name。对列的更改不能与列或表的其他定义相冲突。如某列的默认值为字符串，则数据类型不能更改为非字符串类型，但可先删除默认约束再更改数据类型。以下类型的列不能直接更改。 ① 数据类型为text、image、ntext或timestamp的列。 ② 表的ROWGUIDCOL列。 ③ 计算列或用于计算列中的列。 ④ 用于索引中的列。除非该列数据类型是varchar、nvarchar或varbinary，数据类型没有更改，而且新列大小大于或等于旧列大小。 ⑤ 用于主键/外键/检查/唯一约束中的列。 ⑥ 有默认约束的列的数据类型不能更改，但可更改列的长度、精度或小数位数。 有些数据类型的更改可能导致数据的更改。例如，将数据类型为nchar或nvarchar的列更改为char或varchar类型，将导致扩展字符的转换。降低列的精度或小数位数可能导致数据截断。 (4) ［type_schema_name.］ type_name： 更改后的列的新数据类型或添加的列的数据类型。原来的数据类型必须可以隐式转换为新数据类型。如果要更改的列是标识列，新数据类型必须是支持标识属性的数据类型(整型)。新数据类型不能为timestamp。 (5) precision： 指定的数据类型的精度。 (6) scale： 指定的数据类型的小数位数。 (7) xml_schema_collection： 仅应用于xml数据类型，以便将xml架构与类型相关联。 (8) COLLATE collation_name： 指定更改后的列的新排序规则。 (9) NULL|NOT NULL： 指定该列是否可接受空值。 (10) ［SPARSE］： 指示列为稀疏列。稀疏列已针对NULL值进行了存储优化。不能将稀疏列指定为NOT NULL。 (11) WITH{CHECK|NOCHECK}： 指定表中的数据是否用新添加的或重新启用的FOREIGN KEY或CHECK约束进行验证。 (12) ADD： 指定添加一个或多个列定义、计算列定义或者表约束。 (13) DROP { ［CONSTRAINT］ constraint_name|COLUMN column_name }： 指定从表中删除名为constraint_name的约束或者名为column_name的列。必须删除所有基于列的索引和约束后，才能删除列。 WITH(指定 设置一个或多个删除聚集约束选项。 (14) {CHECK |NOCHECK } CONSTRAINT： 指定启用或禁用constraint_name。 (15) ALL： 指定使用NOCHECK选项禁用所有约束，或者使用CHECK选项启用所有约束。 (16) { ENABLE |DISABLE } TRIGGER： 指定启用或禁用trigger_name。 (17) trigger_name： 指定要启用或禁用的触发器的名称。 【例56】更改表以添加新列，然后再删除该列。 USE Sales ALTER TABLE employee ADD email varchar(20)NULL GO sp_help employee ALTER TABLE employee DROP COLUMN email GO sp_help employee 本例先为employee表添加了email列，通过系统存储过程sp_help可以查看修改后的employee表的各列，再使用DROP子句删除添加的列。 【例57】将表employee的列address改为varchar(150)数据类型，并且不允许为空。 ALTER TABLE employee ALTER COLUMN address varchar(150) NOT NULL GO 注意： 一定要确认已有的数据中列address均不为空后，才能进行此操作。 关于修改表的各种约束操作参见8.3节的内容。 5.2.4使用TransactSQL语句删除表 表所有者可以使用TransactSQL语句删除任何其所有的表。如果不想等待临时表自动删除，则可明确删除临时表。 删除表的TransactSQL语句格式如下： DROP TABLE ［database_name. ［schema_name］.|schema_name.］ table_name ［,...，n］ ［;］ 各选项的含义如下。 (1) database_name： 要在其中创建表的数据库的名称。 (2) schema_name： 表所属架构的名称。 (3) table_name： 要删除的表的名称。 注意： (1) 删除表时，表上的规则或默认值将解除绑定，任何与表关联的约束或触发器将自动除去。如果重新创建表，则必须重新绑定适当的规则和默认值，重新创建任何触发器并添加必要的约束。 (2) 不能使用DROP TABLE删除被FOREIGN KEY约束引用的表。必须先删除引用FOREIGN KEY约束或引用表。如果要在同一个DROP TABLE语句中删除引用表以及包含主键的表，则必须先列出引用表。 (3) 系统表不能使用DROP TABLE语句删除。 【例58】删除当前数据库内的表。 USE Sales GO DROP TABLE employee 本例从当前数据库Sales中删除employee表及其数据和索引。 【例59】删除另外一个数据库内的表。 DROP TABLE Sales.dbo.employee 本例删除Sales数据库内的employee表。可以在服务器实例上的任何数据库内执行此操作。 5.3表中数据的维护 视频讲解 数据库的主要用途是存储数据并使授权的应用程序和用户能够使用这些数据。在数据库中的表对象建立后，用户对表的访问可以归纳为4个基本操作： 添加或插入新数据、检索现有数据、更改或更新现有数据和删除现有数据。 这4种操作通常称为CRUD(Create，Retrieve/Read，Update，Delete)操作。其中的R操作，即对数据表的检索，通常也被称为查询(Query)，将在第6章详细介绍，本节只讨论其余3种操作。 对表中数据进行维护也有两种方法： 一是使用SQL Server管理平台； 二是使用TransactSQL语句。同前面介绍管理平台的操作类似，在管理平台中，右击需要操作的表，在弹出的快捷菜单中选择“打开表”命令，再选择有关命令，即可完成查询、修改和删除表中数据的操作。下面重点介绍表中数据维护的TransactSQL语句。 5.3.1插入数据 INSERT语句可向表中添加一个或多个新行，其语法格式如下： ［WITH ［,...，n］］ INSERT ［TOP ( expression ) ［PERCENT］］ ［INTO］ { ［server_name. database_name. schema_name.|database_name.［schema_name］. |schema_name.］ table_or_view_name { ［( column_list )］ ［］ { VALUES ( { DEFAULT|NULL|expression } ［, ...，n］ ) |derived_table|execute_statement|DEFAULT VALUES } } } 各选项的含义如下： (1) WITH ： 指定在INSERT语句作用域内定义的临时命名结果集(也称为公用表表达式)。结果集源自SELECT语句。 (2) TOP(expression)［PERCENT］： 指定将插入的随机行的数目或百分比。expression可以是行数或行的百分比。在和INSERT、UPDATE或DELETE语句结合使用的TOP表达式中引用的行不按任何顺序排列。 (3) INTO： 一个可选的关键字，可以将它用在INSERT和目标表之间。 (4) server_name： 表或视图所在服务器的名称。如果指定了server_name，则需要database_name和schema_name。 (5) database_name： 数据库的名称。 (6) schema_name： 表或视图所属架构的名称。 (7) table_or view_name： 要接收数据的表或视图的名称。 (8) (column_list)： 要在其中插入数据的一列或多列的列表。必须用括号将column_list括起来，并且用逗号进行分隔。 (9) OUTPUT子句： 将插入行作为插入操作的一部分返回。引用本地分区视图、分布式分区视图或远程表的DML语句，或包含execute_statement的INSERT语句，都不支持OUTPUT子句。在包含子句的INSERT语句中不支持OUTPUT INTO子句。 (10) VALUES： 引入要插入的数据值的列表。 如果VALUES列表中的各值与表中各列的顺序不相同，或者未包含表中各列的值，则必须使用column_list显式指定存储每个传入值的列。 若要插入多行值，VALUES列表的顺序必须与表中各列的顺序相同，且此列表必须包含与表中各列或column_list对应的值以便显式指定存储每个传入值的列。 (11) DEFAULT： 强制数据库引擎加载为列定义的默认值。若某列不存在默认值，并且该列允许NULL值，则插入NULL。DEFAULT对标识列无效。 (12) expression： 一个常量、变量或表达式。表达式不能包含EXECUTE语句。 (13) derived_table： 任何有效的SELECT语句，它返回将加载到表中的数据行。 (14) execute_statement： 任何有效的EXECUTE语句，它使用SELECT或READTEXT语句返回数据。 (15) DEFAULT VALUES： 强制新行包含为每个列定义的默认值。 【例510】使用简单的INSERT语句。 USE Sales GO INSERT Supplier VALUES ('R001','华科电子有限公司','施宾彬','朝阳路56号','2636565') 本例在例52创建的表Supplier中插入一行。由于省略了列表，默认对表中所有列按顺序填入。 另外，如果将值装载到可变长度数据类型的列时，尾随空格(对varchar/nvarchar是空格，对varbinary是零)将被删除。例如本例中向linkman_name列(varchar(20))填入值“施宾彬”，SQL Server将“施宾彬”填入该列并删除其尾随的空格。 而如果向char/nchar类型的列填入的值长度小于列的定义长度时，SQL Server将对填入值向右填充至定义长度。例如本例中向supplier_id列(char(6))填入R001后，系统将增加两个空格在该字符串右边以达到定义长度6。 【例511】显式指定列列表。 INSERT Sales.dbo.supplier (supplier_id,supplier_name，linkman_name，address，telephone) VALUES ('R00015','华科电子有限公司','施宾彬','朝阳路56号','2636565') 本例与例510的INSERT命令功能完全等同。但在本例中表中各列被显式地列出来。显式指定列列表还可用来插入值少于列个数的数据或插入与列顺序不同的数据。例如： --插入值少于列个数的数据 INSERT Sales.dbo.supplier (supplier_id，supplier_name，telephone) VALUES ('R00016','晨光电子实业公司','4561681') --插入与列顺序不同的数据 INSERT Sales.dbo.Supplier(Supplier_name，telephone，Supplier_id) VALUES ('晨光电子实业公司','4561681','R00016') 【例512】将数据装载到带有标识符的表。 --创建customer2表，该表的customer_id为标识列 CREATE TABLE customer2 (customer_id bigint NOT NULL IDENTITY(0,1), customer_name varchar(50)NOT NULL, linkman_name varchar(20), address varchar(50), telephone char(12)NOT NULL) GO --以下语句为customer2表插入数据 INSERT customer2 VALUES ('东方体育用品公司','刘平','东方市中山路25号','75368025') INSERT customer2(customer_name,linkman_name,address,telephone) VALUES ('北京泛亚实业公司','张卫民','长岭市五一路号','68510231') SET IDENTITY_INSERT Sales.dbo.customer2 ON INSERT customer2 (customer_id,customer_name,linkman_name,address,telephone) VALUES ('2','洞庭强华电器公司','马东','滨海市洞庭大道号','76053331') 本例创建了表customer2，其customer_id被定义为标识列。第1、2个INSERT语句允许系统为新行生成标识值。执行第3个INSERT语句前用SET IDENTITY_INSERT Sales.dbo.customer2 ON语句允许标识列的手动插入，并且将一个显式的值(2)插入到标识列。撤销标识列的手动插入使用语句SET IDENTITY_INSERT。Sales.dbo.customer2 OFF。 【例513】使用SELECT和EXECUTE选项装载数据。 --创建一个新表newcustomer CREATE TABLE Sales.dbo.newcustomer (customerName varchar(50)NOT NULL, linkmanName varchar(20) ) --用INSERT…SELECT从customer2表查询数据填入NewCustomer表 INSERT newcustomer SELECT customer_name,linkman_name FROM customer2 --先创建一个存储过程（详细内容参见第10章），再使用INSERT…EXECUTE语句 --从customer2表用存储过程查询数据填入newcustomer表 CREATE PROCEDURE MySp_Customer AS SELECT customer_name,linkman_name FROM customer2 GO INSERT newcustomer EXECUTE MySp_Customer --用INSERT…EXECUTE('string')从customer2表查询数据填入newcustomer表 INSERT newcustomer EXECUTE('SELECT customer_name,linkman_name FROM customer2' ) 本例演示了3种不同的方法，用来从customer2表获取数据，并将数据填入newcustomer表。每种方法都基于一个SELECT语句，该语句从customer2表查询其中两列的值。 第1个INSERT语句使用SELECT语句直接从源表(customer2)检索获取数据； 第2个INSERT语句执行一个包含SELECT语句的存储过程； 第3个INSERT语句执行以字符串形式表示的SELECT语句。 5.3.2修改数据 在创建表并添加数据之后，更改或更新表中的数据就成为维护数据库的一个日常过程。UPDATE语句可以更改表或视图中单行、行组或所有行的数据值。其语法格式如下： ［WITH ［，...，n］］ UPDATE ［TOP ( expression ) ［PERCENT］］ {{ table_alias|{［server_name. database_name. schema_name.|database_name.［schema_name］. | schema_name.］ table_or_view_name }}| @table_variable} SET { column_name={ expression|DEFAULT|NULL } | { udt_column_name.{ { property_name=expression | field_name=expression }|method_name ( argument ［,...，n］ ) } } | column_name {.WRITE ( expression, @Offset, @Length ) } | @variable=expression | @variable=column=expression } ［,...，n］ ［］ ［FROM{ } ［,...n，］］ ［WHERE { }］ 各选项的含义如下： (1) WITH ： 指定在UPDATE语句作用域内定义的临时命名结果集或视图，也称为公用表表达式(CTE)。CTE结果集派生自简单查询并由UPDATE语句引用。 (2) TOP (expression) ［PERCENT］： 指定将要更新的行数或行百分比。expression可以为行数或行百分比。 (3) table_alias： 在表示要从中更新行的表或视图的FROM子句中指定的别名。 (4) server_name： 表或视图所在服务器的名称(使用链接服务器名称或OPENDATASOURCE函数作为服务器名称)。如果指定了server_name，则需要database_name和schema_name。 (5) database_name： 数据库的名称。 (6) schema_name： 表或视图所属架构的名称。 (7) table_or_view_name： 要更新行的表或视图的名称。 (8) @ table_variable： 将表变量指定为表源。 (9) SET： 指定要更新的列或变量名称的列表。 (10) column_name： 包含要更改的数据的列。column_name必须已存在于table_or_view_name中。不能更新标识列。 (11) expression： 返回单个值的变量、文字值、表达式或嵌套SELECT语句(加括号)。expression返回的值替换column_name或@variable中的现有值。 (12) DEFAULT： 指定用为列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许NULL值，则该参数也可用于将列更改为NULL。 (13) udt_column_name： 用户定义类型列。 (14) property_name |field_name： 用户定义类型的公共属性或公共数据成员。 (15) method_name (argument ［,...，n］)： 带一个或多个参数的udt_column_name的非静态公共赋值函数方法。 (16) WRITE (expression, @Offset, @Length)： 指定修改column_name值的一部分。expression替换@Length单位(从column_name的@Offset开始)。只有varchar(max)、nvarchar(max)或varbinary(max)列才能使用此子句来指定。column_name不能为NULL，也不能由表名或表别名限定。 (17) @ variable： 已声明的变量，该变量将设置为expression所返回的值。 (18) ： 在UPDATE操作中，返回更新后的数据或基于更新后的数据的表达式。针对远程表或视图的任何DML语句都不支持OUTPUT子句。 (19) FROM{}： 指定将表、视图或派生表源用于为更新操作提供条件。 (20) WHERE： 指定条件来限定所更新的行。 (21) ： 为要更新的行指定需满足的条件。 【例514】使用简单的UPDATE语句。 UPDATE customer2 SET linkman_name='佚名', address=NULL, telephone='' 本例将所有客户单位的联系人设置为“佚名”，地址设置为空值，电话号码设置为空字符串(非空值)。 也可以在更新中使用计算值。下面的SQL语句将表salary中的“奖金”加倍。 UPDATEsalary SET奖金=奖金*2 【例515】在UPDATE语句中使用WHERE子句。 UPDATE customer2 SET telephone='0731-'+telephone WHERE LEN(telephone)=8 本例将customer2表中的所有本地电话号码前加上区号。判断本地电话号码的逻辑条件是： 列telephone的字符串长度为8(LEN()函数返回字符串长度)。 【例516】在UPDATE语句中使用来自另一个表的信息。 首先创建sell_order表和goods表(参考表212和表213)并输入适量数据，这一步请读者自己完成。然后执行下列命令。 UPDATE sell_order SET cost= sell_order.order_num*goods.unit_price*(1-sell_order.discount) FROM sell_order,goods WHERE sell_order.goods_id=goods.goods_id 本例修改表sell_order中的cost列，以计算每个销售订单的收费。也可以使用表的别名改写为一个较简单的形式。表sell_order的别名为so，goods的别名为g。 UPDATE sell_order SET cost=so.order_num*g.unit_price*(1-so.discount) FROM sell_order so，goods g WHERE so.goods_id=g.goods_id 【例517】在UPDATE语句中使用SELECT…TOP语句。 UPDATE goods SET unit_price=unit_price*0.9 FROM goods, (SELECT TOP 10 goods_ID, sum(order_Num)AS total_num FROM sell_order GROUP BY goods_ID ORDER BY total_num ASC ) AS total_sum WHERE goods.goods_id=total_sum.goods_id 本例使用SELECT…TOP语句的结果集作为依据对goods表进行更新。SELECT查询返回销售量最少的10件货物的编号。WHERE子句使用查询结果作为搜索条件对goods表进行过滤。SET子句对满足条件的货物的单价进行更改。整个UPDATE语句的实际意义是： 对销售状况最差的10件商品进行降价。 5.3.3删除数据 TransactSQL支持两种删除现有表中数据的语句，分别是DELETE和TRUNCATE TABLE语句。 1. DELETE语句 DELETE语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。DELETE语句的语法格式如下： ［WITH ［,...，n］］ DELETE ［TOP ( expression ) ［PERCENT］］ ［FROM］ {{ table_alias|{ ［server_name.database_name.schema_name. | database_name. ［schema_name］.|schema_name.］ table_or_view_name}}| @table_variable} ［］ ［FROM ［,...，n］］ ［WHERE { }］ 各选项的含义如下： (1) WITH ： 指定在DELETE语句作用域内定义的临时命名结果集，也称为公用表表达式。结果集源自SELECT语句。 (2) TOP(expression)［PERCENT］： 指定将要删除的任意行数或任意行的百分比。expression可以为行数或行的百分比。与INSERT、UPDATE或DELETE一起使用的TOP表达式中被引用行将不按任何顺序排列。 (3) FROM： 可选的关键字，可用在DELETE关键字与目标table_or_view_name之间。 (4) server_name： 表或视图所在服务器的名称。如果指定了server_name，则需要database_name和schema_name。 (5) database_name： 数据库的名称。 (6) schema_name： 该表或视图所属架构的名称。 (7) table_or view_name： 要删除行的表或视图的名称。 (8) ： 将已删除行或基于这些行的表达式作为DELETE操作的一部分返回。 (9) FROM ： 指定附加的FROM子句。这个对DELETE的TransactSQL扩展允许从指定数据，并从第一个FROM子句内的表中删除相应的行。这个扩展指定联接，可在WHERE子句中取代子查询来标识要删除的行。 (10) WHERE： 指定用于限制删除行数的条件。如果没有提供WHERE子句，则DELETE删除表中的所有行。 (11) ： 指定删除行的限定条件。 如果DELETE删除了多行，而在删除的行中有任何一行违反约束(主要是外键约束)或触发器，则将取消该语句，返回错误且不删除任何行。如果DELETE语句执行中出现了表达式算术错误(溢出、被零除或域错误)时，SQL Server将取消批处理中的其余部分并返回错误信息。 【例518】不带参数使用DELETE命令删除所有行。 USE Sales GO DELETE customer2 本例从customer2表中删除所有行。 注意： 将DELETE语句与DROP TABLE语句的功能区分开。 【例519】带WHERE子句的DELETE语句，有条件地删除行。 DELETE FROM sell_order WHERE custom_id='C00006' 本例删除sell_order表中custom_id是C00006的所有行。 【例520】在DELETE中使用连接或子查询。 --使用INNER JOIN进行表连接，然后在表sell_order中删除以"东方市"开头的客户的销售订单 DELETE sell_order FROM sell_order so INNER JOIN customer2 cON so.customer_id=c.customer_id WHERE C.address LIKE'东方市%' --等同于下列命令 DELETE sell_order FROM sell_order so，customer2 c WHERE so.customer_id=c.customer_idAND c.address LIKE'东方市%' --使用嵌套子查询查找以"东方市"开头的客户的customer_id，然后在表sell_order中删除这些 --客户的销售订单 DELETE FROM sell_order WHERE customer_id IN (SELECT customer_id FROM customer2 WHERE address LIKE'东方市%') 本例采用三个语句分别演示了基于联接或子查询从基表中删除记录。三个语句实现同样的功能，即将所有地址(address)以“东方市”开头的客户的销售订单从sell_order表中删除。 2. TRUNCATE TABLE语句 TRUNCATE TABLE语句可一次删除表中的所有行，而不会把每一行的删除操作都记入日志。所以TRUNCATE TABLE语句是一种快速清空表的方法。其语法格式如下： TRUNCATE TABLE ［{ database_name.［schema_name］.|schema_name. }］ table_name 各选项的含义如下： (1) database_name： 数据库的名称。 (2) schema_name： 表所属架构的名称。 (3) table_name： 要删除其全部行的表的名称。 注意： (1) TRUNCATE TABLE语句在功能上与不带WHERE子句的DELETE语句相同，两者均删除表中的全部行。 (2) DELETE语句每次删除一行，并在事务日志中进行一次记录，而TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且在事务日志中只记录页的释放。所以TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。 (3) TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。 (4) TRUNCATE TABLE使对新行标识符列(IDENTITY)所用的计数值重置为该列的种子。如果想保留标识计数值，可改用DELETE。 (5) 对于被外键约束所引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录行删除日志，所以它不能激活触发器。 【例521】使用TRUNCATE TABLE语句清空表。 TRUNCATE TABLE customer2 本例清空customer2表中的所有数据，并使customer_id列(bigint，IDENTITY(0,1))的新行标识重置为种子(整数值0)。 本 章 小 结 本章首先介绍了SQL Server中与表相关的一些知识，包括数据库中的表、数据类型和约束。然后，从表结构的维护出发，介绍了在SQL Server 2012管理平台中创建表、修改表和删除表的操作方式，以及使用TransactSQL语句来完成这些工作的方法。最后，从表中数据的维护出发，介绍了使用TransactSQL语句来对表中数据进行新增、修改和删除的方法。 (1) 表的相关概念： 表是数据库中数据的实际存储位置，一个表代表一个实体。表由行和列组成，每行标识实体的一个个体，每列代表实体的一个属性。 (2) 数据类型： 数据类型描述并约束了列中所能包含的数据的种类、所存储值的长度或大小、数字精度和小数位数(对数值数据类型)。 (3) 空值： 未对列指定值时，该列将出现空值。空值不同于空字符串或数值零，通常表示未知。空值会对查询命令或统计函数产生影响，应尽量少使用空值。 (4) 约束： 约束是数据库自动保持数据完整性的机制，它是通过限制列中数据、行中数据和表之间数据来保持数据完整性。SQL Server 2012支持Not Null、Default、Check、Primary Key、Foreign Key、Unique 6种约束。关于约束的操作将在第8章中详细介绍。 (5) 可以使用SQL Server 2012管理平台和TransactSQL语句创建表并对表进行维护，包括修改和删除等操作。 (6) 可以使用SQL Server 2012管理平台和TransactSQL语句对表中数据进行编辑，包括插入、更新和删除等操作。 习题5 一、 选择题 1. 在定义数据表的字段时，“允许NULL值”用于设置该字段是否可输入空值，实际上就是创建该字段的()约束。 A. 主键B. 外键C. 非空D. CHECK 2. 下列关于主关键字的叙述正确的是()。 A. 一个表可以没有主关键字 B. 只能将一个字段定义为主关键字 C. 如果一个表只有一个记录，则主关键字字段可以为空值 D. 以上选项都正确 3. 使用CREATE TABLE语句创建数据表时()。 A. 必须在数据表名称中指定表所属的数据库 B. 必须指明数据表的所有者 C. 指定的所有者和表名称组合起来在数据库中必须唯一 D. 省略数据表名称时，则自动创建一个本地临时表 4. 下列关于ALTER TABLE语句的叙述错误的是()。 A. ALTER TABLE语句可以添加字段 B. ALTER TABLE语句可以删除字段 C. ALTER TABLE语句可以修改字段名称 D. ALTER TABLE语句可以修改字段数据类型 5. 若要删除数据库中已经存在的表A，则可用()。 A. DELETE TABLE AB. DELETE A C. DROP TABLE AD. DROP A 二、 填空题 1. 整数型的int型数的范围为，整数型的tinyint型数的范围为。 2. 表中某列为变长字符数据类型varchar(100)，其中100表示。假如输入的字符串为gtym13e5，存储的字符长度为字节。 3. SQL Server支持的基本数据类型有字符和二进制数据类型、数据类型、逻辑数据类型、数据类型，用于各类数据值的存储、检索和解释。 4. ALTER TABLE语句不能修改数据表的和。 5. TransactSQL中添加记录使用语句，修改记录使用语句，删除记录可使用或语句。 三、 问答题 1. 简述下列数据类型中每一组之间的区别。 char/varcharchar/nchardecimal/float/money 2. 在Sales数据库的Sell_Order表中cost(费用)列允许为空。当一个NULL值和一个货币类型数据被填入到某两行的cost列后，试比较这两个cost值的大小。 3. 简述以下3条SQL语句的异同。 DROP TABLE Orders DELETE Orders TRUNCATE TABLE Orders 四、 应用题 分别使用一条TransactSQL语句完成下列操作。 (1) 在Sales数据库中创建销售订单表Sell_Order(包含所有列，只含非空约束)，其中销售订单编号(order_id1)为标识符列(1,2)。 (2) 在该表中删除列send_date，增加列“发货日期”。 (3) 往表中插入一行，以记录这个销售事件： 2019年2月26日，编号为99的客户从本公司订购了30件编号为135的货物； 编号为16的员工洽谈了该业务并给予该客户95折优惠。 (4) 往表中插入一行，以记录这个销售事件： 2018年10月10日，编号为6的客户从本公司订购了200件编号为26的货物； 编号为02的员工洽谈了该业务并给予该客户8折优惠。该批货物已于2018年12月1日，由编号为10的运输商承运，客户已于2018年12月12日验收，并付清了费用人民币200000元整。 (5) 因编号为29的员工辞职，将他所有未结账(cost未计算)的销售订单转交编号为15的员工处理。 (6) 因编号为100的客户升级为本公司VIP客户，其所有未结账订单的折扣在原折扣上再进行9折。 (7) 将所有发生于2019年1月1日的销售订单删除。