|











|
|
|
 |
0 |
|
 |
À̹ø ½Ã°£¿¡´Â SQL ¹® Áß¿¡¼ °¡Àå ¸¹ÀÌ »ç¿ëµÇ´Â ¸í·É¾îÀÎ SELECT ¸í·É¾îÀÇ »ç¿ë¹ý¿¡ ´ëÇØ ¾Ë¾Æº¸°Ú½À´Ï´Ù.
- Çü½Ä
SELECT ¸í·ÉÀº µ¥ÀÌÅͺ£À̽º¿¡¼ µ¥ÀÌÅ͸¦ °Ë»öÇÒ ¶§ »ç¿ëÇÏ´Â °ÍÀ¸·Î ¾Æ·¡¿Í °°Àº Çü½ÄÀ¸·Î »ç¿ëµË´Ï´Ù.

¿©±â¼ INTO¿Í °ü·ÃµÈ ½Ç½ÀÀº ÇÏÁö ¾Ê½À´Ï´Ù. INTO´Â °Ë»ö °á°ú¸¦ ƯÁ¤ º¯¼ö¿¡ ÀúÀåÇÏ´Â °ÍÀε¥ SQLÀ» ÀÌ¿ëÇÏ¿© ´Ù¸¥ ÇÁ·Î±×·¡¹ÖÀ» ÇÒ ¶§ »ç¿ëµË´Ï´Ù. µû¶ó¼ INTO¿Í °ü·ÃÇØ¼´Â ´Ù¸¥ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¼ ½Ç½ÀÇÏ¸é µÇ°í ¿©±â¼´Â ½Ç½ÀÀ» »ý·«ÇÕ´Ï´Ù.
- SQL ¸í·É¾î ÀÛ¼º¹ý
¾Õ °Á¿¡¼ ÀÌ¹Ì ÀϺδ ¼³¸íÀ» Çß¾ú´Âµ¥ ÀÚ¼¼È÷ »ìÆìº¸ÁÒ.
- SQL ¹®ÀåÀº ¿©·¯ ÁÙ¿¡ °ÉÃÄ ÀÔ·ÂÇÒ ¼ö ÀÖ½À´Ï´Ù.
- ±×¸®°í ÀÔ·ÂÇÒ ¶§´Â µÇµµ·Ï SQL ¸í·É¾î´Â Àб⠽±µµ·Ï µé¿©¾²±â¸¦ Çϰí
- SELECT, FROM, WHERE´Â °¢±â ´Ù¸¥ Line¿¡ Àû´Â °ÍÀÌ ÁÁ½À´Ï´Ù. ±×·¡¾ß ÇÑ´«¿¡ µé¾î¿À´Ï±î¿ä.
- SQL ¸í·É¾î´Â ´ë¼Ò¹®ÀÚ¸¦ ±¸ºÐÇÏÁö ¾Ê½À´Ï´Ù.
- SQL ¹®ÀåÀÇ ³¡Àº ¼¼¹ÌÄÝ·Ð(;)À¸·Î Á¾·áÇÕ´Ï´Ù.
- ½Ç½À¿ë Sample Å×À̺í ÀÛ¼º
¿ì¸®´Â ¾ÕÀ¸·ÎÀÇ ½Ç½ÀÀ» À§ÇØ ¸î °³ÀÇ Å×À̺íÀ» ¸¸µé¾î¾ß µÇ´Âµ¥ ¿ì¼± ù¹øÂ°·Î ´ÙÀ½°ú °°Àº EMP Å×À̺íÀ» ¸¸µé °ÍÀÔ´Ï´Ù.

±×·±µ¥ ¿À¶óŬÀ» ¼³Ä¡ÇÏ¸é ±âº»ÀûÀ¸·Î EMP Å×À̺íÀÌ ÀÖ½À´Ï´Ù. µû¶ó¼ ¾Æ·¡¿Í °°ÀÌ DROP ¸í·ÉÀ» Á༠±âÁ¸ÀÇ EMP Å×À̺íÀ» Áö¿ì°í, CREATE ¸í·ÉÀ¸·Î »õ·Ó°Ô EMP Å×À̺íÀ» ¸¸µé¾î, INSERT¸¦ »ç¿ëÇÏ¿© Å×ÀÌºí¿¡ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇÕ´Ï´Ù.
DROP, CREATE, INSERT µîÀÇ ¸í·É¿¡ °üÇØ¼´Â ÈÄ¿¡ SELECT¿Í °ü·ÃµÈ ¸ðµç ½Ç½ÀÀÌ ³¡³ ÈÄ¿¡ ´Ù·ê ¿¹Á¤À̹ǷΠ¾Æ·¡ÀÇ ¹®ÀåÀ» ±×³É ÀÔ·ÂÇϽñ⸸ ÇÏ¸é µË´Ï´Ù.
¸¸¾à ÀÔ·ÂÇϽñⰡ ¾î·Æ´Ù¸é ¾Æ·¡ÀÇ ¹®ÀåÀ» ±×´ë·Î º¹»çÇÏ¿© SQL*Plus¿¡ ºÙ¿©³Ö±â¸¦ ÇØµµ µË´Ï´Ù.
SQL*Plus¸¦ ½ÇÇàÇÏ¿© ´ÙÀ½ ³»¿ëÀ» Á÷Á¢ ÀÔ·ÂÇϰųª º¹»çÇÏ¿© ºÙ¿©³ÖÀ¸¼¼¿ä.
DROP TABLE EMP;
CREATE TABLE EMP (
EMP_NUM CHAR(5) PRIMARY KEY,
LAST_NAME VARCHAR2(30) NOT NULL,
FIRST_NAME VARCHAR2(30) NOT NULL,
JOB_CODE CHAR(2) NOT NULL,
ADDRESS VARCHAR2(100) );
INSERT INTO EMP VALUES ( '10001', 'GRANT', 'LINDA', 'PE', '34 of 1st Street');
INSERT INTO EMP VALUES ( '10002', 'AMY', 'JONATHAN', 'PS', '200 Rose Street');
INSERT INTO EMP VALUES ( '10003', 'HUROW', 'LILY', 'SS', '101 Bear Town');
INSERT INTO EMP VALUES ( '10004', 'ADAM', 'EVELY', 'CS', '202 Declaration Drive');
INSERT INTO EMP VALUES ( '10005', 'JULIE', 'ROSE', 'PE', '788 McTyne Street');
INSERT INTO EMP VALUES ( '10006', 'ALBERT', 'MAY', 'SS', '320 Elaine Ave');
INSERT INTO EMP VALUES ( '10007', 'AMY', 'KENT', 'SS', '829 Cerritos Ave');
À§¿Í °°Àº ¹æ¹ýÀ¸·Î ´ÙÀ½°ú °°Àº VIDEO Å×À̺íÀ» ¸¸µé¾î º¾½Ã´Ù.

DROP TABLE VIDEO;
CREATE TABLE VIDEO (
VIDEO_NO CHAR(3) PRIMARY KEY,
VIDEO_NAME VARCHAR2(30) NOT NULL,
PLAY_TIME NUMBER NOT NULL,
DESCRIPTION VARCHAR2(100) NOT NULL );
INSERT INTO VIDEO VALUES ('101', 'LAMBO', 150, 'ACTION IN WAR GAME');
INSERT INTO VIDEO VALUES ('201', 'TOW CAPS', 100, 'HUMOROUS STORIES OF CAPS');
INSERT INTO VIDEO VALUES ('260', 'THE LOVER', 150, 'LOVE STORIES IN ASIAN WAR');
INSERT INTO VIDEO VALUES ('340', 'STING', 100, 'THRILLER AND HOT MOVIE');
INSERT INTO VIDEO VALUES ('390', 'ROMEO AND JULIET', 200, 'SAD LOVE STORY OF TWO LOVERS');
INSERT INTO VIDEO VALUES ('401', 'SPEED', 200, 'SPEED ACTION WITH A TRAILOR BUS');
INSERT INTO VIDEO VALUES ('500', 'POWER OF LOVE', 100, 'POWER OF LOVE');
INSERT INTO VIDEO VALUES ('560', 'CINEMA PARADISE', 150, 'MOVIE OF LOVE');
- °£´ÜÇÑ SELECT Äõ¸® ½Ç½À
¾Æ·¡ÀÇ ±¸Ã¼ÀûÀÎ ½Ç½À ¿¹¸¦ ÅëÇØ SELECT~FROM~WHERE; ¹®ÀåÀÇ »ç¿ë¹ýÀ» ÀÍÈ÷½Ã±â ¹Ù¶ø´Ï´Ù.
SQL*Plus¸¦ ½ÇÇàÇÏ¿© ´ÙÀ½ ³»¿ëÀ» Á÷Á¢ ÀÔ·ÂÇØ º¸¼¼¿ä.
- EMP Å×ÀÌºí¿¡¼ LAST_NAMEÀÌ AMYÀÎ ¸ðµç Data¸¦ °Ë»öÇÏ·Á¸é
SELECT *
FROM EMP
WHERE LAST_NAME = 'AMY' ;
¿©±â¼ ÁÖÀÇÇÏ½Ç °ÍÀº ¹®ÀÚ¿À» Ç¥½ÃÇÒ ¶§ Å« µû¿ÈÇ¥(" ")°¡ ¾Æ´Ï¶ó ÀÛÀº µû¿ÈÇ¥(' ')·Î µÑ·¯½Î¾ß ÇÕ´Ï´Ù.
SQL¿¡¼´Â °Ë»öÀ» À§ÇÑ ½ÄÀ» ÀÔ·ÂÇÒ ¶§ ¼ýÀÚÇü µ¥ÀÌÅ͸¸ ±×´ë·Î Ç¥½ÃÇÏ°í ¹®ÀÚÇü µ¥ÀÌÅͳª ³¯Â¥Çü µ¥ÀÌÅÍ´Â ÀÛÀº µû¿ÈÇ¥·Î ¹¾î¾ß ÇÕ´Ï´Ù.
- EMP Å×ÀÌºí¿¡¼ JOB_CODE°¡ SSÀÎ DataÀÇ EMP_NUM, LAST_NAME, FIRST_NAME Ä®·³¸¸ Ç¥½ÃÇÏ·Á¸é
SELECT EMP_NUM,
LAST_NAME,
FIRST_NAME
FROM EMP
WHERE JOB_CODE = 'SS' ;
½°Ç¥ ¿©ºÎ¸¦ ÁÖÀÇÇØ¼ ÀÔ·ÂÇÏ¼Å¾ß µË´Ï´Ù. FIRST_NAME µÚ¿¡´Â ½°Ç¥°¡ ¾ø½À´Ï´Ù.
- WHERE Á¶°Ç Àý¿¡¼ ƯÁ¤ µ¥ÀÌÅ͸¦ ÁöÁ¤ÇÏ´Â °ÍÀÌ ¾Æ´Ï¶ó Ä÷³ À̸§À¸·Î Á¶°ÇÀ» ÁöÁ¤ÇÒ ¼öµµ ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î VIDEO Å×ÀÌºí¿¡¼ VIDEO_NAMEÀ̶ó´Â Ä÷³(Çʵå)°ú DESCRIPTIONÀ̶ó´Â Ä÷³ÀÇ ³»¿ëÀÌ ÀÏÄ¡ÇÏ´Â ÇàÀÇ VIDEO_NO¸¸ Ç¥½ÃÇ϶ó°í ÇÑ´Ù¸é,
SELECT VIDEO_NO
FROM VIDEO
WHERE VIDEO_NAME = DESCRIPTION;¿Í °°ÀÌ ¾²¸é µË´Ï´Ù.
- WHERE Á¶°Ç Àý¿¡¼ ºñ±³ ¿¬»êÀÚ¸¦ ÀÌ¿ëÇØ¼ Á¶°ÇÀ» ÀÔ·ÂÇØµµ µË´Ï´Ù. ¿¹¸¦ µé¾î VIDOE Å×ÀÌºí¿¡¼ PLAY_TIMEÀÌ 2½Ã°£ ÀÌ»óÀÎ ÇàÀÇ VIDEO_NO¸¦ Ç¥½ÃÇÏ·Á¸é,
SELECT VIDEO_NO
FROM VIDEO
WHERE (PLAY_TIME / 60) > 2;
- ºñ±³ ¿¬»êÀÚ
SQL¿¡¼ »ç¿ë °¡´ÉÇÑ ºñ±³ ¿¬»êÀÚ´Â ´ÙÀ½°ú °°½À´Ï´Ù.
°°´Ù(=), °°Áö ¾Ê´Ù(!= ¶Ç´Â <>), ÀÛ´Ù(<), Å©´Ù(>), À۰ųª °°´Ù(<=), Å©°Å³ª °°´Ù(>=)
ÁÖÀÇÇÒ °ÍÀº °°Áö ¾Ê´Ù¸¦ NOT= °ú °°ÀÌ Ç¥½ÃÇØ¼´Â ¾ÈµË´Ï´Ù.
- AND, OR operator
AND¿Í OR´Â »ó½ÄÀûÀÎ ¼±¿¡¼ ÀÌÇØ¸¦ ÇÏ½Ã¸é µË´Ï´Ù.
- AND´Â AND¸¦ Áß½ÉÀ¸·Î Á¿ìÀÇ Á¶°ÇÀÌ ¸ðµÎ ¸¸Á·ÇÒ °æ¿ì¿¡ ÂüÀÌ µË´Ï´Ù.
- OR´Â OR¸¦ Áß½ÉÀ¸·Î Á¿ìÀÇ Á¶°Ç Áß ¾î´À Çϳª¶óµµ ¸¸Á·ÇÒ °æ¿ì ÂüÀÌ µË´Ï´Ù.
- ÇϳªÀÇ SELECT ¹®¿¡¼ ¿©·¯ °³ÀÇ AND ¶Ç´Â OR¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
- Ä÷³ º°¸í(Column Alias)
Ä÷³ º°¸íÀº °Ë»ö °á°ú°¡ ȸ鿡 Ç¥½ÃµÉ ¶§ Ä÷³ Á¦¸ñÀ» ¿øÇÏ´Â ÇüÅ·ΠÃâ·ÂÇϰíÀÚ ÇÒ ¶§ »ç¿ëÇÕ´Ï´Ù.
SELECT VIDEO_NO (AS) "NUMBER" ,
VIDEO_NAME (AS) "Video Name"
FROM VIDEO
WHER PLAY_TIME > 150;
À§ÀÇ ¿¹¿¡¼ AS¸¦ °ýÈ£·Î Ç¥½ÃÇÑ °ÍÀº AS¸¦ »ý·«ÇÒ ¼ö ÀÖ´Ù´Â °ÍÀÔ´Ï´Ù. AS¸¦ ¾²°Å³ª ¾Æ´Ï¸é ÀÔ·ÂÇÏÁö ¸¶¼¼¿ä. (AS)¿Í °°Àº ½ÄÀ¸·Î ÀÔ·ÂÇÏ¸é ¾ÈµË´Ï´Ù.
- ¿¬°á ¿¬»êÀÚ
À§ÀÇ Column Alias¸¦ ÀÌÇØÇÏ¼Ì´Ù¸é ¼³¸íº¸´Ù´Â ¾Æ·¡ÀÇ ¹®ÀåÀ» Á÷Á¢ ½ÇÇàÇØ ºñ±³ÇØ º¸¼¼¿ä.
- SELECT EMP_NUM,
LAST_NAME || ' ' || FIRST_NAME "NAME"
FROM EMP
WHERE JOB_CODE = 'SS' ;
- SELECT EMP_NUM,
LAST_NAME, FIRST_NAME
FROM EMP
WHERE JOB_CODE = 'SS' ;
ù¹øÂ° ¿¹Á¦´Â LAST_NAME°ú FIRST_NAME »çÀ̸¦ ÇÑ Ä ¶ç°í ¿¬°áÇÏ¿© "NAME"À̶ó´Â »õ·Î¿î À̸§ÀÇ Ä÷³ Á¦¸ñÀ¸·Î Ãâ·ÂÇÏ´Â °ÍÀÔ´Ï´Ù. ´ÜÁö ¾Ë¾Æº¸±â ½±µµ·Ï ±×·¸°Ô Ç¥½ÃÇ϶ó´Â ¶æÀÔ´Ï´Ù. ½ÇÁ¦ Ä÷³ÀÇ Á¦¸ñÀÌ ¹Ù²ï °ÍÀº ¾Æ´Õ´Ï´Ù.
³»¿ëÀÌ Á» ¸¹¾Ò³×¿ä.
ÀÌ»ó ¸ñ¿äÀÏ »õº®, µ¿Á־ƺü ¼Õº´¸ñÀ̾ú½À´Ï´Ù. µ¿ÁÖ ÀÚ´Â ¸ð½ÀÀÌ Á¤¸» ¿¹»Þ´Ï´Ù.
|
 |
 |
|
|
|