oracle分组函数

--这是单行注释

/*
这是多行注释
*/

--使用to_char()函数把日期类型转换成字符类型，12小时制
select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual 

--使用to_char()函数把日期类型转换成字符类型，24小时制
select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual 

/*
重点：分组函数
*/

--分组函数就是多行函数

/*
组函数类型(注意：组函数只算非空的，空的不会算进去,也就是说组函数会忽略空值)
1.avg
2.count
3.max
4.min
5.stddev 返回expr的样本标准偏差，它可用作聚集和分析函数
6.sum
*/

SELECT AVG(emp.salary), MAX(salary), MIN(emp.salary), SUM(salary) 
FROM employees emp
--max()函数和min()函数对数据类型没有限制，既可以是数字类型，也可以是字符类型，还可以是日期类型
SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date) 
FROM employees emp
--avg()函数和sum()函数只支持数字类型
SELECT AVG(last_name), SUM(last_name), AVG(hire_date), SUM(hire_date) 
FROM employees emp

--count()函数，返回表中记录总数，适用于任何数据类型
SELECT COUNT(emp.employee_id), COUNT(last_name), COUNT(emp.hire_date)
FROM employees emp
--
SELECT COUNT(1), COUNT(2), COUNT(*), COUNT('test'), COUNT('2018-09-12 10:27:36'), COUNT(666), COUNT('888')
FROM employees
--
SELECT AVG(salary), SUM(salary) / COUNT(salary), COUNT(salary), SUM(salary)
FROM employees
--
SELECT * FROM employees emp WHERE emp.commission_pct IS NOT NULL 

--COUNT()函数只算非空的，空的不会算进去
SELECT COUNT(commission_pct), COUNT(emp.commission_pct) FROM employees emp
--
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct), SUM(commission_pct) / 107, COUNT(commission_pct), SUM(commission_pct)
FROM employees
--
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct), SUM(commission_pct) / COUNT(nvl(commission_pct,0)), COUNT(commission_pct), COUNT(nvl(commission_pct,0)), SUM(commission_pct)
FROM employees
--
SELECT COUNT(commission_pct), COUNT(nvl(commission_pct,0))
FROM employees

--DISTINCT关键字是去重的意思
SELECT COUNT(emp.department_id), COUNT(DISTINCT emp.department_id)
FROM employees emp

/*
分组数据
*/
--求出employees表中各个部门的平均工资
SELECT department_id AS "部门编号", AVG(salary) AS "平均工资"
FROM employees emp
GROUP BY emp.department_id

--注意：where要和from挨着，他们是亲戚
SELECT department_id AS "部门编号", AVG(salary) AS "平均工资"
FROM employees emp
WHERE emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id

/*
多层分组(注意：跟在SELECT后面的非组函数列名，这些列名必须出现在GROUP BY后面，如下面的语句
SELECT后面跟了department_id列和job_id列，所以GROUP BY后面也必须跟department_id列和job_id列，否
则会出错
如果GROUP BY后面跟了某些列，这些列不一定要出现在SELECT后面
)
*/
SELECT department_id AS "部门编号", emp.job_id AS "工种", AVG(salary) AS "平均工资"
FROM employees emp
GROUP BY emp.department_id, emp.job_id

--
SELECT last_name,department_id
FROM employees WHERE LOWER(last_name) = 'king'

/*
注意：如果在过滤条件中使用了组函数，那就不能使用where关键字，只能使用having关键字
注意：我们说where关键字要和from挨着，但是having关键字没有这样的要求，我们也可以
把having关键字放到GROUP BY后面
*/
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 6000 --这里过滤数据时，使用了组函数，所以只能用having关键字，不能使用where关键字
GROUP BY department_id

--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
where emp.salary > 6000 --这里过滤数据时，没有使用组函数，所以使用where关键字
GROUP BY department_id

/*
把having关键字放到GROUP BY后面也是OK的，语法不会报错，至于HAVING放在FROM后面还是放在GROUP BY后面，看大
家的习惯，根据个人喜好，自己选择
*/
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING AVG(emp.salary) > 6000

--顺便排个序
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING AVG(emp.salary) > 6000
ORDER BY emp.department_id ASC 
--
SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING MAX(emp.salary) > 10000
ORDER BY emp.department_id ASC
--
SELECT COUNT(emp.department_id), (AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
--
SELECT COUNT(emp.department_id), MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
--
--嵌套组函数
SELECT MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id

--SELECT后面跟了嵌套组函数的话，那在SELECT后面就不能再跟其他列了
SELECT MAX(AVG(emp.salary)) --正确
--SELECT emp.department_id, MAX(AVG(emp.salary)) --这种写法会报错
FROM employees emp
GROUP BY emp.department_id
--
SELECT emp.department_id, MAX(emp.salary)
FROM employees emp
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY emp.department_id
--
SELECT AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 6200

--
SELECT * FROM employees emp WHERE emp.department_id IS NULL 

--Toronto这个城市的员工的平均工资
SELECT 'toronto', AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id 
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto'
--
SELECT 'toronto' AS "多伦多", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id 
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto' 
--
SELECT loc.city AS "城市", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id 
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto'
GROUP BY loc.city
--(有员工的城市)各个城市的平均工资
SELECT loc.city AS "城市", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id 
AND dep.location_id = loc.location_id
GROUP BY loc.city

--查询平均工资高于8000的部门id和它的平均工资
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 8000
GROUP BY emp.department_id
--
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id, emp.department_id
--
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id
--
SELECT dep.department_id AS "部门编号", dep.department_name AS "部门名称", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id, dep.department_name
--查询最高工资高于8000的部门id和它的平均工资
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp
HAVING MAX(emp.salary) > 8000
GROUP BY emp.department_id
--查询平均工资高于6000的job_title有哪些
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp, jobs
WHERE emp.job_id = jobs.job_id
HAVING AVG(emp.salary) > 6000
GROUP BY jobs.job_title
--方式1
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp, jobs
WHERE emp.job_id = jobs.job_id
GROUP BY jobs.job_title
HAVING AVG(emp.salary) > 6000
--方式2
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp JOIN jobs
ON emp.job_id = jobs.job_id
GROUP BY jobs.job_title
HAVING AVG(emp.salary) > 6000

--
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary) 
FROM employees

--
SELECT emp.job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary) 
FROM employees emp
GROUP BY emp.job_id
--
SELECT emp.job_id, COUNT('hello test') AS "员工人数", COUNT('*'), COUNT(*), COUNT('aaa'), COUNT('666888'), 
COUNT('2018-09-13 12:16:56'), COUNT('2018年09月13日 12:16:56'), COUNT('江西省赣州市于都县'), COUNT('%_'),
COUNT(job_id), COUNT(emp.employee_id), COUNT(emp.department_id)
FROM employees emp
GROUP BY emp.job_id
--最高工资和最低工资的差距
SELECT MAX(emp.salary), MIN(emp.salary), MAX(salary) - MIN(salary) AS "最大工资差距"
FROM employees emp
--查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
--HAVING关键字也可以跟在GROUP BY后面
SELECT emp.manager_id "管理者编号", MIN(emp.salary) 
FROM employees emp
WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
--HAVING跟在GROUP BY后面
HAVING MIN(emp.salary) >= 6000
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
WHERE emp.manager_id IS NOT NULL
--HAVING跟在from后面
HAVING MIN(emp.salary) >= 6000
GROUP BY emp.manager_id

--也可以是下面这种写法
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
--HAVING跟在from后面
HAVING MIN(emp.salary) >= 6000 AND emp.manager_id IS NOT NULL 
GROUP BY emp.manager_id
 
--也可以是下面这种写法
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id 
--HAVING跟在GROUP BY后面
HAVING MIN(emp.salary) >= 6000 AND emp.manager_id IS NOT NULL

--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id 
HAVING emp.manager_id IS NOT NULL
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
HAVING emp.manager_id IS NOT NULL
GROUP BY emp.manager_id 
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
WHERE emp.manager_id IS NOT NULL
--HAVING emp.manager_id IS NOT NULL
GROUP BY emp.manager_id 
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
--WHERE emp.manager_id > 150
HAVING emp.manager_id > 150
GROUP BY emp.manager_id 
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary) 
FROM employees emp
WHERE emp.manager_id > 150
--HAVING emp.manager_id > 150
GROUP BY emp.manager_id 

--查询所有部门的名字，location_id，员工数量和工资平均值
/*
注意：此时COUNT()要写成COUNT(emp.employee_id)，不能写成count(*),
总之要写成count(emp.列名)，不能写成count(*),大家想一想，GROUP BY按照部门名称分组之
后，肯定是有部门的，有些部门下面可能没有员工，部门下面没有员工的话，count(*)会按照部门名称来计算数量，所以count(*)就
会显示成1，但是我们写成count(emp.列名)后，如果遇到部门下面没有员工的话，count(emp.列名)会按照emp表的列来计算，而此
时emp表中的所有列的值都是空，所以此时count(emp.列名)会显示0，大家都知道count()组函数不会把空算进去
*/
SELECT dep.department_name, dep.department_id, dep.location_id, 
COUNT(emp.employee_id), COUNT(emp.salary), COUNT(*),
AVG(emp.salary) 
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.department_id, dep.location_id, COUNT(*), AVG(emp.salary) 
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.department_id, dep.location_id, 
COUNT(emp.employee_id), COUNT(emp.salary), COUNT(*), 
AVG(emp.salary) 
FROM employees emp 
RIGHT OUTER JOIN departments dep
ON emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary) 
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.location_id
--
SELECT dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary) 
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
GROUP BY dep.department_name, dep.location_id
--
SELECT dep.department_id, dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary) 
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
GROUP BY dep.department_id, dep.department_name, dep.location_id
--
SELECT dep.department_id, dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary) 
FROM employees emp, departments dep, Locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
GROUP BY dep.department_id, dep.department_name, dep.location_id

--COUNT(emp.department_id)结果为0，因为department_id列的值是空，所以不会算进去，而COUNT(*)的结果不是0
SELECT COUNT(*), COUNT(emp.employee_id), COUNT(emp.department_id), COUNT('江西省赣州市于都县') 
FROM employees emp
WHERE emp.department_id IS NULL
-- 
/*
查询公司在1995-1998年之间，每年雇佣的人数和这几年之间雇佣人数的总和,结果如下
------------------------------------------
total    1995    1996      1997     1998
------------------------------------------
 12        2      3          5        2  
------------------------------------------ 
*/
--
SELECT COUNT(*) AS "total",
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1995, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1996, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1997, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1998, 1, NULL)) 
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy')
--IN (1995, 1996, '1997', '1998') --in中的数字加不加单引号都可以，数字和字符会(自动/隐式)转换
--IN ('1995', '1996', '1997', '1998')
--BETWEEN 1995 AND 1998
IN (1995, 1996, 1997, 1998)

--
SELECT COUNT('呵呵OK的') AS "total",
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1995, 'aaa', NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), '1996', '123456', NULL)), -- 加不加单引号都可以，数字和字符会(自动/隐式)转换
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1997, 666888, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1998, '哈哈', NULL)) 
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy')
--IN (1995, 1996, '1997', '1998') --in中的数字加不加单引号都可以，数字和字符会(自动/隐式)转换
--IN ('1995', '1996', '1997', '1998')
--BETWEEN 1995 AND 1998
IN (1995, 1996, 1997, 1998)

--
SELECT count(*)
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy') 
--BETWEEN 1995 AND '1998' --这里数字加不加单引号都可以，数字和字符会(自动/隐式)转换
BETWEEN 1995 AND 1998

--
SELECT substr(emp.hire_date, 1, 4)
FROM employees emp
GROUP BY substr(emp.hire_date, 1, 4)

--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
FROM employees emp
GROUP BY hire_date
--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS "年份"
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS years, count(emp.employee_id)
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
HAVING substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) BETWEEN 1995 AND 1998
ORDER BY years ASC

--

--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS years
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
HAVING substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) BETWEEN 1995 AND 1998
ORDER BY years ASC
--
SELECT COUNT(emp.employee_id)
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy'), to_char(emp.hire_date, 'yyyy-mm'), COUNT(*)
FROM employees emp
WHERE emp.salary > 6000
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND emp.hire_date IS NOT NULL
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND emp.salary > 4000 --会报错
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND MAX(emp.salary) > 4000
HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND AVG(emp.salary) > 4000
GROUP BY emp.hire_date
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy') AS mydate
FROM employees emp
--WHERE to_char(emp.hire_date, 'yyyy') = 1999
GROUP BY to_char(emp.hire_date, 'yyyy'), emp.hire_date
HAVING to_char(emp.hire_date, 'yyyy') = 1999
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy') AS mydate
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy') = 1999
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
WHERE emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
HAVING emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
GROUP BY emp.department_id
HAVING emp.department_id IN (40, 60, 80)
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
WHERE emp.commission_pct IS NOT NULL
GROUP BY emp.department_id
HAVING emp.department_id IN (40, 60, 80)
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
WHERE emp.commission_pct IS NOT NULL AND emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--HAVING emp.department_id IN (40, 60, 80)

/*

注意：where关键字和having关键字都是可以用来过滤数据
在过滤数据的时候，不能在where后面使用组函数，但是可以在having后面使用组函数

*/

--求出各部门中平均工资大于6000的部门，以及其平均工资
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000 --having后面可以使用组函数
GROUP BY emp.department_id

/*
我们说where关键字需要跟from挨着，因为where和from是亲戚，但是HAVING关键字可以不和from关键字挨着，
HAVING关键字可以放在GROUP BY关键字后面，如下：

*/
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
GROUP BY emp.department_id
HAVING AVG(emp.salary) > 6000  --HAVING关键字可以放在GROUP BY关键字后面
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id

--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
WHERE emp.department_id IN (70, 80, 90)
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
WHERE emp.salary > 8000 AND to_char(emp.hire_date, 'yyyy') > 1995
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
SELECT emp.department_id, AVG(emp.salary) 
FROM employees emp
HAVING MAX(emp.salary) > 10000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
--
SELECT emp.department_id, MAX(emp.salary) 
FROM employees emp
HAVING MAX(emp.salary) > 10000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

--嵌套组函数，组函数是可以嵌套的(求各个部门平均工资中的最大值)
SELECT MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

/*

max() min() sum() avg() count() 这几个组函数有一个特点，该特点就是不会计算空，即不会把空算进去,如果你想把空也算进
去的话，可以使用nvl()函数

*/

--这是单行注释

/*
这是多行注释
*/

--使用to_char()函数把日期类型转换成字符类型，12小时制
select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual

--使用to_char()函数把日期类型转换成字符类型，24小时制
select to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual

/*
重点：分组函数
*/

--分组函数就是多行函数

/*
组函数类型(注意：组函数只算非空的，空的不会算进去,也就是说组函数会忽略空值)
1.avg
2.count
3.max
4.min
5.stddev 返回expr的样本标准偏差，它可用作聚集和分析函数
6.sum
*/

SELECT AVG(emp.salary), MAX(salary), MIN(emp.salary), SUM(salary)
FROM employees emp
--max()函数和min()函数对数据类型没有限制，既可以是数字类型，也可以是字符类型，还可以是日期类型
SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date)
FROM employees emp
--avg()函数和sum()函数只支持数字类型
SELECT AVG(last_name), SUM(last_name), AVG(hire_date), SUM(hire_date)
FROM employees emp

--count()函数，返回表中记录总数，适用于任何数据类型
SELECT COUNT(emp.employee_id), COUNT(last_name), COUNT(emp.hire_date)
FROM employees emp
--
SELECT COUNT(1), COUNT(2), COUNT(*), COUNT('test'), COUNT('2018-09-12 10:27:36'), COUNT(666), COUNT('888')
FROM employees
--
SELECT AVG(salary), SUM(salary) / COUNT(salary), COUNT(salary), SUM(salary)
FROM employees
--
SELECT * FROM employees emp WHERE emp.commission_pct IS NOT NULL

--COUNT()函数只算非空的，空的不会算进去
SELECT COUNT(commission_pct), COUNT(emp.commission_pct) FROM employees emp
--
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct), SUM(commission_pct) / 107, COUNT(commission_pct), SUM(commission_pct)
FROM employees
--
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct), SUM(commission_pct) / COUNT(nvl(commission_pct,0)), COUNT(commission_pct), COUNT(nvl(commission_pct,0)), SUM(commission_pct)
FROM employees
--
SELECT COUNT(commission_pct), COUNT(nvl(commission_pct,0))
FROM employees

--DISTINCT关键字是去重的意思
SELECT COUNT(emp.department_id), COUNT(DISTINCT emp.department_id)
FROM employees emp

/*
分组数据
*/
--求出employees表中各个部门的平均工资
SELECT department_id AS "部门编号", AVG(salary) AS "平均工资"
FROM employees emp
GROUP BY emp.department_id

--注意：where要和from挨着，他们是亲戚
SELECT department_id AS "部门编号", AVG(salary) AS "平均工资"
FROM employees emp
WHERE emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id

/*
多层分组(注意：跟在SELECT后面的非组函数列名，这些列名必须出现在GROUP BY后面，如下面的语句
SELECT后面跟了department_id列和job_id列，所以GROUP BY后面也必须跟department_id列和job_id列，否
则会出错
如果GROUP BY后面跟了某些列，这些列不一定要出现在SELECT后面
)
*/
SELECT department_id AS "部门编号", emp.job_id AS "工种", AVG(salary) AS "平均工资"
FROM employees emp
GROUP BY emp.department_id, emp.job_id

--
SELECT last_name,department_id
FROM employees WHERE LOWER(last_name) = 'king'

/*
注意：如果在过滤条件中使用了组函数，那就不能使用where关键字，只能使用having关键字
注意：我们说where关键字要和from挨着，但是having关键字没有这样的要求，我们也可以
把having关键字放到GROUP BY后面
*/
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 6000 --这里过滤数据时，使用了组函数，所以只能用having关键字，不能使用where关键字
GROUP BY department_id

--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
where emp.salary > 6000 --这里过滤数据时，没有使用组函数，所以使用where关键字
GROUP BY department_id

/*
把having关键字放到GROUP BY后面也是OK的，语法不会报错，至于HAVING放在FROM后面还是放在GROUP BY后面，看大
家的习惯，根据个人喜好，自己选择
*/
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING AVG(emp.salary) > 6000

--顺便排个序
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING AVG(emp.salary) > 6000
ORDER BY emp.department_id ASC
--
SELECT emp.department_id, AVG(emp.salary), MAX(emp.salary)
FROM employees emp
GROUP BY department_id
HAVING MAX(emp.salary) > 10000
ORDER BY emp.department_id ASC
--
SELECT COUNT(emp.department_id), (AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
--
SELECT COUNT(emp.department_id), MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
--
--嵌套组函数
SELECT MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id

--SELECT后面跟了嵌套组函数的话，那在SELECT后面就不能再跟其他列了
SELECT MAX(AVG(emp.salary)) --正确
--SELECT emp.department_id, MAX(AVG(emp.salary)) --这种写法会报错
FROM employees emp
GROUP BY emp.department_id
--
SELECT emp.department_id, MAX(emp.salary)
FROM employees emp
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY emp.department_id
--
SELECT AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 6200

--
SELECT * FROM employees emp WHERE emp.department_id IS NULL

--Toronto这个城市的员工的平均工资
SELECT 'toronto', AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto'
--
SELECT 'toronto' AS "多伦多", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto'
--
SELECT loc.city AS "城市", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
AND LOWER(loc.city) = 'toronto'
GROUP BY loc.city
--(有员工的城市)各个城市的平均工资
SELECT loc.city AS "城市", AVG(salary)
FROM employees emp, departments dep, locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
GROUP BY loc.city

--查询平均工资高于8000的部门id和它的平均工资
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp
HAVING AVG(emp.salary) > 8000
GROUP BY emp.department_id
--
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id, emp.department_id
--
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id
--
SELECT dep.department_id AS "部门编号", dep.department_name AS "部门名称", AVG(emp.salary)
FROM employees emp,  departments dep
WHERE emp.department_id = dep.department_id
HAVING AVG(emp.salary) > 8000
GROUP BY dep.department_id, dep.department_name
--查询最高工资高于8000的部门id和它的平均工资
SELECT emp.department_id AS "部门编号", AVG(emp.salary)
FROM employees emp
HAVING MAX(emp.salary) > 8000
GROUP BY emp.department_id
--查询平均工资高于6000的job_title有哪些
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp, jobs
WHERE emp.job_id = jobs.job_id
HAVING AVG(emp.salary) > 6000
GROUP BY jobs.job_title
--方式1
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp, jobs
WHERE emp.job_id = jobs.job_id
GROUP BY jobs.job_title
HAVING AVG(emp.salary) > 6000
--方式2
SELECT jobs.job_title, AVG(emp.salary)
FROM employees emp JOIN jobs
ON emp.job_id = jobs.job_id
GROUP BY jobs.job_title
HAVING AVG(emp.salary) > 6000

--
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary)
FROM employees

--
SELECT emp.job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary)
FROM employees emp
GROUP BY emp.job_id
--
SELECT emp.job_id, COUNT('hello test') AS "员工人数", COUNT('*'), COUNT(*), COUNT('aaa'), COUNT('666888'),
COUNT('2018-09-13 12:16:56'), COUNT('2018年09月13日 12:16:56'), COUNT('江西省赣州市于都县'), COUNT('%_'),
COUNT(job_id), COUNT(emp.employee_id), COUNT(emp.department_id)
FROM employees emp
GROUP BY emp.job_id
--最高工资和最低工资的差距
SELECT MAX(emp.salary), MIN(emp.salary), MAX(salary) - MIN(salary) AS "最大工资差距"
FROM employees emp
--查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
--HAVING关键字也可以跟在GROUP BY后面
SELECT emp.manager_id "管理者编号", MIN(emp.salary)
FROM employees emp
WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
--HAVING跟在GROUP BY后面
HAVING MIN(emp.salary) >= 6000
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
WHERE emp.manager_id IS NOT NULL
--HAVING跟在from后面
HAVING MIN(emp.salary) >= 6000
GROUP BY emp.manager_id

--也可以是下面这种写法
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
--HAVING跟在from后面
HAVING MIN(emp.salary) >= 6000 AND emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
 
--也可以是下面这种写法
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
--HAVING跟在GROUP BY后面
HAVING MIN(emp.salary) >= 6000 AND emp.manager_id IS NOT NULL

--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
HAVING emp.manager_id IS NOT NULL
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
--WHERE emp.manager_id IS NOT NULL
HAVING emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
WHERE emp.manager_id IS NOT NULL
--HAVING emp.manager_id IS NOT NULL
GROUP BY emp.manager_id
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
--WHERE emp.manager_id > 150
HAVING emp.manager_id > 150
GROUP BY emp.manager_id
--
SELECT emp.manager_id AS "管理者编号", MIN(emp.salary)
FROM employees emp
WHERE emp.manager_id > 150
--HAVING emp.manager_id > 150
GROUP BY emp.manager_id

--查询所有部门的名字，location_id，员工数量和工资平均值
/*
注意：此时COUNT()要写成COUNT(emp.employee_id)，不能写成count(*),
总之要写成count(emp.列名)，不能写成count(*),大家想一想，GROUP BY按照部门名称分组之
后，肯定是有部门的，有些部门下面可能没有员工，部门下面没有员工的话，count(*)会按照部门名称来计算数量，所以count(*)就
会显示成1，但是我们写成count(emp.列名)后，如果遇到部门下面没有员工的话，count(emp.列名)会按照emp表的列来计算，而此
时emp表中的所有列的值都是空，所以此时count(emp.列名)会显示0，大家都知道count()组函数不会把空算进去
*/
SELECT dep.department_name, dep.department_id, dep.location_id,
COUNT(emp.employee_id), COUNT(emp.salary), COUNT(*),
AVG(emp.salary)
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.department_id, dep.location_id, COUNT(*), AVG(emp.salary)
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.department_id, dep.location_id,
COUNT(emp.employee_id), COUNT(emp.salary), COUNT(*),
AVG(emp.salary)
FROM employees emp
RIGHT OUTER JOIN departments dep
ON emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.department_id, dep.location_id
--
SELECT dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary)
FROM employees emp, departments dep
WHERE emp.department_id(+) = dep.department_id
GROUP BY dep.department_name, dep.location_id
--
SELECT dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary)
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
GROUP BY dep.department_name, dep.location_id
--
SELECT dep.department_id, dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary)
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
GROUP BY dep.department_id, dep.department_name, dep.location_id
--
SELECT dep.department_id, dep.department_name, dep.location_id, COUNT(*), AVG(emp.salary)
FROM employees emp, departments dep, Locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id
GROUP BY dep.department_id, dep.department_name, dep.location_id

--COUNT(emp.department_id)结果为0，因为department_id列的值是空，所以不会算进去，而COUNT(*)的结果不是0
SELECT COUNT(*), COUNT(emp.employee_id), COUNT(emp.department_id), COUNT('江西省赣州市于都县')
FROM employees emp
WHERE emp.department_id IS NULL
--
/*
查询公司在1995-1998年之间，每年雇佣的人数和这几年之间雇佣人数的总和,结果如下
------------------------------------------
total    1995    1996      1997     1998
------------------------------------------
 12        2      3          5        2  
------------------------------------------
*/
--
SELECT COUNT(*) AS "total",
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1995, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1996, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1997, 1, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1998, 1, NULL))
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy')
--IN (1995, 1996, '1997', '1998') --in中的数字加不加单引号都可以，数字和字符会(自动/隐式)转换
--IN ('1995', '1996', '1997', '1998')
--BETWEEN 1995 AND 1998
IN (1995, 1996, 1997, 1998)

--
SELECT COUNT('呵呵OK的') AS "total",
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1995, 'aaa', NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), '1996', '123456', NULL)), -- 加不加单引号都可以，数字和字符会(自动/隐式)转换
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1997, 666888, NULL)),
COUNT(decode(to_char(emp.hire_date, 'yyyy'), 1998, '哈哈', NULL))
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy')
--IN (1995, 1996, '1997', '1998') --in中的数字加不加单引号都可以，数字和字符会(自动/隐式)转换
--IN ('1995', '1996', '1997', '1998')
--BETWEEN 1995 AND 1998
IN (1995, 1996, 1997, 1998)

--
SELECT count(*)
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy')
--BETWEEN 1995 AND '1998' --这里数字加不加单引号都可以，数字和字符会(自动/隐式)转换
BETWEEN 1995 AND 1998

--
SELECT substr(emp.hire_date, 1, 4)
FROM employees emp
GROUP BY substr(emp.hire_date, 1, 4)

--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
FROM employees emp
GROUP BY hire_date
--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS "年份"
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS years, count(emp.employee_id)
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
HAVING substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) BETWEEN 1995 AND 1998
ORDER BY years ASC

--

--
SELECT substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) AS years
FROM employees emp
GROUP BY substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4)
HAVING substr(to_char(emp.hire_date, 'yyyy-mm-dd'), 1, 4) BETWEEN 1995 AND 1998
ORDER BY years ASC
--
SELECT COUNT(emp.employee_id)
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy'), to_char(emp.hire_date, 'yyyy-mm'), COUNT(*)
FROM employees emp
WHERE emp.salary > 6000
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND emp.hire_date IS NOT NULL
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND emp.salary > 4000 --会报错
--HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND MAX(emp.salary) > 4000
HAVING to_char(emp.hire_date, 'yyyy') BETWEEN 1995 AND 1998 AND AVG(emp.salary) > 4000
GROUP BY emp.hire_date
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy') AS mydate
FROM employees emp
--WHERE to_char(emp.hire_date, 'yyyy') = 1999
GROUP BY to_char(emp.hire_date, 'yyyy'), emp.hire_date
HAVING to_char(emp.hire_date, 'yyyy') = 1999
--
SELECT emp.hire_date, to_char(emp.hire_date, 'yyyy') AS mydate
FROM employees emp
WHERE to_char(emp.hire_date, 'yyyy') = 1999
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
WHERE emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
HAVING emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
GROUP BY emp.department_id
HAVING emp.department_id IN (40, 60, 80)
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
WHERE emp.commission_pct IS NOT NULL
GROUP BY emp.department_id
HAVING emp.department_id IN (40, 60, 80)
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
WHERE emp.commission_pct IS NOT NULL AND emp.department_id IN (40, 60, 80)
GROUP BY emp.department_id
--HAVING emp.department_id IN (40, 60, 80)

/*

注意：where关键字和having关键字都是可以用来过滤数据
在过滤数据的时候，不能在where后面使用组函数，但是可以在having后面使用组函数

*/

--求出各部门中平均工资大于6000的部门，以及其平均工资
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000 --having后面可以使用组函数
GROUP BY emp.department_id

/*
我们说where关键字需要跟from挨着，因为where和from是亲戚，但是HAVING关键字可以不和from关键字挨着，
HAVING关键字可以放在GROUP BY关键字后面，如下：

*/
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
GROUP BY emp.department_id
HAVING AVG(emp.salary) > 6000  --HAVING关键字可以放在GROUP BY关键字后面
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id

--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
WHERE emp.department_id IN (70, 80, 90)
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
--WHERE AVG(emp.salary) > 6000 --where后面不能使用组函数
WHERE emp.salary > 8000 AND to_char(emp.hire_date, 'yyyy') > 1995
HAVING AVG(emp.salary) > 6000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
SELECT emp.department_id, AVG(emp.salary)
FROM employees emp
HAVING MAX(emp.salary) > 10000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC
--
--
SELECT emp.department_id, MAX(emp.salary)
FROM employees emp
HAVING MAX(emp.salary) > 10000  --HAVING关键字也可以放在from关键字后面
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

--嵌套组函数，组函数是可以嵌套的(求各个部门平均工资中的最大值)
SELECT MAX(AVG(emp.salary))
FROM employees emp
GROUP BY emp.department_id
ORDER BY emp.department_id ASC

/*

max() min() sum() avg() count() 这几个组函数有一个特点，该特点就是不会计算空，即不会把空算进去,如果你想把空也算进
去的话，可以使用nvl()函数

*/