[Oracle] 교육 2일차

강사 : 김호진 강사님

 Oracle의 기본 및 사용

Oracle의 기본 사항

오라클은 설치할 때 자동으로 환경변수를 설정해줌

최고 관리자 계정

• Windows : admin(administrator)
• MSSQL : sa
• Oracle : sys
• 기본 계정인 hr(human resource) 계정의 비밀번호는 lion, scott계정의 비밀번호는 tiger로 약속

오라클 연결

• SQL 프롬프트에서는 'connect 아이디/패스워드'
• 명령 프롬프트에서는 'sqlplus 아이디/패스워드'

※ 'connect'를 'conn'으로 줄여쓸 수 있음

※ 실무에서는 반드시 패스워드를 빼고 입력해줘야해(다음 결과에서 보안된 패스워드 입력)

오라클 설정

오라클은 한 번 설치하고 나면 그 서비스들이 오라클을 지워도 유지되기 때문에 컴퓨터가 무거워질 수 있기 때문에 오라클을 사용하지 않는 경우에는 Windows + R > services.msc > 'OracleServiceXE', 'OracleXETNSListener'를 중지시켜주면 오라클이 자동으로 실행되지 않음

Oracle의 사용

조회

• select로 시작되는 구문이 나오면 from부터 찾아라!
• DBA_... 테이블은 'SYS' 계정으로 접속할 경우에만 조회 가능

변경

• 데이터의 내용을 바꿀 때는 'update'
• 구조를 바꿀 때는 'alter'

제거

• 데이터의 내용을 제거할 때는 'delete'
• 구조적으로 제거할 때는 'drop'

추가

• 데이터의 내용을 추가할 때는 'insert'
• 구조적으로 추가할 때는 'add'

권한 설정

• GRANT : 권한 부여
• REVOKE : 권한 회수(박탈)

메모리와 COMMIT

• DB 운용에서 가장 중요한 것은 메모리이다! (메모리 절약 = 시간 절약 = 비용 절약)
• 'COMMIT'을 통해 디스크 상에 물리적으로 데이터 저장(COMMIT을 하지 않으면 데이터는 메모리 상에만 존재할 뿐이다.) ↔ 'ROLLBACK'
• DML('INSERT', 'UPDATE', 'DELETE') 구문 이외에는 AUTO COMMIT이다.

SELECT 문의 처리(PARSING) 순서

SELECT 문의 처리(PARSING) 순서는 꼭 외워두자!!!!

SELECT 컬럼명   -- ⑤

FROM 테이블명   -- ①

WHERE 조건절    -- ②

GROUP BY 절     -- ③

HAVING 절       -- ④

ORDER BY        -- ⑥

주의사항

• 오라클은 큰따옴표("")가 아닌 작은따옴표('')로 문자열 표시
• 덧셈 연산자를 문자열 결합 연산자로 사용할 수 없음(산술 연산자로만 사용)
• 덧셈 연산자는 피연산자들을 자동으로 숫자로 형 변환을 함(숫자가 아닐 경우에는 에러 발생)
• 'ORDER BY'로 수행하는 정렬은 부하가 많이 발생한다. (그때문에 실무에서는 사용을 제한하는 경우도 종종 있음)
• 전화번호, 주민번호, 학번, ... 의 데이터는 '숫자' 타입으로 저장하면 절대 안된다. (0으로 시작할 경우 정확한 값의 저장이 불가능)
• 'AS'(별칭)를 붙인다고 해서 원본 데이터의 컬럼명이 바뀌는 것은 아니다.

주요 자료형

1. 숫자형

NUMBER

NUMBER(3)

NUMBER(4,1)

2. 문자형

고정형 크기

CHAR

CHAR(10) -- 10 byte ' 졸려도참자'

CHAR(10) ← '서운성' 6BYTE 이지만 10BYTE 를 소모

가변형

VARCHAR2

VARCHAR2(10) -- 10 BYTE '졸리면자자'

VARCHAR2(10) ← '자면 안되나요?' 10BYTE 를 초과하므로 입력 불가.

VARCHAR2(10) ← '물먹자' 6BYTE 를 소모

3. 날짜형

DATE

Oracle SQL Developer

local_sys

주석문 처리

-- 1줄 주석문 처리

/*
여러줄
(다중행)
주석문
처리
*/

현재 접속한 사용자 계정 확인

--○ 현재 접속한 사용자 계정 확인
show user
--==>> USER이(가) "SYS"입니다.

SELECT USER
FROM DUAL;
--==>> SYS

관찰

--○ 관찰
SELECT 1+2
FROM DUAL;
--==>> 3

SELECT '아직 지루한 오라클 수업'
FROM DUAL;
--==>> 아직 지루한 오라클 수업

SELECT '계속 지루할지도 모르는 오라클 수업'
FROM DUAL;
--==>> 계속 지루할지도 모르는 오라클 수업

SELECT 1 + '2'
FROM DUAL;
--==>> 3

SELECT '2' + '3'
FROM DUAL;
--==>> 5

SELECT '2' + '32'
FROM DUAL;
--==>> 34

SELECT '잠들면' + '안되는데...'
FROM DUAL;
--==>>
/*
ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.
*/

현재 오라클 서버에 존재하는 사용자 계정 정보 조회

--○ 현재 오라클 서버에 존재하는 사용자 계정 정보 조회
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS;
--==>>
/*
SYS	            OPEN
SYSTEM              OPEN
ANONYMOUS           OPEN
HR                  OPEN
APEX_PUBLIC_USER    LOCKED
FLOWS_FILES	    LOCKED
APEX_040000	    LOCKED
OUTLN	            EXPIRED & LOCKED
DIP	            EXPIRED & LOCKED
ORACLE_OCM          EXPIRED & LOCKED
XS$NULL	            EXPIRED & LOCKED
MDSYS               EXPIRED & LOCKED
CTXSYS              EXPIRED & LOCKED
DBSNMP              EXPIRED & LOCKED
XDB                 EXPIRED & LOCKED
*/

HR 사용자 계정을 잠금 상태로 설정

--○ HR 사용자 계정을 잠금 상태로 설정
ALTER USER HR ACCOUNT LOCK;
--==>> User HR이(가) 변경되었습니다.
--※ 이 상황에서 HR 계정으로 접속을 시도해 보았지만... 진행할 수 없음. → 확인

HR 사용자 계정을 잠금해제 상태로 설정

--○ HR 사용자 계정을 잠금해제 상태로 설정
ALTER USER HR ACCOUNT UNLOCK;
--==>> User HR이(가) 변경되었습니다.

local_hr

현재 접속한 자신의 계정 확인

--○ 현재 접속한 자신의 계정 확인
SELECT USER
FROM DUAL;
--==>> HR

scott(new)

※ SYS 로 접속한 상태에서 진행!!!!

사용자 계정 생성 및 패스워드 설정

--○ 사용자 계정 생성 및 패스워드 설정
create user scott
identified by tiger;
--==>> User SCOTT이(가) 생성되었습니다.

권한 부여

--○ 권한 부여
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
--==>> Grant을(를) 성공했습니다.

구조적인 변경

--○ 구조적인 변경
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
--==>> User SCOTT이(가) 변경되었습니다.

--○ 구조적인 변경
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
--==>> User SCOTT이(가) 변경되었습니다.

이후부터는 CONNECT SCOTT/TIGER로 진행!!!!

local_scott

현재 접속한 자신의 계정 확인

--○ 현재 접속한 자신의 계정 확인
SELECT USER
FROM DUAL;
--==>> SCOTT

테이블 생성 (DEPT)

--○ 테이블 생성 (DEPT)
CREATE TABLE DEPT
( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
, DNAME  VARCHAR2(14)
, LOC    VARCHAR2(13)
);
--==>> Table DEPT이(가) 생성되었습니다.

확인 (DEPT)

--○ 확인 (DEPT)
SELECT *
FROM DEPT;

테이블 생성 (EMP)

--○ 테이블 생성 (EMP)
CREATE TABLE EMP
( EMPNO      NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY
, ENAME      VARCHAR2(10)
, JOB        VARCHAR2(9)
, MGR        NUMBER(4)
, HIREDATE   DATE
, SAL        NUMBER(7,2)
, COMM       NUMBER(7,2)
, DEPTNO     NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
--==>> Table EMP이(가) 생성되었습니다.

확인 (EMP)

---○ 확인 (EMP)
SELECT *
FROM EMP;

데이터 입력 (DEPT)

--○ 데이터 입력 (DEPT)
INSERT INTO DEPT VALUES	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES	(40,'OPERATIONS','BOSTON');
--==>> 4 행 이(가) 삽입되었습니다.

데이터 입력 (EMP)

--○ 데이터 입력 (EMP)
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

--==>> 14 행 이(가) 삽입되었습니다.

테이블 생성 (BONUS)

--○ 테이블 생성 (BONUS)
CREATE TABLE BONUS
( ENAME VARCHAR2(10)
, JOB   VARCHAR2(9)
, SAL   NUMBER
, COMM  NUMBER
);
--==>> Table BONUS이(가) 생성되었습니다.

테이블 생성 (SALGRADE)

--○ 테이블 생성 (SALGRADE)
CREATE TABLE SALGRADE
( GRADE NUMBER
, LOSAL NUMBER
, HISAL NUMBER
);
--==>> Table SALGRADE이(가) 생성되었습니다.

데이터 입력 (SALGRADE)

--○ 데이터 입력 (SALGRADE)
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
--==>> 5 행 이(가) 삽입되었습니다.

COMMIT

COMMIT;
--==>> 커밋 완료.

--ROLLBACK;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

현재 SCOTT 계정이 소유하고 있는 테이블 확인(조회)

--○ 현재 SCOTT 계정이 소유하고 있는 테이블 확인(조회)
SELECT *
FROM TAB;
--==>>
/*
BONUS	    TABLE
DEPT	    TABLE
EMP	    TABLE
SALGRADE    TABLE
*/

보너스 테이블 조회

--○ 보너스 테이블 조회
SELECT *
FROM BONUS;

부서정보 테이블 조회

--○ 부서정보 테이블 조회
SELECT *
FROM DEPT;

사원 정보 테이블 조회

--○ 사원 정보 테이블 조회
SELECT *
FROM EMP;

급여 등급 테이블 조회

--○ 급여 등급 테이블 조회
SELECT *
FROM SALGRADE;

테이블의 구조 확인

--○ 테이블의 구조 확인
DESCRIBE EMP;
--==>>
/*
이름       널        유형
-------- -------- ------------
EMPNO    NOT NULL NUMBER(4)
ENAME             VARCHAR2(10)
JOB               VARCHAR2(9)
MGR               NUMBER(4)
HIREDATE          DATE
SAL               NUMBER(7,2)
COMM              NUMBER(7,2)
DEPTNO            NUMBER(2)
*/

Test01

--○ EMP 테이블에서 부서번호가 20번인 사원들의 정보 중
--   사원번호, 사원명, 급여, 직종명, 부서번호 조회.
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
--==>>
/*
7369	SMITH	800	CLERK	  20
7566	JONES	2975	MANAGER	  20
7788	SCOTT	3000	ANALYST	  20
7876	ADAMS	1100	CLERK	  20
7902	FORD	3000	ANALYST	  20
*/

--※ 각 컬럼에 별칭을 부여할 수 있다.
SELECT EMPNO AS "사원번호", ENAME "사원명", SAL 급여, JOB "직종 명", DEPTNO
FROM EMP
WHERE DEPTNO = 20;

Test02

--○ EMP 테이블에서 부서번호가 20번과 30번 직원들의 정보 중
--   사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다.
SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호"
FROM EMP
WHERE DEPTNO = 20 OR DEPTNO = 30;

SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호"
FROM EMP
WHERE DEPTNO IN (20, 30);

Test03

--○ EMP 테이블에서 직종이 CLERK 인 사원들의 정보를 모두 조회한다.
SELECT *
FROM EMP
WHERE JOB = 'CLERK';

--※ 오라클에서 ... 입력된 데이터(값) 만큼은
--   반.드.시 대소문자 구분을 엄격하게 한다.

테이블 복사

--○ 테이블 복사
--> 내부적으로 대상 테이블 안에 들어있는 데이터 내용만 복사하는 과정
CREATE TABLE TBL_EMP
AS
SELECT *
FROM EMP;
--==>> Table TBL_EMP이(가) 생성되었습니다.

SELECT *
FROM TBL_EMP;
--==>>
/*
7369	SMITH	  CLERK	        7902	1980-12-17  00:00:00	800	20
7499	ALLEN	  SALESMAN	7698	1981-02-20  00:00:00	1600	300	  30
7521	WARD	  SALESMAN	7698	1981-02-22  00:00:00	1250	500	  30
7566	JONES	  MANAGER	  7839	1981-04-02  00:00:00	2975	    20
7654	MARTIN	SALESMAN	7698	1981-09-28  00:00:00	1250	1400	30
7698	BLAKE	  MANAGER	  7839	1981-05-01  00:00:00	2850	    30
7782	CLARK MANAGER	        7839	1981-06-09  00:00:00	2450	    10
7788	SCOTT	  ANALYST	  7566	1987-07-13  00:00:00	3000	    20
7839	KING	  PRESIDENT	        1981-11-17  00:00:00	5000	    10
7844	TURNER	SALESMAN	7698	1981-09-08  00:00:00	1500	0	    30
7876	ADAMS CLERK	        7788	1987-07-13  00:00:00	1100	    20
7900	JAMES	  CLERK	        7698	1981-12-03  00:00:00	950	      30
7902	FORD	  ANALYST	  7566	1981-12-03  00:00:00	3000	    20
7934	MILLER	CLERK	        7782	1982-01-23  00:00:00	1300	    10
*/

Test04

--○ 이미 만들어진 테이블에 컬럼을 추가하거나 제거하는 방법
-- TBL_EMP 테이블에 주민번호 정보를 담을 수 있는 컬럼(SSN) 추가.
ALTER TABLE TBL_EMP
ADD SSN CHAR(13);
--==>> Table TBL_EMP이(가) 변경되었습니다.

-- 컬럼이 추가된 테이블 구조 확인
DESC TBL_EMP;
--==>>
/*
이름       널 유형
-------- - ------------
EMPNO      NUMBER(4)
ENAME      VARCHAR2(10)
JOB        VARCHAR2(9)
MGR        NUMBER(4)
HIREDATE   DATE
SAL        NUMBER(7,2)
COMM       NUMBER(7,2)
DEPTNO     NUMBER(2)
SSN        CHAR(13)
*/

SELECT *
FROM TBL_EMP;

Test05

--○ TBL_EMP 테이블에 추가한 SSN(주민번호) 컬럼을 구조적으로 제거
ALTER TABLE TBL_EMP
DROP COLUMN SSN;
--==>> Table TBL_EMP이(가) 변경되었습니다.

SELECT *
FROM TBL_EMP;

-- 권장되지 않는 방법(실수할 수 있어)
DELETE TBL_EMP WHERE EMPNO=7934;

ROLLBACK;

-- 삭제할 데이터 먼저 확인
SELECT *
FROM TBL_EMP
WHERE EMPNO=7934;

-- 'SELECT *'만 DELETE로 변경
DELETE
FROM TBL_EMP
WHERE EMPNO=7934;

-- 메모리에 적용
COMMIT;

-- TBL_EMP 테이블을 구조적으로 삭제
DROP TABLE TBL_EMP;
--==>> Table TBL_EMP이(가) 삭제되었습니다.

SELECT *
FROM TAB;

NULL 의 처리

--○ NULL 의 처리
SELECT 2, 1+2, 3-1, 2*10, 10/2
FROM DUAL;
--==>> 2	3	2	20	5

SELECT 2+NULL, 2-NULL, 2*NULL, 2/NULL
FROM DUAL;
--==>> (NULL) (NULL) (NULL) (NULL)

--※ NULL 은... 현실에 존재하는 특정한 값을 의미하는 것이 아니라
--   아무것도 들어있지 않은 상태를 의미하는 값이므로
--   NULL 을 대상으로 연산을 수행할 경우... 또는 특정 연산 과정에 NULL 이 포함될 경우
--   결과는 무조건 NULL 이다.

다시 EMP 테이블을 복사하여 TBL_EMP 테이블을 생성

--○ 다시 EMP 테이블을 복사하여 TBL_EMP 테이블을 생성
CREATE TABLE TBL_EMP
AS
SELECT *
FROM EMP;
--==>> Table TBL_EMP이(가) 생성되었습니다.

Test06

--○ TBL_EMP 테이블에서 커미션(COMM) → 수당이 NULL 인 사원들의
--   사원명, 직종명, 급여, 커미션을 조회한다.
SELECT ENAME 사원명, JOB 직종명, SAL 급여, COMM 커미션
FROM TBL_EMP
WHERE COMM IS NULL;

--※ NULL 은 값으로 존재하는 것이 아니기 때문에 값으로 비교할 수 없다.
--   =, >=, <=, >, <, !=, <>, ^= 와 같은 연산자를 사용할 수 없다.

Test07

--○ TBL_EMP 테이블에서 20번 부서에 근무하지 않는 사원들의 정보를
--   사원번호, 사원명, 부서번호로 조회한다.

SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO != 20;

SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO <> 20;

SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO ^= 20;

SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE NOT DEPTNO = 20;

SELECT ENAME 사원명, JOB 직종명, SAL 급여, COMM 커미션
FROM TBL_EMP
WHERE COMM IS NOT NULL;

SELECT ENAME 사원명, JOB 직종명, SAL 급여, COMM 커미션
FROM TBL_EMP
WHERE NOT COMM IS NULL;