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

浅浅的花香味﹌ 2022-02-17 04:40 330阅读 0赞

### 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;