ep.24 DBMS 개요, MySQL 소개

2024.8.8

 

◦ 데이터베이스
⚫ ‘데이터의 집합’
⚫ 여러 명의 사용자나 응용프로그램이 공유하는 데이터들
⚫ 동시에 접근 가능해야
⚫ 데이터의 저장 공간’ 자체

◦ DBMS
⚫ 데이터베이스를 관리·운영하는 역할

DBMS 개념도

DB/DBMS의 특징

◦ 데이터의 무결성 (Integrity)
⚫ 데이터베이스 안의 데이터는 오류가 없어야
⚫ 제약 조건(Constrain)이라는 특성을 가짐

◦ 데이터의 독립성
⚫ 데이터베이스 크기 변경하거나 데이터 파일의 저장소 변경시 기존에 작성된 응용프로그램은 전혀 영향을 받지 않아야

◦ 보안
⚫ 데이터베이스 안의 데이터에 데이터를 소유한 사람이나 데이터에 접근이 허가된 사람만 접근할 수 있어야
⚫ 접근할 때도 사용자의 계정에 따라서 다른 권한 가짐

◦ 데이터 중복의 최소화
⚫ 동일한 데이터가 여러 개 중복되어 저장되는 것 방지

◦ 응용프로그램 제작 및 수정이 쉬워짐
⚫ 통일된 방식으로 응용프로그램 작성 가능
⚫ 유지보수 또한 쉬워짐

◦ 데이터의 안전성 향상
⚫ 대부분의 DBMS가 제공하는 백업·복원 기능 이용
⚫ 데이터가 깨지는 문제가 발생할 경우 원상으로 복원 , 복구하는 방법이 명확해짐

◦ 데이터베이스 관리시스템
⚫ 파일시스템의 단점 보완
⚫ 대량의 데이터를 보다 효율적으로 관리하고 운영하기 위해 사용
⚫ DBMS - DataBase Management System
⚫ 데이터의 집합인 ‘데이터베이스’ 를 잘 관리하고 운영하기 위한 시스템 또는 소프트웨어

◦ SQL( Structured Query Language)
⚫ DBMS에 데이터 구축/관리/활용 위해서 사용되는 언어
⚫ DBMS를 통해 중요한 정보들을 입력, 관리, 추출

◦ 계층형 DBMS
⚫ 처음으로 나온 DBMS 개념 - 1960년대에 시작
⚫ 각 계층은 트리Tree 형태, 1:N 관계
◦ 문제점
⚫ 처음 구축한 이후 그 구조를 변경하기가 상당히 까다로움
⚫ 주어진 상태에서의 검색은 상당히 빠름
⚫ 접근 유연성 부족해서 임의의 검색에는 어려움

◦ 망형 DBMS
⚫ 계층형 DBMS의 문제점을 개선하기 위해 1970년대에 시작
⚫ 1:1,1:N, N:M(다대다) 관계 지원 - 효과적이고 빠른 데이터 추출
⚫ 복잡한 내부 포인터 사용
⚫ 프로그래머가 이 모든 구조를 이해해야만 프로그램의 작성 가능

◦ 관계형 DBMS (Relational DBMS)
⚫ 1969년 E.F.Codd라는 학자가 수학 모델에 근거해 고안
⚫ 데이터베이스는 테이블Table이라 불리는 최소 단위로 구성
⚫ 이 테이블은 하나 이상의 열로 구성

관계형 DBMS (Relational DBMS)의 장단점

◦ 장점
⚫ 다른 DBMS에 비해 업무가 변화될 경우 쉽게 변화에 순응
⚫ 유지보수 측면에서도 편리
⚫ 대용량 데이터의 관리와 데이터 무결성Integration보장

◦ 단점
⚫ 시스템 자원을 많이 차지해 시스템이 전반적으로 느려지는 것
⚫ 하드웨어 발전되어 해결

◦ SQL (Structured Query Language)
⚫ 관계형 데이터베이스에서 사용되는 언어, ‘에스큐엘’ 또는 ‘시퀄’
⚫ DBMS 제작 회사와 독립적
⚫ 다른 시스템으로 이식성이 좋음
⚫ 표준이 계속 발전중
⚫ 대화식 언어
⚫ 분산형 클라이언트/서버 구조

정보시스템 구축 절차 요약

◦ 분석, 설계, 구현, 시험, 유지보수의 5가지 단계
◦ 분석
⚫ 구현하고자 하는 프로젝트의 가장 첫 번째 단계
⚫ 시스템 분석 또는 요구사항 분석이라고 불림
⚫ 요구사항 분석은 현재 우리가 ‘무엇을(What)’ 할 것인지 결정
⚫ 사용자의 인터뷰와 업무 조사 등을 수행
⚫ 프로젝트의 첫 단추를 끼우는 중요한 단계
⚫ 분석의 결과로 많은 문서 작성
◦ 설계
⚫ 시스템 설계 또는 프로그램 설계
⚫ 구축하고자 하는 시스템을 ‘어떻게(How)’ 할 것인지 결정
⚫ 대부분의 프로젝트에서 분석과 설계의 과정이 전체 공정의 50% 이상 차지

데이터베이스 모델링과 필수 용어

◦ 데이터베이스 모델링
⚫ 현실세계에서 사용되는 데이터를 MySQL에 어떻게 옮겨 놓을 것인지를 결정하는 과정
⚫ 저장할 정보는 테이블(Table)이라는 형식에 맞춰 저장

◦ 데이터
⚫ 하나하나의 단편적인 정보
⚫ 정보는 있으나 아직 체계화 되지 못한 상태
◦ 테이블
⚫ 데이터를 입력하기 위해, 표 형태로 표현한 것
⚫ Ex) 회원 정보 테이블, 제품 정보 테이블
⚫데이터베이스(DB)
⚫ 테이블이 저장되는 저장소
⚫ 각 데이터베이스는 서로 다른 고유한 이름을 가지고 있음
⚫DBMS (DataBase Management System)
⚫ 데이터베이스를 관리하는 시스템 또는 소프트웨어

◦ 열(=컬럼=필드)
⚫ 각 테이블은 열로 구성
⚫ 회원 테이블의 경우에는 아이디, 회원 이름, 주소 등 3개의 열로 구성
◦ 열 이름
⚫ 각 열을 구분하기 위한 이름
⚫ 열 이름은 각 테이블 내에서는 중복되지 않고, 고유해야 함
⚫데이터 형식
⚫ 열의 데이터 형식
⚫ 테이블을 생성할 때 열 이름과 함께 지정
⚫행(=로우=레코드)
⚫ 실질적인 데이터
⚫ 회원 테이블의 경우 4건의 행 데이터, 즉 4명의 회원이 존재함

◦ 기본 키 (Primary Key) 열
⚫ 기본 키(또는 주 키) 열은 각 행을 구분하는 유일한 열
⚫ 중복되어서는 안되며, 비어 있어서도 안 됨
⚫ 각 테이블에는 기본 키가 하나만 지정
◦ 외래 키(Foreign Key) 필드
⚫ 두 테이블의 관계를 맺어주는 키
⚫SQL (Structured Query Language)
⚫ 구조화된 질의 언어
⚫ 사람과 DBMS가 소통하기 위한 말(언어)

데이터베이스 구축/관리 및 활용의 전반적인 절차

테이블 생성
⚫ 회원테이블, 제품 테이블 각 열의 영문 이름 및 데이터 형식 결정
⚫ 데이터베이스 모델링(특히 물리적 모델링) 시에 결정

데이터 활용
◦ SELECT 열 이름 FROM 테이블 이름 [WHERE 조건]
⚫ 모든 데이터 출력하기 (열 이름 대신 ‘ * ’ )
⚫ 열을 선택해 데이터 출력하기 (열 이름 나열)
⚫ 특정 데이터를 만족하는 데이터 출력하기 (WHERE절에 조건 입력)
◦ 새로운 테이블 생성
⚫ 테이블 이름에 space 가 들어간 경우의 처리(백틱[backtick]키 활용)
⚫ Navigator 창에서 “Refresh All” 의 중요성
⚫ 새로 테이블을 만든 뒤 개체가 보이지 않을 경우 필수로 실행할 것
◦ 테이블 삭제
⚫ DROP TABLE 테이블 이름

인덱스 (Index)
◦ 데이터베이스 ‘튜닝’의 개념
⚫ 데이터베이스 성능 향상
⚫ 쿼리에 응답하는 시간 단축시키는 것
◦ 책 뒤에 붙어 있는 ‘찾아보기’(또는 색인)와 같은 개념
◦ 데이터의 양이 많을수록 효과적으로 작용
⚫ 응답속도가 현저히 차이 나는 결과
◦ 테이블의 열 단위에 생성

뷰 (View)
◦ 가상의 테이블
◦ 실제 행 데이터를 가지고 있지 않음
⚫ 그 실체는 없는 것이며, 진짜 테이블에 링크Link된 개념
⚫ 뷰를 SELECT
⚫ 진짜 테이블의 데이터를 조회하는 것과 동일한 결과

스토어드 프로시저 (Stored Procedure)
◦ MySQL에서 제공해주는 프로그래밍 기능
◦ SQL문을 하나로 묶어 편리하게 사용하는 기능
◦ 다른 프로그래밍 언어와 같은 기능을 담당할 수도 있음
⚫ 실무에서는 SQL문(주로 SELECT)을 매번 하나하나 수행 X
⚫ 스토어드 프로시저로 만들어 놓은 후 스토어드 프로시저 호출

트리거 (Trigger)
◦ 테이블에 부착되어 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되면 실행되는 코드

백업과 복원
◦ 백업
⚫ 현재의 데이터베이스를 다른 매체에 보관하는 작업
◦ 복원
⚫ 데이터베이스에 문제 발생 시 다른 매체에 백업된 데이터를 이용해 원상태로 돌려놓는 작업
◦ 백업과 복원은 DBA(DataBase Administrator: 데이터베이스 관리자)가 해야 할 가장 중요한 일

데이터베이스 백업
◦ 백업용 폴더 작성
⚫ 실제로는 다른 디스크에 이루어져야 의미 있음
◦ DB 백업
⚫ DB내의 모든 트리거, 스토어드 프로시저까지 백업
⚫ 백업 폴더에 백업파일 저장

데이터베이스 복구
◦ DB 삭제 같은 큰 사고를 인위로 발생시켜 실습
◦ 복원 후 데이터가 온전한지 check 하는 것이 중요함

프로젝트 (Project)
◦ 현실세계의 업무를 컴퓨터 시스템으로 옮겨놓는 일련의 과정
◦ 대규모의 프로그램을 작성하기 위한 전체 과정

◦ 분석과 설계 작업 등한시 → ‘소프트웨어 개발 방법론’의 대두
◦ 폭포수 모델 (Waterfall Model)

폭포수 모델 (Waterfall Model)
◦ 가장 오래되고 전통적으로 사용되는 소프트웨어 개발 모델
⚫ 폭포가 떨어지듯이 각 단계가 끝나면 다음 단계로 진행
◦ 장점
⚫ 각 단계가 명확히 구분되어 프로젝트의 진행 단계가 명확해짐
◦ 단점
⚫ 문제점이 발생될 경우 다시 앞 단계로 거슬러 올라가기가 어려움
⚫ 문제점이 대부분 프로그램 구현 단계나 테스트 단계에서 발생
⚫ 대부분의 문제점을 업무 분석단계에서 다시 시작하여 해결

데이터베이스 모델링(데이터 모델링) 개념
◦ 현 세계에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 개체로 옮기기 위한 과정

데이터베이스 모델링 실습
◦ 개념적 모델링
⚫ 업무 분석 단계에 포함
◦ 논리적 모델링
⚫ 업무 분석의 후반부와 시스템 설계의 전반부에 걸쳐 진행
◦ 물리적 모델링
⚫ 시스템 설계의 후반부에 주로 진행

<SELECT... FROM>
◦ 원하는 데이터를 가져와 주는 기본적인 구문
◦ 가장 많이 사용되는 구문
◦ 데이터베이스 내 테이블에서 원하는 정보 추출하는 명령

USE 구문
◦ SELECT문 학습 위해 사용할 데이터베이스 지정
◦ 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행

SELECT와 FROM
◦ SELECT *
⚫ 선택된 DB가 employees 라면 다음 두 쿼리는 동일

◦ SELECT 열 이름
⚫ 테이블에서 필요로 하는 열만 가져오기 가능

⚫ 여러 개의 열을 가져오고 싶을 때는 콤마로 구분

⚫ 열 이름의 순서는 출력하고 싶은 순서대로 배열 가능

◦ 주석(Remark)

DB, TABLE, 열의 이름이 확실하지 않을 때 조회하는 방법
◦ 현재 서버에 어떤 DB가 있는지 보기
⚫ SHOW DATABASES;
◦ 현재 서버에 어떤 TABLE이 있는지 보기
⚫ 데이터베이스에 있는 테이블 정보 조회
⚫ SHOW TABLE STATUS;
⚫ 테이블 이름만 간단히 보기
⚫ SHOW TABLES;
◦ employees 테이블의 열이 무엇이 있는지 확인
⚫ DESCRIBE employees; 또는 DESC employees;
◦ Workbench의 [Navigator]로 확인 가능 하나 명령어를 알아두면 Linux 명령어 모드에서 사용 가능

특정 조건의 데이터만 조회 - <SELECT … FROM … WHERE>
◦ 기본적인 WHERE절
⚫ 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용
⚫ SELECT 필드이름 FROM 테이블이름 WHERE 조건식;

◦ 관계 연산자의 사용
⚫ OR 연산자 : ‘…했거나’, ‘… 또는’
⚫ AND 연산자 : ‘...하고’, ‘…면서’, ‘… 그리고’
⚫ 조건 연산자(=, <, >, <=, >=, < >, != 등)와 관계 연산자(NOT, AND, OR 등)를 조합하여 데이터를 효율적으로 추출 가능

◦ BETWEEN… AND와 IN( ) 그리고 LIKE
⚫ 데이터가 숫자로 구성되어 있으며 연속적인 값 : BETWEEN … AND 사용

⚫ 이산적인(Discrete) 값의 조건 : IN() 사용

⚫ 문자열의 내용 검색 : LIKE 사용(문자뒤에 % - 무엇이든 허용, 한 글자와 매치 ‘_’ 사용)

ANY/ALL/SOME ,서브쿼리(SubQuery, 하위쿼리)
◦ 서브쿼리
⚫ 쿼리문 안에 또 쿼리문이 들어 있는 것
⚫ 서브쿼리 사용하는 쿼리로 변환 예제
⚫ 서브쿼리의 결과가 둘 이상이 되면 에러 발생

◦ ANY
⚫ 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능
⚫ SOME은 ANY와 동일한 의미로 사용
⚫ ‘= ANY(서브쿼리)’는 ‘IN(서브쿼리)’와 동일한 의미
◦ ALL
⚫ 서브쿼리의 결과 중 여러 개의 결과를 모두 만족해야 함

◦ ORDER BY절
⚫ 결과물에 대해 영향을 미치지는 않고 출력되는 순서를 조절하는 구문
⚫ 기본적으로 오름차순 (ASCENDING) 정렬
⚫ 내림차순(DESCENDING)으로 정렬하려면 열 이름 뒤에 DESC
⚫ ORDER BY 구문을 혼합해 사용하는 구문도 가능

⚫ ASC(오름차순)는 디폴트 값이므로 생략 가능

◦ 중복된 것은 하나만 남기는 DISTINCT
⚫ 중복된 것을 골라서 세기 어려울 때 사용하는 구문
⚫ 테이블의 크기가 클수록 효율적
⚫ 중복된 것은 1개씩만 보여주면서 출력
◦ 출력하는 개수를 제한하는 LIMIT
⚫ 일부를 보기 위해 여러 건의 데이터를 출력하는 부담 줄임
⚫ 상위의 N개만 출력하는 ‘LIMIT N’ 구문 사용
⚫ 개수의 문제보다는 MySQL의 부담을 많이 줄여주는 방법
◦ 테이블을 복사하는 CREATE TABLE … SELECT
⚫ 테이블을 복사해서 사용할 경우 주로 사용
⚫ CREATE TABLE 새로운 테이블 (SELECT 복사할 열 FROM 기존테이블)
⚫ 지정한 일부 열만 복사하는 것도 가능
⚫ PK나 FK 같은 제약 조건은 복사되지 않음

GROUP BY 및 HAVING 그리고 집계 함수
◦ GROUP BY절
⚫ 그룹으로 묶어주는 역할
⚫ 집계 함수(Aggregate Function)와 함께 사용
⚫ 효율적인 데이터 그룹화 (Grouping)

⚫ 읽기 좋게 하기 위해 별칭(Alias) AS 사용

◦ GROUP BY와 함께 자주 사용되는 집계 함수

Having절
⚫ WHERE와 비슷한 개념으로 조건 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것
⚫ HAVING절은 꼭 GROUP BY절 다음에 나와야 함(순서 바뀌면 안됨)
◦ ROLLUP
⚫ 총합 또는 중간 합계가 필요할 경우 사용
⚫ GROUP BY절과 함께 WITH ROLLUP문 사용

SQL의 분류
◦ DML (Data Manipulation Language, 데이터 조작 언어)
⚫ 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어
⚫ DML 구문이 사용되는 대상은 테이블의 행
⚫ DML 사용하기 위해서는 테이블이 정의되어 있어야 함
⚫ SQL문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당
⚫ 트랜잭션(Transaction)이 발생하는 SQL도 DML에 속함
⚫ 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것
⚫ 취소 가능

◦ DDL (Data Definition Language, 데이터 정의 언어)
⚫ 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할
⚫ CREATE, DROP, ALTER 자주 사용
⚫ DDL은 트랜잭션 발생시키지 않음
⚫ 되돌림(ROLLBACK)이나 완전적용(COMMIT) 사용 불가
⚫ 실행 즉시 MySQL에 적용
◦ DCL (Data Control Language, 데이터 제어 언어)
⚫ 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문
⚫ GRANT/REVOKE/DENY 구문

데이터의 삽입 : INSERT

◦ 자동으로 증가하는 AUTO_INCREMENT
⚫ INSERT에서는 해당 열이 없다고 생각하고 입력
⚫ INSERT문에서 NULL 값 지정하면 자동으로 값 입력
⚫ 1부터 증가하는 값 자동 입력
⚫ 적용할 열이 PRIMARY KEY 또는 UNIQUE일 때만 사용가능
⚫ 데이터 형은 숫자 형식만 사용 가능

대량의 샘플 데이터 생성
⚫ INSERT INTO … SELECT 구문 사용

⚫ 다른 테이블의 데이터를 가져와 대량으로 입력하는 효과
⚫ SELECT문의 열의 개수 = INSERT 할 테이블의 열의 개수
⚫ 테이블 정의 까지 생략 하려면 CREATE TABLE … SELECT 구문을 사용

데이터의 수정 : UPDATE
◦ 기존에 입력되어 있는 값 변경하는 구문

◦ WHERE절 생략 가능하나 WHERE절 생략하면 테이블의 전체 행의 내용 변경됨
⚫ 실무에서 실수가 종종 일어남, 주의 필요
⚫ 원상태로 복구하기 복잡하며, 다시 되돌릴 수 없는 경우도 있음

데이터의 삭제 : DELETE FROM

◦ 행 단위로 데이터 삭제하는 구문

◦ WHERE절 생략되면 전체 데이터를 삭제함
◦ 테이블을 삭제하는 경우의 속도 비교
⚫ DML문인 DELETE는 트랜잭션 로그 기록 작업 때문에 삭제 느림
⚫ DDL문인 DROP과 TRUNCATE문은 트랜잭션 없어 빠름
⚫ 테이블 자체가 필요 없을 경우에는 DROP 으로 삭제
⚫ 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적

조건부 데이터 입력, 변경
◦ 기본 키가 중복된 데이터를 입력한 경우
⚫ 오류로 입력 불가

◦ 대용량 데이터 처리의 경우 에러 발생하지 않은 구문 실행
⚫ INSERT IGNORE문
⚫ 에러 발생해도 다음 구문으로 넘어가게 처리
⚫ 에러 메시지 보면 적용되지 않은 구문이 어느 것인지 구분 가능
⚫ ON DUPLICATE KEY UPDATE 구문
⚫ 기본 키가 중복되면 데이터를 수정되도록 하는 구문도 활용 가능

WITH절과 CTE 개요
◦ WITH절은 CTE(Common Table Expression)를 표현하기 위한 구문
◦ MySQL 8.0 이후부터 사용 가능하게 됨
◦ CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등을 대신할 수 있으며 간결한 식으로 보여짐
◦ CTE는 ANSI-SQL99 표준(기존 SQL은 ANSI-SQL92 기준)
◦ CTE는 비재귀적 CTE와 재귀적 CTE가 있지만 주로 사용되는 것은 비재귀적 CTE

비재귀적 CTE
◦ 단순한 형태, 복잡한 쿼리문장을 단순화하는데 적합

⚫ CTE는 뷰와 용도가 비슷하지만 개선된 점이 많음
⚫ 뷰는 계속 존재해서 다른 구문에서도 사용 가능하지만, CTE와 파생 테이블은 구문이 끝나면 소멸됨
⚫ 중복 CTE 허용됨

MySQL에서 지원하는 데이터 형식의 종류
◦ Data Type으로 표현
⚫ 데이터 형식, 데이터형, 자료형, 데이터 타입등 다양하게 불림
◦ 데이터 형식에 대한 이해가 필요한 이유
⚫ SELECT문 더욱 잘 활용
⚫ 테이블의 생성 효율적으로 하기 위해 필요

◦ 숫자 데이터 형식