mysql语法------9----分组函数

count:函数只能统计不为空的个数,为空的个数是不能够进行统计的
所有的分组函数都是忽略null值的
和分组函数查询的字段一般情况下是group by后面的字段
#datediff查询两个日期之间相差的天数
select DATEDIFF(‘2019-02-02‘,‘2019-02-01‘);--结果:1
#分组函数
语法:select 分组函数,列(要求出现再group by的后面)
  from 表
  【where筛选条件】
  group by 分组的列表
  【order by 子句】
注意: 查询列表必须特殊,要求是分组函数和group by 后出现的字段
#查询每个部门的平均工资
use myemployees
select avg(salary),department_id from employees group by department_id;

#查询每个工种的最高工资
select max(salary),job_id from employees group by job_id;

#查询每个位置上的部门个数
select count(*),location_id from departments group by location_id;
select count(1),location_id from departments group by location_id;--这两种写法查询出来的结果是一样的
#查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null group by manager_id;
#查询哪个部门的员工个数大于2
#第一步:查询每个部门的员工个数
select count(*),department_id from employees GROUP BY department_id;
#第二步:查询哪个部门的员工个数是大于2的 添加分组后的筛选
select count(*),department_id from employees
GROUP BY department_id
having count(*)>2;

#案例2:查询每个工种有奖金的员工的最高工资》12000的工种编号和最高工资
#第一步:查询每个工种有奖金的员工的最高工资
select max(salary),job_id from employees
where commission_pct is not null
group by job_id
#第二步:查询最高工资》12000
select max(salary),job_id from employees
where commission_pct is not null
group by job_id
having max(salary)>12000

#查询每个领导手下的员工的最低工资大于5000 的领导编号,和最低工资,并且领导编号要大于102
select min(salary),manager_id
from employees
where manager_id>102
GROUP by manager_id
having min(salary)>5000;
#大招:分组函数做条件一定是放在having子句中

#按照表达式或者函数进行分组
#案例:按员工的姓名的长度分组,查询每一组的员工个数,然后在筛选员工个数》5的有哪些
#第一步 查询每个长度的员工个数
select count(1),LENGTH(last_name)
from employees
GROUP BY LENGTH(last_name);
#第二步 添加筛选条件
select count(1),LENGTH(last_name)
from employees
GROUP BY LENGTH(last_name)
having count(1)>5
#别名同样也是支持的
select count(1) c,LENGTH(last_name) d
from employees
GROUP BY d
having c>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 job_id,department_id;--group by后面的顺序是能够颠倒的,最终查询出来的结果是一样的
#案例:查询每个部门每个工种的员工的平均工资,按照平均工资诱导到底进行显示
select avg(salary),department_id,job_id
from employees
where department_id is not null
GROUP BY department_id,job_id
having avg(salary)>=24000 --这里随便的演示了一下having子句的放置位置
order by avg(salary) desc
use girls
show TABLES
select * from beauty

#连接查询
#又称作多表查询,当查询的字段来自于多个表的时候,就会使用到多表连接查询。
#查询每个女神的男朋友 这样拆线呢会产生笛卡尔集的错误情况
select name,boyname from beauty,boys;--这种写法是错误的
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id;

#sql语句的分类
按照年代进行分类:
sql92标准 仅仅支持内连接
sql99标准 推荐使用 支持内连接+外连接(左外和右外)+交叉连接

按照功能进行分类:
内连接
  等值连接
  非等值连接
  自然连接

外连接
  左外连接
  右外连接
  全外连接
  交叉连接
#sql92标准
#等值连接
#查询女神名和对应的男神名
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id;

#查询员工名和对应的部门名 有null的那个部门没有进行显示 在查询的时候我们是可以给表起别名的,有过某个字段显示的时候有歧义的话 对应的视频是71
#如果为表起了别名,则查询到的字段就不能使用原来的表名进行限定了
SELECT last_name,department_name FROM employees,departments where employees.department_id=departments.department_id;


#案例:查询有奖金的员工名和部门名
select a.last_name,b.department_name,a.commission_pct from employees a,departments b where a.department_id=b.department_id
and a.commission_pct is not null

#查询每个城市的部门个数
select count(*),b.city from departments a,locations b
where a.location_id=b.location_id
GROUP BY b.city;

#实现三表连接查询
#案例:查询员工名、部门名、和所在的城市
select a.last_name,b.department_name,c.city
from employees a,departments b,locations c
where a.department_id=b.department_id and b.location_id=c.location_id
and c.city LIKE ‘%s%‘;

#工资登记表的sql脚本

/*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);*/

SELECT * from job_grades

#非等值连接查询
#案例1:查询员工的工资和工资级别
select a.salary,b.grade_level from employees a,job_grades b
where a.salary BETWEEN b.lowest_sal and b.highest_sal;

#自连接;简单的理解就是自己连接自己进行查询
#查询员工名和上级的名称 这种情况说明是这张表中有一些比较特殊的字段,一共查询了这张表两遍 一张表当做两张表来使用,有些情况下,甚至当作更多张表来使用,分别当作了员工表和领导表
SELECT a.employee_id,a.last_name,b.manager_id,b.last_name FROM employees a,employees b where a.employee_id=b.manager_id;
SELECT a.employee_id,a.last_name,b.employee_id,b.last_name FROM employees a,employees b where a.employee_id=b.manager_id;--这两种写法,查询出来的效果是一样的

相关推荐