Login ¡¡ ¢· ¢¹ ¡¡ Mobile II
Hint Food ¸À°úÇâ Diet Health ºÒ·®Áö½Ä ÀÚ¿¬°úÇÐ My Book À¯Æ©ºê Frims ¿ø ·á Á¦ Ç° Update Site

±â¼ú ¡í IT ¡í ÀÎÅͳÝ

SQL ¸í·É¾î

HTML,CSS
- sql
- À©µµ, ÇÁ·Î±×·¥ ASP, VB ÆÁ
- ¹®ÀÚÇ¥, »ùÇÃ
- ½ÄÇ°°ü·Ã S/W

UPDATE Å×À̺í¸í SET ¼öÁ¤µÇ¾î¾ß ÇÒ Ä÷³¸í = ¼öÁ¤µÇ±â¸¦ ¿øÇÏ´Â »õ·Î¿î °ª;
UPDATE PLAYER SET BACK_NO = 99;

µ¥ÀÌŸ °Ë»ö
  USE µ¥ÀÌŸº£À̽º¸í     /* USE ¹®À» »ç¿ëÇÑ µ¥ÀÌŸº£À̽º ¼±Åà */
  SELECT * FROM Å×À̺í¸í WHERE Á¶°ÇÀý          /* Á¶°Ç¿¡ ÇØ´çÇÏ´Â µ¥ÀÌŸ °¡Á®¿À±â */
   SELECT * FROM Å×À̺í¸í WHERE Ä®·³ BETWEEN x AND y    
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ IN (a, b...)          /* Ä®·³ÀÌ aÀ̰ųª bÀÎ µ¥ÀÌŸ °¡Á®¿À±â */
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ LIKE 'ÆÐÅÏ'           /* Ä®·³ÀÌ ÆÐÅÏ°ú °°Àº µ¥ÀÌŸ °¡Á®¿À±â */
   ** ÆÐÅÏ¿¡ »ç¿ëµÇ´Â ±âÈ£´Â %, _°¡ ÀÖ´Ù
      'k%'(k·Î ½ÃÀ۵ǴÂ), '%k%'(Áß°£¿¡ k°¡ ÀÖ´Â), '%k'(k·Î ³¡³ª´Â)
      'p_'(p·Î ½ÃÀÛÇÏ´Â 2ÀÚ¸®), 'p___'(p·Î ½ÃÀÛÇÏ´Â 4ÀÚ¸®), '__p'(3ÀÚ¸® µ¥ÀÌŸÁß p·Î ³¡³ª´Â)
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ IS NULL               /* Ä®·³ÀÌ NULLÀÎ µ¥ÀÌŸ °¡Á®¿À±â */
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ NOT BETWEEN x AND y  
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ NOT IN (a, b...)      
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ NOT LIKE 'ÆÐÅÏ'      
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³ IS NOT NULL           /* Ä®·³ÀÌ NULLÀÌ ¾Æ´Ñ µ¥ÀÌŸ °¡Á®¿À±â */
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³>=x AND Ä®·³<=y        
  SELECT * FROM Å×À̺í¸í WHERE Ä®·³=a or Ä®·³=b
  SELECT * FROM µ¥À̺í¸í WHERE Ä®·³1>=x AND (Ä®·³2=a OR Ä®·³2=b)

  SELECT * FROM Å×À̺í¸í ORDER BY Ä®·³                    /* Ä®·³À» ¿À¸§Â÷¼øÀ¸·Î Àç¹è¿­Çϱâ */
  SELECT * FROM Å×À̺í¸í ORDER BY Ä®·³ ASC                
  SELECT * FROM Å×À̺í¸í ORDER BY Ä®·³ DESC               /* Ä®·³À» ³»¸²Â÷¼øÀ¸·Î Àç¹è¿­Çϱâ */
  SELECT * FROM Å×À̺í¸í ORDER BY Ä®·³1 ASC, Ä®·³2 DESC   /* º¹¼ö Ä®·³ Àç¹è¿­Çϱâ */
  SELECT * FROM Å×À̺í¸í ORDER BY 1 ASC, DESC 3           /* Ä®·³ ¼ø¼­·Î Àç¹è¿­Çϱâ */
   ¿¬»êÀÚ

  1. SELECT ¹®ÀÇ ¿¬»ê
      SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í
  2. ORDER BY ±¸ÀÇ ¿¬»ê
      SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í ORDER BY Ä®·³3+Ä®·³4 DESC
      SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í ORDER BY 3 DESC
  3. WHERE ±¸ÀÇ ¿¬»ê
      SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í WHERE Ä®·³2>=(Ä®·³3+Ä®·³4)
  4. NULL ¿¬»ê
      SELECT Ä®·³1, Ä®·³2, ISNULL(Ä®·³3, 0) + ISNULL(Ä®·³4, 0) AS 'º°¸í' FROM Å×À̺í¸í
     ** ¼öÄ¡Çü µ¥ÀÌŸ¿Í NULL°ª°úÀÇ ¿¬»ê °á°ú´Â Ç×»ó NULLÀÌ´Ù
      ¸¸¾à NULL °ªÀ» ¿øÄ¡ ¾ÊÀ¸¸é ISNULL(Ä®·³, ±âÁØ°ª) ÇÔ¼ö¸¦ »ç¿ëÇؼ­ ±âÁØ°ªÀ» º¯È¯½ÃŲ´Ù
  5. ³¯Â¥ ¿¬»ê  
     SELECT GETDATE()                                    /* ¼­¹öÀÇ ÇöÀç ³¯Â¥¸¦ ±¸ÇÑ´Ù */
     SELECT ³¯Â¥Ä®·³, ³¯Â¥Ä®·³-7 FROM Å×À̺í¸í
     SELECT ³¯Â¥Ä®·³, ³¯Â¥Ä®·³+30 FROM Å×À̺í¸í
     SELECT ³¯Â¥Ä®·³, DATEDIFF(day, ³¯Â¥Ä®·³, GETDATE()) FROM Å×À̺í¸í
   ** ³¯Â¥ÀÇ °¡»ê°ú °¨»êÀº + -·Î ÇÒ ¼ö ÀÖ´Ù
      ³¯Â¥¿Í ³¯Â¥ »çÀÌÀÇ °è»êÀº DATEDIFF(µ¹·ÁÁִ°ª, ½ÃÀÛ³¯Â¥, ³¡³¯Â¥) ÇÔ¼ö¸¦ »ç¿ëÇÑ´Ù
  6. ¹®ÀÚ ¿¬»ê
     SELECT Ä®·³1 + Ä®·³2 FROM Å×À̺í¸í
     SELECT Ä®·³ + '¹®ÀÚ¿­'  FROM Å×À̺í¸í
     SELECT Ä®·³1 + '¹®ÀÚ¿­' + Ä®·³2 FROM Å×À̺í¸í
   ** ±âº» ¿¬°áÀº ¹®ÀÚ¿Í ¹®ÀÚÀÌ°í ¹®ÀÚ¿Í ¼ýÀÚÀÇ ¿¬°áÀº CONVERT ÇÔ¼ö¸¦ »ç¿ëÇØ¾ß ÇÑ´Ù

ÇÔ¼ö
  1. ¼öÄ¡ ÇÔ¼ö
     ROUND(¼öÄ¡°ª, ¹Ý¿Ã¸²À§Ä¡)            /* ¹Ý¿Ã¸² ¹× ÀÚ¸£±â */
     ABS(¼öÄ¡ µ¥ÀÌŸ)                     /* Àý´ë°ª */  
     SIGN(¼öÄ¡ µ¥ÀÌŸ)                    /* ºÎÈ£ */
     SQRT(¼öÄ¡°ª)                         /* Á¦°ö±Ù */
     POWER(¼öÄ¡°ª, n)                     /* n½Â */

  2. ¹®ÀÚ¿­ ÇÔ¼ö
     SUBSTRING(¹®ÀÚ¿­, À§Ä¡, ¹®ÀÚ¼ö)      /* ƯÁ¤ À§Ä¡ ¹®ÀÚ¿­ ÃßÃâ */
     LEFT(¹®ÀÚ¿­, ¹®ÀÚ¼ö)                 /* ¿ÞÂʺÎÅÍ ÁöÁ¤ÇÑ °¹¼ö ¹®ÀÚ¿­ ÃßÃâ */
     RIGHT(¹®ÀÚ¿­, ¹®ÀÚ¼ö)                /* ¿À¸¥ÂʺÎÅÍ ÁöÁ¤ÇÑ °¹¼ö ¹®ÀÚ¿­ ÃßÃâ */
     LEN(¹®ÀÚ¿­)                          /* ¹®ÀÚ¿­ÀÇ ¹®ÀÚ¼ö */
     LTRIM(¹®ÀÚ¿­)                        /* ¹®ÀÚ¿­ ¿ÞÂÊÀÇ ½ºÆäÀ̽º¸¦ »èÁ¦ */
     RTRIM(¹®ÀÚ¿­)                        /* ¹®ÀÚ¿­ ¿À¸¥ÂÊÀÇ ½ºÆäÀ̽º¸¦ »èÁ¦ */
     UPPER(¹®ÀÚ¿­)                        /* ´ë¹®ÀÚ·Î º¯È¯ */
     LOWER(¹®ÀÚ¿­)                        /* ¼Ò¹®ÀÚ·Î º¯È¯ */
   ** À§Ä¡³ª ¹®ÀÚ¼ö´Â ÇѱÛÀÏ °æ¿ì 2¹ÙÀÌÆ®°¡ ÇѱÛÀÚ·Î Ãë±ÞµÈ´Ù
      SELECT LEN('¿ùabcdÈ­¼ö¸ñ')Àº 8À» µ¹·ÁÁØ´Ù

  3. ³¯Â¥ ÇÔ¼ö
     GETDATE()                                /* ÇöÀç ³¯Â¥¿Í ½Ã°¢ */
     DATEADD(µ¹·ÁÁִ°ª, ±â°£, ³¯Â¥)          /* ³¯Â¥ ¹× ½Ã°£ÀÇ ´õÇϱâ¿Í »©±â */
     DATEDIFF(µ¹·ÁÁִ°ª, ½ÃÀÛ³¯Â¥, ³¡³¯Â¥)   /* µÎ ³¯Â¥¿Í ½Ã°£ÀÇ Â÷ÀÌ */
     DATEPART(µ¹·ÁÁִ°ª, ³¯Â¥)               /* ³¯Â¥¿Í ½Ã°£ÀÇ Æ¯Á¤°ªÀ» µ¹·ÁÁØ´Ù */
   ** µ¹·ÁÁִ°ª(¾à¾î)
      Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,
      Hour-hh, Minute-mi, Second-ss, Milisecond-ms
      SELECT DATEADD(dd, 7, ³¯Â¥Ä®·³)  

4. Çüº¯È¯ ÇÔ¼ö
     CONVERT(µ¥ÀÌŸ ŸÀÔ, Ä®·³)                 /* Ä®·³À» ¿øÇÏ´Â µ¥ÀÌŸ ŸÀÔÀ¸·Î º¯È¯ */
     CONVERT(µ¥ÀÌŸ ŸÀÔ, Ä®·³, ³¯Â¥Çü ½ºÅ¸ÀÏ)  /* ¿øÇÏ´Â ³¯Â¥ ½ºÅ¸ÀÏ·Î º¯È¯ */
     CAST(Ä®·³ AS µ¥ÀÌŸ ŸÀÔ)                  /* Ä®·³À» ¿øÇÏ´Â µ¥ÀÌŸ ŸÀÔÀ¸·Î º¯È¯ */
   ** ½ºÅ¸ÀÏ
      1->mm/dd/yy, 2->yy.mm.dd, 3->dd/mm/yy, 4->dd.mm.yy, 5->dd-mm-yy,
      8->hh:mm:ss, 10->mm-dd-yy, 11->yy/mm/dd, 12->yymmdd
      SELECT CONVERT(varchar(10), ³¯Â¥Ä®·³, 2)

±×·ìÈ­
  ÇÔ¼ö
   SELECT COUNT(*) FROM Å×À̺í¸í               /* Àüü µ¥ÀÌŸÀÇ °¹¼ö °¡Á®¿À±â */
   SEELECT COUNT(Ä®·³) FROM Å×À̺í¸í           /* NULLÀº Á¦¿ÜÇÑ Ä®·³ÀÇ µ¥ÀÌŸ °¹¼ö °¡Á®¿À±â */
   SELECT SUM(Ä®·³) FROM Å×À̺í¸í              /* Ä®·³ÀÇ ÇÕ°è ±¸Çϱâ */
   SELECT MAX(Ä®·³) FROM Å×À̺í¸í              /* Ä®·³ÀÇ ÃÖ´ë°ª ±¸Çϱâ */
   SELECT MIN(Ä®·³) FROM Å×À̺í¸í              /* Ä®·³ÀÇ ÃÖ¼Ò°ª ±¸Çϱâ */
   SELECT AVG(Ä®·³) FROM Å×À̺í¸í              /* Ä®·³ÀÇ Æò±Õ°ª ±¸Çϱâ */

  GROUP BY¹®
   SELECT Ä®·³ FROM Å×À̺í¸í GROUP BY Ä®·³  
   SELECT Ä®·³1, SUM(Ä®·³2) FROM Å×À̺í¸í GROUP BY Ä®·³1
   SELECT Ä®·³1, COUNT(*) FROM Å×À̺í¸í GROUP BY Ä®·³1
   SELECT Ä®·³1, Ä®·³2, MAX(Ä®·³3) FROM Å×À̺í¸í GROUP BY Ä®·³1, Ä®·³2
    ** GROUP BY¸¦ ÁöÁ¤ÇÑ °æ¿ì SELECT ´ÙÀ½¿¡´Â ¹Ýµå½Ã GROUP BY¿¡¼­ ÁöÁ¤ÇÑ Ä®·³ ¶Ç´Â
       ±×·ì ÇÔ¼ö¸¸ÀÌ ¿Ã ¼ö ÀÖ´Ù
  Á¶°Ç
   SELECT Ä®·³1, SUM(Ä®·³2) FROM Å×À̺í¸í GROUP BY Ä®·³1 HAVING SUM(Ä®·³2) < a
   SELECT Ä®·³1, SUM(Ä®·³2) FROM Å×À̺í¸í ORDER BY Ä®·³1 COMPUTE SUM(Ä®·³2)
    ** HAVING:        ±×·ì ÇÔ¼ö¸¦ »ç¿ëÇÒ °æ¿ìÀÇ Á¶°ÇÀ» ÁöÁ¤ÇÑ´Ù
       HAVINGÀÇ À§Ä¡: GROUP BYÀÇ µÚ ORDER BYÀÇ ¾Õ¿¡ ÁöÁ¤ÇÑ´Ù
       COMPUTE:       °¢ ±×·ìÀÇ ¼Ò°è¸¦ ¿ä¾àÇؼ­ º¸¿©ÁØ´Ù
                      ORDER BY°¡ Ç×»ó ¼±ÇàÇؼ­ ³ª¿Í¾ß ÇÑ´Ù
  Á¶°ÇÀýÀÇ ¼­ºê Äõ¸®
  ** SELECT ¶Ç´Â INSERTY, UPDATE, DELETE °°Àº ¹®ÀÇ Á¶°ÇÀý¿¡¼­ SELECT¹®À» ¶Ç »ç¿ëÇÏ´Â °ÍÀÌ´Ù
     SELECT¹® ¾È¿¡ ¶Ç ´Ù¸¥ SELECT¹®ÀÌ Æ÷ÇԵǾî ÀÖ´Ù°í Áßø SELECT¹®(NESTED SELECT)À̶ó°í ÇÑ´Ù
  ** µ¥ÀÌŸº£À̽º¿¡´Â ¿©·¯¸íÀÌ ¿¢¼¼½ºÇÏ°í Àֱ⠶§¹®¿¡ Äõ¸®¸¦ ¿©·¯°³ ³ª´©¾î¼­ »ç¿ëÇÏ¸é µ¥ÀÌŸÀÇ °ªÀÌ
     ´Þ¶óÁú¼ö Àֱ⶧¹®¿¡ Æ®·£Àè¼Ç 󸮸¦ ÇÏÁö ¾Ê´Â´Ù¸é º¹¼öÀÇ Äõ¸®¸¦ ÇϳªÀÇ Äõ¸®·Î ¸¸µé¾î »ç¿ëÇØ¾ß ÇÑ´Ù
   SELECT Ä®·³1, Ä®·³2 FROM Å×À̺í¸í WHERE Ä®·³2 = (SELECT Ä®·³2 FROM Å×À̺í¸í WHERE Á¶°Ç)
   SELECT Ä®·³1, Ä®·³2 FROM Å×À̺í¸í WHERE Ä®·³1 IN (SELECT Ä®·³1 FROM Å×À̺í¸í WHERE Á¶°Ç)

  ** ¼­ºê Äõ¸®¿¡¼­´Â ´Ù¸¥ Å×À̺íÀ» Æ÷ÇÔÇÒ ¼ö ÀÖ´Ù
     µÎ°³ÀÇ Å×ÀÌºí¿¡¼­ Àоî¿À´Â ¼­ºêÄõ¸®ÀÇ °æ¿ì ¼­ºê Äõ¸®ÂÊ¿¡ µ¥ÀÌŸ°¡ ÀûÀº Å×À̺íÀ» ÁÖ Äõ¸®ÂÊ¿¡ µ¥ÀÌŸ°¡
     ¸¹Àº Å×À̺íÀ» ÁöÁ¤ÇØ¾ß Ã³¸® ¼Óµµ°¡ »¡¶óÁø´Ù
   SELECT Ä®·³1, Ä®·³2 FROM Å×À̺í¸í WHERE Ä®·³1 IN (SELECT Ä®·³2-1 FROM Å×À̺í¸í2 WHERE Á¶°Ç)

  ** FROM±¸¿¡¼­ ¼­ºê Äõ¸®¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Ù
     »ç¿ë½Ã ¹Ýµå½Ã º°ÄªÀ» ºÙ¿©¾ß ÇÏ°í ó¸® ¼Óµµ°¡ »¡¶óÁø´Ù
   SELECT Ä®·³1, Ä®·³2 FROM Å×À̺í¸í WHERE Á¶°Ç1 AND Á¶°Ç2
   SEELCT Ä®·³1, Ä®·³2 FROM (SELECT Ä®·³1, Ä®·³2 FROM Å×À̺í¸í WHERE Á¶°Ç1) º°Äª WHERE Á¶°Ç2

ÀϺ°,ÁÖº°,¿ùº° Åë°è
-- ÁÖ ´ÜÀ§
Select DATEPART(ww, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(ww, order_dt)
order by DATEPART(ww, order_dt)

-- ¿ù´ÜÀ§
Select DATEPART(mm, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(mm, order_dt)
order by DATEPART(mm, order_dt)

-- ³â´ÜÀ§
Select DATEPART(yy, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(yy, order_dt)
order by DATEPART(yy, order_dt)

--ºÐ±âº°
SELECT A.memyear,A.AA,SUM(A.CNT) AS CNT FROM (
Select DATEPART(yy, regdate) as memyear,DATEPART(mm, regdate) as memmonth,  count(regdate) as cnt
,(CASE DATEPART(mm, regdate) WHEN '1' THEN '1' WHEN '2' THEN '1' WHEN '3' THEN '1'
WHEN '4' THEN '2' WHEN '5' THEN '2' WHEN '6' THEN '2'
WHEN '7' THEN '3' WHEN '8' THEN '3' WHEN '9' THEN '3'
WHEN '10' THEN '4'
WHEN '11' THEN '4'
WHEN '12' THEN '4' ELSE '0' END) AS AA
From SMEM01MEM group by DATEPART(mm, regdate),DATEPART(yy, regdate)
--order by DATEPART(ww, regdate)
) A
group by A.memyear,A.AA
order by A.memyear,A.AA


µ¥ÀÌŸ ÆíÁý
  Ãß°¡
   INSERT INTO Å×À̺í¸í VALUES (°ª1, °ª2, ...)     /* ¸ðµç Çʵ忡 µ¥ÀÌŸ¸¦ ³ÖÀ» ¶§ */
   INSERT INTO Å×À̺í¸í (Ä®·³1, Ä®·³2, ...) VALUES (°ª1, °ª2, ...)   /* ƯÁ¤ Ä®·³¿¡¸¸ µ¥ÀÌŸ¸¦ ³ÖÀ» ¶§ */
   INSERT INTO Å×À̺í¸í SELECT * FROM Å×À̺í¸í2                      /* ÀÌ¹Ì Á¸ÀçÇÏ´Â Å×ÀÌºí¿¡ µ¥ÀÌŸ Ãß°¡ */
   INSERT INTO Å×À̺í¸í(Ä®·³1, Ä®·³2, ...) SELECT Ä®·³1, Ä®·³2, ...) FROM Å×À̺í¸í2
   SELECT * INTO Å×À̺í¸í FROM Å×À̺í¸í2                             /* »õ·Î ¸¸µç Å×ÀÌºí¿¡ µ¥ÀÌŸ Ãß°¡ */
   SELECT Ä®·³1, Ä®·³2, ... Å×À̺í¸í FROM Å×À̺í¸í2
  
  °»½Å
   UPDATE Å×À̺í¸í SET Ä®·³1=°ª1, Ä®·³2=°ª2            /* Àüü µ¥ÀÌŸ °»½Å */      
   UPDATE Å×À̺í¸í SET Ä®·³1=°ª1, Ä®·³2=°ª2 WHERE Á¶°Ç               /* Á¶°Ç¿¡ ÇØ´çµÇ´Â µ¥ÀÌŸ °»½Å */
  
  »èÁ¦
   DELETE FROM Å×À̺í¸í                                              /* Àüü µ¥ÀÌŸ »èÁ¦ */
   DELETE FROM Å×À̺í¸í WHERE Á¶°Ç       /* Á¶°Ç¿¡ ÇØ´çµÇ´Â µ¥ÀÌŸ »èÁ¦ */


¿ÀºêÁ§Æ®
  ** µ¥ÀÌŸº£À̽º´Â ¾Æ·¡ ¿ÀºêÁ§Æ®µéÀ» °¢°¢ÀÇ À¯Àúº°·Î °ü¸®¸¦ Çϴµ¥ Schema(½ºÅ°¸¶)´Â °¢ À¯Àúº° ¼ÒÀ¯ ¸®½ºÆ®ÀÌ´Ù

  1. Table(Å×À̺í)
   ** CREATEÀ϶§ ÇÁ¶óÀ̸Ӹ® Å°¸¦ ¼³Á¤ÇÏÁö ¾Ê´Â´Ù¸é (Ä®·³ int IDENTITY(1, 1) NOT NULL) ÀÚµ¿ Ä®·³À» ¸¸µç´Ù
      µ¥ÀÌŸµéÀÇ ÀÔ·Â ¼ø¼­¿Í Áߺ¹µÈ µ¥ÀÌŸ¸¦ ±¸º°Çϱâ À§Çؼ­ ¹Ýµå½Ã ÇÊ¿äÇÏ´Ù
   ** Å×À̺í Á¤º¸ SP_HELP Å×À̺í¸í, Á¦¾à Á¶°ÇÀº SP_HELPCONSTRAINT Å×À̺í¸í À» »ç¿ëÇÑ´Ù

   CREATE TABLE µ¥ÀÌŸº£À̽ºÀ̸§.¼ÒÀ¯ÀÚÀ̸§.Å×À̺íÀ̸§ (Ä®·³ µ¥ÀÌŸÇü Á¦¾à, ...) /* Å×ÀÌºí ¸¸µé±â */
   DROP TABLE Å×À̺í¸í                                                           /* Å×ÀÌºí »èÁ¦ */
   ALTER TABLE Å×À̺í¸í ADD Ä®·³ µ¥ÀÌŸÇü Á¦¾à, ...                              /* Ä®·³ Ãß°¡ */
   ALTER TABLE Å×À̺í¸í DROP COLUMN Ä®·³                                         /* Ä®·³ »èÁ¦ */
    ** DROP COLUMNÀ¸·Î ´ÙÀ½ Ä®·³Àº »èÁ¦¸¦ ÇÒ ¼ö ¾ø´Ù
       - º¹Á¦µÈ Ä®·³
       - À妽º·Î »ç¿ëÇÏ´Â Ä®·³
       - PRIMARY KEY, FOREGIN KEY, UNIQUE, CHECKµîÀÇ Á¦¾à Á¶°ÇÀÌ ÁöÁ¤µÈ Ä®·³
       - DEFAULT Å°¿öµå·Î Á¤ÀÇµÈ ±âº»°ª°ú ¿¬°áµÇ°Å³ª ±âº» °³Ã¼¿¡ ¹ÙÀεùµÈ Ä®·³
       - ±ÔÄ¢¿¡ ¹ÙÀεùµÈ Ä®·³
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü DEFAULT µðÆúÆ®°ª, ...)                   /* µðÆúÆ® ÁöÁ¤ */
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü CONSTRAINT À̸§ UNIQUE, ...)             /* À¯´ÏÅ© ¼³Á¤ */
    ** UNIQUE¶õ ÁöÁ¤ÇÑ Ä®·³¿¡ °°Àº °ªÀÌ µé¾î°¡´Â°ÍÀ» ±ÝÁöÇÏ´Â Á¦¾àÀ¸·Î ±âº» Å°¿Í ºñ½ÁÇÏÁö¸¸
       NULL °ªÀ» ÇÏ¿ëÇϴ°ÍÀÌ ´Ù¸£´Ù
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü CONSTRAINT À̸§ NOT NULL, ...)           /* NOT NULL ¼³Á¤ */
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü CONSTRAINT À̸§ PRIMARY KEY, ...)        /* ±âº» Å° ¼³Á¤ */
    ** ±âº» Å°´Â À¯´ÏÅ©¿Í NOT NULLÀÌ Á¶ÇÕµÈ Á¦¾àÀ¸·Î »öÀÎÀÌ ÀÚµ¿ÀûÀ¸·Î ÁöÁ¤µÇ°í µ¥ÀÌŸ¸¦
       À¯ÀÏÇÏ°Ô ¸¸µé¾î ÁØ´Ù
    **  ±âº» Å°´Â ÇÑ Å×ÀÌºí¿¡ ÇÑ°³ÀÇ Ä®·³¸¸ °¡´ÉÇÏ´Ù
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü CONSTRAINT À̸§ FOREIGN KEY REFERENCES ºÎ¸ðÅ×À̺íÀ̸§(ºÎ¸ðÄ®·³), ...)        
   CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü CONSTRAINT À̸§ CHECK(Á¶°Ç), ...)        /* CHECK ¼³Á¤ */
    ** CHECK´Â Á¶°ÇÀ» ÀÓÀÇ·Î Á¤ÀÇÇÒ ¼ö ÀÖ´Â Á¦¾àÀ¸·Î ¼³Á¤µÇ¸é Á¶°ÇÀ» ÃæÁ·½ÃÅ°´Â µ¥ÀÌŸ¸¸
       µî·ÏÇÒ ¼ö ÀÖ°í SELECTÀÇ WHERE±¸¿Í °°Àº Á¶°ÇÀ» ÁöÁ¤ÇÑ´Ù
    ** CONSTRAINT¿Í Á¦¾à À̸§À» ¾²Áö ¾ÊÀ¸¸é µ¥ÀÌŸº£À̽º°¡ ¾Ë¾Æ¼­ À̸§À» ºÙÀÌÁö¸¸
       º¹ÀâÇÑ À̸§ÀÌ µÇ±â ¶§¹®¿¡ µÇµµ·ÏÀÌ¸é »ç¿ëÀÚ°¡ ÁöÁ¤Çϵµ·Ï ÇÑ´Ù
    ** CONSTRAINT´Â Ä®·³°ú µ¥ÀÌŸÇüÀ» ¸ðµÎ Á¤ÀÇÇÑ µÚ¿¡ ¸Ç ¸¶Áö¸·¿¡ ¼³Á¤ÇÒ ¼ö ÀÖ´Ù
     CREATE TABLE Å×À̺í¸í (Ä®·³1 µ¥ÀÌŸÇü,
             Ä®·³2 µ¥ÀÌŸÇü, ...
             CONSTRAINT À̸§ PRIMARY KEY(Ä®·³1)
                           CONSTRAINT À̸§ CHECK(Ä®·³2 < a) ...)        
   ALTER TABLE Å×À̺í¸í ADD CONSTRAINT À̸§ Á¦¾à¹®                                /* Á¦¾à Ãß°¡ */
   ALTER TABLE Å×À̺í¸í DROP CONSTRAINT Á¦¾à¸í                                    /* Á¦¾à »èÁ¦ */
   ALTER TABLE Å×À̺í¸í NOCHECK CONSTRAINT Á¦¾à¸í                                 /* Á¦¾à È¿·Â Á¤Áö */
   ALTER TABLE Å×À̺í¸í CHECK CONSTRAINT Á¦¾à¸í                                   /* Á¦¾à È¿·Â À¯È¿ */
    ** Á¦¾à¸íÀº Å×À̺íÀ» ¸¸µé¶§ »ç¿ëÀÚ°¡ ÁöÁ¤ÇÑ ÆÄÀÏ À̸§À» ¸»ÇÑ´Ù

  2. View(ºä)
   ** ÀÚÁÖ »ç¿ëÇÏ´Â SELECT¹®ÀÌ ÀÖÀ»¶§ »ç¿ëÇÑ´Ù
      Å×ÀÌºí¿¡ Á¸ÀçÇÏ´Â Ä®·³µéÁß Æ¯Á¤ Ä®·³À» º¸ÀÌ°í ½ÍÁö ¾ÊÀ»¶§ »ç¿ëÇÑ´Ù
      Å×ÀÌºí°£ÀÇ °áÇÕµîÀ¸·Î ÀÎÇØ º¹ÀâÇØÁø SELECT¹®À» °£´ÜÈ÷ ´Ù·ç°í ½ÍÀ»¶§ »ç¿ëÇÑ´Ù
   ** ºä¸¦ ¸¸µé¶§ COMPUTE, COMPUTE BY, SELECT INTO, ORDER BY´Â »ç¿ëÇÒ ¼ö ¾ø°í
      #, ##À¸·Î ½ÃÀ۵Ǵ Àӽà Å×ÀÌºíµµ ºäÀÇ ´ë»óÀ¸·Î »ç¿ëÇÒ ¼ö ¾ø´Ù
   ** ºäÀÇ ³»¿ëÀ» º¸°í ½ÍÀ¸¸é SP_HELPTEXT ºä¸í À» »ç¿ëÇÑ´Ù
      
   CREATE VIEW ºä¸í AS SELECT¹®                            /* ºä ¸¸µé±â */
   CREATE VIEW ºä¸í (º°Äª1, º°Äª2, ...) AS SELECT¹®    /* Ä®·³ÀÇ º°Äª ºÙÀ̱â */
   CREATE VIEW ºä¸í AS (SELECT Ä®·³1 AS º°Äª1, Ä®·³2 AS º°Äª2, ...)
   ALTER VIEW ºä¸í AS SELECT¹®                                       /* ºä ¼öÁ¤ */
   DROP VIEW ºä¸í                                                    /* ºä »èÁ¦ */
   CREATE VIEW ºä¸í WITH ENCRYPTION AS SELECT¹®                      /* ºä ¾ÏÈ£ */
    ** Çѹø ¾ÏȣȭµÈ ºä´Â ¼Ò½º Äڵ带 º¼ ¼ö ¾øÀ¸¹Ç·Î ºä¸¦ ¾ÏȣȭÇϱâÀü¿¡
       ºäÀÇ ³»¿ëÀ» ½ºÅ©¸³Æ® ÆÄÀÏ·Î ÀúÀåÇÏ¿© º¸°üÇÑ´Ù
   INSERT INTO ºä¸í (Ä®·³1, Ä®·³2, ...) VALUES (°ª1, °ª2, ...)
   UPDATE ºä¸í SET Ä®·³=°ª WHERE Á¶°Ç
    ** ¿ø·¡ Å×ÀÌºí¿¡ ÀÖ´Â ¹Ýµå½Ã °ªÀ» ÀÔ·ÂÇØ¾ß ÇÏ´Â Ä®·³ÀÌ Æ÷ÇԵǾî ÀÖÁö ¾Ê°Å³ª
       ¿ø·¡ Ä®·³À» »ç¿ëÇÏÁö ¾Ê°í º¯ÇüµÈ Ä®·³À» »ç¿ëÇÏ´Â ºä´Â µ¥ÀÌŸ¸¦ Ãß°¡Çϰųª
       °»½ÅÇÒ ¼ö ¾ø´Ù
    ** WHERE Á¶°ÇÀ» ÁöÁ¤ÇÑ ºä´Â ºä¸¦ ¸¸µé¾úÀ»¶§ WITH CHECK OPTIONÀ» ÁöÁ¤ÇÏÁö ¾Ê¾Ò´Ù¸é
       Á¶°Ç¿¡ ¸ÂÁö ¾Ê´Â µ¥ÀÌŸ¸¦ Ãß°¡ÇÒ ¼ö ÀÖÁö¸¸ ºä¿¡¼­´Â º¸ÀÌÁö ¾Ê´Â´Ù
       ¶ÇÇÑ ºä¸¦ ÅëÇؼ­ °¡Á®¿Â Á¶°ÇÀ» ¸¸Á·ÇÏ´Â °ªµµ ºäÀÇ Á¶°Ç¿¡ ¸¸Á·ÇÏÁö ¾Ê´Â °ªÀ¸·Îµµ
       °»½ÅÇÒ ¼ö ÀÖ´Ù
   CREATE VIEW ºä¸í AS SELECT¹® WITH CHECK OPTION
    ** ºäÀÇ Á¶°Ç¿¡ ¸ÂÁö ¾Ê´Â INSERT³ª UPDATE¸¦ ¸·À»·Á¸é WITH CHECK OPTIONÀ» ¼³Á¤ÇÑ´Ù

  3. Stored Procedure(ÀúÀå ÇÁ·Î½ÃÀú)
   ** µ¥ÀÌŸº£À̽º³»¿¡¼­ SQL ¸í·ÉÀ» ÄÄÆÄÀÏÇÒ¶§ ij½Ã¸¦ ÀÌ¿ëÇÒ ¼ö ÀÖÀ¸¹Ç·Î 󸮰¡ ¸Å¿ì ºü¸£´Ù
      ¹Ýº¹ÀûÀ¸·Î SQL ¸í·ÉÀ» ½ÇÇàÇÒ °æ¿ì ¸Åȸ ¸í·É¸¶´Ù ³×Æ®¿öÅ©¸¦ °æÀ¯ÇÒ ÇÊ¿ä°¡ ¾ø´Ù
      ¾îÇø®ÄÉÀ̼Ǹ¶´Ù »õ·Î ¸¸µé ÇÊ¿ä¾øÀÌ ÀÌ¹Ì ¸¸µé¾îÁø ÇÁ·Î½ÃÀú¸¦ ¹Ýº¹ »ç¿ëÇÑ´Ù
      µ¥ÀÌŸº£À̽º ·ÎÁ÷À» ¼öÁ¤½Ã ÇÁ·Î½ÃÀú´Â ¼­¹öÃø¿¡ ÀÖÀ¸¹Ç·Î ¾îÇø®ÄÉÀ̼ÇÀ» ´Ù½Ã ÄÄÆÄÀÏÇÒ ÇÊ¿ä°¡ ¾ø´Ù
   ** ÀúÀå ÇÁ·Î½ÃÀúÀÇ ¼Ò½º Äڵ带 º¸°í ½ÍÀ¸¸é SP_HELPTEXT ÇÁ·Î½ÃÀú¸í À» »ç¿ëÇÑ´Ù

   CREATE PROC ÇÁ·Î½ÃÀú¸í AS SQL¹®   /* ÀúÀå ÇÁ·Î½ÃÀú */
   CREATE PROC ÇÁ·Î½ÃÀú¸í º¯¼ö¼±¾ð AS SQL¹® /* Àμö¸¦ °¡Áö´Â ÀúÀå ÇÁ·Î½ÃÀú */
   CREATE PROC ÇÁ·Î½ÃÀú¸í WITH ENCRYPTION AS SQL¹® /* ÀúÀå ÇÁ·Î½ÃÀú º¸¾È ¼³Á¤ */
   CREATE PROC ÇÁ·Î½ÃÀú¸í                          /* RETURN °ªÀ» °¡Áö´Â ÀúÀå ÇÁ·Î½ÃÀú */
       Àμö1 µ¥ÀÌŸÇü, ... Àμö2 µ¥ÀÌŸÇü OUTPUT
   AS
       SQL¹®
       RETURN ¸®ÅÏ°ª
   DROP PROCEDURE ÇÁ·Î½ÃÀú¸í1, ÇÁ·Î½ÃÀú¸í2, ...    /* ÀúÀå ÇÁ·Î½ÃÀú »èÁ¦ */
  
   ¸í·É¾î
    BEGIN ... END    /* ¹®ÀåÀÇ ºí·Ï ÁöÁ¤ */
    DECLARE @º¯¼ö¸í µ¥ÀÌŸÇü  /* º¯¼ö ¼±¾ð */
    SET @º¯¼ö¸í=°ª    /* º¯¼ö¿¡ °ª ÁöÁ¤ */
    PRINT @º¯¼ö¸í    /* ÇÑ°³ÀÇ º¯¼ö Ãâ·Â */
    SELECT @º¯¼ö1, @º¯¼ö2   /* ¿©·¯°³ÀÇ º¯¼ö Ãâ·Â */
    IF Á¶°Ç     /* Á¶°Ç ¼öÇà */
        ¼öÇà1
    ELSE
        ¼öÇà2                
    WHILE Á¶°Ç1    /* ¹Ýº¹ ¼öÇà */
        BEGIN
            IF Á¶°Ç2
         BREAK    - WHILE ·çÇÁ¸¦ ºüÁ® ³ª°£´Ù
         CONTINUE - ¼öÇàÀ» ó¸®ÇÏÁö ¾Ê°í Á¶°Ç1·Î µÇµ¹¾Æ°£´Ù
     ¼öÇà
        END                
    EXEC ÀúÀåÇÁ·Î½ÃÀú   /* SQL¹®À» ½ÇÇà */
    EXEC @(º¯¼ö·Î ÁöÁ¤µÈ SQL¹®)
    GO     /* BATCH¸¦ ±¸ºÐ ÁöÁ¤ */

   ¿¡Á¦
    1. ±âº» ÀúÀå ÇÁ·Î½ÃÀú
     CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2
    2. Àμö¸¦ °¡Áö´Â ÀúÀå ÇÁ·Î½ÃÀú
     CREATE PROC pUpdateSalary
         @mul float=2, @mul2 int
     AS
         UPDATE Employee SET salary=salary* @Mul* @mul2
     EXEC pUpdateSalary 0.5, 2       /* ¸ðµç º¯¼ö¿¡ °ªÀ» ´ëÀÔ */
     EXEC pUpdateSalary @mul2=2      /* ¿øÇÏ´Â º¯¼ö¿¡¸¸ °ªÀ» ´ëÀÔ */
    3. ¸®ÅÏ°ªÀ» °¡Áö´Â ÀúÀå ÇÁ·Î½ÃÀú
     CREATE PROC pToday
         @Today varchar(4) OUTPUT
     AS
         SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))
         RETURN @Today
     DECLARE @answer varchar(4)
     EXEC pToday @answer OUTPUT
     SELECT @answer AS ¿À´Ã³¯Â¥
    4. º¯¼ö ¼±¾ð°ú ´ëÀÔ, Ãâ·Â
     ** @´Â »ç¿ëÀÚ º¯¼öÀÌ°í @@´Â ½Ã½ºÅÛ¿¡¼­ »ç¿ëÇÏ´Â º¯¼öÀÌ´Ù

     DECLARE @EmpNum int
     SET @EmpNum=10
     SELECT @EmpNum

  4. Trigger(Æ®¸®°Å)
   ** ÇÑ Å×À̺íÀÇ µ¥ÀÌŸ°¡ ÆíÁý(INSERT/UPDATE/DELETE)µÈ °æ¿ì¿¡ ÀÚµ¿À¸·Î ´Ù¸¥ Å×À̺íÀÇ
      µ¥ÀÌŸ¸¦ »ðÀÔ, ¼öÁ¤, »èÁ¦ÇÑ´Ù
   ** Æ®¸®°Å ³»¿ëÀ» º¸°í ½ÍÀ¸¸é SP_HELPTRIGGER Æ®¸®°Å¸í À» »ç¿ëÇÑ´Ù

   CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í FOR INSERT AS SQL¹®         /* INSERT ÀÛ¾÷ÀÌ ¼öÇàµÉ¶§ */
   CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í AFTER UPDATE AS SQL¹®       /* UPDATE ÀÛ¾÷ÀÌ ¼öÇàµÇ°í ³­ ÈÄ */
   CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í INSTEAD OF DELETE AS SQL¹®  
   DROP TRIGGER Æ®¸®°Å¸í

  5. Cursor(Ä¿¼­)
   ** SELECT·Î °¡Á®¿Â °á°úµéÀ» Çϳª¾¿ Àоîµé¿© ó¸®ÇÏ°í ½ÍÀ»¶§ »ç¿ëÇÑ´Ù
   ** Ä¿¼­ÀÇ »ç¿ë¹æ¹ýÀº OPEN, FETCH, CLOSE, DEALLOCATEµî 4´Ü°è¸¦ °ÅÄ£´Ù
   ** FETCH¿¡´Â NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n / @nvar}, RELATIVE {n / @nvar}°¡ ÀÖ´Ù

   SET NOCOUNT ON      /* SQL¹®ÀÇ ¿µÇâÀ» ¹ÞÀº Çà¼ö¸¦ ³ªÅ¸³»´Â ¸Þ½ÃÁö¸¦ ¼û±ä´Ù */
   DECLARE cStatus SCROLL CURSOR    /* ¾ÕµÚ·Î ¿òÁ÷ÀÌ´Â Ä¿¼­ ¼±¾ð */
   FOR
       SELECT ID, Year, City FROM aPlane
   FOR READ ONLY
   OPEN cStatus      /* Ä¿¼­¸¦ ¿¬´Ù */
   DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)
   FETCH FROM cStatus INTO @ID, @Year, @City /* Ä¿¼­¿¡¼­ µ¥ÀÌŸ¸¦ Çϳª¾¿ °¡Á®¿Â´Ù */
   WHILE @@FETCH_STATUS=0                    /* Ä¿¼­°¡ °¡¸£Å°´Â °á°úÀÇ ³¡±îÁö */
   BEGIN
       IF      @Year <= 5              SET @Status='A'
       ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'
       ELSE                            SET @Status='C'
       INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)
       FETCH FROM cStatus INTO @ID, @Year, @City /* Ä¿¼­¿¡¼­ µ¥ÀÌŸ¸¦ Çϳª¾¿ °¡Á®¿Â´Ù */
   END
   CLOSE cStaus                              /* Ä¿¼­¸¦ ´Ý´Â´Ù */
   DEALLOCATE cStatus                        /* Ä¿¼­¸¦ ÇØÁ¦ÇÑ´Ù */



MS-SQL ¹é¾÷ ¹× º¹¿ø

Àüü ¹é¾÷ (Full backup)
   - óÀ½ DB »ý¼º½Ã
   - Æ®·£Àè¼Ç ·Î±×¸¦ ºñ¿üÀ»½Ã
   - DB¿¡ º¯°æÀÌ »ý°åÀ»¶§(ALTER DB)
   - Â÷µî, ·Î±× ¹é¾÷ Àü Çѹø ÀÌ»ó

      BACKUP DATABASE µðºñÀ̸§ TO ÆÄÀÏ&ÀåÄ¡


Â÷µî ¹é¾÷ (Differential Backup)
   - ¸¶Áö¸· ¹é¾÷ ÀÌÈÄ¿¡ º¯°æµÈ ¸ðµç µ¥ÀÌÅ͸¦ ¹é¾÷
   - º¹±¸½Ã ¸¶Áö¸· Â÷µî¹é¾÷°ú
      ¸¶Áö¸·°ú °¡Àå °¡±î¿î Àüü¹é¾÷ ÆÄÀÏ
      BACKUP DATABASE µðºñÀ̸§ TO ÆÄÀÏ&ÀåÄ¡
      WITH DIFFERENTIAL

·Î±× ¹é¾÷ (Log Backup)
   - ¹é¾÷ ¼Óµµ°¡ ºü¸£°í º¹¿ø¼Óµµ°¡ ´À¸®´Ù
   - Áß°£ ·Î±×¹é¾÷ÆÄÀÏ À¯½Ç ½Ã º¹±¸ ºÒ°¡    
      BACKUP LOG µðºñÀ̸§ TO ÆÄÀÏ&ÀåÄ¡


ºÎºÐ ¹é¾÷ (Partial Backup)
   - ÆÄÀÏ ±×·ìÀ» ¹é¾÷
   - ÁÖ ÆÄÀÏ ±×·ì, Àбâ/¾²±â ÆÄÀϱ׷츸 ¹é¾÷
   - ´Ü¼ø º¹±¸ ¸ðµ¨¿¡ °¡Àå ÀûÇÕ
      BACKUP DATABASE µðºñÀ̸§
      READ_WRITE_FILEGROUPS TO ÀåÄ¡


¹Ì·¯ ¹é¾÷ (Mirror Backup)
  - ¹é¾÷ µ¥ÀÌÅÍÀÇ À¯½ÇÀ̳ª ÆÄ¼Õ ½Ã
      µ¥ÀÌÅÍ À¯½ÇÀ» ¸·±â À§ÇØ »ç¿ë
  - ¹é¾÷ ÀåÄ¡ ÀÌ¿Ü¿¡ ÇϳªÀÇ ÀåÄ¡¿¡ ¶È°°ÀÌ ¹é¾÷

      BACKUP DATABASE µðºñ¸í
      TO DISK = 'C:\a.bak'
      MIRROR TO DISK = 'D:\a.bak'
      WITH FORMAT

º¹»ç Àü¿ë ¹é¾÷ (Copy Backup)
   - µ¥ÀÌÅÍ º£À̽ºÀÇ º¹»ç³ª À̵¿ Å×½ºÆ®½Ã »ç¿ë

      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH COPY_ONLY


üũ¼¶ ±â´É (CHECKSUM)
   - ¹é¾÷½Ã µ¥ÀÌÅÍÀÇ ÀÌ»ó À¯¹«¸¦ üũ
   - ¹é¾÷ üũ¼¶À» ÇØ¾ß º¹¿ø½Ã üũ¼¶°¡´É

      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH CHECKSUM


¹é¾÷ ¸Åü ÃʱâÈ­
   - ±âÁ¸ ¹é¾÷ ³»¿ëÀÌ »èÁ¦ µðÆúÆ®´Â NOINIT

      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH INIT


´ÙÁß ¹é¾÷ ÀåÄ¡ ÃʱâÈ­
   - ´ÙÁß ¹é¾÷ ÀåÄ¡¸¦ ÃʱâÈ­ ÇÑÈÄ ¹é¾÷ ¼öÇà
   - ´ë°³ ´Ù¸¥ ³»¿ëÀÌ µé¾îÀÖ´Â ¿©·¯ µð½ºÅ©¿¡
     óÀ½ ¹é¾÷ ÇÒ ¶§ »ç¿ë

      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡1, ÀåÄ¡2
      WITH FORAMT

ºñ¹Ð¹øÈ£ ÁöÁ¤
   - ¹é¾÷ ¹× º¹¿ø½Ã ºñ¹Ð¹øÈ£ ÁöÁ¤
      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH PASSWORD ='ºñ¹Ð¹øÈ£'


¹é¾÷ Áß ¿À·ù¹ß»ý½Ã °è¼Ó ¿©ºÎ
   - À߸øµÈ üũ¼¶ ¹ß»ý½Ã¿¡µµ °è¼Ó ¹é¾÷ ÁøÇà
   - µðÆúÆ®´Â STOP_ONERROR ¿À·ù½Ã ¹é¾÷ ÁßÁö

      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH CONTINUE_AFTER_ERROR


ÁøÇà·ü Ç¥½Ã
   - ´ë¿ë·® ¹é¾÷ ÁøÇà½Ã ÇöÀç »óȲÀ» º¸´Âµ¥ Æí¸®
      BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
      WITH STATS


¹®Á¦ ¹ß»ý½Ã ·Î±× ¹é¾÷ (ºñ»ó ·Î±× ¹é¾÷)
   - NO_TRUNCATE ¿É¼Ç »ç¿ë ÀÌ»ó½Ã¿¡µµ °¡´É

      BACKUP LOG µðºñÀ̸§ TO ÀåÄ¡
      WITH NO_TRUNCATE

----------------------------------------------------------------------------------------

º¹¿ø


Àüü º¹¿ø
   - Àüü ÆÄÀÏÀ» º¹¿øÇÑ´Ù.

      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡

Â÷µî º¹¿ø
   - Â÷µî º¹¿ø. Àüüº¹¿ø°ú µ¿ÀÏ
   - WITH DIFFERENTIAL ¿É¼Ç ÇÊ¿ä ¾øÀ½

      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡

·Î±× º¹¿ø
   - ·Î±× ÆÄÀÏÀ» º¹¿ø

      RESTORE LOG µðºñÀ̸§ FROM ÀåÄ¡


º¹¿ø ¿Ï·á ¹× º¹¿ø Áß
   - Àüü ¹é¾÷°ú ·Î±× ¹é¾÷À» º´ÇàÇÑ °æ¿ì º¹¿ø ¿Ï·á±îÁö
      Á¢±ÙÀ» Á¦¾î ÇÒ ¼ö ÀÖ´Ù.
   - WITH RECOVERY(default)

      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH NORECOVERY


º¹¿ø ÈÄ¿¡ Á¦ÇÑµÈ »ç¿ëÀÚ¸¸ Á¢±Ù Çã¿ë
      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH RESTRICTED_USER

      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH RECOVERY


º¹¿ø ½Ã µ¥ÀÌÅÍ ÆÄÀÏÀÇ À̵¿
   - ±âÁ¸ µð½ºÅ©°¡ ²Ëá°Å³ª ¹®Á¦°¡ ÀÖÀ» ½Ã

      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH MOVE 'AdventureWorks_data' TO 'D:\adv.mdf'
, MOVE 'AdventureWorks_log' TO 'D:\adv.ldf'


¿À·ù°¡ ¹ß»ýÇصµ °è¼Ó º¹¿ø
      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH CONTINUE_AFTER_ERROR


¿ø·¡ ÆÄÀÏÀÌ ÀÖÀ¸¸é µ¤¾î¾²±â
      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH REPLACE

Á¤È®ÇÑ ½ÃÁ¡±îÁö¸¸ º¹¿øÇϱâ
      RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH STOPAT = '³¯Â¥¿Í ½Ã°£'


µ¥ÀÌÅͺ£À̽º ½º³À¼ôÀ¸·Î º¹¿øÇϱâ
      RESTORE DATABASE µðºñÀ̸§ FROM DATABASE_SNAPSHOT = '½º³À¼ô À̸§'


ÆäÀÌÁö º¹¿ø
      RESTORE DATABASE µðºñÀ̸§ PAGE='ÆÄÀϹøÈ£:ÆäÀÌÁö¹øÈ£' FROM ¹é¾÷ÀåÄ¡


ÁõºÐ º¹¿ø
   - ÆÄÀÏ ±×·ì ´ÜÀ§·Î º¹¿ø ÁøÇà
     RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
      WITH PARTIAL

CREATE USER »ç¿ëÀÚ¸í IDENTIFIED BY Æнº¿öµå;   /* »ç¿ëÀÚ »ý¼º°ú Æнº¿öµå ¼³Á¤ */
   CREATE USER »ç¿ëÀÚ¸í IDENTIFIED BY Æнº¿öµå   /* »ç¿ëÀÚ »ý¼º½Ã Å×ÀÌºí½ºÆäÀ̽º ÁöÁ¤ */
    DEFAULT TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í
    TEMPORARY TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í;
   ALTER USER »ç¿ëÀÚ¸í DEFAULT TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í; /* »ç¿ëÀÚ µðÆúÆ® Å×ÀÌºí½ºÆäÀ̽º º¯°æ */
   ALTER USER »ç¿ëÀÚ¸í IDENTIFIED BY Æнº¿öµå;   /* »ç¿ëÀÚ Æнº¿öµå º¯°æ */    
   ALTER USER »ç¿ëÀÚ¸í PASSWORD EXPIRRE;    /* »ç¿ëÀÚ Æнº¿öµå ¹«È¿ */
   ALTER USER »ç¿ëÀÚ¸í ACCOUNT LOCK;    /* »ç¿ëÀÚ Á¢¼Ó ±ÝÁö */
   ALTER USER »ç¿ëÀÚ¸í ACCOUNT UNLOCK;    /* »ç¿ëÀÚ Á¢¼Ó Çã¿ë */
   DROP USER »ç¿ëÀÚ¸í      /* »ç¿ëÀÚ »èÁ¦ */
   DROP USER »ç¿ëÀÚ¸í INCLUDING CONTENTS;    /* »ç¿ëÀÚ¿Í °ü·ÃµÈ ¸ðµç Á¤º¸ »èÁ¦ */
  ±ÇÇÑ  
   ** »ç¿ëÀÚ¸¦ ¸¸µçÈÄ ÇØ´ç »ç¿ëÀÚ¿¡°Ô ±ÇÇÑÀ» ºÎ¿© ÇØ¾ß ÇÑ´Ù
   ** ¿ÀºêÁ§Æ® ±ÇÇÑ ALL(ALTER, DELETE, INDEX, INSERT, REFERENCE, SELECT, UPDATE)À» »ç¿ëÇÏ¿©
      Å×ÀÌºí¿¡ Çѹø¿¡ ¸ðµç ±ÇÇÑÀ» ºÎ¿©ÇÒ ¼ö Àְųª ¿øÇÏ´Â ±ÇÇѸ¸ ºÎ¿©ÇÒ ¼ö ÀÖ´Ù
      ºä´Â DELETE, INSERT, SELECT, UPDATE, ½ÃÄö½º´Â ALTER, SELECT, ÇÁ·Î½ÃÀú´Â EXECUTE ±ÇÇÑÀ» ÁÙ ¼ö ÀÖ´Ù
   ** 120¿©°³³ª µÇ´Â ±ÇÇÑÀ» ÀÏÀÏÀÌ ºÎ¿©ÇÏ´Â ´ÜÁ¡À» ÇØ°áÇϱâ À§ÇØ ROLEÀ» »ç¿ëÇÑ´Ù
    GRANT ±ÇÇÑ1, ±ÇÇÑ2, ... TO »ç¿ëÀÚ¸í             /* »ç¿ëÀÚ¿¡ ±ÇÇÑ ºÎ¿© */

   GRANT CREATE SESSION TO »ç¿ëÀÚ¸í;               /* µ¥ÀÌŸº£À̽º¿¡ Á¢¼ÓÇÒ ¼ö ÀÖ´Â ±ÇÇÑ */
   GRANT CREATE TABLE TO »ç¿ëÀÚ¸í;                 /* Å×À̺íÀ» ¸¸µé¼ö ÀÖ´Â ±ÇÇÑ */
   GRANT SELECT ON Å×À̺í¸í TO »ç¿ëÀÚ¸í;           /* Å×À̺íÀÇ SELECT ±ÇÇÑ */
   GRANT ALL ON Å×À̺í¸í TO »ç¿ëÀÚ¸í;              /* Å×ÀÌºí¿¡¼­ ó¸®ÇÒ ¼ö ÀÖ´Â ¸ðµç ±ÇÇÑ */
   GRANT EXECUTE ÇÁ·Î½ÃÀú¸í TO »ç¿ëÀÚ¸í;           /* ÇÁ·Î½ÃÀú¸¦ ½ÇÇàÇÒ ¼ö ÀÖ´Â ±ÇÇÑ */
   GRANT SELECT ON A»ç¿ëÀÚ¸í.Å×À̺í¸í to B»ç¿ëÀÚ¸í /* A»ç¿ëÀÚ Å×À̺íÀÇ SELECT ±ÇÇÑÀ» B»ç¿ëÀÚ¿¡°Ô ºÎ¿© */
   REVOKE ±ÇÇÑ1, ±ÇÇÑ2, ... FROM »ç¿ëÀÚ¸í;         /* »ç¿ëÀÚÀÇ ±ÇÇÑ Ãë¼Ò */
   REVOKE CREATE SESSION FROM »ç¿ëÀÚ¸í;            /* µ¥ÀÌŸº£À̽º¿¡ Á¢¼ÓÇÒ ¼ö ÀÖ´Â ±ÇÇÑ Ãë¼Ò */
   REVOKE CREATE VIEW FROM »ç¿ëÀÚ¸í;               /* ºä¸¦ ¸¸µé¼ö ÀÖ´Â ±ÇÇÑ Ãë¼Ò */
  
   CONNECT            /* µ¥ÀÌŸº£À̽º¿¡ Á¢¼ÓÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» °¡Áø ·Ñ */
   RESOURCE           /* Å×À̺í, À妽º, ºäµîÀ» »ý¼º, º¯°æ, »èÁ¦ÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» °¡Áø ·Ñ */
   DBA                /* µ¥ÀÌŸº£À̽º¸¦ °ü¸®Çϱâ À§ÇØ ÇÊ¿äÇÑ ±ÇÇÑÀ» °¡Áø ·Ñ */
   RXP_FULL_DATABASE  /* EXPORT ÅøÀ» »ç¿ëÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» °¡Áø ·Ñ */
   IMP_FULL_DATABASE  /* IMPORT ÅøÀ» »ç¿ëÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» °¡Áø ·Ñ */
   SYSDBA             /* µ¥ÀÌŸº£À̽º ½ÃÀÛ°ú Á¾·á¹× ¸ðµç °ü¸®¸¦ Çϱâ À§ÇÑ ÇÊ¿äÇÑ ±ÇÇÑÀ» °¡Áø ·Ñ */
   SYSOPER            /* SYSDBA ±ÇÇÑÀ» Æ÷ÇÔ µ¥ÀÌŸº£À̽º¸¦ »ý¼ºÇÒ ¼ö ÀÖ´Â ±ÇÇÑÀ» °¡Áø ·Ñ */
   GRANT ·Ñ1, ·Ñ2, ... TO »ç¿ëÀÚ¸í;                 /* »ç¿ëÀÚ¿¡°Ô ·Ñ ±ÇÇÑ ºÎ¿© */
   REVOKE ·Ñ1, ·Ñ2, ... FROM »ç¿ëÀÚ¸í;              /* »ç¿ëÀÚÀÇ ·Ñ ±ÇÇÑ Ãë¼Ò */
  ·Î±×¿Â
   CONNECT ¶Ç´Â CONNECT »ç¿ëÀÚID/Æнº¿öµå
  ÀÚ·á »çÀü
   ** ƯÁ¤ Å×ÀÌºí¿¡ °ü·ÃµÈ ÀÚ·á»çÀüÀÌ ¾î¶² °ÍÀÎÁö ¾Ë°í ½ÍÀ»¶§´Â DICTIONARY Å×À̺íÀÇ
      COMMENTS Ä®·³¿¡ ƯÁ¤ Å×À̺í°ú °ü·ÃµÈ ¹®ÀÚ¿­À» ã¾Æº¸¸é µÈ´Ù

   DBA_xxxx        /* DB ÀüüÀÇ °ü·ÃµÈ Á¤º¸·Î SYS, SYSTEM »ç¿ëÀÚ¸¸ º¼ ¼ö ÀÖ´Ù */
   ALL_xxxx /* ÀÚ½ÅÀ̳ª ´Ù¸¥ »ç¿ëÀÚ°¡ »ý¼ºÇÑ ¿ÀºêÁ§Æ®Áß¿¡ ÀÚ½ÅÀÌ º¼ ¼ö ÀÖ´Â Á¤º¸ */
   USER_xxxx       /* ÀÚ½ÅÀÌ »ý¼ºÇÑ ¿ÀºêÁ§Æ® Á¤º¸ */
   V$_xxxx         /* DBÀÇ ¼º´É ºÐ¼®, Åë°è Á¤º¸¸¦ Á¦°øÇϸé X$ÀÇ ºä */
   X$_xxxx         /* DBÀÇ ¼º´É ºÐ¼®, Åë°è Á¤º¸ */

   SELECT * FROM DICTIONATY;    /* ¸ðµç »óÅ Á¤º¸ */
   SELECT * FROM ALL_USERS;    /* »ç¿ëÀÚ ID ¸®½ºÆ® */
   SELECT * FROM USER_USERS;    /* »ç¿ëÀÚ Á¤º¸ */
   SELECT * FROM USER_CONSTRAINTS;    /* Á¦¾à Á¤º¸ */
   SELECT * FROM USER_TABLESPACES    /* Å×ÀÌºí½ºÆäÀ̽º Á¤º¸ */
   SELECT * FROM USER_TABLES;    /* Å×À̺í Á¤º¸ */

   SELECT * FROM DBA_ROLES;                         /* Àüü ·Ñ, SYS, SYSTEM¿¡¼­ »ç¿ë */
   SELECT * FROM USER_ROLE_PRIVS;                   /* ÇöÀç »ç¿ëÀÚ¿¡°Ô ºÎ¿©µÈ ·Ñ È®ÀÎ */
   SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE=·Ñ¸í;    /* ÇØ´ç ·ÑÀÌ ¾î¶² ±ÇÇÑÀ» °¡Áö°í ÀÖ´ÂÁö È®ÀÎ ±â/
   SELECT * FROM USER_TAB_PRIVS_RECD;               /* ¿ÀºêÁ§Æ®¿¡¼­ ºÎ¿© ¹ÞÀº ±ÇÇÑ */
  
TableSpace(Å×ÀÌºí½ºÆäÀ̽º)
  - SYSTEM Å×ÀÌºí½ºÆäÀ̽º
   ** »ç¿ëÀÚ »ý¼º½Ã º°µµÀÇ Å×ÀÌºí½ºÆäÀ̽º¸¦ ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é ½Ã½ºÅÛÀ¸·Î ÁöÁ¤µÈ´Ù
      ½Ã½ºÅÛÀÌ »ç¿ëÀÚ µ¥ÀÌŸ Æ÷ÇÔÀ» Çã¿ëÇÏ´õ¶óµµ »ç¿ëÇÏÁö ¸»¾Æ¾ß ÇÑ´Ù

   µ¥ÀÌŸº£À̽º¿Í ÇÔ²² »ý¼ºµÇ°í ¸ðµç µ¥ÀÌŸº£À̽º¿¡¼­ ÇÊ¿äÇÏ´Ù
   µ¥ÀÌŸ µñ¼Å³Ê¸® Á¤º¸¸¦ Æ÷ÇÔÇÑ´Ù
   ½Ã½ºÅÛ ½ÇÇà Ãë¼Ò ¼¼±×¸ÕÆ®¸¦ Æ÷ÇÔÇÑ´Ù
  - UNDO Å×ÀÌºí½ºÆäÀ̽º
   SQL¹®À» ½ÇÇàÇÑ ÈÄ Æ®·£Àè¼ÇÀ» ROLLBACK Çϱâ À§ÇØ º¯°æ ÀÌÀüÀÇ µ¥ÀÌŸ¸¦ ÀúÀåÇÑ´Ù
  - TEMP Å×ÀÌºí½ºÆäÀ̽º
   SQL¹®À» ½ÇÇàÇÒ ¶§ ³»ºÎ ºÐ·ùÀÛ¾÷(GROUP BY, ORDER BY, ...)À» À§ÇØ »ç¿ëÇÑ´Ù
   USERS Å×ÀÌºí½ºÆäÀ̽º
   »ç¿ëÀÚ°¡ Å×½ºÆ®ÇÒ ¼ö ÀÖ´Â ÀúÀå°ø°£ÀÌ´Ù

  
  - CREATE TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í     /* ÀÚµ¿ È®Àå Å×ÀÌºí½ºÆäÀ̽º¸¦ ¸¸µé±â */
   DATAFILE µð·ºÅ丮¿ÍÆÄÀϸí SIZE Å©±â
   AUTOEXTEND ON NEXT Å©±â MAXSIZE Å©±â;  /* ÀÚµ¿À¸·Î NEXT ¸¸Å­ È®Àå MAXSIZE´Â ÁöÁ¤ÇÏÁö ¾Ê¾Æµµ µÈ´Ù */
  ALTER TABLESPACE RENAME Å×ÀÌºí½ºÆäÀ̽º¸í TO »õÅ×ÀÌºí½ºÆäÀ̽º¸í;  /* Å×ÀÌºí½ºÆäÀ̽º¸í º¯°æ */
  ALTER TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í RENAME           /* ¹°¸®ÀûÀÎ µ¥ÀÌŸ ÆÄÀÏ º¯°æ */
   DATAFILE 'c:\oracle\oradata\jerry01.dbf' TO 'c:\oracle\oradata\aaa\jerry01.dbf';
  ALTER USER »ç¿ëÀÚ¸í TEMPORARY TABLESPACE ÅÛÇÁÅ×ÀÌºí½ºÆäÀ̽º¸í;   /* »ç¿ëÀÚÀÇ ±âº» ÅÛÇÁµ¥ÀÌºí½ºÆäÀ̽º º¯°æ */
  DROP TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í INCLUDING CONTENTS;
   ** Å×ÀÌºí½ºÆäÀ̽º¸¦ »èÁ¦ÇÏ´õ¶óµµ °ü·Ã µ¥ÀÌŸ ÆÄÀÏÀº »èÁ¦µÇÁö ¾Ê±â ¶§¹®¿¡
      Å½»ö±â¿¡¼­ Á÷Á¢ »èÁ¦ÇØ¾ß ÇÑ´Ù

  Å×ÀÌºí ½ºÆäÀ̽º ¹× »ç¿ëÀÚ »ý¼º
   ** Å×ÀÌºí ½ºÆäÀ̽º´Â ÀúÀå ¿µ¿ª ¹× Àӽà Å×ÀÌºí ½ºÆäÀ̽º ÀÌ·¸°Ô µÎ°Ô°¡ ÇÊ¿äÇÏÁö¸¸

   1. CREATE TABLESPACE ts_name
    DATAFILE '/home/oracle/data/ts_name.dbf' SIZE 100M
    AUTOEXTEND ON NEXT 10M
   2. CREATE TEMPORARY TABLESPACE ts_name_temp TEMPFILE '/home/oracle/data/ts_name_temp.dbf' SIZE 10M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K              
    ** Àӽà Å×ÀÌºí ½ºÆäÀ̽º´Â ±âº»À¸·Î Á¤ÇØÁ® ÀÖ´Â TEMP TABLESPACE ¿µ¿ªÀ» »ç¿ëÇصµ µÈ´Ù
   3. CREATE USER »ç¿ëÀÚ¸í IDENTIFIED BY Æнº¿öµå
    DEFAULT TABLESPACE ts_name          
    TEMPORARY TABLESPACE ts_name_temp  
   4. GRANT CONNECT, RESOURCE TO »ç¿ëÀÚ¸í

Table(Å×À̺í)
  ** Á¦¾à Á¶°Ç¿¡´Â PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULLÀÌ ÀÖ´Ù

  CREATE TABLE Å×À̺í¸í (Ä®·³ µ¥ÀÌŸÇü Á¦¾à, ...); /* Å×ÀÌºí ¸¸µé±â */
  CREATE TABLE Å×À̺í¸í (Ä®·³1 µ¥ÀÌŸÇü, ...              /* ±âº» Å° ¼³Á¤ */
                         CONSTRAINT À̸§ RPIMARY KEY (Ä®·³1));
  ALTER TABLE Å×À̺í¸í DROP PRIMARY KEY;                  /* ±âº» Å° »èÁ¦ */
  ALTER TABLE Å×À̺í¸í DROP CONSTRAINT Á¦¾à¸í;            /* Á¦¾à Á¶°Ç »èÁ¦ */
  ALTER TABLE Å×À̺í¸í MODIFY (Ä®·³ µ¥ÀÌŸÇü, ...);       /* Ä®·³ÀÇ Å¸ÀÔ º¯°æ */
  ALTER TABLE Å×À̺í¸í ADD (Ä®·³ µ¥ÀÌŸÇü, ...);          /* Ä®·³ Ãß°¡ */
  ALTER TABLE Å×À̺í¸í DROP COLUMN Ä®·³;                  /* Ä®·³ »èÁ¦ */
  ALTER TABLE Å×À̺í¸í RENAME COLUMN Ä®·³¸í TO »õÄ®·³¸í   /* Ä®·³¸í º¯°æ */
  ALTER TABLE Å×À̺í¸í MOVE TABLESPACE Å×ÀÌºí½ºÆäÀ̽º¸í;  /* Å×À̺íÀÇ Å×ÀÌºí½ºÆäÀ̽º º¯°æ */
  DROP TABLE Å×À̺í¸í;                                    /* Å×ÀÌºí »èÁ¦ */
  RENAME Å×À̺í¸í TO »õÅ×À̺í¸í;                          /* Å×À̺í¸í º¯°æ */
  TRUNCATE TABLE Å×À̺í¸í;                                /* Å×ÀÌºí³»ÀÇ°æ µ¥ÀÌŸ »èÁ¦ */

  SELECT * FROM Å×À̺í¸í;     /* ÇöÀç »ç¿ëÀÚÀÎ °æ¿ì »ç¿ëÀÚ¸íÀº »ý·« */
  SELECT * FROM »ç¿ëÀÚ¸í.Å×À̺í¸í;   /* ´Ù¸¥ »ç¿ëÀÚÀÎ °æ¿ì ÇØ´ç »ç¿ëÀÚ¸íÀ» ÁöÁ¤ */
  SELECT * FROM DBA_CONSTRAINTS                           /* Å×ÀÌºíº° CONSTRAINT(Á¦¾à, PRIMARY KEY, FOREIGN KEY..) */
   WHERE OWNER=»ç¿ëÀÚ¸í AND TABLE_NAME=Å×À̺í¸í;
  SELECT USER FROM DUAL;                                  /* ÇöÀç »ç¿ëÀÚ¸í È®ÀÎ */
  SELECT SYSDATE FROM DUAL;                               /* ¿À´Ã ³¯Â¥ È®ÀÎ */
  SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;      
   ** ¿À¶óŬ¿¡¼­´Â Å×À̺í¾øÀÌ SELECT¹®ÀÇ °á°ú¸¦ º¸°íÀÚ ÇÒ¶§ DUALÀ» »ç¿ëÇÑ´Ù
  DESC Å×À̺í¸í;      /* Å×ÀÌºí ±¸Á¶ */

Sequence(½ÃÄö½º)
  ** MSSQL, MySqlÀÇ ÀÚµ¿ Áõ°¡¿Í °°Àº ¿ªÇÒÀ» ÇÑ´Ù
     SEQUENCE°¡ Çѹø Áõ°¡µÇ¸é µ¥ÀÌŸ°¡ »èÁ¦ µÇ¾îµµ ´Ù½Ã »ç¿ëÇÒ ¼ö ¾ø´Ù
  
  CREATE SEQUENCE ½ÃÄö½º¸í INCREMENT BY n     /* Áõ°¡°ª */
                           START WITH n     /* ½ÃÀÛ°ª */
      MAXVALUE n | NOMAXVALUE   /* ÃÖ´ë°ª/ÃÖ´ë°ª ÁöÁ¤ÇÏÁö ¾ÊÀ½ */
      MINVALUE n | NOMINVALUE   /* ÃÖ¼Ò°ª/ÃÖ¼Ò°ª ÁöÁ¤ÇÏÁö ¾ÊÀ½ */
      CYCLE | NOCYCLE           /* CYCLEÀº ÃÖ´ë°ª±îÁö Áõ°¡°¡ ¿Ï·áµÇ¸é ´Ù½Ã START·Î µ¹¾Æ°£´Ù
                                   NOCYCLEÀº Áõ°¡°¡ ¿Ï·áµÇ¸é ¿¡·¯¸¦ ¹ß»ý½ÃŲ´Ù */
      CACHE n | NOCACHE         /* ½ÃÄö½º¸¦ ¸Þ¸ð¸®¿¡¼­ °ü¸®ÇÏ¸ç ±âº»°ªÀº 20 */
  CREATE TABLE Temp (id number, name varchar(20));
  CREATE SEQUENCE sqe (INCREMENT BY 1, START WITH 1, NOCYCLE, NOCACHE);
  INSERT INTO Temp (id, name) VALUES (seq.NEXTVAL, 'È«±æµ¿');
  
Synonym(½Ã³ë´½)
  ** À̸§ÀÌ ±ä ´Ù¸¥ »ç¿ëÀÚÀÇ °´Ã¼¸¦(»ç¿ëÀÚ¸í.Å×À̺í¸í) ¿ä¾à Çؼ­ »ç¿ëÇÑ´Ù
     PUBLIC Å°¿öµå´Â µ¥ÀÌŸº£À̽º³» ¸ðµç »ç¿ëÀÚµéÀÌ ½Ã³ë´½À» »ç¿ëÇÒ ¼ö ÀÖ°Ô ÇÑ´Ù

  CREATE PUBLIC SYNONYM ½Ã³ë´½¸í FOR »ç¿ëÀÚ¸í.Å×À̺í¸í;  
  CREATE PUBLIC SYNONYM Emp FOR hr.employee;
  SELECT * FROM Emp;

View(ºä)
  ** REPLACE ¸í·ÉÀ» »ç¿ëÇÏ¸é ¸¸µé°Å³ª ¼öÁ¤ÇÒ¶§ ¸¶´Ù ´Ù½Ã ÄÄÆÄÀÏÀ» ÇÑ´Ù

  CREATE OR REPLACE VIEW ºä¸í AS SELECT Ä®·³1, Ä®·³2, ... FROM Å×À̺í¸í WHERE Á¶°Ç;
  DROP VIEW ºä¸í;

Procedure(ÇÁ·Î½ÃÀú)
  CREATE OR REPLACE PROCEDURE ÇÁ·Î½ÃÀú¸í    /* ÇÁ·Î½ÃÀú »ý¼º */
      (ÆĶó¹ÌÅÍ1 µ¥ÀÌŸŸÀÔ, ÆĶó¹ÌÅÍ2 OUT µ¥ÀÌŸŸÀÔ, ÆĶó¹ÌÅÍ3 IN OUT µ¥ÀÌŸŸÀÔ)
  IS
      º¯¼ö ¼±¾ð;
  BEGIN
      ÇÁ·Î±×·¥;
  END;
  DROP PROCEDURE ÇÁ·Î½ÃÀú¸í;                /* ÇÁ·Î½ÃÀú »èÁ¦ */

---Excel ¿­±â

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet : Dim filteext As String = ""

'check for the file type
If IO.Path.GetExtension(fileName) = "xls" Then
                filteext = "Excel 8.0"
ElseIf IO.Path.GetExtension(fileName) = ".xlsx" Then
                filteext = "Excel 12.0"
End If

'open connection

MyConnection = New System.Data.OleDb.OleDbConnection _
               ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=" & filteext & ";")
            MyConnection.Open()

  Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")

  Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), MyConnection)


   MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataSet

    MyCommand.Fill(DtSet)

    DataGridView1.DataSource = DtSet.Tables(0)
            'DtSet.DataSetName.

    MyConnection.Close()
shareimprove this answer


ÆäÀ̽ººÏ       ¹æ¸í·Ï      ¼öÁ¤ 2017-07-01 / µî·Ï 2015-01-22 / Á¶È¸ : 9930 (755)



¿ì¸®ÀÇ °Ç°­À» ÇØÄ¡´Â ºÒ·®Áö½ÄÀÌ ¾ø´Â ¾Æ¸§´Ù¿î ¼¼»óÀ» ²Þ²Ù¸ç ...  2009.12  ÃÖ³«¾ð


¡¡