코린이[코딩 어린이]

Workbench 메뉴의 Database --> Forward Engineer를 선택한다.

Stored Connection이 'Local stance ~'로 선택된 것을 확인하고 continue를 클릭한다.

이후 따로 설정할 필요없이 계속 continue를 눌러서 넘어가준다.

이전에 다이어그램에 만들었던 내용이 자동으로 SQL문으로 생성된것을 볼 수 있다.

continue를 클릭해준다.

문제없이 만들어 졌음을 확인할 수 있다.

여느때와 마찬가지로 Schemas로 넘어가서 오른쪽을 클릭하고 Refresh All을 클릭한다.

문제없이 ModelDB 테이블을 확인할 수 있다.

1. 왼쪽에 Place a New Table아이콘을 클릭
2. Diagram에 나타난 table을 더블클릭
3. Name 변경
4. Column <click to edit> 입력

Column까지 입력하고나면 userTBL의 다이어그램이 완성된다.

이후 buyTBL을 만들어준다. ( 아직 FK설정은 하지 않는다. )

1 : n 관계를 맺어준다 ( 기본키 - 외래 키 관계로도 불린다 )

왼쪽에 1 : n ( place a Relationship Using Existing column )아이콘을 클릭 후 buyTBL의 userName열과 userTBL의 userName열을 차례로 클릭해준다.

이후 File --> Save Model을 클릭하면 설정한 내용이 저장된다.

데이터베이스 모델링

현 세계에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 개체로 옮기기 위한 과정이다.

:: 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업

데이터 모델링 3단계

• 개념적 모델링 : 업무 분석 단계
• 논리적 모델링 : 업무 분석의 후반부와 시스템 설계의 전반부에 걸쳐서 진행
• 물리적 모델링 : 시스템 설계의 후반부에 진행

모델 다이어그램 작성하기

MySQL Model탭이 열린다.

기본 데이터베이스 이름은 mydb로 되어 있는데, 데이터베이스에서 마우스 오른쪽 버튼을 클릭한 후 Edit Schema를 클릭한다.

데이터베이스 이름을 ModelDB로 변경후 창을 닫아준다.

데이터베이스 이름이 변경된 것을 확인할 수 있다.

Add Diagram 더블클릭

그러면 이렇게 EER Diagram탭이 추가되고 다이어그램을 그릴 수 있는 상태가 된다.

백업(Backup)과 복원(Restore)

데이터베이스 관리 측면에서 가장 중요한 주제 중 한가지다.

백업은 현재의 데이터베이스를 다른 매체에 보관하는 작업을 말하며, 복원은 데이터베이스에 문제가 발생했을 때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업을 말한다.

단적으로 DBA(Database Administrator: 데이터베이스 관리자)가 해야 할 가장 중요한 일을 한가지만 뽑으면 백업과 복원을 들 수 있다.

백업과 복원

:: 쇼핑몰 데이터베이스를 백업받은 후, 실수로 데이터를 모두 삭제했을 경우 원 상태로 복원시키기

Workbench왼쪽에 Administration탭을 클릭 후, Data Export를 클릭하면 쿼리창에 Data Export창이 열린다.

shopdb데이터를 체크하고, 아래 Objects to Export부분에 스토어드 프로시저, 스토어드 함수, 트리거 등 전부 백업을 해준다.

맨 아래 finished라는 문구가 나오면 정상작동한 것이다.

그럼 내가 설정해둔 위치에 이렇게 파일이 생성된다.

이제는 백업파일을 테스트해 보기 위해서 productTBL을 삭제해본다.

DELETE FROM productTBL;

데이터를 삭제했기 때문에 아무리 SELECT로 열어봐도 내용이 나오질 않는다.

현재 사용중인 DB를 바로 복원하게 되면 문제가 생길수 있으므로, 우선 다른 DB로 변경한다.

USE sys;

다른 DB를 선택해도 상관없다.

Administration --> Import from Self-Contained File(내가 저장한 파일선택) --> Default Target Schema : shopdb --> Start Import

이제 데이터가 정상복구 되었는지 확인한다.

USE shopDB;

SELECT * FROM productTBL;

shopDB로 DB를 다시 옮겨서 셀렉트로 검색을 해본다.

아까 지우기 전 상태로 돌아온다.

:: 실무에서는 이렇게 계속 저장해주는 방식이 아닌 실시간으로 변경되는 백업 기능을 탑재한 툴을 사용한다. ( 'MySQL Enterprise Backup', 추가사용 권장 툴 : 'MySQL Enterprise Monitor' 서버 상태를 GUI모드에서 한눈으로 파악할 수 있는 기능을 제공해준다. )

MySQLWorkbench 다운받으러가기

MySQLWorkbench 응용 프로그램이 예기치 않게 종료되었습니다

Mac을 업데이트 했더니 어제까지 잘 작동되던 workbench가 작동이 되지 않았다.

이것저것 다양한 방법으로 접속을 시도했지만 끝내 실패하고 workbench를 재설치를 했다.

재설치하고 접속하니 수신양호

응용프로그램 --> MySQLWorkbench 삭제 --> Workbench다운로드

PK( Primay Key )없음

아래 이미지를 보면 오른쪽 아래 Read Only라고 표시되면서 Columns를 수정할 수 없게 될때가 있다.

이는 현재 PRIMARY키가 없는 상태를 의미한다. PRIMAY키가 없으면 아무것도 할 수 없는 상태가 된다.

해결방법

왼쪽 스키마창을 보면 테이블에 도구모양이 있다. 클릭 !

그럼 이중 PK ( PRIMARY KEY )를 선택하고 오른쪽 아래 Apply를 눌러준다.

그리고 다시한번 테이블을 열어보면 오른쪽 아래 Read Only라는 말이 없어지고 테이블에 추가할 수 있게 되었다.

트리거(Trigger)란 ?

테이블에 부착되어서 테이블에 INSERT나 UPDATE작업이 발생되면 실행되는 코드를 말한다.

회원 테이블에 새로운 회원을 추가해줬다.

INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동');

이후 UPDATE로 주소를 변경시켜본다.

UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동' WHERE memberName = '연아';

그런데 UPDATE를 할때 이런 에러가 뜰 때가 있다.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

에러원인 : 테이블에서 키값을 이용한 update나 delete만을 허용하도록 되어 있는데, 그렇지 않고 좀 더 넓은 범위의 SQL을 적용하려고 할 때, MySQLWorkBench에서 경고를 주는 것이다.

즉, 하나의 레코드만 update, delete하도록 설정되어 있는데, 다수의 레코드를 update나 delete하는 SQL명령어가 실행되기 때문에 발생한다.

해결방법 1

set sql_safe_updates=0;

SQL문에 입력하고 적용시켜주면 된다.

해결방법 2

왼쪽에 SQL Editor로 들어와서 아래 Safe Updates (rejects UPDATEs and DELETEs with no restrictions)를 체크해준다.

다시 돌아와서

UPDATE로 주소를 변경하고 다시한번 memberTBL을 SELECT해보면 아래처럼 memberAddress가 변경되어 있을 것이다.

그리고 이번에는 연아를 회원 테이블에서 삭제해본다.

DELETE FROM memberTBL WHERE memberName = ‘연아';

회원 테이블에서 삭제하고나면 연아의 정보는 어디에도 기록되어 있지 않다.

만약 쇼핑몰의 회원이었다면 그걸 증명해줄 방법이 없다.

위같은 사례를 방지하기 위해 회원 테이블에서 행 데이터를 삭제할 경우 다른 테이블에 지워진 데이터와 날짜를 기록해두면 좋다.

보관 테이블 만들기

CREATE TABLE deletedMemberTBL (
    memberID CHAR(8),
    memberName CHAR(5),
    memberAddress CHAR(20),
    deletedDate DATE
);

SELECT * FROM deletedMemberTBL;

테이블을 생성하고 SELECT해보면

deletedMemberTBL 테이블이 생성된다.

memberTBL에서 DELETE작업이 일어나면 deletedMemberTBL에 지워진 데이터가 기록되는 트리거를 만들어준다.

DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL
    AFTER DELETE
    ON memberTBL
    FOR EACH ROW
BEGIN
    -- OLD
    INSERT INTO deletedMemberTBL VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END //
DELIMITER ;

trg_deletedMemberTBL : 트리거 이름

AFTER DELETE : 삭제 후 작동하도록 지정

ON memberTBL : 트리거를 부착할 테이블

FOR EACH ROW : 각 행마다 적용시킴

-- OLD  : 테이블의 내용을 백업 테이블에 삽입

다시한번 회원을 삭제해본다.

회원에서 당탕이를 삭제했다.

deletedMemberTBL을 확인해본다.

백업테이블( deletedMemberTBL )에 날짜가 추가되어 등록되어 있는것을 볼 수 있다.

이렇게 트리거를 사용하는 기본적인 내용이 완료되었다.

뷰(View) : 가상의 테이블

:: 실제 행 데이터를 가지고 있지 않는다.

( 실체는 없고, 진짜 테이블에 링크된 개념이다. )

뷰 생성하기 ( 회원 이름과 주소만 존재하는 뷰 )

CREATE VIEW uv_memberTBL AS SELECT memberName, memberAddress FROM memberTBL;

SELECT = 뷰에 접근하게 되면 뷰 생성시에 입력한 SELECT문이 그때 작동한다.

SELECT * FROM uv_memberTBL;

이런식으로 만들어두면 중요한 정보만 빼고 바꿔야 하는 정보만 보여줄 수 있다.

:: 주민번호나 핸드폰번호처럼 예민한 정보를 같이 둘리는 없겠지만 만약에 두게 되었을 때, 아무에게나 보여주면 안되기 때문에 이런식으로 바꿔야 하는 정보만 따로 뷰로 생성해서 바꿀 수 있도록 해주면 유용하다.

인덱스( Index )란 ?

인덱스는 책 제일 뒷부분에 있는 '찾아보기'와 같은 개념이다.

책의 내용 중 특정 단어를 찾고자 할 때 책의 첫 페이지 ~ 마지막 페이지까지 전부 찾아보는 것보다 책 뒷부분에 '찾아보기'를 찾아보고 색인에 나와 있는 페이지로 바로 찾아간느 것이 훨씬 빠르다.

MySQL employees 샘플 데이터에서 아래 Mary라는 사람을 조회해 본다.

SELECT * FROM indexTBL WHERE first_name = 'Mary';

잠시 후 결과가 나오고 오른쪽아래 밑으로 내려보면 Execution Plan(실행 계획)을 확인 할 수 있다.

Full Table Scan(전체 테이블 스캔) : 테이블 전체를 검색했다.

즉, 인덱스를 사용하지 않고 테이블 전체를 검색 했다는 뜻이다. ( 500건을 모두 읽어서 1개의 결과를 찾아냈다. )

:: 현재는 index가 없기 때문에 전체에서 검색할 수 밖에 없다.

인덱스 생성하기

CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);

인덱스 생성에 성공하면 아래와 같은 문구가 나온다.

다시한번 Mary라는 사람을 조회해 본다.

SELECT * FROM indexTBL WHERE first_name = ‘Mary’;

인덱스를 만든 후에 검색을 했을 경우에는 Non-Unique Key Lookup이라는 문구가 나온다.

Non-Unique Key Lookup : 인덱스를 사용해서 결괄를 찾아냈다.

:: 아래 작은 글씨로 idx_indexTBL_firstname( 인덱스이름 )이 나타나 있다.

( 간단하게 인덱스를 생성한 후 조회하는 것이 데이터 양에 따라 몇십배 이상 빠를수 있다고 생각하면 된다. )

샘플 데이터

mysqlworkbench 다운로드

MySQL WorkBench를 실행한 후 왼쪽 위에 Open a SQL script file in a new query tab을 클릭해서 다운받은 employees를 열어준다.

이후 File --> Run SQL Script --> employees.sql을 다시한번 열어준다.

기본스키마를 설정할 필요가 없기 때문에 run을 클릭한다.

successfully가 뜨면 close를 클릭한다.

Refresh All을 누르면 employees 스키마가 새로 생성된다.

다시한번 왼쪽 위에 Create a new SQL tab for executing queries로 새로운 쿼리창을 열어준다.

use employees;

select * from employees;

데이터가 들어오게 된다.

CREATE TABLE indexTBL (first_name VARCHAR(14), last_name VARCHAR(16), hire_date DATE);

INSERT INTO indexTBL SELECT first_name, last_name, hire_date FROM employees.employees LIMIT 500;

본인이 만든 스키마를 활성화 시키고 실행 시키면 indexTBL 테이블이 만들어지면서 안에 데이터가 들어가게 된다.

SELECT * FROM indexTBL WHERE first_name = 'Mary';

데이터가 나오면 잘 적용된 것이다.