MySQL 多表查询

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null  unique comment '姓名',
    age int check ( age>0 && age<120),
    status char(1) default '1' comment '状态',
    gender char(1)

)comment '用户表';

INSERT INTO user (name, age, status, gender) VALUES ('Alice', 30, '1', 'F');
INSERT INTO user (name, age, status, gender) VALUES ('Bob', 25, '1', 'M');
INSERT INTO user (name, age, status, gender) VALUES ('Charlie', 35, '1', 'M');
INSERT INTO user (name, age, status, gender) VALUES ('Diana', 28, '1', 'F');
INSERT INTO user (name, age, status, gender) VALUES ('Ethan', 40, '1', 'M');
INSERT INTO user (name, age, status, gender) VALUES ('Francesca', 22, '1', 'F');
INSERT INTO user (name, age, status, gender) VALUES ('George', 55, '1', 'M');
INSERT INTO user (name, age, status, gender) VALUES ('Hannah', 29, '1', 'F');
INSERT INTO user (name, age, status, gender) VALUES ('Isaac', 18, '1', 'M');
INSERT INTO user (name, age, status, gender) VALUES ('Jessica', 32, '1', 'F');

select * from user;
INSERT INTO user (age, status, gender) VALUES (32, '1', 'F');

delete from user where id=13;
update user set gender='M' WHERE id=1;

INSERT INTO user (name, age, status, gender) VALUES ('J1a', 10, '1', 'F');

INSERT INTO user (name, age, gender) VALUES ('Is1aa1c', 18, 'M');

# alter table employees  add constraint fk_emp foreign key (department_id) references departments(id) on update cascade on delete cascade;

CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(50) NOT NULL COMMENT '部门名称',
    description TEXT COMMENT '部门描述'
) COMMENT '部门表';


-- 创建员工表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(100) NOT NULL COMMENT '员工姓名',
    position VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10, 2) COMMENT '薪水',
    department_id INT,
    -- 设置外键约束，并指定 ON DELETE CASCADE 以实现级联删除
    FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE
) COMMENT '员工表';

INSERT INTO department (name, description) VALUES
('人力资源部', '负责招聘、培训、员工关系等'),
('技术部', '负责软件开发、系统维护、技术支持等'),
('市场部', '负责市场推广、品牌建设、销售策略等'),
('财务部', '负责财务管理、预算编制、会计审计等'),
('法务部', '负责法律咨询、合同审核、合规管理等'),
('运营部', '负责运营规划、流程管理、客户服务等');

INSERT INTO employee (name, position, salary, department_id) VALUES
('张三', '人力资源经理', 12000.00, 1),
('李四', '招聘专员', 8000.00, 1),
('王五', '高级软件工程师', 25000.00, 2),
('赵六', '软件工程师', 20000.00, 2),
('孙七', '市场总监', 22000.00, 3),
('周八', '市场推广专员', 10000.00, 3),
('吴九', '财务经理', 15000.00, 4),
('郑十', '会计', 12000.00, 4),
('陈十一', '法务主管', 18000.00, 5),
('刘十二', '法务助理', 10000.00, 5),
('高十三', '运营总监', 20000.00, 6),
('黄十四', '运营经理', 16000.00, 6),
('何十五', '客服经理', 13000.00, 6),
('郭十六', '客服专员', 9000.00, 6),
('曹十七', 'IT支持工程师', 14000.00, 2); -- 假设IT支持属于技术部

INSERT INTO employee (name, position, salary) VALUES ('郭十1六', '客服专员', 9000.00);
# 多表查询
select * from employee,department;

select * from department,employee;
# 隐式内连接
select * from employee,department where department_id=department.id;

# 显示内连接 inner join ... on...
select e.name,d.name from department d inner join employee e on e.department_id=d.id;

# 查询emp表的所有数据和对应的部门名称 左外连接
select e.*,d.name from employee e left join department d on e.department_id = d.id;

# 查询dep表的所有数据和对应的员工信息 右外连接
select d.* ,e.* from employee e right join department d on e.department_id = d.id;

# 查询dep表的所有数据和对应的员工信息 左外连接
select d.name ,e.*from department d left join employee e on e.department_id = d.id;

# 自连接 查询员工及其领导的名字
select e.name,e_m.name,e_m.position from employee e inner join employee e_m on e.manager_id = e_m.id;

# 查询员工及其领导 没有领导也要查询材料
select e.name,e_m.name,e_m.position from employee e left  join employee e_m on e.manager_id = e_m.id;

# union 联合查询
select * from employee  e where e.age>50 and e.salary >10000;

select * from employee e where e.salary>10000
UNION
select * from employee e where e.age<50;

# 标量子查询
# 查询运营部的所有员工
select * from employee inner join department d on employee.department_id = d.id;
select id from department where name = '运营部';

select * from employee  where department_id=(select id from department where name = '运营部');

# 列子查询
# 查询技术部和运营部的所有员工信息
select id from department d where d.name='运营部' or d.name='技术部';
select * from employee where department_id in (select id from department d where d.name='运营部' or d.name='技术部');

# 查询比财务部所有人工资都高的员工信息
select e.salary from employee e where e.department_id=(select id from department d where d.name='财务部');
select e.salary from employee e where e.department_id=(select id from department d where d.name='财务部') order by salary desc limit 1;
select * from employee where salary>(select e.salary from employee e where e.department_id=(select id from department d where d.name='财务部') order by salary desc limit 1)


select * from employee where salary> all((select e.salary from employee e where e.department_id=(select id from department d where d.name='财务部')));

# 查询比技术部任意一人工资都高的员工信息
select e.salary from employee e where e.department_id=(select id from department d where d.name='技术部');
select * from employee where salary> any((select e.salary from employee e where e.department_id=(select id from department d where d.name='技术部')));

# 行子查询

select salary,manager_id from employee  where name='王五' or name= '李四';
# 一行 =
select * from employee where (salary,manager_id)=(select salary,manager_id from employee  where name='王五');
# 多行多列 用in
select * from employee where (salary,manager_id) in (select salary,manager_id from employee  where name='王五' or name= '李四');