[데이터베이스 개론] Chapter7 - 데이터베이스 언어 SQL

SQL의 소개

• SQL(Structured Query Language)은 관계 데이터를 위한 표준 질의어로 사용한다.
• SQL은 원하는 데이터가 무엇인지만 말하기 때문에 비절차적 데이터 언어의 특성을 띤다.
• SQL은 SEQUEL(Structured English QUEry Language) 에서 유래했으며 1986년 ANSI와 ISO에서 SQL을 관계 데이터베이스의 표준 질의어로 채택하고 표준화 작업을 진행하였다.
• 이후로 계속 개정을 거쳐 SQL:2022까지 발표되었다.
• SQL은 DBMS에 직접 접근하여 사용하거나 응용 프로그램에 삽입하여 사용할 수도 있다.

SQL을 이용한 데이터 정의

테이블의 생성

CREATE TABLE 테이블_이름 （
속성_이름 데이터_타입 ［NOT NULL］ ［DEFAULT 기본_값］
［PRIMARY KEY （속성.리스트）］
［UNIQUE （속성-리스트）］
［FOREIGN KEY （속성_리스트） REFERENCES 테이블.이름（속성_리스트）］
［ON DELETE 옵션］ ［ON UPDATE 옵션］
［CONSTRAINT 이름］ ［애ECK（조건）］
)；

• 속성의 정의
  • 각 속성의 특성을 고려하여 적절한 데이터 타입을 선택하여 정의한다.
  • CREATE TABLE문을 생성되는 테이블을 구성하는 속성은 기본적으로 널 값이 허용되기 때문에 널 값을 허용하고 싶지 않다면 속성의 이름과 데이터 타입 다음에 NOT NULL을 키워드를 붙여줘야 한다.
  • 기본키는 NOT NULL이 기본이다.
  • 데이터 타입은 DBMS마다 조금씩 다르다. (Oracle : varchar2)
  • 속성에 DEFAULT 키워드를 사용하지 않는다면 널 값이 기본으로 지정된다.

• 키의 정의
• CREATE TABLE 문으로 테이블을 정의할 때는 기본키, 대체키, 외래키를 지정할 수 있다
  • 기본키
    • 기본키는 PRIMARY KEY 키워드를 사용해 지정한다.
    • 기본키를 지정하지 않아도 테이블을 정의할 수 있지만, 가능한 선택하는 것이 좋다.
    • 기본키는 하나만 지정할 수 있고 여러 속성을 조합해 사용할 수 있다.
  • 대체키
    • 대체키는 UNIQUE 키워드를 사용해 지정한다.
    • 대체키는 유일성을 가져야 한다.
    • 대체키는 한 테이블에서 여러 개를 지정할 수 있다.
  • 외래키
    • 외래키는 FOREIGN KEY 키워드를 사용해 지정한다.
    • 외래키를 지정할 때는 참조 무결성 제약조건을 유지하기 위해 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 제시해야 한다.
    • 외래키는 다른 테이블의 기본키, 대체키를 참조할 수 있다.

• 외래키가 참조하는 속성을 삭제하려면 네 가지 방법중 한가지를 선택할 수 있으며, 별도로 지정하지 않는다면 ON DELETE NO ACTION이 기본으로 선택된다.
  • ON DELETE NO ACTION ： 투플을 삭제하지 못하게 한다. 부서 테이블을 참조하는 사원 테이블이 존재하므로 부서 테이블의 투플을 삭제하지 못하게 함
  • ON DELETE CASCADE : 관련 투플을 함께 삭제한다. 사원 테이블에서 홍보부에 근무하고 있는 정소화 사원에 대한 투플도 함께 삭제함
  • ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다. 사원 테이블에서 홍보부에 근무하는 정소화 사원 투플의 소속부서 속성의 값을 NULI로 변경함
  • ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경한다. 사원 테이블에서 홍보부에 근무하는 정소화 사원 투플의 소속부서 속성의 값을 미리 지정한 기본 값으로 변경함
• 외래키가 참조하는 속성을 변경할 때도 네가지 방법을 선택할 수 있다.
  • ON UPDATE NO ACTION : 투플을 변경하지 못하도록 한다.
  • ON UPDATE CASCADE : 관련 투플에서 외래키 값을 함께 변경한다.
  • ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
  • ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

• 데이터 무결성 제약조건의 정의
  • CREATE TABLE문으로 테이블을 정의할 때 CHECK 키워드를 사용해 특정 속성에 대한 제약 조건을 지정할 수 있다.
  • 투플의 삽입이나 수정 시에도 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문은 테이블을 대상으로 하고 수행 결과도 테이블이다.
  • SELECT문에 모든 속성이 필요하다면 *을 사용할 수 있다.

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

• SELECT문의 수행 결과로 반환되는 결과 테이블에서는 동일한 투플이 중복될 수 있다.

• 결과 테이블이 중복을 허용하도록 ALL 키워드를 사용하여 명시적으로 나타내도 된다.

• 결과 테이블에서 투플의 중복을 제거하고 한 번씩만 출력되도록 하려면 DISTINCT 키워드를 사용한다.

• 결과 테이블에 출력되는 속성의 이름을 AS 키워드를 사용하여 다른 이름으로 바꾸어 출력할 수도 있다.
• AS 키워드는 생략이 가능하다.

• 산술식을 이용한 검색
  • SELECT 키워드와 함께 산술식을 제시할 수 있다.
  • 산술식은 속성의 이름과 +, -, *, / 등의 산술 연산자, 상수로 구성한다.

• 조건 검색
  • 조건을 만족하는 데이터만 검색하는 SELECT 문의 기본 형식

SELECT ［ ALL | DISTINCT ］ 속성.리스트
FR애 테이블.리스트
［ WHERE 조건 ］;

• 조건에서는 비교 연산자를 이용해서 숫자뿐 아니라 문자나 날짜 값을 비교할 수 있다.

• LIKE를 이용한 검색
  • 검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다.
  • LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다.

• NULL을 이용한 검색
  • 검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용하고 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다.
  • 널 값의 검색 조건은 '나이 = NULL'과 같은 형태로 표현하면 안 되고 반드시 '나이 IS NULL'의 형태로 표현해야 한다.
  • 널 값은 다른 값과 크기를 비교하면 결과가 모두 false가 된다. 

• 정렬 검색
  • SELECT 문의 검색 결과 테이블은 일반적으로 DBMS가 정한 순서로 출력된다.
  • 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.
  • SELECT 정렬문의 기본 형식

SELECT [ ALL I DISTINCT ] 속성_리스트
FR애 테이블_리스트
[ WHERE 조건 ]
[ ORDER BY 속성_리스트 [ ASC ! DESC ] ];

• 결과를 여러 기준에 따라 정렬하려면 ORDER BY 키워드와 정렬 기준이 되는 속성을 차례로 제시하면 된다.

• 집계 함수를 이용한 검색
  • 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용할 수 있다.
  • 집계 함수는 열 함수(column function) 라고도 하며 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공한다.
  • 집계 함수는 널인 속성 값은 제외하고 계산한다. -> 개수를 정확히 계산하려면 기본키 속성이나, '*'을 이용하여 계산한다.
  • 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다.
  • 집계 함수는 DISTINCT 키워드를 사용해 특정 속성 값의 중복을 없앨 수 있다.

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

SELECT [ ALL | DISTINCT ] 속성.리스트
FR애 테이블_리스트
[ WHERE 조건 ]
[ GROUP BY 속성.리스트 [ HAVING 조건 ] ]
[ ORDER BY 속성.리스트 [ ASC I DESC ] ]；

• 여러 테이블에 대한 조인 검색
  • 여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라 한다.
  • 테이블을 연결하는 속성을 조인 속성이라 하는데, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다.
  • 일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용한다.

• INNER JOIN과 ON 키워드를 이용해 작성하는 방법
  • INNER JOIN은 조인 속성의 값이 같은 투플에 대해서만 검색을 수행하므로 동등 조인이다.

SELECT 속성_리스트
FROM 테이블1 INNER JOIN 테이블2 ON 조인조건
[ WHERE 검색조건 ]

• OUTER JOIN과 ON 키워드를 이용해 작성하는 방법
  • OUTER JOIN은 조인 조건을 만족하지 않는 투플에 대해서도 검색을 수행한다.
  • 검색 대상으로 하는 테이블이 무엇이냐에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 나뉜다.

SELECT 속성.리스트
FR애 테이블1 LEFT | RIGHT I FULL OUTER JOIN 테이블2 ON 조인조건
[ WHERE 검색조건 ]

• 부속 질의문을 이용한 검색
  • SELECT 문 안에 또 다른 SELECT 문을 포함할 수 있다. 이때 외부 SELECT문을 메인 쿼리, 내부 SELECT문을 서브 쿼리라 한다.
  • 서브 쿼리는 괄호로 묶어 사용하고 ORDER BY 절을 사용할 수 없으며 메인 쿼리보다 먼저 실행된다.
  • 서브 쿼리는 하나의 행을 결과로 반환하는 단일 행 서브 쿼리와, 하나 이상의 행을 결과로 반환하는 다중 행 서브 쿼리로 나뉜다.
  • 단일 행 서브 쿼리는 일반 비교 연산자를 사용할 수 있지만, 다중 행 서브 쿼리는 일반 비교 연산자를 사용할 수 없다.

데이터의 삽입

• 테이블의 새로운 투플을 삽입하는데 필요한 SQL 명령어는 INSERT다.
• 테이블에 투플을 삽입하는 방법은 직접 삽입하는 방법과 서브쿼리를 이용하는 방법 두 가지가 있다.

 

• 데이터 직접 삽입
  • INTO절의 속성 이름과 VALUES절의 속성 값은 순서대로 일대일 대응되고 개수도 같아야 한다.
  • INTO절의 속성 이름은 생략할 수 있는데, 생략한 경우에는 테이블을 정의할 때 지정한 속성의 순서대로 VALUES 절의 속성 값이 삽입된다.
  • VALUES절에 나열되는 속성 값이 문자나 날짜 타입의 데이터인 경우에는 작은따옴표로 묶어야 한다.

INSERT
INTO 테이블_이름[（속성_리스트）］
VALUES （속성값|리스트）;

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

INSERT
INTO 테이블_이름［（속성_리스트）］
SELECT 문;

 

• 데이터의 수정
  • 테이블에 저장된 데이터를 수정하기 위해 필요한 SQL 명령어는 UPDATE다.
  • UPDATE 문은 테이블에 저장된 투플에서 특정 속성의 값을 수정한다.
  • 값을 어떻게 수정할 것인지는 SET 키워드 다음에 지정한다.
  • WHERE 절에 제시된 조건을 만족하는 투플만 속성 값을 수정하는데, WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 대상으로 하여 SET 절에서 지정한 대로 속성 값을 수정한다.

UPDATE 테이블_이름
SET 속성.이름1 = 값1, 속성_이름2 = 값2, •••
［WHERE 조건］;

• 데이터의 삭제
  • 테이블에 저장된 데이터를 삭제하기 위해 필요한 SQL 명령어는 DELETE다.
  • DELETE 문은 WHERE 절에 제시한 조건을 만족하는 투플만 삭제한다.
  • WHERE 절을 생략하면 테이블에 존재하는 모든 투플을 삭제하여 빈 테이블이 된다.

DELETE
FROM 테이블_이름
［WHERE 조건］;

뷰

뷰의 개념

• 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다.
• 뷰는 일반 테이블과 달리 논리적으로만 존재하며 데이터를 실제로 저장하고 있지 않지만 일반 테이블과 동일한 방법으로 사용할 수 있어 사용자는 차이를 느끼기 힘들다.
• 뷰를 만드는데 기반이 되는 물리적인 테이블을 기본 테이블이라 하는데 CREATE TABLE문으로 정의된 테이블이 기본 테이블로 사용된다.
• 일반적으로 뷰는 기본 테이블을 기반으로 만들어지지만 다른 뷰를 기반으로 새로운 뷰를 만들 수도 있다.
• 뷰는 창과 같은 역할을 하는데 동일한 기본 테이블도 어떤 뷰로 보느냐에 따라 보이는 부분이 달라지기 때문이다.
• 뷰는 기본 테이블의 내용을 쉽게 검색할 수 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어진다.
• 뷰의 생성과 삭제도 SQL 데이터 정의 기능에 해당한다.

뷰의 생성

• 뷰를 생성하기 위해 필요한 SQL 명령어는 CREATE VIEW다.
• 뷰는 ORDER BY를 사용할 수 없다는 점을 제외하고는 일반 SELECT문과 동일하다. (ORACLE은 허용)
• 뷰를 구성하는 속성의 이름 리스트는 생략할 수 있는데, 생략 시 SELECT 문에서 나열된 속성의 이름을 그대로 사용할 수 있다.
• WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미한다.
• 집계 함수를 통해 계산된 경우에는 뷰를 구성하는 속성의 이름을 명확히 제시해야 한다.

CREATE VIEW 뷰_이름［（속성_리스트）］
AS SELECT 문
［WITH CHECK OPTION］；

 

뷰의 활용

• CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있다.
• 뷰는 내부적으로 기본 테이블에 대한 SELECT문으로 변환되어 수행되고 결과 테이블을 반환한다.

• 뷰는 변경이 가능한 뷰와 변경이 불가능한 뷰가 있는데, 변경이 가능한 뷰는 INSERT 문, UPDATE 문, DELETE문을 수행할 수 있다.
• 이때 뷰에 대한 삽입, 수정, 삭제 연산도 기본 테이블에 수행되기 때문에 결과적으로는 기본 테이블이 변한다.
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
  • 기본 테이블에서 NOT NULL로 지정된 속성이 포함되어 있지 않은 뷰는 변경할 수 없는 경우가 있다.
  • 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수없다.
  • DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.
  • GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.
  • 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.

 

• 뷰의 장점
  • 질의문을 좀 더 쉽게 작성할 수 있다.
    • 특정 조건을 만족하는 투플들로 뷰를 미리 만들어놓으면, 사용자가 WHERE 절 없이 뷰를 검색해도 특정 조건을 만족하는 데이터를 검색할 수 있다. 또한 GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어놓으면, 복잡한 SQL 문을 작성하지 않아도 SELECT 절과 FROM 절만으로 원하는 데이터를 검색할 수 있다.
  • 데이터의 보안 유지에 도움이 된다.
    • 여러 사용자의 요구에 맞는 다양한 뷰를 미리 정의해 두고 사용자가 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 뷰에 포함되지 않은 데이터를 사용자로부터 보호할 수 있다.
  • 데이터를 좀 더 편리하게 관리할 수 있다.
    • 제공된 뷰에 포함되지 않은 기본 테이블의 다른 부분은 사용자가 신경 쓸 필요가 없다. 또 한 제공된 뷰와 관련이 없는 다른 테이블의 변화에도 영향을 받지 않는다.

뷰의 삭제

• 뷰를 삭제하기 위해 필요한 SQL 명령어는 DROP VIEW다.
• 삭제할 뷰를 참조하는 제약조건이 존재한다면 삭제가 수행되지 않기 때문에 삭제하고자 하는 뷰를 참조하는 제약조건을 먼저 삭제해야 한다.

DROP VIEW 뷰.이름;

삽입 SQL

삽입 SQL의 개념과 특징

• SQL을 응용 프로그램 안에 삽입하여 사용하는 SQL을 삽입 SQL이라 한다.
• 삽입 SQL의 특징
  • 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있다.
  • 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙인다.
  • 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있다. 단, SQL 문에서 일반 변수를 사용할 때는 앞에 콜론（：）을 붙여 테이블 이름이나 속성의 이름과 구분한다.
• 수행 결과로 여 러 개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우에는 커서라는 도구가 필요하다.
• 커서는 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할을 한다.
• 프로그램에서는 SELECT 문의 수행 결과로 반환되는 여러 행을 한꺼번에 처리할 수 없으므로 커서를 이용해 한 번에 한 행씩 차례로 처리해야 한다.

커서가 필요 없는 삽입 SQL

• SQL 문을 실행했을 때 특별히 결과 테이블을 반환하지 않는 CREATE TABLE 문, INSERT 문, DELETE 문, UPDATE 문, 결과로 행 하나만 반환하는 SELECT 문은 커서가 필요 없다.

커서가 필요한 삽입 SQL

• SELECT 문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요하다. 커서를 사용하기 전에 먼저 커서의 이름과 커서가 필요한 SELECT 문을 선언해야 한다.

EXEC SQL DECLARE 커서_이름 CURSOR FOR SELECT 문;

• 커서를 선언한 후 SELECT 문을 실행하는 명령이 별도로 필요하다. 

EXEC SQL OPEN 커서_이름;

• OPEN 명령어를 이용해 SELECT 문이 실행되면 검색된 행들이 반환되고, 커서는 검색된 행 들 중에서 첫 번째 행의 바로 앞에 위치한다.
• 검색된 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어는 FETCH다.

EXEC SQL FETCH 커서_이름 INTO 변수_리스트;

• 결과 테이블에는 여러 행이 존재하므로 FETCH 문을 반복해서 여러 번 수행해야 한다. 그래 서 FETCH 문은 프로그램 안에서 일반적으로 반복문과 함께 사용한다.

 

• 커서를 더 사용하지 않을 때는 CLOSE 명령어를 사용한다. CLOSE 문의 기본 형식과 예는 다음과 같다.

EXEC SQL CL0SE 커서_이름;

---

Q & A

본 도서의 내용을 정리한 것이 아닌 학습 스터디를 위해 제가 작성한 질답입니다.
사실과 다른 내용이 있다면 지적 부탁드립니다 :)

Q1 : SQL의 View와 MVC 패턴의 View의 차이점에 대해 설명 해주세요.

SQL에서 view는 가상의 테이블로, 쿼리를 실행하여 결과를 저장하는 뷰입니다.

주로 데이터베이스에 저장된 데이터를 필요에 따라 조작하거나 필터링하기 위해 사용됩니다.
반면에 MVC 패턴의 view는 사용자에게 정보를 표시하는 역할을 합니다.

사용자 인터페이스를 구성하고 사용자 입력을 받아 컨트롤러에 전달합니다.

Q2 : SELECT 명령어에서 집계 함수를 사용할 때 정확한 개수를 세는 방법을 알려 주세요.

집계 함수는 널 값을 제외하고 계산하기 때문에 질의 결과의 개수를 정확히 계산하려면 기본키 속성이나, '*'을 이용하여 계산해야 합니다.

 

Q3 : 검색 조건에서 특정 속성의 값이 널인지 확인하는 키워드에 대해 말하고, 널 값과 값을 비교하면 어떻게 되는지 알려 주세요.

검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용하고 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용합니다. 또한, 널 값과 다른 값을 비교하면 모든 결과가 거짓(false)로 나오게 됩니다.

 

출처 : 김연희, 데이터베이스 개론 3판, 한빛아카데미(2022)