【MYSQL】聚合函数和单表/多表查询练习、子查询、内外连接

目录

1.聚合函数

1.1.group by子句

1.2.having语句

2.单表查询 

2.2单表查询

3.多表查询

 3.2.子查询

5.内链接

6.外连接

1.聚合函数

创建一个表

count:

sum:

avg:

max:

min: 

1.1.group by子句

查看不同的职业的最高/最低工资、平均工资：

1.2.having语句

• 通常和group by连用

使用上面的emp表，筛选平均工资大于1000的职业：

where和having的区别：

• 在上面的指令where是优先比group by 执行的，having 是晚于group by执行的；
• having和where的使用差不多，只是有优先级的差别；

2.单表查询 

2.1.三个表

员工表

部门表

薪资表

  

2.2单表查询

1.查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

select ename, sal, job from emp where (sal>500 or job='manager') and ename like 'J%';

2.按照部门号升序而雇员的工资降序排序

select * from emp order by deptno asc, sal desc;

3.使用年薪进行降序排序

ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值

select ename, sal*12+ifnull(comm,0) '年薪' from emp order by 年薪 desc;

4.显示工资最高的员工的名字和工作岗位

select ename, job, sal from emp where sal=(select max(sal) from emp);

5.显示工资高于平均工资的员工信息

select ename, sal from emp where sal>(select avg(sal) from emp);

6. 显示每个部门的平均工资和最高工资

select deptno, avg(sal), max(sal) from emp group by deptno;

7.显示平均工资低于2000的部门号和它的平均工资

select deptno, avg(sal) avg_sal from emp group by deptno having 2000>avg_sal;

8.显示每种岗位的雇员总数，平均工资

select job, count(job), avg(sal) job_avgsal from emp group by job;

3.多表查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

1.显示部门号为10的部门名，员工名和工资

select emp.deptno, dname, ename, sal from emp,dept where (dept.deptno=emp.deptno) and (emp.deptno=10);

2.显示各个员工的姓名，工资，及工资级别

select ename,sal,grade,losal,hisal from emp,salgrade where sal between losal and hisal;

3.1.自连接：自连接是指在同一张表连接查询

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

select worker.ename,worker.mgr,leader.empno,leader.ename from emp worker,emp leader where worker.mgr=leader.empno and worker.ename='FORD';

 3.2.子查询

• 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

1.单行子查询：

显示SMITH同一部门的员工：

select * from emp where deptno=(select deptno from emp where ename='smith');

2.多行子查询：

in关键字；

• 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10) and deptno!=10;

all关键字；

• 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);

 any关键字；

• 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

 3.多列子查询

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

select ename from emp where (job,deptno) = (select job,deptno from emp where ename='smith') and ename!='smith';

4.在from子句中使用子查询

1.显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

select ename,deptno,sal,asal from emp, (select avg(sal) asal, deptno dn from emp group by deptno) avgsal where sal > avgsal.asal and emp.deptno=avgsal.dn;

2.查找每个部门工资最高的人的姓名、工资、部门、最高工资

select ename, sal, deptno, ms from emp, (select max(sal) ms, deptno dn from emp group by dn) maxs where deptno=dn and sal=ms;

 3.显示每个部门的信息（部门名，编号，地址）和人员数量

select dname, dept.deptno, loc,count_per  from dept, (select count(*) count_per, deptno from emp group by deptno) em where dept.deptno=em.deptno;

4.合并查询--union

• union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
• union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

将工资大于2500或职位是MANAGER的人找出来

select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';

5.内链接

语法：select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

• 和上面使用 from 表1，表2 where 筛选条件；是一样的只是语法不同

案例：显示SMITH的名字和部门名称

语法1：
select ename, dname from emp,dept where ename='SMITH' and emp.deptno=dept.deptno;
语法2：
select ename, dname from emp inner join dept on ename='SMITH' and emp.deptno=dept.deptno;

6.外连接

格式：

外左连接：
select 字段名 from 表名1 left join 表名2 on 连接条件
外右连接：
select 字段名 from 表名1 right join 表名2 on 连接条件

案例表：

查询所有人的成绩没有成绩也要显示信息： 

外左连接：显示以两张表左边为主

外右连接：显示以两张表右边为主