'Database' 카테고리의 글 목록

Database에 해당되는 글 3건

[Database]Oracle 함수정리 Database 2012. 5. 30. 08:18

http://www.statwith.pe.kr/ORACLE/functions001.htm

1. 문자 함수
1-1) CHR
1-2) CONCAT 함수
1-3) INITCAP 함수
1-4) LOWER 함수
1-5) LPAD 함수
1-6) LTRIM 함수
1-7) NLS_INITCAP 함수
1-8) NLS_LOWER 함수
1-9) NLSSORT 함수
1-10) NLS_UPPER 함수
1-11) REPLACE 함수
1-12) RPAD 함수
1-13) RTRIM 함수
1-14) SOUNDEX 함수
1-15) SUBSTR 함수
1-16) TRANSLATE 함수
1-17) TREAT 함수
1-18) TRIM 함수
1-19) UPPER 함수
1-20) ASCII 함수
1-21) INSTR 함수
1-22) LENGTH 함수

2. 날짜 처리함수(datetime function)
2-1) ADD_MONTHS 함수
2-2) CURRENT_DATE 함수
2-3) URRENT_TIMESTAMP 함수
2-4) DBTIMEZONE 함수
2-5) EXTRACT(datetime) 함수
2-6) FROM_TZ 함수
2-7) LAST_DAY 함수
2-8) LOCALTIMESTAMP 함수
2-9) MONTHS_BETWEEN 함수
2-10) NEW_TIME 함수
2-11) NEXT_DAY 함수
2-12) NUMTODSINTERVAL 함수
2-13) NUMTOYMINTERVAL 함수
2-14) ROUND(date) 함수
2-15) SESSIONTIMEZONE 함수
2-16) SYS_EXTRACT_UTC 함수
2-17) SYSDATE 함수
2-18) SYSTIMESTAMP 함수
2-19) TO_DSINTERVAL 함수
2-20) TO_TIMESTAMP 함수
2-21) TO_TIMESTAMP_TZ 함수
2-22) TO_YMINTERVAL 함수
2-23) TRUNC(date) 함수
2-24) TZ_OFFSET 함수

3.데이터 형 변환 함수(conversion function)
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수

4. 기타함수(miscellaneous single row function)
4-1) BFILENAME 함수
4-2) COALESCE 함수
4-3) DECODE 함수
4-4) DEPTH 함수
4-5) DUMP 함수
4-6) EMPTY_BLOB 함수
4-7) EMPTY_CLOB 함수
4-8) EXISTSNODE 함수
4-9) EXTRACT(XML) 함수
4-10) EXTRACTVALUE 함수
4-11) GREATEST 함수
4-12) LEAST 함수
4-13) NLS_CHARSET_DECL_LEN 함수
4-14) NLS_CHARSET_ID 함수
4-15) NLS_CHARSET_NAME 함수
4-16) NULLIF 함수
4-17) NVL2 함수
4-18) PATH 함수
4-19) SYS_CONNECT_BY_PATH 함수
4-20) SYS_CONTEXT 함수
4-21) SYS_DBURIGEN 함수
4-22) SYS_EXTRACT_UTC 함수
4-23) SYS_GUID 함수
4-24) SYS_XMLAGG 함수
4-25) SYS_XMLGEN 함수
4-26) UID 함수
4-27) USER 함수
4-28) USERENV 함수
4-29) VSIZE 함수
4-30) XMLAGG 함수
4-31) XMLCOLATTVAL 함수
4-32) XMLCONCAT 함수
4-33) XMLFOREST 함수
4-34) XMLELEMENT 함수

5.그룹함수 Aggregate 함수
5-1) AVG* 함수
5-2) CORR* CORR* 함수
5-3) COUNT* 함수
5-4) COVAR_POP 함수
5-5) COVAR_SAMP 함수
5-6) CUME_DIST 함수
5-7) DENSE_RANK 함수
5-8) FIRST 함수
5-9) GROUP_ID 함수
5-10) Grouping 함수
5-11) GROUPING_ID 함수
5-12) LAST 함수
5-13) MAX 함수
5-14) MIN 함수
5-15) PERCENTILE_CONT 함수
5-16) PERCENTILE_DISC 함수
5-17) PERCENT_RANK 함수
5-18) RANK 함수
5-19) REGR_(linear regression) function* 함수
5-20) STDDEV 함수
5-21) STDDEV_POP 함수
5-22) STDDEV_SAMP 함수
5-23) SUM 함수
5-24) VAR_POP 함수
5-25) VAR_SAMP 함수
5-26) VARIANCE 함수
5-27) Grouping sets 함수

6. Analytic 함수
6-1) AVG* 함수
6-2) CORR* CORR* 함수
6-3) COUNT* 함수
6-4) COVAR_SAMP 함수
6-5) CUME_DIST 함수
6-6) DENSE_RANK 함수
6-7) FIRST 함수
6-8) FIRST_VALUE 함수
6-9) LAG 함수
6-10) LAST_VALUE 함수
6-11) LEAD 함수
6-12) NTILE 함수
6-13) RATIO_TO_REPORT 함수
6-14) ROW_NUMBER 함수

7. 객체 참조 함수
7-1) REF 타입

8. PseudoColumn을 의미하는 것
8-1) ROWID 컬럼
8-2) ROWNUM 컬럼

1-1) CHR 함수
--------------------------------------------------------------------------------

입력된 수의 바이너리 코드에 해당하는 문자를 반환한다.

【예제】
SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)
2 from dual;

CHR(7
-----
KOREA

SQL>

1-2) CONCAT 함수
--------------------------------------------------------------------------------

입력되는 두 문자열을 연결하여 반환한다.
입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.

첫 번째 문자열 타입 두 번째 문자열 타입 반환되는 문자열 타입 CLOB NCLOB NCLOB NCLOB NCHAR NCLOB NCLOB CHAR NCLOB NCHAR CLOB NCLOB

【예제】
SQL> select concat('Republic of',' KOREA') from dual;

CONCAT('REPUBLICO
-----------------
Republic of KOREA

SQL>

1-3) INITCAP 함수
--------------------------------------------------------------------------------

initcap('string‘) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다

【예제】
SQL> select initcap('beautiful corea') from dual;

INITCAP('BEAUTI
---------------
Beautiful Corea

SQL>

1-4) LOWER 함수
--------------------------------------------------------------------------------

lower(string) 함수는 입력된 문자열을 소문자로 반환한다.

【예제】
SQL> select lower('Beautiful COREA') from dual;

LOWER('BEAUTIFU
---------------
beautiful corea

SQL>

1-5) LPAD 함수
--------------------------------------------------------------------------------

lpad(char1,n,char2) 함수는
지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 왼쪽부터 char2로 채워서 출력한다.

【형식】
lpad (char1, n [, char2] )

【예제】
SQL> select lpad ('Corea', 12, '*') from dual;

LPAD('COREA'
------------
*******Corea

SQL>

1-6) LTRIM 함수
--------------------------------------------------------------------------------

LTRIM(문자열, 문자)함수는 문자열중
좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】
ltrim(char [,set] )

【예제】
SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;

LTRIM('XYXXX
------------
XxyLAST WORD

SQL>

1-7) NLS_INITCAP 함수
--------------------------------------------------------------------------------

nls_initcap(‘string’) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로
나머지는 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_initcap ( char [,'nlsparam'] )

【예제】
SQL> select nls_initcap('beautiful corea', 'nls_sort=binary')
2 from dual;

NLS_INITCAP('BE
---------------
Beautiful Corea

SQL> select nls_initcap('beautiful corea','nls_sort=XDutch')
2 from dual;

NLS_INITCAP('BE
---------------
Beautiful Corea

SQL>

1-8) NLS_LOWER 함수
--------------------------------------------------------------------------------

nls_lower(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_lower ( char [,'nlsparam'] )

【예제】
SQL> select nls_lower('CITTA''','nls_sort=XGerman') from dual;

NLS_LO
------
citta'

SQL>

1-9) NLSSORT 함수
--------------------------------------------------------------------------------

nlssort(‘string’) 함수는 입력 문자열을 소팅하여 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nlssort ( char [,'nlsparam'] )

【예제】
SQL> select * from emp
2 order by nlssort(name, 'nls_sort=XDanish');

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1104 jijoe 220 100
1103 kim 250 100

SQL>

1-10) NLS_UPPER 함수
--------------------------------------------------------------------------------

nls_upper(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】
nls_upper ( char [,'nlsparam'] )

【예제】
SQL> select nls_upper('gro?e') from dual;

NLS_U
-----
gro?e

SQL> select nls_upper('gro?e','nls_sort=XGerman')
2 from dual;

NLS_UP
------
grosse

SQL>

1-11) REPLACE 함수
--------------------------------------------------------------------------------

이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다.
치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.

【형식】
replace (char, search_string [, replacement_string] )

【예제】
SQL> select replace('aaabb','a','b') from dual;

REPLA
-----
bbbbb

SQL> select replace('aaabb','a') from dual;

RE
--
bb

SQL>

1-12) RPAD 함수
--------------------------------------------------------------------------------

rpad(char1,n,char2) 함수는 지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 오른쪽부터 char2로 채워서 출력한다.

【형식】
rpad (char1, n [, char2] )

【예제】
SQL> select rpad('Corea',12,'*') from dual;

RPAD('COREA'
------------
Corea*******

SQL>

1-13) RTRIM 함수
--------------------------------------------------------------------------------

RTRIM(문자열, 문자)함수는 문자열중
우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】
rtrim(char [,set] )

【예제】
SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;

RTRIM exam
----------
BROWINGyxX

SQL>

1-14) SOUNDEX 함수
--------------------------------------------------------------------------------

soundex(‘char’) 함수는 char과 같은 발음의 이름을 표현한다.

【예제】
SQL> select name from emp;

NAME
----------
Cho
Joe
kim
jijoe

SQL> select name from emp
2 where soundex(name) = soundex('jo');

NAME
----------
Joe

SQL>

1-15) SUBSTR 함수
--------------------------------------------------------------------------------

substr(str,m,n) 함수는 문자열 str 중에서 특정 위치 m으로부터 특정 길이n 만큼의 문자를 출력한다.
m이 0이나 1이면 문자열의 첫글자를 의미하고,
n이 생략되면 문자열의 끝까지를 의미한다.
m이 음수이면 뒤쪽으로부터의 위치를 의미한다.

SUBSTRB는 character 대신 byte를 사용하고,
SUBSTDC는 unicode를 사용하며,
SUBSTR2는 UCS2 codepoint를 사용하고,
SUBSTR4는 UCS4 codepoint를 사용한다.

【형식】
{SUBSTR|SUBSTRB|SUBSTRC|SUNBSTD2|SUBSTR4}
( string, position [,substring_length] )

【예제】
SQL> select substr('abcdesfg', 3,2) from dual;

SU
--
cd

SQL> select substr('abcdefg',3) from dual;

SUBST
-----
cdefg

SQL> select substr('abcdefg', -3,2) from dual; ☜ 뒤에서 3번째부터 2글자를 의미한다.
SU
--
ef

SQL>

1-16) TRANSLATE 함수
--------------------------------------------------------------------------------

TRANSLATE (‘char’,‘from_string’,‘to_string’) 함수는
char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를
to_string문자로 각각 변경한다.

【형식】
TRANSLATE ('char','from_string','to_string')

【예제】
SQL> select translate('ababccc','c','d') from dual;

TRANSLA
-------
ababddd

SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TRANSLA
-------
9XXX999

SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
3 from dual;

TRAN
----
2229

SQL>

1-17) TREAT 함수
--------------------------------------------------------------------------------

TREAT 함수는 선언된 타입을 변경함으로써, 수퍼타입을 서브타입인 것처럼 처리할 수 있도록 한다.

【형식】
TREAT ( expr AS [ REF] [schema . ] type )

【예제】
SQL> select x.p.empno from person_table p;
select x.p.empno from person_table p
*
ERROR at line 1:
ORA-00904: "X"."P"."EMPNO": invalid identifier

SQL> select treat(x.p as employee).empno empno,
2 x.p.last_name last_name
3 from person_table x;

EMPNO LAST_NAME
---------- --------------------
Seoul
1234 Inchon
5678 Arirang

SQL>

【예제】
SQL> select name, TREAT(VALUE(p) AS employee_t).salary salary
2 FROM person p;

NAME SALARY
---------------------- ---------
Bob
Joe 100000
Tim 1000

SQL>

1-18) TRIM 함수
--------------------------------------------------------------------------------

이 함수는 LTRIM과 RTRIM 함수를 결합한 형태로
문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.
LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,
TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.
BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.

【형식】
TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]
trim_source )

【예제】
SQL> select trim (0 from 000123400) from dual;

TRIM
----
1234

SQL> select trim(trailing 'a' from 'abca') from dual;

TRI
---
abc

SQL> select trim(leading 'a' from 'abca') from dual;

TRI
---
bca

SQL> select trim(both 'a' from 'abca') from dual;

TR
--
bc

SQL>

1-19) UPPER 함수
--------------------------------------------------------------------------------

upper(string) 함수는 입력된 문자열을 대문자로 반환한다.

【예제】
SQL> select upper('Beautiful COREA') from dual;

UPPER('BEAUTIFU
---------------
BEAUTIFUL COREA

SQL>

1-20) ASCII 함수
--------------------------------------------------------------------------------

ASCII
ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.
char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.

【예제】
SQL> select ascii('Korea') from dual;

ASCII('KOREA')
--------------
75

SQL> select ascii('K') from dual;

ASCII('K')
----------
75

SQL>

1-21) INSTR 함수
--------------------------------------------------------------------------------

이 함수는 문자 스트링 중에서
지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.

【형식】
{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}
( string, substring [, position [,occurrence] ] )

【예제】
SQL> select instr('Corea','e') from dual;

INSTR('COREA','E')
------------------
4

SQL> select instr('corporate floor','or',3,2) from dual;

INSTR('CORPORATEFLOOR','OR',3,2)
--------------------------------
14

SQL> select instrb('corporate floor','or',5,2) from dual;

INSTRB('CORPORATEFLOOR','OR',5,2)
---------------------------------
14

SQL>

1-22) LENGTH 함수
--------------------------------------------------------------------------------

LENGTH(char) 함수는 char의 길이를 반환한다.
LENGTHB는 character 대신 byte를 사용하고,
LENGTHC는 unicode를 사용하며,
LENGTH2는 UCS2 codepoint를 사용하고,
LENGTH4는 UCS4 codepoint를 사용한다.

【형식】
{LENGTH| LENGTHB| LENGTHC| LENGTH2| LENGTH4} (char)

【예제】
SQL> select length('Corea') from dual;

LENGTH('COREA')
---------------
5

SQL> select lengthb('Corea') from dual;

LENGTHB('COREA')
----------------
5

SQL>

2-1) ADD_MONTHS 함수
--------------------------------------------------------------------------------

ADD_MONTHS
ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.

【예제】
SQL> select current_date today, add_months(current_date,1) "next month"
2 from dual;

TODAY next mont
--------- ---------
29-JUL-04 29-AUG-04

SQL>

2-2) CURRENT_DATE 함수
--------------------------------------------------------------------------------

이 함수는 현재 session의 날짜 정보를 반환한다.
【예제】
SQL> select current_date from dual;

CURRENT_D
---------
31-JUL-04

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select current_date from dual;

CURRENT_DATE
--------------------
31-JUL-2004 09:31:57

SQL> alter session set time_zone='-5:0';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
-05:00

SQL>

2-3) URRENT_TIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;

CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04

SQL>

2-4) DBTIMEZONE 함수
--------------------------------------------------------------------------------

데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00

SQL>

2-5) EXTRACT(datetime) 함수
--------------------------------------------------------------------------------

특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터
원하는 날짜 영역을 추출하여 출력한다.

【형식】
EXTRACT ({year|month|day|hour|minute|second|
timezone_hour|timezone_minute|
timezone_region|timezone_abbr}
FROM {datetime_value_expr|interval_value_rxpr})

【예제】
SQL> select extract(year from date '2004-8-2') from dual;

EXTRACT(YEARFROMDATE'2004-8-2')
-------------------------------
2004

SQL>

2-6) FROM_TZ 함수
--------------------------------------------------------------------------------

이 함수는 timestamp 값을 timestamp with time zone 값으로 변환한다.

【형식】
FROM_TZ ( timestamp_value, time_zone_value)

【예제】
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;

FROM_TZ(TIMESTAMP'2004-8-1108:00:00','3:00')
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00

SQL>

2-7) LAST_DAY 함수
--------------------------------------------------------------------------------

이 함수는 지정한 달의 마지막 날을 출력한다.

【형식】
LAST_DAY ( date )

【예제】
SQL> select sysdate, last_day(sysdate) "last day",
2 last_day(sysdate)- sysdate "Days Left"
3 from dual;

SYSDATE last day Days Left
--------- --------- ----------
04-AUG-04 31-AUG-04 27

SQL>

2-8) LOCALTIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 timestamp의 현재 날짜와 시각을 출력한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.

【형식】
localtimestamp [(timestamp_precision)]

【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;

SQL>

【예제】오류가 발생하는 이유를 잘 이해하자.
SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

1 row created.

SQL> select * from local_test;

COL1
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM

SQL>

2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------

MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는
날짜와 날짜 사이의 개월 수를 출력한다.

【예제】
SQL> select months_between
2 (to_date('02-02-2004','MM-DD-YYYY'),
3 to_date('01-01-2003','MM-DD-YYYY') ) "Months"
4 FROM dual;

Months
----------
13.0322581

SQL>

2-10) NEW_TIME 함수
--------------------------------------------------------------------------------

NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.
여기서 사용되는 zone은 다음 중의 하나이다.

AST,ADT : Atlantic Standard or Daylight Time
BST,BDT : Bering Standard or Daylight Time
CST,CDT : Central Standard or Daylight Time
EST,EDT : Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST,HDT : Alaska-Hawaii Standard or Daylight Time
MST,MDT : Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST,PDT : Pacific Standard or Daylight Time
YST,YDT : Yukon Standard or Daylight Time

【예제】
SQL> alter session set nls_date_format =
2 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select NEW_TIME(TO_DATE(
2 '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),
3 'AST', 'PST') FROM DUAL;

NEW_TIME(TO_DATE('11
--------------------
09-NOV-2004 21:23:33

SQL>

2-11) NEXT_DAY 함수
--------------------------------------------------------------------------------

NEXT_DAY(date,char) 함수는 date로부터 char로 명시한 가장 최근의 날짜를 출력한다.

【예제】
SQL> select next_day('02-AUG-2004','MONDAY') from dual;

NEXT_DAY('02-AUG-200
--------------------
09-AUG-2004 00:00:00

SQL>

2-12) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’

【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;

ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82

14 rows selected.

SQL>

2-13) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’

【예제】
SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;

ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84

14 rows selected.

SQL>

2-14) ROUND(date) 함수
--------------------------------------------------------------------------------

이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.
날짜 형식이 없으면 가장 가까운 날을 출력한다.

【형식】
ROUND( date [,fmt] )

【예제】
SQL> select localtimestamp, round(sysdate,'year') from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05

SQL> select localtimestamp,round(sysdate,'day') from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04

SQL> select localtimestamp,round(sysdate) from dual;

LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04

SQL>

2-15) SESSIONTIMEZONE 함수
--------------------------------------------------------------------------------

이 함수는 현재 세션의 시간대역을 출력한다.

【예제】
SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00

SQL> select sessiontimezone, tz_offset(sessiontimezone) from dual;

SESSIONTIMEZONE
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00

SQL>

2-16) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------

sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.

【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM

SQL>

2-17) SYSDATE 함수
--------------------------------------------------------------------------------

이 함수는 오늘 현재 날짜와 시각을 출력한다.

【예제】
SQL> select sysdate, current_timestamp from dual;

SYSDATE
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00

SQL> select to_char
2 (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'MM
-------------------
08-04-2004 13:53:18

SQL>

2-18) SYSTIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 시스템의 날짜를 출력한다.

【예제】
SQL> select sysdate,systimestamp,localtimestamp from dual;

SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM

SQL>

2-19) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】
to_dsinterval ( char [ ‘nlsparam’] )

【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;

SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04

SQL>

2-20) TO_TIMESTAMP 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다

【형식】
to_timestamp ( char [,fmt ['nlsparam'] ] )

【예제】
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;

TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM

SQL>

2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다.

【형식】
to_timestamp_tz ( char [,fmt ['nlsparam'] ] )

【예제】
SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',
2 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2004-8-201:30:00-3:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00

SQL>

2-22) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;

SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05

SQL>

2-23) TRUNC(date) 함수
--------------------------------------------------------------------------------

이 함수는 날짜를 절삭하여 출력한다.

【형식】
TRUNC ( date [.fmt] )

【예제】
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
2 from dual;

TRUNC(TO_
---------
01-JAN-04

SQL>

2-24) TZ_OFFSET 함수
--------------------------------------------------------------------------------

이 함수는 time zone의 offset 값을 출력한다.

【형식】
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
‘{+|-} hh:mi’ } )

【예제】
SQL> select sessiontimezone, tz_offset('ROK') from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00

SQL>

3-1) ASCIISTR 함수
--------------------------------------------------------------------------------

asciistr('string')의 string의 아스키 문자로 반환한다.
Ä
【예제】
SQL> select ascii('ABÄCDE') from dual;
☜ ABÄCDE의 두번째 Ä는 A에 움라우트(Umlaut)가 붙은 글씨이다.

ASCIIS
------
ABDCDE

SQL>

3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------

이 함수는 2진수 벡터를 10진수로 변환한다.

SQL> select bin_to_num(1,0,1,0) from dual;

BIN_TO_NUM(1,0,1,0)
-------------------
10

SQL>

3-3) CAST 함수
--------------------------------------------------------------------------------

데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.

【예제】데이터형식인 경우
SQL> select current_date from dual;

CURRENT_D
---------
30-JUL-04

SQL> select cast(current_date as timestamp) from dual;

CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM

SQL>

3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------

이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');

NAME
----------
jijoe

SQL> select rowid,name from emp;

ROWID NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe

SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-5) COMPOSE 함수
--------------------------------------------------------------------------------

입력된 스트링을 unicode로 나타낸다.

【예제】
SQL> select compose('aa' || unistr('\0308') ) from dual;

CO
--
aa

SQL>

3-6) CONVERT 함수
--------------------------------------------------------------------------------

입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자

【예제】
SQL> select convert('arirang','we8pc850') from dual;

CONVERT
-------
arirang

SQL>

3-7) HEXTORAW 함수
--------------------------------------------------------------------------------

HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL>

3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’

【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;

14 rows selected.

SQL>

3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’

【예제】
SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06

SQL>

【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;

14 rows selected.

SQL>

3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------

RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL> select rawtohex(raw_col) from test;

RAWTOHEX(RAW_COL)
--------------------
7D

SQL>

3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------

RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.

【예제】
SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL
--------------------
7D

SQL> select rawtonhex(raw_col) from test;

RAWTONHEX(RAW_COL)
--------------------
7D

SQL>

3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------

RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------

RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------

이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.

【형식】
TO_CHAR( nchar| clob | nclob)

【예제】
SQL> select to_char('01110') from dual;

TO_CH
-----
01110

SQL>

3-15) TO_CLOB 함수
--------------------------------------------------------------------------------

이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.

【형식】
TO_CLOBR({ lob_column | char})

【예제】
SQL> select to_clob('corea') from dual;

TO_CLOB('COREA')
--------------------------------------------------------------------------
corea

SQL>

3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】
to_dsinterval ( char [ ‘nlsparam’] )

【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;

SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04

SQL>

3-17) TO_LOB 함수
--------------------------------------------------------------------------------

TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.

【예제】
SQL> create table test2(zz clob);

Table created.

SQL> insert into test2
2 (select to_lob(p.raw_col) from test p);

SQL>

3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------

TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.

【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL>

3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------

이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.

【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar('Corea') from dual;

TO_NC
-----
Corea

SQL>

3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------

이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.

【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar(sysdate) from dual;

TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04

SQL>

3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------

이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.

【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])

【예제】
SQL> select to_nchar(1234) from dual;

TO_N
----
1234

SQL> select to_nchar(rownum) from test;

TO_NCHAR(ROWNUM)
----------------------------------------
1

SQL>

3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------

이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.

【형식】
TO_NCLOB({char|lob_column})

【예제】
SQL> select to_nclob('Corea') from dual;

TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea

SQL>

3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------

이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
문자 데이터를 number 형식의 숫자 데이터로 변환한다.

【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])

【예제】
SQL> select to_number('1234') from dual;

TO_NUMBER('1234')
-----------------
1234

SQL>

3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------

TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.

【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL> select dump(to_single_byte('Corea')) from dual;

DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97

SQL> select to_single_byte(chr(65)) from dual;

T
-
A

SQL>

3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;

SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05

SQL>

3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------

이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.

【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )

【예제】
SQL> select translate('Corea' USING char_cs) from dual;

TRANS
-----
Corea

SQL> select to_nchar('Corea') from dual;

TO_NC
-----
Corea

SQL>

3-27) UNISTR 함수
--------------------------------------------------------------------------------

UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.

【예제】
SQL> select unistr('abc\00e5\00f1\00f6') from dual;

UNISTR
------
abc??o

SQL> select unistr('Corea') from dual;

UNIST
-----
Corea

SQL>

4-1) BFILENAME 함수
--------------------------------------------------------------------------------

서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.
【형식】
bfilename ('디렉토리‘,’파일이름‘)

【예제】BFILE을 insert하는 예
SQL> connect system/manager

SQL> host mkdir /export/home/oracle/bfile

SQL> create directory bfile_dir as '/export/home/oracle/bfile';

Directory created.

SQL> grant read on directory bfile_dir to jijoe;

Grant succeeded.

SQL> connect jijoe/joe_password

SQL> create table bfile_doc (id number, doc bfile);

SQL> insert into bfile_doc

1 values(1111,bfilename('bfile_dir','unix.hwp'));

1 row created.

SQL>

4-2) COALESCE 함수
--------------------------------------------------------------------------------

이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다.

【예제】
SQL> select coalesce('','','arirang','kunsan') from dual;

COALESC
-------
arirang

SQL>

4-3) DECODE 함수
--------------------------------------------------------------------------------

DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,
조건2,결과값2,...,기본값);

【예제】
SQL> connect jijoe/jijoe_password
SQL> create table aa(
2 pid number(12) primary key,
3 addr varchar2(20),
4 name varchar2(10));

SQL> insert into aa values(1234,'kunsan','jijoe')
SQL> insert into aa values(3456,'seoul','sunny')

SQL> select * from aa;

PID ADDR NAME
---------- -------------------- ----------
1234 kunsan jijoe
3456 seoul sunny

SQL> select decode(pid,1234,name) name from aa;

NAME
----------
jijoe

SQL>
【예제】
SQL> desc ddd
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER(4)
NAME VARCHAR2(10)
HIRDATE DATE
DEPTNO NUMBER(5)

SQL> select * from ddd;

NO NAME HIRDATE DEPTNO
---------- ---------- --------- ----------
1 student1 01-JAN-04 10
2 student2 01-FEB-04 10
3 student3 01-MAR-04 20
4 student4 01-MAY-04 30

SQL> select count(decode(to_char(hirdate,'MM'),'01',1)) "JAN",
2 count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",
3 count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",
4 count(*) "Total"
5 from ddd
6 where to_char(hirdate,'MM') >= '01' AND
7 to_char(hirdate,'MM') <= '06';

JAN FEB MAR Total
---------- ---------- ---------- ----------
1 1 1 4

SQL>

4-4) DEPTH 함수
--------------------------------------------------------------------------------

DEPTH( correlation_integer) 함수는 UNDER_PATH나 EQUALS_PATH 조건과 함께 사용되는 보조함수이다.
이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.

【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1) DEPTH(2)
------------------------------------------ ----------
/xml.xsd 1

SQL>

4-5) DUMP 함수
--------------------------------------------------------------------------------

지정한 데이터의 위치와 길이 따위를 지정한 형식으로 반환한다.
【형식】
DUMP(expr [,반환형식[,시작위치[,길이]]] )

【예제】
SQL> select dump('Corea', 1016) from dual;

DUMP('COREA',1016)
-----------------------------------------------------
Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61

SQL> select dump('Corea', 8,3,2) "Octal" from dual;

Octal
---------------------
Typ=96 Len=5: 162,145

SQL> select dump('Corea',16,3,2) "ASCII" from dual;

ASCII
-------------------
Typ=96 Len=5: 72,65

SQL>

4-6) EMPTY_BLOB 함수
--------------------------------------------------------------------------------

EMPTY_BLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】
UPDATE print_media SET ad_photo = EMPTY_BLOB();

4-7) EMPTY_CLOB 함수
--------------------------------------------------------------------------------

EMPTY_CLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】
UPDATE print_media SET ad_photo = EMPTY_CLOB();

4-8) EXISTSNODE 함수
--------------------------------------------------------------------------------

이 함수는 node의 존재여부를 확인하여 그 결과를 반환한다.
0은 노드가 남아 있지 않은 경우이고,
1은 아직 노드가 존재하는 경우이다.

【형식】
EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select res,any_path
2 from resource_view
3 where existsnode(res, 'xdbconfig.xml') =0;

26 rows selected.
SQL>

4-9) EXTRACT(XML) 함수
--------------------------------------------------------------------------------

이 함수는 existsnode와 유사한 함수이다.

【형식】
EXTRACT(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select extract(res,'xdbconfig.xml')
2 from resource_view;

26 rows selected.
SQL>

4-10) EXTRACTVALUE 함수
--------------------------------------------------------------------------------

이 함수는 existsnode와 유사한 함수로써 node의 스칼라 값을 반환한다.

【형식】
EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )

【예제】
SQL> select * from resource_view;
SQL> select extractvalue(res,'xdbconfig.xml')
2 from resource_view;

26 rows selected.
SQL>

4-11) GREATEST 함수
--------------------------------------------------------------------------------

GREATEST (expr,...) 함수는 expr 중에서 가장 큰 값을 반환한다.

【예제】
SQL> select greatest(20,10,30) from dual;

GREATEST(20,10,30)
------------------
30

SQL>

4-12) LEAST 함수
--------------------------------------------------------------------------------

LEAST (expr,...) 함수는 expr 중에서 가장 작은 값을 반환한다.

【예제】
SQL> select least(20,10,30) from dual;

GREATEST(20,10,30)
------------------
10

SQL> select least('bb','aa','cc') from dual;

GR
--
aa

SQL>

4-13) NLS_CHARSET_DECL_LEN 함수
--------------------------------------------------------------------------------

NLS_CHARSET_DECL_LEN (byte_count , char_set_id) 함수는 nchar로 선언된 폭을 반환한다.

【예제】
SQL> select nls_charset_decl_len
2 (200, nls_charset_id('ja16eucfixed')) from dual

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
--------------------------------------------------------
100

SQL>

4-14) NLS_CHARSET_ID 함수
--------------------------------------------------------------------------------

nls_charset_id('text') 함수는 문자셋 이름에 대응하는 ID 번호를 반환한다.
여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.

【예제】
SQL> select nls_charset_id('ja16euc') from dual;

NLS_CHARSET_ID('JA16EUC')
-------------------------
830

SQL>

4-15) NLS_CHARSET_NAME 함수
--------------------------------------------------------------------------------

nls_charset_name('number') 함수는 문자섹 ID 번호에 대응하는 문자의 이름을 반환한다.

【예제】
SQL> select nls_charset_name(830) from dual;

NLS_CHA
-------
JA16EUC

SQL> select nls_charset_name(1) from dual;

NLS_CHAR
--------
US7ASCII

SQL>

4-16) NULLIF 함수
--------------------------------------------------------------------------------

NULLIF(expr1, expr2) 함수는
expr1과 expr2를 비교하여
같으면 null을 반환하고,
같지 않으면 expr1을 반환한다.

이는 CASE 문으로 쓰면 다음과 같다.
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

【예제】
SQL> select nullif('aa','AA') from dual;

NU
--
aa

SQL> select nullif('aa','aa') from dual;

NU
--

SQL>

4-17) NVL2 함수
--------------------------------------------------------------------------------

NVL2(expr1, expr2, expr3) 함수는
expr1이 null이 아니면 expr2를 반환하고,
expr1이 null이면 expr3을 반환한다.

【예제】
SQL> select nvl2('','Corea','Korea') from dual;

NVL2(
-----
Korea

SQL> select nvl2('aa','Corea','Korea') from dual;

NVL2(
-----
Corea

SQL>

4-18) PATH 함수
--------------------------------------------------------------------------------

PATH(correlation_path) 함수는 under_path나 equals_path의 보조함수로서,
자원의 관계경로를 반환한다.

【예제】
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1) DEPTH(2)
------------------------------------------ ----------
/xml.xsd 1

SQL>

4-19) SYS_CONNECT_BY_PATH 함수
--------------------------------------------------------------------------------

SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리에서만 유효하며,
column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.

【예제】
SQL> select sys_connect_by_path(name, '/') from emp
2 start with name='jijoe'
3 connect by prior id=1101;

SYS_CONNECT_BY_PATH(NAME,'/')
-----------------------------
/jijoe

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

4-20) SYS_CONTEXT 함수
--------------------------------------------------------------------------------

이 함수는 namespace와 관계되는 parameter의 값을 반환한다.

【형식】
SYS_CONTEXT('namespace','parameter' [,length])

【예제】
SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
JIJOE

SQL> select sys_context('userenv','lang') from dual;

SYS_CONTEXT('USERENV','LANG')
-----------------------------
US

SQL>

userenv에서 사용될 parameter는 다음과 같다.
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL CURRENT_USER
CURRENT_USERID DB_DOMAIN DB_NAME
ENTRY_ID EXTERNAL_NAME FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST INSTANCE
IP_ADDRESS ISDBA LANG
LANGUAGE NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE
NLS_SORT NLS_TERRITORY OS_USER
PROXY_USER PROXY_USERID SESSION_USER
SESSION_USERID SESSIONID TERMINAL

4-21) SYS_DBURIGEN 함수
--------------------------------------------------------------------------------

이 함수는 입력된 argument에 대한 DBURIType의 URL을 반환한다.

【형식】
SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])

【예제】
SQL> select sys_dburigen(id,name) from emp
2 where name='jijoe';

SYS_DBURIGEN(ID,NAME)(URL, SPARE)
------------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

4-22) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------

sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.

【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SQL>

4-23) SYS_GUID 함수
--------------------------------------------------------------------------------

sys_guid() 함수는 globally unique identifier를 반환한다.

【예제】
SQL> select sys_guid() from dual;

SYS_GUID()
--------------------------------
E0F6C6D5767C01ADE034080020B588F4

SQL>

4-24) SYS_XMLAGG 함수
--------------------------------------------------------------------------------

이 함수는 sys_xmlgen 문에서 만든 XML 문을 기본적으로 ROWSET 태그를 새로 추가 시킨다.

【형식】
SYS_XMLAGG( expr [fmt] )

【예제】
SQL> select sys_xmlagg(sys_xmlgen(name)) from emp
2 where name like 'j%';

SYS_XMLAGG(SYS_XMLGEN(NAME))
--------------------------------------------------------------------------
<ROWSET>
<NAME>jijoe</NAME>
</ROWSET>

SQL>

4-25) SYS_XMLGEN 함수
--------------------------------------------------------------------------------

이 함수는 지정한 행이나 열을 XML 문으로 만들어 반환한다.

【형식】
SYS_XMLGEN( expr [fmt] )

【예제】
SQL> select sys_xmlgen(name) from emp
2 where name like 'j%';

SYS_XMLGEN(NAME)
--------------------------------------------------------------------------
<NAME>jijoe</NAME>

SQL>

4-26) UID 함수
--------------------------------------------------------------------------------

UID 함수는 사용자의 유일한 ID를 정수로 반환한다.

【예제】
SQL> select uid from dual;

UID
----------
93

SQL>

4-27) USER 함수
--------------------------------------------------------------------------------

이 함수는 사용자의 이름을 반환한다.

【예제】
SQL> select user,uid from dual;

USER UID
------------------------------ ----------
JIJOE 93

SQL>

4-28) USERENV 함수
--------------------------------------------------------------------------------

USERENV('parameter') 함수는 사용자의 환경에 관한 정보를 반환한다.

parameter는 다음과 같은 것이 있다.
CLIENT_INFO ENTRYID ISDBA LANG
LANGUAGE SESSIONID TERMINAL

【예제】
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.KO16KSC5601

SQL>

4-29) VSIZE 함수
--------------------------------------------------------------------------------

VSIZE('expr') 함수는 expr이 표시되는 바이트 수를 반환한다.

【예제】
SQL> select name, vsize(name) from emp
2 where name like 'jijoe';

NAME VSIZE(NAME)
---------- -----------
jijoe 5

SQL>

$ cat .profile
..........
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601 ☜ 한글 문자셋으로 설정
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8 ☜ UNICODE로 설정
export NLS_LANG
$

테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.

select 한글컬럼명, vsize(한글컬럼명) from 테이블명;

여기서 한글 컬럼에 한글이 3글자라면, vsize 결과가
9이면 unicode이고,
6이면 한글 문자셋으로 저장된 것임을 알 수 있다.

【예제】
SQL> select * from test;

ID NAME
---------- ----------------------------------------
1113 아리랑
1112 쓰리랑

SQL> select name, vsize(name) from test;

NAME VSIZE(NAME)
---------------------------------------- -----------
아리랑 6
쓰리랑 6

SQL>

4-30) XMLAGG 함수
--------------------------------------------------------------------------------

이 함수는 xmlelement에 의해서 XML 태그를 만든 문장을 모으는 기능이다.

【형식】
XMLAGG( XMLType_instance [order_by_clause])

【예제】
SQL> select xmlagg(xmlelement("name",e.name)) from emp e;

XMLAGG(XMLELEMENT("NAME",E.NAME))
--------------------------------------------------------------------------
<name>Cho</name>
<name>Joe</name>
<name>kim</name>
<name>jijoe</name>

SQL>

4-31) XMLCOLATTVAL 함수
--------------------------------------------------------------------------------

이 함수는 XML fragment를 만드는 기능이다

【형식】
XMLCOLATTVAL( value_expr [AS c_alias],...)

【예제】
SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;

XMLCOLATTVAL(E.NAME,E.ID,E.SALARY)
--------------------------------------------------------------------------
<column name="NAME">Cho</column>
<column name="ID">1101</column>
<column name="S

<column name="NAME">Joe</column>
<column name="ID">1102</column>
<column name="S

<column name="NAME">kim</column>
<column name="ID">1103</column>
<column name="S

<column name="NAME">jijoe</column>
<column name="ID">1104</column>
<column name=

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

4-32) XMLCONCAT 함수
--------------------------------------------------------------------------------

XMLCONCAT( XMLType_instance,...) 함수는 XMLType instance를 series로 넣어 만드는 기능이다.

【예제】
SQL> select xmlconcat(
2 xmlelement("name",e.name),xmlelement("bonus",e.bonus))
3 from emp e;

XMLCONCAT(XMLELEMENT("NAME",E.NAME),XMLELEMENT("BONUS",E.BONUS))
--------------------------------------------------------------------------
<name>Cho</name>
<bonus>125</bonus>

<name>jijoe</name>
<bonus>100</bonus>

SQL>

4-33) XMLFOREST 함수
--------------------------------------------------------------------------------

이 함수는 각각의 argument parameter를 XML로 변환한다.

【형식】
XMLFOREST( value_expr [AS c_alias],...)

【예제】
SQL> select xmlelement("emp",
2 xmlforest(e.id, e.name, e.bonus)) from emp e;

XMLELEMENT("EMP",XMLFOREST(E.ID,E.NAME,E.BONUS))
--------------------------------------------------------------------------
<emp>
<ID>1101</ID>
<NAME>Cho</NAME>
<BONUS>125</BONUS>
</emp>

<emp>
<ID>1104</ID>
<NAME>jijoe</NAME>
<BONUS>100</BONUS>

</emp>

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

4-34) XMLELEMENT 함수
--------------------------------------------------------------------------------

이 함수는 XML 태그를 붙이는 기능이다

【예제】
SQL> select xmlelement("name",e.name) from emp e
2 where name like 'j%';

XMLELEMENT("NAME",E.NAME)
--------------------------------------------------------------------------
<name>jijoe</name>

SQL>

5-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
aggregate 함수나
analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
240

SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
236.666667
236.666667
236.666667
250

SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;

AVG
----------
245
236.666667
235
250

SQL>

5-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
1

SQL>

5-3) COUNT* 함수
--------------------------------------------------------------------------------

쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

COUNT(*)
----------
4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
4

SQL> select salary,count(*)
2 over (order by salary)
3 from emp;

SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
220 1
240 2
250 4
250 4

SQL>

5-4) COVAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 population covariance를 반환한다.

【형식】
COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_pop(bonus,salary) from emp;

COVAR_POP(BONUS,SALARY)
-----------------------
62.5

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-5) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
83.3333333

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-6) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
.4

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------

그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;

Dense Rank
----------
2

SQL>

5-8) FIRST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;

Worst Best
---------- ----------
220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-9) GROUP_ID 함수
--------------------------------------------------------------------------------

GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.

【예제】
SQL> select dept_no, group_id() from employees
2 group by dept_no;

DEPT_NO GROUP_ID()
---------- ----------
10 0
20 0

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-10) Grouping 함수
--------------------------------------------------------------------------------

Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여
grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.

. grouping 함수는 인수로 하나의 값만을 가진다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
1은 사용되지 않았음을 의미한다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
2 from aaa
3 group by rollup(grade,deptno);

GRADE DEPTNO SUM(SALARY) GROUPING(DEPTNO)
---------- ---------- ----------- ----------------
1 10 100 0
1 20 500 0
1 30 300 0
1 900 1
2 10 400 0
2 20 200 0
2 30 600 0
2 1200 1
2100 1

9 rows selected.

SQL>

5-11) GROUPING_ID 함수
--------------------------------------------------------------------------------

GROUPING_ID(expr,...) 함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환한다.

【예제】
SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL> select sum(salary), grouping_id(dept_no)
2 from employees
3 group by dept_no;

SUM(SALARY) GROUPING_ID(DEPT_NO)
----------- --------------------
500 0
460 0

SQL>

5-12) LAST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK LAST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;

Worst Best
---------- ----------
220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-13) MAX 함수
--------------------------------------------------------------------------------

이 함수는 최대 값을 반환한다.

【형식】
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select max(salary) over (partition by dept_no)
2 from employees;

MAX(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
250
250
240
240

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
250

SQL>

5-14) MIN 함수
--------------------------------------------------------------------------------

이 함수는 최소 값을 반환한다.

【형식】
MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】
SQL> select min(salary) over (partition by dept_no)
2 from employees;

MIN(SALARY)OVER(PARTITIONBYDEPT_NO)
-----------------------------------
250
250
220
220

SQL> select min(salary) from employees;

MIN(SALARY)
-----------
220

SQL>

5-15) PERCENTILE_CONT 함수
--------------------------------------------------------------------------------

이 함수는 연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_cont(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;

DEPT_NO PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
10 250
20 230

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-16) PERCENTILE_DISC 함수
--------------------------------------------------------------------------------

이 함수는 불연속 모델에 대한 inverse distribution function이다.

【형식】
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]

【예제】
SQL> select dept_no,percentile_disc(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;

DEPT_NO PERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYSALARYDESC)
---------- --------------------------------------------------
10 250
20 240

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------

이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.

【형식】
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select percent_rank(230,0.05) within group
2 (order by salary,bonus) from employees;

PERCENT_RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
------------------------------------------------------
.25

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-18) RANK 함수
--------------------------------------------------------------------------------

이 함수는 그룹 내에서 위치를 반환한다.

【형식】
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)

【예제】
SQL> select rank(230,0.05) within group
2 (order by salary,bonus) from employees;

RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
----------------------------------------------
2
SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------

선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.
사용되는 회귀함수는 자음 중 하나이다.
REGR_SLOPE REGR_INTERCEPT REGR_COUNT
REGR_R2 REGR_AVGX REGR_AVGY
REGR_SXX REGR_SYY REGR_SXY

【형식】
REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|
REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}
(expr1,expr2) [OVER (analytic_clause)]

【예제】
SQL> select regr_slope(salary,bonus) from employees

REGR_SLOPE(SALARY,BONUS)
------------------------
.533333333

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

5-20) STDDEV 함수
--------------------------------------------------------------------------------

이 함수는 standard deviation을 반환한다.

【형식】
STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev(salary) from emp;

STDDEV(SALARY)
--------------
14.1421356

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-21) STDDEV_POP 함수
--------------------------------------------------------------------------------

이 함수는 population standard deviation을 반환한다.

【형식】
STDDEV_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_pop(salary) from emp;

STDDEV_POP(SALARY)
------------------
12.2474487

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-22) STDDEV_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 cumulative sample standard deviation을 반환한다.

【형식】
STDDEV_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select stddev_samp (salary) from emp;

STDDEV_SAMP(SALARY)
-------------------
14.1421356

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-23) SUM 함수
--------------------------------------------------------------------------------

이 함수는 합계를 반환한다.

【형식】
SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
960

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-24) VAR_POP 함수
--------------------------------------------------------------------------------

이 함수는 population variance를 반환한다.

【형식】
VAR_POP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_pop(salary) from emp;

VAR_POP(SALARY)
---------------
150

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-25) VAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 sample variance를 반환한다.

【형식】
VAR_SAMP (expr) [OVER (analytic_clause)]

【예제】
SQL> select var_samp(salary) from emp;

VAR_SAMP(SALARY)
----------------
200

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-26) VARIANCE 함수
--------------------------------------------------------------------------------

이 함수는 variance를 반환한다.

【형식】
VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】
SQL> select variance(salary) from emp;

VARIANCE(SALARY)
----------------
200

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

5-27) Grouping sets 함수
--------------------------------------------------------------------------------

Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,
grouping sets 함수 사용이 불가능한 이전 버전에서
복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
다시 말해서, grouping sets 함수를 사용하면,
group by ... union all을 사용한 것보다
SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호

【예제】
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets(grade,deptno);

GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 900
2 1200
10 500
20 700
30 900

SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets((grade,name),(deptno,name));

GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 100
1 300
1 500
2 200
2 400
2 600
10 100
20 200
30 300
10 400
20 500
30 600

12 rows selected.
SQL>
【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grade,deptno
4 union all
5 select grade,deptno,sum(salary)
6 from aaa
7 group by grade,deptno;

GRADE DEPTNO SUM(SALARY)
---------- ---------- -----------
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600

12 rows selected.

SQL>

composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면
표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며
다음 표를 보고 이해하자.
composite column 문의 경우 group by 문의 경우 group by grouping sets(a,b,c) group by a union allgroup by b union allgroup by c group by grouping sets(a,b,(b,c)) group by a union allgroup by b union allgroup by b,c group by grouping sets((a,b,c)) group by a,b,c group by grouping sets(a,(b),()) group by a union allgroup by b union allgroup by () group by grouping sets(a,rollup(b,c)) group by a union allgroup by rollup(b,c) group by rollup(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by () group by cube(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by ()

6-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
aggregate 함수나
analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
240

SQL>
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
236.666667
236.666667
236.666667
250

SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;

AVG
----------
245
236.666667
235
250

SQL>

6-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
1

SQL>

6-3) COUNT* 함수
--------------------------------------------------------------------------------

쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

COUNT(*)
----------
4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
4

SQL> select salary,count(*)
2 over (order by salary)
3 from emp;

SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
220 1
240 2
250 4
250 4

SQL>

6-4) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
83.3333333

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

6-5) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
.4

SQL> select * from emp;

ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100

SQL>

6-6) DENSE_RANK 함수
--------------------------------------------------------------------------------

그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;

Dense Rank
----------
2

SQL>

6-7) FIRST 함수
--------------------------------------------------------------------------------

Worst Best
---------- ----------
220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-8) FIRST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 첫 번째를 출력한다.

【형식】
FIRST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,first_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);

SALARY FIRST_VALU
---------- ----------
220 jijoe
240 jijoe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-9) LAG 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서,
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LAG(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;

NAME SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- ----------------------------------
jijoe 220 0
Joe 240 220
Cho 250 240
kim 250 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 마지막 번째를 출력한다.

【형식】
LAST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,last_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);

SALARY LAST_VALUE
---------- ----------
220 jijoe
240 Joe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-11) LEAD 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, self join하지 않고
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LEAD(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;

NAME SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- -----------------------------------
jijoe 220 240
Joe 240 250
Cho 250 250
kim 250 0

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-12) NTILE 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.

【형식】
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
2 FROM employees;

NAME SALARY NTILE(3)OVER(ORDERBYSALARYDESC)
---------- ---------- -------------------------------
Cho 250 1
kim 250 1
Joe 240 2
jijoe 220 3

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.

【형식】
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])

【예제】
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
2 FROM employees;

NAME SALARY RATIO_TO_REPORT(SALARY)OVER()
---------- ---------- -----------------------------
Cho 250 .260416667
Joe 240 .25
kim 250 .260416667
jijoe 220 .229166667

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.

【형식】
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
2 FROM employees;

ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME
----------------------------------- ----------
1 Cho
2 kim
3 Joe
4 jijoe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100

SQL>

7-1) REF 타입
--------------------------------------------------------------------------------
테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.
일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.
REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.
REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.
이러한 현상을 REF의 Dangling 현상이고 한다.
이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.
【예제】
SQL> connect jijoe/jijoe_password
connected

SQL> create type person_type as object(
2 first_name varchar2(10),
3 last_name varchar2(10),
4 phone varchar(12),
5 birthday varchar2(12));
6 /

Type created.

SQL> create type emp_type as object (
2 empno number,
3 emp person_type);
4 /

Type created.

SQL> create table emp2 of emp_type
2 oidindex emp_oid;

Table created.

SQL> insert into emp2 values(
2 emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));

1 row created.

SQL> create table dept(
2 empno number(4),
3 ename varchar2(15),
4 mgr REF emp_type SCOPE IS emp2);

Table created.

SQL> insert into dept
2 select empno, 'SCOTT', REF(e)
3 from emp2 e
4 where empno=1000;

1 row created.

【예제】
SQL> select ename,empno from dept;

ENAME EMPNO
--------------- ----------
SCOTT 1000

SQL> select mgr, DEREF(mgr) from dept;

MGR
--------------------------------------------------------------------------------
DEREF(MGR)(EMPNO, EMP(FIRST_NAME, LAST_NAME, PHONE, BIRTHDAY))
--------------------------------------------------------------------------------
0000220208DFA05B27A63701D9E034080020B588F4DFA05B27A63601D9E034080020B588F4
EMP_TYPE(1000, PERSON_TYPE('junik', 'joe', '123-1234', '20-jul-04'))

SQL>

【예제】
SQL> select empno,ename,mgr
2 from dept
3 where mgr is dangling;

no rows selected

SQL> analyze table dept validate REF update set dangling to NULL;

Table analyzed.

SQL>

8-1) ROWID 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWNUM은 테이블에서 select 되어진 일련 번호임
【예제】
SQL> select rownum,ename from emp;

ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 JONES
4 ALLEN
5 MARTIN
6 CHAN

6 rows selected.

SQL> delete from emp where ename='JONES';

1 row deleted.

SQL> select rownum,ename from emp;

ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 ALLEN
4 MARTIN
5 CHAN

SQL>

저작자표시

'Database' 카테고리의 다른 글

[Database]Ms SQL 명령어 (0)	2012.05.23
[Database]MySql 명령어 정리 (0)	2012.05.21

[Database]Ms SQL 명령어 Database 2012. 5. 23. 09:01

출처 :http://systemmania.tistory.com/52

출처 : http://ww.okjsp.pe.kr/seq/151426

출처 : http://blog.naver.com/overa4/110100006585

출처 : http://blog.naver.com/ballkiss/30025300601

출처 : http://blog.daum.net/gksdy2000/8006521

문제가 된다면 삭제하겠습니다.

MS-SQL

** SQL문은 대소문자를 구분하지 않지만 데이타는 대문자와 소문자를 구분한다
주석을 다는 방법은 /* 주석 */ 이거나 한줄만 주석 처리를 할 경우는 문장 맨앞에 --를 붙인다
** 각각의 데이타베이스의 SYSOBJECTS 테이블에 해당 데이타베이스의 모든 정보가 보관되어 있다
SYSOBJECTS의 TYPE 칼럼으로 'U'=사용자 테이블, 'P'=저장 프로시저, 'K'=프라이머리 키, 'F'=포린 키,
'V'=뷰, 'C'=체크 제약등 오브젝트 이름과 정보를 알 수 있다

데이타 검색
USE 데이타베이스명 /* USE 문을 사용한 데이타베이스 선택 */
SELECT * FROM 데이블명 /* 모든 칼럼 불러오기 */
SELECT TOP n * FROM 테이블명 /* 상위 n개의 데이타만 가져오기 */
SELECT 칼럼1, 칼럼2, 칼럼3 FROM 테이블명 /* 특정 칼럼 가져오기 */
SELECT 칼럼1 별명1, 칼럼2 AS 별명2 FROM 테이블명 /* 칼럼에 별명 붙이기 */
SELECT 칼럼3 '별 명3' FROM 테이블명 /* 칼럼 별명에 스페이스가 들어갈 경우는 작은따옴표 사용 */
SELECT DISTINCT 칼럼 FROM 테이블명 /* 중복되지 않는 데이타만 가져오기 */
** 데이타는 오름차순으로 재배열된다
DISTINCT를 사용하면 재배열이 될때까지 데이타가 리턴되지 않으므로 수행 속도에 영향을 미친다 */
SELECT * FROM 테이블명 WHERE 조건절 /* 조건에 해당하는 데이타 가져오기 */
** 조건식에 사용하는 비교는 칼럼=값, 칼럼!=값, 칼럼>값, 칼럼>=값, 칼럼<값, 칼럼<=값이 있다
문자열은 ''(작은따옴표)를 사용한다
날짜 비교를 할때는 'yy-mm-dd' 형식의 문자열로 한다(날짜='1992-02-02', 날짜>'1992-02-02')
SELECT * FROM 테이블명 WHERE 칼럼 BETWEEN x AND y /* 칼럼이 x>=와 y<=사이의 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 IN (a, b...) /* 칼럼이 a이거나 b인 데이타 가져오기 */

SELECT * FROM 테이블명 WHERE 칼럼 LIKE '패턴' /* 칼럼이 패턴과 같은 데이타 가져오기 */
** 패턴에 사용되는 기호는 %, _가 있다
'k%'(k로 시작되는), '%k%'(중간에 k가 있는), '%k'(k로 끝나는)
'p_'(p로 시작하는 2자리), 'p___'(p로 시작하는 4자리), '__p'(3자리 데이타중 p로 끝나는)

Like 패턴 주의점

- MSSQL LIKE 쿼리에서 와일드 카드(예약어) 문자가 들어간 결과 검색시

언더바(_)가 들어간 결과를 보기 위해 아래처럼 쿼리를 날리니

select * from 테이블명 where 컬럼명 like '%_%'

모든 데이터가 결과로 튀어나왔다. -_-;;

언더바가 와일드 카드(쿼리 예약어)이기 때문인데 이럴 땐

select * from 테이블명 where 컬럼명 like '%[_]%'

SELECT * FROM 테이블명 WHERE 칼럼 IS NULL /* 칼럼이 NULL인 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT BETWEEN x AND y /* 칼럼이 x와 y 사이가 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT IN (a, b...) /* 칼럼이 a나 b가 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 NOT LIKE '패턴' /* 칼럼이 패턴과 같지 않은 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼 IS NOT NULL /* 칼럼이 NULL이 아닌 데이타 가져오기 */
SELECT * FROM 테이블명 WHERE 칼럼>=x AND 칼럼<=y
SELECT * FROM 테이블명 WHERE 칼럼=a or 칼럼=b
SELECT * FROM 데이블명 WHERE 칼럼1>=x AND (칼럼2=a OR 칼럼2=b)
** 복수 조건을 연결하는 연산자는 AND와 OR가 있다
AND와 OR의 우선순위는 AND가 OR보다 높은데 우선 순위를 바꾸고 싶다면 ()을 사용한다
SELECT * FROM 테이블명 ORDER BY 칼럼 /* 칼럼을 오름차순으로 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 칼럼 ASC
SELECT * FROM 테이블명 ORDER BY 칼럼 DESC /* 칼럼을 내림차순으로 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 칼럼1 ASC, 칼럼2 DESC /* 복수 칼럼 재배열하기 */
SELECT * FROM 테이블명 ORDER BY 1 ASC, DESC 3 /* 칼럼 순서로 재배열하기 */
** 기본적으로 SELECT 문에서는 출력순서가 보증되지 않기 때문에
데이타의 등록 상태나 서버의 부하 상태에 따라 출력되는 순서가 달라질 수 있다
따라서 출력하는 경우 되도록이면 ORDER BY를 지정한다
** 칼럼 번호는 전체 칼럼에서의 번호가 아니라 SELECT문에서 선택한 칼럼의 번호이고 1부터 시작한다

연산자
** 1순위는 수치 앞에 기술되는 + - 같은 단항 연산자
2순위는 사칙 연산의 산술 연산자인 * / + -
3순위는 = > 비교 연산자
4순위는 AND OR 같은 논리 연산자
()을 붙이면 우선 순위를 바꿀수 있다

1. SELECT 문의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명
2. ORDER BY 구의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 칼럼3+칼럼4 DESC
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 ORDER BY 3 DESC
3. WHERE 구의 연산
SELECT 칼럼1, 칼럼2, 칼럼3+칼럼4 AS '별명' FROM 테이블명 WHERE 칼럼2>=(칼럼3+칼럼4)
4. NULL 연산
SELECT 칼럼1, 칼럼2, ISNULL(칼럼3, 0) + ISNULL(칼럼4, 0) AS '별명' FROM 테이블명
** 수치형 데이타와 NULL값과의 연산 결과는 항상 NULL이다
만약 NULL 값을 원치 않으면 ISNULL(칼럼, 기준값) 함수를 사용해서 기준값을 변환시킨다
5. 날짜 연산
SELECT GETDATE() /* 서버의 현재 날짜를 구한다 */
SELECT 날짜칼럼, 날짜칼럼-7 FROM 테이블명
SELECT 날짜칼럼, 날짜칼럼+30 FROM 테이블명
SELECT 날짜칼럼, DATEDIFF(day, 날짜칼럼, GETDATE()) FROM 테이블명
** 날짜의 가산과 감산은 + -로 할 수 있다
날짜와 날짜 사이의 계산은 DATEDIFF(돌려주는값, 시작날짜, 끝날짜) 함수를 사용한다
6. 문자 연산
SELECT 칼럼1 + 칼럼2 FROM 테이블명
SELECT 칼럼 + '문자열' FROM 테이블명
SELECT 칼럼1 + '문자열' + 칼럼2 FROM 테이블명
** 기본 연결은 문자와 문자이고 문자와 숫자의 연결은 CONVERT 함수를 사용해야 한다

함수
1. 수치 함수
ROUND(수치값, 반올림위치) /* 반올림 및 자르기 */
ABS(수치 데이타) /* 절대값 */
SIGN(수치 데이타) /* 부호 */
SQRT(수치값) /* 제곱근 */
POWER(수치값, n) /* n승 */
2. 문자열 함수 정리

1) Ascii() - 문자열의 제일 왼쪽 문자의 아스키 코드 값을 반환(Integer)

예) SELECT Ascii('abcd')

>> 결과는 a의 아스키 코드값인 97 반환

2) Char() - 정수 아스키 코드를 문자로 반환(Char)

예) SELECT Char(97)

>> 결과는 a 반환

3) Charindex() - 문자열에서 지정한 식의 위치를 반환

예) SELECT Charindex('b','abcde') >> 결과 : 2
SELECT Charindex('b','abcde',2) >> 결과 : 2
SELECT Charindex('b','abcde',3) >> 결과 : 0

-- 인수값이 3개일때 마지막은 abcde 에서의 문자열 검색 시작위치를 말하며

2인경우는 bcde 라는 문자열에 대해서 검색

3인 경우는 cde 라는 문자열에 대해서 검색 하게 된다.

4) Difference() - 두 문자식에 SUONDEX 값 간의 차이를 정수로 반환

예) SELECT Difference('a','b')

5) Left() - 문자열에서 왼쪽에서부터 지정한 수만큼의 문자를 반환

예) SELECT Left('abced',3) 결과 >> 3

6) Len() - 문자열의 길이 반환

예) SELECT Len('abced') 결과>>5

7) Lower() - 대문자를 소문자로 반환

예) SELECT Lower('ABCDE') 결과 >> abcde

8) Ltrim() - 문자열의 왼쪽 공백 제거

예) SELECT Ltrim(' AB CDE') 결과>> AB CDE

9)Nchar() - 지정한 정수 코드의 유니코드 문자 반환

예) SELECT Nchar(20) 결과 >> 

10) Replace - 문자열에서 바꾸고 싶은 문자 다른 문자로 변환

예) SELECT Replace('abcde','a','1') 결과>>1bcde

11) Replicate() - 문자식을 지정한 횟수만큼 반복

예) SELECT Replicate('abc',3) 결과>> abcabcabc

12) Reverse() - 문자열을 역순으로 출력

예) SELECT Reverse('abcde') 결과>> edcba

13) Right() - 문자열의 오른쪽에서 부터 지정한 수 만큼 반환(Left() 와 비슷 )

예) SELECT Right('abcde',3) 결과>> cde

14)Rtrim() - 문자열의 오른쪽 공백 제거

예) SELECT Rtrim(' ab cde ') 결과>> ' ab cde' <-- 공백구분을위해 ' 표시

15) Space() - 지정한 수만큼의 공백 문자 반환

예) SELECT Space(10) 결과 >> ' ' -- 그냥 공백이 나옴

확인을 위해서 SELECT 'S'+Space(10)+'E' 결과 >> S E

16) Substring() - 문자,이진,텍스트 또는 이미지 식의 일부를 반환

예) SELECT Substring('abcde',2,3) 결과>> bcd

17)Unicode() - 식에 있는 첫번째 문자의 유니코드 정수 값을 반환

예)SELECT Unicode('abcde') 결과 >> 97

18)Upper() - 소문자를 대문자로 반환

예) SELECT Upper('abcde') 결과>> ABCDE

※ 기타 함수 Tip

19) Isnumeric - 해당 문자열이 숫자형이면 1 아니면 0을 반환

>> 숫자 : 1 , 숫자X :0

예) SELECT Isnumeric('30') 결과 >> 1

SELECT Isnumeric('3z') 결과 >> 0

20) Isdate() - 해당 문자열이 Datetime이면 1 아니면 0
>> 날짜 : 1 , 날짜 X :0
예) SELECT Isdate('20071231') 결과 >> 1

SELECT Isdate(getdate()) 결과 >> 1
SELECT Isdate('2007123') 결과 >> 0

SELECT Isdate('aa') 결과 >> 0

※ 날짜및 시간함수 정리

getdate() >> 오늘 날짜를 반환(datetime)

1> DateAdd() - 지정한 날짜에 일정 간격을 + 새 일정을 반환

예) SELECT Dateadd(s,2000,getdate())

2> Datediff() - 지정한 두 날짜의 간의 겹치는 날짜 및 시간 범위 반환

예)SELECT DateDiff(d,getdate(),(getdate()+31))

3> Datename() -지정한 날짜에 특정 날짜부분을 나타내는 문자열을 반환

예) SELECT Datename(d,getdate())

4> Datepart() -지정한 날짜에 특정 날짜부분을 나타내는 정수를 반환

예) SELECT Datepart(d,getdate())

** 돌려주는값(약어)
Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,
Hour-hh, Minute-mi, Second-ss, Milisecond-ms
SELECT DATEADD(dd, 7, 날짜칼럼)

>> Datename , Datepart 은 결과 값은 같으나 반환 값의 타입이 틀림.

5> Day() -지정한 날짜에 일 부분을 나타내는 정수를 반환

예) SELECT Day(getdate()) -- 일 반환

SELECT Month(getdate()) -- 월 반환

SELECT Year(getdate()) -- 년 반환 4. 형변환 함수
CONVERT(데이타 타입, 칼럼) /* 칼럼을 원하는 데이타 타입으로 변환 */
CONVERT(데이타 타입, 칼럼, 날짜형 스타일) /* 원하는 날짜 스타일로 변환 */
CAST(칼럼 AS 데이타 타입) /* 칼럼을 원하는 데이타 타입으로 변환 */
** 스타일
1->mm/dd/yy, 2->yy.mm.dd, 3->dd/mm/yy, 4->dd.mm.yy, 5->dd-mm-yy,
8->hh:mm:ss, 10->mm-dd-yy, 11->yy/mm/dd, 12->yymmdd
SELECT CONVERT(varchar(10), 날짜칼럼, 2)

그룹화 함수
SELECT COUNT(*) FROM 테이블명 /* 전체 데이타의 갯수 가져오기 */
SEELECT COUNT(칼럼) FROM 테이블명 /* NULL은 제외한 칼럼의 데이타 갯수 가져오기 */
SELECT SUM(칼럼) FROM 테이블명 /* 칼럼의 합계 구하기 */
SELECT MAX(칼럼) FROM 테이블명 /* 칼럼의 최대값 구하기 */
SELECT MIN(칼럼) FROM 테이블명 /* 칼럼의 최소값 구하기 */
SELECT AVG(칼럼) FROM 테이블명 /* 칼럼의 평균값 구하기 */
GROUP BY문
SELECT 칼럼 FROM 테이블명 GROUP BY 칼럼
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1
SELECT 칼럼1, COUNT(*) FROM 테이블명 GROUP BY 칼럼1
SELECT 칼럼1, 칼럼2, MAX(칼럼3) FROM 테이블명 GROUP BY 칼럼1, 칼럼2
** GROUP BY를 지정한 경우 SELECT 다음에는 반드시 GROUP BY에서 지정한 칼럼 또는
그룹 함수만이 올 수 있다

조건
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 GROUP BY 칼럼1 HAVING SUM(칼럼2) < a
SELECT 칼럼1, SUM(칼럼2) FROM 테이블명 ORDER BY 칼럼1 COMPUTE SUM(칼럼2)
** HAVING: 그룹 함수를 사용할 경우의 조건을 지정한다
HAVING의 위치: GROUP BY의 뒤 ORDER BY의 앞에 지정한다
COMPUTE: 각 그룹의 소계를 요약해서 보여준다
ORDER BY가 항상 선행해서 나와야 한다
조건절의 서브 쿼리
** SELECT 또는 INSERTY, UPDATE, DELETE 같은 문의 조건절에서 SELECT문을 또 사용하는 것이다
SELECT문 안에 또 다른 SELECT문이 포함되어 있다고 중첩 SELECT문(NESTED SELECT)이라고 한다
** 데이타베이스에는 여러명이 엑세스하고 있기 때문에 쿼리를 여러개 나누어서 사용하면 데이타의 값이
달라질수 있기때문에 트랜잭션 처리를 하지 않는다면 복수의 쿼리를 하나의 쿼리로 만들어 사용해야 한다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼2 = (SELECT 칼럼2 FROM 테이블명 WHERE 조건)
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼1 FROM 테이블명 WHERE 조건)
** 서브 쿼리에서는 다른 테이블을 포함할 수 있다
두개의 테이블에서 읽어오는 서브쿼리의 경우 서브 쿼리쪽에 데이타가 적은 테이블을 주 쿼리쪽에 데이타가
많은 테이블을 지정해야 처리 속도가 빨라진다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 칼럼1 IN (SELECT 칼럼2-1 FROM 테이블명2 WHERE 조건)
** FROM구에서 서브 쿼리를 사용할 수 있다
사용시 반드시 별칭을 붙여야 하고 처리 속도가 빨라진다
SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1 AND 조건2
SEELCT 칼럼1, 칼럼2 FROM (SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건1) 별칭 WHERE 조건2

데이타 편집

추가
** NULL 값을 허용하지도 않고 디폴트 값도 지정되어 있지 않은 칼럼에 값을 지정하지 않은채
INSERT를 수행하면 에러가 발생한다
** 수치값은 그대로 문자값은 ''(작은따옴표)로 마무리 한다
** SELECT INTO는 칼럼과 데이타는 복사하지만 칼럼에 설정된 프라이머리, 포린 키등등의 제약 조건은
복사되지 않기 때문에 복사가 끝난후 새로 설정해 주어야 한다

INSERT INTO 테이블명 VALUES (값1, 값2, ...) /* 모든 필드에 데이타를 넣을 때 */
INSERT INTO 테이블명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...) /* 특정 칼럼에만 데이타를 넣을 때 */
INSERT INTO 테이블명 SELECT * FROM 테이블명2 /* 이미 존재하는 테이블에 데이타 추가 */
INSERT INTO 테이블명(칼럼1, 칼럼2, ...) SELECT 칼럼1, 칼럼2, ...) FROM 테이블명2
SELECT * INTO 테이블명 FROM 테이블명2 /* 새로 만든 테이블에 데이타 추가 */
SELECT 칼럼1, 칼럼2, ... 테이블명 FROM 테이블명2

갱신
UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 /* 전체 데이타 갱신 */
UPDATE 테이블명 SET 칼럼1=값1, 칼럼2=값2 WHERE 조건 /* 조건에 해당되는 데이타 갱신 */

- UPDATE~SELECT

UPDATE A
SET A.cyberLectures = B.bizAddress
FROM OF_Member A, OF_Member B
WHERE A.no = B.no

삭제
DELETE FROM 테이블명 /* 전체 데이타 삭제 */
DELETE FROM 테이블명 WHERE 조건 /* 조건에 해당되는 데이타 삭제 */

오브젝트
** 데이타베이스는 아래 오브젝트들을 각각의 유저별로 관리를 하는데 Schema(스키마)는 각 유저별 소유 리스트이다

1. Table(테이블)
** CREATE일때 프라이머리 키를 설정하지 않는다면 (칼럼 int IDENTITY(1, 1) NOT NULL) 자동 칼럼을 만든다
데이타들의 입력 순서와 중복된 데이타를 구별하기 위해서 반드시 필요하다
** 테이블 정보 SP_HELP 테이블명, 제약 조건은 SP_HELPCONSTRAINT 테이블명 을 사용한다

CREATE TABLE 데이타베이스이름.소유자이름.테이블이름 (칼럼 데이타형 제약, ...) /* 테이블 만들기 */
DROP TABLE 테이블명 /* 테이블 삭제 */
ALTER TABLE 테이블명 ADD 칼럼 데이타형 제약, ... /* 칼럼 추가 */
ALTER TABLE 테이블명 DROP COLUMN 칼럼 /* 칼럼 삭제 */
** DROP COLUMN으로 다음 칼럼은 삭제를 할 수 없다
- 복제된 칼럼
- 인덱스로 사용하는 칼럼
- PRIMARY KEY, FOREGIN KEY, UNIQUE, CHECK등의 제약 조건이 지정된 칼럼
- DEFAULT 키워드로 정의된 기본값과 연결되거나 기본 개체에 바인딩된 칼럼
- 규칙에 바인딩된 칼럼
CREATE TABLE 테이블명 (칼럼 데이타형 DEFAULT 디폴트값, ...) /* 디폴트 지정 */
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 UNIQUE, ...) /* 유니크 설정 */
** UNIQUE란 지정한 칼럼에 같은 값이 들어가는것을 금지하는 제약으로 기본 키와 비슷하지만
NULL 값을 하용하는것이 다르다
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 NOT NULL, ...) /* NOT NULL 설정 */
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 PRIMARY KEY, ...) /* 기본 키 설정 */
** 기본 키는 유니크와 NOT NULL이 조합된 제약으로 색인이 자동적으로 지정되고 데이타를
유일하게 만들어 준다
** 기본 키는 한 테이블에 한개의 칼럼만 가능하다
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 FOREIGN KEY REFERENCES 부모테이블이름(부모칼럼), ...)
CREATE TABLE 테이블명 (칼럼 데이타형 CONSTRAINT 이름 CHECK(조건), ...) /* CHECK 설정 */
** CHECK는 조건을 임의로 정의할 수 있는 제약으로 설정되면 조건을 충족시키는 데이타만
등록할 수 있고 SELECT의 WHERE구와 같은 조건을 지정한다
** CONSTRAINT와 제약 이름을 쓰지 않으면 데이타베이스가 알아서 이름을 붙이지만
복잡한 이름이 되기 때문에 되도록이면 사용자가 지정하도록 한다
** CONSTRAINT는 칼럼과 데이타형을 모두 정의한 뒤에 맨 마지막에 설정할 수 있다
CREATE TABLE 테이블명 (칼럼1 데이타형,
칼럼2 데이타형, ...
CONSTRAINT 이름 PRIMARY KEY(칼럼1)
CONSTRAINT 이름 CHECK(칼럼2 < a) ...)
ALTER TABLE 테이블명 ADD CONSTRAINT 이름 제약문 /* 제약 추가 */
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명 /* 제약 삭제 */
ALTER TABLE 테이블명 NOCHECK CONSTRAINT 제약명 /* 제약 효력 정지 */
ALTER TABLE 테이블명 CHECK CONSTRAINT 제약명 /* 제약 효력 유효 */
** 제약명은 테이블을 만들때 사용자가 지정한 파일 이름을 말한다

2. View(뷰)
** 자주 사용하는 SELECT문이 있을때 사용한다
테이블에 존재하는 칼럼들중 특정 칼럼을 보이고 싶지 않을때 사용한다
테이블간의 결합등으로 인해 복잡해진 SELECT문을 간단히 다루고 싶을때 사용한다
** 뷰를 만들때 COMPUTE, COMPUTE BY, SELECT INTO, ORDER BY는 사용할 수 없고
#, ##으로 시작되는 임시 테이블도 뷰의 대상으로 사용할 수 없다
** 뷰의 내용을 보고 싶으면 SP_HELPTEXT 뷰명 을 사용한다

CREATE VIEW 뷰명 AS SELECT문 /* 뷰 만들기 */
CREATE VIEW 뷰명 (별칭1, 별칭2, ...) AS SELECT문 /* 칼럼의 별칭 붙이기 */
CREATE VIEW 뷰명 AS (SELECT 칼럼1 AS 별칭1, 칼럼2 AS 별칭2, ...)
ALTER VIEW 뷰명 AS SELECT문 /* 뷰 수정 */
DROP VIEW 뷰명 /* 뷰 삭제 */
CREATE VIEW 뷰명 WITH ENCRYPTION AS SELECT문 /* 뷰 암호 */
** 한번 암호화된 뷰는 소스 코드를 볼 수 없으므로 뷰를 암호화하기전에
뷰의 내용을 스크립트 파일로 저장하여 보관한다
INSERT INTO 뷰명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...)
UPDATE 뷰명 SET 칼럼=값 WHERE 조건
** 원래 테이블에 있는 반드시 값을 입력해야 하는 칼럼이 포함되어 있지 않거나
원래 칼럼을 사용하지 않고 변형된 칼럼을 사용하는 뷰는 데이타를 추가하거나
갱신할 수 없다
** WHERE 조건을 지정한 뷰는 뷰를 만들었을때 WITH CHECK OPTION을 지정하지 않았다면
조건에 맞지 않는 데이타를 추가할 수 있지만 뷰에서는 보이지 않는다
또한 뷰를 통해서 가져온 조건을 만족하는 값도 뷰의 조건에 만족하지 않는 값으로도
갱신할 수 있다
CREATE VIEW 뷰명 AS SELECT문 WITH CHECK OPTION
** 뷰의 조건에 맞지 않는 INSERT나 UPDATE를 막을려면 WITH CHECK OPTION을 설정한다

3. Stored Procedure(저장 프로시저)
** 데이타베이스내에서 SQL 명령을 컴파일할때 캐시를 이용할 수 있으므로 처리가 매우 빠르다
반복적으로 SQL 명령을 실행할 경우 매회 명령마다 네트워크를 경유할 필요가 없다
어플리케이션마다 새로 만들 필요없이 이미 만들어진 프로시저를 반복 사용한다
데이타베이스 로직을 수정시 프로시저는 서버측에 있으므로 어플리케이션을 다시 컴파일할 필요가 없다
** 저장 프로시저의 소스 코드를 보고 싶으면 SP_HELPTEXT 프로시저명 을 사용한다

CREATE PROC 프로시저명 AS SQL문 /* 저장 프로시저 */
CREATE PROC 프로시저명 변수선언 AS SQL문 /* 인수를 가지는 저장 프로시저 */
CREATE PROC 프로시저명 WITH ENCRYPTION AS SQL문 /* 저장 프로시저 보안 설정 */
CREATE PROC 프로시저명 /* RETURN 값을 가지는 저장 프로시저 */
인수1 데이타형, ... 인수2 데이타형 OUTPUT
AS
SQL문
RETURN 리턴값
DROP PROCEDURE 프로시저명1, 프로시저명2, ... /* 저장 프로시저 삭제 */

명령어
BEGIN ... END /* 문장의 블록 지정 */
DECLARE @변수명 데이타형 /* 변수 선언 */
SET @변수명=값 /* 변수에 값 지정 */
PRINT @변수명 /* 한개의 변수 출력 */
SELECT @변수1, @변수2 /* 여러개의 변수 출력 */
IF 조건 /* 조건 수행 */
수행1
ELSE
수행2
WHILE 조건1 /* 반복 수행 */
BEGIN
IF 조건2
BREAK - WHILE 루프를 빠져 나간다
CONTINUE - 수행을 처리하지 않고 조건1로 되돌아간다
수행
END
EXEC 저장프로시저 /* SQL문을 실행 */
EXEC @(변수로 지정된 SQL문)
GO /* BATCH를 구분 지정 */

에제
1. 기본 저장 프로시저
CREATE PROC pUpdateSalary AS UPDATE Employee SET salary=salary*2

2. 인수를 가지는 저장 프로시저
CREATE PROC pUpdateSalary
@mul float=2, @mul2 int
AS
UPDATE Employee SET salary=salary* @Mul* @mul2
EXEC pUpdateSalary 0.5, 2 /* 모든 변수에 값을 대입 */
EXEC pUpdateSalary @mul2=2 /* 원하는 변수에만 값을 대입 */

3. 리턴값을 가지는 저장 프로시저
CREATE PROC pToday
@Today varchar(4) OUTPUT
AS
SELECT @Today=CONVERT(varchar(2), DATEPART(dd, GETDATE()))
RETURN @Today
DECLARE @answer varchar(4)
EXEC pToday @answer OUTPUT
SELECT @answer AS 오늘날짜

4. 변수 선언과 대입, 출력
** @는 사용자 변수이고 @@는 시스템에서 사용하는 변수이다

DECLARE @EmpNum int, @t_name VARCHAR(20)
SET @EmpNum=10

SET @t_name = '강우정'
SELECT @EmpNum

이런식으로 다중입력도 가능함.

SELECT @no = no, @name = name, @level = level
FROM OF_Member
WHERE userId ='"

4. Trigger(트리거)
** 한 테이블의 데이타가 편집(INSERT/UPDATE/DELETE)된 경우에 자동으로 다른 테이블의
데이타를 삽입, 수정, 삭제한다
** 트리거 내용을 보고 싶으면 SP_HELPTRIGGER 트리거명 을 사용한다

CREATE TRIGGER 트리거명 ON 테이블명 FOR INSERT AS SQL문 /* INSERT 작업이 수행될때 */
CREATE TRIGGER 트리거명 ON 테이블명 AFTER UPDATE AS SQL문 /* UPDATE 작업이 수행되고 난 후 */
CREATE TRIGGER 트리거명 ON 테이블명 INSTEAD OF DELETE AS SQL문
DROP TRIGGER 트리거명

5. Cursor(커서)
** SELECT로 가져온 결과들을 하나씩 읽어들여 처리하고 싶을때 사용한다
** 커서의 사용방법은 OPEN, FETCH, CLOSE, DEALLOCATE등 4단계를 거친다
** FETCH에는 NEXT, PRIOR, FIRST, LAST, ABSOLUTE {n / @nvar}, RELATIVE {n / @nvar}가 있다

SET NOCOUNT ON /* SQL문의 영향을 받은 행수를 나타내는 메시지를 숨긴다 */
DECLARE cStatus SCROLL CURSOR /* 앞뒤로 움직이는 커서 선언 */
FOR
SELECT ID, Year, City FROM aPlane
FOR READ ONLY
OPEN cStatus /* 커서를 연다 */
DECLARE @ID varchar(50), @Year int, @City varchar(50), @Status char(1)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
WHILE @@FETCH_STATUS=0 /* 커서가 가르키는 결과의 끝까지 */
BEGIN
IF @Year <= 5 SET @Status='A'
ELSE IF @Year> 6 AND @Year <= 9 SET @Status='B'
ELSE SET @Status='C'
INSERT INTO aPlane(ID, City, Status) VALUES(@ID, @Year, @Status)
FETCH FROM cStatus INTO @ID, @Year, @City /* 커서에서 데이타를 하나씩 가져온다 */
END
CLOSE cStaus /* 커서를 닫는다 */
DEALLOCATE cStatus /* 커서를 해제한다 */

보안과 사용자 권한
** 보안의 설정 방법은 크게 WINDOWS 보안과 SQL 보안으로 나뉘어 진다
** 사용자에게 역할을 부여하는데는 서버롤과 데이타베이스롤이 있다

1. SA(System Administrator)
** 가장 상위의 권한으로 SQL 서버에 관한 전체 권한을 가지고 모든 오브젝트를 만들거나
수정, 삭제할 수 있다

2. DBO(Database Owner)
** 해당 데이타베이스에 관한 모든 권한을 가지며 SA로 로그인해서 데이타베이스에서 테이블을
만들어도 사용자는 DBO로 매핑된다
** 테이블이름의 구조는 서버이름.데이타베이스이름.DBO.테이블이름이다

3. DBOO(Database Object Owner)
** 테이블, 인덱스, 뷰, 트리거, 함수, 스토어드 프로시저등의 오브젝트를 만드는 권한을 가지며
SA나 DBO가 권한을 부여한다

4. USER(일반 사용자)
** DBO나 DBOO가 해당 오브젝트에 대한 사용 권한을 부여한다

[SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL : http://blog.naver.com/dbwpsl/60041936511

MSSQL 2005 추가 쿼리

-- ANY (OR 기능)

WHERE 나이 >= (SELECT 나이 FROM .......)

-- GROUP BY ALL (WHERE 절과 일치 하지 않는 내용은 NULL 로 표시)

SELECT 주소, AVG(나이) AS 나이 FROM MEMBER

WHERE 성별='남'

GROUP BY ALL 주소

-- 모든 주소는 나오며 성별에 따라 나이 데이터는 NULL

-- WITH ROLLUP

SELECT 생일, 주소, SUM(나이) AS 나이

FROM MEMBER

GROUP BY 생일, 주소 WITH ROLLUP

-- 생일 과 주소를 요약행이 만들어짐

-- WITH CUBE (위의 예제를 기준으로, 주소에 대한 별도 그룹 요약데이터가 하단에 붙어나옴)

-- GROUPING(컬럼명) ROLLUP 또는 CUBE 의 요약행인지 여부 판단(요약행이면 1 아니면 0)

SELECT 생일, 주소, GROUPING(생일) AS 생일요약행여부

-- COMPUTE (GROUP BY 와 상관없이 별도의 테이블로 요약정보 생성)

SELECT 생일, 나이

FROM MEMBER

COMPUTE SUM(나이), AVG(나이)

-- PIVOT (세로 컬럼을 가로 변경)

EX)

학년/ 반 / 학생수

1 1 40

1 2 45

2 1 30

2 2 40

3 1 10

3 2 10

위와 같이 SCHOOL 테이블이 있다면

SELECT 그룹할컬럼명, [열로변환시킬 행]

FROM 테이블

PIVOT(

SUM(검색할열)

FOR 옆으로만들 컬럼명

IN([열로변환시킬 행])

) AS 별칭

--실제 쿼리는

SELECT 학년, [1반], [2반]

FROM SCHOOL

PIVOT(

SUM(학생수)

FOR 반

IN([1반], [2반])

) AS PVT

-- UNPIVOT (가로 컬럼을 세로로)

SELECT 학년, 반, 학생수

FROM SCHOOL

UNPIVOT(

FOR 반

IN( [1반], [2반] )

) AS UNPVT

-- RANK (순위)

SELECT 컬럼명, RANK() OVER( ORDER BY 순위 기준 컬럼명) AS 순위

FROM 테이블

-- PARTITION BY (그룹별로 순위 생성)

SELECT 컬럼명, RANK() OVER( PARTITION BY 그룹기준컬러명 ORDER BY 순위기준컬럼명) AS 순위

FROM 테이블

-- FULL OUTER JOIN (LEFT 조인과 RIGHT 조인을 합한것)

양쪽 어느 하나라도 데이가 있으면 나옴

-- ROW_NUMBER (순차번호 생성)

SELECT ROW_NUMBER() OVER( ORDER BY 기준열) AS 번호, 컬럼명

FROM 테이블

자료형 (데이터타입)

MSSQL 서버에서 사용하는 데이터 타입(자료형)은 두가지가 있다.

1. 시스템에서 제공하는 System data type

내가 생각해도 참 깔끔하게 정리를 잘 해놨다. -_-;;

성능향상을 위해서라면 가능한 작은 자료형을 사용하도록 하자.

불필요하게 int를 쓰는 경우가 흔한데, 사용될 데이터의 범위를 생각해 본 후, 가장 작은 범위의 자료형을 사용하도록 하자.

2. 사용자가 정의 하는 User data type

사용자 정의 자료형이 왜 필요한가?

C언어를 비로한 몇 가지 언어에서 나타나는 사용자 정의 데이터 유형과 같다.

프로젝트에 참가하는 사람들이 동일한 데이터 타입을 사용하고자 원하거나,

한 컬럼에 대한 데이터 유형을 더 쉽게 사용하려고 할 때 적용시킬 수 있다.

사용 방법

sp_addtype [새로운 타입 이름], '[SQL 데이터 타입]'

예

sp_addtype empID, 'CHAR(10)'

sp_addtype empNO, 'CHAR(12)'

* 참고로 자료형을 바꾸는 함수로는 CONVERT() 가 있다.

사용방법

SELECT CONVERT(CHAR(30), title) FROM BOOKS

--> title 라는 컬럼을 CHAR(30) 으로 변환하여 가져오는 것이다.

SELECT CONVERT(VARCHAR(10), 35)

--> 35 라는 숫자를 VARCHAR(10) 으로 변환한다.

흐름 제어문의 종류

흐름 제어문이란 언어의 처리 순서를 변경하거나 변수를 선언하는 등의 문장을 말한다.

○ GOTO 라벨

- GOTO 를 만나면 라벨 부분으로 무조건 건너뛴다. 라벨은 라벨: 으로 정의한다.

예)

DECLARE...

SET...

table_label1:

IF .... GOTO table_label1

--> GOTO table_label1 을 만나면 table_label1: 부분으로 건너 뛴다.

○ RETURN

- RETURN 은 무조건 수행을 중지 하고 원래 호출된 곳으로 돌아간다.

○ IF / ELSE

- 이름만 들어도 알만한 문법이다. 주의 할 점은 조건문 안의 SQL문장이 둘 이상이라면 BEGIN / END 로 묶어 준다.

예)

IF @begin > @end

BEGIN

SELECT * FROM 테이블1 WHERE 조건

RETURN

END

ELSE

SELECT * FROM.........

○ WHILE / BREAK / CONTINUE

- WHILE 다음에 조건으로 반복을 하게 되고,

BREAK 를 만나면 무조건 WHILE 을 벗어나고,

CONTINUE 를 만나면 무조건 WHILE 로 돌아간다.

예)

WHILE 조건

BEGIN

반복하는 동안 실행할 문장들...

IF 조건

BREAK

IF 조건

CONTINUE

END

○ EXEC[UTE]

- EXEC 와 EXECUTE 는 같은 의미이다.

- 두가지 용도로 사용되는데,

- 첫 번째, 스토어드 프로시저를 실행할 때 사용한다.

예)

EXEC stored_procedure

- 두 번재, SQL 문장을 동적으로 변화시키며 수행할 수 있다.

예)

DECLARE @sql VARCHAR(255)

SET @sql = 'SELECT COUNT(*) FROM '

SET @sql = @sql + 'titles '

EXEC(@sql)

--> 실제 수행되는 문장은 SELECT COUNT(*) FROM titles 가 된다.

○ CASE

- 단순 CASE

예)

SELECT

CASE type

WHEN 'a' THEN 'Apple'

WHEN 'b' THEN 'Banana'

ELSE 'No Data'

END AS 과일

, price

FROM titles

- 검색된 CASE

예)

SELECT title_id

, qty AS '수량'

, CASE

WHEN qty >= 50 THEN 'A'

WHEN qty >= 30 THEN 'B'

ELSE 'C'

END AS '등급'

FROM titles

NULLIF : 표현식 1과, 2를 비교

>> 표현식 1과, 2를 비교 두 표현식이 같으면 NULL 을 리턴, 같지 않으면 표현식 1을 리턴
SELECT NULLIF(2,3) -- 2 리턴
SELECT NULLIF(3,3) -- NULL 리턴
사용예 : 양쪽필드에서 수량이 같으면 NULL을 리턴하고 하니면 첫 필드의 값을 리턴할때

COALESCE : 뒤에 오는 표현식중에 처음으로 오는 NULL 이 아닌 값을 리턴

SELECT COALESCE(NULL, 3, 4) -- 3 리턴
SELECT COALESCE(1,NULL,) -- 1 리턴
SELECT COALESCE(NULL,NULL,4) -- 4 리턴

SELECT COALESCE(NULL,NULL, NULL)--문법오류

사용예 : 하나만 값을 가지고 있는 컬럼에서 비교해서 값을 가져올때 매우 좋다

SET : 세성 옵션 (한번설정하면 세션이 끊어 질때까지 유용)

=====================================================================================

SET nocount OFF
: 몇개 행이 처리 되었는지 결과보여주는 것을 설정한다 '

SET rowcount [n]

ex) SET rowcount 4
SELECT title_id FROM titles ORDER BY TITLE

SET rowcount 0

: 보여줄 목록의 행수를 선택한다. 목록의 정렬의 임의로 설정되므로 필요한 순서가 있다면 ORDER BY 를 사용해야 한다.
사용후엔 반드시 SET ROWCOUNT 0 을 이용해서 원위치 시켜놓아야 한다 '

============================== 유니크 키 넣기 ==============================
ALTER TABLE 테이블명 ADD UNIQUE(컬럼1, 컬럼2)
ALTER TABLE 테이블명 DROP CONSTRAINT 유니크명

============================== IDENTITY 관련 ==============================

http://kuaaan.tistory.com/42

http://l2j.co.kr/1460

http://mcdasa.cafe24.com/wordpress/mssql-identity-scope_identity%ec%9d%98-%ec%b0%a8%ec%9d%b4%ec%a0%90/

============================== INSERT SELECT ==============================

http://blog.naver.com/sorkanj2000/50106968790

============================== UPDATE SELECT ==============================

http://applejara.tistory.com/302

============================== JOIN UPDATE ==============================

http://blog.naver.com/ballkiss/30096524074

저작자표시

'Database' 카테고리의 다른 글

[Database]Oracle 함수정리 (0)	2012.05.30
[Database]MySql 명령어 정리 (0)	2012.05.21

[Database]MySql 명령어 정리 Database 2012. 5. 21. 10:07

처음 사용해보는 MySql 명령어 정리입니다.

블러그 깔끔히 정리 하셔서 가져왔습니다. 하단 출처 기재합니다.

1. 새로운 계정 만들고 관리하기(DB 사용권한 주기)

* mysql 접속 : mysql -u계정ID -p비밀번호 데이터베이스명(DB명 생략가능)

* mysql 종료 : quit \q

i) 데이터 베이스명 만들기
create database DB명;

ii) user 테이블에 계정, 비밀번호 등록
insert into user(host, user, password) values('호스트명'. '계정', password('계정비밀번호'));

iii) db테이블에 db사용권한 등록
insert into db valuse('호스트명', 'DB명', '계정', 'Y 12개'...); 모든권한 줄 필요 없으면 N 적절 사용

iv) user, db 테이블 변경 내용 적용
flush privileges;

===> 자세한 내용은 아래의 내용 참고하기

== 중요한 테이블 user, db 테이블 ==

* 계정등록
ex1) desc user; (시스템에 존재하는 계정을 등록하는 테이블임, 계정, 비밀번호 등등 볼때)
-> select host, user, password from user; 로 레크드 보기(뭐있나 ^^)
그리고 계정, 비밀번호 등록해 보기
-> insert into user(host, user, password) values('localhost'. 'php5', password('1234'));
참고 : password('1234') : 비밀번호를 암호화 하여 관리자를 포함한 어느 누구도 비밀번호를 알아내지 못하도록 하기 위함/나중에 select로 확인해 보면 446ㅁ1210000c85644 뭐 이런식으로 암호와 되있을 것임 ^^

* 사용권한 등록(권한부여)
ex2) desc db;
-> insert into db valuse('localhost', 'php5_db', 'php5', 'Y','Y', 10개Y...); 모든권한 부여할려면 Y 12개
php5 계정에서 php5_db 사용할 수 있도록 권한 부여 ==> DB 테이블에 레코드 추가하면 OK~!

* 시스템 적용 (user, db 테이블의 변경된 내용 적용)
flush privileges;
or) mysql 빠져나온 상태에서는 c:\mysql\bin> mysqladmin reload
둘중 한가지로 시스템에 적용해주세요!!

== 참고
update 문을 이용한 비빌번호 변경
ex) 관리자계정(root) 비밀번호 변경해 보기 ^^
i) user mysql; (사용자 계정관리 DB)
ii) update user(테이블) set password = password('1234') where user='root';
iii) select host, user, password form user;
iv) flush privileges;
==> 반드시 db, user 테이블 업데이트 했을시 반드시 mysql 시스템에 적용!
나와서 mysql -uroot -p1234로 접속해 보기 변경 되었는지~

2. 데이터베이스 관련 명령어
i) 데이터베이스 접속
mysql -u계정ID -p비밀번호 데이터베이스명(생략가능)

ex) mysql -uphp5 -p1234 php5_db
만약 DB명을 생략하게 되면 나중에 use 데이터베이스명; 으로^^(ex use php5_db)

ii) 데이터베이스 만들기(생성) - 반드시 root 관리자 계정 접속
create database DB명;
ex) create database php5_db;
* DB목록 확인 : show databases;

iii) 데이터베이스 삭제
drop database DB명;
ex) drop database php5_db;

3.테이블 관련 명령어

* 테이블 생성
create table 테이블명(
필드명 타입,
필드명 타입,
PRIMARY KEY(필드명)
);

ex)
create table friend
num int NOT NULL,
name char(10),
address char(80),
tel char(20),
PRIMARY KEY(num)
);

Tip>
파일로 만들어 명령어 수행하기
i) 위의 명령어를 파일.sql로 만들기(mysql>bin에 저장)
ex) friend.sql

ii) mysql 실행시 < 파일명입력

ex) c:\mysql\bin> mysql -uphp5 -p1234 php5_db < friend.sql
확인)
c:\mysql\bin> mysql -uphp5 -p1234 php5_db
show tables;
desc friend;

* 테이블 목록보기
show tables;

* 테이블 구조보기
desc 테이블명;

* 테이블의 필드 변경하기
alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명];

ex)
alter table friend add age int;
desc friend;

alter table friend add email char(30) after address;

* 필드 삭제
alter table 테이블명 drop 삭제할필드명1, 삭제할필드명2;

ex)alter table friend drop email;

* 필드 수정(필드이름 변경)
alter table 테이블명 change 이전필드명 새로운필드명 타입;

ex)
alter table friend change tel phone int;
desc friend;

* 필드의 타입수정
alter table 테이블명 modify 기존필드명 새로운 타입;

ex) alter table friend modify name int;

* 테이블 이름 변경하기
alter table 이전테이블명 rename 새테이블명;

ex) alter table friend rename student;
show tables;
desc student;

* 테이블 삭제하기
drop table 테이블명;

ex) drop table friend;

3. 테이블 조작명령어
* 데이터 삽입하기(이미만들어진 테이블에 실제데이터 저장)
insert into 테이블명 (필드명1, 필드명2, ... ) values (필드값1, 필드값2, ....);
참고) (필드명1, 필드명2, ... ) 생략가능 ==> 모든 필드에 집어 넣겠다는 뜻

ex) insert into friend (name, name, address, tel) values (1, '배성진', '서울 동작구 노량진동', '234-8998');
insert into friend values (2, '성지연', '대전시 유성구 송강동', '987-333');

==> 삽입된 데이터 보기 : select * from friend;

* 데이터 검색하기(select)
select 필드명1, 필드명2 from 테이블명;
참고) 전체데이터 보기 select * from 테이블명;

* 조건에 맞는 데이터 검색하기(select ~ where)
select 필드명1, 필드명2 from 테이블명 where 조건식;

ex) select id, name, address, tel, sex from mem where sex='W';
select * from men where (age>=20 and age<30) or (age>=40 and age<50) and sex='M'; 20대와 40대 남성 정보

* 특정 문자열을 가진 레코드 검색하기
_ : 임이의 한문자(1byte) __: 한글한글자 2byte

select name, address, tel from men where name like '김%'; 김으로 시작하는 모든 문자열
select naem, id from mem where name like '__용%'; 가운데이름이 용인사람의 이름, 아이디 보기

* 검색된 데이터 정렬하기(order by)
select 필드명1,2 .. form 테이블명 order by 필드명; (오름차순) 1,2,3 abc
내림차순 정렬시 : order by 필드명 desc; (desc만 뒤에 더 붙여주면 OK)
참고) 이름순으로 정렬이 잘 되지 않는 경우
한글이름의 '가나다..'순이 2진 한글코드의 순서와 일치하지 않기 때문
===> mysql 프로그램 설치시 한글이 적용되도록 해당 파라미터를 옵션으로 설정해 주어야 한다.

ex) 서울에 사는 사람들 나이 많은 순대로 정렬
select age, name, address from men where adress like '서울%' order by age desc;

* 데이터 수정(update set where)
update 테이블명 set 필드명 = 필드값(변결할 값) [where 조건식];
ex) men 테이블에서 아이디가 hyun인 레코드의 전화번호를 '888-9999'로 변경
update men set tel='888-9999' where id='hyun';
확인==> select id, name, tel from men where id='hyun';

* 데이터삭제(레코드삭제)
delete from 테이블명 [where 조건식];
-- delete from 테이블명; ===> 주의 : 테이블 전체 레코드 삭제
ex) men 테이블에서 이름이 홍길동인 레코드를 삭제하려면?
delete from men where name='홍길동';
확인==> select * from men where name='홍길동';

4. 데이터베이스의 백업 및 복원

* 데이터베이스 백업(mysqldump > 사용)
c:\mysql\bin> mysqldump -u계정 -p비밀번호 데이터베이스 이름 > 백업파일명
ex) php5계정의 경우 'php5_db'데이터베이스를 'php5_db.sql' 파일에 백업
c:\mysql\bin> mysqldump -uphp5 -p1234 php5_db > php5_db.sql

* 백업파일의 복원(파일실행)(mysql < 사용)
c:\mysql\bin> mysql -u계정 -p비밀번호 db이름 < 백업파일명
ex) mysql -utest -p1234 test_db < php5_db.sql

==== 정리 ====
DB 생성 : create database db명;
DB 목록보기 : show databases;
DB 삭제 : drop database db명;

테이블 생성 : create table 테이블명(필드명1 타입1, 필드명2 타입2,PRIMARY KEY(필드명));
테이블 목록보기 : show tables;
테이블 구조보기 : desc 테이블명;
테이블 필드변경 : alter table을 사용
ex)alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명]; (새로운 필드 삽입, 다른건 윗글참조)
테이블 삭제 : drop table 테이블명;

데이터 조작명령 : 삽입(insert) : insert into 테이블명 (필드명1, 필드명2, ... ) values (필드값1, 필드값2, ....);
삭제(delete from) : delete from 테이블명 [where 조건식];
검색(select) : select 필드명1, 필드명2 from 테이블명 where 조건식;

데이터베이스 백업 : mysqldump -u계정 -p비밀번호 데이터베이스 이름 > 백업파일명
데이터베이스 복원 : mysql -u계정 -p비밀번호 db이름 < 백업파일명
(mysql 명령어를 파일(sql)로 지정해서 일괄적 실행)

[출처] http://blog.naver.com/smsprogram|작성자 1004

저작자표시

'Database' 카테고리의 다른 글

[Database]Oracle 함수정리 (0)	2012.05.30
[Database]Ms SQL 명령어 (0)	2012.05.23

이전 1 다음

JMStyle

검색

검색결과 리스트

Database에 해당되는 글 3건

글

'Database' 카테고리의 다른 글

설정

트랙백

댓글

글

'Database' 카테고리의 다른 글

설정

트랙백

댓글

글

'Database' 카테고리의 다른 글

설정

트랙백

댓글

CATEGORY

RECENT POSTS

RECENT COMMENT

NOTICE

RECENT TRACKBACK

MY LINK

calendar

COUNTER

티스토리툴바

일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30