内容概述
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)
