표준 Sql 구문정리 1부

SQL 과 SQL*PLUS

SQL

ŸSQL은 tool또는 application과 Oracle Server 와 통신하기위한 언어이다. ŸANSI(American National Standards Institute) 는 관계형 DBMS로 SQL 을 채택하고 있다. ŸSQL문장들은 SQL buffer에 한번에 한 개의 명령만 저장되고 SQL*PLUS 명령을 사용해서 편집 될 수 있다. ŸSQL buffer는 SQL*PLUS 가 관리하며 새로운 SQL 명령이 들어오기까지 SQL문장은 남아있다.

 

SQL*PLUS

ŸSQL*PLUS 는 SQL 문장을 인식하고 실행시키기 위한 Oracle Server tool 이다. ŸSQL*PLUS 는 SQL 의 확장은 아니다. ŸSQL*PLUS 문장은 SQL buffer에 들어가지 않는다.

 

SQL구문분류

Retrieval	Manipulation	Definition	Control	Miscellaneous
SELECT	INSERT UPDATE DELETE COMMIT ROLLBACK	CREATE ALTER DROP RENAME	GRANT REVOKE AUDIT NO AUDIT LOCK	COMMENT

 

SQL*PLUS 명령어들

Execution	Edit	Environment	Report Format
/ RUN EXECUTE	LIST APPEND CHANGE DEL INPUT EDIT	SET SHOW PAUSE	COLUMN CLEAR BREAK COMPUTE TTITLE BTITLE
File Manipulation	Interactive	Database Access	Miscellaneous
SAVE GET START @ @@ SPOOL	DEFINE UNDEFINE PROMPT ACCEPT VARIABLE PRINT	CONNECT COPY DISCONNECT	SQLPLUS EXIT HELP DESCRIBE HOST REMARK RUNFORM TIMING WHENEVER

 

4 RETRIEVE DATA

THE SELECT STATEMENT

데이터베이스 테이블로부터 데이터를 검색한다.

 

Syntax

SELECT [DISTINCT]  column_information FROM  table(s) WHERE  condition ORDER BY  expression or keyword

   · SELECT          :  검색하고자 하는 column, express, constant 등을 나열한다.

   · DISTINCT    :  중복된 데이터를 나타내지 않음 – 없으면 나타냄

   · FROM             :  데이터를 얻고자 하는 테이블(들)을 나열한다.

   · WHERE          :  특정 row(s)을 검색하기위한 조건들을 나열한다.[option]

   · ORDER BY    :  검색된 row(s)을 나타내는 우선순위를 나열한다.[option]

 

테이블의 모든 데이터 보기

SELECT { *  |  column_name [ , column_name …] } FROM table_name

 

중복을 없앤 데이터 보기

SELECT DISTINCT column_name FROM table_name

 

열 이름을 바꿔서 데이터 보기

SELECT [DISTINCT] column_name ALIAS_NAME [“alias_name”] FROM table_name

   · ALIAS_NAME : 대문자만 쓰면 바로 rename 된다.

   · “alias_name”  : 만약 alias_name이 공백,특수문자,대소문자를 같이

      혼용해서 쓰면 “ ” 안에 쓴다.

   · column name 과 alias를 콤마로 구분한다.

   · Oracle Server Version 7.1에서는 AS keyword로 column name 과 alias를

    구별한다.

 

특정 레코드 데이터 보기

SELECT { *  |  column_name [ , column_name …] } FROM table_name WHERE condition

   · condition :  column 이름(들), 표현식(들), 상수(들) 과 비교 연산자들로

                       구성되어진다.

 

비교 연산자들

=	같다.
<>	다르다.
>	크다.
>=	크거나 같다.
<	작다.
<=	작거나 같다.
BETWEEN…AND…	두개의 값 사이
NOT BETWEEN… AND…	두개의 값 사이가 아님
IN (List)	List 안의 값들과 같은 값
NOT IN (List)	List 안의 값들과 같지 않은 값
LIKE	문자열의 형태가 같은
IS NULL	Null 값
IS NOT NULL	Null이 아닌 값

 

     Example1 : BETWEEN … AND …

SELECT  first_name, last_name, salary FROM s_emp WHERE salary BETWEEN 1500 AND 3000;

 

     Example2 : IN (List)

SELECT last_name, title, dept_id FROM s_emp WHERE title IN ( ‘Warehouse  Manager’,‘Stock Clerk’);

 

     Example3 : LIKE

SELECT last_name FROM s_emp WHERE last_name LIKE ‘_a%’;

   · %    :  여러 개의 문자와 대치된다.

   · _     :  한 개의 문자와 대치된다.

 

     Example4 : IS NULL

SELECT  id, name, credit_rating FROM s_customer WHERE sales_rep_id IS NULL;

 

복잡한 조건식으로 특정 레코드 데이터 보기

SELECT { *  |  column_name [ , column_name …] } FROM table_name WHERE condition {AND | OR} condition

 

연산우선순위

우선 순위	연산자
1	모든 비교 연산자
2	AND
3	OR

 

     Example1 : AND 연산 후 OR 연산 

SELECT last_name, salary, dept_id FROM s_emp WHERE salary >= 1000 AND dept_id == 44 OR dept_id = 42;

 

     Example2 : OR 연산 후 AND 연산

SELECT last_name, salary, dept_id FROM s_emp WHERE salary >= 1000 AND (dept_id ==44 OR dept_id = 42);

 

데이터를 순서대로 보기

SELECT { *  |  column_name [ , column_name …] } FROM table_name WHERE condition ORDER BY column_name [ASC | DESC]

   · ASC  : 내림차순 정렬[default]

   · DESC : 오름차순 정렬

 

     Example1 :

SELECT last_name, salary, dept_id FROM s_emp WHERE title = ‘Stock Clerk’ ORDER BY dept_id, salary DECS;

   · dept_id  : 내림차순 정렬[default]

   · salary   : 오름차순 정렬

 

EDIT SQL STATEMENTS

SQL문장을 작성한 후 다양한 SQL*PLUS 명령으로 SQL을 편집한다.

 

SQL*PLUS Edit Commands

명   령	설       명
L[IST] n	n line의 SQL 문장을 list로 출력 SQL> L
A[PPEND] text	Current line의 끝에 text를 추가한다. SQL> A , dept_id
C[HANGE]/old_text/new_text	old_text를 new_text로 교체한다. SQL> C/ENAME , JOB/MANAGER , SALARY
DEL	Current line 삭제 SQL> DEL
I[NPUT]	현재 line 뒤에 하나이상의 줄을 삽입한다. 다 입력한 후 [Return]을 더 치면 나간다. SQL> I ORDER BY last_name       [Return]
n text	n번째 줄을 text로 완전히 교체한다. SQL> 2 FROM S_EMP

   · SQL*PLUS 명령어는 대소문자를 구분하지 않는다.

   · List 에는 ; 을 넣지 않는다.

   · * 는 Current line 번호 뒤에 나타난다.

 

     Example :

SQL> SELECT name   2   FROM s_region;   SQL> L 1   1 * SELECT name   SQL> A “Region”   1 * SELECT name “Region”   SQL> L 1   1 * SELECT name “Region”   SQL> C/name/id   1 * SELECT id “Region”   SQL> L 1          SELECT id “Region” 2 *  FROM s_region   SQL> I 3   WHERE name = ‘Asia’ 4   [Return]

 

SQL*PLUS Commands to Execute SQL

명  령	설     명
/	SQL buffer의 문장을 실행시킨다.
RUN	SQL buffer 의 문장을 실행시키고 화면 출력한다.

 

     Example :

SQL> RUN   1   SELECT id “Region” 2         FROM s_region 3 * WHERE name = ‘Asia’

 

Region - - - - - - - - - -              4

 

SQL> /

 

Region - - - - - - - - - -              4

 

SQL*PLUS Commands to Save SQL commands

버퍼에 있는 SQL 명령을 파일에 저장한다.

Syntax

SQL> SAVE filename [REPLACE]

   · REPLACE      : 존재하는 파일의 내용을 저장하고 ,존재하지 않으면 파일을 생성시킨다.

   · 파일명은 *.sql 이 된다.

 

SQL*PLUS Commands to Retrieve a Command File

파일의 내용을 SQL 버퍼에 가져온다.

Syntax

SQL> GET filename

 

SQL*PLUS Commands to Execute a File

저장된 파일을 실행시킨다.

Syntax

SQL> START filename

   · SQL 문장의 내용과 실행결과를 출력한다.

 

5. BUILD REPORTS WITH SQL*PLUS

REPORT를 만들기 위한 SQL*PLUS Commands

종  류	설      명
Environment Commands	SQL 문장의 일반적 행동과 효과에 영향을 미치는 명령어들
Format Commands	Report 표현 양식을 제어하는 명령어들
File Commands	Text 파일로 정보를 처리하는 명령어들

 

Control The Session Environment

Syntax

SET system_variable  value

 

SQL*PLUS System Variables

System Variable 설정	설       명
FEEDBACK n	Query에 의해 얻어질 수 있는 레코드 중 출력할 수 있는 레코드 수를 결정한다. ( default : 6 )
FEEDBACK ON | OFF	FEEDBACK 상태를 결정 ( default : ON )
PAUSE message	화면출력을 잠시 멈추고 message를 출력한다. [Return]을 치면 다시 작동한다.
PAUSE ON | OFF	PAUSE 상태를 결정 ( default : ON )
HEADING ON | OFF	Report에 Heading을 넣을 것인지 결정한다. ( default : ON )
PAGESIZE n	Report에 대한 페이지당 줄 수를 결정한다. ( default : 14 )
LINESIZE n	줄 당 글자의 수를 결정한다. ( default : 80 )
SPACE n	출력할 때 줄 당 간격을 결정한다. 최대값은 10 ( default : 1 )
ECHO ON | OFF	각 명령어가 Report에 표현되는지를 결정 ( default : OFF )

· SHOW 명령어를 이용해서 시스템 변수의 상태를 볼 수 있다.

 

Display Headers and Footers

SQL*PLUS 명령 중 TTITLE, BTITLE로 리포트의 위와 아래에 Header(머리글) 와

Footer(바닥글)를 만든다.

Syntax

TTITLE  [message]

   · BTITLE 도 동일하다.

   · TTITLE, TTITLE OFF 등으로 상태를 결정한다.

 

     Example :

SQL> TTITLE  ‘Salary | Report’

   · |   : 줄 구분

 

Control The Display of Columns

SQL*PLUS 명령 중 COLUMN명령으로 리포트의 각 열의 속성을 제어한다.

Syntax

COLUMN column HEADING heading FORMAT format_mask

   · column : 열의 이름

   · heading : 리포트의 열의 머리글

   ·format_mask : 데이터를 표현하는 방법을 나열한다.

Format mask

요  소	설     명
9	한자리 0을 포함한 정수
,	콤마의 위치 표현
.	소수점의 위치 표현
$	Dollar 표시
An	n 자리의 문자 표현

 

Column Setting

명   령	설     명
COLUMN column	지정된 열의 상태 표시
COLUMN	모든 열의 상태 표시
COLUMN column CLEAR	지정된 열의 상태를 지운다.
CLEAR COLUMNS	모든 열의 상태를 지운다.

 

     Example :

SQL> COLUMN title HEADING ‘Last’ FORMAT A22 SQL> COLUMN salary HEADING ‘Salary’ FORMAT $99,999.99

 

Build a Break Report

Syntax

BREAK ON column [ON column] | SKIP n | PAGE

   · column  : 지정된 열에서 중복된 출력을 억제한다.

   · SKIP n  : 주어진 열의 변수 값이 변할 때  각 line을 건너뛴다.

   · PAGE   :  break가 발생하면 다음 페이지로 지나간다.

   · SELECT 문자의 ORDER BY 절에 의해 break를 제어한다.

 

     Example :

SQL> BREAK ON title SKIP 1 SQL> SELECT title, last_name, salary   2  FROM s_emp   3  WHERE title NOT LIKE ‘VP%’ AND title <> ‘Warehouse Manager’   4  ORDER BY title, last_name, salary

   · title 값의 중복을 없애고 값이 변할 때 마다 한 줄씩 건너 뛴다.

 

Break Setting

명   령	설   명
BREAK	Break 상태를 나타낸다.
CLEAR BREAKS	현재 Break setting을 없앤다.

 

Add Summary Computations

Syntax

COMPUTE function OF compute_column ON break_column

   · compute_column : 계산에 사용될 열 또는 표현식

   · break_column   :  break로 사용될 열

 

Compute Function

함   수	설      명
COUNT	NULL 값을 제외한 레코드 수 계산
NUM	ROW의 수 계산
MAX	최대값
MIN	최소값
SUM	NULL값을 제외한 합계
AVG	NULL값을 제외한 평균 계산
STD	NULL값을 제외한 표준편차
VAR	NULL값을 제외한 분산

 

Compute Setting

명    령	설     명
COMPUTE	현재 compute 상태를 나타낸다.
CLEAR COMPUTE	현재 compute setting을 없앤다.

 

Syntax

BREAK ON column | REPORT SKIP n | PAGE COMPUTE function OF compute_column ON break_column … REPORT

   · REPORT  : 전체 합을 출력하기위해 BREAK와 COMPUTE양쪽에 사용된다.

 

Save Commands and Output

Operating System 텍스트파일 안에 출력뿐 아니라 Reports를 만들어내는 명령들을 저장한다.

Syntax

SPOOL filename

   · filename에 확장자가 없으면 .lst 또는 .lis 가 된다.

 

Handle Spooling

명    령	설    명
SPOOL	현재 Spool 상태를 나타낸다.
SPOOL OFF	Spool 파일을 닫는다.

 

     Example :

SQL> SET PAGESIZE 37 SQL> SET LINESIZE 60 SQL> SET FEEDBACK OFF SQL> TTITLE ‘Employee | Report’ SQL> BTITLE ‘Confidential’ SQL> COLUMN title HEADING ‘Job | Category’ FORMAT A22 SQL> COLUMN last_name HEADING ‘Employee’ FORMAT A22 SQL> COLUMN salary HEADING ‘Salary’ FORMAT $99,999.99 SQL> BREAK ON title SKIP 1 ON REPORT SQL> COMPUTE SUM OF SALARY ON TITLE REPORT SQL> SPOOL salary SQL> SELECT title, last_name, salary   2   FROM s_emp   3   WHERE title NOT LIKE ‘VP%’ AND title <> ‘Warehouse Manager’   4   ORDER BY title, last_name, salary;

 

출력…..

 

Save Multiple SQL and SQL*PLUS Commands

SQL*PLUS EDIT 명령으로 한 개 이상의 SQL 문장과 SQL*PLUS 명령을 편집한 후 STRART 명령으로 실행시킬 수 있다.

Syntax

EDIT filename[.ext]

 

6. MANIPULATE DATA

INSERT DATA

 새로운 레코드를 테이블 안에다 추가한다.

Syntax

INSERT INTO  table [ (column[, column…] ) ] VALUES (value [, value…])

 

     Example1 :

SQL> INSERT INTO s_dept   2   VALUES (11, ‘Finance’, 2); 1 row created.

 

     Example2 :

SQL> INSERT INTO s_emp (id, last_name, first_name, userid, start_date)   2   VALUES (26, ‘Hering’, ‘Elizabeth’, ‘ehering’, SYSDATE); 1 row created.

   · SYSDATE  : 시스템의 현재의 날짜 시간

 

Insert NULL

명시적 또는 암시적 방법으로 새로운 레코드에 NULL값을 넣을 수 있다.

명시적 방법

   · NULL keyword 이용해서 값을 입력한다.

   · String 이나 날짜 에 대해 empty string을 이력한다.

 

     Example1 :

SQL> INSERT INTO s_customer   2   VALUES (216, ‘Sports on Wheels’, NULL, NULL, NULL);  1 row created.

    

     Example2 :

SQL> INSERT INTO s_customer   2   VALUES (216, ‘Sports on Wheels’, ‘’,’’,’’);  1 row created.

   · String 과 Date에만 가능하다.

 

암시적 방법

   ·입력 list에서 생략한다.

     Example :

SQL> INSERT INTO s_customer (id, name, credit_rating )   2   VALUES (216, ‘Sports on Wheels’, ‘EXCELLENT’);  1 row created.

   · 입력하지 않은 column은 NULL로 들어간다.

 

Insert USER NAME

사용자 id을 USER 함수를 이용해서 입력할 수 있다.

SQL> INSERT INTO s_emp (id, first_name, last_name,   2   userid, start_date, salary)   3   VALUES (27, ‘’, ‘Student’, USER, SYSDATE, NULL);

 

Generate Primary Key Values

Sequence Expression으로 Primary key의 새로운 값을 자동으로 생성시킨다.

Sequence는 Oracle Server 자료구조와 독립적이다.

Sequence Expression

표   현	설   명
Sequence.NEXTVAL	sequence에서 사용한 다음 값
Sequence.CURRVAL	Sequence에서 가장 최근 사용된 값을 참조

 

     Example :

SQL> INSERT INTO s_dept (id, name, region_id)   2  VALUES (s_dept_id.NEXTVAL, ‘Education’,1); 1 row created.

   · id의 다음 값을 자동으로 넣어준다.

 

UPDATE DATA

기존의 레코드를 갱신한다.

Syntax

UPDATE table SET column = value [, column = value….] WHERE condition

 

     Example :

SQL> UPDATE s_emp   2   SET manager_id = 1   3   WHERE dept_id = 41;

   · WHERE 절을 생략하면 테이블의 모든 row를 갱신한다.

 

DELETE DATA

기존의 레코드를 삭제한다.

Syntax

DELETE FROM table WHERE condition

 

     Example :

SQL> DELETE FROM s_emp   2   WHERE dept_id = 41;

   · WHERE 절을 생략하면 테이블의 모든 row를 삭제한다.

     (테이블을 삭제하는 것이 아님)

 

CONTROL TRANSACTION

 

Control Transaction Logic

명   령	설     명
COMMIT	모든 변환되지 않은 작업을 영구적으로 DB에 반영하고 작업을 마친다.
ROLLBACK	모든 변환되지 않은 작업을 반영하지 않고 작업을 마친다.

 

COMMIT 이나 ROLLBACK 전의 데이터의 상태

   · 데이터를 조작하는 연산자들은 작업을 데이터베이스 buffer에 우선 반영하고 나중에

     이전 상태로 회복시킬 수 있다.

   · 사용자는 SELECT구문으로 연산 결과를 미리 볼 수 있다.

   · 다른 사용자는 연산결과가  데이터베이스에 반영되기 전에는 연산결과를 볼 수 없다.

   · 사용중인 레코드는 locked 된 상태가 되고 다른 사용자는 그 데이터를 변경

     시킬 수 없다. 

 

COMMIT 후의 데이터의 상태

   · 데이터의 변환이 데이터베이스에 반영되고 이전상태는 회복 되지 않는다..

   · 모든 사용자가 작업의 결과를 볼 수 있다.

   · lock은 풀리고 다른 사용자는 데이터를 변환 시킬 수 있다.

 

ROLLBACK 후의 데이터의 상태

   · 데이터의 변환이 취소 되고 데이터를 이전 상태로 만든다.

   · lock은 풀리고 다른 사용자는 데이터를 변환 시킬 수 있다.

 

Alter Transaction Logic

명   령	설   명
SAVEPOINT	하고있는 작업의 저장시점을 Marking
ROLLBACK TO SAVEPOINT	Marking된 후의 작업은 무시한다.

 

     Example :

SQL> INSERT INTO s_dept (id, name, region_id)   2   VALUES (s_dept_id.NEXTVAL, ‘Education’,3); 1 row createc.   SQL> SAVEPOINT a; Savepoint created/   SQL> INSERT INTO s_dept (id, name, region_id)   2   VALUES (s_dept_id.NEXTVAL, ‘Education’,4); 1 row createc.   SQL> SAVEPOINT b; Savepoint created.   SQL> INSERT INTO s_dept (id, name, region_id)   2   VALUES (s_dept_id.NEXTVAL, ‘Education’,5); 1 row createc.   // 이때 데이터베이스 buffer에 3개의 레코드가 삽입되어 있다.   SQL> ROLLBACK TO SAVEPOINT a; Rollback complete.   SQL> COMMIT; Commit complete.   // 이때 처음의 1개 레코드만 데이터베이스에 삽입된다.

 

암시적Transaction Processing

상      황	결   과
DDL 명령	자동 commit
Commit 이나 Rollback 없이 SQL*PLUS 를 나감	자동 commit
시스템 고장에 의한 종료	자동 rollback

 

PRODUCE SCRIPTS TO MANIPULATE DATA

많은 양의 데이터를 Load 할 때 batch 파일을 생산한 후 START로 실행시킨다.

     Example :

SET ECHO OFF INSERT INTO s_region (id, name) VALUES (1, ‘North America’) /   INSERT INTO s_region (id, name) VALUES (2, ‘South America’) /      …   COMMIT /

   · SET ECHO OFF 는 명령이 실행 되는 동안 명령어를 출력하지 않게 한다.

   · batch 파일 위에 SET FEEDBACK OFF를 추가하면 feedback 메시지를 출력하지 않는다.

 

변수를 사용해서 테이블에 데이터 추가하기

Syntax

ACCEPT substitution_parameter PROMPT message

   · substitution_parameter : 입력받을 변수 명

   · message            : 사용자가 입력할 line에 나타나는 메시지

 

     Example :

SET ECHO OFF ACCEPT region_id PROMPT ‘Please give me the region ID: ’ ACCEPT region_name PROMPT ‘Please give me the region name: ’   INSERT INTO s_region (id, name) VALUES (&region_id, ‘&region_name’) /

 

SQL> START add_region2 Please give me the region ID: 7 Please give me the region ID: Texas   1 row created.

   · 7은 region_id에 Texas는 region_name에 들어간다.

 

7.CREATE TABLES AND DATA STRUCTURE

#Create Tables

   · SQL의 CREATE TABLE 명령을 이용해 무결성 제약을 포함한 데이터베이스

     테이블을 생성한다.

   · SQL*PLUS 의 DESCRIBE명령을 이용해 테이블 구조를 미리 본다.

   · SQL의 ALTER TABLE 명령을 이용해 테이블의 내용을 변경하거나 추가한다.

   · 데이터 사전의 USER_CONSTRANITS와 USER_CONS_COLUMNS의 무결성

     제약을 확인한다.

   · SQL의 CREATE TABLE이나ALTER TABLE을 이용해 무결성 제약을 추가하거나

     삭제한다.

   · SQL의 DROP TABLE명령을 이용해 데이터베이스에서 테이블을 삭제한다.

 

#Create Logical Views

   · SQL의 CREATE VIEW 명령을 이용해 논리적 VIEW 테이블을 생성한다.

   · Column Aliases를 사용해서 VIEW의 Column Heading을 제어한다.

   · WITH CHECK를 사용해서 INSERT나 UPDATE 의 사용이 VIEW에 영향을

     미치지 않도록 보장한다.

   · 데이터 사전의 USER_VIEWS 을 사용해서 존재하는 VIEW 테이블들을 확인한다.

   · SQL의 DROP VIEW명령을 이용해 VIEW테이블을 삭제한다.

 

#Generate Primary Key Values

   · SQL 의 CREATE SEQUENCE 명령을 이용해 PRIMARY KEY 값을 자동 생성한다.

   · sequence_name.NEXTVAL과 sequence.CURRVAL을 사용해 INSERT시 다음 생성될

     값을 참조한다.

   · 데이터 사전의 USER_SEQUENCES를 참조하여 존재하는 sequences을 확인한다.

   · SQL의 DROP SEQUENCE명령을 이용해 데이터베이스에서 sequences을 삭제한다.

 

#Improve Query Performance

   · SQL 의 CREATE INDEX명령으로 인덱스를 만든다.

   · 데이터 사전의 USER_INDEXES로부터 인덱스를 확인한다.

   · SQL의 DROP INDEX명령을 이용해 인덱스를 삭제한다.

 

Data Structure

구   조	목   적
Table	데이터를 저장
View	하나 이상의 테이블로부터 얻은 논리적 집합
Sequence	Primary key값을 자동 생성
Index	Query의 성능을 향상시킴

 

Create Tables to Store Data

Syntax

CREATE TABLE [user.] table_name        ([ column_name datatype | table_constraint]        [, {column_name datatype | table_constraint}] . . . )

   · table_name     : 테이블의 이름

   · column_name : 속성(열)의 이름

   · datatype        : 속성(열)의 데이터 타입

   · table_constraint: 소성(열)의 제약조건

 

명명법

   · 테이블과 열의 이름은 첫 자는 알파벳으로 시작하고 문자길이는 30까지

   ·  A-Z,a-z,0-9만을 사용한다.

   · 예약어나 키워드는 사용할 수 없다.

   · 이미 생성된 테이블과 테이블 이름이 같아서는 안 된다.

 

Oracle Version 6.0 데이터 타입

데이터 타입	설    명
CHAR(size)	대,소문자와 특수문자(+,-,%,$,&등)로 구성되어지고 길이는 255까지 된다.
DATE	BC 1,4712년 1월부터 AD 31,4712 년 12월까지의 날짜와 시간
LONG	CHAR와 유사하지만 테이블 당 하나이상 정의 할 수 없고 LONG형을 가지고 JOIN을 할 수 없다. (64K)
NUMBER	0~9까지의 한 자리 숫자

 

Oracle 7 데이터 타입

데이터 타입	설     명
CHAR(s)	1~255까지의 고정길이 문자 값
VARCHAR2(s)	1~200까지의 가변길이 문자 값
VARCHAR(s)	VARCHAR2(s)와 같다.
DATE	BC 1,4712년 1월부터 AD 31,4712 년 12월까지의 날짜와 시간
LONG	2 gigabytes
NUMBER(p,s)	p의 크기만큼의 전체자리와 소수점 아래 s만큼의 숫자

 

데이터 무결성 제약 조건

제     약	설     명
NOT NULL	NULL값을 가질 수 없다.
UNIQUE	테이블 안의 모든 레코드 중 유일해야 한다.
PRIMARY KEY	테이블의 각 레코드를 유일하게 지시
REFERENCES	참조되는 테이블의 PRIMARY KEY과 이 레코드의 FOREIGN KEY 관계를 형성
CHECK	이후 조건이 항상 TRUE이여야 한다.

 

     Example : CREATE TABLE

SQL> CREATE TABLE s_emp   2   ( id                NUMBER (7),   3    last_name         VARCHAR(25)   4      CONSTRAINT s_emp_last_name_nn NOT NULL,   5    first_name         VARCHAR(25),   6    userid            VARCHAR2(8),   7    start_date         DATE,   8    comments         VARCHAR2(255),   9    manager_id        NUMBER(7),  10    title              VARCHAR2(25),  11    dept_id           NUMBER(7),  12    salary            NUMBER(11,2),  13    commission_pct    NUMBER(4,2),  14      CONSTRAINT s_emp_id_pk PRIMARY KEY (id),  15      CONSTRAINT s_emp_userid_uk UNIQUE (userid),  16      CONSTRAINT s_emp_commission_pct_ck  17             CHECK ( commission_pct IN (10, 12.5, 15, 17.5,  18             20 ) ) );

   · 테이블 생성 후 테이블 구조 확인

      SQL> DESCRIBE s_emp

 

테이블에 열(column) 추가

Syntax

ALTER TABLE table_name ADD (column_name datatype  [, column_name datatype] . . . )

   · table_name  : 존재하는 테이블

   · column_name : 추가 하려는 column이름

   · datatype     : 추가 하려는 column의 데이터 타입

 

     Example :

SQL> ALTER TABLE s_region   2   ADD ( comments VARCHAR2(255));

 

열(column)의 정의 변경

Syntax

ALTER TABLE table_name MODIFY (column_name datatype  [, column_name datatype] . . . )

 

     Example :

SQL> ALTER TABLE s_emp   2   MODIFY ( last_name VARCHAR2(30));

   · 데이터가 존재하면 크기를 줄일 수 없다.

 

데이터에 대한 제약의 추가 및 삭제

     Example1 : 제약 추가 (ADD CONSTRAINT)

SQL> ALTER TABLE s_emp   2   ADD CONSTRAINT s_emp_manager_id_fk   3   FOREIGN KEY (manager_id) REFERENCES s_emp (id);

 

     Example2 : 제약 삭제 (DROP CONSTRAINT)

SQL> ALTER TABLE s_emp   2   DROP CONSTRAINT s_emp_manager_id_fk ;

 

제약 조건들 확인

     Example1 : 테이블 제약 조건의 확인

SQL> SELECT *   2   FROM user_constraints   3   WHERE table_name = ‘S_EMP’;

   · 데이터 사전의 USER_CONSTRAINTS 테이블을 이용해서 S_EMP테이블의

     제약 조건들을 검색한다.

    

     Example2 : 열(column)제약 조건의 확인

SQL> SELECT constraint_name, column_name   2   FROM user_cons_columns   3   WHERE table_name = ‘S_EMP’;

   · 데이터 사전의 USER_CONS_COLUMNS 테이블을 이용해서 S_EMP테이블의

     각각의 열(column)들의 제약 조건들을 검색한다.

 

테이블 삭제

Syntax

DROP TABLE table_name

 

CREATE VIEWS

VIEW를 이용하면 보안성, 편리성 ,예측성이 좋다.

 

Syntax

CREATE VIEW view_name [(alias, [alias] . . . )]  AS query WITH CHECK OPTION [CONSTRAINT constraint]

   · alias  : Query에 의해 검색되는 각 column의 별칭이다.이것의 수는 column의

            수와 일치 해야 한다

   · WITH CHECK OPTION : 삽입과 변경이 이루어지지 않도록 한다.

   · constraint : CHECK OPTION 제약

 

     Example1 :

SQL> CREATE VIEW empvu41 (id_number, employee, job)   2   AS SELECT id, last_name, title   3   FROM s_emp   4   WHERE dept_id = 41; View created.   SQL> SELECT *   2   FROM empvu41;

   · VIEW는 실제 테이블을 생성되는 것이 아니다 SELECT문에 기술된 정보들이

    데이터 사전에 들어가 있다.

 

     Example2 : CHECK OPTION 사용

SQL> CREATE VIEW empvu41   2   AS SELECT *   3   FROM s_emp   4   WHERE dept_id = 20   5   WITH CHECK OPTION; View created.   SQL> UPDATE empvu20   2   SET dept_id = 42   3   WHERE id = 16;   0  rows updated.

   · 위의 내용은 바뀌지 않는다. 위의 view는 오직 dept_id가 20인 레코드만

     볼 수 있기 때문에 만약 바뀐다면 테이블 을 잃게 된다.

 

VIEW의 이름과 구조 확인

     Example :

SQL> SELECT *   2   FROM user_views;

   · 데이터 사전의 USER_VIEWS테이블을 이용해서 VIEW의 이름과 구조를

     확인한다.

   

VIEW 삭제

Syntax

DROP VIEW view_name

 

Generate PRIMARY KEY values

CREATE SEQUENCE명령을 이용해서 PRIMARY KEY값을 자동 생성시킨다.

Syntax

CREATE SEQUENCE sequence_name   [INCREMENT by n]   [START WITH n]   [ {MAXVALUE n | NOMAXVALUE} ]   [ {CYCLE | NOCYCLE} ]   [ {CACHE n | NOCACHE} ]   [ {ORDER | NOORDER}]

   · INCREMENT by n : 다음 생성될 증가치 [정의 되지 않으면 1씩 증가]

   · START WITH n  : 최초 시작될 번호 [정의 되지 않으면 1부터]

   · MAXVALUE n  : 증가 될 수 있는 최대치

   · NOMAXVALUE : 최대치는 10**27이 된다.

   · CYCLE        : 최대나 최소값이 되면 다시 시작

   · CACHE n       : Oracle Server가 관리하는 sequence의 수 [기본 20개]

   · ORDER        : 요구순서로 생성되는 것을 보장

 

     Example : CREATE SEQUENCE

SQL> CREATE SEQUENCE s_dept_id   2   MINVALUE 1   3   MAXVALUE 9999999   4   INCREMENT BY 1   5   START WITH 51   6   NOCACHE   7   NOORDER   8   NOCYCLE;   Sequence created.

 

SEQUENCE 확인

     Example :

SQL> SELECT sequence_name, min_value, max_value,   2     increment_by, last_number   3   FROM user_sequences;

   · 데이터 사전의 USER_SEQUENCES테이블을 이용해서 sequences을 확인한다.

    

 

Primary Key값 참조

표   현	설    명
Sequence_name.NEXTVAL	자동으로 다음의 연속 값을 return
Sequence_name.CURRVAL	최근 사용된 sequence값 return

 

     Example :

SQL> INSERT INTO s_dept   2   VALUES (s_dept_id.NEXTVAL, ‘Finance’, 2); 1 row created.

   · s_dept_id의 최근 값이 50 이였다면 s_dept의 추가되는 id값으 51을 갖게 된다.

 

SEQUENCE 삭제

Syntax

DROP SEQUENCE sequence_name

 

Improve Query Performance

데이터베이스 테이블에 하나 이상의 인덱스를 만들어 query의 수행을 향상시킬 수 있다.

 

Indexes

   · 인덱스는 명시적으로 생성된 객체이다.

   · 인덱스는 독립적이다.

   · 인덱스는 자동적으로 사용되어지고 Oracle Server에 의해 관리된다.

   · 인덱스의 entry는 테이블의 레코드에 직접 연결 되어 있다.

   · 인덱스를 사용함으로써 디스크 I/O를 줄인다.

 

Oracle Server의 데이터 ACCESS 방법

Access 방법	설   명
By ROWID	데이터 의 정확인 위치를 가리키고 직접 제어한다. 가장 빠른 방법이다.
Full-table scan	순차적으로 전체 데이터를 검색한다.
By index	열들의 값을 정렬된 Tree구조로 사용해서 이진 검색을 한다.

 

     Example1 : By ROWID

SQL> SELECT last_name   2   FROM s_emp   3   WHERE rowid = ‘0000100A.0010.0003’;

   · 0000100A : 데이터 블록 위치

   · 0010     : 레코드 위치

   · 0003     : 파일의 위치

 

     Example1 : Full or Index

SQL> SELECT last_name   2   FROM s_emp   3   WHERE last_name = ‘Smith’;

   · 만약 인덱스가 있으면 인덱스로 검색되고 없으면 Full Scan 한다.

 

인덱스 생성의 Trade-Off

#인덱스를  생성해야 할 경우

   · WHERE절이나 JOIN조건에 의해 자주 사용될 때

   · Column안의 모든 값들이 유일한 값을 가지고 있을 때

   · Column 이 광범위한 값을 포함할 때

   · NULL값을 많이 가지고 있을 때

   · 테이블이 크고 대부분의 Query가 10~15%만을 검색할 때

 

#인덱스를  생성하지 말아야 할 경우

   · 테이블이 작을 때

   · 대부분의 Query 가 10~15%이상을 검색할 때

   · 테이블이 자주 바뀔 때

 

CREATE INDEXS

Syntax

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name [, column_name ])

   · UNIQUE  : 같은 데이터가 중복되게 들어 가지 못한다.

   · 만약 만들어 지는 인덱스가 PRIMARY KEY 나 UNIQUE의 제약을 가지고

     있다면 UNIQUE를 명시 하지 않아도 자동으로 중복을 못하게 한다.

 

     Example :

SQL> CREATE INDEX s_emp_last_name_I   2   ON s_emp ( last_name ) ; Index created.

 

INDEXS 확인

     Example :

SQL> SELECT index_name, uniqueness   2   FROM user_indexes   3   WHERE table_name = ‘S_EMP’;

   · 데이터 사전의 USER_INDEXES테이블을 이용해서 index을 확인한다.

 

INDEX 삭제

Syntax

DROP INDEX index_name