# sql99語法
/*
語法：
select 查詢列表
from 表1 別名 【連接類型】
join 表2 別名
on 連接條件
【where 篩選條件】
【group by 分組】
【having 分組后篩選】
【order by 排序列表】
分類
內連接（重點）： inner
外連接
左外（重點）: left 【outer】
右外（類似左外） right 【outer】
全外： full 【outer】
交叉連接：cross
*/
# 一、內連接(就是把兩個表的字段全部連接，表間沒有順序）
/*
select 查詢列表
from 表1 別名
inner join 表2 別名 on 連接條件
inner join 表3 別名 on 連接條件
【where 篩選條件】
【group by 分組】
【having 分組后篩選】
【order by 排序列表】
分類：等值，非等值，自連接
*/
#1 等值連接
#案例1：查詢員工名，部門名 【等值內連】
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id` = d.`department_id`;
#案例2：查詢名字中包含e的員工名和工種名【等值內連 + 篩選】
SELECT last_name, job_title
FROM employees AS e
INNER JOIN jobs AS j
ON e.`job_id` = j.`job_id`
WHERE last_name LIKE '%e%';
#案例3：查詢部門個數>3的城市名和部門個數【等值內連+分組+分組后篩選】
SELECT city,COUNT(*) AS "部門個數"
FROM departments AS d
INNER JOIN locations AS l
ON d.`location_id` = l.`location_id`
GROUP BY l.`city`
HAVING 部門個數>3;
#案例4：查詢部門員工數>3的部門名稱和員工個數，并降序排序
#【等值連接+分組+分組后篩選+ 排序】
SELECT department_name, COUNT(*) AS "員工個數"
FROM departments AS d
INNER JOIN employees AS e
ON d.`department_id` = e.`department_id`
GROUP BY e.`department_id`
HAVING 員工個數 > 3
ORDER BY 員工個數 DESC;
#案例5：查詢員工名，部門名，工種名，并按部門名排序
#【多表等值連接+排序】
SELECT last_name, department_name, job_title
FROM employees AS e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
INNER JOIN jobs AS j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
#2 非等值連接
#案例1：查詢員工的工資級別
SELECT salary,grade_level
FROM employees AS e
INNER JOIN job_grades AS j
ON e.salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#案例2：查詢每個工資級別下員工個數》2的工資級別，并降序排列
SELECT COUNT(*), grade_level
FROM employees AS e
INNER JOIN job_grades AS j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY COUNT(*) DESC;
#3 內連接中的自連接
#案例1：查詢員工的名字，及其上級的名字
SELECT e.last_name, m.last_name
FROM employees AS e
INNER JOIN employees AS m
ON e.`manager_id` = m.`employee_id`
ORDER BY e.`last_name` ASC;
#案例2：查詢名字中包含字符k的員工名，及其上級的名字
SELECT e.last_name, m.last_name
FROM employees AS e
INNER JOIN employees AS m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%'
ORDER BY e.`last_name` ASC;
