MySQL记录相关操作之多表查询

目录

• MySQL记录相关操作之多表查询
  • 一 介绍
  • 二 多表连接查询
    • 1 外链接语法
    • 2 交叉连接：不适用任何匹配条件，生成笛卡尔积
    • 3 内连接：只连接匹配的行
    • 4 外链接之左连接：优先显示左表全部记录
    • 5 外链接之右连接：优先显示右表全部记录
    • 6 全外连接：显示左右两个表全部记录
  • 三 符合条件连接查询
  • 四 子查询
    • 1 带in关键字的子查询
    • 2 带any关键字的子查询
    • 3 带all关键字的子查询
    • 4 带比较运算符的子查询
    • 5 带exists关键字的子查询
    • 6 in与exists **
    • 7 not in与 not exists **
    • 8 应用示例

MySQL记录相关操作之多表查询

一 介绍

# 为了让表的查询更有效率，所以表一般会分拆成多个进行存储，这样就需要使用select语句针对多表进行查询。

# 实验表格准备————————
# 建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

# 插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;


# 查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

二 多表连接查询

1 外链接语法

select 字段列表
	from 表1 inner|left|right join 表2
	on 表1.字段 = 表2.字段;

2 交叉连接：不适用任何匹配条件，生成笛卡尔积

mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  1 | egon       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | egon       | male   |   18 |    200 |  202 | 销售         |
|  1 | egon       | male   |   18 |    200 |  203 | 运营         |
|  2 | alex       | female |   48 |    201 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  2 | alex       | female |   48 |    201 |  202 | 销售         |
|  2 | alex       | female |   48 |    201 |  203 | 运营         |
|  3 | wupeiqi    | male   |   38 |    201 |  200 | 技术         |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  202 | 销售         |
|  3 | wupeiqi    | male   |   38 |    201 |  203 | 运营         |
|  4 | yuanhao    | female |   28 |    202 |  200 | 技术         |
|  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  4 | yuanhao    | female |   28 |    202 |  203 | 运营         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+

3 内连接：只连接匹配的行

# 找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来。

mysql> select employee.*,department.name from employee inner join department on employee.dep_id = department.id;
+----+-----------+--------+------+--------+--------------+
| id | name      | sex    | age  | dep_id | name         |
+----+-----------+--------+------+--------+--------------+
|  1 | egon      | male   |   18 |    200 | 技术         |
|  2 | alex      | female |   48 |    201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 | 技术         |
+----+-----------+--------+------+--------+--------------+

# 上述sql等同于
mysql> select employee.*,department.name from employee,department where employee.dep_id = department.id;

4 外链接之左连接：优先显示左表全部记录

# 以左表为准，即找出所有员工信息，当然包括没有部门的员工
# 本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id = department.id;

+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技术         |
|  5 | liwenzhou  | 技术         |
|  2 | alex       | 人力资源     |
|  3 | wupeiqi    | 人力资源     |
|  4 | yuanhao    | 销售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

5 外链接之右连接：优先显示右表全部记录

# 以右表为准，即找出所有部门信息，包括没有员工的部门
# 本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id = department.id;

+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技术         |
|    2 | alex      | 人力资源     |
|    3 | wupeiqi   | 人力资源     |
|    4 | yuanhao   | 销售         |
|    5 | liwenzhou | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

6 全外连接：显示左右两个表全部记录

# 全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意：MySQL不支持全外连接 full join
# 强调：MySQL可以间接实现全外连接：
mysql> select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
# 查看结果
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+
# 注意：union 与 union all的区别：union会去掉相同的记录

三 符合条件连接查询

# 示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select employee.name, department.name from employee inner join department on employee.dep_id = department.id
where age > 25;
+---------+--------------+
| name    | name         |
+---------+--------------+
| alex    | 人力资源     |
| wupeiqi | 人力资源     |
| yuanhao | 销售         |
+---------+--------------+

# 示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
mysql> select employee.name, department.name from employee inner join department on employee.dep_id = department.id
where age > 25
order by age asc;

四 子查询

# 子查询介绍及用法
1) 子查询是将一个查询语句嵌套在另一个查询语句中。
2) 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3) 子查询中可以包含: in、 not in、 any、 all、 exists 和 not exists 等关键字。
4) 还可以包含比较运算符：= 、 != 、> 、 < 等。

1 带in关键字的子查询

# 查询平均年龄在25岁以上的部门
mysql> select id,name from department
	where id in
	(select dep_id from employee group by dep_id having avg(age) > 25);
	
# 查看技术部员工姓名
mysql> select name from employee 
	where dep_id in
	(select id from department where name='技术');

# 查看不足1人的部门名(子查询得到的是有人的部门id)
mysql> select name from department where id not in (select distinct dep_id from employee);

注意：not in 无法处理null的值，即子查询中如果存在null的值，not in 将无法处理，如下：

mysql> insert emp(name, sex, age) values("xxx","male",66);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
|  7 | xxx        | male   |   66 |   NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from dep;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)

# 子查询中存在null
mysql> select * from dep where id not in (select distinct dep_id from emp);
Empty set (0.00 sec)

# 解决方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id   | name   |
+------+--------+
|  203 | 运营   |
+------+--------+
1 row in set (0.00 sec)

mysql>

2 带any关键字的子查询

# 在SQL中。 any 和 some 是同义词，some 的用法、功能和 any 一摸一样。 

# any 和 in 运算符不同之处1；
any 必须和其他的比较运算符共同使用，而 any 必须将比较运算符放在 any 关键字之前，所以比较的值需要匹配子查询中的任意一个值，这就是 any 在英文中所表示的意义。
mysql> select * from employee where salary = any(
	select max(salary) from employee group by depart_id);

mysql> select * from employee where salary in (
	select max(salary) from employee group by depart_id);

mysql> select * from employee where !(salary = any(
	select max(salary) from employee group by depart_id));
	
mysql> select * from employee where salary not in (
	select max(salary) from employee group by depart_id);
		
#### 结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符  ####

# any 和 in 运算符不同之处2：
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005) 

3 带all关键字的子查询

# all 同 any 类似，只不过all表示的是所有， any 表示的是任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
mysql> select * from employee where salary > all (
	select avg(salary) from employee group by depart_id);

查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
mysql> select * from employee where salary < all (
	select avg(salary) from employee group by depart_id);

查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
mysql> select * from employee where salary < any (
	select avg(salary) from employee group by depart_id);

查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
mysql> select * from employee where salary > any (
	select avg(salary) from employee group by depart_id);

4 带比较运算符的子查询

# 比较运算符：=、!=、>、>=、<、<=、<>
# 查询大于所有人平均年龄的员工名与年龄
mysql> select name, age from employee where age > (select avg(age) from employee);
+---------+------+
| name    | age  |
+---------+------+
| alex    |   48 |
| wupeiqi |   38 |
+---------+------+

# 查询大于部门内平均年龄的员工名、年龄
mysql> select name, age from employee
	inner join 
	(select dep_id, avg(age) avg_age from employee group by dep_id) t2
	on employee.dep_id = t2.dep_id
	where employee.age > t2.avg_age;
+------+------+
| name | age  |
+------+------+
| alex |   48 |
+------+------+

5 带exists关键字的子查询

	exists关键字表示存在，在使用exists关键字时，内层循环语句不返回查询的记录。

	而是返回一个真假值。True或False。

	当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

# department表中存在dept_id=200，Ture
mysql> select * from employee 
	where exists
	(select id from department where id = 200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

# department表中存在dept_id=204，False
mysql> select * from employee
	where exists 
	(select id from department where id = 204);
	Empty set (0.00 sec)

6 in与exists **

！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
==============================exists==============================
# exists：
exists 后面一般都是子查询，后面的子查询被称作相关子查询(即与主语句相关) ，当子查询返回行数时，exists条件返回True。否则返回false，exists 是不返回列表的值的，exists 只在乎括号里的数据能不能查出来，是否存在这样的记录。

# 示例：
查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)；

# exists的执行原理为：
1)、依次执行外部查询：即select * from class 
2)、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3)、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录

==============================  in  ==============================
# in：
in 后面跟的都是子查询，in()后面的子查询 是返回结果集里的

# 示例：
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')

# in 的执行原理为：
1、 in()的执行次序和 exists()不一样， in()的子查询会先产生结果集，
2、 然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出，反之则不输出。

7 not in与 not exists **

!!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!
==============================  not in  ==============================
not in()子查询的执行顺序是：
为了证明 not in 成立，即找不到，需要一条一条地查询，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。

==============================not exists==============================
not exists :
如果主查询表中记录少，子查询表中地记录多，并有索引。就使用 not exists.

# 例如:查询那些班级中没有学生的班级
select * from class where not exists
	(select * from student where student.cid = class.cid);
	
not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

8 应用示例

# 准备数据
create database db13;

use db13

create table student(
    id int primary key auto_increment,
    name varchar(16)
);

create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);

create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);


insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");

insert into course(name,comment) values
("数据库", "数据仓库"),
("数学", "根本学不会"),
("英语", "鸟语花香");


insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);

代码示例：

# 1、查询选修了所有课程的学生id、name:(即该学生根本就不存在一门他没有选的课程。) 
select * from student s where not exists
    (select * from course c where not exists
        (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));


select s.name from student as s
inner join student2course as sc
on s.id = sc.sid
group by s.name 
having count(sc.id) = (select count(id) from course);

# 2、查询没有选择所有课程的学生，即没有全选的学生。(存在这样的一个学生，他至少有一门课没有选) 
select * from student s where exists
    (select * from course c where not exists
        (select * from student2course sc where sc.sid = s.id and sc.cid=c.id));

# 3、查询一门课也没有选的学生。(不存这样的一个学生，他至少选修一门课程) 
select * from student s where not exists
    (select * from course c where exists
        (select * from student2course sc where sc.sid = s.id and sc.cid=c.id));

# 4、查询至少选修了一门课程的学生。
select * from student s where exists
    (select * from course c where exists
        (select * from student2course sc where sc.sid = s.id and sc.cid=c.id));