국비지원학원 1,2일 차 정리 / ORACLE

사용하는 프로그램 : Oracle 11g 버전

첫 시작 윈도우 cmd 창의 sqlplus 실행하여 시작

 

cmd 창의 타이핑은 보기 어렵기 때문에 GUI 환경의 Oracle Developer 라는 프로그램이 있음

 

연습 계정은 기본으로 있는 SCOTT / tiger 으로 사용

 

Developer 에서 -- 표시가 주석 처리

 

emp 테이블에 들어있는 데이터 확인 방법 

SELECT * FROM emp;

-- emp 테이블의 모든 열을 SELECT 하여 출력 하는 명령어

 

DISTINCT 명령어 : 출력 할 값 중 중복된 값 제거

 

SELECT 할 때 열의 이름에 별칭을 정할 수 있음, 너무 길면 가독성이 안좋아짐

ALIAS 라는 뜻의 AS 명령어

SELECT MAX(SAL) AS MAX_SAL -- 원하는 이름으로 열 이름 변경 가능

 

WHERE 명령어 : 불러오는 테이블에서 원하는 조건을 걸어 조건에 부합하는 데이터만 출력 가능

SELECT * FROM EMP WHERE SAL > 3000; -- 3000을 초과하는 값을 가진 행만 출력

 

Oracle 에서의 비교 연산자 사용 방법

A = B : A 와 B 가 같다

A != B, A <> B, A ^= B : A 와 B 가 같지 않다

 

OR 연산자 : OR 연산자를 통해 여러 개의 조건을 걸 수 있음, OR 은 조건 중 하나라도 해당하면 출력

SELECT * FROM EMP
WHERE JOB = 'ANALYST' OR JOB = 'SALESMAN';
-- JOB 의 값이 ANALYST 혹은 SALESMAN 인 값 출력

 

OR 연산자는 모든 조건을 다 적기 어렵기 때문에 IN 연산자를 사용

 

WHERE [열 이름] IN (조건1, 조건2, ...) : 여러 개의 조건을 괄호 안에 압축하여 정리, OR 연산자와 같은 역할

WHERE JOB IN ('ANALYST', 'SALESMAN', 'CLERK')

 

AND 연산자 : WHERE 절에 여러 조건에 모두 부합하는 데이터를 찾기 위해 사용

WHERE SAL < 2000 AND 1000 < SAL
-- SAL 값이 1,000 보다 크고 2,000 보다 작은 값을 조회 BETWEEN 연산자로 압축 가능

 

BETWEEN 연산자 : 지정된 열의 데이터 중 A 이상 B 이하 값에 해당하는 데이터만 출력

WHERE [열 이름] BETWEEN A AND B
WHERE SAL BETWEEN 1000 AND 2000
WHERE SAL >= 1000 AND SAL <= 2000

위의 예시와 같이 1,000 이상 2,000 이하 값 조회, 비교 연산자에서는 <=, >= 등과 같음

 

LIKE 연산자 : 데이터 중 원하는 값이 포함되어 있는지 확인 후 출력

WHERE ENAME LIKE 'S%' -- S 로 시작하는 ENAME 값 조회
WHERE ENAME LIKE '%S" -- S 로 끝나는 ENAME 값 조회
WHERE ENAME LIKE '_A%' -- ENAME 값 중 A 라는 글자가 두번째 글자인 값 조회, ex) JAMES
_ (UnderScore 가 글자 1 글자를 의미)

 

NOT 연산자 : 연산자 중 ~~ 가 아닌 것을 조회하고 싶을 때 사용

WHERE DEPTNO NOT IN (10,20) -- DEPTNO 가 10, 20 이 아닌 값 조회
WHERE ENAME NOT LIKE 'S%' -- ENAME 이 S 로 시작하지 않는 값 조회

 

SUM, AVG, MAX, MIN 명령어

 

SUM() : 괄호 안 열의 값을 모두 더하여 출력  -- 출력 시 한 행으로 해서 출력

SELECT SUM(SAL) FROM EMP -- EMP 테이블의 SAL 값을 모두 더하여 출력

 

AVG() : 괄호 안 열의 값의 평균을 구함

 

SELECT AVG(SAL) FROM EMP -- EMP 테이블 중 SAL 값의 평균을 구함

 

MAX(), MIN() : 괄호 안 열의 최댓값과 최솟값을 구함

SELECT MAX(SAL), MIN(SAL) FROM EMP -- EMP 테이블 중 SAL 값의 최댓값과 최솟값을 구함

 

COUNT() : 해당하는 행의 갯수를 카운트함

SELECT COUNT(*) FROM EMP -- EMP 테이블의 행 갯수 카운트하여 출력

 

SELECT ENAME, SUM(SAL) FROM EMP : EMP 테이블의 ENAME 값과 SAL 값을 모두 더하여 출력하는 명령어이나

ENAME 은 여러 행으로 출력, SUM(SAL) 은 한 행으로 출력되기 때문에 오류 발생

 

Oracle 의 자료형

VARCHAR2 - 가변형 문자열

NUMBER - 숫자

DATE - 날짜 형식

 

ALTER - 변경 명령어

CONN - Connect 명령어

SYS - DB 관리자, 슈퍼 유저, DB 생성과 삭제 가능

SYSTEM - DB 생성과 삭제 불가능

 

ORDER BY - 출력하는 데이터를 기준에 맞게 정렬

ORDER BY DEPTNO, ENAME : DEPTNO 기준으로 정렬 후 같은 DEPTNO 중에서 ENAME 으로 정렬

ORDER BY 명령어 뒤에 아무것도 적지 않거나 ASC 를 적으면 오름차순, DESC 를 적으면 내림차순으로 정렬

 

ROUND 연산자 : 원하는 위치에서 반올림, 기본은 소숫점 첫째자리에서 반올림하여 정수만 출력

SELECT ROUND(1234.5678, 3) FROM DUAL; 
-- 1234.5678 중 소숫점 넷째자리에서 반올림하여 셋째자리 까지만 출력 ex) 1234.568

 

CEIL : 소숫점자리 올림 처리

SELECT CEIL(1234.123) FROM DUAL; -- 무조건 올림 처리하여 1235 출력

 

FLOOR : 소숫점자리 버림 처리

SELECT FLOOR(1234.999) FROM DUAL; -- 무조건 버림 처리하여 1234 출력

 

TRUNC : 원하는 위치 자리수까지 표현 후 나머지 버림

SELECT TRUNC(1234.5678, 2) FROM DUAL; -- 1234.56 출력 후 나머지 버림, 기본은 소숫점 전체 버림

 

MOD(숫자 1, 숫자 2) : 숫자 1을 숫자 2로 나눈 후 나머지 출력

SELECT MOD(11, 2) FROM DUAL; -- 11을 2로 나눈 나머지 1 출력

 

UPPER(column) : 데이터를 대문자로 변환하여 출력

SELECT UPPER('oracle') FROM DUAL; -- ORACLE 출력

 

LOWER(column) : 데이터를 소문자로 변환하여 출력

SELECT LOWER('ORACLE') FROM DUAL; -- oracle 출력

 

INITCAP(column) : 데이터의 첫 글자만 대문자, 나머지는 소문자로 변환하여 출력

SELECT INITCAP('ORACLE') FROM DUAL; -- Oracle 출력

 

LENGTH(column) : 데이터의 길이를 출력하는 명령어

ex) LENGTH('한글') - 괄호 안의 문자열 길이 출력
LENGTHB('한글') - 괄호 안의 문자열 바이트 수 출력

LENGTH('한글') -- 괄호 안의 문자열 길이 출력
LENGTHB('한글') -- 괄호 안의 문자열 바이트 수 출력
SELECT LENGTH('ORACLE'), LENGTH('oracle') FROM DUAL; -- 둘 다 6 출력
SELECT LENGTHB('ORACLE'), LENGTHB('oracle') FROM DUAL; -- 둘 다 6 출력
-- 한글은 글씨 속성에 따라 바이트가 달라짐

 

SELECT * FROM DUAL; : DUAL 이라고 하는 가상의 테이블을 생성하여 단일 결과를 확인할 때 사용

SELECT 1 + 1 FROM DUAL; -- 1 + 1 의 결과 출력

 

SYSDATE : 시스템의 날짜 출력, + 와 - 기호로 날짜 설정 가능

SELECT SYSDATE - 1 FROM DUAL; 
-- 연산 명령어로 시스템 날짜에서 하루 빠진 전일 날짜 출력 가능. 단위는 일

 

ADD_MONTHS : DATE 에 원하는 달만큼 더하여 출력

SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL; -- 현재 날짜에 3달을 더하여 출력

 

MONTHS_BETWEEN : 날짜 간 얼마나 차이가 나는지 출력, 큰 날짜가 앞으로 와야 정수로 출력됨

SELECT MONTHS_BETWEEN('24/01/01', SYSDATE) FROM DUAL; 
-- 24년 1월 1일과 시스템 날짜와의 차이 출력

 

날짜의 ROUND 처리 방법

ROUND(SYSDATE, 'CC;) -- 1세기 기준으로 반올림, 2051/01/01 기준으로 반올림 시 2101/01/01 출력, 이전은 2001/01/01 출력
ROUND(SYSDATE, 'YYYY') -- 해당 연도의 7월 1일 기준으로 반올림 처리
ROUND(SYSDATE, 'Q') -- 해당 분기의 두번째 달 16일 기준으로 반올림 처리
SELECT ROUND(SYSDATE, 'YYYY') FROM DUAL; -- 시스템 날짜를 기준으로 반올림하여 출력

 

자동 형 변환

SELECT EMPNO + '500' FROM EMP; 
-- '500' 이라는 문자열을 EMPNO 에 더할 시 자동 형 변환을 통해 NUMBER로 변환
SELECT '1300' + '1500' FROM DUAL; 
-- '1300', '1500' 문자열 2개를 더할 시 자동 숫자로 변환하여 2800 출력

 

TO_CHAR : 해당 값을 문자열 형식으로 변환하여 출력

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL; 
-- 시스템 시간을 연도/월/일 24시간 형식인 시간/분/초 의 문자열 형식으로 변환하여 출력

 

TO_DATE : 해당 값을 DATE 형식으로 변환

SELECT TO_DATE('2023-07-14', 'YYYY/MM/DD') FROM DUAL; 
-- '2023-07-14' 문자열을 지정한 연도/월/일 DATE 형식으로 변환하여 출력

 

날짜끼리의 연산도 가능

SELECT TO_DATE('2023-12-25', YYYY-MM-DD) - SYSDATE FROM DUAL; 
-- 현재 날짜에서 23년 크리스마스까지 남은 일 수 구하기

 

NULL 값 연산에 포함하기

 

NVL(NULL이 아닐 경우 출력할 값, NULL일 경우 출력할 값)  : NULL 값일 경우와 아닐 경우 어떤 값을 지정할 지 정할 수 있음

SELECT NVL(COMM,0) FROM EMP; 
-- COMM 값이 NULL 이 아닐 경우 기존 COMM 값 그대로 출력, NULL 일 경우 0 을 넣어 출력

 

NVL2(column, NULL일 경우 출력할 값, NULL 이 아닐 경우 출력할 값) : 확인 할 column을 지정하여 NULL 일 경우와 아닐 경우에 지정할 값을 넣어 출력할 수 있음

SELECT NVL2(COMM, SAL * 12 + COMM, SAL * 12) FROM EMP; -- 연봉 계산 예시
COMM 열의 값이 NULL 이 아닐 경우 SAL * 12 + COMM 출력
COMM 열의 값이 NULL 일 경우 SAL * 12 출력

 

GROUP BY : 중복되는 값을 기준으로 삼아 그룹 별 조건을 만족하는 값을 출력

SELECT DEPTNO, MAX(SAL) FROM EMP
GROUP BY DEPTNO; -- DEPTNO 를 기준으로 그룹 생성, 그룹 별 최대 SAL 값 출력

 

HAVING : GROUP BY 의 조건식, GROUP BY 의 결과값에 조건을 걸어 만족하는 결과만 출력 가능

SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL) > 4000; 
-- DEPTNO 를 기준으로 GROUP 을 나누어 최댓값이 4000 을 초과하는 행만 출력

 

WHERE 과 HAVING 의 차이점

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 2000;
-- DEPTNO 그룹의 평균 값이 2,000 을 넘는 행만 출력

 

SELECT DEPTNO, AVG(SAL) 
FROM EMP
WHERE SAL > 2000
GROUP BY DEPTNO;
-- 2,000 이 넘는 값만 골라 DEPTNO 그룹에 포함시켜 평균 구하기

 

WHERE 은 GROUP 을 나눌 때 그룹에 포함 할 행을 골라내는 작업

HAVING 은 GROUP 을 나눈 후 값이 조건에 맞을 경우 출력