mysql学习笔记20211118
一数据库排序的用法
order
select * from emp001 order by sal asc;
升序
降序
select * from emp001 order by sal desc;
1.
select sum(sal) from emp001 where deptno=10; select sum(sal) from emp001 where deptno=20; select sum(sal) from emp001 where deptno=30; select sum(sal) from emp001 where deptno=40;
2.
select avg(sal) from emp001 where deptno=10; select avg(sal) from emp001 where deptno=20; select avg(sal) from emp001 where deptno=30; select avg(sal) from emp001 where deptno=40;
3
select count(empno) from emp001 where deptno=10;
select count(empno) from emp001 where deptno=20;
select count(empno) from emp001 where deptno=30;
select count(empno) from emp001 where deptno=40;
4
select count(comm) from emp001 where deptno=10;
select count(comm) from emp001 where deptno=20;
select count(comm) from emp001 where deptno=30;
select count(comm) from emp001 where deptno=40;
5.
select count(empno) from emp where deptno=10 and comm is null;
select count(empno) from emp where deptno=20 and comm is null;
select count(empno) from emp where deptno=30 and comm is null;
select count(empno) from emp where deptno=40 and comm is null;
6.
select * from emp001 where sal=(select max(sal) from emp001 where deptno=10);
select * from emp001 where sal=(select max(sal) from emp001 where deptno=20);
select * from emp001 where sal=(select max(sal) from emp001 where deptno=30);
select * from emp001 where sal=(select max(sal) from emp001 where deptno=40);
7.
select * from emp001 where sal=(select min(sal) from emp001 where deptno=10);
select * from emp001 where sal=(select min(sal) from emp001 where deptno=20);
select * from emp001 where sal=(select min(sal) from emp001 where deptno=30);
select * from emp001 where sal=(select min(sal) from emp001 where deptno=40);
8.
select empno,ename,job,sex,mgr,hiredate,sal,ifnull(comm,'没有奖金') as comm,deptno from emp where comm is null having deptno=10;
select empno,ename,job,sex,mgr,hiredate,sal,ifnull(comm,'没有奖金') as comm,deptno from emp where comm is null having deptno=20;
select empno,ename,job,sex,mgr,hiredate,sal,ifnull(comm,'没有奖金') as comm,deptno from emp where comm is null having deptno=30;
select empno,ename,job,sex,mgr,hiredate,sal,ifnull(comm,'没有奖金') as comm,deptno from emp where comm is null having deptno=40;
9.
select * from emp001 where mgr is null;
10.
select sal from emp001 where deptno=10 order by sal asc;
select sal from emp001 where deptno=20 order by sal asc;
select sal from emp001 where deptno=30 order by sal asc;
select sal from emp001 where deptno=40 order by sal asc;
11.
select sal from emp001 where deptno=10 order by sal desc;
select sal from emp001 where deptno=20 order by sal desc;
select sal from emp001 where deptno=30 order by sal desc;
select sal from emp001 where deptno=40 order by sal desc;
二、
1.
select empno from emp001 group by empno;
select job from emp001 group by job;
select sex from emp001 group by sex;
select deptno from emp001 group by deptno;
2.
select sex from emp001 group by sex;
select deptno from emp001 group by deptno;
3.
select deptno,sum(sal) from emp001 group by deptno;
4.
select deptno,avg(sal) from emp001 group by deptno;
5.
select deptno,count(empno) from emp001 group by deptno;
6.
select deptno,max(sal) from emp001 group by deptno;
7.
select deptno,min(sal) from emp001 group by deptno;
8.
select deptno,count(empno) from emp001 where deptno=20;
over
