mysql 练习题

准备工作

• 创建表格

  • 部门表 department

    • 字段

      • id：序号，自增，整型，主键
      • number：部门编号：varchar(15)， 非空
      • name：部门名称，varchar(50)，非空
      • location：部门所在地，varchar(50)，非空
      • super_NO：部门经理 ID，varchar(15)

    • 创建表命令

      create table department(
          id int auto_increment primary key,
          number varchar(15) not null,
          name varchar(50) not null,
          location varchar(50) not null,
          super_NO varchar(15)
      );
      

    • 数据

      insert into department
          values 
              (1,'D2019050001','清华集团','北京',null),
              (2,'D2019050002','集团总部','北京','D2019050001'),
              (3,'D2019060001','成都中心','成都','D2019050001'),
              (4,'D2019060002','武汉中心','武汉','D2019050001'),
              (5,'D2019090001','上海中心','上海','D2019050001'),
              (6,'D2020010001','广州中心','广州','D2019050001'),
              (7,'D2019090011','财务部','上海','D2019090001'),
              (8,'D2020020012','行政部','上海','D2019090001'),
              (9,'D2019060011','财务部','成都','D2019060001'),
              (10,'D2019060012','技术部','成都','D2019060001'),
              (11,'D2019060013','网络部','成都','D2019060001'),
              (12,'D2019060014','市场部','成都','D2019060001');
      

  • 员工表 employee

    • 字段

      • id：编号，整型，自增，主键
      • number：员工编号，varchar(15)，not null
      • name：员工姓名，varchar(50)，not null
      • job：职务，varchar(50)，not null
      • leader_NO：varchar(15)
      • hire_date：入职日期 date
      • salary：工资，decimal(10,2)
      • bonus：奖金，decimal(10,2)
      • level：等级，int
      • department_NO：varchar(15)，not null

    • 命令

      create table employee(
          id int auto_increment primary key,
          number varchar(15) not null,
          name varchar(50) not null,
          job varchar(50) not null,
          leader_NO varchar(15),
          hire_date date not null,
          salary decimal(10,2),
          bonus decimal(10,2),
          level int,
          department_NO varchar(15)
      
      );
      

    • 数据

      insert into employee
          values
              (1,'E2018010001','吴所为','总经理',null,'2018-01-01',2800.00,4000.00,9,'D2019060001'),
              (2,'E2018070003','韩金龙','总经理',null,'2018-07-01',2800.00,4000.00,8,'D2019090001'),
              (3,'E2018060002','王黎明','总经理',null,'2018-06-01',2800.00,4000.00,8,'D2019060002'),
              (4,'E2018020002','龚爱国','总经理',null,'2018-02-01',2800.00,4000.00,8,'D2020010001'),
              (5,'E2019050001','马金花','财务总监','E2018010001','2019-01-01',3800.00,500.00,6,'D2019060011'),
              (6,'E2019050018','李昌贵','财务专员','E2019050001','2019-04-21',2800.00,800.00,4,'D2019060011'),
              (7,'E2019100011','王建国','网络管理员','E2018010001','2019-10-01',3200.00,null,5,'D2019060013'),
              (8,'E2019110004','黎锦熙','网络管理员','E2019100011','2019-11-01',3200.00,null,5,'D2019060013'),
              (9,'E2020020023','繁茂森','销售专员','E2019060005','2020-02-01',2800.00,0.00,4,'D2019060014'),
              (10,'E2019060005','张善民','销售经理','E2018010001','2019-06-01',2800.00,500.00,6,'D2019060014'),
              (11,'E2019060009','廖云龙','技术总监','E2018010001','2019-06-01',4800.00,2000.00,7,'D2019060012'),
              (12,'E2019120021','刘盛会','研发工程师','E2019060009','2019-12-11',4800.00,500.00,5,'D2019060012'),
              (13,'E2019020001','马明全','高级工程师','E2019060009','2019-02-01',4800.00,1000.00,6,'D2019060012'),
              (14,'E2019120015','李意','行政专员','E2018070003','2019-12-20',2800.00,500.00,4,'D2019090001'),
              (15,'E2019020017','刘六一','财务总监','E2018070003','2019-02-16',3800.00,1000.00,6,'D2019090011'),
              (16,'E2020020012','陈超','研发工程师','E2019060009','2020-02-18',4200.00,500.00,5,'D2019060012');
      

习题

• 单表练习

  • 1. 查询出部门编号为 D2019060011 的所有员工

    select * 
        from employee
        where department_NO='D2019060011';
    

    • 验证
      ​​
  • 2. 所有财务总监的姓名、员工编号和部门编号。

    select name,number,department_NO
        from employee
        where job='财务总监';
    

    • 验证
      ​
  • 3. 找出奖金高于工资的员工。

    select name 
        from employee
        where bonus>salary;
    

    • 验证
      ​​
  • 4. 找出奖金高于工资的 40% 的员工。

    select name
        from employee
        where bonus>salary*0.4;
    

    • 验证
      ​​

  • 5 找出部门编号为 D2019090011 中所有财务总监，和部门编号为 D2019060011 中所有财务专员的详细资料。

    select *
        from employee
        where department_NO='D2019090011' and job='财务总监' 
        or department_NO='D2019060011' and job='财务专员';
    

    • 验证
      ​​
  • 6. 找出部门编号为 D2019090001 中所有总经理，部门编号为 D2019090011 中所有财务总监，还有即不是总经理又不是销售总监但其工资大或等于 4000 的所有员工详细资料。

    select * 
        from employee
        where (department_NO='D2019090001' and job='总经理' )
        or (department_NO='D2019090011' and job='财务总监')
        or(job!='总经理' and job!='销售总监' and salary>=4000);
    

    • 验证
      ​​

  • 7.有奖金的工种。

    select job
        from employee
        where bonus is not null
        group by job;
    

  • 8.无奖金或奖金低于 1000 的员工。

    select name,bonus
        from employee
        where bonus is null or bonus<1000;
    

  • 9. 查询名字由两个字组成的员工。
    • 汉字通常中 3 个字节

    select name
        from employee
        where length(name)=6;
    

     select name
        from employee
        where name like '__';
    

  • 10.查询 2020 年入职的员工。

    select name
        from employee
        where year(hire_date)='2020';
    

    select name
        from employee
        where hire_date like '2020%';
    

  • 11. 查询所有员工详细信息，用员工编号升序排序。

    select *
        from employee
        order by number;
    

  • 12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。

     select *
        from employee
        order by salary desc,hire_date asc;
    

  • 13. 查询每个部门的平均工资。

        select *
        	from employee
        	group by department_NO;
        

  • 14. 求出每个部门的雇员数量。

    • select department_NO,count(*) as 员工人数

      • from employee
      • group by department_NO;
  • 15. 查询每种工作的最高工资、最低工资、人数.

    • select job,max(salary) as 最高工资,min(salary) as 最低工资,count(id) as 人数

      • from employee
      • group by job;
  • 16. 列出最低薪金大于 4000 的各种工作及从事此工作的员工人数。

    • select job,count(id) as 员工人数

      • from employee
      • where salary>4000
      • group by job;
  • 17. 统计各部门工资总和，显示部门编号和该部门雇员的月工资的总和，并且要满足该部门雇员的月工资合计大于 6000，输出结果按月工资的合计升序排列。

    • select department_NO,sum(salary) as 工资总和

      • from employee
      • group by department_NO
      • having 工资总和>6000
      • order by 工资总和;

• 多表练习

  • 1. 列出所有员工的姓名及其直接上级的姓名。

    • 这里两个字段都在一个表中，但是也要进行多表连接，自己连接自己

    • select e1.name as 员工名称,e2.name as 直接领导

      • from employee as e1 inner join employee as e2
      • on e1.leader_NO=e2.number;

    • select e1.name as 员工名称,e2.name as 直接领导

      • from employee as e1,employee as e2
      • where e1.leader_NO=e2.number;
  • 2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

    • 这题要分几部分完成

      • 1、先完成显示所有员工入职日期和他的直接领导的入职日期，先自己连接自己

        • select *

          • from employee as e1 inner join employee as e2
          • on e1.leader_NO =e2.number;

        • 这样即显示了自己的入职时间，也显示了直接领导的入职时间
          ​​

      • 2、加入筛选条件

        • select e1.number as 员工编号,e1.name as 员工名称,e1.hire_date as 员工入职时间,e2.name as 直接领导名称,e2.hire_date as 直接领导入职时间

          • from employee as e1 inner join employee as e2
          • on e1.leader_NO =e2.number
          • where e1.hire_date<e2.hire_date;

        • ​​

      • 3、还要显示部门的名称，所以还要连接 department 表

        • select e1.number as 员工编号,e1.name as 员工名称,d.name as 部门名称

          • from employee as e1 inner join employee as e2
          • on e1.leader_NO =e2.number
          • inner join department as d
          • on e1.department_NO=d.number
          • where e1.hire_date<e2.hire_date;

        • ​​

    • select e1.number as 员工编号,e1.name as 姓名,d.name as 部门名称

      • from employee as e1 inner join employee as e2
      • on e1.leader_NO=e2.number
      • inner join department as d
      • on e1.department_NO=d.number
      • where e1.hire_date<e2.hire_date;
  • 3. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

    • select *

      • from department as d left outer join employee as e
      • on d.number=e.department_NO
  • 4. 列出在财务部工作的员工的姓名，假定不知道财务部的部门编号。

    • 注意子查询的用法

    • select name

      • from employee
      • where department_NO in(select number from department where name='财务部');
  • 5. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导。

    • 第一步、显示所有员工信息和以及部门领导的名字

      • select *

        • from employee as e1 inner join employee as e2
        • on e1.leader_NO=e2.number;

    • 第二步、显示出部门名称，因为部门名称在 department 表中，所以要连接它

      • select e1.number,e1.name,e1.salary,e1.department_NO,e1.leader_NO,e2.name,d.name

        • from employee as e1 inner join employee as e2
        • on e1.leader_NO=e2.number
        • inner join department as d
        • on e1.department_NO=d.number;

    • 第三步、筛选出大于平均工资的记录

      • select e1.number,e1.name,e1.salary,e1.department_NO,e1.leader_NO,e2.name,d.name

        • from employee as e1 inner join employee as e2
        • on e1.leader_NO=e2.number
        • inner join department as d
        • on e1.department_NO=d.number
        • where e1.salary>(select avg(salary) from employee);
  • 6. 列出与陈超从事相同工作的所有员工及部门名称。

    • select e.*,d.name

      • from employee as e inner join department as d
      • on e.department_NO=d.number
      • where e.job=(select job from employee where name='陈超');
  • 7. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

    • select e.number as 部门编号,d.name as 部门名称 ,d.location as 部门位置,count(*) as 部门人数

      • from employee as e inner join department as d
      • on e.department_NO=d.number
      • group by d.number
      • having 部门人数>=1;
  • 8. 列出薪金高于在财务部工作员工平均薪金的员工姓名和薪金、部门名称

    • select e.name as 员工姓名,e.salary as 员工薪金,d.name as 部门名称

      • from employee as e inner join department as d
      • on e.department_NO=d.number
      • where salary>(select avg(salary) from employee where department_NO in(select number from department where name='财务部'));