MySQL多表查询练习题

练习题

数据表

CREATE TABLE departments (
    dept_no     CHAR(4)         PRIMARY KEY COMMENT '部门编码',
    dept_name   VARCHAR(40)     NOT NULL UNIQUE COMMENT '部门名称'
) COMMENT '部门表';

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL COMMENT '部门编码',
    dept_no     CHAR(4)         NOT NULL COMMENT '雇员ID',
    FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
) COMMENT '雇员与部门关系表';

CREATE TABLE salaries (
    emp_no      INT             NOT NULL COMMENT '雇员ID',
    salary      INT             NOT NULL COMMENT '工资',
    month       INT             NOT NULL COMMENT '月份',
    level       INT             NOT NULL COMMENT '工资等级',
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
) COMMENT '薪资表'; 

-- 向雇员表中插入数据
insert into employees values
('100','1990-08-19','JACK','M','20160811'),
('101','1970-08-12','TOM','M','20100606'),
('102','1996-03-19','JAMES','M','20140101'),
('103','1987-04-28','KETTY','F','20130910'),
('104','1983-05-19','JIM','F','20160418');

-- 向部门表中插入数据
insert into departments values
('1001','A'),
('1002','B'),
('1003','C'),
('1004','D');

-- 向部门与雇员关系表中插入数据
insert into dept_emp values
('100','1001'),
('101','1001'),
('102','1002'),
('103','1003'),
('103','1001');

-- 想工资表中插入数据
insert into salaries values
('100','12000','201601',2),
('101','9000','201601',1),
('102','90000','201601',10),
('103','2300','201601',1),
('104','4000','201601',1),
('100','12000','201602',2),
('101','9000','201602',1),
('102','90000','201602',10),
('103','2300','201602',1),
('104','4000','201602',1),
('100','12000','201603',2),
('101','9000','201603',1),
('102','90000','201603',10),
('103','2300','201603',1),
('104','4000','201603',1),
('100','12000','201604',2),
('101','9000','201604',1),
('102','90000','201604',10),
('103','2300','201604',1),
('104','4000','201604',1);

题目:

-- 1、查看部门表中的所有记录
-- 2、查看员工表中的所有记录
-- 3、查询所有员工所属的部门
-- 4、查询属于A部门的员工
-- 5、查询属于A部门员工的平均工资
-- 6、查询属于A部门员工的每月平均工资
-- 7、查询最高工资的员工属于哪个部门
-- 9、查询所有员工的薪水总和
-- 10、查询各个部门的员工平均工资
-- 11、查询不同工资水平下各有多少员工
-- 12、查询名字中包含M的员工的每月平均工资
-- 13、查询所有女性中工资最高的员工
-- 14、查询每月员工工资之和大于30000的部门有哪些   

整理了图片给大家方便阅读:

补充:

    on delete cascade 是级联删除的意思
    意思是 当你删除主键表时，那么外键表也会跟随一起更新或删除

    1.内连接查询
    *隐式内连接:
    SELECT * FROM employees e, departments d, dept_emp de WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

    *显示内连接:
    SELECT e.name, d.dept_name FROM employees e INNER JOIN dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON d.dept_no=de.dept_no;

    2.外连接查询:
    *左外连接:
    SELECT e.name, d.dept_name FROM employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no LEFT JOIN departments d ON d.dept_no=de.dept_no;

    *右外连接:
    SELECT e.name, d.dept_name FROM employees e RIGHT JOIN dept_emp de ON e.emp_no=de.emp_no RIGHT JOIN de

答案不唯一
提供答案仅限参考:

-- 1、查看部门表中的所有记录
      select * from departments
-- 2、查看员工表中的所有记录
      select * from employees
-- 3、查询所有员工所属的部门  employees departments dept_emp
      select a.emp_no,a.name,b.dept_no,b.dept_name 
        from employees a,departments b,dept_emp c 

      select a.emp_no,a.name,b.dept_no,b.dept_name 
       from employees a left join dept_emp b on a.emp_no = b.emp_no
                        left join departments c on c.dept_no = a.dept_no

      select a.emp_no,a.name from employees a left join dept_emp b on a.emp_no= b.emp_no

-- 4、查询属于A部门的员工  
      select a.name from employees a left join dept_emp b on a.emp_no = b.emp_no
                              left join departments c on c.dept_no = b.dept_no
                             where c.dept_name = 'A'


      SELECT AVG(salary) FROM employees a LEFT JOIN dept_emp b ON a.emp_no = b.emp_no
                                          LEFT JOIN departments c ON c.dept_no = b.dept_no
                                          LEFT JOIN salaries d ON a.emp_no = d.emp_no 
                                         WHERE c.dept_name = 'A'


-- 6、查询属于A部门员工的每月平均工资
      SELECT AVG(salary),d.month FROM employees a LEFT JOIN dept_emp b ON a.emp_no = b.emp_no
                                          LEFT JOIN departments c ON c.dept_no = b.dept_no
                                          LEFT JOIN salaries d ON a.emp_no = d.emp_no 
                                         WHERE c.dept_name = 'A'
                                         group by d.month

-- 7、查询最高工资的员工属于哪个部门
        SELECT d.dept_name, e.name, s.salary 
        FROM employees e, departments d, dept_emp de, salaries s 
        WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no AND s.emp_no = e.emp_no ORDER BY s.salary DESC LIMIT 1;


      select count(dept_no),emp_no from dept_emp group by emp_no;

-- 9、查询所有员工的薪水总和
      select sum(salary) from salaries

-- 10、查询各个部门的员工平均工资
      select avg(a.salary),b.dept_no from salaries a join dept_emp b on a.emp_no = b.emp_no
                    group by b.dept_no

-- 11、查询不同工资水平下各有多少员工
     SELECT COUNT(DISTINCT emp_no),LEVEL FROM salaries GROUP BY LEVEL

-- 12、查询名字中包含M的员工的每月平均工资
      SELECT AVG(salary),MONTH FROM salaries a LEFT JOIN employees b ON a.emp_no = b.emp_no
           WHERE b.name LIKE '%M%'
            GROUP BY MONTH

      SELECT  AVG(a.salary),a.emp_no,b.name FROM salaries a LEFT JOIN employees b ON a.emp_no = b.emp_no
            GROUP BY a.emp_no

-- 13、查询所有女性中工资最高的员工
       select distinct b.name,b.emp_no from salaries a left join employees b on a.emp_no = b.emp_no where salary = (
       select max(salary) from salaries a left join employees b on a.emp_no = b.emp_no 
              where b.gender = 'F') and b.gender = 'F'

-- 14、查询每月员工工资之和大于30000的部门有哪些
       SELECT SUM(a.salary),b.dept_no,a.month,c.dept_name FROM salaries a JOIN dept_emp b ON a.emp_no = b.emp_no
              LEFT JOIN departments c ON c.dept_no = b.dept_no
                GROUP BY b.dept_no,a.month
                HAVING SUM(a.salary) > 30000