WEB 여름3주차 정리 - DATABASE(MySQL)
1. 수업소개 ~ 3. MySQL 설치
데이터베이스(DB)의 목적 = 정보의 정리.
: 데이터베이스 또한 파일에 정보를 정리하는 것이지만, 테이블(표)의 형태로 정보를 정리하여 파일에 수납함으로써
방대한 정보의 관리를 더욱 용이하게 할 수 있다.
스프레드시트(엑셀 등)와 DB의 차이
: 스프레드시트는 기본적으로 GUI형의 응용 프로그램이며, 테이블의 형태를 띠는 점에 있어서는 DB와 유사하나
기본적으로 클릭 조작을 통해 정보를 다루는 명령을 사용한다.
반면 DB는 이러한 조작을 명령어를 통해 처리할 수 있다.
프로그래밍 언어가 어떻게 쓰이는지 생각한다면 이러한 언어형 조작법의 이점을 쉽게 깨달을 수 있을 것이다.
MySQL은 따로 MySQL 사이트에서 설치할 필요 없이, 비트나미를 깔면 같이 동봉되어서 온다.
(MySQL 사이트에서 깔고 싶어도 비주얼 스튜디오의 낮은 버전(2015, 17, 19년 버전)이 없으면 깔리지 않는다. 내가 가진 버전은 2022년 버전이라 설치가 되지 않았다.)
최근 버전의 비트나미에는 mysql이 아닌 mariadb가 동봉되지만 기능은 같다.
컴퓨터의 cmd에서 아래 스크린샷과 같은 경로로 디렉터리를 열고,
아래와 같은 명령어를 입력하여 초기 설치 시에 입력했던 패스워드를 넣으면 데이터베이스 프로그램이 실행된다.
이렇게 프로그램을 실행시켜서 실습을 진행하면 된다.
4. MySQL의 구조
표(table) : 기본이 되는 단위. 하나의 표 그 자체를 의미한다.
데이터베이스(database) 또는 스키마(schema) : 표의 집합. 관계가 있거나 구조가 같은 표들을 모아놓은 것.
데이터베이스 서버(database server) : 데이터베이스의 집합.
5. MySQL 서버접속
MySQL에 접속할 때 치게 되는 커맨드 > mysql -uroot -p
-uroot : root 권한으로 db에 접속하는 것을 의미한다. 루트 권한은 가장 강력한 권한이므로 공식적으로 중요한 db 서비스를 하게 되었을 때는 평상시 접속하는 계정을 달리 만들고, 중요한 수정 작업을 할 때만 root로 접속하도록 한다.
-p : 비밀번호를 입력한다. -p111111 처럼 뒤에 바로 비밀번호를 붙여 입력해도 되지만, 이 경우 비밀번호가 가려지지 않고 드러나 위험하므로 -p를 친 후에 문자가 *로 마스킹이 되는 창에서 비번을 치도록 한다.
6. 스키마의 사용
데이터베이스(스키마)를 만드는 법을 알아보자. 프롬프트에서 다음과 같은 명령어를 입력한다.
> CREATE DATABASE (생성할 데이터베이스 이름);
세미콜론은 필수이다. 모든 쿼리는 ;로 끝나야 하고 그렇지 않으면 아래 스크린샷에서처럼 다음 행에 -> 마크가 뜨며
연속되는 명령어가 더 있는 것으로 간주, 프롬프트가 대기하게 된다.
성공적으로 데이터베이스가 만들어졌다면 Query OK라는 메시지가 뜰 것이다.
만든 데이터베이스를 확인하기 위해서는 > SHOW DATABASES; 명령어를 사용한다. (끝에 S가 붙어야 한다.)
opentutorials라는 데이터베이스가 성공적으로 만들어진 것이 보인다.
이 데이터베이스를 사용하고(표를 조작하고) 싶다면 다음과 같이 입력한다.
> USE (데이터베이스 이름)
하단과 같은 메시지가 떴다면 성공이다.
7. SQL과 테이블의 구조
SQL = Structured Query Language
: 데이터를 정리하고 구조화하는 것에 특화된 언어.
데이터를 처리하기 위해서는 SQL을 통해 데이터베이스 서버에 요청을 전달해야 한다.
서버는 이 SQL로 된 요청을 받아들이고 그에 맞게 테이블을 조작해 사용자에게 내놓는다.
테이블의 구조는 위와 같다. 격자로 된 하나의 표를 테이블이라고 하고,
가로줄 하나를 행(row, record)이라고 한다. 보통 하나의 개체와 그 개체가 가진 feature를 저장하고 있다.
세로줄은 열(column)이라고 한다. 하나의 feature에 대한 그 테이블에 저장된 모든 개체의 정보를 담고 있다.4
8. 테이블의 생성(1~2)
위 명령어는 topic이라고 하는, 글을 올리는 게시판에서 각각의 글의 정보를 담는 데이터베이스를 만든 것이다.
명령문 하나당 하나의 필드를 생성했다. 각각의 명령어를 해독해 보자.
CREATE TABLE topic( => topic이라는 table을 만든다
-> id INT(11) NOT NULL AUTO_INCREMENT,
=> 글의 식별번호(아이디). 정수, 11자리까지 노출. 비워둘 수 없음. 서로 중복되지 않게 등록될 때마다 증가하도록 함
-> title VARCHAR(100) NOT NULL, => 글의 제목. 100글자를 넘으면 잘림. 비워둘 수 없음
-> description TEXT NULL, => 글의 내용. 텍스트로 구성됨. 비워진 상태도 가능.
-> created DATETIME NOT NULL, => 생성 시점. 그 시점의 날짜와 시각을 기록. 비워둘 수 없음
-> author VARCHAR(30) NULL, => 글쓴이. 30글자를 넘으면 잘림. 비워둘 수 없음
-> profile VARCHAR(100) NULL, => 글쓴이의 세부사항. 100글자를 넘으면 잘림. 비워둘 수 없음
-> PRIMARY KEY(id)); => 가장 중요한 칼럼(각 글을 구별하는 키)를 id로 설정
9. CRUD
데이터베이스에 있어서 가장 중요한 네 가지는 CRUD로 요약된다.
Create : 자료의 생성.
Read : 자료의 열람.
Update : 자료의 수정.
Delete : 자료의 삭제.
이 중에서도 생성과 열람은 특히나 자료의 본질이 되며 빠질 수 없다.
수정과 삭제는 비교적 부차적인 요소이며 어떠한 분야에서는 금지되거나 죄악시되기도 한다.
(수정/삭제되어서는 안 되는 사실 기록이나 회계 장부 등. 조작이 금지되는 분야가 분명히 있다.)
10. INSERT
그러면 가장 중요한 create를 하는 방법을 알아보자. 요컨대 테이블에 자료를 '넣는' 방법에 대한 이야기이다.
SHOW TABLES를 입력하면 그 데이터베이스 안에 있는 테이블들의 리스트를 뽑아 준다.
여기서 DESC topic을 입력하면 (DESC는 describe의 약어이다) topic이라는 테이블에 어떤 필드(칼럼명)이 있는지,
거기에 들어가야 할 자료명은 어떤지, 입력하지 않고 비워놓을 수 있는지, 프라이머리 키는 무엇인지 등의
테이블을 만들 때 입력했던 정보들을 테이블 형태로 보여준다.
이것을 참고하여 insert문을 쓰면 된다.
row를 만드는, 즉 정보를 집어넣는 쿼리는 INSERT로 시작한다.
> INSERT INTO 테이블명(필드명1, 필드명2, 필드명3...) VALUES(필드값1, 필드값2, 필드값3...);
이런 식으로 적는다. 잘못 적었다면 프롬프트에서 위 키를 눌러서 썼던 명령어를 불러와서 수정하여 다시 적으면 된다.
만든 테이블을 보려면
> SELECT * FROM 테이블명;
을 적으면 된다. 테이블에서 모든(*) 정보를 열람하겠다는 뜻이다.
이 정도만 알면 나머지는 간단하다. 위 키를 눌러서 INSERT문을 불러오고, 몇 부분을 수정해서(주로 VALUES 부분이 될 것이다) 엔터를 치고 하는 작업을 반복하면 쉽게 여러 개의 row를 생성할 수 있다.
11. SELECT
SELECT문은 테이블에서 원하는 부분을 골라(SELECT) 보여주도록 하는 쿼리문이다.
> SELECT 필드명1, 필드명2, 필드명3 FROM 테이블명;
: 선택한 테이블에서 선택한 필드(열) 부분만 잘라 보여준다.
셀렉트문은 FROM을 생략하는 것도 가능하다. 리터럴 값만으로 컬럼을 출력할 수도 있고,
식을 입력하면 컬럼을 통해 그 값을 출력해 주기도 한다.
WHERE 옵션을 사용하면 출력 결과를 더 한정해 준다. 특정 필드 값으로 어떠한 값을 지니는 행만 출력하고 싶다면
WHERE 필드명=목적 값; 옵션을 덧붙이면 된다.
WHERE 옵션은 반드시 FROM 옵션 뒤에 와야 한다. (부가적인 요소일수록 뒤에 온다고 외워두자.)
옵션은 원하는 만큼 (그리고 출력이 가능한 만큼) 계속 덧붙일 수 있다. 위에서 뽑았던 결과 테이블을 id 기준으로 하여 내림차순으로(descending) 정렬한 것을 보고 싶다면 이 옵션을 붙이면 된다.
ORDER BY id DESC;
위의 테이블에서 출력하고 싶은 행 갯수도 제한할 수 있다. 최대 n개까지의 행만 보고 싶다면 이 옵션을 적자.
LIMIT n;
12. UPDATE
이번에는 테이블에 이미 들어간 행의 내용을 수정하는 방법을 배워보자.
수정하는 쿼리는 UPDATE로 시작한다. 이렇게 적어주면 된다.
> UPDATE 테이블명 SET 수정할 필드1='수정할 값1', 수정할 필드2='수정할 값2' WHERE 조건식;
이렇게 하면 조건식에 맞는 행의 필드값이 입력한 값대로 수정된다.
조건식의 내용은 아무거나 좋지만 수정하고 싶지 않은 행의 내용까지 수정되는 사태를 막으려면
그 행만을 식별할 수 있는 조건으로 하는 것이 좋을 것이고, 보통은 식별자로 프라이머리 키 값을 입력한다.
13. DELETE
수정까지 배웠다면 이제는 삭제를 배울 차례이다. 행을 삭제하는 방법을 알아보자.
삭제 쿼리는 DELETE로 시작하고, 대상이 될 테이블이 필요하므로 반드시 FROM 옵션이 들어가야 한다.
아무 옵션도 입력하지 않고 FROM 테이블명까지만 쓰면 해당 테이블 전체가 삭제되어 버린다.
그 테이블의 그 행만 삭제하고 싶다면 이렇게 갖춰서 적어주자. 삭제는 항상 신중해야 한다.
> DELETE FROM 테이블명 WHERE 조건식;
수정에서와 마찬가지로 조건식은 그 행에만 해당하는 식을 넣어줄 필요가 있다.
해당하는 행이 없어도 에러가 뜨지는 않는다. 아무 행도 삭제되지 않을 뿐이다.
삭제는 문제가 발생하기 정말 쉬운 부분이므로 항상 주의하여 사용하도록 한다.
14. 수업의 정상 ~ 15. 관계형 데이터베이스의 필요성
관계형 데이터베이스의 장점은 1) 유지보수의 용이성과 2) 데이터의 가독성을 모두 챙길 수 있다는 것에 있다.
예시를 보자. 스프레드시트로 나타낸 표이다. 위의 표에서 중복되는 데이터를(작가, 작가 프로필)
별도의 테이블로 빼고 작가에 아이디를 부여한다. 그리고 우하단의 테이블에는 작가의 아이디만을 기재한다.
작가에 대한 세부정보가 필요할 때는 좌하단의 테이블을 참고한다.
이렇게 테이블을 분리함으로써 중복을 최대한 줄이고, 용량을 줄이며 유지보수나 변경을 편하게 할 수 있다.
하지만 매번 이렇게 따로 보면 불편한 것이 당연하다. 어떨 때는 상단의 표처럼 합쳐진 형태의 테이블을 보며
데이터를 한눈에 확인하고 싶을 것이다.
이 기능을 관계형 데이터베이스는 둘 다 수행할 수 있다.
이미 테이블을 분리해 둔 상태의 DB이다. author이라는 표와 topic이라는 표로 테이블이 나뉘었다.
여기서 JOIN 기능을 사용하고 적절한 조건을 입력해 주면 DB가 알아서 표를 합쳐 보여준다.
실제로 표가 합쳐진 것은 아니나, SELECT문으로 열람할 때만큼은 마치 하나의 표처럼 볼 수 있다.
이런 면에서 관계형 데이터베이스는 효율적인 것이다.
16. 테이블 분리하기 ~ 17. JOIN
강의 자료 사이트에서 제공하는 코드대로 각각의 분리된 테이블을 준비해 두었다.
테이블을 조인(결합) 하는 쿼리문은 본질적으로 SELECT문을 사용한다.
> SELECT * FROM 테이블명1 LEFT JOIN 테이블명2 ON 조인 조건;
위 조인은 topic.author_id=author.id,
그러니까 topic 테이블의 author_id 필드가 author 테이블의 id 필드와 같다는 조건을 이용하여 시행한 것이다.
위 표에서 중복되는 author_id와 id 항목을 제외하고 보고 싶으면 해당 항목을 제외한 필드명을 * 자리에 모두 넣고 다시 한 번 셀렉트문을 돌리면 된다.
이때 id라는 필드가 중복되어 필드 리스트가 모호하다는 메시지가 뜰 수 있으므로
이 경우에는 topic.id 등과 같이 표기하여 어느 테이블의 아이디인지 명시해 주도록 한다.
18. 인터넷과 데이터베이스
데이터베이스도 기본적으로 웹과 동일하게 서버와 클라이언트가 존재한다.
데이터를 저장해 놓고 있는 서버 컴퓨터가 웹을 통해 클라이언트에게 데이터를 전달하면
클라이언트 프로그램은 그것을 받아 사용자에게 보여주는 것이다.
mysql의 경우 클라이언트에 크게 두 가지가 있다.
하나는 우리가 실습에 사용했던 것과 같은 프롬프트 형식의 mysql 모니터이고,
다른 하나는 mysql 워크벤치라고 해서 GUI 형식의 클라이언트 프로그램이 따로 있다.
이러한 클라이언트들을 사용해 보면서 천천히 데이터베이스가 웹과 결합하는 형태에 대해 배워볼 것이다.
19. MySQL Client
앞서 말했던 것처럼 mysql 모니터와 mysql 워크벤치에는 각각의 장단점이 있다.
모니터: 프롬프트 형식이기 때문에 어디에서나 적용될 수 있고 쉽게 사용할 수 있다. GUI를 지원하지 않는 환경에서도 사용이 가능하다 / 모든 것을 명령어로 제어해야 하기 때문에 직관성이 떨어진다. 명령어를 외워야 한다
워크벤치: 직관적이다. 명령어를 외울 필요가 없으며 보이는 대로 손쉽게 제어할 수 있다 / 지원하지 않는 환경도 있을 수 있다. 명령어로는 한 줄로 처리할 수 있는 작업을 여러 번의 클릭으로 처리해야 하는 경우도 생기기 때문에 더 번거로워지는 경우도 생길 수 있다
상황에 맞게 사용하여 장점만을 취하는 것이 중요하다. 클라이언트 프로그램은 검색을 통해 쉽게 찾고 얻을 수 있다.
20. MySQL Workbench
마리아디비도 기본적으로 mysql을 기반으로 하기 때문에 같은 워크벤치를 쓸 수 있다.
모니터에서 작성했던 스키마와 테이블들이 그대로 표시되고 있는 것이 보인다.
셀렉트문 쿼리를 친 다음 번개모양을 누르면 결과값이 하단에 표시된다.
새 스키마를 만들 수도 있다. 워크벤치라는 이름의 스키마를 만들었다.
GUI에서 작업을 하면 해당되는 쿼리문이 확인차 나타나는데, 읽어보고 Apply를 누르면 적용된다.
테이블을 새로 만들 수도 있다. Column Name에서 필드명을 작성하고, Datatype을 기입한 뒤
옵션에 맞게 우측에 체크를 해 주면 된다.
기본적인 사용법은 모니터에서 하던 것과 같거나 더 쉽다.
21. 수업을 마치며
수업을 마치며, 데이터베이스를 사용할 때 알아두면 좋을 몇 가지 포인트들을 짚고 넘어간다.
인덱스 : 데이터베이스의 양이 방대해지면 정리에 문제가 생길 수 있다. 이런 경우에는 인덱스라는 키워드를 서치해서 적용하면 좋다.
모델링 : 데이터베이스를 사용할수록 정규화라는 키워드가 눈에 들어올 것이다. 더 효율적인 테이블 구조에 대해서 고민하고자 한다면 이쪽으로도 정보를 찾아보는 것이 좋다.
백업 : 굳이 데이터베이스가 아니더라도 자료를 관리하는 입장에서는 무시할 수 없는 키워드이다. 하드 장치는 언제 고장날지 알 수 없고 어떤 사고가 생길지도 알 수 없으므로 미리미리 백업에 대해서 배워두는 것이 좋을 것이다.
클라우드 : 방대한 데이터를 기업체의 서버에 위탁하는 것이다. 내 컴퓨터에 그만큼의 용량이 없더라도 저장공간을 빌리거나 사면 데이터가 유실될 걱정 없이 데이터베이스를 사용할 수 있다.