《SQL项目实例教程》-模块四

模块四数据库表数据操作任务描述知识要点返回任务描述数据库用表来存储和管理数据，要实现数据的存储，必须向表中添加数据；要实现数据的管理，也需要经常修改和删除表中数据。本模块主要实现“ｅｂｏｏｋ”数据库中各数据表中数据的添加、修改和删除操作。ＳＱＬＳｅｒｖｅｒ为了保证数据的安全性，只有系统管理员（ｓａ）、数据库所有者（ｄｂｏ）、数据库对象的所有者及被授予权限的用户才能对数据表中的数据进行添加、修改和删除操作。任务１添加数据任务１.１向“ＡｄｍｉｎＵｓｅｒ”表中添加数据下一页返回任务描述方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ添加数据①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。②展开“ｅｂｏｏｋ”节点，再展开“表”节点，右击要添加数据的“ＡｄｍｉｎＵｓｅｒ”表，在弹出的快捷菜单中选择“编辑前２００行”命令，就会打开“查询设计器”的结果窗口，如图４.１所示。③在“查询设计器”的表中可以输入新记录，也可以对已有数据进行修改和删除。上一页下一页返回任务描述在此，向“ＡｄｍｉｎＵｓｅｒ”表中输入数据，如图４.２所示。方法二：使用ＩＮＳＥＲＴ语句添加数据在查询编辑器中，输入以下Ｔ－ＳＱＬ命令添加数据。上一页下一页返回任务描述任务１.２向“ＢｏｏｋＴｙｐｅ”、“Ｂｏｏｋ”、“ＯｒｄｅｒＩｎｆｏ”、“ＯｒｄｅｒＤｅ⁃ｔａｉｌ”添加数据根据项目实践需要，向“ｅｂｏｏｋ”数据库的“ＢｏｏｋＴｙｐｅ”、“Ｂｏｏｋ”、“ＯｒｄｅｒＩｎｆｏ”、“Ｏｒ⁃ｄｅｒＤｅｔａｉｌ”各表中添加部分数据，代码如下：／∗向“ＢｏｏｋＴｙｐｅ”表添加数据∗／上一页下一页返回任务描述上一页下一页返回任务描述／∗向“Ｂｏｏｋ”表添加数据∗／上一页下一页返回任务描述上一页下一页返回任务描述上一页下一页返回任务描述／∗向“ＯｒｄｅｒＩｎｆｏ”表添加数据∗／上一页下一页返回任务描述/∗向“ＯｒｄｅｒＤｅｔａｉｌ”表添加数据∗／上一页下一页返回任务描述上一页下一页返回任务描述任务１.３在“Ｂｏｏｋ”表中新添加一行记录，指定所有字段值使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ添加数据的方法之前已经阐述过了，实现任务需求的ＩＮＳＥＲＴ语句如下：上一页下一页返回任务描述执行以上代码，向“Ｂｏｏｋ”表中添加一行新的记录，表中数据如图４.３所示。以上ＩＮＳＥＲＴ语句向表中添加数据时，给出了表中的全部字段，值列表中数据值的数量、顺序与字段列表匹配，数据值的数据类型与对应列的数据类型兼容。当为表中全部字段赋值时，字段列表可以省略，以下为省略字段列表的ＩＮＳＥＲＴ语句：上一页下一页返回任务描述任务１.４在“Ｂｏｏｋ”表中新添加一行记录，指定部分字段值在“Ｂｏｏｋ”表中添加一行记录，部分字段值如下：ｉｓｂｎ为“９７８７０４０３７９９３８”，ｂｏｏｋＮａｍｅ为“ＪＳＰ动态Ｗｅｂ技术实例教程”，ａｕｔｈｏｒ为“聂哲”，ｐｕｂｌｉｓｈｅｒ为“高等教育出版社”，ｐｒｉｃｅ为“２９.８”。实现任务需求的ＩＮＳＥＲＴ语句如下：上一页下一页返回任务描述执行以上代码，运行结果如图４.４所示。以上代码经分析检查，在语法上没有错误，出错的原因在于“Ｂｏｏｋ”表中的“ｂｏｏｋＴｙ⁃ｐｅＩｄ”字段不允许为空。向表中添加数据时，当省略表中的某些列时，这些列值一定要遵循数据完整性要求。代码修改后如下：上一页下一页返回任务描述修改后的代码成功执行，“Ｂｏｏｋ”表数据如图４.５所示。在新添加的数据记录中，未指定数据值的“ｐｉｃｔｕｒｅ”字段和“ｄｅｓｃｒｉｐｔｉｏｎ”字段允许为空，默认取值为ＮＵＬＬ。任务２修改数据任务２.１将ｕｓｅｒｎａｍｅ为“ａｄｍｉｎ１”的管理员密码修改为“ａｄ１２３４”方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改数据上一页下一页返回任务描述使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改数据的操作与添加数据的操作相同。在如图４.２所示的“查询设计器”中，对“ＡｄｍｉｎＵｓｅｒ”表中需要修改的数据根据数据修改要求直接进行修改即可。方法二：使用ＵＰＤＡＴＥ语句修改数据在“查询编辑器”窗口中输入如下语句：ＵＳＥｅｂｏｏｋ上一页下一页返回任务描述ＵＰＤＡＴＥ子句指定修改的数据表为“ＡｄｍｉｎＵｓｅｒ”，ＳＥＴ子句指定修改的字段及其新的数据值，ＷＨＥＲＥ子句指定数据修改条件。执行以上语句，执行结果如图４.６所示。执行结果显示“１行受影响”，表明ＵＰＤＡＴＥ语句更新了一行数据。查看ＡｄｍｉｎＵｓｅｒ表，可以看到已完成数据的修改操作。任务２.２将ｉｓｂｎ为“９７８７１１１４７０９５３”的书籍价格修改为“２５”使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改数据的方法之前已经阐述过了，实现任务需求的ＵＰＤＡＴＥ语句如下：上一页下一页返回任务描述ＵＰＤＡＴＥ子句指定修改的数据表为“Ｂｏｏｋ”，ＳＥＴ子句指定修改的字段及其新的数据值，ＷＨＥＲＥ子句指定数据修改条件。执行以上代码，完成数据修改操作。任务２.３将ｉｓｂｎ为“９７８７０４０３７９９３８”的书籍ｐｉｃｔｕｒｅ修改为“１５.ｊｐｇ”、ｄｅｓｃｒｉｐｔｉｏｎ修改为“全国高职高专教育规划教材”上一页下一页返回任务描述实现任务需求的ＵＰＤＡＴＥ语句如下：执行以上代码，完成数据修改操作，运行结果如图４.７所示。本任务中通过ＳＥＴ子句同时修改了两个字段的值。任务２.４将“英语”类书籍的价格修改为原价的９折实现任务需求的ＵＰＤＡＴＥ语句如下：上一页下一页返回任务描述本任务中修改Ｂｏｏｋ表数据的操作涉及了ＢｏｏｋＴｙｐｅ表，通过ＦＲＯＭ子句指定所涉及的数据表及其连接条件。执行以上语句，执行结果显示“２行受影响”，表明ＵＰＤＡＴＥ语句更新了２行数据，即修改了两本英语类书籍的价格。查看Ｂｏｏｋ表，可以看到已完成数据的修改，如图４.８所示。上一页下一页返回任务描述考虑到项目实践的需要，将“英语”类书籍的价格修改回原价。同时给出数据表连接的另一种方法。任务２.５将“ＡｄｍｉｎＵｓｅｒ”表中所有管理员用户的密码修改为“１２３４５６”实现任务需求的ＵＰＤＡＴＥ语句如下：上一页下一页返回任务描述ＵＰＤＡＴＥ语句缺省ＷＨＥＲＥ子句时用于修改表中所有数据行。执行以上代码，“Ａｄｍｉｎ⁃Ｕｓｅｒ”表中的所有管理员用户密码均被修改，如图４.９所示。考虑到项目实践的需要，将“ＡｄｍｉｎＵｓｅｒ”表中管理员用户的密码修改回原来的数据值。上一页下一页返回任务描述任务３删除数据上一页下一页返回任务描述任务３.１将ｉｓｂｎ为“９７８７１１１４７０９５３”和“９７８７０４０３７９９３８”的书籍信息删除方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ删除数据在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中，打开“ｅｂｏｏｋ”数据库中“Ｂｏｏｋ”表的“查询设计器”窗口，如图４.１０所示。右击要删除的记录，在弹出的如图４.１１所示的对话框中选择“是”，完成指定数据的删除。方法二：使用ＤＥＬＥＴＥ语句删除数据在“查询编辑器”窗口中输入如下语句：上一页下一页返回任务描述ＤＥＬＥＴＥ子句指定删除数据的数据表为“Ｂｏｏｋ”，ＷＨＥＲＥ子句指定数据删除条件。任务３.２将“计算机”类的价格高于５０元的所有书籍信息删除上一页下一页返回任务描述使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ删除数据的方法之前已经阐述过了，实现任务需求的ＤＥＬＥＴＥ语句如下：本任务中删除Ｂｏｏｋ表数据的操作涉及了ＢｏｏｋＴｙｐｅ表，通过ＦＲＯＭ子句指定所涉及的数据表及其连接条件，ＷＨＥＲＥ子句限定了多重删除条件。上一页下一页返回任务描述执行以上语句，执行结果显示“０行受影响”，表明未找到符合条件的记录。任务３.３删除“ＯｒｄｅｒＩｎｆｏ”表中２号订单信息实现任务需求的ＤＥＬＥＴＥ语句如下：ＤＥＬＥＴＥ子句指定删除数据的数据表为“ＯｒｄｅｒＩｎｆｏ”，ＷＨＥＲＥ子句指定数据删除条件。执行以上代码，运行结果如图４.１２所示。上一页下一页返回任务描述程序运行出错的原因在于数据删除操作违背了之前在“ＯｒｄｅｒＤｅｔａｉｌ”表中创建的外键约束，“ＯｒｄｅｒＤｅｔａｉｌ”表中的“ｏｒｄｅｒＩｄ”字段须引用“ＯｒｄｅｒＩｎｆｏ”表中“ｏｒｄｅｒＩｄ”字段的值。所以，删除数据操作必须遵循数据完整性要求，只有当要删除的数据不被其他表引用时，才能删除。任务３.４将ＯｒｄｅｒＩｎｆｏ表中的记录清空方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ删除数据上一页下一页返回任务描述在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中，打开“ｅｂｏｏｋ”数据库中“ＯｒｄｅｒＩｎｆｏ”表的“查询设计器”窗口，删除表中所有记录。具体操作方法在任务３.１中已经做过详细阐述。方法二：使用ＤＥＬＥＴＥ语句清空数据方法三：使用ＴＲＵＮＣＡＴＥＴＡＢＬＥ语句清空数据上一页下一页返回任务描述任务４导入／导出数据任务４.１导出数据将“ｅｂｏｏｋ”数据库中“Ｂｏｏｋ”表的数据导出到Ｅｘｃｅｌ文件ｂｏｏｋ.ｘｌｓ中。操作步骤：①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中选择服务器，展开“数据库”节点，右击“ｅｂｏｏｋ”数据库，在弹出的快捷菜单中选择“任务”→“导出数据”（如图４.１３所示），打开“ＳＱＬＳｅｒｖｅｒ导入和导出向导”对话框，如图４.１４所示。上一页下一页返回任务描述②单击“下一步”命令按钮，进入“选择数据源”界面，如图４.１５所示。在“数据源”下拉列表框中选择“ＳＱＬＳｅｒｖｅｒＮａｔｉｖｅＣｌｉｅｎｔ１０.０”，指定数据源为ＳＱＬＳｅｒｖｅｒ数据；在“服务器名称”下拉列表框中选择或输入数据库服务器；选择适当的身份验证方式；在“数据库”下拉列表框中选择或输入数据库名称，在此选择“ｅｂｏｏｋ”数据库。③单击“下一步”命令按钮，进入“选择目标”界面，如图４.１６所示。在“目标”下拉列表框中选择目标表类型，指定转换后的数据格式，在此选择“ＭｉｃｒｏｓｏｆｔＥｘｃｅｌ”。单击“浏览”按钮，在打开的对话框中创建（或选择）Ｅｘｃｅｌ文件ｂｏｏｋ.ｘｌｓ。上一页下一页返回任务描述④单击“下一步”命令按钮，进入“指定表复制或查询”界面，指定传输数据的方式，如图４.１７所示。可以选择复制源数据库中现有表或视图的全部数据，也可以选择编写ＴＳＱＬ查询语句来指定要传输的数据。⑤单击“下一步”命令按钮，进入“选择源表和源视图”界面，选择要复制的表和视图，如图４.１８所示。其中列出了源数据库中所有的表和视图，可以选择一项或多项要复制的表和视图，这里选择“Ｂｏｏｋ”表。上一页下一页返回任务描述选中表和视图后，可以单击“编辑映射”按钮，在打开的“列映射”对话框中，对表和视图进行转化设置，如删除目标表中的记录设置等；单击“预览”按钮，可以查看选择表转换后的结果。这里选择按默认设置进行转换。⑥单击“下一步”命令按钮，进入“查看数据类型映射”界面，查看源表中数据类型映射到目标中的数据类型的方式，同时选择向导处理转换问题的方式，如图４.１９所示。⑦单击“下一步”命令按钮，进入“保存并运行包”界面，勾选“立即运行”复选框。上一页下一页返回任务描述⑧单击“下一步”命令按钮，进入“完成该向导”界面，如图４.２０所示。单击“完成”命令按钮，系统将开始执行复制数据的操作。执行成功后将弹出如图４.２１所示的“执行成功”界面，单击“关闭”命令按钮，即完成本次数据导出操作。⑩打开“Ｄ：＼ｂｏｏｋ.ｘｌｓ”文件，查看“Ｂｏｏｋ”表中数据是否被正确导出。任务４.２导入数据将ｂｏｏｋ.ｘｌｓ中“Ｂｏｏｋ”表的数据导入“ｅｂｏｏｋ”数据库中的“Ｂｏｏｋ副本”数据表。操作步骤：上一页下一页返回任务描述导入数据与导出数据的方法基本相似，也是使用“导入和导出向导”，下面简单介绍导入数据的过程。①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中选择服务器，展开“数据库”节点，右击“ｅｂｏｏｋ”数据库，在弹出的快捷菜单中选择“任务”→“导入数据”，打开“ＳＱＬＳｅｒｖｅｒ导入和导出向导”对话框。②单击“下一步”命令按钮，进入“选择数据源”界面。在“数据源”下拉列表框中选择“ＭｉｃｒｏｓｏｆｔＥｘｃｅｌ”；单击“浏览”按钮，在打开的对话框中选择ｂｏｏｋ.ｘｌｓ。上一页下一页返回任务描述若导入的Ｅｘｃｅｌ表格中的首行是列的名称，则勾选“首行包含列名称”复选框，如图４.２２所示。③单击“下一步”命令按钮，进入“选择目标”界面。在“目标”下拉列表框中选择“ＳＱＬＳｅｒｖｅｒＮａｔｉｖｅＣｌｉｅｎｔ１０.０”，选择服务器、身份验证方式和数据库“ｅｂｏｏｋ”。④单击“下一步”命令按钮，进入“指定表复制或查询”界面，选择“复制一个或多个表或视图的数据”。⑤单击“下一步”命令按钮，进入“选择源表和源视图”界面，如图４.２３所示。上一页下一页返回任务描述勾选“Ｂｏｏｋ”表复选框，并将“目标”名称修改为“Ｂｏｏｋ副本”。⑥单击“下一步”命令按钮，进入“查看数据类型映射”界面。⑦单击“下一步”命令按钮，进入“保存并运行包”界面，勾选“立即运行”复选框。⑧单击“下一步”命令按钮，进入“完成该向导”界面。单击“完成”命令按钮，系统开始执行复制数据的操作。上一页下一页返回任务描述执行成功后弹出“执行成功”界面，单击“关闭”命令按钮，完成本次数据导入操作。⑨打开ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中刷新“ｅｂｏｏｋ”数据库，查看数据是否被正确导入“Ｂｏｏｋ副本”数据表。上一页返回知识要点一、ＩＮＳＥＲＴ语句向表中添加数据可以使用ＩＮＳＥＲＴ语句。其中：下一页返回知识要点•ＩＮＴＯ是一个可选的关键字，可以将它用在ＩＮＳＥＲＴ和目标表之间。•ｔａｂｌｅ＿ｎａｍｅ是将要接收数据的表名。•ｃｏｌｕｍｎ＿ｌｉｓｔ是要在其中插入数据的字段列表，必须用圆括号括起来，并且用英文逗号将各个字段进行分隔，字段顺序可以不同于表中顺序。如果添加数据时表中的全部字段都接收数据，则ｃｏｌｕｍｎ＿ｌｉｓｔ字段列表可以省略。当省略表中的某些列时，这些列值的确定原则如下：①具有ＩＤＥＮＴＩＴＹ标识符属性的列，其值由系统自动计算得到。上一页下一页返回知识要点②数据类型为ｔｉｍｅｓｔａｍｐ的列，系统自动赋值。③若为计算列，则使用计算值。④已设置默认值的列，其值为默认值。⑤允许列值为空的列，其值为空。•ＶＡＬＵＥＳ用于引入要添加的数据值的列表。•ｖａｌｕｅ＿ｌｉｓｔ用于指定各字段需要添加的数据值列表，与ｃｏｌｕｍｎ＿ｌｉｓｔ中的字段列表在列数、列序都必须保持一致。若之前ｃｏｌｕｍｎ＿ｌｉｓｔ省略，ｖａｌｕｅ＿ｌｉｓｔ为表中所有字段的值列表。上一页下一页返回知识要点值列表必须用圆括号括起来，并且用英文逗号将各个数据值进行分隔。数据值可以有以下三种形式：①使用ＤＥＦＡＵＬＴ关键字：表示插入的值为该列的默认值，这要求建表时已经为该列指定了默认值。此外，对于ＩＤＥＮＴＩＴＹ属性列和ｔｉｍｅｓｔａｍｐ类型列，列值位置也应使用ＤＥＦＡＵＬＴ关键字表示。②使用ＮＵＬＬ关键字：表示插入的值为空，这要求建表时设置该列的属性允许为空。③使用表达式形式：可以是常量、变量或表达式，其值的数据类型要与列的数据类型保持一致，并且输入字符型数据和时间日期型数据时要用单引号括起来。上一页下一页返回知识要点二、ＵＰＤＡＴＥ语句随着数据库系统的实际运行，某些数据可能会发生变化，这时就需要对表中的数据进行修改。修改表数据可以使用ＵＰＤＡＴＥ语句。ＵＰＤＡＴＥ语句的语法格式：上一页下一页返回知识要点其中：•ｔａｂｌｅ＿ｎａｍｅ是需要修改数据的表的名称。•ＳＥＴ用于指定要修改的列或变量名称的列表。•ｃｏｌｕｍｎ＿ｎａｍｅ用于指定要修改数据的列的名称。•ｅｘｐｒｅｓｓｉｏｎ｜ＤＥＦＡＵＬＴ｜ＮＵＬＬ是列值表达式。•ＦＲＯＭ子句用于指定数据修改涉及的数据来源。•ＷＨＥＲＥ子句用于指定数据修改的条件。上一页下一页返回知识要点三、ＤＥＬＥＴＥ语句、ＴＲＵＮＣＡＴＥＴＡＢＬＥ语句随着数据库系统的实际运行，表中可能产生一些无用的数据。这些数据不仅占用空间，还影响查询的速度，应该及时删除它们。删除表数据可以使用ＤＥＬＥＴＥ语句和ＴＲＵＮＣＡＴＥＴＡＢＬＥ语句。１.ＤＥＬＥＴＥ语句的语法格式上一页下一页返回知识要点其中：•ｔａｂｌｅ＿ｎａｍｅ是需要删除数据的表的名称。•ＦＲＯＭ子句用于指定数据删除涉及的数据来源。•ＷＨＥＲＥ子句用于指定数据删除的条件。２.ＴＲＵＮＣＡＴＥＴＡＢＬＥ语句的语法格式ＴＲＵＮＣＡＴＥＴＡＢＬＥｔａｂｌｅ＿ｎａｍｅ其中：•ＴＲＵＮＣＡＴＥＴＡＢＬＥ为关键字。•ｔａｂｌｅ＿ｎａｍｅ为要删除所有记录的表名。上一页下一页返回知识要点ＴＲＵＮＣＡＴＥＴＡＢＬＥ语句用于