코딩테스트 SQL 정리(MySQL)

JOIN

SELECT a.ANIMAL_ID, a.NAME from animal_ins a join animal_outs b on a.animal_id=b.animal_id 
where a.datetime>b.datetime order by a.datetime;

join 자리에 left join, right join 들어갈 수 있음

 

NOT IN

SELECT NAME, DATETIME from ANIMAL_INS where animal_id not in (SELECT animal_id from ANIMAL_OUTS) 
order by datetime limit 3;

not in, limit 3(정렬된 그룹에서 위에 3개 뽑음) 생각

 

IF

SELECT ANIMAL_ID, NAME, if(sex_upon_intake like 'Intact%', 'X', 'O') 중성화 from ANIMAL_INS 

 

DATE_FORMAT

SELECT ANIMAL_ID, NAME, date_format(datetime, '%y-%m-%d') 날짜 from ANIMAL_INS order by ANIMAL_ID

 

집계함수

SELECT count(distinct name) count from ANIMAL_INS

count 자리에 max, min, sum, avg 들어갈 수 있음

 

Between

SELECT *
FROM contacts
WHERE contact_id BETWEEN 100 AND 200;