21-基础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)

-- 多表查询（笛卡尔积）
SELECT * FROM emp,dept;

-- 多表查询（消除无效的笛卡尔积）
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;