学习数据库的第四天_查询单价为238,917的商品-CSDN博客

本文链接：https://blog.csdn.net/weixin_38992377/article/details/102506172

学习回顾

is null 和 is not null
比较运算符 > < >= <= = !=和<>
and 和 or
between x and y 包含x和y
in(x,y,c) not in
模糊查询 like _单个未知 %0或多个未知
排序 order by 字段名 asc/desc,字段名;
分页查询 limit 跳过的条数,请求的条数
运算符 + - * / 7%2和mod(7,2)
聚合函数最大值max 最小值min 平均值avg 求和sum() 计数count()
ifnull(x,y) 如果x值为null 则取y 反之取x
日期相关

now() curdate() curtime()
date(now()) time(now()) extract(year/month/day/hour/minute/second from now())
日期格式化 date_format(时间,格式) %YymcdHhis
非标准时间格式转成标准时间格式， str_to_date(时间,格式)

字符串相关

拼接 concat(s1,s2)
获取长度 char_length()
获取字符串出现位置 instr(str,substr)
截取 left() right() substring()
trim()
upper() lower()
repeat()
replace()
reverse()

练习

案例：查询不是30号部门的员工的所有信息
select * from emp where deptno!=30;
案例：查询奖金不为空的员工的所有信息
select * from emp where comm is not null;
案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
select empno,ename,job from emp order by empno desc;
案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
select ename,job,sal from emp where deptno in(10,30) order by sal;
案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
select * from emp order by deptno,empno desc;
案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;

案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
select empno,ename,sal,comm from emp where ename not like ‘%s%’ order by sal,empno desc;
案例：统计emp表中员工的总数量
select count() from emp;
案例：统计emp表中获得奖金的员工的数量
select count() from emp where comm>0;
案例：求出emp表中所有的工资累加之和
select sum(sal) from emp;
案例：求出emp表中所有的奖金累加之和
select sum(comm) from emp;
案例：求出emp表中员工的平均工资
select avg(sal) from emp;
案例：求出emp表中员工的平均奖金
select avg(comm) from emp;
案例：求出emp表中员工的最高工资
select max(sal) from emp;
案例：求出emp表中员工编号的最大值
select max(empno) from emp;
案例：查询emp表中员工的最低工资。
select min(sal) from emp;
案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
select count(*) 总人数,sum(sal) 工资总和,avg(sal) 平均工资,max(comm) 最大奖金, min(comm) 最低奖金 from emp;

数学相关

向下取整 floor(num)
select floor(3.84);
四舍五入 round(num)
select round(23.8);
四舍五入 round(num,m) m代表小数位数
select round(23.879,2);
非四舍五入 truncate(num,m)
select truncate(23.879,2);
随机数 rand()
select rand();
###分组查询
查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
查询每种职业的最高工资
select job,max(sal) from emp group by job;
查询每个部门的人数
select deptno,count(*) from emp group by deptno;
查询工资大于1000的员工中每个部门的最高工资
select deptno,max(sal) from emp where sal>1000 group by deptno;
查询每个主管的手下人数(通过主管id分组统计数量)
select mgr,count(*) from emp where mgr is not null group by mgr;

多字段分组格式： group by 字段1名,字段2名;

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

练习：

查询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;
查询工资在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;
查询有上级领导的员工，每个职业的人数，工资总和，平均工资，根据人数进行降序排序，如果人数一致则根据平均工资进行升序排序
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

和group by结合使用
在having后面写聚合函数的条件
在where后面写普通字段的条件
having后面也可以写普通字段的条件但是不推荐
各种关键字的顺序：
select … from 表名 where … group by … having … order by… limit…

查询每个部门的平均工资要求平均工资大于2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
查询商品表中每个分类的平均单价，要求平均单价低于100
select category_id,avg(price) a from t_item group by category_id
having a<100;
查询238，917这两个分类下的商品的平均单价
select category_id,avg(price) from t_item where category_id in(238,917) group by category_id;
查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
select deptno,count(*),avg(sal) a from emp
group by deptno having a>2000 order by a desc;
查询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;
查询每年入职的人数
select extract(year from hiredate) y,count(*) from emp group by y;
查询平均工资最高的部门编号和平均工资
select deptno,avg(sal) a from emp
group by deptno order by a desc limit 0,1;

内容回顾

数学相关方法

floor()
round()
round(num,m)
truncate(num,m)
rand()

分组查询 group by

多字段分组 gourp by 字段1名,字段2名

having

结合group by使用
having后面写聚合函数的条件
where后面写普通字段的条件
各个关键字的顺序
select … from 表名 where … group by xxxx having xxxx order by xxxx limit xxxx;

子查询（嵌套查询）

查询员工表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=(select max(sal) from 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’;
查询工资最低的员工的同事们的信息（同事指相同部门）
- 查询最低工资
  select min(sal) from emp;
- 通过最低工资查询最低工资这个人的部门编号
  select deptno from emp where sal=(select min(sal) from emp);
- 通过部门编号查询该部门的员工
  select * from emp where deptno=(select deptno 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 dname from dept where deptno=(select deptno from emp where ename=‘king’);
查询有员工的部门信息（部门表有40号部门，这个部门是没有员工的，想办法过滤掉）
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(上面一坨);

子查询可以写的位置
1. 写在where或having后面当做查询条件的值。
2. 写在创建表的时候
  create table emp_10 as (select * from emp where deptno=10);
3. 写在from后面 必须起别名
  select ename from (select * from emp where deptno=20) newtable;

关联查询

同时查询多张表数据的查询方式称为关联查询
关联查询必须写关联关系，如果不写关联关系会得到两张表的乘积，这种乘积称为笛卡尔积，工作中切记不要出现这种情况

查询每一个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;

等值连接和内连接：
1. 等值连接：select * from A,B where A.x=B.x and A.xxx<3000;
2. 内连接： select * from A join B on A.x=B.x where A.xxx<3000;

查询每一个员工的姓名和对应的部门名（内连接）
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;
查询在new york工作的员工姓名
select e.ename
from emp e join dept d
on e.deptno=d.deptno
where d.loc=‘new york’;
查询James的部门名称和部门地点
select d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.ename=‘james’;
查询每个部门的名称和对应的员工数量
select d.dname,count(*)
from emp e join dept d
on e.deptno=d.deptno
group by d.dname;

外连接：查询一张表的全部数据和对应另外一张表的交集数据
格式： select * from A left/right join B on A.x=B.x where A.xxx<3000;

查询所有的部门名和对应的员工名字
select d.dname,e.ename
from emp e right join dept d
on e.deptno=d.deptno;
关联查询总结：

如果需要查询两张表的交集数据需要使用等值连接或内连接(推荐)
如果查询两张表中一张表的全部数据和对应一张表的交集数据使用外连接

练习:

每个部门的人数，根据人数降序排序
select deptno,count(*) c from emp group by 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) y,count(*) from emp group by y;
少于等于3个人的部门信息
- 不包含40号部门
  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.ename)<=3;
拿最低工资的员工信息
select min(sal) from emp;

select * from emp where sal=(select min(sal) from emp);
只有一个下属的主管信息
select mgr 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);
每月发工资最多的部门信息
下属最多的人，查询其个人信息
拿最高工资员工的同事信息
和最后入职的员工在同一部门的员工信息实现流程和第十题一样
查询平均工资高于20号平均工资的部门信息
查询员工信息和员工对应的部门名称
查询员工信息，部门名称，所在城市
查询Dallas市所有的员工信息
计算每个城市的员工数量
查询员工信息和他的主管姓名
员工信息，员工主管名字，部门名
员工名和他所在部门名
案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资