ch08操纵表中数据

第8章操纵表中数据学习目标本章重点本章内容2/3/20231学习目标表创建之后，就可以对表执行各种操纵了。操纵表实际上就是操纵数据。用户可以根据需要向表中添加数据，可以更新表中已有的数据，甚至可以删除表中不再使用的数据。在更多情况下，用户需要检索表中的数据。如果需要的数据在一个表中，那么检索操作比较简单。如果需要的数据分散在多个不同的表中，那么需要执行复杂的检索操作。本章全面研究操纵表中数据的内容。2/3/20232本章重点插入数据更新数据删除数据检索数据高级操纵加密数据2/3/20233本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/20234本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/202358.1概述下面，通过一个具体示例，研究表创建之后用户面临的操纵数据问题和解决这些操纵数据问题的思路。【例8-1】创建books表，研究数据操纵问题。2/3/20236空表和数据操纵问题表创建之后，表只是一个空表。如何向表中添加数据呢？如果表中已有数据了，但是数据不合适或不正确，那么如何更新这些数据呢？如果表中的数据不再需要了，那么如何删除这些过时的数据呢？如何按照用户需要，将表中的数据检索出来呢？这些问题都是数据操纵问题。用户可以使用INSERT、UPDATE、DELETE、SELECT等语句来解决这些数据操纵问题。2/3/20237本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/202388.2插入数据表创建之后只是一个空表，因此向表中插入数据是在表结构创建之后首先需要执行的操作。向表中插入数据，应该使用INSERT语句。该语句包括了两个子句，即INSERT子句和VALUES子句。INSERT子句指定要插入数据的表名或视图名称，它可以包含表或视图中列的列表。VALUES子句指定将要插入的数据。2/3/20239【例8-2】向books表中插入一行完整数据2/3/202310【例8-3】向books表中插入空值2/3/202311【例8-4】向books表中插入部分列数据2/3/202312【例8-5】使用DEFAULTVALUES子句2/3/202313【例8-6】使用INSERT…SELECT形式2/3/202314【例8-7】使用SELECT…INTO形式2/3/202315【例8-8】使用BULKINSERT语句2/3/202316本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023178.3更新数据可以使用UPDATE语句更新表中已经存在的数据。UPDATE语句既可以一次更新一行数据，也可以一次更新许多行，甚至可以一次更新表中的全部数据行。在UPDATE语句中，使用WHERE子句指定要更新的数据行满足的基本条件，使用SET子句给出新的数据。新数据既可以是常量，也可以是指定的表达式。UPDATE语句的基本语法形式如下：UPDATEtable_or_view_name

SETcolumn_name=expression,…WHEREsearch_condition

2/3/202318【例8-9】查看books表中的数据2/3/202319【例8-10】更新books表中指定图书的页数和价格数据2/3/202320【例8-11】更新books表中所有图书的价格数据2/3/202321【例8-12】在UPDATE语句中使用FROM子句2/3/202322本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023238.4删除数据当表中的数据不再需要时，可以将其删除。一般情况下，使用DELETE语句删除数据。DELETE语句可以从一个表中删除一行或多行数据。删除数据的DELEE语句的基本语法形式如下：DELETEFROMtable_or_name

WHEREsearch_condition

在DELETE语句中，如果使用了WHERE子句，那么将从指定的表中删除满足WHERE子句条件的数据行。如果没有使用WHERE子句，那么将删除指定表中的全部数据。2/3/202324【例8-13】使用DELETE语句删除2/3/202325DELETE语句的说明就像UPDATE语句一样，在DELETE语句中还可以再使用一个FROM子句指定将要删除的数据与其他表或视图之间的关系。也就是说，一个正常的DELETE语句中可以包含两个FROM子句，但是这两个FROM子句的作用是不同的。第一个FROM子句用于指定将要删除的数据所在的表或视图名称，第二个FROM子句用于指定将要删除的数据的其他复杂的条件。DELETE语句只是删除表中的数据，表结构依然存在于数据库中。如果需要删除表结构，那么应该使用DROPTABLE语句。在删除表中的全部数据时，还可以使用TRUNCATETABLE语句。TRUNCATETABLE语句和DELETE语句都可以将表中的全部数据删除，但是两条语句又有不同的特点。2/3/202326本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023278.5检索数据概述如果我们希望检索表中数据，可以使用SELECT语句。在SELECT语句中，有3个基本的组成部分即SELECT子句、FROM子句和WHERE子句。SELECT子句用于指定将要检索的列名称，FROM子句指定将要检索的对象，WHERE子句则用于指定数据应该满足的条件。在一般的检索操作中，SELECT子句和FROM子句是必不可少的。只有当SELECT子句中仅包括常量、变量或算术表达式(没有列名)时，FROM子句才可以省略。WHERE子句是可选的。如果没有使用WHERE子句，表示检索所有的数据。2/3/202328【例8-14】检索表中所有数据2/3/202329本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023308.6使用SELECT子句检索数据SELECT子句是SELECT语句的一部分，指SELECT关键字后面的内容。在SELECT语句中，可以在SELECT子句中选择指定的数据列、使用文字串、改变列标题、执行数据运算、使用ALL关键字及使用DISTINCT关键字等。2/3/202331选择指定的数据列选择指定的数据列是指可以在SELECT子句中指定将要检索的列名称。选择指定的列名称要注意几点，第一，这些列名称应该与表中定义的列名称一致，否则就可能出错或者得到意想不到的结果；第二，列名称之间的顺序既可以与表中定义的列顺序相同，也可以不相同；第三，SELECT语句的检索结果只是影响数据的显示，对表中数据的存储没有任何的影响。2/3/202332【例8-15】检索指定的数据列2/3/202333文字串通常，直接阅读SELECT语句的检索结果是一件头疼的事情，因为显示出来的数据只是一些不连贯的、阅读性不强的信息。为了提高SELECT语句检索结果的可读性，可以通过在SELECT关键字后面增加文字串。通常情况下，使用单引号将文字串引起来。2/3/202334【例8-16】在检索结果中使用文字串2/3/202335【例8-17】在文字串中使用了单引号和双引号2/3/202336改变列标题在默认情况下，在数据检索结果中所显示出来的列标题就是在定义表时使用的列名称。但是，在检索过程中可以根据用户的需要改变显示的列标题。实际上，改变列标题也就是为指定的列定义一个别名。改变列标题有两种方法，一种方法是使用等号(=)，另一种方法是使用AS关键字。当使用=时，其语法形式是：新标题=列名。当使用AS关键字时，其形式是列名AS新标题。由于AS关键字是可以省略的，因此改变列标题也可以写成这种形式：列名新标题。注意，使用等号和使用AS关键字时，新标题和列名的位置是不同的。2/3/202337【例8-18】改变列标题2/3/202338数据运算数据运算就是指对检索的数据进行各种运算。也就是说，可以在SELECT关键字后面列出的列项中使用各种运算符和函数。这些运算符和函数包括算术运算符、数学函数、字符串函数、日期和时间函数及系统函数等。算术运算符可以用在各种数值列上，这些列的数据类型可以是INT、SMALLINT、TINYINT、FLOAT、REAL、MONEY或SMALLMONEY。这些算术运算符包括+、–、*、/和%。2/3/202339【例8-19】使用算术运算符2/3/202340【例8-20】使用数学函数2/3/202341【例8-21】使用字符串函数2/3/202342使用ALL或DISTINCT关键字在SELECT语句中，可以在SELECT子句中通过使用ALL或DISTINCT关键字控制查询结果集的显示样式。ALL关键字表示检索所有的数据，包括重复的数据行。DISTINCT关键字表示仅仅显示那些不重复的数据行，重复的数据行只是显示一次。由于ALL关键字是默认值，所以当没有显式使用ALL或DISTINCT关键字时，隐含着使用ALL关键字。需要明确的是，数据行是否重复，由SELECT子句中ALL或DISTINCT关键字后面的所有表达式项的组合决定。2/3/202343【例8-22】使用ALL或DISTINCT关键字2/3/202344本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023458.7排序在使用SELECT语句时，排序是一种常见的操作。排序是指按照指定的列或其他表达式对结果集进行排列顺序的方式。SELECT语句中的ORDERBY子句负责完成排序操作。在排序时既可以按照升序排列，也可以按照降序排列。关键字ASC表示升序，DESC表示降序，默认情况下是升序。2/3/202346【例8-23】使用排序子句2/3/202347【例8-24】使用多个列进行排序的检索操作2/3/202348【例8-25】使用TOP关键字2/3/202349本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023508.8使用WHERE子句选择数据在上一节的示例中，都是检索表中的全部数据行。但实际，在很多情况下，只需要表中的一部分数据。例如，在包含有数百万、数千万行数据的表中，可能永远也不会执行一个检索表中全部数据的语句，因为在一次查询中处理表中全部数据几乎是不现实的。在SELECT语句中，WHERE子句指定将要搜索的数据行的条件。也就是说，只有满足WHERE子句条件的数据行才会出现在结果集中。这些搜索条件可以分为简单搜索条件、模糊搜索条件和复合搜索条件。2/3/202351简单搜索条件在WHERE子句中，简单搜索条件是指使用比较运算符、范围、列表、合并以及取反等运算方式形成的搜索条件。比较运算符是搜索条件中最常使用的。WHERE子句的语法允许在列名称和列值之间使用比较运算符。例如，比较运算符>(大于)可以用来检索那些大于在WHERE子句中指定的列值的行。2/3/202352【例8-26】使用简单搜索条件2/3/202353模糊搜索条件在检索字符数据时，通常提供的检索条件是不十分准确的，例如这种搜索条件仅仅是包含、类似某种样式的字符。在WHERE子句中，可以使用LIKE关键字实现这种灵活的模糊搜索条件。LIKE关键字用于检索与特定字符串匹配的字符数据。LIKE关键字后面可以跟一个列值的一部分而不是一个完整的列值，从而形成LIKE子句。LIKE子句的语法形式如下：match_expression[NOT]LIKEpattern[ESCAPEescape_character]2/3/202354【例8-27】通配符的示例(1)LIKE'AB%'返回以“AB”开始的任意字符串。(2)LIKE'Ab%'返回以“Ab”开始的任意字符串。(3)LIKE'%abc'返回以“abc”结束的任意字符串。(4)LIKE'%abc%'返回包含“abc”的任意字符串。(5)LIKE'_ab'返回以“ab”结束的三个字符的字符串。(6)LIKE'[ACK]%'返回以“A”、“C”或“K”开始的任意字符串。(7)LIKE'[A-T]ing'返回四个字符的字符串，结尾是“ing”，首字符的范围从A到T。(8)LIKE'M[^c]%'返回以“M”开始且第二个字符不是“c”的任意长度的字符串。2/3/202355【例8-28】使用模糊搜索条件2/3/202356复合搜索条件在WHERE子句中可以使用逻辑运算符把若干个搜索条件合并起来，组成复杂的复合搜索条件。这些逻辑运算符包括AND，OR和NOT。AND运算符表示只有在所有条件都为真时，才返回真。OR运算符表示只要有一个条件为真时，就可以返回真。NOT运算符取相反。当在一个WHERE子句中，同时包含多个逻辑运算符时，其优先级从高到低依次是NOT、AND、OR。2/3/202357【例8-29】使用复合搜索条件2/3/202358本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023598.9聚合技术聚合技术是指对一组数据进行聚合运算得到聚合值的过程。在聚合运算中主要是使用聚合函数。在MicrosoftSQLServer2005系统中，一般情况下可以在3个地方使用聚合函数，即SELECT子句、COMPUTE子句和HAVING子句。本节主要讲述如何在SELECT子句和COMPUTE子句中使用聚合函数，有关HAVING子句使用聚合函数的内容在下一节介绍。2/3/202360SELECT子句中的聚合在SELECT子句中可以使用聚合函数进行运算，运算结果作为新列出现在结果集中。在聚合运算的表达式中，可以包括列名、常量以及由算术运算符连接起来的函数。2/3/202361【例8-30】在SELECT子句中使用聚合函数2/3/202362COMPUTE子句需要指出的是，当在SELECT子句中出现聚合函数时，结果集中的数据全是聚合值，没有明细值。这是使用SELECT子句计算聚合值的缺点。能否解决这种问题呢？答案是肯定的，解决问题的方法就是使用COMPUTE子句。COMPUTE子句使用聚合函数计算聚合值，并且可以依然保持原有的明细值，新的聚合值作为特殊的列出现。COMPUTE子句有两种形式，一种形式是不带BY子句，另一种形式是带BY子句。2/3/202363【例8-31】在COMPUTE子句中使用聚合函数2/3/202364本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023658.10分组技术聚合函数只能产生一个单一的汇总数据，使用GROUPBY子句，则可以生成分组的汇总数据。GROUPBY子句把数据组织起来分成组。一般情况下可以根据表中的某一列进行分组，通过使用聚合函数对每一个组可以产生聚合值。如果希望过滤某些分组，可以使用HAVING子句排。分组技术是指使用GROUPBY子句完成分组操作的技术。如果在GROUPBY子句中没有使用CUBE或ROLLUP关键字，那么表示这种分组技术是普通分组技术。2/3/202366普通分组技术GROUPBY子句、HAVING子句和聚合函数一起完成对每一个组生成一行和一个汇总值。在使用GROUPBY子句和HAVING子句的过程中，要求考虑下列一些条件：在SELECT子句中的非合计列必须出现在GROUPBY子句中。这是因为这些非合计列必须作为组出现，否则不能满足一组只能有一行汇总值的条件。在HAVING子句中的列只返回一个值。因为HAVING子句是作为GROUPBY子句的条件出现的，所以HAVING子句必须与GROUPBY子句同时出现，并且必须在GROUPBY子句之后出现。GROUPBY子句可以包括表达式。GROUPBYALL显示出所有的组，甚至那些不满足WHERE子句条件的组也显示出来。如果GROUPBYALL和HAVING子句同时出现，那么HAVING条件将覆盖ALL。2/3/202367【例8-32】使用GROUPBY子句进行分组2/3/202368使用ROLLUP或CUBE关键字在GROUPBY子句中，可以使用ROLLUP或CUBE关键字获得附加的分组数据，这些附加的分组数据是通过各组之间的组合得到的。实际上，使用CUBE关键字可以生成多维数据。下面，通过一个示例讲述这两个关键字的特点。2/3/202369【例8-33】使用ROLLUP或CUBE关键字2/3/202370本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023718.11连接技术前面介绍的检索操作都是从一个表中检索数据。在实际应用中，经常需要同时从两个表或两个以上表中检索数据，并且每一个表中的数据往往作为一个单独的列出现在结果集中。实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术，或者说连接技术是指对两个表或两个以上表中数据执行乘积运算的技术。在MicrosoftSQLServer2005系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。下面分别研究这些连接技术。2/3/202372交叉连接交叉连接也被称为笛卡尔乘积，返回两个表的乘积。在检索结果集中，包含了所连接的两个表中所有行的全部组合。例如，如果对A表和B表执行交叉连接，A表中有5行数据，B表中有12行数据，那么结果集中可以有60行数据。交叉连接使用CROSSJOIN关键字来创建。实际上，交叉连接的使用是比较少的，但是交叉连接是理解外连接和内连接的基础。2/3/202373【例8-34】交叉连接检索2/3/202374内连接内连接把两个表中的数据连接生成第三个表，在这个第三个表中仅包含那些满足连接条件的数据行。在内连接中，使用INNERJOIN连接运算符，并且使用ON关键字指定连接条件。内连接是一种常用的连接方式，如果在JOIN关键字前面没有明确指定连接类型，那么默认的连接类型是内连接。2/3/202375【例8-35】内连接检索2/3/202376外连接在外连接中，不仅包括那些满足条件的数据，而且某些表不满足条件的数据也会显示在结果集中。也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。这种连接形式在许多情况下是非常有用的，例如在连锁超市统计报表时，不仅要统计那些有销售量的超市和商品，而且还要统计那些没有销售量的超市和商品。需要注意的是，外连接只能用于两个表中。在MicrosoftSQLServer2005系统中，可以使用的3种外连接关键字，即LEFTOUTERJOIN、RIGHTOUTERJOIN和FULLOUTERJOIN。2/3/202377【例8-36】外连接检索2/3/202378本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023798.12子查询技术SELECT语句可以嵌套在其他许多语句中，这些语句包括SELECT、INSERT、UPDATE及DELETE等，这些嵌套的SELECT语句被称为子查询。当一个查询依赖于另外一个查询结果时，那么可以使用子查询。在某些查询中，查询语句比较复杂不容易理解，因此为了把这些复杂的查询语句分解成多个比较简单的查询语句形式时常使用子查询方式。使用子查询方式完成查询操作的技术是子查询技术。2/3/202380【例8-37】使用了子查询技术外连接检索2/3/202381本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023828.13集合运算技术查询语句的结果集往往是一个包含了多行数据集合。集合之间可以进行并、差、交等运算。在MicrosoftSQLServer2005系统中，两个查询语句之间也可以进行集合运算。其中，UNION运算符表示并集运算，EXCEPT运算符从左查询中返回右查询中没有找到的重复值，INTERSECT运算符则返回左右两个查询语句都包含的所有非重复值。需要注意的是，在集合运算时，所有查询语句中的列的数量和顺序必须相同，且数据类型必须兼容。2/3/202383【例8-38】执行集合运算2/3/202384本章内容8.1概述8.2插入数据8.3更新数据8.4删除数据8.5检索数据概述8.6使用SELECT子句检索数据8.7排序8.8使用WHERE子句选择数据8.9聚合技术8.10分组技术8.11连接技术8.12子查询技术8.13集合运算技术8.14公用表表达式8.15PIVOT和UNPIVOT8.16加密表中数据8.17上机练习8.18习题2/3/2023858.14公用表表达式在MicrosoftSQLServer2005系统中，可以使用公用表表达式(commontableexpression，CTE)。CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名