在对数据库中数据信息查询时，有些需求情况要获取的数据信息，是通过多个表的数据信息整合获取到的，就称为多表查询；

查询命令语法格式：

# 笛卡尔乘积连接多表：
select * from t1,t2;

# 内连接查询多表：
select * from t1,t2 where t1.列=t2.列;
select * from t1 [inner] join t2 on t1.列=t2.列;

# 外连接查询多表：左外连接
select * from t1 left join t2 on  t1.列=t2.列;
# 外连接查询多表：右外连接
select * from t1 right join t2 on  t1.列=t2.列;

说明：多表查询的最终目的是将多张表的信息整合为一张大表显示，并将显示的结果信息可以做相应单表的操作处理；

上传加载测试环境：

# 创建多表查询所需模拟数据库和数据表信息
CREATE DATABASE school CHARSET utf8;

# 进入数据库
USE school;

#创建表
CREATE TABLE student (
    sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    sname VARCHAR(20) NOT NULL COMMENT '姓名',
    sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
    ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course (
    cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
    cname VARCHAR(20) NOT NULL COMMENT '课程名字',
    tno INT NOT NULL COMMENT '教师编号'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE sc (
    sno INT NOT NULL COMMENT '学号',
    cno INT NOT NULL COMMENT '课程编号',
    score INT NOT NULL DEFAULT 0 COMMENT '成绩'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher (
    tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
    tname VARCHAR(20) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARSET=utf8;

# 在数据库与数据表中插入模拟数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'sda',20,'m'),
(9,'sdb',20,'f'),
(10,'dasd',25,'m');

INSERT INTO teacher(tno,tname)
VALUES
(101,'teach01'),
(102,'teach02'),
(103,'teach03'),
(104,'teach04');

INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103),
(1004,'go',105);

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

#查看结果
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

一、多表查询方式类型：笛卡尔乘积¶

实现局域teacher表与course表进行多表关联;

Day004-数据库服务查询命令应用-图4

多表关联实际操作：

# 分别查看单表数据信息：
mysql> select * from teacher;
+-----+---------+
| tno | tname   |
+-----+---------+
| 101 | teach01 |
| 102 | teach02 |
| 103 | teach03 |
| 104 | teach04 |
+-----+---------+
4 rows in set (0.00 sec)

mysql> select * from course;
+------+--------+-----+
| cno  | cname  | tno |
+------+--------+-----+
| 1001 | linux  | 101 |
| 1002 | python | 102 |
| 1003 | mysql  | 103 |
| 1004 | go     | 105 |
+------+--------+-----+
4 rows in set (0.00 sec)

# 多表关联查询，默认方式多表查询时，会出现组合乘积效果（4*4=16）,会出现许多无效信息内容
mysql> select * from teacher,course;
+-----+---------+------+--------+-----+
| tno | tname   | cno  | cname  | tno |
+-----+---------+------+--------+-----+
| 104 | teach04 | 1001 | linux  | 101 |
| 103 | teach03 | 1001 | linux  | 101 |
| 102 | teach02 | 1001 | linux  | 101 |
| 101 | teach01 | 1001 | linux  | 101 |
| 104 | teach04 | 1002 | python | 102 |
| 103 | teach03 | 1002 | python | 102 |
| 102 | teach02 | 1002 | python | 102 |
| 101 | teach01 | 1002 | python | 102 |
| 104 | teach04 | 1003 | mysql  | 103 |
| 103 | teach03 | 1003 | mysql  | 103 |
| 102 | teach02 | 1003 | mysql  | 103 |
| 101 | teach01 | 1003 | mysql  | 103 |
| 104 | teach04 | 1004 | go     | 105 |
| 103 | teach03 | 1004 | go     | 105 |
| 102 | teach02 | 1004 | go     | 105 |
| 101 | teach01 | 1004 | go     | 105 |
+-----+---------+------+--------+-----+
16 rows in set (0.00 sec)

多表查询的逻辑思路

for each row in a
   for each row in b
       合并成一行  print

二、多表查询方式类型：内连接（取交集）¶

join,其实就是"inner join",为了简写才写成join；内连接表示以两个表的交集为主,查出来是两个表有交集的部分,其余没有关联就不额外显示出来,这个用的情况也是挺多的,如下

Day004-数据库服务查询命令应用-图7

可以基于笛卡尔乘积方式的结果集，将有意义的信息进行展示，并且是基于两张表里的相同含义字段，进行比较后输出相等的结果信息；

# 内连接查询的简单描述：两个表中有关联条件的行显示出来；
# 比较传统的SQL 92的内连接标准方式
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+---------+------+--------+-----+
| tno | tname   | cno  | cname  | tno |
+-----+---------+------+--------+-----+
| 101 | teach01 | 1001 | linux  | 101 |
| 102 | teach02 | 1002 | python | 102 |
| 103 | teach03 | 1003 | mysql  | 103 |
+-----+---------+------+--------+-----+
3 rows in set (0.00 sec)

# 比较新颖的SQL 99的内连接使用方式
## 样式说明，这里[inner]表示inner可以省略
mysql> select * from teacher [inner] join course on teacher.tno=course.tno;

## 样式一：不省略inner
mysql> select * from teacher inner join course on teacher.tno=course.tno;
+-----+---------+------+--------+-----+
| tno | tname   | cno  | cname  | tno |
+-----+---------+------+--------+-----+
| 101 | teach01 | 1001 | linux  | 101 |
| 102 | teach02 | 1002 | python | 102 |
| 103 | teach03 | 1003 | mysql  | 103 |
+-----+---------+------+--------+-----+
3 rows in set (0.00 sec)

## 样式二：省略inner
mysql> select * from teacher  join course on teacher.tno=course.tno;
+-----+---------+------+--------+-----+
| tno | tname   | cno  | cname  | tno |
+-----+---------+------+--------+-----+
| 101 | teach01 | 1001 | linux  | 101 |
| 102 | teach02 | 1002 | python | 102 |
| 103 | teach03 | 1003 | mysql  | 103 |
+-----+---------+------+--------+-----+
3 rows in set (0.00 sec)

多表查询的逻辑思路（内连接）

for each row in a
   for each row in b
       if ax=b.y  print row

三、多表查询方式类型：外连接（应用更广泛）¶

利用外连接查询时，是可以进行性能优化处理的，因为内连接在底层查询时，是逐行进行比较后输出，整体数据查询检索的效率较低；

1、外连接可以细分为：左外连接-left join on

左外连接表示查询数据结构包含：左表所有数据行+右表满足关联条件的行；

# 左连接查询语法
a left join b on a.x = b.x
-- a表示左表，b表示右表，基于左表a建立关联

# 实际操作演示过程
mysql> select * from teacher left join course on teacher.tno=course.tno;
+-----+---------+------+--------+------+
| tno | tname   | cno  | cname  | tno  |
+-----+---------+------+--------+------+
| 101 | teach01 | 1001 | linux  |  101 |
| 102 | teach02 | 1002 | python |  102 |
| 103 | teach03 | 1003 | mysql  |  103 |
| 104 | teach04 | NULL | NULL   | NULL |
+-----+---------+------+--------+------+
4 rows in set (0.00 sec)
-- 包含了左表的所有数据行信息（teacher），包含了右表的关联数据行信息（course）

# 显示差集信息：
mysql> select * from teacher left join course on teacher.tno=course.tno where course.cno is null;
+-----+---------+------+-------+------+
| tno | tname   | cno  | cname | tno  |
+-----+---------+------+-------+------+
| 104 | teach04 | NULL | NULL  | NULL |
+-----+---------+------+-------+------+
1 row in set (0.00 sec)

外连接方式左连接与右连接区别举例：

# 会将左表作为驱动表，进行外层循环
for each row in a
  for each row in b
    if a.x=b.y print row
    else print a.x b.null

2、外连接可以细分为：右外连接-right join on

右外连接表示查询数据结构包含：右表所有数据行+左表满足关联条件的行；

# 右连接查询语法
a right join b on a.x = b.x
-- a表示左表，b表示右表，基于右表b建立关联

# 实际操作演示过程
mysql> select * from teacher right join course on teacher.tno=course.tno;
+------+---------+------+--------+-----+
| tno  | tname   | cno  | cname  | tno |
+------+---------+------+--------+-----+
|  101 | teach01 | 1001 | linux  | 101 |
|  102 | teach02 | 1002 | python | 102 |
|  103 | teach03 | 1003 | mysql  | 103 |
| NULL | NULL    | 1004 | go     | 105 |
+------+---------+------+--------+-----+
4 rows in set (0.00 sec)
-- 包含了右表的所有数据行信息（course），包含了左表的关联数据行信息（teacher）

# 显示差集信息：
mysql> select * from teacher right join course on teacher.tno=course.tno where teacher.tname is null;
+------+-------+------+-------+-----+
| tno  | tname | cno  | cname | tno |
+------+-------+------+-------+-----+
| NULL | NULL  | 1004 | go    | 105 |
+------+-------+------+-------+-----+
1 row in set (0.00 sec)

外连接方式左连接与右连接区别举例：

# 会将右表作为驱动表，进行外层循环
for each row in b
  for each row in a
    if b.y=a.x print row
    else print b.y a.null

内连接查询数据方法的方法思路：

1）构建多张表的关联图（关系-ER模型）
2）根据查询需求，定位查询的数据出自哪个表
3）将多个数据表进行内连接
4）根据需求在拼接后的大表中调取指定数据

以上面测试环境为例根据方法思路一步一步进行内连接查询

1、查看表信息

# 查看表信息，观察到总共有四张表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
| teacher          |
+------------------+
4 rows in set (0.00 sec)

# 查看四张表的表结构
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int         | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
| tno   | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| sno   | int  | NO   |     | NULL    |       |
| cno   | int  | NO   |     | NULL    |       |
| score | int  | NO   |     | 0       |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| sno   | int              | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)      | NO   |     | NULL    |                |
| sage  | tinyint unsigned | NO   |     | NULL    |                |
| ssex  | enum('f','m')    | NO   |     | m       |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno   | int         | NO   | PRI | NULL    |       |
| tname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2、构建多张表的关联图

Day004-数据库服务查询命令应用-图8-内连接简单ER模型图

3、根据查询需求，定位查询的数据出自哪个表,将多个数据表进行内连接,根据需求在拼接后的大表中调取指定数据

# 切换数据库
use school;

# 统计zhang3，学习了几门课
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno where student.sname='zhang3' group by student.sno ;
+--------+----------+
| sname  | count(*) |
+--------+----------+
| zhang3 |        2 |
+--------+----------+
1 row in set (0.00 sec)

多表连接查询的步骤思路：

进行需求分析，根据查询需求找寻所有需要的表信息；
找寻表的关联，根据多张表字段信息获取关联的字段；（也可以查询间接关系）
组合后的需求，根据多张表组合后定义查询条件信息；

多表联合中多个表的结构信息一览：

Day004-数据库服务查询命令应用-图5

多表查询信息练习题目分析：

01 统计zhang3，学习了几门课？

# 根据需求所需的表信息
student course sc
-- 需要先将student与sc合成一张表，才能在和course建立关联；
student sc
-- 根据题意也可以通过学生的成绩信息，统计学生所学的课程数量；

# 建立表之间关联
select *
from student
join sc
on student.sno=sc.sno;

# 依据组合后的大表进行处理
mysql>
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno;

# 方式一（使用 HAVING）：统计zhang3学习了几门课
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sno having student.sname='zhang3';
+--------+----------+
| sname  | count(*) |
+--------+----------+
| zhang3 |        2 |
+--------+----------+
1 row in set (0.00 sec)

# 方式二（使用 WHERE）：统计zhang3学习了几门课
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno where student.sname='zhang3' group by student.sno ;
+--------+----------+
| sname  | count(*) |
+--------+----------+
| zhang3 |        2 |
+--------+----------+
1 row in set (0.00 sec)

02 查询zhang3，学习的课程名称有哪些？

# 根据需求所需的表信息
student course sc
-- 需要先将student与sc合成一张表，才能在和course建立关联；

# 建立表之间关联
select * from student join sc on student.sno=sc.sno join course on sc.cno=course.cno;
-- 将三张表建立关联后，形成一张大表

# 依据组合后的大表进行处理
mysql> select * from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3';
+-----+--------+------+------+-----+------+-------+------+--------+-----+
| sno | sname  | sage | ssex | sno | cno  | score | cno  | cname  | tno |
+-----+--------+------+------+-----+------+-------+------+--------+-----+
|   1 | zhang3 |   18 | m    |   1 | 1001 |    80 | 1001 | linux  | 101 |
|   1 | zhang3 |   18 | m    |   1 | 1002 |    59 | 1002 | python | 102 |
+-----+--------+------+------+-----+------+-------+------+--------+-----+
2 rows in set (0.00 sec)

# 方式一：获取zhangsan3学习的课程名称
mysql> select student.sname,course.cname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3';
+--------+--------+
| sname  | cname  |
+--------+--------+
| zhang3 | linux  |
| zhang3 | python |
+--------+--------+
2 rows in set (0.00 sec)

# 方式二：利用 GROUP_CONCAT 函数合并课程名称获取zhangsan3学习的课程名称(以上sql语句书写是不严谨的，最好分组的条件为group by student.sno)
mysql> select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3' group by student.sname;
+--------+----------------------------+
| sname  | group_concat(course.cname) |
+--------+----------------------------+
| zhang3 | linux,python               |
+--------+----------------------------+
1 row in set (0.00 sec)

03 查询teach01老师教的学生名？

# 根据需求所需的表信息
teacher course sc student
-- 根据题意需要4张表 teacher 与 course 在于 sc 最后关联student

# 建立表之间关联，将四张表建立关联后，形成一张大表
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno;

# 获取teach01老师教的学生名，显示的信息更详细
mysql> select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname='teach01';
+-----+---------+------+-------+-----+-----+------+-------+-----+--------+------+------+
| tno | tname   | cno  | cname | tno | sno | cno  | score | sno | sname  | sage | ssex |
+-----+---------+------+-------+-----+-----+------+-------+-----+--------+------+------+
| 101 | teach01 | 1001 | linux | 101 |   1 | 1001 |    80 |   1 | zhang3 |   18 | m    |
| 101 | teach01 | 1001 | linux | 101 |   3 | 1001 |    99 |   3 | li4    |   18 | m    |
| 101 | teach01 | 1001 | linux | 101 |   4 | 1001 |    79 |   4 | wang5  |   19 | f    |
| 101 | teach01 | 1001 | linux | 101 |   6 | 1001 |    89 |   6 | zhao4  |   18 | m    |
| 101 | teach01 | 1001 | linux | 101 |   7 | 1001 |    67 |   7 | ma6    |   19 | f    |
| 101 | teach01 | 1001 | linux | 101 |   8 | 1001 |    70 |   8 | sda    |   20 | m    |
+-----+---------+------+-------+-----+-----+------+-------+-----+--------+------+------+
6 rows in set (0.00 sec)

# 通过 group_concat 函数获取teach01老师教的学生名，只返回一行数据
mysql> select teacher.tname,group_concat(student.sname)  from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname='teach01' group by teacher.tno;
+---------+--------------------------------+
| tname   | group_concat(student.sname)    |
+---------+--------------------------------+
| teach01 | zhang3,li4,wang5,zhao4,ma6,sda |
+---------+--------------------------------+
1 row in set (0.00 sec)

04 查询teach01老师教课程的平均分数？

# 根据需求所需的表信息
teacher course sc
-- 根据题意需要4张表 teacher 与 course 在于 sc 关联

# 建立表之间关联
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno;

# 依据组合后的大表进行处理
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno where teacher.tname='teach01';
+-----+---------+------+-------+-----+-----+------+-------+
| tno | tname   | cno  | cname | tno | sno | cno  | score |
+-----+---------+------+-------+-----+-----+------+-------+
| 101 | teach01 | 1001 | linux | 101 |   1 | 1001 |    80 |
| 101 | teach01 | 1001 | linux | 101 |   3 | 1001 |    99 |
| 101 | teach01 | 1001 | linux | 101 |   4 | 1001 |    79 |
| 101 | teach01 | 1001 | linux | 101 |   6 | 1001 |    89 |
| 101 | teach01 | 1001 | linux | 101 |   7 | 1001 |    67 |
| 101 | teach01 | 1001 | linux | 101 |   8 | 1001 |    70 |
+-----+---------+------+-------+-----+-----+------+-------+
6 rows in set (0.00 sec)

# 这种方式查询其实是不严谨的，因为有可能teach01名称可能有重名的，而且teach01老师教多门课程，不能算总课程平均分
mysql> select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno where teacher.tname='teach01';
+---------+---------------+
| tname   | avg(sc.score) |
+---------+---------------+
| teach01 |       80.6667 |
+---------+---------------+
1 row in set (0.00 sec)

# 在结合课程编号进行分组，可以求出xiaoA老师每门课程的平均分（只有tno和cno均相同的才会分为一组显示），可以根据teacher.tno进行分组，这样可以将多个teach01名字的老师分组显示
mysql> select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno where teacher.tname='teach01' group by teacher.tno,course.cno;
+---------+---------------+
| tname   | avg(sc.score) |
+---------+---------------+
| teach01 |       80.6667 |
+---------+---------------+
1 row in set (0.00 sec)

05 每位老师所教课程的平均分，并按平均分排序？

# 根据需求所需的表信息
teacher course sc
-- 根据题意需要4张表 teacher 与 course 在于 sc 关联

# 建立表之间关联
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno;

# 依据组合后的大表进行处理，获取每位老师所教课程的平均分，并按平均分排序
mysql> select teacher.tname,course.cname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno,course.cno order by avg(sc.score);
+---------+--------+---------------+
| tname   | cname  | avg(sc.score) |
+---------+--------+---------------+
| teach02 | python |       70.0000 |
| teach03 | mysql  |       76.7500 |
| teach01 | linux  |       80.6667 |
+---------+--------+---------------+
3 rows in set (0.00 sec)

06 查询teach01老师教的不及格的学生姓名？

# 根据需求所需的表信息
teacher course sc student
-- 根据题意需要4张表 teacher 与 course 在于 sc 最后关联student

# 建立表之间关联，将四张表建立关联后，形成一张大表
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno;

# 依据组合后的大表进行处理
mysql>
select teacher.tname, group_concat(student.sname)
from teacher
join course on teacher.tno = course.tno
join sc on course.cno = sc.cno
join student on sc.sno = student.sno
where teacher.tname = 'teach01' and sc.score < 60
group by teacher.tno;
Empty set (0.00 sec)

07 查询所有老师所教学生不及格的信息？

# 根据需求所需的表信息
teacher course sc student
-- 根据题意需要4张表 teacher 与 course 在于 sc 最后关联student

# 建立表之间关联，将四张表建立关联后，形成一张大表
select * from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno

# 查询所有老师所教学生不及格的信息
mysql> select teacher.tname,group_concat(student.sname)  from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where sc.score<60 group by teacher.tno;
+---------+-----------------------------+
| tname   | group_concat(student.sname) |
+---------+-----------------------------+
| teach02 | zhang3                      |
| teach03 | li4,zh4                     |
+---------+-----------------------------+
2 rows in set (0.00 sec)

多表查询过程别名应用：

在进行数据信息查询时，有些表和有些字段会被经常调用到，而且生成环境中表明和字段名会比较的复杂，在调用时不是很方便；

而且有些时候，查询的SQL语句信息会出现在代码中，在编写代码时也会不太规范，同时也不方便阅读，因此出现了数据库别名概念；

在进行数据库别名应用时，会经常用到两种别名：

1、表别名：（应用更广泛）

# 举例说明别名作用，在没有使用别名时
select teacher.tname,group_concat(student.sname)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where sc.score<60
group by teacher.tno;

# 进行表别名化
select a.tname,group_concat(d.sname)
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+---------+-----------------------+
| tname   | group_concat(d.sname) |
+---------+-----------------------+
| teach02 | zhang3                |
| teach03 | li4,zh4               |
+---------+-----------------------+
2 rows in set (0.00 sec)

2、列别名

# 举例说明别名作用，在没有使用别名时
select a.tname,group_concat(d.sname)
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+-------+-----------------------+
| tname | group_concat(d.sname) |
+-------+-----------------------+
| xiaoQ | zhang3                |
| xiaoA | li4,zh4               |
+-------+-----------------------+
2 rows in set (0.00 sec)

# 进行列别名化
select a.tname as '老师名',group_concat(d.sname)  as '不及格学生名'
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+-----------+--------------------+
| 老师名    | 不及格学生名       |
+-----------+--------------------+
| teach02   | zhang3             |
| teach03   | li4,zh4            |
+-----------+--------------------+
2 rows in set (0.00 sec)

# 进行列别名化，列别名信息是可以在group by子句之后进行调用的
select teacher.tname as a,course.cname as b,avg(sc.score) as c
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno,course.cno
order by c;
+---------+--------+---------+
| a       | b      | c       |
+---------+--------+---------+
| teach02 | python | 70.0000 |
| teach03 | mysql  | 76.7500 |
| teach01 | linux  | 80.6667 |
+---------+--------+---------+
3 rows in set (0.00 sec)

多表联合查询数据-纵向拼接

MySQL UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中，并消去表中任何重复行；

MySQL UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；

同时，每条SELECT语句中的列的顺序必须相同

语法为：

SELECT column,... FROM table1
UNION
SELECT column,... FROM table2 ...

在多个 SELECT 语句中，对应的列应该具有相同的字段属性，且第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称。

UNION 结果集中的列名总是等于 UNION 中第一个SELECT 语句中的列名；
UNION 内部的SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条SELECT语句中的列的顺序必须相同；

纵向拼接要求：

1）多个表的数据拼接，列的数量必须一致
2）多个表的数据拼接，对应类的数据类型必须一致

UNION 与 UNION ALL 的区别

当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

默认地，UNION 操作符选取不同的值，如果允许重复的值，请使用UNION ALL;

当ALL随UNION一起使用时（即 UNION ALL）,不消除重复行；

语法为：

SELECT column,... FROM table1
UNION ALL
SELECT column,... FROM table2 ...

UNION 与 UNION ALL 的用法及注意事项

UNION 表示联合的意思，即把两次或多次查询结果合并起来;

两次查询的列数必须一致，列的类型可以不一样，但推荐查询的每一列，相对应的类型一样；

可以来自多张表的数据，多次SQL语句取出的列名可以不一致，此时以第一个SQL语句的列名为准；

如果不同的语句中取出的行，有完全相同（表示的是每个列的值都相同），那么union会将相同的行合并，最终只保留一行实现去重

如果不想去掉重复的行，可以使用union all；

如果子句中有order by、limit，需要括号包起来，推荐放在所有子句之后，即对最终合并的结果来排序或筛选

语法为：

# union语法(去重)
(select * from a order by id) union (select * from b order id);

# union all语法(不去重)
(select * from a order by id) union all (select * from b order id);

UNION 语句应用用法示例：

UNION 常用于数据类似的两张或多张表查询，如不同的数据分类表，或者是数据历史表等。下面是用于例子的两张原始数据表：

用户表信息01表：

# Employees_China:
E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming

用户表信息02表：

# Employees_USA:
E_ID E_Name
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

使用UNION 命令应用：

列出所有在中国和美国的不同的雇员名：

# 操作命令
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

输出命令结果：

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

这个命令无法列出在中国和美国的所有雇员。

在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

使用UNION ALL命令应用：

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

输出结果信息：

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill

UNION 语句应用项目实战举例

web项目中经常会碰到整站搜索的问题，即客户希望在网站的搜索框中输入一个词语；

然后在整个网站中只要包含这个词的页面都要出现在搜索结果中。

由于一个web项目不可能用一张表就全部搞定的，所以这里一般都是要用union联合搜索来解决整个问题的。

下面列举一下本次使用的union联合搜索的sql语句：

# 查询通过三个子查询组合了两个数据源中的数据，并对查询结果进行了去重和合并处理
select * from
(SELECT `id`,`subject` FROM `article` WHERE `active`='1' AND `subject` LIKE '%调整图片%' ORDER BY `add_time` DESC)
as t1
union all
select * from
(SELECT `id`,`class_name` AS `subject` FROM `web_class` WHERE `active`='1' AND `class_name` LIKE '%调整图片%' ORDER BY `class_id` DESC)
as t2
union
select * from
(SELECT `id`,`subject` FROM `article` WHERE `active`='1' AND (`subject` LIKE '%调整%' OR `subject` LIKE '%图片%') ORDER BY `add_time` DESC)
as t3;

以上SQL语句解释说明：

# 第一个子查询，从 web_class 表中选取处于激活状态且 class_name 包含“调整图片”的记录
SELECT `id`, `subject`
FROM `article`
WHERE `active` = '1' AND `subject` LIKE '%调整图片%'
ORDER BY `add_time` DESC

# 第二个子查询，从 web_class 表中选取处于激活状态且 class_name 包含“调整图片”的记录
SELECT `id`, `class_name` AS `subject`
FROM `web_class`
WHERE `active` = '1' AND `class_name` LIKE '%调整图片%'
ORDER BY `class_id` DESC

# 第三个子查询，再次从 article 表中选取激活状态的记录，不过条件改为标题中包含“调整”或“图片”（条件更宽松）。
SELECT `id`, `subject`
FROM `article`
WHERE `active` = '1' AND (`subject` LIKE '%调整%' OR `subject` LIKE '%图片%')
ORDER BY `add_time` DESC

# 使用UNION ALL 将第一个和第二个子查询的结果合并(不去重)，再使用 UNION 将第三个子查询的结果与前面的结果合并，UNION 会自动去除重复的行。

以上SQL语句的联合查询主要用到了union all和union，至于这两者的区别就是：

union all会列举所有符合条件的查询结果，
union 会将所有符合条件的查询结果做一下去除重复结果的筛选。

对于以上SQL语句的解释就是由于article表和web_class表分属两个不同的表，所以这里不用去除重复结果。

然而以上联合查询的第三个分支的sql查询语句是由分词然后组合出来的查询语句，

这条sql语句查询的结果是肯定包含第一个分支sql语句的查询结果的，这里就显得没必要了，所以没有使用all而去掉重复的查询结果。

MySQL多表查询实战：笛卡尔积、内连接、外连接与驱动表

一、多表查询方式类型：笛卡尔乘积¶

二、多表查询方式类型：内连接（取交集）¶

三、多表查询方式类型：外连接（应用更广泛）¶

评论区