GROUP BY

Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理

1.新建表如下

MariaDB [sawyer]> create table groupby(type varchar(5) not null,number tinyint(4),exp varchar(100));
MariaDB [sawyer]> desc groupby_test;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| type   | varchar(5)   | NO   |     | NULL    |       |
| number | tinyint(4)   | NO   |     | NULL    |       |
| exp    | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2.向表内插入数据
MariaDB [sawyer]> insert groupby_test values('a',5,'a2002'),('a',2,'a2001'),('b',10,'b2003'),('b',6,'b2002'),('b',3,'b2001'),('c',9,'c2005'),('c',9,'c2004'),('c',8,'c2003'),('c',7,'c2002'),('c',4,'c2001'),('a',11,'a2001');
Query OK, 11 rows affected (0.001 sec)
Records: 11  Duplicates: 0  Warnings: 0
MariaDB [sawyer]> select * from groupby_test;
+------+--------+-------+
| type | number | exp   |
+------+--------+-------+
| a    |      5 | a2002 |
| a    |      2 | a2001 |
| b    |     10 | b2003 |
| b    |      6 | b2002 |
| b    |      3 | b2001 |
| c    |      9 | c2005 |
| c    |      9 | c2004 |
| c    |      8 | c2003 |
| c    |      7 | c2002 |
| c    |      4 | c2001 |
| a    |     11 | a2001 |
+------+--------+-------+
11 rows in set (0.001 sec)
3.简单Group By
select type '类型',sum(number) '最大值' from groupby_test GROUP BY type;
这里以type字段进行分组，将相同类别的数量进行比较，取各个类别中的数量最大值
4.GROUP BY与Order by
select type '类型',sum(number) '最大值' from groupby_test GROUP BY type ORDER BY sum(number) desc;
5.组合GROUP BY
SELECT
	type '类型',
	sum( number ) '最大值',
	exp '说明' 
	groupby_test 
GROUP BY
	type,
	groupby_test.exp 
ORDER BY
	sum( number ) DESC
当group by 后接多个字段时，我们可以将多个字段看作一个整体，这里只有type=a的2行内容满足条件，所以其和为11+2=13.
Group By与聚合函数
由于GROUP BY用法下，会出现一个字段内存在多个值出现，这在sql中时不允许的。所以GROUPBY经常与聚合函数一起使用。
1.创建两张表
MariaDB [sawyer]> create table com_list(id int(4) not null primary key auto_increment,name varchar(50) not null);
Query OK, 0 rows affected (0.007 sec)
#修改表相应字段的编码类型
ariaDB [sawyer]> alter table com_list modify name varchar(50) character set utf8;
Query OK, 0 rows affected (0.013 sec)              
Records: 0  Duplicates: 0  Warnings: 0
#导入数据
MariaDB [sawyer]> insert com_list values(1,'Google'),(2,'淘宝'),(3,'微博'),(4,'Facebook');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0
MariaDB [sawyer]> select * from com_list;
+----+----------+
| id | name     |
+----+----------+
|  1 | Google   |
|  2 | 淘宝     |
|  3 | 微博     |
|  4 | Facebook |
+----+----------+
4 rows in set (0.000 sec)
#创建nation_list表(修改编码为UTF8)
MariaDB [sawyer]> create table nation_list(id int(4) primary key not null,address varchar(50)) default charset = utf8;
#添加数据
MariaDB [sawyer]> insert nation_list values(1,'美国'),(5,'中国'),(3,'中国'),(6,'
美国');
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0
MariaDB [sawyer]> select * from nation_list;
+----+---------+
| id | address |
+----+---------+
|  1 | 美国    |
|  3 | 中国    |
|  5 | 中国    |
|  6 | 美国    |
+----+---------+
4 rows in set (0.000 sec)
2.将两张表INNER JOIN
SELECT
	a.id,
	a.NAME,
	b.address 
	com_list a
	INNER JOIN nation_list b ON a.id = b.id
执行结果如下

LEFT JOIN
LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
LEFT JOIN 语法
select column_name(s)
from table 1
LEFT JOIN table 2
ON table 1.column_name=table 2.column_name
同样将上两张表进行LEFT JOIN连接
SELECT
	a.id,
	a.NAME,
	b.address 
	com_list a
	LEFT JOIN nation_list b ON a.id = b.id
执行结果如下

RIGHT JOIN
RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替
RIGHT JOIN语法
select column_name(s)
from table 1
RIGHT JOIN table 2
ON table 1.column_name=table 2.column_name
将上两张table进行RIGHT JOIN操作
SELECT
	a.id,
	a.NAME,
	b.address 
	com_list a
	RIGHT JOIN nation_list b ON a.id = b.id
执行结果如下