MySQL复合查询
MySQL复合查询
1. 表的设计
以下为本文演示多表查询所需的数据库，这个数据库模拟的是一个电子商务业务场景，涵盖了从产品管理、员工管理等多个关键环节，用于电商网站、零售业务管理系统等。
表结构如下：
- 商品管理
- 分类（categories）：对商品进行分类，比如 “电子产品” “家用电器” “服装” 等。
- 产品（products）：每个商品属于某个分类，并且有价格等属性。
- 员工管理（组织架构）
- 员工（employees）：用于管理员工信息，并支持自连接（Self Join），表示员工与直接上级（manager）的关系。
-- 分类表：记录产品分类信息
create table categories (id int auto_increment primary key,name varchar(100) not null
);-- 产品表：记录产品信息，并关联分类
create table products (id int auto_increment primary key,name varchar(100) not null,price decimal(10,2) not null,category_id int,foreign key (category_id) references categories(id)
);-- 员工表：用于自连接示例，记录员工及其直接上级
create table employees (id int auto_increment primary key,name varchar(100) not null,manager_id int DEFAULT null,foreign key (manager_id) references employees(id)
);
以下为演示多表查询所需的示例数据：
-- 插入分类数据
insert into categories (name) values
('电子产品'),
('家用电器'),
('服装'),
('食品'),
('图书');-- 插入产品数据
-- 分类1：电子产品
insert into products (name, price, category_id) values
('智能手机', 3999.00, 1),
('平板电脑', 2999.00, 1),
('笔记本电脑', 5999.00, 1),
('智能手表', 999.00, 1),
('蓝牙耳机', 499.00, 1),
('数码相机', 2499.00, 1);-- 分类2：家用电器
insert into products (name, price, category_id) values
('电视机', 3499.00, 2),
('冰箱', 3999.00, 2),
('洗衣机', 2999.00, 2),
('微波炉', 799.00, 2),
('空调', 2499.00, 2),
('电饭煲', 499.00, 2),
('吸尘器', 699.00, 2);-- 分类3：服装
insert into products (name, price, category_id) values
('T恤衫', 199.00, 3),
('牛仔裤', 299.00, 3),
('连衣裙', 399.00, 3),
('皮夹克', 899.00, 3),
('运动鞋', 599.00, 3);-- 分类4：食品
insert into products (name, price, category_id) values
('有机大米', 99.00, 4),
('低脂牛奶', 89.00, 4),
('纯天然蜂蜜', 129.00, 4),
('进口橄榄油', 199.00, 4),
('黑巧克力', 59.00, 4),
('健康燕麦片', 79.00, 4);-- 分类5：图书
insert into products (name, price, category_id) values
('编程入门', 89.00, 5),
('经典小说', 59.00, 5),
('历史漫谈', 79.00, 5),
('科学探索', 99.00, 5),
('自我提升', 69.00, 5),
('商业管理', 129.00, 5),
('艺术鉴赏', 149.00, 5);-- 插入员工数据（自连接示例）
insert into employees (name, manager_id) values
('王总',NULL), -- 顶级管理
('李经理',1),
('赵经理',1),
('孙经理',1),
('周主管',2),
('吴主管',2),
('郑主管',3),
('冯主管',3),
('陈主管',4),
('褚主管',4),
('卫员工',5),
('蒋员工',6),
('沈员工',7),
('韩员工',8),
('杨员工',9);
2. 子查询
MySQL 允许在一条查询语句中嵌套另一条查询语句，嵌套的查询语句叫做子查询。在执行查询语句时，先执行子查询语句。
语法：
select ... (select ...);
括号内为子查询。
2.1 单行子查询
单行子查询指返回一行的子查询。
2.1.2 显示与 笔记本电脑 为同一类别的商品
select name from products where category_id = (select category_id from products where name = '笔记本电脑');
2.2 多行子查询
多行子查询指返回多行数据的子查询。多行子查询会使用 in 、all 、any 等关键字对子查询的数据进行进一步筛选。
2.2.1 in
in 用于返回某些记录是否在括号内的集合之中，返回在这个集合中的数据。
查询与孙经理相同上级的员工，但不包括孙经理自己：
select name from employees where manager_id in (select distinct manager_id from employees where name = '孙经理') and name <> '孙经理';
2.2.2 all
all 一般可以理解为，比最大的还大。显示家用电器中价格比所有服装价格大的商品：
select name,price from products where category_id = 1 and price > all(select price from products where category_id = 3);
2.2.3 any
any 一般可以理解为，比最小的还小。显示所有商品中，比食品类别任意商品价格低的商品：
select name,price from products where price < any(select price from products where category_id = 4);
2.3 多列子查询
单列子查询是指子查询只返回单列单行数据，而多行子查询是返回单列多行数据。多列子查询则是返回多列数据。
2.3.1 从产品表中查询每个分类中价格最高的商品
这个查询使用 group by 就能实现，因为这个数据库对于多行子查询没有什么合适的例子，注意多行子查询的语法主要是 where 中要返回子查询中的多列字段
select p.id as product_id,p.name as product_name,p.price,c.name as category_name
from products p
join categories c on p.category_id = c.id
where (p.price, p.category_id) in (select max(price) as max_price, category_idfrom productsgroup by category_id
);
3. 多表查询
当一条查询语句在多张表进行查询时，MySQL 会将第一张表中的第一条记录与第二张表中的所有记录组合，再将第一张表中的第二条记录与第二张表中的所有记录组合，一一穷举组合，最终得到一张新的表，这样的组合结果叫做笛卡儿积。由于是简单的穷举组合，不进行处理会有很多的无效数据，因此多表查询时需要对数据进行筛选。
3.1 去除多表查询无效记录
先看看分类（categories）和产品（products）两个表里分别有什么数据：
select * from categories;
直接拼接分类（categories）和产品（products）两个表可以看到，所有数据都被一一穷举拼接：
select * from categories,products;
对于分类（categories）和产品（products），它们相互关联的字段为分类 id，通过这个 id，可以将无效数据去除：
select products.id,products.name,products.price,categories.name from categories,products where products.category_id = categories.id;
注意，这里不直接
select *是为了剔除分类（categories）中的 id。
3.2 其他示例
3.2.1 显示类别为服装的所有商品及其价格
select products.name,products.price,categories.name from categories,products where products.category_id = categories.id and categories.name = '服装';
4. 自连接
当多表查询的对象是同一张表时，这种情况叫做自连接。对同一张表进行查询，由于表的名字相同，需要对至少一张表进行重命名才能进行查询。当一张表的数据之间有关系时，需要查询这种关系，就需要用到自连接，如查看某人的领导是谁的情况。
4.1 显示韩员工的领导
首先看看员工表中的数据：
select * from employees;
4.1.1 多表查询法
select leader.name from employees as leader, employees as worker where worker.manager_id = leader.id and worker.name = '韩员工';
4.1.2 子查询法
select name from employees where id = (select manager_id from employees where name = '韩员工');
4.2 查询所有员工对应的领导
select e.name as employee_name, m.name as manager_name from employees e left join employees m on e.manager_id = m.id;
5. 合并查询
在实际查询中，可以使用 union 和 union all 来合并多个 select 的执行结果。
5.1 union
union 在合并两个 select 的查询结果后，会自动去重。
显示所有商品类型为电子产品或单价大于 1000 元的商品：
select name,price from products where category_id = 1 union
select name,price from products where price > 1000;
5.2 union all
union all 在合并两个 select 的查询结果后，不会对重复数据去重。
显示所有商品类型为电子产品或单价大于 1000 元的商品：
select name,price from products where category_id = 1 union all
select name,price from products where price > 1000;
