SQL 기타사용예

SQL slicing 

SELECT문 안에서는

left(), right()

SELECT MEMBER_ID, MEMBER_NAME, GENDER, left(DATE_OF_BIRTH, 10) AS DATE_OF_BIRTH 
FROM MEMBER_PROFILE 
WHERE TLNO is not null and substr(DATE_OF_BIRTH, 7, 1) = 3  and GENDER = 'W'
order by member_id

Date_Format 이용가능 

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 asc

 

Where 안에서

substr(칼럼이름,시작,문자개수)

칼럼이름 like '%abc%'

SELECT MEMBER_ID, MEMBER_NAME, GENDER, left(DATE_OF_BIRTH, 10) AS DATE_OF_BIRTH 
FROM MEMBER_PROFILE 
WHERE TLNO is not null and substr(DATE_OF_BIRTH, 7, 1) = 3  and GENDER = 'W'
order by member_id


SELECT FACTORY_ID, FACTORY_NAME, ADDRESS from FOOD_FACTORY where ADDRESS like '%강원도%';

 

등 이용

 

SELECT 문에서 NULL일때 변경하는 함수 IFNULL()

SELECT PT_NAME, PT_NO,GEND_CD, AGE, IFNULL(TLNO,"NONE") AS TLNO 
FROM PATIENT 
WHERE AGE<=12 and GEND_CD = "W"
order by AGE desc, PT_NAME asc

 

SELECT문에서 최대값을 가져오고자 할때

이중 SELECT문을 이용하자

정렬후 LIMIT 1 으로도 가능

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT 
WHERE PRICE = (SELECT MAX(PRICE) AS PRICE from FOOD_PRODUCT);