Introduction TO SQL Altibase Education Center Copyrightc2013 Altibase.corp All rights reserved
1. ALTIBASE CONCEPT 1. ALTIBASE HDB 2. 관계형데이터모델 3. ISQL
1.1 ALTIBASE HDB
ALTIBASE HDB Hybrid DBMS 비즈니스변화 IT 인프라변화 IT 환경분석 새로운비즈니스모델등장 고객중심의환경 정보흐름의가속화 글로벌경쟁시대 Memory 가격의지속적인하락 - 대용량 Memory 탑재서버출현 통신장비속도의지속적인발전 - M Byte G Byte 전송속도 - 트랜잭션의대용량화 Mobile Device 보급률확대 디지털화 유비쿼터스컴퓨팅환경 데이터홍수 요구사항변화 고성능데이터처리 대용량데이터처리
데이터차별화 시간의흐름에따라데이터의속성은변함 데이터속성변화 시스템요구사항 HOT DATA, 실시간처리가요구, 접근빈도 80% 최근 6 개월이전 1 년이전 WARM DATA, 준실시간처리요구, 접근빈도 10% TEPID DATA, 대용량처리, 접근빈도 7% COLD DATA, 대용량이력보관용, 접근빈도 3% 10 년이전 효율적인데이터관리를위한 Data 차별화필요 (Hot Data 와 Cold Data 구분 ) IDC 조사기록 : Worldwide Disk Storage System Forecast 2006-2010
데이터차별화 Hybrid DBMS 를통한효율성증대 일반적 MMDBMS 의적용 ( 혼용구조 ) Hybrid DBMS 의적용 MMDBMS 데이터동기화필요 DBA APPLICATION Resource Hybrid DBMS 통합엔진 DBA APPLICATION Resource DRDBMS 유지보수 유지보수 관리에서운영, 구성적인모든비용과노력들이두개의 DBMS 를전부제어해야만함 메모리와디스크저장장치를하나의엔진에서완벽하게융합되어제공함으로효율성극대화 관리적비용 : DRDBMS + MMDBMS 비용추가 운영적비용 : DRDBMS + MMDBMS 비용추가 구성적비용 : DRDBMS + MMDBMS 비용추가 관리적비용 : Hybrid DBMS 만을관리 [ 비용 ½ 절감 ] 운영적비용 : Hybrid DBMS 만을관리 [ 비용 ½ 절감 ] 구성적비용 : Hybrid DBMS 만을관리 [ 비용 ½ 절감 ]
Hybrid DBMS Hybrid DBMS 테이블스페이스구조 DBMS Engine Memory Tablespace Buffer for Disk TBS Memory Disk Tablespace Storage
1.2 관계형데이터모델
관계형데이터모델 관계형데이터베이스 관계형데이터모델 DB 데이터를이차원테이블에저장하고테이블들간관계를정의하는구조 데이터의독립성, 일관성, 무결성을보장 기본키와이를참조하는외래키로데이터간의관계를표현 ER(Entity Relationship) 모델로표현 SQL(Structured Query Language) 을이용하여 DBMS와통신
관계형데이터모델 관계형데이터모델에서사용되는객체 Primary key Attribute Relation Tuple 고객번호 고객이름 거주도시 가입일 1 홍길동 서울 11-JAN-2009 2 김철수 경기 23-SEP-2007 3 박영희 부산 01-JAN-2010 용어개념특징 Relation Attribute Tuple 테이블 열 (column) 행 (row) 각열 (Column) 은유일한값을가지며행의순서무의미 속성의이름은모두달라야함속성들간의순서는중요하지않음 연관된속성의모임파일구조의레코드와같은의미 Primary Key 유일한식별자테이블의모든행들을구별하기위해사용 Domain 속성값들의집합 여러개의속성에서공유속성의이름과는달라도무관
관계형데이터모델 SQL(Structured Query Language) 데이터베이스에저장되는데이터를조작하고, 관리하며, 검색하기위한언어 ;( 세미콜론 ) 으로종료, 대소문자를구분하지않음 SQL 종류 데이터정의어 (Data Definition Language) 메타정보가변경되는 SQL 문, 객체를생성, 변경, 삭제하는 SQL 문 CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE 데이터조작어 (Data Manipulation Language) 데이터를질의하거나데이터를변경하는 SQL 문 AUTOCOMMIT 이 OFF 인경우명시적인 COMMIT, ROLLBACK 이필요 INSERT, DELETE, UPDATE, SELECT, MOVE, ENQUEUE, DEQUEUE, LOCK 데이터제어어 (Data Control Language) 시스템, 세션제어문 ALTER SYSTEM, ALTER SESSION 트랜잭션제어문 COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
1.3 ISQL
isql isql ALTIBASE에접속하여질의수행및결과를조회할수있는유틸리티 DBA권한으로 ALTIBASE 구동및종료, 백업및복구등수행가능 $ALTIBASE_HOME/bin 에위치 Shell::/home/alti2> isql u sys p manager port 20300 nls_use MS949 s 127.0.0.1 ----------------------------------------------------------------- ALTIBASE Client Query utility. Release Version 6.1.1.0.10 Copyright 2000, ALTIBASE Corporation or its subsidiaries. All Rights Reserved. ----------------------------------------------------------------- ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300 isql>
isql is 스크립트 $ALTIBASE_HOME/bin 에위치 isql의입력시옵션들을생략하고접속할수있도록제공하는스크립트 Shell::/home/alti2> cat $ALTIBASE_HOME/bin/is #!/bin/sh trap "" TSTP ${ALTIBASE_HOME}/bin/isql -s 127.0.0.1 -u sys -p manager $*
isql isql 실행시입력옵션 입력옵션 설명 -s ALTIBASE 서버가위치한 IP 를지정 -u ALTIBASE DB 사용자명을지정 -p DB 사용자의패스워드를지정 -port -nls_use ALTIBASE Listen Port 번호를지정 ALTIBASE DB 생성시입력한문자셋을지정 -o isql 에서실행한결과를저장할파일명을지정 -f isql 에서수행할질의및명령을저장한입력파일명을지정 -h 입력옵션에대한도움말을출력
isql isql 실행후입력옵션 (1) 옵션 설명 desc @ 테이블구성정보를확인 지정된파일명을실행! OS 명령을수행하고자할경우 h 수행된질의의목록을확인 / 직전에수행한질의를재수행 ed autocommit spool [filename] show all show user 직전에수행한질의를편집하고자할경우현재세션의 autocommit 모드를변경할경우 (on/off) spool 명령에입력된파일에현재수행결과를기록 isql의현재설정상태및사용자명을출력현재 isql로서버에접속중인사용자명을출력
isql isql 실행후입력옵션 (2) 옵션 설명 colsize linesize pagesize heading timing vertical foreignkeys 칼럼사이즈의길이를지정 하나의레코드의출력라인의길이를지정 지정된개수만큼레코드출력후칼럼타이틀을출력 출력결과에서칼럼타이틀을보이거나감추도록설정 실행한질의의수행시간을 1/100 초단위로출력 칼럼들을세로로출력한라인에 ( 칼럼명 : Value ) 형태로결과를출력 desc 명령으로테이블조회시참조키정보를출력 (on/off)
isql isql 사용예 : 테이블목록의조회 isql> SELECT * FROM TAB; USER NAME TABLE NAME TYPE --------------------------------------------------------------- SYSTEM_ STO_COLUMNS_ SYSTEM TABLE SYSTEM_ STO_DATUMS_ SYSTEM TABLE SYSTEM_ STO_ELLIPSOIDS_ SYSTEM TABLE SYSTEM_ STO_GEOCCS_ SYSTEM TABLE SYSTEM_ STO_GEOGCS_ SYSTEM TABLE.. isql>
isql isql 사용예 : 테이블구성의조회 isql> DESC t1; [ TABLESPACE : SYS_TBS_MEM_DATA ] [ ATTRIBUTE ] ---------------------------------------------------------------- NAME TYPE IS NULL ---------------------------------------------------------------- A INTEGER FIXED NOT NULL [ INDEX ] ---------------------------------------------------------------- NAME TYPE IS UNIQUE COLUMN ---------------------------------------------------------------- SYS_IDX_ID_102 BTREE UNIQUE A ASC [ PRIMARY KEY ] ---------------------------------------------------------------- A isql>
2. SELECT 1. SELECT 기초 2. 단일행함수 3. 그룹함수 & 윈도우함수 4. JOIN 5. 서브쿼리 6. SET 연산자 7. 계층질의
2.1 SELECT 기초
데이터검색 SELECT 데이터베이스에서데이터를검색하는문장 SELECT 문의기능 SELECTION PROJECTION 테이블에서일부의행만검색 테이블에서일부의열만검색 JOIN 서로다른테이블을연결하여데이터를함께검색
데이터검색 기본 SELECT 구문 예제 SELECT * { [DISTINCT ALL] column expression [alias], } FROM table_name; Employee 테이블의모든사원정보를검색 isql> SELECT * 2 FROM employee; Employee 테이블중사원이름과급여를검색 isql> SELECT ename, salary 2 FROM employee; 중복된데이터제거 isql> SELECT dno 2 FROM employee; isql> SELECT DISTINCT dno 2 FROM employee;
NULL NULL 알수없는값 0 또는공백과다름 테이블생성시 NOT NULL 또는 PRIMARY KEY 로정의되지않은모든타입의칼럼은 NULL 을포함할수있음 예제 Employee 테이블의급여정보를검색 isql> SELECT salary 2 FROM employee; SALARY -------------- 1500000 2000000 1700000 500000 NULL 을의미 1200000
Alias Alias 컬럼과테이블의별명을지정 칼럼의별명은 display 시칼럼의이름을바꿔주고, 테이블의별명은 join/subquery 등에서사용 구문 SELECT column_name [AS] alias, FROM table_name [AS] alias; AS 는생략가능 공백, 특수문자를포함하거나대소문자를구별할때는 " ", ' ' 를사용 예제 ename 대신 Employee Name 으로검색 isql> SELECT ename AS "Employee name" 2 FROM employee; Employee name ------------------------ EJJUNG HJNO - Employee name 으로 display
WHERE SELECTION 테이블에서필요한행만검색 WHERE 절사용 구문 SELECT column_name, FROM table_name WHERE conditions; conditions 에는칼럼이름, 표현식, 상수, 비교연산자등으로구성 예제 KSKIM 사원의급여를검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE ename='kskim'; ENAME SALARY ------------------------------------- KSKIM 1800000 1 row selected.
WHERE 조건절구성항목 칼럼, 문자나숫자상수 연산자 (+, -, *, /, =, >, < 등 ) LIKE, IN, BETWEEN, EXISTS, NOT IS NULL, IS NOT NULL 함수 AND, OR, NOT ANY, ALL 상수의표현 문자열 대소문자를구별하며, '( 작은따옴표 ) 로묶는다. ex) 'KSKIM' 날짜 날짜형식을구별하며, '( 작은따옴표 ) 로묶는다. 기본형식은 'DD-MON-YYYY' ex) '05-MAY-2011' 숫자 '( 작은따옴표 ) 로묶지않고숫자만작성한다. ex) 1234
Operator 산술연산자 NUMBER, DATE 데이터에대해사용 산술연산자 설명 사용가능한데이터타입 + 더하기 NUMBER, DATE - 빼기 NUMBER, DATE * 곱하기 NUMBER / 나누기 NUMBER 산술연산자우선순위 곱셈과나눗셈은덧셈과뺄셈보다우선순위가높음 우선순위가동일한연산자는왼쪽에서오른쪽으로계산 괄호 () 를이용하여우선순위를조정 예제 사원이름, 급여정보와연봉을함께검색 isql> SELECT ename, salary, salary * 12 2 FROM employee;
Operator 산술연산자와 NULL NULL 에연산을수행하면그결과값도 NULL 이리턴 예제 사원이름, 급여정보와연봉을함께검색 isql> SELECT ename, salary, salary * 12 2 FROM employee; ENAME SALARY SALARY*12 ------------------------------------- EJJUNG HJNO 1500000 18000000 HSCHOI 2000000 24000000 KSKIM 1800000 21600000 SJKIM 2500000 30000000 HYCHOI 1700000 20400000 HJMIN 500000 6000000 salary 가 NULL 일경우 salary*12 도 NULL 이리턴
Operator 연결연산자 문자열을연결할때는 기호를사용 칼럼과칼럼을연결하거나문자열과칼럼들을연결 예제 사원이름과급여를함께검색 isql> SELECT ename salary 2 FROM employee; ENAME SALARY ----------------------------------------------------------------------- EJJUNG HJNO 1500000 HSCHOI 2000000 사원이름과급여를함께검색 ( 문자열포함 ) isql> SELECT ename ' 의급여는 ' salary 2 FROM employee; ENAME ' 의급여는 ' SALARY 연결된 1 개의칼럼 -------------------------------------------------- EJJUNG 의급여는문자열을함께연결 HJNO 의급여는 1500000
Operator 비교연산자 (1) 비교연산자 설명 = 같음 > ~ 보다큼 >= ~ 보다크거나같음 < ~ 보다작음 <= ~ 보다작거나같음 <>,!= 같지않음 예제 급여가 1000000 이상인사원을검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary >= 1000000; 4002 부서에서일하지않는사원을검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE dno <> 4002;
Operator 비교연산자 (2) 비교연산자 설명 BETWEEN ~ AND ~ 지정한값을포함하여두값사이인가? IN(list) list 에같은값이있는가? IS NULL NULL 값인가? LIKE 문자패턴비교 BETWEEN AND 지정한값사이에있는지를비교. 지정한값은포함하여판단 예제 급여가 1000000 과 2000000 사이인사원을검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary BETWEEN 1000000 AND 2000000; 1000000 2000000
Operator IN () 안에지정된목록의값이일치하는지를비교 예제 4001 또는 4002 부서에서일하는사원을검색 isql> SELECT ename, dno 2 FROM employee 3 WHERE dno IN (4001, 4002); KSKIM, YHBAE 와 CHLEE 사원을검색 isql> SELECT ename 2 FROM employee 3 WHERE ename IN ('KSKIM', 'YHBAE', 'CHLEE');
Operator IS NULL NULL 값인지를비교 = NULL 로비교하면 No rows selected. 예제 급여가아직확정되지않은사원을검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary = NULL; ENAME SALARY ------------------------------------- No rows selected. isql> SELECT ename, salary 2 FROM employee 3 WHERE salary IS NULL; ENAME SALARY ------------------------------------- EJJUNG JDLEE DIKIM 3 rows selected.
Operator LIKE 문자패턴을비교 문자패턴비교시사용가능한기호 기호 설명 _ 하나의문자를대체 ESCAPE %, _ 를포함한문자열을비교시사용 예제 % 문자가없거나하나이상의문자를대체 이름이 K 로시작하는사원정보를검색 isql> SELECT ename 2 FROM employee 3 WHERE ename LIKE 'K%'; 이름의두번째문자가 A 인사원을검색 isql> SELECT ename 2 FROM employee 3 WHERE ename LIKE '_A%';
Operator 이름에 _ 를포함한사원을검색 isql> SELECT ename 2 FROM employee 3 WHERE ename LIKE '%\_%' ESCAPE '\'; ESCAPE 과함께임의의문자를사용가능
Operator 논리연산자 두개의조건의결과를결합하여하나의조건으로생성 논리연산자 AND OR NOT 설명두조건이모두 TRUE이면 TRUE, 그렇지않으면 FALSE. 두조건중에하나라도 TRUE이면 TRUE, 둘다 FALSE이면 FALSE. 조건이 TRUE이면 FALSE, FALSE이면 TRUE 예제 급여가 1000000 이상이면서 4002 부서에서일하는사원을검색 isql> SELECT ename, dno, salary 2 FROM employee 3 WHERE salary >= 1000000 4 AND dno = 4002; 급여가 1000000 이상이거나 4002 부서에서일하는사원을검색 isql> SELECT ename, dno, salary 2 FROM employee 3 WHERE salary >= 1000000 4 OR dno = 4002;
Operator 급여가 1000000 과 2000000 사이가아닌사원을검색 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary NOT BETWEEN 1000000 AND 2000000; 4001, 4002 부서에서일하지않는사원을검색 isql> SELECT ename, dno 2 FROM employee 3 WHERE dno NOT IN (4001, 4002); 이름에 A 를포함하지않는사원을검색 isql> SELECT ename, dno 2 FROM employee 3 WHERE ename NOT LIKE '%A% ;
ORDER BY 결과를정렬 검색결과를정렬 내림차순, 오름차순으로정렬이가능 구문 ASC( 기본값 ) 는오름차순, DESC 는내림차순으로정렬 예제 SELECT column_name, FROM table_name [WHERE conditions] ORDER BY {column_name alias column_index} [ASC DESC]; 사원정보를사원이름순으로내림차순으로정렬하여검색 isql> SELECT ename 2 FROM employee 3 ORDER BY ename DESC; ENAME ------------------------ YHBAE DIKIM CHLEE
ORDER BY 사원정보를사원이름순으로내림차순으로정렬하여검색 (alias 사용 ) isql> SELECT ename AS employee_name 2 FROM employee 3 ORDER BY employee_name DESC; ENAME ------------------------ YHBAE DIKIM CHLEE 사원정보를사원이름순으로내림차순으로정렬하여검색 (column_index 사용 ) isql> SELECT ename 2 FROM employee 3 ORDER BY 1 DESC; ENAME ------------------------ YHBAE DIKIM CHLEE
ORDER BY 사원정보를부서번호의오름차순순으로정렬하고부서번호가같다면사원 이름의내림차순순으로정렬 isql> SELECT dno, ename, salary 2 FROM employee 3 ORDER BY dno, ename DESC; DNO ENAME SALARY -------------------------------------------------- 1001 JHCHOI 2300000 1001 HSCHOI 2000000 1002 KWKIM 980000 1002 HYCHOI 1700000 1003 YHBAE 4000000 1003 MSKIM 2750000 4002 MYLEE 1890000 4002 KMKIM 1800000 4002 HJMIN 500000 4002 DIKIM HJNO 1500000 20 rows selected.
LIMIT 결과집합을제한 검색결과의일부분만반환 구문 start index 생략시첫번째행부터시작 예제 SELECT column_name, FROM table_name [WHERE conditions] [ORDER BY column_name,..] LIMIT [start_index,] row_count; 사원중다섯번째부터 3 명의사원만검색 isql> SELECT * 2 FROM employee 3 LIMIT 5, 3; 급여를내림차순으로정렬한후상위 5 명만검색 isql> SELECT ename 2 FROM employee 3 ORDER BY salary DESC 4 LIMIT 5;
PIVOT PIVOT PIVOT 이란 ROW 형태로주어진 DATA 를 Column 형태로보여주는쿼리 구문 SELECT column_name, FROM table_name PIVOT (pivot_clause pivot_for_clause pivot_in_clause) [WHERE conditions] Pivot_clause Aggregate 되는컬럼을정의하는부분으로 7 가지 (COUNT, MIN, MAX, SUM, AVG, STDDEV, VARIANCE) Aggregate Function 만올수있다. Pivot_for_clause Pivot 되는컬럼을정의하며컬럼이름만올수있다. Pivot_in_clause Pivot For 구문에사용된컬럼의 Filter 를정의하며 Alias 를지정할수있다.
PIVOT 예제 부서별로 PM, PL, MANAGER가몇명인지를확인하는내용을 PIVOT 구문을사용하여검색 isql> SELECT * FROM 2 (SELECT e.emp_job, d.dname, e.eno 3 FROM employee e, department d 4 WHERE e.dno = d.dno) 5 PIVOT (COUNT(eno) 6 FOR emp_job 7 IN ( PM, PL, MANAGER ) ); DNAME 'PM' 'PL' 'MANAGER' --------------------------------------------------------------------- RESEARCH DEVELOPMENT DEPT 1 0 0 1 RESEARCH DEVELOPMENT DEPT 2 1 0 0 SOLUTION DEVELOPMENT DEPT 1 0 0 QUALITY ASSURANCE DEPT 1 0 0 CUSTOMER SUPPORT DEPT 0 1 0 PRESALES DEPT 0 1 0 MARKETING DEPT 0 0 1 BUSINESS DEPT 0 0 1 8 rows selected.
PIVOT 유의사항 중복 Alias 를허용하지않는다. Pivot_clause에 Aggregate Function 만올수있다. Pivot_for 에는순수 column 만올수있다. Pivot_for 의항목개수와 Pivot_in의 sublist 의개수가같아야한다.
2.2 단일행함수
함수 함수 입력값에대해작업을수행하여결과값을리턴 SELECT, WHERE, ORDER BY, START WITH 절에서사용가능 중첩해서사용가능 인수 1 인수 2 인수 n 함수 함수가작업을수행 결과값 함수의기능 데이터의계산수행 출력결과를조작 날짜및숫자형식을지정 칼럼의데이터유형변경
함수 함수의유형 단일행함수 : 하나의입력값에하나의결과값을반환 다중행함수 : 여러개의입력값에하나의결과값을반환 ( 그룹함수 ) 인수 1 단일행함수 결과값 인수 1 인수 2 인수 n 다중행함수 결과값 단일행함수의유형 문자 : 문자를입력하면문자또는숫자를반환 숫자 : 숫자를입력하면숫자를반환 날짜 : Date 데이터의값을연산, Date 값혹은숫자를반환 변환 : 값의데이터타입을변환 기타
함수 단일행함수구문 function_name (column expression [arg1, arg2, ] ) 예제 사원이름을소문자로변경 isql> SELECT ename, LOWER(ename) FROM employee; ENAME LOWER(ENAME) ----------------------------------- EJJUNG ejjung HJNO hjno HSCHOI hschoi 10000 을 19 로나누었을때나머지를검색 isql> SELECT MOD(10000,19) FROM dual; MOD(10000,19) ---------------- 6 1 row selected. dual 은하나의칼럼만가지고있는 dummy 테이블. 주로일시적인연산에사용
문자함수 문자함수 (1) 함수결과설명 ASCII('A') 65 ASCII 코드값을반환 CHAR_LENGTH ('ALTIBASE V5') CHARACTER_LENGTH('ALTIBASE V5') LENGTH('ALTIBASE V5') 11 문자열의길이를반환 CHR(65) A ASCII 코드값을받아서해당하는문자로반환 CONCAT('ALTIBASE', 'V5') ALTIBASEV5 첫번째문자와두번째문자를연결 INITCAP('altibase V5') INSTR('ALTIBASE V5', 'V5') POSITION('ALTIBASE V5', 'V5') Altibase V5 단어의첫문자를대문자, 나머지문자를소문자로반환 10 문자열을찾아그위치를반환 LOWER('ALTIBASE V5') altibase v5 소문자로변환 LPAD(12000, 8, '*') ***12000 LTRIM('ALTIBASE V5','ALTIBASE ') OCTET_LENGTH('ALTIBASE HDB') LENGTHB('ALTIBASE HDB') V5 첫번째문자가두번째지정한숫자길이가되도록왼쪽에서부터세번째문자를삽입하여반환 첫번째문자열이두번째문자열로시작할경우두번째문자열을삭제한후반환 12 입력한문자열의바이트크기를반환
문자함수 문자함수 (2) 함수결과설명 REPLACE2('ALTIBASE V5', 'ALTIBASE,'A.') A.V5 첫번째문자열에포함된두번째문자열을세번째문자열로변환 REPLICATE('Alti', 3) AltiAltiAlti 문자열을지정한숫자만큼반복하여반환 RPAD(12000, 8, '*') 12000*** RTRIM('ALTIBASE V5', 'V5') ALTIBASE SIZEOF('ALTIBASE V5') 11 SUBSTR('ALTIBASE V5', 1, 4) TRANSLATE('ALTIBASE V4', '4', '5') TRIM('ALTIBASE V5', 'A') ALTI ALTIBASE V5 LTIBASE V5 UPPER('ALTIBASE V5') ALTIBASE V5 대문자로변환 첫번째문자가두번째지정한숫자길이가되도록오른쪽에서부터세번째문자를삽입하여반환 첫번째문자열이두번째문자열로끝날경우두번째문자열을삭제한후반환 문자열혹은열에할당된크기를반환테이블생성시정의된칼럼크기를반환 REVERSE_STR('ALTIBASE') ESABITLA 문자열을거꾸로반환 문자열의일부를반환. 첫번째문자부터 4 개의문자열을반환 첫번째문자열에포함되어있는두번째문자를모두세번째문자로변환 첫번째문자열이두번째문자열로시작하거나끝날경우두번째문자열을삭제한후반환
문자함수 예제 이름이 kskim 인사람의정보를검색 isql> SELECT ename, INITCAP(ename), SUBSTR(ename,2,3), REVERSE_STR(ename), INSTR(ename,'K') 2 FROM employee 3 WHERE LOWER(ename) = 'kskim'; ENAME INITCAP(ENAME) SUBSTR(ENAME,2,3) REVERSE_STR(ENAME) INSTR(ENAME,'K') ----------------------------------------------------------------------------- KSKIM Kskim SKI MIKSK 1 emp_job 에여러가지문자함수를사용 isql> SELECT emp_job, SIZEOF(emp_job), LENGTH(emp_job), RPAD(emp_job,8,'*'), 2 TRANSLATE(emp_job,'E','Z') 3 FROM employee; EMP_JOB SIZEOF(EMP_JOB) LENGTH(EMP_JOB) RPAD(EMP_JOB,8,'*') TRANSLATE(EMP_JOB,'E','Z') --------------------------------------------------------------------------------- CEO 15 3 CEO***** CZO DESIGNER 15 8 DESIGNER DZSIGNZR ENGINEER 15 8 ENGINEER ZNGINZZR...
숫자함수 숫자함수 (1) 함수결과설명 ABS(-1234.56) 1234.56 절대값 ACOS(0.3) 1.266104 ACOS ASIN(0.3) 0.304693 ASIN ATAN(0.3) 0.291457 ATAN ATAN2(0.3, 0.2) 0.982794 ATAN2 CEIL(99.9) 100 올림. 소수점을기준 COS(180 * 3.14159265359/180) -1 COS COSH(0) 1 COSH EXP(2.4) 11.023176 e n (e는 2.71828183 ) FLOOR(-99.9) -100 내림. 소수점을기준 LN(2.4) 0.875469 자연로그
숫자함수 숫자함수 (2) 함수결과설명 LOG(10, 100) 2 log 함수 MOD(10, 3) 1 첫번째인자값을두번째인자값으로나눈나머지 POWER(3, 2) 9 RANDOM(2) 첫번째인자값을두번째인자값만큼반복해서곱한값. 지수 random 값을반환. 입력한숫자가같으면같은값을반환 ROUND(123.9994, 3) 123.999 반올림. 두번째숫자가반올림할소수점자릿수를의미 SIGN(15) 1 부호를리턴. 0 이면 0, 양수면 1, 음수면 -1 을리턴 SIN(30 * 3.14159265359/180) 0.5 SIN SINH(1) 1.175201 SINH SQRT(10) 3.162278 제곱근 TAN(135 * 3.14159265359/180 ) -1 TAN TANH(0.5) 0.462117 TANH TRUNC(15.79, 1) 15.7 버림. 두번째숫자가버림할소수점자릿수를의미
숫자함수 예제 salary 를 12 로나누었을때나머지를질의 isql> SELECT ename, salary, MOD(salary,12) 2 FROM employee; ENAME SALARY MOD(SALARY,12) ----------------------------------------------------- EJJUNG HJNO 1500000 0 HSCHOI 2000000 8 KSKIM 1800000 0 SJKIM 2500000 4 123.673 숫자에대해여러가지숫자함수를사용 isql> SELECT ROUND(123.673,2), TRUNC(123.673,-1), CEIL(123.673), FLOOR(123.673), SQRT(123.673) 2 FROM dual; ROUND(123.673,2) TRUNC(123.673,-1) CEIL(123.673) FLOOR(123.673) SQRT(123.673) ------------------------------------------------------------------------------ 123.67 120 124 123 11.1208362994876
숫자함수 random 함수사용 isql> SELECT RANDOM(100) 2 FROM dual; RANDOM(100) -------------- 677741240 isql> SELECT RANDOM(200) 2 FROM dual; RANDOM(200) -------------- 331330603 isql> SELECT RANDOM(100) 2 FROM dual; RANDOM(100) -------------- 677741240
날짜함수 날짜함수 함수결과설명 SYSDATE 28-APR-2011 시스템의날짜를반환 ADD_MONTHS(SYSDATE,5) 28-SEP-2011 날짜에월을더함 DATEADD(SYSDATE, -30, 'DAY') DATEDIFF(SYSDATE, '01-JAN-11', 'DAY') 29-MAR-2011-117 날짜에세번째포맷을두번째숫자만큼더함. -30 일을더함 두번째날짜에서첫번째날짜를포맷만큼뺀수를반환 01-JAN-11 에서 SYSDATE 만큼며칠이지났는지를뺌 DATENAME(SYSDATE,'DAY') EXTRACT(SYSDATE,'MONTH') DATEPART(SYSDATE,'MONTH') THURSDAY 날짜에서월 / 요일이름을추출. MONTH 는월을, DAY 는요일을나타냄 4 날짜에서주어진포맷을추출 LAST_DAY(SYSDATE) 30-APR-2011 날짜에서해당월의마지막날짜를반환 MONTHS_BETWEEN(SYSDATE, '01-JAN-11') 3.8976769.. 첫번째날짜에서두번째날짜를뺀값을개월수로반환 NEXT_DAY(SYSDATE,'SUNDAY') 01-MAY-2011 날짜이후에돌아오는요일의날짜를반환 ROUND(SYSDATE,'MONTH') 01-MAY-2011 날짜에서두번째포맷에맞춰반올림 TRUNC(SYSDATE,'MONTH') 01-APR-2011 날짜에서두번째포맷에맞춰버림
날짜함수 예제 현재시스템의날짜 (SYSDATE) 와 SYSDATE 에대한여러가지날짜함수를사용 isql> SELECT SYSDATE, DATEADD(SYSDATE, 2, 'WEEK'), DATEDIFF(SYSDATE, '01-APR-11', 'DAY') 2 FROM dual; SYSDATE DATEADD(SYSDATE, 2, 'WEEK') DATEDIFF(SYSDATE, '01-APR-11', 'DAY') ------------------------------------------------------------------------------ 29-APR-2011 13-MAY-2011-28 isql> SELECT EXTRACT(SYSDATE,'QUARTER'), DATENAME(SYSDATE,'DAY'), 2 DATENAME(SYSDATE, 'DY') 3 FROM dual; EXTRACT(SYSDATE,'QUARTER') DATENAME(SYSDATE,'DAY') DATENAME(SYSDATE, 'DY') ------------------------------------------------------------------------------ 2 FRIDAY FRI isql> SELECT ROUND(SYSDATE,'YEAR'), ROUND(SYSDATE), TRUNC(SYSDATE, 'DAY'), 2 TRUNC(SYSDATE) 3 FROM dual; ROUND(SYSDATE,'YEAR') ROUND(SYSDATE) TRUNC(SYSDATE, 'DAY') TRUNC(SYSDATE) ------------------------------------------------------------------------------ 01-JAN-2011 30-APR-2011 29-APR-2011 29-APR-2011
날짜함수 예제 입사일 (join_date), 입사일 6 개월이후의날짜, 입사한지몇개월이지났는지 검색 isql> SELECT ename, join_date, ADD_MONTHS(join_date, 6), 2 MONTHS_BETWEEN(SYSDATE, join_date) 3 FROM employee; ENAME JOIN_DATE ADD_MONTHS(JOIN_DATE,6) MONTHS_BETWEEN(SYSDATE, JOIN_DATE) ------------------------------------------------------------------------------ HJNO 18-NOV-1999 18-MAY-2000 137.349825288486 HSCHOI 11-JAN-2000 11-JUL-2000 135.575631740099 입사일이후돌아오는월요일, 입사일해당월의마지막날짜를검색 isql> SELECT ename, join_date, NEXT_DAY(join_date, 'MONDAY'), LAST_DAY(join_date) 2 FROM employee; ENAME JOIN_DATE NEXT_DAY(JOIN_DATE, 'MONDAY') LAST_DAY(JOIN_DATE) ----------------------------------------------------------------------- HJNO 18-NOV-1999 22-NOV-1999 30-NOV-1999 HSCHOI 11-JAN-2000 17-JAN-2000 31-JAN-2000
변환함수 변환함수 함수 결과 설명 TO_BIN(10) 1010 숫자를 2진수형태로변환 TO_CHAR(SYSDATE,'HH:MI:SS') 09:41:01 날짜나숫자를지정한포맷을적용하여문자로변환 TO_DATE('110430','YYMMDD') 30-APR-2011 문자를날짜로변환 TO_HEX(100) 64 숫자를 16 진수형태로변환 TO_NUMBER('$1000','$9999') 1000 문자를지정한포맷을적용하여숫자로변환 TO_OCT(100) 144 숫자를 8 진수형태로변환 ASCIISTR(' 알티 ') \C54C\D2F0 문자열을 ASCII 문자열로반환. ASCII 가아닌문자는 '\xxxx' 와같이 UTF-16 코드로표현 CONVERT('ALTI','MS949','UTF8') ALTI 두번째캐릭터셋의문자를세번째캐릭터셋으로변환 UNISTR('\C54C\D2F0') 알티문자를지정한캐릭터셋으로변환
변환함수 예제 '2011-05-01' 은입사일후며칠지났는지검색 isql> SELECT ename, join_date, TO_DATE('2011-05-01','YYYY-MM-DD') - join_date 2 FROM employee; ENAME JOIN_DATE TO_DATE('2011-05-01','YYYY-MM-DD') - JOIN_DATE ------------------------------------------------------------------------------ HJNO 18-NOV-1999 4182 HSCHOI 11-JAN-2000 4128 ( 날짜 - 날짜 ) 연산은첫번째날짜에서두번째날짜를뺀 day 값을반환 숫자 100 에대해여러가지진법값을반환 isql> SELECT 100, TO_BIN(100), TO_OCT(100), TO_HEX(100) 2 FROM dual; 100 TO_BIN(100) TO_OCT(100) TO_HEX(100) ----------------------------------------------------- 100 1100100 144 64
기타함수 기타함수 함수결과설명 CASE2(a, b, c) DECODE(a, b, c, d) DUMP('A') a 값이참이면 b 를반환. 거짓이면 c 를반환 a 값이 b 이면 c 를반환. b 가아니면 d 를반환.b 를생략하면 NULL 을반환 Type=CHAR(KSC5601) Length=3: 1,0,65 자료형, 길이, 메모리내용출력 GREATEST(10,20,30) 30 인자값중가장큰값을출력 LEAST(10,20,30) 10 인자값중가장작은값을출력 NVL(salary,0) NVL2(salary,salary,0) salary 의값이 NULL 이면 0 로대체 salary 의값이 NULL 이아니면 salary 를출력. NULL 이면 0 를출력 DIGEST('alti', 'SHA-1') A6769CDF8D5EE5D33937A39F1 SHA-1 알고리즘으로변환한문자출력 ROWNUM 테이블이나조인된뷰의레코드번호를정수형태로반환
기타함수 예제 급여가 2000000 보다크면 1 그렇지않으면 2 를반환 isql> SELECT ename, salary, CASE2(salary>2000000, 1, 2) 2 FROM employee; ENAME SALARY CASE2(SALARY>2000000, 1, 2) ------------------------------------------------------------------ KSKIM 1800000 2 SJKIM 2500000 1 HYCHOI 1700000 2 dno 가 4001 이면 salary*1.1, 4002 이면 salary*1.2 그렇지않으면 salary 를반환 isql> SELECT ename, dno, salary, 2 DECODE(dno, 4001, salary*1.1, 4002, salary*1.2, salary) AS new_sal 3 FROM employee; ENAME DNO SALARY NEW_SAL ----------------------------------------------------- DIKIM 2001 1400000 1400000 CHLEE 4001 1900000 2090000 KMKIM 4002 1800000 2160000
기타함수 급여가 NULL 일경우 0 으로반환 isql> SELECT ename, salary, NVL(salary, 0) 2 FROM employee; ENAME SALARY NVL(SALARY,0) ---------------------------------------------------- EJJUNG 0 HJNO 1500000 1500000 HSCHOI 2000000 2000000 isql> SELECT ename, salary, NVL2(salary, salary, 0) 2 FROM employee; ENAME SALARY NVL2(SALARY, SALARY, 0) -------------------------------------------------------------- EJJUNG 0 HJNO 1500000 1500000 HSCHOI 2000000 2000000
2.3 그룹함수 & 윈도우함수
그룹함수 그룹함수 여러행에대해 grouping 하여결과를계산 함수 설명 MIN MAX SUM AVG STDDEV VARIANCE COUNT 최소값최대값합계평균표준편차분산행의수 COUNT(*) 를제외한모든그룹함수는 NULL 을제외하고계산
그룹함수 예제 사원들의최소급여, 최대급여, 평균급여, 급여의합, 사원수를검색 isql> SELECT MIN(salary), MAX(salary), AVG(salary), SUM(salary), COUNT(*) 2 FROM employee; MIN(SALARY) MAX(SALARY) AVG(SALARY) SUM(SALARY) COUNT --------------------------------------------------------------------------- 500000 4000000 1836647.06 31223000 20 평균급여 (NULL 을포함하여계산 ) isql> SELECT AVG(NVL(salary,0)) 2 FROM employee; AVG(NVL(SALARY,0)) --------------------- 1561150 사원의수 isql> SELECT COUNT(salary), COUNT(NVL(salary,0)), COUNT(*) 2 FROM employee; COUNT(SALARY) COUNT(NVL(SALARY,0)) COUNT ------------------------------------------------------------------- 17 20 20
GROUP BY 데이터를 grouping 데이터를그룹으로나눈후그룹함수를적용 구문 예제 SELECT column_name, group_function(), FROM table_name [WHERE conditions] [GROUP BY grouping_expression] ORDER BY {column_name alias column_index} [ASC DESC] LIMIT [start_index,] row_count; 부서번호별로급여의합, 급여의평균을검색 isql> SELECT dno, SUM(salary), AVG(salary) 2 FROM employee 3 GROUP BY dno; DNO SUM(SALARY) AVG(SALARY) ---------------------------------------- 1001 4300000 2150000 1002 2680000 1340000 1003 9753000 2438250
GROUP BY 여러칼럼을 GROUP BY 그룹내그룹 첫번째칼럼을먼저 grouping 하고, 그그룹을두번째칼럼을기준으로또 grouping 을수행 예제 부서번호, 직무별로급여의합, 급여의평균을검색 isql> SELECT dno, emp_job, SUM(salary), AVG(salary) 2 FROM employee 3 GROUP BY dno, emp_job 4 ORDER BY 1, 2; DNO EMP_JOB SUM(SALARY) AVG(SALARY) ---------------------------------------------------------- 1001 ENGINEER 2000000 2000000 1001 MANAGER 2300000 2300000 1002 PM 980000 980000 1002 PROGRAMMER 1700000 1700000 1003 PM 2003000 2003000 1003 PROGRAMMER 4000000 4000000 1003 WEBMASTER 3750000 1875000 2001 PM 1400000 1400000 3001 PL 1800000 1800000
HAVING 그룹의제한 WHERE 절을사용하여그룹을제한할수없다. HAVING 절을이용하여그룹을제한할수있다. 구문 isql> SELECT dno, SUM(salary) 2 FROM employee 3 WHERE SUM(salary) > 2000000 4 GROUP BY dno; [ERR-31061 : An aggregate function is not allowed here. isql> SELECT dno, SUM(salary) 2 FROM employee 3 GROUP BY dno 4 HAVING SUM(salary) > 2000000; SELECT column_name, group_function(), FROM table_name [WHERE conditions] [GROUP BY grouping_expression] [HAVING group_condition] [ORDER BY column_name] LIMIT [start_index,] row_count;
HAVING 예제 급여의최대값이 2000000 이넘는부서를검색 isql> SELECT dno, MAX(salary) 2 FROM employee 3 GROUP BY dno 4 HAVING MAX(salary) > 2000000; DNO MAX(SALARY) --------------------------- 1001 2300000 1003 4000000 3002 2500000 3 rows selected.
WINDOWS FUNCTION 윈도우함수 행과행의관계를정의하거나, 행과행을비교, 연산하는함수 그룹함수와는다르게결과가줄어들지않음 ALTIBASE에서는윈도우함수중그룹별집계함수와순위함수를제공 종류 집계함수 SUM(), AVG(), MAX(), MIN(), COUNT(), STDDEV(), VARIANCE() 순위함수 RANK(), DENSE_RANK(), ROW_NUMBER() 구문 SELECT window_function([argument]) OVER ( [PARTITION BY value expression1 ORDER BY value expression2 [ASC DESC] ] ) FROM table_name;
WINDOWS FUNCTION 집계함수예제 사원정보와사원이속한부서의급여의합, 평균, 최대, 최소값을검색 isql> SELECT ename, dno, salary, 2 SUM(salary) OVER (PARTITION BY dno ) sum_dno_sal, 3 AVG(salary) OVER (PARTITION BY dno) avg_dno_sal, 4 MAX(salary) OVER (PARTITION BY dno) max_dno_sal, 5 MIN(salary) OVER (PARTITION BY dno) min_dno_sal 6 FROM employee; ENAME DNO SALARY SUM_DNO_SAL AVG_DNO_SAL MAX_DNO_SAL MIN_DNO_SAL ------------------------------------------------------------------------- HSCHOI 1001 2000000 4300000 2150000 2300000 2000000 JHCHOI 1001 2300000 4300000 2150000 2300000 2000000 KWKIM 1002 980000 2680000 1340000 1700000 980000 HYCHOI 1002 1700000 2680000 1340000 1700000 980000 JHSEOUNG 1003 1000000 9753000 2438250 4000000 1000000 MSKIM 1003 2750000 9753000 2438250 4000000 1000000 KCJUNG 1003 2003000 9753000 2438250 4000000 1000000 YHBAE 1003 4000000 9753000 2438250 4000000 1000000 DIKIM 2001 1400000 1400000 1400000 1400000 1400000 KSKIM 3001 1800000 1800000 1800000 1800000 1800000 SJKIM 3002 2500000 2500000 2500000 2500000 2500000 20 rows selected.
WINDOWS FUNCTION 순위함수예제 사원정보를급여가높은순서부터순위함수를이용하여검색 isql> SELECT eno, ename, salary, 2 RANK() OVER (ORDER BY salary DESC ) RANK, 3 DENSE_RANK() OVER (ORDER BY salary DESC) DENSE_RANK, 4 ROW_NUMBER() OVER (ORDER BY salary DESC) ROW_NUMBER 5 FROM employee; ENO ENAME SALARY RANK DENSE_RANK ROW_NUMBER --------------------------------------------------------------------- 10 YHBAE 4000000 1 1 1 11 MSKIM 2750000 2 2 2 5 SJKIM 2500000 3 3 3 16 JHCHOI 2300000 4 4 4 14 KCJUNG 2003000 5 5 5 3 HSCHOI 2000000 6 6 6 18 CHLEE 1900000 7 7 7 12 MYLEE 1890000 8 8 8 19 KMKIM 1800000 9 9 9 4 KSKIM 1800000 9 9 10 6 HYCHOI 1700000 11 10 11 2 HJNO 1500000 12 11 12 20 rows selected.
2.4 JOIN
JOIN 개념 두개이상의테이블들을연결하여데이터를출력 EMPLOYEE ENO ENAME DNO 6 gdhong A001 DEPARTMENT DNO DNAME DEP_LOCATION A001 응용기술팀마포 ENAME DNO DNAME gdhong A001 응용기술팀
JOIN 종류 JOIN 조건으로사용되는연산자에따른분류 JOIN EQUI JOIN NON EQUI JOIN 설명 두테이블간의칼럼값들이서로일치하는경우 JOIN 조건으로 '=' 연산자를사용 두테이블간의칼럼값들이서로일치하지않는경우 JOIN 조건으로 'BETWEEN ~ AND' 등의범위비교연산자를사용 FROM 절의 JOIN 형태에따른분류 JOIN INNER JOIN OUTER JOIN 설명 JOIN 조건에서값이일치하는행만반환 JOIN 조건에서값이일치하지않더라도행을반환
JOIN EQUI JOIN 두테이블간의칼럼값들이서로정확하게일치하는경우에사용 WHERE 절에 '=' 연산자를사용해서비교 구문 SELECT table1.column_name, table2.column_name, FROM table1, table2 WHERE table1.column_name = table2.column_name; 예제 사원이어떤부서에서근무하는지를검색 isql> SELECT employee.eno, employee.ename, department.dno, department.dname 2 FROM employee, department 3 WHERE employee.dno = department.dno; 테이블별칭을통한검색 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e, department d 3 WHERE e.dno = d.dno;
JOIN N 개의테이블을 JOIN 최소 (N-1) 개의 JOIN 조건이필요 CUSTOMER ORDERS GOODS CNAME CNO CNO GNO ORDER_DATE GNO GNAME SNKIM 1129 1129 A111100 2011-03-11 A111100 IM-300 CNAME GNAME ORDER_DATE SNKIM IM-300 2011-03-11 예제 고객이주문한상품에대한정보를조회 isql> SELECT c.cname, g.gname, o.orders_date 2 FROM customer c, orders o, goods g 3 WHERE c.cno = o.cno 4 AND o.gno = g.gno 5 ORDER BY c.cname;
JOIN NON EQUI JOIN 두테이블간의칼럼값들이서로일치하지않는경우에사용 WHERE 절에 BETWEEN AND, >, <, >=, <= 등의비교연산자를사용구문 EMPLOYEE ENO ENAME SALALY 6 gdhong 2300000 SALGRADE GRADE LOSAL HISAL 3 2000001 3000000 ENAME SALARY GRADE gdhong 2300000 3 예제 사원이받는급여의등급을조회 isql> SELECT e.ename, e.salary, s.grade 2 FROM employee e, salgrade s 3 WHERE e.salary BETWEEN s.losal AND s.hisal;
JOIN INNER JOIN JOIN 조건에서값이일치하는행만반환 ON 절을사용하여 JOIN 조건을명시 구문 SELECT table1.column_name, table2.column_name, FROM table1 [INNER] JOIN table2 ON table1.column_name = table2.column_name; 예제 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e, department d 3 WHERE e.dno = d.dno; isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e INNER JOIN department d 3 ON e.dno = d.dno;
JOIN OUTER JOIN JOIN 조건을만족하지않는값도반환 EMPLOYEE ENO ENAME DNO 6 gdhong DEPARTMENT DNO DNAME DEP_LOCATION A001 응용기술팀마포 ENAME DNO DNAME gdhong 구문 SELECT table1.column_name, table2.column_name, FROM table1 {LEFT RIGHT FULL} OUTER JOIN table2 ON table1.column_name = table2.column_name; LEFT OUTER JOIN 왼쪽테이블을기준으로해서조인조건에만족하지않는값까지반환 RIGHT OUTER JOIN 오른쪽테이블을기준으로해서조인조건에만족하지않는값까지반환
JOIN FULL OUTER JOIN 양쪽테이블을기준으로해서조인조건에만족하지않는값까지반환 예제 사원이어떤부서에서근무하는지를검색부서를아직배정받지못한사원정보까지함께검색 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e LEFT OUTER JOIN department d 3 ON e.dno = d.dno; isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM department d RIGHT OUTER JOIN employee e 3 ON e.dno = d.dno; 사원이어떤부서에서근무하는지검색부서를아직배정받지못한사원및사원이아직없는부서정보도함께검색 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM department d FULL OUTER JOIN employee e 3 ON e.dno = d.dno;
JOIN ORACLE STYLE OUTER JOIN 오라클데이터베이스와응용프로그램을 ALTIBASE HDB 로좀더쉽게포팅할수있도록, 오라클스타일의 OUTER JOIN 연산자를지원 예제 사원이어떤부서에서근무하는지검색부서를아직배정받지못한사원정보까지함께검색 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e LEFT OUTER JOIN department d 3 ON e.dno = d.dno; isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM employee e, department d 3 WHERE e.dno = d.dno(+); 사원정보가없는부서정보까지함께검색 isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM department d LEFT OUTER JOIN employee e 3 ON e.dno = d.dno; isql> SELECT e.eno, e.ename, d.dno, d.dname 2 FROM department d, employee e 3 WHERE e.dno(+) = d.dno;
JOIN 유의사항 JOIN 조건이잘못되거나생략했을경우 cartesian product 가발생한다. ( 각테이블의행의수를곱한만큼결과집합이생성 ) INNER JOIN 사용시 ON 절은반드시작성해야한다. NATURAL JOIN, CROSS JOIN, USING 절을이용한 JOIN 은지원하지않는다.
2.5 SUBQUERY
SUBQUERY 개념 하나의 SQL 문안에포함되어있는또다른 SQL 문 main query 가실행되기전에먼저실행 subquery 에서는 main query 의모든칼럼을참조할수있지만 main query 에서는 subquery 의칼럼을참조할수없음 Main Query gildong 보다많은급여를받는사원은? Subquery gildong 의급여는? Main Query Subquery ( SELECT FROM WHERE ) SELECT.. FROM.. WHERE.. WHERE..
SUBQUERY 구문 예제 SELECT select_list FROM table_name WHERE 표현식연산자 (SELECT select_list FROM table_name WHERE 조건식 ); gildong 보다급여를많이받는사원을출력 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary > (SELECT salary 4 FROM employee 5 WHERE ename = 'gildong') ; LA 에서일하는사원을출력 isql> SELECT ename, dno 2 FROM employee 3 WHERE dno = (SELECT dno 4 FROM department 5 WHERE dep_location= 'LA') ;
SUBQUERY subquery 유형 단일행 subquery subquery 에서한행만반환 Main Query Subquery 다중행 subquery subquery 에서여러행을반환 Main Query Subquery 다중열 subquery subquery 에서여러열을반환 반환 반환 KIM KIM LEE PARK Main Query Subquery 반환 KIM 10 LEE 20
SUBQUERY subquery 유형 subquery 에서한행만반환 단일행비교연산자를사용 연산자 의미 예제 = 같음 > 보다큼 >= 크거나같음 < 보다작음 <= 작거나같음 <>,!= 같지않음 20 번사원과같은부서에서일하는사원을출력 isql> SELECT ename, dno 2 FROM employee 3 WHERE dno = (SELECT dno 4 FROM employee 5 WHERE eno = 20) ;
SUBQUERY 20 번사원과같은부서에서일하면서 17 번사원보다급여를많이받는사원을출력 isql> SELECT ename, dno, salary 2 FROM employee 3 WHERE dno = (SELECT dno 4 FROM employee 5 WHERE eno = 20) 6 AND salary > (SELECT salary 7 FROM employee 8 WHERE eno=17); 급여를가장많이받는사원을출력 subquery 내에그룹함수를사용 isql> SELECT ename, salary 2 FROM employee 3 WHERE salary = (SELECT max(salary) 4 FROM employee);
SUBQUERY 평균급여가 4002 번부서의평균급여보다많은부서번호를출력 HAVING 절에서 subquery 를사용 isql> SELECT dno, AVG(salary) 2 FROM employee 3 GROUP BY dno 4 HAVING AVG(salary) > (SELECT AVG(salary) 5 FROM employee 6 WHERE dno=4002);
SUBQUERY 다중행 subquery subquery 에서여러행을반환 다중행비교연산자를사용 연산자 IN ANY ALL EXISTS 의미 subquery 결과에존재하는임의의값과일치 subquery 결과에존재하는어느하나의값이라도만족 >ANY : 최소값보다큰경우 <ANY : 최대값보다작은경우 subquery 결과에존재하는모든값에만족 >ALL : 최대값보다큰경우 <ALL : 최소값보다작은경우 NULL 을포함할경우 No rows selected. subquery 결과를만족하는값이존재하는지여부를확인 예제 1003 부서에서근무하고있는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary = (SELECT salary 4 FROM employee 5 WHERE dno = 1003); [ERR-31002 : A single-row subquery returns more than one row.]
SUBQUERY 1003 부서에서근무하고있는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary IN (SELECT salary 4 FROM employee 5 WHERE dno = 1003); DNO ENAME SALARY -------------------------------------------------- 1003 YHBAE 4000000 1003 MSKIM 2750000 1003 KCJUNG 2003000 1003 JHSEOUNG 1000000 4 rows selected.
SUBQUERY 4002 번부서에서근무하는사원중급여를가장많이받는사람보다급여를 적게받는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary <ANY (SELECT salary 4 FROM employee 5 WHERE dno=4002); 500000, 1800000, 1890000 4002 번부서에서근무하는사원중급여를가장적게받는사람보다도급여를적게받는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary <ALL (SELECT salary 4 FROM employee 5 WHERE dno=4002); 500000, 1800000, 1890000
SUBQUERY 4002 번부서에서근무하는사원중급여를가장많이받는사람보다급여를 많이받는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary >ALL (SELECT salary 4 FROM employee 5 WHERE dno=4002) ; 500000, 1800000, 1890000 4002 번부서에서근무하는사원중급여를가장적게받는사람보다도급여를많이받는사원들을출력 isql> SELECT dno, ename, salary 2 FROM employee 3 WHERE salary >ANY (SELECT salary 4 FROM employee 5 WHERE dno=4002) ; 500000, 1800000, 1890000
SUBQUERY 다중열 subquery subquery 에서여러칼럼의데이터를반환 () 와 IN 연산자를이용하여비교 예제 13 번사원과같은부서이면서같은직무를하는사람과 15 번사원과같은부서이면서같은직무를하는사원을출력 isql> SELECT eno, ename, dno, emp_job 2 FROM employee 3 WHERE (dno, emp_job) IN (SELECT dno, emp_job 4 FROM employee 5 WHERE eno IN (13, 15)) ; ENO ENAME DNO EMP_JOB ---------------------------------------------------------- 11 MSKIM 1003 WEBMASTER 13 KWKIM 1002 PM 15 JHSEOUNG 1003 WEBMASTER 3 rows selected.
SUBQUERY 13, 15 번사원과같은부서이면서 13, 15 번사원과같은직무를하는사원을 출력 isql> SELECT eno, ename, dno, emp_job 2 FROM employee 3 WHERE dno IN (SELECT dno 4 FROM employee 5 WHERE eno IN (13, 15)) 6 AND emp_job IN (SELECT emp_job 7 FROM employee 8 WHERE eno IN (13,15)); ENO ENAME DNO EMP_JOB -------------------------------------------------------------- 13 KWKIM 1002 PM 11 MSKIM 1003 WEBMASTER 14 KCJUNG 1003 PM 15 JHSEOUN 1003 WEBMASTER 4 rows selected.
SUBQUERY scalar subquery SELECT 절에서사용하는 subquery main query의 row 수만큼반복되어실행 조건에만족하는 subquery의데이터가없을경우 NULL을반환 하나의행, 열만출력해야함 예제 사원정보와각사원이속한부서의평균급여를함께출력 isql> SELECT ename, salary, 2 (SELECT AVG(salary) FROM employee WHERE dno = e.dno) avg_sal 3 FROM employee e; ENAME SALARY AVG_SAL ------------------------------------------------- HSCHOI 2000000 2150000 KSKIM 1800000 1800000 SJKIM 2500000 2500000 HYCHOI 1700000 1340000 HJMIN 500000 1396666.67
SUBQUERY inline view FROM 절에서사용하는 subquery 동적인 view main query 에서 inline view 의칼럼사용가능 (subquery 에서는사용불가 ) 예제 사원정보와각사원이속한부서의평균급여를함께출력 isql> SELECT e.ename, e.salary, a.avg_sal 2 FROM employee e, (SELECT dno, AVG(salary) avg_sal FROM employee GROUP BY dno) a 3 WHERE e.dno = a.dno; ENAME SALARY AVG_SAL ----------------------------------------- HSCHOI 2000000 2150000 JHCHOI 2300000 2150000 HYCHOI 1700000 1340000 KWKIM 980000 1340000 YHBAE 4000000 2438250
SUBQUERY 유의사항 subquery 는 ( ) 로묶는다. subquery 에 ORDER BY 절은사용할수없다. isql> SELECT ename, salary 2 FROM employee 3 WHERE salary > (SELECT salary 4 FROM employee 5 WHERE ename = 'gildong' 6 ORDER BY salary) ; 다중행 subquery 는다중행연산자를사용한다. scalar subquery 는 main query 의레코드수만큼반복되어실행되므로성능저하가일어날수있다.
2.6 SET 연산자
SET Operator 개념 여러개의질의결과를집합연산을수행하여하나의결과집합으로반환 종류 SET Operator UNION UNION ALL MINUS INTERSECT 설명합집합을출력. 교집합부분은한번만출력합집합을출력. 교집합부분은반복출력차집합을출력교집합을출력 구문 SELECT statement1 {UNION UNION ALL MINUS INTERSECT} SELECT statement2;
SET Operator UNION 합집합 첫번째검색문과두번째검색문의결과를모두출력 동일한검색결과가있을경우한번만출력 결과순서는보장하지않음 예제 customer_co 테이블과 customer_pe 테이블의모든 name 정보를얻어온다. 만약 name이중복되면한번만출력 isql> SELECT name FROM customer_co; NAME ---------- ALTIBASE KIM isql> SELECT name FROM customer_pe; NAME ---------- LEE KIM isql> SELECT name FROM customer_co 2 UNION 3 SELECT name FROM customer_pe; NAME -------------- ALTIBASE KIM LEE
SET Operator UNION ALL 첫번째검색문과두번째검색문의결과를모두출력 동일한검색결과가있을경우반복해서출력 첫번째결과집합을출력후두번째결과집합을출력 예제 customer_co 테이블과 customer_pe 테이블의모든 name 정보를얻어온다. isql> SELECT name FROM customer_co; NAME ---------- KIM ALTIBASE isql> SELECT name FROM customer_pe; NAME ---------- LEE KIM isql> SELECT name FROM customer_co 2 UNION ALL 3 SELECT name FROM customer_pe; NAME -------------- KIM ALTIBASE LEE KIM
SET Operator MINUS 차집합 첫번째검색결과에서두번째검색결과를제외한결과를출력 예제 customer_pe 테이블에존재하지않는 customer_co 테이블의 name 정보를얻어 온다. isql> SELECT name FROM customer_co; NAME ---------- KIM ALTIBASE isql> SELECT name FROM customer_pe; NAME ---------- LEE KIM isql> SELECT name FROM customer_co 2 MINUS 3 SELECT name FROM customer_pe; NAME -------------- ALTIBASE
SET Operator INTERSECT 교집합 첫번째검색결과와두번째검색결과중중복된검색결과를출력 예제 customer_pe 테이블에도존재하고 customer_co 테이블에도존재하는 name 정보 를얻어온다. isql> SELECT name FROM customer_co; NAME ---------- KIM ALTIBASE isql> SELECT name FROM customer_pe; NAME ---------- LEE KIM isql> SELECT name FROM customer_co 2 INTERSECT 3 SELECT name FROM customer_pe; NAME -------------- KIM
SET Operator 유의사항 첫번째검색 column 의수와두번째검색 column 의수는같아야한다. isql> SELECT name, cust_no FROM customer_co 2 INTERSECT 3 SELECT name FROM customer_pe; [ERR-31062 : Mismatched number of expressions in the target lists of SELECT statements for a SET query] 첫번째검색 column 의타입과두번째검색 column 의타입은같아야한다. isql> SELECT cust_no FROM customer_co 2 INTERSECT 3 SELECT name FROM customer_pe; [ERR-21011 : Invalid literal] ORDER BY 절은마지막에위치해야한다. isql> SELECT name, cust_no FROM customer_co 2 ORDER BY 1 3 INTERSECT 4 SELECT name FROM customer_pe; UNION ALL 을제외한 SET Operator 사용시집합연산을위한비용이들기때문에성능이느려질수있다.
2.7 계층질의
Hierarchical Query 개념 동일테이블에계층적으로상위와하위데이터가있을때계층적조건을만족하는행들을검색 사원 1 2 3 4 5 eno mgr 1 2 1 3 1 4 3 5 3 1. 데이터모델 2. 계층형구조 3. 테이블
Hierarchical Query 구문 SELECT select_list FROM table_name WHERE condition START WITH condition CONNECT BY condition [IGNORE LOOP]; START WITH 절 계층구조전개의시작위치를지정 조건에만족하는모든행들을루트로사용 CONNECT BY 절 계층구조의부모행들과자식행들간의관계를식별하는조건을명시 이전에검색된행과현재행을구분하기위해 PRIOR 연산자를사용 IGNORE LOOP 계층구성에서 loop 이발생될경우 loop 을발생시키는행을질의의결과집합에추가하지않음 ( 기본적으로 loop 이발생되면 error 발생 )
Hierarchical Query PRIOR 이전에검색된행과현재행을구분하기위해서사용 CONNECT BY, select_list, WHERE절에서사용가능 PRIOR 하위 = 상위 상위데이터에서하위데이터방향으로전개 isql> SELECT ename, eno, mgr 2 FROM employee; ENAME ENO MGR ------------------------- KIM 1 LEE 2 1 PARK 3 1 CHOI 4 3 HAN 5 3 SEO 6 2 JOON 7 2 isql> SELECT ename, eno, mgr 2 FROM employee 3 START WITH mgr IS NULL 4 CONNECT BY PRIOR eno = mgr; ENAME ENO MGR -------------------------- KIM 1 LEE 2 1 SEO 6 2 JOON 7 2 PARK 3 1 CHOI 4 3 HAN 5 3
Hierarchical Query PRIOR PRIOR 상위 = 하위 하위데이터에서상위데이터방향으로전개 isql> SELECT ename, eno, mgr 2 FROM employee; ENAME ENO MGR -------------------------- KIM 1 LEE 2 1 PARK 3 1 CHOI 4 3 HAN 5 3 SEO 6 2 JOON 7 2 isql> SELECT ename, eno, mgr 2 FROM employee 3 START WITH eno = 7 4 CONNECT BY PRIOR mgr = eno; ENAME ENO MGR -------------------------- JOON 7 2 LEE 2 1 KIM 1
Hierarchical Query LEVEL 질의결과가 root 데이터 ( 가장처음에전개 ) 이면 1, 그하위데이터는 2 로하여 leaf 데이터까지 1 씩증가시키는 pseudo column select_list, WHERE, ORDER BY, GROUP BY, HAVING 절에서사용가능 isql> SELECT ename, eno, mgr, level 2 FROM employee 3 START WITH mgr IS NULL 4 CONNECT BY PRIOR eno = mgr; ENAME ENO MGR LEVEL ------------------------------ KIM 1 1 LEE 2 1 2 SEO 6 2 3 JOON 7 2 3 PARK 3 1 2 CHOI 4 3 3 HAN 5 3 3 isql> SELECT ename, eno, mgr, level 2 FROM employee 3 START WITH eno = 7 4 CONNECT BY PRIOR mgr = eno; ENAME ENO MGR LEVEL ---------------------------------- JOON 7 2 1 LEE 2 1 2 KIM 1 3
Hierarchical Query IGNORE LOOP 데이터전개도중이미나타났던동일한데이터가다시나타나면 loop 발생 A loop in hierarchical query detected. 오류발생 loop 발생하더라도오류없이이후데이터는전개하지않음 isql> SELECT * FROM employee; ENAME ENO MGR ------------------------------ KIM 1 7 isql> SELECT ename, eno, mgr, level 2 FROM employee 3 START WITH eno = 1 4 CONNECT BY PRIOR eno = mgr; ENAME ENO MGR LEVEL ------------------------------------- KIM 1 7 1 LEE 2 1 2 SEO 6 2 3 JOON 7 2 3 [ERR-311A4 : A loop in hierarchical query detected.] isql> SELECT ename, eno, mgr, level 2 FROM employee 3 START WITH eno = 1 4 CONNECT BY PRIOR eno = mgr IGNORE LOOP; ENAME ENO MGR LEVEL ----------------------------------- KIM 1 7 1 LEE 2 1 2 SEO 6 2 3 JOON 7 2 3 PARK 3 1 2 CHOI 4 3 3 HAN 5 3 3
Hierarchical Query 유의사항 START WITH 절은 CONNECT BY 절이없는경우사용할수없음 START WITH, CONNECT BY 절에는 subquery를포함할수없음 inline view, view에대해 hierarchical query는사용할수없음 isql> SELECT ename, eno, mgr, level 2 FROM (SELECT ename, eno, mgr 3 FROM employee) 4 START WITH eno = 1 5 CONNECT BY eno = mgr; [ERR-311A1 : A hierarchical query on neither a created view nor inline view is allowed
3. 데이터변경 (DML) 1. INSERT 2. UPDATE 3. DELETE 4. MOVE
3.1 INSERT
데이터변경 DML (Data Manipulation Language) 데이터조작어로칭하며, 새로운행의삽입및기존행의삭제, 기존칼럼 의변경과이동등을가능하게하는 SQL DML 문의종류및기능 종류 기능 INSERT 데이터베이스의특정테이블에새로운레코드를삽입 UPDATE 데이터베이스의특정테이블에서조건에해당하는레코드를찾아명시한칼럼들의값을변경 DELETE 특정테이블에서원하는조건의레코드를찾아삭제 MOVE 원천테이블에서원하는조건의레코드를찾아대상테이블로이동
INSERT INSERT 테이블에새로운레코드를삽입 고객번호 고객이름 거주도시 가입일 1 홍길동 서울 11-JAN-2009 2 김철수 경기 23-SEP-2007 3 박영희 부산 01-JAN-2010 INSERT 4 장동건인천 18-JUL-2011
INSERT 기본 INSERT 구문 INSERT INTO table_name[(column_name,..)] VALUES (value,..); 예제 고객테이블에데이터를입력 isql> INSERT INTO customer 2 VALUES ( 8001011212123, 'HJKIM', 3 'STUDENT', '025282222', 'F', '0101', '150763', 4 ' 서울영등포구여의도동 63 대한생명빌딩 '); 1 row inserted. 주문테이블에데이터를입력 isql> INSERT INTO orders(ono, order_date, eno, cno, gno, qty, arrival_date, processing) 2 VALUES (11290012, TO_DATE ('2000/11/29 01:17:00', 'YYYY/MM/DD HH:MI:SS'), 3 12, 7610011000001, 'E111100001', 1000, 4 TO_DATE('2000/12/01 09:10:00', 'YYYY/MM/DD HH:MI:SS'), 'D'); 1 row inserted.
Subquery 를이용한 INSERT Subquery 를이용한 INSERT 예제 INSERT INTO table_name[(column_name,..)] SELECT column_name, FROM table_name [WHERE conditions] [ORDER BY column_name,..] LIMIT [start_index,] row_count; 주문테이블중 processing= D 의조건을만족하는레코드를찾아 delayed_processing 테이블에입력 isql> DESC delayed_processing ----------------------------------------------------- NAME TYPE IS NULL ----------------------------------------------------- CNO CHAR(14) FIXED ORDER_DATE DATE FIXED isql> INSERT INTO delayed_processing 2 SELECT cno, order_date 3 FROM orders 4 WHERE processing = 'D'; 1 row inserted.
INSERT 시 DEFAULT 값사용 DEFAULT INSERT INTO table_name[(column_name)] VALUES (DEFAULT value,..) DEFAULT VALUES; 예제 고객테이블에 DEFAULT 값을이용해입력 isql> INSERT INTO customer 2 VALUES (7111111431202, 'DJKIM', 'DESIGNER', '023442542', DEFAULT, '1111', 3 135010, 'Jigu Bank'); 1 row inserted. t1 테이블의전체칼럼들에 DEFAULT 값을입력 isql> CREATE TABLE t1 (c1 INTEGER DEFAULT 0, C2 DATE DEFAULT SYSDATE); Create seccess. isql> INSERT INTO t1 DEFAULT VALUES; 1 row inserted isql> SELECT * FROM t1; C1 C2 ------------------------------------ 0 2011/06/09 17:31:55
INSERT 시주의사항 주의사항 칼럼이름을명시하지않았을경우테이블을생성할때테이블의칼럼의개수보다더많은데이터를입력하거나적은데이터를입력할경우오류발생 칼럼이름을명시하지않았을경우테이블을생성할때나열한칼럼순서대로입력 칼럼이름을명시한경우칼럼의개수와삽입할값들의개수는동일해야하며호환가능한데이터형이어야함 일부칼럼은명시하고일부칼럼을명시하지않을경우에명시하지않는칼럼에정의된 DEFAULT 값이삽입되거나 NULL 이삽입 동일한테이블을이용하여 INSERT ~ SELECT 구문을사용가능 NOT NULL 제약조건이명시된칼럼에 NULL 값을입력할수없음
3.2 UPDATE
UPDATE UPDATE 테이블에서명시한칼럼들의데이터를변경 고객번호 고객이름 거주도시 가입일 1 홍길동 서울 11-JAN-2009 2 김철수 경기 23-SEP-2007 3 박영희 부산 01-JAN-2010 UPDATE 고객번호 고객이름 거주도시 가입일 1 홍길동 서울 11-JAN-2009 2 김철수 인천 23-SEP-2007 3 박영희 부산 01-JAN-2010
UPDATE 기본 UPDATE 구문 UPDATE table_name SET column_name = value,... [WHERE conditions]; 예제 사원테이블에서 KMLEE 의급여를변경 isql> UPDATE employee 2 SET salary = 2500000 3 WHERE ename = 'KMLEE'; 1 row updated. 사원테이블에서모든사원들의급여를일괄적으로 7% 씩인상 isql> UPDATE employee 2 SET salary = salary * 1.07; 20 rows updated. 사원테이블에서 KMKIM 의직책과급여를변경 isql> UPDATE employee 2 SET emp_job = PM, salary = 3500000 3 WHERE ename = 'KMKIM'; 1 row updated.
UPDATE 다중칼럼수정 UPDATE table_name SET (column_name, column_name,...) = (value, value,...) [WHERE conditions]; 예제 고객테이블에서 KSKIM 고객의직업과주소를변경 isql> UPDATE customer 2 SET (cus_job, address) = ('BANKER', 'JungGu Pusan') 3 WHERE cname = 'KSKIM'; 1 row updated.
UPDATE SET 절에 subquery 를갖는 UPDATE UPDATE table_name SET column_name = (SELECT statement ) [WHERE conditions]; 예제 보너스테이블에서 10 번사원의직책을 PM 으로변경하고, bonus 를평균에서 10% 인상한값으로변경 isql> UPDATE bonuses 2 SET emp_job = 'PM, 3 bonus = (SELECT 1.1 * AVG(bonus) FROM bonuses) 4 WHERE eno = 10; 1 row updated.
UPDATE WHERE 절에 subquery 를갖는데이터수정 UPDATE table_name SET column_name = value, WHERE column_name 연산자 (SELECT statement ); 예제 주문테이블에서 MYLEE 사원이받은주문량을 50 개씩줄임 isql> UPDATE orders 2 SET qty = qty 50 3 WHERE eno = (SELECT eno FROM employee WHERE ename = MYLEE ); 12 row updated.
UPDATE 시주의사항 주의사항 UPDATE 사용시 WHERE 절을생략할경우, 명시한테이블의전체행을변경 같은칼럼을두번이상사용불가능 SET 절에 subquery 사용시 = 연산자로이용해야하며 subquery 에서 2 개이상의데이터를리턴하면에러발생 NOT NULL 제약조건이있는칼럼을 NULL 로변경할수없음
3.3 DELETE
DELETE DELETE 테이블에서레코드를삭제 고객번호 고객이름 거주도시 가입일 1 홍길동 서울 11-JAN-2009 2 김철수 경기 23-SEP-2007 3 박영희 부산 01-JAN-2010 DELETE 고객번호고객이름거주도시가입일 1 홍길동서울 11-JAN-2009 3 박영희부산 01-JAN-2010
DELETE 기본 DELETE 구문 DELETE [FROM] table_name [WHERE conditions]; 예제 주문테이블의전체데이터를삭제 isql> DELETE FROM orders; 1 row deleted. 물품테이블에서물품명이 IM-300 인레코드를삭제 isql> DELETE FROM goods 2 WHERE gname = 'IM-300'; 1 row deleted.
DELETE WHERE 절에 subquery 를갖는데이터삭제 DELETE [FROM] table_name [WHERE column_name 연산자 ( SELECT statement ); 예제 주문테이블에서 KMKIM 사원이받은주문을삭제 isql> DELETE FROM orders 2 WHERE eno = (SELECT eno FROM employee 3 WHERE ename = 'KMKIM'); 9 rows deleted.
LIMIT LIMIT 삭제되는행의수를제한 DELETE 구문의맨끝에사용 구문 예제 DELETE [FROM] table_name [WHERE conditions] LIMIT [start_index,] row_count; 사원테이블에서부서명이 PRESALES DEPT 인데이터중한건을삭제 isql> DELETE FROM employee 2 WHERE dno = (SELECT dno 3 FROM department 4 WHERE dname = 'PRESALES DEPT') 5 LIMIT 1; Delete success.
DELETE 시주의사항 DELETE 시주의사항 WHERE 조건절을생략할경우테이블의모든레코드를삭제함 대량의데이터를 DELETE 시 LIMIT 을사용하여작업단위를나눌수있음
3.4 MOVE
MOVE MOVE source 테이블에서원하는조건의레코드를찾아 target 테이블로이동 Target 테이블에 INSERT + source 테이블에 DELETE 수행 source 테이블에서 target 테이블로 INSERT target 테이블로 INSERT 후 source 테이블에서 DELETE 1 홍길동 서울 2 김철수 경기 3 박영희 부산 TABLE1 2 김철수 경기 3 박영희 부산 TABLE1 10 장동건 인천 11 현빈 천안 1 홍길동 서울 TABLE2
MOVE 기본 MOVE 구문 예제 MOVE INTO target_table_name FROM source_table_name [WHERE conditions]; t1 테이블에서 t2 테이블로전체레코드를이동 isql> MOVE INTO t2 2 FROM t1; 10 rows moved. t1 테이블에서 t2 테이블로 c1=10 인레코드만이동 isql> MOVE INTO t2 2 FROM t1 3 WHERE c1 = 10; 10 rows moved.
MOVE 특정칼럼만이동 예제 MOVE INTO target_table_name[(column_name,..)] FROM source_table_name [(column_name,..)] [WHERE conditions]; t2 테이블의 i2=4 를만족하는데이터중 i1 과 i2 칼럼의데이터만 t1 으로이동 isql> MOVE INTO t1(i1, i2) 2 FROM t2(i1, i2) 3 WHERE T2.I2 = 4; 2 rows moved T2 테이블의 (i1, i2, i3) 칼럼의데이터를 t1 으로이동 isql> MOVE INTO t1 2 FROM t2(i1, i2, i3); 5 rows moved
LIMIT LIMIT MOVE 구문으로이동하는레코드의수를제한 MOVE 구문의맨끝에사용 MOVE INTO target_table_name FROM source_table_name [WHERE conditions] LIMIT [start_index,] row_count; 예제 고객테이블에서고객번호가 7001011111111 과 7912319999999 사이인 레코드중 3 건만 customer_new 테이블로이동 isql> MOVE INTO customer_new 2 FROM customer 3 WHERE cno BETWEEN 7001011111111 AND 7912319999999 4 LIMIT 3; 3 rows moved.
MOVE 시주의사항 주의사항 WHERE 조건절을생략했을경우테이블의모든행이이동 FROM 절에서테이블의칼럼명을생략할경우, 전체칼럼이이동 칼럼의이름을명시할경우명시하지않은칼럼에는 NULL 값이들어감명시되지않은칼럼이 NOT NULL 제약조건인경우는에러발생 Source 테이블과 Target 테이블은동일테이블일수없음 두테이블간의칼럼개수가같아야하며, 데이터타입도호환가능해야함
4. 트랜잭션관리 1. COMMIT / ROLLBACK / SAVEPOINT
4.1 COMMIT / ROLLBACK / SAVEPOINT
트랜잭션관리 트랜잭션의정의 트랜잭션이란하나이상의 SQL 로이루어진논리적인작업단위 데이터베이스의동시성을제어하고데이터의일관성을유지 트랜잭션의특징 ACID 정상적인트랜잭션의경우데이터베이스무결성을유지하기위해서 ACID 특성을만족시켜야함 Atomicity 트랜잭션내의모든문장이반영 (COMMIT) 되거나, 철회 (ROLLBACK) 되어야함 Consistency 트랜잭션으로인해데이터베이스의무결성이깨지지않아야함 Isolation 한개의트랜잭션이다른트랜잭션의영향을받지않아야함 Durability 완료된트랜잭션은어떤상황에서도영구적으로유지되어야함
COMMIT COMMIT 지금까지트랜잭션안에서수행한모든 SQL 문의결과를데이터베이스에영구적으로반영하면서해당트랜잭션을종료시키는구문 구문 COMMIT; 예제 HSCHOI 사원의급여를 4000000 으로변경한후 COMMIT 을수행 isql> UPDATE employee 2 SET salary = 4000000 3 WHERE ename = HSCHOI ; isql> COMMIT; isql> SELECT ename, salary 2 FROM employee 3 WHERE ename = HSCHOI ; ENAME SALARY ---------------------------- HSCHOI 4000000
ROLLBACK ROLLBACK 지금까지트랜잭션안에서수행한모든 SQL 문들을취소시키고, 데이터를트랜 잭션수행이전상태로복원 구문 예제 ROLLBACK; HSCHOI 사원의급여를변경한후 ROLLBACK 구문을사용해변경을취소 isql> SELECT ename, salary FROM employee 2 WHERE ename = HSCHOI ; ENAME SALARY ----------------------------- HSCHOI 4000000 isql> UPDATE employee 2 SET salary = 4500000 3 WHERE ename = HSCHOI ; isql> ROLLBACK; isql> SELECT ename, salary FROM employee 2 WHERE ename = HSCHOI ; ENAME SALARY ----------------------------- HSCHOI 4000000
SAVEPOINT SAVEPOINT 하나의트랜잭션을여러개의부분으로나누어저장점을표시 ROLLBACK 구문을이용하여해당부분까지만취소가가능 구문 SAVEPOINT savepoint_name; ROLLBACK TO SAVEPOINT savepoint_name;
SAVEPOINT ROLLBACK 과 SAVEPOINT DML1 SAVEPOINT A DML 2 DML 3 SAVEPOINT B DML 4 Time ROLLBACK TO SAVEPOINT B ROLLBACK TO SAVEPOINT A ROLLBACK
SAVEPOINT 예제 사원테이블을변경할때마다저장점을저장하고저장점으로트랜잭션을되돌림 isql> UPDATE employee 2 SET salary = 2300000 3 WHERE ename = 'HSCHOI'; 1 row updated. isql> SAVEPOINT save1; isql> DELETE FROM employee 2 WHERE ename = 'KMKIM'; 1 row deleted. isql> SAVEPOINT save2; isql> INSERT INTO employee(eno, ename, salary, sex) 2 VALUES(21, 'MSJUNG', 3000000, 'F'); 1 row inserted isql> ROLLBACK TO SAVEPOINT save1; isql> SELECT * FROM employee 2 WHERE ename IN ( HSCHOI, KMKIM, MSJUNG ); ENO ENAME SALARY SEX ------------------------------------------- 20 HSCHOI 2300000 F 21 KMKIM 1000000 M 2 rows selected.
트랜잭션관리 데이터일관성 트랜잭션이진행중일때자신의트랜잭션에서는변경이후데이터를조회 트랜잭션이진행중일때다른트랜잭션에서는변경이전데이터를조회 트랜잭션을 COMMIT하면다른트랜잭션에서도변경이후데이터를조회 isql> SELECT eno, salary FROM employee 2 WHERE eno = 10; ENO SALARY --------------- 10 2000000 isql> UPDATE employee 2 SET salary= 3000000 3 WHERE eno = 10; isql> SELECT eno, salary FROM employee 2 WHERE eno = 10; ENO SALARY --------------- 10 3000000 isql> COMMIT; isql> SELECT eno, salary FROM employee 2 WHERE eno = 10; ENO SALARY --------------- 10 2000000 isql> SELECT eno, salary FROM employee 2 WHERE eno = 10; ENO SALARY --------------- 10 3000000
트랜잭션관리 한트랜잭션이변경중인데이터를다른트랜잭션이변경할수없음 Tx1 Tx2 isql> UPDATE employee 2 SET salary= 3000000 3 WHERE eno = 10;... isql> COMMIT; isql> UPDATE employee 2 SET salary= 4000000 3 WHERE eno = 10; Tx1 트랜잭션이 COMMIT/ROLLBACK 할때까지 Blocking 상태에빠져무한정대기
트랜잭션관리 트랜잭션유의사항 ALTIBASE 는기본적으로 AUTOCOMMIT 모드로동작하며 DML 수행시자동으로 ( 암묵적으로 ) COMMIT DDL 문은수행이완료됨과동시에자동으로 COMMIT 이수행 NON-AUTOCOMMIT 모드에서만 SAVEPOINT 가유효함 AUTOCOMMIT 모드변경 isql isql> AUTOCOMMIT OFF; isql> AUTOCOMMIT ON; 현재세션 isql> ALTER SESSION SET AUTOCOMMIT = FALSE; isql> ALTER SESSION SET AUTOCOMMIT = TRUE;
5. ALTIBASE 객체 1. 객체종류 2. 테이블스페이스 & 사용자 3. 테이블 4. 인덱스 5. 뷰 6. 시퀀스 7. 시노님 8. 트리거 9. 큐
5.1 객체종류
객체종류 ALTIBASE HDB 객체 데이터베이스객체는스키마객체와비스키마객체로구분함 스키마객체스키마에포함되어특정사용자에의해관리되는객체 스키마객체의종류 종류 Constraint Index Sequence Synonym Table Procedure/Function View Trigger Queue 설명데이터의정합성을보장하기위한제약조건질의성능향상을위해사용되는물리적인저장구조순차적으로증감하는유일한숫자값을자동으로생성객체에정의한별칭행과열로구성된 2차원저장구조절차적질의처리를제공하는객체논리적가상테이블 (logical table) DML 발생시 DBMS에서절차적질의처리를자동수행메시지를저장하는큐테이블구조의객체
객체종류 비스키마객체 특정스키마에포함되지않고전체데이터베이스수준에서관리되는객체 비스키마객체의종류 종류 Replication User Tablespace Directory 설명트랜잭션로그를네트워크를통해전송하여실시간데이터복제기능을수행스키마의구성단위가장큰논리적데이터저장구조저장프로시저에서파일처리를위해사용하는객체
객체종류 객체이름생성규칙 객체들은한사용자내에서유일한이름을사용 최대 40 바이트까지사용가능 A-Z, a-z, 0-9, _, $ 만을사용 ALTIBASE 의예약어는사용할수없음 첫글자는반드시문자나 _ 로사용해야함 대소문자를구별하거나, 특수문자사용할경우큰따옴표를이용하여표현 ex) CREATE TABLE "support@altibase.com"
5.2 테이블스페이스 & 사용자
ALTIBASE Storage 스토리지구조 하나의데이터베이스는한개이상의테이블스페이스로구성되며, 하나의테이블스페이스는다수의세그먼트또는, 다수의페이지로구성됨 ALTIBASE Storage 구조 Database 메모리테이블스페이스 32K 크기의페이지들로구성 Tablespace Segment Extent Page Tablespace Page 디스크테이블스페이스 다수의세그먼트로구성 세그먼트는다수의익스텐트로구성 익스텐트는 8K 크기의페이지 64 개로구성됨 (512K) 디스크테이블스페이스 메모리테이블스페이스
테이블스페이스개념 테이블스페이스 (Tablespace / TBS) 데이터베이스를구성하는최상위논리적인구조 테이블, 인덱스등의데이터베이스객체들이저장되는논리적인저장소 데이터베이스운영을위해기본적으로하나이상의테이블스페이스가필요
테이블스페이스종류 ALTIBASE 에서제공하는테이블스페이스 데이터속성에따른분류 메모리테이블스페이스 (Memory Tablespace) 디스크테이블스페이스 (Disk Tablespace) 생성시점에따른분류 시스템테이블스페이스 (System Tablespace) 사용자 시스템 일반사용자, SYS 테이블스페이스종류 SYSTEM DICTIONARY TABLESPACE SYSTEM UNDO TABLESPACE SYSTEM MEMORY DEFAULT TABLESPACE SYSTEM DISK DEFAULT TABLESPACE SYSTEM DISK TEMPORARY TABLESPACE 사용자테이블스페이스 (User Tablespace) 사용자의필요에따라선택적으로생성 임시 TBS, 데이터 TBS( 메모리 TBS, 휘발성 TBS, 디스크 TBS)
메모리테이블스페이스생성 메모리테이블스페이스 데이터를메모리에저장하여, 모든트랜잭션처리를메모리상에서처리 체크포인트시에물리적인파일 (checkpoint image file) 에저장 DB 구동시에모든데이터를하드디스크에저장된물리적인파일로부터읽어서메모리로업로드하여사용 구문 ( 기본 ) CREATE MEMORY [DATA] TABLESPACE tablespace_name SIZE size (K M G) ; 예제 초기사이즈가 512M 인메모리테이블스페이스를생성 isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 512M ; Create success. isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 510M ; [ERR-110EE : The initial size of the tablespace should be multiple of expand chunk size ( EXPAND_CHUNK_PAGE_COUNT * PAGE_SIZE(32K) = 4096K )] 메모리테이블스페이스는기본적으로 4M 단위로생성및확장가능함
메모리테이블스페이스생성 구문 ( 자동확장추가 ) CREATE MEMORY [DATA] TABLESPACE tablespace_name SIZE size (K M G) [AUTOEXTEND [ON [NEXT size] [MAXSIZE size] OFF) ] 예제 초기사이즈가 512M 이고, 128M 단위로자동확장가능한최대크기가 2G 인메모리테이블스페이스를생성 isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 512M 3 AUTOEXTEND ON NEXT 128M MAXSIZE 2G ; Create success. 초기사이즈가 512M 이고, 자동확장을하지않는메모리테이블스페이스를생성 isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 512M 3 AUTOEXTEND OFF ; Create success.
메모리테이블스페이스생성 구문 ( 체크포인트경로추가 ) 예제 CREATE MEMORY [DATA] TABLESPACE tablespace_name SIZE size (K M G) [AUTOEXTEND [ON [NEXT size] [MAXSIZE size] OFF) ] [CHECKPOINT PATH path [SPLIT EACH size]] ; 초기사이즈가 512M 이고, 최대 1G 까지 128M 단위로자동확장가능한메모리테이블스페이스를생성 ( 체크포인트이미지파일은다중화를위해 3 개의디렉토리에나누어저장 ) isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 512M 3 AUTOEXTEND ON NEXT 128M MAXSIZE 1G 4 CHECKPOINT PATH /dbs/path1, /dbs/path2, /dbs/path3 ; Create success. isql> CREATE MEMORY TABLESPACE test_mem 2 SIZE 512M 3 AUTOEXTEND ON NEXT 128M MAXSIZE 1G 4 CHECKPOINT PATH /dbs/path1, /dbs/path2, /dbs/path3 5 SPLIT EACH 256M ; Create success.
휘발성테이블스페이스생성 휘발성테이블스페이스 메모리테이블스페이스와동일한구조의테이블스페이스 체크포인트를하지않고, 리두로그를기록하지않음 구문 CREATE VOLATILE [DATA] TABLESPACE tablespace_name SIZE size (K M G) [AUTOEXTEND [ON [NEXT size][maxsize size] OFF) ] ; 예제 초기사이즈가 512M 이고, 최대 1G 까지 128M 단위로자동확장가능한휘발성테이블스페이스를생성 isql> CREATE VOLATILE DATA TABLESPACE test_mem 2 SIZE 512M 3 AUTOEXTEND ON NEXT 128M MAXSIZE 1G ; Create success.
디스크테이블스페이스생성 디스크테이블스페이스 모든데이터가디스크에저장되는테이블스페이스 물리적으로데이터파일로구성되고, 논리적으로세그먼트, 익스텐트, 페이지로구성 구문 CREATE [DISK] [DATA] TABLESPACE tablespace_name DATAFILE datafile_name ' ; 예제 기본경로에데이터파일 test01.dbf 를생성하는 test_disk 테이블스페이스를 생성 isql> CREATE TABLESPACE test 2 DATAFILE 'test01.dbf'; Create success.
디스크테이블스페이스생성 구문 ( 자동확장추가 ) 예제 CREATE [DISK] [DATA] TABLESPACE tablespace_name DATAFILE datafile_name ' [SIZE size (K M G) ] [REUSE] [AUTOEXTEND [ON [NEXT size][maxsize size] OFF)]; 데이터파일 test01.dbf, test02.dbf, test03.dbf 로구성된 100MB 의 test_disk 테이블스페이스를생성 ( 자동확장하지않음 ) isql> CREATE TABLESPACE test_disk 2 DATAFILE test01.dbf, test02.dbf, test03.dbf 3 SIZE 100M AUTOEXTEND OFF; Create success. 데이터파일 test01.dbf, test02.dbf, test03.dbf 로구성되고, 초기크기가 100MB, 2G 까지자동확장하는 test_disk 테이블스페이스를생성 isql> CREATE TABLESPACE test_disk 2 DATAFILE test01.dbf, test02.dbf, test03.dbf 3 SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G ; Create success.
임시테이블스페이스생성 임시테이블스페이스 디스크데이터에대한질의수행중생성되는임시결과를저장하기위한테이블스페이스 트랜잭션이종료하는시점에해당질의가남긴모든데이터들은사라짐 구문 CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE tempfile_name [SIZE size (K M G) ] [REUSE] [AUTOEXTEND [ON [NEXT size][maxsize size] OFF) ] ; 예제 tbs.temp 로구성된 test_temp 임시테이블스페이스를생성. 임시파일의크기는 10M 이고, 5M 크기로자동확장. isql> CREATE TEMPORARY TABLESPACE test_temp 2 TEMPFILE 'tbs.temp 3 SIZE 10M AUTOEXTENED ON NEXT 5M ; Create success.
테이블스페이스변경 테이블스페이스변경 ALTER TABLESPACE 구문으로테이블스페이스에데이터파일추가 / 삭제, 자동확장설정, 최대크기등에대해서변경이가능 구문 ( 자동확장추가 ) ALTER TABLESPACE tablespace_name { [ ADD DROP ] [ DATAFILE TEMPFILE ] [ ALTER [ DATAFILE TEMPFILE ] file_name SIZE [ AUTOEXTEND [ON [NEXT size][maxsize size] OFF) ] }; 예제 test_disk 테이블스페이스에 64 MB 의데이터파일 test01.dbf 를추가 ( 공간이더필요할때는 500K 씩파일이자동확장 ) isql> ALTER TABLESPACE test_disk 2 ADD DATAFILE test01.dbf SIZE 64M 3 AUTOEXTEND ON NEXT 500K; Alter success.
테이블스페이스변경 test_disk 디스크테이블스페이스가자동확장을하지않도록변경 isql> ALTER TABLESPACE test_disk 2 ALTER DATAFILE ' test01.dbf' AUTOEXTEND OFF; Alter success. test_disk 테이블스페이스의데이터파일 test01.dbf 를삭제하시오. isql> ALTER TABLESPACE test_disk 2 DROP DATAFILE test01.dbf ; Alter success.
테이블스페이스삭제 테이블스페이스삭제 데이터베이스에서테이블스페이스를제거함 시스템테이블스페이스는삭제할수없음 구문 예제 DROP TABLESPACE tablespace_name [INCLUDING CONTENTS] [ AND DATAFILES CASCADE CONSTRAINTS] ; 메모리테이블스페이스 test_mem 을삭제 isql> DROP TABLESPACE test_mem; Drop success. 디스크테이블스페이스 test_disk 의모든객체, 데이터파일들과함께테이블스페이스를삭제 isql> DROP TABLESPACE test_disk 2 INCLUDING CONTENTS AND DATAFILES; Drop success.
사용자개념 USER 스키마를구성하는단위 DB 생성초기에는시스템관리자인 SYSTEM_ 와 SYS 사용자만존재 일반스키마를구축하기위해서는일반사용자를생성 사용자종류 SYSTEM_ 메타테이블의소유자 SYS DBA 로모든권한을가지며, 시스템수준의모든작업을수행함 일반사용자 CREATE 구문을통해생성된사용자로자신이소유한스키마객체에대한권한을가짐
사용자개념 비밀번호규칙 객체이름과유사한규칙 지정할수있는최대크기는운영체제에따라다르며 8~40자사이 Solaris10, Windows XP 이후 : 40자 Solaris 2.8 이후, Windows NT : 11자 그외운영체제 : 8자 최대크기보다많이입력된경우, 이후문자는무시함
사용자생성 사용자생성 CREATE 구문을이용하여생성하며, 사용자생성시비밀번호를지정하고테이블스페이스를지정할수있음 최대크기보다많이입력된경우, 이후문자는무시함 구문 ( 기본 ) CREATE USER user_name IDENTIFIED BY password; 예제 사용자명이 alti 암호가 altibase 인사용자를생성 isql> CREATE USER alti 2 IDENTIFIED BY altibase; Create success.
사용자생성 구문 ( 테이블스페이스추가 ) CREATE USER user_name IDENTIFIED BY password [DEFAULT TABLESPACE tablespace_name] [TEMPORARY TABLESPACE tablespace_name [ACCESS tablespace_name ON OFF]; 예제 사용자명이 alti, 암호가 altibase 인사용자가 SYS_TBS_MEM_DATA 테이블스페이스에대해사용권한을갖도록생성 isql> CREATE USER alti 2 IDENTIFIED BY altibase 3 ACCESS sys_tbs_mem_data ON; Create success.
사용자생성 사용자명이 alti, 암호는 altibase 인사용자가 default tablespace 로 test_disk 를, temporary tablespace 로 SYS_TBS_DISK_TEMP 를사용하며, test_mem 테이블스페이스에대해사용권한을갖도록생성 isql> CREATE USER alti 2 IDENTIFIED BY altibase 3 DEFAULT TABLESPACE test_disk 4 TEMPORARY TABLESPACE sys_tbs_disk_temp 5 ACCESS test_mem ON; Create success.
사용자변경 사용자변경 사용자의암호, 기본테이블스페이스 / 임시테이블스페이스 / 테이블스페이스사용권한을변경 구문 예제 ALTER USER user_name [IDENTIFIED BY password ] [DEFAULT TABLESPACE tablespace_name] [TEMPORARY TABLESPACE tablespace_name [ACCESS tablespace_name ON/OFF]; alti 사용자의비밀번호를 edu 로변경 isql> ALTER USER alti 2 IDENTIFIED BY edu; Alter success.
사용자변경 alti 사용자의 default tablespace 를 sys_tbs_disk_data 로변경 isql> ALTER USER alti 2 DEFAULT TABLESPACE sys_tbs_disk_data; Alter success. alti 사용자가 sys_tbs_mem_data 에접근하지못하도록변경 isql> ALTER USER alti 2 ACCESS sys_tbs_mem_data OFF; Alter success.
사용자삭제 사용자삭제 데이터베이스에명시된사용자를제거함 구문 DROP USER user_name [CASCADE]; 예제 사용자 alti 와사용자가소유한모든객체들을삭제 isql> DROP USER alti 2 CASCADE; Drop success.
5.3 테이블
테이블개념 테이블정의 데이터를저장하기위한가장기본적인논리적데이터저장구조 열 (column) 과행 (row) 으로구성됨 관계형데이터베이스시스템에서가장중요한객체 테이블종류 메모리테이블 데이터를메모리에적재후, 모든트랜잭션처리를메모리상에서진행함 디스크 I/O가거의발생하지않음 디스크테이블 데이터를디스크에적재후, 일부의데이터를메모리영역 ( 버퍼 ) 에올려서사용 데이터변경이나조회시디스크 I/O 가발생할수있음
테이블생성 구문 ( 기본 ) CREATE TABLE table_name ( column_name datatype [DEFAULT expr] [column_constraint], [table_constraint] ) ; 예제 isql> CREATE TABLE emp 2 (ename VARCHAR(10), 3 age NUMBER, 4 phone VARCHAR(20)); Create success.
테이블개념 데이터타입 분류데이터타입설명 문자형 CHAR NCHAR VARCHAR NVARCHAR 고정길이문자형데이터타입. 최대 32KB 고정길이유니코드문자형데이터타입가변길이문자형데이터타입. 최대 32KB 가변길이유니코드문자형데이터타입 숫자형 Native Type Non-Native Type SMALLINT INTEGER BIGINT REAL DOUBLE NUMERIC NUMBER DECIMAL FLOAT 2Bytes 크기의정수형데이터타입 4Bytes 크기의정수형데이터타입 8Bytes 크기의정수형데이터타입 C 의 FLOAT 과동일한데이터타입. 4Bytes 크기 8 bytes 크기의부동소수점데이터타입 고정소수점데이터타입 NUMERIC 데이터타입과동일 precision, scale 아무것도안주면 FLOAT 와동일 NUMERIC 데이터타입과동일 부동소수점데이터타입 precision 만지정가능
테이블개념 데이터타입 분류데이터타입설명 날짜형 DATE 8Bytes 마이크로초까지저장 / 검색가능. HH 는 HH24 와동일 이진형 대용량 BIT BYTE NIBBLE BLOB CLOB 0 과 1 로만이루어진고정길이이진데이터타입최대크기 131068 고정길이이진데이터타입최대 32KB 가변길이이진데이터타입최대크기 254 대용량이진형데이터타입최대 2GB 대용량문자형데이터타입최대 2GB 공간형 GEOMETRY 공간형데이터타입. 최대 100MB
데이터타입 문자형데이터타입 CHAR 명시된크기만큼고정길이를가지는문자형데이터타입 명시된크기보다입력값의크기가작을경우뒷부분은공백으로채워짐 CHAR [(size)] isql> CREATE TABLE emp ( ename CHAR(20) ); VARCHAR 명시된크기내에서가변길이를가지는문자형데이터타입 명시된크기는최대저장가능한문자열의길이 VARCHAR [(size)] isql> CREATE TABLE emp ( ename VARCHAR(50) );
데이터타입 NCHAR 명시된크기만큼고정길이를가지는문자형데이터타입 칼럼의문자하나당크기는내셔널캐릭터셋의설정에따라서다르게저장됨 NCHAR [(size)] NVARCHAR 명시된크기내에서가변길이를가지는문자형데이터타입 칼럼의문자하나당크기는내셔널캐릭터셋의설정에따라서다르게저장됨 NVARCHAR [(size)]
데이터타입 숫자형데이터타입 (Native Type) BIGINT 8 바이트크기의정수형데이터타입 C의 long(64 bit) 이나 long long(32 bit) 과동일한데이터타입 BIGINT isql> CREATE TABLE emp ( eno BIGINT) ; DOUBLE 8 바이트크기의부동소수점형타입 ( C의 double과동일함 ) DOUBLE isql> CREATE TABLE emp ( price DOUBLE) ; INTEGER 4 바이트크기의정수형데이터타입 ( C의 int와동일함 ) INTEGER isql> CREATE TABLE emp ( eno INTEGER) ;
데이터타입 SMALLINT 2바이트크기의정수형데이터타입 C의 short와동일한데이터타입 SMALLINT isql> CREATE TABLE t1 ( c1 SMALLINT) ; REAL 4 바이트크기의부동소수점형타입 C의 float 와동일한데이터타입 REAL isql> CREATE TABLE t1 ( c1 REAL) ;
데이터타입 숫자형데이터타입 (Non Native Type) NUMERIC Precision 과 scale 을가지는숫자형데이터타입으로 precision 만큼의유효숫자와 scale 만큼의소수점이하정밀도를가지는고정소수점형 precision 과 scale 이모두생략되면 precision 은 38, scale 은 0 인정수를표현하는형식인고정소수점으로사용 FLOAT NUMERIC [(precision, scale)] isql> CREATE TABLE t1 ( c1 NUMERIC(5,0) ) ; -1E+120 에서 1E+120 까지의부동소수점숫자데이터타입 FLOAT [ (precision) ] DECIMAL NUMERIC 데이터타입과동일한데이터타입 DECIMAL [(precision, [ scale])]
데이터타입 NUMBER NUMERIC type의 alias형으로 precision과 scale이명시되지않으면 FLOAT과동일하게취급 NUMBER [(precision, scale)] isql> CREATE TABLE t1 ( c1 NUMBER(10,2) ) ;
데이터타입 날짜형데이터타입 DATE 날짜를표현하는데이터타입 8 바이트크기를가짐 Microsec까지표현가능 DATE isql> CREATE TABLE emp ( birth DATE ) ; 이진데이터타입 BYTE 명시된크기만큼고정된길이를가지는이진데이터타입 (16 진수로표현 ) 1 바이트는 2 개의문자를입력할수있음 (ex. BYTE(1) => 00~FF) BYTE [(size)] isql> CREATE TABLE orders ( gno BYTE(2) ) ;
데이터타입 NIBBLE 명시된크기만큼가변길이를가지는이진데이터타입 (16 진수로표현 ) 칼럼의크기는기본값으로한개의문자크기이며, 최대 255 크기까지허용 BYTE 와달리명시된 size 만큼의문자만을입력가능 (ex. NIBBLE(1) => 0~F) NIBBLE [(size)] BIT 0과 1로만이루어진고정길이를갖는이진데이터타입 기본값으로 1 bit이며, 최대길이는 131068 bit BIT [(size)] VARBIT 0과 1로만이루어진가변길이를갖는이진데이터타입 기본값으로 1bit이며, 최대길이는 131068 bit (128KB) VARBIT [(size)] isql> CREATE TABLE emp ( code VARBIT(10) ) ;
데이터타입 LOB 데이터타입 대용량데이터를저장할수있는데이터타입 최대 2G 까지저장 이진데이터를저장하는 BLOB 과문자열데이터를저장하는 CLOB 으로구분 BLOB 이진형데이터를저장하기위한타입으로 2G 크기내에서가변길이를가지는이진형데이터타입 CLOB BLOB 문자형대용량데이터를저장하기위한것으로, 2GB 크기내에서가변길이를가지는문자형데이터타입 CLOB 예제 isql> CREATE TABLE emp ( address CLOB, image BLOB) ;
데이터타입유의사항 숫자형데이터타입 가능하면 SMALLINT, INTEGER, BIGINT, REAL, DOUBLE등 native type으로지정을권장 => 데이터처리시변환비용에따른 overhead를줄일수있음. 저장공간의효율성이좋음 LOB 타입제약사항 NON-AUTOCOMMIT MODE 로수행하지않으면오류발생 Connection is in autocommit mode. One can not operate on LOB datas with autocommit mode on 프로시저나트리거에서사용할수없음 Temp Tablespace 에서사용할수없음 인덱스를생성할수없음
제약조건 Constraints in ALTIBASE 제약조건 설명 PRIMARY KEY PRIMARY KEY 값은테이블내에서유일해야하며, NULL 값을가질수없다. 한테이블내에정의가능한 PRIMARY KEY 개수는하나이다. UNIQUE UNIQUE 값은테이블내에서유일해야한다. NULL 값은포함가능하다. FOREIGN KEY 다른테이블의 PRIMARY KEY/UNIQUE 값을참조하는외래키를정의한다. NOT NULL/NULL DEFAULT NOT NULL : 해당칼럼에 NULL 값을가질수없다. NULL : 해당칼럼에 NULL 값을가질수있다. 칼럼값을명시해주지않을경우기본으로저장될값을명시. 지정되지않으면기본값은 NULL 로명시된다. CHECK 제약조건은제공하지않는다.
테이블생성 구문 ( 제약조건 ) column_level CREATE TABLE table_name ( column_name datatype [DEFAULT expr] [CONSTRAINT constraint_name] constraint_type, ) ; table_level CREATE TABLE table_name ( column_name datatype [DEFAULT expr], [CONSTRAINT constraint_name] constraint_type (column_name), ) ; NOT NULL, NULL, DEFAULT 제약조건은 column_level 로만정의가능 복합칼럼으로구성되는제약조건은 table_level 로만정의가능
제약조건 예제 Primary key isql> CREATE TABLE t1(c1 INTEGER PRIMARY KEY, c2 CHAR(10)); Create success. isql> DESC t1 [ TABLESPACE : SYS_TBS_MEM_DATA ] [ ATTRIBUTE ] ------------------------------------------------------------------- NAME TYPE IS NULL ------------------------------------------------------------------- C1 INTEGER FIXED NOT NULL C2 CHAR(10) FIXED [ INDEX ] ------------------------------------------------------------------- NAME TYPE IS UNIQUE COLUMN ------------------------------------------------------------------- SYS_IDX_ID_122 BTREE UNIQUE C1 ASC [ PRIMARY KEY ] ------------------------------------------------------------------- C1
제약조건 Foreign Key isql> CREATE TABLE t2(c1 INTEGER PRIMARY KEY, c3 CHAR(10)); Create success. isql> CREATE TABLE t1 (c1 INTEGER, c2 CHAR(10), 2 FOREIGN KEY(c1) REFERENCES t2(c1)); Create success. isql> CREATE TABLE t1 (c1 INTEGER, c2 CHAR(10), 2 FOREIGN KEY(c1) REFERENCES t2(c1)); [ERR-31011 : TABLE NOT FOUND] isql> CREATE TABLE t2(c1 INTEGER, c3 CHAR(10)); Create success. isql> CREATE TABLE t1 (c1 INTEGER, c2 CHAR(10), 2 FOREIGN KEY(c1) REFERENCES t2(c1)); [ERR-31049 : Unable to find referenced constraint] 참조할테이블 T2 가없어서오류발생 참조할테이블 T2 에기본키가없어서오류발생
제약조건 Unique / Default isql> CREATE TABLE t1(c1 INTEGER UNIQUE, c2 CHAR(10) DEFAULT 'ALTIBASE'); Create success. isql> DESC t1 [ TABLESPACE : SYS_TBS_MEM_DATA ] [ ATTRIBUTE ] --------------------------------------------------------------- NAME TYPE IS NULL --------------------------------------------------------------- C1 INTEGER FIXED C2 CHAR(10) FIXED [ INDEX ] --------------------------------------------------------------- NAME TYPE IS UNIQUE COLUMN --------------------------------------------------------------- SYS_IDX_ID_125 BTREE UNIQUE C1 ASC T1 has no primary key isql> INSERT INTO t1(c1) VALUES(1); 1 row inserted. isql> SELECT * FROM t1; C1 C2 --------------------------- 1 ALTIBASE 1 row selected.
제약조건 NULL / NOT NULL isql> CREATE TABLE t1 (c1 INTEGER NULL, c2 CHAR(10) NOT NULL); Create success. isql> DESC t1 [ TABLESPACE : SYS_TBS_MEM_DATA ] [ ATTRIBUTE ] ------------------------------------------------------------------- NAME TYPE IS NULL ------------------------------------------------------------------- C1 INTEGER FIXED C2 CHAR(10) FIXED NOT NULL T1 has no index T1 has no primary key isql> INSERT INTO t1(c2) VALUES('ALTIBASE'); 1 row inserted. isql> SELECT * FROM t1; C1 C2 --------------------------- ALTIBASE C2 칼럼에는 NULL 이들어갈수없어서오류발생 1 row selected. isql> INSERT INTO t1(c1) VALUES(1); [ERR-31074 : Unable to insert(or update) NULL into NOT NULL column.]
테이블생성 예제 column_level 제약조건생성 isql> CREATE TABLE emp( 2 eno INTEGER CONSTRAINT emp_pk PRIMARY KEY, 3 ename VARCHAR(10) NOT NULL, 4 age NUMBER DEFAULT 1, 5 dno INTEGER REFERENCES dept(dno)); Create success. isql> SET FOREIGNKEYS ON isql> DESC emp; ----------------------------------------------------------------- NAME TYPE IS NULL ----------------------------------------------------------------- ENO INTEGER FIXED NOT NULL ENAME VARCHAR(10) FIXED NOT NULL AGE FLOAT FIXED DNO SMALLINT FIXED [ PRIMARY KEY ] ----------------------------------------------------------------- ENO [ FOREIGN KEYS ] ----------------------------------------------------------------- * SYS_CON_ID_411 * SYS_IDX_ID_405 ( DNO ) ---> SYS.DEPT ( DNO )
테이블생성 table_level 제약조건생성 isql> CREATE TABLE emp( 2 eno INTEGER, 3 ename VARCHAR(10) NOT NULL, 4 age NUMBER DEFAULT 1, 5 dno INTEGER, 6 CONSTRAINT emp_pk PRIMARY KEY(eno), 7 CONSTRAINT emp_fk FOREIGN KEY (dno) REFERENCES dept(dno)); Create success. isql> SET FOREIGNKEYS ON isql> DESC emp; -------------------------------------------------------------------- NAME TYPE IS NULL -------------------------------------------------------------------- ENO INTEGER FIXED NOT NULL ENAME VARCHAR(10) FIXED NOT NULL AGE FLOAT FIXED DNO SMALLINT FIXED [ PRIMARY KEY ] -------------------------------------------------------------------- ENO [ FOREIGN KEYS ] -------------------------------------------------------------------- * EMP_FK * SYS_IDX_ID_405 ( DNO ) ---> SYS.DEPT ( DNO )
테이블생성 사원번호, 사원이름, 부서번호, 성별, 생일을칼럼으로가지는테이블을생성 isql> CREATE TABLE employee( 2 eno INTEGER, 3 ename CHAR(20), 4 dno INTEGER, 5 sex CHAR(1), 6 birth DATE ) ; Create success. 주문번호, 주문일자, 판매사원, 고객주민번호, 상품번호, 주문수량을칼럼으로갖는 orders 테이블을생성. 주문번호와주문일자를기본키로생성 isql> CREATE TABLE orders( 2 ono INTEGER, 3 order_date DATE, 4 eno INTEGER NOT NULL, 5 cno CHAR(14) NOT NULL, 6 gno INTEGER NOT NULL, 7 qty INTEGER DEFAULT 1, 8 PRIMARY KEY(ono, order_date)); Create success.
테이블생성 구문 ( 추가구문 ) CREATE TABLE table_name ( column_name datatype [DEFAULT expr] [column_constraint], [table_constraint] ) [MAXROWS integer ] [TABLESPACE tablespace_name ] [AS subquery ]; 예제 column_level 제약조건생성 isql> CREATE TABLE employee( 2 eno INTEGER PRIMARY KEY, 3 ename CHAR(20) NOT NULL, 4 dno INTEGER, 5 sex CHAR(1) DEFAULT 'M' NOT NULL, 6 birth DATE ) 7 TABLESPACE edu_mem ; Create success.
테이블생성 employee 테이블을 edu_mem 테이블스페이스에생성하고최대 row 수는 1000000 으로지정 isql> CREATE TABLE employee( 2 eno INTEGER PRIMARY KEY, 3 ename CHAR(20) NOT NULL, 4 dno INTEGER, 5 sex CHAR(1) DEFAULT 'M' NOT NULL, 6 birth DATE ) 7 MAXROWS 1000000 8 TABLESPACE edu_mem; Create success. employee 테이블에서부서번호가 10 인조건을만족하는데이터를가진 emp_dept_10 테이블을생성 isql> CREATE TABLE emp_dept_10 2 AS 3 SELECT * 4 FROM employee 5 WHERE dno = 10 ; Create success.
테이블생성 주의사항 칼럼크기지정시최대허용크기를넘거나최소크기보다작으면오류발생 PRIMARY KEY 는한테이블에 2 개이상존재할수없음 참조제약조건의경우 FOREIGN KEY 와 PRIMARY KEY 의칼럼개수는동일해야함 참조제약조건의경우 FOREIGN KEY 와 PRIMARY KEY 의칼럼데이터타입은동일해야함 한테이블에 PRIMARY KEY 또는 UNIQUE 의총합이 32 개를넘을수없음 CREATE TABLE AS SELECT 의경우칼럼명을명시하였다면그개수는검색대상에명시한칼럼개수와동일해야함 CREATE TABLE AS SELECT 의경우 CREATE TABLE 문에칼럼명을명시하지않고검색대상에표현식을사용한경우반드시생성할테이블의칼럼명으로사용하기위해별명 (alias) 이존재해야함
테이블생성 고려사항 Tablespace 와데이터 Memory Tablespace Disk Tablespace Volatile tablespace 고성능데이터 대용량데이터 logging 이필요없는고성능데이터 테이블생성시 tablespace 절지정하여데이터특성에맞게테이블을생성 Ex) 최신일주일동안의데이터를자주 access 한다고하면, 최신일주일데이터는 memory table 로, 일주일이지난 history 성데이터는 disk table 로생성 동일테이블을디스크, 메모리테이블스페이스에나눠서생성할수없음
테이블변경 구문 ( 추가구문 ) 예제 ALTER TABLE table_name { ADD [ COLUMN ] ( column_name data_type ) ALTER [ COLUMN ] ( column_name { SET DEFAULT DROP DEFAULT NULL NOT NULL } MODIFY COLUMN ( column_name data_type ) DROP [ COLUMN ] ( column_name ) ADD table_level_constraint DROP {CONSTRAINT constraint_name PRIMARY KEY UNIQUE(column_name)} RENAME COLUMN column_name TO new_column_name MAXROWS ALL INDEX [ENABLE DISABLE] COMPACT }; orders 테이블에주문상태칼럼을추가 isql> ALTER TABLE orders 2 ADD COLUMN (processing CHAR(1) DEFAULT '0'); Alter success.
테이블변경 orders 테이블의주문상태칼럼의크기를 CHAR(2) 로변경 isql> ALTER TABLE orders 2 MODIFY COLUMN (processing CHAR(2)); Alter success. orders 테이블의주문상태칼럼의이름을변경 isql> ALTER TABLE orders 2 RENAME COLUMN processing TO process; Alter success. orders 테이블의주문상태칼럼을삭제 isql> ALTER TABLE orders 2 DROP COLUMN process; Alter success.
테이블 COMPACT 테이블 COMPACT 데이터가없는빈페이지들에대하여페이지를반환 메모리 / 휘발성테이블과큐만지원 구문 ALTER TABLE table_name COMPACT ; 예제 데이터가삭제된후에 orders 테이블에할당되어있는빈공간을반환 isql> ALTER TABLE orders 2 COMPACT; Alter success.
테이블 TRUNCATE 테이블 TRUNCATE 명시된테이블에서모든데이터를삭제하고 삭제된데이터는취소할수없음 구문 TRUNCATE TABLE table_name ; 예제 orders 테이블의모든데이터를삭제 isql> SELECT COUNT(*) FROM orders; COUNT ----------------------- 1000 isql> TRUNCATE TABLE orders; Truncate success. isql> SELECT COUNT(*) FROM orders; COUNT ----------------------- 0
테이블삭제 테이블 DROP 테이블을삭제 구문 예제 DROP TABLE table_name [CASCADE [CONSTRAINTS] ] ; orders 테이블을삭제 isql> DROP TABLE orders; Drop success. dept 테이블에의해참조되는 emp 테이블을삭제 isql> DROP TABLE emp; [ERR-3102A : A foreign key constraint that depends on the table or column exists.] isql> DROP TABLE emp 2 CASCADE CONSTRAINTS; Drop success.
테이블 RENAME 테이블 RENAME 테이블의이름변경 구문 RENAME table_name TO new_table_name ; 예제 employee 테이블의이름을 emp 로변경 isql> RENAME employee TO emp; Rename success. isql> SELECT ename 2 FROM emp 3 LIMIT 1; ENAME ------------------------ EJJUNG isql> ALTER TABLE employee 2 RENAME TO emp; Alter success.
5.4 인덱스
인덱스개념 INDEX 질의문의성능향상을위해테이블과는별도로저장되는객체 Index 대상칼럼값을 sorting 하여저장 Unique, Primary Key 로지정한칼럼은내부적으로 Unique Index 가생성 메모리테이블의인덱스는메모리에, 디스크테이블의인덱스는디스크에 생성 테이블에대해물리적, 논리적으로독립적인객체이기때문에테이블에관계없이삭제, 수정이가능 테이블의레코드가수정되면해당인덱스도수정이됨 디스크인덱스는질의및인덱스저장시디스크 I/O 비용을줄이기위해테이블과별도의디스크에분리해서저장하는것을권장
인덱스개념 인덱스종류 인덱스속성 Unique Index 인덱스칼럼에중복을허용하지않는인덱스 Non-Unique Index 인덱스칼럼에중복값을허용하는인덱스 UNIQUE 옵션생략시기본적으로생성이되는인덱스 Composite Index 여러개의칼럼들로구성된인덱스 Single Index 하나의칼럼으로구성된인덱스 인덱스저장위치 Memory Index Memory Table 에대한 Index 실제 Table 의데이터에대한 pointer(16bytes) 만저장 Disk Index Disk Table 에대한 Index 칼럼의값과테이블의레코드주소값이저장
인덱스생성 구문 CREATE [UNIQUE ] INDEX index_name ON table_name ( column_name [ ASC DESC ], ) ; 예제 employee 테이블의 salary 칼럼에오름차순의인덱스생성 isql> CREATE INDEX emp_idx1 2 ON employee (salary ASC) ; Create success. employee 테이블의 ename 칼럼에 unique 인덱스생성 isql> CREATE UNIQUE INDEX emp_idx2 2 ON employee (ename) ; Create success. employee 테이블에 dno, emp_job 칼럼에 composite 인덱스생성 isql> CREATE UNIQUE INDEX emp_idx3 2 ON employee (dno, emp_job) ; Create success.
인덱스생성 구문 ( 추가 ) 예제 CREATE [ UNIQUE ] INDEX index_name ON table_name ( column_name [ ASC DESC ], ) [TABLESPACE tablespace_name ] [NOPARALLEL PARALLEL parallel_factor ] ; employee 테이블의 salary 칼럼에 4 개의 CPU 를사용하도록병렬옵션을지정하여 test_mem 테이블스페이스에인덱스를생성 isql> CREATE INDEX emp_idx1 2 ON employee (salary ASC) 3 TABLESPACE test_mem 4 PARALLEL 4; Create success.
인덱스변경 인덱스변경 인덱스를재구축하거나인덱스의이름을변경 구문 ALTER INDEX index_name [REBUILD] [RENAME TO new_ index_name ]; 예제 인덱스 t1_idx1 을재구축 isql> ALTER INDEX t1_idx1 2 REBUILD ; Alter success. 인덱스 t1_idx1 의이름을 t1_idx 로변경 isql> ALTER INDEX t1_idx1 2 RENAME TO t1_idx; Alter success.
인덱스삭제 인덱스 DROP 인덱스를삭제 구문 DROP INDEX index_name ; 예제 인덱스 t1_idx1 를삭제 isql> DROP INDEX t1_idx1; Drop success.
5.5 뷰
뷰개념 View 하나이상의테이블에서데이터의부분집합을논리적으로표시 실제데이터가저장되지않는가상테이블 사용목적 데이터 access 를제한하기위해사용 복잡한질의를쉽게작성하기위해사용 ALTIBASE View 의특징 View 를통해조회만가능 DML(INSERT, UPDATE, DELETE) 이가능한 Updatable View 는제공하지않음 View 의부연질의에대한제한사항 검색대상표현식의개수는최대 1024개 CURRVAL, NEXTVAL 의사열을사용할수없음
뷰생성 구문 예제 CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [( alias_name)] AS sub_query [WITH READ ONLY]; 사원테이블에서각부서의평균월급을부서별로질의하는뷰를생성 isql> CREATE VIEW avg_sal 2 AS 3 SELECT dno, AVG(salary) emp_avg_sal 4 FROM employee 5 GROUP BY dno; Create success. isql> SELECT * FROM avg_sal ; DNO EMP_AVG_SAL ------------------------------------ 1001 2150000 1002 1340000...
뷰삭제 뷰삭제 뷰를삭제하며 base table 은삭제되지않음 구문 DROP VIEW view_name ; 예제 뷰 avg_sal 을삭제 isql> DROP VIEW avg_sal; Drop success. isql> SELECT * FROM avg_sal ; [ERR-31031 : Table not found isql> SELECT ename FROM employee; ENAME ------------------------ EJJUNG HJNO HSCHOI
5.6 시퀀스
시퀀스개념 SEQUENCE 연속적인숫자를생성하는객체 PRIMARY KEY 칼럼의유일값을생성하는데주로사용 트랜잭션과무관한객체. ROLLBACK 등으로인해값이복구되지않음 시퀀스는테이블과독립적이므로테이블에대한블로킹현상이없음 메모리에일정개수를캐시해놓기때문에성능이빠름.NEXTVAL 을먼저사용한후에.CURRVAL 을사용할수있음
시퀀스개념 시퀀스를사용하지않을때의문제점 유일값을얻어올때테이블데이터를조회 (ex. MAX(column)+1) 하는방법은 블로킹현상이발생 User 1 SELECT MAX(c1)+1 FROM t1;.. INSERT INTO t1 VALUSE(10003,..);. COMMIT; T1.c1 1 10001 10002 User 2 SELECT MAX(c1)+1 FROM t1;.. INSERT INTO t1 VALUSE(10003,..); =>Blocking. => DUP 오류발생