Mysql 코딩테스트 준비

1. Limit

SELECT name, datetime
from animal_ins
order by datetime
LIMIT 5;  // 5이면 위에서 1~5번까지 출력

 

2. COALESCE ( ifnull(컬럼, ' ') 도 똑같이 사용 됨)

SELECT warehouse_id, warehouse_name, address, coalesce(freezer_yn, 'N') as FREEZER_YN
from food_warehouse
where address like "경기도%"
order by warehouse_id
/*COALESCE( 컬럼명, '임의출력값' ) -> NULL값을 다른 명칭으로 바꾸어주는 함수로 COALESCE( FREEZER, 'N' ) 작성하면 NULL값이 N으로 출력된다.*/

 

3. DATE_FORMAT(hire_ymd, '%Y-%m-%d')

 

SELECT dr_name, dr_id, mcdp_cd, DATE_FORMAT(hire_ymd, '%Y-%m-%d') as hire_ymd
from doctor
where mcdp_cd like 'CS' or mcdp_cd like 'GS'
order by hire_ymd desc, dr_name asc;

 

4. DATEDIFF(END_DATE, START_DATE) +1

- 몇일 차이나는지 체크

 

5. IF( ? , 참, 거짓) 

SELECT HISTORY_ID, CAR_ID, date_format(start_date, "%Y-%m-%d") as START_DATE, 
date_format(end_date, "%Y-%m-%d") as END_DATE, IF(DATEDIFF(END_DATE, START_DATE) +1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
where start_date like "2022-09%"
order by history_id desc;

 

6. LEFT(DATETIME, 10) , RIGHT(DATETIME, 10)

- 왼쪽에서부터 짜르기

- 오른쪽에서부터 짜르기 

SELECT ANIMAL_ID, NAME, LEFT(DATETIME, 10) FROM ANIMAL_INS;

 

 

7.LIMIT N,M;

- N번째 위치부터 M 개를 출력할지

- 오름차순이기에 2번째 큰수부터 4개더 밑으로 출력

SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1,4;

 

 

8. CONCAT

- 컬럼끼리 합치기

SELECT USER_ID,	NICKNAME,
CONCAT(CITY, ' ', STREET_ADDRESS1,' ',STREET_ADDRESS2) AS 전체주소,
       CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8,4)) AS 전화번호
FROM USED_GOODS_BOARD AS B JOIN USED_GOODS_USER AS U