MYSQL復(fù)雜查詢(xún)方法實(shí)例分析

這篇“MYSQL復(fù)雜查詢(xún)方法實(shí)例分析”文章的知識(shí)點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來(lái)看看這篇“MYSQL復(fù)雜查詢(xún)方法實(shí)例分析”文章吧。

建庫(kù)建表插入數(shù)據(jù)

代碼直接按順序復(fù)制就可以

-- 建庫(kù)
CREATE DATABASE `emp`;
-- 打開(kāi)庫(kù)
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;
-- 插入數(shù)據(jù)
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表：

測(cè)試題

1.列出與“SCOTT”從事相同工作的所有員工及部門(mén)名稱(chēng)，部門(mén)人數(shù)。

2. 列出公司各個(gè)工資等級(jí)雇員的數(shù)量、平均工資。

3. 列出薪金高于在部門(mén)30工作的所有員工的薪金的員工姓名和薪金、部門(mén)名稱(chēng)。

4. 列出在每個(gè)部門(mén)工作的員工數(shù)量、平均工資和平均服務(wù)期限。

5. 列出所有員工的姓名、部門(mén)名稱(chēng)和工資。

6. 列出所有部門(mén)的詳細(xì)信息和部門(mén)人數(shù)。

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

8. 列出各個(gè)部門(mén)的MANAGER(經(jīng)理)的最低薪金、姓名、部門(mén)名稱(chēng)、部門(mén)人數(shù)。

9. 列出所有員工的年工資，所在部門(mén)名稱(chēng)，按年薪從低到高排序。

10. 查出某個(gè)員工的上級(jí)主管及所在部門(mén)名稱(chēng)，并要求出這些主管中的薪水超過(guò)3000

11. 求出部門(mén)名稱(chēng)中，帶‘S’字符的部門(mén)員工的、工資合計(jì)、部門(mén)人數(shù)。

12. 給任職日期超過(guò)30年或者在87年雇傭的雇員加薪，加薪原則：10部門(mén)增長(zhǎng)10%，20部門(mén)增長(zhǎng)20%， 30部門(mén)增長(zhǎng)30%，依次類(lèi)推。

13. 列出至少有一個(gè)員工的所有部門(mén)的信息：

14. 列出薪金比SMITH對(duì)的所有員工：

15. 列出所有員工的姓名以及其直接上級(jí)的姓名：

16. 列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)、姓名，部門(mén)名稱(chēng)

17. 列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息，同時(shí)列出那些沒(méi)有員工的部門(mén)

18. 列出所有"CLERK(職員)"的姓名以及部門(mén)名稱(chēng)，部門(mén)的人數(shù)

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

20. 列出在部門(mén)"SALES"工作的員工的姓名，假定不知道銷(xiāo)售部的部門(mén)編號(hào)

21. 列出薪金高于公司平均薪金的所有員工，所在部門(mén)，上級(jí)領(lǐng)導(dǎo)，公司的工資等級(jí)

22. 列出至少有一個(gè)員工的所有部門(mén)編號(hào)、名稱(chēng)，并統(tǒng)計(jì)出這些部門(mén)的平均工資、最低工資、最高工 資。

23. 列出薪金比“SMITH”或“ALLEN”多的所有員工的編號(hào)、姓名、部門(mén)名稱(chēng)、其領(lǐng)導(dǎo)姓名。

24. 列出所有員工的編號(hào)、姓名及其直接上級(jí)的編號(hào)、姓名，顯示的結(jié)果按領(lǐng)導(dǎo)年工資的降序排列。

25. 列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)、姓名、部門(mén)名稱(chēng)、部門(mén)位置、部門(mén)人數(shù)。

26. 列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息（數(shù)量、平均工資），同時(shí)列出那些沒(méi)有員工的部門(mén)。

27. 列出所有“CLERK”（辦事員）的姓名及其部門(mén)名稱(chēng)，部門(mén)的人數(shù)，工資等級(jí)。

28. 列出最低薪金大于1500的各種工作及此從事此工作的全部雇員人數(shù)及所在部門(mén)名稱(chēng)、位置、平均工 資。

29. 列出在部門(mén)“SALES”（銷(xiāo)售部）工作的員工的姓名、基本工資、雇傭日期、部門(mén)名稱(chēng)，假定不知道 銷(xiāo)售部的部門(mén)編號(hào)。

30. 列出薪金高于公司平均薪金的所有員工，所在部門(mén)，上級(jí)領(lǐng)導(dǎo)，公司的工資等級(jí)。

31. 列出與“SCOTT”從事相同工作的所有員工及部門(mén)名稱(chēng)，部門(mén)人數(shù)。

32. 查詢(xún)dept表的結(jié)構(gòu)

33. 檢索emp表，用is a 這個(gè)字符串來(lái)連接員工姓名和工種兩個(gè)字段

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

答案不唯一，僅供參考

有點(diǎn)亂，直接粘過(guò)來(lái)的格式不一樣大家將就一下，勉強(qiáng)還是能看清的

– 2. 列出公司各個(gè)工資等級(jí)雇員的數(shù)量、平均工資。

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. 列出薪金高于在部門(mén)30工作的所有員工的薪金的員工姓名和薪金、部門(mén)名稱(chēng)。

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. 列出在每個(gè)部門(mén)工作的員工數(shù)量、平均工資和平均服務(wù)期限。

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

– 5. 列出所有員工的姓名、部門(mén)名稱(chēng)和工資。

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

– 6. 列出所有部門(mén)的詳細(xì)信息和部門(mén)人數(shù)。

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. 列出各個(gè)部門(mén)的MANAGER(經(jīng)理)的最低薪金、姓名、部門(mén)名稱(chēng)、部門(mén)人數(shù)。

– binary 實(shí)現(xiàn)區(qū)分大小寫(xiě) – select ename from emp where job = binary ‘MANAGER'; – select binary ‘a(chǎn)' = ‘a(chǎn)'; – select binary ‘a(chǎn)'; – 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. 列出所有員工的年工資，所在部門(mén)名稱(chēng)，按年薪從低到高排序。

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

– 10. 查出某個(gè)員工的上級(jí)主管及所在部門(mén)名稱(chēng)，并要求出這些主管中的薪水超過(guò)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. 求出部門(mén)名稱(chēng)中，帶‘S’字符的部門(mén)員工的、工資合計(jì)、部門(mén)人數(shù)。

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. 給任職日期超過(guò)30年或者在87年雇傭的雇員加薪，加薪原則：10部門(mén)增長(zhǎng)10%，20部門(mén)增長(zhǎng) 20%，30部門(mén)增長(zhǎng)30%，依次類(lèi)推。

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. 列出至少有一個(gè)員工的所有部門(mén)的信息

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. 列出所有員工的姓名以及其直接上級(jí)的姓名：

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

– 16. 列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)、姓名，部門(mén)名稱(chēng)

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. 列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息，同時(shí)列出那些沒(méi)有員工的部門(mén)

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

– 18. 列出所有"CLERK(職員)"的姓名以及部門(mén)名稱(chēng)，部門(mén)的人數(shù)

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的各種工作以及從事此工作的全部雇員人數(shù)

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

– 20. 列出在部門(mén)"SALES"工作的員工的姓名，假定不知道銷(xiāo)售部的部門(mén)編號(hào)

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. 列出薪金高于公司平均薪金的所有員工，所在部門(mén)，上級(jí)領(lǐng)導(dǎo)，公司的工資等級(jí)

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. 列出至少有一個(gè)員工的所有部門(mén)編號(hào)、名稱(chēng)，并統(tǒng)計(jì)出這些部門(mén)的平均工資、最低工資、最高 工資。

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”多的所有員工的編號(hào)、姓名、部門(mén)名稱(chēng)、其領(lǐng)導(dǎo)姓名。

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

– 24. 列出所有員工的編號(hào)、姓名及其直接上級(jí)的編號(hào)、姓名，顯示的結(jié)果按領(lǐng)導(dǎo)年工資的降序排 列。

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. 列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)、姓名、部門(mén)名稱(chēng)、部門(mén)位置、部門(mén)人數(shù)。

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. 列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息（數(shù)量、平均工資），同時(shí)列出那些沒(méi)有員工的部門(mén)。

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”（辦事員）的姓名及其部門(mén)名稱(chēng)，部門(mén)的人數(shù)，工資等級(jí)。

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的各種工作及此從事此工作的全部雇員人數(shù)及所在部門(mén)名稱(chēng)、位置、 平均工資。

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

– 29. 列出在部門(mén)“SALES”（銷(xiāo)售部）工作的員工的姓名、基本工資、雇傭日期、部門(mén)名稱(chēng)，假定 不知道銷(xiāo)售部的部門(mén)編號(hào)。

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. 列出薪金高于公司平均薪金的所有員工，所在部門(mén)，上級(jí)領(lǐng)導(dǎo)，公司的工資等級(jí)。

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”從事相同工作的所有員工及部門(mén)名稱(chēng)，部門(mén)人數(shù)。

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. 查詢(xún)dept表的結(jié)構(gòu)

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

– 33. 檢索emp表，用is a 這個(gè)字符串來(lái)連接員工姓名和工種兩個(gè)字段 is a 是oracle數(shù)據(jù)庫(kù)

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;

以上就是關(guān)于“MYSQL復(fù)雜查詢(xún)方法實(shí)例分析”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對(duì)大家有幫助，若想了解更多相關(guān)的知識(shí)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。