Mysqlによるemp、dept、salgradeテーブルに関するクエリー操作

初心者は3つの表に触れます:emp、dept、salgrade表、各種の文の操作を練習するのは更に適当です
しかし、ネット上の多くの操作文はoracleで操作されており、編集者はmysqlを勉強する際に、ネット上の書くことを参考にして多くの問題に直面している.
いずれもoracle文とmysql文の互換性がないためです.
複数行のsql文を書くときやネストされたクエリーのときは、メモして、行とインデントして、筋道がはっきりしています.
みんなは学習する時、小編の書くフォーマットと書く字のインデントを見ることに注意して、同じ級のSELECT、WHEREはできるだけそれに対して、子級のはインデントして、妨害を避けます
簡単な文は答えを出すことができて、複雑な文、書く構想を与えることができます
一:単一テーブルクエリー

 1 -- 1)      SQL        (    )：
 2 -- a)      20          ：
 3 SELECT * FROM emp WHERE deptno = 20;
 4 -- b)        （COMM）    （SAL）     ：
 5 SELECT * FROM emp WHERE comm > sal;
 6 -- c)             20%     :
 7 SELECT * FROM emp WHERE comm > sal*0.2;
 8 -- d)      10       MANAGER 20       CLERK      :
 9 SELECT * FROM emp WHERE (job = 'manager' AND deptno = 10) OR (job = 'clerk' AND deptno = 20);
10 -- e)            MANAGER CLERK，        2000       ：
11 SELECT * FROM emp WHERE (sal >= 2000) AND (job NOT IN('manager','clerk'));
12 -- f)               100     :
13 SELECT * FROM emp WHERE (comm < 100 ) OR comm IS NULL;
14 -- g)               10      :
15 SELECT * FROM emp WHERE (NOW() - hiredate) >= 10;
16 -- h)          ，                    :
17 SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)),LOWER(SUBSTRING(ename,2,(CHAR_LENGTH(ename)-1))))FROM emp;

(h)解析:使用する5つの関数:
upper():文字列を大文字で処理
lower():文字列を小文字で処理する
substring(ename,1,1):文字列を1つの文字列から切り取ります.たとえば、smith:
substring(Smith,1,1):最初の文字から1文字を切り取った結果、s
substring(smith,2,(char_length('smith')-1):2番目の文字から、文字長-1文字を切り取ると、頭文字の残りの文字が除去され、結果:mith:
　　char_length():指定した文字列の長さを取得します.
　　concat(str1,str2);2つの文字列をつなぎ合わせる

-- i)       2           :
SELECT * FROM emp WHERE hiredate LIKE '%-02-%';
-- j)             、        ，            ，              :
SELECT ename,SUBSTR(hiredate FROM 1 FOR 7) FROM emp ORDER BY SUBSTR(hiredate FROM 6 FOR 2),SUBSTRING(hiredate FROM 1 FOR 4);

二:マルチテーブルクエリー

-- a)                          :
SELECT e.* FROM emp e
CROSS JOIN emp m
ON (e.job = m.job) AND (e.deptno <> m.deptno);
-- b)                     、      :
SELECT d.*,COUNT(e.ename),AVG(e.sal) FROM emp e,dept d
WHERE e.deptno = d.deptno
GROUP BY d.deptno,d.dname;

多表検索は難しくなく、あまり説明をしないで、分からないので、伝言を残したり、編集者の最初の2編のエッセイを見たりして、説明があります.
3:ネストされたサブクエリ

-- a)      10            :
--       
SELECT * FROM emp
WHERE empno IN(SELECT empno FROM emp WHERE deptno = 10);
-- b)                      :
--       
SELECT * FROM emp
WHERE sal IN(SELECT AVG(sal) FROM emp  GROUP BY deptno);
-- c)                       : 
--      
SELECT * FROM emp e
WHERE sal > (SELECT AVG(sal) FROM emp WHERE e.deptno = deptno GROUP BY deptno);
-- d)                                :
--       
SELECT e.*,a.avgsal 
FROM emp e,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) a 
WHERE e.sal > a.avgsal AND e.deptno = a.deptno;

ネストされたサブクエリは、難しくありません.もし分からないことがあれば、次の文章を見てください.この文章は主に文の練習を主とし、あまり知識の説明をしません.
四:集約関数を使用したクエリー(重点紹介)
一般的な5つの集約関数:sum()、count()、max()、min()、avg().集約関数は通常groupby句とともに使用されます
 

-- a)                  :
SELECT job,COUNT(ename),AVG(sal) FROM emp GROUP BY job;

--b)各部門の各職種の人数と平均賃金を統計する:SELECT deptno,job,COUNT(*),AVG(sal)FROM emp GROUP BY deptno,job;

 ：   ，    

 

-- c)               ：

2つの方法があります.
方法1:ページング文の利用

-- 1、         ,          
SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc ;
-- 2、    ，      ，        
SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc  LIMIT 1;
-- 3、             ，         
SELECT * FROM dept d WHERE d.deptno =(
                SELECT deptno  FROM emp GROUP BY deptno ORDER BY count(*) desc  LIMIT 1);

方法2:面倒

WHERE deptno IN(
            SELECT b.deptno FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno) b
            WHERE 
            b.count IN (
                        SELECT MAX(a.count)FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno ) a));

-- d)                 :

2つの方法があります.
方法1:ページング・クエリーの使用:

-- 1、          ,       
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、    ，       ，            
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、          ，       
SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1;

方法2:

SELECT deptno FROM dept
WHERE deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
            b.avgsal IN(
                         SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));

-- e)                 :

2つの方法があります.
方法1:ページングの利用

-- 1、          ,       
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、    ，       ，            
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、             ，     ，      
SELECT dname FROM dept d WHERE d.deptno = (
            SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1);

方法2:

SELECT dname FROM dept
WHERE
deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
                b.avgsal IN(
                        SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));

-- f)                  :

2つの方法があります.
方法1:ページングの利用

-- 1、          ,       
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal ;
-- 2、    ，       ，            
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal LIMIT 1;
-- 3、             ，     ，      
SELECT dname FROM dept d WHERE d.deptno = (
            SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal)  LIMIT 1);

方法2:

SELECT dname FROM dept
WHERE
deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
            b.avgsal IN(
                       SELECT MIN(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));

-- g)                    :

SELECT d.dname FROM dept d
WHERE 
d.deptno IN
            (SELECT a.deptno FROM (SELECT e.deptno FROM emp e,salgrade s
                                   WHERE (e.sal BETWEEN s.losal AND s.hisal)GROUP BY e.deptno ORDER BY avg(s.grade)) a) LIMIT 1;

-- h)          ，         :

方法1:

-- 1、                  
SELECT deptno,job,MIN(sal) FROM emp WHERE job = 'manager' GROUP BY deptno;
-- 2、    ，    ，        ，          
SELECT deptno FROM emp  WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 ;
-- 3、          ，     
SELECT dname FROM dept d WHERE d.deptno = (
                SELECT deptno FROM emp  WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 );

方法2:

select dname from dept d where d.deptno IN

(select deptno from emp where job='MANAGER' group by deptno order by min(sal));

-- i)                       :

-- 1、           
SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president');
-- 2、  
SELECT ename,sal FROM emp 
    WHERE  sal >(SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president'))
        AND job = 'manager' OR job = 'president';

5:ネストされたサブクエリ

-- a)        【       1000】      :
            -- 1、        1000     ,      
            SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno;
            -- 2、             ，               
            SELECT d.* FROM dept d 
            WHERE d. deptno NOT IN(SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno);

-- b)               1000            :
        -- 1、        1000     ,      
        SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno;
        -- 2、    ，     
        SELECT * FROM emp e 
        JOIN dept d
        ON e.deptno = d.deptno 
        WHERE e.deptno NOT IN(SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno);

-- c)              900~3000        : 
        -- 1、        900-3000     
        SELECT deptno,MAX(sal),MIN(sal) FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900;
        -- 2、           ，       
        SELECT * from dept d WHERE d.deptno IN(
                    SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900); 

-- d)            900~3000              :
        SELECT * FROM emp e
        WHERE e.deptno = (SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900);

-- e)                    ：
        -- 1、         ，      
        SELECT DISTINCT e2.deptno FROM emp e1,emp e2 WHERE e1.mgr = e2.empno;
        -- 2、      ，      
        SELECT * FROM dept 
        WHERE deptno IN(SELECT DISTINCT e2.deptno FROM emp e1,emp e2 WHERE e1.mgr = e2.empno);

-- f)      30         3      :
        -- 1、  30        
        SELECT * FROM emp WHERE deptno = 30 ORDER BY sal DESC
        -- 2、    ，       
        SELECT  e.* from (SELECT * FROM emp WHERE deptno = 30 ORDER BY sal DESC ) e LIMIT 3;

-- g)           2 ，1985           DALLAS     、     ：
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN  losal AND hisal) AND s.GRADE = 2
AND e.hiredate>'1985' 
AND d.loc = 'dallas';

-- 6)     SQL        ：
-- a)                            1000:
UPDATE emp b
SET sal=(SELECT sal FROM (SELECT deptno,avg(sal)+1000 sal FROM emp GROUP BY deptno) a 
WHERE a.deptno=b.deptno)
SELECT * FROM emp;
-- b)          ,          ,  :
UPDATE emp e SET (sal,comm)=(SELECT sal,comm FROM emp WHERE empno=mgr);
SELECT * FROM emp

 
転載先:https://www.cnblogs.com/ncl-960301-success/p/10778154.html