MYSQL_DQL语言的学习（2）

DQL（数据查询）语言的学习（2）

注意：以上为employees表、departments表、locations表、job_grades表、jobs表*

目录：

1. 分组函数
2. 分组查询
3. 连接查询
   3.1 sql92语法
   3.2 sql99语法

1、分组函数

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

特点：
1、 sum、avg一般用于处理数值型
2、 max、min、count可以处理任何类型
3、 以上分组函数都忽略null值
4、 可以和distinct搭配实现去重的效果
5、 一般用count(*)统计函数
6、 和分组函数一同查询的字段要求是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;

#2、和distinct搭配

select sum(distinct salary),sum(salary) from employees;
select count(distinct salary),count(salary) from employees;

#3、count函数的详细介绍

select count(salary) from employees;
select count(*) from employees;
          等价与
select count(1) from employess;

#练习

案例：查询公司员工工资的最大值，最小值，平均值，总和

select max(salary),min(salary),avg(salary),sum(salary) from employees;

案例：查询员工表中的最大入职时间和最小入职时间的相差天使（diffrence）

select datediff(max(hire_date),min(hire_date)) difference from employees;

案例：查询部门编号为90的员工个数

select count(*) from employees where department_id=90;

2、分组查询

语法：
select 分组函数，列（要求出现在group by的后面）
from 表
where 筛选条件
group by 分组的列表
order by 子句

注意：
查询列表必须特殊，要求是分组函数和group by 后出现的字段
特点：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
技巧：
（1） 分组函数做条件肯定是放在having子句中
（2） 能用分组前筛选的，就优先考虑使用分组前筛选
2、group by子句支持单个字段分组，多个字段分组（多个字段之间使用逗号隔开没有顺序要求，表达式或函数用的较少）
3、也可以添加排序（排序放在整个分组查询的最后）

#简单的分组查询

案例：查询每个工种的最高工资

select max(salary) job_id 
from employees 
group by job_id;

案例：查询每个位置上的部门个数

select count(*),location_id 
from departments 
group by location_id;

#添加分组前筛选条件

案例：查询邮箱中包含e字符的，每个部门的平均工资

select avg（salary）,department_id 
from employess 
where email like '%a%' 
group by department_id;

案例：查询有奖金的每个领导手下员工的最高工资

select max(salary),manager_id  
from employees
where commission_pct is not null 
group by manger_id;

#添加复杂的分组后筛选条件

案例：查询哪个部门的员工个数大于2
解决方法：
1、 查询每个部门的员工个数

select count(*),department_id 
from employees 
group by department_id;

2、 根据1的结果进行筛选，查询哪个部门员工个数>2

select count(*),department_id 
from employees 
group by department_id 
having count(*)>2;

注意：使用having是指在查询每个部门的员工个数后进行筛选

案例：查询每个工种有奖金的员工的最高工资>12000的工作编号和最高工资

select job_id,max(salary) 
from employees 
where commission_pic is not null 
group by job_id
having max(salary)>12000;

案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

select manger_id,min(salary) 
from employees 
where manger_id>102 
group by manger_id 
having min(salary)>5000;

#按表达式或函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？

select count(*),length(last_name) length 
from employees 
group by length 
having count(*)>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 max(salary),min(salary),avg(salary),sum(salary),job_id 
from employees
group by job_id 
order by job_id asc;

案例2：查询员工最高工资和最低工资的差距（difference）

select max(salary)-min(salary) as difference from employess;

案例3：查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select min(salasy) ,manger_id 
from employees 
where manger_id is not null 
group by manger_id 
having min(salary)>=6000;

案例4：查询所有部门的编号，员工数量和平均值，并按平均工资降序

select department_id,count(*),avg(salary) 
from employees 
group by department_id 
order by avg(salary) desc;

案例5：选择具有各个job_id的员工人数

select job_id ,count(*) from employees group by job_id;

**

3、连接查询

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

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
1、按年代标准：
sql92标准：仅仅支持内连接
sql99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接
2、按功能分类：
内连接：
（1）等值连接
（2）非等值连接
（3）自连接

		外连接：
				（1 ）左外链接
				（2）右外连接
				（3）全外连接
				
		交叉连接：

#一、sql92标准
#等值连接
1、 多表等值连接的结果为多表的交集部分
2、 N表连接，至少需要n-1个连接条件
3、 多表的顺序没有要求
4、 一般需要为表起别名
5、 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

案例：查询girl表中boy_id对应boys表中的男朋友姓名

select name,boyNmae from girl,boys where girl.boy_id=boys.id;

#1、为表起别名
1、 提高语句的简洁度
2、 区分多个重名的字段
3、 两个表的顺序可以调换
注意：如果为表起了别名，则查询的字段就不能使用原来的表名

案例：查询员工名、工种名、工种号

select last_name,e.job_id,job_title 
from employees e,jobs j 
where e.job_id=j.job_id;

#2、可以加筛选
案例：查询有奖金的员工名，部门号

select last_name,department_name
from employees e, departments d 
where commission_pct is not null and e.department_id=d.department_id;

案例：查询城市名中第二个字符为o的部门名和城市名

select department_name,city 
from departments d,locations l 
where d.location_id=l.location and city like ‘_o%’;

#3、可以加分组
案例：查询每个城市的部门个数

select count(*) number,city 
from  departments d,location l 
where d.location_id=l.location_id 
group by city; 

案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select department_name,d.manger_id,min(salary) min_sal 
from departments d,employees e 
where d.department_id=d.department_id and commission_pct is not null 
group by demparment_name;

#4、可以加排序
案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

select job_title,count(*) 
from jobs j, employees e 
where j.job_id=e.job_id 
group by job_title 
order by count(*) desc;

#5、可以实现三表连接
案例：查询员工名、部门名和所在的城市

select last_name,department_name,city 
from employees e,departments d,locations l 
where e.department_id=d.department and d.location_id= l.location_id;

#非等值连接
案例：查询员工的工资和工资级别

select salary,grdae_level 
from employees e,grade_level g 
where salary between g.lowest_sal and g.highest_sal;

#自连接（在自己表中查询）
案例：查询员工名和上级的名称

select e.employee_id,e.last_name,m.employee_id,m.last_name 
from employees e,employees m 
where e.employee_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 job_id from employees where job_id like '%a%e%';

案例：显示当前日期，以及去前后空格，截取子字符串的函数

select tirm(now());

案例;在三张表中查询姓名，年级名，成绩

select s.name,g.name,score 
from student s,grade g,result r 
where s.gradeID=g.id and s.id=r.studentNo;

#练习

1、显示所有员工的姓名，部门号和部门名称

select last_name,d.department_id,department_name 
from employees e,departments d 
where e.department_id=d.department_id;

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

select job_id,location_id 
from employees e,departments d 
where department_id=90 and e.department_id=d.department_id;

3、选择所有奖金的员工的last_name,job_id,l.location_id,city;

select last_name,job_id,location_id,city 
from employees e,departments d,locations l 
where commission_pct is not null and d.location_id=l.location_id and d.department_id=e.department_id;

4、 选择city 在Toronto工作的员工的last_name,job_id,department_id,department_name;

select last_name,job_id,department_name,d.department_id
from employees e,departments d,locations l 
where e.department_id=d.department and d.location_id=l.location_id and city=’Toronto’;

5、查询每个工作，每个部门的部门名，工种名和最低工资

select department_name,job_title,min(salary) 
from departments d,employees e,jobs j 
where d.department_id=e.department_id and e.job_id= j.job_id 
group by department_name,job_tittle;

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

select count(*),country_id 
from departments d,locations lwhere d.location_id=l.location_id 
group by country_id 
having count(*)>2;

7、 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号

select e.last_name,e.employee_id,e.last_name,e.employee_id 
from employees e,employees m 
where e.employee_id=m.manger_id;

#二、sql99语法
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接:inner
外连接
1、左外：left 【outer】
2、右外：right【outer】
3、全外：full【outer】
交叉连接：croos

#内连接
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

分类：
1、等值 2、非等值 3、自连接

特点：
1、 添加排序、分组、筛选
2、 inner可以省略
3、 筛选条件放在where后面，连接条件可以放在on后面，提高分离性，便于阅读
4、 inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

#等值连接
案例：查询员工名、部门名

select last_name,department_name 
from employees e inner join departments d 
on  e.department_id =d.deparment_id; 

案例：查询员工中包含e的员工名和工种名（筛选）

select last_name,job_title 
from employees e inner join jobs j 
on e.job_id=j.job_id 
where last_name like '%e%’and job_title like '%e%';

案例：查询部门个数>3的城市名和部门个数（分组+个数）

select city,count(*) 
from department d inner join locations l 
on d.location_id=l.location_id 
group by city  
having count(*)>3;

案例：查询员工名、部门号、工种名，并按部门名降序

select last_name,e.department_id,job_tittle 
from employees e
inner join departments d on e.departmnet_id=d.department_id 
inner join jobs j on e.job_id=j.job_id 
order by deparment_name desc;

#非等值连接
案例：查询员工的工资级别

select salary,grade_level 
from employees e 
inner join job_grades j on salary  between j.lowest_sal and j.highest_sal;

案例：查询每个工资级别的个数>2，并且按工资级别降序

select count(*),grade_level 
from employees e 
inner join job_grades j on salary between j.lowest_sal and j.highest_sal 
group by grade_level 
having count(*)>20 
order by grade_level desc; 

#自连接
案例：查询姓名中包含e员工的名字、上级领导名字

select e.last_name,m.last_name 
from employees e join employees m on e.employee_id=m.manger_id 
where e.last_name like '%e%';

#外连接
**应用场景：**用于查询一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
注意：主表是指想查哪个哪个就是主表
2、左外连接，left，join左边的是主表
右外连接：right，join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有而从表中没有的记录

案例：查询男朋友不在男神表的女神名
#左外链接

select g.name 
from girls g left outer join boys b on g.boy_id=b.id 
where b.id is null;

#右外连接

select g.name 
from boys b right outer join girls g on g.boy_id=b.id
where b.id is null;

案例：查询哪个部门没有员工
#左外连接

select department_name 
from departments d 
left join employees e on d.department_id=e.department_id 
 where e.employee_id is null;

#右外连接

select department_name 
from employees e  right join departments d on d.department_id=e.department_id  
where e.employee_id is null;

#全外连接

select department_name 
from departments d full join employees e on d.department_id=e.department_id  
where e.employee_id is null;

#交叉连接（类似于笛卡尔乘积）

select department_name 
from departments d cross join employees e on d.department_id=e.department_id  
where e.employee_id is null;

#练习
1、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

select b.id,b.name,g.id,g.name 
from girls g left join boys b on g.boy_id= b.id 
where g.boy_id>3; 

2、查询哪个城市没有部门

select department_name,city 
from departments d right join locations l on d.location_id=l.location_id 
where department_name is null;

3、查询部门名为sal或it的员工信息

select e.*,d.department from employees e  right join departments d on e.deparment_id=d.department_id 
where department_name in (SAL,IT);