대엉코딩

0. 용어 정리

    릴레이션(Relation) : 행과 열로 구성된 테이블 (물리적 데이터 저장소 구조)

    속성(Attribute) : 열(Column) , 세로 값

    튜플(Tuple) : 행(Row) , 가로값

    차수 : 속성의 수, 즉 Column의 수 

    인스턴스 : 정의된 스키마에 따라 저장된 실제 데이터 집합

    스키마 : 릴레이션의 구성, 정보 등 기본적인 구조를 정의한 것 (논리적 데이터 구조)

    도메인 : 속성의 가질 수 있는 값의 집합 ( varchar(45), char(1), INT ..... )

    카디날리티(Cardinality) : 튜플의 수

    차수 (Degree) : 속성의 수

1. 관계형 데이터 베이스의 일반적인 형태

      1.0 관계형 데이터 모델이란?

           - 서로 관련된 데이터 포인트에 대한 접근 및 저장을 제공하는 데이터베이스 유형 

           - 그 형태가 간단하고 직관적이다.

           - 키 (key) 라는 고유의 ID가 포함된다.      

           - SQL이 부각되면서 더욱 널리 사용되게 되었다.

           - 컴퓨터에선 SQL을 이용하여 릴레이션을 생성 및 관리, 제약조건 선언, 연산 등을 수행한다.

      1.1 스키마 (예시)

      1.2 관계형 데이터베이스 릴레이션

         (위의 스키마를 이용하여 만든 릴레이션은 아님! )

       1.3 기본적인 특징

          - 중복된 튜플은 허용하지 않는다.

          - 하나의 속성의 값들은 모두 같은 도메인 값을 가진다.

          - 각 속성의 이름은 고유하다 (중복 x)

          - 순서는 상관 없다 (튜플, 속성)

          - 속성은 단일값이여야 한다. ( ex. 위의 릴레이션에서 이름에 박진성,박수진 을 동시에 넣을 수 없다)

2. 관계형 데이터베이스의 키 (KEY)

  2.1 후보키 ( Candidate KEY)

    - 튜플들을 구별할 수 있는 속성 또는 속성의 집합

    - 키가 되는 속성들의 튜플 값들은 모두 달라서 튜플들을 구별할 수 있어야 한다.

    - 하나의 속성으로는 키가 될 수 없지만, 두개의 속성을 묶어서 키로 사용하는 경우도 있다.

   2.2 기본키 (Primary KEY)

    - 후보키 중 선택된 key 

    - 후보키의 특징을 그대로 가지며, null 값을 가지고 있으면 안된다.

    - 키값의 변동이 있어선 안된다. 

   2.3 외래키 (Foreign key)

    - 관계형 데이터베이스에서 서로 다른 테이블끼리의 연결을 위해 중요한 역할을 하는 key

    - 관계된 다른 테이블간의 참조관계를 나타낸다

    - NULL 값을 가질 수 있다.

3. 관계형 데이터베이스의 무결성 제약조건

   3.1 도메인 무결성 제약조건

      - 각 튜플들은 속성의 도메인에 지정된 값 ( INT, VARCHAR(45), CHAR(1) 등 ) 만을 가져야 한다.

   3.2 개체 무결성 제약조건

      - 기본키(primary key)를 지정하고, 이 키는 릴레이션 내에 튜플들을 구별할 수 있도록 하는 고유한 값을 가져야 하          며, NULL은 허용되지 않는다는 조건을 만족해야 한다.

   3.3 참조 무결성 제약조건   

      - 외래키는 참조할 수 없는 값을 가질 수 없다.

      - 외래키 값은 참조 릴레이션의 기본키 값과 동일하거나  NULL이어야 한다.

      - 외래키 제약조건이라고도 한다.

      - 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 같아야 한다.

      - 자식 릴레이션의 값이 변경될 때, 부모 릴레이션의 제약을 받는다.

    3.4 키 (KEY) 무결성 제약조건

       - 한 릴레이션에는 최소한 하나의 키가 존재해야 한다.

0. 시작에 앞서.

- 지금 하는 프로젝트에서도 사용하는 mysql과 같은 데이터베이스를 기본적인 이론부분부터 다시 정리해보자.

1. 데이터 베이스란?

- 한 조직 내에서 필요로 하는 데이터를 공동으로 사용할 수 있도록 중복을 최소화하여 통합 저장한 데이터의 집합체. 데이터를 효율적으로 처리하기 위해 개발된 것으로, 데이터의 중복에서 오는 모순성을 제거할 수 있고 새로 개발된 응용 프로그램도 데이터베이스 내에 있는 기존의 데이터를 사용할 수 있음. 자료틀. 순화어는 `기초 자료'.

( 정의 출처 : Oxford Languages )

2. DBMS(DataBase Management System) 란?

- 데이터베이스를 관리할 수 있게 해주는 소프트웨어

- 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 제공해주고 데이터베이스를 관리해주는 소프트웨어

- 데이터의 종속성과 중복성 문제를 해결하기 위해 제안되었다.

- 데이터베이스의 구성, 접근방법, 유지관리에 대한 모든 책임을 진다.

- Oracle, MySQL, MSSQL 등이 있다.

 2-1. 데이터 종속성이란?

    - 데이터와 프로그램 사이에는 상호 의존관계가 있다.

    - 데이터에 대한 접근과 저장방법이 프로그램 안에 명세되어있다.

    - 그러므로 프로그램은 해당 데이터에 맞는 접근 방법으로 작성되어야 한다.

    - 따라서, 데이터의 속성이 변경되면, 이를 기초로 한 응용프로그램도 변경되어야 한다. 

      (반대로 프로그램의 데이터 저장방식에 따라 데이터의 저장방식이 바뀌기도 한다.)

    - 데이터 종속성 때문에 데이터의 구조가 바뀌면 프로그램도 수정해야 하므로 비용이 많이 들어간다.

    - 결국 데이터와 응용프로그램이 의존관계에 있다는 것이다.

2-2 데이터 중복성이란?

    - 같은 데이터를 사용하더라도, 그 사용방식에 따라 구조와 양식을 다르게 하여 사용하는 경우가 많이 존재한다.

    - 파일 시스템에서는 이러한 경우에 별도의 파일을 만들어 사용해야 한다.

    - 이러한 상황 발생시 같은 내용의 구조가 다른 데이터들이 많이 생겨나고, 하나의 시스템에서 같은 데이터가 중복되

      되게 저장 관리하는 것을 데이터 중복성이라고 한다.

    - 결국 같은 데이터가 중복된채 저장되어 있는 것이고, 이를 관리함에 있어 일부가 수정될 경우 같은 모든 중복데이터

       를 수정해주어야 한다. (그렇지 않으면 불일치성이 발생한다.)

3. DBMS 사용 효과

- 1. 중복성을 피해 공간 절약

- 2. 데이터의 일관성을 유지하기 편리

- 3. 데이터 무결성 유지

- 4. 데이터를 통합하여 관리가능

- 5. 보안을 유지하기에 편리하다.

- 6. 데이터베이스의 논리적 물리적 독립성을 보장

4. DBMS가 갖춰야할 기능

- 1. 정의 ( Definition )

    : 응용프로그램이 요구하는 데이터를 지원하기 위해 데이터베이스에 저장될 데이터의 타입과 구조, 이용방식, 제약 조

      건등을 정확히 명시하여야 하며, 데이터와 데이터 사이의 관계를 명확하게 명시해야 한다.

- 2. 조작 ( Manipulation )

    : 응용프로그램과 데이터베이스 사이에 인터페이스를 제공하여, 데이터 탐색, 수정, 삽입, 삭제를 할 수 있도록 한다.

- 3. 제어 기능 ( Control )

    : 데이터의 무결성을 유지하고, 보안에 관련된 부분을 제어해야 한다. 또한, 여러 사용자가 동시에 접근하여 수정할 때

      도 항상 정확성을 유지할 수 있도록 Concurrency control ( 동시(병행) 제어 ) 가 가능해야 한다. 

1. FROM, JOIN

- 먼저 FROM ( 테이블 ) JOIN ( 테이블 ) 이 실행되어 데이터셋을 뽑아냅니다.

- Sub Query문 (서브쿼리)도 실행되며 임시 테이블이 생성될 수 있습니다.

2. WHERE

- 1에서 뽑아낸 데이터셋에서 WHERE 조건문에 기재된 제약조건 대로 이를 충족하는 행만 뽑아냅니다.

3. GROUP BY

- 2에서 뽑아낸 데이터셋에 대해서 그룹조건을 적용하여 그룹으로 묶어줍니다.

4. HAVING

- 그룹으로 묶여진 데이터셋에 대해서 HAVING 조건절을 만족하는 행만이 남습니다.

5. SELECT

- 4까지 완료된 데이터 셋에 대하여 SELECT문을 실행하여 원하는 칼럼을 설정합니다.

6. DINSTINCT

- DISTINCT를 사용할 경우 이 단계에서 중복되는 행은 제거 됩니다.

7. ORDER BY

- 지금까지 만들어진 데이터셋을 어떤식으로 정렬 할 것인지 정합니다(오름차순, 내림차순)

8. LIMIT / OFFSET

- 최종적으로 범위를 지정 (지정된 범위를 벗어나는 행을 제거) 합니다.

[출처]:

https://sqlbolt.com/lesson/select_queries_order_of_execution
jaimemin.tistory.com/1475?category=1060172

1. 페이지네이션(페이징) 이란 ?

- 서버와 클라이언트의 상황에서 보통 모든 데이터를 한 번에 가져오지 않습니다.

- 보통 필요한 갯수를 지정하고 상황에 맞춰 정렬기준이 조건에 추가됩니다( 정렬기준 + 갯수)

- 이러한 조건을 맞춰서 데이터를 가져오는 것을 Pagenation(페이지네이션), 페이징 이라고 합니다.

- 페이지네이션을 처리하는 방법으로는 대표적으로 1. 오프셋 기반 페이지네이션, 2.커서 기반 페이지네이션

  으로 처리 가능합니다.

- 비교적 구현이 매우 간단한 오프셋 기반 페이지네이션 부터 살펴보겠습니다.

2. Offset-based Pagination (오프셋 기반)

- 제가 사용하는 MySQL로 말씀드리자면 LIMIT을 사용하여 개수를 지정하면 됩니다.

SELECT UserId FROM membership ORDER BY CreateAt desc LIMIT 20,40

- 좀 더 일반적인 변수를 사용한 쿼리문은 이렇게 되겠네요

( pagenum -> 페이지(1.2.....) )

( takenum -> 한번에 불러올 데이터(row) 수)

'SELECT UserId FROM membership
ORDER BY CreateAt DESC
LIMIT' + ( $takenum*($pagenum-1))+','+$takenum;

- 이렇듯 매우 직관적이고 이해하기가 쉽습니다. 사용하기도 편하구요.

- 보통 밑에 이동할 수 있는 페이지가 있고 페이지를 뛰어 넘어갈 수 있다면 offset 기반입니다.

- 하지만 제가 생각하는 가장 와닿는 문제는 예를 들겠습니다.

- 사용자가 매우 많은 사이트에서 글을 쓰는 사람이 매우 많다고 하겠습니다.

- 초당 평균 20개의 새로운 데이터가 들어온다고 가정하고, 한 페이지당 20개의 데이터를

- 보여준다면, 이를 오프셋 기반으로 구현을 한다면 1페이지의 20개의 글을 본 사람이

- 2페이지의 글을 보려고 눌렀을때, 방금 본 20개의 데이터를 그대로 다시 보게 됩니다.

- 즉, 중복데이터를 출력하게 됩니다. 그리고 이런 최악은 아니더라도 생각보다 이런 상황은

- 빈번하게 나타나고, 아마  경험하신 분들도 많이 있으실거라 생각합니다.

- 또 다른 문제는, 정렬기준 조건에 따라서 row가 몇번 째 데이터인지 바뀌게 되는데,

- 당연히 DB는 모든 경우에 따른 rownum을 가지고 있지 않기때문에 

- row의 수가 많아질수록 성능에 문제가 생기게 됩니다.

-이러한 단점을 보완해줄 수 있는 것이 커서 기반 페이지네이션 입니다.

3. Cursor-based Pagination (커서 기반)

- 음.... cursor를 포인터라고 생각하면 이해가 빠를 것 같습니다.

- cursor를 만들어 놓고 "이 cursor가 가리키는 것 다음 부터 n개의 데이터를 주세요" 하는 방식입니다.

- 오프셋 방식을 굳이 예로 들자면 "n개의 데이터를 page-1만큼 스킵하고 그 다음부터 n개의 데이터를 주세요"

  가 될 것 같네요.

- 만약 cursor가 int로 된 UserId라면 UserId 자체를 커서로 사용하여도 상관이 없습니다. (UserId - PK)

- 하지만 cursor를 회원가입 시기(CreateAt) 라고 지정한다면 어떻게 될까요?????

(데이터가 만들어진 순서대로 출력하고 싶을 때)

- 상황 설명을 위해 LIMIT를 사용해서 현재 가정한 테이블의 상태를 확인해보겠습니다.

  (CreateAt은 설명의 편의성을 위해서 간단한 숫자로 대체하여 표현하겠습니다.)

SELECT UserId,CreateAt FROM membership 
ORDER BY CreateAt ASC LIMIT 4

- 전혀 문제가 없어 보입니다. 하지만 만약에 UserId가 "114"와 동시에 가입한 115,116,117이 있을경우

- cursor를 CreateAt으로 설정할 경우 cursor>4 인 리스트를 다음페이지에 불러올 텐데 그럴경우

- 115,116,117의 데이터는 누락이 발생합니다.

- 여기서 알 수 있는 것은

- "커서 기반 페이지네이션을 위해서는 정렬 기준에 포함되는 필드 중 하나이상은 반드시 고유값을 가져야 한다는 것 "

- 설명을 위해 테이블의 상황을 살짝 수정하겠습니다. 아래와 같습니다.
  (말이 좀 안되지만 age는 중복값이 없는 고유값이라고 가정을 좀 하겠습니다.)

- 이 다음 페이지의 데이터를 얻기 위해서는 다음과 같은 쿼리를 날리면 됩니다.

SELECT UserId,CreateAt,Age FROM membership
WHERE (CreateAt >4) or (CreateAt=4 and Age>20)
ORDER BY CreateAt ASC, Age ASC LIMIT 4

- 이렇게 날리면 CreateAt이 4 인 115,116,117도 누락되지 않습니다.

- 문제가 모두 해결된 것 처럼 보이지만 여기서도 제가 생각하는 문제점 중 하나는

- 이렇게 조건절에 필요사항이 달림에 따라서 클라이언트 측에서도 이를 이해하고

- ORDER BY에 달려있는 필드 들을 이해하고 이에 해당하는 값들을 요청시 마다 보내야 하는데

- 당연히 클라이언트 측은 그걸 좋아하지 않고 원치 않습니다

- 그러므로 WHERE절에 걸리는 조건들을 이용해서 고유한 값인 CURSOR(커서)를 만들면 됩니다.

- 간단한 예시로 CreateAt이 최대 6자라고 하고 AGE가 최대 3자 라고 가정하면,

- 두 가지를 합쳐서 "4" + "17" => "000004017" 이런 식으로 가공하여 CURSOR 값으로 사용을 하는 것입니다.

- 커서를 기준으로 출력 후 , 그 다음 리스트는 이 커서 기준으로 다음부터 데이터를 가져오면

- 위의 문제들이 해결된다는 것을 알 수 있습니다.

4. Offset vs Cursor

- 제 생각에는 왠만하면 커서를 이용하는 것이 좋은 것 같습니다만

- 데이터의 입력이 매우 드물고, 또는 중복되는 데이터가 나와도 크게 상관이 없는경우

- 데이터의 수 자체가 그리 많지 않아서 성능에 딱히 연연하지 않아도 되는 경우

- 등에서는 오프셋을 사용해도 크게 문제되지는 않을 것 같습니다.

- 하지만 유저가 접속해서 사용하는 경우 커서 기반을 사용하는 것이 좋습니다.

** Pdo사용을 위해선 php 버전이 5.1.0 이상 이어야 합니다**

1.PDO(PHP Data Object) 란?

- PHP에서 여러종류의 데이터 베이스에 접근할 수 있게 해주는 PHP 확장 모듈이다.(클래스)

- (MySQL, MS SQL, Oracle......)

-반면, mysql 함수나 mysqli 클래스는 MySQL 서버만을 대상

- 즉, 여러종류의 데이터베이스를 같은 방식으로 사용할 수 있게 해준다.

- Pdo는 Prepared Statement를 제공하여 SQL을 미리 데이터베이스에서 컴파일 해두고

  parameter만 값만 바꿔 처리 함으로써 성능의 향상을 위해 된다. 그리고 SQL injection 방어용

  으로도 사용될 수 있는데, Pdo 역시 Prepared Statement를 제공하므로 SQL injection 방어에 사용될

  수 있습니다. <밑에 예시 코드에서 확인하시면 됩니다.>

2. 데이터베이스에 연결

- 데이터베이스에 접속하기 위해서 Pdo를 이용해보자.

- 먼저 pdo를 생성하여야 한다.

3.쿼리준비

- 데이터베이스와 연결후 

- prepare() 메소드를 사용.

4.데이터를 바인딩, 실행

- execute() 메소드의 인수로 쿼리 파라미터에 사용할 값을 넘겨준다.

5. 결과값 가져오기

-이는 다른 예시인데 위의 예시는 그냥 insert를 하는 기능을 수행하는 함수 였기 때문이다.

-(1) fetch()

    : 메소드를 한번 실행하면 쿼리결과에서 한행을 가지고 온다

      반복문을 통해 처리하는 경우가 많다.

-(2) fetchAll()

    : 한번에 모든 행을 가지고 올 떄 사용한다.

**fetch의 모드 구성

      -1 FETCH_BOTH

          - 가져오기 모드를 지정해주지 않으면 이 모드가 지정되는데,

             결과 값을 가지고 올떄 칼럼이름을 키로 사용하는 배열과 칼럼의 순서를 키로 사용하는

             배열 두가지 배열을 만들기 때문에 성능이 좋지않다.

      -2 FETCH_ASSOC

          - 칼럼명을 키로 사용하는 연관배열을 반환한다.

          - $row['COLUMN_NAME']와 같은 방식으로 사용한다.

      -3 FETCH_NUM

          - 컬럼의 순서를 키로 사용하는 연관배열을 반환한다.

          - 가져온 데이터는 $row[NUM]과 같은 식으로 사용한다.

       - 이 3가지 외에도 OBJ(객체) 등이 있다....

ec2 인스턴스에서 임대한 서버에 apm이나 nginx mysql php 등등 을 구축해서

사용을 하려다 보면 예상치 못한 오류들을 만나는데요 

권한과 관련된 문제들도 많이 만나고, ec2의 경우 유저명과 관련된 오류도 만나고...(ubuntu, ec2-user...)

..... 방화벽과 관련된 문제들도 만나는데요

저는 데이터그립과 ec2 서버에 설치된 mysql 데이터 베이스를 연동해서 사용하려 하는데 test connection에서

오류가 나타나서 확인해보니

ec2에 설치된 mysql 이 외부에서 접속이 가능하도록 해주어야 연결이 되더군요  !

아래와 같이 입력 하셔서 외부접속을 허용해주시면 됩니다. !

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root의 패스워드';