MySQL 简单查询记录（三）

#sql99连接查询写法  内连接（等值连接、非等值连接、自连接）
#1、等值连接
#案例一:查询员工名、部门名
SELECT 
  d.department_name,
  e.last_name 
FROM
  departments d 
  INNER JOIN employees e 
    ON e.`department_id` = d.`department_id`

#案例二:查询名字中包含e的员工名和工种名
SELECT 
  e.last_name,
  j.job_title 
FROM
  employees e 
  INNER JOIN jobs j 
    ON e.`job_id` = j.job_id 
WHERE e.`last_name` LIKE "%e%" 

#案例三：查询部门个数大于3城市名和部门个数
SELECT 
  COUNT(*) 部门个数,
  city 
FROM
  departments d 
  INNER JOIN locations l 
    ON d.`location_id` = l.location_id 
GROUP BY city 
HAVING COUNT(*) > 3 

#案例四: 查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序
SELECT 
  COUNT(*) 员工个数,
  department_name 
FROM
  departments d 
  INNER JOIN employees e 
    ON e.`department_id` = d.`department_id` 
GROUP BY department_name 
HAVING COUNT(*) > 3 
ORDER BY 员工个数 DESC 

#案例五: 查询员工名、部门名、工种名,并按部门名降序 (三表连接)
SELECT 
  last_name,
  department_name,
  job_title 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
  INNER JOIN jobs j 
    ON e.`job_id` = j.job_id 
ORDER BY department_name DESC 

#2、非等值连接
#案例1:查询员工的工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades j 
    ON salary BETWEEN `lowest_sal` AND `highest_sal` 

#案例2:查询每个工资级别的个数大于20的个数,并且按工资级别降序
SELECT 
  COUNT(*),
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades j 
    ON salary BETWEEN `lowest_sal` 
    AND `highest_sal` 
GROUP BY grade_level 
HAVING COUNT(*) > 20
ORDER BY grade_level DESC 

#3、自连接
#查询员工姓名中包含k字符的员工名、上级的名字
SELECT 
  e.last_name AS 员工,
  m.last_name AS 领导 
FROM
  employees e 
  INNER JOIN employees m 
    ON e.manager_id = m.employee_id 
WHERE e.last_name LIKE "%k%"

#sql99连接查询写法  外连接（左外连接、右外连接、全外连接）
#查询没有男朋友的女神名
#左外连接
SELECT 
  b.`name` 
FROM
  beauty b 
  LEFT OUTER JOIN boys bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.id IS NULL 
#右外连接
SELECT 
  b.`name` 
FROM
  boys bo 
  RIGHT OUTER JOIN beauty b
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.id IS NULL 

#案例查询出哪个部门没有员工
#左外连接
SELECT 
  d.*,
  e.`employee_id`
FROM
  departments d
  LEFT OUTER JOIN employees e
    ON d.`department_id` = e.`department_id` 
  WHERE e.`employee_id` IS NULL
#右外连接 
SELECT 
  d.*,
  e.`employee_id` 
FROM
  employees e
  RIGHT OUTER JOIN departments d
    ON d.`department_id` = e.`department_id` 
  WHERE e.`employee_id` IS NULL

#sql99连接查询写法  交叉连接（笛卡尔积）
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo ;

#案例一、查询编号>3的女神的男朋友信息,如果有则列出详细信息,没有用null填充
SELECT 
  bo.*,
  b.id,
  b.`name` 
FROM
  beauty b  
  LEFT OUTER JOIN boys bo
    ON bo.`id` = b.`boyfriend_id` 
WHERE b.`id` > 3 
#案例二、查询出哪个城市没有部门
SELECT 
  g.city,
  d.* 
FROM
  locations g 
  LEFT OUTER JOIN departments d 
    ON g.`location_id` = d.`location_id` 
WHERE d.`department_id` IS NULL 
#案例三、查询出部门名为SAL或IT的员工信息
SELECT 
  e.*,
  d.`department_name`,
  d.`department_id`
FROM
  departments d
  LEFT JOIN employees e
    ON e.`department_id` = d.`department_id` 
WHERE d.`department_name` IN ('SAL','IT')