22-基础SQL-多表查询-连接查询（内连接、外连接、自连接）

多表查询分类

案例：创建部门表 和 员工表（熟悉多表查询）

-- 部门表
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 "员工表";

-- 部门表数据
INSERT INTO dept(id,name)
VALUES
(1,"研发部"),
(2,"市场部"),
(3,"财务部"),
(4,"销售部"),
(5,"总经办")

-- 员工表数据
INSERT INTO emp(id,name,age,job,salary,entrydate,managerid,dept_id)
VALUES
(1, "员工1", 66, "总裁", 20000, "2000-01-01", null, 5),
(2, "员工2", 35, "经理", 12500, "2007-01-01", 1, 1),
(3, "员工3", 20, "开发", 8300, "2020-01-01", 2, 1),
(4, "员工4", 23, "开发", 10400, "2021-01-01", 2, 1),
(5, "员工5", 26, "开发", 11000, "2021-01-01", 3, 1)

连接查询-内连接