Ch 7. 데이터베이스 언어 SQL

01 SQL의 소개

SQL은 관계형 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어입니다. 필요한 데이터가 무엇인지 제시하고 데이터를 어떻게 처리해야하는 지 언급할 필요가 없기에 비절차적 데이터 언어의 특징을 띕니다. SQL은 기능에 따라 데이터정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 나눕니다.

 

데이터 정의어: 테이블을 생성하고 변경, 삭제하는 기능을 제공합니다.

데이터 조작어: 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정, 삭제, 검색 하는 기능을 제공합니다.

데이터 제어어: 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 하는 언어입니다. 데이터베이스 관리자가 주로 사용합니다.

02 SQL를 이용한 데이터 정의

SQL의 데이터 정의 기능

SQL의 데이터 정의기능은 테이블 생성(CREATE), 생성된 테이블 구조의 변경(ALTER), 테이블 삭제(DROP)로 분류할 수 있습니다.

 

테이블의 생성

속성의 정의

테이블을 구성하는 각 속성의 데이터 타입을 선택해야 하며, 테이블을 구성하는 속성은 기본적으로 널 값이 허용됩니다. 따라서 널 값을 허용하지 않으려면 속성을 정의할 때 속성의 이름과 데이터 타입 다음에 NOT NULL 키워드를 포함해야 합니다. 

 

속성의 데이터 타입

데이터 타입	의미
INT, INTEGER	정수
SMALLINT	보다 작은 정수
CHAR(n), CHARACTER(n)	길이가 n인 고정 길이의 문자열
VARCHAR(n) 또는 CHARACTER VARTING(n)	최대 길이가 n인 가변 길이의 문자열
NUMERIC(p, s) 또는 DECIMAL(p,s)	고정 소수점 실수 (p는 소수점을 제회한 전체 숫자의 길이, s는 소수점 이하 숫자의 길이)
FLOAT(n)	부동 소수점 실수
DATE	연, 원, 일로 표현되는 날짜
TIME	시, 분, 초로 표현되는 시간
DATETIME	날짜와 시간

 

키의 정의

CREATE TABLE 문으로 테이블을 정의할 때는 기본키, 대체키, 외래키를 지정할 수 있습니다.

PRIMARY KEY ex 

PRIMARY KEY(고객아이디) 
PRIMARY KEY(주문고객, 주문제품)

UNIQUE ex

UNIQUE (고객이름)

 

FOREIGN KEY (외래키의 경우 어떤 테이블의 어떤 속성을 참조하는지 REFERENCES 키워드 다음 명시해야합니다. 그리고 참조되는 테이블에서 튜플을 삭제하거나 변경할 때 처리 방법을 선택해야 합니다.) 

ON DELETE NO ATION: 튜플을 삭제하지 못하게 한다.

ON DELETE CASCADE: 관련 튜플을 함께 삭제한다.
ON DELETE SET NULL: 관련 튜플의 와래키 값을 NULL로 변경한다.
ON DELETE SET DEFAULT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

ON UPDATE NO ACTION: 튜플을 변경하지 못하도록 한다.
ON UPDATE CASCADE: 관련 튜플에서 외래키 값을 함께 변경한다.
ON UPDATE SET NULL: 관련 튜플의 외래키 값을 NULL로 변경한다.
ON UPDATE SET DEFEAT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

ex

FOREIGN KEY(소속부서) REFERENCES 부서(부서번호) ON DELETE CASCADE ON UPDATE CASCADE

FOREIGN KEY(소속부서) REFERENCES 부서(부서번호)
# delete나 update 처리 방법이 자동으로 선택됩니다.

 

데이터 무결성 제약조건의 정의

CHECK 키워드를 통해 특정 속성에 대한 제약조건을 지정할 수 있습니다.

ex

CHECK(재고량 >= 0 AND 재고량 <= 10000) #재고량은 0에서 10000사이값을 유지해야함
CONSTRAINT CHK_CPY CHECK(제조업체 = '한빛제과') 
#모든 제품의 제조업체로 한빛제과만 허용된다는 데이터 무결성 제약조건에 CHK_CPY이라는 고유의 이름을 부여함.
#다른 테이블에는 CHK_CPY이라는 이름으로 정의된 제약조건이 있으면 안 됨

테이블 생성 예

CREATE TABLE 고객 (
	고객아이디 VATCHAR(20) NOT NULL,
        고객이름 VARCHAR(10) NOT NULL,
        나이 INT,
        등급 VARCHAR(10) NOT NULL,
        직업 VARCHAR(20),
        적립금 INT DEFAULT 0,
        PRIMARY KEY(고객아이디)
);

테이블의 변경

테이블은 ALTER TABLE 문으로 변경할 수 있습니다. ALTER TABLE 문을 이용해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능합니다.

 

새로운 속성 추가

ex

ALTER TABLE 테이블_명
	ADD 속성_명 테이터_타입 [NOT NULL] [DEFAULT 기본_값];

기존 속성 삭제

ex

ALTER TABLE 테이블_명 DROP COLUMN 속성_명

새로운 제약 조건 추가

ALTER TABLE 테이블_명 ADD CONSTRAINT 제약조건_명 제약조건_내용

 

기존 제약 조건 삭제

ALTER TABLE 테이블 명 DROP CONSTRAINT 제약조건 명

테이블의 삭제

 

 

DROP TABLE 테이블 명

03 SQL을 이용한 데이터 조작

SQL의 데이터 조작 기능

SQL의 데이터 조작 기능은 원하는 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제로 분류할 수 있습니다.

 

데이터의 검색(SELECT)

기본 검색

SELETCT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트;

산술식을 이용한 검색

조건 검색

LIKE를 이용한 검색

NULL을 이용한 검색

정렬 검색

집계 함수를 이용한 검색

그룹별 검색

여러 테이블에 대한 조인 검색

부속 질의문을 이용한 검색

 

데이터의 삽입(INSERT)

데이터 직접 삽입

부속 질의문을 이용한 데이터 삽입

 

데이터의 수정(UPDATE)

 

데이터의 삭제(DELETE)

 

 

04 뷰

 

뷰의 개념

 

뷰의 생성

SQL의 뷰를 생성하기 위한 명령어는 CREATE VIEW 입니다.

ex

CREATE VIEW 뷰_명[(속성_리스트)]
AS SELEFCT 문
[WITH CHECK OPTION];

뷰의 활용

CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있습니다.

ex

CREATE VIEW 제품1  AS SELECT 제품번호, 재고량, 제조업체 FROM 제품 WITH CHECK OPTION;

SELECT * FROM 제품1;

뷰의 장점

1. 질의문을 좀 더 쉽게 작성할 수 있다.

2. 데이터의 보안 유지에도 도움이 된다.

3. 데이터를 좀 더 편리하게 관리할 수 있다.

 

뷰의 삭제

 

05 삽입 SQL

DROP VIEW 뷰_명;

삽입 SQL의 개념과 특징

삽입 SQL 특징

삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있습니다.

프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙입니다.

프로그램 안의 일반 변수를 삽입 SQL 문에서 사용할 수 있습니다. 단 SQL 문에서 일반 변수를 사용할 때는 앞에 콜론(:)을 붙여 테이블 이름이나 속성의 이름과 구분합니다.

 

커서가 필요 없는 삽입 SQL

결과로 행 하나만 반환하는 SELECT 문은 커서가 필요없습니다.

- CREAT, TABLE, INSERT, DELETE, UPDATE

커서가 필요한 삽입 SQL

SELECT 문 처럼 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요합니다.

선언

EXEC SQL DECLARE 커서_명 CURSOR FOR SELECT 문;

실행 명령

EXEC SQL OPEN 커서_명;

실행 종료

EXEC SQL CLOSE 커서_명;