[정보처리기사] SQL 응용 : 데이터베이스 기본(3)

 

1️⃣ 트랜잭션(Transaction)

☆☆☆

 

---

(3) DML(Data Manipulation Language, 데이터 조작어)

 

1. 데이터 조작어의 개념

 

• DB에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

---

2. DML 명령어

 

< DML의 유형 >

유형	동작	설명
SELECT	조회	테이블 내 컬럼에 저장된 데이터를 조회
INSERT	삽입	테이블 내 컬럼에 데이터를 추가
UPDATE	수정	테이블 내 컬럼에 저장된 데이터를 수정
DELETE	삭제	테이블 내 컬럼에 저장된 데이터를 삭제

 

---

3. SELECT(데이터 조회) 명령어

 

⓵ SELECT 명령어의 개념

 

• 데이터의 내용을 조회할 때 사용하는 명령어

SELECT [ALL | DISTINCT] 속성명1, 속성명2...
  FROM 테이블명1, ...
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC] ];

구분	설명
SELECT 절	• 검색하고자 하는 속성명, 계산식을 기술 • 속성명 별칭은 AS를 사용하며 생략 가능함 • 2개 이상의 테이블을 대상으로 검색할 때는 '테이블명.속성명'으로 표현 • 술어 부문은 ALL이 기본값  - ALL : 모든 튜플을 검색할 때 사용하고, SELECT 뒤에 명시하지 않은 경우 ALL로 인식  - DISTINCT : 중복된 속성이 조회될 경우 그중 1개만 검색(SELECT 뒤에 명시된 속성이 중복될 경우 1개만 검색)
FROM 절	질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
WHERE 절	검색할 조건을 기술
GROUP BY 절	속성값을 그룹으로 분류하고자할 때 사용
HAVING 절	GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정
ORDER BY 절	속성값을 정렬하고자할 때(ASC : 오름차순, DESC : 내림차순, ASC/DESC 키워드 생략 시 오름차순 정렬) 사용

 

  ㉮ SELECT 절 : 테이블에서 출력할 컬럼을 명시하기 위해 사용

 

 ■ 예 

이름	과목	학점
철수	C 언어	A
유리	자료구조	A
짱구	자료구조	A
훈이	알고리즘	B

SELECT 과목 FROM 성적;	성적 테이블에서 과목을 모두 출력
SELECT DISTINCT 과목 FROM 성적;	성적 테이블에서 과목 중에 중복된 값을 제거하여 출력
SELECT 과목 FROM 성적 WHERE 학점 = 'A';	성적 테이블에서 학점이 'A'인 과목을 모두 출력
SELECT DISTINCT 과목 FROM 성적 WHERE 학점 = 'A';	성적 테이블에서 과목 중에 중복된 값을 제거하여 출력
SELECT COUNT(DISTINCT 과목) FROM 성적;	성적 테이블에서 과목 중에 중복된 값을 제거한 튜플의 개수를 출력
SELECT DISTINCT 과목, 학점 FROM 성적;	성적 테이블에서 과목, 학점 두 컬럼이 모두 같은 값이면 제거하여 출력

  ※ SELECT 절에서 「컬럼명 AS 명칭」  형태로 쓰면 결과 출력 시 컬럼명이 별칭으로 표기되고, 추가로 AS는 생략 가능함

---

  ㉯ WHERE 절 

 

< WHERE 절 문법 >

구분	연산자	설명
비교	=	값이 같은 경우 조회
	<>, !=	값이 다른 경우 조회
	<, <=, >, >=	비교 연산에 해당하는 데이터 조회
범위	BETWEEN	• 값1 보다 크거나 같고, 값2 보다 작거나 같은 데이터 조회
집합	IN	• 컬럼이 IN 안에 포함된 경우의 데이터 조회
	NOT IN	• 컬럼이 IN 안에 포함되지 않은 경우의 데이터 조회
패턴	LIKE	• 컬럼이 패턴에 포함된 경우의 데이터 조회  - % : 0개 이상의 문자열과 일치  - [ ] : 1개의 문자와 일치  - [^] : 1개의 문자와 불일치  - _ : 특정 위치의 1개의 문자와 일치
NULL	IS NULL	• 컬럼이 NULL인 데이터 조회
	IS NOT NULL	• 컬럼이 NULL이 아닌 데이터 조회
복합조건	AND	• 조건1과 조건2 모두를 만족하는 데이터 조회
	OR	• 조건1과 조건2 둘 중 하나를 만족하는 데이터 조회
	NOT	• 조건에 해당하지 않는 데이터 조회

 ■ 예

BETWEEN	SELECT * FROM PRODUCT WHERE PRICE BETWEEN 50000 AND 80000; → 상품(PRODUCT) 테이블에서 가격(PRICE)이 50000보다 크거나 같고 80000보다 작거나 같은 튜플을 조회
IN	SELECT * FROM PRODUCT WHERE PRICE IN(40000, 50000, 60000); → 상품(PRODUCT) 테이블에서 가격(PRICE)이 40000 또는 50000 또는 60000인 튜플을 조회
LIKE	SELECT * FROM PRODUCT WHERE NAME LIKE '정보%'; → 상품(PRODUCT) 테이블에서 이름(NAME)이 '정보'로 시작되는 문자열 검색
	SELECT * FROM PRODUCT WHERE NAME LIKE '[ABCD]&'; → 첫 번째 문자가 'A' 또는 'B' 또는 'C' 또는 'D'인 문자열과 일치하는 문자열 검색
IS NULL	SELECT * FROM PRODUCT WHERE PRICE IS NULL; → 상품(PRODUCT) 테이블에서 가격(PRICE)이 NULL 값인 경우의 튜플을 조회

 

---

  ㉰ GROUP BY 절 : 속성값을 그룹으로 분류하고자할 때 사용함

 

 ■ 예

이름	직책	부서	급여
철수	차장	마케팅	5000
유리	차장	전산	4800
짱구	사원	마케팅	2500
훈이	사원	마케팅	2700

SELECT 직책, COUNT(직책), SUM(급여) FROM 급여 GROUP BY 직책;	GROUP BY 절에 명시된 직책을 기준으로 그룹을 묶었을 때 직책별 건수(COUNT)와 급여 합계(SUM)를 출력
SELECT 직책, SUM(급여) AS 급여합계 FROM 급여 GROUP BY 부서;	GROUP BY 절에 명시된 부서를 기준으로 그룹을 묶었을 때, 부서별 급여 합계(SUM)를 출력
SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급여 GROUP BY 직책, 부서;	GROUP BY 절에 명시된 직책, 부서를 기준으로 그룹을 묶었을 때, 각 직책, 부서에 대한 급여 합계(SUM)를 출력
SELECT COUNT(*) FROM 급여;	GROUP BY 절이 없으면 절체 테이블이 하나의 그룹이 되고, 그룹에 해당하는 총 튜플의 수(COUNT)를 출력

---

  ㉱ HAVING 절 : GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 때 사용함

 

 ■ 예

이름	직책	부서	급여
철수	차장	마케팅	5000
유리	차장	전산	4800
짱구	사원	마케팅	2500
훈이	사원	마케팅	2700

SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급여 GROUP BY 직책, 부서 HAVING SUM(급여) >= 5000;

GROUP BY 절에 명시도니 직책, 부서를 기준으로 그룹을 묶었을 때 각 직책, 부서에 대한 급여합계가 5000 이상인 급여합계를 출력

 

---

  ㉲ ORDER BY 절 : 속성값을 정렬하고자할 때 사용함

 

 ■ 예

이름	과목	학점
철수	C 언어	A
유리	자료구조	A
짱구	자료구조	A
훈이	알고리즘	B

SELECT * FROM 성적 ORDER BY 이름;	• ORDER BY 절에 ASC와 DESC가 명시되어 있지 않아 ASC가 기본값 • 이름에 대해 오름차순 정렬 • 문자열일 경우 가나다순으로 정렬
SELECT * FROM 성적 ORDER BY 과목, 이름;	ORDER BY 절에 2개 이상의 속성이 있는 경우 먼저 선언된 속성으로 정렬 후, 같은 값일 때 다음 속성으로 정렬
SELECT * FROM 성적 ORDER BY 학점 DESC 이름 ASC;	• ORDER BY 절에 2개 이상의 속성에 대해서 오름차순과 내림차순을 섞어서 사용할 수 있음 • 학점은 내림차순으로 정렬한 후 같은 값일 때 이름을 오름차순으로 정렬

 

---

⓶ 조인(Join)

 

  ㉮ 조인의 개념

 

• 2개 이상의 테이블을 연결하여 데이터를 검색하는 방법
• 두 릴레이션으로부터 관련된 튜플들을 결합하여 하나의 튜플로 만드는 가장 대표적인 데이터 연결 방법

< 논리적 조인 유형(상세) >

유형	설명
내부 조인(Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출하는 기법
외부 조인 (Outer Join)	• 왼쪽 외부 조인(Left Outer Join) : 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출하는 기법 • 오른쪽 외부 조인(Right Outer Join) : 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출하는 기법 • 완전 외부 조인(Full Outer Join) : 양쪽의 모든 데이터를 추출하는 기법
교차 조인(Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출하는 기법
셀프 조인(Sell Join)	자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

---

  ㉯ 조인의 유형

 

내부 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];	• 같은 이름의 컬럼이 여러 테이블에 있을 경우 '별칭.컬럼명' 형태로 명시 • INNER 라는 키워드는 생략해도 내부 조인이 됨 • 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정
왼쪽 외부 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A LEFT [OUTER]JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];	• OUTER라는 키워드는 생략해도 왼쪽 외부 조인이 됨 • 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정
오른쪽 외부 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];	• OUTER라는 키워드는 생략해도 오른쪽 외부 조인이 됨 • 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정
완전 외부 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];	• OUTER 라는 키워드는 생략해도 완전 외부 조인이 됨 • 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정
교차 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A CROSS JOIN 테이블2 B	조인 조건이 없는 모든 데이터 조합을 추출하기 때문에 ON 절이 없음
셀프 조인	SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블1 B ON 조인조건 [WHERE 검색조건];	• 같은 테이블명을 쓰고 별칭만 A, B와 같이 다르게 함 • 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정

■ 예

내부 조인
SELECT A.책번호, A.책명, B.가격 FROM 도서 A JOIN 도서가격B ON A.책번호 = B.책번호;	'도서' 테이블은 A, '도서가격' 테이블은 B라는 명칭으로 설정 후 책번호가 같은 것끼리 조인하여 출력

왼쪽 외부 조인
SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A LEFT JOIN 도서가격B  ON A.책번호 = B.책번호;	'도서' 테이블은 A, '도서가격' 테이블은 B라는 별칭으로 설정 후 왼쪽에 위치한 '도서' 테이블 기준으로 왼쪽 외부 조인하여 출력

오른쪽 외부 조인
SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A RIGHT JOIN 도서가격B ON A.책번호 = B.책번호;	'도서' 테이블은 A, '도서가격' 테이블은 B라는 별칭으로 설정 후 오른쪽에 위치한 '도서가격' 테이블 기준으로 오른쪽 외부 조인하여 출력

완전 외부 조인
SELECT A.책번호, A.책명, B.책번호,  B.가격 FROM 도서 A FULL JOIN 도서가격B ON A.책번호 = B.책번호;	'도서' 테이블은 A, '도서가격' 테이블은 B라는 별칭으로 설정 후 완전 외부 조인하여 출력

교차 조인
SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A CROSS JOIN 도서가격B;	'도서' 테이블은 A, '도서가격' 테이블은 B라는 별칭으로 설정 후 조건 설정 없이 교차 조인하여 출력

셀프 조인
SELECT A.책번호, A.책명, B.책번호, B.책명 FROM 도서 A JOIN 도서 B ON A.선수과목_책번호 = B.책번호;	'도서' 테이블은 A, B라는 별칭으로 따로 설정 후, 테이블 내의 다른 컬럼을 조건으로 두어 셀프 조인하여 출력