SQL循序渐进（2）

linker

7、所有的合计函数如下表所示：

MIN

返回一个给定列中最小的数值

MAX

返回一个给定列中最大的数值

SUM

返回一个给定列中所有数值的总和

AVG

返回一个给定列中所有数值的平均值

COUNT

返回一个给定列中所有数值的个数

COUNT(*)

返回一个表中的行数

合计函数用于从select语句中计算一个"返回列的数据"。它们是总结了所选数据列的结果。虽然它们需要"GROUP BY"子句（后面一个教程介绍），但是这些函数也可以在不用使用"GROUP BY"子句的情况被使用，比如 ：

select AVG(salary)

FROM employee;

这条语句将返回单一的结果，它包含了从employee表中所有salary列数据的平均值。为了更好的理解，我们再举个例子：

select AVG(salary)

FROM employee;

where title = Programmer ;

以上这条语句将返回employee表中所有title列为 Programmer 的数据的平均值。

下面的例子中使用的语句跟其它合计函数有点不用，因为没有一个类被指定给COUNT函数。这条语句实际上将返回employee表的行数，如下：

select Count(*)

FROM employees;


最后给出本节教程的配套练习：

1）作一个公司的销售表items_ordered，里面有price、product和amount。


从items_ordered表中选择price最大的数据。这里提示：使用MAX函数。

2) 计算items_ordered表中的行数。


8、GROUP BY子句

首先讲讲GROUP BY 子句语法：

select column1, SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list";

这个GROUP BY子句将集中所有的行在一起，它包含了指定列的数据以及允许合计函数来计算一个或者多个列。当然最好解释的方法是给出一个例子啦：

假设我们将从employee表中搜索工资最高的列，可以使用以下的SQL语句：

select max(salary), dept

FROM employee

GROUP BY dept;

这条语句将在每一个单独的部门中选择工资最高的工资。结果他们的salary和dept将被返回。

9、
HAVING子句

下面先给出HAVING子句的语法：

select column1, SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list"

HAVING "condition";

这个HAVING子句允许你为每一个组指定条件，换句话说，可以根据你指定的条件来选择行。如果你想使用HAVING子句的话，它应该处再GROUP BY子句之后。

下面将以一个例子来解释HAVING子句。假设我们的employee表中包含雇员的name、departmen、salary和age。如果你想为每个部门中每个雇员选择平均工资的话，你可以使用下面的SQL语句：

select dept, avg(salary)

FROM employee

GROUP BY dept;

当然，如果你还想只计算和显示salary大于20000的平均工资的话，你还可以加上HAVING子句：

select dept, avg(salary)

FROM employee

GROUP BY dept

HAVING avg(salary) > 20000;

10、
ORDER BY子句

ORDER BY子句的语法为：

select column1, SUM(column2)

FROM "list-of-tables"

ORDER BY "column-list" [ASC | DESC];

[ ] = optional

ORDER BY是一个可选的子句，它允许你根据指定要order by的列来以上升或者下降的顺序来显示查询的结果。例如：

ASC = Ascending Order – 这个是缺省的

DESC = Descending Order

下面举个例子：

select employee_id, dept, name, age, salary

FROM employee_info

where dept = Sales

ORDER BY salary;

这条SQL语句将从employee_info表中列dept等于 Sales 选择employee_id,、dept、 name、 age和 salary，并且根据他们的salary按升序的顺序来列出检索结果。

如果你想对多列排序的话，那么在列与列之间要加上逗号，比如 ：

select employee_id, dept, name, age, salary

FROM employee_info

where dept = Sales

ORDER BY salary, age DESC;

11、
组合条件和布尔运算符

以下的SQL语句中就含有组合条件：

select column1, SUM(column2)

FROM "list-of-tables"

where "condition1" AND "condition2";

AND运算符可以在where子句中连接两个或者多个条件。AND条件的两旁必须都为true（真），即两个条件都同时满足的时候，这些行才将被显示。

当然，你也可以使用OR运算符，它也可以在where子句中连接两个或者多个条件。但是，只要OR运算符两旁有一个为true的时候条件就满足了，因此行才会被显示。所以你使用OR运算符的时候，可以是OR运算符两旁只有一个为true或者两旁都为true。

下面举个例子吧：

select employeeid, firstname, lastname, title, salary

FROM employee_info

where salary >= 50000.00 AND title = Programmer ;

这条SQL语句是从employee_info表中选择salary大于等于50000.00并且title等于 Programmer 的列employeeid、 firstname、 lastname、 title和 salary。此时必须AND运算符两旁的条件都为真，行才会最为检索结果返回。如果其中有一个条件为假，那么就什么都没有显示。

你可以使用圆括号将条件括起来，虽然它们也不一定是必须的，但是括起来看起来更清晰一些，这是一个编程习惯的问题。比如 ：

select employeeid, firstname, lastname, title, salary

FROM employee_info

where (salary >= 50000.00) AND (title = Programmer );

下面再举个例子：

select firstname, lastname, title, salary

FROM employee_info

where (title = Sales ) OR (title = Programmer );

这条语句将从employee_info表中选择title等于 Sales 或者等于 Programmer 的列firstname、 lastname, title和 salary。

12、
IN 和 BETWEEN 条件运算符

下面是IN条件运算符的SQL语句：

select column1, SUM(column2)

FROM "list-of-tables"

where column3 IN (list-of-values);

下面是BETWEEN条件运算符的SQL语句：

select column1, SUM(column2)

FROM "list-of-tables"

where column3 BETWEEN value1 AND value2;

实际上，IN条件运算符是一个设置成员测试运算符，也就是说，它用于测试是否一个数值处在IN关键字之后提供的数值之中。举个例子如下：

select employeeid, lastname, salary

FROM employee_info

where lastname IN ( Hernandez , Jones , Roberts , Ruiz );

这条语句是从employee_info表中选择lastname等于Hernandez、Jones、 Roberts或者 Ruiz名字之一的列employeeid、 lastname和 salary。如果它在其中就将返回行。

IN条件运算符可以使用混合条件来替代，比如你可以使用等号运算符或者使用OR运算符等等，但是结果是一样的，例如：

select employeeid, lastname, salary

FROM employee_info

where lastname = Hernandez OR lastname = Jones OR lastname = Roberts OR lastname = Ruiz ;

你可以观察到，利用IN运算符时语句会更加简短并且容易读，特别是在你测试两个或者三个数值以上的时候尤为突出。

当然你也可以使用NOT IN 来在你的列表中排除行的。


而BETWEEN条件运算符是用与测试一个数值是否处在BETWEEN关键字两边指定数值的中间，比如：

select employeeid, age, lastname, salary

FROM employee_info

where age BETWEEN 30 AND 40;

这条SQL语句是从employee_info表中选择age处于30到40岁之间（包括30岁和40岁）的列employeeid、age、 lastname和salary。

这条语句同样可以不用BETWEEN运算符，而使用混合条件来替代，例如：

select employeeid, age, lastname, salary

FROM employee_info

where age >= 30 AND age <= 40;

当然，你也可以类似于NOT IN的方法，使用NOT BETWEEN来排除一些数据。

13、
数学运算符

标准的ANSI SQL-92支持下面四个基本的算术运算符：
+
加
-
减

*
乘

/

除
%
求余

其中求余运算符决定除法的余数。这个运算符不是ANSI SQL支持的，但是，绝大多数的数据库支持他。下面是一些有用的数学函数，因为可能要用到它，所以我这里要集中提一下。在ANSI SQL-92中不支持这些函数，但是它们可能对于某些特殊的RDBMS是有效的。然而它们对于几个主要的数据库系统都是有效的。下面就说说这些数学函数吧：


ABS(x)


返回x的绝对值


SIGN(x)


当x为负数、零、正数的时候分别返回x的符号-1、0或者1


MOD(x,y)


返回x除以y的余数，跟x%y作用一样


FLOOR(x)


返回小于等于x的最大整数


CEILING(x) 或 CEIL(x)


返回大于等于x的最小整数


POWER(x,y)


返回x的y次方的数值


ROUND(x)


返回最接近于x的数


ROUND(x,d)


返回小数点数为4的接近于x的数


SQRT(x)


返回x的平方根


下面举个例子：

select round(salary), firstname

FROM employee_info

上面这条语句将从employee_info表中选择salary最接近的数以及firstname列。
join子句

　　不知你有没有发现直到现在我们利用select语句来检索的时候只能从一个表中进行。如果你想从两个表或者更多的表中进行检索，该怎么办呢？好在我们可以使用SQL和关系数据库系统的一个很有用的特性，即"join"。为了简单说明，实际上"join"就是使得关系数据库系统相关的东东。"join"允许你从两个表或者更多的表连接数据进行数据检索，而只需要利用一个select语句。如果在FROM关键字之后有多个表的话，"join"可以在SQL select语句中识别它们。

下面举个例子：

select "list-of-columns"

FROM table1,table2

where "search-condition(s)"

"join"

通过示范当你只处理一个表的时候会发生什么事情可以使得"join"的解释更简单，所以这里我没有使用"join"。这个单一的数据库有事也被称为"flat table"（平表）。现在你有一个表的数据库用来检索所有顾客的信息以及他们从你的商店买了什么，下面就是这个表的所有列：


每次一个新行被插入到表中，所有的列都将被更新，这样就导致了不必要的"多余数据"。比如，每次Jenny买东西，下面的行都将被插入到表中：


为了避免"多余数据"，一个最好的方法：让数据库有两个表： 其中一个用来对顾客保持跟踪；另外一个用来对他们买什么东西保持跟踪。 即有"Customer_info" 表和"urchases" 表：

"Customer_info" 表为：


customer_number


firstname


lastname


address


city


state


zip


"urchases" 表为：


customer_number


date


item


price


现在开始，不管顾客什么时候进行重复的购物，只有第二个表"urchases" 需要更新。这样我们就减少了多余的数据，也就是说我们规格化了这个数据库。

你仔细点就会发现两个表中还是有一个"cusomer_number"列是相同的。这个列包含了单独的顾客号，它将用来join（连接）两个表。下面举个例子来使用这两个表，假如你想搜索顾客的名字以及他们所买的东西，你可以使用以下的语句来实现：

select customer_info.firstname, customer_info.lastname, purchases.item

FROM customer_info, purchases

where customer_info.customer_number = purchases.customer_number;

特殊的"join"有为"Inner join" 或者"Equijoin"，这是一个最常见的"join"类型，以后我们经常用使用到或者看到。

这里要注意每列总是在表名之前，这却也不是必需的。这是一个好的练习对于帮助你澄清列后面跟着表的认识有很大帮助。如果两个表之间有一个相同的列，它就是必须的。我这里推荐在使用join的时候最好在所有列之后加上表名。

注意；上面描述的这个语法将在绝大多数的数据库系统起作用，本教程的也是一样。但是结果你会发现你上面的语句并不起作用，请仔细检查一下吧。

当然你可以试一试修改以上的代码，你可以使用join（ANSI SQL-92语法规范中的INNER join）：

select customer_info.firstname, customer_info.lastname, purchases.item

FROM customer_info INNER join purchases

ON customer_info.customer_number = purchases.customer_number;

再举另外一个例子：

select employee_info.employeeid, employee_info.lastname, employee_sales.comission

FROM employee_info, employee_sales

where employee_info.employeeid = employee_sales.employeeid;

这个例子将从employee_info和employee_sales表中选择当employee_info表的employeeid等于employee_sales表的employeeid的employeeid 、emplyee_info表中lastname以及employee_sales表中的comission数值。

从上面的例子中可以发现利用JION的语句比价简练。既然有这样的有点，我们何乐而不为呢？


索引

索引允许DBMS更快地访问数据。系统创建了这个内部地数据结构（即索引），它导致当查询以列为索引的时候搜索行，这样查询会快得多。这个索引对于给定列索引数值的时通知DBMS找到表中某一行，这有点象书的索引，它告诉你对于给定的字你能找到哪一页。下面让我们在AntiqueOwners列中为OwnerID创建索引：

create INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

下面语句是为名字创建所以：

create INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME);

为了删除索引，你可以利用drop：

drop INDEX OID_IDX;

就象前面教程中，我们也可以"drop"（删除）一个表。上面第二个例子中，是在两列上创建索引的。

有些DBMS不强迫要求主键，换句话说就是，类的唯一性不会自动强制。这是什么意思呢，好象听起来云里雾里的。好吧，再给你举个例子，如果你象插入另外一行到AntiqueOwners表中，比如这个OwnerID是02，有些系统可以让你这样做即使我们要求所有行的数值都要是不同的。为了避免两行有相同的值，我们有一种方法来克服，就是在列上创建唯一的索引，而在这个列上我们需要它成为主键，这样就可以系统不会出现复制的情况：

create UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID);

DISTINCT和排除复制

假如你象列出所有买过古董的ID和名字，很明显，你可能会将所有的顾客都列出来而没有考虑有些顾客是买过多讲古董的，所以这时你会发现有些数据是重复的。这就意味着你需要通知SQL来排除复制的行，而不管这个顾客买过多少个古董只需要列出一次即可。为了实现这个目的，你可以使用DISTINCT关键字。

首先我们需要为AntiqueOwners表来一个equijoin以得到顾客的LastName和First的详细数据。但是，你要考虑到Antiques表中的SellerID列是AntiqueOwners表的一个外码，所以顾客只能在AntiqueOwners表列出ID和名字的行才被列出。我们还想在列的数据中排除SellerID复制的发生，所以我们要在发生重复的列上使用DISTINCT 。

为了防止复制的发生，我们还想将LastName以字母顺序排列，然后在按字母顺序排列FirstName最后排列OwnerID，因此我们还必须使用ORDER BY子句，具体语句如下：

select DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME

FROM ANTIQUES, ANTIQUEOWNERS

where SELLERID = OWNERID

ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID

在这个例子中，因为每个人都买都一个件古董，所以我们将Lasname以字母顺序列出所有的古董拥有者


Aliases 、In以及子查询

在本节教程中，我们将要介绍Aliases、 In以及子查询的用法。首先我们看一下一个查询语句，它搜索所有已经定货的顾客的LastName以及他们定什么货，语句如下：

select OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered

FROM ORDERS ORD, ANTIQUEOWNERS OWN

where ORD.OWNERID = OWN.OWNERID

AND ORD.ITEMDESIRED IN


(select ITEM

FROM ANTIQUES);

这条查询语句的结果为：

Last Name Item Ordered

--------- ------------

Smith Table

Smith Desk

Akins Chair

Lawson Mirror

下面好好解释一下上面的这条语句：

"Last Name" 和"Item Ordered"给出了报告的数据头。

OWN & ORD是aliases（别名），它们使用在FROM子句中，可在它们的后面加一个点号再加列名就可以进行查询了。这样做就避免了模棱两可的情况，特别是在equijoin where子句中当两个列都名为OwenerID的时候，而点号就通知SQL我们使用是两个不同表的不同OwnerID。

这里要注意，在FROM子句中Orders表被首先列出，并且确保AntiqueOwners表只用于详细的信息（Last Name）。更为重要的，在where子句中的AND强迫In子查询被调用（"= ANY" or "= SOME" 都等价于使用IN）。但这到底做了些什么呢？当这个子查询完成了，它就返回Antiques表的所有Items因为这里没有where子句。然后，对于从Orders表列出的行，ItemDesired必须在从Antiques表中返回的Items列表中，然后在定货可以有另外的拥有者填写的情况下列出一个item。你可以想想这个方法：子查询从Orders 表中的每一个ItemDesired被比较中返回一系列的Items；如果ItemDesired是在从Antiques表中返回的，那么条件才为真。

梦想成真

好帖，有才