— 准备数据
    create table dept(
    id int auto_increment comment ‘ID’ primary key,
    name varchar(50) not null comment ‘部门名称’
    )comment ‘部门表’;

    create table emp(
    id int auto_increment comment ‘ID’ primary key,
    name varchar(50) not null comment ‘姓名’,
    age int comment ‘年龄’,
    job varchar(20) comment ‘职位’,
    salary int comment ‘薪资’,
    entrydate date comment ‘入职时间’,
    managerid int comment ‘直属领导ID’,
    dept_id int comment ‘部门ID’
    )comment ‘员工表’;

    — 添加外键
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
    INSERT INTO dept (id, name) VALUES (1, ‘研发部’), (2, ‘市场部’),(3, ‘财务部’), (4, ‘销售部’), (5, ‘总经办’), (6, ‘人事部’);
    INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
    (1, ‘金庸’, 66, ‘总裁’,20000, ‘2000-01-01’, null,5),
    (2, ‘张无忌’, 20, ‘项目经理’,12500, ‘2005-12-05’, 1,1),
    (3, ‘杨逍’, 33, ‘开发’, 8400,’2000-11-03’, 2,1),
    (4, ‘韦一笑’, 48, ‘开发’,11000, ‘2002-02-05’, 2,1),
    (5, ‘常遇春’, 43, ‘开发’,10500, ‘2004-09-07’, 3,1),
    (6, ‘小昭’, 19, ‘程序员鼓励师’,6600, ‘2004-10-12’, 2,1),
    (7, ‘灭绝’, 60, ‘财务总监’,8500, ‘2002-09-12’, 1,3),
    (8, ‘周芷若’, 19, ‘会计’,48000, ‘2006-06-02’, 7,3),
    (9, ‘丁敏君’, 23, ‘出纳’,5250, ‘2009-05-13’, 7,3),
    (10, ‘赵敏’, 20, ‘市场部总监’,12500, ‘2004-10-12’, 1,2),
    (11, ‘鹿杖客’, 56, ‘职员’,3750, ‘2006-10-03’, 10,2),
    (12, ‘鹤笔翁’, 19, ‘职员’,3750, ‘2007-05-09’, 10,2),
    (13, ‘方东白’, 19, ‘职员’,5500, ‘2009-02-12’, 10,2),
    (14, ‘张三丰’, 88, ‘销售总监’,14000, ‘2004-10-12’, 1,4),
    (15, ‘俞莲舟’, 38, ‘销售’,4600, ‘2004-10-12’, 14,4),
    (16, ‘宋远桥’, 40, ‘销售’,4600, ‘2004-10-12’, 14,4),
    (17, ‘陈友谅’, 42, null,2000, ‘2011-10-12’, 1,null);
    ⚫ 概述: 指从多张表中查询数据
    ⚫ 笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛尔积)

    image.png
    迪卡尔积:
    select from emp,dept;
    消除迪卡尔积：
    select     from emp,dept where emp.dept_id=dept.id

    多表查询分类
    ➢ 连接查询
    内连接：相当于查询A、B交集部分数据
    外连接：
    左外连接：查询左表所有数据，以及两张表交集部分数据
    右外连接：查询右表所有数据，以及两张表交集部分数据
    自连接：当前表与自身的连接查询，自连接必须使用表别名
    ➢ 子查询
    image.png