[DB] 정리

Data Base

• 데이터를 영구적으로 저장 할 수 있는 저장소
• DB를 이용하면 서로 다른 시스템 간에 데이터를 공유 할 수 있다.

📌 Data Base 를 관리 하는 시스템을 DBMS 라고 한다.

📌 User 만들기 와 Right 생성

• Root는 모든 권한을 가지고 있다.
• Root를 통해 사용자의 명령어 실행 및 접근 권한을 조정 할 수 있다.

📌 Table

• JAVA에서의 Class 와 같다고 볼 수 있다.
• 최초의 기둥(Column)을 잡아두고 행(Row)을 하나씩 채워 간다.
• EX) 액셀 시트 하나가 하나의 테이블이다.
  _ EX) 액셀 시트를 모아둔게 DB 이다.

DDL [Data Definr Language]

• DB Table 등을 생성하는 언어
• DB 목록 보기 [ Show databases; ]
• MYDB라는 데이터 베이스 생성 [ Creare database MYDB; ]
• DB Table 생성 [Create table [테이블명]([컬럼명][데이터타입](크기)...];

📌 데이터타입 종류

📖 자주 사용하는 일부

1. 문자타입

   • 고정형 : 한번 정해진 크기가 변하지 않음 ( 2글자로 선언 했으나 1글자로 오면 남은 한자리는 공백 처리 [ Char(바이트수); ]
   • 가변형 : 정해진 크기보다 적게 들어오면 알아서 크기를 줄여준다 [ Varchar(바이트수); ]

2. 숫자 타입 : Int , Long, Float, Double

3. Boolean

4. 날짜타입

   • Date : 0000-00-00
   • DateTime : 0000-00-00 00:00:00
   • Timestamp : DateTime 과 같지만 Time Zone 에 따라 시간 변경

📖 Table 구조 보기 : [ desc [ 테이블명 ]; ]
📖 Table 삭제 : [ drop table [ 테이블 명]; ]

📌 Column 이름 변경

• Alter table [ 테이블명 ] RENAME column [ 변경전 이름 ] to [ 변경 후 이름 ];
  (주의) ★ 변경 시 기존 데이터가 삭제 된다.

📌 Column 추가

• Alter table [ 테이블 명 ] ADD Column ([컬럼명][데이터타입](크기));

📌 Column 속성 변경

• Alter table [ 테이블 명 ] MODIFY Column [컬러명][테이터타입](크기);

 EX) ALTER TABLE employees MODIFY COLUMN commision float(4,2); 
 -- float(전체자릿수,소숫점자릿수) -> 00.00

📌 Column 삭제

• Alter table [테이블명] DROP Column [컬럼명];

DML [ Data Manipulation Language ]

• DB 나 Table 등을 생성하는 언어 ( 명령어 )
• (select, insert, Update, delete, upsert)
• 데이터를 취급 하는 구문을 우리는 DML 이라고 한다.

📌 Insert [ 데이터 삽입 ]

• insert into [테이블명] values(컬럼명에 넣을 값...);

📌 Delete [ 데이터 삭제 ]

• Delete from [테이블명] where [조건]

📌 Update [ 데이터 수정 ]

• Update [테이블명] SET [컬럼] = [값] where[조건]
  EX) dept_no 가 dev005 인 row 에 대해서 commsion 을 30 으로 변경

 Update employees set commision = 30 where depart_no = 'dev005';

📖 데이터 수정 및 삭제 시 where 라는 조건이 필요 조건이 없으면 테이블에 있는 모든 내용이 삭제, 수정 되기 떄문

📌 Select [ 데이터 조회 ] 📌

---

📌 데이터 조회 : 특정 컬럼 조회, 산술, 문자열 합침

• DB 에서는 특정한 데이터를 선택(select) 하여 볼 수 있다.
• 세밀한 선택을 하기 위해서는 조건문을 사용 할 수 도 있다.

---

• 📖 Select [조회할 컬럼] From [테이블명];
• 📖 숫자로 된 컬럼에 대해서는 계산해서 볼여 줄 수 있다. ( 실제로 계산되는 건 아님)
• 📖 문자열을 합칠 수 있디. 다만 데이터 타입이 문자열이여야 한다. / 숫자 - 숫자 불가능 문자-숫자는 가능

---

• ★ 이 모든건 실제 데이터를 가공해서 보여줄 뿐이지 조작 되진 않는다.

---

📌 특정 조건의 데이터를 조회 / and, or, beteen

• Select [조회할 컬럼] from [테이블] where [조건]

select from employees where family_name = '김'; - if(name == '김')
select from employees where salary > 3000000; - if(salaray>3000000)

• AND [ && ]

 - salary >= 1000000 && salary <= 3000000 자바 버전
 - salary >= 1000000 AND salary <= 3000000 DB 버전

select * from employees where salary >= 1000000 AND salary <= 3000000;

• OR [ || ]

select * from employees where family_name = '김' or salary = 2000000;

• Between And ( 특정값 사이를 구할 떄 사용 )

select first_name, family_name, salary from employees where salary between 500000 and 4000000;

부등호가 특수문자로 인식되는 경우가 있어서 피하고자 할떄 종종 사용한다.

📌 Deistinct [ 중복제거 ]

• Select Deistinct [출력할 컬럼] From [테이블]

select distinct family_name from employees where salary = 2000000;

• deistinct 은 하나의 컬럼만 사용 할때 가장 정확한 효과가 나타난다.

📌 IN ( or 하고 같음 )

select * from employees where family_name = 
'김' or family_name = '이' or family_name = '박';

• OR 로 비교하는 칼럼이 모두 같을 경우 사용한다. ( 속도도 훨씬 빠르다. )

select * from employees where family_name IN ('김', '이', '박');

📌 Is null || Is not null

commision 에서 null 인 값을 찾아라 
select from employees where commision is null; 

commision 에서 null 인 아닌 값을 찾아라
select from employees where commision is not null;

📌 Like

• 일부 비슷한 문자열에만 검색
• 숫자에는 대충 비슷하다라는 개념이 없다.
• 검색 기능을 사용할떄 가장 많이 사용

 where [컬러명] like '%[문자열]%'

• 남발할 경우 속도 저하가 심하다.

ze% : ze 로 시작하지만 뒤에는 아무거나 와도 된다. -> ze 로 시작하는...
%com : 앞에 아무거나 와도 되지만 뒤는 com 으로 끝나야 한다. -> com 으로 끝나는...
%se% : se 앞뒤에 아무거나 와도 된다. -> se 를 포함하는...
%s%e% : s 와 e 사이에 아무거나 와도 된다. -> s 또는 e 를 포함하는...

Transaction

• DB 에서 사용되는 쪼갤 수 없는 업무처리의 단위
• 하나의 작업 내부의 과정이 잘못되면 전부 취소 된다. EX) 송금

📌 Roll Back

• 모든 작업을 다시 돌려 놓겠다는 명령어

📌 Commit

• 모든 작업을 정상적으로 처리하겠다는 명령어

★ Commit과 Roll back은 데이터(DML)에 한정된다.
DCL, DDL 은 되돌릴 수 없다.

📌 Autocommit 확인 방법

• auto commit 설정 여부 -> select @@autocommit;

• set autocommit = 1; -- autocommit 설정 on

• set autocommit = 0; -- autocommit 설정 off

📌 Constraint [ 제약 조건 ]

• 제약조건은 컬럼에 데이터를 넣을 경우 특정한 조건을 만족시켜야 할 경우 사용한다.

📌 Primary Key [ 기본키 제약 조건 ] = PK

• 📖 중복과 null을 허용하지 않음
• 📖 테이블 종속적이다. [ 하나의 테이블에서 하나의 PK ]
• 하나의 컬럼으로 하나의 PK를 만들 수 있지만 여러개의 컬럼을 합쳐서 만들 수 있다. ( 최대 16개 )

📌 PK 생성 방법

• table 생성 시 할때 생성 ( 가장 많이 활용 )

create table pktest(first_col int(3) primary key, second_col varchar(4)
);

• table 생성 후 추가 : alter table [테이블명] add constraint 제약조건 타입 ;

📌 Combination Key [ 복합키 ]

• 하나의 컬럼으로 하나의 기본키를 구성하는 것이 가장 좋으나 어쩔 수 없는 경우 다수의 컬럼을 조합해서 기본키를 생성
• 외래키를 이용해 복합키를 만드는 경우가 있는데, 이 경우 Update 나 insert에 제한을 주므로 사용하지 않는 것이 좋다.

📌 Not null

• null을 허용하지 않음
• not null은 제약조건이지만 속성으로 취급한다.
• alter table [테이블명] Modify 컬러명;

  alter table pk_test modify second_col varchar(4) not null;

• 제약조건 확인 : select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = [테이블명]

 select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'pk_tow_test';

• 제약조건 삭제 : alter table [테이블] Drop [제약조건 종류];

 alter table pk_tow_test drop Primary key;

📌 RDBMS [ Relation Data Base Mangerment System ]

• 관계형 DB

📌 참조 제약 조건 [ 외래키 제약조건 ]

• Table 생성 시 추가
• Table 생성 후 추가 :
  alter table [테이블명] add constraint [제약조건종류] refernces [가져올테이블](가져올 컬럼)

 alter table child_table add constraint foreign key(user_id) references parent_table(user_id);

• 부모 자식 관계란 부모의 기본키를 자식이 가져다 쓰는 관계이다.
• 이때 부모의 기본키를 나의 기본키로 사용하는 경우를 식별관계라고 한다.
• 그렇지 않을 경우는 비식별 관계 라고 한다.
• 식별 관계는 1:1 관계에서 사용한다.

📌 연계 참조 무결성 제약조건

• 부모자식간에 논리적으로 앞뒤가 맞아야한다는 제약조건
  - 참조한 상태로 논리적으로 연결되어 있다.
• 부모한테 없는 값을 외래키에 넣으려고 하면 에러가 나타난다.
  - 왜? 연계 참조 무결성 제약조건이 깨지므로
  • 부모테이블의 기본키는 User04가 없지만, 자식이 그것을 쓰려고한다
    - 부모 없는 자식은 없다.

📌 On delete cascade 옵션

- 📖 연계 참조 무결성을 해치지 않고 삭제 하려면 자식이 먼저 삭제되고, 이후에 부모가 삭제 되어야한다.

• 이 과정을 자동으로 수행해주는 On delete cascade 옵션이 있다.
• 단, 테이블 생성 시 옵션을 넣어주어햐 한다.

📌 Unique 제약조건

• 중복을 허용하지 않는 제약조건
• 테이블에 다수 존재 할 수 있다.
• Uique + not null = PK : 기본키가 없다고 하면 PK가 될 수 있다.
  - 그래서 후보키라고도 불리운다 ( PK가 될 수 잇는 조건을 갖춤 )
  

  alter table [테이블명] add constraint [제약조건종류](컬럼명)

📌 Check 제약조건

• 조건이 맞지 않으면 받지 않는 제약조건
• 주의 사항 : 조건은 기존 데이터가 만족해야할 조건을 걸어야 한다.
• 안쓰는 이유 : 조건의 유연성이 떨어짐, 조건 변경시 DDL 을 수정해야한다.
  - 이럴 경우 자바에서 먼저 수정해주면 된다.
  
• ※ 어떤 조건이 걸려 있는지 파악하기 쉽지 않기에 잘 사용하지 않음

Sub Query

• 쿼리 안의 쿼리 라는 뜻
• 사전에 추출된 내용에서 재검색
• 검색 된 내용을 가상 컬럼을 만들어 추가 가능

📌 내가 사용한 쿼리를 조건문으로 사용

📌 상하관계 쿼리

• A-B table 합쳐서 보여줄때는 필수적으로 상하관계 쿼리를 사용 단, 쿼리의 양이 많아진다.

Join

📌 Join 이란?

• 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
• 두개의 table 이 join 하기 위해서는 적어도 하나의 공통된 컬럼이 존재해야 한다.
• join은 일반적으로 공통점이 있는 부모자식관계에서 잘 이루어진다.
  ( 공통 값만 있다면 Join은 가능하다.)

📌 Join의 종류

• Cross Join, 📖Equi Join(등가조인, 내부조인) 등 4가지
• non equi Join ( 거의 안쓰기 떄문 다루지 않음)
• self Join, 📖Outer Join

📌 Cross Join [ 거의 안씀 ]

• 두개의 테이블을 카다시안 곱을 수행 -> 경우의 수를 판단
• from [tableA] cross join [tableB]
• (,) 로 cross Join은 생략 가능
• ※ Cross join 은 아직 정제되지 않은 순수한 Join 형태

📌 Equi Join 📌

• 가장 일반적으로 사용하는 Join
• 양 쪽 테이블의 값이 있어야 보여준다.

📌 Equi Join 등가조인

• 가장 많이 사용 / 가장 편함
• 📖 (=을 사용)

select e.ename,d.deptname from emp e , dept d where e.deptno = d.deptno; 

📌 Equi Join 내부조인 📌

 사용법 : from [tableA] inner join [tableB]

• inner 는 생략 가능하며 Join을 위한 조건은 where가 아닌 on을 사용
• Using 을 사용하여 조건을 줄 수 있다.
• Using(서브쿼리)가 가능하다. -> 가장 큰 장점
• 📖 서브쿼리로 가져온 공통된 값을 Join을 통해 값을 뽑아 올 수 있다.

📌 Equi Join 내츄럴조인

• 📖 네츄럴 조인은 두 테이블 간 공통되는 칼럼이 있으면 자연스럽게 합쳐진다.
• 그래서 Join을 위한 조건절이 필요 없다.
• 📖 주의 사항 : 공통되는 컬럼명은 단축명을 사용하지 않는다
  ( 다른 DB에서 에러 날 수 있다.)

📌 Self join

• Equi Join과 똑같다. 다만 두개의 테이블이 둘 다 자신이라는 것만 다르다.
• 📖 그래서 self Join을 하면 두 데이터 간 카다시안 곱을 수행

📌 Outer Join 외부조인 📌

• 정보(종류)의 불균형이 있을 때 사용 [📖?]
• [tableA][left | right] outer join [tableB] on 조건절
• left ( 왼쪽을 기준으로 더 있는 값을 보여줌 )
• right ( 오른쪽을 기준으로 더 있는 값을 보여줌 )
• full ( 서로 없는 값을 보여줌 )
• 📖 outer 는 생략 가능

select e.deptno ,e.ename , d.deptname from emp e left join dept d on e.deptno = d.deptno ;

📌 left Join

• Join을 기준으로 왼쪽 테이블의 데이터를 기준으로 보여준다
• 오른쪽에 없는 내용은 null로 표시

📌 right Join

• Join을 기준으로 오른쪽 테이블의 데이터를 기준으로 보여준다
• 오른쪽에 없는 내용은 null로 표시

📌 Full Outer Join

• 서로 없는 내용을 보여 줄 수 있는 방법
• 📖 maria DB 에서는 지원하고 있지 않음

📌 Left Join 과 Right Join 을 Union 하면 Full Outer Join 효과 📌

select e.deptno, e.ename , d.deptname from emp e left join dept d on e.deptno = d.deptno
	union
select d.deptno, e.ename , d.deptname from emp e right join dept d on e.deptno = d.deptno ;

• 📖 없는 내용은 null로 처리 된다.

Set

• 다수의 테이블을 집합 연산 할 수 있다.

📌 Set 종류

• 합집합 ( Union, Union All )
• 교집합 ( Intersect )
• 차집합 ( Minus ) -> maria DB 에서는 사용 X

📌 Union

• 중복을 제거한 합집합 : 중복을 제거해주는 대신 성능 저하가 심함

📌 Union All

• 중복 제거 없이 합집합을 실행
• 📖 Union All은 순수하게 실행한다기 보다는 정제해서 사용한다.
  : 📖 Union All 이후에 중복제거 추가 등

📌 Intersect

• 양 테이블에 존재하는 중복된 데이터만 확인 한다.

📌 Miuns

• Maria DB 에서는 차집합은 지원하지 않음

📖 성능 저하로 인한 Union 대체 방법 📖

• 우선 Union All로 중복 상관 없이 전체 통합
• 이후 합친 내용을 가지고 중복 제거

Index

📌 Index 란? [ 개념중요 ]

• 검색을 빠르게 하기 위한 수단
• 📖 PK 와 Unique Key가 지정되면 따로 Index가 생성된다.

📌 Index 장단점

• 장점 : 검색 속도가 빨라지며, 시스템 부하를 줄여 성능향상
• 단점 : 인덱스 생성에 시간과 공간 필요, 삽입/갱신/삭제가 빈번히 일어날 경우 성능 저하

📌 Index 의 필요/ 불필요

• 필요 : 데이터가 많을 경우, Join이 많이 사용된 경우
• 불필요 : 검색이 없을 경우, 데이터가 많지 않을 경우, 삽입/갱신/삭제 빈번히 일어나는 경우

📌 Index 생성 방법 3가지

• 고유 인덱스 , 비고유 인덱스 , 결합(복합)인덱스

📌 Index 생성 방법 [ 고유 인덱스 ]

• 중복되지 않는 데이터에 대해서만 설정되는 인덱스
• PK나 Unique key를 걸어주면 자동으로 생성되는 인덱스 이다.
• 📖 단. 인덱스 생성 시 중복 데이터가 있으면 에러 발생

create unique index [인덱스 이름] on [테이블명](컬럼)

📌 Index 생성 방법 [ 비고유 인덱스 ]

• FK 생성 시 이 인덱스가 걸려 있다.
• 📖 중복데이터가 있어도 Index 생성 가능

create index [인덱스 이름] on [테이블명](컬럼)

📌 Index 생성 방법 [ 결합(복합) 인덱스 ]

• 복합키 처럼 여러 컬럼을 조합해서 Index 생성이 가능( 최대 16개 )

create [unique] index [인덱스 이름] on [테이블명](컬럼,...)

📌 Index 확인 방법

 show index from [테이블명]

📌 Index 삭제 방법

• Index는 table 속성으로 취급하여 alter ... drop 사용

alter table [테이블명] drop index [인덱스이름];

📌 In & Exists

• Esists가 in보다 속도가 빠르다.

📖 Esists가 in보다 속도가 빠른 이유 📖

• 📖 SQL 에서는 데이터를 확보해 놓고 다음 작업을 진행하는 것이 빠르기 떄문
• 📖 Exists 는 메인쿼리를 통해 데이터를 확보해두고, 서브쿼리 내용으로 일치하는 내용을 보여주기 때문에 In 보다 속도가 빠르다.
• 📖 Union All 을 사용할때와 같이..

📌 In

• '=' 비교만 가능 / 크거나 작거나는 비교 불가능
• Or 조건의 검색 결과를 가져온다.
• 서브쿼리가 먼저 실행되고 그 결과를 가지고 메인 쿼리를 실행

📌 Exists

• 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 " 만족하는 값이 하나라도 존재한다면' 참(O) / 거짓(X)
• 📖 메인 쿼리가 먼저 실행되고, 이후 서브쿼리가 실행 된다.

📌 Any

• 메인쿼리의 비교조건이 서브쿼리의 결과중에 하나라도 일치하면 참
• In 처럼 사용 가능하고, 크다/ 작다를 활용 할 수 있다.
• (=Any) : In 과 같은 효과

📌 All

• Any 와 반대
• 사용법은 =All, =any 와 같지만 AND 조건이다.
• 📖 서브쿼리 안의 값이 여러개가 나타날 경우 사용하면 안된다.

View [ 매우 중요 ]

📌 View 란? 📌

• 📖 여러 테이블의 데이터를 모아서 만든 가상 table
• 복잡한 쿼리를 통해 얻을 수 있는 결과를 간단한 쿼리로 얻을 수 있게 한다.
• 한개의 View로 여러 table에 대한 데이터를 검색 할 수 있다.
  - 📖 단. View는 자신만의 인덱스를 가질 수 없다.
  - 📖 View의 Index를 넣고 싶다면 컬럼의 원본 Table에서 Index를 추가해야됨

📌 View 생성

create [or replace] view [뷰이름] as [뷰를 생성할 쿼리]

• 📖 Or replace : 기존 View를 수정 할때 사용

📌 Or replace 사용 목적 📌 [중요]

• Or replce 는 생성 시 사용하지 말고 수정 시 사용해주면 좋다
• 📖 생성 시 사용하지 않는 이유는 기존에 있는 데이터를 덮어쓰이기 떄문
• 📖 수정 시 사용하는 경우는 데이터가 있으면 수정 없으면 새로 만들어 주기 때문
• 📖 or replce를 사용 할 때 목적이 분명해야한다.
  View의 수정인가. View의 생성인가에 따라 사용여부가 달라짐

📌 View 확인

show create view [뷰이름]

📌 View 사용

• table 사용과 같음

 select * from name_query;

📌 View 수정

• alter로도 수정이 가능하지만 일반적으로 or replace를 사용
  데이터가 있으면 수정하고, 없으면 만들어 주기 떄문에
• 📖 View에서 수정을 가하면 원본 table의 데이터도 수정된다.
• 📖 With check option : View를 생성한 조건식을 만족하는 컬럼에 대해서는 수정을 막아주는 옵션

📌 View 삭제

drop view [뷰이름]

📌 Auto_Increment

• 자동으로 증가하는 속성 -> 카운트 할 때
  - 📖 중복과 null이 절대 나타날수 없다. 테이블 종속적이므로 PK에 자주 사용
• 중복되지 않는 데이터를 얻기 위해 사용

📌 Auto_Increment 생성 방법

• table 생성 시 함께 생성
• 이미 생성 된 table에 추가
  📖 PK에 Auto_Increment 키워드를 추가해주면 됨

테이블 생성 시 : no int(10) primary key auto_increment
생성된 테이블에 추가 시 : alter table test modify no int(10) primary key auto_increment;

📌 Limit

• Limit n : n 개 보여주기
• Limit n,m : n ~ m 개 보여주기
• Offset n : n번 부터 ~
• 📖 위 내용은 일반적인 페이징 처리 방식
• 📖 Offset의 순서를 위에서 하나씩 세면서 내려가기 때문
  위 방식은 데이터가 많아지면 느려진다.
  

📌 Limit 속도 개선 방법

• 1단계 : 데이터를 정렬 후 내용을 가져온다.
• 2단계 : 인덱스가 없다면 인덱스 추가 / 인덱스가 있다면 할용
  📖 이미 적용된 인덱스를 활용해서 값을 찾는 방식이므로 더 빠름
  

📌 Function [ 함수 ]

Normalization [ 정규화 ]

📌 Normalization [ 정규화 ] 란?

• RDBMS 설계에서 중복을 최소화하게 데이터를 구조화 하는 프로세스
• 자료 중복으로 인해 이상 현상이 발생 할 수 있어 이를 최소화하는 것이 정규화의 목적
• 중복되는 ID 를 가진 컬럼이 여기저기 있는데 삭제,삽입,갱신 시 무작위로 선택되는 것을 이상 현상이라고 부름

📌 정규화의 과정

• 총 여섯 과정이 있다.
• 그 중 실제로는 3NF까지만 사용한다.

📌 1NF ( 제 1 정규화 )

• 모든 항목에 값이 있어야하며( not null ), 중복열이 없어야 한다.
• 📖 데이터 값이 없거나 한개 이상이거나 여러개 일 경우가 있으니 table을 어떻게 쪼갤 것인가
• 📖 식별관계 막 쓰지 말기 -> 식별관계는 데이터가 없을수도 있고 딱 하나 일때 사용

📌 2NF ( 제 2 정규화 )

• 개체의 속성이 한 식별자에 종속 되어야 한다.
• 하나의 식별자로 하나의 데이터가 가져와 지는가?
  단일 키로 테이블을 어떻게 쪼갤 것인지

📌 3NF ( 제 3 정규화 )

📌 지나친 정규화 사용 시

• 쿼리를 복잡하게 만들고 성능을 저하 시킨다. Ex) 6개의 table을 Join 한다면???
• 그래서 table을 합하는 작업을 역정규화라고 한다.

Denormalization [ 역정규화 ]

• 📖 역정규화 과정까지 가면 개막장인거다