230406 서른 네 번째 수업_SQL

BETWEEEN 날짜

• 입사일이 90/01/01 ~ 01/01/01

SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';

---

LIKE:

비교하려는 컬럼값이나 내가 제시한 특정 패턴에 만족하는 경우 조회

[표현법]

비교대상컬럼 LIKE '특정패턴'
: 특정패턴 제시는 '%', '_'를 와일드카드로 사용함

• '%': 0글자 이상
  EX) 비교대상컬럼 LIKE 'A%' => 비교대상의 컬럼값이 'A'로 시작하는 것 조회
  비교대상컬럼 LIKE '%A' => 비교대상의 컬럼값이 'A'로 끝나는 것 조회
  비교대상컬럼 LIKE '%A%' => 비교대상의 컬럼값이 'A'가 포함된 것 조회

• '_' : 1글자
  EX) 비교대상컬럼 LIKE '_A' => 비교대상의 컬럼값이 무조건 한 글자만(총 글자수 2개) 조회
  EX) 비교대상컬럼 LIKE '__A' => 비교대상의 컬럼값이 무조건 두 글자만(총 글자수 3개) 조회
  EX) 비교대상컬럼 LIKE '_A_' => 비교대상의 컬럼값이 앞,뒤로 한 글자만(총 글자수 3개) 조회

EMPLOYEE테이블에서 사원 이름 중 가운데가 '하'인 사원들의 사원명, 전화번호 조회

SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '\_하%'; --외자인 이름도 가능
-- WHERE EMP_NAME LIKE '\_하\_'; 외자 불가

EMPLOYEE테이블에서 이메일 중 4번째 값이 언더바인 사원의 사번,이름,이메일 조회

SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '\_\__\_%' ESCAPE '\';
--WHERE NOT EMAIL  LIKE '\_\__\_%' ESCAPE '\';
-- 와일드카드 기호와 조회할 패턴 문자가 같다면 
--데이터값으로 취급하고 싶은 값 앞에 나만의 와일드카드 아무 문자,숫자 제시하고 나만의 와일드카드를 ESCAPE로 등록

1. SELECT EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';

2. SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';

3. SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%정보%';

4. SELECT DEPT_ID, DEPT_TITLE
FROM DEPARTMENT
WHERE DEPT_TITLE LIKE '해외영업%';

---

IS NULL | IS NOT NULL:

컬럼값이 NULL이 있을 경우 NULL값에 사용되는 연산자

EMPLOYEE테이블에서 부서배치는 받지 않았지만 보너스는 받는 사원들의 이름,보너스,부서코드 조회

SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;

---

IN | NOT IN:

IN: 컬럼값이 내가 제시한 목록 중에 일치하는 값이 있는 것만 조회
NOT IN: 컬럼값이 내가 제시한 목록 중에 일치하는 값을 제외한 나머지만 조회

[표현법]

비교대상컬럼 IN('값1', '값2', '값3' ...)

EMPLOYEE테이블에서 부서코드가 'D6'이거나 'D8'이거나 'D5'인 부서원들의 이름, 부서코드, 급여 조회

SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D6','D8','D5');

위의 사원들이 아닌 그 외 사원들

SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE NOT IN ('D6','D8','D5');

---

연산자 우선순위:

1. ()
2. 산술연산자
3. 연결연산자(||)
4. 비교연산자
5. IS (NOT)NULL / LIKE / (NOT)IN
6. BETWEEN AND
7. NOT(논리연산자)
8. AND(논리연산자) 8,9 동등한 거 아님 주의!
9. OR(논리연산자)

EMPLOYEE테이블에서 직급코드가 J7이거나 J2인 사원들 중 급여가 200만원 이상인 사원의 모든 컬럼 조회

SELECT *
FROM EMPLOYEE
WHERE (JOB_CODE = 'J7' OR JOB_CODE = 'J2') AND SALARY >= 2000000;
--괄호를 넣어줘야 내가 원하는 우선순위대로 계산함

1. SELECT EMP_NAME, MANAGER_ID, DEPT_CODE
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL AND DEPT_CODE IS NULL;

2. SELECT EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE SALARY*12 >= 30000000 AND BONUS IS NULL;

3. SELECT EMP_ID, EMP_NAME, HIRE_DATE, DEPT_CODE
FROM EMPLOYEE
WHERE HIRE_DATE >= '95/01/01' AND DEPT_CODE IS NOT NULL;

4. SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE, BONUS
FROM EMPLOYEE
WHERE SALARY BETWEEN 2000000 AND 50000000 AND HIRE_DATE >= '01/01/01' AND BONUS IS NULL;

5. SELECT EMP_ID 사번, EMP_NAME 사원명, SALARY 급여, (BONUS*SALARY)*12 "보너스포함 연봉"
FROM EMPLOYEE
WHERE (SALARY*BONUS)*12 IS NOT NULL AND EMP_NAME LIKE '%하%';

---

ORDER BY:

SELECT문 가장 마지막 줄에 작성 뿐만 아니라 실행 순서도 맨 마지막

[표현법]

SELECT 조회할컬럼1, 조회할컬럼2, ...
FROM 테이블명
WHERE 조건식
ORDER BY 정렬기준 컬럼명 | 별칭 | 컬럼순번 [ASC|DESC][NULLS FIRST | NULLS LAST]

-ASC: 오름차순 정렬, 기본값
-DESC: 내림차순 정렬

• NULLS FIRST: 정렬하고자하는 컬럼값에 NULL이 있으면 해당 데이터를 맨 앞에 배치(DESC일 때의 기본값)
• NULLS LAST: 정렬하고자하는 컬럼값에 NULL이 있으면 해당 데이터를 맨 뒤에 배치(ASC일 때의 기본값)

정렬기준이 여러개일 때

보너스 기준으로 내림차순, 보너스 같으면 급여 오름차순

SELECT *
FROM EMPLOYEE
ORDER BY BONUS DESC, SALARY;

전 사원의 연봉(보너스포함)별 내림차순 정렬 NULL값은 맨 마지막에 나오도록 조회

SELECT EMP_NAME, (SALARY\*(1+BONUS))\*12 "보너스포함 연봉"
FROM EMPLOYEE
ORDER BY (SALARY\*(1+BONUS))*12 DESC NULLS LAST;

---

함수(function):

전달된 컬럼값을 읽어들여 함수를 실행한 결과 반환

• 단일행 함수: n개의 값을 읽어들여 n개의 결과값 반환(매 행마다 함수 실행)
• 그룹 함수: n개의 값을 읽어들여 1개의 결과값 반환(그룹별로 함수 실행)
  

SELECT절에 단일행 함수와 그룹함수를 함께 사용할 수 없음

함수식을 기술할 수 있는 위치: SELECT절, WHERE절, ORDER BY절, HAVING절

<지금부터는 모두 단일행 함수 소개>

문자 처리 함수

1. LENGTH | LENGTHB:

LENGTH(컬럼|'문자열'): 해당 문자열의 글자수 반환
LENGTHB(컬럼|'문자열'): 해당 문자열의 BYTE수 반환

• 한글: 오라클XE버전 => 1글자당 3BYTE (ㄱ,ㅏ,김 모두 1글자로 인식)
  오라클EE버전 => 1글자당 2BYTE
• 그 외: 1글자당 1BYTE

SELECT LENGTH('오라클'), LENGTHB('오라클')
FROM DUAL; --오라클에서 제공하는 가상테이블

SELECT LENGTH('ㅋㅋ'), LENGTHB('ㅋㅋ')
FROM DUAL;

2. INSTR:

문자열로부터 특정 문자의 시작위치(INDEX)를 찾아서 반환(반환형: NUMBER)
★ORACLE에서 INDEX는 1부터 시작, 찾는 문자가 없을 땐 0 반환

INSTR(컬럼|'문자열', '찾을 문자', [찾을 위치의 시작값, [순번]])
-찾을 위치의 시작값
1 : 앞에서부터 찾기(기본값)
-1: 뒤에서부터 찾기

SELECT INSTR('JAVASCRIPTJAVAORACLE', 'A') FROM DUAL; -- 2반환
SELECT INSTR('JAVASCRIPTJAVAORACLE', 'A', -1) FROM DUAL; -- 2반환
SELECT INSTR('JAVASCRIPTJAVAORACLE', 'A', 1, 3) FROM DUAL; -- 17반환
SELECT INSTR('JAVASCRIPTJAVAORACLE', 'A', -1, 2) FROM DUAL; --14반환

EMPLOYEE테이블에서 EMAIL, EMAIL '_'의 인덱스값, '@'의 인덱스값

SELECT EMAIL, INSTR( EMAIL, '_') "_위치", INSTR( EMAIL, '@') "@위치" 
FROM EMPLOYEE;

3. SUBSTR:

문자열에서 특정 문자열을 추출하여 반환(반환형: CHARACTER)

SUBSTR(컬럼|'문자열', POSITION, [LENGTH])
-POSITION: 문자열을 추출할 시작위치 INDEX
-LENGTH: 추출할 문자 개수(생략시 마지막까지 추출)

SELECT SUBSTR('ORACLEHTMLCSS', 7) FROM DUAL; -- HTMLCSS 반환
SELECT SUBSTR('ORACLEHTMLCSS', 7,4) FROM DUAL; --글자 4개만해서 HTML만 추출
SELECT SUBSTR('ORACLEHTMLCSS', 1, 6) FROM DUAL; --ORACLE만 추출
SELECT SUBSTR('ORACLEHTMLCSSRE', -9, 4) FROM DUAL; --거꾸로 시작하여 HTML추출

EMPLOYEE테이블에서 여성 사원들의 사번, 사원명, 성별 조회

SELECT EMP_ID 사번, EMP_NAME 이름, SUBSTR(EMP_NO, 8,1) 성별
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8,1) = '2' OR SUBSTR(EMP_NO, 8,1) = '4';
--SUBSTR이 CHAR로 반환하기 때문에 홑따옴표 써야됨

EMPLOYEE테이블에서 남성 사원들의 사번, 사원명, 성별 조회(IN구문으로)

SELECT EMP_ID 사번, EMP_NAME 이름, SUBSTR(EMP_NO, 8,1) 성별
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8,1) IN('1','3')
ORDER BY EMP_NAME;

EMPLOYEE테이블에서 아이디(이메일)만 추출하여 사원명, 이메일, 아이디 조회

SELECT EMP_NAME 이름, EMAIL 이메일, SUBSTR(EMAIL, 1, INSTR(EMAIL,'@')-1) 아이디
FROM EMPLOYEE;

4. LPAD | RPAD:

문자열을 조회할 때 통일감있게 조회하고자 할 때 사용(반환형: CHARACTER)
문자열에 덧붙이고자하는 문자를 왼쪽 또는 오른쪽에 덧붙여서 최종 N길이만큼의 문자열 반환
JAVA의 PRINTF 용법이랑 비슷

LPAD | RPAD('문자열', 최종적으로 반환할 문자의 길이, [덧붙이고자하는 문자(생략시 공백])

주민번호 971125-1** 이렇게 조회

SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,8), 14, '*')
FROM EMPLOYEE;

위에 거 연결연산자로 더 간결하게 표현

SELECT EMP_NAME, SUBSTR(EMP_NO,1,8) || '******'
FROM EMPLOYEE;

5. LTRM | RTRIM / TRIM

• LTRM | RTRIM: 문장열에서 특정 문자를 제거한 나머지 반환(반환형: CHARACTER)
• TRIM: 문자열 앞/뒤 양쪽에 있는 지정한 문자들을 제거한 나머지 문자열 반환
  [표현법]

  LTRIM | RTRIM('문자열'|컬럼, [제거할 문자열])
  TRIM([LEADING|TRAILNG|BOTH(기본값)]제거할 문자열 FROM '문자열'|컬럼)

-LEADING: 왼쪽 제거 = LTRIM
-TRAILING: 오른쪽 제거 = RTRIM

문자열의 왼쪽 혹은 오른쪽을 제거할 문자들을 찾아 제거한 나머지 문자열을 반환
제거할 문자열 생략시 기본값 공백

SELECT LTRIM('     K H     ') || '정보교육원' FROM DUAL; --K H     정보교육원 반환
SELECT RTRIM('     K H     ') || '정보교육원' FROM DUAL; --     K H정보교육원 반환

SELECT LTRIM('JAVAJAVASCRIPTJSP','JAVA') FROM DUAL; --SCRIPTJSP 반환
SELECT LTRIM('BACAABCFDSCA', 'ABC') FROM DUAL; --FDSCA 반환
--단어단위가 아니라 글자단위로 제거하고 다른 글자가 나오는 부분부터 제거 안 됨
SELECT LTRIM('12845KIM92835', '0123456789') FROM DUAL; --KIM92835 반환

SELECT RTRIM('JAVAJAVASCRIPTJSP','JAVA') FROM DUAL; --그대로 반환
SELECT RTRIM('BACAABCFDSCA', 'ABC') FROM DUAL; --CA만 제거 
SELECT RTRIM('12845KIM92835', '0123456789') FROM DUAL; --12845KIM 반환

SELECT TRIM('     K H     ') || '정보교육원' FROM DUAL; --K H정보교육원 반환
SELECT TRIM('A' FROM 'AAAJAVASCRIPTAAA') FROM DUAL; --JACASCRIPT반환

SELECT TRIM(LEADING 'A' FROM 'AAAJAVASCRIPTAAA') FROM DUAL; --JAVASCRIPTAAA 반환
SELECT TRIM(TRAILING 'A' FROM 'AAAJAVASCRIPTAAA') FROM DUAL; --AAAJAVASCRIPT 반환

6. LOWER | UPPER |INITCAP(앞글자만 대문자)

LOWER | UPPER |INITCAP('문자열')

SELECT LOWER('Java Javascript Oracle') FROM DUAL;
SELECT UPPER('Java Javascript Oracle') FROM DUAL;
SELECT INITCAP('java javascript oracle') FROM DUAL;

7. CONCAT:

문자열 2개만을 전달받아 하나로 합친 결과 반환

CONCAT('문자열'|컬럼1 ,'문자열'|컬럼2)

SELECT CONCAT('Oracle', '오라클') FROM DUAL;
SELECT 'Oracle' || '오라클' FROM DUAL;

--SELECT CONCAT('Oracle', '오라클', '010-1234-5678') FROM DUAL; --인수의 개수 오류
SELECT 'Oracle' || '오라클' || '010-1234-5678' FROM DUAL; --개수 무상관

8. REPLACE:

기존 문자를 새 문자로 바꿈

REPLACE('문자열'|컬럼, '기존문자열', '바꿀문자열')

SELECT REPLACE(EMAIL, 'kh.or.kr', 'naver.com')
FROM EMPLOYEE;