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 table:

emp テーブル:

salgrade テーブル:

testquestion

1. 「SCOTT」と同じ業務に従事するすべての従業員と部門名と部門の人数をリストします。

2. 会社の各給与等級の従業員の数と平均給与をリストします。

3. 部門 30 で働く全従業員の給与よりも給与が高い従業員の名前、給与、部門名をリストします。

4. 各部門で働く従業員の数、平均給与、平均勤続年数をリストします。

5. 全従業員の名前、部門名、給与をリストします。

6.すべての部門の詳細と部門の人数をリストします。

7. さまざまな仕事の最低賃金と、その仕事を行う従業員の名前を列挙します。

8. 各部門のマネージャーの最低給与、名前、部門名、部門の人数をリストします。

9. 全従業員の年収と部署名を年収の低い順に並べて記載します。

10. 従業員の上司と部署の名前を調べ、その上司の 3000 を超える給与を尋ねます。

11. 部署名に「S」が付いているものを調べます。部門の従業員、給与総額、部門の人数。

12. 勤続 30 年を超える従業員、または 1987 年に入社した従業員の給与を上げる場合、昇給の原則は、部門 10 は 10% 増加、部門 20、30 は 20% 増加です。部門 30 では % 増加、など。

13. 少なくとも 1 人の従業員がいるすべての部門の情報をリストします:

14. 給与比率が SMITH であるすべての従業員をリストします:

15. すべての従業員の名前と名前をリストします。直属の上司の名前:

16. 雇用日が直属の上司より前のすべての従業員の番号、名前、部門名をリストします。

17.部門名とその部門の従業員情報を記載し、従業員がいない部門についてはリスト

#18.「事務員(スタッフ)」全員の名前と部門名、部門の人数をリスト

##19. 最低給与が 1,500 を超えるさまざまな仕事と、この仕事に従事している従業員の総数をリストします。

20. 部門「営業」で働く従業員の名前をリストします。営業部門の担当者は不明です

21. 給与が会社の平均給与より高い従業員全員、所属部署、上司、会社の給与等級を列挙してください

##22. 部門番号をすべて列挙してくださいおよび少なくとも 1 人の従業員が含まれる名前、およびこれらを数えます。部門の平均給与、最低給与、および最高給与。

23. 給与が「SMITH」または「ALLEN」を超えるすべての従業員の番号、名前、部門名、およびそのリーダーの名前をリストします。

24. 全社員の人数と名前、直属の上司の人数と名前をリスト化し、リーダーの年収が高い順に表示されます。

25. 直属の上司より入社日が古いすべての従業員の番号、氏名、部署名、部署名、部署名数を記載します。

26. これらの部門の部門名と従業員情報 (人数、平均給与) をリストし、従業員がいない部門もリストします。

27. すべての「CLERK」（事務員）の名前とその部門名、部門の人数、給与等級をリストします。

28. 最低給与が 1,500 を超えるさまざまな仕事と、この仕事に従事している従業員の数、部門の名前、場所、平均給与をリストします。

29.「営業」という部門（営業部）に勤務する社員の氏名、基本給、入社日、部署名を列挙します（営業部の部署名番号は不明とする）。

30. 給与が会社の平均給与より高いすべての従業員、その部署、上司、および会社の給与等級をリストします。

31.「SCOTT」と同じ職務を遂行するすべての従業員、部門名、部門の人数をリストします。

32. dept テーブルの構造をクエリします

33. emp テーブルを取得し、文字列 is a を使用して従業員名と勤務形態の 2 つのフィールドを接続します

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. 各部門のマネージャーの最低給与、名前、部門名、部門の人数をリストします。

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

以上がMYSQL 複雑なクエリメソッドの例の分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。