今日内容概述

1.连表查询
  -1.1 内连接
  -1.2 左连接
  -1.3 右连接
  -1.4 合并数据
2.子查询

今日内容详细

1.连表查询

1.1 内连接（INNER JOIN ）

两个或多个表之间通过某种关系，或按照某种规则合并起来查询出来的数据即为连表查询，连表查询是企业中常用的一种查询数据的方式，在关系型数据库中连表查询是很常见的。但是连表查询仅仅限于同一个数据库内多张数据表互相连接，不同数据库中的数据表无法使用连表查询。

内连接
把两个数据表中的所有数据一次性按照某种条件一次性查询出来。
案例：
我们先创建几个表：

创建一张学生表
create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null comment '学生名称',
age tinyint(2) not null default 18 comment '年龄'
);
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 18      |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

再创建一张科目表
mysql> create table if not exists major(
    -> id int primary key auto_increment,
    -> name varchar(20) not null comment '科目'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc major;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

创建一张学生选择科目表
mysql> create table if not exists student_major(
    -> id int primary key auto_increment,
    -> stu_id int not null comment '学生表ID',
    -> m_id int not null comment '科目表ID' 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student_major;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| stu_id | int(11) | NO   |     | NULL    |                |
| m_id   | int(11) | NO   |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

创建一张教师表
mysql> create table if not exists teacher(
    -> id int primary key auto_increment,
    -> name varchar(20) not null comment '教师名称'
    -> );
Query OK, 0 rows affected (0.01 sec)

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

创建教师教授科目表
mysql> create table if not exists teach_major(
    -> id int primary key auto_increment,
    -> tea_id int not null comment '教师表ID',
    -> m_id int not null comment '科目表ID'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc teach_major;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| tea_id | int(11) | NO   |     | NULL    |                |
| m_id   | int(11) | NO   |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

表创建完毕后我们为表中插入数据
insert into student (name,age) values('egon',18),('jason',20),('alex',26);

insert into major (name) values ('linux'),('golang'),('python');

insert into teacher(name) values ('老铁'),('老刘'),('老板');

我们查看一下：
mysql> select * from student;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | egon  |  18 |
|  2 | jason |  20 |
|  3 | alex  |  26 |
+----+-------+-----+
3 rows in set (0.00 sec)

mysql> select * from major;
+----+--------+
| id | name   |
+----+--------+
|  1 | linux  |
|  2 | golang |
|  3 | python |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
+----+--------+
| id | name   |
+----+--------+
|  1 | 老铁   |
|  2 | 老刘   |
|  3 | 老板   |
+----+--------+
3 rows in set (0.00 sec)

现在我们为剩下的两个表插入数据
案例为：
egon 修linux和golang;jason修linux和python;alex全修
然后老铁教python,老刘教golang,老板教linux;

那么数据应该这样插入;
mysql> INSERT INTO student_major (stu_id, m_id) VALUES (1, 1),(1,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO student_major (stu_id, m_id) VALUES (2, 1),(2,3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO student_major (stu_id, m_id) VALUES (3, 1),(3,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student_major;
+----+--------+------+
| id | stu_id | m_id |
+----+--------+------+
|  1 |      1 |    1 |
|  2 |      1 |    2 |
|  3 |      2 |    1 |
|  4 |      2 |    3 |
|  5 |      3 |    1 |
|  6 |      3 |    2 |
|  7 |      3 |    3 |
+----+--------+------+
7 rows in set (0.00 sec)


mysql> INSERT INTO teach_major (tea_id, m_id) VALUES (1,3),(2,1),(3,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from teach_major;
+----+--------+------+
| id | tea_id | m_id |
+----+--------+------+
|  1 |      1 |    3 |
|  2 |      2 |    1 |
|  3 |      3 |    2 |
+----+--------+------+
3 rows in set (0.00 sec)

# 数据表准备好之后，我们来康康需求：
1.查询出egon修了哪几门学科？

普通的查询：
mysql> select * from student where id = 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  18 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select * from student_major where stu_id = 1;
+----+--------+------+
| id | stu_id | m_id |
+----+--------+------+
|  1 |      1 |    1 |
|  2 |      1 |    2 |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> select * from major where id = 1 or id = 2;
+----+--------+
| id | name   |
+----+--------+
|  1 | linux  |
|  2 | golang |
+----+--------+
2 rows in set (0.00 sec)
查到了，和我们之前规定的一样

# 但是，这样很慢，而且很繁琐

下面我们使用连表的方式查询：
mysql> select student.id,student.name,student.age,major.name from student inner join student_major on student.id = student_major.stu_id inner join major on student_major.m_id = major.id where student.id =1;
+----+------+-----+--------+
| id | name | age | name   |
+----+------+-----+--------+
|  1 | egon |  18 | linux  |
|  1 | egon |  18 | golang |
+----+------+-----+--------+
2 rows in set (0.00 sec)

感觉有点懵逼......我们再看一个例子：
查询alex选修科目的代课老师
mysql> SELECT  student.id,student.name,student.age, major.name,teacher.name  FROM  student  INNER JOIN  student_major ON student.id = student_major.stu_id   INNER JOIN major ON student_major.m_id = major.id  INNER JOIN teach_major ON teach_major.m_id = major.id INNER JOIN teacher ON teacher.id =  teach_major.tea_id WHERE student.id = 3;
+----+------+-----+--------+--------+
| id | name | age | name   | name   |
+----+------+-----+--------+--------+
|  3 | alex |  26 | python | 老铁   |
|  3 | alex |  26 | linux  | 老刘   |
|  3 | alex |  26 | golang | 老板   |
+----+------+-----+--------+--------+
3 rows in set (0.00 sec)

# 尽量理解吧......

1.2 左连接（LEFT JOIN ）

左连接顾名思义就是以左边的表为主表，其他的表为副表；也就是说会把左边表中所有的符合条件的数据全部查询出来，至于后面的表有没有内容不管，没有内容则用空来代替。

案例：
我们在student表中插入一名学生jerry
mysql> insert into student(name,age) values ('jerry',23);
Query OK, 1 row affected (0.00 sec)

我们分别输入内连接查询和左连接查询看看有什么区别
mysql> select * from student inner join student_major on student.id = student_major.stu_id;
+----+-------+-----+----+--------+------+
| id | name  | age | id | stu_id | m_id |
+----+-------+-----+----+--------+------+
|  1 | egon  |  18 |  1 |      1 |    1 |
|  1 | egon  |  18 |  2 |      1 |    2 |
|  2 | jason |  20 |  3 |      2 |    1 |
|  2 | jason |  20 |  4 |      2 |    3 |
|  3 | alex  |  26 |  5 |      3 |    1 |
|  3 | alex  |  26 |  6 |      3 |    2 |
|  3 | alex  |  26 |  7 |      3 |    3 |
+----+-------+-----+----+--------+------+
7 rows in set (0.00 sec)

mysql> select * from student left join student_major on student.id = student_major.stu_id;
+----+-------+-----+------+--------+------+
| id | name  | age | id   | stu_id | m_id |
+----+-------+-----+------+--------+------+
|  1 | egon  |  18 |    1 |      1 |    1 |
|  1 | egon  |  18 |    2 |      1 |    2 |
|  2 | jason |  20 |    3 |      2 |    1 |
|  2 | jason |  20 |    4 |      2 |    3 |
|  3 | alex  |  26 |    5 |      3 |    1 |
|  3 | alex  |  26 |    6 |      3 |    2 |
|  3 | alex  |  26 |    7 |      3 |    3 |
|  4 | jerry |  23 | NULL |   NULL | NULL |
+----+-------+-----+------+--------+------+
8 rows in set (0.00 sec)
左表中有 jerry这名学生，所以左连接将他查出来了，jerry并没有其他的数据，所以均为null

1.3 右连接（RIGHT JOIN ）

右链接顾名思义就是用右边表作为主表，其他表作为副表。也就是说，右链接是会把右边的表中的所有的数据全部都查询出来，至于左边的表如果没有数据既用空代替。

案例：
我们还用刚才的例子：
mysql> select * from student inner join student_major on student.id = student_major.stu_id;
+----+-------+-----+----+--------+------+
| id | name  | age | id | stu_id | m_id |
+----+-------+-----+----+--------+------+
|  1 | egon  |  18 |  1 |      1 |    1 |
|  1 | egon  |  18 |  2 |      1 |    2 |
|  2 | jason |  20 |  3 |      2 |    1 |
|  2 | jason |  20 |  4 |      2 |    3 |
|  3 | alex  |  26 |  5 |      3 |    1 |
|  3 | alex  |  26 |  6 |      3 |    2 |
|  3 | alex  |  26 |  7 |      3 |    3 |
+----+-------+-----+----+--------+------+
7 rows in set (0.00 sec)
mysql> select * from student_major right join student on student.id = student_major.stu_id;
+------+--------+------+----+-------+-----+
| id   | stu_id | m_id | id | name  | age |
+------+--------+------+----+-------+-----+
|    1 |      1 |    1 |  1 | egon  |  18 |
|    2 |      1 |    2 |  1 | egon  |  18 |
|    3 |      2 |    1 |  2 | jason |  20 |
|    4 |      2 |    3 |  2 | jason |  20 |
|    5 |      3 |    1 |  3 | alex  |  26 |
|    6 |      3 |    2 |  3 | alex  |  26 |
|    7 |      3 |    3 |  3 | alex  |  26 |
| NULL |   NULL | NULL |  4 | jerry |  23 |
+------+--------+------+----+-------+-----+
8 rows in set (0.00 sec)

# 偷个小懒，但道理和左连接是一样的。

1.4 合并数据

顾名思义就是将多个SQL语句查询出来的数据合并一次性查询出来。需要注意的是，两边的字段必须一致。
mysql> select name,age from student union select * from teacher;
+-------+--------+
| name  | age    |
+-------+--------+
| egon  | 18     |
| jason | 20     |
| alex  | 26     |
| jerry | 23     |
| 1     | 老铁   |
| 2     | 老刘   |
| 3     | 老板   |
+-------+--------+
7 rows in set (0.00 sec)

# 没什么好注意的，两表的字段保持一致就行了

2.子查询

子查询顾名思义就是在SQL中依赖于另一个SQL语句的结果来共同查询一个结果。每一个子查询语句只能够返回一条数据。在工作用极其不建议使用子查询，因为子查询的性能非常低。
# 了解一下即可
案例：
select * from student where id = (select distinct stu_id from student_major limit 1);
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  18 |
+----+------+-----+
1 row in set (0.00 sec)