MySQL多表连接查询练习

浅浅的花香味﹌ 2024-04-06 13:01 94阅读 0赞

### 一、笛卡尔集&内连接 ###

1.查询90号部门员工的job\_id和90号部门的location\_id

SELECT job_id,location_id
    FROM employees e,departments d
    WHERE e.`department_id`=d.`department_id`
    AND e.`department_id`=90;

2.选择所有有奖金的员工的last\_name,department\_name,location\_id,city

SELECT last_name,department_name,l.location_id,city
    FROM employees e,departments d,locations l
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.`location_id`
    AND e.`commission_pct` IS NOT NULL;

3.选择city在Toronto工作的员工的last\_name , job\_id , department\_id , department\_name

SELECT last_name , job_id , d.department_id , department_name 
    FROM employees e,departments d,locations l
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.`location_id`
    AND city='Toronto';

### 二、外连接 ###

1.查询城市名包含a字符的哪个城市没有部门，并按城市名降序

SELECT city
    FROM departments d
    RIGHT OUTER JOIN locations l
    ON d.`location_id`=l.`location_id`
    WHERE d.`department_id` IS NULL
    AND city LIKE '%a%'
    ORDER BY city DESC;

2.查询部门名为SAL或IT的员工信息

SELECT e.*,d.`department_name`
    FROM departments d
    LEFT JOIN employees e
    ON d.`department_id`=e.`department_id`
    WHERE d.`department_name` IN('SAL','IT');

### 三、子查询 ###

1.返回公司工资最少的员工的last\_name,job\_id和salary

SELECT last_name,job_id,salary
    FROM employees
    WHERE salary=(
    SELECT MIN(salary)
    FROM employees
    );

2.查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT MIN(salary),department_id
    FROM employees 
    GROUP BY department_id
    HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees 
    WHERE department_id=50
    );

3.查询管理者是King的员工姓名和工资

SELECT last_name,salary
    FROM employees
    WHERE manager_id IN(
    SELECT employee_id
    FROM employees
    WHERE last_name='K_ing'
    );

4.查询在部门的location\_id为1700的部门工作的员工的员工号

SELECT employee_id
    FROM employees
    WHERE department_id =ANY(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id =1700
    );