MySQl基础入门⑬.5

创建多表连接查询

表准备

CREATE TABLE 员工信息 (
    员工号 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    性别 ENUM('男', '女') NOT NULL,
    出生日期 DATE NOT NULL,
    部门 VARCHAR(50) NOT NULL,
    手机号码 VARCHAR(20) NOT NULL,
    -- 根据数据库不同，可能需要为手机号码字段添加UNIQUE约束以确保唯一性
    UNIQUE KEY `unique_phone` (`手机号码`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO 员工信息 (姓名, 性别, 出生日期, 部门, 手机号码) VALUES
('张三', '男', '1985-06-12', '技术部', '13800138001'),
('李四', '女', '1990-11-7', '市场部', '13912345678'),
('王五', '男', '1988-09-22', '人力资源部', '13711112222'),
('赵六', '女', '1992-04-15', '财务部', '13600001111'),
('陈七', '男', '1995-12-25', '技术部', '13522223333'),
('周八', '女', '1987-07-18', '市场部', '13433334444'),
('吴九', '男', '1991-08-28', '销售部', '13344445555'),
('郑十', '女', '1986-03-10', '研发部', '13255556666'),
('钱十一', '男', '1993-05-20', '人力资源部', '13166667777'),
('孙十二', '女', '1989-12-2', '客服部', '13077778888'),
('朱十三', '男', '1994-07-13', '财务部', '12988889999'),
('马十四', '女', '1990-09-25', '技术部', '12899990000'),
('唐十五', '男', '1987-02-19', '市场部', '12700001112'),
('曹十六', '女', '1992-11-11', '销售部', '12611112223');

---

CREATE TABLE 销售表 (
    销售员ID INT(11) NOT NULL,
    -- 假设销售员ID为外键，引用员工信息表的员工号
    FOREIGN KEY (销售员ID) REFERENCES 员工信息(员工号),
    销售业绩金额 DECIMAL(10, 2) NOT NULL,
    部门 VARCHAR(50) NOT NULL,
    -- 可以根据需要添加其他字段，如销售日期、产品信息等
    销售日期 DATE,
    产品名称 VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO 销售表 (销售员ID, 销售业绩金额, 部门, 销售日期, 产品名称) VALUES
(1, 8500.50, '技术部', '2023-04-01', '全麦面包'),
(2, 1200.75, '市场部', '2023-04-02', '低脂牛奶'),
(3, 4500.00, '人力资源部', '2023-04-03', '红富士苹果'),
(4, 9800.25, '财务部', '2023-04-04', '新鲜鸡蛋'),
(5, 7200.10, '技术部', '2023-04-05', '即食燕麦片'),
(6, 5500.60, '市场部', '2023-04-06', '巧克力棒'),
(7, 15000.00, '销售部', '2023-04-07', '混合坚果'),
(8, 6800.30, '研发部', '2023-04-08', '绿茶饮料'),
(9, 3200.90, '人力资源部', '2023-04-09', '冷冻海鲜拼盘'),
(10, 8000.45, '客服部', '2023-04-10', '意大利面'),
(11, 7000.80, '财务部', '2023-04-11', '橄榄油'),
(12, 5000.20, '技术部', '2023-04-12', '黑咖啡豆'),
(13, 9500.15, '市场部', '2023-04-13', '酸奶'),
(14, 4000.55, '销售部', '2023-04-14', '薯片');

---

CREATE TABLE 进货表 (
    进货记录ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -- 假设收货人为公司员工，因此收货人ID为外键，引用员工信息表的员工号
    收货人ID INT(11) NOT NULL,
    FOREIGN KEY (收货人ID) REFERENCES 员工信息(员工号),
    产品名称 VARCHAR(20) NOT NULL,
    货号 VARCHAR(50) NOT NULL,
    总数量 INT(11) NOT NULL,
    总金额 DECIMAL(10, 2) NOT NULL,
    -- 可以根据需要添加其他字段，如进货日期、供应商信息等
    进货日期 DATE,
    供应商名称 VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO 进货表 (收货人ID, 货号, 总数量, 总金额, 进货日期, 供应商名称) VALUES
(1, 'GR001', 200, 8500.00, '2023-04-01', '绿叶食品有限公司'),
(2, 'DR002', 150, 4500.00, '2023-04-02', '大洋乳制品厂'),
(3, 'FR003', 300, 9800.00, '2023-04-03', '田园鲜果农场'),
(4, 'EG004', 500, 7200.00, '2023-04-04', '金鸡饲养场'),
(5, 'MT005', 250, 5500.00, '2023-04-05', '美味甜品工坊'),
(6, 'CH006', 180, 6800.30, '2023-04-06', '可可巧克力工厂'),
(7, 'NM007', 120, 15000.00, '2023-04-07', '坚果世界'),
(8, 'TE008', 220, 9500.15, '2023-04-08', '茶源饮品集团'),
(9, 'FS009', 350, 4000.55, '2023-04-09', '海之鲜冷冻食品'),
(10, 'SP010', 280, 8000.45, '2023-04-10', '意面之家'),
(11, 'OL011', 160, 7000.80, '2023-04-11', '橄榄庄园'),
(12, 'CF012', 200, 5000.20, '2023-04-12', '咖啡原产地'),
(13, 'YD013', 190, 9800.25, '2023-04-13', '酸奶乐园'),
(14, 'CP014', 230, 4500.90, '2023-04-14', '脆皮薯片厂');

1.交叉连接查询

在MySQL中，交叉连接（Cross Join）又称为笛卡尔积，它返回两个表中所有行的组合。如果一个表有m行，另一个表有n行，那么交叉连接会产生m*n行的结果集。这种类型的查询并不常用，除非你确实需要所有可能的组合。

格式一
select 字段名 from 表1 cross join 表2;

案例：select * from 员工信息 cross join 销售表;
省略写法：select 字段名 from 表1,表2;

2.内连接查询

内连接（INNER JOIN）是关系型数据库中常用的查询方式之一，它用来返回两个或多个表中满足连接条件的所有记录。内连接基于一个共同列（或多个共同列），只显示那些在所有参与连接的表中都匹配的行。

格式一
select 字段名 from 表名1 join 表名2 ON 表名1.关系字段=表名2.关系字段;

案例：select 姓名,销售业绩金额 from 员工信息 join 销售表 on 员工信息.员工号=销售表.销售员ID;

JOIN关键字指定了内连接操作。
FROM  员工信息指定了查询的第一个表（左表）。
INNER JOIN 销售表指定了查询的第二个表（右表）。
ON 员工信息.员工号=销售表.销售员ID;是连接条件，表示只有当员工信息表的员工号与销售表的销售员ID相等时，才会从这两个表中选择相应的行进行匹配。
执行这个查询后，结果将只包含那些在员工信息表和销售表中都有对应销售业绩金额的记录，显示员工姓名和他们的销售额。

---

外连接（Outer Join）在数据库查询中用于返回两个或更多表中匹配的行，以及左表或右表中没有匹配项的行。外连接有三种主要类型：左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。在MySQL中，不直接支持FULL OUTER JOIN，但可以通过UNION或其他方法模拟。

左外连接（LEFT JOIN）

左外连接会返回左表（LEFT JOIN关键字左边的表）的所有记录，即使右表中没有匹配。如果右表中没有匹配，则结果集中右表的部分将为NULL。

示例：假设我们想列出所有员工及其销售业绩，包括那些没有销售记录的员工。

SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
FROM 员工信息
LEFT JOIN 销售表 ON 员工信息.员工号 = 销售表.销售员ID;

右外连接（RIGHT JOIN）

右外连接与左外连接相反，它会返回右表（RIGHT JOIN关键字右边的表）的所有记录，即使左表中没有匹配。如果左表中没有匹配，则结果集中左表的部分将为NULL。

示例：假设我们想列出所有销售记录及其对应的员工信息，包括那些员工信息可能不存在的销售记录（这种情况较少见，但理论上可以这样查询）。

SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
FROM 员工信息
RIGHT JOIN 销售表 ON 员工信息.员工号 = 销售表.销售员ID;

全外连接（模拟）

MySQL不直接支持FULL OUTER JOIN，但可以通过UNION来模拟，以合并左外连接和右外连接的结果，从而获取两边都不缺失的记录。

示例：展示所有员工和销售记录的组合，无论是否有匹配。

(
    SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
    FROM 员工信息
    LEFT JOIN 销售表 ON 员工信息.员工号 = 销售表.销售员ID
) UNION (
    SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
    FROM 员工信息
    RIGHT JOIN 销售表 ON 员工信息.员工号 = 销售表.销售员ID
) ORDER BY 员工号;

注意：最后一个示例中的ORDER BY语句是为了排序结果，以便查看更清晰。在实际应用中，根据需求决定是否需要排序。

---

做前须知：

IFNULL()的用法

select 收货人ID,ifnull(总数量,0) from 进货表;

在MySQL中，正确的函数是IFNULL()，但实际上正确的函数名是IFNULL()应为IFNULL()。IFNULL(expression, value_if_null)函数用于判断第一个表达式expression是否为NULL，如果是NULL，则返回第二个参数value_if_null，否则返回第一个表达式的值。这是一个条件函数，用于处理NULL值的简单替换场景。

作用：
IFNULL()函数在SQL中的作用主要是处理NULL值。当我们在查询数据库时，有时会遇到某些字段的值为NULL，这可能会影响到后续的数据处理或展示。IFNULL()函数允许我们为这些NULL值提供一个默认的替换值。

---

练习题

1.列出所有员工的姓名以及他们销售的商品名称，对于没有销售记录的员工，商品名称显示为"未销售"。

2.编写一个SQL查询，使用右外连接（RIGHT JOIN）来整合“进货表”和“员工信息表”，确保所有员工都被列出，无论他们是否有关联的进货记录。查询应展示员工的姓名以及他们负责的商品货号（如果有的话）。对于没有进货记录的员工，货号显示为NULL。

3.列出每个员工的姓名以及他们销售的任意一款产品名称，如果员工没有销售记录，则显示"无销售记录"。

4.展示每种进货的商品名称及其对应的收货员工姓名，如果商品尚未被指派收货员工，则显示"待指派"。