mySQL学习记录-DQL进阶5、6
#二、分组函数
/*
功能：用做统计使用，又称聚合函数或统计函数或组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、是否忽略NULL值
以上所有的分组函数都忽略NULL值
3、可以和DISTINCT搭配，去重
4、COUNT函数的单独介绍
5、和分组函数一同查询的字段要求是group by后的字段
*/
#1、简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;#非空的值有几个
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数 FROM employees;
#2、参数支持哪些类型
#SUM、AVG 数值型
SELECT SUM(last_name),AVG(last_name) FROM employees;#字符型虽然不报错，但是无意义
#MAX、MIN 按字母排序、日期排序
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
#COUNT 任何类型都可以，用于判断非空字段的个数
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3、忽略NULL值
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;
#SUM肯定忽略了NULL值,因为NULL值+XX，还是NULL
#下面验证AVG是否忽略了NULL值
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35 FROM employees;
#验证结果说明AVG和SUM一样，都忽略了NULL值
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
#4、和DISTINCT搭配
SELECT SUM(DISTINCT salary) AS 去除重复后再求和的值,SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#5、COUNT函数的详细介绍(重载)
SELECT COUNT(salary) FROM employees;
#统计行数，只要同一行里面有一个不为NULL，则会被统计上，用的比较多
SELECT COUNT(*) FROM employees;
#写什么都可以统计行数
SELECT COUNT(1) FROM employees;
SELECT COUNT(2) FROM employees;
SELECT COUNT('马牛逼') FROM employees;
#效率：
#在以前用MYISAM存储引擎的情况下，COUNT(*)的效率最高
#现在普遍是INNODB存储引擎，COUNT(*)和COUNT(1)的效率差不多
#6、和分组函数一同查询的字段有限制
#没报错但逻辑错误
SELECT AVG(salary),employee_id FROM employees;
#测试
#查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) AS 最大值,MIN(salary) AS 最小值,AVG(salary) AS 平均值,SUM(salary) AS 总和 FROM employees;
SELECT MAX(salary) AS 最大值,MIN(salary) AS 最小值,TRUNCATE(AVG(salary),2) AS 平均值,SUM(salary) AS 总和 FROM employees;
#查询员工表中的最大入职日期和最小入职日期的相差天数(取名为DIFFRENCE)
#需要用到DATEDIFF函数
SELECT MAX(hiredate),MIN(hiredate),DATEDIFF(MAX(hiredate),MIN(hiredate)) AS 'DIFFRENCE' FROM employees;
#查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;
#进阶5：分组查询
/*
语法：
SELECT 分组函数,列(要求出现在GROUP BY的后面)
FROM 表名
(WHERE)
GROUP BY 分组的列表
(ORDER BY 子句)
注意：
查询列表比较特殊，要求是分组函数和GROUP BY后面的字段
特点：
1、分组查询的筛选条件分类
数据源 位置 关键字
分组前筛选 原始表 GROUP BY子句前 WHERE
分组后筛选 分组后的结果集 GROUP BY子句后 HAVING
①、分组函数做条件肯定是放在HAVING中
②、能用分组前筛选的，优先考虑使用分组前筛选
*/
#引入：查询每个部门的平均工资
SELECT AVG(salary) FROM employees;#这样只是所有工资的AVG
#用GROUP BY进行分组
SELECT AVG(salary),department_id FROM employees GROUP BY department_id;
#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#案例2：查询每个领导下有奖金的员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#添加复杂(分组后的筛选，HAVING)的筛选条件
#案例1：查询哪个部门的员工个数>2
SELECT COUNT(*)>2,department_id FROM employees GROUP BY department_id;#用0、1表示
#或者：
SELECT CASE
WHEN COUNT(*)>2 THEN '员工个数大于2'
ELSE '员工个数没有大于2'
END AS '员工个数',
department_id FROM employees GROUP BY department_id;
#或者：
#这样会报错
SELECT COUNT(*),department_id FROM employees WHERE COUNT(*)>2 GROUP BY department_id;
#在使用分组函数时，WHERE的判断时优先于GROUP BY，但是这里的COUNT(*)使用的却是GROUP BY后的结果，所以会报错
#这种情况下可以使用HAVING
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
#案例2：查询每个工种有奖金的员工的最高工资，且最高工资>12000的工种编号和其最高工资
#这题比较特殊，有无奖金可以先在分组前筛选，即WHERE，而分组后最高工资>12000放在HAVING后
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000 ;
#案例3：查询每个领导编号>102的领导手下的最低工资且最低工资>5000的，对应领导编号和对应最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
原因：没有有效的连接条件；解决：添加有效连接
分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准（推荐）：所有的内连接+外连接（左、右）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
*/
SELECT * FROM beauty;#12行
SELECT * FROM boys;#4行
#出现问题
SELECT `name`,boyName FROM beauty,boys;#48行
#应使用连接查询
#笛卡尔集的错误现象
SELECT `name`,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;
#一、sql92标准
/*
①、多表等值连接的结果为多表的交集部分
②、n表连接，至少需要n-1个连接部分
③、多表的顺序没有要求
④、一般需要为表起别名
⑤、可以搭配前面介绍的所有子句
*/
#1、等值连接
#案例1：查询女名和对应男名
SELECT `name`,boyName FROM beauty,boys
WHERE beauty.`boyfriend_id`=boys.`id`;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2、为表起别名
#查询员工名、工种号、工种名
SELECT last_name,employees.job_id,job_title FROM employees,jobs
WHERE employees.`job_id`=jobs.`job_id`;#臃肿
#可改为
SELECT last_name,e.job_id,job_title FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;
SELECT last_name,employees.job_id,job_title FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;#如果为表起了别名，不能用原来的表名去限定
#3、可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name FROM employees,departments
WHERE employees.`commission_pct` IS NOT NULL AND employees.`department_id`=departments.`department_id`
#案例2：查询城市名中第二个字母为O的城市，对应的部门名和城市名
SELECT department_name,city FROM departments d,locations l
WHERE d.location_id=l.location_id AND l.city LIKE '_O%';
#4、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(department_id) 个数,city,d.location_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY d.location_id;
#案例2：查询部门内有拿奖金的员工所在的部门名和对应的领导编号和该部门的最低工资
#这题估计是翻译过来的，这是原题干：
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
#有点为了做题而做题了，况且departments表中是一个部门一个领导
#而employees表中却是一个部门可能多个领导
SELECT commission_pct,department_name,employees.manager_id,MIN(salary)
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`
AND commission_pct IS NOT NULL
GROUP BY employees.`department_id`;
#5、加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*) FROM jobs,employees
WHERE jobs.`job_id`=employees.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#6、三表连接
#案例：查询员工名，部门名和所在的城市
SELECT last_name,department_name,city
FROM employees,departments,locations
WHERE employees.`department_id`=departments.`department_id`
AND departments.`location_id`=locations.`location_id`;
#2、非等值连接
#使用下面这段代码，刷新后加载工资等级（注意不要复制到注释
/*CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);*/
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level FROM employees,job_grades
WHERE employees.`salary` BETWEEN job_grades.`lowest_sal` AND job_grades.`highest_sal`;
#只看A级
SELECT salary,grade_level FROM employees,job_grades
WHERE employees.`salary` BETWEEN job_grades.`lowest_sal` AND job_grades.`highest_sal` AND grade_level='A';
#3、自连接
#表格起别名的好处：可以用来自连接
#案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name 员工名,e.manager_id,m.last_name 领导名
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#或者
SELECT e.employee_id,e.last_name 员工名,m.employee_id,m.last_name 领导名
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#测试
#显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary) FROM employees;
#查询员工表的employee_id,job_id,last_name，并且按department_id降序,salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary ASC;
#查询员工表的job_id中包含a和e的，并且a在e前面
SELECT DISTINCT(job_id) FROM employees
WHERE job_id LIKE '%a%e%';#distinct可加可不加
#已知表student，里面有id(学号)，name，gradeId(年级编号)
#已知表grade，里面有id（学年编号），name（年级名）
#已知表result，里面有id，score，studentNo（学号）
#要求查询姓名、年级名、成绩
SELECT s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.gradeId=g.id AND s.id=r.studentNo;
#显示当前日期，以及去前后空格，截取字符串的函数
SELECT DATE(NOW());
SELECT TRIM(字符 FROM'');
SELECT SUBSTR(str,pos,len);