[ SQL ] 데이터베이스 #3 SQL

SQL(Structured Query Language)

 

 

1. SQL 

• SQL 은 데이터베이스에서 데이터의 검색, 데이터의 삽입, 데이터의 수정, 데이터의 삭제를 하기 위한 데이터베이스 질의 언어로 관계 대수나 관계 해석을 기초로 한 고급 데이터 언어이다.
• ANSI (American National Standards Institute) 와 ISO (International Standards Organization) 에서 관계형데이터베이스의 표준언어로 채택되어 거의 모든 상용 관계 DBMS 에서 지원되고 있다.
• SQL 은 구조화 질의이 뿐만 아니라 데이터의 정의, 데이터의 조작, 데이터의 제어 기능 등을 수행하고 관계형 DB 의 서브 언어로 데이터 정의어, 데이터 질의어, 데이터 조작어를 포함한다.
• SQL 은 데이터 처리 작업의 단순화, 데이터 질의, 보고서 작성, 데이터 조작 기능을 제공한다.
• SQL 을 잘 활용하면 데이터베이스의 데이터 관리를 최대한 효율적으로 이용할 수 있다.
• 데이터베이스 프로그래밍에서 중심적으로 다루는 SQL 은 데이터 질의어와 데이터 조작어이다.

 - SQL 로 접근 방법: 
1) 단말기에서 대화식 처리: SQL 의 명령어로 검색, 삽입, 수정, 삭제 등의 작업 처리를 한다.
2) 응용 프로그램 이용 처리: 호스트 언어에서 SQL 언어를 삽입하여 사용한다.

 

 

2. SQL의 필요한 구성

 

(1) 데이터 베이스

• 나의 조직 안에서 다수의 사용자가 공동으로 사용하기 위하여 통합되고 저장된 운영 자료의 집합을 말하며 일반적으로 DB라고 불린다.
• 원칙적으로 같은 내용의 자료가 중복되어 있지 않은 통합자료를 관리한다.

(2) 테이블

• 실제 데이터를 저장하는곳
• 기본테이블 : 정의된 테이블로 독자적인 사용이 가능하다
• 임시테이블 : 처리 과정에서 중간 결과로 만들어진 테이블이다
• 가상 테이블 : 어떤 테이블로 유도된 테이블로 뷰(view)라고 한다.

(3) 도메인

• 나의 조직 안에서 다수의 사용자가 공동으로 사용하기 위하여 통합되고 저장된 운영 자료의 집합을 말하며 일반적으로 DB라고 불린다.
• 원칙적으로 같은 내용의 자료가 중복되어 있지 않은 통합자료를 관리한다.

 

 

 

 

3. SQL 문

(1) SQL 문 

• SQL 문은 관계형 데이터베이스에서 데이터베이스나 테이블을 생성하거나 데이터의 검색, 수정, 삭제, 입력 등을 하기 위해서 사용하는 언어를 의미한다.

 

(2) SQL 문 종류

• DDL (Data Definition Language) : 데이터 정의어

 - create 문은 데이터베이스나 테이블을 생성한다.

 - alter 문은 데이터베이스나 테이블을 수정한다.

 - drop 문은 데이터베이스나 테이블을 삭제한다.

 - rename 문은 데이터베이스명이나 테이블명을 변경한다.

 - truncate 문은 데이터베이스나 테이블의 저장 공간을 삭제한다.

 

• DQL (Data Query Language) : 데이터 질의어

 - select 문은 데이터를 검색

 

• DML (Data Manipulation Language) : 데이터 조작어

 -  insert 문은 데이터를 입력한다.
 -  update 문은 데이터를 수정한다.
 - delete 문은 데이터를 삭제한다.

 

• TCL (Transaction Control Language) : 데이터 처리어를 의미하며 데이터의 작업 처리인 트랜잭션에 적용

 -  commit 명령어는 트랜잭션을 저장한다.
 -  rollback 명령어는 트랜잭션을 취소한다.
 -  savepoint 명령어는 트랜잭션 내의 책갈피 기능을 한다.

 

• DCL (Data Control Language) : 데이터 제어어

 -  grant 명령어는 데이터베이스의 권한을 부여한다.

 -  revoke 명령어는 데이터베이스의 권한을 취소한다.

 

 

 

< 데이터베이스 프로그래밍의 방법 >

 

1) SQL 전용 언어를 사용하는 방법
SQL 자체의 기능을 확장하여 변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법이다.
Oracle 은 PL/SQL 이라는 언어를 사용한다.

2) 프로그래밍 언어에 SQL 을 삽입하여 사용하는 방법
호스트 언어가 자바 등 프로그래밍 언어인 경우로 프로그래밍 언어로 작성된 응용
프로그램에서 데이터베이스에 저장된 데이터를 관리하고 조회한다.
삽입된 SQL 문은 RDBMS 의 컴파일러가 처리한다.

 

3) 웹 프로그래밍 언어에 SQL 을 삽입하여 사용하는 방법
호스트 언어가 JSP, ASP, PHP 등 웹 스크립트 언어인 경우다.

 

4) 4GL(4th Generation Language) 사용하는 방법
데이터베이스 관리 기능과 비주얼 프로그래밍 기능을 갖춘 GUI 기반 소프트웨어 개발 도구를 사용하여 프로그래밍하는 방법이다.
Delphi, Power Builder, Visual Basic 등이 있다.

 

 

 

 

 

오라클 데이터베이스

- 기능적 제한
* 서버당 하나의 인스턴스만 설치할 수 있다.
* CPU 가 여러 개가 있는 서버에 설치하더라도 하나의 CPU 만 사용하게 되어있다.
* 사용자 데이터가 최대 11GB 까지만 저장할 수 있다.
* 메모리는 최대 1GB 까지만 사용할 수 있다.

scott 계정은 Oracle Standard Edition 11g 에서는 제공하지만, Oracle Express Edition 11g 에서는 제공되지 않는다.
Oracle Express Edition 11g 에서 scott 계정을 사용하려면 수동으로 설치해야 한다.

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin 폴더에서 scott.sql 파일을 설치한다. 경로확인★

 

SQL Developer 에디터에서 최고 관리자인 sys 계정으로 접속하여 scott.sql 파일을 설치한다.

 

▶ scott 계정의 실습 테이블

 

 

 

- SQL Developer

오라클 11g 의 통합 개발 환경은 Toad, SQLGate, SQL Developer 등이 있으며 오라클에서 무료로 배포하는 SQL Developer 를 사용

 

(1) 경로추가

압축을 풀고 폴더 내의 sqldeveloper.exe( ) 파일을 관리자 권한으로 실행한다.

JDK 미포함 된 설치파일을 실행시켰음으로 JDK 설치한 곳의 경로를 입력한다.

 

 

(2) 사용자 접속
녹색의 십자가 표시 클릭

(3)  sys 계정 등록
sys 계정으로 접속하기 위해서는 접속에 필요한 내용을 입력하여 등록한다.
접속 유형에서 롤(L)을 SYSDBA 로 설정해야 한다.

(4) scott 계정 등록
scott 계정으로 접속하기 위해서는 접속에 필요한 내용을 입력하여 등록한다.
접속 유형에서 롤(L)을 기본값으로 설정해야 한다

 

 

 

< 오라클의 데이터 타입 > 

테이블 컬럼을 속성을 정의한다.

프로시저와 함수의 인자에 사용되는 값이 저장되는 방식을 결정한다.

 

- 오라클의 데이터 타입은 내장 데이터 타입과 사용자 정의 데이터 타입으로 구분

 

 

• [ 내장 데이터 타입 ]

1. Character 데이터 타입

오라클10g 버전 이하에서는 영문 이외의 문자는 2바이트로 인식,

오라클11g 버전 이상에서는 영문 이외의 문자는 3바이트로 인식

 

(1) char 데이터 타입

고정 길이 문자 데이터를 저장한다.

문자 데이터를 정의할 때 지정된 길이만큼의 바이트로 저장한다.

char 데이터 타입으로 문자 데이터를 저장할 때 공백 문자는 스페이스로 추가되어 저장된다.

문자데이터를 문자개수로 저장할 때는 char데이터 타입이 아닌 nchar데이터 타입으로 사용 할 수 있다.

 

공백문자를 스페이스로 대체하는 것은 정확한 비교를 하는데 방해가  될 수 있다.

char 데이터 타입은 문자 개수와 상관없이 항상 지정된 바이트 수만큼 공간이 할당된다.

최대 2000바이트 까지 저장 가능하며 자릿수를 지정하지 않으면 기본 길이는 1바이트이다.

공백 문자가 없는 고정 길이 문자 데이터일 경우에만 사용을 권장한다.

 

 

(2) varchar2 데이터 타입

가변 길이 문자 데이터를 저장한다.

문자 데이터를 정의할 때 지정된 길이만큼의 바이트로 저장한다.

char 데이터 타입으로 문자 데이터를 저장할 때 공백 문자는 스페이스로 추가되지 않고 실제 데이터만 저장된다.

문자 데이터를 문자 개수로 저장할 때는 varchar2 데이터 타입이 아닌 nvarchar2 데이터 타입으로 사용할 수 있다.

 

문자 개수와 상관없이 항상 지정된 바이트 수만큼 공간이 할당된다.

최대 4000 바이트까지 저장 가능하며 자릿수는 반드시 지정해야 한다.

입력되는 문자개수만큼만 공간이 할당되며 데이터 값이 유동적일 때 사용을 권장한다

 

 

(3) clob 데이터 타입

변동 길이 문자 데이터를 최대 4 기가까지 저장한다.

문자 데이터를 정의할 때 지정된 길이만큼의 바이트로 저장한다.

문자 데이터를 문자 개수로 저장할 때는 clob 데이터 타입이 아닌 nclob 데이터 타입으로 사용할 수 있다.

기본적으로 insert 문에서는 수행한다.

select 문이나 update 문에서 수행하기 위해서는 별도의 PL/SQL 패키지이나 API 를 사용해야 한다.

 

(4) long 데이터 타입

clob 데이터 타입 이전에 사용되던 변동 길이 문자 데이터로 최대 2 기가까지 저장한다.

 

 

 

 

2. Number데이터 타입

number([전체자릿수], [소수점자릿수])

수치 데이터를 저장하기 위한 데이터 타입이다.

변동 길이의 숫자 데이터를 저장하는 데 사용된다.

정수값과 실수값을 저장할 때 최대 21 바이트까지 사용할 수 있다.

지정된 자릿수만큼 공간이 할당된다.

정수값을 저장하기 위해서는 소수점자릿수를 생략하면 된다.

전체자릿수와 소수점자릿수 모두 생략하면 입력한 데이터 값만큼 공간이 할당된다.

 

 

 

3. Date데이터 타입

 

(1) date데이터 타입

날짜와 시간을 고정 길이로 저장하기 위한 데이터 타입이다.

데이터 길이는 고정된 7 바이트이다.

세기, 년, 월, 일, 시간, 분, 초 등을 표기하는 데 사용한다.

 

(2) timestamp데이터 타입

date 데이터 타입의 확장된 모델로 정밀도가 높다.

데이터 길이는 고정된 11 바이트이다

첫 7 바이트는 date 데이터 타입과 같고 마지막 4 바이트는 초 이하 단위로 저장한다.

세기, 년, 월, 일, 시간, 분, 초 등을 표기하는 데 사용한다.

초 이하 단위는 9 자리까지 저장할 수 있다.

특정 시점을 나타내는 데 사용되며 타임 존과 관련된 정보를 어떤 방식으로 저장하는지에 따라 달라진다.

 

(3) interval데이터 타입

은 timestamp 데이터 타입 간의 차이를 나타내는데 사용되는 데이터 타입이다.

interval 데이터 타입은 년과 월의 필드의 차이를 나타내는데 두 timestamp 데이터 타입 간의 기간을 년, 개월, 일, 시간, 분, 초로 나타낸다.

 

 

 

 

 

4. Binary데이터 타입

 

(1) 이미지 데이터 타입

이미지 데이터를 저장하는 데 사용된다.

raw 데이터 타입과 long raw 데이터 타입 대신에 blob 데이터 타입과 bfile 데이터 타입을 사용하는 것을 권장한다.

 

 - blob 데이터 타입
최대 4 기가까지의 바이너리 데이터를 저장한다.
데이터 타입의 내부 저장 방식은 clob 데이터 타입과 유사하다.
저장된 데이터는 트랜잭션에 의해 변경할 수 있다.

 

 - bfile 데이터 타입
바이너리 데이터를 데이터베이스 외부인 운영체제상의 파일 시스템에 저장한다.
최대 30 바이트의 디렉터리 별칭과 최대 256 바이트의 파일 이름 표현이 가능하다.
저장된 데이터는 변경할 수 없다.
무결성, 보안, 지속성은 운영체제에서 보장되어야 한다.

 

 - raw 데이터 타입
raw 데이터 타입은 최대 2000 바이트까지 저장할 수 있다.

 

 - long raw 데이터 타입
long raw 데이터 타입은 최대 2 기가 바이트까지 저장할 수 있다.

 

 

 

 

 

(2) 컬럼데이터 타입

Binary 데이터 타입으로 테이블의 열에 대한 고유식별자이다.

특정 열의 물리적으로 저장하지는 않지만, 특정 행을 찾는 가장 빠른 구조를 제공한다.

 

 

 - rowid 컬럼

테이블의 행에 대한 고유식별자이며 명시적으로 컬럼값으로서 저장되지 않는다.

오라클에서 인덱스를 생성하기 위하여 내부적으로 사용하는 슈도(Pseudo) 컬럼이다.

데이터베이스에 보관되지 않으며 데이터베이스 자료도 아니다.

사용자가 임으로 변경하거나 삭제할 수 없다

테이블의 컬럼처럼 참조만 가능하며 데이터베이스에 값이 저장되지는 않는다.

행의 물리적 주소를 직접 부여하지는 않지만, 행 위치를 지정하는 데 사용한다.

사용하면 가장 빠르게 테이블의 행을 액세스할 수 있다.

주어진 키 값의 집합을 가진 행을 지정하기 위해 인덱스에 저장된다.

 

 

 

 - rownum 컬럼

슈도(Pseudo) 컬럼으로 rowid 컬럼과 거의 같은 기능을 가진다.

MySQL 데이터베이스의 limit 키워드와 같은 기능이다.

SQL 문의 결과에 차례대로 숫자 1 부터 순차적으로 번호를 부여하는 컬럼이다.

게시판의 페이징 처리할 때 사용하며 다음 문에서 사용한다.

update 문 사용 시 다중 건이 나올 때 첫 번째 건만 갱신할 때 where 문에서 사용한다.

delete 문 사용 시 다중 건이 나올 때 첫 번째 건만 삭제할 때 where 문에서 사용한다.

 

 

 

 

• [ 사용자 정의 데이터 타입 ]

1. Object데이터 타입

데이터 타입은 데이터들을 조작하기 위한 함수 혹은 프로시저를 묶어서 사용자가 정의한 새로운 데이터 타입이다.

사용자가 정의한 복합 데이터 타입이다

테이블이나 컬럼으로 사용할 수 있다.

내부적으로는 일반 테이블 형태로 변환되어 저장되며 내장 데이터 타입이 될 때까지 확장된다.

사용자가 정의할 때마다 생성되므로 정해진 데이터 타입 코드는 없다.

 

 - 3 가지 구성 요소

name: object type 에 대한 고유식별자

attribute: built-in 데이터타입 또는 다른 object 데이터타입

method: PL/SQL, C, Java 와 같은 언어로 작성된 모듈

 

 

2. Collection데이터 타입

(1) varray데이터 타입

순서를 갖는 요소의 1 차원 배열로 저장하는 데 사용된다.

컬럼 자체는 스칼라 타입의 컬럼처럼 단일값처럼 간주한다.

데이터조작어에 의해 조작할 수 있다.

각 요소에는 인덱스가 있으며 배열 상의 순서를 나타낸다.

요소들은 배열의 맨 끝부터 삭제할 수 있다.

 

(2) nested table 데이터 타입

순서를 지정하지 않은 레코드나 행의 집합이다.

테이블을 하나의 컬럼에 저장할 때 사용된다.

 

 

 

3. reference데이터 타입

다른 객체를 참조하는 reference 데이터 타입도 사용자 정의 데이터 타입에 해당한다.

reference 데이터 타입은 테이블에 저장된 객체에 대한 참조이며 객체 그 자체는 아니다.