DQL数据查询语言（多表处理）—/—＜7＞

一、多表处理

当前有两个表，一个是学生表student，一个是分数表score

student表字段名表示如下（共1000条数据）：

score表字段表示如下（共6000条数据）：

1、求每个学生的总分

SELECT id,SUM(score)
FROM score
GROUP BY id

2、将上述的总分与student表合并

 a表 join b表 on 条件

使用join on 函数来合并两个表，join前后各一个表表示合并，默认为内连接，on后面写两个表的关联条件

实例代码如下：

SELECT * FROM
(SELECT id,SUM(score) FROM score GROUP BY id) t1
JOIN student t2
ON t1.id=t2.id

代码解析：

        将上述1中生成的总分分数的表用括号括起来并命名为t1，然后再使用join函数将它与student表合并，student表取别名为t2，条件是t1表中的id等于t2表中的id，然后再使用select * from将这一整个表生成出来，此处*号可以更改，使用t1.id输出t1表中的id字段内容，也可使用t1.*来输出t1表总的所有数据，同样也可更改t2来取你想要数据

3、解析join

例如有两个表如下，第一个是person表，第二个是height表

    

1）内连接inner：

此时单单使用join取合并这两个表，其得到的结果如图所示

           

可以发现，数id=3与id=4的人没有合并出来，此时默认为inner join，可以不写inner

2）左连接left join、右连接right join

左连接：即保留join左边的所有数据，join前面的表就是左表，后面的表就是右表

   

右连接：即保留join右边的所有数据

    

3）全连接union

去重合并 union

例如下列代码，使用union合并两个表，一个是学生表的前十条数据，另一个是学生表的第五条开始的后十条数据，其合并结果会有去重的作用，即除去合并时出现的完全相同的行

其结果为：

不去重合并 union all

如果想要不去重可以使用union all ，不去重的合并所有数据

其运行结果为：此时重复数据没有被去除

此时如果想合并前面的两个person表和height表，可以直接把左合并与右合并分别用括号括起来,分别当做一个表，然后再使用union将两个表的数据完全合并，即可得到下列全连接形式

 4、隐式内连接（古董写法）

SELECT *
FROM student t1,score t2
WHERE t1.id=t2.id

其结果同样和join内连接合并的一样

5、求每个班的平均分

直接给出全部代码：

SELECT clazz,AVG(tt1.sum_sco)
FROM(SELECT t2.*,t1.sum_scoFROM(SELECT id,SUM(score) sum_sco FROM score GROUP BY id) t1RIGHT JOIN student t2ON t1.id=t2.id) tt1
GROUP BY clazz

接下来跟着思路走：

1、分析表

        两张表，学生表和分数表，分数表里有学生id、科目id和分数，学生表里有学生id、学生姓名、年龄、性别以及班级

student：

score：

2、分析思路

        要求班级平均成绩首先要把班级总分求出来，要求班级总分首先要有每个学生的总成绩

如此思路即可：

1）求学生总分：

对每个学生进行分组，对每个科目的分数求和，得到以下结果

SELECT id,SUM(score) sum_sco --取别名
FROM score 
GROUP BY id

2）合并表

        求出总分后将总分合并到学生表，得到下列代码：

SELECT t2.* -- 打印结果只需要t2表的所有字段，t1表的学生总分,t1.sum_sco
FROM(SELECT  -- 学生总分id,SUM(score) sum_sco FROM score GROUP BY id) t1  -- 将学生总分表取别名为t1RIGHT JOIN student t2  --合并student 表，并取别名t2ON t1.id=t2.id  --t1表的学生id等于t2表的学生id

3）求平均分

        得到了带学生总分的学生表，即可将班级分组，对每个学生的总分求平均值

SELECT clazz  -- 以单个班级分组，分别求每个班的平均分,AVG(tt1.sum_sco) -- 对班级内学生总分求平均值
FROM(SELECT   -- 将合并后带有学生总分表当做一个表，取别名tt1t2.*,t1.sum_scoFROM(SELECT id,SUM(score) sum_sco FROM score GROUP BY id) t1RIGHT JOIN student t2ON t1.id=t2.id) tt1 
GROUP BY clazz

 

6、总结

 1) 单表主要操作

        where 条件/ group by分组 / having条件 / select输出 / order by排序 / limit限制条数 

 2）多表主要操作

        join / union 合并

3）执行顺序

        执行顺序首先括号内最高，其次是join，然后是where> group by> having > select > order by > limit