MySQL分组统计与多表联合查询的基本知识归纳总结

一 分组统计

count( )    统计记录数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 统计student表中所有行数
-- 统计对整张表的所有行的数目,含有NULL值的行也会被计入
select count(*) from student;
 
-- 与count(*)是一样的,这里的1没有特殊意义,也可以写成count(2),count(3)...
-- 不过某些数据库会对count(1)进行优化,从而导致count(1)比count(*)更快
-- 因为不知道MySQL具体优化没优化,所以建议尽量使用count(1)而不使用count(*)进行查询
select count(1) from student;
 
-- 统计指定字段的所有行的数目,该字段值为NULL的行不会被计入
select count(id) from student;
 
-- 优化建议:如果某张表count用的比较多时,可以在最短的字段建立一个辅助索引
-- 会极大地提升count性能。
min( )    统计指定字段的最小值
?
1
2
-- 统计student表中age字段的最小值
select min(age) from student;
max( )    统计指定字段的最大值
?
1
2
-- 统计student表中age字段的最大值
select max(age) from student;

avg( )    统计指定字段的平局值

?
1
2
-- 统计student表中age字段的平均值
select avg(age) from student;
sum( )    统计指定字段的和
?
1
2
-- 统计student表中age字段的和
select sum(age) from student;
group by    对指定字段进行分组
?
1
2
-- 统计student表中每个年龄的总数
select age,count(age) from student group by age;
having    对分组后的数据进行条件筛选,与where相似,但是只针对分组后的数据,where无法筛选聚合函数

?
1
2
-- 统计student表中年龄大于20的每个年龄的总数
select age,count(age) from student group by age having age > 20;
二 多表联合查询

笛卡尔积

笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。

?
1
2
3
4
5
6
7
8
-- MySQL中有4种写法可以获得笛卡尔积,这4种写法MySQL运行效果是没有任何区别的
select * from student,class;
 
select * from student inner join class;
 
select * from student cross join class;
 
select * from student join class;
inner join    内连接,也叫等值连接,查询符合A表与B表匹配关系的数据

?
1
2
3
4
5
6
7
8
-- 查询出name为leo并且含有所在班级信息的数据
select * from student inner join class on student.cid = class.id where student.name = 'leo';
+----+------+-----+----+-----------+
| id | name | cid | id | classname |
+----+------+-----+----+-----------+
|  6 | leo  |   2 |  2 | 二班      |
+----+------+-----+----+-----------+
1 row in set (0.00 sec)

注释 : join inner join 是一样的效果,所以在使用inner join时可将inner省略。

​显式( explicit ) inner join 与 隐式( implicit ) inner join

1
2
3
4
5
6
-- 显式(explicit)
select * from student join class on student.cid = class.id;
 
-- 隐式(implicit)
select * from student,class where student.cid = class.id;
-- 以上2条语句分别是显式inner join 与隐式inner join,MySQL在执行这2条语句时没有任何区别

left join    左连接,从左表产生一套完整的记录,与匹配的记录(右表) .如果没有匹配,右侧将包含null。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 根据student表的id字段进行匹配
select * from student left join class on student.id = class.id
+----+----------------+-----+------+-----------+
| id | name           | cid | id   | classname |
+----+----------------+-----+------+-----------+
|  1 | Alicia Keys    |   3 |    1 | 一班       |
|  2 | Bob            |   4 |    2 | 二班       |
|  3 | Alice          |   2 |    3 | 三班       |
|  4 | Beyonce        |   1 |    4 | 四班       |
|  5 | Marilyn Manson |   4 |    5 | 五班       |
|  6 | leo            |   2 | NULL | NULL      |
+----+----------------+-----+------+-----------+
6 rows in set (0.00 sec)

注释 : left join 与 left outer join 是一样的效果,所以在使用left outer join时可将outer省略

right join    右连接,从右表产生一套完整的记录,与匹配的记录(左表) .如果没有匹配,右侧将包含null。

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 根据class表的classname字段进行匹配
select * from student right join class on student.id = class.classname
+------+------+------+----+-----------+
| id   | name | cid  | id | classname |
+------+------+------+----+-----------+
| NULL | NULL | NULL |  1 | 一班         |
| NULL | NULL | NULL |  2 | 二班          |
| NULL | NULL | NULL |  3 | 三班          |
| NULL | NULL | NULL |  4 | 四班         |
| NULL | NULL | NULL |  5 | 五班          |
+------+------+------+----+-----------+
5 rows in set (0.00 sec)

full join    全连接,产生的所有记录(双方匹配记录)在表A和表B。如果一张表没有匹配,则另一张表将包含null。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- full join MySQL本身没有full join,但是可以使用union关键词来模拟full join
select * from student left join class on student.id = class.id
union
select * from student right join class on student.id = class.classname
+------+----------------+------+------+-----------+
| id   | name           | cid  | id   | classname |
+------+----------------+------+------+-----------+
| NULL | NULL           | NULL |    1 | 一班         |
| NULL | NULL           | NULL |    2 | 二班          |
| NULL | NULL           | NULL |    3 | 三班          |
| NULL | NULL           | NULL |    4 | 四班         |
| NULL | NULL           | NULL |    5 | 五班          |
|    1 | Alicia Keys    |    3 |    1 | 一班         |
|    2 | Bob            |    4 |    2 | 二班          |
|    3 | Alice          |    2 |    3 | 三班          |
|    4 | Beyonce        |    1 |    4 | 四班         |
|    5 | Marilyn Manson |    4 |    5 | 五班          |
|    6 | leo            |    2 | NULL | NULL      |
+------+----------------+------+------+-----------+
11 rows in set (0.00 sec)

自连接    与表自身进行检索操作,in other words 就是与另一张一模一样的表进行join关联操作

1
2
3
4
5
6
-- 在不知道leo同学班级id的情况下查询出student表中与leo一个班级的同学
-- 子查询方式
select name from student where cid in (select cid from student where name = 'leo') and name != 'leo';
 
-- 自连接方式
select a.name from student as a join student as b on a.cid = b.cid where b.name = 'leo' and a.name != 'leo';
注释:可以看出子查询的方式需要查询2次student表,而自连接的方式是笛卡尔积,只需要查询一次student表,效率方面肯定是高于子查询方式的,所以在能够使用自连接方式查询的时候尽量不要使用子查询

发布评论
还没有评论,快来抢沙发吧!