01 데이터베이스 개요
01) 데이터베이스 소개
데이터베이스의 정의
데이터들의 집합 : 한 조직 안에서 여러 사용자와 응용 프로그램이 공동으로 사용하는 데이터들을 통합하여 저장하고 운영하는 데이터
02) DB종류
관계형 데이터베이스
- 데이터를 row, column을 가지는 테이블로 표현
- 테이블 간의 관계를 이용해 데이터를 효과적으로 관리하여 데이터의 무결성을 보장
- 정의된 테이블(스키마)에 맞게 데이터가 삽입되므로 데이터의 안정성을 보장
- 데이터의 구조가 일관적인 경우에 주로 사용
- MySQL, PostgreSQL, MariaDB 등
비관계형 데이터베이스
- 데이터를 저장할 수 있는 유형의 제한이 없음
- 새로운 유형의 데이터를 추가하기 용이
- 데이터의 구조가 일관적이지 안혹 자주 변경되는 경우에 적합
- 대용량의 데이터를 더 빠르게 처리할 수 있음
- MongoDB, Redis, Apache Cassandra 등
03) 관계형 DB만들기
SQL 작성 규칙
- SQL문법은 대문자로 작성 권장
- 테이블명, 속성명은 소문자로 작성권장
- 이름은 항상 의미가 잘 드러나도록 ( fn -> file_name)
- 이름에 여러 단어 혼합하는 경우 _ 를 이용해 구분
- 주석처리 : --
- 명령어 끝에 세미콜론(;) 작성
테이블 정의
CREATTE TABLE 테이블명(
속성1 데이터타입1 제약조건1
id int NOT NULL,
name varchar(16)
);-
DB 테이블 목록 확인
SHOW TABLES; -
테이블 구조 확인
DESC 테이블명; -
데이터 삽입
INSERT INTO 테이블명(속성1, 속성2, ...)
VALUES(값1, 값2,...);속성값을 넣지 않으면 DEFAULT === NULL;
속성의 순서는 중요하지 않음 속성과 속성값만 잘 대입하면 됨
모든 속성을 정의한 순서대로 입력하는 경우 속성 목록은 삭제 가능
데이터 출력
SELECT 속성1, 속성2,... FROM 테이블명;- My SQL 데이터타입 : https://dev.mysql.com/doc/refman/8.0/en/data-types.html
04) DB 정의어
테이블 수정하기
- 컬럼추가:
ALTER TABLE 테이블명ADD COLUMN 컬럼명데이터타입제약조건;- 컬럼수정:
ALTER TABLE 테이블명MODIFY COLUMN 컬럼명데이터타입제약조건;- 컬럼이름변경:
ALTER TABLE 테이블명CHANGE COLUMN 기존컬럼명새로운컬럼명데이터타입제약조건;- 컬럼삭제:
ALTER TABLE 테이블명DROP COLUMN 컬럼명;- 테이블이름변경:
ALTER TABLE 기존테이블명 RENAME 새로운 테이블명;테이블 삭제
DROP TABLE 테이블명;02 데이터베이스 구성하기
01) 제약 조건
제약조건으로 데이터 무결성 지킬 수 있음. -정확성, 일관성
NOT NULL
널 값 비허용 .
아무것도 안썼을 때 기본 값은 NULL
UNIQUE
중복값 비허용
널값은 비교 불가로 중복되어도 에러 발생 안함.
DEFAULT
기본값 설정
아무런 값을 지정하지 않으면 DEFAULT로 기입된 값이 적용되게함.
CHECK
값의 범위 제한하여 특정 값만 허용.
CREATE TABLE customer(
id VARCHAR(16) UNIQUE,
name VARCHAR(16) NOT NULL,
address VARCHAR(16) DEFAULT 'No Adress',
age INT CHECK (age>=19)
);제약조건 정의 (CONSTRAINT)
CONSTRAINT customer_id_unique UNIQUE (id),
CONSTRAINT 제약조건이름 제약조건 (적용할 속성);- 생성된 제약 조건 확인
SELECT * FROM information_schema.table_constraints;제약조건 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 제약조건 (적용할 속성);
ALTER TABLE customer
ADD CONSTRAINT cusomter_chk_2 CHECK (age>=20 AND name='메리');- DAFAULT제약조건 수정
ALTER TABLE 테이블명
ALTER 속성명 SET DEFAULT 지정할기본값내용;제약조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;- DEFAULT 제약조건 삭제
ALTER TABLE 테이블명
ALTER 속성명 DROP DEFAULT;02) KEY
조건에 만족 튜플을 찾거나, 정렬할 때 기준이 되는 속성
기본키 (PRIMARY KEY)
서로다른 튜플을 유일하게 식별할 수 있는 기준이 되는 속성 (예 : ID)
중복값 불가 .
널값 불가
테이블당 1개만 설정가능!
외래키(FOREIGN KEY)
다른 테이블의 기본키를 참조하는 속성으로 테이블의 관계를 정의
참조가 되는 테이블의 기본키에 없은 값은 참조할 테이블에서 가져올 수 없음
FOREIGN KEY (참조할 속성) REFERENCES 참조되는테이블(참조되는 속성)
CONSTRAINT oredr_history_pk PRIMARY KEY (customer_id, order_id);
FOREIGN KEY(customer_id) REFERENCES customer(id);
-- customer테이블의 id값을 참조해옴
-- 고객이 두개 이상의 주문을 했을 수도 있는경우 생김 두개 이상의 속성을 primary key로 지정하려면 괄호로 묶어줌.
출처:엘리스트랙 강의노트
무결성 제약조건
-
개체 무결성 : 기본키는 널값과 중복된 값을 가질 수 없음 (NOT NULL + UNIQUE)
-
참조 무결성 : 외래키는 널이거나 참조되는 릴레이션의 기본키 값과 동일
-
도메인 무결성 : 특정 속성값은 그 속성이 정의된 도메인에 속한 값이어야 함
-
NULL 무결성 : 특정 속성값은 널 값을 가질 수 없음 (반드시 받아야하는 값은 NOT NULL이어야함)
-
고유 무결성 : 각 튜플이 가지는 속성값들은 서로달라야 함 -> 기본키가짐
-
키 무결성: 테이블에 최소 한 개 이상의 키 존재
03) 데이터 모델링
현실 세계의 데이터를 단순화, 추상화하여 표현한 모델
- 개체(Entity) : 데이터로 표현하려고 하는 현실 세계의 개념이나 정보의 단위
( 예 킥보드 , 회원) - 명사 , 인스턴스 - 속성 : 개체에 대한 정보
- 관계 : 개체 간의 연관성 (고객은 킥보드를 대여한다. (동사))
과정 :
개념적 설계 : 현실 세계를 추상적개념으로 표현 . 도식화하여 표현
-> 논리적 설계 : DBMS가 처리할 수 있는 스키마 설계
-> 물리적 설계 : DBMS에 터에블을 저장할 구조를 설계
04) ER다이어그램(Peter Chen, IE)
Entity-Relationship diagram
03 데이터 모델링
01) 이상현상과 정규화
이상현상(Anomaly)
: 잘못된 DB 설계로 발생하는 오류
- 삽입 이상 : 데이터를 삽입할 때 불필요한 내용까지 삽입해야 하는 문제
- 갱신 이상 : 중복된 데이터 중 일부만 갱신되어 발생하는 문제 (갱신된 일부 데이터 불일치)
- 삭제 이상: 어떤 데이터를 삭제할 때 다른 유용한 정보도 함께 삭제되는 문제
--> 발생원인 : 함수 종속성
어떤 속성 A의 한 값이 다른 속성 B에 속한 하나의 값에만 매핑되는 경우,
A를 결정자, B를 종속자라고 함 (A -> B)
완전 함수 종속, 부분함수 종속, 이행 함수 종속 등이 있음.
정규화
이상 현상을 제거하기 위해 DB를 구조화 하는 과정
- 데이터 간의 종속성을 제거하여 중복되는 데이터를 줄임
- 데이터의 일관성과 무결성을 보장
02) 1차 정규화(1NF)
ERD를 통해 최종 형태의 DB를 설계하는 것이 아니라 지속해서 변경해 나가야 함
- 테이블의 컬럼이 하나의 값만 갖도록 도메인을 원자값(더 이상 나눌 수 없는 가장 작은 값)으로 설정하는 과정
=== 도메인을 원자값으로 설정하는 과정
03) 2차 정규화
이상현상이 발생하는 원인은?
부분함수 종속
기본키를 구성하는 속성 중 일부가 결정자 역할을 하는 경우
기본기가 복합키 or 후보키가 결정자인 경우
완전 함수 종속
기본키를 구성하는 속성 모두가 결정자 역할을 하는 경우
부분함수 종속을 제거하고 완전 함수 종속이 되도록 테이블을 분해하는 과정
04) 3차 정규화
이행 함수 종속
X -> Y (결정자 -> 종속자), Y -> Z 라는 종속 관계가 있을 때, X -> Z가 성립하는 경우
이행 함수 종속을 제거하도록 테이블을 분해하는 과정
05) BCNF
(boyce-code normal form)
결정자이면서 후보키가 아닌것 제거
역정규화
정규화된 DB의 성능을 개선하기 위해 다시 통합하여 구조를 재구성하는 것
정규화를 통해 테이블을 분해하면 여러 테이블을 조인할 때 성능이 크게 떨어질 수 있음
따라서, 자주 사용되는 쿼리를 고려하여, 데이터 중복을 감안해 역정규화를 하는 것이 효율적일 수 있다.
04 데이터베이스 구현
- 관리자모드로 cdm open
: ctrl + r -> cmd -> 열기 - cmd에서 mysql 연결
mysql -u root -p-> 비번 : ****
데이터 제어어(DCL :Data Control Language)
데이터베이스에 접근하는 권한을 관리하는 등의 데이터 제어
- GRANT : DB에 권한 부여
- REVOKE : DB 권한 회수
트랜잭션 : DB를 조작하는 작업의 단위, DCL에서 트랜젝션을 제어하는 명령어를 따로 Transaction control language)라고 분류하기도 함.
- COMMIT : 트랜잭션 작업을 반영하여 저장
- ROLLBACK : 트랜잭션 작업을 취소하여 이전 상태로 돌림
-- 로컬에서 접속하는 유저 생성, 비번1234로 해줌 .
GRAND SELECT ON shared_kickboard.* TO js@localhost;
-- SELECT 권한 부여 sh~~~디비의 모든 테이블 을 js@localhost에게..
FLUSH PRIVILEGES;
-- 여기까지 해서 마무리
\q;
루트에서 부여해준 권한 보기
REVOKE SELECT ON shared_kickboard.* FROM merry@localhost;
-- 부여해준 권한 회수하기05) 인덱스
DB테이블의 검색 속도를 향상 시키기 위한 자료구조
원하는 데이터를 쉽게 찾아갈 수 있음.
단, 모든 데이터를 조회할 때는 인덱스가 불필요함
인덱스 사용 전략
- 규모가 큰 테이블
- 데이터의 삽입, 수정, 삭제 작업이 많지 않은 컬럼
- WHERE 조건절이나 ORDER BY(정렬), JOIN을 자주 하는 컬럼
- 데이터의 중복도가 낮은 컬럼
(인덱스 이름) (테이블이름)
CREATE INDEX customer_index ON customer (phone_number);
-- 인덱스 추가
ALTER TABLE customer DROP INDEX customer_index;
-- 인덱스 삭제쿼리 수행 속도 비교하는 법
SET profiling = 1;
-- 쿼리 수행속도를 확인하기 위한 명령어
-- ...쿼리문 작성...
SHOW prifiles;
-- 쿼리 수행속도를 Duration에서 비교해볼 수 있는 테이블 보여줌 