{"id":449,"date":"2014-01-21T09:54:36","date_gmt":"2014-01-21T18:54:36","guid":{"rendered":"\/blog\/?p=449"},"modified":"2023-09-21T09:38:47","modified_gmt":"2023-09-21T00:38:47","slug":"ms-sql-%ec%a0%95%eb%a6%ac","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=449","title":{"rendered":"MS-SQL \uc815\ub9ac"},"content":{"rendered":"\n

 ** SQL\ubb38\uc740 \ub300\uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud558\uc9c0 \uc54a\uc9c0\ub9cc \ub370\uc774\ud0c0\ub294 \ub300\ubb38\uc790\uc640 \uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud55c\ub2e4<\/span>
    \uc8fc\uc11d\uc744 \ub2e4\ub294 \ubc29\ubc95\uc740 \/* \uc8fc\uc11d *\/ \uc774\uac70\ub098 \ud55c\uc904\ub9cc \uc8fc\uc11d \ucc98\ub9ac\ub97c \ud560 \uacbd\uc6b0\ub294 \ubb38\uc7a5 \ub9e8\uc55e\uc5d0 --\ub97c \ubd99\uc778\ub2e4<\/span>
 ** \uac01\uac01\uc758 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 SYSOBJECTS \ud14c\uc774\ube14\uc5d0 \ud574\ub2f9 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ubaa8\ub4e0 \uc815\ubcf4\uac00 \ubcf4\uad00\ub418\uc5b4 \uc788\ub2e4<\/span>
    SYSOBJECTS\uc758 TYPE \uce7c\ub7fc\uc73c\ub85c 'U'=\uc0ac\uc6a9\uc790 \ud14c\uc774\ube14, 'P'=\uc800\uc7a5 \ud504\ub85c\uc2dc\uc800, 'K'=\ud504\ub77c\uc774\uba38\ub9ac \ud0a4, 'F'=\ud3ec\ub9b0 \ud0a4,<\/span>
    'V'=\ubdf0, 'C'=\uccb4\ud06c \uc81c\uc57d\ub4f1 \uc624\ube0c\uc81d\ud2b8 \uc774\ub984\uacfc \uc815\ubcf4\ub97c \uc54c \uc218 \uc788\ub2e4<\/span>


 \ub370\uc774\ud0c0 \uac80\uc0c9<\/span>
  USE \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uba85     \/* USE \ubb38\uc744 \uc0ac\uc6a9\ud55c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \uc120\ud0dd *\/<\/span>
  SELECT * FROM \ub370\uc774\ube14\uba85     \/* \ubaa8\ub4e0 \uce7c\ub7fc \ubd88\ub7ec\uc624\uae30 *\/<\/span>
  SELECT TOP n * FROM \ud14c\uc774\ube14\uba85    \/* \uc0c1\uc704 n\uac1c\uc758 \ub370\uc774\ud0c0\ub9cc \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, \uce7c\ub7fc3 FROM \ud14c\uc774\ube14\uba85  \/* \ud2b9\uc815 \uce7c\ub7fc \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT \uce7c\ub7fc1 \ubcc4\uba851, \uce7c\ub7fc2 AS \ubcc4\uba852 FROM \ud14c\uc774\ube14\uba85 \/* \uce7c\ub7fc\uc5d0 \ubcc4\uba85 \ubd99\uc774\uae30 *\/<\/span>
  SELECT \uce7c\ub7fc3 '\ubcc4  \uba853' FROM \ud14c\uc774\ube14\uba85                    \/* \uce7c\ub7fc \ubcc4\uba85\uc5d0 \uc2a4\ud398\uc774\uc2a4\uac00 \ub4e4\uc5b4\uac08 \uacbd\uc6b0\ub294 \uc791\uc740\ub530\uc634\ud45c \uc0ac\uc6a9 *\/<\/span>
  SELECT DISTINCT \uce7c\ub7fc FROM \ud14c\uc774\ube14\uba85                      \/* \uc911\ubcf5\ub418\uc9c0 \uc54a\ub294 \ub370\uc774\ud0c0\ub9cc \uac00\uc838\uc624\uae30 *\/<\/span>
   ** \ub370\uc774\ud0c0\ub294 \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc7ac\ubc30\uc5f4\ub41c\ub2e4<\/span>
      DISTINCT\ub97c \uc0ac\uc6a9\ud558\uba74 \uc7ac\ubc30\uc5f4\uc774 \ub420\ub54c\uae4c\uc9c0 \ub370\uc774\ud0c0\uac00 \ub9ac\ud134\ub418\uc9c0 \uc54a\uc73c\ubbc0\ub85c \uc218\ud589 \uc18d\ub3c4\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce5c\ub2e4 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74\uc808                     \/* \uc870\uac74\uc5d0 \ud574\ub2f9\ud558\ub294 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
   ** \uc870\uac74\uc2dd\uc5d0 \uc0ac\uc6a9\ud558\ub294 \ube44\uad50\ub294 \uce7c\ub7fc=\uac12, \uce7c\ub7fc!=\uac12, \uce7c\ub7fc>\uac12, \uce7c\ub7fc>=\uac12, \uce7c\ub7fc<\uac12, \uce7c\ub7fc<=\uac12\uc774 \uc788\ub2e4<\/span>
      \ubb38\uc790\uc5f4\uc740 ''(\uc791\uc740\ub530\uc634\ud45c)\ub97c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
      \ub0a0\uc9dc \ube44\uad50\ub97c \ud560\ub54c\ub294 'yy-mm-dd' \ud615\uc2dd\uc758 \ubb38\uc790\uc5f4\ub85c \ud55c\ub2e4(\ub0a0\uc9dc='1992-02-02', \ub0a0\uc9dc>'1992-02-02')<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc BETWEEN x AND y       \/* \uce7c\ub7fc\uc774 x>=\uc640 y<=\uc0ac\uc774\uc758 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc IN (a, b...)          \/* \uce7c\ub7fc\uc774 a\uc774\uac70\ub098 b\uc778 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>


  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc LIKE '\ud328\ud134'           \/* \uce7c\ub7fc\uc774 \ud328\ud134\uacfc \uac19\uc740 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
   ** \ud328\ud134\uc5d0 \uc0ac\uc6a9\ub418\ub294 \uae30\ud638\ub294 %, _\uac00 \uc788\ub2e4<\/span>
      'k%'(k\ub85c \uc2dc\uc791\ub418\ub294), '%k%'(\uc911\uac04\uc5d0 k\uac00 \uc788\ub294), '%k'(k\ub85c \ub05d\ub098\ub294)<\/span>
      'p_'(p\ub85c \uc2dc\uc791\ud558\ub294 2\uc790\ub9ac), 'p___'(p\ub85c \uc2dc\uc791\ud558\ub294 4\uc790\ub9ac), '__p'(3\uc790\ub9ac \ub370\uc774\ud0c0\uc911 p\ub85c \ub05d\ub098\ub294)<\/span>

Like \ud328\ud134 \uc8fc\uc758\uc810<\/span>

more...<\/span>


  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc IS NULL               \/* \uce7c\ub7fc\uc774 NULL\uc778 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc NOT BETWEEN x AND y   \/* \uce7c\ub7fc\uc774 x\uc640 y \uc0ac\uc774\uac00 \uc544\ub2cc \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc NOT IN (a, b...)      \/* \uce7c\ub7fc\uc774 a\ub098 b\uac00 \uc544\ub2cc \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc NOT LIKE '\ud328\ud134'       \/* \uce7c\ub7fc\uc774 \ud328\ud134\uacfc \uac19\uc9c0 \uc54a\uc740 \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc IS NOT NULL           \/* \uce7c\ub7fc\uc774 NULL\uc774 \uc544\ub2cc \ub370\uc774\ud0c0 \uac00\uc838\uc624\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc>=x AND \uce7c\ub7fc<=y<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc=a or \uce7c\ub7fc=b<\/span>
  SELECT * FROM \ub370\uc774\ube14\uba85 WHERE \uce7c\ub7fc1>=x AND (\uce7c\ub7fc2=a OR \uce7c\ub7fc2=b)<\/span>
   ** \ubcf5\uc218 \uc870\uac74\uc744 \uc5f0\uacb0\ud558\ub294 \uc5f0\uc0b0\uc790\ub294 AND\uc640 OR\uac00 \uc788\ub2e4<\/span>
      AND\uc640 OR\uc758 \uc6b0\uc120\uc21c\uc704\ub294 AND\uac00 OR\ubcf4\ub2e4 \ub192\uc740\ub370 \uc6b0\uc120 \uc21c\uc704\ub97c \ubc14\uafb8\uace0 \uc2f6\ub2e4\uba74 ()\uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc                    \/* \uce7c\ub7fc\uc744 \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc7ac\ubc30\uc5f4\ud558\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc ASC<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc DESC               \/* \uce7c\ub7fc\uc744 \ub0b4\ub9bc\ucc28\uc21c\uc73c\ub85c \uc7ac\ubc30\uc5f4\ud558\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc1 ASC, \uce7c\ub7fc2 DESC   \/* \ubcf5\uc218 \uce7c\ub7fc \uc7ac\ubc30\uc5f4\ud558\uae30 *\/<\/span>
  SELECT * FROM \ud14c\uc774\ube14\uba85 ORDER BY 1 ASC, DESC 3           \/* \uce7c\ub7fc \uc21c\uc11c\ub85c \uc7ac\ubc30\uc5f4\ud558\uae30 *\/<\/span>
          ** \uae30\ubcf8\uc801\uc73c\ub85c SELECT \ubb38\uc5d0\uc11c\ub294 \ucd9c\ub825\uc21c\uc11c\uac00 \ubcf4\uc99d\ub418\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0<\/span>
      \ub370\uc774\ud0c0\uc758 \ub4f1\ub85d \uc0c1\ud0dc\ub098 \uc11c\ubc84\uc758 \ubd80\ud558 \uc0c1\ud0dc\uc5d0 \ub530\ub77c \ucd9c\ub825\ub418\ub294 \uc21c\uc11c\uac00 \ub2ec\ub77c\uc9c8 \uc218 \uc788\ub2e4<\/span>
      \ub530\ub77c\uc11c \ucd9c\ub825\ud558\ub294 \uacbd\uc6b0 \ub418\ub3c4\ub85d\uc774\uba74 ORDER BY\ub97c \uc9c0\uc815\ud55c\ub2e4<\/span>
   ** \uce7c\ub7fc \ubc88\ud638\ub294 \uc804\uccb4 \uce7c\ub7fc\uc5d0\uc11c\uc758 \ubc88\ud638\uac00 \uc544\ub2c8\ub77c SELECT\ubb38\uc5d0\uc11c \uc120\ud0dd\ud55c \uce7c\ub7fc\uc758 \ubc88\ud638\uc774\uace0 1\ubd80\ud130 \uc2dc\uc791\ud55c\ub2e4<\/span>



 \uc5f0\uc0b0\uc790<\/span>
  ** 1\uc21c\uc704\ub294 \uc218\uce58 \uc55e\uc5d0 \uae30\uc220\ub418\ub294 + - \uac19\uc740 \ub2e8\ud56d \uc5f0\uc0b0\uc790<\/span>
     2\uc21c\uc704\ub294 \uc0ac\uce59 \uc5f0\uc0b0\uc758 \uc0b0\uc220 \uc5f0\uc0b0\uc790\uc778  * \/ + -<\/span>
     3\uc21c\uc704\ub294 = > \ube44\uad50 \uc5f0\uc0b0\uc790<\/span>
     4\uc21c\uc704\ub294 AND OR \uac19\uc740 \ub17c\ub9ac \uc5f0\uc0b0\uc790<\/span>
     ()\uc744 \ubd99\uc774\uba74 \uc6b0\uc120 \uc21c\uc704\ub97c \ubc14\uafc0\uc218 \uc788\ub2e4<\/span>

  1. SELECT \ubb38\uc758 \uc5f0\uc0b0<\/span>
     SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, \uce7c\ub7fc3+\uce7c\ub7fc4 AS '\ubcc4\uba85' FROM \ud14c\uc774\ube14\uba85<\/span>
  2. ORDER BY \uad6c\uc758 \uc5f0\uc0b0<\/span>
     SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, \uce7c\ub7fc3+\uce7c\ub7fc4 AS '\ubcc4\uba85' FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc3+\uce7c\ub7fc4 DESC<\/span>
     SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, \uce7c\ub7fc3+\uce7c\ub7fc4 AS '\ubcc4\uba85' FROM \ud14c\uc774\ube14\uba85 ORDER BY 3 DESC<\/span>
  3. WHERE \uad6c\uc758 \uc5f0\uc0b0<\/span>
     SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, \uce7c\ub7fc3+\uce7c\ub7fc4 AS '\ubcc4\uba85' FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc2>=(\uce7c\ub7fc3+\uce7c\ub7fc4)<\/span>
  4. NULL \uc5f0\uc0b0<\/span>
     SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, ISNULL(\uce7c\ub7fc3, 0) + ISNULL(\uce7c\ub7fc4, 0) AS '\ubcc4\uba85' FROM \ud14c\uc774\ube14\uba85<\/span>
   ** \uc218\uce58\ud615 \ub370\uc774\ud0c0\uc640 NULL\uac12\uacfc\uc758 \uc5f0\uc0b0 \uacb0\uacfc\ub294 \ud56d\uc0c1 NULL\uc774\ub2e4<\/span>
      \ub9cc\uc57d NULL \uac12\uc744 \uc6d0\uce58 \uc54a\uc73c\uba74 ISNULL(\uce7c\ub7fc, \uae30\uc900\uac12) \ud568\uc218\ub97c \uc0ac\uc6a9\ud574\uc11c \uae30\uc900\uac12\uc744 \ubcc0\ud658\uc2dc\ud0a8\ub2e4<\/span>
  5. \ub0a0\uc9dc \uc5f0\uc0b0<\/span>
     SELECT GETDATE()                                    \/* \uc11c\ubc84\uc758 \ud604\uc7ac \ub0a0\uc9dc\ub97c \uad6c\ud55c\ub2e4 *\/<\/span>
     SELECT \ub0a0\uc9dc\uce7c\ub7fc, \ub0a0\uc9dc\uce7c\ub7fc-7 FROM \ud14c\uc774\ube14\uba85<\/span>
     SELECT \ub0a0\uc9dc\uce7c\ub7fc, \ub0a0\uc9dc\uce7c\ub7fc+30 FROM \ud14c\uc774\ube14\uba85<\/span>
     SELECT \ub0a0\uc9dc\uce7c\ub7fc, DATEDIFF(day, \ub0a0\uc9dc\uce7c\ub7fc, GETDATE()) FROM \ud14c\uc774\ube14\uba85<\/span>
   ** \ub0a0\uc9dc\uc758 \uac00\uc0b0\uacfc \uac10\uc0b0\uc740 + -\ub85c \ud560 \uc218 \uc788\ub2e4<\/span>
      \ub0a0\uc9dc\uc640 \ub0a0\uc9dc \uc0ac\uc774\uc758 \uacc4\uc0b0\uc740 DATEDIFF(\ub3cc\ub824\uc8fc\ub294\uac12, \uc2dc\uc791\ub0a0\uc9dc, \ub05d\ub0a0\uc9dc) \ud568\uc218\ub97c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
  6. \ubb38\uc790 \uc5f0\uc0b0<\/span>
     SELECT \uce7c\ub7fc1 + \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85<\/span>
     SELECT \uce7c\ub7fc + '\ubb38\uc790\uc5f4'  FROM \ud14c\uc774\ube14\uba85<\/span>
     SELECT \uce7c\ub7fc1 + '\ubb38\uc790\uc5f4' + \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85<\/span>
   ** \uae30\ubcf8 \uc5f0\uacb0\uc740 \ubb38\uc790\uc640 \ubb38\uc790\uc774\uace0 \ubb38\uc790\uc640 \uc22b\uc790\uc758 \uc5f0\uacb0\uc740 CONVERT \ud568\uc218\ub97c \uc0ac\uc6a9\ud574\uc57c \ud55c\ub2e4<\/span>



 \ud568\uc218<\/span>
  1. \uc218\uce58 \ud568\uc218<\/span>
     ROUND(\uc218\uce58\uac12, \ubc18\uc62c\ub9bc\uc704\uce58)            \/* \ubc18\uc62c\ub9bc \ubc0f \uc790\ub974\uae30 *\/<\/span>
     ABS(\uc218\uce58 \ub370\uc774\ud0c0)                     \/* \uc808\ub300\uac12 *\/<\/span>
     SIGN(\uc218\uce58 \ub370\uc774\ud0c0)                    \/* \ubd80\ud638 *\/<\/span>
     SQRT(\uc218\uce58\uac12)                         \/* \uc81c\uacf1\uadfc *\/<\/span>
     POWER(\uc218\uce58\uac12, n)                     \/* n\uc2b9 *\/<\/span>
  2.  \ubb38\uc790\uc5f4 \ud568\uc218 \uc815\ub9ac<\/span>



1)  Ascii() - \ubb38\uc790\uc5f4\uc758 \uc81c\uc77c \uc67c\ucabd \ubb38\uc790\uc758 \uc544\uc2a4\ud0a4 \ucf54\ub4dc \uac12\uc744 \ubc18\ud658(Integer)<\/span>

\uc608) SELECT Ascii('abcd')<\/span>

    >>  \uacb0\uacfc\ub294 a\uc758 \uc544\uc2a4\ud0a4 \ucf54\ub4dc\uac12\uc778 97 \ubc18\ud658<\/span>



2) Char() - \uc815\uc218 \uc544\uc2a4\ud0a4 \ucf54\ub4dc\ub97c \ubb38\uc790\ub85c \ubc18\ud658(Char)<\/span>

\uc608) SELECT Char(97)<\/span>

      >> \uacb0\uacfc\ub294 a \ubc18\ud658<\/span>



3) Charindex() - \ubb38\uc790\uc5f4\uc5d0\uc11c \uc9c0\uc815\ud55c \uc2dd\uc758 \uc704\uce58\ub97c \ubc18\ud658<\/span>

\uc608)  SELECT Charindex('b','abcde')   >> \uacb0\uacfc : 2<\/span>
      SELECT Charindex('b','abcde',2) >> \uacb0\uacfc : 2<\/span>
      SELECT Charindex('b','abcde',3) >> \uacb0\uacfc : 0<\/span>

       -- \uc778\uc218\uac12\uc774 3\uac1c\uc77c\ub54c \ub9c8\uc9c0\ub9c9\uc740 abcde \uc5d0\uc11c\uc758 \ubb38\uc790\uc5f4 \uac80\uc0c9 \uc2dc\uc791\uc704\uce58\ub97c \ub9d0\ud558\uba70<\/span>

            2\uc778\uacbd\uc6b0\ub294 bcde \ub77c\ub294 \ubb38\uc790\uc5f4\uc5d0 \ub300\ud574\uc11c \uac80\uc0c9<\/span>

            3\uc778 \uacbd\uc6b0\ub294 cde \ub77c\ub294 \ubb38\uc790\uc5f4\uc5d0 \ub300\ud574\uc11c \uac80\uc0c9 \ud558\uac8c \ub41c\ub2e4.<\/span>



4) Difference() - \ub450 \ubb38\uc790\uc2dd\uc5d0 SUONDEX  \uac12 \uac04\uc758 \ucc28\uc774\ub97c \uc815\uc218\ub85c \ubc18\ud658<\/span>

\uc608)  SELECT Difference('a','b')<\/span>



5) Left() - \ubb38\uc790\uc5f4\uc5d0\uc11c \uc67c\ucabd\uc5d0\uc11c\ubd80\ud130 \uc9c0\uc815\ud55c \uc218\ub9cc\ud07c\uc758 \ubb38\uc790\ub97c \ubc18\ud658<\/span>

\uc608)  SELECT Left('abced',3)       \uacb0\uacfc >> 3<\/span>



6) Len() - \ubb38\uc790\uc5f4\uc758 \uae38\uc774 \ubc18\ud658<\/span>

\uc608) SELECT Len('abced')         \uacb0\uacfc>>5<\/span>



7) Lower() - \ub300\ubb38\uc790\ub97c \uc18c\ubb38\uc790\ub85c \ubc18\ud658<\/span>

\uc608) SELECT Lower('ABCDE')    \uacb0\uacfc >> abcde<\/span>



8) Ltrim() - \ubb38\uc790\uc5f4\uc758 \uc67c\ucabd \uacf5\ubc31 \uc81c\uac70<\/span>

\uc608)  SELECT Ltrim('   AB  CDE')    \uacb0\uacfc>> AB CDE<\/span>



9)Nchar()  - \uc9c0\uc815\ud55c \uc815\uc218 \ucf54\ub4dc\uc758 \uc720\ub2c8\ucf54\ub4dc \ubb38\uc790 \ubc18\ud658<\/span>

\uc608)  SELECT Nchar(20)       \uacb0\uacfc  >><\/span>





10) Replace -  \ubb38\uc790\uc5f4\uc5d0\uc11c \ubc14\uafb8\uace0 \uc2f6\uc740 \ubb38\uc790 \ub2e4\ub978 \ubb38\uc790\ub85c \ubcc0\ud658<\/span>

\uc608) SELECT Replace('abcde','a','1')    \uacb0\uacfc>>1bcde<\/span>



11) Replicate()  - \ubb38\uc790\uc2dd\uc744 \uc9c0\uc815\ud55c \ud69f\uc218\ub9cc\ud07c \ubc18\ubcf5<\/span>

\uc608) SELECT Replicate('abc',3)              \uacb0\uacfc>> abcabcabc<\/span>



12) Reverse() - \ubb38\uc790\uc5f4\uc744 \uc5ed\uc21c\uc73c\ub85c \ucd9c\ub825<\/span>

\uc608)   SELECT Reverse('abcde')             \uacb0\uacfc>> edcba<\/span>



13) Right()  - \ubb38\uc790\uc5f4\uc758 \uc624\ub978\ucabd\uc5d0\uc11c \ubd80\ud130 \uc9c0\uc815\ud55c \uc218 \ub9cc\ud07c \ubc18\ud658(Left() \uc640 \ube44\uc2b7 )<\/span>

\uc608) SELECT Right('abcde',3)                 \uacb0\uacfc>> cde<\/span>



14)Rtrim()  - \ubb38\uc790\uc5f4\uc758 \uc624\ub978\ucabd \uacf5\ubc31 \uc81c\uac70<\/span>

\uc608) SELECT Rtrim(' ab cde  ')               \uacb0\uacfc>>  ' ab cde'  <-- \uacf5\ubc31\uad6c\ubd84\uc744\uc704\ud574 ' \ud45c\uc2dc<\/span>



15)  Space()  -  \uc9c0\uc815\ud55c \uc218\ub9cc\ud07c\uc758 \uacf5\ubc31 \ubb38\uc790 \ubc18\ud658<\/span>

\uc608)  SELECT Space(10)                  \uacb0\uacfc >>  '         '   -- \uadf8\ub0e5 \uacf5\ubc31\uc774 \ub098\uc634<\/span>

      \ud655\uc778\uc744 \uc704\ud574\uc11c  SELECT 'S'+Space(10)+'E'   \uacb0\uacfc >> S        E<\/span>



16) Substring()  -  \ubb38\uc790,\uc774\uc9c4,\ud14d\uc2a4\ud2b8 \ub610\ub294 \uc774\ubbf8\uc9c0 \uc2dd\uc758 \uc77c\ubd80\ub97c \ubc18\ud658<\/span>

\uc608) SELECT Substring('abcde',2,3)      \uacb0\uacfc>>  bcd<\/span>



17)Unicode() - \uc2dd\uc5d0 \uc788\ub294 \uccab\ubc88\uc9f8 \ubb38\uc790\uc758 \uc720\ub2c8\ucf54\ub4dc \uc815\uc218 \uac12\uc744 \ubc18\ud658<\/span>

\uc608)SELECT Unicode('abcde')              \uacb0\uacfc >> 97<\/span>



18)Upper() - \uc18c\ubb38\uc790\ub97c \ub300\ubb38\uc790\ub85c \ubc18\ud658<\/span>

\uc608) SELECT Upper('abcde')              \uacb0\uacfc>> ABCDE<\/span>



\u203b \uae30\ud0c0 \ud568\uc218 Tip<\/span>



19) Isnumeric - \ud574\ub2f9 \ubb38\uc790\uc5f4\uc774 \uc22b\uc790\ud615\uc774\uba74 1 \uc544\ub2c8\uba74 0\uc744 \ubc18\ud658<\/span>

>> \uc22b\uc790 : 1  , \uc22b\uc790X :0<\/span>

\uc608) SELECT Isnumeric('30')       \uacb0\uacfc >> 1<\/span>

     SELECT Isnumeric('3z')        \uacb0\uacfc >> 0<\/span>



20) Isdate()  - \ud574\ub2f9 \ubb38\uc790\uc5f4\uc774 Datetime\uc774\uba74 1 \uc544\ub2c8\uba74 0<\/span>
>> \ub0a0\uc9dc : 1   , \ub0a0\uc9dc X :0<\/span>
\uc608) SELECT Isdate('20071231')    \uacb0\uacfc >> 1<\/span>

     SELECT Isdate(getdate())      \uacb0\uacfc >> 1<\/span>
     SELECT Isdate('2007123')      \uacb0\uacfc >> 0<\/span>

     SELECT Isdate('aa')             \uacb0\uacfc >> 0<\/span>





\u203b \ub0a0\uc9dc\ubc0f \uc2dc\uac04\ud568\uc218 \uc815\ub9ac<\/span>



getdate()  >> \uc624\ub298 \ub0a0\uc9dc\ub97c \ubc18\ud658(datetime)<\/span>



1> DateAdd()   - \uc9c0\uc815\ud55c \ub0a0\uc9dc\uc5d0 \uc77c\uc815 \uac04\uaca9\uc744 + \uc0c8 \uc77c\uc815\uc744 \ubc18\ud658<\/span>

\uc608) SELECT Dateadd(s,2000,getdate())<\/span>



2> Datediff()  - \uc9c0\uc815\ud55c \ub450 \ub0a0\uc9dc\uc758 \uac04\uc758 \uacb9\uce58\ub294 \ub0a0\uc9dc \ubc0f \uc2dc\uac04 \ubc94\uc704 \ubc18\ud658<\/span>

\uc608)SELECT DateDiff(d,getdate(),(getdate()+31))<\/span>



3> Datename() -\uc9c0\uc815\ud55c \ub0a0\uc9dc\uc5d0 \ud2b9\uc815 \ub0a0\uc9dc\ubd80\ubd84\uc744 \ub098\ud0c0\ub0b4\ub294 \ubb38\uc790\uc5f4\uc744 \ubc18\ud658<\/span>

\uc608) SELECT Datename(d,getdate())<\/span>



4> Datepart() -\uc9c0\uc815\ud55c \ub0a0\uc9dc\uc5d0 \ud2b9\uc815 \ub0a0\uc9dc\ubd80\ubd84\uc744 \ub098\ud0c0\ub0b4\ub294 \uc815\uc218\ub97c  \ubc18\ud658<\/span>

\uc608) SELECT Datepart(d,getdate())<\/span>

 ** \ub3cc\ub824\uc8fc\ub294\uac12(\uc57d\uc5b4)<\/span>
      Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,<\/span>
      Hour-hh, Minute-mi, Second-ss, Milisecond-ms<\/span>
      SELECT DATEADD(dd, 7, \ub0a0\uc9dc\uce7c\ub7fc)<\/span>

>> Datename , Datepart \uc740 \uacb0\uacfc \uac12\uc740 \uac19\uc73c\ub098 \ubc18\ud658 \uac12\uc758 \ud0c0\uc785\uc774 \ud2c0\ub9bc.<\/span>



5> Day() -\uc9c0\uc815\ud55c \ub0a0\uc9dc\uc5d0 \uc77c \ubd80\ubd84\uc744 \ub098\ud0c0\ub0b4\ub294 \uc815\uc218\ub97c  \ubc18\ud658<\/span>

\uc608) SELECT Day(getdate())      -- \uc77c \ubc18\ud658<\/span>

     SELECT Month(getdate())   -- \uc6d4 \ubc18\ud658<\/span>

       SELECT Year(getdate())           --  \ub144 \ubc18\ud658     4. \ud615\ubcc0\ud658 \ud568\uc218<\/span>
     CONVERT(\ub370\uc774\ud0c0 \ud0c0\uc785, \uce7c\ub7fc)                 \/* \uce7c\ub7fc\uc744 \uc6d0\ud558\ub294 \ub370\uc774\ud0c0 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658 *\/<\/span>
     CONVERT(\ub370\uc774\ud0c0 \ud0c0\uc785, \uce7c\ub7fc, \ub0a0\uc9dc\ud615 \uc2a4\ud0c0\uc77c)  \/* \uc6d0\ud558\ub294 \ub0a0\uc9dc \uc2a4\ud0c0\uc77c\ub85c \ubcc0\ud658 *\/<\/span>
     CAST(\uce7c\ub7fc AS \ub370\uc774\ud0c0 \ud0c0\uc785)                  \/* \uce7c\ub7fc\uc744 \uc6d0\ud558\ub294 \ub370\uc774\ud0c0 \ud0c0\uc785\uc73c\ub85c \ubcc0\ud658 *\/<\/span>
   ** \uc2a4\ud0c0\uc77c<\/span>
      1->mm\/dd\/yy, 2->yy.mm.dd, 3->dd\/mm\/yy, 4->dd.mm.yy, 5->dd-mm-yy,<\/span>
      8->hh:mm:ss, 10->mm-dd-yy, 11->yy\/mm\/dd, 12->yymmdd<\/span>
      SELECT CONVERT(varchar(10), \ub0a0\uc9dc\uce7c\ub7fc, 2)<\/span>



 \uadf8\ub8f9\ud654  \ud568\uc218<\/span>
   SELECT COUNT(*) FROM \ud14c\uc774\ube14\uba85               \/* \uc804\uccb4 \ub370\uc774\ud0c0\uc758 \uac2f\uc218 \uac00\uc838\uc624\uae30 *\/<\/span>
   SEELECT COUNT(\uce7c\ub7fc) FROM \ud14c\uc774\ube14\uba85           \/* NULL\uc740 \uc81c\uc678\ud55c \uce7c\ub7fc\uc758 \ub370\uc774\ud0c0 \uac2f\uc218 \uac00\uc838\uc624\uae30 *\/<\/span>
   SELECT SUM(\uce7c\ub7fc) FROM \ud14c\uc774\ube14\uba85              \/* \uce7c\ub7fc\uc758 \ud569\uacc4 \uad6c\ud558\uae30 *\/<\/span>
   SELECT MAX(\uce7c\ub7fc) FROM \ud14c\uc774\ube14\uba85              \/* \uce7c\ub7fc\uc758 \ucd5c\ub300\uac12 \uad6c\ud558\uae30 *\/<\/span>
   SELECT MIN(\uce7c\ub7fc) FROM \ud14c\uc774\ube14\uba85              \/* \uce7c\ub7fc\uc758 \ucd5c\uc18c\uac12 \uad6c\ud558\uae30 *\/<\/span>
   SELECT AVG(\uce7c\ub7fc) FROM \ud14c\uc774\ube14\uba85              \/* \uce7c\ub7fc\uc758 \ud3c9\uade0\uac12 \uad6c\ud558\uae30 *\/<\/span>
  GROUP BY\ubb38<\/span>
   SELECT \uce7c\ub7fc FROM \ud14c\uc774\ube14\uba85 GROUP BY \uce7c\ub7fc<\/span>
   SELECT \uce7c\ub7fc1, SUM(\uce7c\ub7fc2) FROM \ud14c\uc774\ube14\uba85 GROUP BY \uce7c\ub7fc1<\/span>
   SELECT \uce7c\ub7fc1, COUNT(*) FROM \ud14c\uc774\ube14\uba85 GROUP BY \uce7c\ub7fc1<\/span>
   SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, MAX(\uce7c\ub7fc3) FROM \ud14c\uc774\ube14\uba85 GROUP BY \uce7c\ub7fc1, \uce7c\ub7fc2<\/span>
    ** GROUP BY\ub97c \uc9c0\uc815\ud55c \uacbd\uc6b0 SELECT \ub2e4\uc74c\uc5d0\ub294 \ubc18\ub4dc\uc2dc GROUP BY\uc5d0\uc11c \uc9c0\uc815\ud55c \uce7c\ub7fc \ub610\ub294<\/span>
       \uadf8\ub8f9 \ud568\uc218\ub9cc\uc774 \uc62c \uc218 \uc788\ub2e4<\/span>


  \uc870\uac74<\/span>
   SELECT \uce7c\ub7fc1, SUM(\uce7c\ub7fc2) FROM \ud14c\uc774\ube14\uba85 GROUP BY \uce7c\ub7fc1 HAVING SUM(\uce7c\ub7fc2) < a<\/span>
   SELECT \uce7c\ub7fc1, SUM(\uce7c\ub7fc2) FROM \ud14c\uc774\ube14\uba85 ORDER BY \uce7c\ub7fc1 COMPUTE SUM(\uce7c\ub7fc2)<\/span>
    ** HAVING:        \uadf8\ub8f9 \ud568\uc218\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc758 \uc870\uac74\uc744 \uc9c0\uc815\ud55c\ub2e4<\/span>
       HAVING\uc758 \uc704\uce58: GROUP BY\uc758 \ub4a4 ORDER BY\uc758 \uc55e\uc5d0 \uc9c0\uc815\ud55c\ub2e4<\/span>
       COMPUTE:       \uac01 \uadf8\ub8f9\uc758 \uc18c\uacc4\ub97c \uc694\uc57d\ud574\uc11c \ubcf4\uc5ec\uc900\ub2e4<\/span>
                      ORDER BY\uac00 \ud56d\uc0c1 \uc120\ud589\ud574\uc11c \ub098\uc640\uc57c \ud55c\ub2e4<\/span>
  \uc870\uac74\uc808\uc758 \uc11c\ube0c \ucffc\ub9ac<\/span>
   ** SELECT \ub610\ub294 INSERTY, UPDATE, DELETE \uac19\uc740 \ubb38\uc758 \uc870\uac74\uc808\uc5d0\uc11c SELECT\ubb38\uc744 \ub610 \uc0ac\uc6a9\ud558\ub294 \uac83\uc774\ub2e4<\/span>
      SELECT\ubb38 \uc548\uc5d0 \ub610 \ub2e4\ub978 SELECT\ubb38\uc774 \ud3ec\ud568\ub418\uc5b4 \uc788\ub2e4\uace0 \uc911\ucca9 SELECT\ubb38(NESTED SELECT)\uc774\ub77c\uace0 \ud55c\ub2e4<\/span>
   ** \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0\ub294 \uc5ec\ub7ec\uba85\uc774 \uc5d1\uc138\uc2a4\ud558\uace0 \uc788\uae30 \ub54c\ubb38\uc5d0 \ucffc\ub9ac\ub97c \uc5ec\ub7ec\uac1c \ub098\ub204\uc5b4\uc11c \uc0ac\uc6a9\ud558\uba74 \ub370\uc774\ud0c0\uc758 \uac12\uc774<\/span>
      \ub2ec\ub77c\uc9c8\uc218 \uc788\uae30\ub54c\ubb38\uc5d0 \ud2b8\ub79c\uc7ad\uc158 \ucc98\ub9ac\ub97c \ud558\uc9c0 \uc54a\ub294\ub2e4\uba74 \ubcf5\uc218\uc758 \ucffc\ub9ac\ub97c \ud558\ub098\uc758 \ucffc\ub9ac\ub85c \ub9cc\ub4e4\uc5b4 \uc0ac\uc6a9\ud574\uc57c \ud55c\ub2e4<\/span>
    SELECT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc2 = (SELECT \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74)<\/span>
    SELECT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc1 IN (SELECT \uce7c\ub7fc1 FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74)<\/span>
   ** \uc11c\ube0c \ucffc\ub9ac\uc5d0\uc11c\ub294 \ub2e4\ub978 \ud14c\uc774\ube14\uc744 \ud3ec\ud568\ud560 \uc218 \uc788\ub2e4<\/span>
      \ub450\uac1c\uc758 \ud14c\uc774\ube14\uc5d0\uc11c \uc77d\uc5b4\uc624\ub294 \uc11c\ube0c\ucffc\ub9ac\uc758 \uacbd\uc6b0 \uc11c\ube0c \ucffc\ub9ac\ucabd\uc5d0 \ub370\uc774\ud0c0\uac00 \uc801\uc740 \ud14c\uc774\ube14\uc744 \uc8fc \ucffc\ub9ac\ucabd\uc5d0 \ub370\uc774\ud0c0\uac00<\/span>
      \ub9ce\uc740 \ud14c\uc774\ube14\uc744 \uc9c0\uc815\ud574\uc57c \ucc98\ub9ac \uc18d\ub3c4\uac00 \ube68\ub77c\uc9c4\ub2e4<\/span>
    SELECT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uce7c\ub7fc1 IN (SELECT \uce7c\ub7fc2-1 FROM \ud14c\uc774\ube14\uba852 WHERE \uc870\uac74)<\/span>
   ** FROM\uad6c\uc5d0\uc11c \uc11c\ube0c \ucffc\ub9ac\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4<\/span>
      \uc0ac\uc6a9\uc2dc \ubc18\ub4dc\uc2dc \ubcc4\uce6d\uc744 \ubd99\uc5ec\uc57c \ud558\uace0 \ucc98\ub9ac \uc18d\ub3c4\uac00 \ube68\ub77c\uc9c4\ub2e4<\/span>
    SELECT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac741 AND \uc870\uac742<\/span>
    SEELCT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM (SELECT \uce7c\ub7fc1, \uce7c\ub7fc2 FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac741) \ubcc4\uce6d WHERE \uc870\uac742<\/span>

 \ub370\uc774\ud0c0 \ud3b8\uc9d1<\/span>


  \ucd94\uac00<\/span>
   ** NULL \uac12\uc744 \ud5c8\uc6a9\ud558\uc9c0\ub3c4 \uc54a\uace0 \ub514\ud3f4\ud2b8 \uac12\ub3c4 \uc9c0\uc815\ub418\uc5b4 \uc788\uc9c0 \uc54a\uc740 \uce7c\ub7fc\uc5d0 \uac12\uc744 \uc9c0\uc815\ud558\uc9c0 \uc54a\uc740\ucc44<\/span>
      INSERT\ub97c \uc218\ud589\ud558\uba74 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud55c\ub2e4<\/span>
   ** \uc218\uce58\uac12\uc740 \uadf8\ub300\ub85c \ubb38\uc790\uac12\uc740 ''(\uc791\uc740\ub530\uc634\ud45c)\ub85c \ub9c8\ubb34\ub9ac \ud55c\ub2e4<\/span>
   ** SELECT INTO\ub294 \uce7c\ub7fc\uacfc \ub370\uc774\ud0c0\ub294 \ubcf5\uc0ac\ud558\uc9c0\ub9cc \uce7c\ub7fc\uc5d0 \uc124\uc815\ub41c \ud504\ub77c\uc774\uba38\ub9ac, \ud3ec\ub9b0 \ud0a4\ub4f1\ub4f1\uc758 \uc81c\uc57d \uc870\uac74\uc740<\/span>
      \ubcf5\uc0ac\ub418\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0 \ubcf5\uc0ac\uac00 \ub05d\ub09c\ud6c4 \uc0c8\ub85c \uc124\uc815\ud574 \uc8fc\uc5b4\uc57c \ud55c\ub2e4<\/span>

   INSERT INTO \ud14c\uc774\ube14\uba85 VALUES (\uac121, \uac122, ...)     \/* \ubaa8\ub4e0 \ud544\ub4dc\uc5d0 \ub370\uc774\ud0c0\ub97c \ub123\uc744 \ub54c *\/<\/span>
   INSERT INTO \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc1, \uce7c\ub7fc2, ...) VALUES (\uac121, \uac122, ...)   \/* \ud2b9\uc815 \uce7c\ub7fc\uc5d0\ub9cc \ub370\uc774\ud0c0\ub97c \ub123\uc744 \ub54c *\/<\/span>
   INSERT INTO \ud14c\uc774\ube14\uba85 SELECT * FROM \ud14c\uc774\ube14\uba852                      \/* \uc774\ubbf8 \uc874\uc7ac\ud558\ub294 \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud0c0 \ucd94\uac00 *\/<\/span>
   INSERT INTO \ud14c\uc774\ube14\uba85(\uce7c\ub7fc1, \uce7c\ub7fc2, ...) SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, ...) FROM \ud14c\uc774\ube14\uba852<\/span>
   SELECT * INTO \ud14c\uc774\ube14\uba85 FROM \ud14c\uc774\ube14\uba852                             \/* \uc0c8\ub85c \ub9cc\ub4e0 \ud14c\uc774\ube14\uc5d0 \ub370\uc774\ud0c0 \ucd94\uac00 *\/<\/span>
   SELECT \uce7c\ub7fc1, \uce7c\ub7fc2, ... \ud14c\uc774\ube14\uba85 FROM \ud14c\uc774\ube14\uba852<\/span>

  \uac31\uc2e0<\/span>
   UPDATE \ud14c\uc774\ube14\uba85 SET \uce7c\ub7fc1=\uac121, \uce7c\ub7fc2=\uac122            \/* \uc804\uccb4 \ub370\uc774\ud0c0 \uac31\uc2e0 *\/<\/span>
   UPDATE \ud14c\uc774\ube14\uba85 SET \uce7c\ub7fc1=\uac121, \uce7c\ub7fc2=\uac122 WHERE \uc870\uac74               \/* \uc870\uac74\uc5d0 \ud574\ub2f9\ub418\ub294 \ub370\uc774\ud0c0 \uac31\uc2e0 *\/<\/span>



- UPDATE~SELECT<\/span>

UPDATE A<\/span>
SET A.cyberLectures = B.bizAddress<\/span>
FROM OF_Member A, OF_Member B<\/span>
WHERE A.no = B.no<\/span>



  \uc0ad\uc81c<\/span>
   DELETE FROM \ud14c\uc774\ube14\uba85                                              \/* \uc804\uccb4 \ub370\uc774\ud0c0 \uc0ad\uc81c *\/<\/span>
   DELETE FROM \ud14c\uc774\ube14\uba85 WHERE \uc870\uac74       \/* \uc870\uac74\uc5d0 \ud574\ub2f9\ub418\ub294 \ub370\uc774\ud0c0 \uc0ad\uc81c *\/<\/span>



 \uc624\ube0c\uc81d\ud2b8<\/span>
  ** \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub294 \uc544\ub798 \uc624\ube0c\uc81d\ud2b8\ub4e4\uc744 \uac01\uac01\uc758 \uc720\uc800\ubcc4\ub85c \uad00\ub9ac\ub97c \ud558\ub294\ub370 Schema(\uc2a4\ud0a4\ub9c8)\ub294 \uac01 \uc720\uc800\ubcc4 \uc18c\uc720 \ub9ac\uc2a4\ud2b8\uc774\ub2e4<\/span>

  1. Table(\ud14c\uc774\ube14)<\/span>
   ** CREATE\uc77c\ub54c \ud504\ub77c\uc774\uba38\ub9ac \ud0a4\ub97c \uc124\uc815\ud558\uc9c0 \uc54a\ub294\ub2e4\uba74 (\uce7c\ub7fc int IDENTITY(1, 1) NOT NULL) \uc790\ub3d9 \uce7c\ub7fc\uc744 \ub9cc\ub4e0\ub2e4<\/span>
      \ub370\uc774\ud0c0\ub4e4\uc758 \uc785\ub825 \uc21c\uc11c\uc640 \uc911\ubcf5\ub41c \ub370\uc774\ud0c0\ub97c \uad6c\ubcc4\ud558\uae30 \uc704\ud574\uc11c \ubc18\ub4dc\uc2dc \ud544\uc694\ud558\ub2e4<\/span>
   ** \ud14c\uc774\ube14 \uc815\ubcf4 SP_HELP \ud14c\uc774\ube14\uba85, \uc81c\uc57d \uc870\uac74\uc740 SP_HELPCONSTRAINT \ud14c\uc774\ube14\uba85 \uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>

   CREATE TABLE \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc774\ub984.\uc18c\uc720\uc790\uc774\ub984.\ud14c\uc774\ube14\uc774\ub984 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 \uc81c\uc57d, ...) \/* \ud14c\uc774\ube14 \ub9cc\ub4e4\uae30 *\/<\/span>
   DROP TABLE \ud14c\uc774\ube14\uba85                                                           \/* \ud14c\uc774\ube14 \uc0ad\uc81c *\/<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 ADD \uce7c\ub7fc \ub370\uc774\ud0c0\ud615 \uc81c\uc57d, ...                              \/* \uce7c\ub7fc \ucd94\uac00 *\/<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 DROP COLUMN \uce7c\ub7fc                                         \/* \uce7c\ub7fc \uc0ad\uc81c *\/<\/span>
    ** DROP COLUMN\uc73c\ub85c \ub2e4\uc74c \uce7c\ub7fc\uc740 \uc0ad\uc81c\ub97c \ud560 \uc218 \uc5c6\ub2e4<\/span>
       - \ubcf5\uc81c\ub41c \uce7c\ub7fc<\/span>
       - \uc778\ub371\uc2a4\ub85c \uc0ac\uc6a9\ud558\ub294 \uce7c\ub7fc<\/span>
       - PRIMARY KEY, FOREGIN KEY, UNIQUE, CHECK\ub4f1\uc758 \uc81c\uc57d \uc870\uac74\uc774 \uc9c0\uc815\ub41c \uce7c\ub7fc<\/span>
       - DEFAULT \ud0a4\uc6cc\ub4dc\ub85c \uc815\uc758\ub41c \uae30\ubcf8\uac12\uacfc \uc5f0\uacb0\ub418\uac70\ub098 \uae30\ubcf8 \uac1c\uccb4\uc5d0 \ubc14\uc778\ub529\ub41c \uce7c\ub7fc<\/span>
       - \uaddc\uce59\uc5d0 \ubc14\uc778\ub529\ub41c \uce7c\ub7fc<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 DEFAULT \ub514\ud3f4\ud2b8\uac12, ...)                   \/* \ub514\ud3f4\ud2b8 \uc9c0\uc815 *\/<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 CONSTRAINT \uc774\ub984 UNIQUE, ...)             \/* \uc720\ub2c8\ud06c \uc124\uc815 *\/<\/span>
    ** UNIQUE\ub780 \uc9c0\uc815\ud55c \uce7c\ub7fc\uc5d0 \uac19\uc740 \uac12\uc774 \ub4e4\uc5b4\uac00\ub294\uac83\uc744 \uae08\uc9c0\ud558\ub294 \uc81c\uc57d\uc73c\ub85c \uae30\ubcf8 \ud0a4\uc640 \ube44\uc2b7\ud558\uc9c0\ub9cc<\/span>
       NULL \uac12\uc744 \ud558\uc6a9\ud558\ub294\uac83\uc774 \ub2e4\ub974\ub2e4<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 CONSTRAINT \uc774\ub984 NOT NULL, ...)           \/* NOT NULL \uc124\uc815 *\/<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 CONSTRAINT \uc774\ub984 PRIMARY KEY, ...)        \/* \uae30\ubcf8 \ud0a4 \uc124\uc815 *\/<\/span>
    ** \uae30\ubcf8 \ud0a4\ub294 \uc720\ub2c8\ud06c\uc640 NOT NULL\uc774 \uc870\ud569\ub41c \uc81c\uc57d\uc73c\ub85c \uc0c9\uc778\uc774 \uc790\ub3d9\uc801\uc73c\ub85c \uc9c0\uc815\ub418\uace0 \ub370\uc774\ud0c0\ub97c<\/span>
       \uc720\uc77c\ud558\uac8c \ub9cc\ub4e4\uc5b4 \uc900\ub2e4<\/span>
    **  \uae30\ubcf8 \ud0a4\ub294 \ud55c \ud14c\uc774\ube14\uc5d0 \ud55c\uac1c\uc758 \uce7c\ub7fc\ub9cc \uac00\ub2a5\ud558\ub2e4<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 CONSTRAINT \uc774\ub984 FOREIGN KEY REFERENCES \ubd80\ubaa8\ud14c\uc774\ube14\uc774\ub984(\ubd80\ubaa8\uce7c\ub7fc), ...)<\/span>
   CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc \ub370\uc774\ud0c0\ud615 CONSTRAINT \uc774\ub984 CHECK(\uc870\uac74), ...)        \/* CHECK \uc124\uc815 *\/<\/span>
    ** CHECK\ub294 \uc870\uac74\uc744 \uc784\uc758\ub85c \uc815\uc758\ud560 \uc218 \uc788\ub294 \uc81c\uc57d\uc73c\ub85c \uc124\uc815\ub418\uba74 \uc870\uac74\uc744 \ucda9\uc871\uc2dc\ud0a4\ub294 \ub370\uc774\ud0c0\ub9cc<\/span>
       \ub4f1\ub85d\ud560 \uc218 \uc788\uace0 SELECT\uc758 WHERE\uad6c\uc640 \uac19\uc740 \uc870\uac74\uc744 \uc9c0\uc815\ud55c\ub2e4<\/span>
    ** CONSTRAINT\uc640 \uc81c\uc57d \uc774\ub984\uc744 \uc4f0\uc9c0 \uc54a\uc73c\uba74 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uac00 \uc54c\uc544\uc11c \uc774\ub984\uc744 \ubd99\uc774\uc9c0\ub9cc<\/span>
       \ubcf5\uc7a1\ud55c \uc774\ub984\uc774 \ub418\uae30 \ub54c\ubb38\uc5d0 \ub418\ub3c4\ub85d\uc774\uba74 \uc0ac\uc6a9\uc790\uac00 \uc9c0\uc815\ud558\ub3c4\ub85d \ud55c\ub2e4<\/span>
    ** CONSTRAINT\ub294 \uce7c\ub7fc\uacfc \ub370\uc774\ud0c0\ud615\uc744 \ubaa8\ub450 \uc815\uc758\ud55c \ub4a4\uc5d0 \ub9e8 \ub9c8\uc9c0\ub9c9\uc5d0 \uc124\uc815\ud560 \uc218 \uc788\ub2e4<\/span>
     CREATE TABLE \ud14c\uc774\ube14\uba85 (\uce7c\ub7fc1 \ub370\uc774\ud0c0\ud615,<\/span>
             \uce7c\ub7fc2 \ub370\uc774\ud0c0\ud615, ...<\/span>
             CONSTRAINT \uc774\ub984 PRIMARY KEY(\uce7c\ub7fc1)<\/span>
                           CONSTRAINT \uc774\ub984 CHECK(\uce7c\ub7fc2 < a) ...)<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 ADD CONSTRAINT \uc774\ub984 \uc81c\uc57d\ubb38                                \/* \uc81c\uc57d \ucd94\uac00 *\/<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 DROP CONSTRAINT \uc81c\uc57d\uba85                                    \/* \uc81c\uc57d \uc0ad\uc81c *\/<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 NOCHECK CONSTRAINT \uc81c\uc57d\uba85                                 \/* \uc81c\uc57d \ud6a8\ub825 \uc815\uc9c0 *\/<\/span>
   ALTER TABLE \ud14c\uc774\ube14\uba85 CHECK CONSTRAINT \uc81c\uc57d\uba85                                   \/* \uc81c\uc57d \ud6a8\ub825 \uc720\ud6a8 *\/<\/span>
    ** \uc81c\uc57d\uba85\uc740 \ud14c\uc774\ube14\uc744 \ub9cc\ub4e4\ub54c \uc0ac\uc6a9\uc790\uac00 \uc9c0\uc815\ud55c \ud30c\uc77c \uc774\ub984\uc744 \ub9d0\ud55c\ub2e4<\/span>



  2. View(\ubdf0)<\/span>
   ** \uc790\uc8fc \uc0ac\uc6a9\ud558\ub294 SELECT\ubb38\uc774 \uc788\uc744\ub54c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
      \ud14c\uc774\ube14\uc5d0 \uc874\uc7ac\ud558\ub294 \uce7c\ub7fc\ub4e4\uc911 \ud2b9\uc815 \uce7c\ub7fc\uc744 \ubcf4\uc774\uace0 \uc2f6\uc9c0 \uc54a\uc744\ub54c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
      \ud14c\uc774\ube14\uac04\uc758 \uacb0\ud569\ub4f1\uc73c\ub85c \uc778\ud574 \ubcf5\uc7a1\ud574\uc9c4 SELECT\ubb38\uc744 \uac04\ub2e8\ud788 \ub2e4\ub8e8\uace0 \uc2f6\uc744\ub54c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
   ** \ubdf0\ub97c \ub9cc\ub4e4\ub54c COMPUTE, COMPUTE BY, SELECT INTO, ORDER BY\ub294 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uace0<\/span>
      #, ##\uc73c\ub85c \uc2dc\uc791\ub418\ub294 \uc784\uc2dc \ud14c\uc774\ube14\ub3c4 \ubdf0\uc758 \ub300\uc0c1\uc73c\ub85c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4<\/span>
   ** \ubdf0\uc758 \ub0b4\uc6a9\uc744 \ubcf4\uace0 \uc2f6\uc73c\uba74 SP_HELPTEXT \ubdf0\uba85 \uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>

   CREATE VIEW \ubdf0\uba85 AS SELECT\ubb38                            \/* \ubdf0 \ub9cc\ub4e4\uae30 *\/<\/span>
   CREATE VIEW \ubdf0\uba85 (\ubcc4\uce6d1, \ubcc4\uce6d2, ...) AS SELECT\ubb38    \/* \uce7c\ub7fc\uc758 \ubcc4\uce6d \ubd99\uc774\uae30 *\/<\/span>
   CREATE VIEW \ubdf0\uba85 AS (SELECT \uce7c\ub7fc1 AS \ubcc4\uce6d1, \uce7c\ub7fc2 AS \ubcc4\uce6d2, ...)<\/span>
   ALTER VIEW \ubdf0\uba85 AS SELECT\ubb38                                       \/* \ubdf0 \uc218\uc815 *\/<\/span>
   DROP VIEW \ubdf0\uba85                                                    \/* \ubdf0 \uc0ad\uc81c *\/<\/span>
   CREATE VIEW \ubdf0\uba85 WITH ENCRYPTION AS SELECT\ubb38                      \/* \ubdf0 \uc554\ud638 *\/<\/span>
    ** \ud55c\ubc88 \uc554\ud638\ud654\ub41c \ubdf0\ub294 \uc18c\uc2a4 \ucf54\ub4dc\ub97c \ubcfc \uc218 \uc5c6\uc73c\ubbc0\ub85c \ubdf0\ub97c \uc554\ud638\ud654\ud558\uae30\uc804\uc5d0<\/span>
       \ubdf0\uc758 \ub0b4\uc6a9\uc744 \uc2a4\ud06c\ub9bd\ud2b8 \ud30c\uc77c\ub85c \uc800\uc7a5\ud558\uc5ec \ubcf4\uad00\ud55c\ub2e4<\/span>
   INSERT INTO \ubdf0\uba85 (\uce7c\ub7fc1, \uce7c\ub7fc2, ...) VALUES (\uac121, \uac122, ...)<\/span>
   UPDATE \ubdf0\uba85 SET \uce7c\ub7fc=\uac12 WHERE \uc870\uac74<\/span>
    ** \uc6d0\ub798 \ud14c\uc774\ube14\uc5d0 \uc788\ub294 \ubc18\ub4dc\uc2dc \uac12\uc744 \uc785\ub825\ud574\uc57c \ud558\ub294 \uce7c\ub7fc\uc774 \ud3ec\ud568\ub418\uc5b4 \uc788\uc9c0 \uc54a\uac70\ub098<\/span>
       \uc6d0\ub798 \uce7c\ub7fc\uc744 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0 \ubcc0\ud615\ub41c \uce7c\ub7fc\uc744 \uc0ac\uc6a9\ud558\ub294 \ubdf0\ub294 \ub370\uc774\ud0c0\ub97c \ucd94\uac00\ud558\uac70\ub098<\/span>
       \uac31\uc2e0\ud560 \uc218 \uc5c6\ub2e4<\/span>
    ** WHERE \uc870\uac74\uc744 \uc9c0\uc815\ud55c \ubdf0\ub294 \ubdf0\ub97c \ub9cc\ub4e4\uc5c8\uc744\ub54c WITH CHECK OPTION\uc744 \uc9c0\uc815\ud558\uc9c0 \uc54a\uc558\ub2e4\uba74<\/span>
       \uc870\uac74\uc5d0 \ub9de\uc9c0 \uc54a\ub294 \ub370\uc774\ud0c0\ub97c \ucd94\uac00\ud560 \uc218 \uc788\uc9c0\ub9cc \ubdf0\uc5d0\uc11c\ub294 \ubcf4\uc774\uc9c0 \uc54a\ub294\ub2e4<\/span>
       \ub610\ud55c \ubdf0\ub97c \ud1b5\ud574\uc11c \uac00\uc838\uc628 \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \uac12\ub3c4 \ubdf0\uc758 \uc870\uac74\uc5d0 \ub9cc\uc871\ud558\uc9c0 \uc54a\ub294 \uac12\uc73c\ub85c\ub3c4<\/span>
       \uac31\uc2e0\ud560 \uc218 \uc788\ub2e4<\/span>
   CREATE VIEW \ubdf0\uba85 AS SELECT\ubb38 WITH CHECK OPTION<\/span>
    ** \ubdf0\uc758 \uc870\uac74\uc5d0 \ub9de\uc9c0 \uc54a\ub294 INSERT\ub098 UPDATE\ub97c \ub9c9\uc744\ub824\uba74 WITH CHECK OPTION\uc744 \uc124\uc815\ud55c\ub2e4<\/span>



  3. Stored Procedure(\uc800\uc7a5 \ud504\ub85c\uc2dc\uc800)<\/span>
   ** \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub0b4\uc5d0\uc11c SQL \uba85\ub839\uc744 \ucef4\ud30c\uc77c\ud560\ub54c \uce90\uc2dc\ub97c \uc774\uc6a9\ud560 \uc218 \uc788\uc73c\ubbc0\ub85c \ucc98\ub9ac\uac00 \ub9e4\uc6b0 \ube60\ub974\ub2e4<\/span>
      \ubc18\ubcf5\uc801\uc73c\ub85c SQL \uba85\ub839\uc744 \uc2e4\ud589\ud560 \uacbd\uc6b0 \ub9e4\ud68c \uba85\ub839\ub9c8\ub2e4 \ub124\ud2b8\uc6cc\ud06c\ub97c \uacbd\uc720\ud560 \ud544\uc694\uac00 \uc5c6\ub2e4<\/span>
      \uc5b4\ud50c\ub9ac\ucf00\uc774\uc158\ub9c8\ub2e4 \uc0c8\ub85c \ub9cc\ub4e4 \ud544\uc694\uc5c6\uc774 \uc774\ubbf8 \ub9cc\ub4e4\uc5b4\uc9c4 \ud504\ub85c\uc2dc\uc800\ub97c \ubc18\ubcf5 \uc0ac\uc6a9\ud55c\ub2e4<\/span>
      \ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \ub85c\uc9c1\uc744 \uc218\uc815\uc2dc \ud504\ub85c\uc2dc\uc800\ub294 \uc11c\ubc84\uce21\uc5d0 \uc788\uc73c\ubbc0\ub85c \uc5b4\ud50c\ub9ac\ucf00\uc774\uc158\uc744 \ub2e4\uc2dc \ucef4\ud30c\uc77c\ud560 \ud544\uc694\uac00 \uc5c6\ub2e4<\/span>
   ** \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800\uc758 \uc18c\uc2a4 \ucf54\ub4dc\ub97c \ubcf4\uace0 \uc2f6\uc73c\uba74 SP_HELPTEXT \ud504\ub85c\uc2dc\uc800\uba85 \uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>

   CREATE PROC \ud504\ub85c\uc2dc\uc800\uba85 AS SQL\ubb38   \/* \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800 *\/<\/span>
   CREATE PROC \ud504\ub85c\uc2dc\uc800\uba85 \ubcc0\uc218\uc120\uc5b8 AS SQL\ubb38 \/* \uc778\uc218\ub97c \uac00\uc9c0\ub294 \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800 *\/<\/span>
   CREATE PROC \ud504\ub85c\uc2dc\uc800\uba85 WITH ENCRYPTION AS SQL\ubb38 \/* \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800 \ubcf4\uc548 \uc124\uc815 *\/<\/span>
   CREATE PROC \ud504\ub85c\uc2dc\uc800\uba85                          \/* RETURN \uac12\uc744 \uac00\uc9c0\ub294 \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800 *\/<\/span>
       \uc778\uc2181 \ub370\uc774\ud0c0\ud615, ... \uc778\uc2182 \ub370\uc774\ud0c0\ud615 OUTPUT<\/span>
   AS<\/span>
       SQL\ubb38<\/span>
       RETURN \ub9ac\ud134\uac12<\/span>
   DROP PROCEDURE \ud504\ub85c\uc2dc\uc800\uba851, \ud504\ub85c\uc2dc\uc800\uba852, ...    \/* \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800 \uc0ad\uc81c *\/<\/span>

   \uba85\ub839\uc5b4<\/span>
    BEGIN ... END    \/* \ubb38\uc7a5\uc758 \ube14\ub85d \uc9c0\uc815 *\/<\/span>
    DECLARE @\ubcc0\uc218\uba85 \ub370\uc774\ud0c0\ud615  \/* \ubcc0\uc218 \uc120\uc5b8 *\/<\/span>
    SET @\ubcc0\uc218\uba85=\uac12    \/* \ubcc0\uc218\uc5d0 \uac12 \uc9c0\uc815 *\/<\/span>
    PRINT @\ubcc0\uc218\uba85    \/* \ud55c\uac1c\uc758 \ubcc0\uc218 \ucd9c\ub825 *\/<\/span>
    SELECT @\ubcc0\uc2181, @\ubcc0\uc2182   \/* \uc5ec\ub7ec\uac1c\uc758 \ubcc0\uc218 \ucd9c\ub825 *\/<\/span>
    IF \uc870\uac74     \/* \uc870\uac74 \uc218\ud589 *\/<\/span>
        \uc218\ud5891<\/span>
    ELSE<\/span>
        \uc218\ud5892<\/span>
    WHILE \uc870\uac741    \/* \ubc18\ubcf5 \uc218\ud589 *\/<\/span>
        BEGIN<\/span>
            IF \uc870\uac742<\/span>
         BREAK    - WHILE \ub8e8\ud504\ub97c \ube60\uc838 \ub098\uac04\ub2e4<\/span>
         CONTINUE - \uc218\ud589\uc744 \ucc98\ub9ac\ud558\uc9c0 \uc54a\uace0 \uc870\uac741\ub85c \ub418\ub3cc\uc544\uac04\ub2e4<\/span>
     \uc218\ud589<\/span>
        END<\/span>
    EXEC \uc800\uc7a5\ud504\ub85c\uc2dc\uc800   \/* SQL\ubb38\uc744 \uc2e4\ud589 *\/<\/span>
    EXEC @(\ubcc0\uc218\ub85c \uc9c0\uc815\ub41c SQL\ubb38)<\/span>
    GO     \/* BATCH\ub97c \uad6c\ubd84 \uc9c0\uc815 *\/<\/span>



   \uc5d0\uc81c<\/span>
    1. \uae30\ubcf8 \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800<\/span>
     CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2<\/span>


    2. \uc778\uc218\ub97c \uac00\uc9c0\ub294 \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800<\/span>
     CREATE PROC pUpdateSalary<\/span>
         @mul float=2, @mul2 int<\/span>
     AS<\/span>
         UPDATE Employee SET salary=salary* @Mul* @mul2<\/span>
     EXEC pUpdateSalary 0.5, 2       \/* \ubaa8\ub4e0 \ubcc0\uc218\uc5d0 \uac12\uc744 \ub300\uc785 *\/<\/span>
     EXEC pUpdateSalary @mul2=2      \/* \uc6d0\ud558\ub294 \ubcc0\uc218\uc5d0\ub9cc \uac12\uc744 \ub300\uc785 *\/<\/span>


    3. \ub9ac\ud134\uac12\uc744 \uac00\uc9c0\ub294 \uc800\uc7a5 \ud504\ub85c\uc2dc\uc800<\/span>
     CREATE PROC pToday<\/span>
         @Today varchar(4) OUTPUT<\/span>
     AS<\/span>
         SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))<\/span>
         RETURN @Today<\/span>
     DECLARE @answer varchar(4)<\/span>
     EXEC pToday @answer OUTPUT<\/span>
     SELECT @answer AS \uc624\ub298\ub0a0\uc9dc<\/span>


    4. \ubcc0\uc218 \uc120\uc5b8\uacfc \ub300\uc785, \ucd9c\ub825<\/span>
     ** @\ub294 \uc0ac\uc6a9\uc790 \ubcc0\uc218\uc774\uace0 @@\ub294 \uc2dc\uc2a4\ud15c\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 \ubcc0\uc218\uc774\ub2e4<\/span>

     DECLARE @EmpNum int, @t_name VARCHAR(20)<\/span>
     SET @EmpNum=10<\/span>

     SET @t_name = '\uac15\uc6b0\uc815'<\/span>
     SELECT @EmpNum<\/span>



\uc774\ub7f0\uc2dd\uc73c\ub85c \ub2e4\uc911\uc785\ub825\ub3c4 \uac00\ub2a5\ud568.<\/span>

SELECT  @no = no, @name = name, @level = level<\/span>
FROM OF_Member<\/span>
WHERE userId ='\"<\/span>





  4. Trigger(\ud2b8\ub9ac\uac70)<\/span>
   ** \ud55c \ud14c\uc774\ube14\uc758 \ub370\uc774\ud0c0\uac00 \ud3b8\uc9d1(INSERT\/UPDATE\/DELETE)\ub41c \uacbd\uc6b0\uc5d0 \uc790\ub3d9\uc73c\ub85c \ub2e4\ub978 \ud14c\uc774\ube14\uc758<\/span>
      \ub370\uc774\ud0c0\ub97c \uc0bd\uc785, \uc218\uc815, \uc0ad\uc81c\ud55c\ub2e4<\/span>
   ** \ud2b8\ub9ac\uac70 \ub0b4\uc6a9\uc744 \ubcf4\uace0 \uc2f6\uc73c\uba74 SP_HELPTRIGGER \ud2b8\ub9ac\uac70\uba85 \uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>

   CREATE TRIGGER \ud2b8\ub9ac\uac70\uba85 ON \ud14c\uc774\ube14\uba85 FOR INSERT AS SQL\ubb38         \/* INSERT \uc791\uc5c5\uc774 \uc218\ud589\ub420\ub54c *\/<\/span>
   CREATE TRIGGER \ud2b8\ub9ac\uac70\uba85 ON \ud14c\uc774\ube14\uba85 AFTER UPDATE AS SQL\ubb38       \/* UPDATE \uc791\uc5c5\uc774 \uc218\ud589\ub418\uace0 \ub09c \ud6c4 *\/<\/span>
   CREATE TRIGGER \ud2b8\ub9ac\uac70\uba85 ON \ud14c\uc774\ube14\uba85 INSTEAD OF DELETE AS SQL\ubb38<\/span>
   DROP TRIGGER \ud2b8\ub9ac\uac70\uba85<\/span>



  5. Cursor(\ucee4\uc11c)<\/span>
   ** SELECT\ub85c \uac00\uc838\uc628 \uacb0\uacfc\ub4e4\uc744 \ud558\ub098\uc529 \uc77d\uc5b4\ub4e4\uc5ec \ucc98\ub9ac\ud558\uace0 \uc2f6\uc744\ub54c \uc0ac\uc6a9\ud55c\ub2e4<\/span>
   ** \ucee4\uc11c\uc758 \uc0ac\uc6a9\ubc29\ubc95\uc740 OPEN, FETCH, CLOSE, DEALLOCATE\ub4f1 4\ub2e8\uacc4\ub97c \uac70\uce5c\ub2e4<\/span>
   ** FETCH\uc5d0\ub294 NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n \/ @nvar}, RELATIVE {n \/ @nvar}\uac00 \uc788\ub2e4<\/span>

   SET NOCOUNT ON      \/* SQL\ubb38\uc758 \uc601\ud5a5\uc744 \ubc1b\uc740 \ud589\uc218\ub97c \ub098\ud0c0\ub0b4\ub294 \uba54\uc2dc\uc9c0\ub97c \uc228\uae34\ub2e4 *\/<\/span>
   DECLARE cStatus SCROLL CURSOR    \/* \uc55e\ub4a4\ub85c \uc6c0\uc9c1\uc774\ub294 \ucee4\uc11c \uc120\uc5b8 *\/<\/span>
   FOR<\/span>
       SELECT ID, Year, City FROM aPlane<\/span>
   FOR READ ONLY<\/span>
   OPEN cStatus      \/* \ucee4\uc11c\ub97c \uc5f0\ub2e4 *\/<\/span>
   DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)<\/span>
   FETCH FROM cStatus INTO @ID, @Year, @City \/* \ucee4\uc11c\uc5d0\uc11c \ub370\uc774\ud0c0\ub97c \ud558\ub098\uc529 \uac00\uc838\uc628\ub2e4 *\/<\/span>
   WHILE @@FETCH_STATUS=0                    \/* \ucee4\uc11c\uac00 \uac00\ub974\ud0a4\ub294 \uacb0\uacfc\uc758 \ub05d\uae4c\uc9c0 *\/<\/span>
   BEGIN<\/span>
       IF      @Year <= 5              SET @Status='A'<\/span>
       ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'<\/span>
       ELSE                            SET @Status='C'<\/span>
       INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)<\/span>
       FETCH FROM cStatus INTO @ID, @Year, @City \/* \ucee4\uc11c\uc5d0\uc11c \ub370\uc774\ud0c0\ub97c \ud558\ub098\uc529 \uac00\uc838\uc628\ub2e4 *\/<\/span>
   END<\/span>
   CLOSE cStaus                              \/* \ucee4\uc11c\ub97c \ub2eb\ub294\ub2e4 *\/<\/span>
   DEALLOCATE cStatus                        \/* \ucee4\uc11c\ub97c \ud574\uc81c\ud55c\ub2e4 *\/<\/span>



 \ubcf4\uc548\uacfc \uc0ac\uc6a9\uc790 \uad8c\ud55c<\/span>
  ** \ubcf4\uc548\uc758 \uc124\uc815 \ubc29\ubc95\uc740 \ud06c\uac8c WINDOWS \ubcf4\uc548\uacfc SQL \ubcf4\uc548\uc73c\ub85c \ub098\ub258\uc5b4 \uc9c4\ub2e4<\/span>
  ** \uc0ac\uc6a9\uc790\uc5d0\uac8c \uc5ed\ud560\uc744 \ubd80\uc5ec\ud558\ub294\ub370\ub294 \uc11c\ubc84\ub864\uacfc \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub864\uc774 \uc788\ub2e4<\/span>

  1. SA(System Administrator)<\/span>
   ** \uac00\uc7a5 \uc0c1\uc704\uc758 \uad8c\ud55c\uc73c\ub85c SQL \uc11c\ubc84\uc5d0 \uad00\ud55c \uc804\uccb4 \uad8c\ud55c\uc744 \uac00\uc9c0\uace0 \ubaa8\ub4e0 \uc624\ube0c\uc81d\ud2b8\ub97c \ub9cc\ub4e4\uac70\ub098<\/span>
      \uc218\uc815, \uc0ad\uc81c\ud560 \uc218 \uc788\ub2e4<\/span>

  2. DBO(Database Owner)<\/span>
   ** \ud574\ub2f9 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \uad00\ud55c \ubaa8\ub4e0 \uad8c\ud55c\uc744 \uac00\uc9c0\uba70 SA\ub85c \ub85c\uadf8\uc778\ud574\uc11c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0\uc11c \ud14c\uc774\ube14\uc744<\/span>
      \ub9cc\ub4e4\uc5b4\ub3c4 \uc0ac\uc6a9\uc790\ub294 DBO\ub85c \ub9e4\ud551\ub41c\ub2e4<\/span>
   ** \ud14c\uc774\ube14\uc774\ub984\uc758 \uad6c\uc870\ub294 \uc11c\ubc84\uc774\ub984.\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc774\ub984.DBO.\ud14c\uc774\ube14\uc774\ub984\uc774\ub2e4<\/span>

  3. DBOO(Database Object Owner)<\/span>
   ** \ud14c\uc774\ube14, \uc778\ub371\uc2a4, \ubdf0, \ud2b8\ub9ac\uac70, \ud568\uc218, \uc2a4\ud1a0\uc5b4\ub4dc \ud504\ub85c\uc2dc\uc800\ub4f1\uc758 \uc624\ube0c\uc81d\ud2b8\ub97c \ub9cc\ub4dc\ub294 \uad8c\ud55c\uc744 \uac00\uc9c0\uba70<\/span>
      SA\ub098 DBO\uac00 \uad8c\ud55c\uc744 \ubd80\uc5ec\ud55c\ub2e4<\/span>

  4. USER(\uc77c\ubc18 \uc0ac\uc6a9\uc790)<\/span>
   ** DBO\ub098 DBOO\uac00 \ud574\ub2f9 \uc624\ube0c\uc81d\ud2b8\uc5d0 \ub300\ud55c \uc0ac\uc6a9 \uad8c\ud55c\uc744 \ubd80\uc5ec\ud55c\ub2e4<\/span>





[SQL \uc11c\ubc84 2005 \uc2e4\uc804 \ud65c\uc6a9] \u2460 \ub354 \uac15\ub825\ud574\uc9c4 T-SQL  : http:\/\/blog.naver.com\/dbwpsl\/60041936511<\/span>
 MSSQL 2005 \ucd94\uac00 \ucffc\ub9ac<\/span>





-- ANY (OR \uae30\ub2a5)<\/span>

WHERE \ub098\uc774 >= (SELECT \ub098\uc774 FROM .......)<\/span>



-- GROUP BY ALL (WHERE \uc808\uacfc \uc77c\uce58 \ud558\uc9c0 \uc54a\ub294 \ub0b4\uc6a9\uc740 NULL \ub85c \ud45c\uc2dc)<\/span>

SELECT \uc8fc\uc18c, AVG(\ub098\uc774) AS \ub098\uc774 FROM MEMBER<\/span>

WHERE \uc131\ubcc4='\ub0a8'<\/span>

GROUP  BY ALL \uc8fc\uc18c<\/span>

-- \ubaa8\ub4e0 \uc8fc\uc18c\ub294 \ub098\uc624\uba70 \uc131\ubcc4\uc5d0 \ub530\ub77c \ub098\uc774 \ub370\uc774\ud130\ub294 NULL<\/span>



-- WITH ROLLUP<\/span>

SELECT \uc0dd\uc77c, \uc8fc\uc18c, SUM(\ub098\uc774) AS \ub098\uc774<\/span>

FROM MEMBER<\/span>

GROUP BY \uc0dd\uc77c, \uc8fc\uc18c WITH ROLLUP<\/span>

-- \uc0dd\uc77c \uacfc \uc8fc\uc18c\ub97c \uc694\uc57d\ud589\uc774 \ub9cc\ub4e4\uc5b4\uc9d0<\/span>



-- WITH CUBE (\uc704\uc758 \uc608\uc81c\ub97c \uae30\uc900\uc73c\ub85c, \uc8fc\uc18c\uc5d0 \ub300\ud55c \ubcc4\ub3c4 \uadf8\ub8f9 \uc694\uc57d\ub370\uc774\ud130\uac00 \ud558\ub2e8\uc5d0 \ubd99\uc5b4\ub098\uc634)<\/span>



-- GROUPING(\uceec\ub7fc\uba85) ROLLUP \ub610\ub294 CUBE \uc758 \uc694\uc57d\ud589\uc778\uc9c0 \uc5ec\ubd80 \ud310\ub2e8(\uc694\uc57d\ud589\uc774\uba74 1 \uc544\ub2c8\uba74 0)<\/span>

SELECT \uc0dd\uc77c, \uc8fc\uc18c, GROUPING(\uc0dd\uc77c) AS \uc0dd\uc77c\uc694\uc57d\ud589\uc5ec\ubd80<\/span>



-- COMPUTE (GROUP BY \uc640 \uc0c1\uad00\uc5c6\uc774 \ubcc4\ub3c4\uc758 \ud14c\uc774\ube14\ub85c \uc694\uc57d\uc815\ubcf4 \uc0dd\uc131)<\/span>

SELECT \uc0dd\uc77c, \ub098\uc774<\/span>

FROM MEMBER<\/span>

COMPUTE SUM(\ub098\uc774), AVG(\ub098\uc774)<\/span>



-- PIVOT (\uc138\ub85c \uceec\ub7fc\uc744 \uac00\ub85c \ubcc0\uacbd)<\/span>

EX)<\/span>

\ud559\ub144\/ \ubc18 \/ \ud559\uc0dd\uc218<\/span>

 1     1     40<\/span>

 1     2     45<\/span>

 2     1     30<\/span>

 2     2     40<\/span>

 3     1     10<\/span>

 3     2     10<\/span>

\uc704\uc640 \uac19\uc774 SCHOOL \ud14c\uc774\ube14\uc774 \uc788\ub2e4\uba74<\/span>



SELECT \uadf8\ub8f9\ud560\uceec\ub7fc\uba85, [\uc5f4\ub85c\ubcc0\ud658\uc2dc\ud0ac \ud589]<\/span>

FROM \ud14c\uc774\ube14<\/span>

   PIVOT(<\/span>

      SUM(\uac80\uc0c9\ud560\uc5f4)<\/span>

      FOR \uc606\uc73c\ub85c\ub9cc\ub4e4 \uceec\ub7fc\uba85<\/span>

         IN([\uc5f4\ub85c\ubcc0\ud658\uc2dc\ud0ac \ud589])<\/span>

         ) AS \ubcc4\uce6d<\/span>



--\uc2e4\uc81c \ucffc\ub9ac\ub294<\/span>

SELECT \ud559\ub144, [1\ubc18], [2\ubc18]<\/span>

FROM SCHOOL<\/span>

   PIVOT(<\/span>

      SUM(\ud559\uc0dd\uc218)<\/span>

      FOR \ubc18<\/span>

         IN([1\ubc18], [2\ubc18])<\/span>

         ) AS PVT<\/span>



-- UNPIVOT (\uac00\ub85c \uceec\ub7fc\uc744 \uc138\ub85c\ub85c)<\/span>



SELECT \ud559\ub144, \ubc18, \ud559\uc0dd\uc218<\/span>

FROM SCHOOL<\/span>

  UNPIVOT(<\/span>

    FOR \ubc18<\/span>

      IN( [1\ubc18], [2\ubc18] )<\/span>

    ) AS UNPVT<\/span>



-- RANK (\uc21c\uc704)<\/span>

SELECT \uceec\ub7fc\uba85, RANK() OVER( ORDER BY \uc21c\uc704 \uae30\uc900 \uceec\ub7fc\uba85) AS \uc21c\uc704<\/span>

FROM \ud14c\uc774\ube14<\/span>



-- PARTITION BY (\uadf8\ub8f9\ubcc4\ub85c \uc21c\uc704 \uc0dd\uc131)<\/span>

SELECT \uceec\ub7fc\uba85, RANK() OVER( PARTITION BY \uadf8\ub8f9\uae30\uc900\uceec\ub7ec\uba85 ORDER BY \uc21c\uc704\uae30\uc900\uceec\ub7fc\uba85) AS \uc21c\uc704<\/span>

FROM \ud14c\uc774\ube14<\/span>



-- FULL OUTER JOIN (LEFT \uc870\uc778\uacfc RIGHT \uc870\uc778\uc744 \ud569\ud55c\uac83)<\/span>

\uc591\ucabd \uc5b4\ub290 \ud558\ub098\ub77c\ub3c4 \ub370\uc774\uac00 \uc788\uc73c\uba74 \ub098\uc634<\/span>





-- ROW_NUMBER (\uc21c\ucc28\ubc88\ud638 \uc0dd\uc131)<\/span>

SELECT ROW_NUMBER() OVER( ORDER BY \uae30\uc900\uc5f4) AS \ubc88\ud638, \uceec\ub7fc\uba85<\/span>

FROM \ud14c\uc774\ube14<\/span>



\uc790\ub8cc\ud615 (\ub370\uc774\ud130\ud0c0\uc785)<\/span>

MSSQL \uc11c\ubc84\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 \ub370\uc774\ud130 \ud0c0\uc785(\uc790\ub8cc\ud615)\uc740 \ub450\uac00\uc9c0\uac00 \uc788\ub2e4.<\/span>



1. \uc2dc\uc2a4\ud15c\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 System data type<\/span>









\ub0b4\uac00 \uc0dd\uac01\ud574\ub3c4 \ucc38 \uae54\ub054\ud558\uac8c \uc815\ub9ac\ub97c \uc798 \ud574\ub1a8\ub2e4. -_-;;<\/span>

\uc131\ub2a5\ud5a5\uc0c1\uc744 \uc704\ud574\uc11c\ub77c\uba74 \uac00\ub2a5\ud55c \uc791\uc740 \uc790\ub8cc\ud615\uc744 \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\uc790.<\/span>

\ubd88\ud544\uc694\ud558\uac8c int\ub97c \uc4f0\ub294 \uacbd\uc6b0\uac00 \ud754\ud55c\ub370, \uc0ac\uc6a9\ub420 \ub370\uc774\ud130\uc758 \ubc94\uc704\ub97c \uc0dd\uac01\ud574 \ubcf8 \ud6c4, \uac00\uc7a5 \uc791\uc740 \ubc94\uc704\uc758 \uc790\ub8cc\ud615\uc744 \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\uc790.<\/span>





2. \uc0ac\uc6a9\uc790\uac00 \uc815\uc758 \ud558\ub294  User data type<\/span>



\uc0ac\uc6a9\uc790 \uc815\uc758 \uc790\ub8cc\ud615\uc774 \uc65c \ud544\uc694\ud55c\uac00?<\/span>

C\uc5b8\uc5b4\ub97c \ube44\ub85c\ud55c \uba87 \uac00\uc9c0 \uc5b8\uc5b4\uc5d0\uc11c \ub098\ud0c0\ub098\ub294 \uc0ac\uc6a9\uc790 \uc815\uc758 \ub370\uc774\ud130 \uc720\ud615\uacfc \uac19\ub2e4.<\/span>

\ud504\ub85c\uc81d\ud2b8\uc5d0 \ucc38\uac00\ud558\ub294 \uc0ac\ub78c\ub4e4\uc774 \ub3d9\uc77c\ud55c \ub370\uc774\ud130 \ud0c0\uc785\uc744 \uc0ac\uc6a9\ud558\uace0\uc790 \uc6d0\ud558\uac70\ub098,<\/span>

\ud55c \uceec\ub7fc\uc5d0 \ub300\ud55c \ub370\uc774\ud130 \uc720\ud615\uc744 \ub354 \uc27d\uac8c \uc0ac\uc6a9\ud558\ub824\uace0 \ud560 \ub54c \uc801\uc6a9\uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/span>



\uc0ac\uc6a9 \ubc29\ubc95<\/span>

sp_addtype  [\uc0c8\ub85c\uc6b4 \ud0c0\uc785 \uc774\ub984], '[SQL \ub370\uc774\ud130 \ud0c0\uc785]'<\/span>



\uc608<\/span>

sp_addtype empID, 'CHAR(10)'<\/span>

sp_addtype empNO, 'CHAR(12)'<\/span>







* \ucc38\uace0\ub85c \uc790\ub8cc\ud615\uc744 \ubc14\uafb8\ub294 \ud568\uc218\ub85c\ub294 CONVERT() \uac00 \uc788\ub2e4.<\/span>



\uc0ac\uc6a9\ubc29\ubc95<\/span>

SELECT CONVERT(CHAR(30), title) FROM BOOKS<\/span>

--> title \ub77c\ub294 \uceec\ub7fc\uc744 CHAR(30) \uc73c\ub85c \ubcc0\ud658\ud558\uc5ec \uac00\uc838\uc624\ub294 \uac83\uc774\ub2e4.<\/span>



SELECT CONVERT(VARCHAR(10), 35)<\/span>

--> 35 \ub77c\ub294 \uc22b\uc790\ub97c VARCHAR(10) \uc73c\ub85c \ubcc0\ud658\ud55c\ub2e4.<\/span>





\ud750\ub984 \uc81c\uc5b4\ubb38\uc758 \uc885\ub958<\/span>



\ud750\ub984 \uc81c\uc5b4\ubb38\uc774\ub780 \uc5b8\uc5b4\uc758 \ucc98\ub9ac \uc21c\uc11c\ub97c \ubcc0\uacbd\ud558\uac70\ub098 \ubcc0\uc218\ub97c \uc120\uc5b8\ud558\ub294 \ub4f1\uc758 \ubb38\uc7a5\uc744 \ub9d0\ud55c\ub2e4.<\/span>



\u25cb GOTO \ub77c\ubca8<\/span>

  - GOTO \ub97c \ub9cc\ub098\uba74 \ub77c\ubca8 \ubd80\ubd84\uc73c\ub85c \ubb34\uc870\uac74 \uac74\ub108\ub6f4\ub2e4. \ub77c\ubca8\uc740 \ub77c\ubca8: \uc73c\ub85c \uc815\uc758\ud55c\ub2e4.<\/span>

\uc608)<\/span>

DECLARE...<\/span>

SET...<\/span>

table_label1:<\/span>

.<\/span>

.<\/span>

IF .... GOTO table_label1<\/span>

.<\/span>

--> GOTO table_label1 \uc744 \ub9cc\ub098\uba74 table_label1: \ubd80\ubd84\uc73c\ub85c \uac74\ub108 \ub6f4\ub2e4.<\/span>



\u25cb RETURN<\/span>

  - RETURN \uc740 \ubb34\uc870\uac74 \uc218\ud589\uc744 \uc911\uc9c0 \ud558\uace0 \uc6d0\ub798 \ud638\ucd9c\ub41c \uacf3\uc73c\ub85c \ub3cc\uc544\uac04\ub2e4.<\/span>



\u25cb IF \/ ELSE<\/span>

  - \uc774\ub984\ub9cc \ub4e4\uc5b4\ub3c4 \uc54c\ub9cc\ud55c \ubb38\ubc95\uc774\ub2e4. \uc8fc\uc758 \ud560 \uc810\uc740  \uc870\uac74\ubb38 \uc548\uc758 SQL\ubb38\uc7a5\uc774 \ub458 \uc774\uc0c1\uc774\ub77c\uba74 BEGIN \/ END \ub85c \ubb36\uc5b4 \uc900\ub2e4.<\/span>

\uc608)<\/span>

IF @begin > @end<\/span>

    BEGIN<\/span>

        SELECT * FROM \ud14c\uc774\ube141 WHERE \uc870\uac74<\/span>

        RETURN<\/span>

    END<\/span>

ELSE<\/span>

    SELECT * FROM.........<\/span>



\u25cb WHILE \/ BREAK \/ CONTINUE<\/span>

  - WHILE \ub2e4\uc74c\uc5d0 \uc870\uac74\uc73c\ub85c \ubc18\ubcf5\uc744 \ud558\uac8c \ub418\uace0,<\/span>

    BREAK  \ub97c \ub9cc\ub098\uba74 \ubb34\uc870\uac74 WHILE \uc744 \ubc97\uc5b4\ub098\uace0,<\/span>

    CONTINUE \ub97c \ub9cc\ub098\uba74 \ubb34\uc870\uac74 WHILE \ub85c \ub3cc\uc544\uac04\ub2e4.<\/span>

\uc608)<\/span>

WHILE  \uc870\uac74<\/span>

BEGIN<\/span>

         \ubc18\ubcf5\ud558\ub294 \ub3d9\uc548 \uc2e4\ud589\ud560 \ubb38\uc7a5\ub4e4...<\/span>

         IF \uc870\uac74<\/span>

               BREAK<\/span>

         IF \uc870\uac74<\/span>

               CONTINUE<\/span>

END<\/span>



\u25cb EXEC[UTE]<\/span>

  - EXEC \uc640 EXECUTE \ub294 \uac19\uc740 \uc758\ubbf8\uc774\ub2e4.<\/span>

  - \ub450\uac00\uc9c0 \uc6a9\ub3c4\ub85c \uc0ac\uc6a9\ub418\ub294\ub370,<\/span>

  - \uccab \ubc88\uc9f8, \uc2a4\ud1a0\uc5b4\ub4dc \ud504\ub85c\uc2dc\uc800\ub97c \uc2e4\ud589\ud560 \ub54c \uc0ac\uc6a9\ud55c\ub2e4.<\/span>

\uc608)<\/span>

EXEC stored_procedure<\/span>



  - \ub450 \ubc88\uc7ac, SQL \ubb38\uc7a5\uc744 \ub3d9\uc801\uc73c\ub85c \ubcc0\ud654\uc2dc\ud0a4\uba70 \uc218\ud589\ud560 \uc218 \uc788\ub2e4.<\/span>

\uc608)<\/span>

DECLARE  @sql  VARCHAR(255)<\/span>

SET  @sql  = 'SELECT COUNT(*) FROM '<\/span>

SET  @sql  =  @sql + 'titles '<\/span>

EXEC(@sql)<\/span>

--> \uc2e4\uc81c \uc218\ud589\ub418\ub294 \ubb38\uc7a5\uc740 SELECT COUNT(*) FROM titles \uac00 \ub41c\ub2e4.<\/span>



\u25cb CASE<\/span>

  - \ub2e8\uc21c CASE<\/span>

\uc608)<\/span>

SELECT<\/span>

    CASE type<\/span>

        WHEN 'a'  THEN  'Apple'<\/span>

        WHEN 'b'  THEN  'Banana'<\/span>

        ELSE  'No Data'<\/span>

    END AS  \uacfc\uc77c<\/span>

, price<\/span>

FROM titles<\/span>



  - \uac80\uc0c9\ub41c CASE<\/span>

\uc608)<\/span>

SELECT  title_id<\/span>

, qty AS '\uc218\ub7c9'<\/span>

, CASE<\/span>

       WHEN  qty >= 50  THEN  'A'<\/span>

       WHEN  qty >= 30  THEN  'B'<\/span>

       ELSE  'C'<\/span>

  END  AS  '\ub4f1\uae09'<\/span>

FROM  titles<\/span>



 NULLIF : \ud45c\ud604\uc2dd 1\uacfc, 2\ub97c \ube44\uad50<\/span>

>> \ud45c\ud604\uc2dd 1\uacfc, 2\ub97c \ube44\uad50 \ub450 \ud45c\ud604\uc2dd\uc774 \uac19\uc73c\uba74 NULL \uc744 \ub9ac\ud134,  \uac19\uc9c0 \uc54a\uc73c\uba74 \ud45c\ud604\uc2dd 1\uc744 \ub9ac\ud134<\/span>
SELECT NULLIF(2,3) -- 2 \ub9ac\ud134<\/span>
SELECT NULLIF(3,3) -- NULL \ub9ac\ud134<\/span>
 \uc0ac\uc6a9\uc608 : \uc591\ucabd\ud544\ub4dc\uc5d0\uc11c \uc218\ub7c9\uc774 \uac19\uc73c\uba74 NULL\uc744 \ub9ac\ud134\ud558\uace0 \ud558\ub2c8\uba74 \uccab \ud544\ub4dc\uc758 \uac12\uc744 \ub9ac\ud134\ud560\ub54c<\/span>





 COALESCE : \ub4a4\uc5d0 \uc624\ub294 \ud45c\ud604\uc2dd\uc911\uc5d0 \ucc98\uc74c\uc73c\ub85c \uc624\ub294 NULL \uc774 \uc544\ub2cc \uac12\uc744 \ub9ac\ud134<\/span>



SELECT COALESCE(NULL, 3, 4) -- 3 \ub9ac\ud134<\/span>
SELECT COALESCE(1,NULL,) -- 1 \ub9ac\ud134<\/span>
SELECT COALESCE(NULL,NULL,4) -- 4 \ub9ac\ud134<\/span>

SELECT COALESCE(NULL,NULL, NULL)--\ubb38\ubc95\uc624\ub958<\/span>

 \uc0ac\uc6a9\uc608 : \ud558\ub098\ub9cc \uac12\uc744 \uac00\uc9c0\uace0 \uc788\ub294 \uceec\ub7fc\uc5d0\uc11c \ube44\uad50\ud574\uc11c \uac12\uc744 \uac00\uc838\uc62c\ub54c \ub9e4\uc6b0 \uc88b\ub2e4<\/span>





SET : \uc138\uc131 \uc635\uc158 (\ud55c\ubc88\uc124\uc815\ud558\uba74 \uc138\uc158\uc774 \ub04a\uc5b4 \uc9c8\ub54c\uae4c\uc9c0 \uc720\uc6a9)<\/span>

=====================================================================================<\/span>

SET nocount OFF<\/span>
  : \uba87\uac1c \ud589\uc774 \ucc98\ub9ac \ub418\uc5c8\ub294\uc9c0 \uacb0\uacfc\ubcf4\uc5ec\uc8fc\ub294 \uac83\uc744 \uc124\uc815\ud55c\ub2e4 '<\/span>

SET rowcount [n]<\/span>

ex) SET rowcount 4<\/span>
SELECT title_id FROM titles ORDER BY TITLE<\/span>

SET rowcount 0<\/span>

  : \ubcf4\uc5ec\uc904 \ubaa9\ub85d\uc758 \ud589\uc218\ub97c \uc120\ud0dd\ud55c\ub2e4. \ubaa9\ub85d\uc758 \uc815\ub82c\uc758 \uc784\uc758\ub85c \uc124\uc815\ub418\ubbc0\ub85c \ud544\uc694\ud55c \uc21c\uc11c\uac00 \uc788\ub2e4\uba74 ORDER BY \ub97c \uc0ac\uc6a9\ud574\uc57c \ud55c\ub2e4.<\/span>
\uc0ac\uc6a9\ud6c4\uc5d4 \ubc18\ub4dc\uc2dc     SET ROWCOUNT 0 \uc744 \uc774\uc6a9\ud574\uc11c \uc6d0\uc704\uce58 \uc2dc\ucf1c\ub193\uc544\uc57c \ud55c\ub2e4 '<\/span>





============================== \uc720\ub2c8\ud06c \ud0a4 \ub123\uae30  ==============================<\/span>
ALTER TABLE \ud14c\uc774\ube14\uba85 ADD UNIQUE(\uceec\ub7fc1, \uceec\ub7fc2)<\/span>
ALTER TABLE \ud14c\uc774\ube14\uba85 DROP CONSTRAINT \uc720\ub2c8\ud06c\uba85<\/span>



============================== IDENTITY \uad00\ub828 ==============================<\/span>

http:\/\/kuaaan.tistory.com\/42<\/span>

http:\/\/l2j.co.kr\/1460<\/span>

http:\/\/mcdasa.cafe24.com\/wordpress\/mssql-identity-scope_identity%ec%9d%98-%ec%b0%a8%ec%9d%b4%ec%a0%90\/<\/span>



============================== INSERT SELECT ==============================<\/span>

http:\/\/blog.naver.com\/sorkanj2000\/50106968790<\/span>



============================== UPDATE SELECT ==============================<\/span>

http:\/\/applejara.tistory.com\/302<\/span>



============================== JOIN UPDATE ==============================<\/span>

http:\/\/blog.naver.com\/ballkiss\/30096524074<\/span><\/p>


<\/p>

\ucd9c\ucc98 : http:\/\/zewtion.tistory.com\/259
<\/p>\n","protected":false},"excerpt":{"rendered":"

 ** SQL\ubb38\uc740 \ub300\uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud558\uc9c0 \uc54a\uc9c0\ub9cc \ub370\uc774\ud0c0\ub294 \ub300\ubb38\uc790\uc640 \uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud55c\ub2e4    \uc8fc\uc11d\uc744 \ub2e4\ub294 \ubc29\ubc95\uc740 \/* \uc8fc\uc11d *\/ \uc774\uac70\ub098 \ud55c\uc904\ub9cc \uc8fc\uc11d \ucc98\ub9ac\ub97c \ud560 \uacbd\uc6b0\ub294 \ubb38\uc7a5 \ub9e8\uc55e\uc5d0 –\ub97c \ubd99\uc778\ub2e4 ** \uac01\uac01\uc758 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 SYSOBJECTS \ud14c\uc774\ube14\uc5d0 \ud574\ub2f9 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ubaa8\ub4e0 \uc815\ubcf4\uac00 \ubcf4\uad00\ub418\uc5b4 \uc788\ub2e4    SYSOBJECTS\uc758 TYPE \uce7c\ub7fc\uc73c\ub85c ‘U’=\uc0ac\uc6a9\uc790 \ud14c\uc774\ube14, ‘P’=\uc800\uc7a5 \ud504\ub85c\uc2dc\uc800, ‘K’=\ud504\ub77c\uc774\uba38\ub9ac \ud0a4, ‘F’=\ud3ec\ub9b0 \ud0a4,    ‘V’=\ubdf0, ‘C’=\uccb4\ud06c \uc81c\uc57d\ub4f1 \uc624\ube0c\uc81d\ud2b8 \uc774\ub984\uacfc \uc815\ubcf4\ub97c \uc54c \uc218 \uc788\ub2e4 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-449","post","type-post","status-publish","format-standard","hentry","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/449","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=449"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/449\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}