안산드레아스

앞서 데이터베이스 모델링에 관한 설명을 했다.

https://ansan-survivor.tistory.com/1167

위 내용을 바탕으로 실제로 MySQL Workbench로 간단히 구축해본다.

이 방법을 이용하면 GUI로 쉽게 모델링 관계를 구축할 수 있으며, 자동으로 SQL문을 생성시켜 테이블을 만들 수 있다.

1. Workbench 로컬호스트 로그인. 설치및 관련 사항은 아래 참고

https://ansan-survivor.tistory.com/1130

2. 새 모델 만들기

3. 다이아그램 생성하기

4. 첫번째 테이블 추가하기

5. 두번째 테이블 추가하기

    위와 동일한방식으로 추가

6. 1:N 관계 만들기(GUI로 디스플레이)

7. 모델을 저장 (경로에 한글이 있으면 안된다. 드라이브 바로 아래 추천)

<생성한 모델로 테이블 생성시키기>

1. Database - Forward Engineer

2. Local instance MySQL 선택 후 Next

3. Next

4. MySQL Table을 뽑아낼 것이므로 선택 후 Next

5. 자동으로 SQL 테이블 생성문이 생성된것을 볼 수 있다. Next 후 Close

6. 생성된 테이블 확인하기, Refresh 시키기

(결과) 모델링으로 만든 것을 Schemas 의 테이블로 Export하였다.

참고 영상)

https://www.youtube.com/watch?v=K7yGG9fcGG0&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=13 

영상에 관한 교재 구매 링크

https://link.coupang.com/a/jPjb7

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

실제로 데이터베이스 모델링은 엄청 복잡하지만, 아래 영상에서는 간략하게 설명해 주었다. 이를 정리한다.

(아래 영상을 참고)

https://youtu.be/JfROL0b_bTo

영상을 간략히 정리.

예전에는 데이터가 별로 없어서 혼자서 해결할 수 있다면, 지금은 무수히도 많은 데이터를 처리해야 함으로 반드시 데이터베이스가 구축이되어야 한다.  여러 사람들의 분업된 데이터를 데이터베이스 서버를 통해 손쉽게 처리할 수 있도록 해야한다.

 - 프로젝트(project)란? 현실의 업무를 컴퓨터에서 처리할 수 있도록 옮겨놓는 과정. 대규모 프로그램을 만들기 위한 과정

 - 자주 사용되는 모델 : "폭포수모델(waterfall model)", 앞의 단계가 마무리 되어야 넘어가는 모델 *업무분석, 시스템설계* 부분이 제일 중요, 전체 많은 시간할당이 필요, 프로그램 구현은 코딩단계

 - 데이터베이스 모델링 이란?  현실에 있는 작업을 PC의 DBMS에 옮기는 과정. (고객 주문관리 등을 PC내의 테이블로 변경하는 과정. 데이터베이스화 작업), 실제 실무자와 함께 작업을 해야 최상의 DB가 구축됨.

 - 데이터베이스 모델링 종류?   개념적모델링, 논리적모델링, 물리적모델링  으로 구분

<모델링 테이블이 만들어지는 과정>

    * 정리되지 않은 현실의 데이터를 정리해서 테이블로 만들기

1. Raw데이터

    (그냥 쇼핑몰 방문 기록) 방문일지 쓰는 것 처럼 작성.

    문제점, 방문할때마다 쓰는거기 때문에 구매를 했을 수 있고 안했을 수 있고, 중복된 이름이 발생할 수 있음

2.  물건을 구매하지 않을 사람들을 따로 모아서 제거 (유효한 데이터만 수집)

    고객방문기록을 유효한 데이터로 두 테이블로 정리 -> 고객테이블, 구매테이블

3. 고객테이블의 중복된 고객을 정리.

4. 구매테이블의 문제점, 누가? 구매했는지 모름

5. 고객테이블과 구매 테이블의 관계 연결 (PK - FK table)

    FK가 있는 테이블을 자식테이블 (Child table), PK가 있는 테이블은 부모테이블 (parent table)

    1:N 방식 (PK는 하나, FK는 여러개)

    주요키, PK (Primary key) : 오직 1개만 존재 고유한 키

    외래키, FK (foreign key) : 두 테이블을 연결시키는 키

    FK는 값이 NULL이거나, 부모테이블의 PK와 동일해야 함.

  아래 예에서 "김범수"가 청바지 3개 구매건에 대해 조회 할때 순서.

 김범수씨 정보확인 -> FK(고객이름)에서 PK(고객이름) 확인 -> 김범수 개인정보 확인

6. 결정한 데이터에 대한 테이블 구조를 정의. 열이름, 데이터 타입, NULL(필수사항) 등 확인

    위와 같은 과정을 거쳐 딱 필요한 데이터를 골라서 아래와 같은 Table을 만든다.

    그리고나서 이 데이터로 이제 Workbench에서 Database를 구축하는 것이다.

응용소프트웨어와 연동하기 위해서는 비쥬얼 스튜디오의 

마이크로소프트 비쥬얼 스튜디오 설치 링크

https://visualstudio.microsoft.com/downloads/

1. 비쥬얼 스튜디오의 여러 패키지중,  ASP.NET 을 설치한다.

2. MySQL과 Visual Studio를 연결해주는  설치 (회원가입 필요)

https://dev.mysql.com/downloads/connector/odbc/

    64비트, 32비트 모두 설치

3. ODBC설정

    3-1. 제어판 실행 후 관리도구 실행

    3-2. ODBC data source (32-bit) 실행

        *64비트는 추후 32비트를 커버하지 못하므로, 32비트로 진행

    3-3. 시스템DSN에서 추가, MySQL ODBC 8.0 Unicode Driver 선택 (unicode를 해야 한글이 깨지지 않음)

    3-4. Data source이름 입력 및 아래와 같이 등록. (root 암호 로그인한 후 내가 사용할 db선택)

    3-5. 결과 확인 (myodbc가 생성되어 추가되었다)

4. 비쥬얼 스튜디오 2019 ASP.NET 새 프로젝트 만들기

5. Web Form 실행

6. 하단의 디자인 탭 선택 - 도구상자 - 데이터 - sqlDataSource를 빈프로젝트에 드레그

7. 데이터 소스를 구성하여 쿼리 테스트

8. 데이터를 화면에 표시하기

9. 저장 후 브라우저로 실행해서 확인해보기

백업이란? 간단하게 중요한 데이터가 실수나 공격으로 인한 데이터 손실을 막기 위해 동일한 데이터를 복사하여 다른 장소, 다른 매체 등에 보관하는 것. 이를 다시 가져오는 것이 복원.

데이터베이트 관리에 매우 중요한 작업.

이를 실습한다.

MySQL Workbench DBMS으로 진행.

MySQL DBMS가 없다면 아래를 통해 설치한다.

https://ansan-survivor.tistory.com/1130

<데이터 백업하기>

1. 다른 네트워크 장치 또는 다른 디스크에 백업폴더 생성.

2. 새로운 SQL창 생성

3. 사용할 DB선택 (USE문 사용), Table 선택

USE shopping_db ;
SELECT * FROM product_info_table ;

    use문을 쓰면 해당 db를 선택한 것처럼 된다.

    테이블 선택 후 전체 data (*) 선택

4. Administration - Data Export - 백업하고자 하는 db선택 - Export to Self-contained File - 경로선택 - Include Create Schema 선택

(백업 결과) 해당 폴더에 .sql 파일로 백업이 되었다.

<데이터 복원하기>

    (먼저 실수로 DELETE로 데이터를 날렸다 가정)

DELETE FROM product_info_table;

1. 새로운 DB에 복원하기 위해 SYS(시스템)호출

USE sys ;

2. Administration - Data Import/Restore - 경로선택 - 백업하고자 하는 db선택 - Start Import 선택

(결과) 다시 확인해보니 DB가 복구되었다.

트리거(Trigger)?

  Table에 어떤 신호가 가해졌을 때 미리 정해진 활동이 자동으로 실행되는 것.

  구체적으로 특정 테이블에 INSERT, DELETE, UPDATE 같은 "DML"문이 수행될 때, 데이터베이스에서 자동으로 동작.

  사용자호출 필요없음, 자동으로 동작!

기존 테이블

1. 테이블에 새로운 사람 가입 (INSERT 문)

INSERT INTO user_address_table VALUES ('1234', 'yoon', 'suwon');
SELECT * FROM user_address_table

2. 테이블의 내용 바꾸기 (UPDATE 문)

UPDATE user_address_table SET address = 'busan' WHERE name = 'Yoon'

3. 테이블에서 제거하기 (DELETE 문)

DELETE FROM user_address_table WHERE name = 'Yoon';

4. 제거된 사람의 데이터베이스 테이블 추가 (제거된 데이터 까지 관리)

    관리를 위한 새로운 테이블을 생성함!

CREATE TABLE removedName (
	ID char(8),
    Name char(8),
    Address char(20),
    deletedDate date  --  삭제 날
);

5. 위 과정으로 트리거 생성하기!

DELIMITER //
CREATE TRIGGER check_removed_name
	AFTER DELETE 			-- 삭제 되면 작동
	ON user_address_table		-- 어떤 테이블에?
    FOR EACH ROW			-- 각 row(행) 마다 적용

-- 테이블에 백업데이터 삽입
BEGIN
	INSERT INTO removedName
		VALUES (OLD.ID, OLD.Name, OLD.Address, CURDATE() );
END
// DELIMITER ;

(테스트 해보기)

이제 user_address_table의 내용을 하나 삭제해본다.

DELETE FROM user_address_table WHERE Name = 'Tom' ;

(결과) 트리거에 의해 자동으로 Tom 데이터가 removedname에 저장됨을 확인 할 수 있다.

MySQL DBMS가 없다면 아래를 통해 설치한다.

https://ansan-survivor.tistory.com/1130

아래 View로 만든 테이블 정보를 바탕으로 테스트.

https://ansan-survivor.tistory.com/1153

Procedure(프로시져)는 SQL 명령어로 실행시킨 일련의 명령 집합을 하나로 묶어 함수화 시킨다고 보면 된다.

아래 직원이 View 테이블에서 Computer와 TV의 가격정보를 빼온다고 본다.

  아래 쿼리문을 돌리면 두개의 결과값이 나온다.

  그런데 이 찾는 쿼리가 만약 2개가 아니고 100만개라면? 자주 써야 한다면? 완전 노가다가 될 것이다.

  한번에 하나 만들어놓고 이후에는 계속 가져다 쓰는게 유리하다.

  그래서 프로시져를 만든다.

<프로시져 만들기>

   프로시져는 앞과 뒤에 아래와 같은 프레임을 만든다.

DELIMITER //
CREATE PROCEDURE 프로시져이름()
BEGIN
	~~~
    ~~~
    ~~~
    ...
END
// DELIMITER ;

1. 프로시져 생성

DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
	SELECT * FROM user_viewTable WHERE type = 'Computer';
	SELECT * FROM user_viewTable WHERE type = 'TV';
END
// DELIMITER ;

2. 생성한 프로시져 콜하기

    이제 myProc()의 begin과 end 사이에 구문이 함수 호출만으로 실행된다.

CALL myProc() ;

(결과) myProc()는 Stored Procedures에 저장되었다. CALL로 호출해서 사용 가능하다.

MySQL에서 View(뷰)란?

가상의 테이블을 만드는 것. (view는 table에 링크가 걸려있는 것 뿐. 바로가기 아이콘 정도?)

실제 table처럼 행과 열을 갖고 있지만 그렇다고 데이터를 저장하고 있지 않다.

보안상 직원에게 내부 데이터를 전체 공개하지 않고, 특정 데이터만 뽑아서 선택적으로 table 방식으로 공개

이제 직원은 View를 통해서 Type과 price정보만 볼 수 있다. 해당 데이터는 Table에 링크되어있다.

나머지 정보는 View를 통해 볼 수 없다.

< DBMS로 뷰를 생성해보기. >

MySQL DBMS가 없다면 아래를 통해 설치한다.

https://ansan-survivor.tistory.com/1130

1. 새로운 쿼리 만들기

2. 쿼리문 실행시켜보기 (product_info_table)에서 일부 데이터만 불러와서 view를 만듦

CREATE VIEW user_viewTable
AS
	SELECT type, price FROM product_info_table ;

    (쿼리결과)

    1. 새로운 view 이름생성.

    2. product_info_table 정보를 가져옴

    3. 그중 type과 price 정보만을 추출하여 만듦

View에 대해서 쿼리문을 테이블과 같은 방법으로 실행시킬 수 있다.

View에 대한정보 전체 불러오기

SELECT * FROM user_viewTable ;

 (결과)

쿼리문을 만들고 실행시켰으나, 아래와 같은 메세지만 나올뿐 result grid가 보이지 않는다.

다시 보이게 하는 방법은 아래와 같다.

1. 아래 아이콘 클릭

2. 그러면 아래와 같이 창이 뜨는데, 우측에서 Result Grid를 선택

(결과) 다시 실행하면 이전과 같이 잘 보인다.

(참고)

https://stackoverflow.com/questions/28038580/how-to-hide-view-result-grid-in-mysql-workbench