数据库总结(一):基本SQL
数据库总结(二):基本查询
数据库总结(三):分组,联结
数据库总结(四):表设计之关联关系
数据库总结(五):视图,约束,索引
数学相关的函数
- 向下取整 floor(num)
select floor(3.14); - 四舍五入 round(num)
select round(23.8);
round(num,m)
select round(23.879,2); - 非四舍五入 truncate(num,m)
select truncate(23.879,2); - 随机数 rand() 0-1
select rand();
-获取0-5的整数随机数
select floor(rand()*6);
-3-5的随机数
分组查询 group by
-
查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno; -
查询每个部门的工资总和
select deptno,sum(sal) from emp group by deptno; -
查询每种职业的最高工资
select job,max(sal) from emp group by job; -
查询每个领导下的人数
select mgr,count(*) from emp
where mgr is not null group by mgr; -
查询每个部门工资大于1000的员工数量
select deptno,count(*) from emp
where sal>1000 group by deptno;
多字段分组查询 只需要在group by后面写多个字段名通过逗号分隔 -
每个部门每个主管的手下人数
select deptno,mgr,count(*) from emp
where mgr is not null
group by deptno,mgr;53.案例:查询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;54.案例:查询工资在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;
55.案例:查询含有上级领导的员工,每个职业的人数,工资的总和,平均工资,最低工资,最后根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列
select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null
group by job order by c desc,a;
having
where后面只能写普通字段的条件 不能写聚合函数的条件
having和where类似都是用于添加条件的,having后面可以写普通字段的条件也可以写聚合函数的条件,但是建议写聚合函数的条件,而且要结合group by 使用
- 查询每个部门的平均工资,要求平均工资大于2000
-错误写法 where后面不能写聚合函数
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
-正确写法:
select deptno,avg(sal) a from emp
group by deptno having a>2000; - 查询商品表中每个分类的平均单价,要求平均单价小于100
select category_id,avg(price) a from t_item group by category_id having a<100; - 查询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 desc; - 查询emp表中平均工资高于2000的部门编号,部门人数,平均工资,最后根据平均工资降序排序
select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc; - 查询emp表中不是以s开头的职位,每个职位的名字,人数,工资总和,最高工资,过滤掉平均工资是3000的职位,根据人数升序,如果人数一致则根据工资总和降序
select job,count(*) c,sum(sal) s,max(sal) from emp where job not like ‘s%’ group by job having avg(sal)!=3000 order by c, s desc; - 查询每年入职的人数(扩展)
select extract(year from hiredate) year,count(*) from emp
group by year; - 查询最高平均工资的部门编号(扩展) (并列第一的问题不能解决)
select deptno from emp group by deptno
order by avg(sal) desc limit 0,1;
子查询(嵌套查询)
可以在查询语句中嵌套另一条sql语句,可以嵌套n层
- 查询emp表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp); - 查询emp表中工资大于平均工资的员工信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp); - 查询工资高于20号部门最高工资的员工信息
select max(sal) from emp where deptno=20;
select * from emp where sal>(select max(sal) from emp where deptno=20); - 查询和jones相同工作的其它员工信息
select job from emp where ename=‘jones’;
select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’; - 查询工资最低的员工的同事们的信息(同事=相同job)
-得到最低工资
select min(sal) from emp;
-得到拿最低工资员工的职位
select job from emp where sal=(select min(sal) from emp);
-通过职位得到此职位的员工信息排除最低工资那个哥们儿
select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp); - 查询最后入职的员工信息
select max(hiredate) from emp;
select * from emp where hiredate=(select max(hiredate) from emp); - 查询员工king所属的部门编号和部门名称(需要用到部门表dept)
select deptno from emp where ename=‘king’;
select deptno,dname from dept where deptno=(select deptno from emp where ename=‘king’); - 查询有员工的部门信息(查询在员工表中出现的部门的信息)
-得到员工表中的部门编号
select distinct deptno from emp;
-查询上面结果对应的部门详情
select * from dept where deptno in (select distinct deptno from emp); - 查询平均工资最高的部门信息(难度最高 需要考虑并列第一问题)
- 查询最高的平均工资
select avg(sal) a from emp group by deptno order by a desc limit 0,1; - 通过最高的平均工资找到对应的部门编号
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); - 通过部门编号查询部门信息
select * from dept where deptno in(上面一坨);
- 查询最高的平均工资
子查询总结:
- 嵌套在sql语句中的查询语句称为子查询
- 子查询可以嵌套n层
- 子查询可以写在什么位置?
- 写在where和having的后面 当做查询条件的值
- 写在创建表的时候
- 格式: create table 表名 as (子查询)
create table newemp as (select * from emp where deptno=10);
- 格式: create table 表名 as (子查询)
- 写在from后面当成一个虚拟表 必须有别名
select ename from (select * from emp where deptno=10) newtable;
关联查询
同时查询多张表的查询方式称为关联查询
- 查询每一个员工的姓名和其对应的部门名称
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno; - 查询在new york工作的所有员工信息
select e.*
from emp e,dept d
where e.deptno=d.deptno
and d.loc=‘new york’;
笛卡尔积
- 关联查询必须写关联关系,如果不写则得到两张表的乘积,这个乘积称为笛卡尔积
- 工作中不允许出现因为 超级耗内存 有可能直接崩溃
等值连接和内连接
- 等值连接和内连接都是关联查询的查询方式
- 等值连接和内连接查询到的结果一样,都为两张表的交集数据
- 等值连接: select * from A,B where A.x=B.x and A.age=18;
- 内连接:select * from A 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; - 查询在new york工作的所有员工信息
select e.*
from emp e join dept d
on e.deptno=d.deptno
where d.loc=‘new york’;
外链接
- 格式:select * from A left/right join B on A.x=B.x where A.age=18;
- 查询部门表的全部名称和对应的员工姓名
select d.dname,e.ename
from emp e right join dept d
on e.deptno=d.deptno;
关联查询总结:
- 关联查询的查询方式包括三种: 等值连接、内连接和外链接
- 如果查询两张表的交集数据使用等值连接和内连接,推荐使用内连接
- 如果查询一张表的全部数据和另外一张表的交集数据使用外连接,外链接只需要掌握一种即可
总结
- 数学相关函数
- 向下取整 floor()
- 四舍五入 round()
- 非四舍五入 truncate()
- 随机数 rand() 0-1
- 分组查询 group by
- having 后面写聚合函数的条件 需要结合group by使用 where后面写普通字段的条件
- 子查询:嵌套在另外一条sql语句中的查询语句称为子查询,可以嵌套n层
可写的位置: 可写的位置:- 写在where和having后面 当做查询条件的值
- 写在创建表的时候,把查询出来的结果保存到新的表中
- 写在from后面当成一张虚拟表 必须有别名
- 关联查询:同时查询多张表的查询方式称为关联查询
- 关联查询的查询方式三种:
- 等值连接:select * from A,B where A.x=B.x and A.age=18;
- 内连接: select * from A join B on A.x=B.x where A.age=18;
- 外链接: select * from A left/right join B on A.x=B.x where A.age=18;
- 如果需要查询两张表的交集数据使用等值连接和内连接(推荐)
- 如果查询一张表的全部数据和另外一张表的交集数据使用外链接(左外和右外都可以)
- 笛卡尔积:关联查询不写关联关系则会得到两张表的乘积,这个乘积称为笛卡尔积
作业:
-
每个部门的人数,根据人数降序排序
- select deptno,count(*) c from emp group by deptno order by c desc;
- 正确写法
select d.deptno,count(e.empno) c
from emp e right join dept d
on e.deptno=d.deptno group by d.deptno order by c desc;
-
每个部门中,每个主管的手下人数
select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr; -
每种工作的平均工资
select job,avg(sal) from emp group by job; -
每年的入职人数
select extract(year from hiredate) year,count(*) from emp
group by year; -
少于等于3个人的部门信息
- 得到部门编号
select deptno from emp group by deptno having count(*)<=3; - 通过部门编号查部门信息
select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3); - 包含40号部门的写法:
select d.*
from emp e right join dept d
on e.deptno=d.deptno group by d.deptno
having count(e.empno)<=3;
- 得到部门编号
-
拿最低工资的员工信息
select * from emp where sal=(select min(sal) from emp); -
只有一个下属的主管信息
-得到主管编号
select * from emp where mgr is not null
group by mgr having count()=1;
-通过主管编号查询详细信息
select * from emp where empno in(select mgr from emp where mgr is not null
group by mgr having count()=1); -
每月发工资最多的部门信息
- 得到部门最高的工资总和
select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1; - 通过最高的工资总和查询部门编号
select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1); - 通过部门编号得到部门详情
select * from dept where deptno in(上面一坨);
- 得到部门最高的工资总和
-
下属最多的人,查询其个人信息
-得到最多的人数
select count() from emp group by mgr order by count() desc limit 0,1;
-通过最大人数得到领导的编号
select mgr from emp group by mgr having count()=(select count() from emp group by mgr order by count(*) desc limit 0,1);- 通过领导编号得到个人详情
select * from emp where empno in(上面一坨);
- 通过领导编号得到个人详情
-
拿最低工资员工的同事信息
select min(sal) from emp;select job from emp where sal=(select min(sal) from emp);
select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
-
和最后入职的员工在同一部门的员工信息
select max(hiredate) from emp;
select deptno from emp where hiredate=(select max(hiredate) from emp);
select * from emp where deptno=(第二坨) and hiredate!=(第一坨); -
查询平均工资高于20号平均工资的部门信息
select avg(sal) from emp where deptno=20;select d.*
from emp e join dept d
on e.deptno=d.deptno
group by d.deptno
having avg(sal)>(select avg(sal) from emp where deptno=20); -
查询员工信息和员工对应的部门名称
select e.*,d.dname
from emp e join dept d
on e.deptno=d.deptno; -
查询员工信息,部门名称,所在城市
select e.*,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno; -
查询Dallas市所有的员工信息
select e.*
from emp e join dept d
on e.deptno=d.deptno where d.loc=‘dallas’; -
计算每个城市的员工数量
select d.loc,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
group by d.loc; -
查询员工信息和他的主管姓名
select e.ename,m.ename
from emp e join emp m
on e.mgr=m.empno; -
员工信息,员工主管名字,部门名
select e.ename,m.ename,d.dname
from emp e join emp m
on e.mgr=m.empno
join dept d
on e.deptno=d.deptno; -
员工名和他所在部门名
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno; -
案例:查询emp表中所有员工的姓名以及该员工上级领导的编号,姓名,职位,工资
select e.ename,m.empno,m.ename
from emp e left join emp m
on e.mgr = m.empno; -
案例:查询emp表中名字中没有字母’K’的所有员工的编号,姓名,职位以及所在部门的编号,名称,地址
insert into emp (empno,ename) values(10010,‘苍老师’);select e.empno,e.ename,e.job,d.*
from emp e left join dept d
on e.deptno=d.deptno
where e.ename not like ‘%k%’; -
案例:查询dept表中所有的部门的所有的信息,以及与之关联的emp表中员工的编号,姓名,职位,工资
select d.*,e.empno,e.ename,e.job,e.sal
from emp e right join dept d
on e.deptno=d.deptno;