mysql分组语法标准_（九）mysql语法-分组函数和多表连接查询

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;--这两种写法，查询出来的效果是一样的