Contents

1 INSERT
2 SEQUENCE
3 UPDATE
4 DELETE


1 INSERT #

  1.  
  2. INSERT覓語 一危襯 曙 . 蠍磯蓋 覓碁 れ螻 螳給.
  3.  
  4. INSERT INTO 企覈(1,2, ...)
  5. VALUES (DATA1, DATA2, ...);
  6.  
  7. 一危襯 ロる 襾殊 伎 蟆 覓伎手? 轟壱 企 襷れ伎
  8. . 企 襷 蟆 蠏碁Μ 螳 殊 . れ 譟伎 曙 螻,
  9. 覓朱Μ 蟆蟾讌 螻ろ伎 企 燕伎 .
  10.  
  11. INSERT 螳 伎手鍵 螻 蟆願鍵 覓語 螳牛 覓碁襷
  12. 螳襦 螻, 蠏碁 蠏碁 蟆 譯殊 牛伎 企 詞語襷 螻 螻覃 蟆
  13. . 誤 蟆 TABLE る 覿覿 谿瑚蠍 覦.
  14.  
  15. 豐螳 TABLE 覓碁
  16.  
  17. CREATE TABLE 企覈 (
  18. 貉朱1 一危,
  19. 貉朱2 一危, ....);
  20.  
  21.  
  22.  
  23. 企螳襯 燕螻, 曙企慨襦 蟆給.INSERT襯 蠍
  24. 襯 燕 覲企襦 蟆給. 覓伎 襴語 覈襯伎 覿れ 覓伎^蟇 磯
  25. 蟇磯 るジ 豈企 豪語襯 谿瑚蠍 覦.
  26.  
  27.  
  28.  
  29. SQL> CONNECT SYSTEM/MANAGER
  30. 郁屋給.
  31. SQL> CREATE USER INSERTER IDENTIFIED BY INSERTER
  32. 2 DEFAULT TABLESPACE USERS;
  33.  
  34. 螳 焔給.
  35.  
  36. SQL> GRANT CONNECT, RESOURCE TO INSERTER;
  37.  
  38. 蟠 覿給.
  39.  
  40. SQL> CREATE TABLE TEST_INSERT (
  41. 2 ID NUMBER PRIMARY KEY,
  42. 3 NAME VARCHAR(10));
  43.  
  44. 企 焔給.
  45.  
  46. SQL> INSERT INTO TEST_INSERT VALUES(23, 'YASI');
  47.  
  48. 1 螳 襷れ伎給.
  49.  
  50. SQL> SELECT * FROM TEST_INSERT;
  51.  
  52. ID NAME
  53. ---------- ----------
  54. 23 YASI
  55.  
  56. SQL> -- 襷 貉殊 襯 覦蠑語 ロ螻 矩る?? 貉殊 襯 覦蠖 譯朱 .
  57. SQL> INSERT INTO TEST_INSERT(NAME, ID) VALUES ('伎', 24);
  58.  
  59. 1 螳 襷れ伎給.
  60.  
  61.  
  62.  
  63. 覿 企 伎 螳 蠎 ロ伎 螻 ロ讌
  64. れ 蟆 覲伎 蟆. 蠍一 蠎 ロ伎 る 蟆 NOT NULL 覩誤
  65. 螻 ロ讌 蟆 NULL 螳 る 蟆. 企 蟆 企 朱
  66. レ 讌 れ旧 牛伎 覲企襦 蟆給. 襾殊 螳 企 燕.
  67.  
  68.  
  69.  
  70. SQL> CREATE TABLE STUDENT(
  71. 2 S_NUM CHAR(7) PRIMARY KEY,
  72. 3 S_NAME VARCHAR(8) NOT NULL,
  73. 4 S_PHONE VARCHAR(11));
  74.  
  75. 企 焔給.
  76.  
  77.  
  78.  
  79. 貉殊 覲企 一危 曙
  80.  
  81. SQL> INSERT INTO STUDENT(S_NUM, S_NAME) VALUES('9555023', '伎');
  82.  
  83. 1 螳 襷れ伎給.
  84.  
  85. SQL> INSERT INTO STUDENT(S_NUM, S_NAME, S_PHONE)
  86. 2 VALUES('9555007', '覲', '01198101134');
  87.  
  88. 1 螳 襷れ伎給.
  89.  
  90. SQL> SELECT * FROM STUDENT;
  91.  
  92. S_NUM S_NAME S_PHONE
  93. ------- -------- -----------
  94. 9555023
  95. 9555007 覲 01198101134
  96.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari

2 SEQUENCE #

  1. 覿 企 襷り 朱覯 朱 曙螻 螳 蟆.
  2. る狩伎 る朱 螳豌企ゼ 燕 譴狩. る 朱 讀螳 襯 燕
  3. 譯朱 螳豌伎. 覓碁 れ螻 螳給.
  4.  
  5.  
  6.  
  7. CREATE SEQUENCE SEQUENCE_NAME
  8. INCREMENT BY
  9. START WITH
  10. MAXVALUE 螳 / NOMAXVALUE
  11. MINVALUE 螳 / NOMINVALUE
  12. CYCLE / NOCYCLE
  13. CACHE 螳 / NOCACHE
  14. ORDER / NOODER;
  15.  
  16.  
  17.  
  18. 譯殊伎狩 旧 覈螳襯 危エ覲企 一 MAXVLAUE/NOMAXVALUE螳 旧 り 讀
  19. 螳 豕螳 . MAXVALUE 蠍磯蓋螳 1024. NOMAXVALUE襦 れ
  20. 覃 覓危襦 螳 手蟆 . CYCLE企 旧 れ 豕螳 覃 蠏 れ螳
  21. 朱 豕豐 螳 蟆. 蠍磯蓋螳 螳 . CACHE/NOCACHE朱 旧
  22. 貂襯 襦 讌 蟆. 願 讌讌 朱 蠍磯蓋朱 る狩伎 20
  23. 螳 貂.
  24.  
  25. 煙 企慨蟆給..
  26.  
  27.  
  28.  
  29. SQL> CREATE sequence seq_iii
  30. 2 increament BY 1
  31. 3 start WITH 50
  32. 4 maxvalue 100
  33. 5 nocache
  34. 6 nocycle;
  35. increament BY 1
  36. *
  37. 2 る:
  38. ORA-00933: SQL 覈轟願 覦襯願 譬襭讌 給
  39.  
  40.  
  41. SQL> c /increament BY 1/increment BY 1
  42. 2* increment BY 1
  43. SQL> run
  44. 1 CREATE sequence seq_iii
  45. 2 increment BY 1 -- 1襦 讀螳
  46. 3 start WITH 50 -- 50覿
  47. 4 maxvalue 100 -- 豕螳 100
  48. 5 nocache
  49. 6* nocycle
  50.  
  51. 譯朱碁瑚 焔給.
  52.  
  53. SQL> SELECT seq_iii.NEXTVAL FROM dual;
  54.  
  55. NEXTVAL
  56. ----------
  57. 50
  58.  
  59. SQL> SELECT seq_iii.currval FROM dual;
  60.  
  61. CURRVAL
  62. ----------
  63. 50
  64.  
  65. SQL> CREATE TABLE seq_test (id number);
  66.  
  67. 企 焔給.
  68.  
  69. SQL> CREATE TABLE seq_test (id number);
  70.  
  71. 企 焔給.
  72.  
  73. SQL> INSERT INTO seq_test VALUES (seq_iii.NEXTVAL);
  74.  
  75. 1 螳 襷れ伎給.
  76.  
  77. SQL> SELECT * FROM seq_test;
  78.  
  79. ID
  80. ----------
  81. 51
  82. SQL> -- 企 煙 貉殊 蠍磯蓋螳 れ伎がる INSERT れ螻 螳 覃企
  83.  
  84. SQL> ALTER TABLE STUDENT
  85. 2 ADD APPLY DATE DEFAULT SYSDATE;
  86.  
  87. 企 覲蟆暑給.
  88.  
  89. SQL> INSERT INTO STUDENT
  90. 2 VALUES ('9655023', '煙', '0164506701', DEFAULT);
  91.  
  92. 1 螳 襷れ伎給.
  93.  
  94. SQL> -- APPLY 貉殊 蠍磯蓋螳朱 ろ 讌襯
  95. SQL> SELECT * FROM STUDENT
  96. 2 WHERE S_NUM = '9655023';
  97.  
  98. S_NUM S_NAME S_PHONE APPLY
  99. ------- -------- ----------- --------
  100. 9655023 煙 0164506701 02/05/19
  101.  
  102. SQL> -- DEFAULT 朱 れ襦 蠍磯蓋螳 れ願 覈 譯殊 蠍磯蓋螳 れ願.
  103. SQL> INSERT INTO STUDENT(S_NUM, S_NAME, S_PHONE)
  104. 2 VALUES ('9555037', '語', '0172204580');
  105.  
  106. 1 螳 襷れ伎給.
  107.  
  108. SQL> SELECT * FROM STUDENT
  109. 2 WHERE S_NUM = '9555037';
  110.  
  111. S_NUM S_NAME S_PHONE APPLY
  112. ------- -------- ----------- --------
  113. 9555037 語 0172204580 02/05/19
  114.  
  115.  
  116. れ 覲蟆曙 覈 焔碁螻 螳 start WITH覓語 る 蟆 訖.,
  117. 轟 れ 螳 伎蠍 覓語. DROP sequence sequence_name
  118. .
  119.  
  120.  
  121. 伎 蠍磯蓋 INSERT 覓語 覯 給. 麹 螳譯? 伎 SELECT 覓瑚骸 豎讌
  122. INSERT 覓語 れ牛企慨襦 蟆給. 襾殊 企 燕.
  123.  
  124.  
  125.  
  126. SQL> CREATE TABLE TEMP
  127. 2 (EMPNO NUMBER(4) PRIMARY KEY,
  128. 3 ENAME VARCHAR2(10),
  129. 4 JOB VARCHAR2(9),
  130. 5 MGR NUMBER(4),
  131. 6 HIREDATE DATE,
  132. 7 SAL NUMBER(7,2),
  133. 8 COMM NUMBER(7,2),
  134. 9 DEPTNO NUMBER(2));
  135.  
  136. 企 焔給.
  137.  
  138.  
  139.  
  140.  
  141.  
  142. SQL> INSERT INTO TEMP
  143. 2 SELECT * FROM EMP
  144. 3 WHERE DEPTNO = 20;
  145.  
  146. 5 螳 襷れ伎給.
  147.  
  148. SQL> SELECT * FROM TEMP;
  149.  
  150. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  151. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  152. 7369 SMITH CLERK 7902 80/12/17 800 20
  153. 7566 JONES MANAGER 7839 81/04/02 2975 20
  154. 7788 SCOTT ANALYST 7566 87/04/19 3000 20
  155. 7876 ADAMS CLERK 7788 87/05/23 1100 20
  156. 7902 FORD ANALYST 7566 81/12/03 3000 20
  157.  
  158. SQL> -- 企 覦覯 讌~
  159. SQL> CREATE TABLE TEMP2
  160. 2 AS
  161. 3 SELECT * FROM EMP
  162. 4 WHERE DEPTNO = 10;
  163.  
  164. 企 焔給.
  165.  
  166. SQL> SELECT * FROM TEMP2;
  167.  
  168. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  169. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  170. 7782 CLARK MANAGER 7839 81/06/09 2450 10
  171. 7839 KING PRESIDENT 81/11/17 5000 10
  172. 7934 MILLER CLERK 7782 82/01/23 1300 10
  173. 1111 MILLER CLE%RK 7782 82/01/23 1300 10
  174.  
  175.  
  176.  
  177. 企蟆 SELECT 覓語 伎伎 INSERT襯 給. 譬 覲旧″ 貎朱Μ覓語 曙
  178. 蟆讌.
  179.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari


3 UPDATE #

  1. UPDATE 至骸 螳 覲蟆暑 螳 蟆 覩誤. UPDATE INSERT
  2. 螳讌螻 給. 覿 壱襦 誤語 螻 螻豺 企至 ? 讌郁襦 讌願
  3. 蠏 れ れ 覲蟆暑 l給.UPDATE 覓碁 れ螻 螳給.
  4.  
  5.  
  6.  
  7. UPDATE TABLE_NAME
  8. SET COLUMN_NAME = VALUE
  9. WHERE CONDITION...
  10.  
  11.  
  12.  
  13. れ旧 牛伎 牛覲企襦 蟆給.
  14.  
  15.  
  16.  
  17. SQL> UPDATE TEMP
  18. 2 SET SAL = 1000
  19. 3 WHERE EMPNO = 7369;
  20.  
  21. 1 螳煙給.
  22.  
  23. SQL> SELECT EMPNO, SAL FROM TEMP
  24. 2 WHERE EMPNO = 7369;
  25.  
  26. EMPNO SAL
  27. ---------- ----------
  28. 7369 1000
  29.  
  30. SQL> -- 襷 SAL(蠍) 瑚(蠍 10%) 誤 れ轟′朱 覲蟆渚る?
  31. SQL> UPDATE TEMP
  32. 2 SET SAL = SAL - (SAL*0.1);
  33.  
  34. 5 螳煙給.
  35.  
  36. SQL> SELECT EMPNO, SAL FROM TEMP;
  37.  
  38. EMPNO SAL
  39. ---------- ----------
  40. 7369 900
  41. 7566 2677.5
  42. 7788 2700
  43. 7876 990
  44. 7902 2700
  45.  
  46. SQL> COMMIT;
  47.  
  48. 貉る 襭給.
  49.  
  50.  
  51.  
  52. UPDATE 蠍磯蓋 企給. 企 貎朱Μ襯 伎 所 覲旧″ UPDATE覓語 覲
  53. 襦 蟆給.
  54.  
  55.  
  56.  
  57. SQL> SELECT EMPNO, SAL FROM TEMP;
  58.  
  59. EMPNO SAL
  60. ---------- ----------
  61. 7369 900
  62. 7566 2677.5
  63. 7788 2700
  64. 7876 990
  65. 7902 2700
  66.  
  67. SQL> SELECT AVG(SAL) FROM EMP;
  68.  
  69. AVG(SAL)
  70. ----------
  71. 2021.66667
  72.  
  73. SQL> -- EMP 蠏蠍覲企 TEMP 企 SAL 蠏朱 螻豺.
  74.  
  75. SQL> UPDATE TEMP
  76. 2 SET SAL = (SELECT AVG(SAL) FROM EMP)
  77. 3 WHERE SAL < (SELECT AVG(EMP.SAL)
  78. 4 FROM EMP, TEMP
  79. 5 WHERE EMP.EMPNO = TEMP.EMPNO);
  80.  
  81. 2 螳煙給.
  82.  
  83. SQL> SELECT EMPNO, SAL FROM TEMP;
  84.  
  85. EMPNO SAL
  86. ---------- ----------
  87. 7369 2021.67 -- 螳煙
  88. 7566 2677.5
  89. 7788 2700
  90. 7876 2021.67 -- 螳煙
  91. 7902 2700
  92.  
  93. SQL> COMMIT;
  94.  
  95. 貉る 襭給.
  96.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari


4 DELETE #

  1. DELETE 企 一危 蟆. DROPDELETE螳 豌 覓伎
  2. り語給. DROP 螳豌(, 碁煙,)襯 蟆願, DELETE 一危
  3. . 朱讌 襷蠍 覦. れ旧 牛伎 覲企襦 蟆
  4. .
  5.  
  6.  
  7.  
  8. DELETE table_name
  9. WHERE condition
  10.  
  11.  
  12.  
  13.  
  14.  
  15. SQL> -- 覈 一危
  16. SQL> DELETE TEMP;
  17.  
  18. 5.
  19.  
  20. SQL> SELECT COUNT(*) FROM TEMP;
  21.  
  22. COUNT(*)
  23. ----------
  24. 0
  25.  
  26. SQL> ROLLBACK;
  27.  
  28. 襦る葦 襭給.
  29.  
  30. SQL> SELECT COUNT(*) FROM TEMP;
  31.  
  32. COUNT(*)
  33. ----------
  34. 5
  35.  
  36. SQL> -- EMPNO 螳 7788
  37. SQL> DELETE TEMP
  38. 2 WHERE EMPNO = 7788;
  39.  
  40. 1.
  41.  
  42. SQL> COMMIT;
  43.  
  44. 貉る 襭給.
  45.  
  46. SQL> -- 殊 80 81 伎
  47. SQL> DELETE TEMP
  48. 2 WHERE HIREDATE BETWEEN '80/01/01' AND '81/12/31';
  49.  
  50. 3.
  51.  
  52. SQL> COMMIT;
  53.  
  54. 貉る 襭給.
  55.  
  56. SQL> SELECT * FROM TEMP;
  57.  
  58. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  59. ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
  60. 7876 ADAMS CLERK 7788 87/05/23 2021.67 20
  61.  
  62.  
  63.  
  64.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari