MySQL基础练习：连接查询

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;

-- 创建部门表
CREATE TABLE department (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,   -- 部门ID
    name VARCHAR(100) NOT NULL              -- 部门名称
);

-- 插入部门数据
INSERT INTO department (name)
VALUES
    ('Tech Department'),
    ('HR Department'),
    ('Marketing Department'),
    ('Finance Department'),
    ('Sales Department'),
		('没员工的部门');

-- 创建员工表
CREATE TABLE employee (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 员工ID，自动递增
    name VARCHAR(100) NOT NULL,            -- 员工姓名
    address VARCHAR(200) NOT NULL,         -- 员工地址
    work_place VARCHAR(100) NOT NULL,      -- 工作地点
    gender ENUM('MALE', 'FEMALE', 'OTHER') NOT NULL,  -- 性别
    hire_date TIMESTAMP NOT NULL,          -- 入职日期
    salary DECIMAL(10, 2),                 -- 薪水
    phone VARCHAR(20),                     -- 联系电话
    age INT,                               -- 员工年龄
    department_id BIGINT,                  -- 部门ID，与部门表关联
    leader_id BIGINT,                      -- 直属领导ID，自关联到员工表
    CONSTRAINT FK_department FOREIGN KEY (department_id) REFERENCES department (id), -- 部门外键
    CONSTRAINT FK_leader FOREIGN KEY (leader_id) REFERENCES employee (id)            -- 直属领导外键
);

-- 插入员工数据
INSERT INTO employee (name, address, work_place, gender, hire_date, salary, phone, age, department_id, leader_id)
VALUES
    ('Alice Zhang', '123 Main St, Beijing', 'Tech Department', 'FEMALE', '2024-01-10 09:00:00', 8000.50, '13800138000', 30, NULL, NULL),
    ('Bob Liu', '456 Elm St, Shanghai', 'HR Department', 'MALE', '2023-05-15 09:00:00', 6000.00, '13900139000', 35, NULL, NULL),
    ('Charlie Wang', '789 Pine St, Shenzhen', 'Marketing Department', 'MALE', '2022-09-20 09:00:00', 7500.75, '13700137000', 28, 3, 1),
    ('Diana Li', '321 Oak St, Guangzhou', 'Tech Department', 'FEMALE', '2024-03-01 09:00:00', 9500.00, '13600136000', 32, 1, 1),
    ('Ethan Zhao', '654 Maple St, Hangzhou', 'Finance Department', 'MALE', '2023-08-12 09:00:00', 7000.25, '13500135000', 40, 4, 2),
    ('Fiona Xu', '101 Birch St, Suzhou', 'HR Department', 'FEMALE', '2024-02-20 09:00:00', 6500.00, '13400134000', 29, 2, 2),
    ('George Chen', '202 Cedar St, Chengdu', 'Tech Department', 'MALE', '2021-06-10 09:00:00', 7200.30, '13300133000', 38, 1, 1),
    ('Helen Wu', '303 Pine St, Wuhan', 'Sales Department', 'FEMALE', '2022-11-15 09:00:00', 8200.00, '13200132000', 26, 5, 2),
    ('Ivan Zhang', '404 Walnut St, Nanjing', 'Marketing Department', 'MALE', '2023-12-05 09:00:00', 7800.45, '13100131000', 33, 3, 3),
    ('Julia Liu', '505 Maple St, Xi\'an', 'Finance Department', 'FEMALE', '2021-09-30 09:00:00', 8800.60, '13000130000', 30, 4, 5),
    ('Kevin Yang', '606 Oak St, Tianjin', 'Tech Department', 'MALE', '2022-07-18 09:00:00', 9500.10, '12900129000', 27, 1, 1),
    ('Lily Zhou', '707 Birch St, Zhengzhou', 'HR Department', 'FEMALE', '2024-01-25 09:00:00', 5600.00, '12800128000', 25, 2, 2),
    ('Mike Li', '808 Cedar St, Dalian', 'Sales Department', 'MALE', '2021-11-10 09:00:00', 6700.50, '12700127000', 37, 5, 2),
    ('Nina Wang', '909 Pine St, Qingdao', 'Finance Department', 'FEMALE', '2023-04-22 09:00:00', 7300.75, '12600126000', 34, 4, 5),
    ('Oscar Zhang', '1001 Walnut St, Chengdu', 'Tech Department', 'MALE', '2023-03-05 09:00:00', 8000.00, '12500125000', 31, 1, 1),
    ('Pamela Liu', '1102 Maple St, Shijiazhuang', 'Marketing Department', 'FEMALE', '2022-12-01 09:00:00', 7500.00, '12400124000', 29, 3, 3),
    ('Quinn Xu', '1203 Oak St, Hangzhou', 'Sales Department', 'MALE', '2024-04-10 09:00:00', 6900.00, '12300123000', 36, 5, 2),
    ('Rachel Chen', '1304 Birch St, Suzhou', 'Tech Department', 'FEMALE', '2021-10-05 09:00:00', 8800.20, '12200122000', 33, 1, 1),
    ('Steven Wu', '1405 Cedar St, Shanghai', 'HR Department', 'MALE', '2023-02-10 09:00:00', 6400.00, '12100121000', 33, 2, 2),
    ('Tracy Wang', '1506 Pine St, Guangzhou', 'Marketing Department', 'FEMALE', '2022-08-30 09:00:00', 7700.85, '12000120000', 27, 3, 3);
		

-- 查询每个员工的名字、工作地点、部门名称和直属领导名字。
SELECT 
    e.name AS employee_name,
    e.work_place,
    d.name AS department_name,
    l.name AS leader_name
FROM 
    employee e
INNER JOIN 
    department d 
ON 
    e.department_id = d.id
LEFT JOIN 
    employee l 
ON 
    e.leader_id = l.id;


-- 查询每个员工的名字和部门名称(内连接)
SELECT e.id AS employee_id,e.name AS employee_name,d.name AS department_name 
FROM employee e 
INNER JOIN department d ON e.department_id = d.id 
ORDER BY e.id ASC;


-- 查询每个员工的名字和部门名称(左连接，就算员工没有部门信息，也要在结果展示)
SELECT e.id AS employee_id,e.name AS employee_name,d.name AS department_name 
FROM employee e 
LEFT OUTER JOIN department d ON e.department_id = d.id
ORDER BY e.id ASC;

-- 查询每个员工的名字和部门名称(右连接，就算部门下边一个员工也没有，也显示在结果中)
SELECT e.id AS employee_id,e.name AS employee_name,d.name AS department_name 
FROM employee e 
RIGHT OUTER JOIN department d ON e.department_id = d.id
ORDER BY e.id ASC;

-- 查询每个员工的直属领导信息（员工名字、直属领导名字）
SELECT 
    e.name AS employee_name,
    l.name AS leader_name
FROM 
    employee e
LEFT JOIN 
    employee l
ON 
    e.leader_id = l.id;