mysql学习笔记(3)——进阶查询

一、分组函数

1.功能:用于统计,又称聚合函数、组函数、统计函数
2.分类:sum 求和、avg平均值、max最大值、min最小值、count计算个数
3.特点:

a、sum、avg一般用于处理数值型,max、min、count可以处理任何类型
b、以上分组函数都忽略null值
c、可与 DISTINCT 搭配去重
d、count函数的单独介绍:一般用count(*)计算行数
e、和分组函数一同查询的字段有限制(一同查询的字段要求是group by后的字段,其他的都不行)

SELECT * FROM mylist m 
SELECT SUM(salary) FROM mylist  
SELECT avg(salary) FROM mylist 
SELECT min(salary) FROM mylist 
SELECT MAX(salary) FROM mylist 
SELECT count(salary) FROM mylist 

4、可与 DISTINCT d搭配去重

SELECT  SUM(DISTINCT salary)去重工资, SUM(salary)工资 FROM mylist  
SELECT COUNT(DISTINCT id) ,count(id)  FROM mylist

5、count函数的单独介绍

SELECT count(salary) FROM mylist m  //统计该字段非空值的个数
SELECT count(*) FROM mylist m  //统计行数
SELECT count(1) FROM mylist m   //统计结果集的行数

效率:MYISAM引擎下,count()效率更高
INNODB引擎下,count(
)和count(1)的效率差不多,比count(字段)高一些

6.和分组函数一同查询的字段有限制(一一同查询的字段要求是group by后的字段,其他的都不行)

 错误写法: SELECT AVG(salary),id from mylist

题目:
1.查询公司员工工资最大值,最小值,平均数,总和

SELECT max(salary) 最大值 ,min(salary) 最小值,avg(salary) 平均数,sum(salary) 总和 
FROM mylist 

2.查询员工表中最大入职时间和最小入职时间相差天数

SELECT datediff(max(entrytime),min(entrytime)) 相差天数 FROM mylist m 

3.查询工资为10000的员工个数

SELECT COUNT(*) FROM mylist
WHERE salary=10000

二、分组查询

一、语法:
SELECT  分组函数,列(要求出现在GROUP BY 后的字段)
FROM 表
WHERE 筛选条件
GROUP BY 分组的列表
HAVING 分组后的筛选
ORDER BY 子句

注意:查询的表必须特殊,要求是分组函数和group BY 后面出现的字段

二、特点:

a.分组查询中筛选条件为两类

分组前的筛选  原始表        GROUP BY 子句的前面 where
分组后的筛选  分组后的结果   GROUP BY 子句的后面 HAVING

b.分组函数做条件肯定是放在having子句中
c.能用分组前筛选的,就考虑使用分组前的筛选
d、GROUP BY 子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或者函数(用的较少),
e、可以添加排序(放在整个分组查询的最后)

三、题目:

1.求每个工种(job_id)的最高工资

SELECT max(salary),job_id FROM mylist
GROUP BY job_id

2.求每个位置的部门个数

SELECT COUNT(department_id),location_id  FROM departments 
GROUP BY location_id 

3.添加筛选条件:

(一)添加分组前的筛选条件

a.查询邮箱中包含8字符的,求部门的平均工资

SELECT AVG(salary),department_id FROM mylist 
WHERE email like'%8%'
GROUP BY department_id

b.查询有奖金的每个领导下的员工的最高工资

SELECT max(salary) ,manage_id FROM mylist 
WHERE  amount NOT IS NULL
GROUP BY manage_id
(二)添加分组后的筛选条件

a.查询哪个部门员工个数>2

SELECT count(*) ,department_id FROM mylist 
GROUP BY department_id
HAVING count(*)>2

b.查询每个工种有奖金的员工最高工资>12000的工种编号和最高工资
SELECT max(salary),job_id FROM mylist
WHERE amount IS NOT NULL
GROUP BY job_id
HAVING max(salary>12000)

1.按函数/函数分组:

题目:按员工姓名长度分组,查询每组员工个数,筛选员工个数>5的有哪些

SELECT count(*),LENGTH (name) FROM mylist 
GROUP BY LENGTH (name)
HAVING count(*)>5
2.按多个字段分组,添加排序

题目:查询每个部门,每个工种的平均工资(工资从高到低显示)

SELECT avg(salary),department_id,job_id FROM mylist 
GROUP BY department_id,job_id
ORDER BY avg(salary) DESC 

练习题目一:
1.查询各个job_id的员工工资的最大值,最小值,平均值,总和,并按照job_id升序

SELECT max(salary),min(salary),avg(salary),sum(salary),job_id  FROM mylist
GROUP BY job_id 
ORDER BY job_id ASC 

2.查询员工最高工资,最低工资的差距

SELECT max(salary)-min(salary) DATEDIFF FROM mylist 

3.查询各管理者手下员工的最低工资(不能低于6000),没有管理者的员工不计算在内

SELECT min(salary),manage_id FROM mylist 
WHERE manage_id  IS NOT null
GROUP BY manage_id 
HAVING min(salary)>6000

4.查询所有部门的编号,员工数量和平均工资,按照平均工资降序

SELECT department_id,count(*),avg(salary)
FROM mylist 
GROUP BY department_id
ORDER BY avg(salary)DESC

5.选择具有各个job_id的员工人数

SELECT count(*),job_id FROM mylist 
GROUP BY job_id 

练习题目二:
1.显示所有员工姓名,部门号和部门名称

  SELECT last_name,d.department_id,d.department_name 
  FROM employees e ,departments d  
  WHERE e.department_id =d.department_id 

2.查询90号部门员工的job_id和90号部门的location_id

  SELECT e.job_id,d.location_id,e.department_id
  FROM employees e ,departments d
  WHERE e.department_id  = d.department_id
  and e.department_id  =90

3.选择所有有奖金的员工的last_name,department_name,location_id,city

  SELECT e.last_name,d.department_name,d.location_id,l.city
  FROM employees e,departments d ,locations l
  WHERE e.department_id=d.department_id 
  AND d.location_id=l.location_id
  AND  e.commission_pct IS NOT NULL

4.选择City 在Toronto工作的员工的last_name,job_id,department_id,department_name

   SELECT e.last_name,e.job_id,d.department_id,d.department_name
   FROM employees e ,departments d ,locations l 
   WHERE e.department_id=d.department_id
   AND l.location_id=d.location_id
   AND l.city ='Toronto'

5.查询每个工种,每个部门的部门名和最低工资

   SELECT j.job_title,d.department_name,MIN(e.salary)
   FROM employees e,jobs j ,departments d  
   WHERE e.job_id =j.job_id 
   AND e.department_id =d.department_id     
   GROUP BY j.job_title,d.department_name

6.查询每个国家下的部门个数大于2的国家编号

   SELECT country_id,count(*) 部门个数
   FROM locations l,departments d
   WHERE l.location_id= d.location_id
   GROUP BY l.country_id
   HAVING count(*)>2

7.选择指定员工姓名,员工号,以及其他管理者的姓名,员工号,结果类似下面的格式

   SELECT e.last_name,e.employee_id 'Mgr',m.last_name 'manager',m.employee_id 'Mgr'
   FROM employees e,employees m
   WHERE m.employee_id=e.manager_id
   AND e.last_name='kochhar'
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容