单表基本查询
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;