小米训练营-第二次作业

1. 建表语句

create table `position`
(
    `id`          bigint   not null auto_increment,
    `name`        char(20) not null,
    `status`      tinyint,
    `gmt_created` datetime,
    `gmt_modify`  datetime,
    primary key (`id`),
    unique index  `uk_name` (`name`)
);

create table `employee`
(
    `id`          bigint       not null auto_increment,
    `name`        varchar(255) not null,
    `gender`      tinyint,
    `phone`       varchar(64),
    `email`       varchar(255),
    `birth_date`  date,
    `hire_date`   date,
    `manager_id`  bigint,
    `position`    bigint,
    `status`      tinyint,
    `gmt_created` datetime,
    `gmt_modify`  datetime,
    primary key (`id`),
    index `k_manager_id` (`manager_id`)
);

create table `emp_dep_relation`
(
    `id`            bigint not null auto_increment,
    `employee_id`   bigint not null,
    `department_id` bigint not null,
    `start_date`    date,
    `end_date`      date,
    `status`        tinyint,
    `gmt_created`   datetime,
    `gmt_modify`    datetime,
    primary key (`id`),
    index `k_employee_id` (`employee_id`),
    index `k_department_id` (`department_id`)
);

create table `department`
(
    `id`          bigint       not null auto_increment,
    `name`        varchar(255) not null,
    `manager_id`  bigint,
    `parent_id`   bigint,
    `status`      tinyint,
    `gmt_created` datetime,
    `gmt_modify`  datetime,
    primary key (`id`),
    index `k_manager_id` (`manager_id`),
    index `k_parent_id` (`parent_id`)
);

create table `emp_pro_relation`
(
    `id`          bigint not null auto_increment,
    `employee_id` bigint,
    `project_id`  bigint,
    `status`      tinyint,
    `gmt_created` datetime,
    `gmt_modify`  datetime,
    primary key (`id`),
    index `k_employee_id` (`employee_id`),
    index `k_project_id` (`project_id`)
);

create table `project`
(
    `id`          bigint       not null auto_increment,
    `name`        varchar(255) not null,
    `manager_id`  bigint,
    `start_date`  date,
    `end_date`    date,
    `status`      tinyint,
    `gmt_created` datetime,
    `gmt_modify`  datetime,
    primary key (`id`),
    index `k_manager_id` (`manager_id`)
);

2. 查出员工张三的上级领导姓名

插入数据（初始化）

insert into employee
values (1, '韩磊', 0, '13599991111', 'hanlei@xm.com', '1991-01-01', '2008-08-08', null, 1, 1, '2008-08-08 00:00:00',
        '2018-08-08 12:00:01'),
       (2, '张三', 1, '18688880169', 'zhangsan@xm.com', '1992-02-02', '2010-01-08', 1, 2, 1, '2008-08-08 00:00:00',
        '2018-08-08 12:00:01');

执行查询操作

# 子查询
select name as "张三的上级领导姓名"
from employee
where manager_id in (
    select id
    from employee
    where name = '张三'
);

# 连表查询
select m.name as "张三的上级领导姓名"
from employee e
left join employee m on e.manager_id = m.id
where e.name = '张三';

3. 将position表中的name字段属性修改为varchar(200)

alter table position modify `name` varchar(200)

4. 统计财务部的员工数

插入数据（初始化）

# 部门表
insert into department
values (1, '腾达集团', 1, null, 1, '2008-08-08 00:00:00', '2008-08-08 00:00:00'),
       (2, '销售部', 2, 1, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01'),
       (3, '财务部', 3, 1, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01'),
       (4, '税务部', 4, 3, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01');

# 员工部门关系表
insert into emp_dep_relation
values (1, 1, 1, '2008-08-08 00:00:00', null, 1, '2008-08-08 00:00:00', '2018-08-08 00:00:00'),
       (2, 2, 2, '2008-08-08 00:00:00', null, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01'),
       (3, 3, 3, '2008-08-08 00:00:00', null, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01'),
       (4, 4, 4, '2008-08-08 00:00:00', null, 1, '2008-08-08 00:00:00', '2018-08-08 12:00:01');

查询财务部的员工数

# 根据财务部的名字查询
select count(*) as `财务部员工数`
from emp_dep_relation
left join department on emp_dep_relation.department_id = department.id
where department.name = '财务部';

# 根据财务部的id查询
select count(*) as `财务部员工数`
from emp_dep_relation
where department_id = 3;

5. 统计财务部及其子部门的员工数

select count(*) as `财务部及子部门员工数`
from emp_dep_relation
left join department on emp_dep_relation.department_id = department.id
where department.id = 3
   or department.parent_id = 3;

6. 查询没有参与任何项目的员工姓名

select name as `没有参与任何项目的员工姓名`
from employee
left join emp_pro_relation on employee.id = emp_pro_relation.employee_id
where emp_pro_relation.employee_id is null;