扫码加入数据分析学习群

1. 查询指定列：查询emp表中ename,job,sal

select ename,job,sal from emp ;

2. 设置别名：查询每位员工调整后的薪资(基本工资+1000)

select ename,job,sal+1000 薪资 from emp ;

3. 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪

select ename,empno,sal*12 年薪 from emp ;

4. 查询不重复的数据：查询emp表中有哪些部门

select distinct deptno from emp ;

5. 查询基本工资大于等于2000小于等于3000的员工信息

①select * from emp where sal between 2000 and 3000;

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

6.查询10号部门和20号部门中sal低于2000的员工信息-- 优先级是not、and、 or，用括号改变优先级。

①select * from emp where sal<2000 and deptno in(10,20);

②select * from emp where sal<2000 and (deptno=10 or deptno=20);

7. 练习：查询salesman的所属部门：姓名,职位,所在部门

select ename,job,deptno from emp where job="salesman";

8. 查询mgr为空的记录

select *from emp where mgr is null;

9. 练习：查询comm不为空的记录

select *from emp where comm is not null;

10.查询姓名以a开头的员工信息

select *from emp where ename like "a%";

11. 查询姓名中包含a的员工信息

select *from emp where ename like "%a%";

12.查询姓名中第二个字符为a的员工信息

select *from emp where ename like "_a%";

13. 练习：查询员工姓名中不包含s的员工信息

select *from emp where ename not like "%s%";

14.单字段排序：查询所有员工信息按sal降序(desc)显示

select *from emp order by sal desc;

15.多字段排序：查询所有员工信息按deptno升序(asc)、sal降序显示

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

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

16. 查询基本工资最高的前5位员工

①select *from emp order by sal desc limit 5;

②select * from emp order by sal desc limit 0,5;

17. 查询基本工资第6到10名的员工（ limit [相对第一行的偏移量], 行数）

select * from emp order by sal desc limit 5,5;

18. 练习：查询最后入职的5位员工

select * from emp order by hiredate desc limit 5;

聚合运算(会自动忽略空值)

19.查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和

select count(empno) 员工总数,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 工资总和

from emp;

分组查询(分组之后只能显示（select）分组字段、聚合运算以及与分组字段一一对应的字段)

20.查询各部门的平均工资

select deptno,avg(sal) from emp group by deptno;

21. 查询各部门不同职位的平均工资

select deptno,job,avg(sal) from emp group by deptno,job order by deptno asc,avg(sal) desc;

22.练习：查询各部门的员工数

select deptno,count(empno) from emp group by deptno order by deptno asc;

23.练习：查询各部门不同职位的人数

select deptno,job, count(job) from emp group by deptno,job order by deptno asc;

分组后筛选(having是对分组后的再次筛选）

24. 查询各部门clerk的平均工资

①select deptno,job,avg(sal) from emp where job='clerk' group by deptno,job order by deptno asc;

②select deptno,job,avg(sal) from emp group by deptno,job having job='clerk' order by deptno asc;

25. 查询平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;