[SQL] JOIN, 서브쿼리

그룹함수

---

Group by 절

• select 문에서 group by 를 사용할 경우 database는 쿼리 된 테이블의 행을 그룹으로 묶는다.
• database는 선택 목록의 집계함수를 각 행 그룹에 적용하고 각 그룹에 대해 단일 결과 행을 반환한다.
• group by 절을 생략하면 데이터베이스는 선택목록의 집계함수를 쿼리 된 테이블의 모든 행에 적용 가능
• select 절의 모든 요소는 group by 절의 표현식, 집계함수를 포함하는 표현식, 또는 상수만 가능.

---

Having 절

• group by 한 결과에 조건을 추가할 경우 having절을 사용
• 쿼리의 실행순서를 보면 where 절이 group by 절보다 먼저 실행되기 때문에 aggregate 조건은 having 절에 작성
• 부서별 평균 급여가 7000 이상인 부서 번호 , 평균급여

---

Set (집합 연산자)

• 모든 집합 연산자는 동일한 우선순위를 갖는다.
• select 절에 있는 column 개수와 타입이 일치해야 한다.

---

Data Modeling

• 정보화 시스템을 구축하기 위해 어떤 데이터가 존재하는지 또는 업무에 필요한 정보는 무엇인지 분석하는 방법
• 관계형 데이터베이스는 이 표의 개념을 사용해 데이터를 구성하는 방법을 사용.

---

개념적 데이터베이스 모델링

• 업무분석 단계에서 얻어진 내용을 토대로 우선 Entity를 추출하고 엔티티 내에 속성을 구성하며 엔티티간의 관계를 정의해서 ER다이어그램을 정의하는 관계

1. 사용자 부문의 처리현상을 분석한다.
2. 중요 실체와 관계를 파악하여 ERD를 작성한다.
3. 실체에 대한 상세 정의를 한다.
4. 식별자를 정의하고, 식별자 업무 규칙을 정한다.
5. 실체별로 속성을 상세화 한다.
6. 필요한 속성 및 영역을 상세 정의한다.
7. 속성에 대한 업무규칙을 정의한다.
8. 각 단계를 마친 후 사용자와 함께 모델을 검토한다.

개체를 뽑아내고 그개체에서 어떤 속성을 뽑아 내고 어떤 관계가 있는지 파악.

---

---

개체(Entity)

• 사용자와 관계가 있는 주요 객체로서 업무 수행을 위해 데이터로 관리되어져야하는 사람, 사물, 장소 , 사건 등을 개체라고 한다.
• (영속적으로 존재하는 거) ex. 학생, 직원

Entity 찾는 법

• 영속적으로 존재하는 것
• 새로 식별이 가능한 데이터 요소를 가짐.
• Entity 는 반드시 속성을 가져야 함.
• 명사적 표현.

속성(Attribute)

• 저장할 필요가 있는 실체에 대한 정보
• 개체(Entity)의 성질, 분류, 수량, 상태 , 특성 등을 나타내는 세부사항
• 개체에 포함되는 속성의 숫자는 10개 내외로 하는 것이 바람직함.
• 최종 DB 모델링 단계를 통해 테이블의 컬럼으로 활용

ex) 학생: 학번,이름,주민,주소

속성의 유형

• 기초속성: 원래 갖고 있는 속성으로 현업에서 기본으로 사용되는 속성
• 추출속성: 기초속성으로부터 계산(가공)에 의해 얻어질 수 있는 속성
• 설계속성: 실제로 존재하지 않으나 시스템의 효율성을 도모하기 위해 설계자가 임의로 부여하는 속성.

식별자

한 개체에서 인스턴스를 유일하게 구분할 수 있는 단일 속성 또는 속성그룹

• 후보키(Candidate key) : 개체 내에서 각각의 인스턴스를 구분할 수 있는 속성으로 기본키가 될 수 있는 후보속성

• 기본키: 개체(Entity) : 개체에서 각 인스턴스를 유일하게 식별하는데 가장 적합한 KEY. 기본키 설정시 고려할 사항으로 해당 실체를 대표할 수 있을 것. 업무적으로 활용도가 높을 것, 길이가 짧을 것 등이 있따.

• 대체키(Alternate Key) : 후보키 중 기본키로 선정되지 않은 속성

• 복합키(COmposite Key) : 하나의 속성으로 기본키가 될 수 없는 경우 둘 이상의 컬럼을 묶어서 식별자로 정의하는 경우

• 대리키(Surrogate Key) : 식별자가 너무 길거나 여러개의 속성으로 구성되어 있는 경우에 인위적으로 추가할 식별자.

---

관계(Relationship)

• 정의 : 두 개체간의 업무적인 연관성 또는 관련 사실
• relationship 분석

1. 각 개체간에 특정한 존재여부 결정
2. 현재의 관계 뿐 아니라 장래에 사용될 경우도 고려

---

논리적 데이터베이스 모델링

• 개념적 데이터베이스 모델링 단계에서 정의된 ER-Diagram을 Mapping Rule을 적용하여 관계형 데이터베이스 이론에 입각한 스키마를 설계하는 단계와 이를 이용해 필요하면 정규화 하는 단계로 구성

논리적 데이터베이스 모델링

기본키(Primary Key)

• 후보키 중 선택한 주 키
• 널 값을 가질 수 없다(not null)
• 동일한 값이 중복해서 저장될 수 없다.(Unique)

참조키, 이웃키(Foreign key)

• 관계를 맺는 두 엔티티에서 서로 참조하는 릴레이션의 attribute로 지정되는 키.

처음 한게 사원 엔티티 만들고 사원번호라는 pk와 번호 등 넣음.

테이블 2개 만들었다 부서가 없는 사원도 있으니 0도 포함.

N:M 학생도 여러명, 과목도 여러개 가능.

---

정규화

• Attribute 간에 존재하는 함수적 종속성을 분석해서 관계형 스키마를 더 좋은 구조로 정제해나가는 일련의 과정
• 데이터의 중복을 제거하고 속성들을 본래의 제자리에 위치시키는 것.

제 1 정규화

• 반복되는 그룹 속성을 제거한 뒤 기본 테이블의 기본키를 추가해 새로운 테이블을 생성하고 기존의 테이블과 1:N의 관계를 형성하는 과정
• 반복되는 그룹속성이란 같은 성격과 내용의 컬럼이 연속해서 나타나는 컬럼을 말함.

제 2 정규화

• 복합키(Composite Primary Key)에 전체적으로 의존하지 않는 속성 제거
• 제 2 정규화의 대상이 되는 테이블은 키가 여러 칼럼으로 구성되 ㄴ경우
• 복합키의 일부분에 의해 종속되는 것을 부분적 함수 종속관계라 하며 이를 제거하는 작업

제 3 정규화

• 기본키에 의존하지 않고 일반 컬럼에 의존하는 컬럼을 제거한다.
• 이행적 종 함수 종속관계를 갖는 컬럼을 제거하는 과정

이 부분은 회원 안에서 주문이라는 게 또 갈라짐.

이게 최종 테이

회원, 상품이 주 테이 주문하게 되면 포스기

---

1. 엔티티가 테이블로 바뀐다가 첫번쨰 규칙.

맨 처음 카디널리티 표현함 (3종류 1:1, 1:N, N:M)

1:1 은 아무쪽에 몰아도 되고 1:N은 다쪽으로 몰아라 M:N은 중간에 끼어있는 관계라는 애도 테이블이 된다.

한 학생은 여러 과목 수강하고(디비도 듣고 os도 듣고 등등) 그리고 한 과목도 한학생만 듣는게 아님( 1:n, n:1 의 관계라 다대다 관계이다.)

어떤 학생이 무슨 정보를 듣는고나가

과목번호와 학번 합해서pk 주거나 수강번호라는 걸 하나 더 만들어서 pk로 만들 수 있다.

---

엔티티는 테이블 변환이 되고 1:1이나 1:N은 FK가 된다.

1:1에서는 아무나 가져가고 1:N에서는 N이 상대방의 pk가져감

M:N에선 테이블 하고 중간의 관계가 또 테이블로 만들어지니까 외래키가 2개가 된다. 애트리뷰트는 컬럼

키 애트리뷰트는 밑줄쳐서 표시하던거. 그게 기본키가 된다.

---

유저 아이디가 유저의 아이디 가르킴. 유저 아이디가 외래키 저 경우 외래키가 다른 pk 연결하면 비식별 관게라고 함.

---

엄밀히 따지면 dao는 데이터베이스 관련 일만 하는거 더하고 뺴고 곱하고 이런건 서비스에서 하는거
모든 일처리는 서비스, 서비스에서 나온 일처리를 가지고 디비로 가라 이건 dao

---

뷰란?(VIEW)

기존의 테이블을 바탕으로 생성되는 가상의 테이블.

조인등의 작업을 해서 만든 결과 테이블이 가상으로 저장된 형태이다.

• 데이터를 직접 소유하지 않으며 뷰를 통해 기존 테이블 내용 검색

• 보안상 이유 혹은 자주 사용되는 복잡한 쿼리를 보다 간단하게 사용하기 위해 만듬.

• 물리적인 저장공간을 가지지 않음

• 하나 이상의 테이블로부터 만들어 짐

• 뷰를 액세스 하게되면 간접적으로 테이블 액세스 하게 됨

뷰의 목적은 빠른 검색이 목적임.

1000페이지 되는 책이 있는데 이 책 안에서 뷰라는 주제로 내용을 일곡 싶은데 빨리 찾으려면 빨리 찾을 수 있는 방법은 1페이지 부터 계속 넘겨가면서 찾아가는 순차검색, 아니면 맨 뒤에 가면 바로가기 있는데 이게 인덱. abc순이나 가나다 순으로 정리된 걸 찾을 수 있는 거.

뷰도 마찬가지 테이블 만들고 인서트 집어넣는데 데이터들이 연속적으로 저장공간 저장된다 생각 가능. 1번 데이터 인서트 시키면 전체 에서 비어있는 곳

인덱스

인덱스 생성기준

1. sql 문의 where절에서 자주 사용하는 컬럼이 대상이 됨
2. 빈번하게 변경되지 않는 테이블에 적용(빈번하게 적용되는 거에 적용하면 비효율 적)
3. 데이터가 많은 테이블에 효과적( 책 생각해보면 1000페이지 넘으면 자료 찾기 힘든데 인덱스 있으면 찾기 쉬움)
4. 테이블간 Join 에 사용되는 컬럼을 선정.

데이터가 많은 테이블에 효과적이고 아니면 굳이 인덱스를 만들지 않아도 된다. 변경이 한번 일어나면 500페이지 기준으로 쫙 정리해 놨는데 530페이지로 늘어났는데 그럼 인덱스로 다시 조정이 되어야 한다.

인덱스를 사용하지 말아야 할 경우

1. 테이블에 행이 적은 경우
2. 찾는 컬럼이 where 조건에 자주 사용되지 않을 떄
3. 테이블이 자주 입력, 수정, 삭제 될 떄
4. 검색 결과 양이 많은 경우(전체 데이터의 15%)

혹시라도 인덱스 만들라는 문제가 나올수 있는데 인덱스 만드는 전치사는 on

인덱스 생성방법

그리고 인덱스는 떨어져 있는데 실제 주소값같은 경우(물리적인 위치값) 하나 주면 그 값을 찾고 그 갖ㅅ이 어딘지를 알고 바로 찾아갈 수 있도록 함.