2022.3.21学习总结

1.分组函数剩余部分学习

#分组函数
/*
功能：用作统计用，又称为聚合函数或统计函数或组函数
分类:
sum 求和、avg 平均值，max 最大值、min 最小值、count 计算个数
*/

#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;

SELECT ROUND(AVG(salary),2)
FROM employees;

#2.参数支持哪些类型

#SUM.AVG适用数值型，NULL不参与运算 
#MIX.MIN使用数值型，字符型，NULL不参与比较
#COUNT适用任何型，NULL不计数

#3.搭配distinct

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

#4.count函数详细
SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;  #统计行数

SELECT COUNT(1) FROM employees;  #统计行数

/*效率:MYISAN存储引擎下，count(*)的效率高
     INNODB存储引擎下，COUNT(*)和count(1)的效率差不多
*/

#5.和分组函数一同查询的字段有限制，需要是group by字段
SELECT AVG(salary),employee_id FROM employees; #无意义

#练习
#1.查询公司员工工资的最大值，最小值，平均值，求和
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary)
FROM employees;

#2.查询员工表中最大入职时间和最小入职时间的相差天数(datediff)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))
FROM employees;

SELECT DATEDIFF('2022-4-07','1994-11-20')

2.分组查询练习

#分组查询
/*
   select 分组函数(sum,avg,min,max,count)，需要分组的列
   from 表
  【where 筛选条件】   where一定要放在group的前面,分组前筛选
   group by 需要分组的列
  【having 筛选条件】  having放在group by后面，分组后筛选
  【order by 子句】	
  注意：
  查询列表必须特殊，要求是分组函数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(department_name),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;

#添加复杂的筛选条件,分组后筛选
#添加1，查询哪个部门的员工个数大于2

SELECT COUNT(employee_id),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id)>2;

#案例2，查询每个工种工资大于12000的并有奖金的员工的最高工资和工资编号
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;

#按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组员工个数，筛选出大于5的有哪些
SELECT COUNT(*) AS 员工个数,LENGTH(CONCAT(first_name,last_name)) AS 姓名长度
FROM employees
GROUP BY 姓名长度
HAVING 员工个数>5;

#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

#添加排序
#案例：查询每个部门每个工种的员工的平均工资，并且按平均工贸高低排序
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

#练习
#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

#2.查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary)
FROM employees;

#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#4.查询所有部门的编号，员工数量和平均工资，并按平均工资降序
SELECT COUNT(*),AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

#5.选择具有各个job_id的员工人数
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;

3.连接查询-内连接-等值连接练习

#连接查询
/*
含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1有N行，表2有M行，结果=m*n行
发生原因：没有有效的连接条件
解决原因：添加有效的连接条件

分类：
      按年代分类：sql92标准（仅支持内连接）.sql99标准（推荐，支持内连接+外连接（左右）+交叉连接）
      按功能分类：内连接，外连接，交叉连接
                  内连接：等值连接.非等值连接.自连接
                  外连接：左外连接.右外连接.全外连接
                

*/
SELECT * FROM beauty;

SELECT * FROM boys;

#笛卡尔乘积
SELECT NAME,boyname
FROM beauty,boys;

#解决
SELECT NAME,boyname
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

#一.sql92标准
#1.等值连接
/*
特点：
1.结果为交集部分
2.n表连接需要n-1个连接条件
3.多表顺序没有要求
4.需要为表其别名
5.可以搭配所有查询子句
*/
#案例1：查询女生名对应男生名
SELECT NAME,boyname
FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

#案例2：查询员工名和对应的部门名
SELECT CONCAT(first_name,last_name) AS 员工名,department_name AS 部门名
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2.有重复项的前面加表.限定-------为表起别名
/*
提高语句简洁渡，区分多个重名的字段
注意：因为先走from，所以为表起了别名就不能再使用原名了
*/
#查询员工名，工种号，工种名

SELECT CONCAT(first_name,last_name) AS 员工名,job_title 工种号,e.`job_id` AS 工种号
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

#3.两个表的顺序可以调换
SELECT CONCAT(first_name,last_name) AS 员工名,job_title 工种号,e.`job_id` AS 工种号
FROM jobs AS j,employees AS e
WHERE e.`job_id`=j.`job_id`;

#4.可以加筛选,筛选条件与等值连接的值用and连接，不用','
#案例1：查询有奖金的员工名，部门名
SELECT CONCAT(first_name,last_name) AS 员工名,department_name AS 部门名,commission_pct
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND e.commission_pct IS NOT NULL;

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

#5.可以加分组

#案例1.查询每个城市的部门个数
SELECT COUNT(*),city
FROM locations AS l,departments AS d
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

#案例2.查询有奖金员工的每个部门的部门名和领导编号以及该部门的最低工资
SELECT MIN(salary),department_name,d.manager_id
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL
GROUP BY department_name;

#6.可以加排序

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

#7.可以三表连接

#案例.查询员工名，部门名，所在城市,城市带S，按员工名排序
SELECT CONCAT(first_name,last_name),department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`
AND city LIKE '%s%'
ORDER BY CONCAT(first_name,last_name);

4.明日学习计划，连接查询剩余内容，sql其他内容，公司项目sql内容学习