[2일차] SELECT 마무리와 FUNTION 기능들

오늘은 정리할게 많다. 대부분 Java 의 기능과 비슷하기 때문에 빨리빨리 보고 외우면 된다 !

간단한 것들은 실행결과를 보지 않고 바로바로 넘어가도록 하겠다 !

어제에 이어서 정리해보면

 <AND, OR 연산자>
    조건을 여러개 연결할 때 사용한다
    [표현법]
    조건 A AND 조건 B -> 조건 A와 조건B 모두 만족하는 값만 가지고온다
    조건 A OR 조건 B -> 조건A와 조건B중 하나라도 만족하는 값은 가지고 온다.
    
    <BETWEEN AND>
    조건식에 사용되는 구문
    몇 이상 몇 이하인 범위에 대한 조건을 제시할 때 사용하는 연산자(이상, 이하만 가능)
    [표현법]
    비교대상 컬럼 BETWEEN 하한값 AND 상한값

 

자바와 다른점은 자바는 AND, OR순으로 && , || 을 사용했지만 SQL에서는 다르다.

SQL은 AND, OR 이라고 영어로 직접 표기한다.

또 BETWEEN을 사용해도 된다.

--급여가 350만원 이상 600만원 이하인 모든 사원의 사원명, 사번, 급여조회
SELECT EMP_NAME, EMP_ID, SALARY
FROM EMPLOYEE
--WHERE SALARY > 3500000 AND SALARY <6000000;
WHERE SALARY BETWEEN 3500000 AND 6000000;

 

NOT 연산자도 사용이 가능하다.

 

--급여가 350만원ㅇ 미만, 600만원 초과인 모든 사원의 사원명 사번 급여 조회
SELECT EMP_NAME, EMP_ID, SALARY
FROM EMPLOYEE
--WHERE SALARY < 3500000 OR SALARY > 60000000;
WHERE NOT SALARY BETWEEN 3500000 AND 6000000; --NOT 연산자 사용 가능

 

 

다음으로 LIKE이다. 배울 때 느꼈지만 자주 사용할 것 같다.

 <LIKE>
    비교하고자하는 컬럼값이 내가 제시한 특정 패턴에 만족할 경우에 조회
    
    [표현법]
    비교할 대상 컬럼 LIKE '특정패턴';
    
    특정패턴을 제시할 때 와일드카드라는 특정패턴이 정의되어있다.
    '%" : 포함문자 검색 (0글자 이상 전부 조회)
    Ex) 비교할대상컬럼 LIKE '문자%' : 비교대상컬럼값 중에서 해당문자로 시작하는 값들만 조회
        비교할대상컬럼 LIKE '%문자' : 비교대상컬럼값 중에서 해당문자로 끝나는 값들만 조회
        비교할대상컬럼 LIKE '%문자%' : 비교대상컬럼값 중에서 해당문자가 포함된 값 조회
        
    '_' : 1글자를 대체하는 검색
    EX) 비교할대상컬럼 LIKE '_문자' : 비교대상컬럼값 문자앞에 아무글자나 N글자가 있는 값을 조회
        비교할대상컬럼 LIKE '문자_' : 비교대상컬럼값 문자뒤에 아무글자나 N글자가 있는 값을 조회
        비교할대상컬럼 LIKE '_문자_' : 비교대상컬럼값 문자앞뒤에 아무글자나 N글자가 있는 값을 조회

 

LIKE에는 와일드 카드라는게 있는데 % 와 _ 이다. 이것을 잘 사용하면 무궁무진하게 응용 할 수 있다.

와일드카드 %의 사용법

--사원들중에 성이 전씨인 사원들의  사원명, 급여, 입사일, 조회
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '전%';

--사원들중에서 이름에 '하'라는 글자가 포함된 사원의 전화번호 목록을 조회
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';

 

 

 

와일드카드 _의 사용법

--사원들중에서 이름에 중간글자가 '하'인 사원의 이름 전화번호 목록 조회
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '_하_'; --앞에 무조껀 한 글자, 뒤에 무조껀 한 글자 있어야함.

--전화번호의 3번째 자리가 1인 사원들의 사번, 사원명, 전화번호 조회
SELECT EMP_ID, EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '__1%'; --섞어서도 가능

--이메일 중 _의 앞글자가 3글자인 사원들의 사번, 이름, 이메일 조회
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
--WHERE EMAIL LIKE '____%' 와일드카드문자 때문에 정상출력이 되지 않는다.
--와일드 카드 문자와 일반문자의 구분이 필요하다.
--데이터값으로 취급하고 싶은 와일드카드 문자 앞에 나만의 탈출문자를 제시해서 탈출시켜주면된다.
--ESCAPE OPTION을 등록해서 사용해야한다
WHERE EMAIL LIKE  '___\_%' ESCAPE '\';

--위 사원들이 아닌 그 외의 사원들을 조회하고 싶다 컬럼동일
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE NOT EMAIL LIKE  '___\_%' ESCAPE '\';

 

 

다음은 <IN> 이다. 

<IN>
WHERE 절에서 비교대상 컬럼값이 내가 제시한 목록중에 일치하는 값이 있는지 검사

[표현법]
비교대상컬럼  IN ('값1', '값2' ...);

 

 

보통 OR 을 쓰는 코드를 줄이는데 효율적이다.

--부서코드가 D6이거나 D8이거나 D5인 부서원들의 이름, 부서코드, 급여
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
--WHERE DEPT_CODE = 'D6' OR DEPT_CODE = 'D8' OR DEPT_CODE='D5';
WHERE DEPT_CODE IN ('D6','D8','D5');

주석되어 있는 곳 --WHERE을 보면 OR로 작성되어있고 그 밑은 IN으로 작성되어 있는데

코드가 확연하게 줄어든 것을 볼 수 있다.

 

 

다음은 NULL검사이다. Java에서는 null을 비교할 때 ==null 으로 했지만 SQL은 다르다.

<IS NULL & IS NOT NULL>
컬럼값에 NULL이 있을 경우 NULL값을 비교하기 위해서는 위의 연산자를 사용해야한다.

 

그냥 딱 봐도 직관적으로 이해가 쉽다.

--보너스를 받지 않은 사원들의 사번, 이름, 급여, 보너스를 조회해보자
SELECT EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE BONUS IS NULL ;

--부서배치를 아직 받지 않았고 보너스를 받은 사원들의 이름, 보너스, 부서코드를 조회
SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;

 

 

그리고 자바에서도 있었던 연산자의 우선순위인데 이것은 우리가 일상에서 쓰는 것과 비슷하고 ( ) 괄호 처리만 잘해준다면 문제 없다.

 

 <연산자 우선순위>
    1. 산술연사자
    2. 연결연산자 || 이어버리는거
    3. 비교연산자
    4. IS NULL / LIKE / IN
    5. BETWEEN A AND B
    6. NOT
    7. AND
    8. OR

 

마지막으로 간단한 문제 하나를 보면서 정리해보겠다.

 

--5. 보너스를 포함하는 연봉이 NULL이 아니고 이름에 '하'가 포함된 사원들의 사번, 사원명, 급여, 보너스 포함 연봉을 조회
SELECT EMP_ID, EMP_NAME, SALARY, (SALARY +(SALARY*BONUS)) * 12 AS 연봉, '원' AS 단위
FROM EMPLOYEE
WHERE ((SALARY +(SALARY*BONUS)) * 12) IS NOT NULL AND EMP_NAME LIKE '%하%';

 

 

마지막으로 SELECT에서 배울 ORDER BY의 역할은 정렬이다.

JAVA에서는 Collections.sort 나 list.sort 처럼 정렬해주는 기능을 가지고 있는데 개인적으로 SQL의 정렬이

훨씬 편하게 잘 되어있는 것 같다.

 

 <ORDER BY 절>
    SELECT문 가장 마지막 줄에 작성, 실행 순서 또한 가장 마지막에 실행한다
    
    [표현법]
    SELECT 조회할 컬럼....
    FROM 조회할 테이블
    WHERE 조건식
    ORDER BY 정렬기준이 될 컬럼명 | 별칭 | 컬럼순번 [ASC | DESC] [NULL FIRST | NULL LAST]
    
    ASC : 오름차순  작->큰
    DESC : 내림차순 큰->작
    
    NULL은 기본적으로 가장 큰 값으로 분류된다.
    NULLS FIRST : 정렬하고자하는 컬럼 값에 NULL이 있을 경우 해당데이터 맨 앞에 배치(DESC일 때 기본값)
    NULLS LAST : 정렬하고자하나는 컬럼 값에 NULL이 있을 경우 해당데이터 맨 뒤에 배치(ASC일 때 기본값)

 

 

이번에도 말로만 보면 어려우니 코드와 비교해가면서 보도록 하겠다.

 

SELECT *
FROM EMPLOYEE
ORDER BY BONUS; --기본값이 오름차순, NULL LAST가 기본값

SELECT *
FROM EMPLOYEE
ORDER BY BONUS NULLS FIRST; --NULL 앞으로 땡기기

SELECT *
FROM EMPLOYEE
ORDER BY BONUS DESC; --내림차순

SELECT *
FROM EMPLOYEE
ORDER BY BONUS DESC, SALARY ASC; --BONUS의 값이 똑같다면 SALARY로 오름차순 정렬한다.

--전 사원의 사원명, 연봉(보너스제외) 조회 (이 때 연봉별 내림차순 정렬)
SELECT EMP_NAME, SALARY * 12 AS 연봉
FROM EMPLOYEE
ORDER BY 연봉 DESC; --순번사용가능 오라클은 전부 1부터 시작 (근데 잘 안씀)

 

이것으로 SELECT의 기능들을 살펴보았다. 어렵진 않지만 확실하게 쌓이면 쌓일수록 헷갈릴 수 있다는 생각이든다!

 

SQL 함수에 대하여 

여기서부터는 SQL 함수에 대해 다뤄보도록 하겠다.

우리가 제일 먼저 배울 함수는 ORDER BY 이다!

 

그럼 SQL에서 함수란 무엇일까?

자바에서는 메소드라불리고 현실세계에서는 공식정도로 볼 수 있을 것 같다.

 

함수란 어떤 기능을 수행하기 위해 만들어진 틀 같은 것이며 입력값이 나오면 일정한 공식을 거쳐서 출력값이 나오게된다.

 

SQL의 함수의 종류에 대해서 크게 2가지로 나눠보자

<함수 FUNTION>
    전달된 컬럼값을 읽어들여서 함수를 실행한 결과를 반환
    
    단일행 함수 : N개의 값을 읽어들여서 N개의 결과값을 리턴한다(매 행 마다 함수 실행 결과를 반환)
    그룹  함수 : N개의 값을 읽어들여서 1개의 결과값을 리턴한다.(그룹을 지어서 그룹별로 함수 실형결과를 반환)
    
    >>SELECT절에 단일행 함수와 그룹함수를 함께 사용하지 못함
    왜? 결과 행의 갯수가 다르기 때문에
    
    >>함수식을 기술할 수 있는 위치 : SELECT절 WHERE절 ORDER BY절 GROUP BY절 HAVING절

 

크게 단일행 함수, 그룹 함수로 나뉘는데 일단 SQL에서 행은 1개의 데이터를 의미한다. 1개의 데이터는 SQL 테이블에서

1행을 의미한다. 즉 테이블에 있는 행의 갯수만큼 실행되는게 단일행 함수이다.

그룹함수는 모든 행들의 데이터를 모아서 딱 한번 실행해서 1개의 값을 내는게 그룹 함수이다.

 

단일 함수

<문자 처리 함수>
    
    *LENGTH(컬럼 | '문자열') : 해당 문자열의 글자 수를 반환
    *LENGTHB(컬럼 | '문자열') : 해당 문자열의 바이트 수를 반환
    
    '최' '나' 'ㄱ' 한글은 글자당 3BYTE
    영문자, 숫자, 특수문자 글자당 1BYTE

*한글은 차별 당해서 3BYTE가 아니라 그냥 글자 수가 많아서 3BYTE씩 먹는 것이다.

 

SELECT LENGTH ('오라클'), LENGTHB('오라클')
FROM DUAL; --> 결과는 3, 9

SELECT LENGTH('ORACLE'), LENGTHB('ORACLE')
FROM DUAL; --> 결과는 6, 6

SELECT EMP_NAME, LENGTH (EMP_NAME), LENGTHB(EMP_NAME)
      ,EMAIL, LENGTH(EMAIL), LENGTHB(EMAIL)
FROM EMPLOYEE;

*DUAL은 더미테이블으로써 우리가 원하는 값을 보고 싶을때 빈 테이블에 값을 넣어 불러오는 것이다.

 

마지막 쿼리의 결과

 

 

---------------------문자열 처리 함수--------------------

INSTR

*INSTR
문자열로부터 특정 문자의 시작위치를 찾아서 반환

INSTR(컬럼 | '문자열', '찾고자하는 문자', ['찾을 위치의 시작값', 순번])

 

 

SELECT INSTR('AABAACAABBAA','B')FROM DUAL; --앞쪽에 있는 첫 B는 3번째 위치에 있다고 나옴-> 결과는 NUMBER
-- 찾을 위치 시작값 : 1 순번 : 1 => 기본값
SELECT INSTR('AABAACAABBAA','B',1)FROM DUAL; --> 결과값 : 3
SELECT INSTR('AABAACAABBAA','B',-1)FROM DUAL; --> 결과값 : 10 (뒤에서부터 찾기만 읽을 때는 앞으로 읽어서 알려준다.)
SELECT INSTR('AABAACAABBAA','B',1,2)FROM DUAL; --> 결과값 : 9 (앞에서부터 찾고 2번째를 찾음)

 

 

SUBSTR (중요)

*SUBSTR / 자주쓰임
문자열에서 특정 문자열을 추출해서 반환

[표현법]
SUBSTR(STRING, POSITION, [LENGTH])
-STRING : 문자타입의 컬럼 | '문자열'
-POSITION : 문자열 추출할 시작위치의 값
-LENGTH : 추출할 문자 갯수(생략하면 끝까지)

 

감을 잡아보자 !

SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL; --> 결과 : THEMONEY
SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL; --> 결과 : ME
SELECT SUBSTR('SHOWMETHEMONEY', 1, 6) FROM DUAL; --> 결과 : SHOWME
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL; --> 결과 : THE

 

그럼 남자사원들만 구분하는 법은 무엇일까, 주민등록번호 뒷자리의 시작이1 또는 3을 찾아내면 된다.

--사원들중 남사원들만 조회
SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = 1 OR SUBSTR(EMP_NO,8,1) = 3
ORDER BY EMP_NAME;

남사원 조회의 쿼리 결과

 

--이메일 아이디 부분만 추출
--사원목록에서 사원명, 이메일, 아이디 조회
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL,1,INSTR(EMAIL, '@' )-1)
FROM EMPLOYEE;

그럼 같은 기능으로 아이디만 추출해보자

아이디만 추출하는 쿼리의 결과

 

 

LPAD / RPAD 다음으로 문자열을 조회할 때 통일감 있게 조회할 때 사용하는 기능이다.

*LPAD / RPAD

[표현법]
LPAD/RPAD(STRING, 최종적으로 반환할 문자의 길이,[덧붙이고자 하는 문자])

문자열에 덧붙이고자하는 문자를 오니쪽 또는 오른쪽에 붙여서 최종 N길이만큼 문자열을 반환한다.

 

 

SELECT EMP_NAME, LPAD(EMAIL,20,' ') --띄어쓰기가 기본 값, 오른쪽 정렬
FROM EMPLOYEE;

위 쿼리의 결과

 

 

SELECT EMP_NAME, LPAD(EMAIL,20,'#') -- #넣어서 #채워줌
FROM EMPLOYEE;

위 쿼리의 결과

 

 

SELECT EMP_NAME, RPAD(EMAIL,20,' ') --왼쪽정렬
FROM EMPLOYEE;

위 쿼리의 결과

 

--사원들의 사원명, 주민등록번호 조회(910524-1****** 형식으로)
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,8),14,'*')
FROM EMPLOYEE;

위 쿼리의 결과

 

 

LTRIM / RTRIM자바에서 사용했던 TRIM과 비슷한 함수가 있다. 기능도 공백제거로 거의 비슷하다.

*LTRIM / RTRIM
문자열에서 특정 문자를 제거한 나머지를 반환
LTRIM/RTRIM(STRING, [제거하고자하는 문자들])

문자열의 왼쪽 혹은 오른쪽에서 제거하고자하는 문자들을 찾아서 제거한 나머지 문자열을 반환

 

간단하게 기능을 봐보자

SELECT LTRIM('ACABACCKH','ABC') FROM DUAL;

위 쿼리의 결과

삭제할 문자가 'ABC'라 ABC 한 묶음으로 다닐 줄 알았는데 그게 아니였다. ABC 따로 따로 A B C 문자를 모두 지우는 것이다

LTRIM : 왼쪽부터 우리가 설정한 문자가 나오지 않을 때 까지 지움

RTRIM : 오른쪽부터 우리가 설정한 문자가 나오지 않을 때 까지 지움

 

SELECT LTRIM('    D  H') FROM DUAL; --앞에서부터 다른 문자가 나올 때 가지만 공백제거 (기본값)

위 쿼리의 결과

 

 

TRIM 당연히 기본 TRIM 도 있다.

 *TRIM
문자열의 앞/뒤/양쪽에 있는 지정한 문자들을 제거한 나머지 문자열 반환
TRIM([LEADING | TRAILING | BOTH] 제거하고자하는 문자열 FROM 문자열)

SELECT TRIM('     D      H     ') FROM DUAL; --양쪽에 있는 공백을 제거해준다 --> 결과 : D      H
SELECT TRIM('Z' FROM 'ZZZDHZZZ') FROM DUAL; --양쪽에 있는 특정 문자를 제거한다 --> 결과 : DH

 

 

 

LOWER / UPPER / INITCAP우리가 자바에서 사용했던 소문자->대문자, 대문자->소문자 기능도 있다.

 *LOWER / UPPER / INITCAP
    
LOWER : 다 소문자로 변경한 문자열 반환
UPPER : 다 대문자로 변경한 문자열 반환
INITCAP : 띄어쓰기 기준 첫 글자마자 대문자로 변경한 문자열 반환

SELECT LOWER('Welcome To my World') FROM DUAL; -->결과 : welcome to my world
SELECT UPPER('Welcome To my World') FROM DUAL; -->결과 : WELCOME TO MY WORLD
SELECT INITCAP('welcome to my world') FROM DUAL; -->결과 : Welcome To My World

 

 

CONCAT 문자열을 합치는 기능을 가진 함수

 *CONCAT
CONCAT(STRING1, STRING2)

SELECT CONCAT('가나다','ABC') FROM DUAL; --> 결과 : 가나다ABC
SELECT '가나다' || 'ABC' FROM DUAL; --> 결과 : 가나다ABC

우리가 저번에 배웠었던 || 도 문자열을 합치는 기능을 가지고 있다.

둘의 차이는 무엇일까

먼저 CONCAT은 두 개의 문자열만 합칠수 있다.

하지만 ||는 다중으로 합칠  수 있어서 ||가 더 선호된다.

 

REPLACE 특정문자열에서 특정부분을 다른 부분으로 교체

 *REPLACE
특정문자열에서 특정부분을 다른 부분으로 교체
REPLACE (문자열, 찾을 문자열, 변경할 문자열)

이것은 자바의 기능과 거의 동일하다.

 

SELECT EMAIL, REPLACE(EMAIL, 'KH.or.kr','gmail.com')
FROM EMPLOYEE;

위 쿼리를 실행하여 모든 EMAIL의 주소가 바뀐 모습

 

 

 

--------------------숫자 처리 함수--------------------

여기서부턴 문자열이 아닌 숫자를 처리하는 함수를 알아보겠다.

 

ABS

ABS
숫자의 절댓값을 구해주는 함수

SELECT ABS(-10), ABS(-6.32) FROM DUAL; --> 결과 : 10, 6.32

 

 

MOD

MOD
두 수를 나눈 나머지 값을 반환해주는 함수
MOD(NUMBER, NUMBER)

SELECT MOD(10, 3)FROM DUAL; -->결과 : 1
SELECT MOD(10.9, 3) FROM DUAL; --> 결과 : 1

 

 

ROUND

ROUND
반올림한 결과를 반환
ROUND(NUMBER, [위치])

SELECT ROUND(123.456) FROM DUAL; --기준이 소수점 첫 째 자리 (기본값 0)

위 쿼리의 결과

 

SELECT ROUND(123.456,-1) FROM DUAL; --음수가 커질수록 소수점에서 정수쪽으로 이동

 

위 쿼리의 결과

 

SELECT ROUND(123.456, 1) FROM DUAL; --양수가 커질수록 소수점 둘 째 셋 째 ... 쪽으로 이동

 

위 쿼리의 결과

 

마지막으로 오늘 배운 것을 종합하는 간단한 문제를 풀어보고 마무리 하겠다

 

QUIZ

JOB_CODE가 J7이거나 J6이면서 SALARY 값이 200만원 이상이고
BONUS가 있고 여자이며 이메일주소는 _앞에 3글자만 있는 사원의
이름, 주민등록번호, 직급코드, 부서코드, 급여, 보너스를 조회하고 싶다

 

SELECT EMP_NAME, EMP_NO, JOB_CODE, DEPT_CODE, SALARY, BONUS
FROM EMPLOYEE
WHERE (JOB_CODE ='J7' OR JOB_CODE='J6') AND SALARY > 2000000 AND BONUS IS NOT NULL
AND SUBSTR(EMP_NO,8,1) = 2 AND EMAIL LIKE '___\_%' ESCAPE '\';

 

 

 

 

 

 

 

오늘도 느꼈다.

그리 어렵지 않다. 아니 많이 쉬운편이다.

하지만 이게 모두 모아 기능을 사용해야할 때 헷갈리지 않으려면 복습을 열심히 해야겠다는 생각이 한 번 더 들었다.