MySQL基础篇（运算符、排序分页、多表查询、函数）-3

-- 1、IF(value,value1,value2)
-- 如果value的值为TRUE，返回value1， 否则返回value2
SELECT IF(1>0,'data1','data2')
-- 2、IFNULL(value1, value2)
-- 如果value1不为NULL，返回value1，否 则返回value2
SELECT IF(1>0,'data1','data2')
-- 3、CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END
-- 相当于Java的if...else if...else...
SELECT
  last_name,
  salary,
CASE
  WHEN salary >= 15000 THEN '高薪'  
  WHEN salary >= 10000 THEN '底薪' 
  ELSE '半残' 
END 'details'
FROM
  employees
-- 4、CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END
-- 相当于Java的switch...case...
SELECT
  last_name,
  salary,
CASE 
  salary >= 15000
  WHEN salary >= 15000 THEN '高薪' 
  WHEN salary >= 10000 THEN '底薪' 
  ELSE '半残' 
END 'details'
FROM
  employees

# 1.显示系统时间(注：日期+时间)
SELECT NOW()
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT
  employee_id,
  last_name,
  salary * 1.2 'new salary'
FROM
 employees
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT
  employee_id,
  LENGTH( last_name ) last_name 
FROM
  employees 
ORDER BY
  first_name DESC;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT
  CONCAT( employee_id, last_name, salary ) OUT_PUT 
FROM
  employees
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT
  DATEDIFF(SYSDATE(),hire_date) / 365 worked_years,
  DATEDIFF(SYSDATE(),hire_date) worked_days
FROM
  employees
ORDER BY 
worked_years DESC
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id
-- 为80 或 90 或110, commission_pct不为空
SELECT
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
  DATE_FORMAT(hire_date,'%Y') >= '1997'
AND
  department_id IN (80,90,110)
AND
  commission_pct IS NOT NULL
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT
  last_name,
  hire_date
FROM
  employees
WHERE
  DATEDIFF(NOW(),hire_date) > 10000
# 8.做一个查询，产生下面的结果
-- -- <last_name> earns `<salary>` monthly but wants <salary*3>
-- -- Dream Salary
-- -- King earns 24000 monthly but wants 72000
SELECT
  CONCAT(
    last_name,
    ' earns ',
    TRUNCATE ( salary, 0 ),
    ' monthly but he wants ',
  TRUNCATE ( salary * 3, 0 )) 'Dream Salary'
FROM
  employees
-- 9.使用CASE-WHEN，按照下面的条件：
-- -- job       grade
-- -- AD_PRES   A
-- -- ST_MAN    B
-- -- IT_PROG   C
-- -- SA_REP    D
-- -- ST_CLERK  E
-- -- 产生下面的结果
-- -- Last_name   Job_id    Grade
-- -- king        AD_PRES   A
SELECT
  last_name Last_name,
  job_id Job_id,
CASE
    job_id 
    WHEN 'AD_PRES' THEN
    'A' 
    WHEN 'ST_MAN' THEN
    'B' 
    WHEN 'IT_PROG' THEN
    'C' 
    WHEN 'SA_REP' THEN
    'D' 
    WHEN 'ST_CLERK' THEN
    'E' ELSE 'F' 
  END Grade 
FROM
  employees

-- 1、可以对数值型数据使用AVG 和 SUM 函数。
SELECT 
  AVG(salary),
  MAX(salary),
  MIN(salary),
  SUM(salary)
FROM
  employees
6461.682243 24000.00  2100.00 691400.00 
-- 2、COUNT(*)返回表中记录总数，适用于任意数据类型。  
SELECT
  COUNT(*)
FROM
  employees;
107

单个部门进行分组：
SELECT
  department_id,
  AVG(salary),
  SUM(salary)
FROM
  employees
多个部门进行分组：
SELECT
  department_id,
  job_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id

SELECT
  department_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id

SELECT
  department_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id WITH ROLLUP

SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

#1.where子句可否使用组函数进行过滤?
不可以，where后面一般跟非组函数条件进行过滤
HAVING后面一般跟组函数进行过滤
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary)
FROM
  employees
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT
  job_id,
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary)
FROM
  employees
GROUP BY
  job_id
#4.选择具有各个job_id的员工人数
-- COUNT()这里的参数可填*,1,employee_id
-- 但是要注意如果填字段名，count不计算空值
SELECT
  job_id,
  COUNT(*)
FROM
  employees
GROUP BY
  job_id
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT
  MAX( salary ) - MIN( salary ) 'DIFFERENCE' 
FROM
  employees
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
  manager_id,
  MIN( salary ) 
FROM
  employees 
WHERE
--  salary > 6000 题中说到最低工资，所以应该用聚合函数MIN来求得最低工资然后进行判断
  manager_id IS NOT NULL 
GROUP BY
  manager_id
HAVING
  MIN(salary) >= 6000
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT
  d.department_name,
  d.location_id,
  COUNT(employee_id),
  AVG( salary ) '平均工资'
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,location_id 
ORDER BY
  '平均工资' DESC
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
-- 这里的每个其实也可看成所有！
-- 最后结果出现很多空值是因为部门有很多是没有人的，那么就会出现job_id和salary为空的情况
SELECT
  d.department_name,
  e.job_id,
  MIN( salary ) 
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,
  job_id

SELECT
  d.department_name,
  d.location_id,
  COUNT(employee_id),
  AVG( salary ) '平均工资'
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,location_id 
ORDER BY
  '平均工资' DESC