mysql必知必会-笔记 12-15章

12章汇总数据

聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数

1. 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
2. 获得表中行组的和。
3. 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

SQL聚集函数
函 数                 说 明
AVG()           返回某列的平均值
COUNT()         返回某列的行数
MAX()           返回某列的最大值
MIN()           返回某列的最小值
SUM()           返回某列值之和

举例：
AVG()函数
mysql> select avg(prod_price) as avg_price from products;
mysql> select avg(prod_price) as avg_price from products where vend_id = 1003;  //指定条件
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+

COUNT()函数
检索客户总数：
mysql> select count(*) as num_cust from customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
COUNT()函数有两种使用方式：
1. 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
2. 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
mysql> select count(cust_email) as num_cust from customers; //只对具有电子邮件地址的客户计数

MAX()函数
返回指定列中的最大值， 需要指定列名， MAX()函数忽略列值为NULL的行。
mysql> select max(prod_price) as max_pricce from products;

MIN()函数
返回指定列的最小值。与MAX()一样，MIN()要求指定列名，忽略列值为NULL的行

SUM()函数
mysql> select quantity from orderitems where order_num= 20005;
+----------+
| quantity |
+----------+
|       10 |
|        3 |
|        5 |
|        1 |
+----------+
mysql> select sum(quantity) as items_ordered from orderitems where order_num= 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+

聚集不同值

使用DISTINCT
mysql> select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

组合聚集函数
mysql> select count(*) as num_items, min(prod_price) as price_min,max(prod_price) as price_max, avg(prod_price) as prive_avg from products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | prive_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

13章分组数据

group by 分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算.

创建分组

mysql> select vend_id, count(*) as num_prods from products;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |        14 |
+---------+-----------+ 
mysql> select vend_id, count(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
在具体使用GROUP BY子句前，需要知道一些重要的规定：
1.  GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
  （所以不能从个别的列取回数据）。
3. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
   GROUP BY子句中指定相同的表达式。不能使用别名。
4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组

HAVING子句

1. HAVING非常类似于WHERE, 所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
2. HAVING支持所有WHERE操作符
mysql> select cust_id, count(*) as orders from orders group by cust_id having count(*)>=2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
这里WHERE子句不起作用，因为过滤是基于分组聚集值而不是特定行值的
mysql> select vend_id,count(*) as num_prods from products where prod_price >=10 group by vend_id
   --> having count(*) >=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

分组和排序

ORDER BY与GROUP BY
        ORDER BY                        GROUP BY
+--------------------------+--------------------------------------------------
|   排序产生的输出           |    分组行。但输出可能不是分组的顺序                 
+--------------------------+--------------------------------------------------
|   任意列都可以使用（甚至    |    只可能使用选择列或表达式列，而且必须使用每个选择    
|   非选择的列也可以使用）    |    列表达式                                      
+--------------------------+--------------------------------------------------
|   不一定需要              |    如果与聚集函数一起使用列（或表达式），则必须使用        
+--------------------------+--------------------------------------------------

mysql> select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)>=50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
mysql> select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum
   -->(quantity*item_price)>=50 order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

SELECT子句顺序

SELECT子句及其顺序: 以下为必须遵循的顺序。
子 句         说 明                         是否必须使用
SELECT      要返回的列或表达式           是
FROM        从中检索数据的表                仅在从表选择数据时使用
WHERE       行级过滤                    否
GROUP BY    分组说明                    仅在按组计算聚集时使用
HAVING      组级过滤                    否
ORDER BY    输出排序顺序                  否
LIMIT       要检索的行数                  否

14章使用子查询

子查询（subquery），即嵌套在其他查询中的查询。

mysql> select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
嵌套查询顺序：子查询总是从内向外处理

15章联结表

一些概念：

1.  联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结
2.  外键（foreign key）： 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
3.  如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？答案是使用联结
4.  SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。

举例：

mysql> select vend_name, prod_name,prod_price from vendors,products 
   --> where vendors.vend_id = products.vend_id order by vend_name,prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R us | .5 ton anvil   |       5.99 |
| Anvils R us | 1 ton anvil    |       9.99 |
| Anvils R us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
这里，最大的差别是所指定的两个列（prod_name  和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。
通过where子句连接: where vendors.vend_id = products.vend_id

笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积, 笛卡尔积不是我们想要的结果

mysql> select vend_name, prod_name,prod_price from vendors,products order by vend_name,prod_name; //没有where过滤

内部联结 inner join

下面语句返回与上面相同的结果：
mysql> select vend_name,prod_name, prod_price from vendors inner join products on vendors.vend_id = 
   --> products.vend_id order by vend_name, prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R us | .5 ton anvil   |       5.99 |
| Anvils R us | 1 ton anvil    |       9.99 |
| Anvils R us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

联结多个表

mysql> select vend_name,prod_name, prod_price from vendors ,orderitems, products where products.vend_id =
    --> vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| Anvils R us | .5 ton anvil   |       5.99 |
| Anvils R us | 1 ton anvil    |       9.99 |
| ACME        | TNT (5 sticks) |      10.00 |
| ACME        | Bird seed      |      10.00 |
+-------------+----------------+------------+

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

mysql必知必会-笔记 12-15章

12章 汇总数据

13章 分组数据

14章 使用子查询

15章 联结表

推荐阅读更多精彩内容

12章汇总数据

13章分组数据

14章使用子查询

15章联结表