数据库--day5

#添加筛选
#查询有奖金的员工名，部门名
SELECT last_name,department_name,`commission_pct`
FROM employees AS e,`departments` AS d
WHERE `commission_pct`IS NOT NULL 
    AND e.`employee_id`=d.`department_id`

#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,`city`
FROM departments AS d,locations AS l
WHERE l.`location_id`=d.`location_id`
    AND `city` LIKE '_o%'

#添加分组
#查询每个城市的部门个数
SELECT COUNT(*),city
FROM `locations` AS l,`departments` AS d
WHERE d.`location_id`=l.`location_id`
GROUP BY `city`

#查询有奖金的 每个部门的部门名和 部门的领导编号 和该部门的最低工资

SELECT department_name,d.`manager_id` ,MIN( salary )
FROM `departments` AS d,`employees` AS e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY  `department_name`,d.`manager_id`;

#添加排序
#查询每个工种的工种名，员工个数，按员工个数降序
SELECT `job_title`,COUNT(*)
FROM `jobs`,`employees` e
WHERE jobs.`job_id`=e.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;

#三表连接
#查询员工名，部门名，城市
SELECT last_name,department_name,city
FROM `employees`AS e,`departments`AS d,`locations` AS l
WHERE e.`department_id`=d.`department_id`
AND l.`location_id`=d.`location_id`

#----------------------------------------------------------------
#多表等值连接的结果为多表的交集
#
#
#----------------------------------------------------------------

/*非等值连接


*/
#查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `job_grades`AS jl,`employees`AS e
WHERE `salary`<`highest_sal`AND`salary`>`lowest_sal`


/*自连接
在一张表内查询连接
要给一张表取多个别名以区分
*/
#查询员工名和上级的名称
SELECT e.`last_name`AS 员工,m.`last_name`AS 上级
FROM employees AS e ,employees AS m
WHERE e.`manager_id`=m.`employee_id`



#--------进阶6 连接查询 --sql 99语法-----------#
#--------进阶6 连接查询 --sql 99语法-----------#
#--------进阶6 连接查询 --sql 99语法-----------#
#--------进阶6 连接查询 --sql 99语法-----------#
/*
支持的连接类型
内连接 inner  等值连接 非等值连接 自连接
外连接（
   左外 left，
   右外 right，
   全外 full） 
交叉连接 cross

select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by]
[having]
[order by]

*/
/*
inner
#----------------1、等值连接------------------------
*/

#查询员工名，部门名
SELECT `last_name`,`department_name`
FROM `departments`AS d 
      INNER JOIN `employees`AS e
ON d.`department_id`=e.`department_id`

#添加筛选
#查询名字中包含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的城市名名和部门个数， (分组+筛选)
/*
先查询每个城市的部门个数，
再根据结果筛选出部门个数大于3的城市，
所以用having而不是where
*/
SELECT `city`,COUNT(*)
FROM `locations` l INNER JOIN `departments` d
ON l.`location_id`=d.`location_id`
#where count(*)>3
GROUP BY `city`
HAVING COUNT(*)>3

#4.查询哪个部门的部门员工个数>3的部门名和员工个数，
#  并按个数降序(排序)

SELECT `department_name`,COUNT(*)
FROM `employees` e INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序

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 j.`job_id`=e.`job_id`
ORDER BY `department_name` DESC;

/*
inner
#----------------2、非等值连接----------------
*/
#查询工资级别
SELECT 
  `grade_level`,
  `salary` 
FROM
  `employees` e 
  INNER JOIN `job_grades` j 
    ON e.`salary` BETWEEN j.`lowest_sal` 
    AND j.`highest_sal` 
    
#查询工资级别个数》20的个数，并按工资级别进行降序  

SELECT 
  `grade_level`,
  COUNT(*)
FROM
  `job_grades` j 
  INNER JOIN `employees` e 
    ON e.`salary` BETWEEN j.`lowest_sal` 
    AND j.`highest_sal` 
GROUP BY `grade_level` 
HAVING COUNT(*) >20
ORDER BY `grade_level` DESC ;  
    
/*
inner
#----------------3、自连接----------------
*/

#查询员工名和他的上级的名字
SELECT 
  e.last_name,
  m.last_name 
FROM
  `employees` AS e 
  INNER JOIN `employees` AS m 
    ON e.`manager_id` = m.`employee_id`