大数据MySQL之sql语句简单分析1

–1.> < = >= <= <>
select * from emp where sal >3000;
select * from emp where sal <>5000;
select * from emp where sal =5000;

–2.模糊查询 like
select * from emp where ename like ‘%S%’; S不知道什么位置
select * from emp where ename like ‘S%’; S开头
select * from emp where ename like ‘%S’; S结尾
select * from emp where ename like '_o%’; 第3个字符为o的数据； 占位符

–3.排序
select * from emp order by sal; 默认升序
select * from emp order by sal asc ; asc升序
select * from emp order by sal desc ; desc降序

select * from emp order by deptno asc,sal desc ;

–4.限制多少行
select * from emp limit 2;
select * from emp order by deptno asc,sal desc limit 2;

–5.聚合 group by … having …
–6.聚合函数 sum count avg max min

select
deptno,sum(sal) as sumsal
from emp
group by deptno;
– group by 字段 必须 出现在 select 字段
– having
select
deptno,sum(sal) as sumsal
from emp
group by deptno
having sum(sal)>10000;

select
deptno,job,sum(sal) as sumsal
from emp
group by deptno,job;

–7.数量 最大值等
select count(*) from emp ;
select max(sal) from emp ;

–8.组合
select
deptno,job,sum(sal)

from emp
where job=‘SALESMAN’
group by deptno,job
having sum(sal) >3000
order by sum(sal) desc
limit 1

select
deptno,job,sal
from emp
where job=‘SALESMAN’

select
deptno,job,sum(sal)
from emp
where job=‘SALESMAN’
group by deptno,job

9.as 别名

10.union
drop table a;
create table a(id int,name double );
insert into a values(1,19.999);
insert into a values(2,‘xiaoyanj’);
insert into a values(3,‘lanyang’);

drop table b;
create table b(id int,address timestamp);
insert into b values(1,‘2018-10-10 00:00:00’);
insert into b values(2,2);
insert into b values(3,‘b3’);
insert into b values(4,‘b4’);
insert into b values(5,‘b5’);
insert into b values(3,‘lanyang’);

–去重复数据
select * from a
union
select * from b

–不去重复
select * from a
union all
select * from b

select id,name from a
union all
select id,address from b

–a.名称是第一张表决定 b.不用* 指定字段 c.对应字段类型保持一致