mysql数据库（入门）四

最新推荐文章于 2022-08-27 13:35:31 发布

FSMEKO

最新推荐文章于 2022-08-27 13:35:31 发布

阅读量518

点赞数 1

分类专栏： mysql数据库

本文链接：https://blog.csdn.net/FSMEKO/article/details/83855354

版权

mysql数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数学相关函数

floor(num) 向下取整
```
select floor(3.84); 值为3
```

round(num) 四舍五入
```
select round(23.8); 值为24
```
round(num,m) 四舍五入 m代表小数位数
```
select round(23.869,2); 值为23.87
```
truncate(num,m) 和上面一样（非四舍五入）
```
select truncate(23.869,2); 值为23.86
```
rand() 随机数获取0-1的随机数
```
    select rand();
```
获取 3-8的随机数
```
select floor(rand()*6)+3;
```
获取 8-10的随机数 0-2 3 +8
```
select floor(rand()3)+8;
```

分组查询

先建两张表数据自己添加

create table emp(
EMPNO int(4) primary key,
ENAME varchar(10) not null,
JOB varchar(9),
MGR int(4),
HIREdate date,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(4)
);//员工表

create table dept(
DEPTNO int(4) primary key,
DNAME varchar(14) not null unique,
LOC varchar(13)
);//部门表

分组查询通常和聚合函数结合使用
一般情况下每个部门(职位、分类) 就以部门(职位、分类)作为分组的条件
可以有多个分组条件

查询每个部门的最高工资

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

查询每个职位的平均工资

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

查询每个部门下每个主管的手下人数

select deptno,mgr,count(*) from emp
group by deptno,mgr;

group by 存在的位置 select * from emp where ...... group by ... order by ... limit ...
案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
```
select deptno,count(*),sum(sal) from emp
group by deptno
order by count(*),sum(sal) desc;
```
案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
```
select deptno,avg(sal),min(sal),max(sal) from emp 
where sal between 1000 and 3000
group by deptno
order by avg(sal);
```
案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
```
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;
```
1. 查询每个部门的平均工资，要求平均工资大于2000。
2. 以下为错误语法： where后面不能写聚合函数
```
select deptno,avg(sal) a from emp
where a>2000
group by deptno;
```
使用having 解决聚合函数的条件过滤问题，having写在group by 后面

where后面写普通字段的过滤条件，having后面写聚合函数的过滤条件

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

创建一张商城表

CREATE TABLE `t_item` (
  `id` varchar(200) NOT NULL COMMENT '商品id',
  `category_id` bigint(20) DEFAULT NULL COMMENT '分类id',
  `item_type` varchar(100) DEFAULT NULL COMMENT '商品系列',
  `title` varchar(100) DEFAULT NULL COMMENT '商品标题',
  `sell_point` varchar(150) DEFAULT NULL COMMENT '商品卖点',
  `price` bigint(20) DEFAULT NULL COMMENT '商品单价',
  `num` int(10) DEFAULT NULL COMMENT '库存数量',
  `barcode` varchar(30) DEFAULT NULL COMMENT '条形码',
  `image` varchar(500) DEFAULT NULL COMMENT '图片路径',
  `status` int(1) DEFAULT '1' COMMENT '商品状态  1：上架   2：下架   3：删除',
  `priority` int(10) DEFAULT NULL COMMENT '显示优先级',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modified_time` datetime DEFAULT NULL COMMENT '最后修改时间',
  `created_user` varchar(50) DEFAULT NULL COMMENT '创建人',
  `modified_user` varchar(50) DEFAULT NULL COMMENT '最后修改人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询每个分类商品的库存总量，高于199999的库存总量

select category_id,sum(num) s from t_item
group by category_id
having s>199999;

查询每个分类商品所对应的平均单价，要求平均单价低于100

select category_id,avg(price) a from t_item
group by category_id
having a<100;

查询分类id为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;
```
查询emp表中名字不是以s开头，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序排序，如果一致根据工资总和降序排序
```
select job,count(*) c,sum(sal) s,max(sal) from emp where ename not like 's%'
group by job
having avg(sal)!=3000
order by c,s desc;
```

查询emp表每年入职的人数（提高题）

select extract(year from hiredate) year,count(*) from emp group by year;

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

将以上两条合并成一条sql

select * from emp where sal=(select max(sal) from emp);

查询emp表中工资超过平均工资的所有员工信息
```
select * from emp where sal>(select avg(sal) from emp);
```

查询工资高于20号部门平均工资的员工信息

select * from emp where sal>(select avg(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));

having 要和 group by 结合使用

查询最后入职的员工信息

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(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));

子查询总结
1. 嵌套在SQL语句中查询语句称为子查询
2. 子查询能嵌套n层
3. 子查询可写的位置：
4. 可以写在 where/having的后面作为查询条件的值
5. 可以写在 from后面当一张新表 新表必须有别名
6. 可以写在创建表的时候
```
`create table t_emp_10 as (select * from emp where deptno=10);`
```
关联查询
- 同时查询多张表的数据称为关联查询
- 查询每一个员工的姓名和对应的部门名称
```
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
```

查询在纽约工作的所有员工的信息 s

elect e.*
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
```

内连接用的更多

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;

外连接
- 左外连接：以join 左边表为主表左边表显示所有数据右边交集数据
```
select e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;
```
右外连接：以join 右边表为主表右边表显示所有数据左边交集数据
```
select e.ename,d.dname
from emp e right join dept d
on e.deptno=d.deptno;
```
关联查询总结：
```
    等值连接，内连接，外连接都是关联查询的查询方式使用哪一种取决于具体业务需求
```
1. 查两个表的交集数据使用内连接(推荐)或等值连接
2. 查一个表所有数据另外一个表交集数据使用外连接