MySQL数据库（四）

数学相关函数

1. 向下取整 floor(num)
   select floor(3.8);
2. 四舍五入 round(num)
   select round(23.8);
3. 四舍五入 round(num,m) m代表小数位数
   select round(23.879,2);
4. 非四舍五入 truncate(num,m) m代表小数位数
   select truncate(23.879,2);
5. 随机数 rand() 0-1随机数
   select rand();

• 3-5的随机整数 3，4，5 0-2+2
  select floor(rand()*3+3);

分组查询

• 格式：group by 分组字段名
• 每个或每种什么 就以什么进行分组查询

1. 查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
2. 查询每种职业的最高工资
   select job,max(sal) from emp group by job;
3. 查询每个部门的人数
   select deptno,count(*) from emp group by deptno;
4. 查询工资大于1000的员工中每个部门的最高工资
   select deptno,max(sal) from emp where sal>1000 group by deptno;
5. 查询每个领导的手下人数
   select mgr,count(*) from emp where mgr is not null group by mgr;

• 多字段分组在group by后面写多个字段名

1. 查询每个部门，每种职业的平均工资
   select deptno,job,avg(sal) from emp group by deptno,job;

2. 查询emp表中每个部门的编号、人数、工资总和、根据人数进行升序排序，如果人数一致根据工资总和降序排序
   select deptno,count(),sum(sal) from emp group by deptno order by count(),sum(sal) desc;

   select deptno,count(*) c,sum(sal) s from emp group by deptno order by c,s desc;

3. 查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排序
   select deptno,avg(sal) a,min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by a;

4. 查询有上级领导的员工，每个职业的人数，工资总和，平均工资，根据人数进行降序排序，如果人数一致则根据平均工资进行升序排序
   select job,count(*) c,sum(sal),avg(sal) a from emp where mgr is not null group by job order by c desc,a;

having

• where 后面只能写普通字段的条件，不能写聚合函数的条件
• 把聚合函数的条件写在having后面
• 各个关键字的顺序
  select … from … where … group by … having … order by … limit …;

1. 查询每个部门的平均工资，要求平均工资大于2000
   select deptno,avg(sal) a from emp where a>2000 group by deptno;
   select deptno,avg(sal) a from emp group by deptno having a>2000;
2. 查询每个分类category_id的平均单价 要求平均单价低于100
   select category_id,avg(price) a from t_item group by category_id having a<100;
3. 查询分类id为238，917的两个分类的平均单价
   select category_id,avg(price) from t_item where category_id in(238,917) group by category_id;
4. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a from emp
   group by deptno
   having a>2000
   order by a desc;
5. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序
   select deptno,sum(sal),avg(sal) a from emp
   where sal between 1000 and 3000
   group by deptno
   having a>=2000
   order by a;

子查询（嵌套查询）

1. 查询emp表中工资最高的员工信息
   select max(sal) from emp; 5000
   select * from emp where sal=(select max(sal) from emp);

2. 查询高于平均工资的员工信息
   select avg(sal) from emp;
   select * from emp where sal>(select avg(sal) from emp);

3. 查询工资高于20号部门最高工资的员工信息

   select * from emp where sal>(select max(sal) from emp where deptno=20);

4. 查询和jones相同工作的其它员工信息
   select job from emp where ename=‘jones’;

   select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;

5. 查询工资最低的员工的同事们的信息（同事=同一个部门）

   1. 得到最低工资
      select min(sal) from emp;
   2. 通过最低工资找到部门编号
      select deptno from emp where sal=(select min(sal) from emp);
   3. 通过部门编号查询该部门的人，还要去掉工资最低的
      select * from emp where deptno in(select deptno from emp where sal=(select min(sal) from emp)) and sal != (select min(sal) from emp);

6. 查询最后入职的员工信息
   select max(hiredate) from emp;
   select * from emp where hiredate=(select max(hiredate) from emp);

7. 查询king的部门编号和部门名称（需要用到dept表）
   select deptno from emp where ename=‘king’;
   select dname from dept where deptno=(select deptno from emp where ename=‘king’);

8. 查询有员工的部门信息（想办法过滤掉40号部门，因为它没员工）
   select distinct deptno from emp;

   select * from dept where deptno in(select distinct deptno from emp);

9. 查询平均工资最高的部门信息（难度最高）需要考虑并列最高的问题

   1. 得到最高的平均工资
      select avg(sal) a from emp group by deptno order by a desc limit 0,1;
   2. 通过最高平均工资查部门编号
      select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
   3. 通过部门编号查部门信息
      select * from dept where deptno in(上面一坨);

• 子查询总结:

1. 可以嵌套无数层
2. 子查询可写的位置：
   1. 把子查询写在where或having的后面，当成查询条件的值
   2. 写在创建表的时候
      create table emp_20 as (select * from emp where deptno=20);
   3. 写在from后面，当成一张虚拟的表 必须有别名
      select ename from (select * from emp where deptno=20) newtable;

关联查询

• 同时查询多张表的数据的查询方式称为关联查询
• 如果关联查询不写关联关系会得到两张表数据的乘积，这种乘积称为笛卡尔积。笛卡尔积是一种错误的查询结果会占用大量的内存，工作中切记不要出现。

1. 查询每一个员工的员工姓名和对应的部门名称
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
2. 查询在new york工作的员工姓名
   select e.ename
   from emp e,dept d
   where e.deptno=d.deptno and d.loc=‘new york’;

等值连接和内连接

• 等值连接和内连接查询到的是一样的结果，为两张表的交集数据

1. 等值连接： select * from A,B where A.x=B.x and A.age=18;
2. 内连接：select * from A [inner] join B on A.x=B.x where A.age=18;

• 案例：查询每一个员工的员工姓名和对应的部门名称
  select e.ename,d.dname
  from emp e join dept d
  on e.deptno=d.deptno;

外连接

• 外连接查询到的是一张表的全部数据和另外一张表的交集数据
• 格式： select * from A left/right [outer] join B on A.x=B.x where A.age=18;

1. 查询所有的部门名和其对应的员工名
   select d.dname,e.ename
   from emp e right join dept d
   on e.deptno=d.deptno;

关联查询总结

1. 查询方式有三种：等值连接、内连接、外连接
2. 如果查询的数据是两张表的交集数据使用等值或内连接(推荐)
3. 如果查询的数据是一张表的全部数据和另外一张表的交集数据使用外连接