数据库常用表操作SQL语句案例

1. 简单SQL查询语句

1. 案例：查询没有上级领导的员工的编号，姓名，工资
   select empno,ename,sal from emp where mgr is null;
2. 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
   select ename,job,sal,comm from emp where comm=0 or comm is null;
3. 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
   select empno,ename,job,comm from emp where comm>0;
4. 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
   select ename,sal,mgr from emp where mgr is not null;
5. 案例：查询emp表中名字以‘S’开头的所有员工的姓名
   select ename from emp where ename like ‘s%’;
6. 案例：查询emp表中名字的最后一个字符是’S’的员工的姓名
   select ename from emp where ename like ‘%s’;
7. 案例：查询倒数的第2个字符是‘E’的员工的姓名
   select ename from emp where ename like ‘%e_’;
8. 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
   select ename from emp where ename like ‘%n__’;
9. 案例：查询emp表中员工的名字中包含‘A’的员工的姓名
   select ename from emp where ename like ‘%a%’;
10. 案例：查询emp表中名字不是以’K’开头的员工的所有信息
    select * from emp where ename not like ‘k%’;
11. 案例：查询emp表中名字中不包含‘A’的所有员工的信息
    select * from emp where ename not like ‘%a%’;
12. 案例：做文员的员工人数（job 中 含有 CLERK 的）
    select count(*) from emp where job=‘clerk’;
13. 案例：销售人员 job: SALESMAN 的最高薪水
    select max(sal) from emp where job=‘salesman’;
14. 案例：最早和最晚入职时间
    select min(hiredate),max(hiredate) from emp;
15. 案例：查询类别 163的商品总库存量
    select sum(num) from t_item where category_id=163;
16. 案例：查询 类别 163 的商品
    select * from t_item where category_id=163;
17. 案例：查询商品价格不大于100的商品名称列表
    select title from t_item where price<=100;
18. 案例：查询品牌是联想,且价格在40000以上的商品名称和价格
    select title,price from t_item where title like ‘%联想%’ and price>40000;
19. 案例：查询品牌是三木,或价格在50以下的商品名称和价格
    select title,price from t_item where title like ‘%三木%’ or price<50;
20. 案例：查询品牌是三木、广博、齐心的商品名称和价格
    select title,price from t_item where title like ‘%三木%’ or title like ‘%广博%’ or title like ‘%齐心%’;
21. 案例：查询品牌不是联想、戴尔的商品名称和价格
    select title,price from t_item where title not like ‘%联想%’ and title not like ‘%戴尔%’;
22. 案例：查找品牌是联想且价格大于10000的名称
    select title from t_item where title like ‘%联想%’ and price>10000;
23. 案例：查询联想或戴尔的电脑名称列表
    select title from t_item where title like ‘%联想%’ or title like ‘%戴尔%’;
24. 案例：查询卖点含有’赠’产品名称
    select title from t_item where sell_point like ‘%赠%’;
25. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
    select empno,ename,job,sal from emp where sal between 1000 and 2000;
26. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
    select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
27. 案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
    select empno,ename,job,sal from emp where ename like ‘%e%’ and job!=‘manager’;
28. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
    select empno,ename,deptno from emp where deptno in(10,20);
29. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
    select empno,eanme,job,comm from emp where comm=0 or comm is null or ename not like ‘%t_’;
30. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
    select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
31. 案例：查询不是30号部门的员工的所有信息
    select * from emp where deptno!=30;
32. 案例：查询奖金不为空的员工的所有信息
    select * from emp where comm is not null;
33. 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
    select empno,ename,job from emp order by empno desc;
34. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
    select ename,job,sal from emp where deptno in(10,30) order by sal;
35. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
    select * from emp order by deptno,empno desc;
36. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
    select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;
37. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
    select empno,ename,sal,comm from emp where ename not like ‘%s%’ order by sal,empno desc;
38. 案例：统计emp表中员工的总数量
    select count(*) from emp;
39. 案例：统计emp表中获得奖金的员工的数量
    select count(*) from emp where comm>0;
40. 案例：求出emp表中所有的工资累加之和
    select sum(sal) from emp;
41. 案例：求出emp表中所有的奖金累加之和
    select sum(comm) from emp;
42. 案例：求出emp表中员工的平均工资
    select avg(sal) from emp;
43. 案例：求出emp表中员工的平均奖金
    select avg(comm) from emp;
44. 案例：求出emp表中员工的最高工资
    select max(sal) from emp;
45. 案例：求出emp表中员工编号的最大值
    select max(empno) from emp;
46. 案例：查询emp表中员工的最低工资。
    select min(sal) from emp;
47. 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
    select count(*) 总人数,sum(sal) 工资总和,avg(sal) 平均工资,max(comm) 最高奖金, min(comm) 最低奖金 from emp;

2. 嵌套查询

1. 每个部门的人数，根据人数降序排序
   select deptno,count(*) c from emp
   group by deptno order by c desc;

2. 每个部门中，每个主管的手下人数
   select deptno,mgr,count(*) from emp
   where mgr is not null
   group by deptno,mgr;

3. 每种工作的平均工资
   select job,avg(sal) from emp group by job;

4. 每年的入职人数
   select extract(year from hiredate) year,count(*) from emp group by year;

5. 少于等于3个人的部门信息

   select d.*
   from emp e right join dept d
   on e.deptno=d.deptno
   group by deptno
   having count(e.ename)<=3;

6. 拿最低工资的员工信息
   select * from emp where sal=(select min(sal) from emp);

7. 只有一个下属的主管信息
   select mgr from emp where mgr is not null group by mgr having count()=1;
   select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count()=1);

8. 每月发工资最多的部门信息

   • 得到最高的工资总和
     select sum(sal) s from emp group by deptno order by s desc limit 0,1;
   • 通过工资总和查部门的编号
     select deptno from emp group by deptno having sum(sal)=(select sum(sal) s from emp group by deptno order by s desc limit 0,1);
   • 通过部门编号查部门信息
     select * from dept where deptno in (上面一坨);

9. 下属最多的人，查询其个人信息

   • 得到下属最多的人数
     select count(*) c from emp group by mgr order by c desc limit 0,1;
   • 通过人数找mgr
     select mgr from emp group by mgr having count()=(select count() c from emp group by mgr order by c desc limit 0,1);
   • 通过mgr找个人信息
     select * from emp where empno in(上面一坨);

10. 拿最高工资员工的同事信息
    select * from emp where deptno = (select deptno from emp where sal=(select max(sal) from emp)) and sal !=(select max(sal) from emp);

11. 和最后入职的员工在同一部门的员工信息 实现流程和第十题一样
    select max(hiredate) from emp;

    select deptno from emp where hiredate=(select max(hiredate) from emp);

    select * from emp where deptno=(select deptno from emp where hiredate=(select max(hiredate) from emp)) and hiredate!=(select max(hiredate) from emp);

12. 查询平均工资高于20号平均工资的部门信息
    select avg(sal) from emp where deptno=20;

    select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20);

    select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20));

13. 查询员工信息和员工对应的部门名称
    select e.*,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;

14. 查询员工信息，部门名称，所在城市
    select e.*,d.dname,d.loc
    from emp e join dept d
    on e.deptno=d.deptno;

15. 查询Dallas市所有的员工信息
    select e.*
    from emp e join dept d
    on e.deptno=d.deptno
    where d.loc=‘dallas’;

16. 计算每个城市的员工数量
    select d.loc,count(e.empno)
    from emp e right join dept d
    on e.deptno=d.deptno group by d.loc;

17. 查询员工信息和他的主管姓名 凡是自关联 把一张表当成两张表
    select e.ename,m.ename
    from emp e join emp m
    on e.mgr = m.empno;

18. 员工信息，员工主管名字，部门名
    select e.ename,m.ename,d.dname
    from emp e join emp m
    on e.mgr = m.empno
    join dept d
    on e.deptno=d.deptno;

19. 员工名和他所在部门名
    select e.ename,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;

20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
    select e.ename,m.empno,m.ename,m.job,m.sal
    from emp e left join emp m
    on e.mgr=m.empno;

21. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
    select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
    from emp e left join dept d
    on e.deptno=d.deptno
    where e.ename not like ‘%k%’;

22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
    select d.*,e.empno,e.ename,e.job,e.sal
    from emp e right join dept d
    on e.deptno=d.deptno;