4.2 多行函数
不管函数处理多少条,只返回一条记录
如果你的数据可以分为多个组,那么返回的数据条数和组数相同
每个部门的平均薪资
10 20 30 --> 3
常用的多行函数有5个
max 最大值: 如果处理的值是字符串,将会把值按照字典序排序
min 最小值: 如果处理的值是字符串,将会把值按照字典序排序
avg 平均值: 只能用于数值型数据,求平均值
sum 求和: 如果求和过程中有null,那么不会计算在内
count 求总数: 如果统计的数据中有null,不会把null统计在内
经典的错误
--查询公司最低薪资的员工是谁?
select min(sal) ,ename from emp;
mysql语法可行 -- 5.7之前可以 ,即使问题解决了 结果也是不对的
oracle不可行
将来工作的时候不能把普通列和组函数写在一起,虽然mysql语法不会报错,但是给的结果是错误的
4.3 数据分组
按照某一个条件进行分组,每一组返回对应的结果
group by 可以对指定的列进行分组,列尽量有相同的
having可以对分组之后的数据进行过滤,所以能出现在having中的比较项一定是被分组的列或者是组函数
底层(注意!!!)
where称之为行级过滤,处理的是表中每一行数据的过滤
having称之为组级过滤,处理的是分组之后的每一组数据
能使用where的,尽量不要使用having
--查询每种工作的平均薪资 select job,avg(sal) from emp group by job; --查询每个部门的最高薪资和最低薪资 select max(sal),min(sal) from emp; select deptno,max(sal),min(sal) from emp group by deptno; --查询每个部门的人数和每月工资总数 select deptno,count(empno),sum(sal) from emp group by deptno; --查询每个部门,每种工作的平均薪资 select deptno,job , avg(sal) from emp group by deptno,job; select deptno,job , avg(sal) from emp group by deptno,job order by depto,job; --查询个人姓名的平均薪资--尽量对多数据进行分组 select ename, max(sal),min(sal) from emp group by ename;
--查询平均薪资高于2500的部门 select deptno,avg(sal) from emp group by deptno having avg(sal)>=2500; select deptno,avg(sal) from emp group by deptno having ename like '%A%'; --查询20部门的平均薪资(比较这两种,最终推荐第二种) select deptno,avg(sal) from emp group by deptno having deptno = 20; select deptno,avg(sal) from emp where deptno = 20 group by deptno;
--查询10 20部门中,并且在二月份入职员工中,每个部门中平均薪资高于1500的工作是什么,并按照部门,工作平均薪资进行排序 select * from emp where deptno in (10,20) ; select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by deptno ,job having avg(sal)>1500 ; select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by deptno ,job having avg(sal)>1500 order by deptno ,avg(sal); --美观写法 SELECT deptno, job, avg( sal ) '平均薪资' FROM emp WHERE deptno IN ( 10, 20 ) GROUP BY deptno, job HAVING avg( sal )> 1500 ORDER BY deptno, avg( sal );
4.4 DQL单表关键字执行顺序
select: 我们要显示那些列的数据
from: 从那张表中获取数据
where: 从表中获取数据的时候进行行级的数据过滤
group by: 对数据进行分组处理,一组获取对应的结果
having: 组级过滤,组级过滤的数据必须是分组条件或者是组函数
order by: 排序 asc desc
执行的顺序(面试题)
from --> where -->group by -->having-->select -->order by
4.5 多表查询
a. 查询的两张表如果出现同名的列,我们需要将表名标注到列名前面
b. 如果是非同名的列,表名可加可不加,推荐加上
为了书写方便,可以给表添加别名
一般情况下取首字母,特殊情况下取它所代表的含义
表的别名只在本次查询中生效
c. **如果表与表进行关联查询的时候,如果不添加关联条件,查询的总记录数就是ab = 笛卡尔积
select * from emp,dept;
a 15 b 10 c 10 -->1500条
d. 多表查询的时候必须要加条件
等值
非等值
--查询每个员工所在的部门名称 select ename,deptno from emp; select deptno,dname from dept; select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept; --等值关联查询 select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno; select emp.ename,dept.dname from emp , dept where emp.deptno = dept.deptno; --添加别名 select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
4.6 表与表关联的方式
因为表的关联条件和业务查询条件放在了一起,为了防止混淆于是提供了下面三种方式
自然连接
-- 会自动选择列名相同并且类型相同的列 --查询薪资大于2000的员工姓名和部门名称 select e.ename,d.dname from emp e ,dept d where e.deptno = d.deptno and e.sal >2000; --自然连接 select e.ename,d.dname from emp e natural join dept d ; select e.ename,d.dname from emp e natural join dept d where e.sal > 2000 ;
using
-- 不需要mysql帮我们选择等值连接的列,现在我们指定等值连接的列 ----查询薪资大于2000的员工姓名和部门名称 using select e.ename,d.dname from emp e join dept d using(deptno); select e.ename,d.dname from emp e join dept d using(deptno) where e.sal > 2000;
on
-- 我们可以指定两张表关联的条件,可以是非等值的操作 ----查询薪资大于2000的员工姓名和部门名称 using select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno); select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno) where e.sal > 2000; --查询每个员工所对应的薪资等级 select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal; select e.ename,s.grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
查询名字中带有A字母的员工姓名,部门名称和薪资等级
-- 第一种写法 SELECT e.ename, d.dname, s.grade FROM emp e, dept d, salgrade s WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.ename LIKE '%A%'; ----------------------------------------- -- 第二种写法 SELECT e.ename, d.dname, s.grade FROM emp e JOIN dept d USING ( deptno ) JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal) WHERE e.ename LIKE '%A%';
4.7 表与表的外连接
当我们对两张表进行关联查询的时候,基于数据的原因导致其中一张表中的数据没办法被完全查询出来
外连接可以让没查询出来的数据也显示出来
因为我们写SQL的时候表总有左右之分 ,外连接也分为
左外连接:显示左面表所有的数据
右外连接:显示右面表所有的数据
--统计每个部门的人数 select deptno,count(empno) from emp group by deptno; select * from emp e join dept d using(deptno); select * from emp e left join dept d using(deptno); select * from emp e right join dept d using(deptno); select deptno,count(e.empno) from emp e right join dept d using(deptno) group by deptno; -------------------------全外连接 SELECT deptno, e.ename, d.dname FROM emp e RIGHT JOIN dept d USING ( deptno ) UNION SELECT deptno, e.ename, d.dname FROM emp e LEFT JOIN dept d USING ( deptno ); -------------------------Oracle的全外连接使用 Full Join
4.8 表与表的自连接
我们要查询的两个字段同时处于一张表上,我们只能将一张表当做含有不同意义的两张表去处理
给相同的表取不同的简称(按照所代表的含义去取)
--查询每个员工与其直属领导的名字 select e.ename,m.ename from emp e,emp m where e.mgr = m.empno; select e.ename,m.ename from emp e join emp m on(e.mgr = m.empno);
4.9 表与表的子连接(常用!!)
-- 把一个SQL语句的查询结果当成另外一个SQL语句的查询条件 --查询公司中薪资最低的员工姓名 select ename,sal from emp where sal = (select min(sal) from emp); --查询公司中谁的薪资高于平均薪资 select ename,sal from emp where sal > (select avg(sal) from emp); --谁的薪资高于20部门员工的薪资 select ename,sal from emp where sal > all(select sal from emp where deptno = 20 ); select ename,sal from emp where sal > some(select sal from emp where deptno = 20 ); select ename,sal from emp where sal in (select sal from emp where deptno = 20 );
4.10 表与表的伪表查询 (常用!!)
如果我们所需要的查询条件 需要别的SQL语句提供
如果只需要一个条件,那么可以使用子查询来完成
如果需要多个查询条件,这是就要将所有的查询结果当做伪表进行管理
我们需要把一些含有特殊符号的列名设置别名,然后给伪表设置一个别名(见名知意)
--查询高于自己部门平均薪资的员工信息 select deptno,avg(sal) avgsal from emp group by deptno; SELECT e.ename, e.sal, e.deptno FROM emp e, ( SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno ) d WHERE e.deptno = d.deptno AND e.sal > d.avgsal;