6๏ธโฃ SQL11 [MySQL] ์ฝ๋ฉํ ์คํธ๋ฅผ ์ํ SQL ๋ฌธ๋ฒ DISTINCT ์ค๋ณต ์ ๊ฑฐ SELECT DISTINCT NAME LIMIT ๊ฒฐ๊ณผ ๊ฐ์ ์ ํ (์์ ๋ช ๊ฐ๋ง ์ถ๋ ฅ) # ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํ์ฌ ๊ฐ์ฅ ์์ ๊ฒ๋ถํฐ 3๊ฐ ์ถ๋ ฅ ORDER BY ID LIMIT 3 IN ๋ค์ ์ค๋ ๋ฐ์ดํฐ์ ํฌํจ๋๋์ง ํ๋จ # IN WHERE ID IN (SELECT ID FROM ...) # NOT IN WHERE ID NOT IN (SELECT ID FROM ...) IS NULL null์ธ์ง ํ๋จ # IS NULL WHERE A.ID IS NULL # IS NOT NULL WHERE A.ID IS NOT NULL LIKE ๋ค์ ์ค๋ ๋ฌธ์์ด์ ํฌํจ๋๋์ง ํ๋จ # ๋์ด AB๋ก ๋๋๋ NAME ์ฐพ๊ธฐ WHERE NAME LIKE '%AB' # AB๋ก ์์ํ๋ NAME ์ฐพ๊ธฐ WHERE .. 2022. 2. 4. [MySQL] INNER, OUTER JOIN ์ ๋ฆฌ (์ค๋ช ์ ์ฐ์ผ ํ ์ด๋ธ ์์) TABLE_A ID ALPHABET 1 abc 2 abcd 3 abcde TABLE_B ID NUM 2 10 3 20 4 30 ๐ INNER JOIN ๊ต์งํฉ์ ์๋ฏธํ๊ณ , ๊ณตํต ๋ถ๋ถ๋ง SELECT๋๋ค. SELECT A.ID, A.ALPHABET, A.NUM FROM TABLE_A AS A INNER JOIN TABLE_B AS B ON A.ID = B.ID ID ALPHABET NUM 2 abcd 10 3 abcde 20 ๐ LEFT OUTER JOIN (1) ๊ณตํต ๋ถ๋ถ ํฌํจ, ์กฐ์ธ ๊ธฐ์ค ์ผ์ชฝ ํ ์ด๋ธ์ ์ ๋ถ๋ฅผ SELECT ํ๋ค. SELECT A.ID, A.ALPHABET, A.NUM FROM TABLE_A AS A LEFT OUTER JOIN TABLE_B AS B ON A.I.. 2022. 2. 3. [MySQL] Programmers Lv.4 ์ ์ฒด ๋ฌธ์ ํ์ด ์ฐ์ ์ ์๊ฑฐํธ๊ฐ ๋ด๊ธด ์ฅ๋ฐ๊ตฌ๋ SELECT DISTINCT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk' AND CART_ID IN ( SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') ORDER BY CART_ID ๐ DISTINCT ํค์๋๋ก ์ค๋ณต ์ ๊ฑฐ ๊ฐ๋ฅ ๋ณดํธ์์์ ์ค์ฑํํ ๋๋ฌผ SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME FROM ANIMAL_INS AS INS JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME ORDER B.. 2022. 2. 2. [MySQL] Programmers Lv.3 ์ ์ฒด ๋ฌธ์ ํ์ด ์์ด์ง ๊ธฐ๋ก ์ฐพ๊ธฐ -- join ๋ฏธ์ฌ์ฉ SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS WHERE ANIMAL_ID NOT IN ( SELECT ANIMAL_ID FROM ANIMAL_INS) -- join ์ฌ์ฉ SELECT OUTS.ANIMAL_ID, OUTS.NAME FROM ANIMAL_OUTS AS OUTS LEFT OUTER JOIN ANIMAL_INS AS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID WHERE INS.ANIMAL_ID IS NULL ORDER BY OUTS.ANIMAL_ID ๐ JOIN ์ฌ์ฉํ ๋๋ ๊ฐ ํ ์ด๋ธ์ ์ด๋ฆ ๋ถ์ฌ์ ์จ์ฃผ๊ณ , ON์ผ๋ก ์ธ๋ํค ๋ง์ถฐ์ฃผ๊ธฐ ๐ OUTER JOIN ์ฌ์ฉํ๋ฉด ์๋ก ์ด๋ค ์ฐจ์ด๊ฐ ์๋์ง ์ ์ ์์ (h.. 2022. 2. 2. [MySQL] Programmers Lv.2 ์ ์ฒด ๋ฌธ์ ํ์ด ๊ณ ์์ด์ ๊ฐ๋ ๋ช ๋ง๋ฆฌ ์์๊น SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS "count" FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ์ต์๊ฐ ๊ตฌํ๊ธฐ SELECT MIN(DATETIME) AS "์๊ฐ" FROM ANIMAL_INS ์ด๋ฆ์ el์ด ๋ค์ด๊ฐ๋ ๋๋ฌผ ์ฐพ๊ธฐ SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE NAME LIKE "%el%" AND ANIMAL_TYPE = "Dog" ORDER BY NAME ๐ LIKE๋ ๋ฌธ์์ด์ ํจํด์ ๊ฒ์ํ๋ ์ญํ ๐ SQL์ ๊ธฐ๋ณธ์ ์ผ๋ก ๋์๋ฌธ์ ๊ตฌ๋ถํ์ง ์์ ์ ์ ์๊ฐ ๊ตฌํ๊ธฐ(1) SELECT HOUR(DATETIME) AS "HOUR", COUN.. 2022. 2. 2. ์ด์ 1 2 3 ๋ค์