Oracle SQL 쿼리문 정리

 

 

 

[오라클] Oracle SQL 쿼리문 정리

 

쿼리문을 한번 잘 알아두시면 다른 데이터베이스 사용할 때 유용합니다.

쿼리문은 거의 비슷하기 때문이죠!

 

잘 사용 안하다 보면 자꾸 까먹어서 정리차원에 포스트를 합니다.

 

■ 오라클 접속

1) 관리자로 접근하는 방법

sqlplus sys(system)/[비밀번호] as sysdba

sqlplus / as sysdba (계정명과 비번 생략할 수 있음) => DBA로 접속

 

2) 일반 계정으로 접근하는 방법

sqlplus 계정명/계정비밀번호

 

■ 락(lock) 계정을 풀려면 sys 계정으로 접속하여

alter user scott identified tiger account unlock; 실행함

alter user [추가할 계정] identified [비밀번호] account unlock;

 

■ 데이타베이스 사용자 변경

conn 사용자명/사용자비밀번호

 

■ 데이타베이스 정보 확인

show parameter db_name;

 

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

■ 테이블 구조 확인 방법

desc 테이블명;

 

■ 테이블 조회

select * from tab;

 

문자데이터와 날짜데이터는 반드시 ''(단일 따옴표)를 사용하여 표현함

리터널은 대소문자를 구분함

 

오라클에서 NULL값

1) 데이타 검색시 컬럼의 값이 비어있는 형태임

2) 기본적으로 NULL값을 허용하며 제약조건에서 NULL값을 허용하지 않을 수 있음

3) NULL값의 연산 결과는 NULL값으로 나옴

 

=> NULL값 가진 컬럼 연산시 NVL 함수를 사용함

NVL(컬럼명, 기본값)

 

##########################################################################

SELECT (검색)

SELECT [DISTINCT] {컬럼명} [as alias]

FROM 테이블명

[WHERE 조건식]

[ORDER BY 컬럼명 ASC|DESC];

 

=> DISTINCT : 중복되어 출력되는 값을 제거하는 키워드

 

=> WHERE 절 조건식

 

[비교연산자]

= (같다), != (같지않다), > (크다), >= (크거나 같다), < (작다), <= (작거나 같다)

 

[논리연산자]

AND, OR, NOT

 

[BETWEEN a AND b] <=> 반대는 NOT BETWEEN a AND b 임

특정 범위의 값을 검색할 때 사용함

 

[IN] <=> 반대는 NOT IN 임

여러 개의 값을 동시에 비교하여 일치하는 데이터를 얻을 때 사용

 

[LIKE] <=> 반대는 NOT LIKE 임

임의의 문자 또는 문자열을 포함한 데이타를 검색할 때 사용

% (퍼센트) : 없거나 여러개의 문자를 대체함

_(언더바) : 하나의 문자를 대체함

 

[IS NULL] <=> 반대는 IS NOT NULL 임

컬럼의 데이터 중에서 NULL값을 얻을 때 사용

 

=> ORDER BY 절

특정 컬럼에 대해 정렬해서 보기 원할 때 사용

ASC : 오름차순 (기본값으로 생략가능함)

DESC : 내림차순

 

<오라클 유용한 함수들>

테이블명에 dual 을 넣으면 임시적으로 사용되는 더미(dummy) 테이블임

 

SYSDATE : 시스템의 현재 날짜를 구하는 함수

MAX(컬럼명) : 지정한 컬럼의 값중에서 최대값을 구하는 함수

MIN(컬럼명) : 지정한 컬럼의 값중에서 최소값을 구하는 함수

SUM(컬럼명) : 컬럼들의 총합을 구하는 함수

AVG(컬럼명) : 평균을 구하는 함수

COUNT(컬럼명) : 컬럼의 행의 개수를 구하는 함수

COUNT(*) : 전체 레코드의 개수를 구함

TO_CHAR(컬럼명, [포멧]) : 날짜 및 수치 데이터를 문자로 변환하기 위한 함수, 특정 포멧 지정할 수 있음

 

[날짜 출력 포멧 형식]

YYYY : 년도 4자리

YY : 년도 2자리

MM : 월을 숫자로 표현

MON : 월을 알파벳으로 표현

DAY : 요일 표현

DY : 요일을 약어로 표현

 

[시간 출력 포멧 형식]

AM, PM : 오전, 오후 시각표시

A.M, P.M : 오전, 오후 시각표시

HH, HH12 : 시간 (1~12)

HH24 : 24시간으로 표현 (0~24)

MI : 분 표현

SS : 초 표현

 

[숫자 출력 포멧 형식]

9 : 한자리의 숫자 표현

0 : 앞부분을 0으로 표현

$ : 달러 기호를 앞에 표현

. : 소수점을 표시

, : 특정 위치에 , 표시

B : 공백을 0으로 표현

L : 지역 통화

 

##########################################################################

INSERT (입력)

INSERT INTO 테이블명 [ (컬럼명, 컬럼명)]

VALUES (값, 값);

 

UPDATE (수정)

UPDATE 테이블명

SET 컬럼명=새로운값 [, 컬럼명=새로운값]

[WHERE 조건식];

 

DELETE (삭제)

DELETE FROM 테이블명

[WHERE 조건식];

 

1) ROLLBACK 으로 트랜잭션 취소가 가능함

2) 테이블의 데이타만 삭제됨

3) ROLLBACK 구현을 위해서는 UNDO 기능이 추가됨

 

##########################################################################

CREATE (생성)

<컬럼 레벨 지정방식>

CREATE TABLE 테이블명 (

         컬럼명 데이타형 DEFAULT 값,

         컬럼명 데이타형[CONSTRAINT 제약조건이름] 제약조건);

 

<테이블 레벨 지정방식>

CREATE TABLE 테이블명 (

         컬럼명 데이타형 DEFAULT 값,

         컬럼명 데이타형,

         [CONSTRAINT 제약조건이름] 제약조건);

 

=> DEFAULT 값

1) DEFAULT 에서 지정한 값으로 저장되고 지정하지 않은 곳은 NULL값으로 저장됨

2) 현재 날짜를 입력하는 경우이거나 항상 동일한 값만 가지는 컬럼에 사용됨

 

=> 제약조건(Constraints)

1) 컬럼에 데이타를 저장할 때 유효한 데이타 저장을 위한 제약조건 사용할 수 있음

2) 제약조건이름 지정 방식 : 테이블명_컬럼명_제약조건약어 로 표현

 

[제약조건]

PRIMARY KEY : 레코드의 중복을 방지할 목적으로 사용, 유일한 값을 가지고 NULL값 허용안됨 (약어 PK)

FOREIGN KEY : 여러 테이블간의 관계를 맺기 위해 사용, 다른 테이블 PK 컬럼값 또는 UNIQUE 컬럼값 및 NULL값만을 가져야 함, (약어 FK)

UNIQUE : 유일한 값을 가지거나 NULL값을 가질 수 있음 (약어 UK)

NOT NULL : 반드시 값을 가져야 함 (약어 NN), 컬럼 레벨 방식으로만 지정 가능함

CHECK : 지정한 조건에 일치하는 데이타 값만 저장 가능함 (약어 CK)

 

=> 테이블 복사해서 만들기

CREATE TABLE 새테이블명 (컬럼명, 컬럼명)

AS

SELECT 컬럼명, 컬럼명 FROM 기존테이블명

[WHERE 만족하지 않는 조건식];

 

1) CTAS(씨탁스)라 부르며 제약조건은 복사안됨

2) 데이타까지 복사됨

3) WHERE절에 만족하지 않는 조건식을 넣으면 데이타는 복사가 안됨

 

##########################################################################

DROP (삭제)

1) 테이블을 삭제하면 저장된 데이타도 삭제됨

2) FK 제약조건이 있는 경우 테이블 삭제가 불가능함

3) 참조하는 테이블부터 먼저 삭제하거나 제약조건 비활성 시킨후 삭제요망

4) 10g 부터 휴지통같은 recyclebin 을 이용하여 테이블 관리함

5) recyclebin 저정된 테이블은 복구도 가능하고 완전 삭제도 가능함

6) 테이블을 삭제하면 BIN 으로 시작되는 테이블 생성됨 (recyclebin 저장된 이름)

 

DROP TABLE 테이블명 [purge];

 

=> recyclebin 테이블 확인 방법

SHOW recyclebin;

 

=> recyclebin 테이블 복구 방법

FLASHBACK TABLE 삭제된테이블명 TO BEFORE DROP;

 

=> recyclebin 테이블 휴지통 지우는 방법

PURGE recyclebin;

 

=> recyclebin 넣지 않고 완전 삭제하는 방법

PURGE 키워드를 사용하면 FLASHBACK 으로 복구 불가능함

 

##########################################################################

TRUNCATE (저장공간 삭제)

1) 테이블에 저장된 데이타를 삭제하는 구문

2) ROLLBACK 으로 트랜잭션 취소할려고 해도 불가능함

3) 테이블의 데이타 및 저장구조가 삭제됨 따라서 메모리를 효율적으로 관리 가능함

4) ROLLBACK 이 안되니까 UNDO 기능이 필요없음

 

TRUNCATE TABLE 테이블명;

 

##########################################################################

ALTER (수정)

1) 기존 테이블의 구조를 변경하는 구문

2) 컬럼 추가, 삭제, 데이타형 변환 및 길이 변경이 가능함

3) 제약조건도 추가/삭제 가능함

 

=> 컬럼 추가

ALTER TABLE 테이블명

ADD (컬럼명 데이터형, [컬럼명 데이터형]);

 

=> 제약조건 추가

ALTER TABLE 테이블명

ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명);

 

=> 컬럼 변경

ALTER TABLE 테이블명

MODIFY (컬럼명 데이터형);

 

=> 제약조건 변경

ALTER TABLE 테이블명

MODIFY (컬럼명 데이타형 [CONSTRAINT 제약조건명] 제약조건);

 

=> 컬럼 삭제

ALTER TABLE 테이블명

DROP (컬럼명);

 

##########################################################################

시퀸스 (SEQUENCE)

1) 일정 규칙에 의해 연속적으로 자동 증가, 감소해야 하는 경우 적용

2) 테이블내 정수값을 가지는 PK 에 사용됨

 

CREATE SEQUENCE 시퀀스명

[ START WITH n ] 시퀀스 번호의 시작값 지정 (기본 1부터 시작)

[ INCREMENT BY n ] 시퀀스의 증가, 감소치 지정 (기본 1씩 증가, 음수도 가능)

[ MAXVALUE n | NOMAXVALUE ] 최대값 지정

[ MINVALUE n | NOMINVALUE ] 최소값 지정

[ CYCLE | NOCYCLE ] 최대값까지 증가가 완료하면 다시 초기값에서 시작하도록 지정

[ CACHE n | NOCACHE ]; 기본 20개의 cache 를 관리

 

시퀀스명.NEXTVAL : 시퀀스의 다음값을 얻음

시퀀스명.CURRVAL : 시퀀스의 현재값을 얻음

 

##########################################################################

RENAME (이름변경)

 

##########################################################################

COMMIT (트랜잭션 저장)

ROLLBACK (트랜잭션 취소)

SAVEPOINT (트랜잭션 책갈피 기능)

 

##########################################################################

GRANT (권한 부여)

GRANT 권한 및 롤

TO 사용자계정;

 

=> 권한 및 롤

1) CONNECT : 데이타베이스 접속할 수 있는 롤

2) RESOURCE : 테이블 생성, 데이터 조회, 삭제, 수정, 저장 할 수 있는 롤

 

REVOKE (권한 취소)

 

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

<사용자 계정 생성>

 

CREATE USER 사용자명

IDENTIFIED BY 비밀번호;

 

<사용자 계정 비번 변경>

 

ALTER USER 사용자명

IDENTIFIED BY 새비밀번호;

 

<사용자 삭제 - DBA만 가능함>

DROP USER 사용자명 CASCADE;

 

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

조인 (JOIN)

 

Equi Join : 두 테이블의 공통적으로 존재하는 컬럼값이 일치하는 행을 연결하여 조인

SELECT 테이블1.컬럼명, 테이블2.컬럼명

FROM 테이블1, 테이블2

WHERE 테이블1.공통컬럼명 = 테이블2.공통컬럼명

 

별칭을 사용할 경우

SELECT 별칭1.컬럼명, 별칭2.컬럼명

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼명 = 별칭2.공통컬럼명

 

Non Equi Join : 동등연산자(=) 이외의 연산자를 사용하여 조인하는 방법

 

Self Join : 다른 테이블이 아닌 자기 자신과의 조인을 의미함

 

Outer Join : 조인 조건에 만족하지 않아도 해당 행을 출력하고 싶을 때 사용할 수 있음

조인할 데이터가 없는 쪽에 (+) 연산자를 사용함