MYSQL復雜查詢方法實例分析

這篇“MYSQL復雜查詢方法實例分析”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MYSQL復雜查詢方法實例分析”文章吧。

建庫建表插入數據

代碼直接按順序復制就可以

-- 建庫
CREATE DATABASE `emp`;
-- 打開庫
USE emp;
-- 建dept表
CREATE TABLE `dept`( `deptno` INT(2) NOT NULL, `dname` VARCHAR(14), `loc` VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 鍵emp表
CREATE TABLE `emp` ( `empno` int(4) NOT NULL PRIMARY KEY, `ename` VARCHAR(10), `job` VARCHAR(9), `mgr` int(4), `hiredate` DATE, `sal` float(7,2), `comm` float(7,2), `deptno` int(2), CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 建salgrade表
CREATE TABLE `salgrade` ( `grade` int, `losal` int, `hisal` int ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入數據
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (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. 列出公司各個工資等級雇員的數量、平均工資。

show tables; select * from salgrade; select s.grade,count(),avg(e.sal) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by s.grade ;

– 3. 列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金、部門名稱。

select ename,sal,d.dname,d.deptno from emp e left join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);

– 4. 列出在每個部門工作的員工數量、平均工資和平均服務期限。

select count(),avg(sal),avg(year(now())-year(hiredate)) from emp group by deptno;

– 5. 列出所有員工的姓名、部門名稱和工資。

SALES research accounting select e.ename,d.dname,e.sal from emp e left join dept d on d.deptno = e.deptno;

– 6. 列出所有部門的詳細信息和部門人數。

OPERATIONS select d.,count(e.ename) from dept d left join emp e on e.deptno = d.deptno group by d.deptno;

– 7. 列出各種工作的最低工資及從事此工作的雇員姓名。

select a.ename,t. from emp a left join (select e.job,min(e.sal) from emp e group by e.job) t on a.job = t.job;

– 8. 列出各個部門的MANAGER(經理)的最低薪金、姓名、部門名稱、部門人數。

– binary 實現區分大小寫 – select ename from emp where job = binary ‘MANAGER'; – select binary ‘a' = ‘a'; – select binary ‘a'; – select binary ‘A'; select * from emp where job = binary ‘MANAGER'; select a.mm,c.ename,c.job,b.dname,b.cc from (select d.deptno,min(sal) mm from emp e left join dept d on e.deptno = d.deptno where job = ‘MANAGER' group by deptno) a left join (select d.deptno,d.dname,count() cc from emp e left join dept d on e.deptno = d.deptno group by d.deptno) b on a.deptno = b.deptno left join emp c on c.sal = a.mm and b.deptno = c.deptno ;

– 9. 列出所有員工的年工資，所在部門名稱，按年薪從低到高排序。

select empno,ename,sal12,d.dname from emp left join dept d on d.deptno = emp.deptno order by sal12 asc;

– 10. 查出某個員工的上級主管及所在部門名稱，并要求出這些主管中的薪水超過3000

select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000 and a.empno = 7566; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno;

– 11. 求出部門名稱中，帶‘S’字符的部門員工的、工資合計、部門人數。

select d.dname,count(),sum(e.sal) from emp e left join dept d on e.deptno = d.deptno where d.dname like ‘%s%' group by d.deptno; select * from emp;

– 12. 給任職日期超過30年或者在87年雇傭的雇員加薪，加薪原則：10部門增長10%，20部門增長 20%，30部門增長30%，依次類推。

select empno,ename,sal,sal+sal*(deptno/100) from emp where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; update emp set sal = sal+sal*(deptno/100) where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; select * from emp;

– 13. 列出至少有一個員工的所有部門的信息

select distinct d.* from dept d join emp e on d.deptno = e.deptno;

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

select * from emp where sal < (select sal from emp where ename = ‘SMITH')

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

select a.empno,a.ename,b.ename from emp a left join emp b on a.mgr = b.empno;

– 16. 列出受雇日期早于其直接上級的所有員工的編號、姓名，部門名稱

select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno;

– 17. 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門

select * from dept d left join emp e on d.deptno = e.deptno;

– 18. 列出所有"CLERK(職員)"的姓名以及部門名稱，部門的人數

select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count() cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = ‘CLERK';

– 19. 列出最低薪金大于1500的各種工作以及從事此工作的全部雇員人數

select job,max(sal),min(sal),avg(sal),count() from emp where sal>1500 group by job;

– 20. 列出在部門"SALES"工作的員工的姓名，假定不知道銷售部的部門編號

select ename from emp where deptno in (select deptno from dept where dname=‘sales'); select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname=‘sales';

– 21. 列出薪金高于公司平均薪金的所有員工，所在部門，上級領導，公司的工資等級

select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal; select a.ename,b.ename from emp a join emp b on a.mgr = b.empno and a.sal> (select avg(sal) from emp);

– 22. 列出至少有一個員工的所有部門編號、名稱，并統計出這些部門的平均工資、最低工資、最高 工資。

select e.deptno,d.dname,avg(e.sal),max(e.sal),min(sal),count() from dept d join emp e on e.deptno = d.deptno group by e.deptno;

– 23. 列出薪金比“SMITH”或“ALLEN”多的所有員工的編號、姓名、部門名稱、其領導姓名。

select a.empno,a.ename,d.dname,b.ename from (select * from emp where sal >(select min(sal) from emp where ename in (‘smith',‘allen'))) a left join emp b on a.mgr = b.empno left join dept d on d.deptno = a.deptno;

– 24. 列出所有員工的編號、姓名及其直接上級的編號、姓名，顯示的結果按領導年工資的降序排 列。

select a.empno,a.ename,a.sal12,b.empno,b.ename,b.sal12 from emp a left join emp b on a.mgr = b.empno order by b.sal12;

– 25. 列出受雇日期早于其直接上級的所有員工的編號、姓名、部門名稱、部門位置、部門人數。

select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno;

– 26. 列出部門名稱和這些部門的員工信息（數量、平均工資），同時列出那些沒有員工的部門。

select d.deptno,d.dname,count(e.ename),avg(sal) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;

– 27. 列出所有“CLERK”（辦事員）的姓名及其部門名稱，部門的人數，工資等級。

select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count() cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = ‘CLERK'; select e.deptno,count(e.deptno) from (select a.deptno,a.ename,d.dname,s.grade from (select deptno,ename,sal from emp where job=‘CLERK') a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal) aa left join emp e on aa.deptno = e.deptno group by e.deptno; select t1.,t2.deptcount from (select d.deptno,e.ename,e.job,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal where e.job = ‘CLERK') t1 join (select deptno, count() as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;

– 28. 列出最低薪金大于1500的各種工作及此從事此工作的全部雇員人數及所在部門名稱、位置、 平均工資。

select job,max(sal),min(sal),avg(sal),count() from emp where sal>1500 group by job;

– 29. 列出在部門“SALES”（銷售部）工作的員工的姓名、基本工資、雇傭日期、部門名稱，假定 不知道銷售部的部門編號。

select e.ename,e.sal,e.hiredate,d.dname from emp e join dept d on d.deptno = e.deptno and d.dname=‘sales';

– 30. 列出薪金高于公司平均薪金的所有員工，所在部門，上級領導，公司的工資等級。

select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal;

– 31. 列出與“SCOTT”從事相同工作的所有員工及部門名稱，部門人數。

create view v1 as select b.ename,d.dname,a.cc from (select deptno,count(*) cc from emp group by deptno) a join (select ename,deptno from emp where job = (select job from emp where ename = ‘scott')) b on b.deptno=a.deptno
left join dept d on d.deptno = b.deptno; select * from v1;

– 32. 查詢dept表的結構

desc emp; describe emp; show create table emp; show columns from emp;

– 33. 檢索emp表，用is a 這個字符串來連接員工姓名和工種兩個字段 is a 是oracle數據庫

select concat(empno,ename,job) from emp; select concat_ws(‘-',empno,ename,job) from emp; select distinct job from emp; select group_concat(distinct job) from emp; select group_concat(distinct ename) from emp; select group_concat(distinct job order by job asc separator ‘=') from emp;

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

select ename,sal,comm from emp where comm is not null; select ename,sal,comm from emp where comm is not null and comm>0;

以上就是關于“MYSQL復雜查詢方法實例分析”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。