데이터베이스 언어 SQL
SQL
Structured Query Language
= 관계 데이터베이스를 위한 표준 질의어
= 비절차적 데이터 언어
통상 질의어는 데이터 언어 중 비절차적 데이터 조작어에 해당하지만, SQL은 정의 및 제어 기능까지 제공하므로 데이터 언어로 이해할 수 있다.
발전 역사
연구용 관계 DBMS인 SYSTEM R을 위한 언어 SEQUEL(Structured English QUEry Language)에서 유래
→ 새로운 관계 DBMS를 사용하기 위해서 그에 맞는 질의어를 재학습시켜야 하는 문제
→ 미국 표준 연구소 ANSI와 국제 표준화 기구 ISO에서 SQL을 관계 데이터베이스의 표준 질의어로 채택&표준화 작업 진행
사용 방식
대화식 SQL: DBMS에 직접 접근하여 질의를 작성해 사용
삽입 SQL: 프로그래밍 언어로 작성된 응용 프로그램에 삽입해 사용
SQL의 분류
데이터 정의어(DDL): 테이블을 생성, 변경, 제거하는 기능 제공
데이터 조작어(DML): 테이블에 새 데이터를 삽입하거나, 테이블에 저장된 데이터를 수정·삭제·검색하는 기능 제공
데이터 제어어(DCL): 보안을 위해 데이터에 대한 접근 및 사용 권한을 사용자별로 부여하거나 취소하는 기능을 제공(DB 관리자가 주로 사용)
SQL을 이용한 데이터 정의
SQL의 데이터 정의 기능
① 테이블 생성(CREATE TABLE)
② 테이블 변경(ALTER TABLE)
③ 테이블 삭제(DROP TABLE)
SQL문은 세미콜론으로 문장의 끝을 표시하고, 대소문자를 구분하지 않는다.
테이블 생성
생성할 테이블의 이름, 이를 구성하는 속성들의 이름과 데이터 타입 및 제약 사항에 대한 정의, 기본키·대체키·외래키의 정의, 데이터 무결성을 위한 제약조건의 정의 등을 포함해야 한다.
① 각 속성의 이름과 데이터 타입, 기본 제약 사항
② 기본키(하나만 존재 가능)
③ 대체키
④ 외래키
⑤ 데이터 무결성을 위한 제약조건
* []로 표시한 항목은 생략 가능
속성 정의
속성의 이름 작성 → 속성의 특성에 맞는 적절한 데이터 타입 선택 → 속성의 널 값 허용 여부와 기본 값 필요 여부 결정
CREATE TABLE문으로 생성되는 테이블을 구성하는 속성은 기본적으로 널 값 허용됨 → 비허용 원할 시 NOT NULL 표기
* 기본키를 구성하는 모든 속성은 반드시 NOT NULL 키워드 포함(개체 무결성 제약조건 구현)
기본 값을 지정하지 않으면 널 값이 기본으로 저장됨
기본 값 지정할 시 → DEFAULT 키워드 사용, 문자열이나 날짜 데이터는 작은 따옴표로 묶어서 표기
* 작은따옴표로 묶인 문자열은 대소문자를 구분한다.
키의 정의
기본키
PRIMARY KEY 키워드를 사용해 지정
각 투플을 식별할 수 있게 함
모든 테이블에서 반드시 하나만 지정 가능, 여러 개의 속성으로 구성도 가능
ex) PRIMARY KEY(고객아이디), PRIMARY KEY(주문고객, 주문제품)
대체키
UNIQUE 키워드를 사용해 지정
각 투플을 유일하게 식별하는 특성은 기본키와 동일
대체키로 지정된 속성의 값은 유일성을 가져야 하고(테이블 내 중복 불가), 기본 키와 달리 널 값 허용
한 테이블에서 여러 개 지정 가능
외래키
FOREIGN KEY 키워드를 사용해 지정
외래키가 어떤 테이블의 무슨 속성을 참조하는지 REFERENCES 키워드 다음에 명확히 제시하여 출처를 분명히 해야 함(참조 무결성 제약조건 유지 → 참조되는 테이블에서 투플 함부로 삭제 및 변경 불가)
* 참조되는 테이블에서 투플을 삭제하거나 변경할 때 처리 방법을 지정하는 옵션
① ON DELETE NO ACTION: 투플 삭제 불가 *별도 지정 없을 시 기본 선택됨
② ON DELETE CASCADE: 관련 테이블 함께 삭제
③ ON DELETE SET NULL: 관련 투플의 외래키 값을 NULL로 변경
④ ON DELETE SET DEFAULT: 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경
변경 시에는 DELETE → UPDATE 키워드로 동일하게 삭제 대신 변경 동작
데이터 무결성 제약조건의 정의
CHECK 키워드를 사용해 제약조건 지정 → 테이블에 제약조건을 만족하는 투플만 존재하게 됨
CONSTRAINT 키워드를 사용해 CHECK 키워드를 사용해 지정한 제약조건에 고유 이름을 부여할 수 있음 → 식별 용이
테이블 변경
새로운 속성 추가
기존 속성 삭제
삭제할 속성과 관련된 제약조건이 존재하거나 이 속성을 참조하는 다른 속성이 존재하면 삭제 불가.
관련된 제약조건이나 참조하는 다른 속성을 먼저 삭제해야 한다.
새로운 제약 조건 추가
기존 제약조건 삭제
테이블 삭제
SQL을 이용한 데이터 조작
SQL의 데이터 조작 기능
① 데이터 검색(SELECT)
② 데이터 삽입(INSERT)
③ 데이터 수정(UPDATE)
④ 데이터 삭제(DELETE)
데이터 삽입
INSERT문 사용
방식 1) 직접 삽입 2) 부속 질의문을 이용해 삽입
데이터 직접 삽입
INTO 키워드와 함께 투플을 삽입할 테이블의 이름 제시, 속성 이름 나열 → 나열 순서대로 VALUES 뒤 속성 값들이 차례로 삽입됨
INTO 절의 속성 이름과 VALUES 절의 속성 값은 순서대로 일대일 대응이 되어야 하고, 개수도 같아야 함
INTO 절의 속성 이름 리스트는 생략 가능; 이 경우 테이블 정의 시에 지정한 속성 순서대로 삽입
문자나 날짜 타입 데이터는 작은따옴표로 묶어서 표기
부속 질의문을 이용한 데이터 삽입
부속 질의문인 SELECT문을 이용해 다른 테이블에서 검색한 데이터를 투플로 삽입
데이터 검색
SELECT문 사용
기본 검색
SELECT 키워드와 함께 검색하고 싶은 속성 이름을 콤마로 구분하여 차례로 나열
FROM 키워드와 함께 검색하고 싶은 속성이 있는 테이블 이름을 콤마로 구분하여 차례로 나열
SELECT문은 대상과 수행 결과가 모두 테이블이다.
* 사용: 테이블에 존재하는 모든 속성을 검색하기 위해 속성 이름을 전부 나열하지 않고 *로 생략해 사용 가능; 결과 테이블 속성 순서가 원본 테이블이 정의한 속성 순서와 동일해짐 (ex. SELECT * FROM 고객;)
SELECT문의 수행 결과로 반환되는 결과 테이블은 일반 릴레이션과 달리 동일한 투플이 중복될 수 있다.
- ALL: 결과 테이블이 투플의 중복을 허용하도록 명시적으로 지정(기본 값)
- DISTINCT: 결과 테이블이 투플의 중복을 허용하지 않도록 지정
AS 키워드: 결과 테이블에 출력되는 속성 이름을 AS 키워드 뒤 변경할 이름을 작성하여 지정(키워드 생략 가능)
지정하는 이름에 공백이 포함된 경우 오라클에서는 큰따옴표, MS SQL 서버에서는 작은따옴표로 묶어야 한다.
산술식을 이용한 검색
속성의 이름과 산술 연산자, 상수로 구성
(ex. SELECT 제품명, 단가 + 500 AS "조정 단가" FROM 제품;)
조건 검색
조건을 만족하는 데이터만 검색
WHERE 키워드와 함께 비교 연산자와 논리 연산자를 이용한 검색 조건 제시
숫자, 문자, 날짜 값 비교 모두 가능
LIKE를 이용한 검색
검색 조건을 부분적으로만 알고 있을 때 LIKE 키워드 사용
문자열을 이용하는 조건에만 사용 가능
%: 0개 이상의 문자
_: 1개의 문자
NULL을 이용한 검색
검색 조건에서 특정 속성의 값이 널 값인지 비교할 때, IS NULL 키워드 사용
반대로 널 값이 아닌지 비교하려면 IS NOT NULL 키워드 사용
검색 조건에서 널 값을 다른 값과 크기를 비교하면 결과가 모두 false가 된다.
정렬 검색
결과 테이블의 내용을 사용자가 원하는 기준에 따라 정렬하여 출력할 때, ORDER BY 키워드 사용
오름차순이면 ASC, 내림차순 정렬이면 DESC로 표현(오름차순;알파벳이나 사전 순이 기본 값)
널 값은 오름차순에서 맨 마지막, 내림차순에서 맨 먼저로 출력된다.
MS SQL 서버는 오름차순에서 널 값이 맨 먼저 출력되고, 내림차순에서 맨 마지막에 출력된다.
집계 함수를 이용한 검색
특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 사용
집계 함수(accregate function) = 열 함수(column function): 개수, 합계, 평균, 최대최소값 계산 기능 제공
집계 함수 사용 시 주의 사항
- 널인 속성 값은 제외하고 계산한다.
- WHERE 절에는 사용할 수 없고, SELECT 절이나 HAVING 절에서만 사용할 수 있다.
→ 정확한 개수를 계산하기 위해서 보통 기본키 속성이나 *를 주로 이용
그룹별 검색
테이블에서 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색하기 위해 GROUP BY 키워드 사용
→ GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성 지정
그룹에 대한 조건 추가하려면 HAVING 키워드와 함께 사용
GROUP BY 키워드가 없는 SELECT문 = 테이블 전체를 하나의 그룹으로 보고 검색하는 것
그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋다. (결과를 잘 확인할 수 있도록)
집계 함수나 GROUP BY 절에 있는 속성 외의 속성을 SELECT 절에 작성할 수는 없다.
여러 테이블에 대한 조인 검색
테이블을 연결하려면 조인 속성의 이름은 달라도 되지만, 도메인은 반드시 같아야 한다.
일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 활용한다.
- FROM 절에 검색에 필요한 모든 테이블을 나열
- WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
- 같은 이름의 속성이 서로 다른 테이블에 존재할 수 있으므로 테이블 이름과 속성의 이름을 . 기호로 연결
- 테이블 이름이 길면 별명 사용; AS 키워드 생략 가능
부속 질의문을 이용한 검색
부속 질의문, 서브 질의문(sub query): SELECT문 안에 있는 nested SELECT문
상위 질의문, 주 질의문(main query): 다른 SELECT문을 포함하는 상위 SELECT문
부속 질의문은 괄호로 묶어 작성
ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행
부속 질의문의 종류
- 단일 행 부속 질의문: 하나의 행을 결과로 반환. 일반 비교 연산자 사용 가능
- 다중 행 부속 질의문: 하나 이상의 행을 결과로 반환. 일반 비교 연산자 사용 불가. IN 연산자 함께 사용
* IN 연산자: 부속 질의문의 결과 값들 중에서 하나라도 일치하는 것이 있으면 검색 조건이 참이 되게 하는 연산자
데이터 수정
UPDATE문 사용
테이블에 저장된 투플에서 특정 속성의 값 수정
값을 어떻게 수정할 것인지는 SET 키워드 다음에 지정
이때 WHERE 절에 제시된 조건을 만족하는 투플만 속성 값을 수정하는데, WHERE 절 생략 시 테이블에 존재하는 모든 투플을 대상으로 하여 SET 절에서 지정한 대로 속성 값을 수정한다.
UPDATE문에 부속 질의문이 포함되기도 한다.
데이터 삭제
DELETE문 사용
WHERE 절에 제시한 조건을 만족하는 투플만 삭제
WHERE 절이 생략된 경우 테이블에 존재하는 모든 투플을 삭제하여 빈 테이블이 된다. (빈 테이블이 남아 DROP TABLE과 다름)
DELETE문에 부속 질의문이 포함될 수도 있다.
조인
SELECT ...
FROM 테이블명1
JOIN 테이블명2
ON 테이블명1.칼럼 = 테이블명2.칼럼
LEFT OUTER JOIN, RIGHT OUTER JOIN
MySQL 내장 함수
뷰
다른 테이블을 기반으로 만들어진 가상 테이블
일반 테이블과 달리 데이터를 실제로 저장하고 있지 않음. 논리적으로만 존재하면서 일반 테이블과 동일하게 사용
기본 테이블(base table): 뷰를 만드는 데 기반이 되는 물리적인 테이블
다른 뷰를 기반으로 새로운 뷰를 만들 수도 있음
기본 테이블의 내용을 쉽게 검색할 수 있게 해주지만, 테이블 내용을 바꾸는 작업은 제한적으로 이루어진다.
뷰의 생성과 삭제도 SQL의 데이터 정의 기능에 해당됨
뷰의 생성
SELECT문에서 ORDER BY를 사용할 수 없다.
WITH CHECK OPTION: 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미
집계 함수를 통해 새로 계산된 속성이 있는 경우 뷰를 구성하는 속성의 이름을 생략 없이 정확히 제시해야 한다.
뷰의 활용
모든 뷰에 검색 연산을 수행할 수 있다.
뷰에 대한 SELECT문이 내부적으로 기본 테이블에 대한 SELECT문으로 변환되어 수행된다.
→ 뷰에 대한 삽입, 수정, 삭제 연산은 결과적으로 기본 테이블에 수행된다.
삽입, 수정, 삭제가 불가능한 뷰(변경 불가능한 뷰) = 기본 테이블에서 어떤 투플을 어떻게 변경할지 명확히 제시하지 못하는 뷰
- 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰
- 기본 테이블에 있던 내용이 아니라, 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰
- DISTINCT 키워드를 포함하여 정의한 뷰
- GROUP BY 절을 포함하여 정의한 뷰
- 여러 테이블을 조인하여 정의한 뷰
뷰의 장점
1. 질의문을 좀 더 쉽게 작성할 수 있다.
2. 데이터의 보안 유지에 도움이 된다.
3. 데이터를 좀 더 편리하게 관리할 수 있다.
뷰의 삭제
뷰를 삭제해도 기본 테이블은 영향받지 않는다.
삭제할 뷰를 참조하는 제약조건이 존재하는 경우 삭제가 수행되지 않는다.(해당 제약조건을 먼저 삭제)
삽입 SQL
삽입 SQL의 개념과 특징
C, C++, JAVA 등과 같은 프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL문
특징
- 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
- 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL문 앞에 EXEC SQL을 붙임
- 프로그램에 선언된 일반 변수를 삽입 SQL문에서 사용 가능. 단, SQL문에서 일반 변수를 사용할 때 앞에 콜론을 붙여 테이블 이름이나 속성의 이름과 구분함
커서: 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할
SELECT문의 수행 결과로 반환되는 여러 행을 프로그램에서 한꺼번에 처리할 수 없으므로 커서를 이용해 한 번에 한 행씩 차례로 처리해야 한다.
커서가 필요 없는 삽입 SQL
SQL문을 실행했을 때 결과 테이블을 반환하지 않는 CREATE TABLE문 INSERT문, DELETE문, UPDATE문
결과로 행 하나만 반환하는 SELECT문
= 커서가 필요 없다.
삽입 SQL에서 사용할 변수는 EXEC SQL BEGIN DECLARE SECTION과 EXEC SQL END DECLARE SECTION 사이에 미리 선언해야 한다.
커서가 필요한 삽입 SQL
SELECT문의 실행 결과로 여러 행이 검색되는 경우 커서가 필요
커서의 이름과 커서가 필요한 SELECT문으로 커서를 선언하는 삽입 SQL문
커서에 연결된 SELECT문을 실행하는 삽입 SQL문
OPEN 명령어로 실행되면 검색된 행들이 반환되고, 커서는 검색된 행들 중 첫 번째 행의 바로 앞에 위치하게 된다.
→ FETCH 명령어를 통해 커서를 이동시킨다.
커서를 이동해 처리할 다음 행을 가리키게 하고, 커서가 가리키는 행으로부터 속성 값들을 가져와 변수에 저장하는 FETCH문
일반적으로 반복문과 함께 사용
커서 사용을 종료할 때 사용하는 CLOSE문
출처: 데이터베이스 개론 2판 김연희 저, MySQL로 배우는 데이터베이스 개론과 실습 박우창 외2