RDBMS (관계형 데이터 베이스)

유진·2023년 1월 12일
DatabaseRDBMSmysql
1

1월 11일 (수) - 모각소 1주차

RDBMS

정의 : 열(속성), 행(레코드, 튜플)으로 이루어진 정보를 저장하는 테이블

주요 기능 : 테이블 Join

  • 기본키 (필수, 1개) → 유일한 값, 데이터를 구분할 수 있음
    • 고유하게 식별됨
  • 외래키 (선택, 여러 개 가능)
    • 기본키가 외래키도 될 수 있음

이점

  • 직관적인 데이터 표현 방법을 제공
  • 관련 데이터 포인트에 쉽게 접근 가능
  • 대량의 구조화된 데이터를 관리해야 하는 조직에서 많이 사용
    • 인벤토리 추적, 트랜잭션 데이터 처리, 애플리케이션 로깅
  1. 유연성
    1. 전체 데이터베이스 구조를 변경하거나 기존 애플리케이션에 영향을 주지 않고 테이블, 관계를 추가/삭제하고 데이터를 변경할 수 있다.
  2. ACID 규정 준수
    1. 원자성, 일관성, 격리, 내구성 성능 지원
    2. 오류, 실패, 기타 잠재적 오작동에 관계없이 데이터 유효성을 검사할 수 있다.
  3. 사용 편의성
    1. 사용자도 데이터베이스와 상호작용하는 방법을 배울 수 있는 SQL을 사용하여 복잡한 쿼리를 쉽게 실행할 수 있다.
  4. 공동작업
    1. 여러 사용자가 동시에 데이터를 운영하고 액세스할 수 있다.
    2. 잠금 기능 (lock)으로 업데이트 도중 데이터에 동시에 액세스할 수 없다.
  5. 내장된 보안 기능
    1. 역할 기반 보안을 통해 데이터 액세스가 특정 사용자로 제한될 수 있다.
  6. 데이터베이스 정규화
    1. 정규화 : 데이터 중복성을 줄이고 데이터 무결성을 개선

DATA TYPE

  • CHAR : 고정 길이
  • VARCHAR : 가변 길이
  • BLOB
  • SMALLINT
  • INT : 정수형
  • FLOAT : 부동 소수형
  • DOUBLE : 부동 소수형
  • DECIMAL : 고정 소수형
  • DATE : YYYY-MM-DD
  • DATETIME : YYYY-MM-DD hh:mm:ss
  • TIMESTAMP : 자동으로 날짜, 시간 저장
  • TIME : 00:00:00
  • BOOLEAN : TRUE, FALSE

SQL문

Database

1. 조회 (SHOW)

SHOW DATABASES;

2. 사용 (USE)

USE 데이터베이스_이름;

3. 생성 (CREATE)

CREATE 데이터베이스_이름;

4. 삭제 (DROP DATABASE)

DROP DATABASE 데이터베이스_이름;

Table

1. 생성

CREATE TABLE 테이블_이름(
	Field명 DataType(데이터 자리수) 조건;
	Field명 DataType(데이터 자리수) 조건;
	Field명 DataType(데이터 자리수) 조건;
	PRIMARY KEY(Field명)
);

CREATE TABLE topic(
	id INT(11) NOT NULL AUTO_INCREMENT;
  title VARCHAR(100) NOT NULL;
  description TEXT NULL;
  created DATETIME NOT NULL;
  author VARCHAR(15) NULL;
  profile VARCHAR(200) NULL;
  PRIMARY KEY(id)
 );

2. 테이블 정보 조회

DESCRIBE 테이블_이름;
DESC 테이블_이름;

3. 테이블 삭제

DROP TABLE 테이블_이름;

4. 필드 추가

ALTER TABLE 테이블_이름 ADD 필드_이름 필드_타입;

ALTER TABLE Reservation ADD Phone INT;

5. 필드 삭제

ALTER TABLE 테이블_이름 DROP 필드_이름;

ALTER TABLE Reservation DROP RoomNum;

6. 필드 타입 변경

(INT → DATE, CHAR→INT 이런 경우)

ALTER TABLE 테이블_이름 MODIFY COLUMN 필드_이름 필드_타입;

ALTER TABLE Reservation MODIFY COLUMN ReserveDate VARCHAR(20);

Record

1. 레코드 기본 조회

SELECT * FROM 테이블_이름;

2. 레코드 조건 조회 (WHERE)

SELECT * FROM 테이블_이름 **WHERE field="내용"**;

SELECT * FROM Customers WHERE country='KR';
SELECT * FROM Customers WHERE id='asd111';

SELECT **COUNT(*)** FROM 테이블명 WHERE 필드명='내용'; 
// 해당 필드에서 값이 '내용'인 레코드 개수

SELECT * FROM 테이블이름 WHERE 필드명 **LIKE '%문자열%'**; 
// 문자열 포함된 레코드 조회

SELECT * FROM 테이블이름 WHERE 필드명 **IN (A, B)**; 
// 괄호 내의 일치하는 값이 있다면 조회

정렬 (ORDERED BY)

SELECT * FROM Costomers ORDER BY age; 
// 오름차순 정렬 (Default)

SELECT * FROM Costomers ORDER BY age DESC; 
// 내림차순 정렬

중복 제거 (DISTINCT)

SELECT DISTINCT 필드명 FROM 테이블이름; // 해당 필드 기준으로 중복된 레코드제거

예시

// Info 데이터베이스의 Users 테이블에서 userName이 'A'로 시작하는 레코드들의 userId, userName 필드 조회
SELECT userId, userName FROM Info.Users WHERE userName LIKE'A%';

// Customers 테이블에서 age가 25인 레코드의 개수 조회
SELECT COUNT(*) FROM Customers Where age='25';

3. 레코드 추가 (INSERT INTO, VALUES)

INSERT INTO 테이블_이름(필드1, 필드2) VALUES(필드1, 필드2);

모든 필드 복사

INSERT INTO Dest_테이블_이름 SELECT * FROM Source_테이블_이름;

지정 필드 복사

INSERT INTO Dest_테이블_이름(필드1, 필드2, 필드3) 
	SELECT 필드1, 필드2, 필드4 FROM Source_테이블_이름;

예시

// Customers 테이블에 userName은 John, age는 25인 레코드와 userName은 coco, age는 27인 레코드 추가
INSERT INTO Customers(userName, age) VALUES ('John', 25), ('coco', 27);

4. 레코드 수정 (UPDATAE SET WHERE)

UPDATE 테이블_이름 SET 필드1=값1, 필드2=값2 WHERE 조건;

예시

// Reservation 테이블에서 Name 필드 값이 '홍길동'인 
// 모든 레코드의 RoomNum의 값을 2002로 변경
UPDATE Reservation SET RoomNum=2002 WHERE Name='홍길동';

5. 레코드 삭제 (DELETE)

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

예시

// Reservation 테이블에서 Name 필드 값이 '홍길동'인 모든 레코드를 삭제
DELETE FROM Reservation WHERE Name='홍길동';

Join

1. 왼쪽 외부 조인

SELECT * FROM A LEFT JOIN B ON A.key = B.key;

2. 왼쪽 안티 조인

SELECT * FROM A 
LEFT JOIN B ON A.key = B.key 
WHERE B.key IS NULL;

3. 오른쪽 외부 조인

SELECT * FROM A RIGHT JOIN B ON A.key = B.key

4. 오른쪽 안티 조인

SELECT * FROM A 
RIGHT JOIN B ON A.key = B.key
WHERE A.key IS NULL

5. 내부 조인 (교집합)

SELECT * FROM A
INNER JOIN B ON A.key = B.key

6. 전체 조인 (합집합)

SELECT * FROM A
FULL OUTER JOIN B ON A.key = B.key

7. 합집합 - 교집합

SELECT * FROM A
FULL OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL **OR** B.key IS NULL

집계함수

→ NULL값 제외

1. COUNT (행의 개수)

2. SUM (합계)

3. AVG (평균)

4. MAX (최댓값)

5. MIN (최솟값)

6. DISTINCT (중복 행 제거)

// birthYear, birthMonth, birthday 값이 동일한 행 제외
SELECT **DISTINCT birthYear, birthMonth, birthday**
FROM Players
ORDER BY birthYear;

// COUNT 연산 후에 DISTINCT 연산 실행
SELECT DISTINCT COUNT(birthday)
FROM Players;

// birthday 값 중복 행 제외한 후에 COUNT 연산
SELECT COUNT(DISTINCT birthday)
FROM Players;

GROUP BY

  • 특정 행을 기준으로 집계함수를 사용하여 데이터를 추출할 때 사용
  • 지정한 열에서 같은 값을 가진 행끼리 묶어 그룹화한 집합을 넘겨줄수 있다
// sample 테이블에서 name으로 묶어서 name을 조회
SELECT name FROM sample GROUP BY name;
// name 값을 기준으로 그룹화되므로 DISTINCT와 같음
  • GROUP BY절에서 지정한 열 이외의 열은 집계함수를 사용해야만 SELECT구에 사용할 수 있음
// ERROR
SELECT name, age, grade FROM sample GROUP BY name;

// 정상
SELECT name, SUM(age), AVG(grade) FROM sample GROUP BY name;

HAVING 절 : 조건

// emp 테이블에서 deptno 값이 20 또는 30인 행의 job값과 sal의 합계를
// job을 기준으로 sal의 합계가 5000 초과인 행 조회
SELECT job, SUM(sal) AS sum_sal, AVG(sal) AS avg_sal
FROM emp
WHERE deptno IN (20, 30)
GROUP BY job HAVING SUM(sal) > 5000 AND AVG(sal) > 2000
// HAVING절에서 AND, OR 연산자를 사용하여 여러 조건을 부여할 수 있음

서브쿼리

  • SELECT에 의한 데이터 질의
  • 하부의 부수적인 질의
  • 괄호로 묶어서 지정
  • 주로 WHERE에 사용

예시

// sample 테이블에서 a의 최솟값을 가진 행을 삭제
DELETE FROM sample 
WHERE a=(SELECT MIN(a) FROM sample);

// 위와 같은 결과
// 서브쿼리를 변수로 사용할 수 있음
va = (SELECT MIN(a) FROM sample);
DELETE FROM sample WHERE a = va;
// a 값을 모두 최댓값으로 수정
UPDATE sample SET a = (SELECT MAX(a) FROM sample);
// sample1과 sample2의 행의 갯수를 sample에 넣는다
INSERT INTO sample VALUES(
(SELECT COUNT(*) FROM sample1),
(SELECT COUNT(*) FROM sample2)
);

상관 서브쿼리

  • EXISTS (sub query)
  • NO EXISTS (sub query)
  • 서브쿼리가 반환하는 값이 있는지 확인
// sample2에 no열의 값과 같은 행이 있으면 
// sample1 테이블의 a값을 '있음'으로 갱신
UPDATE sample1 SET a = '있음'
WHERE EXISTS (
		SELECT * FROM sample2 WHERE no2 = no
);

UPDATE sample1 SET a = '있음'
WHERE EXISTS (
		SELECT * FROM sample2 WHERE sample2.no2 = sample1.no
);

데이터베이스 Key 종류

  • 후보키 : 유일성과 최소성을 만족하는 키
    • 유일성 : 해당 키로 하나의 튜플을 식별할 수 있음
    • 최소성 : 꼭 필요한 속성으로만 이루어짐
  • 기본키 : 후보키 중 하나
    • Null 값을 가질 수 없음
    • 동일한 값을 가질 수 없음
  • 대체키/보조키 : 후보키-기본키
  • 외래키 : 다른 릴레이션의 속성
    • 참조 관계를 표현하는 데에 쓰임
  • 슈퍼키 : 유일성은 만족하지만 최소성은 만족하지 못하는 키

JOIN

  • 두 가지 이상의 릴레이션을 연결해서 데이터를 조회하는 방법
  1. 내부 조인
    1. 동등 조인
      1. 자연 조인 : 동일한 컬럼 명을 가진 테이블에서 모든 컬럼 비교
      2. 교차 조인 : 곱집합 반환, 모든 경우의 수 (M*N)
  2. 외부 조인 : 특정 테이블의 데이터가 모두 필요한 경우
    1. Left Outer Join
    2. Right Outer Join
  3. 셀프 조인 : 자기 자신 + 자기 자신

스키마

DB의 구조와 제약조건에 관한 명세를 기술한 것

정규화

: 중복을 최소화하기 위해 데이터를 분해하는 작업

  • 장점
    • 이상 현상 방지
      • 삽입 이상
      • 갱신 이상
      • 삭제 이상
  • 단점
    • 릴레이션 간의 연산이 많아질 수 있다.

반정규화

성능 향상을 위해 중복을 통합하는 기법

무결성 보존을 위해 적당한 정규화를 진행해야 함

  • 제 1 정규형
    • 도메인이 원자 값만을 가짐
  • 제 2 정규형
    • 완전 함수적 종속
  • 제 3 정규형
    • 기본키에 대해 이행적 종속 제거
  • BCNF 정규형
    • 모든 결정키가 후보키

함수적 종속성

X→Y : 릴레이션 R에서 X값을 알면 Y를 알 수 있고, X 값에 의해 Y 값이 달라질 때


인덱스

  • 검색 연산의 속도 높이는 방법
  • 항상 정렬된 상태 유지 → 탐색 빠름
  • 데이터 삽입/삭제/수정 시에 추가적인 작업 필요 → 실행 속도 느림
  • 저장 성능 희생 but 읽기 속도 높임

사용하는 자료구조

  • B Tree : 칼럼의 값을 변형하지 않고 원래의 값으로 인덱싱, 등호 뿐만 아니라 부등호 연산에도 적용 가능
    • 이진 트리 확장버전 → 가질 수 있는 최대 자식 node 2개 이상
    • 항상 정렬된 상태로 저장
    • 특정 노드의 데이터가 K개 → 자식 노드의 개수 = K+1
    • 왼쪽 서브 트리는 작은 값, 오른쪽 서브 트리는 큰 값
  • Hash : 해시 값을 이용한 인덱싱

클러스터드 인덱스 vs 비클러스터드 인덱스

클러스터드 인덱스

  • 테이블당 하나만 생성 가능
  • 인덱스로 지정된 열에 맞춰 자동 정렬됨

비클러스터드 인덱스

  • 테이블당 여러 개 생성 가능

트랜잭션

  • 데이터베이스의 상태를 변경시키는 작업의 단위

4가지 특징 ACID

  • A : 원자성
    • 트랜잭션의 연산은 모두 반영되어야 함. 하나라도 실패하면 모두 취소되어야 함
  • C : 일관성
    • 트랜잭션을 성공하면 언제나 일관성있는 DB상태로 변화함
  • I : 독립성, 격리성
    • 둘 이상의 트랜잭션이 동시에 수행되는 경우, 다른 트랜잭션의 연산에 끼어들 수 없음
  • D : 지속성
    • 완료된 트랜잭션은 영구적으로 반영되어야 한다.

Commit & Rollback

Commit

  • 모든 작업을 정상적으로 처리하겠다고 확정하는 명령
  • 실제 DB에 저장
  • Commit 수행 후 하나의 트랜잭션을 종료

Rollback

  • 작업 중 문제가 발생 → 변경사항을 취소하고 트랜잭션 종류

  • 이전 Commit까지 복구

  • 장점 : 데이터 무결성 보장

profile
유진
다음 포스트

DFS & BFS

0개의 댓글