這裡分享了關於Mysql相關基礎select查詢，一共20題，答案僅供參考，不代表唯一答案，有多種方法的朋友們歡迎在下方評論留言。 ...
數據獲取
表的數據信息(sql文件)放在這個鏈接里了，提取碼：52xz，需要的自行提取。數據來自網上的練習，已經給小伙伴們總結好了。
https://pan.baidu.com/s/11YMWaXtZb9K60cpOuYTwag將數據導入到mysql中
大家可以直接在navicat運行該腳本，就可以直接導入數據了，如果遇到問題很可能是編碼問題，改一下資料庫的編碼為gb2312，如圖：
基礎20題
點擊新建查詢就可以寫我們的mysql基礎20題了。
#(1)查詢和Zlotkey相同部門的員工姓名和工資
SELECT
last_name,
salary
FROM
employees
WHERE
department_id = ( SELECT department_id from employees WHERE last_name = "Zlotkey" );
#(2)查詢工資比公司平均工資高的員工的員工號，姓名和工資。
SELECT
employee_id,
last_name,
salary
FROM
employees
WHERE
salary > (SELECT AVG(salary) FROM employees);
#(3)選擇工資大於所有JOB_ID = SA_MAN 的員工的工資的員工的last_name, job_id, salary
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary > ( SELECT max( salary ) FROM employees WHERE job_id = "SA_MAN" );
#(4)查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
SELECT
employee_id,
last_name
FROM
employees
WHERE
department_id IN (
SELECT DISTINCT
( department_id )
FROM
employees
WHERE
last_name LIKE "%u%")
#(5)查詢在部門的location_id為1700的部門工作的員工的員工號
SELECT
employee_id
FROM
employees
WHERE
department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 );
#(6)查詢管理者是King的員工姓名和工資
SELECT
last_name,
salary
FROM
employees
WHERE
manager_id IN ( SELECT employee_id FROM employees WHERE last_name = "K_ing" );
#(7)查詢工資最低的員工信息: last_name, salary
SELECT
last_name,
salary
FROM
employees
WHERE
salary = ( SELECT min( salary ) FROM employees );
#(8)查詢平均工資最低的部門信息
SELECT
b.department_id,
d.department_name,
d.manager_id,
d.location_id
FROM
departments d
JOIN (
SELECT
department_id,
AVG( salary ) AS avg_salary
FROM
employees
GROUP BY
department_id
HAVING
avg_salary IN (
SELECT
MIN( a.avg_salary )
FROM (
SELECT
department_id,
AVG( salary ) AS avg_salary
FROM employees
GROUP BY department_id
) AS a
)
) AS b
ON d.department_id = b.department_id;
#(9)查詢平均工資最低的部門信息和該部門的平均工資（相關子查詢）
SELECT
b.department_id,
d.department_name,
d.manager_id,
d.location_id,
b.avg_salary
FROM
departments d
JOIN (
SELECT
department_id,
AVG( salary ) AS avg_salary
FROM
employees
GROUP BY
department_id
HAVING
avg_salary IN (
SELECT
MIN( a.avg_salary )
FROM (
SELECT
department_id,
AVG( salary ) AS avg_salary
FROM employees
GROUP BY department_id
) AS a
)
) AS b
ON d.department_id = b.department_id;
#(10)查詢平均工資最高的 job 信息
SELECT
b.job_id,
j.job_title,
j.min_salary,
j.max_salary
FROM
jobs j
JOIN
(
SELECT
job_id ,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
job_id
HAVING
avg_salary in
(
SELECT
MAX(a.avg_salary)
FROM
(
SELECT
job_id,
AVG( salary ) AS avg_salary
FROM
employees
GROUP BY
job_id
) as a
)
)as b
ON j.job_id = b.job_id;
#(11)查詢平均工資高於公司平均工資的部門有哪些?
SELECT
department_id,
AVG(salary) as avg_salary
FROM
employees
WHERE
department_id is not null
GROUP BY
department_id
HAVING
avg_salary > (
SELECT
AVG(salary)
FROM
employees );
#(12)查詢出公司中所有 manager 的詳細信息
SELECT
*
FROM
employees
WHERE
employee_id in
(
SELECT
DISTINCT manager_id
FROM
employees);
#(13)各個部門中 最高工資中最低的那個部門的 最低工資是多少?
SELECT
MAX(salary) as max_salary
FROM
employees
WHERE
department_id is not null
GROUP BY
department_id
ORDER BY
max_salary
LIMIT 0,1;
#(14)查詢平均工資最高的部門的 manager 的詳細信息: last_name, department_id, email, salary
SELECT
last_name,
department_id,
email,
salary
FROM
employees
WHERE
employee_id in
(
SELECT
DISTINCT manager_id
FROM
employees
WHERE
department_id = (
SELECT
department_id
FROM
employees
GROUP BY
department_id
HAVING
AVG( salary ) IN (
SELECT
MAX( a.avg_salary )
FROM
(
SELECT
department_id,
AVG( salary ) AS avg_salary
FROM
employees
GROUP BY
department_id
) AS a
)
)
);
#(15)查詢部門的部門號，其中不包括job_id是"ST_CLERK"的部門號
SELECT
department_id
FROM
employees
WHERE
job_id NOT IN (
SELECT
job_id
FROM
employees
WHERE
job_id = "ST_CLERK");
#(16)選擇所有沒有管理者的員工的last_name
SELECT
last_name
FROM
employees
WHERE
manager_id is null;
#(17)查詢員工號、姓名、雇用時間、工資，其中員工的管理者為 ‘De Haan’
SELECT
employee_id,
last_name,
hiredate,
salary
FROM
employees
WHERE
manager_id = (SELECT
employee_id
FROM
employees
WHERE
last_name = "De Haan");
#(18)查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資（相關子查詢）
SELECT employee_id,last_name,salary,department_id
FROM employees e1
WHERE salary > (
# 查詢某員工所在部門的平均
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.`department_id`);
#(19)查詢每個部門下的部門人數大於 5 的部門名稱（相關子查詢）
SELECT
department_name
FROM
departments
WHERE
department_id in (
SELECT
department_id
FROM
employees
GROUP BY
department_id
HAVING
COUNT(*) > 5);
#(20)查詢每個國家下的部門個數大於 2 的國家編號（相關子查詢）
SELECT
country_id
FROM
locations
WHERE
location_id in (
SELECT
location_id
FROM
departments
GROUP BY
location_id
HAVING
COUNT(*) > 2);
好了，今天的分享已經結束了，以後我還會多多更新這些對於大數據技術較為基礎的相關分享，希望大家能共同進步，一起加油！
