es查询多张表

1. 聚合查询

一般需要搭配MySQL中的一些内置“函数”

1）count：用来计算结果的行数

<mysql> select name,decription from user;
+--------+--------------+
| name   | decription   |
+--------+--------------+
| 曹操   | 乱世枭雄     |
| 刘备   | 仁德之主     |
| 孙权   | 年轻有为     |
+--------+--------------+
3 rows in set (0.00 sec)

<mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.03 sec)

2）sum

<mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    28.5 | 80.0 |    67.8 |
|    3 | 沙和尚    |    61.7 | 78.6 |    48.5 |
|    4 | 猪八戒    |    66.0 | 80.0 |    67.8 |
|    5 | 孙仲谋    |    21.0 | 23.0 |    91.5 |
|    6 | 曹孟德    |    67.5 | 75.5 |    67.8 |
|    7 | 刘玄德    |    66.9 | 80.0 |    69.8 |
+------+-----------+---------+------+---------+
6 rows in set (0.00 sec)

<mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|     417.1 |
+-----------+
1 row in set (0.00 sec)

3）avg

<mysql> select avg(math) from exam_result;
+-----------+
| avg(math) |
+-----------+
|  69.51667 |
+-----------+
1 row in set (0.00 sec)

4）max

<mysql> select max(math) from exam_result;
+-----------+
| max(math) |
+-----------+
|      80.0 |
+-----------+
1 row in set (0.03 sec)

5）min

<mysql> select min(math) from exam_result;
+-----------+
| min(math) |
+-----------+
|      23.0 |
+-----------+
1 row in set (0.00 sec)

求小于80的同学的平均分

<mysql> select avg(math) from exam_result where math < 80;
+-----------+
| avg(math) |
+-----------+
|  59.03333 |
+-----------+
1 row in set (0.00 sec)

6）group by
把得到的查询结果集按照一定的规律分组（可以分成多个组）

select [列] from [表名] group by [前面的列]；

使用以上语句，就可以将这一列重复的类型压缩成一个

如果要求平均值

select [列] ，avg(列) from [表名] group by [前面的列]；

如果针对group by进行筛选，使用having

select [列] ，avg(列) from [表名] group by [前面的列] having avg(列) > 250;

1. 联合/多表查询

实现联合查询的基本机制：笛卡尔积

多表查询的过程就是先计算两张表的笛卡尔积，再根据一些条件对笛卡尔积中的记录进行筛选

如果针对两个比较大的表进行联合查询，笛卡尔积的计算开销会很大，最终的查找效率也比较低，在生产环境中，不应该对达标进行联合查询。

例子：

已经创建了四个表结构

1）内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

a）查找名字为“许仙”的分数

select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id and student.name ='许仙';

把where后面的条件称之为：连接条件

另外一种写法为：

（join on 方式）

select student.id,student.name,score.student_id,score.score from student inner join score on student.id = score.student_id and student.name ='许仙';

b）查找所有同学的总成绩以及基本信息

select student.id,student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;

b）查找所有同学的每一科的成绩以及基本信息

select student.name, course.name, score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

2）自连接

自连接是指在同一张表连接自身进行查询

a）“计算机原理”分数大于“java”的分数的同学

select s1.student_id, s1.score, s1.course_id, s2.student_id, s2.score, s2.course_id from score s1,score s2 
 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

3）子查询

a）和某一同学同班的同学

select name from student where classes_id = (select classes_id from student where name = '不想毕业');

b）查询语文或者英文课程所对应的成绩

借助in：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

借助exist也可以：

select * from score where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);

4）合并查询

相当于把多个表查询的结果集合合并成一个集合（需要保证多个结果集之间的字段和数目都得一致）

a）查询id<3或者是英文课程

方法一：

方法二：