카테고리 없음

[SQLD] 2과목 | 데이터베이스 (DB) 와 DBMS

wjdwwidz 2024. 11. 17. 17:41

데이터베이스 (DB) 와 DBMS

데이터베이스 : 데이터의 집합. 꼭 형식을 갖추지 않아도 엑셀 파일을 모아둔다면 그것 또한 데이터베이스
DBMS : 데이터를 효과적으로 관리하기 위한 시스템
- MYSQL, ORACLE

관계형 데이터베이스 구성 요소

계정 , 테이블, 스키마(특정 유저가 갖고 있는 모든 오브젝트, 테이블이 어떤 구조로 되어있는지 기본적인 구조를 정의)

테이블

정의 : 엑셀시트의 행과 열의 구조를 갖는 2차원 구조로 구성

컬럼은 속성이라고도 부름 (모델링 단계마다 부르는 용어가 다름)

특징 : 하나의 테이블은 반드시 하나의 유저 (계정) 소유여야 함

테이블 간의 관계는 일대일(1:1) , 일대다(1:N) , 다대다(N:N) 의 관계를 가질 수 있음
테이블명은 중복될 수 없지만, 소유자가 다른 경우 같은 이름으로 생성 가능
ex) SCOTT 소유의 EMP 테이블 존재, HR 소유의 EMP 테이블 생성 가능(같은 계정 내 동일한 객체명 생성 불가, 객체의 type이 다르면 가능)
테이블은 행 단위로 데이터가 입력, 삭제 되며 수정은 값 단위로 가능
사원 테이블에 새로운 사원 정보를 사원번호, 사원이름 등의 테이블 내 모든 컬럼의 값을 동시에 전달하며 입력, 삭제 시에는 해당 사원의 모든 정보가 삭제됨 (수정 시에는 특정 직원의 ‘급여’ (객체) 만 수정 가능), 값 삭제는 삭제가 아니라 null 로 업데이트 하는 것

SQL

관계형 데이트베이스에서 데이터 조회 및 조작, DBMS시스템 관리 기능을 명령하는 언어
대소문자 를 구문하지 않음

관계형 데이터베이스 특징

데이터의 분류, 정렬, 탐색 속도가 빠름
신뢰성이 높고, 데이터의 무결성 보장
기존의 작성된 스키마를 수정하기 어려움
데이터베이스의 부하를 분석하는 것이 어려움

데이터 무결성

데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정없이 없음을 보증하는 것

데이터 무결성 종류

개체 무결성 : 기본 key에 null, 기본키는 중복 할 수 없음
참조 무결성 : 외래키는 null 이거나 참조테이블의 기본키 값과 동일해야 한다.
도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값이어야 함
NULL 무결성 : 특정 속성에 대해 NULL 을 허용하지 않는 특징
고유 무결성 : 특정 속성에 대해 값이 중복되지 않는 특징
키 무결성 : 하나의 relation 에는 적어도 하나의 키가 존재해야 함

ERD

엔터티 , 관계, 속성이 있다.
현실 세계의 데이터는 이 3가지의 구성으로 모두 표현 가능

SELECT 문

DML : SELECT, INSERT, UPDATE, DELETE
DDL : CREATE, ALTER, DROP, TRUNCATE, RENAME
DCL : GRANT, REVOKE
TCL : COMMIT, ROLLBACK

select문 구조

SELECT FROM WHERE GROUPBY HAVING ORDER

실행은 from > where > group by > having > select > order by 순서대로 실행됨

SELECT 문 세부 사항

SELECT 절 주요 내용

DISTINCT: 중복된 데이터를 제거하고 고유한 값을 반환.
- 예: SELECT DISTINCT department FROM employees;
AS: 컬럼이나 테이블에 별칭(alias)을 부여.
- 예: SELECT first_name AS 이름, salary AS 급여 FROM employees;
함수 사용: 데이터를 가공하기 위해 다양한 내장 함수 사용.
- 예: SELECT UPPER(first_name) AS 대문자_이름 FROM employees;

FROM 절

데이터를 가져올 테이블이나 서브쿼리를 명시.
JOIN을 사용해 여러 테이블 간의 관계를 연결.
- INNER JOIN: 두 테이블 모두에서 조건이 일치하는 행만 반환.
- LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블의 조건 일치 행 반환.
- RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블의 조건 일치 행 반환.
- FULL OUTER JOIN: 양쪽 테이블의 모든 행을 반환하고 일치하지 않는 부분은 NULL.
```
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
```

WHERE 절

데이터 필터링 조건을 지정.

조건식: =(같음), <>(다름), >(크다), <(작다), BETWEEN, LIKE, IN, IS NULL 등.

SELECT first_name, salary
FROM employees
WHERE salary > 5000 AND department_id = 10;

GROUP BY 절

데이터를 그룹화하여 집계 함수(SUM, AVG, COUNT, MAX, MIN)와 함께 사용.

SELECT department_id, AVG(salary) AS 평균급여
FROM employees
GROUP BY department_id;

HAVING 절

GROUP BY로 그룹화된 데이터에 대한 조건 지정.
- WHERE와 다르게 집계 함수 조건을 사용할 수 있음.
```
SELECT department_id, COUNT(*) AS 직원수
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
```

ORDER BY 절

결과를 정렬. 기본은 오름차순(ASC), 내림차순은 DESC 사용.

SELECT first_name, salary
FROM employees
ORDER BY salary DESC, first_name ASC;

집계 함수

데이터를 요약, 분석할 때 사용.
- COUNT(): 레코드 개수 반환.
- SUM(): 숫자 컬럼의 합 반환.
- AVG(): 숫자 컬럼의 평균 반환.
- MAX(), MIN(): 최댓값 및 최솟값 반환.
```
SELECT COUNT(*) AS 직원수, AVG(salary) AS 평균급여
FROM employees;
```

서브쿼리 (Subquery)

하나의 쿼리 안에 포함된 또 다른 쿼리. ( ) 안에 작성.

단일 행 서브쿼리: 하나의 값 반환.

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

다중 행 서브쿼리: 여러 값을 반환. IN, ANY, ALL과 함께 사용.

SELECT first_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

인덱스 (Index)

데이터베이스의 검색 속도를 향상시키기 위해 사용하는 구조.
주로 Primary Key, Foreign Key 또는 자주 검색되는 컬럼에 생성.
단점: 너무 많은 인덱스는 삽입/삭제/갱신 작업의 성능 저하를 초래.

트랜잭션 (Transaction)

데이터베이스 작업의 최소 단위. 모든 작업이 성공해야 완료(COMMIT), 하나라도 실패 시 롤백(ROLLBACK).

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

ACID 속성:
- Atomicity (원자성): 트랜잭션의 작업이 전부 수행되거나 전혀 수행되지 않음.
- Consistency (일관성): 트랜잭션 완료 후 데이터가 일관된 상태를 유지.
- Isolation (독립성): 서로 다른 트랜잭션 간 간섭 방지.
- Durability (지속성): 완료된 트랜잭션은 영구적으로 반영.