Oracle SQL(3)

해석순서

 

GROUP BY

[참고] https://extbrain.tistory.com/56
select [그룹컬럼], [집계함수] from [테이블명] group by [그룹컬럼];

SELECT type, COUNT(name) AS cnt FROM hero_collection GROUP BY type;

--hero_collection 테이블에서 type 컬럼을 기준으로 그룹핑한 결과를 보여주는 쿼리

 

HAVING

• where절과 비슷한 조건절의 역할을 하지만 where 뒤로 와야하고 group by와 함께 사용해야함
• 집계함수를 사용하여 조건을 부여할 수 있음

select [그룹컬럼], [집계함수] from [테이블명] group by [그룹컬럼] having [조건절];

select addr, count(*) from sungjuk group by addr having count(*) = 3;
-- 주소별 인원수가 3인 행을 조회

 

CASE WHEN

• IF문, SWITCH문의 기능을 함
• ELSE 부분은 생략 가능
• 만족하는 조건이 없으면 NULL을 리턴함
• SELECT절, WHERE절, PL/SQL 등 많은 부분에서 사용 가능

 
IF문 방식
CASE WHEN [칼럼 = 조건] THEN [리턴값] ELSE [리턴값] END AS [새로운칼럼의 이름]

SELECT ename
     , deptno
     , CASE WHEN deptno = '10' THEN 'New York'
            WHEN deptno = '20' THEN 'Dallas'
            ELSE 'Unknown'
       END AS loc_name
  FROM emp
 WHERE job = 'MANAGER'

 
SWITCH문 방식
CASE [칼럼] WHEN [조건] THEN [리턴값] ELSE [리턴값] END AS [새로운칼럼의 이름]

SELECT ename
     , deptno
     , CASE deptno 
            WHEN 10 THEN 'New York'
            WHEN 20 THEN 'Dallas'
            ELSE 'Unknown'
       END AS loc_name
  FROM emp
 WHERE job = 'MANAGER'

 

서브쿼리

• 하나의 SQL 문에 포함되어 있는 또 다른 SQL 문
• 괄호로 감싸서 사용
• 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능

---

문자열 관련 함수

lower() / upper()

문자열을 전부 소문자 또는 대문자로 전환
lower([문자열]) / upper([문자열])

select lower('Hello World') from dual;
select upper('Hello World') from dual;

 

length()

문자열의 글자갯수 조회 (공백 포함)
length([문자열])

select length('Hello World') from dual;

 

substr()

문자열에서 지정한 인덱스 번호부터 지정한 숫자만큼 문자열을 출력
→ 데이터베이스에서의 문자열의 인덱스는 1부터 시작함
substr([문자열],[인덱스번호],[숫자])

select substr('Hello World', 1, 5) from dual;
-- 'Hello World'의 1인덱스 부터 5글자 까지 출력

 

instr()

특정 문자 위치의 인덱스 번호를 반환함
→ 데이터베이스에서의 문자열의 인덱스는 1부터 시작함
instr([문자열], [특정문자])

select instr('Hello World','W') from dual;

 

lpad() / rpad()

오라클에서 왼쪽, 오른쪽에 특정문자를 채워서 문자열 길이를 맞출 때 사용
lpad([문자열], [문자열길이], [특정문자]) / rpad([문자열], [문자열길이], [특정문자])

select lpad('SKY', 5, '*') from dual; --**SKY
select rpad('SKY', 5, '*') from dual; --SKY**

 

replace()

문자열에서 문자열 안의 특정 문자를 모두 찾아서 전부 새로운 특정 문자로 바꿔주는 함수
replace([문자열], [문자열 안의 특정문자], [바꿀새로운문자])

select replace('HAPPY','P', 'K') from dual; --HAKKY

 

chr()

• 함수인자에 대응하는 문자를 리턴하는 함수
• 인자에 아스키코드를 넣으면 대응하는 문자를 리턴함

아스키코드

아스키코드	문자
10	line feed
13	carriage return
32	space
65	A
97	a

select chr(65) from dual; --아스키코드 문자 변환 'A'
select chr(66) from dual; --아스키코드 문자 변환 'B'
select chr(97) from dual; --아스키코드 문자 변환 'a'
select chr(98) from dual; --아스키코드 문자 변환 'b'

 

concat()

문자열+문자열 또는 칼럼의 내용+문자열 을 연결해줌
concat([문자열], [문자열])
concat([칼럼명], [문자열])

select concat('로미오', '줄리엣') from dual;

select concat(uname, '의 평균은'), concat(aver, '입니다') from sungjuk;

 

결합연산자 (||)

• select 절에 첨부하여 두 개의 문자열을 하나로 만들 수 있음
• 해당 연산자를 사용하면 해당 유저에 영향을 주진 않고 유저내의 테이블로 스크립스 생성해줌

select uname || '의 평균은' || aver || '입니다' from sungjuk;

---

숫자 관련 함수

abs()

절대값 반환 abs([숫자])

 

mod()

나머지 함수(%) mod([숫자, 나눌숫자])

select mod(5,3) from dual; --2

 

소숫점 조정 함수

함수	사용방법	결과값
ceil()	ceil([숫자])	소수점 올림 (정수로 만듦)
trunc()	trunc([숫자], [남길 소수점자리])	소수점 내림 (남길 소수점 자리까지만 출력)
round()	round([숫자], [남길 소수점자리])	소수점 반올림 (남길 소수점 자리까지만 출력)

 

to_number()

문자열을 숫자형으로 변환 to_number([숫자])

select to_number('123')+1 from dual; --124
select '123'+1 from dual; --124 , 내부적으로 to_number()가 호출됨

---

날짜 관련 함수

sysdate

• 쿼리문을 실행하는 순간의 날짜, 시간, 분, 초를 알려줌
• 괄호 없이 사용

select sysdate from dual;

 

extract()

년, 월, 일 추출
extract([년 or 월 or 일] from [날짜형칼럼])

select extract(year from sysdate) from dual; --년
select extract(month from sysdate) from dual; --월
select extract(day from sysdate) from dual; --일

 

날짜데이터의 연산

일 수 연산
sysdate+[숫자]

select sysdate+100 from dual; --오늘날짜 +100일
select sysdate-100 from dual; --오늘날짜 -100일

 

months_between()

months_between([날짜데이터1], [날짜데이터2])
두개의 날짜 데이터에서 개월 수 연산

select months_between('2023-02-27', '2022-10-30') from dual; --(3.9)
select months_between('2023-02-27', '2023-05-30') from dual; --(-3.9)

 

to_date()

문자열을 날짜형으로 변환
to_date([날짜형식의 문자열], [날짜포맷])

SELECT TO_DATE('2021-12-12', 'YYYY-MM-DD')
     , TO_DATE('2021-12-12 17:10:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual;

---

nvl()

nvl([조회한 값], [바꿀값])

• 조회한 값이 null이라면 바꿀 값으로 대체됨
• 행 추가시 서브쿼리 이용하여 사용가능

select nvl(max(kor), 0) as max_kor --max(kor)값이 null이라면 0으로 대체함
from sungjuk;