_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › SQL蠍磯蓋
Data Analysis(R)
Data Analysis(Python)
Statistics
Data Warehouse
Database System
SQL Server
Trino
Hive
Hadoop
Regular Expressions
Tools
Money
Working Smart
Blog
Booklog
Philosophy
Link
Miscellaneous
__ edit




Contents

1 SQL螻殊 豌襷
2 SELECT 一
3 SELECT 蠍一1
4 SELECT 蠍一2
5 LIKE 一一襯 伎 企Г豺 蟆
6 BETWEEN 一一襯 伎 蟆
7 蠍 覯 一
8 IN 一一襯 伎 蟆
9 IS NULL
10 蟆郁骸
11 讌螻 螻 GROUP BY
12 貎朱Μ覓語 蠍磯蓋 豌襴螻殊
13 HAVING
14 譟一語
15 觚貎朱Μ
16 讌 一一


[edit]

1 SQL螻殊 豌襷 #

る狩伎 SCOTT/TIGER襦 企 譟壱
  1. SQL> -- 企螳 SCOTT願 れ螳 TIGER 襦 
  2. SQL> CONNECT SCOTT/TIGER 
  3. 郁屋給.
  4.  
  5. SQL> --  企 襦  螳?
  6. SQL> SHOW USER
  7. USER "SCOTT"
  8.  
  9. SQL> -- SCOTT  覈 企 譟壱
  10. SQL> SELECT * FROM TAB;    --> SCOTT  覈 企 譟壱
  11.  
  12. TNAME                          TABTYPE  CLUSTERID
  13. ------------------------------ ------- ----------
  14. BONUS                          TABLE
  15. DEPT                           TABLE
  16. EMP                            TABLE
  17. MICROSOFTDTPROPERTIES          TABLE
  18. SALGRADE                       TABLE
  19.  
  20. SQL> --  EMP()企 覈 蟆 譟壱 蟆.. 
  21. SQL> SELECT * FROM EMP;       
  22.  
  23.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  24. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  25.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  26.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  27.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  28.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  29.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  30.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  31.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  32.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  33.       7839 KING       PRESIDENT            81/11/17       5000                    10
  34.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  35.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  36.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  37.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  38.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  39.  
  40. 14 螳  給.
  41.  
  42. SQL> SELECT * FROM DEPT;    -----> DEPT(覿)企 覈 一危襯 譟壱..
  43.  
  44.     DEPTNO DNAME          LOC
  45. ---------- -------------- -------------
  46.         10 ACCOUNTING     NEW YORK
  47.         20 RESEARCH       DALLAS
  48.         30 SALES          CHICAGO
  49.         40 OPERATIONS     BOSTON
  50.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari



磯Μ SELECT * FROM EMP; 手 給.願 'SQL企 覓伎瑚' 危ｴ覲伎 螳 覲願 覓伎瑚襯 覈伎 蟆 訖(譴 蟆暑蟾讌 蟆一..) 襾語 る狩伎 蟆. SQL 語伎願鍵 覓語 覓碁 螳讌螻 給. 覓碁 危ｴ覺. SELECT覓語 蠍磯蓋蟲譟磯 れ螻 螳給.

  1. SELECT COLUMN_NAME1, COLUMN_NAME2....
  2. FROM TABLE_NAME
  3.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

2 SELECT 一 #


FROM れ 企覈 覈 譴. 讀, 願 襦 覲願 企 企 讌襯 覈 譯朱 蟆.SELECT れ 貉朱 覈伎.覓朱 SELECT 螳讌 襯 伎 一危襯 譟一(豺一磯)り碓 給.覯 企慨蟆給.


SELECT 一
  1. SQL> SELECT 1+1 FROM DUAL;
  2.  
  3.        1+1
  4. ----------
  5.          2
  6.  
  7. SQL> SELECT 'A' || 'B' FROM DUAL;
  8.  
  9. 'A
  10. --
  11. AB
  12.  
  13. SQL> SELECT 'A' + 'B' FROM DUAL;  --> 譯殊 蟾蟆 危ｴ 覺. 
  14. SELECT 'A' + 'B' FROM DUAL
  15.        *
  16. 1 る:
  17. ORA-01722: 豺螳 覿
  18.  
  19.  
  20. SQL> SELECT '1' + 1 FROM DUAL;
  21.  
  22.      '1'+1
  23. ----------
  24.          2
  25.  
  26. SQL> SELECT '1' + '1' FROM DUAL;
  27.  
  28.    '1'+'1'
  29. ----------
  30.          2
  31.  
  32. SQL> SELECT '1' || '1' FROM DUAL;
  33.  
  34. '1
  35. --
  36. 11
  37.  
  38. SQL>  -- 襾語 朱 襦蠏碁覦螻 螳. 讌 蠍 一一 蟆郁骸螳 襾語襦  
  39. SQL> -- 蟆襷覲伎.
  40. SQL> SELECT MOD(3,2) FROM DUAL;
  41.  
  42.   MOD(3,2)
  43. ----------
  44.          1
  45.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

谿瑚'
MSSQL SERVER 覓語 蠍 一一 覈 "+" 蠍壱碁ゼ . , FROM 觜朱 螳 一 螳 蟆 蟆 給. '1' + '1' 覓語 蠍 一一 '11' 蟆郁骸螳 給. 
  1. SELECT '1' + '1'  
  2.  
  3. ---- 
  4. 11
  5.  
  6. (1螳  )
  7.  
  8. SELECT 3%2 AS '襾語'
  9.  
  10. 襾語         
  11. ----------- 
  12. 1
  13.  
  14. (1螳  )
  15.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

3 SELECT 蠍一1 #


豌 覲伎 覿れ DUAL企 企 豌 企 螳 覈襯 蟆.DUAL企 企 螳 螻 螳 貉殊 螳 る狩伎 襷れ企 轟 企. る狩伎 一一 '+'襦 螻 覓語 一一 '||'襦 .磯Μ SELECT 貉殊 危 蟆 朱 蟆 給. 譴 り讌襷 螳讌 螻, SELECT 譴豌 給. 讌蠍 螳 牛 螻企襦 覲旧″ 蟆 譴 企慨襦 蟆給.

覿れ 譯殊 蟾蟆 危ｴ覲 蟆 '1' 螻 1 谿伎. '1' 覓語襯 願,1 襯 企 蟆. MSSQL SERVER '1' + '1' = 2 襯 讌讌 給. 讀, 覓企Μ 覓語朱 " 覓語 + 覓語" 覓語襦 語る 蟆. 蠏碁る 1 + '1' 企 蟆手? 覿覈 蟆 + 覓語 朱 蟆. 蠏碁磯 蟆郁骸 2 . 蟆郁骸襦 覲 覿覈 '1' 1襦 覦る 蟆 給. 讀, 一一 覓語襯 襦 覲 蟆.

螳 螳企.蟾 FROM 企覈 覈 譴り 給. 願 讌朱 螳伎 . 企企朱 2谿 螻糾 讌企手 螳螻 覲 EMP企 EMPNO, ENAME襷 譟壱り 螳 覺.讀, 企 覯螻, 企 譟壱朱朱 一一 螳 願碓讌. 襾殊 企至 伎 蟾? 襾殊 企 蠏狩伎 .

  1.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  2. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  3.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  4.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  5.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  6.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  7.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  8.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  9.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  10.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  11.       7839 KING       PRESIDENT            81/11/17       5000                    10
  12.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  13.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  14.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  15.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  16.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  17.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

覯讌碁 螳語 EMPNO, ENAME襷 豢れ 螳語 . 

  1.      EMPNO ENAME
  2. ---------- ----------
  3.       7369 SMITH
  4.       7499 ALLEN
  5.       7521 WARD
  6.       7566 JONES
  7.       7654 MARTIN
  8.       7698 BLAKE
  9.       7782 CLARK
  10.       7788 SCOTT
  11.       7839 KING
  12.       7844 TURNER
  13.       7876 ADAMS
  14.       7900 JAMES
  15.       7902 FORD
  16.       7934 MILLER
  17.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

讌 螻覦 蠍碁 . EMP企 豌企 讌願 覦襦 覯, 企 讌. 蟆郁骸朱 蠏 FROM -> SELECT .襾殊 企 蠏狩伎 SELECT 企 貉殊 螳語る 蟆... 

  1. SQL> SELECT EMPNO, ENAME FROM EMP;
  2.  
  3.      EMPNO ENAME
  4. ---------- ----------
  5.       7369 SMITH
  6.       7499 ALLEN
  7.       7521 WARD
  8.       7566 JONES
  9.       7654 MARTIN
  10.       7698 BLAKE
  11.       7782 CLARK
  12.       7788 SCOTT
  13.       7839 KING
  14.       7844 TURNER
  15.       7876 ADAMS
  16.       7900 JAMES
  17.       7902 FORD
  18.       7934 MILLER
  19.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

螳 14螳 覦 蠍 覓語 企一 譟壱 覲願 給.襷 企 螳讌螻 企 蠍一 螳 襷覈 る 企一 豢リ讌螻 覲願 伎.讀, 襷覈譴 企 SMITH 谿城 る 蟆 谿語朱 企れ 殊. 覓朱 企 螳 給. 蠏碁 PRIMARY KEY螳 譟伎螻, 譟壱襯 譟郁唄 蟇語伎朱 WHERE 譟伎 蟆.

  1. SELECT COLUMN_NAME1, COLUMN_NAME2....
  2. FROM TABLE_NAME
  3. WHERE CONDITION.....
  4.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

襷 譴 蠍(SAL) 1500 伎 覦 譟壱螻 矩る 企至 伎 蟾? 襾殊 EMP企(豌伎) 蠏狩伎 .
  1.  
  2.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  3. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  4.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  5.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  6.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  7.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  8.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  9.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  10.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  11.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  12.       7839 KING       PRESIDENT            81/11/17       5000                    10
  13.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  14.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  15.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  16.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  17.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  18.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

れ 蠍 1500 伎 企朱 譟郁唄 襷 讌 谿場 . ( 覿覈 襷讌襷 譟郁唄 襷 谿場 企り 蟆企手 螳伎 .)

  1.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  2. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  3.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  4.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30  <-- 1
  5.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  6.       7566 JONES      MANAGER         7839 81/04/02       2975                    20  <-- 2
  7.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  8.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30  <-- 3
  9.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10  <-- 4
  10.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20  <-- 5
  11.       7839 KING       PRESIDENT            81/11/17       5000                    10  <-- 6
  12.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30  <-- 7
  13.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  14.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  15.       7902 FORD       ANALYST         7566 81/12/03       3000                    20  <-- 8
  16.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  17.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

蟆郁骸 れ螻 螳給.

  1. SQL> SELECT * FROM EMP
  2.   2  WHERE SAL >= 1500;
  3.  
  4.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  5. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  6.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  7.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  8.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  9.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  10.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  11.       7839 KING       PRESIDENT            81/11/17       5000                    10
  12.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  13.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  14.  
  15. 8 螳  給.
  16.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

願 襦 豌襴 蟆 . 讌 襦 豌襴 蟆. 企り覃伎 SAL >= 1500 譟郁唄 襷譟燕 蟇碁企 蟆 覯 蟇碁企 蟆. 譟郁 覿 蟆. 企至 覯(讌)襦 豌襴襯 襷瑚?? 朱 螳 螳讌 覿 螻蟲譟一 . 譟, , 貊 蟇碁 願鍵 伎 豈襯 る 伎手鍵襯 給. 襷 貊襷 る 豌襴 貊 螻朱企 殊 蟆願, 讌 襦 豌襴 蟆 豈襯 伎 蟆 蟆給. 企ゼ 覲企 朱朱 豌襴 讌 豌襴螳 企 焔レ姶襯 螳讌螻 讌 り. 襷 危願 螳る 蠏 谿 語伎 旧伎語 谿朱 螳 覦 る 蟆. 譴 豌伎朱 覲碁る 麹 語 覈襯願給. 讌 螻 蟲譟磯 襷詞 覈襯伎朱 る 讌 襷語. 讌蠍 豌 螻蟾.

朱 SQL るる 螻 覃 谿 譴 螳螻, 蠍磯蓋朱 讌 螻覦 螳讌り 碁ロ伎 . 伎覿 SELECT ~ FROM ~ WHERE ~ 企蟆 3螳 螳讌螻 谿碁 れ旧 企慨襦 蟆給.

EMP 企 蟯 蟆願 DEPT 企 覿 蟯 蟆. 企 覲企, 覯, 企, 讌覓, 讌, , 蠍, 貉る語, 覿覯 朱 給. 覿 覿覯, 覿企, 豺.|


[edit]

4 SELECT 蠍一2 #

  1. SQL> -- 覈  覯語 企 譟壱
  2. SQL> SELECT EMPNO, ENAME 
  3.   2  FROM EMP;
  4.  
  5.      EMPNO ENAME
  6. ---------- ----------
  7.       7369 SMITH
  8.       7499 ALLEN
  9.       7521 WARD
  10.       7566 JONES
  11.       7654 MARTIN
  12.       7698 BLAKE
  13.       7782 CLARK
  14.       7788 SCOTT
  15.       7839 KING
  16.       7844 TURNER
  17.       7876 ADAMS
  18.       7900 JAMES
  19.       7902 FORD
  20.       7934 MILLER
  21.  
  22. 14 螳  給.
  23.  
  24. SQL> --  讌覓願 CLERK  覯 譟壱
  25. SQL> SELECT EMPNO
  26.   2  FROM EMP
  27.   3  WHERE JOB = 'CLERK';
  28.  
  29.      EMPNO
  30. ----------
  31.       7369
  32.       7876
  33.       7900
  34.       7934
  35. SQL> -- 貉殊 豺蟆 (4レ 覲牛煙 谿瑚 蟆)
  36. SQL> SELECT EMPNO || ' ' || ENAME || ' ' || JOB
  37.   2  FROM EMP;
  38.  
  39. EMPNO||''||ENAME||''||JOB
  40. -------------------------------------------------------------
  41. 7369 SMITH CLERK
  42. 7499 ALLEN SALESMAN
  43. 7521 WARD SALESMAN
  44. 7566 JONES MANAGER
  45. 7654 MARTIN SALESMAN
  46. 7698 BLAKE MANAGER
  47. 7782 CLARK MANAGER
  48. 7788 SCOTT ANALYST
  49. 7839 KING PRESIDENT
  50. 7844 TURNER SALESMAN
  51. 7876 ADAMS CLERK
  52. 7900 JAMES CLERK
  53. 7902 FORD ANALYST
  54. 7934 MILLER CLERK
  55. 1111 MILLER CLE%RK
  56.  
  57. 15 螳  給.
  58.  
  59. SQL> -- る狩 SQL 蠍磯蓋朱 覓語 蟲覿 讌 讌襷 
  60. SQL> -- ル 蟆 蠍一朱 蠍 覓語 WHERE  譟郁唄 蠍一  覓語 蟲覿
  61. SQL> -- .  
  62. SQL> SELECT EMPNO
  63.   2  FROM EMP
  64.   3  WHERE JOB = 'CLERK';
  65.  
  66.  貊螳 給.
  67.  
  68. SQL>  -- 蠍 3000 伎  企 譟壱
  69. SQL> SELECT ENAME
  70.   2  FROM EMP
  71.   3  WHERE SAL >= 3000;
  72.  
  73. ENAME
  74. ----------
  75. SCOTT
  76. KING
  77. FORD
  78.  
  79. SQL> -- 覯瑚 7698  覿讌 企螻 讌覓企ゼ 譟壱
  80. SQL> SELECT ENAME, JOB
  81.   2  FROM EMP
  82.   3  WHERE MGR = 7698;  -->    .
  83.  
  84. ENAME      JOB
  85. ---------- ---------
  86. ALLEN      SALESMAN
  87. WARD       SALESMAN
  88. MARTIN     SALESMAN
  89. TURNER     SALESMAN
  90. JAMES      CLERK
  91.  
  92. SQL> -- 覯碁 るジ讓曙朱 豺一 り 企 殊曙 豺一 る 蟆 覲  .
  93. SQL> -- 願 覯瑚 願, 企 覓語企朱 蟆 覩誤. 
  94. SQL> --  覓語 殊曙朱 豺一 蟆郁骸螳 .
  95.  
  96. SQL> -- 覯瑚 7698  覿讌譴 蠍 1500 危 讌  覈 譟壱
  97. SQL> SELECT *
  98.   2  FROM EMP
  99.   3  WHERE MGR = 7698 AND SAL <= 1500;
  100.  
  101.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  102. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  103.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  104.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  105.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  106.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  107.  
  108. SQL> -- 覿覯瑚 20願碓 蠍 3000伎  覈 覲企ゼ 譟壱
  109. SQL> SELECT *
  110.   2  FROM EMP
  111.   3  WHERE DEPTNO = 20 OR SAL >= 3000;
  112.  
  113.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  114. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  115.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  116.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  117.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  118.       7839 KING       PRESIDENT            81/11/17       5000                    10
  119.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  120.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  121.  
  122. 6 螳  給.
  123.  
  124. SQL> -- 讌覓願 ANALYST願, 
  125. SQL> -- 覿覯瑚 20願碓 蠍 3000伎  覈 覲企ゼ 譟壱      
  126. SQL> -- れ 碁.
  127. SQL> SELECT *
  128.   2  FROM EMP
  129.   3  WHERE JOB = 'ANALYST' 
  130.   4  AND DEPTNO = 20 OR SAL >= 3000;
  131.  
  132.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  133. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  134.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  135.       7839 KING       PRESIDENT            81/11/17       5000                    10
  136.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  137.  
  138. SQL> -- 願 碁.
  139. SQL> SELECT *
  140.   2  FROM EMP
  141.   3  WHERE DEPTNO = 20 OR SAL >= 3000
  142.   4  AND JOB = 'ANALYST';
  143.  
  144.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  145. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  146.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  147.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  148.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  149.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  150.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  151.  
  152. SQL> -- 襷 蟆 !!!!
  153. SQL> SELECT *
  154.   2  FROM EMP
  155.   3  WHERE JOB = 'ANALYST'
  156.   4  AND (DEPTNO = 20 OR SAL >= 3000);
  157.  
  158.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  159. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  160.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  161.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  162.  
  163. SQL> -- AND OR譴 企 蟆 一螳 蟾?  覲企 AND螳 一螳 .
  164. SQL>
  165. SQL> -- 譴覲牛 蟇 一一 DISTINCT
  166. SQL> SELECT JOB FROM EMP;
  167.  
  168. JOB
  169. ---------
  170. CLERK
  171. SALESMAN
  172. SALESMAN
  173. MANAGER
  174. SALESMAN
  175. MANAGER
  176. MANAGER
  177. ANALYST
  178. PRESIDENT
  179. SALESMAN
  180. CLERK
  181. CLERK
  182. ANALYST
  183. CLERK
  184.  
  185. 14 螳  給.
  186.  
  187. SQL> SELECT DISTINCT JOB, ENAME FROM EMP;
  188.  
  189. JOB       ENAME
  190. --------- ----------
  191. ANALYST   FORD
  192. ANALYST   SCOTT
  193. CLERK     ADAMS
  194. CLERK     JAMES
  195. CLERK     MILLER
  196. CLERK     SMITH
  197. MANAGER   BLAKE
  198. MANAGER   CLARK
  199. MANAGER   JONES
  200. PRESIDENT KING
  201. SALESMAN  ALLEN
  202. SALESMAN  MARTIN
  203. SALESMAN  TURNER
  204. SALESMAN  WARD
  205.  
  206. 14 螳  給.
  207.  
  208. SQL> -- ENAME 覈 襴覩襦 JOB朱  讌襷 JOB  譴覲旧蟇磯 讌 覈詩. 
  209. SQL>
  210.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

伎 螳 譬 ″? 磯Μ EMP 企企朱 豌伎 WHERE 譟郁唄 蠍一 譴朱 伎 磯Μ螳 讌 螳語 給. 企蟆 WHERE れ襦 螳 讌 襷れ 願鍵 讌 蟯 襷崎 給. 伎 所 轟(?) 一一 伎 危ｴ覲願給. 覿 蟯 豈 谿場朱り '一危覯伎'手 蟆 り. 蠏碁 豈 覈 '一危覯伎'手 る
蟆 譯. 覲危旧 '一危覯伎 ろ'企 '一危覯伎 蟯襴' '一危覯伎る' 企手 給. 企 企れ朱 蟆 企 讌蟾? 螳覲企 蠍, 譴螳蠍, 蠍 螻 蟆 覈給 覲伎手. 讀, 企Г豺 蟆 蟆. 企 'LIKE' 一一襯 . 詞 譬, 覦朱, 蠎, ~豌 煙 詞 給. SQL LIKE '蠎' 詞 蟆讌. 覯 覲手?

[edit]

5 LIKE 一一襯 伎 企Г豺 蟆 #

  1. SQL> SELECT * FROM EMP
  2.   2  WHERE JOB LIKE 'PRE%';
  3.  
  4.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  5. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  6.       7839 KING       PRESIDENT            81/11/17       5000                    10
  7.  
  8. SQL> SELECT * FROM EMP
  9.   2  WHERE JOB LIKE '%ENT';
  10.  
  11.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  12. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  13.       7839 KING       PRESIDENT            81/11/17       5000                    10
  14.  
  15. SQL> -- ル 一危襯 蠍一朱 蠍 覓語 覓語 蟲覿
  16. SQL> SELECT * FROM EMP
  17.   2  WHERE JOB LIKE 'PRE%';
  18.  
  19.  貊螳 給.
  20.  
  21. SQL> SELECT * FROM EMP
  22.   2  WHERE JOB LIKE '%SI%';
  23.  
  24.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  25. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  26.       7839 KING       PRESIDENT            81/11/17       5000                    10
  27.  
  28. SQL> SELECT * FROM EMP
  29.   2  WHERE JOB LIKE '%E%';
  30.  
  31.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  32. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  33.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  34.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  35.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  36.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  37.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  38.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  39.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  40.       7839 KING       PRESIDENT            81/11/17       5000                    10
  41.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  42.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  43.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  44.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  45.  
  46. 12 螳  給.
  47.  
  48. SQL> SELECT * FROM EMP
  49.   2  WHERE JOB LIKE '_RESIDENT';
  50.  
  51.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  52. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  53.       7839 KING       PRESIDENT            81/11/17       5000                    10
  54.  
  55. SQL> SELECT * FROM EMP
  56.   2  WHERE JOB LIKE '_A%';
  57.  
  58.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  59. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  60.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  61.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  62.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  63.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  64.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  65.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  66.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  67.  
  68. 7 螳  給.
  69.  
  70. SQL> -- 讌 企Г豺 蟆 .
  71. SQL> SELECT * FROM EMP
  72.   2  WHERE HIREDATE LIKE '81%';
  73.  
  74.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  75. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  76.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  77.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  78.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  79.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  80.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  81.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  82.       7839 KING       PRESIDENT            81/11/17       5000                    10
  83.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  84.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  85.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  86.  
  87. 10 螳  給.
  88.  
  89. SQL> -- NOT 一一 伎
  90. SQL> SELECT * FROM EMP
  91.   2  WHERE HIREDATE NOT LIKE '81%';
  92.  
  93.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  94. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  95.       7369 SMITH      CLERK           7902 80/12/17        800                    20
  96.       7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  97.       7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  98.       7934 MILLER     CLERK           7782 82/01/23       1300                    10
  99.       1111 MILLER     CLE%RK          7782 82/01/23       1300                    10   
  100.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

'%' 磯Μ螳 殊企 启襯 蟆 一企 '*'蠍壱語 螳 覩碁ゼ 螳讌. 讀, 豌 覲企 覦 螳 JOB 'PRE'螳 れ願 覈 覲企ゼ 譟壱 蟆 . '_' 蠍襷 覩誤. 讀, '_RESIDENT' 覩碁 豌蠍螳 企 蟆企 伎 襷れ広 蟆 覈 螳語る朱 詞 蟆. 襷讌襷 覲企 覦 螳 讌 企Г豺 蟆 . 蠍一 '%' '_'襯 朱 豺企 覓語手 .

  1. SQL> -- 襷 '%' 蠍壱瑚 れ願 覓語伎  企Г豺 蟆 る?
  2.  
  3. SQL> INSERT INTO EMP VALUES
  4.   2  (1111,'MILLER','CLE%RK',7782,TO_DATE('23-1-1982','DD-MM-YYYY'),1300,NULL,10);
  5.  
  6. 1 螳  襷れ伎給.
  7.  
  8. SQL> SELECT ENAME, JOB FROM EMP
  9.   2  WHERE EMPNO = 1111;
  10.  
  11. ENAME      JOB
  12. ---------- ---------
  13. MILLER     CLE%RK
  14.  
  15. SQL> -- 谿場 覓語 '\'襯 覿願 '\' れ る 覓語 '%'螳 一一螳  
  16. SQL> -- 蠎 '\' 覓語螳 企 .  
  17. SQL> SELECT ENAME, JOB
  18.   2  FROM EMP
  19.   3  WHERE JOB LIKE '%\%%' ESCAPE '\';
  20.  
  21. ENAME      JOB
  22. ---------- ---------
  23. MILLER     CLE%RK
  24.  
  25. SQL> SELECT ENAME, JOB
  26.   2  FROM EMP
  27.   3  WHERE JOB LIKE '%/%%' ESCAPE '/';
  28.  
  29. ENAME      JOB
  30. ---------- ---------
  31. MILLER     CLE%RK
  32.  
  33. SQL> 
  34.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

6 BETWEEN 一一襯 伎 蟆 #

  1. SQL> -- BETWEEN 伎 81 05 01 ~ 81 12 31 伎   譟壱
  2. SQL> SELECT EMPNO, ENAME, HIREDATE
  3.   2  FROM EMP
  4.   3  WHERE HIREDATE BETWEEN '81/05/01' AND '81/12/31';
  5.  
  6.      EMPNO ENAME      HIREDATE
  7. ---------- ---------- --------
  8.       7654 MARTIN     81/09/28
  9.       7698 BLAKE      81/05/01
  10.       7782 CLARK      81/06/09
  11.       7839 KING       81/11/17
  12.       7844 TURNER     81/09/08
  13.       7900 JAMES      81/12/03
  14.       7902 FORD       81/12/03
  15.  
  16. 7 螳  給.
  17.  
  18. SQL> -- BETWEEN 一一 企朱 AND 一一襦 覦 ...
  19. SQL> -- BETWEEN 一一螳 AND 一一襦 覦 螳 譯殊譯 讌ъ..覓伎企  ...
  20. SQL> -- 蠏碁 譟郁企朱 觜襯 焔レ 螳讌蟆 螻 矩る BETWEEN  AND襯 ...
  21. SQL> -- AND 一一襯 伎 81 05 01 ~ 81 12 31 伎   譟壱
  22. SQL> SELECT EMPNO, ENAME, HIREDATE
  23.   2  FROM EMP
  24.   3  WHERE HIREDATE >= '81/05/01' AND HIREDATE <= '81/12/31';
  25.  
  26.      EMPNO ENAME      HIREDATE
  27. ---------- ---------- --------
  28.       7654 MARTIN     81/09/28
  29.       7698 BLAKE      81/05/01
  30.       7782 CLARK      81/06/09
  31.       7839 KING       81/11/17
  32.       7844 TURNER     81/09/08
  33.       7900 JAMES      81/12/03
  34.       7902 FORD       81/12/03
  35.  
  36. 7 螳  給.
  37.  
  38. SQL> -- NOT BETWEEN  伎
  39. SQL> SELECT EMPNO, ENAME, HIREDATE
  40.   2  FROM EMP
  41.   3  WHERE HIREDATE NOT BETWEEN '81/05/01' AND '81/12/31';
  42.  
  43.      EMPNO ENAME      HIREDATE
  44. ---------- ---------- --------
  45.       7369 SMITH      80/12/17
  46.       7499 ALLEN      81/02/20
  47.       7521 WARD       81/02/22
  48.       7566 JONES      81/04/02
  49.       7788 SCOTT      87/04/19
  50.       7876 ADAMS      87/05/23
  51.       7934 MILLER     82/01/23
  52.       1111 MILLER     82/01/23
  53.  
  54. 8 螳  給.
  55.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

7 蠍 覯 一 #

ろ襴渚碁ゼ 覲旧伎 SQL*PLUS ろ. 
  1. DROP TABLE TEST;
  2. CREATE TABLE TEST( NAME VARCHAR(6));
  3. INSERT INTO TEST VALUES ('');
  4. INSERT INTO TEST VALUES ('螳');
  5. INSERT INTO TEST VALUES ('螳');
  6. INSERT INTO TEST VALUES ('蠍');
  7. INSERT INTO TEST VALUES ('');
  8. INSERT INTO TEST VALUES ('');
  9. INSERT INTO TEST VALUES ('る?');
  10. INSERT INTO TEST VALUES ('襾碁?');
  11. INSERT INTO TEST VALUES ('');
  12. INSERT INTO TEST VALUES ('朱');
  13.  
  14. SQL> -- る狩伎...
  15. SQL> SELECT * FROM TEST
  16.   2  WHERE NAME BETWEEN '' AND '';
  17.  
  18. NAME
  19. ------
  22.  
  23. SQL> SELECT * FROM TEST
  24.   2  WHERE NAME BETWEEN '螳' AND '';
  25.  
  26. NAME
  27. ------
  28. 螳
  29. 螳
  30. 蠍
  32. る?
  34.  
  35. 6 螳  給.
  36.  
  37. -- MS-SQL SERVER ...
  38. SELECT * FROM TEST
  39. WHERE NAME BETWEEN '' AND ''
  40.  
  41. NAME   
  42. ------ 
  44. 螳
  45. 螳
  46. 蠍
  49. る?
  51.  
  52. (8螳  )
  53.  
  54. SELECT * FROM TEST
  55. WHERE NAME BETWEEN '螳' AND ''
  56.  
  57. NAME   
  58. ------ 
  59. 螳
  60. 螳
  61. 蠍
  64. る?
  66.  
  67. (7螳  )
  68.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

8 IN 一一襯 伎 蟆 #

  1. SQL> -- IN 一一 覈螳 螳譴 覈 貉手骸 殊 螳  蟆曙 蟆 譟郁唄 谿
  2. SQL> -- 覯瑚 7369, 7782, 7789   EMPNO, ENAME, JOB, SAL   蟆
  3. SQL> SELECT EMPNO, ENAME, JOB, SAL
  4.   2  FROM EMP
  5.   3  WHERE EMPNO IN (7369, 7782, 7789);
  6.  
  7.      EMPNO ENAME      JOB              SAL
  8. ---------- ---------- --------- ----------
  9.       7782 CLARK      MANAGER         2450
  10.       7369 SMITH      CLERK            800
  11.  
  12. SQL> -- IN 一一  覈 螳譴 覯瑚 7789   朱襦 蟆 .
  13. SQL> -- OR 一一襯 伎 蟆 (覦襦  蟆螻 螳.)
  14. SQL> -- DBMS IN  OR 一一襦 覦蠑語 一壱讌襷 蠏 螳谿 螳企. 
  15. SQL> SELECT EMPNO, ENAME, JOB, SAL
  16.   2  FROM EMP
  17.   3  WHERE EMPNO = 7369
  18.   4  OR EMPNO = 7782
  19.   5  OR EMPNO = 7789;
  20.  
  21.      EMPNO ENAME      JOB              SAL
  22. ---------- ---------- --------- ----------
  23.       7782 CLARK      MANAGER         2450
  24.       7369 SMITH      CLERK            800
  25.  
  26. SQL> -- 覿覯瑚 20, 30   EMPNO, ENAME, JOB, DEPTNO襯 譟壱
  27. SQL> SELECT EMPNO, ENAME, JOB, DEPTNO
  28.   2  FROM EMP
  29.   3  WHERE DEPTNO NOT IN (20, 30);
  30.  
  31.      EMPNO ENAME      JOB           DEPTNO
  32. ---------- ---------- --------- ----------
  33.       7782 CLARK      MANAGER           10
  34.       7839 KING       PRESIDENT         10
  35.       7934 MILLER     CLERK             10
  36.       1111 MILLER     CLE%RK            10
  37.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

9 IS NULL #


蟯螻 一危覯伎る (NULL) 螳 覿覈 覈螻 給. 螳 '螳 蠍磯 朱 讌 覈詩 覩語'朱 覩碁ゼ 螳讌. 磯Μ螳 覓危 * 1000 一一 覃 覓危螳 NULL * 1000 覃 NULL螳 給. 覲危旧 DBMS 螳
讌 朱 蠏 螳 NULL 螳朱 覦給. レ 讌 る 蟆 0(ZERO) 覩碁 螻, ''(SPACE) 覩碁 蟆. 襷 (覯, 企, 覯) 朱Μろる襯 螳讌 襴企伎 DBMS 企襦 襷れ 9555023(覯)螻 伎(企)襷 曙る 願 覩碁 螻給葦瑚? 覃 0 手? 願 '螳 朱 讌 覈詩 螳' 蟆. 螳 曙讌 り 讌蠍 ロ 覯瑚 蟆手? 蠏瑚 蟆. 磯Μ螳 轟危語 れ願 螳 螳 企慨覃 螻 蟆 覲伎 蟆. 蟆曙 伎 ロ讌 蟆 蟆. 襷 螳 弰 朱 譯殊螳 る 譯殊 ｌ り 伎 譯殊螳 0願碓 螻給葦手? . 螳 螳 覩 讀, '螳 朱 讌 覈詩 螳' 蟆. 螳 覩碁ゼ 瑚襖 襦 蠍 覦.

  1. SQL> SELECT EMPNO, ENAME, JOB
  2.   2  FROM EMP
  3.   3  WHERE COMM IS NULL;
  4.  
  5.      EMPNO ENAME      JOB
  6. ---------- ---------- ---------
  7.       7369 SMITH      CLERK
  8.       7566 JONES      MANAGER
  9.       7698 BLAKE      MANAGER
  10.       7782 CLARK      MANAGER
  11.       7788 SCOTT      ANALYST
  12.       7839 KING       PRESIDENT
  13.       7876 ADAMS      CLERK
  14.       7900 JAMES      CLERK
  15.       7902 FORD       ANALYST
  16.       7934 MILLER     CLERK
  17.       1111 MILLER     CLE%RK
  18.  
  19. 11 螳  給.
  20.  
  21. SQL> -- COMM = NULL 企手 伎 NULL螳  蟆 企 讌讌 .
  22. SQL> -- '=' 蟆 伎 螳  觜蟲 一一 企.
  23. SQL> SELECT EMPNO, ENAME, JOB
  24.   2  FROM EMP
  25.   3  WHERE COMM = NULL;
  26.  
  27.  貊螳 給.
  28.  
  29. SQL> -- NULL 螻給葦 .
  30. SQL> SELECT EMPNO, ENAME, JOB
  31.   2  FROM EMP
  32.   3  WHERE COMM = '';
  33.  
  34.  貊螳 給.
  35.  
  36. SQL> -- NULL 0  覩襦 COMM = 0  襷 蟆.
  37. SQL> SELECT EMPNO, ENAME, JOB
  38.   2  FROM EMP
  39.   3  WHERE COMM = 0;
  40.  
  41.      EMPNO ENAME      JOB
  42. ---------- ---------- ---------
  43.       7844 TURNER     SALESMAN
  44.  
  45. SQL> COMM = NULL  . 讀, COMM IS NULL 伎 . 
  46. SQL> SELECT EMPNO, ENAME, COMM
  47.   2  FROM EMP
  48.   3  WHERE COMM IN (NULL, 1400);
  49.  
  50.      EMPNO ENAME            COMM
  51. ---------- ---------- ----------
  52.       7654 MARTIN           1400 
  53.  
  54. SQL> --  蟆 れ 伎 磯..
  55. SQL> SELECT EMPNO, ENAME, COMM
  56.   2  FROM EMP
  57.   3  WHERE COMM = NULL
  58.   4  OR COMM = 1400;
  59.  
  60.      EMPNO ENAME            COMM
  61. ---------- ---------- ----------
  62.       7654 MARTIN           1400
  63.  
  64. SQL> SELECT EMPNO, ENAME, COMM
  65.   2  FROM EMP
  66.   3  WHERE COMM NOT IN (NULL, 1400); 
  67.  
  68.  貊螳 給.
  69.  
  70. SQL> -- 伎 磯 れ螻 螳. 
  71. SQL> SELECT EMPNO, ENAME, COMM
  72.   2  FROM EMP
  73.   3  WHERE COMM <> NULL
  74.   4  AND COMM <> 1400;     --> OR螳  AND螳 .
  75.  
  76.  貊螳 給.
  77.  
  78. SQL> -- 企蟆 豺  蟆郁骸襯 詞  れ襦  蟆郁骸 れ螻 螳 蟆企.
  79. SQL> SELECT EMPNO, ENAME, COMM
  80.   2  FROM EMP
  81.   3  WHERE COMM IS NOT NULL
  82.   4  AND COMM <> 1400;
  83.  
  84.      EMPNO ENAME            COMM
  85. ---------- ---------- ----------
  86.       7499 ALLEN             300
  87.       7521 WARD              500
  88.       7844 TURNER              0
  89.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

IN 一一 OR襦 覦り OR NOT一一螳 覿. 讀, 

  1.       <OR>
  2. ---------------
  3. A   B      蟆郁骸
  4. ---------------
  5. 0   0       0 
  6. 0   1       1 
  7. 1   0       1
  8. 1   1       1
  9.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

蠏碁 AND 一一襯 蟆 覲伎碁. 企ゼ 觜蟲 覲願給.
            <AND>
---------------------
A   B      蟆郁骸   NOT
---------------------
0   0       0      1
0   1       0      1
1   0       0      1
1   1       1      0

蠏碁 螳螳 A B NOT 豬覃 1 0 螻, 0 1 ..
   <NOT OR>
---------------
-   - 
A   B      蟆郁骸
---------------
1   1       1   --> A = 0 願 NOT A = 1, B = 0 願 NOT B = 1
1   0       1 
0   1       1 
0   0       0
讀, OR螳 AND 蟆郁骸 NOT 豬 螳 給.

[edit]

10 蟆郁骸 #

覲伎ろ 蟲豢 ろ 蟆 覲企ゼ 螻牛蠍 . 襷 煙 磯ジ 煙襯 襷り襖 一危螳 ル 覲伎ろ 煙襯 願鍵 伎 煙 蟆 讌 ル 襦 蟆蟆郁骸 る 願 蟆 覲襦 覲願 讌 覈詩 螳 給. 企 蟆 螳 覈 DBMS 蠍磯レ 覈 蟲 螻, 譴 SQL 覈 . れ ORDER BY. ORDER BY 螳 譴 ろ. ろ 企襦(ろ 殊 讀螳.) 蟆曙一襷 蟆 譬給. 

  1. SQL> -- 蠍 螳 蟆 覦  朱   覯語 企螻 蠍 譟壱
  2. SQL> SELECT EMPNO, ENAME, SAL
  3.   2  FROM EMP
  4.   3  ORDER BY SAL;
  5.  
  6.      EMPNO ENAME             SAL
  7. ---------- ---------- ----------
  8.       7369 SMITH             800
  9.       7900 JAMES             950
  10.       7876 ADAMS            1100
  11.       7521 WARD             1250
  12.       7654 MARTIN           1250
  13.       7934 MILLER           1300
  14.       1111 MILLER           1300
  15.       7844 TURNER           1500
  16.       7499 ALLEN            1600
  17.       7782 CLARK            2450
  18.       7698 BLAKE            2850
  19.       7566 JONES            2975
  20.       7788 SCOTT            3000
  21.       7902 FORD             3000
  22.       7839 KING             5000             
  23.  
  24. 15 螳  給.
  25.  
  26. SQL> --   れ ORDER BY 企. 
  27. SQL> -- ASC  企殊姶 願, 蠍磯蓋螳朱 覈 譯殊  . 
  28. SQL>  SELECT EMPNO, ENAME, SAL
  29.   2   FROM EMP
  30.   3   ORDER BY SAL ASC;
  31.  
  32.      EMPNO ENAME             SAL
  33. ---------- ---------- ----------
  34.       7369 SMITH             800
  35.       7900 JAMES             950
  36.       7876 ADAMS            1100
  37.       7521 WARD             1250
  38.       7654 MARTIN           1250
  39.       7934 MILLER           1300
  40.       1111 MILLER           1300
  41.       7844 TURNER           1500
  42.       7499 ALLEN            1600
  43.       7782 CLARK            2450
  44.       7698 BLAKE            2850
  45.       7566 JONES            2975
  46.       7788 SCOTT            3000
  47.       7902 FORD             3000
  48.       7839 KING             5000
  49.  
  50. 15 螳  給.
  51.  
  52. SQL> -- 蠍 螳 襷 覦  朱   覯語 企螻 蠍 譟壱
  53. SQL> -- DESC る谿 
  54. SQL>  SELECT EMPNO, ENAME, SAL
  55.   2   FROM EMP
  56.   3   ORDER BY SAL DESC;
  57.  
  58.      EMPNO ENAME             SAL
  59. ---------- ---------- ----------
  60.       7839 KING             5000
  61.       7788 SCOTT            3000  <-- 螳 螳.
  62.       7902 FORD             3000  <-- 螳 螳.
  63.       7566 JONES            2975
  64.       7698 BLAKE            2850
  65.       7782 CLARK            2450
  66.       7499 ALLEN            1600
  67.       7844 TURNER           1500
  68.       7934 MILLER           1300  <-- 螳 螳.
  69.       1111 MILLER           1300  <-- 螳 螳.
  70.       7521 WARD             1250  <-- 螳 螳.
  71.       7654 MARTIN           1250  <-- 螳 螳.
  72.       7876 ADAMS            1100
  73.       7900 JAMES             950
  74.       7369 SMITH             800
  75.  
  76. 15 螳  給.
  77.  
  78. SQL> --  蟆郁骸豌 SAL 螳 蟆曙磯 企朱 蟆郁骸襯 
  79. SQL> SELECT EMPNO, ENAME, SAL
  80.   2  FROM EMP
  81.   3  ORDER BY SAL, ENAME;
  82.  
  83.      EMPNO ENAME             SAL
  84. ---------- ---------- ----------
  85.       7369 SMITH             800
  86.       7900 JAMES             950
  87.       7876 ADAMS            1100
  88.       7654 MARTIN           1250
  89.       7521 WARD             1250
  90.       7934 MILLER           1300
  91.       1111 MILLER           1300
  92.       7844 TURNER           1500
  93.       7499 ALLEN            1600
  94.       7782 CLARK            2450
  95.       7698 BLAKE            2850
  96.       7566 JONES            2975
  97.       7902 FORD             3000
  98.       7788 SCOTT            3000
  99.       7839 KING             5000
  100.  
  101. 15 螳  給.
  102.  
  103. SQL> SELECT EMPNO, ENAME, SAL
  104.   2  FROM EMP
  105.   3  ORDER BY SAL DESC, ENAME ASC;
  106.  
  107.      EMPNO ENAME             SAL
  108. ---------- ---------- ----------
  109.       7839 KING             5000
  110.       7902 FORD             3000
  111.       7788 SCOTT            3000
  112.       7566 JONES            2975
  113.       7698 BLAKE            2850
  114.       7782 CLARK            2450
  115.       7499 ALLEN            1600
  116.       7844 TURNER           1500
  117.       7934 MILLER           1300
  118.       1111 MILLER           1300
  119.       7654 MARTIN           1250
  120.       7521 WARD             1250
  121.       7876 ADAMS            1100
  122.       7900 JAMES             950
  123.       7369 SMITH             800
  124.  
  125. 15 螳  給.
  126.  
  127. SQL> -- 襷 SELECT  企 襦  り 覃 
  128. SQL> -- れ螻 螳 襦 覈企 .
  129. SQL> SELECT EMPNO, ENAME, SAL
  130.   2  FROM EMP
  131.   3  ORDER BY 1,2;
  132.  
  133.      EMPNO ENAME             SAL
  134. ---------- ---------- ----------
  135.       1111 MILLER           1300
  136.       7369 SMITH             800
  137.       7499 ALLEN            1600
  138.       7521 WARD             1250
  139.       7566 JONES            2975
  140.       7654 MARTIN           1250
  141.       7698 BLAKE            2850
  142.       7782 CLARK            2450
  143.       7788 SCOTT            3000
  144.       7839 KING             5000
  145.       7844 TURNER           1500
  146.       7876 ADAMS            1100
  147.       7900 JAMES             950
  148.       7902 FORD             3000
  149.       7934 MILLER           1300
  150.  
  151. 15 螳  給.
  152.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

11 讌螻 螻 GROUP BY #


伎 所 豐覲伎れ 企糾 殊 讌螻 GROUP BY 伎 覲願給. 讌螻 企 覯 螳讌 一一 . れ 蠏碁襦 磯狩 覲伎蠍 覦. 

  1. SQL> CREATE TABLE A_FN(
  2.   2       讌螻 VARCHAR(20) PRIMARY KEY,
  3.   3       る    VARCHAR(30) );
  4.  
  5. 企 焔給.
  6.  
  7. SQL> INSERT INTO A_FN VALUES ('AVG(覈覈)','蠏螳');
  8.  
  9. 1 螳  襷れ伎給.
  10.  
  11. SQL> INSERT INTO A_FN VALUES ('COUNT(覈覈  *)', '蟆 貊 豐 ');
  12.  
  13. 1 螳  襷れ伎給.
  14.  
  15. SQL> INSERT INTO A_FN VALUES ('MAX(覈覈)', '豕螻螳');
  16.  
  17. 1 螳  襷れ伎給.
  18.  
  19. SQL> INSERT INTO A_FN VALUES ('MIN(覈覈)', '豕螳');
  20.  
  21. 1 螳  襷れ伎給.
  22.  
  23. SQL> INSERT INTO A_FN VALUES ('SUM(覈覈)', '螻');
  24.  
  25. 1 螳  襷れ伎給.
  26.  
  27. SQL> COMMIT;
  28.  
  29. 貉る 襭給.
  30.  
  31. SQL> SELECT * FROM A_FN
  32.   2  ORDER BY 讌螻;
  33.  
  34. 讌螻             る
  35. -------------------- ------------------------------
  36. AVG(覈覈)          蠏螳
  37. COUNT(覈覈  *) 蟆 貊 豐 
  38. MAX(覈覈)          豕螻螳
  39. MIN(覈覈)          豕螳
  40. SUM(覈覈)          螻
  41.  
  42. SQL> 
  43.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

る 蟆豌 螳 れ 企 讌 蠏 轟 讌 れ 讌螻襯 . 讌螻 螳螳 る 誤 蟆覲企る れ旧 牛伎 朱 蟆 譬給. れ旧朱 覦襦 れ願蟆給.

  1. SQL> -- 覈  蠍  蠏
  2. SQL> SELECT AVG(SAL) FROM EMP;
  3.  
  4.   AVG(SAL)
  5. ----------
  6. 2021.66667
  7.  
  8. SQL> -- EMP 企 豐  螳
  9. SQL> SELECT COUNT(*) FROM EMP;
  10.  
  11.   COUNT(*)
  12. ----------
  13.         15
  14.  
  15. SQL> -- 譴 螳 襷 蠍
  16. SQL> SELECT MAX(SAL) FROM EMP;
  17.  
  18.   MAX(SAL)
  19. ----------
  20.       5000
  21.  
  22. SQL> -- 譴 螳  蠍
  23. SQL> SELECT MIN(SAL) FROM EMP;
  24.  
  25.   MIN(SAL)
  26. ----------
  27.        800
  28.  
  29. SQL> -- 豌 蟆 讌蠍 蠍 豐
  30. SQL> SELECT SUM(SAL) FROM EMP;
  31.  
  32.   SUM(SAL)
  33. ----------
  34.      30325
  35.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

讌螻 螳螳 蠍磯蓋 螳給. 襷 轟 譟郁唄 蟇瑚, 讌 蟆郁骸螳 詞朱る GROUP BY 襯 譴 . れ 覺.

  1. SQL> -- 螳 覿覲  覦 蠍 豐?
  2. SQL> SELECT DEPTNO, SUM(SAL)
  3.   2  FROM EMP
  4.   3  GROUP BY DEPTNO;
  5.  
  6.     DEPTNO   SUM(SAL)
  7. ---------- ----------
  8.         10      10050
  9.         20      10875
  10.         30       9400
  11. SQL> -- 覿覯瑚 10  覿  れ 蠍 豐?
  12. SQL> SELECT DEPTNO, SUM(SAL)
  13.   2  FROM EMP
  14.   3  WHERE DEPTNO = 10
  15.   4  GROUP BY DEPTNO;
  16.  
  17.     DEPTNO   SUM(SAL)
  18. ---------- ----------
  19.         10      10050
  20.  
  21. SQL> -- 螳 覿   覈覈瑚?
  22. SQL> SELECT DEPTNO, COUNT(*) FROM EMP
  23.   2  GROUP BY DEPTNO;
  24.  
  25.     DEPTNO   COUNT(*)
  26. ---------- ----------
  27.         10          4
  28.         20          5
  29.         30          6
  30. SQL> -- 80   覈覈瑚?
  31. SQL> SELECT HIREDATE, COUNT(*) FROM EMP
  32.   2  WHERE HIREDATE LIKE '80%'
  33.   3  GROUP BY HIREDATE;
  34.  
  35. HIREDATE   COUNT(*)
  36. -------- ----------
  37. 80/12/17          1        
  38.  
  39. SQL> -- 讌螻 蠍磯蓋朱 螳 誤螻 螻壱.
  40. SQL> SELECT SUM(COMM) FROM EMP;
  41.  
  42.  SUM(COMM)
  43. ----------
  44.       2200
  45.  
  46. SQL> -- 企 蟆曙磯 螳 誤讌 朱襦 燕 蟆郁骸螳 .
  47. SQL> SELECT SUM(SAL + COMM) FROM EMP;
  48.  
  49. SUM(SAL+COMM)
  50. -------------
  51.          7800
  52.  
  53. SQL> -- NVL() 螳 覯讌 朱誤磯 豺. 豌覯讌 朱誤磯 覲危 螻壱
  54. SQL> --  貉殊 企 .
  55. SQL> SELECT SUM(SAL + NVL(COMM, 0)) FROM EMP;
  56.  
  57. SUM(SAL+NVL(COMM,0))
  58. --------------------
  59.                32525
  60.  
  61. SQL> SELECT SUM(SAL) + SUM(COMM) FROM EMP;
  62.  
  63. SUM(SAL)+SUM(COMM)
  64. ------------------
  65.              32525
  66.  
  67. SQL> -- "譯殊!!!" GROUP BY襯  SELECT  讌螻 GROUP BY 讌 覈 
  68. SQL> -- 語 企 貉朱  . 螳企慨覃 螳螳  覈 螻 
  69. SQL> -- 覯碁ゼ 螳讌螻  SELECT  覯, 覿覯碁ゼ 譟壱螻 
  70. SQL> -- 朱 螳  覿覯碁 GROUP BY   企至 譟壱襯   蟆螳?
  71. SQL> -- 危願 螳る  る 谿蠏 谿蠏 曙 覲伎蠍....
  72. SQL> SELECT EMPNO, DEPTNO, COUNT(*)
  73.   2  FROM EMP
  74.   3  WHERE DEPTNO = 10
  75.   4  GROUP BY DEPTNO;
  76. SELECT EMPNO, DEPTNO, COUNT(*)
  77.        *
  78. 1 る:
  79. ORA-00979: GROUP BY   給                   
  80.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

12 貎朱Μ覓語 蠍磯蓋 豌襴螻殊 #


朱 讌 螳 ″ 覿 襷朱Μ . 蟆曙磯 GROUP BY螳 れ願 貎朱Μ覓語 麹 危願 螳給. 伎 GROUP BY螳 れ願 SELECT覓語 企至 豌襴 蟆郁骸朱 蟆 覲伎讌螳襯 危ｴ覲願給. れ 襯 螳讌螻 る蟆給. 

  1. SQL> SELECT DEPTNO, SUM(SAL), COUNT(*)
  2.   2  FROM EMP
  3.   3  WHERE HIREDATE LIKE '81%'
  4.   4  GROUP BY DEPTNO
  5.   5  ORDER BY SUM(SAL);
  6.  
  7. (1) 磯Μ螳 豌 危ｴ覲碁  襾殊 蠏狩 蟆 FROM  危 企. 
  8.  
  9.    EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  10. -------- ---------- --------- ---------- -------- ---------- ---------- ----------
  11.     7369 SMITH      CLERK           7902 80/12/17        800                    20
  12.     7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  13.     7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  14.     7566 JONES      MANAGER         7839 81/04/02       2975                    20
  15.     7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  16.     7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  17.     7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  18.     7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  19.     7839 KING       PRESIDENT            81/11/17       5000                    10
  20.     7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  21.     7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  22.     7900 JAMES      CLERK           7698 81/12/03        950                    30
  23.     7902 FORD       ANALYST         7566 81/12/03       3000                    20
  24.     7934 MILLER     CLERK           7782 82/01/23       1300                    10
  25.     1111 MILLER     CLE%RK          7782 82/01/23       1300                    10
  26.  
  27. (2) 蠏 れ 譟郁唄 蠍一 HIREDATE LIKE '81%'  蟆襷 蟇碁.
  28.  
  29.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  30. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  31.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  32.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  33.       7566 JONES      MANAGER         7839 81/04/02       2975                    20
  34.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  35.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  36.       7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  37.       7839 KING       PRESIDENT            81/11/17       5000                    10
  38.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  39.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  40.       7902 FORD       ANALYST         7566 81/12/03       3000                    20
  41.  
  42. (3) れ DEPTNO 覲襦 蠏碁９ 讌 譴.                  
  43.        SAL     DEPTNO
  44. ---------- ----------
  45.       1600         30 <-- 蠏碁９3
  46.       1250         30 <-- 蠏碁９3
  47.       2975         20 <-- 蠏碁９2
  48.       1250         30 <-- 蠏碁９3
  49.       2850         30 <-- 蠏碁９3
  50.       2450         10 <-- 蠏碁９1
  51.       5000         10 <-- 蠏碁９1
  52.       1500         30 <-- 蠏碁９3
  53.        950         30 <-- 蠏碁９3
  54.       3000         20 <-- 蠏碁９2
  55.  
  56. (4) SELECT  蠍一 讌螻襯 伎伎 蠏碁９1, 蠏碁９2, 蠏碁９3 伎 螳 蠏碁９ 
  57.     螻襯 願, 螳 蠏碁９   襯  , SELECT  蠍一 襦 
  58.     . 襷  襷 蠏碁９朱   る 蠏碁９  讀螳蟆讌. 蠍一 蠏碁９1,
  59.     蠏碁９2.. 螳 朱 る 蠍 伎 覿 蟆.   
  60.  
  61.     DEPTNO   SUM(SAL)   COUNT(*)
  62. ---------- ---------- ----------
  63.         10       7450          2   --> 蠏碁９ 1 : 覿覯 10 
  64.         20       5975          2   --> 蠏碁９ 2 : 覿覯 20 
  65.         30       9400          6   --> 蠏碁９ 3 : 覿覯 30 
  66.  
  67. (5) 襷讌襷朱 ORDER BY 蠍一 襦 SUM(SAL)襦 企殊姶    蟆 蟆郁骸
  68.     讌 覲伎譴. 
  69.  
  70.     DEPTNO   SUM(SAL)   COUNT(*)
  71. ---------- ---------- ----------
  72.         20       5975          2
  73.         10       7450          2
  74.         30       9400          6        
  75.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

襴覃 蟆 FROM --> WHERE --> GROUP BY --> ORDER BY --> SELECT 朱 豌襴. 願 覲企 蟆 谿朱 豌襴 蟆 ? 讌覓語 讌 給. DBMS 襦蠏碁覦 語企 襷 蟆企襦 谿朱 豌襴 覦 . 手 一 覿 螻 蟆. 蠏碁 願 豐燕蟲 れ 襴覃伎 '覿 螳譯?' '~! るジ 襷 磯蟾 るジ 譟郁 り 一~!!' 手 一 蟆螻 螳給. 譴 蟆 DBMS螳 旧 蠍 狩 SQL企 螳 螳 蟆郁骸讌 蟆郁骸襯 讌朱 豌襴る 蟆. 覿 覿 蠍壱 ろ企ゼ 讌 螻, 蟲譟一願 讌 螻襯 蠍磯ゼ 覦 訖.

[edit]

13 HAVING #


蠏碁９ 伎 譬 危ｴ覲企襦 蟆給. 襷 覿 " 5覈 伎 覿 覿覯, 蠏 覿 蠍 豐螻 覿 " 襯 螻 矩る 企至 伎 蟾? 蠏碁９朱 覓苦 譟郁唄 蠍一 譴 蟆讌. 朱 譟郁唄 WHERE 一襷 蠏碁９ 譟郁唄 HAVING . 襯 蠏碁襦 螻 HAVING 襷 щ 覲願給. 

  1. SQL> SELECT DEPTNO, SUM(SAL), COUNT(*)
  2.   2  FROM EMP
  3.   3  WHERE HIREDATE LIKE '81%'
  4.   4  GROUP BY DEPTNO
  5.   5  HAVING COUNT(*) >= 5
  6.   6  ORDER BY SUM(SAL);
  7.  
  8.     DEPTNO   SUM(SAL)   COUNT(*)
  9. ---------- ---------- ----------
  10.         30       9400          6
  11.  
  12. SQL> -- HAVING SELECT  豌襴蠍 覦襦  . 
  13.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

14 譟一語 #


伎蟾讌 磯Μ 企襷 螳讌螻 譟壱襯 給. 蠏碁 蟯螻 覈(る狩伎 螳豌-蟯螻 DBMS企.) 蠏 伎 壱 讌螻 蟯螻襯 襷崎 給. 願 螳 企 覲願 襷 豢レ る 覩語. れ襦 企襷 る 覲企 襷豺 給. 覓朱 一危覯伎れ 企 覲企ゼ 襷 豢ロ .

壱 讌 覓朱Μ朱 蟲覃 企 . 覓朱 蠏 螻殊 蟇一覃 壱 讌 螳襦 讓手讌. 企蟆 讓手讌蟇磯 覿 蟯螻襯 襷崎 螳 伎 企 譟壱襯 JOIN . JOIN 覯 伎覿 れ旧 牛 牛覲企襦 蟆給.

  1. SQL> -- 覯語 企螻 蠏   覿覈 譟壱
  2. SQL> -- WHERE EMP.DEPTNO = DEPT.DEPTNO 譟郁唄 譯殊. EMP.DEPTNO 企.貉朱
  3. SQL> SELECT EMP.EMPNO, EMP.ENAME,EMP.DEPTNO,DEPT.DNAME
  4.   2  FROM EMP, DEPT
  5.   3  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  6.  
  7.      EMPNO ENAME          DEPTNO DNAME
  8. ---------- ---------- ---------- --------------
  9.       7369 SMITH              20 RESEARCH
  10.       7499 ALLEN              30 SALES
  11.       7521 WARD               30 SALES
  12.       7566 JONES              20 RESEARCH
  13.       7654 MARTIN             30 SALES
  14.       7698 BLAKE              30 SALES
  15.       7782 CLARK              10 ACCOUNTING
  16.       7788 SCOTT              20 RESEARCH
  17.       7839 KING               10 ACCOUNTING
  18.       7844 TURNER             30 SALES
  19.       7876 ADAMS              20 RESEARCH
  20.       7900 JAMES              30 SALES
  21.       7902 FORD               20 RESEARCH
  22.       7934 MILLER             10 ACCOUNTING
  23.       1111 MILLER             10 ACCOUNTING
  24.  
  25. 15 螳  給.
  26.  
  27. SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME
  28.   2  FROM EMP, DEPT
  29.   3  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  30. SELECT EMPNO, ENAME, DEPTNO, DNAME
  31.                      *
  32. 1 る:
  33. ORA-00918: 伎 螳 襷ろ
  34.  
  35. SQL> -- DEPTNO EMP企螻 DEPT企 覈 譟伎覩襦 企 企 螳語讌 
  36. SQL> -- る狩伎 讌 覈詩. DEPTNO  DEPT.DEPTNO襦 企慨
  37. SQL> SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME
  38.   2  FROM EMP, DEPT
  39.   3  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  40.  
  41.      EMPNO ENAME          DEPTNO DNAME
  42. ---------- ---------- ---------- --------------
  43.       7369 SMITH              20 RESEARCH
  44.       7499 ALLEN              30 SALES
  45.       7521 WARD               30 SALES
  46.       7566 JONES              20 RESEARCH
  47.       7654 MARTIN             30 SALES
  48.       7698 BLAKE              30 SALES
  49.       7782 CLARK              10 ACCOUNTING
  50.       7788 SCOTT              20 RESEARCH
  51.       7839 KING               10 ACCOUNTING
  52.       7844 TURNER             30 SALES
  53.       7876 ADAMS              20 RESEARCH
  54.       7900 JAMES              30 SALES
  55.       7902 FORD               20 RESEARCH
  56.       7934 MILLER             10 ACCOUNTING
  57.       1111 MILLER             10 ACCOUNTING
  58.  
  59. 15 螳  給.
  60.  
  61. SQL> -- 蠎 譟郁唄 EMP.DEPTNO = DEPT.DEPTNO 襯 覈 譯殊伎 螳?
  62. SQL> -- 襯 覃 4レ  襷 覲 豺危 螻煙 覯襴磯. 
  63. SQL> -- 覿 襾碁Μ  蟆 覈 SQL襦 伎朱 . 襾碁Μ 襷企蟆 讌
  64. SQL> -- 襷螻  語. ^^;
  65. SQL> SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME
  66.   2  FROM EMP, DEPT;
  67.  
  68.      EMPNO ENAME          DEPTNO DNAME
  69. ---------- ---------- ---------- -----------
  70.       7369 SMITH              10 ACCOUNTING
  71.       7499 ALLEN              10 ACCOUNTING
  72.       7521 WARD               10 ACCOUNTING
  73.         .   .                  .    .
  74.         .   .                  .    .
  75.         .   .                  .    .
  76.       7900 JAMES              40 OPERATIONS
  77.       7902 FORD               40 OPERATIONS
  78.       7934 MILLER             40 OPERATIONS
  79.       1111 MILLER             40 OPERATIONS
  80.  
  81. 60 螳  給.
  82.  
  83. SQL> -- 譟一語 れ牛蠍  蟆 襷り鍵.
  84. SQL> UPDATE EMP
  85.   2  SET DEPTNO = NULL
  86.   3  WHERE EMPNO=1111;
  87.  
  88. 1  螳煙給.
  89.  
  90. SQL> SELECT DEPTNO FROM EMP
  91.   2  WHERE EMPNO = 1111;
  92.  
  93.     DEPTNO
  94. ----------
  95. SQL> --  襯  危ｴ覲伎蠍...
  96. SQL> -- 碁譟一語 譴螻 る狩伎 覦覯  譴  蟆  蟠ロ...
  97. SQL> -- MS-SQL SERVER  (+) 蠍壱碁 碁譟一語 
  98. SQL> -- 殊 碁 譟一 (譴覦覯)
  99. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  100.   2  FROM EMP LEFT OUTER JOIN DEPT
  101.   3  ON EMP.DEPTNO = DEPT.DEPTNO;
  102.  
  103.      EMPNO ENAME          DEPTNO
  104. ---------- ---------- ----------
  105.       7369 SMITH              20
  106.       7499 ALLEN              30
  107.       7521 WARD               30
  108.       7566 JONES              20
  109.       7654 MARTIN             30
  110.       7698 BLAKE              30
  111.       7782 CLARK              10
  112.       7788 SCOTT              20
  113.       7839 KING               10
  114.       7844 TURNER             30
  115.       7876 ADAMS              20
  116.       7900 JAMES              30
  117.       7902 FORD               20
  118.       7934 MILLER             10
  119.       1111 MILLER                  -->   企至伎 蟾襯 螳伎 
  120.  
  121. 15 螳  給.
  122. SQL> -- る狩伎  螳 殊 碁譟一語   .  
  123. SQL> -- (+) 蠍壱瑚 覿 覩碁 殊曙  企(蠍一 EMP) щ碁る 覩碁 覲
  124. SQL> -- 覃 . 
  125. SQL> -- SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  126.   2  -- FROM EMP, DEPT
  127.   3  -- WHERE EMP.DEPTNO = DEPT.DEPTNO(+);
  128. SQL>
  129. SQL> -- るジ讓 碁 譟一 (譴覦覯)
  130. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  131.   2  FROM EMP RIGHT OUTER JOIN DEPT
  132.   3  ON EMP.DEPTNO = DEPT.DEPTNO;
  133.  
  134.      EMPNO ENAME          DEPTNO
  135. ---------- ---------- ----------
  136.       7369 SMITH              20
  137.       7499 ALLEN              30
  138.       7521 WARD               30
  139.       7566 JONES              20
  140.       7654 MARTIN             30
  141.       7698 BLAKE              30
  142.       7782 CLARK              10
  143.       7788 SCOTT              20
  144.       7839 KING               10
  145.       7844 TURNER             30
  146.       7876 ADAMS              20
  147.       7900 JAMES              30
  148.       7902 FORD               20
  149.       7934 MILLER             10
  150.                               40   -->   企至伎 蟾襯 螳伎 
  151.  
  152. 15 螳  給.
  153.  
  154. SQL> -- る狩伎 るジ讓 碁 譟一語  螳 企 .
  155. SQL> -- SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  156.   2  -- FROM EMP, DEPT
  157.   3  -- WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;
  158. SQL> -- 讓 碁 譟一 (譴覦覯)
  159. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  160.   2  FROM EMP FULL OUTER JOIN DEPT
  161.   3  ON EMP.DEPTNO = DEPT.DEPTNO;
  162.  
  163.      EMPNO ENAME          DEPTNO
  164. ---------- ---------- ----------
  165.       7369 SMITH              20
  166.       7499 ALLEN              30
  167.       7521 WARD               30
  168.       7566 JONES              20
  169.       7654 MARTIN             30
  170.       7698 BLAKE              30
  171.       7782 CLARK              10
  172.       7788 SCOTT              20
  173.       7839 KING               10
  174.       7844 TURNER             30
  175.       7876 ADAMS              20
  176.       7900 JAMES              30
  177.       7902 FORD               20
  178.       7934 MILLER             10   
  179.       1111 MILLER                  -->   企至伎 蟾襯 螳伎 
  180.                               40   -->   企至伎 蟾襯 螳伎 
  181.  
  182. 16 螳  給.
  183.  
  184. SQL> -- 所 企蟆 讌 . 讓 碁譟一語  譴 磯殊 .
  185. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  186.   2  FROM EMP, DEPT
  187.   3  WHERE EMP.DEPTNO(+) = DEPT.DEPTNO(+);
  188. WHERE EMP.DEPTNO(+) = DEPT.DEPTNO(+)
  189.                     *
  190. 3 る:
  191. ORA-01468: outer-join 企 1螳襷 讌  給
  192.  
  193. SQL> -- れ 觜蟲 覲伎蠍 覦.
  194. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  195.   2  FROM EMP, DEPT
  196.   3  WHERE EMP.DEPTNO(+) = DEPT.DEPTNO
  197.   4  AND EMPNO(+) = 7521;
  198.  
  199.      EMPNO ENAME          DEPTNO
  200. ---------- ---------- ----------
  201.                               10
  202.                               20
  203.       7521 WARD               30
  204.                               40
  205.  
  206. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  207.   2  FROM EMP RIGHT OUTER JOIN DEPT
  208.   3  ON EMP.DEPTNO = DEPT.DEPTNO
  209.   4  WHERE EMP.EMPNO = 7521;          --> WHERE  ...
  210.  
  211.      EMPNO ENAME          DEPTNO
  212. ---------- ---------- ----------
  213.       7521 WARD               30
  214.  
  215. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  216.   2  FROM EMP RIGHT OUTER JOIN DEPT
  217.   3  ON EMP.DEPTNO = DEPT.DEPTNO
  218.   4  AND EMP.EMPNO = 7521;            --> ON  AND 襯 伎伎 一一 .
  219.  
  220.      EMPNO ENAME          DEPTNO
  221. ---------- ---------- ----------
  222.       7521 WARD               30
  223.                               40
  224.                               20
  225.                               10
  226.  
  227. SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO
  228.   2  FROM EMP LEFT OUTER JOIN DEPT      --> 殊 碁 譟一 
  229.   3  ON EMP.DEPTNO = DEPT.DEPTNO
  230.   4  AND EMP.EMPNO IN (7521, 7369, 7788);
  231.  
  232.      EMPNO ENAME          DEPTNO
  233. ---------- ---------- ----------
  234.       7369 SMITH              20
  235.       7499 ALLEN
  236.       7521 WARD               30
  237.       7566 JONES
  238.       7654 MARTIN
  239.       7698 BLAKE
  240.       7782 CLARK
  241.       7788 SCOTT              20
  242.       7839 KING
  243.       7844 TURNER
  244.       7876 ADAMS
  245.       7900 JAMES
  246.       7902 FORD
  247.       7934 MILLER
  248.       1111 MILLER
  249.  
  250. 15 螳  給.
  251.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

MS-SQL Server 碁譟一
||MS-SQL Server 譴 譟一碁逢覯螻 MS-SQL Server豌伎 譟一語 覦覯 螻牛螻 給. MS-SQL Server 豌伎 譟一 覦覯 '=*', '*=', '*=*'襦 譟一 一一 襦螻 給. 蠏碁 MS豸′ 伎 螳 覦覯 豢豌讌 朱, 譴 襷蟆 譟一語
一 襦 蟠螻 給. MS 譴 譟一 覦覯 蟠 伎 譴 讌 豺 覲企ゼ 豢ロ 蠍 覓語.||

[edit]

15 觚貎朱Μ #


伎 觚貎朱Μ 伎 覲願給. 覲危旧 觚貎朱Μ WHERE 觜蟲襯 蠍 るジ 貎朱Μ襯 伎. 朱朱 觚貎朱Μ螳 襾殊 豌襴螻, 覃語深襴螳 . 覿れ 觚貎朱Μ襯 燕 襾殊 貎朱Μ襯 燕 覃語深襴襯 燕伎 蟆 . 一 螳 蟆 觚貎朱Μ 襯 螳螻 觚貎朱Μ襯 燕伎狩覃, WHERE 觜蟲 蟆 碁 覲旧螳瑚襯 螳伎 一一襯 伎 . '覦焔語企狩' .

  1. SQL> --朱 觚貎朱Μ 
  2. SQL> SELECT * FROM EMP
  3.   2  WHERE DEPTNO = (SELECT DEPTNO 
  4.   3                  FROM DEPT
  5.   4                  WHERE DEPTNO = 30); 
  6.  
  7.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  8. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  9.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  10.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  11.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  12.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  13.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  14.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  15.  
  16. 6 螳  給.
  17.  
  18. SQL> --  觚貎朱Μ 螳 蟆郁骸襯 企 譟一語一
  19. SQL> --  觚貎朱Μ 企朱 伎螳 覲 . 蠏碁覩襦 觚貎朱Μ襯 譟
  20. SQL> -- 語朱 覦蠑碁 螻殊  ろ 殊企襦 襷伎 觚貎朱Μ覲企 譟一語 
  21. SQL> -- 蟠ロ. 蠏碁  螳 譟郁唄企朱 觚貎朱Μ螳 焔レ  觜襯  . 
  22. SQL> -- 蟆  觚貎朱Μ 譟一語 螻 襷 蟆企. 
  23. SQL> SELECT EMP.* 
  24.   2  FROM EMP, DEPT
  25.   3  WHERE EMP.DEPTNO = DEPT.DEPTNO
  26.   4  AND DEPT.DEPTNO = 30;
  27.  
  28.      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
  29. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  30.       7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  31.       7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
  32.       7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
  33.       7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  34.       7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  35.       7900 JAMES      CLERK           7698 81/12/03        950                    30
  36.  
  37. 6 螳  給.
  38.  
  39. SQL> -- IN 一一襯 伎 觚貎朱Μ
  40. SQL> SELECT EMPNO, ENAME, HIREDATE
  41.   2  FROM EMP
  42.   3  WHERE EMPNO IN (SELECT EMPNO
  43.   4                     FROM EMP
  44.   5                     WHERE HIREDATE LIKE '82%'); 
  45.  
  46.      EMPNO ENAME      HIREDATE
  47. ---------- ---------- --------
  48.       7934 MILLER     82/01/23
  49.       1111 MILLER     82/01/23
  50.  
  51. SQL> -- ALL 一一襯 伎 觚貎朱Μ... ALL 一一螳 る 觚貎朱Μ  焔 覈
  52. SQL> -- 覈 螳 譟郁唄 襷譟燕 襷 蟆 譟郁唄  TRUE螳 .
  53. SQL> -- 企 DEPTNO 30  蠍覲企る  襷 蠍 覦  ENAME, SAL, 
  54. SQL> -- DEPTNO蟆
  55. SQL> SELECT ENAME, SAL, DEPTNO 
  56.   2  FROM EMP
  57.   3  WHERE SAL > ALL (SELECT SAL
  58.   4                   FROM EMP
  59.   5                   WHERE DEPTNO = 30);
  60.  
  61. ENAME             SAL     DEPTNO
  62. ---------- ---------- ----------
  63. JONES            2975         20
  64. SCOTT            3000         20
  65. KING             5000         10
  66. FORD             3000         20
  67.  
  68. SQL> -- 蟆郁記  螳 貎朱Μ 螳 覩瑚 . 
  69. SQL> SELECT ENAME, SAL, DEPTNO 
  70.   2  FROM EMP
  71.   3  WHERE SAL > 2850;
  72.  
  73. ENAME             SAL     DEPTNO
  74. ---------- ---------- ----------
  75. JONES            2975         20
  76. SCOTT            3000         20
  77. KING             5000         10
  78. FORD             3000         20
  79.  
  80. SQL> -- ANY/SOME 一一 觚貎朱Μ 伎 焔 螳 譴 企 朱 譟郁唄 襷譟燕
  81. SQL> -- 覃 蟆 譟郁唄 TRUE螳 .
  82. SQL> -- ANY/SOME 螻朱 狩覃,  ANY, IN, ALL 一一 觜蟲襯 覲伎蠍 覦.
  83.  SQL> SELECT ENAME, SAL, DEPTNO 
  84.   2  FROM EMP
  85.   3  WHERE SAL = ALL (SELECT SAL
  86.   4                   FROM EMP
  87.   5                   WHERE DEPTNO = 30);
  88.  
  89.  貊螳 給.
  90.  
  91. SQL> SELECT ENAME, SAL, DEPTNO 
  92.   2  FROM EMP
  93.   3  WHERE SAL IN (SELECT SAL
  94.   4                   FROM EMP
  95.   5                   WHERE DEPTNO = 30);
  96.  
  97. ENAME             SAL     DEPTNO
  98. ---------- ---------- ----------
  99. JAMES             950         30
  100. WARD             1250         30
  101. MARTIN           1250         30
  102. TURNER           1500         30
  103. ALLEN            1600         30
  104. BLAKE            2850         30
  105.  
  106. 6 螳  給.
  107.  
  108. SQL> SELECT ENAME, SAL, DEPTNO 
  109.   2  FROM EMP
  110.   3  WHERE SAL = ANY (SELECT SAL
  111.   4                   FROM EMP
  112.   5                   WHERE DEPTNO = 30);
  113.  
  114. ENAME             SAL     DEPTNO
  115. ---------- ---------- ----------
  116. JAMES             950         30
  117. WARD             1250         30
  118. MARTIN           1250         30
  119. TURNER           1500         30
  120. ALLEN            1600         30
  121. BLAKE            2850         30
  122.  
  123. 6 螳  給.
  124.  
  125. SQL> -- EXISTS 一一 觚貎朱Μ 蟆郁骸螳 螳 螳 讀, TRUE語 FLASE 語 
  126. SQL> --  一一企. るジ 一一 觜 TURE/FALSE襷 覃企覩襦 螳覲蟆   
  127. SQL> --  襷るレ  一一
  128. SQL> SELECT ENAME, JOB, DEPTNO
  129.   2  FROM EMP
  130.   3  WHERE EXISTS (SELECT *           -- 譯殊 蟆.. EXISTS 一一  覓願 .
  131.   4                FROM DEPT
  132.   5                WHERE DEPT.DEPTNO = EMP.DEPTNO
  133.   6                AND DEPTNO = 30); 
  134.  
  135. ENAME      JOB           DEPTNO
  136. ---------- --------- ----------
  137. ALLEN      SALESMAN          30
  138. WARD       SALESMAN          30
  139. MARTIN     SALESMAN          30
  140. BLAKE      MANAGER           30
  141. TURNER     SALESMAN          30
  142. JAMES      CLERK             30
  143.  
  144. 6 螳  給.
  145.  
  146. SQL> -- IN 一一襯 伎伎 螳 蟆郁骸襯   . 蠏碁 EXISTS 一一螳  螳覲.
  147. SQL> SELECT ENAME, JOB, DEPTNO
  148.   2  FROM EMP
  149.   3  WHERE DEPTNO IN (SELECT DEPTNO
  150.   4                   FROM DEPT
  151.   5                   WHERE DEPT.DEPTNO = EMP.DEPTNO
  152.   6                   AND DEPTNO = 30);
  153.  
  154. ENAME      JOB           DEPTNO
  155. ---------- --------- ----------
  156. ALLEN      SALESMAN          30
  157. WARD       SALESMAN          30
  158. MARTIN     SALESMAN          30
  159. BLAKE      MANAGER           30
  160. TURNER     SALESMAN          30
  161. JAMES      CLERK             30
  162.  
  163. 6 螳  給.
  164.  
  165. SQL> -- 蟯觚貎朱Μ: 覦蟾レ SELECT 覓語 貉殊 螳 觚貎朱Μ 譟郁唄 伎
  166. SQL> -- 襾殊 觚貎朱Μ 伎 覃語深襴 貉 一危襯 蟲螻 觚貎朱Μ襯 ろ ,
  167. SQL> -- 蠏 蟆郁骸螳 伎伎 覃語深襴襯 ろ
  168. SQL> -- 企 伎襦 蟯觚貎朱Μ 貎朱Μ焔レ 伎. 襷覃 企 貎朱Μ襯 襷れ 襷
  169. SQL> -- 襦 伎狩.
  170. SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 
  171.   2  FROM EMP MAIN_QUERY               
  172.   3  WHERE SAL > (SELECT SUM(COMM)
  173.   4               FROM DEPT
  174.   5               WHERE DEPT.DEPTNO = MAIN_QUERY.DEPTNO
  175.   6               AND DEPT.DEPTNO = 30);
  176.  
  177.      EMPNO ENAME             SAL     DEPTNO
  178. ---------- ---------- ---------- ----------
  179.       7499 ALLEN            1600         30
  180.       7521 WARD             1250         30
  181.       7844 TURNER           1500         30
  182.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

磯Μ 觚貎朱Μ 螳讌 一一 伎 危ｴ覲伎給. れ朱 蠍 譟一誤 SELF JOIN 伎 危ｴ覲企襦 蟆給. SELF JOIN 企 覲旧″ 一一 襷 一. 譟一語 蠍 譟一語願鍵 覓語 覦 覲覈(alias)襯 伎 .

  1. SQL> SELECT T1.EMPNO "MANAGER", T1.ENAME, T2.EMPNO, T2.ENAME, T2.MGR
  2.   2  FROM EMP T1 INNER JOIN EMP T2
  3.   3  ON T1.EMPNO = T2.MGR;  
  4.  
  5.    MANAGER ENAME           EMPNO ENAME             MGR
  6. ---------- ---------- ---------- ---------- ----------
  7.       7902 FORD             7369 SMITH            7902
  8.       7698 BLAKE            7499 ALLEN            7698
  9.       7698 BLAKE            7521 WARD             7698
  10.       7839 KING             7566 JONES            7839
  11.       7698 BLAKE            7654 MARTIN           7698
  12.       7839 KING             7698 BLAKE            7839
  13.       7839 KING             7782 CLARK            7839
  14.       7566 JONES            7788 SCOTT            7566
  15.       7698 BLAKE            7844 TURNER           7698
  16.       7788 SCOTT            7876 ADAMS            7788
  17.       7698 BLAKE            7900 JAMES            7698
  18.       7566 JONES            7902 FORD             7566
  19.       7782 CLARK            7934 MILLER           7782
  20.       7782 CLARK            1111 MILLER           7782
  21.  
  22. 14 螳  給.
  23.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

[edit]

16 讌 一一 #


伎 SELECT 蠍磯蓋 襷讌襷 讌一一襯 る蟆給. 讌一一 讌, 蟲讌, 谿讌. 讌 UNION, 蟲讌 INTERSECT, 谿讌 MINUS 一一襯 . れ旧 覃 覦襦 蟆.

  1. SQL> SELECT EMPNO, ENAME, DEPTNO
  2.   2  FROM EMP
  3.   3  WHERE DEPTNO = 10       .
  4.   4  UNION                   -- UNION 襷   , 譴覲旧蟇 蟾讌 殊企.
  5.   5  SELECT EMPNO, ENAME, DEPTNO
  6.   6  FROM EMP
  7.   7  WHERE DEPTNO = 20;
  8.  
  9.      EMPNO ENAME          DEPTNO
  10. ---------- ---------- ----------
  11.       1111 MILLER             10
  12.       7369 SMITH              20
  13.       7566 JONES              20
  14.       7782 CLARK              10
  15.       7788 SCOTT              20
  16.       7839 KING               10
  17.       7876 ADAMS              20
  18.       7902 FORD               20
  19.       7934 MILLER             10
  20.  
  21. 9 螳  給.
  22. SQL> -- WHERE 'OR DEPTNO = 20' 曙 UNION ALL螻 UNION 觜蟲 覲碁
  23. SQL> SELECT EMPNO, ENAME, DEPTNO
  24.   2  FROM EMP
  25.   3  WHERE DEPTNO = 10
  26.   4  UNION ALL       -- UNION ALL   , 譴覲旧蟇  殊企讌 .
  27.   5  SELECT EMPNO, ENAME, DEPTNO
  28.   6  FROM EMP
  29.   7  WHERE DEPTNO = 20;
  30.  
  31.      EMPNO ENAME          DEPTNO
  32. ---------- ---------- ----------
  33.       7782 CLARK              10
  34.       7839 KING               10
  35.       7934 MILLER             10
  36.       1111 MILLER             10
  37.       7369 SMITH              20
  38.       7566 JONES              20
  39.       7788 SCOTT              20
  40.       7876 ADAMS              20
  41.       7902 FORD               20
  42.  
  43. 9 螳  給.
  44.  
  45. SQL> --  UNION   曙 覲願鍵  蟆
  46. SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL
  48.   3  SELECT 'YASICOM', 'YASICOM' FROM DUAL;
  49. SELECT 'YASI', 'YASI', 'YASI' FROM DUAL
  50. *
  51. 1 る:
  52. ORA-01789: 讌 蟆郁骸 伎 螳 襴暑    --> 覺 
  53.  
  54. SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL
  56.   3  SELECT 'YASICOM', 'YASICOM', 0 FROM DUAL;
  57. SELECT 'YASI', 'YASI', 'YASI' FROM DUAL
  58.                        *
  59. 1 る:
  60. ORA-01790:  螻 螳 一危 伎伎     --> 覺 
  61.  
  62. SQL> SELECT 'YASI', 'YASI', 'YASI' FROM DUAL
  64.   3  SELECT 'YASICOM', 'YASICOM', '0' FROM DUAL;
  65.  
  66. /*
  67. 'YASI'  'YASI'  'YAS
  68. ------- ------- ----
  69. YASI    YASI    YASI
  70. YASICOM YASICOM 0
  71. */
  72.  
  73. SQL> SELECT EMPNO, ENAME, DEPTNO
  74.   2  FROM EMP
  76.   4  SELECT EMPNO, ENAME, DEPTNO
  77.   5  FROM EMP
  78.   6  WHERE DEPTNO = 10;
  79.  
  80.      EMPNO ENAME          DEPTNO
  81. ---------- ---------- ----------
  82.       1111 MILLER             10
  83.       7782 CLARK              10
  84.       7839 KING               10
  85.       7934 MILLER             10
  86.  
  87. SQL> SELECT EMPNO, ENAME, DEPTNO
  88.   2  FROM EMP
  90.   4  SELECT EMPNO, ENAME, DEPTNO
  91.   5  FROM EMP
  92.   6  WHERE DEPTNO = 10;
  93.  
  94.      EMPNO ENAME          DEPTNO
  95. ---------- ---------- ----------
  96.       7369 SMITH              20
  97.       7499 ALLEN              30
  98.       7521 WARD               30
  99.       7566 JONES              20
  100.       7654 MARTIN             30
  101.       7698 BLAKE              30
  102.       7788 SCOTT              20
  103.       7844 TURNER             30
  104.       7876 ADAMS              20
  105.       7900 JAMES              30
  106.       7902 FORD               20
  107.  
  108. 11 螳  給.
  109.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari
鏤
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

襷 譬 蟆螻 譬 覦朱 螳 豈. 襷 蟲滑唄 襷 豈企る 企讌讌 蟆 企 蟾.