MYSQL数据库

MYSQL数据库

MYSQL条件查询

select * from emp;
select * from emp where deptno=1;

MYSQL 排序和限制

select * from emp order by sal;
select * from emp where deptno=1 and sal<3000;
select * from emp roder by deptno;
select * from emp order by deptno,sal,desc;

select * from emp order by sal limit 3;
select * from emp order by deptno;

聚合

select count(1) from emp;
select deptno,count(1) from emp group by deptno;

既要统计各部门人数，又要统计总人数（rollup总人数）
select deptno,count(1) from emp group by deptno with rollup;

统计人数大于1人的部门
select deptno,count(1) from emp group by deptno having count(1)>1;

统计所有员工的薪水总额、最高和最低薪水；
select sum(sal),max(sal),min(sal)from emp;

表链接

select * from emp where deptno in(select deptno from dept);
select * from emp where deptno in(select deptno from dept1);