WHEREÀýÀÇ Á¶°Ç¿¡ ÀÇÇÑ ÇàÀÇ °Ë»ö
WHEREÀýÀÇ Á¶°Ç¿¡ ÀÇÇÑ ÇàÀÇ °Ë»ö
ÀÚ À̹ø½Ã°£Àº WHEREÀýÀÇ Á¶°Ç¿¡ ÀÇÇÑ ÇàÀÇ °Ë»öÀ» ÇÒ ½Ã°£ ÀÔ´Ï´Ù. ¾Õ¿¡¼ whereÀýÀº Á¶°Ç¿¡ »ç¿ëÇÑ´Ù°í ¸Àº¸±â¸¸ »ì¦ º¸¿© µå·ÈÁö¿ä? ¸¹Àº 󸮰¡ ¾î¶»°Ô ÀÌ·ç¾îÁö´ÂÁö °øºÎÇÒ ½Ã°£ ÀÔ´Ï´Ù. ÀÌ·±°ÍÀ» »ý°¢ÇØ º¼±î¿ä?
sales Å×ÀÌºí¿¡¼ µ¥ÀÌÅ͸¦ ¸ðµÎ °Ë»öÇÑ °ªÀÌ ÀÌ·± ½ÄÀ̶ó°í »ý°¢À» ÇØ º¸¼¼¿ä. ±×·±´ë ÄÚ³ÀÌÀÇ Á÷¼Ó »ó°üÀÌ ¹è°¡Â¯ ÆÀÀå´ÔÀÌ ¿ä±¸¸¦ ÇÏ´Â °Ì´Ï´Ù. "À½. ÄÚ³±º?! ¿ì¸® PUBS åȸ»ç´Â qty Áï Æȸ°¼ö·®ÀÌ 10±Ç ÀÌ»óÀΠ常 ÇÊ¿ä ÇÏ´Ù³× ¿À´ÃÁßÀ¸·Î ÀÌ 10±Ç ÀÌ»ó Æȸ°Ã¥¸¸ °ñ¶ó³»°Ô!!!! ¸øÇϸé Áý¿¡ ¸ø°¡³×!!!
DB¸¦ °øºÎÇÑÁö º¸¸§µµ ¾È됬´Âµð ÀÌ·± ³¯º¶ôÀÌ ¶³¾î Áø°Ì´Ï´Ù. ±×·¡¼ °øºÎ¸¦ ÇϱâÀ§ÇØ Ã¥À» ã´øÁß... ¹Ù·Î WHERE À̶ó´Â Á¶°ÇÀýÀÇ ÁöÁ¤ÀÌ °¡´ÉÇÑ °ÍÀ» ¾Ë°Ô µÇ¾ú½À´Ï´Ù
WHERE Àý¿¡ Ž»ö Á¶°ÇÀ» ±Ù°ÅÇÏ¿© ¾î¶² ÇàÀ» °Ë»öÇÒ Áö¸¦ ÁöÁ¤ÇÑ´Ù. SELECT select_list FROM table_list WHERE search_conditions
ÁÖÀÇ»çÇ× - WHERE Àý¿¡´Â °¡´ÉÇϸé NOTÀº »ç¿ëÇÏÁö ¾Ê´Â °ÍÀÌ ÁÁ´Ù. - NOTÀ» »ç¿ëÇÏ¸é »öÀÎÀÇ µµ¿òÀ» ¹ÞÀ» ¼ö ¾ø´Ù. - ¿¬»êÀÚ ¾Õ¿¡´Â °¡´ÉÇϸé Ä÷³ À̸§ÀÌ ¿Àµµ·Ï ÇÑ´Ù
Ž»ö Á¶°Ç¿¡ Æ÷ÇÔÇÒ ¼ö ÀÖ´Â °Íµé - ºñ±³ ¿¬»êÀÚ =. >, <, >=, <=, <>, !=, !, !> - ¹üÀ§ BETWEEN, NOT BETWEEN - ¸®½ºÆ® IN, NOT IN - ÀÏÄ¡ÇÏ´Â ¹®ÀÚ¿ LIKE, NOT LIKE - ¾Ë ¼ö ¾ø´Â °ª IS NULL, IS NOT NULL - °áÇÕ AND, OR - ºÎÁ¤ NOT
ÀÇ ½ÄÀÌ µÈ´ä´Ï´Ù. ÀÌÁ¦ ÀÚ Æȸ°¼ö°¡ 10±Ç ÀÌ»óÀΠ常 º¸¿©µå¸± ½Ã°£ÀÌÁö¿ä?
select * from sales where qty > 10 |
¶Ç ´Ù¸¥ »ùÇà ÀÔ´Ï´Ù. authors Å×ÀÌºí¿¡¼ zip Ä÷³ÀÌ 9000 º¸´Ù Å« ³à¼®À» °ñ¶ó º¼±î¿ä?
SELECT * FROM authors WHERE zip > 90000 |
authors Å×ÀÌºí¿¡¼ state°¡ CAÀÎ ³à¼®µé¸¸ °ñ¶ó³» º¸Áö¿ä
SELECT * FROM authors WHERE state = 'CA' |
ÀÚ À§ÀÇ ºñ±³¿Í ¹º°¡ ´Ù¸¥°Ô °Ë»ö¾î¿¡ ºÙ¾î ÀÖÁö¿ä? ¹®ÀÚ¿ÀÇ ºñ±³´Â ¹Ýµå½Ã ' ' ·Î ÀÛÀº ¶¡Ç¥¸¦ ¾²¼Å¾ß ÇÕ´Ï´Ù. ¿Ö³Ä±¸¿ä? 1235 ¶ó´Â °ª°ú '1235' ¶ó´Â °ªÀ» ºñ±³Çϱâ À§ÇÔÀ̶ø´Ï´Ù. Àú°Ô ¹¹³Ä±¸¿ä? ¾ÕÀÇ °ÍÀº ¼öÄ¡Çü µ¥ÀÌÅÍ, µÚÀÇ°ÍÀº ¹®ÀÚ¿ µ¥ÀÌÅÍ ÀÌÁö¿ä!
´ÙÀ½Àº titles Å×ÀÌºí¿¡¼ price°¡ NULLÀÎ ³à¼®À» °ñ¶ó³» º¸Áö¿ä
SELECT title FROM titles WHERE price IS NULL |
¿©±â¼ NULL À̶õ ³à¼®¿¡ ´ëÇØ Á»´õ ¾Ë¾Æ º¼±î¿ä? ¸¹Àº ºÐµéÀÌ NULLÀÌ ¹ºÁö Àß °³³äÀ» ¸ø ÀâÀ¸½Ã°í NULL = ºó ¹®ÀÚ¿ ½ÄÀ¸·Î »ý°¢ÇÏ°í °è½Ê´Ï´Ù
¾Æ´Õ´Ï´Ù. NULLÀº °ªÀÌ ÁöÁ¤µÇÁö ¾Ê¾Ò´Ù!!! ¶ó´Â ÀÇ¹Ì ÀÔ´Ï´Ù. ' ' (ºó¹®ÀÚ¿) ÀÌ ³à¼®Àº? ºó ¹®ÀÚ¿ÀÌ ÁöÁ¤µÇ¾î ÀÖ´Ù´Â ÀǹÌÀ̸ç. NULLÀº? °ªÀÌ ¾î¶² °ªµµ ÁöÁ¤µÇÁö ¾Ê¾Ò´Ù´Â ÀÇ¹Ì ÀÔ´Ï´Ù. ÃßÈÄ NULL¿¡ ´ëÇÑ À̾߱Ⱑ ³ª¿Íµµ Çرò¸®Áö ¸¶½Ã±æ ¹Ù¶ø´Ï´Ù
´ÙÀ½Àº titlesÅ×ÀÌºí¿¡¼ ytd_sales°¡ 4095 º¸´Ù Å©°í 12000 º¸´Ù ÀÛÀº ³à¼®µé¸¸ »Ì¾Æ¼ ºÁ º¼±î¿ä?
SELECT title_id, ytd_sales FROM titles WHERE ytd_sales > 4095 AND ytd_sales < 12000
|
ÀÚ ºñ½ÁÇÑ Ã³¸®°¡ between À̶ó´Â ¹üÀ§ °Ë»öÀÌ ÀÖ½À´Ï´Ù
SELECT title_id, ytd_sales FROM titles WHERE ytd_sales between 4095 AND 12000
|
°ÅÀÇ ºñ½ÁÇѵí ÇÏÁö¿ä? ÇÏÁö¸¸ ¹Ù·Î À§ÀÇ AND °Ë»ö°ú °á°ú°¡ Ʋ¸³´Ï´Ù? ½ÇÇàÇØ º¸½Ã¸é ¾Æ½Ã°ÚÁö¿ä. ¹Ù·Î ÀÌ»ó ÃÊ°ú ¶ó´Â ÃøÁ¤°ª¿¡ ´ëÇÑ ºÎºÐÀÌ ´Ù¸¨´Ï´Ù.
´ÙÀ½Àº titlesÅ×ÀÌºí¿¡¼ typeÀÌ mod_cookÀ̰ųª trad_cook ÀÎ ³à¼®µé¸¸ °ñ¶ó º¸Á®
SELECT title, type FROM titles WHERE type IN ('mod_cook', 'trad_cook')
|
À§ÀÇ »ùÇðú ¾à°£¸¸ ´Þ¸® NOTÀ» »ç¿ëÇÏ´Â ¹æ½Ä ÀÔ´Ï´Ù. °á°ú´Â ÇÊÀÌ ¿À½ÃÁÒ?
SELECT title, type FROM titles WHERE type IN ('mod_cook', 'trad_cook')
|
typeÀÌ mod_cookÀ̰ųª trad_cook ÀÎ ³à¼®µéÀ» °ñ¶ó³» º¸ÁÒ. À§ÀÇ INÀ» »ç¿ëÇÑ ¹æ½Ä°ú´Â ¶Ç´Ù¸¥ ORÀ» »ç¿ëÇÑ ¹æ½ÄÀÔ´Ï´Ù
SELECT title, type FROM titles WHERE type = 'mod_cook' OR type = 'trad_cook'
|
ÀÚ ¸î¸î »ùÇÃÀ» ¾Ë¾Æ º¸¼Ì´Âµ¥¿ä. Áß¿äÇÑ À̾߱⸦ µå¸®Áö¿ä. À§¿¡¼ ¹®ÀÚ¿À» ºñ±³½Ã ¿¹¸¦µé¸é WHERE type = 'mod_cook' OR type = 'trad_cook' ÀÌ·± ½ÄÀÏ °æ¿ì¿¡¿ä. type À̶õ Ä÷³Àº ¹®ÀÚ¿ Ä÷³ ÀÔ´Ï´Ù. ±×³É = ¿¬»êÀÚ¸¦ »ç¿ëÇØ ºñ±³¸¦ Çß½À´Ï´Ù. ±ÛÄ¡¿ä? ÇÏÁö¸¸ SQL¼¹ö´Â ´Ù¾çÇÑ ¹®ÀÚ¿ Á¶°Ç 󸮸¦ À§ÇØ like »ç¿ëÀ» ±ÇÀåÇÏ°í ÀÖ½À´Ï´Ù
LIKE Å°¿öµå SELECT select_list
FROM table_list
WHERE expression [NOT] LIKE ¡°string¡±
- Á¤±Ô Ç¥Çö½Ä(regular expression)Çü½ÄÀÇ ¿ÍÀϵåÄ«µå »ç¿ë
¿ÍÀϵå Ä«µå(Wildcard)
% : ÀÓÀÇÀÇ 0°³ ÀÌ»óÀÇ ¹®ÀÚ¿
_ : ÀÓÀÇÀÇ ÇÑ ±ÛÀÚ
[ ] : ÁöÁ¤µÈ ¹üÀ§ ¶Ç´Â ÁýÇÕ ¾ÈÀÇ ÇÑ ¹®ÀÚ
[^] : ÁöÁ¤µÈ ¹üÀ§ ¶Ç´Â ÁýÇÕ¿¡ ¾ø´Â ÇÑ ¹®ÀÚ
ÀÌ·± ½ÄÀÌÁö¿ä.
¸ÕÀú »ùÇ÷Πstores Å×ÀÌºí¿¡¼ stor_nameÀÌ ¾Õ ±ÛÀÚ°¡ ¹»·Î ½ÃÀÛÇÏ°Ç »ó°ü ¾ø°í ³¡±ÛÀÚ°¡ ¹»·Î ½ÃÀÛÇÏ°Ç »ó°ü ¾ø°í ¹®ÀÚ¿»ó¿¡ Books¶ó´Â ¹®ÀÚ¿¸¸ Á¸ÀçÇÏ´ÂÁö ¾Ë°í ½ÍÀ»¶§ »ç¿ëÇÏ´Â LIKE ±¸¹®À» ¾Ë¾Æº¸¸é?
SELECT stor_name FROM stores WHERE stor_name LIKE '%Books%'
|
´ÙÀ½À¸·Î stores Å×ÀÌºí¿¡¼ stor_nameÀÌ Ã³À½Àº Bo·Î ½ÃÀÛÇÏ°í ³¡Àº ¾î¶§µµ »ó°ü¾ø´Â »ùÇÃÀ» °Ë»öÇØ º¼±î¿ä?
SELECT stor_name FROM stores WHERE stor_name LIKE 'Bo%'
|
À̹ø¿£ ¾à°£ º¹ÀâÇѵí ÇÏÁö¸¸? ÇѱÛÀÚ Ä¡È¯ÀÎ _ (¾ð´õ¹Ù¶ó°í ºÎ¸£Áö¿ä) ¸¦ »ç¿ëÇØ Ã¹±ÛÀÚ´Â E·Î ½ÃÀÛÇÏ°í ÇѱÛÀÚ´Â ¸ð¸£¸ç ´ÙÀ½¿¡ iÀÚ°¡ ³ª¿À¸ç ¾Æ¿ï·¯ µÞ±ÛÀÚ´Â ¹¹À̾ »ó°ü¾ø´Â ¹®ÀÚ¿À» °Ë»ö ÇÑ´Ù¸é?
SELECT stor_name FROM stores WHERE stor_name LIKE 'E_i%'
|
´ÙÀ½À¸·Î ùÀÚ´Â B·Î ½ÃÀÛÇÏ¸ç ´ÙÀ½¿¡ aÀÚ°¡ ¾ø°í ¾Æ¿ï·¯ µÞ±ÛÀÚ°¡ ¹¹¶óµµ »ó°ü¾ø´Â ¹®ÀÚ¿À» °Ë»öÇÒ °æ¿ì´Â?
SELECT stor_name FROM stores WHERE stor_name LIKE 'B[^a]%'
|
ÀÇ ½ÄÀÌÁö¿ä.
¿©±â¼ ¤À¸¼Å¾ß ÇÒ °ÍÀº? °¡´ÉÇÏ½Ã¸é ¹®ÀÚ¿ÀÇ ºñ±³¿¡´Â like¸¦ »ç¿ëÇϼż ó¸® ÇϽøé ÁÁ´Ù´Â °Í ÀÔ´Ï´Ù. ¾Æ¿ï·¯ ¹®ÀÚ¿À» ºñ±³ÇÑ ¼öÄ¡ µ¥ÀÌÅÍÇü¿¡´Â = À» »ç¿ëÇϽðųª < ¶Ç´Â > ÀÇ ºñ±³ À̱¸¿ä
³¡À¸·Î º¹½À Â÷¿ø¿¡¼ ¾à°£´õ º¹ÀâÇÑ °á°úÀÇ ¼±Åà ÀÔ´Ï´Ù.
SELECT title_id, title, pub_id, price, pubdate FROM titles WHERE (title LIKE 'T%' OR pub_id = '0877') AND (price > $16.00)
SELECT title_id, title, pub_id, price, pubdate FROM titles WHERE (title LIKE 'T%') OR (pub_id = '0877' AND price > $16.00)
SELECT title_id, title, pub_id, price, pubdate FROM titles WHERE title LIKE 'T%' OR pub_id = '0877' AND (price > $16.00)
|
°á°úÀÇ ½ÇÇà¼ø¼¸¦ ¸ÕÀú Àß »ý°¢ÇØ º¸¼¼¿ä. ¾Æ¿ï·¯ ¹®ÀÚ¿¿¡ ´ëÇÑ °Ë»ö°ú ¼öÄ¡ÇüÀÇ È¥ÇÕ °Ë»ö Á¶°Ç¿ª½Ã ÀÔ´Ï´Ù. ÂùÂùÈ÷ ÆÇ´ÜÇØ º¸½Ã¸é? ´À³¦ÀÌ ¿À½Ç °Ì´Ï´Ù
À̹øÀº ¸¹ÀÌ »ç¿ëÇÏ´Â Á¶°ÇÀ» ÀÌ¿ëÇÑ µ¥ÀÌÅÍ °Ë»öÀ» º¸¼Ì½À´Ï´Ù. ¼ö°íÇϼ̽À´Ï´Ù
WHEREÀýÀÇ Á¶°Ç¿¡ ÀÇÇÑ ÇàÀÇ °Ë»ö ¹®¼ÀÇ ³¡ÀÔ´Ï´Ù
|