데이터베이스(DBMS)_오라클 Select 함수

▶distinct - 중복제거

 

해당 employees 표에서 

 

 

데이터가 중복되는 불필요한 부분을 제거하고 특정 데이터 종류만 확인하고 싶을 때 사용한다.

 

Distinct를 통해 JOB_ID에 명시한 열 중에서 같은 내용이 두 개 이상일 경우, 중복 행은 한개만 남겨두고 그 밖의 행은 모두 제거한다.

 

 

 

만약 열이 두개인 경우( , ) 를 사용하여 여러개의 열을 명시할 수 있다.

ex) SELECT DISTINCT JOB_ID, DEPTNO

     FROM EMP;

 

▶비교연산자 - >=, >, <=, < 

 

SALARY가 5000 이상인 사람을 조회하고싶을 때 이와 같이 사용한다.

 

 

비교연산자는 우리가 흔히 수학에서 봤던 비교연산자와 동일하나 다른점 하나는 문자열도 사용할 수있다는 것이다.

 

SELECT *
FROM EMP
WHERE ENAME >= 'F'
;

 

일 경우에는 ENAME의 열 값의 첫 문자가 대문자 F와 비교했을 때 알파벳 순서상 F와 같거나 F보다 뒤에있는 문자열을 출력하라는 의미이다.

 

SELECT *
FROM EMP
WHERE ENAME <= 'FORZ'
;

 

이는 ENAME이 FORZ보단 작다는 의미로 ENAME의 열값이 FOIRZ를 포함한 문자열보다 알파벳 순서로 앞에있는 행을 출력하라는 의미이다. 예를들어 ENAME에 FIND문자열이 있다면 FIND의 첫문자는 FORZ로 같지만, 두번재문자 I가 FORZ의 O알파벳 순서보다 빠름으로 WHERE절의 조건식에 해당하는 값이 된다. 

 

▶NOT함수

 

같지 않다로 사용하는 함수이다.

보통 복잡한 조건식에서 정반대의 결과를 얻고자 할 때 유용하게 사용할 수 있다.

조건식을 일일이 수정하여 작성하는 것 보다 NOT연산자로 한번에 뒤집어서 사용하는 것이 간편하고 SQL문 작성시간도 줄일 수 있다.

 

[NOT]

 

 

[<>]

 

 

[!=]

 

 

▶OR

 

[OR]

 

Department id가 50 또는 manager Id가 100인 사람을 검색을 원할 때 사용

 

 

OR은 하나뿐만 아니라 여러개를 사용해도 무관하며 각 조건식 사이에 AND또는 OR연산자를 추가하여 사용할 수 있다.

 

예를 들어 salary가 6500, 7700, 13000인 경우의 수를 모두 구해야하는경우 

OR을 계속해서 사용해도 된다는 의미이다.

 

 

[IN]

 

or과 동일한 기능을 가지고 (,)이 or의 역할을 한다.

 

 

 

 

▶betseen A and B

 

Salary가 4000보다 크거나 8000보다 작은 경우를 출력하라 라는 의미로

between은 A보다 크거나 같고(최소값) B보다 작거나 같다(최대값)라는 표현을 할 때 사용한다.

 

 

▶like함수

 

이메일이나 게시판 제목 또는 내용 검색 기능처럼 일부 문자열이 포함된 데이터를 조회할 때 사용한다.

LIKE함수는 %와 같이 사용되는데 %는 와일드 카드라고 한다.

와일드 카드는 특정 문자또는 문자열을 대체하거나 문자열의 패턴을 표기하는 특수 문자로 LIKE와 함께 사용할 수있는 와일드카드는 %외에도 _가 있다.

 

 

FIRST_NAME의 D로 시작하는 데이터를 가져오라는 의미이다.

 

 

소문자 d로 끝나는 FIRST_NAME의 데이터를 가지고 오라는 의미이다.

즉, %는 어떠한 값이 와도 상관이 없다는 의미라고 생각하면 편하다.

 

그럼 특정 자리수의 특정 문자값에 따라 데이터를 구할 수 있을까?

 

 

위처럼 언더바를 통해 자리수를 표현하고 그 뒤에 e를 통해 특정 문자를 표현한다.

_ _ e %는 첫번째 문자가 와일드카드(_)이기에 문자 종류와 상관없이 e앞에는 2개의 문자가 오는 것을 의미한다. 세번째 문자가 e이고 그 이후에는 어떤 종류의 문자가 몇개가 오든 상관없다.

 

LIKE %AM%로 하면 열 값에 AM이라는 단어가 포함되어 있는 모든 사원 데이터를 찾아라 라는 의미이다. 만약, AM이라는 단어가 포함되지않은 사원의 데이터를 원하면 앞에 NOT을 붙혀 간단하게 표현할 수 있다.

 

와일드 문자가 데이터의 일부분이라면 \를 사용한다.

예를들어 A_A를 LIKE함수를 통해 표현해야한다면 'A\_A' ESCAPE '\' 처럼 작성을 하게되면 \ 뒤에 있는 _문자는 카드 기호가 아닌 데이터에 포함된 문자로 인식을 하기에 정상적으로 ESCAPE절에서 사용이 가능하다. 

 

[check]

 

LIKE연산자와 와일드 카드를 사용한 SELECT문은  사용하기 간편하고 기능면에서 활용도가 높다. 이를 잘 사용하게되면 데이터 조회 속도도 몹시 빨라지게되니 LIKE함수는 잘 기억해두도록 한다.

 

 

▶IS NULL

 

[IS NULL]

 

null의 값만 출력한다.

null이란 데이터가 완전히 '비어있는'상태를 의미한다.

 

WHERE절은 조건식의 결과 true인 행만 출력하는데 이런 연산결과 값이 NULL이면 true도 false도 아니기에 출력 값에서 제외되어버린다. 이에, 다른 어떠한 연ㅅ나자로는 특정 열의 데이터가 NULL인지 아닌지를 구분할 수 없고 이를 위해 IS NULL를 사용한다.

 

 

[is not null]

 

null값이 아닌 경우

 

 

▶ORDER BY

SELECT문을 통해 데이터를 조회할 때 시간이나 이름순서 또는 다른 기준으로 데이터를 정렬하여 출력하는 경우에 사용이 되며 SELECT문을 사용할 때 사용할 수 있는 여러절 중 가장 마지막부분에 작성한다.

 

[오름차순: order by ASC]

 

ASC를 작성하고 옆에는 정렬 기준이 되는 열 이름을 지정한다.

 

 

ASC는 생략이 가능하다.

그래서 ODER BY FIRST_NAME 만 입력을 해도 자동으로 오름차순으로 조회가 된다.

 

[내림차순: order by desc]

 

내림차순으로 데이터를 정렬해준다.

 

 

내림차순은 생략이 불가능하기에 내림차순을 원하면 DESC를 꼭 기재해주어야한다.

 

[ASC와 DESC 동시에 사용하기]

 

ORDER BY 절에는 우선순위를 고려하여 여러개의 정렬 기준을 지정할 수 있다.

예를들어 부서번호를 오름차순으로 정렬하고, 부서 번호가 같은 사원일 경우 급여를 기준으로 내림차순으로 정렬을 원할 때면 

 

SELECT *

FROM EMP

ORDER BY DEPTNO ASC, SAL DESC 로 표기해준다.

 

[ORDER BY문 주의사항]

 

ORDER BY 정렬은 꼭 필요한 경우가 아니면 사용하지 않는 것을 권장한다. 데이터를 특정 기준에 따라 가지런히 순서를 맞추는 것은 많은 자원, 비용을 소모하는 작업이다.

또한 정렬해야하는 데이터의 용량이 많을 수록 시간도 오래걸린다. 

SQL문의 효율이 낮아지는 것은 서비스 응답시간이 느려진다는 것을 뜻한다. 

 

즉, 정렬을 하지 않으면 결과를 더 빨리 출력할 수있음으로 SQL문의 효율을 높이기 위하여 꼭 필요한 경우가 아니면 사용하지 않도록 주의한다.

 

▶SUM

 

특정 카테고리를 모두 더해준다.

 

▶COUNT

 

특정 카테고리의 수를 세준다.

 

 

count로 수는 세아리지만 특정 카테고리를 제거하고 숫자를 셀 수도 있다.

 

 

▶avg

 

평균을 구하는 함수이다.

 

 

[MAX 함수]

 

최대값을 구하는 함수이다.

 

 

▶MIN

 

최소값을 구하는 함수이다.

 

 

가장 먼저 입사한 사람을 구하였다.

 

▶abs

 

 

음수 -23을 양수로 바꾸어주었다.

 

 

절대값을 23으로 입력하고 as를 통해 이름을 abs로 변경해줄 수 도 있다.

즉, 절대값을 구해주는 함수이다.

 

▶round

 

 

반올림 해준다.

 

▶trunc

 

 

 

소숫점 2자리까지만 출력

 

 

-1을 통해서 . 앞의 수를 0으로 처리

 

▶concat 함수

 

문자열 끼리 연결해주는 함수

 

 

이런 형식으로도 사용 가능하다.

 

 

▶initcap

 

글짜를 대문자로 만드는 함수

기준은 띄어쓰기이다.

 

 

▶lower(char), upper(char)

 

대소문자 만들어주는 함수

 

 

▶Lpad

 

왼쪽에 문자를 채워준다.

 

 

값, 총 문자길이, 채움문자 순서로 데이터가 출력된다.

 

LPAD2는 7문자 LPAD3은 8글자인데 good은 4글자이니 남은 공간을 #과 L로 왼쪽부터 채워준다.

LPAD1에서 6다음 아무것도 작성하지 않으면 공백으로 출력된다.

 

 

▶Rpad 함수

 

Lpad와 동일한 함수로 오른쪽부터 공백을 채워준다.

 

 

▶LTRIM, RTRIM 함수

 

문자를 제거하는 함수로 LTRIM은 왼쪽 RTRIM은 오른쪽 문자를 지워준다.

 

 

첫번째는 왼쪽에 g가 있기에 g만 지워진 것이고

두번째는 왼쪽에 o가 없기에 삭제가 되지 않았고

세번째에서 go뿐만 아니라 뒤의 o까지 goo 가 지워진다.

 

 

▶Substr(문자열, 시작위치, 길이)

 

 

 

 

 

앞의 문자열을 시작위치(1)부터 길이까지(4)만큼만 잘라서 보여준다.

 

 

 

 

8번째 자리수의 문자열(공백포함) 부터 4번째 이후의 자리까지 잘라와서 rnin이 출력되었다.

 

 

 

-를 사용하면 뒷쪽의 문자열부터 가져올 수 있다.

 

▶replace

 

 

첫번째 문자열을 두번째 문자열로 변경해주는 함수로 morning 자리에 evenning이 들어간 것을 확인할 수 있다.

 

▶sysdate

 

 

해당 컴퓨터의 시간을 가져와준다.

게시판의 날짜, 시간등으로 활용된다.

몹시 활용성이 높은 함수로 여러가지 활용법을 보자면

 

 

[add_month]

 

 

 

 해당 월부터 N월을 구한다

 

 

[last_day]

 

현재 달의 마지막 날짜

 

 

[INTERVAL] 

 

 

현재 달을 기준으로 N월,날,시간,초 등을 컨트롤해준다

 

▶to char()

 

 

문자열을 변경해주는 예시로서 -를 /로 변경해주었다.

 

 

이렇게 뒤에 분,초도 추가할 수 있다.

또한 날짜형은 숫자 데이터타입인데 to char()을 통해 abs 즉, 문자열로 변경된 것을 확인할 수 있다.

 

▶to date()

 

 

문자열에서 숫자로 변경해주는 기능으로 2번째 22/04/11은 문자열로 정상 출력 되었지만 to_date()로 감싼 날짜는 숫자로 출력됨을 확인할 수 있다. 

 

▶nvl()

 

 

널 값을 다른 데이터(0)으로 변경하였다.

 

 

이렇게 문자열도 가능하며 null값이 아닌 데이터도 함께 출력이 된다.

 

▶decode()

 

 

department에는 Marketing도 있고 IT도 있고 여러가지 부서가 있다.

NAME에는 department의 특성을 나타내는 이름들이 명시적으로 표기되어있는데 이 NAME이 너무 길어 별칭을 만들어 간단하게 출력하고 조회하고 싶을 때 decode()함수를 사용한다.

 

 

switch문의 역할을 하는 함수이다.

department_id 가 20이면 Marketing -> MA 로 가져오고

60이면 IT -> IT

90이면 Executive -> EX/ ETC 로 변경하여 데이터를 가져오게 하였고 결과물이 위의 사진이다.

 

▶case()

 

 

decode()와 비슷한 역할을 하지만 좀 더 복잡한 연산을 사용할 수 있다.

else if문과 같은 함수이다.

 

 

department가 너무 길어서 end끝에 알리아스를 주어 이름을 변경하였다.

case문은 끝에 end를 반드시 작성하여 끝맺음을 해준다.

 

▶distinct, group by 

 

AVG, COUNT, MAX, MIN, SUM등의 다중행 함수는 지정 테이블의 데이터를 가공해서 하나의 결과값만 출력한다. 

즉, DEPARTMENT별로 급여의 평균값을 구해야한다면 밑의 예시처럼 각 부서의 평균값을 구하기 위해 SELECT문을 하나하나 작성해야한다는 것이다.

 

 

다중행 함수는 AVG옆에는 부서코드 열을 바로 붙힐 수 없음으로 10,20,30을 직접 작성하여 별칭을 주었다. 이는 몹시 번거로운 방법이고 특정 부서를 추가하거나 삭제할 떄 SQL문을 수정해야함으로 바람직하지않다.

이럴 때 사용하는 것이 GROUP BY이다.

 

 

의미있는 하나의 결과를 특정 열 값별로 묶여서 출력해준다.

GROUP BY절을 작성하여 데이터를 그룹화 할 수 있ㄷ고 그룹으로 묶은 기준 열을 지정해주면된다.

 

 

DEPARTMENT의 중복되는 항목들을 하나로 묶어서 그룹으로 만들었다.

 

GROUP BY는 다중행 함수를 사용하지 않은 일반열은 GROUP BY절에 명시하지않으면 SELECT 절에서 사용할 수 없다는 것이다. 이 말은!

 

 

DEPARTMENT_ID를 기준으로 GROUP BY를 사용하면 SELECT문에 DEPARTMENT_ID , 다중행 함수만 출력이 가능하고 

 

 

 

다른 카테고리를 출력하려하면 에러가 난다.

 

*distinct 와 group by의 차이점

DISTINCT	GROUP BY
중복이 되는 데이터를 삭제시켜준다.
컬럼의 중복을 제거 조회	중복되는 항목들을 하나의 항목으로 묶어 중복을 체크
중복을 제거해야할 경우 사용한다.	데이터를 그룹핑 해서 그 결과를 가져오는 경우 사용한다
	집계 함수와 짝을 이루어 사용할 수 있다.

 

만약 sum의 함수를 사용하면서 distinct를 사용한다면

 

 

오류가 난다.

단순 중복제거이기때문에 부서별 연봉 총 합을 알 수 없기 때문

 

 

group by를 사용하여 그룹핑 한 후 sum 으로 더해준다.

 

▶having 절

 

GROUP BY절에 조건을 줄 때 사용한다.

 

HAVING 절은 SELECT문에 GROUP BY절이 존재할 때만 사용할 수 있다. 그리고 GROUP BY 절을 통해 그룹화된 결과 값의 범위를 제한하는데 사용한다.

 

예를들어 각 부서의 인원이 10명 이상인 사람들의 부서값을 알고싶을 때 GROUP BY 절과 HAVING 절을 작성하면 되는데 하나의 그룹안에서 통계나 데이터를 뽑아야하는데 그 데이터를 찾아내기위해 조건을 주어야할 때 HAVING절을 사용한다고 생각한다.

 

 

 

HAVING절은 WHERE와 비슷하게 지정한 조건식이 참인 결과만 출력하며 조건식을 지정하는 점에서 아주 비슷하다. 그러나 WHERE절은 출력 대상행을 제한하는 것이고 HAVING은 그룹화된 대상을 출력에서 제한하므로 쓰임새는 전혀 다르다. 그래서 WHERE 절과 HAVING절을 동시에 사용하는 것이 가능하고 WHERE절이 HAVING절보다 상위에 작성되어 먼저 작동된다.