创建与管理表课件

创建与管理表

4.1创建表

表的创建是使用表的前提。表定义为列的集合，数据在表中是按行和列的组织形式排列的，每一行代表惟一的一条记录，每一列则代表记录中的一个域。在SQLServer2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。

在SQLServer2000中，可以使用企业管理器和使用CREATETABLE语句两种方法创建表。

4.1.1使用企业管理器创建表

【例4-1】使用企业管理器创建“系部表”。

①在控制台树中展开XSCJ数据库。

②右击【表】，在弹出的菜单中单击【新建表】命令，出现如图4-1所示的对话框。图4-1【新建表】对话框在该对话框中，需要对表的内容进行定义，其具体内容为：l输入列名。列名在表中必须惟一，列的命名必须遵守SQLServer数据库对象的命名规则。l选择正确的数据类型、数据长度和精度，有关数据类型的相关知识可以参见2.1.3Transact-SQL语法元素一节的内容。l规定该列的数据可否为空。l设置表的主键和索引。创建主键的方法非常简单，首先选中要建立主键的列，单击工具栏的【设置主键】按钮即可。建立为主键的列旁会出现一个小钥匙形的标志。

③本例在【列名】中输入“系部编号”，【数据类型】框中选择“char”，在【长度】中输入长度“2”，不允许为空。④继续设置列，在【列名】中输入“系部名称”，【数据类型】框中选择“char”，在【长度】中输入长度“16”，不允许为空。

⑤设置完成后，如图4-2所示。图4-2使用企业管理器创建“系部表”⑥单击企业管理器工具栏上的【保存】按钮，出现如图4-3所示的对话框，输入表名“系部表”，最后单击【确定】按钮完成表的创建。图4-3为创建的表命名【例4-2】使用企业管理器创建“学生基本信息表”。

①在控制台树中展开XSCJ数据库。

②右击【表】，在弹出的菜单中单击【新建表】命令。③本例在【列名】中输入“学号”，【数据类型】框中选择“char”，在【长度】中输入长度“8”，不允许为空。

④继续设置列，在【列名】中输入“姓名”，【数据类型】框中选择“char”，在【长度】中输入长度“12”，不允许为空。

⑤继续设置列，在【列名】中输入“性别”，【数据类型】框中选择“char”，在【长度】中输入长度“2”，不允许为空。⑥继续设置列，在【列名】中输入“族别”，【数据类型】框中选择“char”，在【长度】中输入长度“8”，不允许为空。

⑦继续设置列，在【列名】中输入“出生日期”，【数据类型】框中选择“datetime”，在【长度】中输入长度“8”，不允许为空。

⑧继续设置列，在【列名】中输入“政治面貌”，【数据类型】框中选择“char”，在【长度】中输入长度“8”，不允许为空。⑨继续设置其他列，完成后的表结构如图4-4所示。

⑩单击企业管理器工具栏上的【保存】按钮，输入表名“学生基本信息表”，最后单击【确定】按钮完成表的创建。图4-4使用企业管理器创建“学生基本信息表”4.1.2使用CREATETABLE语句创建表

其语法形式如下：

CREATETABLE

[数据库名.[拥有者名].]表名

(

列名列的属性

[，…n])

）

其中：列的属性包括列的数据类型、列的长度、列上的约束等。【例4-3】使用SQL语句创建“班级表”、“课程信息表”、“成绩表”。

在查询分析器中运行如下命令：USEXSCJ

GO

CREATETABLE班级表

(

班级编号char(8)NOTNULL,

系部编号char(2)NOTNULL,

班级名称char(16)NOTNULL

)

GO

CREATETABLE课程信息表(

课程编号char(4)NOTNULL,

课程名称char(30)NOTNULL,

学分decimal(5)NOTNULL,

学时decimal(5)NOTNULL,

考核类型char(6)NOTNULL,

任课教师char(12)NOTNULL,

系部编号char(2)NOTNULL,

上课时间char(40)NOTNULL

)GO

CREATETABLE成绩表

(

学号char(8)NOTNULL,

课程编号char(4)NOTNULL,

成绩decimal(9)NOTNULL

)

GO4.2管理表

4.2.1修改表结构

1．使用企业管理器修改表结构

使用企业管理器可以修改表的定义，包括添加、删除列，更改列的名称、类型和长度。也可以添加、删除、修改约束，启用或禁用约束和触发器等。具体操作时只需右击要修改的表，在弹出的菜单中选择【设计表】命令即可。【例4-4】使用企业管理器修改XSCJ数据库中的“课程信息表”，将“课程编号”列设置为主键。

①在控制台树中展开XSCJ数据库，单击【表】。

②在详细列表中右击【课程信息表】，在弹出的菜单中单击【设计表】命令。

③选中要建立主键的列“课程编号”，单击企业管理器工具栏的【设置主键】按钮即可。建立为主键的列旁即出现一个小钥匙形的标志。

④单击企业管理器工具栏上的【保存】按钮完成表的修改。2．使用ALTERTABLE语句修改表结构

（1）修改表设置主键

基本语法如下：

ALTERTABLE表名

ADDCONSTRAINT约束名称

PRIMARYKEYCLUSTERED

(

列名[,…]

)其中：

ADDCONSTRAINT表示增加约束。

PRIMARYKEY表示主键。

CLUSTERED表示聚集索引，一般主键为聚集索引。

【例4-5】使用SQL语句把“课程信息表”的“课程编号”列设置为主键“PX_课程信息表”，再将其删除。

在查询分析器中运行如下命令即可：

USEXSCJ

GO

ALTERTABLE课程信息表

ADDCONSTRAINTPX_课程信息表

PRIMARYKEYCLUSTERED

(课程编号)

GO（2）添加列

向表中增加一列时，应使新增加的列有默认值或允许为空值，SQLServer将向表中已存在的行填充新增列的默认值或空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQLServer不知道该怎么处理那些已经存在的行。

向表中添加列的语句格式如下：

ALTERTABLE表名

ADD列名列的描述【例4-6】向“学生基本信息表”中添加“Email：”列。

在查询分析器中运行如下命令：

USEXSCJ

GO

ALTERTABLE学生基本信息表

ADDEmail：varchar(20)NULL

GO（3）删除列

删除一列的语句格式为：

ALTERTABLE表名

DROP列名【例4-7】将“学生基本信息表”中添加的“Email：”列删除。

在查询分析器中运行如下命令：

USEXSCJ

GO

ALTERTABLE学生基本信息表

DROPCOLUMNEmail：

GO（4）修改列定义

表中的每一列都有其定义，包括列名、数据类型、数据长度以及是否允许为空值等，这些值都可以在表创建好以后修改。

修改列定义的语句格式为：

ALTERTABLE表名

ALTERCOLUMN列名列的描述【例4-8】将“学生基本信息表”中的“姓名”列改为最大长度为20的varchar型数据，且不允许空值。

在查询分析器中运行如下命令：

USEXSCJ

GO

ALTERTABLE学生基本信息表

ALTERCOLUMN姓名varchar(20)NOTNULL

GO

默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须在以下两个条件满足时才能成功：

①列中没有存放是空值的记录

②在列上没有创建索引

4.2.2表的更名

1．使用企业管理器重新命名表

【例4-9】使用企业管理器将XSCJ数据库中的“成绩表”更名为“学生成绩表”。

①在控制台树中展开XSCJ数据库，单击【表】。

②在详细列表中右击“成绩表”，在弹出的菜单中选择【重命名】命令。

③输入“学生成绩表”。

④单击【是】按钮确认。

⑤单击【确定】按钮完成重命名。

2．使用sp_rename存储过程重新命名表

【例4-10】使用SQL命令将XSCJ数据库中的“学生成绩表”重新更名为“成绩表”。

在查询分析器中运行如下命令：

USEXSCJ

GO

EXECsp_rename‘学生成绩表’,’成绩表’

GO

4.2.3表的删除

1.使用企业管理器删除表

【例4-11】使用企业管理器删除XSCJ数据库中的“课程信息表”。

①在控制台树中展开XSCJ数据库，单击【表】。

②在详细列表中右击“课程信息表”，在弹出的菜单中选择【删除】命令。

③单击【全部删除】按钮，完成删除。

2.利用DROPTABLE语句删除表

DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。DROPTABLE语句的语法形式如下：

DROPTABLE表名

【例4-12】使用SQL语句删除XSCJ数据库中的“学生基本信息表”。

在查询分析器中运行如下命令：

USEXSCJ

GO

DROPTABLE学生基本信息表

GO

注：为保证本书的连贯性，删除后应该原样恢复。4.3表的数据操作

4.3.1向表中插入数据

1．使用INSERT语句

基本语法为：

INSERT[INTO]表名

[(列名)]

VALUES(表达式)

（1）添加数据到一行中的所有列

当将数据添加到一行的所有列时，使用VALUES关键字来给出要添加的数据。INSERT语句中无需给出表中的列名，只要VALUES中给出的数据与用CREATETABLE定义表时给定的列名顺序相同即可。【例4-13】向“班级表”中输入一行数据。

在查询分析器中运行如下命令：

USEXSCJ

GO

INSERTINTO班级表

VALUES(‘20051004’,’01’,’05高职网络’)

GO返回的结果为：

（所影响的行数为1行）

需要注意的是：

①输入的顺序和数据类型必须与表中列的顺序和数据类型一致。

②可以不给全部列赋值，但没有赋值的列必须是可以为空的列。

③字符型和日期型值插入时要用单引号扩起来。（2）添加数据到一行中的部分列

要将数据添加到一行中的部分列时，则需要同时给出要使用的列名以及要赋给这些列的数据。【例4-14】向“学生基本信息表”中输入一行数据。

在查询分析器中运行如下命令：

USEXSCJ

GO

INSERTINTO学生基本信息表(学号，姓名，家庭住址)

VALUES(‘000107’,’巴尔夏提’,’新疆库尔勒市’)

GO

返回的结果为：

（所影响的行数为1行）

对于这种添加部分列的操作，在添加数据前应确认未在VALUES列表中出现的列允许不允许为NULL；只有允许为NULL的列，才可以不出现在VALUES列表中。2．使用企业管理器添加数据

【例4-15】使用企业管理器向“班级表”中输入一行数据。

①在控制台树中展开XSCJ数据库，单击【表】。

②在详细列表中右击“班级表”，在弹出的菜单中选择【打开表】命令中的【返回所有行】命令。

③在出现的窗口中分别输入“20051005”、“01”、“05高职软件”。

④单击【关闭】按钮即可保存确认。

4.3.2使用UPDATE语句更新表中数据

语法格式为：

UPDATE表名

SET列名=表达式

[WHERE条件]

【例4-16】将刘瑞恒的出生日期改为。

在查询分析器中运行如下命令：

USEXSCJ

GO

UPDATE学生基本信息表

SET出生日期='1985/10/31'

WHERE姓名=’刘瑞恒’

GO

返回的结果为：

（所影响的行数为1行）4.3.3使用DELETE语句删除表中数据

使用T-SQL中的DELETE语句可以删除数据表中的一个或多个记录。

DELETE语句最简单的形式如下：

DELETE[FROM]表名

[WHERE条件表达式]

其中，表名是要删除数据的表的名字。如果DELETE语句中没有WHERE子句限制，表中的所有记录都将被删除。【例4-16】删除“学生基本信息表”中姓名为杨文利的学生记录。

在查询分析器中运行如下命令：

USEXSCJ

GO

DELETEFROM学生基本信息表

WHERE姓名=’杨文利’

GO

返回的结果为：

（所影响的行数为1行）本章小结

本章介绍了如何创建、修改和删除表，以及如何使用SQL语句修改、添加、删除表结构等。同学们应该重点掌握如何使用企业管理器和SQL语句创建、删除、修改表及表中的数据。练习与上机

一．选择题

1．使用下列哪种语句可以创建数据表（）。

A、CREATEDATABASEB、CREATETABLE

C、ALTERDATABASED、ALTERTABLE

2．使用下列哪种语句可以修改数据表（）。

A、CREATEDATABASEB、CREATETABLE

C、ALTERDATABASED、ALTERTABLE

3．使用下列哪种语句可以删除数据表（）。

A、DROPDATABASEB、CREATETABLE

C、ALTERDATABASED、DROPTABLE

4．使用下列哪种语句可以向表中插入数据（）。

A、INSERTB、UPDATE

C、DELETED、CREATE

5．使用下列哪种语句可以更新表中数据（）。

A、INSERTB、UPDATE

C、DELETED、CREATE

6．使用下列哪种语句可以删除表中数据（）。

A、INSERTB、UPDATE

C、DELETED、CREATE二．填空题

1．在SQLServer2000中，可以使用_____________和____________语句两种方法创建表。

2．在SQLServer2000中，可以使用_____________和____________语句两种方法重新命名表。