쌍용강북교육센터 국비 학원 Day 24일차 Oracle (Primary Key, Sequence, Like 연산자, 단일 행 함수)

 

쌍용 강북 교육센터 국비학원 Day 24일차

 

 

간단하게 Oracle 용어를 정리해보자면

 

테이블 Table (엔티티) - 데이터를 저장하기 위한 저장소, 표와 같은것

컬럼 Column (필드) - 관계형 데이터베이스에서 행을 분류하는 기준

로우 Row (행,레코드) -  데이터를 저장하는 값으로 컬럼내의 단 하나의 값

 

 

 

PRIMARY KEY (기본키)

 

식별자 (Identifier)
어떤 대상을 유일하게 식별 및 구별할 수 있는 이름을 뜻합니다.

키 (KEY)
데이터베이스에서 조건에 만족하는 행을 찾거나 순서대로 정렬할 때 다른 레코드들과 구별할 수 있는 유일한 기준이 되는 속성(Attribute) 입니다.

기본키 (PRIMARY KEY)
관계형 데이터베이스에서 행의 식별자로 이용하기에 가장 적합한 것을 테이블마다 설계자에 의해 선택된 키를 말다.

 

 

기본키(고유키) 를 사용하는 이유

• 기본키를 설정해주면 잘못된 데이터가 입력 혹은 수정시에 에러를 발생시킨다.
• 기본키를 지정하게 되면 해당 기본키가 지정된 행 또한 고유하다는걸 알 수 있다.
• SELECT 으로 검색을 할 때 기본키가 지정된 것과 안된 것의 속도는 차이는 매우 크다.
• 기본키는 NOT NULL + UNIQUE의 기능을 가지고 있다.

 

 

 

기본키 설정하는 법

 

 - 테이블 생성시

 

(컬럼) (타입) PRIMARY KEY 

(컬럼) (타입) CONSTRAINT (제약조건 명) PRIMARY KEY

CONSTRAINT (제약조건 명) PRIMARY KEY (컬럼명),(컬럼명),...

 

- 기존테이블에 추가

 

ALTER TABLE (테이블명) ADD PRIMARY KEY (칼럼명) ;

 

기본키를 설정하는 방법은 테이블을 생성할 때와 기존 테이블에 수정하는 두 가지가 있지만, 보통 테이블을 생성할 때 설정하는 것을 권장한다. 그 이유는 컬럼이 이미 중복되는 것이 있다면 기본키를 추가할 수 없기 때문이다. (데이터를 수정 해야한다)

 

 

 

 

 

SEQUENCE (시퀸스)

 

• 유일한(UNIQUE) 값을 생성해주는 오라클 객체이다.
• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• 보통 PRIMARY KEY 값을 생성하기 위해 사용한다.
• 메모리에 Cache되었을 때 SEQUENCE 값의 액세스 효율이 증가 한다.
• SEQUENCE는 테이블과는 독립적으로 저장되고 생성된다. 따라서 하나의 SEQUENCE를 여러 테이블에서 쓸 수 있다.

 

 

 

START WITH (num) - 시퀸스의 시작값을 지정한다.

INCREMENT BY (num) - 시퀸스의 증가값을 지정한다.

 

NOMAXVALUE - 최대값을 지정하지 않는다.

NOMINVALUE - 최소값을 지정하지 않는다.

MINVALUE - 최소값을 지정한다. (cycle이 있을때만 minvalue 에 주어진 값이 사용되고 minvalue 값은 start with 값보다 같거나 작아야 한다)

CYCLE - 반복하는 싸이클을 만든다.

NOCYCLE - 반복을 하지 않는다. (최대값 도달시 시퀀스 생성 중단)

NOCACHE or CACHE - 시퀀스를 빨리 지정하기 위한 메모리에 캐쉬 지정. (no cache시 기본값 20)

 

( ※ 데이터 입력을 시도할 때 추가에 실패했을 경우에도 시퀀스는 증가한다)

 

 

 

Like 연산자

like는 ' = ' 과 같은 뜻을 지니지기도 와일드 카드와 사용해 포함된 문자열의 값을 찾는데에도 사용된다. like 연산자와 함께 사용되어지는 ' % '와 ' _ ' 를 wild chracter (wild card) 라고 부른다.

 

' % ' - 글자수와 관계없이 아무글자와 관계없는걸 뜻한다.

' _ ' - 반드시 아무글자 1개만을 뜻한다.

 

 

와일드 카드를 활용해 주민번호를 찾는 예제이다

 

 

 

 

ESCAPE

 

99.99% 만 구하고자 할 때

 

문자열에 포함된 ' % ' , ' _ ' 를 찾고자 할때에는 escape 시켜야한다. escape 문자를 선언하고 그 다음에 나오는 한글자  whild character (%, _)는 그 기능을 상실하고 순수글자 그대로 인식한다. escape 문자는 아무거나 사용할 수 있지만 보통 혼동되지 않도록 특수기호로 사용한다.

 

 

 

 

단일 행 함수

 

단일 행 함수는 결과값이 메모리에 로드되어진 행의 개수만큼 동일한 개수로 나온다. 즉 행의 연산이라고 생각하자.

 

 - 문자 함수

 

upper ( '문자열' ) - '문자열' 에서 소문자가 있으면 모두 대문자로 변환해준다.

lower ( '문자열' ) - '문자열'에서 대문자가 있으면 모두 소문자로 변환해준다.

initcap ( '문자열' ) - '문자열'을 단어별 (구분자 공백으)로 첫글자만 대문자, 나머지는 소문자로 변환 시켜준다.

substr ( '문자열' , 시작 글자번호, 추출할 글자수) - '문자열' 중에 문자열의 특정 일부분을 선택해 올 때 사용한다. 시작 글자번호부터 추출할 글자 수만큼 추출한다.

instr ( '문자열' , '찾을문자열' , 시작위치 , 몇번째 문자) - 어떤 문자열에서 찾을 문자열을 시작위치부터 몇번째 나오는지 문자열의 위치 (숫자)를 알려준다. 값이 없으면 0 출력 (시작위치가 음수이면 반대편 끝에서부터 읽어온다)

reverse ( '문자열' ) - 문자열을 거꾸로 변환해준다.

lpad ( '문자열' , 넣을byte, '넣을문자') - 문자열에 넣을문자를 공간만큼(byte) 왼쪽부터 문자열에 채운다.

rpad ( '문자열' , 넣을byte, '넣을문자') - 문자열에 넣을문자를 공간만큼(byte) 오른쪽부터 문자열에 채운다.

ltrim ( '문자열' , '제거할 문자열') - 문자열에서 제거할 문자열에 해당하는 문자를 왼쪽부터 제거한다. 제거할 문자열을 입력하지 않을시 공백을 제거한다.

rtrim ( '문자열' , '제거할 문자열') - 문자열에서 제거할 문자열에 해당하는 문자를 오른쪽부터 제거한다. 제거할 문자열을 입력하지 않을시 공백을 제거한다.

trim ( '문자열' ) - 문자열의 왼쪽, 오른쪽부터 공백을 제거한다.

translate ( '문자열' , (num), '대체할 문자열') - 문자열을 num으로 맵핑해서 대체할 문자열로 변환한다.

replace ( '문자열' , '대체될 문자열', '대체할 문자열) - 문자열에서 대체될 문자를 대체할 문자열로 변환한다.

length ( '문자열' ) - 문자열의 길이를 나타낸다.

 

 

 

 - 숫자 함수

 

mod - 나머지를 출력한다.

trunc (num, num2) - 몫만 출력하거나 해당 값을 절삭해준다. (num2 소수자리까지도 절삭 가능)

round (num, num2) - num을 소수 num2 째 자리만큼 반올림을 해준다. 음수는 반대

power (num, num2) - 거듭제곱을 나타낸다. num의 (num2)제곱

sqrt (num) - num의 제곱근을 나타낸다.

sin, cos, tan, asin, acos, atan - 삼각함수값을 나타낸다.

sign - 결과값이 양수라면 1, 0이라면 0, 음수라면 -1을 변환.

ceil (num) - 입력값이 실수일때 그 실수보다 큰 최소의 정수를 나타낸다. 정수는 그대로 출력.

floor (num) - 입력값이 실수일때 그 실수보다 작은 최대의 정수를 나타낸다. 정수는 그대로 출력

ascii ( '문자' ) - 해당문자의 아스키 코드값을 출력

chr (num) - 십진수를 해당 아스키 코드로 변환

 

 

 

 - 날짜 함수

 

날짜에서의 기본적으로 연산의 단위는 일수이다.

 

to_yminterval - 년, 월을 기준으로 나타내어 +, - 를 연산한다. ex) ( '01-02' )

to_dsinterval - 일, 시간, 분, 초를 기준으로 나타내어 +, - 를 연산한다. ex) ( '003 04:05:06' )

add_months (날짜, 숫자) - 월을 기준으로 날짜를 숫자만큼 연산한다.

moths_between (날짜1, 날짜2) - 날짜1에서 날짜2를 뺀 값으로 숫자의 단위는 개월 수이다.

last_day (특정날짜) - 특정날짜의 월의 맨 마지막 날을 나타낸다.

next_day (특정날짜, '요일명') - 특정날짜로부터 다음에 돌아오는 가장 빠른 '요일명' 에 해당하는 날짜를 나타낸다.

extract - 날짜에서 년, 월, 일을 숫자형태로 추출한다.

 

 

 

 - 변환 함수

 

to_char (날짜, '형식') - 날짜를 해당하는 '형식' 모양으로 문자 형태를 변환 시켜준다.

to_char (숫자, '형식') - 숫자를 해당하는 '형식' 모양으로 문자 형태를 변환 시켜준다.

to_date (문자, '형식') - 문자열을 해당하는 날짜형으로 변환 시켜준다.

to_number (문자) - 숫자모양을 가지는 문자를 숫자형태로 변환 시켜준다.

 

 

 

 

 

 - 기타 함수

 

 

case (조건) when (값) then (실행문) else (실행문) end - 조건문의 값이 참인 값의 실행문을 실행한다. 모두 거짓일경우 else문 실행. (조건이 없을경우 true, false 를 따진다)

(== case when (조건문) then (실행문) else (실행문) end 도 동일하다)

decode (조건문, 값, 실행문, else문) - case 문과 동일

 

자주 사용하는 중요한 함수이다. 밑에 성적을 구하는 예제를 참고하자.

 

 

case when then else

 

case when then else

 

 

decode

 

 

결과

 

 

 

 

greatest , lesast - 나열한 값 중에서 최댓값과 최솟값을 찾아준다.

rank - 나열한 값을 순차적으로 나열해주고 그 값의 등수(석차)를 구해준다. (where 절에 바로 사용 불가하다)

dense_rank - 나열한 값을 순차적으로 나열해주고 그 값의 서열을 구해준다.

lag, lead - 다른행을 불러온다. 게시판에서 특정글을 조회할때 많이 사용한다.