230118 SQL 응용

kangjuju·2023년 1월 17일
ORDER BYWHEREbetweenconcatdistinctinjsonlikeroundselect기타함수날짜함수내장함수숫자함수함수
0

Acorn

목록 보기
5/15

REFERENCE :
https://velog.io/@ckstn0777/SQL-%EA%B8%B0%EB%B3%B8-SELECT-%EB%AC%B8
https://github.com/pykwon/etc/blob/master/sample_table_MariaDbl.txt

예제

SELECT

<모든 행열 값 읽기.>
SELECT * FROM JIKWON; 

<선택적으로 읽기. 컬럼 순서는 임의적으로>
SELECT JIKWON_NAME,JIKWON_PAY, JIKWON_NO FROM JIKWON; 

<컬럼에 별명>
SELECT JIKWON_NO AS 사번, JIKWON_NAME AS 직원명, JIKWON_PAY 연봉 FROM JIKWON;

<즉석으로 가상의 컬럼을 사용>
SELECT JIKWON_NAME AS 이름, JIKWON_PAY 연봉,
JIKWON_PAY*0.02 AS 기부금 FROM JIKWON;

<가공>
SELECT CONCAT(JIKWON_NAME,'님') AS 이름 FROM JIKWON;

ORDER BY 정렬

ORDER BY 컬럼명 [ASC | DESC ]

<PAY 기준 내림차순>
SELECT * FROM JIKWON ORDER BY JIKWON_PAY DESC;
<PAY 기준 오름차순(기본값)>
SELECT * FROM JIKWON ORDER BY JIKWON_PAY ASC;
<두가지 기준으로 정렬>
SELECT * FROM JIKWON ORDER BY JIKWON_JIK ASC, JIKWON_PAY DESC;
<계산한 컬럼추가 후 그 데이터 기준 정렬>
SELECT JIKWON_NAME, JIKWON_PAY, JIKWON_PAY / 100 * 100 AS PAY
 FROM JIKWON ORDER BY PAY ASC;
<중복 자료 배제>
SELECT DISTINCT JIKWON_JIK FROM JIKWON;

SELECT~ WHERE

<특정 컬럼 조건 데이터 추출>
SELECT * FROM JIKWON WHERE JIKWON_JIK != '대리';

SELECT * FROM JIKWON WHERE JIKWON_NO <= '5'; 

SELECT * FROM JIKWON WHERE JIKWON_IBSAIL = '08-09-01';

SELECT * FROM JIKWON WHERE JIKWON_NO >= '5' OR JIKWON_NO <= '10';

SELECT * FROM jikwon WHERE JIKWON_JIK = '사원'
										AND JIKWON_GEN = '남' AND JIKWON_PAY <= 3500;
                                        
SELECT * FROM jikwon WHERE JIKWON_JIK = '사원' 
									AND (JIKWON_GEN = '여' OR JIKWON_IBSAIL >= 2017-1-1);
                                    <괄호를 사용함으로써 가독성을 높였다,>
                                    
<BETWEEN 시작값 AND 끝값>
SELECT * FROM jikwon WHERE JIKWON_IBSAIL BETWEEN '2015-1-1' AND '2020-11-31';

SELECT * FROM jikwon WHERE JIKWON_NO NOT BETWEEN 5 AND 10;
(JIKWON_NO >= '5' OR JIKWON_NO <= '10';) 와 같음

SELECT * FROM jikwon WHERE JIKWON_NAME BETWEEN '김' AND '박';

NOT BETWEEN 사용시 해당 조건의 반대되는 값을 얻는다.
조건 사용시 부정조건은 속도를 느리게하는 원인이 될 수도 있다.

IN

멤버 조건에 성립하는 데이터를 추출한다.

<jikwon_jik에서 3가지 직업을 추출>

SELECT * FROM jikwon WHERE
 jikwon_jik = '대리' OR jikwon_jik = '과장' OR jikwon_jik = '부장';
<결과는 같지만 가독성이 더 좋다> 
SELECT * FROM jikwon WHERE jikwon_jik IN ('대리','과장','부장');

LIKE ( % / _ )

<~로 시작하는, 끝나는 문자열>
SELECT * FROM jikwon WHERE jikwon_name LIKE '이%';
SELECT * FROM jikwon WHERE jikwon_pay LIKE '3%'; 
SELECT * FROM jikwon WHERE jikwon_name LIKE '%유%'; 
SELECT * FROM jikwon WHERE jikwon_name LIKE '이_라';

<주민번호 상 남자만 출력>
SELECT * FROM gogek WHERE gogek_jumin LIKE '%-1%';

null 비교

<null값 추출엔 비교연산자가 아닌 is를 써야한다>
SELECT * FROM jikwon WHERE jikwon_jik IS NULL;
SELECT * FROM jikwon WHERE jikwon_jik IS NOT NULL;

LIMIT

<조건에 맞는 결과 중 상위 5개만 출력>
SELECT * FROM jikwon WHERE jikwon_pay > 3000 LIMIT 5;

<결과 중 상위 4번째부터 10번째까지 출력>
SELECT * FROM jikwon WHERE jikwon_pay > 3000 LIMIT 3,10;

응용

AS / IN / BETWEEN / ORDER BY / LIMIT

SELECT jikwon_NO AS 사번, jikwon_NAME 직원명, jikwon_JIK,JIKWON_PAY / 12 AS 보너스,
JIKWON_IBSAIL FROM jikwon

WHERE JIKWON_JIK IN('사원','과장','대리')
AND
((JIKWON_PAY > 3000 AND JIKWON_IBSAIL BETWEEN '2010-1-1' AND '2019-12-31') OR
(JIKWON_NAME LIKE '이%' AND JIKWON_IBSAIL BETWEEN '2010-1-1' AND '2021-12-31;'))

ORDER BY JIKWON_JIK, JIKWON_PAY DESC LIMIT 10,3;

JSON

<JSON 형식 데이터가 가 필요할때>

SELECT JSON_OBJECT
('JIKWON_NO',JIKWON_NO,'JIKWON_NAME',JIKWON_NAME,'연봉',JIKWON_PAY)
AS 'JSON DATA' FROM jikwon WHERE JIKWON_JIK = '대리';

결과를 격자 행 내보내기로 입맛에 맞게 데이터를 가공할 수 있다.

내장함수

데이터조작의 효율성을 향상 - https://reddb.tistory.com/112

문자함수

<USER : 유저 정보 출력>
SELECT USER();

<LOWER : UPPER 문자함수 : 소,대문자로 변환출력>
SELECT 'HelLO', LOWER('HelLO'),UPPER('HelLO');

<SUBSTR : 해당문자열의 n번째부터 m글자 추출. -는 뒤에서부터 >
SELECT SUBSTR('Hello World',3),SUBSTR('Hello World',2,3),SUBSTR('Hello World',-5,3);

<TRIM  : 양쪽, 왼쪽, 오른쪽 공백 제거>
SELECT TRIM(' aabb bbaa '),LTRIM(' aabb bbaa '),RTRIM(' aabb bbaa ');

<INSTR  :  해당문자의 위치인덱스>
SELECT INSTR ('Hello world','e');

<REPLACE :  해당문자열의 특정 문자를 지정하여 대체>
SELECT REPLACE('010-1111-1111','-','/');

<예제
jikwon 테이블에서 이름에 '이'가 포함된 직원이 있는 경우,
'이'부터 2글자출력. 마지막에'이'가 있으면  한글자 출력>
SELECT jikwon_name, SUBSTR(jikwon_name,INSTR(jikwon_name,'이'),2) FROM jikwon
WHERE jikwon_name LIKE '%이%';


...
..
.

숫자함수

<ROUND : 반올림. 지정시 n의 자리 수까지만 반올림하여 출력>
SELECT ROUND(345.678), ROUND(345.678,2),ROUND(355.678,-2);
SELECT jikwon_name, jikwon_pay, ROUND(jikwon_pay * 0.2345,1) AS tex FROM jikwon LIMIT 3;

<TRUNCATE : 버림>
SELECT TRUNCATE(345.678,0),TRUNCATE(345.678,1),TRUNCATE(345.678,-1);

<MOD:  나머지. 둘중 하나만 쓰면됨>
SELECT MOD(15,2),15%2;

<GREATEST : 최소, 최대값 추출 > 
SELECT GREATEST(23,5,22,12), LEAST(23,5,22,12);
...
..
.

날짜함수

<현재 날짜 시간. 유형별로 있다.>
SELECT NOW(),SYSDATE(),CURDATE(),CURRENT_TIMESTAMP();

<ADDDATE : 날짜 덧셈뺄셈>
SELECT ADDDATE('2013-01-10',3),ADDDATE('2013-01-10',250),ADDDATE('2013-01-10',-5);

<DATE_ADD : 날짜에 시간 더하기>
SELECT DATE_ADD(NOW(),INTERVAL 3 [MINUTE/DAY/MONTH/YEAR]);

<DATE_SUB : 날짜에 시간 빼기>
SELECT DATE_SUB(NOW(),INTERVAL 2 [MINUTE/DAY/MONTH/YEAR]);

<DATEDIFF : 날짜간의 일수 차이>
SELECT DATEDIFF(NOW(), '2018-2-13');

<LAST_DAY : 해당 월의 마지막 날>
SELECT LAST_DAY(SYSDATE());

<DAYOFYEAR : 해당년도로부터 몇번째날인지>
SELECT DAYOFYEAR(SYSDATE());

<TIMESTAMPDIFF : 차이를 연, 분기, 월, 주, 일, 시, 분, 초를 지정>
SELECT TIMESTAMPDIFF(QUARTER,NOW(),'2023-8-5'),
TIMESTAMPDIFF(HOUR,NOW(),'2023-8-5');

형변환 함수 DATE_FORMAT / %

https://itworldyo.tistory.com/70

SELECT DATE_FORMAT('2023-1-18','%Y%M%D'),
			DATE_FORMAT(NOW(), '%Y년%m월 %d일 %h시%i분 %s초'); 

SELECT DATE_FORMAT(NOW(),'%a'),DATE_FORMAT(NOW(),'%W');

SELECT jikwon_name, jikwon_ibsail,DATE_FORMAT(jikwon_ibsail,'%W') FROM jikwon LIMIT 5;


<CAST: 문자열을 날짜로 casting>
SELECT CAST('2013$1$18' AS DATE);

<RPAD/LPAD 자리수를 잡고 공백을 지정문자로 채움>
SELECT RPAD(56,10,'*'),LPAD(56,10,'*');

기타함수

DENSE_RANK 와 RANK의 차이를 확인

<순위 결정>
SELECT jikwon_no,jikwon_name,jikwon_pay,
RANK() over(ORDER BY jikwon_pay),
DENSE_RANK() over(ORDER BY jikwon_pay) FROM jikwon;


<NVL(VALUE1, VALUE2) : VALUE1이 NULL 이면 value2가 수행 >
SELECT jikwon_no, jikwon_name,nvl(jikwon_jik,'임시직') FROM jikwon;

<NVL2(VALUE1, VALUE2, VALUE3) : 
VALUE1이 NULL인지 평가. NULL이면 2, 아니면 3 적용>
SELECT jikwon_no, jikwon_name,nvl2(jikwon_jik,'정규직','임시직') FROM jikwon;

<NULLIF(VALUE1,VALUE2) : 두 개의 값이 일치하면 NULL을, 아니면 VALUE1수행>
SELECT jikwon_no, jikwon_name,jikwon_jik, NULLIF(JIKWON_JIK,'대리') FROM jikwon;

<조건 표현식 - case 구문>
SELECT case 10 / 5 
when 5 then '5일때' 
when 2 then '2일때'
ELSE '둘다아님' END AS '결과';
<case응용>
SELECT jikwon_no,jikwon_name,jikwon_pay,
case jikwon_pay
when 9900 then '9900만 출력'
when 3500 then '3500만 출력'
ELSE '기타 연봉들' END AS '출력결과'
FROM jikwon;
<case응용2>
SELECT jikwon_no,jikwon_name,jikwon_jik,jikwon_pay,
case jikwon_jik
when '이사' then jikwon_pay * 0.05
when '부장' then jikwon_pay * 0.04
when '과장' then jikwon_pay * 0.05
ELSE jikwon_pay*0.02 END AS '기부금'
FROM jikwon;

<조건 표현식 - case 구문2>
SELECT jikwon_no,jikwon_name,jikwon_gen,
case 
when jikwon_gen = '남' then 'M'
when jikwon_gen = '여' then 'F' END AS gender
FROM jikwon WHERE jikwon_jik = '과장';
<case 응용 2-2>
SELECT jikwon_no,jikwon_name,jikwon_pay,
case
when jikwon_pay >= 7000 then '만족'
when jikwon_pay >= 5000 then '보통'
ELSE '부족' END AS result
FROM jikwon WHERE jikwon_jik IN('대리','과장');

//case를 지정하지 않고 where에서 필터링함

<if(조건) 참값,거짓값 as 별명>
SELECT jikwon_no,jikwon_name,jikwon_pay, 
if(TRUNCATE(jikwon_pay / 1000,0) >=5, '만족','찝찝') AS result FROM jikwon;

profile
kangjuju
다음 포스트

230118 SQL 예제2 case

0개의 댓글