MySQl单表基本查询

单表基本查询

1.查询所有的部门编号

select distinct deptno from emp ;
select deptno from emp GROUP  BY deptno ;

2.查询所有人的姓名,薪水

select ename,sal from emp;

3.查询所有岗位名称

select distinct job from emp ;
SELECT job from emp GROUP BY job;

4.查询所有薪水超过两千的员工信息

SELECT * from emp where sal>2000;

5.查询所有20部门的员工姓名，编号及薪水

select ename,empno,sal,deptno from emp where deptno=20;

6.查询所有没有奖金的员工信息

select * from emp where comm is null;

7.查询所有有奖金的员工信息

select * from emp where comm is  not null ;
select * from emp where comm>0;

8.查询最高领导的员工信息

select * from emp ORDER BY mgr desc LIMIT 0,1;

9.查询所有81年之后入职的员工信息

select * from emp where hiredate>='1981-12-31' ;
select * from emp where hiredate>='1982' ;

10.查询所有薪水在2000-4000范围内的员工信息

select * from emp where sal BETWEEN 2000 and 4000;

select * from emp where sal >=2000 and sal<= 4000;

11.查询所有部门编号是10或30的员工信息

select * from emp where deptno=10 or deptno=30;
select * from emp where deptno in (10,30);

12.查询所有20部门并且薪水超过2000的员工信息：

select * from emp where deptno=20 and sal>=2000;

13.查询所有薪水不在2000-4000范围内的员工信息

select * from emp where  not sal BETWEEN 2000 and 4000;

14.查询部门编号不是10，30的员工信息

select * from emp where not deptno <>10 or deptno<>30;

15.查询姓名里面包含ALL的员工姓名

select * from emp where ename like '%ALL%';

16.查询所有以”S”开头的同学

select * from emp where ename like 'S%';

17.查询第二个字母为A的员工姓名

select * from emp where ename like '_A%';

18.查询所有员工的编号、姓名、部门编号、职位、薪水，按照薪水降序排列

select empno,ename,deptno,job,sal from emp ORDER BY sal DESC;

19.查询所有员工信息，按照部门降序排列，部门内按照薪水升序排列

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

20.查询姓名中包含‘A’员工的姓名，编号，薪水，按照薪水降序排列

select  ename ,empno,sal from emp where ename like '%A%' ORDER BY sal desc;

21.查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列

select ename,empno,sal,comm,(sal+if(comm is null,0,comm))*12 as '年收入' from emp ORDER BY '年收入'>10000 ,'年收入' desc;  

22.查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列

select ename,empno,sal,comm,(sal+if(comm is null,0,comm))*12 as '年收入' from emp ORDER BY '年收入'>10000 ,'年收入' desc;