使用数据处理函数

函数没有SQL的可移植性强

使用函数

大多数SQL实现支持以下类型的函数
①用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数
②用于数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数
③用于处理日期和时间值并从这些值提取特定成分(例如，返回两个日期之差，检查日期有效性等)的日期和时间函数
④返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数

文本处理函数


使用Upper()函数
SELECT vend_name,Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较
下面给出Soundex()的例子，customers表中有个顾客Coyote Inc.，其联系名为Y.Lee。但联系名应该是 Y.Lie，如果按正确的联系名搜索不会返回数据
SELECT cust_name,cust_contact FROM customers WHERE cust_contact=’Y.Lie’;

而使用Soundex()，它匹配所有发音类似于Y.Lie的联系名
SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex(‘Y.Lie’);

日期和时间处理函数

应该总是使用4位数字的年份

基本的日期比较：
SELECT cust_id,order_num FROM orders WHERE order_date=’2005-09-01’;

更多时候，时间还包括下订单当天的具体时间，这时上面的语句会检索不到，因为WHERE会匹配失败。
所以应该指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。应该使用Date()函数，Date(order_date)指示MySQL仅提取列的日期部分:
SELECT cust_id,order_num FROM orders WHERE Date(order_date)=’2005-09-01’;

如果你想检索出2005年9月下的所有订单，可以:
SELECT cust_id,order_num FROM orders WHERE Date(order_date) Between ‘2005-09-01’ AND ‘2005-09-30’;

还可以：
SELECT cust_id,order_num FROM orders WHERE Year(order_date)=2005 AND Month(order_date)=9;

数值处理函数

汇总数据

聚集函数(aggregate function)

聚集函数 ——运行在行组上，计算和返回单个值的函数。

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。

AVG函数
AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

使用AVG()返回 products表中所有产品的平均价格：
SELECT AVG(prod_price) AS avg_price FROM products;

AVG()可以确定特定列或行的平均值。下面的例子返回特定供应商锁提供的平均价格:(首先会过滤出vend_id=1003的行再求平均)
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;

COUNT函数
COUNT()函数进行计数，可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：
①使用COUNT(*)对表中行的数目进行计数，不管表列中所包含的是空值NULL还是非空值
②使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

返回customers表中客户的总数:
SELECT COUNT(*) AS num_cust FROM customers;

只对具有电子邮件地址的客户计数:
SELECT COUNT(cust_email) AS num_cust FROM customers;

MAX()函数
MAX()返回指定列中的最大值，要求指定列名:
SELECT MAX(prod_price) AS max_price FROM products;

MAX()函数会自动忽略值为NULL的行。

MAX()一般用来找出最大的数值或日期值，还可以将它用来返回任意列的最大值，包括返回文本列的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

MIN()函数
MIN()返回指定列中的最小值，要求指定列名:
SELECT MIN(prod_price) AS max_price FROM products;

与MAX一样。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

SUM()函数
SUM()用来返回指定列值的和(总计)

orderitems表包含订单中实际的物品，每个物品有相应的数量(quantity).可如下检索所订购物品的总数:
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;

还可以用来合计计算值，合计每项物品额item_price*quantity，得出总的订单金额:
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num=20005;

聚集不同值

以上5个聚集函数都可以如下使用:
①对所有的行执行计算，指定ALL参数或不给参数(默认为ALL)
②只包含不同的值，指定DISTINCT参数

使用AVG()函数返回指定供应商提供的产品的平均价格，使用DISTINCT参数，平均值只考虑不同的价格:
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;

组合聚集函数

实际上SELECT语句可根据需要包含多个聚集函数:
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products;

分组数据

数据分组

分组运行把数据分为多个逻辑组，以便对每个组进行聚集计算

创建分组

分组是在SELECT语句的GROUP BY子句中建立的
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

GROUP BY子句指示MySQL按vend_id***排序并分组数据*。

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果进行聚集。

使用GROUP BY子句，需知道的规定:
①GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
②如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。即在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
③GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
④除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
⑤如果分组列中具有NULL值，则NULL将作为一个分组返回。如果类中有多行NULL值，它们将分为一组
⑥GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下:
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

过滤分组

MySQL运行过滤分组，规定包括那些分组，排除哪些分组。

WHERE过滤指定的是行而不是分组，因此应该使用HAVING子句。
HAVING和WHERE十分类似，唯一的差别是WHERE过滤行，而HAVING过滤分组。
HAVING支持所有WHERE操作符。

过滤COUNT（*）>=2的那些分组:
SELECT cust_id,COUNT() AS orders FROM orders GROUP BY cust_id HAVING COUNT()>=2;

同时使用WHERE和HAVING,下面例子列出具有2个（含）以上，价格为10（含）以上的产品额供应商：
SELECT vend_id,COUNT() AS num_prod FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT()>=2;

WHERE子句过滤所有prod_price至少为10的行，然后按vend_id分组数据，HAVING子句过滤计数至少为2的分组。

没有WHERE子句
SELECT vend_id,COUNT() AS num_prod FROM products GROUP BY vend_id HAVING COUNT()>=2;
结果如下:

分组和排序

一般在使用GROUP BY子句时，也应该给出ORDER BY子句。

检索总计订单价格大于50的订单号和总即订单价格
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

排序输出，使用ORDER BY子句

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

SELECT子句顺序

14.使用子查询

14.1子查询

SELECT语句时SQL的查询，SQL还允许创建子查询，即嵌套在其他查询中的查询。

14.2 利用子查询进行过滤

订单存储在两个表中：对于包含订单号，客户ID，订单日期的每个订单，orders表存储一行；各订单物品存储在相关的orderitems表中。orders表不存储客户信息，它只存储客户的ID。实际的客户信息存储在customers表中

如果需要列出订购TNT2的所有客户，应该怎样检索?
①检索包含物品TNT2的所有订单的编号
②检索具有前一步骤列出的订单编号的所有客户的ID
③检索前一步骤返回的所有客户ID的客户信息

可以单独作为一个查询来执行，把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE语句。

也可以使用子查询将3个查询组合成一条语句。

对于第一条SELECT语句
SELECT order_num FROM orderitems WHERE prod_id=’TNT2’;

第二条
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

于是我们可以把第一，二条变为子查询组合两个查询
SELECT cust_id FROM orders WHERE order_num in(SELECT order_num FROM orderitems WHERE prod_id=’TNT2’);
首先执行括号内的

下一步是检索这些客户ID的客户信息
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);

可以转换为子查询
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id=’TNT2’));

在WHERE子句中使用子查询能够编写出功能很强并且灵活的SQL语句，对于能嵌套的子查询没有数目限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

作为计算字段使用子查询

需要显示customers表中每个客户的订单总数。订单与相应的客户ID存在在orders表中
①从customers表中检索客户列表
②对于检索出的每个客户，统计其在orders表中的订单数目

对客户10001的订单进行计数:
SELECT COUNT(*) AS orders FROM orders WHERE cust_id=10001;

为了对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;

这里orders.cust_id=customers.cust_id使用了完全限定列名

15.联结表

15.1 联结

联结是利用SQL的SELECT能执行的最重要的操作。

关系表
假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生成该产品的供应商信息。

现在，假如有同一供应商生成的多种产品，那么在何处存储供应商信息呢?
应该将这些数据与产品信息分开的理由如下：
①因为同一个供应商生成的每个产品的供应商时相同，对每个产品重复此信息浪费时间和存储空间
②如果供应商信息改变，只要改动一次即可
③如果有重复数据，很难保证每次输入该数据的方式都相同。

关系表的设计就是要确保百信息分解为多个表，一类数据一个表。各表通过关系互相关联。

在这个例子中，可建立两个表，一个存储供应商信息，另一个存产品信息。vendors表包含所有供应商信息， 每个供应商一行，每个供应商具有唯一的标识，此标识称为主键(primary key),可以是供应商ID或其他任何唯一值。

products表只存储产品信息，除了存储供应商ID（vendors的主键）外不存储其他供应商信息。vendors表的主键又叫做products的外键，它将vendors与products表关联，利用供应商ID能从vendors表中找出相应供应商的信息。

外键(foreign key)为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

好处是上面3点的反面。

关系数据库的可伸缩性比非关系数据库要好

为什么要使用联结
联结是一种机制，用来在一条SELECT语句中关联表。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

15.2 创建联结

联结的创建，规定要联结的所有表以及它们如何关联。
SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;

FROM语句列出了两个表，它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结。（需使用完全限定列名）

WHERE 子句的重要性
在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二表中的每个行配对，而不管它们逻辑上是否可以配在一起。

由没有联结条件的表返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
SELECT vend_name,prod_name,prod_price FROM vendors,products ORDER BY vend_name,prod_name;

返回84行，在vendors表中返回6行，在products表中返回14行。

内部联结
目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的相等测试。这种联结也被称为内部联结

可以使用稍微不同的语法来明确指定联结的类型:
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

这里两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

联结多个表

下面这个例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中。每个物品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的 记录中。这里的FROM子句列出了三个表，而WHERE子句定义了这两个联结条件，第三个联结条件用来过滤出订单20005中的物品。

SELECT prod_name,vend_name,prod_price,quantity FROM orderitems,products,vendors WHERE products.vend_id=vendors.vend_id AND orderitems.prod_id=products.prod_id AND order_num=20005;

在回顾14章中的子查询，
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN
(SELECT cust_id FROM orders WHERE order_num IN
(SELECT order_num FROM orderitems WHERE prod_id=’TNT2’));

可以使用联结：
SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.cust_id=orders.cust_id AND orderitems.order_num=orders.order_num AND prod_id=’TNT2’;

16 创建高级联结

16.1 使用表别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样可以缩短SQL语句，允许在单条SELECT语句中多次使用相同的表。

下面的SELECT语句和上一章例子相同，但使用了别名
SELECT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id=’TNT2’;

使用表别名的主要原因之一是在单挑SELECT语句中不只一次引用相同的表

16.2 使用不同类型的联结

自联表
假如你发现某物品DTNTP存在问题，因此你想知道其供应商生成的其他物品是否也有问题，此查询首先要求找到供应商，然后找出其他生产物品，下面是一种方法,使用了子查询:
SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id=’DTNTR’);

使用联结的相同查询:
SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id=’DTNTR’;

此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次，对products的引用具有二义性，因此使用了表别名，第一次出现为别名p1,第二次出现为p2.
SELECT语句使用p1前缀明确给出所需离列的全名，否则将报错。
WHERE通过匹配p1中的vend_id和p2中的vend_id，首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

自然联结
无论何时对表进行联结，应该至少有一个列不止在一个表中(被联结的列)。标准的联结（内部联结）返回所有数据，甚至相同的类多次出现。
自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符(SELECT *)，对其他所有表的列使用明确的子集来完成的。
SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id=’FB’;

事实上，我们至今建立的每个内部联结都是自然联结。

外部联结

联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

下面的SELECT语句给出一个简单的内部联结，检索所有客户及其他订单:
SELECT customers.cust_id,orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id;

外部联结语法类似，为了检索所有客户，包括那些没有订单的客户，可如下进行:
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。
在使用OUTER JOIN语法时，必须使用RIGTH或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)
SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id=orders.cust_id;

使用带聚集函数的联结

聚集函数可以与联结一起使用。

如果要检索所有客户以及每个客户所下的订单数，如下:
此SELECT语句使用INNER JOIN将customers和orders表相互关联，GROUP BY 子句按客户分组数据，调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回
SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;

16.4 使用联结和联结条件

①要注意使用的联结类型。
②保证使用正确的联结条件，否则将返回不正确的数据
③总是提供联结条件，否则会得出笛卡尔积
④在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。

17 组合查询

17.1 组合查询

MySQL运行执行多个查询(多个SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

有两种基本情况需要使用组合查询:
①在单个查询中从不同的表返回类似结构的数据
②对单个表执行多个查询，按单个查询返回数据。

多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单个查询完成的工作相同。
即任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。

17.2 创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的查询结果组合成单个结果集

使用UNION
UNION的使用只需给出每条SELECT语句，在每条语句之间放上关键字UNION。

首先看单条语句:
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5;

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

组合两条语句,UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集:
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

作为参考，这里给出使用多条WHERE 子句而不是使用UNION的相同查询：
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 OR vend_id IN (1001,1002);

UNION规则
①UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
②UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过各个列不需要以相同的次序列出）
③列数据类型必须兼容：类型不必完全相同，但必须是DBMS可恶意隐含地转换的类型。

包含或取消重复的行
UNION从查询结果集中会自动去除了重复的行（它的行为与单条SELECT语句中使用多个WHERE子句条件一样）

这是UNIO的默认行为，但如果需要返回所有匹配行，可以使用UNION ALL而不是UNION
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 UNION ALL SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE

对组合查询结果进行排序
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。不允许使用多条ORDER BY 子句。
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id,prod_price;

使用UNION的组合查询可以应用不同的表。