mysql多表联合查询
1. 什么是多表联合查询
前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。 在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。
2. 多表联合查询优点
1, 缓存效率更高, 许多应用程序可以方便地缓存单表查询对应的结果对象. 如果关联中的某个表发生了变化, 那么就无法使用查询缓存了, 而拆分后, 如果某个表很少改变, 那么基于该表的查询就可以重复利用查询缓存结果了.
2, 多表信息联合的列表页面分页显示, 只需要显示一部分的数据, 如果是多表联合查询那要把所有数据联结查出来再执行 limit, 如果是多次单表查询, 先对单表进行筛选, 先执行 limit 再与其余表去关联, 数据量会大大减小
3, 如果数据库没有进行读写分离 (主从备份), 在并发量高的时候, 由于写表会加排他锁, 把多表联合查询改成单表查询后锁的粒度变小, 减少了锁的竞争
4, 在数据量变大之后, 普遍会采用分库分表的方法来缓解数据库的压力, 采用单表查询比多表联合查询更容易进行分库, 不需要对 sql 语句进行大量的修改, 更易扩展. 分库分表的中间件一般对跨库 join 都支持不好
5, 查询本身效率也可能会有所提升. 查询 id 集的时候, 使用 IN() 代替关联查询, 可以让 MySQL 按照 ID 顺序进行查询, 这可能比随机的关联要更高效.
6, 业务高速增长时, 数据库作为最底层, 最容易遇到瓶颈, 单机数据库计算资源很贵, 数据库同时要服务写和读, 都需要消耗 CPU, 为了能让数据库的吞吐变得更高,
而业务又不在乎那几百微妙到毫秒级的延时差距, 业务会把更多计算放到 service 层做, 毕竟计算资源很好水平扩展, 数据库很难啊, 这是一种重业务, 轻 DB 的架构
7, 可以减少冗余记录的查询, 在应用层做关联查询, 意味着对于某条记录应用只需要查询一次, 而在数据库中做关联查询, 则可能需要重复地访问一部分数据.
3. 交叉连接(CROSS JOIN)
3.1 笛卡尔积
交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。 笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。 例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2}
B = {3,4,5}
集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
并且，从以上结果我们可以看出：
-
两个集合相乘，不满足交换率，即 A×B≠B×A。
-
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。
3.2 交叉连接
#SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
#SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件
#创建school库 创建tb_students_info表
mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(50),age tinyint,sex varchar(10),height int,course_id int);
Query OK, 0 rows affected (0.01 sec)
mysql> desc tb_students_info;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
| course_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql>
#插入表tb_students_info数据
mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'Male',160,1),('Green',23,'Male',158,2),('Henry',23,'Female',185,1),('Jane',22,'Male',162,3),('Jim',24,'Female',175,2),('John',21,'Male',172,4),('Lily',22,'Male',165,4),('Susan',23,'Male',170,5),('Thomas',22,'Female',178,5),('Tom',23,'Female',165,5);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+--------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+--------+--------+-----------+
| 1 | Dany | 25 | Male | 160 | 1 |
| 2 | Green | 23 | Male | 158 | 2 |
| 3 | Henry | 23 | Female | 185 | 1 |
| 4 | Jane | 22 | Male | 162 | 3 |
| 5 | Jim | 24 | Female | 175 | 2 |
| 6 | John | 21 | Male | 172 | 4 |
| 7 | Lily | 22 | Male | 165 | 4 |
| 8 | Susan | 23 | Male | 170 | 5 |
| 9 | Thomas | 22 | Female | 178 | 5 |
| 10 | Tom | 23 | Female | 165 | 5 |
+----+--------+------+--------+--------+-----------+
10 rows in set (0.00 sec)
mysql>
#创建tb_course表
mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50));
Query OK, 0 rows affected (0.00 sec)
#插入tb_course表数据
mysql> insert tb_course(course_name) values('Java'),('Mysql'),('Python'),('Go'),('C++');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | Mysql |
| 3 | Python |
| 4 | Go |
| 5 | C