Beispielanalyse einer komplexen MySQL-Abfragemethode

Erstellen Sie eine Datenbank, erstellen Sie eine Tabelle und fügen Sie Daten ein.

Der Code kann direkt in der Reihenfolge kopiert werden.

Testfragen

1. Listen Sie alle Mitarbeiter und Abteilungsnamen sowie die Anzahl der Abteilungsmitarbeiter auf, die mit der gleichen Arbeit wie „SCOTT“ beschäftigt sind.

2. Listen Sie die Anzahl und das Durchschnittsgehalt der Mitarbeiter in jeder Gehaltsstufe des Unternehmens auf.

3. Listen Sie die Namen, Gehälter und Abteilungsnamen der Mitarbeiter auf, deren Gehalt höher ist als das Gehalt aller in Abteilung 30 tätigen Mitarbeiter.

4. Geben Sie die Anzahl der in jeder Abteilung tätigen Mitarbeiter, das durchschnittliche Gehalt und die durchschnittliche Betriebszugehörigkeit an.

5. Listen Sie die Namen, Abteilungsnamen und Gehälter aller Mitarbeiter auf. 6. Listen Sie alle Abteilungsdetails und die Anzahl der Abteilungsmitarbeiter auf.

7. Listen Sie die Mindestlöhne für verschiedene Jobs und die Namen der Mitarbeiter auf, die diese Jobs ausführen.

8. Geben Sie das Mindestgehalt, den Namen, den Abteilungsnamen und die Anzahl der Personen im MANAGER jeder Abteilung an.

9. Listen Sie das Jahresgehalt und den Abteilungsnamen aller Mitarbeiter auf, sortiert nach Jahresgehalt von niedrig nach hoch.

10. Finden Sie den Namen des Vorgesetzten und der Abteilung eines Mitarbeiters heraus und verlangen Sie, dass das Gehalt dieser Vorgesetzten 3.000 übersteigt.

11. Finden Sie das Gesamtgehalt der Abteilungsmitarbeiter mit „S“-Zeichen im Abteilungsnamen und der Anzahl der Abteilungsmitarbeiter .

12. Um das Gehalt von Mitarbeitern zu erhöhen, die seit mehr als 30 Jahren beschäftigt sind oder im Jahr 1987 eingestellt wurden, gilt das Gehaltserhöhungsprinzip: 10 % Erhöhung für Abteilung 10, 20 % Erhöhung für Abteilung 20, 30 % Erhöhung für Abteilung 30 und so weiter.

13. Listen Sie die Informationen aller Abteilungen mit mindestens einem Mitarbeiter auf:

14. Listen Sie alle Mitarbeiter auf, deren Gehalt höher ist als SMITH:

15. Listen Sie die Namen aller Mitarbeiter und deren direkten Vorgesetzten auf:

16. Listen Sie die Nummern, Namen und Abteilungsnamen aller Mitarbeiter auf, deren Beschäftigungsdatum vor dem ihrer unmittelbaren Vorgesetzten liegt

17. Listen Sie die Abteilungsnamen und Mitarbeiterinformationen dieser Abteilungen auf und listen Sie auch die Abteilungen auf, die keine Mitarbeiter haben

18. Listen Sie die Namen aller „CLERK (Mitarbeiter)“ und Abteilungsnamen sowie die Anzahl der Personen in der Abteilung auf.

19 Listen Sie die verschiedenen Jobs mit einem Mindestgehalt von mehr als 1.500 auf und geben Sie die Anzahl aller damit beschäftigten Mitarbeiter an Job

20. Listen Sie die Jobs in der Abteilung „VERKAUF“ auf. Die Namen der Mitarbeiter, sofern die Abteilungsnummer der Vertriebsabteilung nicht bekannt ist.

21 Listen Sie alle Mitarbeiter auf, deren Gehalt über dem Durchschnittsgehalt des Unternehmens liegt , Vorgesetzte und die Gehaltsstufe des Unternehmens

22. Listen Sie mindestens einen Mitarbeiter auf. Es werden alle Abteilungsnummern und -namen sowie das Durchschnittsgehalt, das Mindestgehalt und das Höchstgehalt dieser Abteilungen berechnet.

23. Listen Sie die Nummern, Namen, Abteilungsnamen und Namen ihrer Führungskräfte aller Mitarbeiter auf, deren Gehalt mehr als „SMITH“ oder „ALLEN“ beträgt.

24. Listen Sie die Nummern und Namen aller Mitarbeiter sowie die Nummern und Namen ihrer unmittelbaren Vorgesetzten auf. Die angezeigten Ergebnisse sind in absteigender Reihenfolge des Jahresgehalts des Leiters angeordnet.

25. Listen Sie die Nummer, den Namen, den Abteilungsnamen, den Abteilungsstandort und die Anzahl der Abteilungsmitarbeiter auf, deren Beschäftigungsdatum vor dem ihrer unmittelbaren Vorgesetzten liegt.

26. Listen Sie die Abteilungsnamen und Mitarbeiterinformationen (Anzahl, Durchschnittsgehalt) dieser Abteilungen auf und listen Sie auch die Abteilungen ohne Mitarbeiter auf.

27. Listen Sie die Namen aller „CLERK“ (Angestellten) und deren Abteilungsnamen, Anzahl der Personen in der Abteilung und Gehaltsstufe auf.

28. Listen Sie die verschiedenen Jobs mit einem Mindestgehalt von mehr als 1.500 auf und geben Sie die Anzahl der in diesem Job beschäftigten Mitarbeiter sowie den Namen der Abteilung, den Standort und das Durchschnittsgehalt an.

29. Listen Sie die Namen, Grundgehälter, Beschäftigungsdaten und Abteilungsnamen der Mitarbeiter auf, die in der Abteilung „VERKAUF“ (Verkaufsabteilung) tätig sind. Es wird davon ausgegangen, dass die Abteilungsnummer der Vertriebsabteilung nicht bekannt ist.

30. Listen Sie alle Mitarbeiter auf, deren Gehalt über dem Durchschnittsgehalt des Unternehmens liegt, sowie ihre Abteilungen, Vorgesetzten und die Gehaltsstufe des Unternehmens.

31. Listen Sie alle Mitarbeiter und Abteilungsnamen sowie die Anzahl der Abteilungsmitarbeiter auf, die denselben Job wie „SCOTT“ ausüben.

32. Fragen Sie die Struktur der Abteilungstabelle ab

33 und verwenden Sie die Zeichenfolge a, um die beiden Felder „Mitarbeitername“ und „Art der Arbeit“ zu verbinden.

34 Mitarbeiter mit Provisionen in der emp-Tabelle.

Die Antwort ist nicht eindeutig, sie dient nur als Referenz

Es ist etwas chaotisch, das Format zum direkten Einfügen ist anders, begnügen wir uns damit, aber es ist immer noch klar zu sehen

– Listen Sie die Anzahl der Mitarbeiter in jeder Gehaltsstufe des Unternehmens sowie das Durchschnittsgehalt auf.

-- 建库
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);

3. Listen Sie die Namen, Gehälter und Abteilungsnamen aller Mitarbeiter auf, deren Gehalt höher als 30 Mitarbeiter in der Abteilung ist.

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 ;

– Geben Sie die Anzahl der Mitarbeiter in jeder Abteilung, das durchschnittliche Gehalt und die durchschnittliche Betriebszugehörigkeit an.

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

– Listen Sie alle Namen, Abteilungsnamen und Gehälter aller Mitarbeiter auf.

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

– Listen Sie alle Abteilungsdetails und die Anzahl der Abteilungsmitarbeiter auf.

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

– Listen Sie die Mindestlöhne für verschiedene Jobs und die Namen der Mitarbeiter auf, die diese Jobs ausführen.

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

– Listen Sie das Mindestgehalt, den Namen, den Namen der Abteilung und die Anzahl der Abteilungsmitarbeiter in jeder Abteilung auf.

– 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&#39;)

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

Das obige ist der detaillierte Inhalt vonBeispielanalyse einer komplexen MySQL-Abfragemethode. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!