MySQL (1) - 테이블 생성, CRUD, JOIN

인류는 오래전부터 컴퓨터를 이용해서 정보를 장하고 관리하고 싶어했으며, 그러한 마음에서 File이라는 정보관리 도구가 생겨나게 되었다.
하지만 File만으로는 정보를 효과적으로 입력, 저장, 출력하기가 어려워지면서
데이터를 잘 정리정돈하여 필요할 때 쉽게 꺼내 쓰고싶은 욕심이 생겼다.
파일의 한계를 극복하기 위해 만들어진 소프트웨어들을 DATABASE 라고 부른다.
데이터베이스 이후 관계형 데이터베이스가 출현하였고 데이터베이스의 절대 강자가 되었다.

-유튜브 '생활코딩' 中

 

관계형 데이터베이스는 데이터를 표의 형태로 정리정돈 할 수 있고 정렬, 검색과 같은 기능을 빠르고 안전하게

할 수 있으며 MySQL, Oracle, SQL Server, DB2와 같은 제품들이 모두 관계형 데이터베이스이다.

이 중 MySQL은 무료이고 오픈소스이며 관계형 데이터베이스의 대부분의 기능을 갖추고 있다.

WEB 개발자들의 좋은 대안이 되었고, 웹과 함께 MySQL은 동반성장하기 시작했으며 현재도 MySQL로부터 수 많은 웹사이트들이 동작한다.

 

• 엑셀의 스프라이드 시트와 데이터베이스의 차이점은?

데이터를 표의 형태로 표현해준다는 것과 전체적인 기능은 서로 비슷하다.(공통점)

스프라이드 시트와 달리 데이터베이스는 코딩을 통해, SQL이라는 컴퓨터 언어를 통해 제어할 수 있다.

즉, 데이터베이스에게 말을 거는 것이다.

스프라이드 시트는 사용자가 손으로 클릭하는 동작을 통해 데이터를 제어한다.

 

데이터베이스를 구축하면, 데이터베이스에 저장된 데이터를 웹이나 앱을 통해 공유할 수 있고

빅데이터나 인공지능을 통해 분석할 수도 있다.

출처 https://www.youtube.com/watch?v=h_XDmyz--0w&list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW

 

 

MySQL의 구조

 

데이터를 기록하는 최종적인 곳은 표(table) 이다.

MySQL, 관계형 데이터베이스는 엑셀과 같은 스프라이드 시트와 비슷한 구조를 가졌다.

사용할수록 표들은 점점 많아지게 되어 있고, 표가 늘어나면 정리정돈할 필요성이 생긴다.

MySQL에서는 서로 연관된 표들을 그룹핑해서 연관되어있지 않은 표들과 분리한다. (테이블이 파일이라면 그것을 구분하는 일종의 폴더같은 개념)

이러한 디렉토리를 데이터베이스라고 한다.

MySQL 에서는 데이터베이스라는 표현과 함께 스키마라는 표현을 함께 쓴다(데이터베이스 = 스키마)

즉, 스키마는 표들을 그룹핑할때 사용하는 일종의 폴더다. 연관된 표들을 그룹핑해준다.

그렇게 스키마들이 많아지면 이것을 데이터베이스 서버에 저장한다.

MySQL을 설치했다는 것은, 데이터베이스 서버라는 프로그램을 설치했다는 것이다.

출처 https://www.youtube.com/watch?v=d3ye07XRexs&list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW&index=13

서버 접속

데이터베이스를 사용했을 때 얻을 수 있는 효용 중 하나는 보안이다.

https://www.boannews.com/media/view.asp?idx=103257 

[긴급] 거의 모든 서버가 위험하다! 매우 치명적인 ‘로그4j’ 보안 취약점 발견

파일은 운영체제만 뚫리면 막 수정할 수 있지만, 데이터베이스는 자체적인 보안 체계를 가지고 있기 때문에

좀 더 안전하게 데이터를 보관할 수 있다.

또한 데이터베이스에는 여러 사람을 등록할 수 있는데, 권한 기능이 있어 원하는 사용자에게 특정 권한을 줄 수 있다.

기본 유저는 root라고 하며, 관리자이기 때문에 모든 권한이 열려있다.

중요한 시스템이라면 별도의 사용자를 추가로 만들어서 평소에는 그 사용자로 작업하는 것을 권장한다.

 

스키마의 사용

• 서버에 들어왔다면 스키마(=표들을 저장하는 데이터베이스)를 만들어서 표를 만들 준비를 한다.
• 스키마를 만들기 위해서는 CREATE DATABASE ; 키워드를 사용한다.
• 스키마를 삭제하기 위해서는 DROP DATABASE ; 키워드를 사용한다.
• 스키마를 보려면 show databases ; 키워드를 사용한다.
• 특정 스키마를 사용하려면 USE 키워드를 사용한다. 지금부터 내리는 명령은 해당 데이터베이스에게 내린다는 뜻

서버와 데이터베이스를 확인했다면 표를 만들어 볼텐데, 그 전에 SQL에 대해 알아보자.

SQL이란 컴퓨터의 언어로, Structured Query Language의 약자인데

Structured는 표를 정돈하는 것을 구조화 되었다고 표현하는 것이며

Query는 데이터베이스에게 내리는 명령어이다.

SQL은 어떤 컴퓨터 언어보다도 쉬우면서도, 관계형 데이터베이스에 속하는 제품들이 공통적으로

데이터베이스 서버를 제어할 때 사용하는 언어이므로 중요하다.

 

 

표(Table)

표는 영어로 Table이라고 부른다.

테이블의 X축을 row, record, 또는 행이라고 부른다. 데이터 자체를 뜻한다.

테이블의 Y축을 column, 또는 열이라고 부른다. 데이터의 타입이나 구조를 뜻한다.

출처 https://www.youtube.com/watch?v=d3ye07XRexs&list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW&index=13

해당 테이블에는 2개의 row, 4개의 column이 있다.

 

 

테이블의 생성

출처 https://www.youtube.com/watch?v=d3ye07XRexs&list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW&index=13

위의 표를 데이터베이스에 추가해보자.

여기서 컬럼은 title, description, created, author, profile이다.

테이블에 컬럼을 추가하는 방법은 다음과 같다.

//테이블을 생성하고 컬럼 추가하기
CREATE TABLE 테이블명(
컬럼 이름 타입(길이) NULL 유무, auto 등등
PRIMARY KEY(컬럼));

//topic 테이블을 생성하고 컬럼 추가하기
CREATE TABLE topic(
id INT(11) NOT NULL AUTO_INCREMENT, //AUTO_INCREMENT : 자동증가
title VARCHAR(100) NOT NULL, 
description TEXT NULL,
created DATETIME NOT NULL,
author VARCHAR(30) NULL,
profile VARCHAR(100) NULL,
PRIMARY KEY(id));

 

 

데이터의 CRUD

테이블을 생성하고 컬럼을 추가했다면, 이제 컬럼 안에 데이터를 추가할 수 있다.

데이터베이스의 본질은 CRUD이며 모든 데이터베이스는 CRUD라는 공통분모를 가진다.

 

1. 데이터를 추가하는 것을 Create 라고 한다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

DESC 키워드를 사용하여 테이블의 전체적인 구조(컬럼의 상태)를 알 수 있다.

topic 테이블에 추가한 id, title, description, created, author, profile 컬럼의 상태를 확인할 수 있다.

이것을 참고하여 컬럼 안에 데이터를 넣는다. 키워드는 INSERT INTO 이다.

MySQL에 대한 데이터를 테이블에 추가하였다. NOW()는 현재 시간을 뜻하며

id는 자동으로 값이 1씩 증가하는 AUTO_INCREMENT 이기 때문에 굳이 값을 적지 않았다.

Query OK! 구문이 나오면 데이터를 넣는 것에 성공한 것이다.

 

 

2. 추가한 데이터를 읽어오는 것을 Read라고 한다. 

SELECT 문을 사용하며 읽어오는 방법을 여러 가지로 제약할수 있기 때문에 경우에 따라 복잡할 수도 있다.

topic 테이블로 데이터를 가져오고 싶다면? SELECT FROM 키워드를 사용하여

위에서 INSERT 문으로 추가했던 데이터를 확인할 수 있다.

 

컬럼을 제약하여 id, title, author 출력하고 싶다면?

SELECT id, title, author FROM topic;

행을 제약하여 author의 값이 egoing인 행만 가져오고 싶다면?

SELECT * FROM topic WHERE author='egoing';

id 값을 기준으로 큰 숫자가 나오게 정렬하고 싶다면?

SELECT * FROM topic ORDER BY id DESC; //작은 순으로 정렬은 ASC;

4건 중 2건의 데이터만 출력하고 싶다면?

SELECT * FROM topic LIMIT 2;

이런 식으로 SELECT 문은 여러가지 제약이 가능하다.

 

 

3. 데이터를 수정하는 것을 UPDATE라고 한다.

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

현재 topic 테이블은 위와 같다. 

여기서 id가 5인 SQL Server의 title과 description을 수정하고 싶다면

UPDATE SET 구문을 사용하여 수정이 가능하다.

SQL Server는 SQL server로, 내용은 SQL server is...로 위와 같이 변경된 것을 확인할 수 있다.

 

 

4. 데이터를 삭제하는 것을 DELETE 라고 한다.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

데이터를 삭제할때는 DELETE FROM 키워드를 사용한다.

WHERE id = 5; 로 id가 5번인 행을 삭제하였다.

 

데이터를 수정하거나 삭제할 때 주의할 점은, WHERE문을 잘 확인해야 한다는 것이다 (데이터가 전부 수정되거나 삭제되는 참사가 일어날수도 있다)

 

 

왜 관계형 데이터베이스는 다른 데이터베이스와 구분되는 걸까? 관계형 데이터베이스의 필요성

맨 위의 표인 topic 테이블은 author와 profile 컬럼의 데이터가 중복되고 있다.

이렇게 데이터의 중복이 발생하면 용량이 낭비되고 하나를 수정하면 전체를 수정해야 하는 등 여러가지 애로사항이 생긴다. 

따라서 중복을 제거하기 위해 아래와 같이 topic 테이블에서 author와 profile의 컬럼을 따로 떼어내어

author 테이블을 새롭게 만들고, 기존의 topic 테이블은 author_id로 연결하는 작업을 한다.

이렇게 되면 중복이 제거되고 author 테이블이 변경되면 author 테이블을 참조하는 모든 테이블의 데이터가 자동으로 변경되어 유지보수가 훨씬 편해진다.

 

하지만 별도의 author 테이블을 만들면

기존의 topic 테이블은 직관적으로 데이터를 한 눈에 볼 수 있었으나, 테이블을 쪼개서 참조값만을 적어놓게 되니

그 행에 해당되는 별도의 표를 일일이 찾아가 값을 확인해야하는 불편 사항이 생긴다.

데이터를 별도의 테이블로 보관하여 중복을 발생시키지 않으면서도,

실제로 데이터를 볼 때는 하나의 표로 합쳐진 결과로 볼 수 있는 방법은 없을까?

데이터를 저장할때는 분산되어서, 보여줄때는 합쳐서 보고싶은 관점으로 JOIN이 생겨나게 되었다.

 

각각 분리된 테이블을 읽을 땐 마치 하나의 테이블로 저장되었던 것처럼 바꿀 수 있는 JOIN은 관계형 데이터베이스의 꽃이라고 할 수 있다.

기존에 이렇게 분리되어 저장된 topic과 author 테이블은

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

JOIN을 사용하면 하나의 테이블로 연결하여 읽을 수 있다.

여기서 ON은 무엇으로 연결할지를 나타내며 author의 id 값으로 연결하였다.

 

JOIN으로 연결된 두 테이블의 컬럼 중 id, title, description,created만 보고 싶다면?

이름을 바꾸고 싶다면 AS를 사용한다.

 

 

 

 

 

 

 

해당 게시글은 '생활코딩' 님의 MySQL 강의를 참고하였습니다.

출처 https://www.opentutorials.org/course/3161