教学材料《数据库》-第6章

6.1数据更新

6.1.1向表中添加数据1.使用企业管理器向表中添加数据图6.1“编辑前200行”命令图6.2输入记录2.使用T-SQL语句向表中添加数据在Transact-SQL中使用INSERT语句插入数据记录，语法格式如下：INSERTINTOtable_name[column1,column2,…columnN]VALUES(value1,value2,…valueN)各项参数含义如下：Table_name：插入数据记录的表的名称。Column1，column2，…columnN:表示要插入数据的列，多列之间用逗隔开。该项内容可以省略，如果省略表示向数据表中插入整行数据。Value1，value2，…valueN：表示插入的数据记录的值。在使用INSERTVALUES语句的时候，要注意以下几条原则：

插入的新行数据必须满足被插入记录表的约束关系，否则该操作将不会成功；

使用VALUES子句指定需要插入的数据，其数据的类型和顺序必须和column1,…columnN中列的数据类型及顺序相对应，保持一致。

如果列存在默认值或允许空值，就可以在column1,column2,…columnN中忽略该列。SQL-Server将自动插入。6.1.1向表中添加数据6.1.2修改表中的数据1.使用企业管理器修改表中的数据图6.5定位到要更改的记录图6.6修改记录的内容2.使用T-SQL语句修改表中的数据

利用Transact-SQL中的UPDATE语句可以对数据记录进行修改。该语句可以一次更新一条或者多条数据记录，也可以一次更新数据表中的所有数据记录。

UPDATE语句的语法格式如下：

UPDATEtable_name

SETcolumn1=value1,

column2=value2,…

columnN=valueN

WHEREcondition各项参数含义如下：table_name：指定要修改数据的表的名称，一次只能修改一个表中的数据。

column1：更新数据的字段名称。

value：更新后的数据。WHEREcondition：条件表达式，指定修改满足条件表达式的数据记录。注意：如果省略WHEREcondition条件表达式，数据表中的所有记录将被修改。6.1.2修改表中的数据

随着数据库的使用和对数据的修改，表中存在着一些无用的数据，这些数据不仅占用空间，还会影响修改和查询的速度，所以要及时删除它们。

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

图6.12选择“删除”图6.13确认删除提示框6.1.3删除表中的数据

（1）使用DELETE语句删除数据记录

T-SQL语言提供了DELETE语句用来从表或视图中删除一行或多行记录。其语句语法如下：

DELETE[FROM]{table_name|view_name}WHEREsearch_conditions与UPDATE语句一样，若忽略WHERE子句，将删除表中所有的行。注意：

DELETE语句不能删除单个列的值（可用UPDATE），只能删除表中的整行记录；

同INSERT、UPDATE一样，从一个表中删除某行记录必须不违背数据库中的任何约束，否则SQLServer将拒绝执行该操作。在修改数据库时，头脑中应该始终不要忘记这个潜在问题。

使用DELETE语句仅删除行记录，不删除表本身（可用DROPTABLE语句）。（2）使用TRUNCATETABLE语句删除数据

在SQLServer中，除了使用DELETE语句删除数据记录之外，还可以使用TRUNCATETABLE语句删除数据。该语句的功能是删除数据表中的所有数据记录，因此也称为清除表数据语句。

TRUNCATETABLE语句语法格式如下：TRUNCATETABLEtable_name6.1.3删除表中的数据2.使用T-SQL语句删除表中的数据TRUNCATETABLE语句与DELETE语句的对比如下：TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同，二者均删除表中的全部行。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项，而TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少，删除数据不可恢复，而DELETE语句操作可以通过事务回滚，恢复删除的操作。

需要说明的是，如果要删除的表被其他表建立了外键引用，则无法删除该表的数据。如果要删除记录，则要先删除引用表的FOREIGNKEY引用。6.2数据的查询6.2.1SELECT查询语句SELECT[ALL|DISTINCT][TOPn]表达式列表

[INTO新表名]FROM基本表|视图名列表

[WHERE查询条件]

[GROUPBY分组列名表]

[HAVING逻辑表达式][ORDERBY排序列名表[ASC|DESC]]SELECT子句：指定由查询结果返回的列。ALL：表示返回查询的所有记录。DISTINCT：表示在返回查询结果时，去掉重复的数据记录。TOPn：返回查询结果集中的前N行。INTO子句：将查询结果存储到新表或视图中。FROM子句：用于指定数据源，即引用的列所在的表或视图。如果对象不止一个，那么它们之间必须用逗号分开。WHERE子句：指定用于限制返回的行的搜索条件。如果SELECT语句没有WHERE子句，DBMS假设目标表中的所有行都满足搜索条件。GROUPBY子句：指定用来放置输出行的组，并且如果SELECT子句表达式列表中包含聚合函数，则计算每组的汇总值。HAVING子句：指定组或聚合函数的搜索条件。HAVING通常与GROUPBY子句一起使用。ORDERBY子句：指定结果集的排序方式。ASC关键子表示升序排列结果，DESC关键字表示降序排列结果。如果没有指定任何一个关键字，那么ASC就是默认的关键字。如果没有ORDERBY子句，DBMS将根据输入表中数据的存放位置来显示数据。注意：在这一系列的子句中，SELECT子句和FROM子句是必需的，其他的子句根据需要都是可选的。在SELECT语句中，子句的顺序非常重要。即当执行SELECT语句时，DBMS的执行步骤如下：SELECT，INTO，FROM，WHERE，GROUPBY，HAVING，ORDERBY。

1、查询指定列2、查询所有列3、使用运算列4、改变列标题显示5、为结果集消除重复列6.2.2简单查询6、限制返回行数大多数查询都不希望得到表中所有的记录，而是一些满足条件的记录，这时就要用到WHERE子句。在SELECT查询语句中，WHERE子句是可选的，用于指定查询条件，使得SELECT语句的结果表中只包含那些满足查询条件的记录。使用WHERE子句时，WHERE子句必须紧跟在FROM子句后面。语法格式如下：

SELECT[ALL|DISTINCT][TOPn]表达式列表

FROM基本表|视图名列表

[WHERE条件表达式]

条件表达式是通过运算符连接起来的逻辑表达式，常用的运算符见表6.1。

6.1常用的运算符查询条件运算符比较运算符=，<，>，<=，>=，<>，!=，!<，!>范围运算符BETWEENAND，NOTBETWEENAND集合成员资格运算符IN，NOTIN字符匹配符LIKE，NOTLIKE空值ISNULL，ISNOTNULL逻辑运算符AND，OR，

NOT6.2.3单表有条件查询用户可以利用ORDERBY子句对查询结果按照一个或多个字段进行升序（ASC）或降序（DESC）排序，默认值为升序。语法格式如下：

SELECT[ALL|DISTINCT][TOPn]表达式列表

FROM基本表|视图名列表

[WHERE查询条件][ORDERBY排序列名表[ASC|DESC]]注意：当指定ASC选项时，将最后显示属性列为空值的记录。当指定DESC选项时，将最先显示属性列为空值的记录。如果有多个列名，各列在ORDERBY子句中的顺序决定了排序过程中的优先级，即查询结果首先以第一列值的顺序进行排序，而只有当第一列出现相同的值时，这些数据行再按第二列值的顺序进行排序，以此类推。Ntext、text、image或xml类型的列，不能用于ORDERBY子句。除非同时指定TOP，否则ORDERBY子句在视图、内联函数、派生表和子查询中无效。如果指定了SELECTDISTINCT，那么ORDERBY子句中的项就必须出现在选择列表中。ORDERBY子句一定要放在以上所有子句的最后。6.2.4排序子句聚合函数具体用法具体含义COUNTCOUNT([DISTINCT|ALL]*)统计元组个数COUNTCOUNT([DISTINCT|ALL]<列名>)统计一列中值的个数SUMSUM([DISTINCT|ALL]<列名>)计算一列值的总和（此列必须为数值型）AVGAVG([DISTINCT|ALL]<列名>)计算一列值的平均值（此列必须为数值型）MAXMAX([DISTINCT|ALL]<列名>)求一列值中的最大值MINMIN([DISTINCT|ALL]<列名>)求一列值中的最小值1、聚合函数

SQLServer的聚合函数是综合信息的统计函数，也称为聚集函数或集函数，是T-SQL所提供的系统函数，可以返回一列、几列或全部列的汇总数据，用于计数或统计。常用的聚合函数如表6.3所示。表6.3聚合函数的具体用法及含义6.2.5分组与汇总聚合函数对一组值执行计算，并返回单个值。如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为默认值），则表示不取消重复值。除了COUNT以外，聚合函数都会忽略空值。

2、分组汇总使用聚合函数只返回单个汇总，而在实际应用中，更多的是需要进行分组汇总数据，如汇总每个同学的平均分、总分等。使用GROUPBY子句可以进行分组汇总，为结果集中的每一行产生一个汇总值。GROUPBY子句与聚合函数有密切关系，在某种意义上说，如果没有聚合函数，GROUPBY子句也没有多大用处了。语法格式如下：SELECT[ALL|DISTINCT][TOPn]表达式列表

FROM基本表|视图名列表

[WHERE查询条件][GROUPBY分组列名表]6.2.5分组与汇总

3、分组筛选如果使用GROUPBY子句分组，则还可用HAVING子句对分组后的结果进行过滤筛选。HAVING子句通常与GROUPBY子句一起使用，用于指定组或合计的搜索条件，其作用与WHERE子句相似，二者的区别如下：（1）作用对象不同：WHERE子句作用于表和视图中的行，而HAVING子句作用于形成的组。WHERE子句限制HAVING子句限制查找的组。（2）执行顺序不同。若查询语句中同时有WHERE子句和HAVING子句，执行时，先去掉不满足WHERE条件的行，然后分组，分组后再去掉不满足HAVING条件的组。（3）WHERE子句中不能直接使用聚合函数，但HAVING子句的条件中可以包含聚合函数。

HAVING子句的语法如下：

SELECT[ALL|DISTINCT][TOPn]表达式列表

FROM基本表|视图名列表

[WHERE查询条件][GROUPBY分组列名表][HAVING逻辑表达式]6.2.5分组与汇总

4、明细汇总使用GROUPBY子句对查询数据进行分组汇总，为每一个组产生一个汇总结果，每个组只返回一行，无法看到详细信息。使用COMPUTE和COMPUTEBY子句既能够看到统计汇总的结果又能够浏览详细数据。语法如下：

SELECT[ALL|DISTINCT][TOPn]表达式列表

FROM基本表|视图名列表

[WHERE查询条件][ORDERBY排序列名表[ASC|DESC]][COMPUTE集函数[BY列名]]注意：COMPUTE[BY]子句不能与SELECTINTO子句一起使用；COMPUTE子句中的列必须在SELECT子句的字段列表中出现；COMPUTEBY表示按指定的列进行明细汇总，使用BY关键字时必须同时使用ORDER

BY子句，并且COMPUTEBY中出现的列必须具有与ORDERBY后出现的列相同的顺序，且不能跳过其中的列。6.2.5分组与汇总一个数据库中的多个表之间一般都存在某种内在联系，若一个查询同时涉及两个或两个以上的表，则称之为连接查询，这是关系数据库中最主要的查询。表与表之间的连接分为交叉连接（CrossJoin）、内连接（InnerJoin）、自连接（SelfJoin）、外连接（OuterJoin）。外连接又分为3种，即左外连接（LeftJoin）、右外连接（RightJoin）、全外连接（FullJoin）。连接查询的类型可以在SELECT语句的FROM子句中指定，也可以在WHERE子句中指定。

1、交叉连接格式

SELECT列名FROM表1CROSSJOIN表2

或SELECT列名FROM表1,表2功能其相当于广义笛卡儿积，不能加筛选条件，即不能带WHERE子句。结果表是第一个表的每行与第二个表的每行拼接后形成的表，结果表的行数等于两个表行数之积。6.2.6连接查询

2、内连接

内连接命令的一般格式如下：

SELECT列名FROM表1,表2WHERE连接条件表达式或SELECT列名FROM表1INNERJOIN表2ON连接条件表达式[WHERE条件表达式]

其中，第一种命令格式的连接类型在WHERE子句中指定，第二种命令格式的连接类型在FROM子句中指定。，此时为不等值连接。说明：（1）FROM后可跟多个表名，表名与别名之间用空格间隔。（2）典型的连接条件是指定每个表中要用于连接的列是在一个表中为外键的字段，而在另一个表中则为主键。（3）若不定义别名，表的别名默认为表名，定义别名后必须使用定义的别名。（4）若在输出列或条件表达式中出现两个表的公共字段，则在公共字段名前必须加别名。6.2.6连接查询

3、自连接连接操作不只是在不同的表之间进行，一张表内还可以进行自身连接操作，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。自连接的命令的一般格式如下：

SELECT列名FROM表1别名1,表1别名2

WHERE连接条件表达式6.2.6连接查询

4、外连接

外连接不但包含满足条件的行，还包括相应表中的所有行，只能用于两个表的连接。实际上，基本表的外连接操作可以分为以下3类。（1）左外连接①格式

Select列名from表1leftouterjoin表2on连接条件表达式②功能返回满足条件的行及左表1中所有的行。如果左表的某条记录在右表中没有匹配记录，则在查询结果中右表2的所有选择属性列用NULL填充。（2）右外连接①格式

Select列名from表1rightouterjoin表2on连接条件表达式②功能返回满足条件的行及右表2中所有的行。如果右表的某条记录在左表中没有匹配记录，则在查询结果中左表1的所有选择属性列用NULL填充。（3）全外连接①格式

Select列名from表1fullouterjoin表2on连接条件表达式②功能返回满足条件的行及左右表1、2中所有的行。当某条记录在另一表中没有匹配记录，则在查询结果中对应的选择属性列用NULL填充。其中，OUTER关键字均可忽略。6.2.6连接查询

所谓嵌套查询指的是在一个SELECT查询语句中包含另一个（或多个）SELECT查询语句。其中，外层的SELECT查询语句叫主查询，内层的SELECT查询语句叫子查询。通常，当想要根据由另一个查询中的数据来设置列的值时，就要用到子查询。嵌套查询的执行过程是：首先执行子查询语句，得到的子查询结果集传递给外层主查询语句，作为外层主查询的查询项或查询条件使用。嵌套查询又可称为子查询。关于嵌套查询的注意事项：子查询可以嵌套多层。子查询需用圆括号括起来。子查询中不能使用COMPUTE[BY]和INTO子句。子查询的SELECT语句中不能使用image、text或ntext数据类型。1、单列单值嵌套查询6.2.7嵌套查询2、单列多值嵌套查询3