[TOC]
데이터 베이스 3
1. 관계 대수
릴레이션을 내부적으로 처리하기 위한 연산들의 집합
집합연산
- 합집합 시 양쪽 릴레이션에 동일한 투플이 있다면 한번만 표시
- 카티션 프로덕트
- 차수 : 속성의 수
- 카디널리티 : 투플의 수
관계 연산
릴레이션의 구조적 특성에 기반을 둔 연산을 포함
- 셀렉트
- 특정 투플을 추출
- 연산자는 시그마(σ)
- 프로젝트
- 특정 속성을 추출
- 연산자는 파이(Π)
- 조인
- 두 릴레이션의 공통 속성을 기준으로 조인 조건을 만족하는 투플을 수평으로 결합
- 연산자 ⋈
세타 조인
- 조인_조건식에 비교연산자 사용
동등 조인
- 조인 조건식에 ''='' 비교연산자를 사용하는 세타조인
- 자연 조인
- 동등 조인 결과 중에서 중복 속성을 자동 제거한 조인(학번이 2번 들어갈 것을 한번으로)
- 연산자 ⋈N
- 디비전(division)
- 특정 값들을 모두 가지고 있는 투플을 찾는 연산
- 두 릴레이션 R1과 R2의 디비전 R1÷R2가 반환하는 것은 R2의 모든 투플에 연관된 R1의 투플 중에서 R2에 속한 속성을 제외한 나머지 속성 값만으로 구성된 릴레이션
- R2의 모양을 그대로 도장찍을 수 있는 부분이 R1에 있다면 R2와 같은 부분을 제외한 속성값만 출력
- 연산자는 ÷
확장 연산
- 자연 조인의 확장된 형태인 세미 조인 연산과 외부 조인 연산
- 합집합의 확장된 형태인 외부 합집합 연산
세미 조인
자연 조인이 반환하는 결과 릴레이션 중에서 한쪽 릴레이션 속성만으로 한정하여 반환하 는 제한적 자연 조인 연산
중복은 제거됨
연산자는 ⋉ 또는 ⋊
왼쪽 세미 조인
- 오른쪽 세미 조인
외부 조인
자연 조인 결과에 포함되지 않는, 조인에 실패한 투플까지 모두 포함하도록 확장한 연산
자연 조인의 확장된 형태
대응 속성 없이 추가된 투플들은 널 값을 채워서 반환
완전 외부 조인(⟗)
왼쪽 외부 조인(⟕)
- 오른쪽 외부 조인(⟖)
- 외부 합집합
- 합병 가능하지 않은(정확히 말하면 부분적으로만 합병 가능한) 두 릴레이션의 투플을 합병
- 대응하는 속성이 없는 경우도 널 값을 채워 모든 투플을 결과 릴레이션에 포함
관계 대수의 활용
DBMS는 SQL 질의문을 처리하기 위해 내부적으로 관계 대수식을 스스로 작성하고 연산 자를 순서대로 적용함으로써 질의 결과를 생성
예시
질의 트리
생성된 후보 관계 대수식은 DBMS 안의 질의 최적화 과정을 위해 질의트리로 변환
최적의 관계 대수식을 결정하기 위한 DBMS 내부의 질의 트리 작성과 최적화 과정
질의 최적화
연산 순서를 조정함으로써 연산으로 생기는 중간 릴레이션의 크기를 최소화하는 것
최적화 기본 규칙
1. AND 연산자로 연결된 셀럭트 연산(σ)은 분리 => 개별 셀럭트 연산(σ)으로
2. 셀럭트 연산(σ)은 가능한 먼저 실행
3. 프로젝트 연산(Π)도 프로젝트 속성을 분리해서 개별 프로젝트 연산으로 변환, 가능한 먼저 실행
4. 여러 셀럭트 연산(σ) 중에서 결과 릴레이션의 크기가 가장 작은 것 부터 제한적 셀럭트 연산 순으로 질의 트리 최하단으로 이동
5. 카티션 프로덕트 연산(×)과 바로 위의 셀럭트 연산(σ)은 하나의 조인 연산(⋈)으로 통합 변환
6. OR 연산자로 연결된 조건식은 가능하면 AND연산자로 연결된 조건식으로 변환
2. SQL 기초
관계형 데이터베이스 표준 언어로서 가장 많이 사용되는 데이터 언어
univDB
스키마
DB 내용
실습 준비
1. 관리자 계정 생성
CREATE USER 'manager'@'%' IDENTIFIED BY '1234';2. 생성한 manager 관리자 계정에 권한 부여
GRANT ALL ON *.* TO 'manager'@'%'WITH GRANT OPTION;3. 관리자 계정으로 접속
SELECT user();
SHOW DATABASES;4. UnivDB 데이터베이스 생성
3. DML
SELECT
행 검색
테이블로부터 데이터를 검색하기 위해서는 SELECT문 사용
# 테이블의 특정 열 검색
SELECT 컬럼명 FROM 테이블명;
# 테이블의 모든 열 검색
SELECT * FROM 테이블명;
# 중복행을 제거하고 검색 : DISTINCT
SELECT DISTINCT 컬럼명 FROM 테이블명;
# 조건 검색: WHERE절
SELECT 컬럼명 FROM 학생 WHERE 조건식;
SELECT 이름 FROM 학생 WHERE (학년>=2 AND 학년<=4) OR NOT(소속학과 = '컴퓨터');
SELECT 이름 FROM 학생 WHERE (학년 BETWEEN 1 AND 3) OR NOT(소속학과 = '컴퓨터');
# 순서화 검색: ORDER BY절
SELECT 컬럼명 FROM 테이블명 WHERE 조건식 ORDER BY 정렬컬럼명 [ASC|DESC];
SELECT * FROM 학생 ORDER BY 학년 ASC, 이름 DESC; //1차로 학년으로 오름차순, 2차로 이름으로 내림차순
# 그룹화 검색: GROUP BY절
# 검색할 대상을 먼저 구분하고 각각 그룹에 대한 검색을 함
SELECT 컬럼명 FROM 테이블명 GROUP BY 그룹기준열
SELECT 나이, COUNT(*) AS '나이별 학생수' FROM 학생
WHERE 나이>=20 and 나이<30
GROUP BY 나이;
# 그룹 조건 검색: HAVING 절
#GROUP BY절을 통해 생성된 그룹 중에서도 특정 조건을 만족하는 그룹만으로 검색을 제한
SELECT 컬럼명 FROM 테이블명 GROUP BY 그룹기준열 HAVING 그룹조건식
SELECT 학년, COUNT(*) AS '학년별 학생수'
FROM 학생
GROUP BY 학년
HAVING COUNT(*)>=2📌별칭(alias)
서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법
EMPLOYEE.DNO
FROM EMPLOYEE AS E, DEPARTMENT AS D집계 함수(aggregate function)
실제 테이블 저장 값이 아닌 행의 개수(count) 또는 특정 열의 값 평균(average)을 구하 는 질의가 필요할 경우를 위해 SQL은 집계 함수를 제공
집계 함수
각 열에 대한 기본 통계 결과를 반환하는 함수
COUNT( ), MAX( ), MIN( ), SUM( ), AVG( )
LIKE 문자 연산자 검색 : _ % 와일드카드
부분 문자열(substring) 포함 여부를 검색하기 위해 WHERE절 ‘검색_조건식’에 사용되 는 비교 연산자
검색 문자열 값을 정확히 모르거나 일부만 알고 있는 경우
_ : 문자열 중 특정 위치에 1개의 모든 문자를 허용
% : 문자열 중 임의 위치에 0개 이상의 모든 문자(열)을 허용
SELECT 학번, 이름 FROM 학생 WHERE 이름 LIKE '이__';널 값 검색 : IS NULL, IS NOT NULL
=으로는 검색 불가
- NULL이 포함된 산술식 연산의 결과 = NULL
- NULL이 포함된 행은 대부분 집계함수 결과에서 제외
- NULL값을 일반 비교연산자로 비교하면 결과는 항상 FALSE
집합 연산자를 이용한 검색 : UNION
서로 다른 SQL 검색문의 결과 집합이 의미적으로 또 형식적으로 일치할 경우 관계 대수의 집합 연산자를 적용
- UNION(합집합) 연산자
- 여러 SELECT 명령문의 검색 결과를 결합
- 결과 테이블에서 자동으로 중복 행을 제거한 나머지 행들을 반환
<br>
<br>
<br>
#여학생이거나 'A'학점을 받은 학생의 학번 검색
SELECT 학번 FROM 학생
WHERE 성별 = '여'
UNION
SELECT 학번 FROM 수강
WHERE 평가학접 = 'A';subquery를 이용한 검색
- 질의문 안에 중첩(nested)되어 포함된 또 다른 SELECT 검색문
- 괄호 안의 가장 안쪽 subquery부터 먼저 수행되고 가장 바깥쪽 주 질의문이 가장 나중에 수행
- WHERE절의 검색 조건 값을 미리 알지 못하거나 실시간으로 조건식을 완성해야 하는 경우 유용
- 내부 질의(inner query): 안쪽에 위치한 subquery
- 외부 질의(outer query) 또는 주 질의(main query): 바깥쪽 주 질의문
SELECT 이름 FROM 학생
WHERE 학번 IN (SELECT 학번 FROM 수강 WHERE 과목번호='c002');
<br>
<br>
<br>
# 중첩 subquery
# 정보보호 과목을 수강한 학생의 이름을 검색
SELECT 이름 FROM 학생
WHERE 학번 IN (SELECT 학번
FROM 수강
WHERE 과목번호 = (SELECT 과목번호 FROM 과목
WHERE 이름 = '정보보호'));IN 연산자
나열된 값들 중에서 하나라도 일치하는 경우 참(true)을 반환하는 연산자
- NOT IN은 나열된 값들 중 어떤 값도 일치하지 않을 경우 참을 반환
- 여러 값들과의 반복 비교를 위해 ‘=’ 기호를 나열할 필요 없이 한꺼번에 표현하도록 함
SELECT 이름 FROM 학생
WHERE 학번 IN('s001', 's003', 's004');EXISTS 연산자
subquery의 실행 결과로 반환되는 행이 존재하는지 존재 유무를 확인하는 연산자
SELECT 이름 FROM 학생
WHERE EXISTS (SELECT *
FROM 수강
WHERE 수강.학번=학생.학번 AND 과목번호 = 'c002')ORDER BY
사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시됨
- 디폴트는 오름차순(ASC), DESC를 지정하여 정렬순서를 내림차순으로 설정가능
SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE
WHERE DNO = 2
ORDER BY SALARY;조인 검색: JOIN
둘 이상의 테이블로부터 연관된 행들의 결합을 통해서 검색 결과를 생성
크로스 조인(cross join)
‘조인_조건식’ 없이 이루어진 조인
관계 대수의 카티션 프로덕트 연산을 적용한 결과(두 테이블을 곱한 결과)를 반환
대부분의 행이 의미 없는 기계적인 결합임
동등 조인(equi join)
‘조인_조건식’에 ‘=’ 연산자를 사용하는 동등 조건에 의한 조인
두 테이블 행들 사이의 의미 있는 조합만을 검색
크로스 조인의 결과 테이블 중에서 의미 없는 조합을 제외함으로써 관계 대수의 동등 조인 연산을 적용한 결과를 반환
셀프 조인(self join)
특정 테이블에 속하는 행들을 같은 테이블에 속하는 행들과 조인하는 것
하나의 테이블은 테이블 별칭을 다르게 지정함으로써 마치 복제된 두 테이블이 존재 하는 것처럼 조인이 가능
외부 조인
조인 연습
#학생 중에서 과목번호가 'c002'인 과목을 수강한 학생의 학번과 이름, 과목번호 그리고 변환중간성적(학생별 중#간 성적의 10% 가산점수)을 검색하시오
SELECT 학번, 이름, 과목번호, 중간성적+(중간성적*0.1) AS 변환중간성적
FROM 학생 JOIN 수강 ON 학생.학번 = 수강.학번
WHERE 과목번호 = 'c002'
#학생 중에서 '정보보호'과목을 수강한 학생의 학번과 이름, 과목번호를 검색하시오
SELECT 학번, 이름, 과목번호
FROM (학생 JOIN 수강 ON 학생.학번 = 수강.학번) JOIN 과목 ON 수강.과목번호 = 과목.과목번호
WHERE 과목.이름 = '정보보호'
#학생중에서 과목번호가 'c002'인 과목을 수강한 학생의 이름, 과목번호를 검색하시오
SELECT 이름, 과목번호
FROM 학생 AS S, 수강 E
WHERE S.학번 = E.학번 AND 과목번호 = 'C002'INSERT
'학생1'테이블에 학번 'g001', 이름 '김연아1', 주소 '서울 서초', 학년 4, 나이 23, 성별 '여', 휴대폰번호 '010-1111-2222', 소속학과 '컴퓨터'인 학생 정보를 삽입하시오.
INSERT INTO 학생1
VALUES ('g001', '김연아1', '서울 서초', 4, 23, '여', '010-1111-2222', '컴퓨터')#SQL SELECT 결과를 INSERT 하기
INSERT INTO username_history (user_id, user_name) SELECT user_id, user_name FROM userUPDATE
행 수정
#단일 행, 단일 열 수정
UPDATE 학생1
SET 학년=3
WHERE 이름 = '이은진';
#복수 행, 복수 열 수정
UPDATE 학생1
SET 학년=학년+1, 소속학과='자유전공학부'
WHERE 학년=4;
#subquery를 이용한 수정
UPDATE 학생1
SET 소속학과 = NULL
WHERE 학번 NOT IN(SELECT 학번
FROM 수강1);
UPDATE 학생1
SET 학번 = (SELECT 학번
FROM 학생1
WHERE 이름 = '이은진')
WHERE 학번='s003'DELETE
행 삭제
DELETE FROM 학생1
WHERE 이름='송윤아';
DELETE FROM 학생1
WHERE 학년=3;
DELETE FROM 과목1
WHERE 과목번호 IN
(SELECT 과목번호
FROM 수강1
GROUP BY 과목번호
HAVING COUNT(*)<2);
# 모든 행 삭제
DELETE FROM 학생1;