Java养成计划----学习打卡第六十六天
内容导航
Java（打卡第六十六天)
MySQL数据库单行处理函数，多行处理函数（分组函数）
昨天的博客中已经分享了条件查询，排序，以及单行处理函数的lower，upper，substr
首先我们来复习一下，这里还是连接world数据库，然后使用city表进行操作
mysql> SELECT
-> Name AS '城市名称',LOWER(SUBSTR(CountryCode,1,2)) '国家编码',District,Population AS '人口'
-> FROM
-> city
-> WHERE
-> ID <= 10
-> ORDER BY
-> CountryCode DESC,Population ASC;
+----------------+----------+---------------+---------+
| 城市名称 | 国家编码 | District | 人口 |
+----------------+----------+---------------+---------+
| Tilburg | nl | Noord-Brabant | 193238 |
| Eindhoven | nl | Noord-Brabant | 201843 |
| Utrecht | nl | Utrecht | 234323 |
| Haag | nl | Zuid-Holland | 440900 |
| Rotterdam | nl | Zuid-Holland | 593321 |
| Amsterdam | nl | Noord-Holland | 731200 |
| Mazar-e-Sharif | af | Balkh | 127800 |
| Herat | af | Herat | 186800 |
| Qandahar | af | Qandahar | 237500 |
| Kabul | af | Kabol | 1780000 |
+----------------+----------+---------------+---------+
10 rows in set (0.02 sec)
这里就使用了排序，条件和两个单行处理函数
起始下标都是从1开始的
现在查询名字中第一个字母为A就有两种方式了
- WHERE name LIKE ‘A%’ //模糊查询的方式
- WHERE SUBSTR(name,1,1) = ‘A’ //截串的方式
单行处理函数（续）
CONCAT 字符串拼接函数
数据库中不支持使用+来拼接字符串，java中就是使用+来拼接字符串的，并且字符串至少要有一边为字符串
数据库只能使用字符串拼接函数concat来拼接字符串
//基本格式
SELECT
CONCAT(col_name,newstr) ……
这里试着给city表中的conctycode加一个cfeng的尾串
mysql> SELECT
-> Name AS '城市名称',CONCAT(LOWER(SUBSTR(CountryCode,2,2)) ,' Cfeng') '国家编码',District,Population AS '人口'
-> FROM
-> city
-> WHERE
-> ID <= 10;
+----------------+----------+---------------+---------+
| 城市名称 | 国家编码 | District | 人口 |
+----------------+----------+---------------+---------+
| Kabul | fg Cfeng | Kabol | 1780000 |
| Qandahar | fg Cfeng | Qandahar | 237500 |
| Herat | fg Cfeng | Herat | 186800 |
| Mazar-e-Sharif | fg Cfeng | Balkh | 127800 |
| Amsterdam | ld Cfeng | Noord-Holland | 731200 |
| Rotterdam | ld Cfeng | Zuid-Holland | 593321 |
| Haag | ld Cfeng | Zuid-Holland | 440900 |
| Utrecht | ld Cfeng | Utrecht | 234323 |
| Eindhoven | ld Cfeng | Noord-Brabant | 201843 |
| Tilburg | ld Cfeng | Noord-Brabant | 193238 |
+----------------+----------+---------------+---------+
10 rows in set (0.00 sec)
LENGTH 字符串长度
可以获得字符串的长度，可以进行很多操作
比如这里我们想要搜索Name长度为3的城市[从4079数据中查询]
mysql> SELECT
-> *
-> FROM
-> city
-> WHERE
-> LENGTH(Name) = 3;
+------+------+-------------+---------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------+-------------+---------------------+------------+
| 29 | Ede | NLD | Gelderland | 101574 |
| 362 | Itu | BRA | S?o Paulo | 132736 |
| 396 | Jaú | BRA | S?o Paulo | 109965 |
| 454 | Poá | BRA | S?o Paulo | 89236 |
| 1387 | Qom | IRN | Qom | 777677 |
| 1608 | Yao | JPN | Osaka | 276421 |
| 1644 | Uji | JPN | Kyoto | 188735 |
| 1647 | Ube | JPN | Yamaguchi | 175206 |
| 1660 | Tsu | JPN | Mie | 164543 |
| 1680 | Ota | JPN | Gumma | 145317 |
| 1683 | Ome | JPN | Tokyo-to | 139216 |
| 1751 | Ise | JPN | Mie | 101732 |
| 1785 | Ibb | YEM | Ibb | 103300 |
| 1794 | Ni? | YUG | Central Serbia | 175391 |
| 2254 | Osh | KGZ | Osh | 222700 |
| 2488 | Fès | MAR | Fès-Boulemane | 541162 |
| 2752 | Iwo | NGA | Oyo & Osun | 362000 |
| 2759 | Ede | NGA | Oyo & Osun | 307100 |
| 2760 | Aba | NGA | Imo & Abia | 298900 |
| 2761 | Ife | NGA | Oyo & Osun | 296800 |
| 2762 | Ila | NGA | Oyo & Osun | 264000 |
| 2763 | Oyo | NGA | Oyo & Osun | 256400 |
| 2768 | Jos | NGA | Plateau & Nassarawa | 206300 |
| 2774 | Owo | NGA | Ondo & Ekiti | 183500 |
| 2804 | Epe | NGA | Lagos | 101000 |
| 2845 | Wah | PAK | Punjab | 198400 |
| 2902 | Ica | PER | Ica | 194820 |
| 3134 | Ulm | DEU | Baden-Württemberg | 116103 |
| 3379 | Van | TUR | Van | 219319 |
| 3588 | Ufa | RUS | Ba?kortostan | 1091200 |
| 3775 | Hue | VNM | Thua Thien-Hue | 219149 |
+------+------+-------------+---------------------+------------+
31 rows in set (0.00 sec)
这里还可以配合substr来将countryCode变成首字母大写的单词【所有的查询操作都是不会改变表中的数据的DQL）
mysql> SELECT
-> Name AS '城市名称',CONCAT(SUBSTR(Name,1,1),LOWER(SUBSTR(CountryCode,2,LENGTH(Name)-1))) '国家编码',District,Population AS '人口'
-> FROM
-> city
-> WHERE
-> ID <= 10;
+----------------+----------+---------------+---------+
| 城市名称 | 国家编码 | District | 人口 |
+----------------+----------+---------------+---------+
| Kabul | Kfg | Kabol | 1780000 |
| Qandahar | Qfg | Qandahar | 237500 |
| Herat | Hfg | Herat | 186800 |
| Mazar-e-Sharif | Mfg | Balkh | 127800 |
| Amsterdam | Ald | Noord-Holland | 731200 |
| Rotterdam | Rld | Zuid-Holland | 593321 |
| Haag | Hld | Zuid-Holland | 440900 |
| Utrecht | Uld | Utrecht | 234323 |
| Eindhoven | Eld | Noord-Brabant | 201843 |
| Tilburg | Tld | Noord-Brabant | 193238 |
+----------------+----------+---------------+---------+
10 rows in set (0.00 sec)
TRIM 去空格
有的时候字符串中有空格，但是查询的时候可以去掉空格
还有就是可以将输入的字符串给去掉空格
mysql> SELECT
-> *
-> FROM
-> city
-> WHERE
-> Name = TRIM(' Kabul');
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
这里需要注意的是，字符串需要使用单引号引起来
ROUND 四舍五入
之前的结构都是
SELECT 字段名 FROM 表名
查询表中的数据
那如果这里的字段名换成一个字面量\字面值会怎么样
mysql> SELECT
-> 'Cfeng' AS '别名'
-> FROM
-> city
-> WHERE
-> ID <= 10;
+-------+
| 别名 |
+-------+
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
| Cfeng |
+-------+
10 rows in set (0.00 sec)
这里的结果就是给出一列全部都是字面量的数据，这显然是一种错误查询
如果这里的=='Cfeng’是一个数字呢，还是一样的结果，因为数字也是字面量==，在java中它们都是有一个常量池的，String的intern函数设计的常量池最容易考察了
SELECT 可以跟某个表的字段名，也可以跟字面量，这里可以将字段名看作变量，字面量看作数据，也就是常量
分析上面的内容，就是想表明这些函数里既可以放入变量【字段名】处理，也可以直接放入一个常量处理，比如上面的TRIM 和 SUBSTR
ROUND的使用方式
ROUND(data,m) m代表的是保留的小数的位数
这里使用之前cfengbase中的fruits表来实验
+------+-------------+--------------+---------------+---------------+
| id | fruits_name | fruits_price | fruits_origin | fruits_remark |
+------+-------------+--------------+---------------+---------------+
| 1001 | 葡萄 | 3.7 | 山东 | 红香妃 |
| 1002 | 苹果 | 2.8 | 山东 | 红富士 |
| 1003 | 香蕉 | 6 | 海南 | 小芭蕉 |
+------+-------------+--------------+---------------+---------------+
3 rows in set (0.03 sec)
mysql> SELECT
-> id,fruits_name,ROUND(fruits_price,0),fruits_origin,fruits_remark
-> FROM
-> fruits;
+------+-------------+-----------------------+---------------+---------------+
| id | fruits_name | ROUND(fruits_price,0) | fruits_origin | fruits_remark |
+------+-------------+-----------------------+---------------+---------------+
| 1001 | 葡萄 | 4 | 山东 | 红香妃 |
| 1002 | 苹果 | 3 | 山东 | 红富士 |
| 1003 | 香蕉 | 6 | 海南 | 小芭蕉 |
+------+-------------+-----------------------+---------------+---------------+
3 rows in set (0.01 sec)
上面ROUND中是0代表保留到0位，也就是保留到整数位
-
-2 保留到百位
-
-1 保留到十位 【和数学中不一样，这里的意思是舍去个位，比如1246 ---- 》 1250】
-
0 保留到整数位
-
1 保留一位小数
-
2 保留两位小数
RAND 生成随机数
这里使用上面的查询字面量来看一下这个函数
mysql> SELECT
-> RAND()
-> FROM
-> fruits;
+--------------------+
| RAND() |
+--------------------+
| 0.9906442416744718 |
| 0.532462233242078 |
| 0.6903784719206192 |
+--------------------+
3 rows in set (0.00 sec)
IFNULL 将null转换为一个具体的值
ifnull是空处理函数，专门处理空的
IFNULL(table_name,m); //当table_name为空的时候，将其当作m来处理
在所有数据库中，只要有null参与的运算，最终结果都是NULL
数据库中，只要有null参与的运算，最终的结果都是空，这显然是不符合我们的预期的，所以之类就要使用IFNULL来处理
mysql> SELECT
-> *
-> FROM
-> fruits;
+------+-------------+--------------+---------------+---------------+--------------+
| id | fruits_name | fruits_price | fruits_origin | fruits_remark | fruits_bonus |
+------+-------------+--------------+---------------+---------------+--------------+
| 1001 | 葡萄 | 3.7 | 山东 | 红香妃 | NULL |
| 1002 | 苹果 | 2.8 | 山东 | 红富士 | NULL |
| 1003 | 香蕉 | 6 | 海南 | 小芭蕉 | NULL |
+------+-------------+--------------+---------------+---------------+--------------+
3 rows in set (0.00 sec)
这里如果将price和最后一行相加就可能会出现错误，所以使用IFNULL处理最后一行数据
IFNULL的用法是IFNULL(处理的数据，如果为空当作哪个数据)
mysql> SELECT
-> fruits_price + IFNULL(fruits_bonus,0) AS '水果总价'
-> FROM
-> fruits;
+----------+
| 水果总价 |
+----------+
| 3.7 |
| 2.8 |
| 6 |
+----------+
3 rows in set (0.00 sec)
所以这里的意思是如果是NULL，就将数据当作0来与前面的数据相加
在没有连接数据库时，可以使用base_name.table_name来直接操作表
mysql> SELECT *
-> FROM
-> cfengbase.fruits;
+------+-------------+--------------+---------------+---------------+--------------+
| id | fruits_name | fruits_price | fruits_origin | fruits_remark | fruits_bonus |
+------+-------------+--------------+---------------+---------------+--------------+
| 1001 | 葡萄 | 3.7 | 山东 | 红香妃 | NULL |
| 1002 | 苹果 | 2.8 | 山东 | 红富士 | NULL |
| 1003 | 香蕉 | 6 | 海南 | 小芭蕉 | NULL |
+------+-------------+--------------+---------------+---------------+--------------+
3 rows in set (0.00 sec)
CASE ……WHEN……THEN……WHEN……THEN……ELSE……END
程序中经常有分裂处理的需求，一般就是用if else或者switch语句进行处理，在java中还可以使用策略模式解决问题，数据库查询的时候有的时候也会遇到这样的情况
这个时候就可以使用这里的case来处理了
这里匹配的就是col_name，所以when后面就是具体的值，就和LIke是相同的还有IN，都是代表的相等
CASE col_name WHEN …… THEN ……WHEN ……THE……ELSE……END
比如这里演示处理cfengbase中的fruits表，当水果名是苹果时，价格上调2，当水果名是香蕉师，水果价格上调1，其他就不变
mysql> SELECT
-> fruits_name,CASE fruits_name WHEN '葡萄'THEN fruits_price+3 WHEN '苹果'THEN fruits_price+1 ELSE fruits_price END
-> FROM
-> fruits;
+-------------+------------------------------------------------------------------------------------------------------+
| fruits_name | CASE fruits_name WHEN '葡萄'THEN fruits_price+3 WHEN '苹果'THEN fruits_price+1 ELSE fruits_price END |
+-------------+------------------------------------------------------------------------------------------------------+
| 葡萄 | 6.7 |
| 苹果 | 3.8 |
| 香蕉 | 6 |
+-------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT
-> (fruits_name,CASE fruits_name WHEN '葡萄'THEN fruits_price+3 WHEN '苹果'THEN fruits_price+1 ELSE fruits_price END) AS '水果新价格'
-> FROM
-> fruits;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT
-> fruits_name,(CASE fruits_name WHEN '葡萄'THEN fruits_price+3 WHEN '苹果'THEN fruits_price+1 ELSE fruits_price END) AS '水果新价格'
-> FROM
-> fruits;
+-------------+------------+
| fruits_name | 水果新价格 |
+-------------+------------+
| 葡萄 | 6.7 |
| 苹果 | 3.8 |
| 香蕉 | 6 |
+-------------+------------+
3 rows in set (0.00 sec)
这里操纵的字段就是price，所以起的别名就是price，和case后面的量是没有关系的，另外一个点就是起别名只能是一个字段，第二项就是只能包含一项，两项所以就报错了
mysql> SELECT
-> fruits_name,fruits_price '水果原价格',(CASE fruits_name WHEN '葡萄'THEN fruits_price+3 WHEN '苹果'THEN fruits_price+1 ELSE fruits_price END) AS '水果新价格'
-> FROM
-> fruits;
+-------------+------------+------------+
| fruits_name | 水果原价格 | 水果新价格 |
+-------------+------------+------------+
| 葡萄 | 3.7 | 6.7 |
| 苹果 | 2.8 | 3.8 |
| 香蕉 | 6 | 6 |
+-------------+------------+------------+
3 rows in set (0.00 sec)
日期时间函数之后单独讲解，接下来就是看一下分组处理函数了
多行处理函数（分组函数）
之前分析的时候已经分析过了，单行处理函数处理字段之后就是每一行，也就是每一个数据处理之后都会得到一个结果，但是多行处理函数就是多行才对应一个输出，比如max，sum就是多行才能得到一个结果
从最开始的查询就是WHERE，而单行的数据处理函数都是在LIne 2 选择字段的时候对字段进行的处理
多行处理函数的特点： 输入多行，最后输出一行
- count 计数
- sum 求和
- avg 求平均值
- max 最大值
- min 最小值
注意： 分组函数在使用时总是要先进行分组，然后才能使用，如果没有进行分组，那就默认将整张表当做一组
MAX ,MIN
max是找出数据中的最大值，和简单的单行处理处理函数一样，就是处理的是字段，位置在第二行，不同的是，输出的数据只有1行了
max(col)
min(col)
比如那张有4079行数据的city表，找出人口最大的数据
mysql> SELECT
-> MAX(Population)
-> FROM
-> city;
+-----------------+
| MAX(Population) |
+-----------------+
| 10500000 |
+-----------------+
1 row in set (0.02 sec)
同时可以使用where条件查询来获得人口最少城市的数据
注意，不能将max函数放在where后面来直接使用，要先查询出来之后才能进行精确的搜索
mysql> SELECT
-> *
-> FROM
-> city
-> WHERE
-> Population = MAX(Population);
ERROR 1111 (HY000): Invalid use of group function
mysql> SHOW ERRORS;
+-------+------+-------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------+
| Error | 1111 | Invalid use of group function |
+-------+------+-------------------------------+
1 row in set (0.00 sec)
直接搜索会报上面的错误
多行处理函数和单行处理函数的处理是不一样的，单行处理函数可以直接搭配字段，并且因为每一行都会输出一个结果，所以每一个字段都可以得到处理，多行处理函数不能搭配其他字段使用
mysql> SELECT
-> ID,Name,CountryCode,District,MAX(Population)
-> FROM
-> city;
+------+-------+-------------+----------+-----------------+
| ID | Name | CountryCode | District | MAX(Population) |
+------+-------+-------------+----------+-----------------+
| 1 | Kabul | AFG | Kabol | 10500000 |
+------+-------+-------------+----------+-----------------+
这里搭配使用，发现错误是，MAX字段自动输出最大的结果，一共只能输出一个结果，所以其他的字段的输出都是按照第一行的数据来输出的，这显然是不符合要求的，所以只能将数据拿出来之后，单独使用最大值进行数据的搜索
mysql> SELECT
-> *
-> FROM
-> city
-> WHERE
-> Population = 10500000;
+------+-----------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 |
+------+-----------------+-------------+-------------+------------+
1 row in set (0.00 sec)
COUNT 计数 AVG 平均值 SUM 求和
这几个分组函数的使用都是相似的，这里演示一下
其实这几个函数的搜索就是字面量的搜索，因为select后面跟的就是处理后的结果，也即是数据字面量
mysql> SELECT
-> COUNT(Name) AS '城市总数',MAX(Population) '最大人口数',MIN(Population) '最小人口数',AVG(Population) '人口平均数',SUM(Population) '人口总数'
-> FROM
-> city;
+----------+------------+------------+-------------+------------+
| 城市总数 | 最大人口数 | 最小人口数 | 人口平均数 | 人口总数 |
+----------+------------+------------+-------------+------------+
| 4079 | 10500000 | 42 | 350468.2236 | 1429559884 |
+----------+------------+------------+-------------+------------+
1 row in set (0.00 sec)
所以只能和分组函数处理的结果并列，而不能和没有处理的普通字段并列
分组函数使用的注意事项
自动忽略NULL
之前的单行处理函数有一个函数是IFNULL(col_name,m),就是因为如果有null，整个运算结果也即使null，但是多行处理函数就会自动处理空NULL
+------+-------------+--------------+---------------+---------------+--------------+
| id | fruits_name | fruits_price | fruits_origin | fruits_remark | fruits_bonus |
+------+-------------+--------------+---------------+---------------+--------------+
| 1001 | 葡萄 | 3.7 | 山东 | 红香妃 | NULL |
| 1002 | 苹果 | 2.8 | 山东 | 红富士 | NULL |
| 1003 | 香蕉 | 6 | 海南 | 小芭蕉 | NULL |
+------+-------------+--------------+---------------+---------------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT
-> SUM(fruits_bonus)
-> FROM
-> fruits;
+-------------------+
| SUM(fruits_bonus) |
+-------------------+
| 0 |
+-------------------+
1 row in set, 3 warnings (0.00 sec)
这里三个null求和的结果是0，就因为这里将null给忽略成空了，不需要提前对null进行处理,null代表什么也没有，在条件查询中判断null也要使用IS
不需要叫条件，比如 IS NOT NULL
COUNT(*)与COUNT(col_name)的区别
也许会疑惑着两者的区别，其实两者的区别还是有的，原因就是上面那条导致的
因为所有的分组处理函数都是会忽略空的，所以count也当然会忽略空了，那么如果col_name中有null，那就只会计数不空的数据，
-
count(*)中 ＊代表所有的字段，所以这个的意思就是统计一共有多少行，不存在一行的所有数据都是空的,只要有一行的数据有一个不为空，那么数据就是有效的
-
count(col_name) 就是统计col_name字段下的所有不空的数据，所以
count(col_name ) <= count(*)
mysql> SELECT
-> COUNT(*),COUNT(District)
-> FROM
-> city;
+----------+-----------------+
| COUNT(*) | COUNT(District) |
+----------+-----------------+
| 4079 | 4079 |
+----------+-----------------+
1 row in set (0.00 sec)
分组函数不能直接使用在WHERE字段中
上面分析最大值MAX的时候就尝试过了，MAX也是在第二行，不能再WHERE中直接使用，否则会报错
+-------+------+-------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------+
| Error | 1111 | Invalid use of group function |
+-------+------+-------------------------------+
报错的信息是分组函数的无效使用,分组查询【GROUP BY】
这里再分组查询的时候会详细分析原因
也就是因为分组函数要分组之后才能使用，如果没有分组，则默认为所有数据为一组，根据执行顺序，是过滤WHERE之后才会进行分组ORDER BY，WHERE之后还没有分组，所以不能执行，SELECT
所有的分组函数可以组合一起用
上面已经演示过了，其实分组函数的作用就是将数据处理成为一个字面量，也就是SELECT的第二种选取方式，选取的数据都是一行，所以不能和没有处理的字段一起使用，只能和同样的分组函数来一起使用，因为输出的都是同一组的处理后的一个结果‘
mysql> SELECT
-> COUNT(*) AS '城市总数',MAX(Population) '最大人口数',MIN(Population) '最小人口数',AVG(Population) '人口平均数',SUM(Population) '人口总数'
-> FROM
-> city;
+----------+------------+------------+-------------+------------+
| 城市总数 | 最大人口数 | 最小人口数 | 人口平均数 | 人口总数 |
+----------+------------+------------+-------------+------------+
| 4079 | 10500000 | 42 | 350468.2236 | 1429559884 |
+----------+------------+------------+-------------+------------+
1 row in set (0.00 sec)
这里可以使用简单的条件查询语句先进行分组之后再进行操作
//也就是先选取再进行的运算
mysql> SELECT
-> MIN(Population)
-> FROM
-> city
-> WHERE
-> ID <= 10;
+-----------------+
| MIN(Population) |
+-----------------+
| 127800 |
+-----------------+
1 row in set (0.00 sec)
分组查询
之前已经介绍过简单查询和条件查询，简单查询主要是选取的字段还有别名问题以及对字段进行简单的四则运算，条件查询使用WHERE关键字可以限制条件进行查询，其中包括模糊查询，代表的都是=
那分组查询就是先对数据进行分组，之后对每一组数据进行操作，这个时候就要使用到分组查询了
基本格式为
SELECT
……
FROM
……
GROUP BY
……
之前的所有关键字的顺序为
SELECT
……
FROM
……
WHERE
……
GROUP BY
……
ORDER BY
……
这里的执行顺序就是先from，之后过滤WHERE，之后分组GROUP BY，分组之后再SELECT,最后在ORDER BY来进行排序，SELECT后面就是其语句，也就是说之前的单行处理函数，还有多行函数都是在SELECT中就是已经ORDER BY了，所以是可以使用的，在WHERE时还没有处理ORDER BY，所以不行
也就是如果没有排序，那么selsect语句就是最后执行的，所以函数都是可以默认执行的
GROUP BY col_name
按照字段col_name 进行分组，就是所有col_name相同的为一组，这里举例按照不同的CountryCode进行分组
mysql> SELECT
-> CountryCode,MAX(Population)
-> FROM
-> city
-> WHERE
-> ID <= 100
-> GROUP BY
-> CountryCode;
+-------------+-----------------+
| CountryCode | MAX(Population) |
+-------------+-----------------+
| AFG | 1780000 |
| NLD | 731200 |
| ANT | 2345 |
| ALB | 270000 |
| DZA | 2168000 |
| ASM | 5200 |
| AND | 21189 |
| AGO | 2022000 |
| AIA | 961 |
| ATG | 24000 |
| ARE | 669181 |
| ARG | 2982146 |
+-------------+-----------------+
12 rows in set (0.01 sec)
这里的语句执行的顺序是，先From city；之后进行WHERE ID <= 100进行过滤，过滤之后GROUP BY CountyrCode进行分组，分组之后就进行SELECT CountryCode，Max(Population)按照分组进行输出不同分组的结果，这里和上面的同时写普通字段不同，哪里只有一组，所以就混乱了
- 这里要考虑一个问题，上面提到过了，就是在分组函数时，携带其他的字段？
SELECT
Name,CountryCode,MAX(Population)
FROM
city
WHERE
ID <= 100
GROUP BY
CountryCode
这个和上面所说的问题也要，在Mysql中可以执行但是没有一样，但是在Oracle中是不合法的
这是因为这里的数据是按照CountryCode分组的，所以CountryCode是可以的，Name就没有意义，上面的没有GROUP BY，所以所有的是一组，没有按照任何的字段分组，那么分组函数就不能和任何的字段并列。
也就是只有分组的字段才能和分组函数并列，在一条SELECT语句当中，如果GROUP BY语句。SELECT语句后面只能跟 ： 参加分组的字段，以及分组函数，其他的一律不准跟
多次分组查询
之前查询时讲过多字段查询，后面排序的时候也提到了多个依据排序，那分组也就可以多依据分组了，就是先按照col_name分组，当分组相同的时候，再按照另外的标准分组
GROUP BY col_name,col_name2;
就是先按照col_name进行分组，当col_name相同的时候，再按照col_name2进行分组
mysql> SELECT
-> CountryCode,District,MAX(Population)
-> FROM
-> city
-> WHERE
-> ID <= 100
-> GROUP BY
-> CountryCode,District;
+-------------+------------------+-----------------+
| CountryCode | District | MAX(Population) |
+-------------+------------------+-----------------+
| AFG | Kabol | 1780000 |
| AFG | Qandahar | 237500 |
| AFG | Herat | 186800 |
| AFG | Balkh | 127800 |
| NLD | Noord-Holland | 731200 |
| NLD | Zuid-Holland | 593321 |
| NLD | Utrecht | 234323 |
| NLD | Noord-Brabant | 201843 |
| NLD | Groningen | 172701 |
| NLD | Gelderland | 153491 |
| NLD | Overijssel | 149544 |
| NLD | Flevoland | 142465 |
| NLD | Limburg | 122087 |
| NLD | Drenthe | 105853 |
| ANT | Cura?ao | 2345 |
| ALB | Tirana | 270000 |
| DZA | Alger | 2168000 |
| DZA | Oran | 609823 |
| DZA | Constantine | 443727 |
| DZA | Annaba | 222518 |
| DZA | Batna | 183377 |
| DZA | Sétif | 179055 |
| DZA | Sidi Bel Abbès | 153106 |
| DZA | Skikda | 128747 |
| DZA | Biskra | 128281 |
| DZA | Blida | 127284 |
| DZA | Béja?a | 117162 |
| DZA | Mostaganem | 115212 |
| DZA | Tébessa | 112007 |
| DZA | Tlemcen | 110242 |
| DZA | Béchar | 107311 |
| DZA | Tiaret | 100118 |
| DZA | Chlef | 96794 |
| DZA | Gharda?a | 89415 |
| ASM | Tutuila | 5200 |
| AND | Andorra la Vella | 21189 |
| AGO | Luanda | 2022000 |
| AGO | Huambo | 163100 |
| AGO | Benguela | 130000 |
| AGO | Namibe | 118200 |
| AIA | – | 961 |
| ATG | St John | 24000 |
| ARE | Dubai | 669181 |
| ARE | Abu Dhabi | 398695 |
| ARE | Sharja | 320095 |
| ARE | Ajman | 114395 |
| ARG | Distrito Federal | 2982146 |
| ARG | Buenos Aires | 1266461 |
| ARG | Córdoba | 1157507 |
| ARG | Santa Fé | 907718 |
| ARG | Tucumán | 470809 |
| ARG | Salta | 367550 |
| ARG | Corrientes | 258103 |
| ARG | Chaco | 229212 |
| ARG | Entre Rios | 207041 |
+-------------+------------------+-----------------+
55 rows in set (0.00 sec)
- 技巧：两个字段联合成一个字段来看【两个字段联合分组】
只是这里要注意顺序，CountryCode在District之前
SELECT只能跟参加分组的字段
HAVING 联合GROUP BY 进行再过滤
写SQL语句的时候WHERE在GROUP之前，有可能WHERE过滤的不够彻底，使用GROUP BY之后获得的数据不满意，第一种就是直接在WHERE里面进行详细的过滤，第二种就是分组之后使用HAVING 再次过滤
GROUP BY
col_name
HAVING
MAX(col) ……
这里可以试验一下
比如上面的过滤不满意，要删选出人口数量大于40 0000的
mysql> SELECT
-> CountryCode,District,MAX(Population)
-> FROM
-> city
-> WHERE
-> ID <= 100
-> GROUP BY
-> CountryCode,District
-> HAVING
-> MAX(Population) > 400000;
+-------------+------------------+-----------------+
| CountryCode | District | MAX(Population) |
+-------------+------------------+-----------------+
| AFG | Kabol | 1780000 |
| NLD | Noord-Holland | 731200 |
| NLD | Zuid-Holland | 593321 |
| DZA | Alger | 2168000 |
| DZA | Oran | 609823 |
| DZA | Constantine | 443727 |
| AGO | Luanda | 2022000 |
| ARE | Dubai | 669181 |
| ARG | Distrito Federal | 2982146 |
| ARG | Buenos Aires | 1266461 |
| ARG | Córdoba | 1157507 |
| ARG | Santa Fé | 907718 |
| ARG | Tucumán | 470809 |
+-------------+------------------+-----------------+
13 rows in set (0.00 sec)
这里就是先进行的操作FROM，之后WHERE ,再之后GROUP BY，之后HAVING 过滤输出,最后SELECT 选择
当WHERE可以过滤掉时，优先选择WHERE，因为HAVING效率过低，相当于时分组之后又舍去了很多数据，效率太低了
WHERE实在完成不了工作时，再使用HAVING
- WHERE完成不了的工作，将上面的人口最大值换成平均值，那么WHERE是不可能过滤掉的，比如选择平均人口大于400000？
mysql> SELECT
-> CountryCode,District,AVG(Population)
-> FROM
-> city
-> WHERE
-> ID <= 100
-> GROUP BY
-> CountryCode,District
-> HAVING
-> AVG(Population) > 400000;
+-------------+------------------+-----------------+
| CountryCode | District | AVG(Population) |
+-------------+------------------+-----------------+
| AFG | Kabol | 1780000.0000 |
| DZA | Alger | 2168000.0000 |
| DZA | Oran | 609823.0000 |
| DZA | Constantine | 443727.0000 |
| AGO | Luanda | 2022000.0000 |
| ARE | Dubai | 669181.0000 |
| ARG | Distrito Federal | 2982146.0000 |
| ARG | Buenos Aires | 413425.6957 |
| ARG | Córdoba | 1157507.0000 |
| ARG | Santa Fé | 630390.5000 |
| ARG | Tucumán | 470809.0000 |
+-------------+------------------+-----------------+
11 rows in set (0.00 sec)
WHERE中不可能使用AVG(Population) > 400000
SELECT
……
FROM
……
WHERE
……
GROUP BY
……
HAVING
……
ORDER BY
…… //顺序不能颠倒
这里的执行顺序就是将SELECT放到ORDRE BY 之前就行了
mysql> SELECT
-> CountryCode,AVG(Population) AS avgpop
-> FROM
-> city
-> WHERE
-> ID <= 100 AND CountryCode <> 'ARG'
-> GROUP BY
-> CountryCode
-> HAVING
-> AVG(Population) > 400000
-> ORDER BY
-> avgpop DESC;
+-------------+-------------+
| CountryCode | avgpop |
+-------------+-------------+
| AFG | 583025.0000 |
| AGO | 512320.0000 |
+-------------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT
-> CountryCode,AVG(Population) AS avgpop
-> FROM
-> city
-> WHERE
-> ID <= 100
-> GROUP BY
-> CountryCode
-> HAVING
-> AVG(Population) > 400000
-> ORDER BY
-> avgpop DESC;
+-------------+-------------+
| CountryCode | avgpop |
+-------------+-------------+
| AFG | 583025.0000 |
| ARG | 513810.6250 |
| AGO | 512320.0000 |
+-------------+-------------+
3 rows in set (0.00 sec)
288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
