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

SQL

• 관계 데이터베이스를 위한 표준 질의어로 많이 사용되는 언어
  사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 어떻게 처리해야 하는지 절차를 언급할 필요가 없음 (비절차)
• DBMS에 직접 접근하여 대화식으로 질의를 작성, C나 Java 같은 언어로 작성한 응용 프로그램에 삽입하여 사용

SQL의 분류

• 데이터 조작어, 데이터 정의어, 데이터 제어어

모든 SQL 문은 ;으로 문장 끝을 표시, SQL 문에 사용되는 키워드는 대소문자를 구분하지 않음

SQL을 이용한 데이터 정의

• 테이블 생성, 생성된 테이블 구조의 변경, 테이블 삭제로 분류

테이블 생성 - CREATE TABLE

• 생성할 테이블을 구성하는 속성들의 이름, 데이터 타입 및 제약 사항에 대한 정의, 기본키/대체키/외래키의 정의, 데이터 무결성을 위한 제약조건의 정의 등을 포함
• 1)은 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항을 정의
  2)는 기본키로 테이블에 하나만 존재
  3)은 대체키로 테이블에 여러 개 존재 가능
  4)는 외래키로 테이블에 여러 개 존재 가능
  5)는 데이터 무결성을 위한 제약조건으로 여러 개 존재 가능

CREATE TABLE 테이블_이름 (
1) 속성_이름 데이터_타입 [NOT NULL] [DEFAULT]
2) [PRIMARY KEY (속성_리스트)]
3) [UNIQUE (속성_리스트)]
4) [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]
5) [CONSTRAINT 이름] [CHECK (조건)]
);

속성의 정의

• CREATE TABLE 문으로 생성되는 테이블을 구성하는 속성은 기본적으로 널 값이 허용, 허용하지 않는 속성을 정의하려면 NOT NULL 명시 (기본키 - 개체 무결성 제약조건, 꼭 입력해야 된다고 판단되는 속성)
  DEFAULT로 기본 값을 지정 가능, 숫자는 그대로 표현, 문자열이나 날짜는 작은따옴표 내에 표기

키의 정의

• 기본키, 대체키, 외래키를 지정할 수 있음
  • PRIMARY KEY로 기본키 설정(없어도 가능하나 선택하는 것이 좋음)
  • UNIQUE로 대체키 설정(여러 개 가능)
  • FOREIGN KEY로 외래키 설정(지정할 때는 출처를 분명히 해야함 REFERENCES를 제시 - 참조 무결성 제약조건을 유지하기 위함)
• 참조되는 테이블에서 투플을 삭제하거나 변경할 때 처리하는 방식
  NO ACTION - 기본 설정, 투플을 삭제/변경하지 못하게 함
  CASCADE - 관련 투플을 함께 삭제/변경
  SET NULL - 관련 투플의 외래키 값을 NULL로 변경
  SET DEFAULT - 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경
• 데이터 무결성 제약조건의 정의
  CHECK를 사용해 특정 속성에 대한 제약조건을 지정할 수 있음 (CHECK로 지정한 제약조건을 만족하는 투플만 존재)
  CONSTRAINT로 CHECK에 고유한 명칭 부여 가능, 이후에 제약조건을 수정하거나 제거할 때 식별하기가 쉬움

테이블 변경 - ALTER TABLE

• 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능

새로운 속성 추가

ALTER TABLE 테이블_이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT];

기존 속성 삭제

ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름;

새로운 제약조건 추가

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

기존 제약조건 삭제

ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

테이블 삭제 - DROP TABLE

• 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않음, 삭제하고자 하는 테이블을 참조하는 외래키 제약조건을 먼저 삭제해야 함

SQL을 이용한 데이터 조작

• 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제로 분류

데이터 검색 - SELECT

• 다양한 검색 유형을 지원하며, 일반 사용자들이 가장 많이 사용

기본검색

SELECT [ALL or DISTINCT] 속성_리스트
FROM 테이블_리스트;

• 테이블과 속성 모두 여러 개를 검색하고 싶을 경우 ,로 구분하여 나열
  테이블에 존재하는 모든 속성을 검색하기 위해 SELECT *를 사용할 수 있음
  ALL은 기본 설정이지만 명시적 표시 가능, 중복 제거를 위해 DISTINCT 사용
  AS 키워드를 통해 출력되는 결과 테이블의 이름 수정 가능

산술식을 이용한 검색

• SELECT 키워드와 함께 산술식을 제시할 수 있지만 실제로 값이 변경되는 것은 아니며 결과 테이블만 변경돼서 출력

조건검색

SELECT [ALL or DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건];

• WHERE절 안에 비교 연산자와 논리 연산자를 이용한 검색 조건 제시

LIKE를 이용한 검색

• 검색 조건을 부분적으로만 알 경우 LIKE 키워드를 이용해 검색, 단 문자열을 이용하는 조건에서만 사용
  • % - 0개 이상의 문자, _ - 1개의 문자
    LIKE 'DATA%' - DATA로 시작하는 문자열
    LIKE '__A%' - 세 번째 글자가 'A'인 문자열

NULL을 이용한 검색

• 검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 사용
  반대의 경우엔 IS NOT NULL / '이름 = NULL' 형태로 표현하면 안됨

정렬 검색

SELECT [ALL or DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC or DESC] ];

• 일반적으로 SELECT를 통한 결과 테이블은 DBMS가 정한 순서로 출력되기에 사용자가 원하는 순서로 출력하기 위해선 ORDER BY를 사용함
  • 오름차순 정렬 - ASC (기본 정렬)
  • 내림차순 정렬 - DESC

집계 함수를 이용한 검색

• 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 이용
  열 함수라고도 하며 개수, 합계, 평균, 최댓값, 최솟값 계산 기능 제공
  SUM, AVG는 숫자 데이터 타입만 COUNT, MAX, MIN 모든 데이터(MAX, MIN에서 문자열은 사전의 역/정순서와 같은 방식으로 비교)COUNT 함수 - 테이블의 모든 속성에 적용하여 개수를 계산할 수 있음
  개수를 정확히 계산하려면 널 값이 없는 속성에 COUNT 함수를 적용하는 것이 좋기에 기본키 속성이나 *를 이용
  DISTINCT를 사용하여 특정 속성 값의 중복 제거 가능
• ※주의 사항 - 널인 속성 값은 제외하고 계산, WHERE절에서는 사용할 수 없고 SELECT절이나 HAVING절에서만 사용
  집계 함수를 이용해 계산된 결과 값을 출력할 때는 AS 키워드를 사용해 새 이름을 부여하는게 좋음

그룹별 검색

• 테이블에서 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY를 사용
  그룹에 대한 조건을 추가하려면 GROUP BY와 HAVING을 함께 사용
  GROUP BY가 없는 SELECT문은 테이블 전체를 하나의 그룹으로 간주

SELECT [ALL or DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건] ]
[ORDER BY 속성_리스트 [ASC or DESC] ];

• 그룹을 나누는 기준이 되는 속성을 SELECT절에 작성하지 않으면 어떤 그룹에 대한 검색 결과인지 확인하기 어려움
  그룹별 검색할 때 집계 함수나 GROUP BY절에 있는 속성 외의 속성은 SELECT절에 사용할 수 없음
  여러 그룹으로 나눌 경우 기준이 되는 속성을 GROUP BY절에 순서대로 작성

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

• 여러 개의 테이블을 연결하여 데이터를 검색하는 것
  조인 검색을 하려면 테이블을 연결해주는 속성이 필요 > 조인 속성(일반적으로 외래키를 사용) 도메인은 반드시 같아야 함
  조인 검색을 위한 SQL문은 FROM절에서 검색에 필요한 모든 테이블을 나열, WHERE절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시
  테이블 이름이 길면 속성 이름 앞에 소속 테이블을 표기하는 일이 번거로울 수 있음 따라서 FROM절에 테이블 이름과 별명을 함께 제시 가능 (AS생략 가능)

서브쿼리(부속 질의문)를 이용한 검색

• SELECT문 안에 또 다른 SELECT문 - 부속 질의문(sub query)
  서브쿼리는 괄호로 묶어 작성하고, ORDER BY절을 사용할 수 없으며, 상위 질의문보다 먼저 수행
  단일 행 서브쿼리(결과 값을 하나만 반환), 다중 행 서브쿼리(결과 값을 여러 개 반환) 존재
  단일 행 서브쿼리는 일반 비교 연산자를 사용할 수 있으나 다중 행 서브쿼리는 사용할 수 없음
• 다중 행 서브쿼리와 함께 사용할 수 있는 연산자
  • IN - 서브쿼리의 결과 값 중 일치하는 것이 있으면 검색 조건이 참
  • NOT IN - 서브쿼리의 결과 값 중 일치하는 것이 없으면 검색 조건이 참
  • EXISTS - 서브쿼리의 결과 값이 하나라도 존재하면 검색 조건이 참
  • NOT EXISTS - 서브쿼리의 결과 값이 하나라도 존재하지 않으면 검색 조건이 참
  • ALL - 서브쿼리의 결과 값 모두와 비교한 결과가 참이면 검색 조건을 만족
    (비교 연산자와 함께 사용)
  • ANY or SOME - 서브쿼리의 결과 값 중 하나라도 비교한 결과가 참이면 검색 조건을 만족
    (비교 연산자와 함께 사용)

데이터 삽입 - INSERT

• 데이터 삽입은 직접 삽입과 서브쿼리를 이용해 삽입하는 방식으로 나뉨

데이터 직접 삽입

INSERT
INTO 테이블_이름[(속성_리스트)]
VALUES (속성값_리스트);

• INTO 절에서 속성 이름의 리스트는 생략 가능하지만 생략한 경우 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입됨
  VALUES절에 나열되는 속성 값은 문자나 날짜 타입의 데이터인 경우 작은따옴표로 묶음

서브쿼리를 이용한 데이터 삽입

INSERT
INTO 테이블_이름[(속성_리스트)]
SELECT 문;

데이터 수정 - UPDATE

UPDATE 테이블_이름
SET 속성_이름1 = 값1, 속성_이름2 = 값2, ...
[WHERE 조건];

• WHERE절을 생략하면 테이블에 존재하는 모든 투플을 대상
  UPDATE문에 서브쿼리가 포함되는 경우도 존재

데이터 삭제 - DELETE

DELETE
FROM 테이블_이름
[WHERE 조건];

• WHERE절을 생략하면 테이블에 존재하는 모든 투플을 삭제하여 빈 테이블이 됨
  (DROP TABLE과 다름)

뷰

• 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블
  일반 테이블과 달리 데이터를 실제로 저장하고 있지 않음
  일반적으로 뷰는 기본 테이블을 기반으로 만들어지지만 다른 뷰를 기반으로 만들 수 있음
  동일한 테이블도 어떤 뷰로 보느냐에 따라 보이는 부분이 달라짐
  뷰를 통해 내용을 쉽게 검색할 수는 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적

뷰 생성

CREATE VIEW 뷰_이름[(속성_리스트)]
AS SELECT 문
[WITH CHECK OPTION];

• AS SELECT문은 생성하고자 하는 뷰의 정의를 지니며, ORDER BY를 제외한 일반 SELECT문과 동일
  속성의 이름 리스트는 생략 가능하며 생략 시 기존의 이름을 그대로 사용, 하지만 AS SELECT문에서 집계함수를 이용할 경우 속성의 이름을 생략할 수 없음
• WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미

뷰 활용

• 일반 테이블처럼 원하는 데이터를 검색할 수 있음
  뷰에 대한 SELECT문이 내부적으로 기본 테이블에 대한 SELECT문으로 변환되어 수행되기 때문
  뷰에 대한 삽입, 수정, 삭제 연산도 기본 테이블에 수행되기에 기본 테이블이 변함
  그러나 모든 뷰에 허용되는 것은 아니며 뷰를 통한 기본 테이블의 변화는 제한적※ 변경이 되더라도 뷰에 대한 연산은 결과적으로 기본 테이블의 내용을 자동으로 바꾸기 때문에 주의해야 함
• 기본 테이블의 기본키를 지닌 뷰는 INSERT, UPDATE, DELETE가 가능하지만 기본키를 지니지 않은 뷰는 오류가 발생
  why? 기본키가 없는 뷰는 어떤 투플에 대한 삽입, 수정, 삭제 연산인지 명확히 구분되지 않음 그리고 집계 함수로 계산된 값은 기본 테이블이 원래 지닌 값이 아니기에 변경을 할 수 없음

변경이 불가능한 뷰의 특징

• 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
• 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰
• DISTINCT를 포함하여 정의한 뷰
• GROUP BY절을 포함하여 정의한 뷰
• 여러 개의 테이블을 조인하여 정의한 뷰 (변경이 불가능한 경우가 많음)

뷰의 장점

• 질의문을 좀 더 쉽게 작성할 수 있다. (특정 조건을 만족하는 투플들로 뷰를 만들어놓으면 WHERE절 없이 쉽게 검색)
• 데이터의 보안 유지에 도움이 된다. (뷰에 포함되지 않은 데이터를 사용자로부터 보호)
• 데이터를 좀 더 편리하게 관리할 수 있다. (제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요가 없음)

뷰 삭제

DROP VIEW 뷰_이름;

• 뷰를 삭제하더라도 기본 테이블은 영향을 받지 않음

삽입 SQL(임베디드 SQL)

• 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL문
  삽입SQL문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
  프로그램 안의 일반 명령문과 구별하기 위해 앞에 EXEC SQL을 붙임
  프로그램에 선언된 일반 변수를 사용할 수 있으나 앞에 :를 붙여 테이블 이름이나 속성의 이름과 구분함
• 여러 개의 행을 반환하는 SELECT문을 사용할 경우 커서라는 도구가 필요

커서

• 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할

커서가 필요 없는 삽입 SQL

CREATE문, INSERT문, DELETE문, UPDATE문, 결과로 행 하나만 반환하는 SELECT문

커서가 필요한 삽입 SQL

여러 행을 결과로 반환하는 SELECT문