ํฐ์คํ ๋ฆฌ ๋ทฐ
[WHERE] + ์กฐ๊ฑด๋ฌธ
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
๊ฐ์ ์กฐ๊ฑด๋ฌธ ๊ฒ์ฌ๋ == ๊ฐ ์๋ =๋ฅผ ์ด๋ค!
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
[ORDER BY] ์ด๋ฆ๊ธฐ์ค ์ ๋ ฌ, ์ด๋ฆ์ด ๊ฐ์ ๊ฒฝ์ฐ ๋์ค์ ๋ณดํธํ ๊ธฐ๊ฐ ์ ๋๋ก ์ ๋ ฌ
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC
์ญ์ ์ ๋ ฌํ๊ธฐ → DESC ์ฌ์ฉ
SELECT NAME, DATETIME FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC
DATE_FORMAT ์ง์ , 3์์ ์ฌ์ ์ถ๋ ฅ ** ์ฐ์์ผ ๋์๋ฌธ์ ์ฃผ์
SELECT MEMBER_ID, MEMBER_NAME, GENDER, **DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH**
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH)=3
AND TLNO is NOT NULL
AND GENDER = 'W'
ORDER BY MEMBER_ID;
[GROUP BY] ํน์ ์ปฌ๋ฝ ๊ทธ๋ฃนํ, HAVING → ๊ทธ๋ฃนํํ ๊ฒฐ๊ณผ์ ์กฐ๊ฑด์ ๊ฒ
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC
๋๋ช
๋๋ฌผ ์ ์ฐพ๊ธฐ
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME
ํ๋ถ์ธ๊ณผ ๋๋ ์ผ๋ฐ์ธ๊ณผ ์์ฌ ๋ชฉ๋ก ์ถ๋ ฅ
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS'
OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME
MAX
SELECT MAX(PRICE) AS MAX_PRICE FROM PRODUCT
๊ฐ์ฅ ๋น์ผ ์ํ ์ ๋ณด ์ถ๋ ฅ
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) AS PRICE FROM FOOD_PRODUCT);
์ต๋๊ฐ ๊ตฌํ๊ธฐ
SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME = (SElECT MAX(DATETIME) FROM ANIMAL_INS)
COUNT
๋๋ฌผ ์ ๊ตฌํ๊ธฐ
# row๊ฐฏ์ ๊ตฌํ๊ธฐ
SELECT COUNT(*)
FROM ANIMAL_INS
๊ณ ์์ด์ ๊ฐ๋ ๋ช๋ง๋ฆฌ ์์๊น
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
์ค๋ณต ๊ฐ ์ ๊ฑฐ
COUNT(DISTINCT) ํ์ฉํ๊ธฐ !!!
LIKE ๊ตฌ๋ฌธ → WHERE์ ์ ์ฌ์ฉ๋จ. ๋ถ๋ถ์ ์ผ๋ก ์ผ์นํ๋ ์ปฌ๋ผ์ ์ฐพ์ ๋ ์ฌ์ฉ
- ์ฌ์ฉ ์์
- -A๋ก ์์ํ๋ ๋ฌธ์๋ฅผ ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE 'A%'
- -A๋ก ๋๋๋ ๋ฌธ์ ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE '%A'
- -A๋ฅผ ํฌํจํ๋ ๋ฌธ์ ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE '%A%'
- -A๋ก ์์ํ๋ ๋๊ธ์ ๋ฌธ์ ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE 'A_'
- -์ฒซ๋ฒ์งธ ๋ฌธ์๊ฐ 'A''๊ฐ ์๋ ๋ชจ๋ ๋ฌธ์์ด ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE'[^A]'
- -์ฒซ๋ฒ์งธ ๋ฌธ์๊ฐ 'A'๋๋'B'๋๋'C'์ธ ๋ฌธ์์ด ์ฐพ๊ธฐ-- SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE '[ABC]' SELECT ์ปฌ๋ผ๋ช FROM ํ ์ด๋ธ WHERE ์ปฌ๋ผ๋ช LIKE '[A-C]'
# ์ง๋ฃ๊ณผ๋ณ ์ด ์์ฝ ํ์ ์ถ๋ ฅํ๊ธฐ
SELECT MCDP_CD AS '์ง๋ฃ๊ณผ์ฝ๋', COUNT(*) AS '5์์์ฝ๊ฑด์'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY COUNT(*), MCDP_CD ASC
JOIN
SELECT PRODUCT_CODE, SUM(B.SALES_AMOUNT*A.PRICE) AS SALES
FROM PRODUCT A
JOIN OFFLINE_SALE B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE
** INS์๋ ์๊ณ OUT์๋ ์๋ ๋ฐ์ดํฐ๋ฅผ ๊ฒ์ฌํด์ผํ๋ฏ๋ก
OUT๋ฐ์ดํฐ๋ฅผ ๊ธฐ์ค์ผ๋ก ๋ณํฉ
select์ order by ์ ์์ ํ ์ด๋ธ ์ด๋ฆ ๋ถ์ฌ์ค์ผ ์ค๋ฅ ์๋จ!!
์์ด์ง ๊ธฐ๋ก ์ฐพ๊ธฐ
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A
LEFT JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID
DATEDIFF ๋ ์ง ๋ฉฐ์น ์ฐจ์ด์ธ์ง ๊ณ์ฐ! ์๋์ฐจ ํ๊ท ๋์ฌ ๊ธฐ๊ฐ ๊ตฌํ๊ธฐ
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC
** ์ค๋ณต์ ๊ฑฐ DISTINCT
** ๋ฌธ์์ด ์ฐพ๊ธฐ LIKE
%EL ์ด๋ผ๊ณ ํ๋ฉด ๋์๋ฆฌ๊ฐ EL์ธ ๋ฐ์ดํฐ๋ฅผ ์ฐพ๊ฒ๋ฉ๋๋ค.
EL%๋ผ๊ณ ํ๋ฉด ์ฒ์์ด EL์ธ ๋ฐ์ดํฐ๋ฅผ ์ฐพ๊ฒ๋ฉ๋๋ค.
%EL%๋ผ๊ณ ํ๊ฒ๋๋ค๋ฉด ์ด๋๊ฐ์ EL์ด ๋ค์ด๋ง ์์ผ๋ฉด ๋ฉ๋๋ค
JOIN 3๊ฐ
SELECT C.APNT_NO, A.PT_NAME, C.PT_NO, C.MCDP_CD, B.DR_NAME, C.APNT_YMD
FROM APPOINTMENT C
JOIN (SELECT PT_NAME, PT_NO FROM PATIENT) A
JOIN (SELECT MCDP_CD, DR_NAME, DR_ID FROM DOCTOR) B
ON A.PT_NO = C.PT_NO and B.DR_ID = C.MDDR_ID
WHERE C.MCDP_CD = 'CS'
AND C.APNT_CNCL_YN = 'N'
AND C.APNT_YMD LIKE '2022-04-13%'
GROUP BY PT_NAME
ORDER BY C.APNT_YMD
IF Data REGEXP ('^[0-9]+$')
์ ๊ทํํ์
^ : ์์ํ๋ ๋ฌธ์์ด์ ์ฐพ์
[]: ์์ ๋์ด๋ ํจํด์ ํด๋นํ๋ ๋ฌธ์์ด ์ฐพ์
$ : ~~$ ๋ก ๋๋๋ ๋ฌธ์์ด์ ์ฐพ์
+ : 1ํ์ด์ ๋ํ๋๋ ๋ฌธ์ ์ฐพ์
{m,n} : mํ ์ด์ nํ ์ดํ ๋ฐ๋ณต๋๋ ๋ฌธ์ ์ฐพ์
"[A-z]+" ๋์ ๋ฌธ์์ด์์ ์ํ๋ฒณ์ด ํ ๊ฐ ์ด์์ธ ๋ฌธ์์ด์ ์ฐพ์
"^[0-9]+" ํ ๊ฐ ์ด์์ ์ซ์๋ก ์์ํ๋ ๋ฌธ์์ด์ ์ฐพ์
[^๋ฌธ์] : ๊ดํธ ์์ ๋ฌธ์๋ฅผ ํฌํจํ์ง ์์ ๋ฌธ์์ด์ ์ฐพ์
if data regexp('^[0-9]+$')
IF๋ฌธ
if ( ์กฐ๊ฑด๋ฌธ, ์ฐธ์ผ๋ ๊ฐ, ๊ฑฐ์ง์ผ๋ ๊ฐ)
SELECT IF(required, 'ํ์' '์ ํ') AS 'ํ์์ฌ๋ถ' FROM TABLE
SELECT
A.seq,
IF(A.seq <= 3, 'A', 'B') AS if_result
FROM Table A
CASW ~ WHEN ์กฐ๊ฑด๋ฌธ
CASE
WHEN ์กฐ๊ฑด1 THEN '์กฐ๊ฑด1 ๋ฐํ๊ฐ'
WHEN ์กฐ๊ฑด2 THEN '์กฐ๊ฑด2 ๋ฐํ๊ฐ'
ELSE '์ถฉ์กฑ๋๋ ์กฐ๊ฑด ์์๋ ๋ฐํ๊ฐ'
END
SELECT
seq,
CASE
WHEN (u.seq BETWEEN 1 AND 3) THEN 'A'
WHEN (u.seq BETWEEN 4 AND 6) THEN 'B'
ELSE 'C'
END AS case_result
FROM Table u
๋ฌธ์์ด ์๋ฅด๊ธฐ
SUBSTRING(๋ฌธ์์ด , ์์์์น, ๊ธธ์ด)
SELECT SUBSTRING("WWW.Google.COM", 3);
SELECT SUBSTRING("WWW.Google.COM", 3, 5);
SELECT SUBSTRING("WWW.Google.COM" FROM 5);
SELECT SUBSTRING("WWW.Google.COM" FROM 2 FOR 2);
#W.Google.COM
#W.Goo
#Google.COM
#WW
๋ฌธ์์ด ํฉ์น๊ธฐ
SELECT CONCAT(๋ฌธ์์ด1, ๋ฌธ์์ด2 [, ๋ฌธ์์ด3 ...]) FROM [์กฐํ ํ
์ด๋ธ๋ช
]
SELECT CONCAT('HA','BBB',' ','FOR','CD') FROM DUAL;
SELECT CONCAT('999','.','111') FROM DUAL;
SELECT CONCAT('HA','BBB',NULL,'FOR','CD') FROM DUAL;
#HABBB FORCD
#999.111
#NULL
mysql ์ซ์,๋ฌธ์ ์ฌ์ด์ - ๋ฃ๊ธฐ
ex) 201512 ๋ผ๋๋ฌธ์๋ฅผ 2015-12๋ผ๊ณ ๋ฐ๊พธ๊ณ ์ถ์๋ ์ฐ๋ํจ์
update money set
month = concat(substr(MONTH,1,4),'-',substr(MONTH,5,6))
number = concat(substring(month,1,4),'0',substring(month,5,6))
'Programming > ํ๋ก๊ทธ๋๋จธ์ค' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
230224 [1012: ์ ๊ธฐ๋๋ฐฐ์ถ] (0) | 2023.09.02 |
---|---|
230224 [SQL(JOIN)&DFS+BFS&๊ตฌํ๋ฌธ์ ๋ค ๋ณต์ต] (0) | 2023.09.02 |
230221 [ํ๋ก๊ทธ๋๋จธ์ค ํ๊ฒ ๋๋ฒ] (0) | 2023.09.02 |
230221 [ํ๋ก๊ทธ๋๋จธ์ค SQLํ ์ด๋ธ ์กฐํ] (0) | 2023.09.02 |
230221 [ํ๋ก๊ทธ๋๋จธ์ค ์์ ๋ง๋ค๊ธฐ] (0) | 2023.09.02 |
- Total
- Today
- Yesterday
- gan
- ๊ตฌ๊ธ์ฝ๋ฉ
- ๋๋ฆผ๋ถ์ค
- lgaimers
- ์ฝ๋ฉ๊ณต๋ถ
- ํ ํฌ์๋ฐ
- AI์ปจํผ๋ฐ์ค
- HyperCLOVA
- Aimers
- AIRUSH2023
- CLOVAX
- ์ฝ๋ฉ์๋ฌ
- WGAN
- ํ์ด์ฌ์ฝํ
- SQL
- ํ์ด์ฌ
- AIRUSH
- SKTECHSUMMIT
- C์ธ์ด
- ์คํ ์ด๋ธ๋ํจ์
- ๋ ผ๋ฌธ๋ฆฌ๋ทฐ
- MYSQL
- ํ๋ก๊ทธ๋๋จธ์ค
- ๊ธฐ์ ์ปจํผ๋ฐ์ค
- ๋ ผ๋ฌธ์ฝ๊ธฐ
- ์ฝํ ์ค๋น
- dreambooth
- StableDiffusion
- DALLE
- ์ฝํ
์ผ | ์ | ํ | ์ | ๋ชฉ | ๊ธ | ํ |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |