MySQL多表查询练习题

心已赠人 2022-05-14 11:08 294阅读 0赞

## 练习题 ##

数据表

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'),
    ('104','1004');
    
    -- 想工资表中插入数据
    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的部门有哪些

整理了图片给大家方便阅读:  
![这里写图片描述][70]

补充:

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

[70]: /images/20220514/21293816c7fc4001a02b6897d976f437.png