第5章 表的创建与管理

第5章表的创建与管理知识技能目标:1．熟练掌握数据表的创建、修改和删除2．熟练掌握表数据的插入、修改和删除3．学会数据的导入与导出第5章表的创建与管理引导案例：按照第2章的设计，在教务管理数据库中建立学生表、课程表、选课表、班级表、专业表、系部表和用户表，实现教务管理数据库的设计。本章就来介绍如何在教务管理数据库中建立这些表，在各表中输入数据，并对这些表和数据进行管理。第5章表的创建与管理5.1用SSMS创建表和管理表及表数据5.2用T-SQL语句创建管理表5.3用T-SQL语句管理表数据5.4数据导入与导出小结5.1用SSMS创建表和管理表及表数据5.1.1创建表表用以存储数据，是数据库中最重要的部分。用SQLServerManagementStudio创建表，方法如下：1）打开SQLServerManagementStudio。2）在对象资源管理器中展开要建表的数据库，如“教务管理数据库”。3）在该数据库下“表”结点上单击右键，选择“新建表”，如图5.1所示。返回目录返回目录5.1.1创建表图5.1新建表5.1用SSMS创建表和管理表及表数据返回目录4）在如图5.2所示的窗口中定义表结构。即按照事先的设计逐个定义表中的列（字段），确定各字段的名称（列名）、数据类型、长度、是否允许为空等。如图5.2所示，定义的是“学生表”的结构。5.1.1创建表5.1用SSMS创建表和管理表及表数据返回目录5.1.1创建表图5.2定义表结构5.1用SSMS创建表和管理表及表数据返回目录5.1.1创建表5）完成表结构定义后，单击工具栏上的“保存”图标或选择菜单“文件”下的“保存”项，保存新建的表。6）在出现的“选择名称”对话框中输入表的名称，如“学生表”，如图5.3所示。单击“确定”按钮，完成表的创建。图5.3输入表名称5.1用SSMS创建表和管理表及表数据5.1.1创建表需要说明：1）表中的列名必须唯一，但同一列名可以在数据库中不同的表中使用。2）表名最大128个字符，要符合标识符命名规则。3）每个表最多可包含1024列。4）列的长度必须在8000字节内。返回目录5.1用SSMS创建表和管理表及表数据5.1.2修改表结构数据表建立后，如果需要，可以对表的结构进行修改。修改表结构的操作有：增加新列对现有列的属性进行修改，包括对列名、数据类型、列长度、精度进行修改删除列用SQLServerManagementStudio可以非常直观地修改数据表结构，方法如下：1）打开SQLServerManagementStudio。2）在对象资源管理器中展开要修改的表所在的数据库，如“教务管理数据库”。返回目录5.1用SSMS创建表和管理表及表数据5.1.2修改表结构3）展开“表”结点，在“学生表”上单击右键，选择“修改”，则进入图5.2所示的窗口。4）修改表结构。可以直接将光标移动到要修改的列上进行列名、类型和长度的修改；若要增加新列，先选择新增列的位置，然后单击鼠标右键，选择“插入列”命令，这时在该位置上方出现一个空行，在这个空行中定义新列即可；选中要删除列，单击右键选择“删除列”则可以把列删除。5）修改完毕后单击“保存”按扭。修改表结构，也可以在对象资源管理器中展开要修改的表，在列上单击右键，选择“新建列”可添加新列；在某列上单击右键，选择“修改”则可以修改列；选择“删除”则直接把该列删除；选择“重命名”返回目录5.1用SSMS创建表和管理表及表数据5.1.2修改表结构可以修改列名。如图5.4所示。返回目录5.1用SSMS创建表和管理表及表数据5.1.3删除表当表不需要时可以将表删除。删除表后，该表的结构定义、约束、数据以及和表相关的数据库对象，例如索引、触发器、授权都从数据库中永久删除。用SQLServerManagementStudio删除表，方法如下：1）打开SQLServerManagementStudio。2）在对象资源管理器中展开要删除的表所在的数据库，如“教务管理数据库”。3）展开“表”结点，在要删除的表上单击右键，选择“删除”，则出现删除对象对话框，如图5.5所示。返回目录5.1用SSMS创建表和管理表及表数据5.1.3删除表图5.5删除表返回目录5.1用SSMS创建表和管理表及表数据5.1.3删除表4）在“删除对象”对话框中单击“确定”按钮。返回目录5.1用SSMS创建表和管理表及表数据5.1.4插入、修改、删除表中数据表用以存储数据，当表创建好后，就可以在表中输入数据。表中数据经常需要更新，包括插入、修改和删除数据。例如学生选课，学习结束后需要录入考试成绩；如果学生信息改变，需要更新现有记录；如果学生退学了，则需要删除这个学生的信息。用SQLServerManagementStudio插入、修改、删除表数据，方法如下：1）打开SQLServerManagementStudio。2）在对象资源管理器中展开数据库，如“教务管理数据库”。3）展开“表”节点，右键单击要操作的表，如“学生表”，选择“打开表”命令，如图5.6所示。返回目录5.1用SSMS创建表和管理表及表数据图5.6打开表返回目录5.1用SSMS创建表和管理表及表数据5.1.4插入、修改、删除表中数据5.1.4插入、修改、删除表中数据4）单击“打开表”命令后出现如图5.7所示的窗口，在该窗口中可以插入、修改和删除表中数据。例如，可将光标移到一个新的空行上输入新的数据；也可将光标移动到任意行的字段上，修改数据；还可将光标移动到任意行，选中该行单击右键，选择“删除”，实现删除行操作。SQLServe2005提供数据的自动保存功能。返回目录5.1用SSMS创建表和管理表及表数据5.1.4插入、修改、删除表中数据图5.7表数据操作返回目录5.1用SSMS创建表和管理表及表数据5.1.4插入、修改、删除表中数据注意，在图5.7所示的窗口中，光标可以直接在窗口上移动，也可以通过窗口左下角的记录移动按钮移动，按钮可实现将光标移动到第一条，前一条，后一条，最后一条，如果知道记录所在的行，也可以直接输入数字，移动指定的记录行上。返回目录5.1用SSMS创建表和管理表及表数据5.2用T-SQL语句创建管理表表的创建与管理，表中数据的操作，可以使用SQLServerManagementStudio实现，也可以用T-SQL语句实现。用T-SQL语句实现的脚本程序保存好，以后可以多次执行，特别是可以在应用程序开发中，在前台程序中运行T-SQL命令可以操纵后台数据库。本章对表的操作一定要先打开指定的数据库“教务管理数据库”，可以用两种方法打开，一种是直接在窗口中工具栏上的数据库下拉列表中选择，另一种方法是使用命令：USE教务管理数据库GO打开数据库，再执行本章所有例题的命令。返回目录5.2.1创建表用Ｔ-SQL语句创建表，需使用CREATETABLE命令。命令格式：CREATETABLE表名（列名数据类型[（长度）]|列名AS计算列表达式[，…n]）【例5.1】用T-SQL语句在教务管理数据库中创建学生表”。【例5.2】用T-SQL语句在教务管理数据库中创建课程表。【例5.3】用T-SQL语句在教务管理数据库中创建选课表。返回目录5.2用T-SQL语句创建管理表5.2.1创建表【例5.1】用T-SQL语句在教务管理数据库中创建学生表。CREATETABLE学生表(学号char(11),姓名nchar(5),性别nchar(1),出生日期datetime,政治面貌varchar(10),入学时间datetime,联系电话char(20),班级char(5),备注varchar(200),)返回目录5.2用T-SQL语句创建管理表5.2.1创建表【例5.2】用T-SQL语句在教务管理数据库中创建课程表。CREATETABLE课程表(课程号char(6),课程名varchar(20),学时tinyint,学分tinyint,课程类型nchar(5),课程性质nchar(2),考核方式nchar(2),学期char(1),备注varchar(200))返回目录5.2用T-SQL语句创建管理表5.2.1创建表【例5.3】用T-SQL语句在教务管理数据库中创建选课表CREATETABLE选课表(学号char(11),课程号char(6),成绩numeric(5,1))返回目录5.2用T-SQL语句创建管理表返回目录5.2.2修改表修改表包括向表中添加列、删除列和修改表中列的定义。使用T-SQL语句修改表，需使用ALTERTABLE命令。1.添加列命令格式：ALTERTABLE表名ADD列名数据类型[（长度）][，…n]【例5.4】用T-SQL语句在学生表中添加新列电子邮箱，类型为字符型，长度为20个字符，允许为空。ALTERTABLE学生表ADD电子邮箱char(20)NULL5.2用T-SQL语句创建管理表返回目录5.2.2修改表可以一次添加多列。如果表中已有数据，则新添加的列则只能是允许为空，或者设置有默认值，或者要添加的列是标识列或时间戳列，这是因为做添加列操作时，SQLServer2005为表中的每个现有数据行插入一个值，如果不允许为空，又没有默认值，则返回错误，添加失败，如果允许为空，则将空值插入该列。2.修改列属性命令格式：ALTERTABLE表名ALTERCOLUMN列名数据类型[（长度）]5.2用T-SQL语句创建管理表返回目录5.2.2修改表【例5.5】修改学生表的列电子邮箱数据类型为可变长度的字符类型，长度为22。ALTERTABLE学生表ALTERCOLUMN电子邮箱varchar(22)3.删除列命令格式：ALTERTABLE表名DROPCOLUMN列名[，…n]【例5.6】删除学生表的电子邮箱列ALTERTABLE学生表DROPCOLUMN电子邮箱可以一次删除多列。5.2用T-SQL语句创建管理表5.2.3表更名如果需要可以重命名表名。需要用到系统存储过程SP_RENAME，这个过程可以重命名任何数据库对象，例如表，视图，存储过程或函数。基本语法：SP_RENAME旧对象名，新对象名【例5.7】将课程表更名为课程信息表。SP_RENAME课程表,课程信息表返回目录5.2用T-SQL语句创建管理表返回目录5.2.4删除表使用T-SQL语句删除表，需使用DROPTABLE命令。命令格式：DROPTABLE表名[，…n]一次可以同时删除多个表。【例5.8】将课程信息表删除DROPTABLE课程信息表5.2用T-SQL语句创建管理表5.3.1插入数据在SQLServer中插入数据只能一行一行插入，不能同时插入多行。使用T-SQL语句向表中插入数据，需使用INSERT命令。命令格式：INSERT[INTO]表名[(列名，…)]VALUES(值，…)说明：(列名，…)是可选项，如果插入的数据是部分列，或插入列与表定义的顺序不一致时，可以选择它。如果不选择这项，表示要插入的数据包含所有列。VALUES中的值也可以是可计算出值的表达式。返回目录5.3用T_SQL语句管理表数据5.3.1插入数据向表中插入行时要注意：数据值的数量和顺序必须与字段名列表中的数量和顺序一样。值的数据类型必须与表的列中的数据类型匹配，否则插入失败。值如果是采用默认值写DEFAULT，如果是空值写NULL。不需要包含带有IDENTITY属性的列。插入数据类型如果是字符型、日期型必须要用单引号引住，日期型注意输入格式，中文版SQLServer是‘yyyy-mm-dd’。如果插入数据是数值型，则不必用引号。返回目录5.3用T_SQL语句管理表数据5.3.1插入数据【例5.9】向学生表插入一行数据。【例5.10】用向学生表插入一行数据，目前只知道该学生学号，姓名。【例5.11】将学生表的女生单独保存到一个女生表返回目录5.3用T_SQL语句管理表数据返回目录5.3.1插入数据【例5.9】向学生表插入一行数据。INSERTINTO学生表VALUES(‘20080101001’,‘李海强’,‘男’,‘1988