数据多表查询

多表连接查询

1. 能够对多张数据表进行查询操作

2. 导语
   在数据库操作中，数据往往不是存在一张表中的，同一个项目中，根据设计范式，数据可能分散在不同的多张表中，这时查询数据时，就需要多表查询。

3. 普通多表查询（无意义）
   作用：直接将表放在from后面，进行读取。 语法：select 表名.字段 … from 表名1，表名2…

select * from t_student,t_class;

这种查询方式没有任何意义。
在查询时，数据库会将表1中的数据逐条和表2中的所有数据连接，组成一条新记录。
查询的结果为 M * N 条，实际就是笛卡尔积结果。
3. 多表查询连接条件
在多表个表进行查询时，表与表之间应该是有有关系的，一般会以外键的形式来建立表间的关系。 查询时按照条件建立记录的匹配规则。 比如学生表中保存了学生的信息和所在班级的ID，班级表中保存了班级的信息。 在查询学生的班级信息时，可以通过学生表中的班级ID和班级表中的ID匹配进行查询

select t_student.c_name,t_class.c_name from t_student,t_class where t_student.c_class_id = t_class.c_id;

4. 表别名
   在多表操作时，由于表的名字比较长，在写SQL语句时非常不方便。可以在查询 时，给表起个别名，代替表名来操作

语法：select 别名.字段名… from 表1 as 表1别名，表2 表2别名… [条件]

select ts.c_name as ‘姓名’ , tc.c_name ‘班级名’ from t_student as ts,t_class tc where ts.c_class_id = tc.c_id;

5. 内连接查询
   作用：查询的结果为两个表匹配到的数据 语法：select * from 表1 inner join 表2 on 表1.列 运算符 表2.列

图示：

数据库默认的连接方式就是内连接查询， inner join 可以不显示的写出来。 这种连接方式会以笛卡尔积的形式进行连接。 所以在连接时，必须要给定连接条件。 连接条件使用 on 进行指定。尽量不要使用 where,where在其它连接方式时，指定的连接条件无效。

select ts.c_name, tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id = tc.c_id;

6. 左连接查询
作用：查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。

语法：select * from 表1 left join 表2 on 表1.列 运算符 表2.列

图示：

select ts.c_name, tc.c_name from t_student as ts left join t_class tc on ts.c_class_id = tc.c_id;

7. 右连接查询
   作用：查询的结果为根据右表中的数据进行连接，如果左表中没有满足条件的记录，则连接空值。

语法：select * from 表1 right join 表2 on 表1.列 运算符 表2.列

图示：

select ts.c_name, tc.c_name from t_student as ts right join t_class tc on ts.c_class_id = tc.c_id;

在实际工作中，右连接使用的非常少，因为左连接完全可以替代右连接，在连接过程中，只需要调整表的顺序即可。
8. 子查询
作用：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

语法：select * from 表1 where 条件 运算符 （select 查询）

外部那个select语句则称为主查询
主查询和子查询的关系

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句
标量子查询 作用：子查询返回的结果是一个数据(一行一列) 语法：主查询 where 条件 比较运算符 (列子查询) 示例：查询班级中年龄大于平均年龄的学生信息

查询班级学生平均年龄
查询大于平均年龄的学生

select * from t_student where c_age > (select avg(c_age) from t_student);

列级子查询 作用：子查询返回的结果是一列(一列多行) 语法：主查询 where 条件 in (列子查询) 示例：查询所有学生所在班级的班级名称

找出学生表中所有的班级 id
找出班级表中对应的名字

select * from t_class where c_id in (select c_class_id from t_student);

行级子查询 作用：子查询返回的结果是一行(一行多列) 语法：主查询 where (字段1,2,…) = (行子查询) 示例：查找班级年龄最大,所在班号最小的的学生

找出最大年龄和最小班号
找出年龄和班号满足条件的学生

select * from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);

9. 自连接查询
   作用：在查询数据时，只有一张表，查询时使用自己连接自己。 语法：select * from 表 as 表别名1 inner join 表 表别名2 on 表别名1.列 运算符 表别名2.列 where 条件

为什么需要自连接 现要设计表结构来存储全国所有的省份和全国所有的市

设计省信息的表结构provinces

id 省的编号
ptitle 省名称
设计市信息的表结构citys

id 市编号
ctitle 市名称
proid 市所属的省的编号
citys表的proid表示城市所属的省，对应着provinces表的id值

如果需要查询一个省 比如广东省对应的所有的时的信息 ,我们可以使用两个表连接查询。

问题: 能不能将两个表合成一张表呢？

观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的。存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。

那么将可以将两张表合为一张表来存储数据。
定义表areas，结构如下

id
atitle
pid

关于这个表的说明:

因为省没有所属的省份，所以可以填写为null
城市所属的省份pid，填写省所对应的编号id
这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
思考: 如果还是要查询广东省对应的所有的市的信息，咱们应该怎么做呢?

areas表和自身进行连接这种形式的连接 就成为自连接。

准备数据
创建areas表的语句如下： 注意，表所在的数据库字符集必须是utf8的，如果不是会导入数据出错
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
从sql文件中导入数据
source /home/python/Desktop/areas.sql;
示例1：查询一共有多少个省

select count(*) from areas where pid is null;

示例2： 查询省的名称为“山西省”的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle=‘山西省’;

示例3：查询市的名称为“广州市”的所有区县

select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle=‘广州市’;