1. ORACLE :: DataBase와 SQL Developer 사용하기

0. Data & Database

정보란? 데이터를 기반으로 의미를 부여한 것

Data와 Database에 대해서 좀더 자세히 알아보자.

1) Data

: 관찰 결과로 나타난 정량적 혹은 정성적인 실제 값

에베레스트이 높이 8848m → 데이터 에베레스트는 세계에서 제일 높은 산이다 → 정보

2) Database

: 한 조직에 필요한 정보를 여러 응용 시스템에서 공용할 수 있도록 논리적으로 연관된 데이터를 모으고 중복 데이터를 최소화하여 구조적으로 통합 및 저장 해놓은 것

정의

1. 운영 데이터(Operational Data) : 조직의 목적을 위해 사용됨
2. 공용 데이터(Shared Data) : 공동으로 사용됨
3. 통합 데이터(Integrated Data) : 중복 최소화로 데이터 불일치 현상 제거
4. 저장 데이터(Stored Data) : 컴퓨터 저장장치에 저장됨

특징

1. 실시간 접근성(real time accessibility) : 사용자 요청에 따라 실시간으로 결과 서비스
2. 계속적인 변화(continuos change)
3. 동시 공유(concurrent sharing) : 서로 다른 업무 및 여러 사용자에게 동시 공유됨
4. 내용에 따른 참조(reference by content) : 데이터의 물리적 위치가 아니라,, 데이터 값에 따라 참조

3) DBMS(DataBaseManageSystem?)

: 데이터베이스에서 데이터 추출, 조작, 정의, 제어 등을 할 수 있게 해주는 데이터베이스 전용 관리 프로그램

기능

추출(Retrieval)	사용자가 조회하는 데이터 혹은 응용 프로그램의 데이터 추출
조작(Manipulation)	데이터를 조작하는 소프트웨어(응용 프로그램)가 요청하는 데이터를 삽입, 수정, 삭제
정의(Definition)	데이터 구조를 정의하고 구조에 대한 삭제 및 변경
제어(Control)	데이터베이스 사용자를 생성하고 모니터링하며 접근 제어 백업과 회복, 동시성 제어 등 기능 지원

종류와 특징

• 정형 데이터(관계형 DB)인지 비정형 데이터인지에 따라서 종류가 나뉠 수 있다
• 정형 데이터 → Oracle(비쌈), MariaDB, Mysql …
• 중간 → Mongo…
• Oracle 말고도 다양한 종류가 있다.

사용시 이점

1. 데이터 독립화 : 데이터와 응용 프로그램을 분리
2. 데이터 중복 최소화, 데이터 무결성 보장 : 무결성 손상될 가능성 감소, 저장공간 낭비 방지
3. 데이터 보안 향상 : 응용프로그램은 DBMS가 허용하는 데이터에만 접근 가능(직접 접근은 X) → 권한에 맞게 데이터 접근 제한, 데이터 암호화해서 저장 가능
4. 관리 편의성 향상 : 데이터 백업 가능, 장애 발생시 데이터 복구 가능

변천 과정

파일시스템 → 계층 데이터 모델(1970) → 네트워크 데이터 모델(1970) → 관계 데이터모델(1980) → 객체 데이터 모델(1990) → 객체-관계 데이터 모델(2000)

• 지금까지 이용되는건 관계 데이터 모델과 객체-관계 데이터 모델

 

1. SQL Developer

Db에 접속해서 관리할 수 있는 프로그램

맥북 실리콘칩은 자체적으로 실행이 불가능해서 가상으로 실행가능한 시스템(도커)를 올린뒤에 시스템을 윈도우처럼 변환해주는 colima가 먼저 선행되어야만 실행된다.

DB를 관리할 저장소가 미리 선별된 뒤, oracle에서 제공하는 프로그램으로 관리하게 된다.

관리 계정은 SYSTEM → 저장소 관리자이기 때문에 모든 곳에 접근 가능

• 다른 계정에서 접근하려면 관리자인 SYSTEM에서 허용 및 권한을 부여해줘야하며 할당된 영역에 한해서 별도로 생성된 id, pw로 접속해야한다.
• 권한: 접근 권한, 할당된 저장소 이용 권한

앞으로 ORACLE의 데이터베이스를 관리할 수 있는 SQL Developer 프로그램 xe버전을 이용 (프로그램 설치 과정은 다른 블로그에서 많이 다루고 있으니 생략한다)

1) 접속

기본적으로 공부를 위해선 자신의 컴퓨터 혹은 노트북에서 로컬 데이터베이스를 생성한다. 기본값으로 아래와같이 설정된다. 

• 호스트명 : localhost
• 포트 번호 :  1521

2) 워크시트

sql이라는 확장자의 워크시트 내에서 명령문을 작성한다. 계정에 접속하면 자동으로 생성된다.

3) 사용자 계정 생성 : 계정 생성 및 권한 부여 

1. 사용자계정을 관리자계정에서 생성해준다

-- 사용자계정에 C## 붙이지 않게 하기
-- SESSION 설정을 변경해줘야함
-- ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;

CREATE USER 사용자계정명 IDENTIFIED BY 비번 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

-- 11G는 뒤에 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;를 자동으로 붙여주기 때문에 붙이면 에러가 난다.
CREATE USER JH IDENTIFIED BY JH;

• 관리자 계정에 접속해서 계정생성 명령어 사용
• USERS: 기본 오라클에서 제공하는 사용자 공간
• 사용자계정명은 대소문자 구분 없음, 비밀번호는 대소문자 구분되니까 유의
  • 원래 사용자 계정명 앞에 C##을 붙여줘야되는데 번거로우니까 세션 설정 변경을 같이 해준다.

 

2. 계정 생성 이후에는 적절한 권한을 부여해줘야한다. → GRANT 권한또는ROLE이름 TO 사용자명

• 기본적으로 DB접속 권한, 할당된 DB 영역을 사용할 권한 부여
  • DB접속 권한: CONNECT(ROLE/명칭)
  • DB영역 사용 권한: RESOURCE(ROLE/명칭)

-- 접속 권한 부여
GRANT CONNECT TO JH;
GRANT RESOURCE TO JH;
-- 한번에 쓰려면 GRANT CONNECT, RESOURCE TO JH;

 

3. 테이블 정보 확인하기(조회) → SELECT * FROM TAB

4) 주요 용어

전체 테이블은 자바로 치면 하나의 List 컬렉션인 셈이다.

• 행(row) : 데이터 테이블에서 가로 한 줄을 차지한다. 자바로 치면 한개 클래스(한 줄)에 선언된 필드 변수와 초기화된 값들인 셈.
• 컬럼(column) : 데이터 테이블에서 세로 한 줄. 가장 윗 칸이 컬럼명. 자바로 치면 컬럼 명이 필드 변수명인 셈.
• 기본 키(Primary Key) : 기본키 한개가 테이블마다 있음 → 중복값이 없어야 한다.
• 외래 키(Foreign Key) : 따로 입력한 a 데이터 테이블의 데이터를 b 테이블에 연결할 때 사용, 무결성 깨지 않기 위해 사용 
• null: 데이터에는 null값도 들어갈 수 있다. null은 연산할 수 없다.

 

2. SQL(Structured Query Language)

: 관계형 데이터베이스에서 사용하는 표준 검색 언어

쓰는 방식과 형식이 정해져있기 때문에 Structure

분류용도명령어

분류	용도	명령어	비고
DQL(Data Query Language)	데이터 검색	SELECT
DML(Data Manipulation Language)	데이터 조작	INSERT, UPDATE, DELETE
DDL(Data Definition Language)	데이터 정의	CREATE, DROP, ALTER
TCL(Transaction Control Language)	트랜잭션 제어	COMMIT, ROLLBACK	마지막에 일괄로 저장처리하는 것= 커밋, 저장 처리 전에 전체 일괄 취소하는 것= 롤백

 

1) DQL : 데이터 검색

(1) SELECT

데이터를 조회한 결과(Result Set)를 받아볼 수 있음

• 0개 이상의 행이 포함될 수 있음
• 특정 기준에 의해 정렬가능한 테이블 데이터를 조회할 수 있다

작성법

SELECT 조회할 컬럼명[, 컬럼명, ...] 	-- 모든 컬럼 조회시 컬럼명 대신 * 사용 가능, 1개 이상 지정 필수
FROM 가져올 테이블명;  -- 조회 대상 컬럼이 포함된 테이블 명 기술, 마무리 세미콜론
[WHERE 조건식;]	-- 조건을 만족시키는 행들만 Result Set에 포함됨
	
-- 조회 결과는 기술한 커럼 명 순으로 표시됨

ex)
-- 근로자 테이블에 있는 전체 컬럼 데이터 조회하기
SELECT * FROM EMPLOYEE;
-- 해당 테이블에서 이름, 주민번호, 이메일, 급여, 보너스 조회하기
SELECT EMP_NAME, EMP_NO, EMAIL, SALARY, BONUS 
FROM EMPLOYEE;

 

컬럼 작성부에 산술연산을 작성하고 그 결과를 출력할 수도 있다. (가상 컬럼이 조회됨)

• 가상 컬럼에 이름을 부여할 수 있다 -> as 사용
  • 보통 한글 깨짐 방지를 위해 영어로 하는 것이 흔하다.
  • 띄어쓰기보다는 _언더바를 사용한다.
  • 별칭에 띄어쓰기, 특수기호 하려면 “”로 묶어주면 된다

-- 컬럼명을 변경해서 출력하기
-- 컬럼명 AS 출력할명칭 / 컬럼명 출력할명칭
SELECT EMP_NAME AS 이름, SALARY 월급
FROM EMPLOYEE;

-- 별칭에 띄어쓰기나 특수문자는 불가능(별칭을 ""로 묶어주면 가능)
SELECT EMP_NAME AS 이름, SALARY "월급^^*"
FROM EMPLOYEE;

• 산술연산(+, -, /, *)시 컬럼명을 사용할 수 있다.

-- 컬럼 작성시 산술연산도 가능하다.
SELECT EMP_NAME, SALARY * 12 as 연봉
FROM EMPLOYEE;

• 컬럼끼리 연산도 가능하다.
• null값과는 연산이 불가능하다 → null 출력됨
  • null의 대체값을 넣어주는 함수 입력 가능 : NVL(칼럼명, 대체값)

SELECT EMP_NAME, SALARY, NVL(BONUS, 0), NVL(SALARY * BONUS, SALARY)
FROM EMPLOYEE;

 

null값을 조회하려면

null을 찾는 연산자 사용 → IS NULL / IS NOT NULL

 

DUAL 테이블

오라클에서는 산술연산을 테스트해볼 수 있는 테스트용 테이블을 만들어놓았다 (DUAL)

-- DUAL
SELECT 10+10, 20*3,  10/3
FROM DUAL;

문자열 출력

-- 문자열 출력
SELECT '오라클 너무 쉽당'
FROM DUAL; 

• 문자열끼리는 결합연산 불가

-- 문자열 + 문자열 결합연산 불가능
-- + 연산시에는 무조건 수치만 가능함
SELECT '오라클 너무 쉽당'+ '잇힝'
FROM DUAL;

(2) DISTINCT

: 중복되는 ROW를 제외하고 출력해주는 기능

• 컬럼작성부 맨앞에만 작성 가능

→ SELECT DISTINCT 컬럼명 FROM 테이블명

-- 주의사항 : 하나의 row 는 분리될수 없는 하나의 그룹임. 한 row에 있는 칼럼은 하나의 그룹으로 묶여서 중복 카운트가 진행됨
SELECT DISTINCT DEPT_CODE, JOB_CODE
FROM EMPLOYEE;
-- 출력
D9	J1
D9	J2

(3) WHERE

: 원하는 ROW만 조회할 때 사용하는 조건 명령어 (WHERE절 자체가 true, false를 반환해야한다.)

• 연산자 이용 가능
• 조건이 여러 개일 경우 논리 연산자(AND, OR)를 이용

(4) ESCAPE  :: 역슬래시 \

이스케이프 문자를 그때그때 지정할 수 있음

-- 이메일에 _앞글자가 3글자인 사원 조회: 사원명, 이메일
SELECT EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___\\_%' ESCAPE '\\';

선동일	sun_di@BS.or.kr
유재식	yoo_js@BS.or.kr
...