MYSQL复杂查询方法实例分析(mysql,开发技术)

这篇“MYSQL复杂查询方法实例分析”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“MYSQL复杂查询方法实例分析”文章吧。

建库建表插入数据

代码直接按顺序复制就可以

--建库CREATEDATABASE`emp`;--打开库USEemp;--建dept表CREATETABLE`dept`(`deptno`INT(2)NOTNULL,`dname`VARCHAR(14),`loc`VARCHAR(13),CONSTRAINTpk_deptPRIMARYKEY(deptno))ENGINE=InnoDBDEFAULTCHARSET=utf8;--键emp表CREATETABLE`emp`(`empno`int(4)NOTNULLPRIMARYKEY,`ename`VARCHAR(10),`job`VARCHAR(9),`mgr`int(4),`hiredate`DATE,`sal`float(7,2),`comm`float(7,2),`deptno`int(2),CONSTRAINTfk_deptnoFOREIGNKEY(deptno)REFERENCESdept(deptno))ENGINE=InnoDBDEFAULTCHARSET=utf8;--建salgrade表CREATETABLE`salgrade`(`grade`int,`losal`int,`hisal`int)ENGINE=InnoDBDEFAULTCHARSET=utf8;--插入数据INSERTINTOdeptVALUES(10,'ACCOUNTING','NEWYORK');INSERTINTOdeptVALUES(20,'RESEARCH','DALLAS');INSERTINTOdeptVALUES(30,'SALES','CHICAGO');INSERTINTOdeptVALUES(40,'OPERATIONS','BOSTON');INSERTINTOEMPVALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);INSERTINTOEMPVALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);INSERTINTOEMPVALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);INSERTINTOEMPVALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);INSERTINTOEMPVALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);INSERTINTOEMPVALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);INSERTINTOEMPVALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);INSERTINTOEMPVALUES(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);INSERTINTOEMPVALUES(7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);INSERTINTOEMPVALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);INSERTINTOEMPVALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);INSERTINTOEMPVALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);INSERTINTOEMPVALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);INSERTINTOEMPVALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);INSERTINTOSALGRADEVALUES(1,700,1200);INSERTINTOSALGRADEVALUES(2,1201,1400);INSERTINTOSALGRADEVALUES(3,1401,2000);INSERTINTOSALGRADEVALUES(4,2001,3000);INSERTINTOSALGRADEVALUES(5,3001,9999);

dept表：

emp表：

salgrade表：

测试题

1.列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

2. 列出公司各个工资等级雇员的数量、平均工资。

3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。

5. 列出所有员工的姓名、部门名称和工资。

6. 列出所有部门的详细信息和部门人数。

7. 列出各种工作的最低工资及从事此工作的雇员姓名。

8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。

9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。

10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。

12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%， 30部门增长30%，依次类推。

13. 列出至少有一个员工的所有部门的信息：

14. 列出薪金比SMITH对的所有员工：

15. 列出所有员工的姓名以及其直接上级的姓名：

16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号

21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工 资。

23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。

25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工 资。

29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道 销售部的部门编号。

30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

32. 查询dept表的结构

33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段

34. 检索emp表中有提成的员工姓名、月收入及提成。

答案不唯一，仅供参考

有点乱，直接粘过来的格式不一样大家将就一下，勉强还是能看清的

– 2. 列出公司各个工资等级雇员的数量、平均工资。

showtables;select*fromsalgrade;selects.grade,count(),avg(e.sal)fromempeleftjoinsalgradesone.salbetweens.losalands.hisalgroupbys.grade;

– 3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

selectename,sal,d.dname,d.deptnofromempeleftjoindeptdone.deptno=d.deptnowheree.sal>(selectmax(sal)fromempwheredeptno=30);

– 4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。

selectcount(),avg(sal),avg(year(now())-year(hiredate))fromempgroupbydeptno;

– 5. 列出所有员工的姓名、部门名称和工资。

SALESresearchaccountingselecte.ename,d.dname,e.salfromempeleftjoindeptdond.deptno=e.deptno;

– 6. 列出所有部门的详细信息和部门人数。

OPERATIONSselectd.,count(e.ename)fromdeptdleftjoinempeone.deptno=d.deptnogroupbyd.deptno;

– 7. 列出各种工作的最低工资及从事此工作的雇员姓名。

selecta.ename,t.fromempaleftjoin(selecte.job,min(e.sal)fromempegroupbye.job)tona.job=t.job;

– 8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。

–binary实现区分大小写–selectenamefromempwherejob=binary‘MANAGER';–selectbinary‘a'=‘a';–selectbinary‘a';–selectbinary‘A';select*fromempwherejob=binary‘MANAGER';selecta.mm,c.ename,c.job,b.dname,b.ccfrom(selectd.deptno,min(sal)mmfromempeleftjoindeptdone.deptno=d.deptnowherejob=‘MANAGER'groupbydeptno)aleftjoin(selectd.deptno,d.dname,count()ccfromempeleftjoindeptdone.deptno=d.deptnogroupbyd.deptno)bona.deptno=b.deptnoleftjoinempconc.sal=a.mmandb.deptno=c.deptno;

– 9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。

selectempno,ename,sal12,d.dnamefromempleftjoindeptdond.deptno=emp.deptnoorderbysal12asc;

– 10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

selecta.empno,a.ename,b.ename,b.salfromempaleftjoinempbona.mgr=b.empnowhereb.sal>3000anda.empno=7566;selecta.empno,a.ename,b.ename,b.salfromempaleftjoinempbona.mgr=b.empnowhereb.sal>3000;selecta.empno,a.ename,b.ename,b.salfromempaleftjoinempbona.mgr=b.empno;

– 11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。

selectd.dname,count(),sum(e.sal)fromempeleftjoindeptdone.deptno=d.deptnowhered.dnamelike‘%s%'groupbyd.deptno;select*fromemp;

– 12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长 20%，30部门增长30%，依次类推。

selectempno,ename,sal,sal+sal*(deptno/100)fromempwhereyear(curdate())-year(hiredate)>30oryear(hiredate)=2022;updateempsetsal=sal+sal*(deptno/100)whereyear(curdate())-year(hiredate)>30oryear(hiredate)=2022;select*fromemp;

– 13. 列出至少有一个员工的所有部门的信息

selectdistinctd.*fromdeptdjoinempeond.deptno=e.deptno;

– 14. 列出薪金比SMITH低的所有员工

select*fromempwheresal<(selectsalfromempwhereename=‘SMITH')

– 15. 列出所有员工的姓名以及其直接上级的姓名：

selecta.empno,a.ename,b.enamefromempaleftjoinempbona.mgr=b.empno;

– 16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

selecta.empno,a.ename,b.ename,d.dnamefromempaleftjoinempbona.mgr=b.empnoanda.hiredate<b.hiredateleftjoindeptdond.deptno=a.deptno;

– 17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select*fromdeptdleftjoinempeond.deptno=e.deptno;

– 18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

selecta.ename,a.job,b.dname,b.ccfromempajoin(selectd.deptno,d.dname,count()ccfromdeptdleftjoinempeond.deptno=e.deptnogroupbyd.deptno)bonb.deptno=a.deptnoanda.job=‘CLERK';

– 19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

selectjob,max(sal),min(sal),avg(sal),count()fromempwheresal>1500groupbyjob;

– 20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号

selectenamefromempwheredeptnoin(selectdeptnofromdeptwheredname=‘sales');selecte.enamefromempejoindeptdone.deptno=d.deptnoandd.dname=‘sales';

– 21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

selecta.ename,a.en,d.dname,a.sal,s.gradefrom(selecta.deptno,a.ename,b.enameen,a.salfromempajoinempbona.mgr=b.empnoanda.sal>(selectavg(sal)fromemp))aleftjoindeptdona.deptno=d.deptnoleftjoinsalgradesona.salbetweens.losalands.hisal;selecta.ename,b.enamefromempajoinempbona.mgr=b.empnoanda.sal>(selectavg(sal)fromemp);

– 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高 工资。

selecte.deptno,d.dname,avg(e.sal),max(e.sal),min(sal),count()fromdeptdjoinempeone.deptno=d.deptnogroupbye.deptno;

– 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

selecta.empno,a.ename,d.dname,b.enamefrom(select*fromempwheresal>(selectmin(sal)fromempwhereenamein(‘smith',‘allen')))aleftjoinempbona.mgr=b.empnoleftjoindeptdond.deptno=a.deptno;

– 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排 列。

selecta.empno,a.ename,a.sal12,b.empno,b.ename,b.sal12fromempaleftjoinempbona.mgr=b.empnoorderbyb.sal12;

– 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

selecta.empno,a.ename,b.ename,d.dnamefromempaleftjoinempbona.mgr=b.empnoanda.hiredate<b.hiredateleftjoindeptdond.deptno=a.deptno;

– 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

selectd.deptno,d.dname,count(e.ename),avg(sal)fromdeptdleftjoinempeond.deptno=e.deptnogroupbyd.deptno;

– 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

selecta.ename,a.job,b.dname,b.ccfromempajoin(selectd.deptno,d.dname,count()ccfromdeptdleftjoinempeond.deptno=e.deptnogroupbyd.deptno)bonb.deptno=a.deptnoanda.job=‘CLERK';selecte.deptno,count(e.deptno)from(selecta.deptno,a.ename,d.dname,s.gradefrom(selectdeptno,ename,salfromempwherejob=‘CLERK')aleftjoindeptdona.deptno=d.deptnoleftjoinsalgradesona.salbetweens.losalands.hisal)aaleftjoinempeonaa.deptno=e.deptnogroupbye.deptno;selectt1.,t2.deptcountfrom(selectd.deptno,e.ename,e.job,d.dname,s.gradefromempejoindeptdone.deptno=d.deptnojoinsalgradesone.salbetweens.losalands.hisalwheree.job=‘CLERK')t1join(selectdeptno,count()asdeptcountfromempgroupbydeptno)t2ont1.deptno=t2.deptno;

– 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、 平均工资。

selectjob,max(sal),min(sal),avg(sal),count()fromempwheresal>1500groupbyjob;

– 29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定 不知道销售部的部门编号。

selecte.ename,e.sal,e.hiredate,d.dnamefromempejoindeptdond.deptno=e.deptnoandd.dname=‘sales';

– 30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

selecta.ename,a.en,d.dname,a.sal,s.gradefrom(selecta.deptno,a.ename,b.enameen,a.salfromempajoinempbona.mgr=b.empnoanda.sal>(selectavg(sal)fromemp))aleftjoindeptdona.deptno=d.deptnoleftjoinsalgradesona.salbetweens.losalands.hisal;

– 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

createviewv1asselectb.ename,d.dname,a.ccfrom(selectdeptno,count(*)ccfromempgroupbydeptno)ajoin(selectename,deptnofromempwherejob=(selectjobfromempwhereename=‘scott'))bonb.deptno=a.deptnoleftjoindeptdond.deptno=b.deptno;select*fromv1;

– 32. 查询dept表的结构

descemp;describeemp;showcreatetableemp;showcolumnsfromemp;

– 33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段 is a 是oracle数据库

selectconcat(empno,ename,job)fromemp;selectconcat_ws(‘-',empno,ename,job)fromemp;selectdistinctjobfromemp;selectgroup_concat(distinctjob)fromemp;selectgroup_concat(distinctename)fromemp;selectgroup_concat(distinctjoborderbyjobascseparator‘=')fromemp;

– 34. 检索emp表中有提成的员工姓名、月收入及提成。

selectename,sal,commfromempwherecommisnotnull;selectename,sal,commfromempwherecommisnotnullandcomm>0;

以上就是关于“MYSQL复杂查询方法实例分析”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。