实验3数据表的基本操作.docx

实验3数据表的基本操作.docx

《实验3数据表的基本操作.docx》由会员分享，可在线阅读，更多相关《实验3数据表的基本操作.docx（27页珍藏版）》请在冰豆网上搜索。

实验3数据表的基本操作

实验3数据表的基本操作

实验目的

●掌握数据表的创建、修改和删除操作

●学会使用企业管理器和Transact-SQL语句操作数据表

背景知识

在使用数据库的过程中，接触最多的就是数据库中的表。

表是数据存储的地方，是数据库中最重要的部分，管理好表也就管理好了数据库。

表是由行和列组成的。

创建表的过程主要就是定义表的列的过程。

表的列名在同一个表中具有唯一性，同一列的数据属于同一种数据类型。

除了用列名和数据类型来指定列的属性外，还可以定义其它属性：

是否为空、默认值、标识符列、全局唯一标识符列等。

约束是SQLServer提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。

在SQLServer中有5种约束：

主关键字约束（PrimaryKeyConstraint）、外关键字约束（ForeignKeyConstraint）、惟一性约束（UniqueConstraint）、检查约束（CheckConstraint）和默认约束（DefaultConstraint）。

实验示例

1.创建数据表

一、使用企业管理器创建数据表

在myDB数据库中新建一个数据表myTable，演示各种数据类型列以及含有空值列、默认值列、标识符列、全局唯一标识符列属性的数据类型列。

注：

如果没有myDB数据库，就参考实验2创建一个myDB数据库。

在企业管理器中，展开数据库“myDB”节点，右键单击“表”对象，从弹出的快捷菜单中选择“新建表”命令，如图3-1所示，就会出现新建表窗口，在新建表窗口中可以定义列名称、列定义、长度、精度、小数位数、是否允许为空、默认值、标识符列等。

图3-1选择“新建表”命令

下图定义一个整型列ColInt，该列不能为空，默认值为0，如图3-2所示。

每当在表中为该列插入带空值的行时，显示该列的默认值。

图3-2定义整型列ColInt

下图定义一个日期型列ColDateTime，该列不能为空，且默认值为函数getdate（），如图3-3所示。

图3-3定义日期型列ColDateTime

下图定义一个标识符属性列ColInd，其初值为1，增量也为1，如图3-4所示。

注意标识符列必须以decimal、int、numeric、smallint、bigint或tinyint数据类型定义。

在定义标识符列时，必须指定种子和增量值（其默认值均为1）。

图3-4定义标识符列ColInd

下图定义一个全局唯一标识符属性列ColRow，如图3-5所示。

注意一个表最多只能有一列定义为是RowGuid列，且该列必须定义为uniqueidentifier数据类型。

如果应用程序需要生成在整个数据库或世界各地所有网络计算机的全部数据库中均为唯一的标识符列，使用全局唯一标识符列属性。

图3-5定义全局唯一标识符列ColRow

按照类似的方法，设计好数据表后，单击工具栏上的“保存”按钮

，由于是新建数据库表，弹出“选择名称”对话框，如图3-6所示。

输入表名“myTable”，然后按“确定”，即可保存。

这样数据表myTable就创建好了。

图3-6设置数据表名

在企业管理器中，可以生成指定数据表的Transact-SQL语句，以便了学习Transact-SQL语言。

在企业管理器中，右键单击myTable表，在弹出的快捷菜单中选择“生成SQL脚本”命令，如图3-7所示。

图3-7选择“生成SQL脚本”命令

弹出“生成SQL脚本”对话框，如图3-8所示。

图3-8“生成SQL脚本”对话框

按下“预览”按钮，自动生成了数据表myTable的SQL语句，如图3-9所示。

图3-9生成SQL脚本预览

二、使用Transact-SQL语句创建数据表

例如：

利用Transact-SQL语句创建上面用企业管理器创建的数据表，但表名为myTable2。

在查询分析器中输入SQL命令，如图3-10所示。

图3-10使用Transact-SQL语句创建数据表

另外，可以将查询分析器中的内容保存到一个SQL文件，便于以后使用。

单击工具栏“保存”按钮，如图3-11所示。

图3-11保存SQL语句

弹出“保存查询”对话框，在该对话框中指定文件名路径及文件名，如图3-12所示。

图3-12指定文件路径和文件名

按下“保存”按钮，即可保存当前内容到一个指定的SQL文件中。

例如：

使用Transact-SQL语句在数据库DBS中创建学生成绩管理系统数据表，包括三个数据表：

课程数据表Course、学生数据表Student、学生成绩数据表SC。

具体的SQL命令如图3-13所示。

图3-13使用Transact-SQL语句创建数据表

注：

上面这个建立学生成绩管理系统的命令集可以保存到一个SQL文件中（如：

CreateTable.sql），便于以后使用。

2.创建带约束的数据表

一、主关键字约束

主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。

每个表中只能有一个主关键字约束，也不允许指定主关键字列有NULL属性。

1．使用企业管理器指定主关键字约束

在企业管理器中，用鼠标右键单击要操作的数据表，从弹出的快捷菜单中选择“设计表”命令，弹出“设计表”对话框。

在该对话框中，选择要设置为主键的字段（如果需要选定多个字段，在按住Ctrl键的同时，用鼠标单击每个要选的字段），然后用鼠标右键单击选中的字段，从弹出的快捷菜单中选择“设置主键”命令，或者单击工具栏上的“设置主键”按钮。

例如：

设置Student数据表的Sno为主关键字约束。

具体操作如图3-14所示。

图3-14指定单个字段为主关键字

例如：

设置SC数据表的Sno和Cno为主关键字约束。

具体操作如图3-15所示。

图3-15指定多个字段为主关键字

2．使用Transact-SQL语句指定主关键字约束

使用Transact-SQL语句设置主关键字约束的命令是CONSTRAINTconstraint_namePRIMARYKEY。

图3-16演示了利用Transact-SQL语句来设置单个字段或多个字段为主关键字约束。

图3-16使用Transact-SQL语句指定主关键字约束

二、外关键字约束

外关键字约束定义了表之间的关系，当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字，并设定它适合哪个表中哪些列相关联。

外关键字约束的作用还体现在，当向含有外关键字的表插入数据时，如果与之相关联的表的列中无与插入的外关键字列值相同的值时，系统会拒绝插入数据。

1．使用企业管理器指定外关键字约束

例如：

设置SC数据表的Sno是一个外关键字，参考Student表中的Sno；设置SC数据表的Cno是一个外关键字，参考Course表中的Cno。

在企业管理器中，打开数据表SC的数据结构，如图3-17所示。

图3-17选择“设计表”命令

数据表SC的数据结构如图3-18所示，单击工具栏上的“管理关系”按钮。

图3-18SC表的结构

弹出“属性”对话框，选择“关系”选项卡，按照图3-19所示方法设置SC数据表的Sno是一个外关键字，参考Student表中的Sno。

图3-19设置SC表的外关键字Sno

同样的方法，图3-20表示设置SC数据表的Cno是一个外关键字，参考Course表中的Cno。

图3-20设置SC表的外关键字Cno

2．使用Transact-SQL语句指定外关键字约束

例如：

利用Transact-SQL语句设置SC数据表的Sno是一个外关键字，参考Student表中的Sno；设置SC数据表的Cno是一个外关键字，参考Course表中的Cno。

具体的SQL命令如图3-21所示。

图3-21使用Transact-SQL语句指定外关键字约束

三、唯一性约束

可使用UNIQUE约束确保在非主键列或允许为空值的列中不输入重复值。

尽管UNIQUE约束和PRIMARYKEY约束都强制唯一性，但对于非主键列或允许为空值的列应使用UNIQUE约束而不是PRIMARYKEY约束。

1．使用企业管理器指定唯一性约束

在企业管理器中，用鼠标右键单击要操作的数据表，从弹出的快捷菜单中选择“设计表”命令，弹出“设计表”对话框。

单击工具栏上的“管理索引/键”按钮，弹出“属性”对话框，选择“索引/键”选项卡，单击该选项卡中的“新建”按钮，在“列名”栏中选择要将唯一约束附加的列，然后选中复选框“创建UNIQUE”，选择“约束”选项，单击“关闭”按钮即可。

例如：

设置Student数据表的Sname是一个满足唯一性约束的列。

具体操作如图3-22所示。

图3-22指定唯一性约束

2．使用Transact-SQL语句指定唯一性约束

例如：

使用Transact-SQL语句设置Student数据表的Sname是一个满足唯一性约束的列。

具体的SQL命令如图3-23所示。

图3-23使用Transact-SQL语句指定唯一性约束

四、检查约束

检查约束CHECK对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。

可以对每个列设置检查条件。

1．使用企业管理器指定检查约束

在企业管理器中，用鼠标右键单击要操作的数据表，从弹出的快捷菜单中选择“设计表”命令，弹出“设计表”对话框。

单击工具栏上的“管理约束”按钮，弹出“属性”对话框，选择“CHECK约束”选项卡，单击该选项卡中的“新建”按钮，在“约束表达式”文本框中输入检查约束的表达式，输入完毕后，在“约束名”文本框中输入约束的名称，然后单击“关闭”按钮即可。

例如：

设置Student数据表中的Sage的值必须在0-100之间。

具体操作如图3-24所示。

图3-24指定检查约束

2．使用Transact-SQL语句指定检查约束

例如：

使用Transact-SQL语句设置Student数据表中的Sage的值必须在0-100之间，Ssex的值只能是“男”或“女”。

具体的SQL命令如图3-25所示。

图3-25使用Transact-SQL语句指定检查约束

五、默认约束

默认约束DEFAULT通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。

1．使用企业管理器指定默认约束

在企业管理器中，用鼠标右键单击要操作的数据表，从弹出的快捷菜单中选择“设计表”命令，弹出“设计表”对话框。

选定要设置默认约束的字段后，在默认值栏中输入该字段的默认值，即可创建默认约束。

如图3-2或3-3所示。

2．使用Transact-SQL语句指定默认约束

方法1：

在CREATETABLE中使用DEFAULT关键字创建默认定义，将常量表达式指派为列的默认值。

这是首选的标准方法，也是定义默认值的更简明的方法。

方法2：

使用CREATEDEFAULT语句创建默认对象，然后使用sp_binddefault系统存储过程将它绑定到列上。

3.修改数据表

当数据表创建好后，可以根据需要对表的列、约束等属性进行添加、删除及修改，这就需要修改数据表的结构。

一、使用企业管理器修改数据库表

若要修改某数据表，必须首先进行数据表的结构设计界面，然后就与新建数据表一样，可以增加、删除数据列，修改某数据列的数据类型、长度等属性，还可以修改数据表的主键、外键、索引、唯一性约束、检查约束等。

例如：

要修改学生数据表Student。

在企业管理器中，打开Student表的“设计表”对话框，如图3-26所示，按图中所示的方法来修改Student表结构。

图3-26修改数据表

二、使用Transact-SQL语句修改数据库表

使用Transact-SQL语句修改数据库表在实际工作中往往意义不大，实际工作中往往不如首先删除要修改的数据表，然后重新创建数据表。

例如：

向学生数据表Student中增加一个数据列住址Saddr，字符型20。

具体的SQL命令如图3-27所示。

图3-27使用Transact-SQL语句添加字段

例如：

给学生数据表Student中系号增加一个检查约束，检查其字符数必须为2。

具体的SQL命令如图3-28所示。

图3-28使用Transact-SQL语句添加检查约束

注：

在计算字符串长度时，一个ASCII字符占一个长度，而一个汉字也只占一个长度，这点与其它系统有点不一样，如VC、VB、Delphi中，一个汉字都是占二个长度。

例如：

删除学生数据表Student中住址列。

具体的SQL命令如图3-29所示。

图3-29使用Transact-SQL语句删除字段

4.更新数据库表的数据

一、向表中插入数据

1．使用企业管理器向表中插入数据

在企业管理器中，选择需要添加数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，再选择“返回所有行”命令，这时将显示该表的数据，可以在空行处输入要添加的数据。

例如：

向学生数据表Student插入数据。

如图3-30所示，选择“返回所有行”命令以显示Student表的数据。

图3-30选择“返回所有行”命令

图3-31所示的是插入数据后的情况。

图3-31向表中纯插入数据

2．使用Transact-SQL语句向表中插入数据

表中插入数据的Transact-SQL语句是INSERT。

INSERT命令可以向表中一次追加一条记录，也可以从另外的表或查询结果中追加数据。

例如：

向学生数据表Student插入数据，其数据同上。

具体的SQL命令如图3-32所示。

图3-32使用Transact-SQL语句向表中插入数据

例如：

将另一个学生数据表Student0中男生的数据插入到学生数据表Student中。

具体的SQL命令如图3-33所示。

图3-33使用Transact-SQL语句向表中插入数据

二、删除表中的数据

1．使用企业管理器删除表中的数据

在企业管理器中，选择需要删除数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，再选择“返回所有行”命令，这时将显示该表的数据，此时选中要删除的记录，单击鼠标右键，从弹出的快捷菜单中选择“删除”命令，即可删除该记录。

例如：

删除学生数据表Student中女同学数据。

具体操作如图3-34所示。

图3-34删除表中的数据

2．使用Transact-SQL语句删除表中的数据

删除表中数据的Transact-SQL语句是DELETE。

该命令可以与WHERE字句配合使用，用于删除符合指定条件的记录。

例如：

删除学生数据表Student中女同学数据。

具体的SQL命令如图3-35所示。

图3-35使用Transact-SQL语句删除表中的数据

例2：

删除学生数据表Student的全部数据。

具体的SQL命令如图3-36所示。

图3-36使用Transact-SQL语句删除表中的数据

说明：

使用TRUNCATETABLE也能清空表中的数据，如TRUNCATETABLEStudent。

如TRUNCATETABLE语句可以删除表中的所有数据，只留下一个表格的定义。

使用TRUNCATETABLE语句执行操作通常要比使用DELETE语句快，因为TRUNCATETABLE是不记录日志的操作，TRUNCATETABLE将释放表的数据和索引所占据的所有空间。

三、修改表中的数据

1．使用企业管理器修改表中的数据

在企业管理器中，选择需要删除数据的表，用鼠标右键单击该表，从弹出的快捷菜单中选择“打开表”命令，再选择“返回所有行”命令，这时将显示该表的数据，此时可对表中的相关记录进行修改。

2．使用Transact-SQL语句修改表中的数据

修改表中数据的Transact-SQL语句是UPDATE。

该命令可以与WHERE字句配合使用，用于修改符合指定条件的记录。

例如：

将选课表SC中男同学的成绩增加5%（使用子查询）。

具体的SQL命令如图3-37所示。

图3-37使用Transact-SQL语句修改表中的数据

5.删除数据库表

在删除数据表时，必须首先删除与此表有外关键字约束的表。

如：

学生数据表Student的Sno是学生成绩表SC的外关键字，删除Student之前，必须首先删除SC。

一、使用企业管理器删除数据表

在企业管理器中，用鼠标右键单击要删除的表，从弹出的快捷菜单中选择“删除”命令，则会出现“除去对象”对话框，单击“全部除去”按钮，即可删除表。

单击“显示相关性”按钮，则会出现“相关性”对话框，该对话框列出了该对象所依赖的对象和依赖于该表的对象，当有对象依赖于该表时，该表就不能被删除。

例如：

删除学生成绩数据表SC。

具体操作如图3-38所示。

图3-38选择“删除”命令

弹出“除去对象”对象框，按下“全部除去”即可删除指定的数据表。

如图3-39所示。

图3-39全部除去SC表

二、使用Transact-SQL语句删除数据表

使用DROP命令删除数据表。

例如：

删除学生成绩数据表SC的Transact-SQL语句是：

DROPTABLESC