###数学相关函数
1. 向下取整 floor(num)
select floor(3.84); //3
2. 四舍五入 round(num);
select round(3.84); // 4
-round(num,m) m代表小数位数
select round(3.84567,3); //3.846
3. 非四舍五入 truncate(num,m) //建表语句中，使用truncate表示删除重新创建该表
select truncate(3.84567,3);
4. 随机数 rand() 0-1 5-10 0-5
select floor(rand()*6) + 5;
3-8 0-5 select floor(rand()*6) +3;
###分组查询
- 分组查询通常和聚合函数结合使用，以组为单位进行统计
- 一般情况下，题目中每个xxx 就在group by后面写xxx
1. 查询每个部门的最高工资
select max(salary) ,dept from emp group by dept;
2. 查询每个部门的平均工资
select dept, avg(salary) from emp group by dept;
4. 查询每个部门中薪资大于5000的人数
select dept,count(*) from emp where salary >5000 group by dept;
5. 查询每个部门下面的人数：
select dept,count(*) from emp group by dept;
53. 案例：查询emp表中每个部门中工资大于5000人数以及这些人工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
select dept, count(*) count,sum(salary) sumsal from emp where salary >5000 group by dept order by count,sumsal desc;
54. 案例：查询每个部门工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
select dept,avg(salary) avgsal,min(salary) minsal,max(salary) maxsal from emp
where salary between 1000 and 3000 group by dept order by avgsal;
- 如果需要使用多个字段进行分组 直接在group by 后面写多个字段名通过逗号分隔
1. 查询每个部门下每个领导的手下人数（自己加字段）
select dept,mgr,count(*) from emp
where mgr is not null group by dept,mgr;
###having
- where后面只能写普通字段的条件不能写聚合函数
- having后面可以写普通字段条件也可以写聚合函数，但是推荐在having后面只写聚合函数
- having写在group by 的后面
- select * from 表名 where ..... group by xxx having .... order by ..... limit ...;
2. 查询每个部门的平均工资，要求平均工资大于2000
select dept, avg(salary) avgsal from emp group by dept having avgsal>2000;
3. 查询每个部门的平均工资，过滤掉平均工资低于6000的，并且员工名字不能以八开头，。
select dept, avg(salary) avgsal from emp where name not like '八%' group by dept having avgsal>=6000;
### 子查询
- 子查询
1. 查询emp表中工资高于平均工资的员工信息
select * from emp where salary> (select avg(salary) from emp);
2. 查询和‘关羽’相同工作的员工信息
select * from emp where name !='关羽' and salary =(select salary from emp where name='关羽');
3. 查询工资最低的员工的所在部门同事信息
-求最低工资
select min(salary) from emp
-通过最低工资得到这个员工的部门编号
select dept from emp where salary = (select min(salary) from emp)
-通过部门编号找到其它的员工信息
select * from emp where dept = (select dept from emp where salary = (select min(salary) from emp));
- having 要写在 group by 后面
4. 查询有员工的部门信息（需要两张表）
-得到员工表出现的部门编号
select distinct dept from emp;
-通过部门编号得到部门信息
select * from dept where dept in (select distinct dept from emp);
5. 查询平均工资最高的部门信息（史诗级难度！！,细细体会哦）
我们先逆推：
1）；想要找到平均工资最高的部门信息，首先要知道这个部门是谁？
select * from emp where dept in (
......
)
2）：该部门必须是平均工资最高的那个部门（按部门分组的平均工资）
select * from emp where dept in (
select dept from emp group by dept having avg(salary)=(
......
)
)
3): 解决上面的那个问题，平均工资最高的是多少？（这样就可以得打最终答案了）
按部门分组求每个部门的平均工资，并且按平均工资降序，分页第一条就是最高的平均工资
select * from emp where dept in (
select dept from emp group by dept having avg(salary)=(
select avg(salary) sal from emp group by dept order by sal desc limit 0,1
)
)
或者我们来顺推：想要得到平均工资最高的部门信息，假如我们知道了每个部门的平均工资，
看那个部门最高，最高的那个部门求出来了，这个部门的员工信息也就出来了。
1）：求每个部门的平均工资和所在部门：
select avg(salary) sal,dept from emp group by dept
2): 求出最高平均工资和部门，在上面的基础上降序排列
select avg(salary) sal,dept from emp group by dept order by sal desc limit 0,1
3:）：把该部门分离出来，再嵌套一层查询(最高平均工资的部门):from的表格是一个查询语句，我们通常加一个别名
select dept from (
select avg(salary) sal,dept from emp group by dept order by sal desc limit 0,1
)t
4）：部门找到了，查询该部门所有：
select *from emp where dept = (
select dept from (
select avg(salary) sal,dept from emp group by dept order by sal desc limit 0,1
)t
)
- 子查询可以写在什么位置
1. 写在where或having后面 当做查询条件的值
2. 写在创建表的时候 把查询结果保存成一张新的表
create table emp_20 as (select * from emp where deptno=20);
3. 写在from后面 当成一个虚拟表 **必须有别名**
select * from emp where dept=20;
select name,sal from (select * from emp where dept=20) newtable;
###关联查询
- 同时查询多张表的数据称为关联查询 （这里先执行提供的表格）
drop table if exists emp;
create table emp(
empno int(4) primary key comment '员工编号',
ename varchar(10) not null comment '员工姓名',
job varchar(9) comment '员工职业',
mgr int(4) comment '部门主管编号',
hiredate date comment '日期',
sal double(7,2) comment '工资',
comm double(7,2) comment '绩效',
deptno int(4) comment '部门编号'
);
drop table if exists dept;
create table dept(
deptno int(4) primary key comment '部门编号',
dname varchar(14) not null unique comment '部门名称',
loc varchar(13) comment '地址'
);
insert into dept VALUES (10,'会计部','new yore');
insert into dept VALUES (20,'研究部','hubei');
insert into dept VALUES (30,'销售部','hunan');
insert into dept VALUES (40,'操作部','beijing');
SELECT * FROM dept;
insert into emp VALUES
(101,'Tom','书记员',111,str_to_date('17-12-1980','%d-%m-%Y'),800,null,20);
insert into emp VALUES
(102,'Jetty','推销员',106,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);
insert into emp VALUES
(103,'Cat','推销员',106,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);
insert into emp VALUES
(104,'fanfan','经理',108,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);
insert into emp VALUES
(105,'cong','推销员',106,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);
insert into emp VALUES
(106,'lili','经理',108,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30);
insert into emp VALUES
(107,'yueyue','经理',108,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10);
insert into emp VALUES
(108,'yuxin','总统',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);
insert into emp VALUES
(109,'qiaoqiao','推销员',106,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);
insert into emp VALUES
(110,'zhenzhen','书记员',106,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30);
insert into emp VALUES
(111,'laozhang','分析师',104,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);
insert into emp VALUES
(112,'jiezi','书记员',107,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10);
commit;
1. 查询每一个员工的编号姓名和对应的部门名称(两个表中都有部门编号deptno,可以用deptno作为条件等值查询,from的是多个表，加别名)
select e.empno, e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;
2. 查询在new york工作的员工信息 （两表关联同样用共有的部门号关联）
select * from emp e,dept d
where e.deptno = d.deptno and d.loc='new yore';
####笛卡尔积
- 如果关联查询不写关联关系则结果为两张表的乘积，这个乘积称为笛卡尔积。
- 笛卡尔积为一种错误的查询结果，切记工作中不要出现
###等值连接和内连接
关于内连接，左右外链接，请看图文解说：https://blog.csdn.net/weixin_40072979/article/details/85328688
- 等值连接和内连接查询到的内容一样，都为两张表中有关联关系的数据(交集部分)
- 等值连接： select * from A,B where A.x=B.x and A.age=18;
- 内连接： select * from A [inner] join B on A.x=B.x where A.age=18; //inner 可要可不要
1. 查询每一个员工的姓名和对应的部门名称
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;
###外连接
- 内连接和等值连接查询到的是交集部分的数据，外连接查询到的是某一张表的全部数据+另外一张表的交集数据
- 左/右外连接： select * from A left/right join B on A.x=B.x where A.age=18;
1. 查询所有员工姓名和对应的部门名称
select e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;
###关联查询总结
- 关联查询的查询方式： 等值连接 内连接和外连接
- 如果想查询的数据为两张表的交集数据使用等值连接或内连接(推荐)
- 如果查询的数据是一张表的全部数据和另外一张表的交集数据则使用外连接
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
