SQL & SQL*PLUS Syntax 정리

/*************************************************************

  SQL & SQL*PLUS Syntax 정리

**************************************************************/

 

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

            -       * syntax 기호                                    -

               -                                                                           -

               -         [] : 대괄호 안의 내용이 생략가능                     -

               -          | : 또는                                                      -

               -        {} : 중괄호 안의 내용이 하나 이상 올 수 있다.     -

               -        ... : 앞 표기와 같은 형식으로 더 올 수 있다.        -

               -          , : 구분자                                                    -

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

 

 

* SELECT
SELECT [DISTINCT] {*, COLUMN [ALIAS], . . .}
FROM TABLE_NAME
[WHERE CONDITION]
[ORDER BY {COLUMN, EXPRESSION} [ASC|DESC]];

 

* 비교연산자

= : 같다.

> : 보다 크다.

>= : 보다 크거나 같다.

< : 보다 작다.

<= : 보다 작거나 같다.

<>, !=, ^= : 같지 않다.

NOT Column_name = : 같지 않다.

NOT Column_name > : 보다 크지 않다.

 

* SQL연산자

between a and b : a와 b 사이에 있다. (a, b값 포함)

in (list) : list의 값 중 어느 하나와 일치한다.

like : 문자형태와 일치한다. (%, _ 사용)

        %는 값이 없거나 하나이상, _는 하나의 문자

in null : null 값을 가졌다.

not between a and b : a와 b사이에 있지 않다. (a, b값 포함하지 않음)

not in (list) : list의 값과 일치하지 않는다.

not like : 문자 형태와 일치하지 않는다.

not is null : null 값을 갖지 않는다.

 

* 논리연산자

and : 양쪽 컴포넌트의 조건이 true이면 true를 리턴

or : 한쪽 컴포넌트의 조건만이 true이면 true를 리턴

not : 이후의 조건이 false이면 true를 리턴

 

* 우선순위 규칙

1) 괄호

2) 수치연산자 > 결합연산자 > 비교연산자

3) is[not] null, like, [not] in > [not] between

4) not > and > or

 

* 문자형 함수

1) 변환함수

lower : 알파벳값을 소문자로 변환

  syntax -> lower(column|expression)

upper : 알파벳 값을 대문자로 변환

  syntax -> upper(column|expression)

initcap : 첫번째 글자만 대문자로 변환

  syntax -> initcap(column|expression)

2) 문자조작함수

concat : 두 문자열을 연결(합성)       

  syntax -> concat(column1|expression1, column2|expression2)

substr : 문자열 중 특정 문자 또는 문자열의 일부분을 선택       

  syntax -> substr(column|expression,m,[,n])

length : 문자열의 길이를 구함       

  syntax -> length(column|expression)

instr : 명명된 문자의 위치를 구함       

  syntax -> instr(column|expression,m[,n])

lpad : 왼쪽 문자자리 채움       

  syntax -> lpad(column|expression,n,''''string'''')

rpad : 오른쪽 문자자리 채움       

  syntax -> rpad(column|expression,n,''''string'''')

ltrim : 왼쪽 문자를 지움       

  syntax -> ltrim(column1|expression1, ''''string'''')

rtrim : 오른쪽 문자를 지움       

  syntax -> rtrim(column1|expression1, ''''string'''')

translate : 특정 문자열을 대체       

  syntax -> translate(column1|expression1, ''''string1'''', ''''string2'''')

replace : 특정 문자열을 대신       

  syntax -> replace(column1|expression1, ''''string1'''', ''''string2'''')

 

* 숫자형 함수

round : 숫자를 반올림

  syntax -> round(column1|expression1, n)

trunc : 숫자를 절삭

  syntax -> trunc(column1|expression1, n)

mod : 나머지를 구함

  syntax -> mod(column1|expression1, n)

power : 거듭제곱

  syntax -> power(column1|expression1, n)

sqrt : 제곱근

  syntax -> sqrt(column1|expression, n)

sign : 양수, 음수, 0인지를 구분

  syntax -> sign(column1|expression1)

chr : ascii값에 해당하는 문자를 구함

  syntax -> chr(column1|expression1)

 

* 날짜연산

date + number : 결과는 date : 일수를 날짜에 더함

date - number : 결과는 date : 날짜에서 일수를 뺌

date - date : 결과는 일수(숫자) : 어떤 날짜에서 다른 날짜를 뺌

date + number/24 : 결과는 date : 시간을 날짜에 더함

 

* 날짜함수

months_between : 두 날짜사이의 월수를 계산

  syntax -> months_between(date1, date2)

add_months : 월을 날짜에 더함

  syntax -> add_months(date1, n)

next_day : 명시된 날짜로부터 다음 요일에 대한 날짜를 나타냄

  syntax -> next_day(date1, ''''string''''|n)

last_day : 월의 마지막 날을 계산

  syntax -> last_day(date1)

round : 날짜를 반올림

  syntax -> round(date1 [,fmt]) -> fmt는 ''''month'''', ''''year''''

trunc : 날짜를 절삭

  syntax -> trunc(date1 [,fmt])

 

* 암시적 형변환 (Oracle서버가 자동형변환)

  varchar2 or char -> number

  varchar2 or char -> date

  number -> varchar2

  date -> varchar2

 

* 변환함수

to_char : 숫자나 문자값을 지정한 형식의 varchar2문자열로 변환

  syntax1 -> to_char(date, ''''fmt'''') : 날짜를 문자로

  syntax2 -> to_char(number, ''''fmt'''') : 숫자값을 문자로

to_number : 숫자를 포함하는 문자열을 숫자로 변환

  syntax -> to_number(char) : 숫자를 포함하는 문자열을 숫자로 변환

to_date : 날짜를 나타내는 문자열을 명시된 날짜로 변환

  syntax -> to_date(char [,''''fmt'''']) : 날짜를 나타내는 문자열을 명시된 날짜로 변환

 

* 날짜 형식

scc or cc : 세기(BC날짜에는 -를 붙임)

years indates yyyy of syyyy : 년(BC날짜에는 -를 붙임)

yyy or yy or y : 년의 마지막 3, 2 또는 1자리 수

y,yyy : 콤마가 있는 년

|yyy,|yy,|y,| : ISO표준에 바탕을 둔 4, 3, 2또는 1자리 수

syser or year : 문자고 표현된 년(BC날짜에는 _S를 붙임)

bc or ad : bc/ad 지시자

b.c or a.d : .이 있는 bc/ad지시자

q : 년의 4분의 1

mm : 두자리 값의 월

month : 9자리를 위해 공백을 추가한 월이름

mon : 세자리의 약어로 된 월이름

rm : 로마숫자 월

ww or w : 년이나 월의 주

ddd or dd or d : 년, 월 또는 주의 일

day : 9자리를 위해 공백을 추가한 요일 이름

dy : 세자리 약어로 된 요일 이름

j : Jilian day (bc4713년 12월 31일 이후의 요일 수)

 

* 시간형식

am or pm : 정오 지시자

a.m or p.m : .이 있는 정오 지시자

hh or hh12 or hh24 : 하루 중 시간(1-12, 0-23)

mi : 분(0-59)

ss : 초(0-59)

sssss : 자정 이후의 초(0-86399)

/ . , : 사용 문자가 결과에 다시 나타남

"문자" : 인용부호내의 문자(들)가 결과에 출력

 

* 숫자에 영향을 주는 접미사

th : 서수(ddth -> 4th)

sp : 명시한 수(ddsp -> four)

spth or thsp : 명시한 서수(ddspth -> fourth)

 

* 숫자형식

9 : 9의 수는 출력폭을 지정

0 : 맨 앞에 0을 출력

$ : $ 기호

L : 지역 화폐기호

. : 명시한 위치에 소수점

, : 명시한 위치에 콤마

mi : 우측에 마이너스 기호(음수 값)

pr : 음수를 ()로 묶음

eeee : 과학적인 부호 표기

v : 10을 n번 곱함

b : 0을 0아닌 공백으로 출력

 

* 기타함수

case, decode : case나 if-then-else-end if 문장의 조건적 조회

1) case

  syntax -> case expr when 조건 then 반환값

                               [when 조건 then 반환값]

                                ...

                               else 반환값

                 end

2) decode

  syntax -> decode(col|expr, search1, result1[,search2, result2, ...][,default])

 

* 중첩함수 : 여러 단일행함수의 중첩

  syntax -> f3(f2(f1()))

 

* 그룹함수

avg(distinct|all|n) : null값을 제외한 n개 행의 평균값

count(distinct|all|expr|*) : null이 아닌 행의 개수

max(distinct|all|expr) : 최대값

min(distinct|all|expr) : 최소값

stddev(distinct|all|n) : null값을 제외한 n의 표준편차

sum(distinct|all|n) : null값을 제외한 n의 합계

variance(distinct|all|n) : null값을 제외한 n의 분산

 

* 그룹함수

  syntax : select group_function(column) [,group_function(column)...]

              from table_name

              [where condition]

              [ order by column]

 

* 그룹핑

  syntax : select group_function(column) [,group_function(column)...]

              from table_name

              [where condition]

              [group by group_by_expression]

              [having group_condition]

              [ order by column]

 

* 조인

1) cartesian join : 모든 가능한 행들의 조인 (결과가 의미없는 경우가 대부분)

2) equi join(inner join) : 조인 조건이 정확히 일치하는 경우 사용 (일반적으로 PK, FK사용)

  syntax : select table1.column1 [,table2.column2, ...]

              from table1, table2

              where table1.column1 = table2.column2;

3) non-equi join : 조인 조건이 정확히 일치하지 않는 경우에 사용

4) outer join : 조인 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력

                    조인시킬 값이 없는 쪽에 (+)연산자 적용

  syntax : select table1.column1 [,table2.column2, ...]

              from table1, table2

              where table1.column1 = table2.column2(+);

5) self join : 하나의 테이블에서 행들을 조인하고자 할 경우에 사용

 

* SET연산자

  syntax : select *|column1[,column2, column3, ...]

              from table1

              ...

             set operator

              select *|column1[,column2, column3, ...]

              from table2

              ...

             [order by column|expression];

1) union : 각 결과의 합 (합집합:중복되는 값은 한번만 출력)

2) union all : 각 결과의 합 (합집합:중복되는 값 그대로 출력)

3) intersect : 각 결과의 중복되는 부분만 출력 (교집합)

4) minus : 첫번째 결과에서 두번째 결과를 뺌 (차집합)

 

* 서브쿼리

  syntax : select select_list

              from table

              where expression

              operator

              (select select_list

              from table

              where expression)

 

* 서브쿼리에서의 연산자(operator)

1) in : 2개 이상의 값을 리턴하는 서브쿼리에 대해 비교연산자를

         기술하면 에러가 발생. 이런 경우 서브쿼리에서 리턴된 목록의

         각각과 비교하여 쿼리를 수행하는 연산자

2) any : 서브쿼리에서 리턴된 목록의 각각의 값과 비교

3) all : 서브쿼리에서 리턴된 목록의 모든 값과 비교

4) exists: 서브쿼리에서 적어도 1개의 행을 리턴하면 논리식은 참

 

* 다중열 서브쿼리

  syntax : select *|column1[,column2, ...]

              from table

              where (column1, column2 ...)

              in

              (select column1, column2, ...

              from table

              where condition);

 

* SQL*PLUS 명령어

a[ppend] text : 현재 편집라인의 끝에 text를 추가

c[hange]/old/new:현재 편집 라인의 old문자를 new문자로 바꿈

del [n] : n라인을 삭제

i[nput] [text] : 현재 편집 라인 다음에 라인을 추가하여 text를 추가

l[ist] [n] : SQL문장을 보여주고, 편집 라인을 이동

n text : n번재 라인을 text로 바꿈

r[un] : buffer에 있는 명령어를 실행한다.(/와 동일)

edit [filename[.ext]] : 지정된 파일의 내용이나 버퍼의 내용을 운영체제의

                               문자편집기로 불러온다.

sav[e] [filename[.ext]] [rep[lace] | app[end]]

 : SQL버퍼의 내용을 파일에 저장하고 기존 파일에 추가하기 위해서 append를,

   기존 파일에 중복해서 쓰려면 replace를 사용한다. 기본적인 파일 확장자는 sql이다.

sta[rt] [filename[.ext]] : 지정된 파일을 수행한다. start라는 명열 대신에 @를

                                   사용할 수 있다. 파일 확장자가 .sql이 아니면 파일 확장자를 명시

get [filename[.ext]] : SQL버퍼에 파일의 내용을 기록한다. 파일명의 기본적인 확장자는

                               .lis또는 .lst이다

spo[ol] [filename[.ext]] [off | out] : SQL*PLUS의 내용을 파일에 저장

host : sql*plus안에서 호스트 운영체제의 명령어를 실행한다.

! : 운영체제 shell로 나들이

!vi file_name.sql : file_name.sql을 vi편집기로 부름 (unix)

 

* set : sql*plus의 환경설정

  syntax : set 시스템변수 값

- 앞 숫자는 기본값

array[size] {20|n} : 데이터베이스 데이터 패치의 크기를 설정

colsep { | text} : 열 사이에 출력되는 문자를 설정 (디폴트:공백하나)

feed[back] {6|off|on} : 질의가 최소한 n개이어야 row의 수를 출력

hea[ding] {off|on} : 열의 heading을 출력할지의 여부 결정

lin[esize] {80|n} : 라인당 문자의 수

long {80|n} : long값을 출력하기 위해 최대 폭을 설정

pages[ize] {24|n} : page당 line수를 지정

pau[se] {off|on|text} : 화면제어를 함

term[out] {off|on} : 결과를 화면에 출력할지의 여부를 결정

col[umn] [column_option] : 열 포맷을 제어

    syntax : col[umn] [{column|alias} [option]]

    option : cle[ar] : 어떤 열의 형식을 해제

               for[mat] format : 열 데이터의 디스플레이를 변경

               hea[ding] text : 열 헤딩을 설정, 수직 바(|)는 헤딩 라인을 한줄 띄움

               jus[tify] [align] : 열 heading을 정렬(좌, 우, 중간)

              nopri[nt] : 열을 숨김

              nul[l] text : null일때 디스플레이할 텍스트 명시

              pri[nt] : 열을 보여줌

              tru[ncated] : 디스플레이 되는 첫번째 라인의 마지막 문자열을 절삭

              wra[pped] : 문자열이 끝나면 다음 라인으로 이동

tti[tle] [text|off|on] : 리포트의 머리말을 명시

bti[tle] [text|off|on] : 리포트의 꼬리말을 명시

bre[ak] [on report_element] : 중복값을 제거하고 라인 피드로 행들을 단락지음

                                           -> 해제시 clear break

  syntax : break on column[|alias|row] [skip n|dup|page] on ... [on report]

              page : break값이 변경될 대 새로운 page로 skip

              skip n : break값이 변경될 때 n만큼 줄을 skip(column, row, page, report)

              duplicate : 중복되는 값을 출력

compute : SQL*PLUS명령어를 이용하여 요약된 계산을 한다.

              해제시 -> clear compute

  syntax : compute function of compute_column on break_column

              function : count, num, max, min, sum, avg, std, var중 하나

             compute_column : 계산에 사용되는 column이나 식

             break_column : break명령으로 기술된 column

 

* 치환변수

& : 리턴되는 데이터를 동적으로 제한, 변수를 인식

 

* 사용자 변수 정의

1) define variable = value : char데이터형 사용자 변수를 생성하고 값을 할당

2) define variable : 변수, 변수 값, 변수 데이터형을 출력

3) define : 값과 데이터형을 가진 모든 데이터형을 출력

4) accept : 사용자 입력 라인을 읽고 그것을 변수에 저장

    syntax : accept variable [datatype] [for[mat] format] [prompt text] [hide]

                variable : 값을 저장하는 변수의 이름

                              존재하지 않으면 SQL*PLUS가 그것을 생성하여 사용

                datatype : number, char 또는 date, char는 최대 길이 240바이트

                               date는 형식 모델을 다시 검사하고 데이터형은 char

                format : 형식 모델을 명시(예:a10, 9,999)

                text : 사용자가 값을 입력하기 전에 값을 출력

                hide : 사용자 입력을 숨긴다.(예:패스워드)

 

* 오라클 객체

1) table : 행과 열로 구성된 기본적인 저장 구조

2) view : 하나 이상의 테이블에서 데이터의 부분집합을 논리적으로 표현

3) sequence : 고유한 번호를 자동으로 발생시키는 객체로 주로 PK값 생성에 사용

4) index : 질의(select) 성능을 향상시키기 위하여 사용하는 물리적인 저장 구조

5) synonym : 객체에 대한 이름을 부여

 

* 테이블 생성

  syntax : create table [schema.]table_name

                       (column datatype [default expr] [column_constraint],

                       ...

                       [table_constraint]);

 

* 오라클 data type

varchar2(n) : 가변길이 문자 데이터 (1~4000byte)

char(n) : 고정 길이 문자 데이터 (1~2000byte)

number(p, s) : 전체 p자리 중 소수점 이하 s자리 (p:1~38, s:-84~127)

date : 7byte(bc4712년 1월1일부터 ad9999년 12월 31일)

long :가변길이 문자 데이터 (1~2Gbyte)

clob : 단일 바이트 가변 길이 문자 데이터 (1~4Gbyte)

raw(n) : n byte의 원시 이진 데이터 (1~2000)

long raw : 가변 길이 원시 이진 데이터 (1~2Gbyte)

blob : 가변 길이 이진 데이터 (1~4Gbyte)

bfile : 가변 길이 외부 파일에 저장된 이진 데이터 (1~4Gbyte)

 

* constraints (제약)

- primary key(pk) : 유일하게 테이블의 각행을 식별 (not null과 unique 동시 만족)

- foreign key(fk) : 열과 참조된 열 사이의 외래키 관계를 적용하고 설정합니다.

- unique key(uk) : 테이블의 모든 행을 유일하게 하는 값을 가진 열 (null을 허용)

- not null(nn) : 열은 null값을 포함할 수 없습니다.

- check(ck) : 참이어야 하는 조건을 지정함(대부분 업무 규칙을 설정)

1) column level

  syntax : column datatype [constraint constraint_name] constraint_type

2) table level

  syntax : column datatype,

              ...

              [constraint constraint_name] unique(column1[,column2...])

 

* subquery를 사용한 테이블 생성

  syntax : create table table_name [column1[,column2...]] as subquery

 

* 테이블 수정 (add 열추가, modify 열수정, drop 열삭제)

  syntax : alter table table_name

              add (column datatype [default expr]

              [,column datatype [default expr]...]

 

* 제약조건 수정 (add 제약추가, drop 제약삭제) : 제약변경은 못함

  syntax : alter table table_name

              add [constraint constraint_name] constraint_type (column);

  - add자리에 disable, enable로 제약조건활성화 결정할 수 있다.

 

* 객체 이름 변경

  syntax : rename old_name to new_name

 

* truncate table (테이블 비움:롤백불가)

  syntax : truncate table table_name

 

* 테이블에 주석문 추가

  syntax : comment on table table_name | column table.column is ''''text'''';

 

* 테이블 삭제

  syntax : drop table table_name

 

* DML

insert : 테이블에 새로운 행 추가

  syntax : insert into table_name [(column1[, column2, ...])]

              values (value1[, value2 ...]);

update : 테이블의 행 내용을 변경

  syntax : update table_name

              set column1 = value1 [,column2 = value2, ...]

              [where condition];

delete : 테이블의 행 삭제

  syntax : delete [from] table_name

              [where condition];

merge : 행이 존재하면 update, 새로운 행이면 insert

           merge into table_name as table_alias

           using (table/view/sub_query) as alias

           on (join condition)

           when matched then

                    update set

                    col1 = col1_val1, col2 = col2.val2

           when not matched then

                    insert (column_list) values (column_values);

commit : 저장되지 않은 모든 변경 사항을 Database에 저장

  syntax : commit;

savepoint : savepoint 설정

  syntax : savepoint name;

rollback : 저장되지 않은 모든 변경 사항을 취소

  syntax : rollback [to savepoint name]

 

* sequence (nextval, currval 사용)

  syntax : create sequence sequence_name

                        [increment by n]

                        [start witn n]

                        [{maxvalue n | nomaxvalue}]

                        [{minvalue n | nominvalue}]

                        [{cycle | nocycle}]

                        [{cache | nocache}]

  - sequence_name : sequence의 이름입니다.

  - increment by n : 정수 값인n으로 sequence번호 사시의 간격을 지정.

                            이 절이 생략되면 sequence는 1씩 증가.

  - start with n : 생성하기 위해 첫번째 sequence를 지정.

                       이 절이 생략되면 sequence는 1로 시작.

  - maxvalue n : sequence를 생성할 수 있는 최대 값을 지정.

  - nomaxvalue : 오름차순용 10^27 최대값과 내림차순용 -1의 최소값을 지정.

  - minvalue n : 최소 sequence를 지정.

  - nominvalue : 오름차순용 1과 내림차순용 -(10^26)의 최소값을 지정

  - cycle | nocycle : 최대 도는 최소갑에 도달한 후에 계속 값을 생성할지의

                             여부를 지정, nocycle이 디폴트.

  - cache | nocache : 얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고

                                유지하는가를 지정. 디폴트로 오라클 서버는 20을 cache

 

* sequence 수정

  syntax : alter sequence sequence_name

              ... (생성과 같다)

 

* sequence 제거

  syntax : drop sequence sequence_name

 

* view 생성 (simple view)

  syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]

              as subquery

              [with check option [constraint constraint_name]]

              [with read only]

  - or replace : 이미 존재한다면 다시 생성한다.

  - force : base table유무에 관계없이 view를 만든다.

  - noforce : 기본 테이블이 존재할 경우에만 view를 생성한다.

  - view_name : view의 이름

  - alias : subquery를 통해 선택된 값에 대한 컬럼명이 된다.

  - subquery : select 문장을 기술한다.

  - with check option : view에 의해 엑세스 될 수 있는 행만이 입력, 갱신될 수 있다.

  - constraint : check option 제약 조건에 대해 지정된 이름이다.

  - with read only : 이 view에서 DML이 수행될 수 없도록 한다.

 

* view 생성 (complex view)

  syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]

              as subquery 

              [with check option [constraint constraint_name]]

              [with read only]            

  - complex view에서는 둘이상의 테이블이 조인되어 view에 나타난다.

 

* view 제거

  syntax : drop view view_name;

 

* inline view : from절에 subquery를 사용

 

* top-n analysis

  syntax : select [column_list], rownum

              from (select [column_list] from table order by top-n_column)

              where rownum <= n;

 

* index 종류

  - unique index : 지정된 열의 값이 고유함을 보장

  - non-unique index : 데이터를 검색할 때 가장 빠른 결과를 보장

  - single column index : 하나의 열만 인덱스에 존재

  - composite index : 여러 열을 결합하여 하나의 인덱스를 생성(16개의 열까지)

 

* index 생성 (사용자정의)

  syntax : create index index_name

              on table_name (column1[,column2, ...]);

 

* index 제거

  syntax : drop index index_name;

 

* synonym 생성

  syntax : create [public] synonym synonym_name

              for object_name;

 

* synonym 제거

  syntax : drop [public] synonym synonym_name;

 

* user 생성

  syntax : create user user_name

              idntified by password;

 

* 권한부여

  syntax : grant system_privilege1[,system_privilege2, ...]

              to user_name1[,user_name2, ...]

              [with admin option]

  - with admin option을 주면 부여받은 권한을 다시 부여할 수 있다.

 

* 권한제거

  syntax : revoke system_privilege1[,system_privilege2, ...] | role1[,role2, ...]

              from {user1[,user2, ...] | role1[,role2 ...] | public};

 

* Role 생성 : Role은 권한(privilege의 모임)

  syntax : create role role_name;

 

* Role 부여

  syntax : grant role_name to user_name;