一、连接查询[连表查询、多表查询]

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

内连接查询(inner join)

查询的结果为两个表匹配到的数据

使用内连接，必须保证两个表都会对应id的数据才会被查询出来。

格式：select 字段1,字段2... from 主表A inner join 从表B on 主表A.主键=从表B.外键

例如：查询学生的信息[ 成绩、名字、班级 ] 我们给学生表添加一个学生信息，然后使用该学生的主键id来连表查询成绩、名字和班级。

insert into student (name,sex,age,class,description) values ('刘德华',1,17,406,'');

select achievement,name,class

from student as a

inner join achievement as b

on a.id=b.sid

where id=101;

# 上面语句因位该学生只在学生表student中有数据，而成绩表中没有数据，所以使用内连接，连表查询的结果是

Empty set (0.00 sec)

同样，如果从表有数据，而主表没有数据，则使用内连接查询一样无法查询到结果。

#例如，添加一个成绩记录，是不存在学生

insert into achievement (sid,cid,achievement) values (102,10,85);

select achievement,name,class

from student as a

inner join achievement as b

on a.id=b.sid

where id=102;

右连接查询(right join)

只要从表有数据，不管主表是否有数据，都会查询到结果。[以从表的结果为主例如：a.id=b.sid，只要b表的sid存在就可以； a.id不存在不要紧左表中不存在的数据使用null填充]

查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

格式：select 字段1,字段2... from 主表 right join 从表 on 主表.主键=从表.外键

select achievement,name,class from student as a

right join achievement as b

on a.id=b.sid

where b.sid=102;

左连接查询(left join)

只要主表有数据，不管从表是否有数据都会被查询出来。

查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

格式：select * from 表1 left join 表2 on 表1.列 = 表2.列

例如，使用左连接查询学生表与成绩表，查询学生姓名及分数

select achievement,name,class

from student as a

left join achievement as b

on a.id=b.sid;

等同于

select achievement,name,class

from achievement as b

right join student as a

on a.id=b.sid;

总结：三种连表查询，最常用的是 left join，然后inner join保证数据的一致性。右连接基本上都是使用左连接代替。

select 表.字段1,表.字段2,表.字段3.....

from 主表

left join 从表1 on 主表.主键=从表1.外键

left join 从表2 on 主表.主键=从表2.外键

     # 这里和从表2连接的on条件看实际情况，也会出现从表1.主键=从表2.外键的情况

left join 从表3 on 主表.主键=从表3.外键

     # 这里可以是(从表1或从表2).主键=从表2.外键的情况

left join ...

多表关联格式

多表查询的效率，性能比单表要差。

多表查询以后，还会带来字段多了会引起字段覆盖的情况、

主表student      从表1 achievement   从表2 course

 name                 xxx                              name

上面三张表如果连表，则出现主表的name覆盖从表2的name这种情况。

上面两个问题：

1. 把多表查询语句可以替换成单表查询语句【需要优化的情况】

2. 把重复的字段名，分别使用as来设置成别的名称。

多表查询的缺点

练习：

select sum(b.achievement) sum   # 有时候as可以不写

from student as a

left join achievement as b on a.id=b.sid

where a.id=20;

查询id为20的学生的考试总分.

1. 先查305的学生信息

2. 再查305的学生成绩

3. 再查305的学生成绩对应的课程

4. 最后查305的学生成绩对应的课程的老师

select a.name,b.achievement,c.course,d.name

from student as a

left join achievement as b on a.id=b.sid

left join course as c on b.cid=c.id

left join lecturer as d on d.id=c.lecturer_id

where a.class=305;

查询305班级所有学生的课程名称、课程成绩、以及对应课程的授课老师。

上面代码的效果：

+--------+-------------+----------------+--------+

| name   | achievement | course         | name   |

+--------+-------------+----------------+--------+

| 谭季同 |       100.0 | Photoshop      | 唐老师 |

| 谭季同 |        79.0 | 负载均衡       | 杜老师 |

| 谭季同 |        78.5 | Flask项目      | 白老师 |

| 白瀚文 |        73.0 | go             | 陈老师 |

| 白瀚文 |        65.0 | webpy          | 林老师 |

| 白瀚文 |        86.0 | 数据分析       | 郑老师 |

| 白瀚文 |        60.0 | API接口        | 宋老师 |

| 晁然   |         0.0 | Flask          | 陈老师 |

| 晁然   |        78.0 | Python网络编程 | 江老师 |

| 晁然   |        78.0 | HTML5          | 丘老师 |

| 白素欣 |        81.0 | Django项目     | 易老师 |

| 白素欣 |        90.0 | Python         | 黄老师 |

| 白素欣 |        39.0 | Nginx          | 曹老师 |

| 庄晓敏 |        82.5 | Nginx          | 曹老师 |

| 庄晓敏 |        68.0 | Python         | 黄老师 |

| 庄晓敏 |       100.0 | API接口        | 宋老师 |

+--------+-------------+----------------+--------+

上面代码的效果：

二、单表的连表查询[自关联查询]

核心就是把一张表看做2张表来操作

# 建表：

create table area(

    id smallint not null auto_increment comment '主键ID',

    name char(30) not null comment '地区名称',

    pid smallint not null default 0 comment '父级地区ID',

    primary key (id)

) engine=innodb charset=utf8;

insert into area (name,pid) values ('广东',0),('深圳',1),('龙岗',2),('福田',2),('宝安',2);

格式：select 字段1,字段2...from 主表(当前表) as a left join 从表(当前表) as b on a.主键=b.外键

# 主表看成保存深圳的表，

# 从表看成保存深圳子地区的表

select b.id,b.name

from area as a

left join area as b on a.id=b.pid

where a.name='深圳';

查找深圳地区的子地区，SQL代码：

三、子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

格式：select 字段 from 表名 where 条件(另一条查询语句)

主查询

主要查询的对象,第一条 select 语句

主查询和子查询的关系

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句

##### 例如：查询406班上大于平均年龄的学生

使用 子查询：

1. 查询406班学生平均年龄

2. 查询大于平均年龄的学生

查询406班级学生的平均身高

select name,age from student where age > (select avg(age) as avg from student where class=406) and class=406;

例如：查询406班上大于平均年龄的学生

having

group by 字段 having 条件；

过滤筛选，主要作用类似于where关键字，用于在SQL语句中进行条件判断，过滤结果的。

但是与where不同的地方在于having只能跟在group by 之后使用。

练习：查询301班级里的学生的平均成绩大于班上平均成绩的学生成绩信息(name，平均分，班级)。

# 先求301班的平均成绩

select avg(achievement) as achi from student as a

left join achievement as b on a.id=b.sid

where class=301;

# 判断301中的每个人平均成绩大于上面的到的平均成绩

select name,avg(achievement) from student as a

left join achievement as b on a.id=b.sid

where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a

left join achievement as b on a.id=b.sid

where class=301);

mysql> select avg(achievement) as achi from student as a

left join achievement as b on a.id=b.sid

where class=301;

+----------+

| achi     |

+----------+

| 68.96875 |

+----------+

1 row in set

mysql> select name,

achievement from student as a

left join achievement as b on a.id=b.sid

where class=301;

+--------+-------------+

| name   | achievement |

+--------+-------------+

| 程星云 | 63.5        |

| 程星云 | 72          |

| 程星云 | 40.5        |

| 娄镇明 | 86.5        |

| 娄镇明 | 85.5        |

| 柳宗仁 | 99          |

| 柳宗仁 | 60          |

| 曾嘉慧 | 90          |

| 王紫伊 | 73          |

| 王紫伊 | 0           |

| 王紫伊 | 55          |

| 黄威   | 98          |

| 黄威   | 79          |

| 黄威   | 62.5        |

| 庄信杰 | 60          |

| 庄信杰 | 79          |

+--------+-------------+

16 rows in set

mysql> select sum(achievement)

 from student as a

left join achievement as b on a.id=b.sid

where class=301;

+------------------+

| sum(achievement) |

+------------------+

| 1103.5           |

+------------------+

1 row in set

mysql> select count

(achievement) from student as a

left join achievement as b on a.id=b.sid

where class=301;

+--------------------+

| count(achievement) |

+--------------------+

|                 16 |

+--------------------+

1 row in set

mysql> select name,avg(achievement) from student as a

left join achievement as b on a.id=b.sid

where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a

left join achievement as b on a.id=b.sid

where class=301);

+--------+------------------+

| name   | avg(achievement) |

+--------+------------------+

| 娄镇明 | 86               |

| 庄信杰 | 69.5             |

| 曾嘉慧 | 90               |

| 柳宗仁 | 79.5             |

| 黄威   | 79.83333         |

+--------+------------------+

5 rows in set

mysql>

结果

select查询语句的完整格式

select distinct 字段1,字段2....
from 表名 as 表别名
left join 从表1 on 表名.主键=从表1.外键
left join ....
where ....
group by ... having ...
order by ...
limit start,count

执行顺序为：
- from 表名[包括连表]
- where ....
- group by ...
- select distinct *
- having ...
- order by ...
- limit start,count
实际使用中，只是语句中某些部分的组合，而不是全部

巴特西

MySql数据库多表操作