[Database] 데이터베이스 기본적인 개념정리 (2)

sunbun·2023년 9월 2일

Database SQLD WIL db 딥다이브

Database

목록 보기

2/2

SQL Advanced in MariaDB/MySQL (1)

테이블 생성 CREATE문

Create, Alter, Drop문의 특징
- Database 테이블 생성, 수정, 삭제를 위하여 사용
CREATE Database 기본 문법
- Create Database 데이터베이스_이름;
- Drop Database 데이터베이스_이름;

데이터의 형식

데이터의 형식 - 정수
데이터의 형식 - 실수
- float이나 double은 입력한 값과 가장 비슷한 값 저장함
- 정확한 숫자를 입력할 때는 decimal을 써야함

데이터의 형식 - 문자열
- Char-set에 따라 한 문자당 소요되는 byte 수가 달라짐

데이터의 형식 - 날짜와 시간 형식

stamp같은 경우는 몇 가지 특성만 알아두면 된다!
- 날짜(연도-월-일)
- 날짜시간(연도-월-일- 시:분:초)
- 시간(시:분:초)
- 날짜(연도)

테이블 생성 시 주의사항
- 반드시 수강2 테이블 생성 시 과목2 테이블과 학생2 테이블 먼저 만들어 둬야함!
- 해당 테이블에서 학번과 과목번호는 각 학생2의 학번, 과목2의 과목번호와 기본키 PRIMARY KEY가 같아야 한다.
  - 속성뿐만 아니라, 자릿수도 맞춰주면 좋음!
- FK는 행 입력할 때 외래키가 참조하는 값들이 있어야 한다.

DROP TABLE문의 예제

사용자 및 권한 관리의 설정

계정 생성: CREATE USER

CREATE USER 사용자_계정 IDENTIFIED BY '비밀번호';

CREATE USER 명령문의 예제
```
CREATE USER 'user1'@'127.1.1.1' IDENTIFIED BY '1111';     #1
CREATE USER 'user2'@'localhost' IDENTIFIED BY '2222';     #2
CREATE USER 'user3'@'192.182.10.2' IDENTIFIED BY '3333';  #3
CREATE USER 'user4'@'%' IDENTIFIED BY '4444';             #4
```
1. user1은 로컬 호스트에서만 접속 허용, 암호 1111
2. user2도 로컬 호스트에서만 접속 허용, 암호 2222
3. 해당 IP에서만 user3라는 사용자 접속 가능, 암호 3333
  1. 외부 해킹 가능성으로 인해 직접 권한 안 줌!
4. user4 사용자는 어느 호스트에서 들어온 접속도 권한을 주지 않았음
SELECT USER(); 는 현재 접속 사용자 확인, SELECT * FROM USER; 는 전체 리스트 보기

권한의 부여

GRANT문의 형식 - 지정한 사용자 계정에게 권한을 지정할 때

GRANT 권한_내용 ON 권한_대상 TO 사용자_계정
GRANT 권한_내용 ON 권한_대상 TO 사용자_계정 WITH GRANT OPTION;

GRANT문의 예제

GRANT INSERT, UPDATE, DELETE ON univDB.* TO 'user1'@'127.1.1.1';
# SELECT 없어서 입력, 삭제, 수정만 가능하고 조회 불가능!

GRANT ALL ON ** TO 'user4'@'%' WITH GRANT OPTION;
# ALL = 다 받는다..

GRANT SELECT ON univDB.학생 TO 'user2'@'localhost'

SHOW GRANTS FOR 'user1'@'127.1.1.1';

SHOW GRANTS;

뷰(View)

실제 데이터를 저장하지 않는 가상 테이블(Virtual Table) = 데이터베이스를 바라보는 창문(window)
- 미리보기정도의 역할
뷰에 대해 사용자가 질의를 요청하면, DBMS는 뷰 정의 참조하여 질의 수행, 그 결과 반환
- 각 사용자별로 서로 다르게 볼 수 있도록 하는 것
주로 기반 테이블로부터 정의, 또 다른 뷰 기반으로 정의 가능

장점

편의성 - 복잡한 질의문 작성이 쉽고 간단해짐
보안성 - 데이터 보안 유지가 쉬움
재사용성 - 반복되는 질의문 작성에 효율적
독립성 - 스키마 변경에도 뷰 질의문 변경 필요 X

VIEW를 통한 데이터 변경에서 뷰 변경 제한되는 이유
- 뷰 검색은 제한 없이 가능한 반면 뷰 변경은 특정한 경우에만 가능
  (여기 적힌 뷰들은 뷰를 통한 데이터 변경이 불가능한 경우, 입력/수정/삭제 제한)
  - 기반 테이블의 PK를 포함하지 않은 뷰
  - 집계 함수(계산) 결과를 포함하는 뷰
  - Distinct를 적용하거나 Group by 절을 적용한 경우
  - 다수의 기반 테이블의 조인을 통해 생성된 뷰
  - 뷰에 포함되지 않은 기반 테이블의 열이 NN(Not Null)인 경우
- 뷰 변경이 제한되는 이유
  - 뷰를 정의하는 ‘SELECT_검색문’의 조건이 다양하기 때문
  - 뷰가 참조하는 기반 테이블을 변경해야 함 → 내부 처리가 어려운 경우는 변경이 허용되지 않음

인덱스의 개념

인덱스
- 테이블 안의 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터베이스 객체
- 책 페이지를 목차나 색인을 통해 쉽게 찾는 것과 같은 원리
필요성과 목적
- SQL 실행 시, 디스크 접근 횟수를 줄여 검색 속도를 높임
- 대부분 DBMS는 B-트리(Balanced Tree) 구조의 인덱스를 지원
  
  [MySQL] B-tree, B+tree란? (인덱스와 연관지어서)
  
  헷갈리면 참고하기 좋을 것 같은 포스팅 임베드.
- 루트 노드로부터 리프 노드까지의 탐색 길이가 같아서 모든 데이터에 대한 일정 수준의 검색 시간 보장

CREATE INDEX

명령문 형식
1. 인덱스를 역순으로 생성
2. 테이블의 열값에 중복이 없는 유일한 인덱스를 만드는 명령(해당 열에서 중복된 값이 허용되지 않음)
3. 열값의 정렬 방식을 지정

예제

CREATE INDEX idx_수강
	ON 수강(학번, 과목번호) ;

CREATE UNIQUE INDEX idx_과목
	ON 과목(이름 ASC);

SHOW INDEX FROM 수강;

DROP INDEX

인덱스 삭제를 위한 DROP INDEX 명령문의 형식
```
DROP INDEX 인덱스_이름 ON 테이블_이름 ;
```

DROP INDEX 적용 예

DROP INDEX idx_수강 ON 수강;              # 1
ALTER TABLE 과목 DROP INDEX idx_과목 ;    # 2

주의 사항

불필요한 인덱스는 오히려 성능을 떨어뜨리고 저장 공간만 낭비
꼭 필요한 만큼만 효과적으로 생성해야 함
- 적은 수의 record는 굳이 index 하지않음
하나의 테이블에 3-5개 정도의 인덱스 생성 권장
인덱스 생성이 필요한 경우!
- 기본키와 외래키 (대부분의 DBMS는 자동으로 인덱스 생성)
- WHERE나 조인 조건식 또는 ORDER BY나 GROUP BY에 자주 사용되는 테이블 열
  - 기준으로 자꾸 이걸 불러올 때!
- 가변길이 문자형이나 실수형, 날짜형 열보다는 정수형, 고정길이 문자형 열
  - 실수형과 날짜열은 진짜 잘 안 쓰긴 함
인덱스 생성이 불필요한 경우
- 갱신이 빈번한 테이블 열의 경우, 인덱스가 바람직하지 않음
  - update 多
- 집계 함수, 내장 함수를 적용하여 열 값을 변형하는 경우
  - e.g.) where절이 count나 sum인 경우
- ‘성별’ 같은 열처럼 도메인이 작아서 열의 선택도(selectivity)가 높을 경우
- 범위 검색을 하는 경우
- 테이블의 행 개수가 별로 없는 경우