SQL Server 2000 结构化查询语言详解
来源：互联网 发布：单片机种类及选型 编辑：程序博客网 时间：2024/05/01 10:03
SQL SERVER 2000 结构化查询语言详解
SQL提供了一些列的聚集函数对表中数据进行统计分析。在SQL中，可以使用分组命令将列中的数据按照一定的条件进行分组。在很多情况下，聚集函数与分组命令同时运用，即只将聚合函数作用到满足条件的分组上。
8.1 聚合分析的基本概念
聚合分析实际上就是对一组数据的统计分析，在SQL中，可以通过使用聚合函数来实现。
在访问数据库时，经常需要对表中的某列数据进行统计分析，如求其最大值、最小值、平均值等。所有这些针对表中一列或者多列数据的分析就称为聚合分析。
在SQL中，可以使用聚合函数快速实现数据的聚合分析。与第7章介绍的SQL中的函数不同，聚合函数是对列中的一系列数据进行处理，返回单个统计值；而前面的函数则是对列中的单个数据进行处理。
SQL提供的聚合函数有求和函数SUM()、最大值函数MAX()、最小值函数MIN()、平均值函数AVG()、计数函数COUNT()等，如表8-1所示。
表8-1 聚合函数及其功能
函 数 名 称 | 函 数 功 能 |
SUM() | 返回选取结果集中所有值的总和 |
MAX() | 返回选取结果集中所有值的最大值 |
MIN() | 返回选取结果集中所有值的最小值 |
AVG() | 返回选取结果集中所有值的平均值 |
COUNT() | 返回选取结果集中行的数目 |
8.2 聚合函数的应用
聚合函数在数据库数据的查询分析中，应用十分广泛。本节将分别对各聚合函数的应用进行说明。
求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下。
SELECT SUM(column_name)
FROM table_name
说明：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。
实例1 SUM函数的使用
从TEACHER表中查询所有男教师的工资总数。TEACHER表的结构和数据可参见
SELECT SUM(SAL) AS BOYSAL
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.1所示。
图8.1 TEACHER表中所有男教师的工资总数
实例2 SUM函数对NULL值的处理
从TEACHER表中查询年龄大于40岁的教师的工资总数。实例代码：
SELECT SUM(SAL) AS OLDSAL
FROM TEACHER
WHERE AGE>=40
运行结果如图8.2所示。
图8.2 TEACHER表中所有年龄大于40岁的教师的工资总数
当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该值。
COUNT()函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。两种使用形式如下。
1．使用COUNT(*)函数对表中的行数计数
COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。
实例3 COUNT(*)函数的使用
查询TEACHER表中的所有记录的行数。实例代码：
SELECT COUNT(*) AS TOTALITEM
FROM TEACHER
运行结果如图8.3所示。
图8.3 使用COUNT(*)函数对表中的行数计数
在该例中，SELECT语句中没有WHERE子句，那么认为表中的所有行都满足SELECT语句，所以SELECT语句将返回表中所有行的计数，结果与
如果DBMS在其系统表中存储了表的行数，COUNT(*)将很快地返回表的行数，因为这时，DBMS不必从头到尾读取表，并对物理表中的行计数，而直接从系统表中提取行的计数。而如果DBMS没有在系统表存储表的行数，将具有NOT NULL约束的列作为参数，使用COUNT( )函数，则可能更快地对表行计数。
COUNT(*)函数将准确地返回表中的总行数，而仅当COUNT()函数的参数列没有NULL值时，才返回表中正确的行计数，所以仅当受NOT NULL限制的列作为参数时，才可使用COUNT( )函数代替COUNT(*)函数。 |
2．使用COUNT( )函数对一列中的数据计数
COUNT( )函数可用于对一列中的数据值计数。与忽略了所有列的COUNT(*)函数不同，COUNT( )函数逐一检查一列（或多列）中的值，并对那些值不是NULL的行计数。
实例4 查询多列中所有记录的行数
查询TEACHER表中的TNO列、TNAME列以及SAL列中包含的所有数据行数。实例代码：
SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,
COUNT(SAL) AS TOTAL_SAL
FROM TEACHER
运行结果如图8.4所示。
图8.4 使用COUNT( )函数对一列中的数据计数
可见，TNO列与TNAME列由于其中不含有NULL值，所以其计数与使用COUNT(*)函数对TEACHER表中的记录计数结果相一致，而SAL列由于其中有两行数据为NULL，所以这两列没有被计入在内，计数结果也就是8。
3．使用COUNT( )函数对多列中的数据计数
COUNT( )函数不仅可用于对一列中的数据值计数，也可以对多列中的数据值计数。如果对多列计数，则需要将要计数的多列通过连接符连接后，作为COUNT( )函数的参数。下面将结合具体的多列计数的实例，说明其使用过程。
关于如何使用连接符连接多列可参见本书的7.2节。 |
实例5 使用COUNT( )函数对多列中的数据计数
统计TEACHER表中的TNO列、TNAME列和SAL列中分别包含的数据行数，以及TNO列和TNAME列、TNAME列和SAL列一起包含的数据行数。实例代码：
SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,
COUNT(SAL) AS TOTAL_SAL,
COUNT(CAST(TNO AS VARCHAR(5)) + TNAME) AS T_NONAME,
COUNT(TNAME + CAST(SAL AS VARCHAR(5))) AS T_NAMESAL
FROM TEACHER
运行结果如图8.5所示。
图8.5 使用COUNT( )函数对多列中的数据计数
在进行两列的连接时，由于它们的数据类型不一致，因此要使用CAST表达式将它们转换成相同的数据类型。
在
COUNT( )函数只对那些传递到函数中的参数不是NULL的行计数。 |
4．使用COUNT函数对满足某种条件的记录计数
也可以在SELECT语句中添加一些子句约束来指定返回记录的个数。
实例6 使用COUNT函数对满足某种条件的记录计数
查询TEACHER表中女教师记录的数目。实例代码：
SELECT COUNT(*) AS TOTALWOMEN
FROM TEACHER
WHERE TSEX='女'
运行结果如图8.6所示。
图8.6 使用COUNT函数对满足某种条件的记录计数
这时结果为6而不是前面的所有记录10。之所以可以通过WHERE子句定义COUNT()函数的计数条件，这与SELECT语句各个子句的执行顺序是分不开的。前面已经讲过，DBMS首先执行FROM子句，而后是WHERE子句，最后是SELECT子句。所以COUNT()函数只能用于满足WHERE子句定义的查询条件的记录。没有包括在WHERE子句的查询结果中的记录，都不符合COUNT()函数。
当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。语法如下。
SELECT MAX (column_name) / MIN (column_name)
FROM table_name
说明：列column_name中的数据可以是数值、字符串或是日期时间数据类型。MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。
实例7 MAX()函数的使用
查询TEACHER表中教师的最大年龄。实例代码：
SELECT MAX (AGE) AS MAXAGE
FROM TEACHER
运行结果如图8.7所示。
图8.7 TEACHER表中教师的最大年龄
然而，在实际应用中得到这个结果并不是特别有用，因为经常想要获得的信息是具有最大年龄的教师的教工号、姓名、性别等信息。
然而SQL不支持如下的SELECT语句。
SELECT TNAME, DNAME, TSEX, MAX (AGE)
FROM TEACHER
因为聚合函数处理的是数据组，在本例中，MAX函数将整个TEACHER表看成一组，而TNAME、DNAME和TSEX的数据都没有进行任何分组，因此SELECT语句没有逻辑意义。同样的道理，下面的代码也是无效的。
SELECT TNAME, DNAME, TSEX,SAL ,AGE
FROM TEACHER
WHERE AGE＝MAX (AGE)
解决这个问题的方法，就是在WHERE子句中使用子查询来返回最大值，然后再基于这个返回的最大值，查询相关信息。
实例8 在WHERE子句中使用子查询返回最大值
查询TEACHER表中年纪最大的教师的教工号、姓名、性别等信息。
实例代码：
SELECT TNAME, DNAME, TSEX, SAL, AGE
FROM TEACHER
WHERE AGE＝(SELECT MAX (AGE) FROM TEACHER)
运行结果如图8.8所示。
图8.8 在WHERE子句中使用子查询返回最大值
MAX()和MIN()函数不仅可以作用于数值型数据，也可以作用于字符串或是日期时间数据类型的数据。
实例9 MAX()函数用于字符型数据
如下面代码：
SELECT MAX (TNAME) AS MAXNAME
FROM TEACHER
运行结果如图8.9所示。
图8.9 在字符串数据类型中使用MAX的结果
可见，对于字符串也可以求其最大值。
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。 |
当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小，如下面的实例。
实例10 MAX()、MIN()函数用于时间型数据
从COURSE表中查询最早和最晚考试课程的考试时间。其中COURSE表的结构和数据可参见本书6.1节的表6-1。实例代码：
SELECT MIN (CTEST) AS EARLY_DATE,
MAX (CTEST) AS LATE_DATE
FROM COURSE
运行结果如图8.10所示。
图8.10 COURSE表中最早和最晚考试课程的考试时间
可见，返回结果的数据类型与该列定义的数据类型相同。
确定列中的最大值（最小值）时，MAX( )（MIN( )）函数忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则MAX( )/MIN( )函数将返回NULL值。 |
函数AVG()用于计算一列中数据值的平均值。语法如下。
SELECT AVG (column_name)
FROM table_name
说明：AVG()函数的执行过程实际上是将一列中的值加起来，再将其和除以非NULL值的数目。所以，与SUM( )函数一样，AVG()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。
实例11 AVG()函数的应用
从TEACHER表中查询所有教师的平均年龄。实例代码：
SELECT AVG (AGE) AS AVG_AGE
FROM TEACHER
运行结果如图8.11所示。
图8.11 TEACHER表中所有教师的平均年龄
在计算平均值时，AVG()函数将忽略NULL值。因此，如果要计算平均值的列中有NULL值，计算均值时，要特别注意。
实例12 AVG()函数对NULL值的处理
从TEACHER表中查询所有教师的平均工资。实例代码：
SELECT AVG (SAL) AS AVG_AGE1，SUM(SAL)/COUNT(*) AS AVG_AGE2,
SUM(SAL)/COUNT(SAL) AS AVG_AGE3
FROM TEACHER
运行结果如图8.12所示。
图8.12 TEACHER表中所有教师的平均工资
可以发现得到了不同的结果。实际上，“AVG (SAL)”与“SUM(SAL)/COUNT(SAL)”语句是等价的。因为AVG(SAL)语句的执行过程实际上是将SAL列中的值加起来，再将其和（也就等价于SUM(SAL)）除以非NULL值的数目（也就等价于COUNT(SAL)）。而语句“SUM(SAL)/COUNT(*)”则不然，因为COUNT(*)返回的是表中所有记录的个数，而不管SAL列中的数值是否为NULL。
AVG()函数在计算一列的平均值时，忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则AVG()函数将返回NULL值。 |
如果不想对列中的所有值求平均，则可在WHERE子句中使用搜索条件来限制用于计算均值的行。
实例13 在WHERE子句中使用搜索条件来限制用于计算均值的行
从TEACHER表中查询所有计算机系教师的平均年龄。实例代码：
SELECT AVG (AGE) AS AVGCOMPUTER_AGE
FROM TEACHER
WHERE DNAME = '计算机'
运行结果如图8.13所示。
图8.13 TEACHER表中所有计算机系教师的平均年龄
当执行SELECT语句时，DBMS将表中的每行对WHERE子句中的搜索条件“DNAME = '计算机'”求值。只有那些搜索条件为True时，行中的AGE值才传到均值函数AVG (AGE)中。
当然，除了显示表中某列的平均值，还可用AVG()函数作为WHERE子句的一部分。与前面介绍的MAX()函数一样，不能直接用于WHERE子句，必须以子查询的形式。
实例14 AVG()函数作为WHERE子句中搜索条件的一部分
从TEACHER表中查询所有年龄高于平均年龄的教师的信息。实例代码：
SELECT *
FROM TEACHER
WHERE AGE >= (SELECT AVG (AGE) FROM TEACHER)
ORDER BY AGE
运行结果如图8.14所示。
图8.14 TEACHER表中所有年龄高于平均年龄的教师的信息
前面介绍的5种聚合函数，可以作用于所选列中的所有数据（不管列中的数据是否有重置），也可以只对列中的非重值进行处理，即把重复的值只取一次进行聚合分析。当然，对于MAX()/MIN()函数来讲，重值处理意义不大。
可以使用ALL关键字指明对所选列中的所有数据进行处理，使用DISTINCT关键字指明对所选列中的非重值数据进行处理。以AVG()函数为例，语法如下。
SELECT AVG ([ALL/DISTINCT] column_name)
FROM table_name
说明：[ALL/DISTINCT]在缺省状态下，默认是ALL关键字，即不管是否有重值，处理所有数据。其他聚合函数的用法与此相同。
Microsoft Access数据库不支持在聚合函数中使用DISTINCT关键字。 |
实例15 聚合分析的重值处理
从TEACHER表中查询工资SAL列中存在的所有记录数。实例代码：
SELECT COUNT(ALL SAL) AS ALLSAL_COUNT
FROM TEACHER
运行结果如图8.15所示。
图8.15 TEACHER表中工资SAL列中存在的所有记录数
当然，在代码中去除ALL关键字，也可以得到相同的结果。而如果从TEACHER表中，查询工资SAL列中存在的不同记录的数目，可采用如下代码。
SELECT COUNT(DISTINCT SAL) AS DISTINCTSAL_COUNT
FROM TEACHER
运行结果如图8.16所示。
图8.16 TEACHER表中SAL列存在的不同记录的数目
对比两个结果，使用DISTINCT关键字后，工资SAL列中的重值并没有列入统计的范围之内。另外还要强调一点，在所有5种聚合函数中，除了COUNT(*)函数外，其他的函数在计算过程中都忽略NULL值，即把NULL值的行排除在外，不进行分析。
前面介绍的实例中，聚合函数都是单独使用的。聚合函数也可以组合使用，即在一条SELECT语句中，可以使用多个聚合函数。
实例16 使用多个聚合函数
如下面的代码：
SELECT COUNT(*) AS num_items,
MAX(SAL) AS max_sal,
Min(AGE) AS min_age,
SUM(SAL)/COUNT(SAL) AS avg_sal,
AVG(DISTINCT SAL) AS disavg_sal
FROM TEACHER
运行结果如图8.17所示。
图8.17 聚合函数的组合应用
该例在一条SELECT语句中，几乎用到了所有的聚合函数。其中num_items为TEACHER表所有记录的条目，max_sal为TEACHER表中记录的最高工资，min_age为TEACHER表中记录的最小年龄，avg_sal为所有TEACHER表中的工资记录的平均值，disavg_sal为TEACHER表中所有不同的工资记录的平均值。
8.3 组合查询
在实际SQL应用中，经常需要进行分组聚合，即将查询对象按一定条件分组，然后对每一个组进行聚合分析。
创建分组是通过GROUP BY子句实现的。与WHERE子句不同，GROUP BY子句用于归纳信息类型，以汇总相关数据。而为什么要使用GROUP BY子句创建分组呢？可通过下面这个简单例子来说明。
实例17 单一分组的查询
假如要从TEACHER表中查询所有男教师的平均工资，用前面介绍的聚合函数AVG(),实现代码如下：
SELECT AVG(SAL) AS boyavg_sal
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.18所示。
图8.18 TEACHER表中查询所有男教师的平均工资
而如果同时需要查询所有女教师的平均工资，该如何处理呢？显然，采用上述方法只能在WHERE子句中改变查询条件，重新查询。而如果要在一次查询中，同时得到二者的查询结果，就需要以性别为基准，将表中的所有数据记录分组，即男教师组和女教师组，并分别对两组数据进行分析，即计算工资（SAL列）的平均值。
实现上述功能，就需要使用分组子句GROUP BY。包括GROUP BY子句的查询就称为组合查询。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
说明：GROUP BY子句依据column列里的数据对行进行分组，即具有相同的值的行被划为一组。它一般与聚合函数同时使用。当然，这里的SUM()函数也可以是其他聚合函数。所有的组合列（GROUP BY子句中列出的列）必须是来自FROM子句列出的表，不能根据实际值、聚合函数结果或者其他表达式计算的值来对行分组。
实例18 GROUP BY子句分组查询
从TEACHER表中查询所有男教师的平均工资和所有女教师的平均工资，实现代码如下。
SELECT TSEX+'教师'AS TEACHER, AVG(SAL) AS avg_sal
FROM TEACHER
GROUP BY TSEX
运行结果如图8.19所示。
图8.19 TEACHER表中所有男教师和所有女教师的平均工资
下面分析一下DBMS执行该实例的步骤。
上节介绍的GROUP BY子句进行组合查询，在GROUP BY子句中只有一列，它是组合查询的最简单形式。如果表中的行组依赖于多列，只要在查询的GROUP BY子句中，列出定义组所需的所有列即可。
实例19 GROUP BY子句根据多列组合行
从TEACHER表中查询各个系男教师和女教师的人数。实现代码：
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX
ORDER BY DNAME
运行结果如图8.20所示。
图8.20 TEACHER表中各系男教师和女教师的人数
从结果中可以发现，只有计算机系列出了男教师和女教师的人数。而别的系，只列出了一个值，这是因为，在TEACHER表中，这些系中的教师只有一种性别，如生物系只有两个女教师，而没有男教师，系统就认为该行记录为NULL，所以生物系的男教师的人数记录就不包含在结果表中。
在使用GROUP BY子句根据多列组合行时，可以在GROUP BY子句中使用ROLLUP运算符和CUBE运算符，扩展查询结果。两者的主要不同在于，CUBE运算符扩展的信息要比ROLLUP运算符多，下面结合具体的实例讲解二者的使用及区别。
1．ROLLUP运算符的使用
实例20 使用ROLLUP运算符扩查询
使用ROLLUP运算符扩展实例19查询结果。实现代码：
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH ROLLUP
ORDER BY DNAME
运行结果如图8.21所示。
图8.21 ROLLUP运算符扩展的组合查询结果
与实例19相比，增加了7行数据。其中一行（结果中的第1行）为TEACHER表中所有教师的总人数，另外还分别为各系（DNAME）分组增加了一行（结果中的第3、5、8、10、12、14行），统计了各系教师的总人数。
实例21 改变GROUP BY子句中列的排列顺序对ROLLUP运算符的影响
如果改变GROUP BY子句中列的排列顺序，使用ROLLUP运算符会得到不同的结果，如下面的代码：
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY TSEX, DNAME WITH ROLLUP
ORDER BY DNAME
运行结果如图8.22所示。
图8.22 依据系名排序后的结果
与
2．CUBE运算符的使用
实例22 使用CUBE运算符扩展查询
使用CUBE运算符扩展实例19查询结果。实现代码：
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH CUBE
ORDER BY DNAME
运行结果如图8.23所示。
图8.23 使用CUBE运算符扩展的组合查询结果
从结果中可以发现，通过使用CUBE运算符，结果集中除了包含多列组合（DNAME和TSEX）的统计结果外，还包含了整表（TEACHER表）的统计结果和各单列（DNAME、TSEX）的统计结果。
当GROUP BY子句中用于分组的列中出现NULL值时，将如何分组呢？按照前面的介绍，NULL不等于NULL（在WHERE子句中有过介绍）。然而，在GROUP BY子句中，却将所有的NULL值分在同一组，即认为它们是“相等”的。
实例23 GROUP BY子句中的NULL值处理
从TEACHER表中查询所有的工资数及各工资的人数。实现代码：
SELECT SAL,COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY SAL
ORDER BY SAL
运行结果如图8.24所示。
图8.24 TEACHER表中所有的工资数及各工资的人数
可见，SAL列中的两行NULL值被归为了一组。
GROUP BY子句分组，只是简单地依据所选列的数据进行分组，将该列具有相同值的行划为一组。而实际应用中，往往还需要删除那些不能满足条件的行组，为了实现这个功能，SQL提供了HAVING子句。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
HAVING SUM(column) condition value
说明：HAVING通常与GROUP BY子句同时使用。当然，语法中的SUM()函数也可以是其他任何聚合函数。DBMS将HAVING子句中的搜索条件应用于GROUP BY子句产生的行组，如果行组不满足搜索条件，就将其从结果表中删除。
前面介绍的有关WHERE子句的所有操作，如使用连接符、通配符、函数等，在HAVING子句中都可以使用。 |
实例24 HAVING子句的应用
从TEACHER表中查询至少有两位教师的系及教师人数。实现代码：
SELECT DNAME, COUNT(*) AS num_teacher
FROM TEACHER
GROUP BY DNAME
HAVING COUNT(*)>=2
运行结果如图8.25所示。
图8.25 TEACHER表中至少有两位教师的系及教师人数
8.3.6 HA
8.3.7 VING*+
8.3.8 +4子句与WHERE子句
HAVING子句和WHERE子句的相似之处在于，它也定义搜索条件。但与WHERE子句不同，HAVING子句与组有关，而不是与单个的行有关。
在SELECT语句中，WHERE和HAVING子句的执行顺序不同。在本书的
下面通过几个实例讲解HAVING子句和WHERE子句的不同作用。
实例25 HAVING子句和WHERE子句的不同作用
从TEACHER表中查询有女教师的系及拥有的女教师数量。实现代码：
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
运行结果如图8.26所示。
图8.26 TEACHER表中具有女教师的系及拥有的女教师数量
可见得到了3个系，与TEACHER表中数据相吻合。如果在上例中不使用WHERE子句，而是使用HAVING子句，教师限制为女教师，如下面的代码：
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
GROUP BY DNAME
HAVING TSEX='女'
执行该代码，系统会给出以下出错提示信息。
Column 'TEACHER.TSEX' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
不能把单个的TSEX的值应用于组，包括在HAVING子句中的列必须是组列。因此，在这种情况下，WHERE子句就不可能用HAVING子句代替。
在数据的分组聚合分析中，HAVING子句与WHERE子句也可以共存。WHERE子句在分组之前过滤数据，而HAVING子句则过滤分组后的数据。
实例26 HAVING子句与WHERE子句联合使用
查询至少有两名女教师的系及拥有的女教师数量。实现代码：
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
运行结果如图8.27所示。
图8.27 TEACHER表中至少有两名女教师的系及拥有的女教师数量
这里通过HAVING子句对分组结果进行搜索，去除了不满足搜索条件（即只有一个教师的经济管理系）的行。
通常情况下，HAVING子句都与GROUP BY子句一起使用，这样就可以聚合相关数据，然后筛选这些数据，以进一步细化搜索。然而，如果没有GROUP BY子句，HAVING子句也可以单独使用。
实例27 HAVING子句的单独使用
如下面的代码：
SELECT COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
HAVING COUNT(TSEX)>4
运行结果如图8.28所示。
图8.28 单独使用HAVING子句的查询结果
上述代码实现的功能实际上是从教师表中查询所有女教师的数量，如果女教师的数量大于4，则将其作为查询结果，而如果数量少于或者等于4，那么查询结果将为空值。当然，这种不使用GROUP BY子句而使用HAVING子句的情况，在实际应用中很少用到。
至此，SELECT语句中的所有子句都介绍完了，它们在SELECT查询语句中的排列顺序及主要作用如表8-2所示。
表8-2 SELECT查询语句及其所有子句
顺 序 号 | 子句关键词 | 子 句 功 能 |
1 | SELECT | 从指定表中取出指定的列的数据 |
2 | FROM | 指定要查询操作的表 |
3 | WHERE | 用来规定一种选择查询的标准 |
4 | GROUP BY | 对结果集进行分组，常与聚合函数一起使用 |
5 | HAVING | 返回选取的结果集中行的数目 |
6 | ORDER BY | 指定分组的搜寻条件 |
如果在同一个SELECT查询语句中，用到了表8-2所示的一些查询子句，则各查询子句的排列就依照它们的顺序号由低到高的顺序。因此，完整的SELECT查询语句可以表示为：
SELECT select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
其中[ ]中的部分为可选项。
实例28 在SELECT语句中综合使用查询子句
从TEACHER表中查询至少有两名女教师的系及拥有的女教师数量，并按女教师的数量升序的顺序排列结果。实现代码：
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
ORDER BY num_girl
运行结果如图8.29所示。
图8.29 对图8.27中按数量升序的排列结果
上一章介绍了如何向表中插入数据，本章将主要讨论通过UPDATE 和DELETE语句更新和删除表中的数据。
12.1 更新表中的数据
在SQL中，可以使用UPDATE语句来修改表中的现有数据，也称为更新数据库表中的数据。
使用UPDATE语句，一次可以改变数据库表中单行上的值，也可以改变表中选定的一些行上的多列数据，当然也可以更新所有行的数据。语法如下。
UPDATE table_name
SET column1=value1,
column2=value2,
……
WHERE search_condition
说明：UPDATE语句实际上主要包括三部分：要更新的表名、列名和它们的新值以及选择更新行的搜索条件。
UPDATE语句中的WHERE子句确定表（table_name）中要修改的数据行。SET子句提供要修改的列值的清单。简单来讲，UPDATE语句执行时，一次一行地处理整个表，更新那些搜索为True（即WHERE子句为True）的行上的SET子句列出的列值。UPDATE语句把那些搜索条件为False或NULL的行中的数据保持不变。
由UPDATE的语法可见，SET子句包括了列赋值表达式的清单。表列名在赋值清单中作为赋值目标只能出现一次。并且表达式必须要产生一个与要赋值的列的数据类型相兼容的值。
一定要注意不要忽略WHERE子句，如果没有指明WHERE子句，则数据库表中所有行的记录都将被更新。 |
使用UPDATE语句可以更新单列数据也可以更新多列数据。下面通过几个实例介绍一下使用UPDATE语句更新列值的操作。
实例1 复制TEACHER表数据到新表
本节所有的更新实例均是基于TEACHER表（参见本书
SELECT *
INTO New_Teacher
FROM TEACHER
查看表New_Teacher的数据。
SELECT *
FROM New_Teacher
运行结果如图12.1所示。
图12.1 New_Teacher表中的记录
1．更新单列数据
实例2 更新单列数据
“三八”节到了，该月为所有女教师的工资增加100元过节费。实例代码：
UPDATE New_Teacher
SET SAL=SAL+100
WHERE TSEX='女'
查看表New_Teacher的数据。
SELECT *
FROM New_Teacher
运行结果如图12.2所示。
图12.2 女教师的工资增加100元后的纪录
可见，表中所有女教师SAL列的数据都增加了100，而其他数据都没有改变。需要注意的是教工号为8和10的两个女教师的SAL列数据仍为NULL，这是因为NULL值参与任何数学运算其结果仍为NULL，因此“NULL+100”结果仍为NULL。
2．多列数据的更新
使用UPDATE语句一次也可以更新多列数据，只要写入SET子句的更新清单中即可。
实例3 多列数据的更新
新的一年开始了，所有教师的年龄增加了1岁，同时给所有教师的工资增加10%。实例代码如下。
（1）先将女教师的工资恢复到原来值。
UPDATE New_Teacher
SET SAL=SAL-100
WHERE TSEX=’女’
（2）更新所有教师的年龄和工资信息。
UPDATE New_Teacher
SET AGE = AGE +1,
SAL= SAL*1.1
（3）查看表New_Teacher的数据。
SELECT *
FROM New_Teacher
运行结果如图12.3所示。
可见，所有的SAL列和AGE列的数据都发生了改变。
图12.3 更新表New_Teacher后的纪录
3．通过更新删除列中的数据
有时候需要通过数据的更新操作，实现删除某列数据的目的。通常的做法是把该列的值设置为NULL。当然，这样做的前提是该列允许为空值，即没有非空约束。
实例4 通过更新删除列中的数据
生物系教师的工资进行了调整，目前还不知道调整后的具体工资，因此需要把其原有的工资信息删除。实例代码：
UPDATE New_Teacher
SET SAL = NULL
WHERE DNAME ='生物'
查看表New_Teacher的数据。
SELECT *
FROM New_Teacher
运行结果如图12.4所示。
图12.4 将生物系教师原有的工资信息删除
可见，生物系所有的教师的SAL列值都变为NULL。
在执行一条新的UPDATE语句（特别是带有复杂搜索条件的）之前，最好应先确定一些更新操作所影响的数据行数。 |
例如上例，执行数据更新前，确定一些更新操作影响的行数，即所有生物系教师的记录数，代码如下。
SELECT COUNT(*)
FROM New_Teacher
WHERE DNAME ='生物'
运行结果如图12.5所示。
图12.5 生物系教师的记录数
由此可知，UPDATE语句将更新New_Teacher表中的两行数据，这样有利于减少更新操作的错误概率。
在UPDATE语句的WHERE子句中，可以使用比较运算符（=、>、<、>=、<=、<>）来确定搜索条件，同样也可以使用子查询来确定需要更新的行。下面就结合具体的实例说明其应用。
实例5 复制TEACHER表数据到新表
同样，为了不影响TEACHER表中的原始数据，需要将TEACHER表中的数据复制到表New_Teacher2表中。这样，直接操作New_Teacher2表就可以了。代码如下。
SELECT *
INTO New_Teacher2
FROM TEACHER
查看表New_Teacher2的数据。
SELECT * FROM New_Teacher2
运行结果如图12.6所示。
图12.6 将TEACHER表中的数据复制到表New_Teacher2表
实例6 利用子查询更新多行的值
在NEW_ Teacher2表中，当男教师的工资少于所有男教师的平均工资时，将该男教师的工资提高5%。实例代码：
UPDATE NEW_Teacher2
SET SAL = SAL +SAL * 0.05
WHERE TSEX='男'
AND SAL < (SELECT avg(SAL)
FROM NEW_Teacher2
WHERE TSEX='男')
查看更新后表New_TEACHER2中的数据。
SELECT * FROM New_TEACHER2
运行结果如图12.7所示。
图12.7 将工资少于所有男教师的平均工资的男教师的工资提高5%
可以发现，教工号为1、3、6的男教师的SAL列数据进行了更新，其余数据均没有发生变化。
特别需要强调一点，使用子查询选择要更新的行时，可以在子查询中引用正在更新的表，如本例中，子查询语句就引用了表NEW_Teacher2。这时，子查询引用的表中的数据都认为是更新前的数据。
如在该实例中，代码执行时，DBMS首先读出NEW_Teacher2表中的第一行记录，经验证其满足WHERE子句中的搜索条件，就将其SAL列中的数据更新，即由800更新为840。而后系统从NEW_Teacher2表取出第二条记录，此时在WHERE子句中执行子查询即使用AVG聚合函数，其中要用到第一条记录的SAL列中的数据，这时采用的是更新前的数据，即仍然为800。也就是说，在整个UPDATE语句执行期间，子查询中的AVG函数得到的值始终是相同的。
虽然UPDATE语句只允许改变单个表中的列值，但在UPDATE语句的WHERE子句中可以使用任何可用的表。因此可根据别的表中的相关值来决定目标表中要更新的数据行。
实例7 依据外表值更新数据
在NEW_Teacher2表中，将所授课程学时超过40个（COURSE表），或者是在STUDENT表中其所授课程的成绩存在优秀（超过85分）的教师发放100元的奖金。实例代码：
UPDATE NEW_Teacher2
SET SAL = SAL +100
WHERE CNO IN (SELECT CNO FROM COURSE
WHERE CNO = NEW_Teacher2.CNO
AND CTIME >= 40)
OR CNO IN (SELECT CNO FROM STUDENT
WHERE CNO = NEW_Teacher2.CNO
GROUP BY CNO
HAVING MAX(MARK) >= 85)
查看更新后表New_TEACHER2中的数据。
SELECT * FROM New_TEACHER2
运行结果如图12.8所示。
图12.8 依据外表值更新数据
对比更新前后NEW_Teacher2表中的数据，共有5行记录（1、3、4、6、9）的SAL列数据进行了更新。
有时对数据库表中的数据完成某种更新操作，需要使用UPDATE语句分几步完成，这时特别需要注意数据更新的顺序。
实例8 分步更新表
在NEW_Teacher2表中，工资超过1500的缴纳10%所得税，超过800的缴纳5%所得税，其余的不缴税。重新计算缴税后各教师的工资。实例代码：
UPDATE NEW_Teacher2
SET SAL = SAL * 0.95
WHERE SAL <= 1500
AND SAL >800
/*继续更新大于1500的工资*/
UPDATE NEW_Teacher2
SET SAL = SAL * 0.9
WHERE SAL >1500
查看更新后表NEW_Teacher2的数据。
SELECT * FROM NEW_Teacher2
运行结果如图12.9所示。
图12.9 分步更新NEW_Teacher2表后的纪录
上面的两个UPDATE语句的执行顺序是正确的，而如果将两个UPDATE语句的执行顺序颠倒，则可能会造成执行结果错误，其中原因读者可自行分析
12.2 删除表中的数据
在SQL中，可以使用DELETE语句从数据库删除数据。DELETE语句只能从表中删除数据，不能删除表定义本身。
使用DELETE语句从数据库删除数据，基本语法如下。
DELETE FROM table_name
WHERE search_condition
说明：该语句将从表中删除符合条件的数据行，如果没有WHERE语句，则删除所有数据行。通过使用DELETE语句的WHERE子句，SQL可以删除单行数据、多行数据以及所有行数据。使用DELETE语句时，应注意以下几点。
在DELETE语句中没有指定列名，这是因为不能从表中删除单个列的值，只能整行删除。如果需要删除特定的列值，应使用UPDATE语句将该值设置为NULL，当然，前提是该列没有NOT NULL约束，具体操作可参加 |
DELETE语句可以删除数据库表中的单行数据、多行数据以及所有行数据。同时在WHERE子句中，也可以通过子查询删除数据。本节主要通过一个实例说明如何从表中删除单行数据。
实例9 DELETE语句删除单行数据
从New_Teacher2表中，删除教工号为8的教师记录。实例代码：
DELETE FROM New_Teacher2
WHERE TNO=8
查看表New_Teacher2的数据。
SELECT * FROM New_Teacher2
运行结果如图12.10所示。
图12.10 删除New_Teacher2表中教工号为8的教师记录
可见，教工号为8的教师记录已经被整行删除。
与前面介绍的数据更新操作一样，可以在WHERE子句中设置各种搜索条件，也可以通过子查询或者多表连接的方式选择要删除的行。
实例10 DELETE语句删除多行数据
从New_Teacher2表中，删除工资低于所有教师平均工资的男教师的记录。
实例代码：
DELETE FROM New_Teacher2
WHERE TSEX=’男’
AND SAL < (SELECT avg(SAL)
FROM New_Teacher2)
查看表New_Teacher2的数据。
SELECT * FROM New_Teacher2
运行结果如图12.11所示。
可见，教工号为1和3的两位男教师记录已经被删除。
图12.11 删除New_Teacher2表中工资低于所有教师平均工资的男教师的记录
如果DELETE语句中，不包含WHERE子句，则该表的所有行都将被删除。
实例11 DELETE语句删除所有行
删除New_Teacher2表中的所有记录，代码如下：
DELETE FROM New_Teacher2
查看表New_Teacher2的数据。
SELECT * FROM New_Teacher2
运行结果如图12.12所示。
图12.12 删除New_Teacher2表中的所有记录
可以发现，New_Teacher2表中的所有记录都被删除了，但是New_Teacher2表本身（各个字段）还存在，并没有被删除。
从
TRUNCATE TABLE table_name
虽然使用DELETE语句和TRUNCATE TABLE语句都能够删除表中的所有数据，但是使用TRUNCATE TABLE语句比用DELETE语句快得多，表现为以下两点。
TRUNCATE TABLE语句不能用于有外关键字依赖的表。 |
实例12 TRUNCATE TABLE语句删除表中的数据
删除New_Teacher表中的所有数据，代码如下。
TRUNCATE TABLE New_Teacher
查看删除数据后表New_Teacher中的数据。
SELECT * FROM New_Teacher
运行结果如图12.13所示。
图12.13 删除New_Teacher表中的所有数据
可见，表New_Teacher中的数据均被删除，但表结构并没有被删除，这与DROP TABLE语句是不同的。
12.3 通过视图更新表
由于视图是一张虚表，所以对视图的更新，最终实际上是转换成对视图的底层表的更新。因此，可以通过更新视图的方式实现对表中数据的更新。视图的更新操作包括插入、修改和删除数据，而通过视图向表中插入数据在上章已经介绍过了，这里主要讨论通过更新视图修改和删除表中的数据。
在本书第4章索引与视图的创建中已经介绍过，不是所有的视图都是可更新的。可更新的视图必须满足如下条件。
只有在创建视图时，满足了上面几点，才可以对视图进行更新，即对创建视图的基本表进行更新操作。
可以像更新表一样，使用UPDATE语句更新视图。更新视图的语法如下。
UPDATE View_name
SET column1=value1,
column2=value2,
……
WHERE search_condition
说明：与更新表相比，只是将表名替换为视图名，其他与更新表中数据的语法完全相同。当然视图必须满足可更新视图的条件。
可更新的视图必须是基于单个的底层表。 |
在UPDATE语句中，使用视图代替底层表的两个好处是：
比如，要求用户只能更新TEACHER表中的男教师的教工号、姓名、所在系、工资信息，而TEACHER表中的其他信息不能够被更新。这时候，就可以将TEACHER表中的相应信息创建视图View_Teacher，并授予可更新权限，而将TEACHER表的权限设置成不可更新的（有关权限的授予在第13章会有详细介绍）。SQL代码如下。
CREATE VIEW View_Teacher(Boy_NO, Boy_Name, Boy_Depart, Boy_Salary)
AS
SELECT TNO, TNAME, DNAME, SAL
FROM TEACHER
WHERE TSEX = '男'
查看视图的数据。
SELECT * FROM View_Teacher
运行结果如图12.14所示。
图12.14 视图View_Teacher中的记录
通过该视图就可以实现对TEACHER表中的男教师的教工号、姓名、所在系、工资信息的修改，而不能改变TEACHER表中的其他任何数据。
实例13 通过视图更新表数据
为工资低于1000元的男教师增加10％的工资，代码如下。
UPDATE View_Teacher
SET Boy_Salary = Boy_Salary + Boy_Salary *0.1
WHERE Boy_Salary < 1000
查看更新后的View_Teacher视图中的数据，如图12.15所示。
图12.15 为工资低于1000元的男教师增加10％工资后的纪录
系统在执行此代码时，首先从数据字典中找到View_Teacher视图的定义，然后把此定义和更新数据操作结合起来，转换成等价的、对底层表TEACHER的数据更新操作。相当于对TEACHER表执行以下操作。
UPDATE TEACHER
SET SAL = SAL + SAL *0.1
WHERE TSEX = '男'
AND SAL < 1000
此时，查看TEACHER表中的数据：
SELECT * FROM TEACHER
运行结果如图12.16所示。
图12.16 与图12.15等价的另一种表示
可见，所有相关数据已经得到更新。
通过视图也可以从表中删除行。与UPDATE语句一样，如果想使用视图作为DELETE语句的目标表，该视图也必须要满足可更新条件。视图不必显示底层表中的所有列，但是视图的SELECT语句必须只引用单个表，即DELETE语句的目标表。
通过视图，使用DELETE语句删除表中数据的基本语法如下。
DELETE FROM View_name
WHERE search_condition
说明：当使用视图作为DELETE语句的目标表时，只能删除那些在视图的SELECT子句中满足搜索条件，即满足视图选择标准的那些行。假如通过上节创建的View_Teacher视图，删除TEACHER表中的数据，就只能删除那些TEACHER表中的TSEX值为“男”的那些行。
实例14 通过视图删除表数据
将工资低于1000元的男教师记录删除，代码如下。
DELETE FROM View_Teacher
WHERE Boy_Salary < 1000
查看更新后的View_Teacher视图中的数据，如图12.17所示。
图12.17 删除工资低于1000元的男教师信息后的记录
系统在执行此代码时，首先从数据字典中找到View_Teacher视图的定义，然后把此定义和删除数据操作结合起来，转换成等价的、对底层表TEACHER的数据删除操作。相当于对TEACHER表执行以下操作。
DELETE FROM TEACHER
WHERE TSEX = '男'
AND SAL < 1000
此时，查看TEACHER表中的数据：
SELECT * FROM TEACHER
运行结果如图12.18所示。
图12.18 更新TEACHER信息后的记录
可见，在底层表即TEACHER表中，删除的也是一整列的信息。
对视图使用DELETE语句可以删除底层表中的数据，这一点与第4章介绍的使用DROP VIEW语句删除视图是不同的。使用DROP VIEW语句删除视图后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，而DELETE语句则将底层表中的相应的数据行也删除了。这一点在实际应用中要特别小心。 |
至此，由第4章、第11章以及本节的介绍，视图的基本概念、操作、用途就介绍完了。从中可以发现，由于视图中的数据不是存放在视图中的，即视图没有相应的存储空间，对视图的一切操作最终都要转换成对基本表的操作，这样看来使操作更加复杂，那么为什么还要使用视图呢？使用视图有如下几个主要的优点。
- SQL Server 2000 结构化查询语言详解
- 精通SQL---结构化查询语言详解
- 精通SQL-结构化查询语言详解之聚合分析
- 结构化查询语言（SQL）详解之一
- 结构化查询语言SQL
- SQL结构化查询语言
- 结构化查询语言 SQL
- SQL (结构化查询语言)
- 结构化查询语言 SQL
- 结构化查询语言SQL
- SQL 结构化查询语言
- SQL 结构化查询语言
- SQL结构化查询语言
- SQL Server结构查询SQL
- 第10章《子查询》[精通SQL---结构化查询语言详解]
- 结构化查询语言（SQL）原理
- 结构化查询语言（SQL）原理
- SQL（结构化查询语言）基础
- Perl网络编程 （转载）
- 我的blog
- atoi、atol、strtod、strtol、strtoul、gcvt、ecvt、fcvt、sprintf、toascii、tolower类型转换
- 触发器的创建及使用(sqlserver 2005)
- Java 数据类型
- SQL Server 2000 结构化查询语言详解
- 因为，有了“奶粉门”事件以后所以，我买食品的基本原则如下：
- 物料搬运单(Move Order)
- 巧用一条SQL语句实现其它进制到十进制转换
- Managing Certificates for Application Testing
- 教您7招防电磁辐射
- 成功实施SOA计划的八个最佳方法
- jQuery实例：图片展示效果,非常cool
- Java: JNI完全手册(DLLの呼び出す)
