데이터베이스 언어 SQL

이 글은 김연희 저 - "데이터베이스 개론 3판"을 공부하고 정리하여 작성하였습니다.

 

1. SQL의 소개

• SQL(Structured Query Language)은 관계 데이터베이스를 위한 표준 질의어로 많이 사용하는 언어
• SQL은 사용자가 처리를 원하는 데이터가 무엇인지만 제시하고 데이터를 처리하는 방법은 필요가 없어 비절차적 데이터 언어의 특징이 있음
• 1974년에 개발된 SEQUEL(Structured English QUEry Language)에서 유래됨
• SEQUEL은 IBM 연구소에서 개발한 연구용 관계 데이터베이스 관리 시스템 SYSTEM R 언어였는데 회사에 따라 여러 형태로 존재하여 불편한 점이 더 많았음
• 그래서 1986년 ANSI와 ISO에서 SQL을 관계 데이터베이스 표준 질의어로 채택하고 표준화 작업 진행
• SQL의 분류
  • 데이터 정의어(DDL): 테이블을 생성하고 변경•삭제하는 기능을 제공
  • 데이터 조작어(DML): 테이블에 새 데이터를 삽입하거나 테이블에 저장된 데이터를 수정•삭제•검색하는 기능 제공
  • 데이터 제어어(DCL): 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 하는 언어, 주로 데이터베이스 관리자가 사용

 

2. SQL을 이용한 데이터 정의

• SQL의 데이터 정의 기능
  1. 테이블 생성: CREATE TABLE
  2. 테이블 변경: ALTER TABLE
  3. 테이블 삭제: DROP TABLE

2.1. 테이블 생성

• 새로운 테이블을 생성하기 위한 구성
  • 테이블 이름
  • 테이블을 구성하는 속성의 이름
  • 데이터 타입 및 제약사항에 대한 정의
  • 기본키•대체키•외래키의 정의
  • 데이터 무결성을 위한 제약조건 정의 등
• SQL 명령어: CREATE TABLE
• CREATE TABLE 문의 기본 형식

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

1. 테이블을 구성하는 각 속성의 이름과 데이터 타입, 기본적인 제약 사항 정의
2. 기본키로 테이블에 하나만 존재
3. 대체키로 테이블에 여러 개 존재할 수 있음
4. 외래키로 테이블에 여러개 존재할 수 있음
5. 데이터 무결성을 위한 제약조건으로 테이블에 여러 개 존재할 수 있음

2.1.1. 속성의 정의

• 테이블을 구성하는 각 속성의 데이터 타입 선택 후 속성의 널 값 허용 여부와 기본값 필요 여부를 결정해야 함
• 기본적으로는 null 값이 허용되고 허용하지 않으려면 NOT NULL 키워드를 포함해야 함
• 특히, 기본키를 구성하는 모든 속성은 널 값을 가질 수 없도록 반드시 NOT NULL 키워드를 표기
• 속성의 데이터 타입

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

• ex)

고객아이디 VARCHAR(20) NOT NULL
적립금 INT DEFAULT 0
담당자 VARCHAR(10) DEFAULT '방경아'

2.1.2. 키의 정의

• CREATE TABLE 문으로 테이블을 정의할 때는 기본키, 대체키, 외래키로 지정할 수 있음
• 기본키는 PRIMARY KEY 키워드를 사용해 지정
• 기본키는 하나만 지정할 수 있고 여러 개의 속성으로 구성할 수도 있음
• 외래키는 FOREIGN KEY 키워드를 사용해 지정
• 외래키를 지정할 때는 참조 무결성 제약조건을 유지하기 위해 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 명확히 제시해야 함
• 튜플을 삭제할 때 처리하는 방법 (ON DELETE)
  1. ON DELETE NO ACTION : 튜플을 삭제하지 못하게 함
  2. ON DELETE CASCADE : 관련 튜플을 함께 삭제함
  3. ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경함.
  4. ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경함.
• 튜플을 변경할 때 처리하는 방법 (ON UPDATE)
  1. ON UPDATE NO ACTION : 튜플을 변경하지 못하게 함
  2. ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경함
  3. ON UPDATE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경함.
  4. ON UPDATE SET DEFAULT : 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경함.

2.1.3. 테이블 무결성 제약조건의 정의

• CREATE TABLE 문으로 테이블을 정의할 때는 CHECK 키워드를 사용해 특정 속성에 대한 제약조건을 지정할 수 있음
• 테이블에는 CHECK 키워드로 지정한 제약조건을 만족하는 튜플만 존재하게 됨.
• 테이블에서 항상 정확하고 유효한 데이터를 유지하기 위해 데이터 무결성을 위한 제약조건을 표현하는 방법

예)

• CHECK(재고량>=0 AND 재고량<=10000) : 모든 제품의 재고량은 항상 0개 이상, 10000개 이하로 유지
• CONSTRAINT CHK_CPY CHECK(제조업체='한빛제과') : 모든 제품의 제조업체로 한빛제과만 허용된다는 데이터 무결성 제약조건에 CHK_CPY라는 고유의 이름을 부여

2.3. 테이블의 변경

• 테이블은 ALTER TABLE 문으로 변경할 수 있음
• ALTER TABLE을 통해 새로운 속성 추가, 기존 속성 삭제, 새로운 제약조건 추가, 기존 제약조건 삭제 등이 가능

1. 새로운 속성의 추가

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

2. 기존 속성의 삭제

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

3. 새로운 제약조건의 추가

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

4. 기존 제약조건의 삭제

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

2.4. 테이블의 삭제

• DROP TABLE 명령어로 삭제할 수 있음.

DROP TABLE 테이블_이름

 

3. SQL을 이용한 데이터 조작

• SQL의 데이터 조작 기능
  1. 데이터 검색: SELECT
  2. 데이터 삽입: INSERT
  3. 데이터 수정: UPDATE
  4. 데이터 삭제: DELETE

3.1. 데이터 검색

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

• SELECT 키워드와 함께 검색하고 싶은 속성의 이름을 콤마(,)로 구분하여 차례로 나열
• *를 사용하면 결과 테이블의 모든 속성을 검색하며 원본 테이블의 속성 순서와 같게 나열됨
• 동일한 튜플을 허용하지 않을 경우 DISTINCT 키워드를 사용하여 한 번씩만 출력되도록 함.
• AS 키워드를 사용하여 출력되는 속성의 이름을 다른 이름으로 변경할 수 있음.
• 그리고 AS 키워드는 생략이 가능함.
• 만약에 지정한 이름에 공백이 포함된 경우, 오라클에선 큰따옴표로 묶고 MS SQL에선 작은 따옴표로 묶는다.
• 산술식을 이용한 검색
  • 산술식은 속성의 이름과 +,-,*,/ 등의 산술연산자, 상수로 구성함.
  • SELECT 제품명, 단가 + 500 AS "조정 단가" FROM 제품;

3.1.1. 조건 검색

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

• WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건을 제시하면 됨.
• 조건은 숫자 뿐만 아니라 문자, 날짜 값도 비교할 수 있음

1. LIKE를 이용한 검색
   • 검색 조건을 부분적으로만 알고 있는 경우 사용하여 검색
   • LIKE 키워드는 문자열을 이용하는 조건에서만 사용 가능

   기호	설명
   %	0개 이상의 문자
   _	1개의 문자

   사용 예	설명
   LIKE ‘데이터%’	데이터로 시작하는 문자열
   LIKE ‘%데이터’	데이터로 끝나는 문자열
   LIKE ‘%데이터%’	데이터가 포함된 문자열
   LIKE ‘데이터___’	데이터로 시작하는 6자 길이의 문자열
   LIKE ‘___한%’	세번째 글자가 ‘한’인 문자열

2. NULL을 이용한 검색
   • 특정 속성의 값이 널 값인지를 비교하기 위해 IS NULL 키워드를 사용
   • WHERE 나이 IS NULL , WHERE 나이 IS NOT NULL
3. 정렬 검색
   • 검색 결과 테이블은 일반적으로 DBMS가 정한 순서로 출력되지만 사용자가 원하는 순서대로 출력하려면 ORDER BY 키워드를 사용

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

   • 지정하지 않는 경우 오름차순(ASC)로 기본정렬함.
   • 정렬기준이 여러 개인 경우 정렬이 기준이 되는 속성을 차례대로 제시함.
4. 집계 함수를 이용한 검색
   • 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수를 이용함.
   • 열 함수라고도 하며 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공
   • SUM, AVG는 숫자 데이터에서만 적용할 수 있고 나머지는 모든 데이터에 사용 가능함.
   • 집계 함수를 사용할 때 주의 사항
     1. 집계 함수는 널인 속성 값을 제외하고 계산
     2. 집계 함수는 WHERE 절을 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용 가능
5. 그룹별 검색
   • 테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용함.
   • 그룹에 대한 조건을 추가하려면 GROUP BY 절과 HAVING 절을 같이 사용함.

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

6. 여러 테이블에 대한 조인 검색
   • 여러 개의 테이블을 연결하여 데이터를 검색하는 것을 말함.
   • 조인 검색을 위해선 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라 함.
   • 일반적으로 테이블의 외래키를 조인속성으로 사용함

   SELECT 제품.제품명
   FROM 제품, 주문
   WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;
   

   • 표준 SQL에서는 INNER JOIN과 ON을 이용해 작성하기도 함.

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

7. 부속 질의문(서브쿼리)을 이용한 검색
   • SELECT 문 안에 또 다른 SELECT 문을 포함할 수도 있음
   • 다른 SELECT 문 안에 들어 있는 SELECT 문을 부속 질의문 또는 서브 쿼리라 함.
   • 그리고 다른 SELECT 문을 포함하는 SELECT 문을 상위 질의문 또는 메인 쿼리라 함.
   • 서브쿼리는 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며 먼저 수행됨.

   SELECT 제품명, 단가
   FROM 제품
   WHERE 제조업체 = (
   	SELECT 제조업체
   	FROM 제품
   	WHERE 제품명 = '달콤비스킷'
   );
   

   • 서브쿼리 결과문이 단일 값인 경우 = 연산자를 사용할 수 있음
   • 부속 질의문의 결과값을 여러 개 반환하는 경우, IN 연산자를 함께 사용함

   SELECT 제품명, 단가
   FROM 제품
   WHERE 제조업체 IN (
   	SELECT 주문제품
   	FROM 주문
   	WHERE 주문고객 = 'banana'
   );
   

   • 다중 행 서브쿼리에 사용 가능한 연산자

   연산자	설명
   IN	서브쿼리 결과 중 일치하는 것이 있으면 검색 조건이 참
   NOT IN	서브쿼리 결과 중 일치하는 것이 없으면 검색 조건이 참
   EXISTS	서브쿼리 결과 값이 하나라도 존재하면 검색 조건이 참
   NOT EXISTS	서브쿼리 결과 값이 하나도 존재하지 않으면 검색 조건이 참
   ALL	서브쿼리 결과 값 모두와 비교한 결과가 참이면 검색 조건을 만족
   ANY 또는 SOME	서브쿼리 결과 값 하나라도 비교한 결과가 참이면 검색 조건을 만족

3.2. 데이터의 삽입

• 테이블에 새로운 튜플을 삽입하기 위해 필요한 SQL문은 INSERT다.
• INSERT 문을 이용해 삽입하는 방법은 두가지다.
  • 테이블에 튜플을 직접 삽입
  • 서브쿼리를 이용해 튜플을 삽입

1. 데이터 직접 삽입
   • INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응되고 개수도 같아야 함.
   • INTO 절의 속성 이름의 리스트는 생략할 수 있고 생략한 경우 지정한 순서대로 VALUES 절의 속성 값이 삽입됨.
   • VALUES 절에 나열되는 속성 값은 문자나 날짜 타입의 데이터인 경우 작은 따옴표로 묶어야 함.
2. INSERT INTO 테이블_이름[(속성_리스트)] VALUES(속성값_리스트);
3. 서브쿼리를 이용한 데이터 삽입
4. INSERT INTO 테이블_이름[(속성_리스트)] SELECT 문; INSERT INTO 한빛제품(제품명, 재고량, 단가) SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체 = '한빛제과'

3.3. 데이터의 수정

• 테이블에 저장된 데이터를 수정하기 위해 필요한 SQL 명령어는 UPDATE

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

• UPDATE 문은 테이블에 저장된 튜플에 서 특정 속성의 값을 수정함.
• WHERE 절을 생략하면 모든 튜플을 대상으로 속성 값을 수정함.

3.4. 데이터의 삭제

• 테이블에 저장된 데이터를 수정하기 위해 필요한 SQL 명령어는 DELETE

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

• WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
• WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제하여 빈 테이블이 됨

 

4. 뷰

4.1. 뷰의 개념

• 뷰(view)는 다른 테이블을 기반으로 만들어진 가상 테이블
• 논리적으로만 존재하면서도 일반 테입르과 동일한 방법으로 사용할 수 있음
• 뷰를 만드는 데 기반이 되는 물리적 테이블을 기본 테이블이라 함.
• 뷰의 생성과 삭제도 SQL의 데이터 정의 기능에 해당함.

4.2. 뷰의 생성

• 뷰를 생성하기 위해 필요한 SQL 명령어는 CREATE VIEW
• CREATE VIEW 뷰_이름[(속성_리스트)] AS SELECT 문 [WITH CHECK OPTION];
• 새로 생성할 뷰 이름을 제시한 후, 뷰를 구성하는 속성의 이름을 괄호 안에 나열함.
• AS 키워드와 함께 기본 테이블에 대한 SELECT 문을 제시함.
• SELECT 문은 생성하고자 하는 뷰의 정의를 담고 있고 ORDER BY를 사용할 수 없단 점만 제외하면 일반 SELECT 문과 동일함.
• WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 WHERE 키워드와 함께 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미

CREATE VIEW 우수고객(고객아이디, 고객이름, 나이, 등급)
AS SELECT 고객아이디, 고객이름, 나이, 등급
	FROM 고객
	WHERE 등급='vip'
WITH CHECK OPTION;

SEECT * FROM 우수고객;

4.3. 뷰의 활용

• CREATE VIEW 문으로 생성된 뷰에서도 일반 테이블처럼 원하는 데이터를 검색할 수 있음
• 뷰에 대한 SELECT 문은 내부적으로 기본 테이블에 대한 SELECT 문으로 변환되어 수행됨
• 뷰에 대한 INSERT, DELETE, UPDATE도 가능하지만 제한적.
• 기본 테이블의 기본키 속성을 포함한 수정과 삭제 연산은 실행이 되지만 그렇지 않은 경우 실행되지 않음
• 변경이 불가능한 뷰의 특징
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 경우
  • 기본 테이블에 있던 내용이 아닌 집계함수로 새로 계산된 내용을 포함한 뷰
  • DISTINCT 키워드를 포함하여 정의한 뷰
  • GROUP BY 절을 포함하여 정의한 뷰
  • 여러 개의 테이블을 조인하여 정의한 뷰는 안되는 경우가 많음
• 뷰의 대표적인 장점
  1. 질의문을 좀 더 쉽게 작성할 수 있음
     • 뷰를 미리 만들면 사용자가 WHERE 절 없이 뷰를 검색해도 특정 조건을 만족하는 데이터를 검색할 수 있음
     • 복잡한 SQL 문 작성 없이 원하는 데이터를 검색할 수 있음
  2. 데이터 보안 유지에 도움이 됨
  3. 데이터를 좀 더 편리하게 관리 할 수 있음

4.4. 뷰의 삭제

• 뷰를 삭제하기 위해 필요한 SQL 명령어는 DROP VIEW
• DROP VIEW 뷰_이름

 

5. 삽입 SQL

5.1. 삽입 SQL의 개념과 특징

• C, C++, JAVA 등과 같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL 문
• 초보 사용자도 삽입 SQL을 통해 데이터베이스 기능을 쉽게 이용할 수 있음
• 삽입 SQL 사용할 때의 특징
  • 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
  • 일반 명령문과 구분하기 위해 삽입 SQL문 앞에 EXEC SQL을 붙임
  • 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있음, 앞에 콜론(:)을 붙여 테이블 이름이나 속성의 이름과 구분함
• 수행 결과로 여러 개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우 커서(cursor)라는 도구를 이용해 한 번에 한 행씩 차례로 처리함
• 커서는 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할

5.2. 커서가 필요없는 삽입 SQL

• 특별히 결과 테이블을 반환하지 않는 쿼리는 커서가 필요 없음
• 예시
  • 제품번호를 입력하면 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색하여 화면에 출력해주는 프로그램

  int main(){
  	
  	// 1. 삽입 SQL에 사용할 변수 미리 선언
  	EXEC SQL BEGIN DECLARE SECTION;
  				char p_no[4], p_name[21];
  				int price;
  	EXEC SQL END DECLARE SECTION;
  	
  	...
  	// 2. 제품번호 입력(p.no)(생략)
  	
  	// 3. 삽입 SQL을 통해 변수 저장
  	EXEC SQL SELECT 제품명, 단가 INTO :p_name, :price
  			FROM 제품
  			WHERE 제품번호 = :p.no;
  	
  	...
  }
  

  • BEGIN DECLARE SECTION 과 END DECLARE SECTION 사이에 삽입 SQL 문에 사용할 변수를 미리 선언
  • 삽입 SQL에서 사용할 변수를 선언할 때는 데이터 타입에 주의해야 함.
  • 제품번호 속성 길이 최대 3자 + ‘\0’을 마지막에 저장할 수 있도록 최대 4자로 제한
  • 사용자 입력을 받은 후, 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품 이름과 단가를 검색하여 p_name, price 변수에 값을 저장하는 삽입 SQL 문이다.
  • 저장할 변수를 INTO 키워드 다음에 차례로 나열하는 것이 차이점
  • 이 SELECT 문은 하나의 행을 결과로 반환하여 커서가 필요 없음

5.3. 커서가 필요한 삽입 SQL

• SELECT 문의 실행 결과로 여러 행이 검색되는 경우, 한번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요함.
• 우선 커서의 이름과 커서가 필요한 SELECT 문 선언

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

• 커서를 선언한 후 SELECT 문을 실행하는 명령

EXEC SQL OPEN 커서_이름;

• OPEN 명령어를 이용해 실행하면 검색된 행들이 반환되고 커서는 검색된 행들 중 첫번째 행의 바로 앞에 위치함
• 검색된 행들을 차례로 처리하기 위해 이동시키는 명령어는 FETCH
• 커서를 이동하여 처리할 다음 행을 가리키게 하고, 커서가 가리키는 행으로부터 속성 값들을 가져와 변수에 저장

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

EX)
EXEC SQL FETCH product_cursor INTO :p_name, :price;

• 일반적으로 FETCH는 여러번 수행되기 때문에 반복문과 함께 사용
• 커서를 사용하지 않으면 CLOSE 명령어를 사용

EXEC SQL CLOSE 커서_이름;

 

 

자료

• 데이터베이스 개론 3판 (김연희 저, 2024.1)