MariaDB 전체 운영 실습

데이터베이스 생성

HeidiSQL 왼쪽 데이터베이스 목록 창의 'localhost'에서 우클릭 후 [새로 생성] > [데이터베이스]

데이터베이스 이름 설정 후 create 코드 부분 확인, <확인> 클릭

 

테이블 생성

데이터베이스 우클릭 - 새로 생성 (O) - 테이블 (U)

새로 생성 - 테이블 (U)

<추가> 클릭 후 정보 입력

기본 키 설정

열 선택, 마우스 우클릭 - 새 인덱스 생성 (Y) - PRIMARY 선택

설정 후 <저장>

생성된 테이블 확인

 

데이터 입력

 

기본적인 SELECT문 사용

select * from membertbl;

오류가 뜰 경우 데이터베이스 정확하게 선택했는지 확인

 

IntelliSence 기능

글자 일부 입력 후 Ctrl + Space 누르면 관련된 글자 뜨고, Enter 누르면 자동 완성

대소문자 구분 x

 

Select 기본 형식은 SELECT 열이름 FROM 테이블 이름 WHERE 조건

*는 모든 열

회원 이름과 주소만 출력

select memberName, memberaddress FROM membertbl;

특정 한 명의 정보 추출

select * from membertbl where membername = '지운이';

 

테이블 생성

키보드 제일 왼쪽 위의 백틱(backtick) 키 (`) 주의

create table `my testTBL` (id int);

테이블 이름에 띄어쓰기 허용

중간에 공백이 있는 개체의 이름을 사용할 때는 백틱으로 묶어 줘야 하나의 이름으로 인식함

하이픈 두 개(--)가 앞에 붙으면 그 줄 모두 주석 처리(하이픈 뒤에는 공백 한 개를 꼭 붙일 것)

데이터베이스 목록 창에 안 보임

쿼리 창에서 create문으로 개체를 생성하면 왼쪽 창에 자동으로 적용되지 X

쿼리 창에서 SQL문으로 생성한 후 왼쪽 창에서 바로 확인하고 싶다면

해당 개체 그룹 선택 > 새로 고침 혹은 F5

테이블 삭제

데이터베이스 튜닝

· 데이터베이스의 성능을 향상시키거나 응답하는 시간을 단축시키는 것

· 특히 쿼리에 대한 응답을 줄이기 위해 가장 집중적으로 보는 부분 중 하나가 '인덱스'

· 인덱스를 적절히 활용하고 있느냐에 따라서 시스템의 성능이 몇십 배 이상 차이도 날 수 있음

 

인덱스 사용

인덱스가 없는 상태에서 쿼리가 어떻게 작동하는지 확인

explain select * from indextbl where first_name = 'mary';

explain은 쿼리문이 실행될 때 어떤 방식으로 실행되는지 '실행 계획'의 내용을 보여 줌

type이 'all'이라고 나오는데 all은 인덱스를 사용하지 않고, 테이블 전체를 검색(scan)했다는 뜻 = 500건 모두 읽고 1개의 결과 찾음

 

인덱스 생성

CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);

인덱스 이름 idx_indexTBL_firstname은 indeTBL 테이블의 first_name 열에 생성된 색인이 됨

인덱스 이름은 별로 중요하지 않지만 이름만으로 어느 테이블의 어느 열에 설정된 인덱스인지 알 수 있도록 지정해 주는 게 좋음

인덱스를 만들고 실행해도 결과 값은 별 차이가 없음

EXPLAIN SELECT * FROM indexTBL WHERE first_name = 'Mary';

explain문을 붙여 실행하면 내부에 인덱스를 만들기 전과 만든 후에 차이가 생김

type이 all > ref로 바뀌었는데 ref는 인덱스를 사용해 결과를 찾아냈다는 의미

> possible_keys 및 key에 인덱스 이름인 idx_indexTBL_firstname이 나타남

 

뷰

· 가상의 테이블, 사용자 입장에서는 테이블과 동일하게 보이지만 뷰는 실제 행 데이터를 가지고 있지 않음

· 진짜 테이블에 링크된 개념, 뷰를 select하면 결국 진짜 테이블의 데이터를 조회하는 것과 동일한 결과

· 실제 데이터에 영향을 주지 않음

 

회원 이름과 주소만 존재하는 뷰 생성, 뷰 이름은 uv_memberTBL (userview)

CREATE VIEW uv_memberTBL
AS
    SELECT memberName, memberAddress FROM memberTBL ;

뷰 조회

SELECT * FROM uv_memberTBL ;

 

제대로 생성됐는지 확인

 

스토어드 프로시저

· SQL문을 하나로 묶어서 편리하게 사용하는 기능

· 실무에서는 SQL문 하나씩 실행하는 것보다 스토어드 프로시저로 관리하는 경우가 많음

 

myProc이라는 이름의 스토어드 프로시저 만들기

DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
    SELECT * FROM memberTBL WHERE memberName = '당탕이' ;
    SELECT * FROM producttbl WHERE productName = '냉장고' ;
END //
DELIMITER ;

 

생성한 스토어드 프로시저 실행

CALL myProc() ;

 

트리거

· 테이블에 부착돼 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되면 실행되는 코드

· ex 회원이 탈퇴할 경우 탈퇴자 테이블에 데이터를 일부 옮겨 놓고 지우는 작업

 

트리거의 용도 실습

· 회원 가입 / 업데이트 / 삭제의 일련 작업

· 회원 가입했던 적이 있는지, 기록이 없어 증명을 요구할 경우 어떻게 하는지?

    > 삭제 작업 일어날 경우 백업 테이블에 지워진 데이터 기록하는 트리거 생성

 

INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동'); -- 새로운 회원 입력

SELECT * FROM memberTBL -- 데이터 입력 확인

 

 

UPDATE membertbl SET memberAddress = '서울시 강남구 역삼동' WHERE memberName = '연아'; -- 주소 변경

SELECT * FROM membertbl WHERE memberName = '연아'; -- 변경 확인

 

DELETE FROM membertbl WHERE memberName = '연아'; -- 회원 탈퇴

SELECT * FROM membertbl -- 탈퇴 확인

트리거를 사용하지 않아 '연아'가 회원이었다는 정보는 어디에도 기록되어 있지 않음

 

데이터 보관 테이블 생성

CREATE TABLE deletedMemberTBL (
    memberID char(8) ,
    memberName char(5) ,
    memberAddress char(20),
    deletedDate date -- 삭제한 날짜
);

회원 테이블에 DELETE 작업이 일어나면 백업 테이블에 지워진 데이터가 기록되는 트리거 생성

DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하게 지정
    ON memberTBL -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용
BEGIN
    -- OLD 테이블의 내용을 백업 테이블에 삽입
    INSERT INTO deleteMemberTBL
        VALUES ( OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() );
END //
DELIMITER ;

 

삭제해 보고, 삭제된 데이터가 백업 테이블에 들어가는지 확인

SELECT * FROM memberTBL;

 

productTBL에도 연습

CREATE TABLE deletedProductTBL (
    productName char(4),
    cost int,
    makeDATE date,
    company char(5),
    amount int,
    deletedDate date
);
-- 지워진 데이터 보관할 테이블 생성

DELIMITER //
CREATE TRIGGER trg_deletedProductTBL
    AFTER DELETE
    ON producttbl
    FOR EACH ROW
BEGIN
    INSERT INTO deletedproductTBL
        VALUES( OLD.productName, OLD.cost, OLD.makeDate, OLD.company, OLD.amount, CURDATE() );
END //
DELIMITER ;
-- productTBL에 DELETE 작업 일어날 때, 백업 테이블에 지워진 데이터가 기록되는 트리거 생성

만들어진 것 새로 고침으로 확인

 

!!CREATE 코드 확인하는 법!!

 

처음 예제로 돌아와서 memberTBL 데이터 삭제

DELETE FROM membertbl WHERE memberName = '연아';

1개 행이 지워짐

SELECT * FROM membertbl; -- 삭제 확인

삭제된 것 확인

INSERT INTO producttbl VALUES ('아이폰', '100', '2023-10-20', NULL, '3');

DELETE FROM producttbl WHERE productName = '아이폰';

아이폰 생성했다가 삭제

 

데이터베이스 백업 및 관리

백업과 복원

 

백업

· 현재의 데이터베이스를 다른 매체에 보관하는 작업

· 다른 디스크에 백업을 받아야 의미가 있음

    - 디스크가 깨지면 백업 받은 것도 다 날아감

 

복원(Restore)

· 데이터베이스에 문제가 발생했을 때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업

 

데이터베이스 백업

데이터베이스를 SQL로 내보내기

삭제, 생성 모두 클릭, 데이터: 삽입으로 변경, 파일명에 위치 넣고 내보내기 클릭

여기서 삽입에 체크 안 하면 데이터 다 날림

내보냈는지 확인

백업 폴더 확인

 

복원을 위해 냅다 productTBL 날리기

DELETE FROM producttbl;

복원 과정

USE mysql; -- 사용 중인 DB를 복원하면 문제가 생길 수 있어 일단 다른 DB 선택

경고 떠도 무시

USE ShopBD;
SELECT * FROM productTBL; -- 데이터 복원 확인

 

MariaDB와 응용 프로그램의 연결

데이터베이스를 웹에서 서비스

 

Visual Studio Community 설치

odbc 설치

 

연결

windows 검색창에서 odbc 입력 > 관리자 권한 실행

Test DSN 클릭

설정 후 모두 next 클릭하다가 finish

연결 완료

 

ASP.NET 웹 응용프로그램 작성

visual studio 2017 실행

이름 고대로 두고 확인

누르면 다음과 같은 창이 뜸

좌측 하단

디자인 클릭해 디자인 모드로 변경

sqlDataSource 더블클릭 혹은 드래그해 우측 빈 디자인 창에 가져다 놓기

연결 테스트 후 확인

다음 클릭

쿼리 테스트 후 마침

왼쪽 도구 상자 클릭 - 데이터 - ListView 더블클릭

데이터 소스 선택 SqlDataSource1 선택 후 ListView 구성 클릭

ListView 구성