第4章-创建和管理SQL-Server-2005数据表

第4章

创建和管理SQLServer2005数据表基本内容：表的基本概念；数据表的创建、修改和删除；查看和编辑表中的数据；重点：数据表的创建、修改和删除;编辑表中数据；难点：使用SQL语句创建和修改数据表；要求：理解表的特点和类型，熟练掌握创建和修改表的相关技术。本章目录4.1表的概念4.2数据表的创建4.3数据表的修改4.4添加和修改表数据本章小结4.1表的概念表是关系模型中中表示实体的方式，是数据库存储数据的主要对象。SQLServer数据库的表由行和列组成，行有时也称为记录，列有时也称为字段或域，如图4.1所示。行列订单号客户代号产品号单价数量订单日期10248VINET1114.00202006-07-0510248VINET429.80152006-07-0510249TOM2218.60102006-07-0610250JACK1114.00302006-07-0810250JACK4134.50252006-07-08图4.1表的结构在表中，行的顺序可以是任意的，一般按照数据插入的先后顺序存储。在使用过程中，可以使用排序语句或按照索引对表中的行进行排序。列的顺序也可以是任意的，对于每一个表，最多可以允许用户定义1024列。在同一个表中，列名必须是唯一的，即不能有名称相同的两个或两个以上的列同时存在于一个表中，并且在定义时为每一个列指定一种数据类型。但是，在同一个数据库的不同表中，可以使用相同的列名。表的类型在SQLServer2005系统中把表分成了4种类型，即普通表、临时表、已分区表和系统表。普通表就是通常提到的数据库中存储数据的数据表，是最重要、最基本的表其他几种类型的表都是有特殊用途的表，往往是在特殊应用环境下，为了提高系统的使用效率而派生出来的。临时表，顾名思义是临时创建的、不能永久生存的表。临时表被创建之后，可以一直存储到SQLServer实例断开连接为止。临时表又可以分为本地临时表和全局临时表，本地临时表只对创建者是可见的，全局临时表在创建之后对所有的用户和连接都是可见的。已分区表是将数据水平划分成多个单元的表，这些单元可以分散到数据库中多个文件组里面，实现对单元中数据的并行访问。如果表中的数据量非常庞大，并且这些数据经常被以不同的使用方式来访问，那么建立已分区表是一个有效的选择。系统表储存了有关SQLServer服务器的配置、数据库配置、用户和表对象的描述等系统信息。一般来说，只能由DBA来使用系统表。4.2数据表的创建

4.2.1在图形界面下创建数据表

4.2.2用SQL命令创建数据表

4.2.1在图形界面下创建数据表表的有关操作都是在MicrosoftSQLServer2005的MicrosoftSQLServerManagementStudio中完成的。在MicrosoftSQLServer2005系统中，可以使用ManagementStudio完成创建表、修改表结构、查看表属性信息等操作。本节将以“学生选课”数据库中Student表的创建为例，介绍如何在图形界面下完成数据表的创建。在MicrosoftSQLServerManagementStudio中，创建一个名为“学生选课”的数据库。右键单击该数据库中的“表”节点，在弹出菜单中选择“新建表”，打开表设计器，如图4.2所示。图4.2使用表设计器创建表在图4.2的结构中修改表属性，将表名“Table_1”改为“Student”，并输入表中各列的名称、数据类型、是否为空等信息。选中任意一列，在窗体的下半部分将显示该列的主要属性信息。如图4.3所示。图4.3建立Student表结构SQLServer的主要列属性1．允许空（NULL或NOTNULL）该属性定义在输入数据时指定列是否可以为空。在SQLServer中，列的默认属性为“允许空”，例如，Student表中的Ssex列和Sage列。当某列不允许为空时，只需要在该列的“允许空”选项中取消复选框就行了，如Sno列。2．标识规范（IDENTITY）很多表中使用编号列来标识表中的记录。在插入数据时，用户并不需要指定编号列的值，只要他们互不相同就行了。在这种情况下，可以将编号列设置为标识列，设置成功后，每插入一条记录，系统都会根据增量值自动为该列生成新数据。定义标识列需要指定两个值：种子值和增量值。这样，表中第一行记录的IDENTITY列的值就是种子值，其他行的IDENTITY列的值是在前一行值的基础上增加一个增量值得到的。通常将标识列的类型定义为int或bigint。例如，在上面创建学生表Student时，如果将学号列“Sno”定义为int型，那么就可以通过更改窗口下方的列属性将其设置为标识列，然后由自动生成该列的值。如图4.4所示。SQLServer的主要列属性由于IDENTITY属性列的增长是单方向的，所以一般情况下不能手工为设置了IDENTITY属性的列添加数据。而且，如果删除了这些列中的部分数据，还会造成标识符序列空缺——已删除的标识符值是不能重用的，系统不会自动补充这部分数据值。解决办法：使用SETIDENTITY_INSERT语句将标识列设置为可以插入数据的状态。该语句的基本格式为：

SETIDENTITY_INSERT<表名>{ON|OFF}

其中，ON表示可以插入数据，OFF表示拒绝插入数据。

SQLServer的主要列属性3．主键（PRIMARYKEY）主键是表中的一列或者一组列，它的值可以惟一标识表中的每一行记录。例如，每一个学生入学后都有一个学号，而且该学号和其他任何一个学生都是不同的，或者说，确定了一个学号就确定了一个学生。因此，在设计表时，可以把学号定义为主键。4.2.2用SQL命令创建数据表基本语法如下：

CREATETABLE表名

(

列名1数据类型和长度1列属性1,

列名2数据类型和长度2列属性2, …………

列名n数据类型和长度n列属性n )在CREATETABLE语句中需要指出的元素与在表设计器中相同，包括表名、列名、列的数据类型以及列属性等。在SQLServerManagementStudio中，单击工具栏上的“新建查询”，在查询窗口中输入下面的脚本命令，可以创建4.2.1节中定义的Student表。

USE学生选课

GO CREATETABLEStudent (

Sno

int PRIMARYKEYIDENTITY(20050301,1),

Sname varchar(20) NOTNULL,

Ssex char(2), Sage smallint,

Sdept varchar(20) )其中，USE语句表示选择数据库；PRIMARYKEY属性定义Sno字段为主键；IDENTITY(20050301,1)属性定义Sno列的第一行记录值为20050301，以后每一行的Sno列值在此基础上依次递增，增量为1；NOTNULL表示不允许Sname列为空。在创建表的过程中，除了在列中直接指定数据类型和属性之外，还可以对某些列进行计算。例如，使用计算列创建学生成绩表Sgrade可用下面语句实现：

CREATETABLESgrade (

Sno

int, Grade1 int, Grade2 int, Grade3 int, TotalASGrade1+Grade2+grade3 )注意:一般情况下，计算列的数据并不进行物理存储，它仅仅是一个虚拟列，只能用于显示。如果希望将该列的数据物理化存储，可以使用PERSISTED关键字。

4.3数据表的修改4.3.1查看表属性4.3.2修改表结构4.3.3删除数据表4.3.1查看表属性在SQLServerManagementStudio中，选中要查看的数据表，单击鼠标右键，选择“属性”，将打开“表属性”对话框，如图4.7所示。图4.7通过“表属性”对话框查看表属性还可以通过sp_help存储过程来查看表结构信息。如图4.8所示。图4.8使用存储过程查看表结构信息4.3.2修改表结构1、在图形方式下修改表结构在SQLServerManagementStudio中，选中要查看的数据表，单击右键，在弹出菜单中选择“修改”，打开表设计器即可修改表结构。2、使用ALTERTABLE语句修改表结构添加列修改列属性删除列修改列名和表名添加列基本语法：

ALTERTABLE表名ADD列名数据类型和长度列属性【例4.1】在表Student中新增一列“入学年月”，列名为Sentrance，数据类型为datetime，允许空值。执行以下语句：ALTERTABLEStudentADDSentrance

datetime注意：当向表中新增一列时，最好为该列定义一个默认约束，使该列有一个默认值。这一点可以使用关键字DEFAULT来实现；如果增加的新列没有设置默认值，并且表中已经有了其他数据，那么必须指定该列允许空值，否则，系统将产生错误信息。修改列属性基本语法：ALTERTABLE表名ALTERCOLUMN列名新数据类型和长度新列属性例如，【例4.1】中创建的Sentrance列是datetime类型，并且不允许为空。现在要将该列改为smalldatetime类型，并且允许为空。

SQL语句如下：ALTERTABLEStudentALTERCOLUMNSentrance

smalldatetimeNULL

删除列基本语法：

ALTERTABLE表名DROPCOLUMN列名例如，要删除Student表中的Sentrance列，可以执行下面的SQL语句。

ALTERTABLEStudentDROPCOLUMNSentrance修改列名和表名可以使用sp_rename存储过程对表和表中的列进行重命名，重命名的基本语法为：sp_rename

原对象名,新对象名例如，如果想将Student表改名为StudentInfo，可以执行以下SQL语句：

sp_renameStudent,StudentInfo4.3.3删除数据表删除表就是将表中的数据和表的结构从数据库中永久性的移除。也就是说，表一旦被删除，就无法恢复，除非还原数据库。因此，执行此操作时应该慎重。在SQLServerManagementStudio中，选中要查看的数据表，单击右键，在弹出菜单中选择“删除”，将弹出“删除对象”对话框，单击“确定”按钮，选中的表就从数据库中被删除了。也可以使用DROPTABLE语句来完成数据表的删除。DROPTABLE语句的语法：

DROPTABLE表名【例4.2】删除“学生选课”数据库中的exam表。USE学生选课GODROPTABLEexam在使用DROPTABLE语句删除数据表时，需要注意以下几点：DROPTABLE语句不能删除系统表；DROPTABLE语句不能删除正被其他表中的外键约束参考的表。当需要删除这种有外键约束参考的表时，必须先删除外键约束，然后才能删除该表；当删除表时，属于该表的约束和触发器也会自动被删除。如果重新创建该表，必须重新创建相应的规则、约束和触发器等；DROPTABLE语句可以一次性删除多个表，表之间用逗号分开。4.4添加和修改表数据4.4.1手工添加表数据4.4.2查看表记录4.4.3用INSERT语句插入数据4.4.4用UPDATE语句更新数据4.4.5用DELETE语句删除数据表创建以后，往往只是一个没有数据的空表。因此，向表中出入数据可能是创建表之后首先要执行的操作。无论表中是否有数据，都可以根据需要向表中添加数据。当表中的数据不合适或者出现了错误时，可以更新表中的数据。如果表中的数据不再需要了，则可以删除这些数据。4.4.1手工添加表数据在SQLServerManagementStudio中，选中要查看的数据表，单击右键，在弹出菜单中选择“打开表”，可以打开查询表数据的窗口，该窗口显示了表中已经存储的数据，数据列表的最后有一个空行。如图4.11所示。插入数据时，将光标定位在空白行某个字段的编辑框中，就可以输入新数据。编辑完成后，选中其他行，即可完成数据的插入。图4.11编辑表数据注意：在编辑表中数据的过程中，有些读者可能会遇到这样一个问题，就是无法输入中文。这种情况出现在表的第一列为标识列、并且打开表时表中已经存在数据，此时，光标定位在第一条记录的标识列中，再将光标定位到其他的编辑框，将无法输入中文。解决方法是在定义表结构时，不要将标识列定义为表的第一列，或者使用SQL命令进行数据的插入。4.4.2查看表记录在SQLServerManagementStudio中，选中要查看的数据表，单击右键，在弹出菜单中选择“打开表”，可以打开查询表数据的窗口，就可以查看该表中现已存储的数据。另外，用户还还可在查询窗口中使用SELECT命令查看一个或多个表中的数据。4.4.3用INSERT语句插入数据INSERT语句的基本语法：INSERTINTO表名(列名1,列名2,……,列名n)VALUES(值1,值2,……,值n)INSERT子句指定要插入的数据表名，并且可以同时指定表的列名称。VALUES子句指定要插入的数据。例：

USE学生选课GOINSERTINTOStudent(Sname,Ssex,Sage,Sdep)VALUES('曾玉林','男',20,'CS')使用INSERT插入数据时注意几点：要插入的数据表必须已经存在。要插入数据的表结构必须和SELECT语句的结果集兼容，也就是说，二者的列的数量和顺序必须相同、列的数据类型必须兼容等。当完全按照表中列的存储顺序来安排VALUES子句中的值时，可以在INSERTINTO子句中省略列名。向表中插入数据时，数字数据可以直接插入，但是字符数据和日期数据要用英文单引号引起来，不然就会提示系统错误。一般情况下，使用INSERT语句一次只能插入一行数据。但是如果在INSERT语句中包含了SELECT语句，就可以一次插入多行数据了。使用SELECT语句插入数据的基本语法形式为：

INSERTINTO表名（列名1,列名2,……,列名n）

SELECT语句可以使用UPDATE语句更新表中已经存在的数据，该语句既可以一次更新一行数据，也可以一次更新多行数据，甚至可以一次更新表中的全部数据行。UPDATE语句基本语法：UPDATE表名SET列名1＝值1,列名2＝值2,……,列名n＝值nWHERE更新条件表达式当执行UPDATE语句时，如果使用了WHERE子句，则指定表中所有满足WHERE子句条件的行都将被更新，如果没有指定WHERE子句，则表中所有的行都将被更新。4.4.4用UPDATE语句更新数据【例4.3】将学生表Student中“刘尘”所属的学院由CS改为SS。USE学生选课GOUPDATEStudentSETSdep='SS'WHERESname='刘尘'执行结果如图4.15所示，有一行记录被更新。图4.15更新数据表数据更新数据时，每个列既可以被直接赋值，如例4.3，也可以通过计算得到新值。【例4.4】将所有学生的年龄增加1岁。USE学生选课GOUPDATEStudentSETSage=Sage+14.4.5用DELETE语句删除数据当表中的数据不再需要的时候，可以将其删除。一般情况下，可以使用DELETE语句删除表中的数据。该语句可以从一个表中删除一行或多行数据。使用DELETE语句删除数据的基本语法形式如下：DELETEFROM表名WHERE条件表达式在DELETE语句中，如果使用了WHERE子句，表示从指定的表中删除满足