MySQL 기초 정리

연관된 표들을 그룹화 한 것을 데이터베이스라고 표현할 수도 있지만, 이를 Schema라고 표현할 수 있다.

MySQL을 설치한 것은 곧 데이터베이스 서버를 설치한 것이다.

간단한 용어들

Theme. 테이블 만들기

show databases;
drop database opentutorials;
create database opentutorials;
use opentutorials;
 
CREATE TABLE topic (
id INT(11) NOT NULL 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)
);
/*
"topic"이라는 이름의 table을 만들었다. 위 코드에 대한 설명을 하자면, 
Not Null: 값이 없는 것을 허용하지 않겠다.
auto_increment: 기본적으로 시작값은 1이며, 각 새 레코드가 추가 될때마다 1씩 자동증가 한다.
int(11)에서, int는 Integer이고 11은 table에서 몇 개까지 노출시킬 지를 정한 것(11개만 저장할 수 있다는 것이 아니다)
varchar(100)에서 varchar는 문자열의 데이터 타입을 의미하고, 100은 100"글자"를 말한다.
text: varchar보다 긴 문자열의 데이터 타입
datetime: 날짜와 시간을 모두 표현할 수 있는 데이터 타입
primary key(column명): 괄호 안에 있는 column명이 가장 중요한 컬럼. 예컨대 이를 기준으로 다른 값들과 중복되지 않게 구분할 수 있다.
*/
 

DESC topic;

위 코드에서 나온 용어들에 대해 정리해보면,

NULL, NOT NULL

NULL: 값이 없다

NOT NULL: NULL 값을 가지지 못하도록 지정한다.

 

cf) SQL의 주요 datatypes

1) 정수

 INT

2) 수치

 DECIMAL: 전체 자릿수, 소수 자릿수가 고정된 숫자 데이터 형식. 예를들어, DECIMAL(5,2)의 형식인 경우 234.5678은 234.58이 된다. (전체 자릿수 5개, 소수 둘째짜리까지 반올림)

3) 실수

 FLOAT

4) 날짜 및 시간

 DATE: 날짜

 TIME: 시간

 DATETIME: 날짜와 시간

5) 문자열

 VARCHAR, TEXT(이때, TEXT가 VARCHAR보다 최대 문자열 길이가 더 길다)

 

Theme. CRUD

Create: 생성한다

Read: 읽는다

Update: 수정한다.

Delete: 삭제한다

어떤 데이터베이스든 반드시 가지고 있는 4가지의 작업.

각각의 작업이 SQL에서 어떻게 이루어지고 있는지 하나씩 살펴보도록 하자.

 

1. Create

INSERT문을 이용한다.

desc topic; -- desc: 테이블의 어떤 column이 있는지와 구조는 어떤지 나타냄
 
-- Create
INSERT INTO    topic (title, description, created, author, profile)
VALUES('MySQL', 'MySQL is..', now(), 'egoing', 'developer');
-- INSERT문을 통해 데이터를 생성(Create)할 수 있다.
-- id는 자동으로 증가하니까 생성해줄 필요가 없다.
 
INSERT INTO    topic (title, description, created, author, profile)
VALUES('Oracle', 'Oracle is..', now(), 'egoing', 'developer');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('SQL Server', 'SQL Server is..', now(), 'duru', 'data administrator');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('PostgreSQL', 'PostgreSQL is..', now(), 'taeho', 'data scientist, developer');
INSERT INTO    topic (title, description, created, author, profile)
VALUES('MongoDB', 'MongoDB is..', now(), 'egoing', 'developer');
SELECT * FROM topic; -- 모든 행 조회

Create

2. Read

SELECT문을 이용한다.

-- 선택한 column에 대한 모든 행을 조회
SELECT id, title, created, author 
FROM topic;

-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing';

-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회 + 정렬(내림차순)
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing' ORDER BY id DESC;

-- 선택한 column에 대한 모든 행을 조회 + 일정한 값을 가진 행만을 조회 + 정렬(내림차순) + 출력되는 행의 개수(2개) 설정
SELECT id, title, created, author 
FROM topic WHERE author = 'egoing' ORDER BY id DESC LIMIT 2;

3. Update

위 결과에서 id  = 2일 때의 title의 Oracle은 'ORACLE'로, description의 Oracle is는 'ORACLE is'로 수정해보자.

이때, WHERE와 같은 제어문을 반드시 주의해서 써줘야한다. 자칫 원하는 행뿐만 아니라 앞의 조건들을 만족한 행들의 값들이 한번에 수정되어 버리는 참사가 벌어질 수 있기 때문이다.

-- id가 2인 행의 title, description 값을 수정해주는 방법
UPDATE topic SET title = 'ORACLE', description = 'ORACLE is..' WHERE id = 2;

id = 2인 행의 값들이 수정된 것을 확인할 수 있다.

 

4. DELETE

위의 전체 행에 대한 결과에서 MongoDB에 대한 행을 지워보자.

-- MongoDB를 삭제해보자.
DELETE FROM topic WHERE id = 5;
SELECT * FROM topic;

 

Theme. 관계형 데이터베이스의 이해(Relational Database)와 JOIN을 위한 테이블 나누기

-- Relational Database(관계형 데이터베이스)의 이해
-- 먼저, topic 테이블을 쪼개보자.
RENAME TABLE topic TO topic_backup;
SELECT * FROM topic_backup;
DESC topic_backup;
--
-- Table structure for table `author`
--
 
CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `profile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `author`
--
 
INSERT INTO `author` VALUES (1,'egoing','developer');
INSERT INTO `author` VALUES (2,'duru','database administrator');
INSERT INTO `author` VALUES (3,'taeho','data scientist, developer');
SELECT * FROM author;
DESC author;
--
-- Table structure for table `topic`
--
 
CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` text,
  `created` datetime NOT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
 
--
-- Dumping data for table `topic`
--
 
INSERT INTO `topic` VALUES (1,'MySQL','MySQL is...','2018-01-01 12:10:11',1);
INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...','2018-01-03 13:01:10',1);
INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...','2018-01-20 11:01:10',2);
INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...','2018-01-23 01:03:03',3);
INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...','2018-01-30 12:31:03',1);
SELECT * FROM topic;
DESC topic;

그 결과, 3가지 테이블에 대한 정보는 다음과 같다.

1. topic_backup(기존의 topic)

DESC topic_backup;

 

SELECT * FROM topic_backup;

 

2. author

 

DECS author;

 

SELECT * FROM author;

3. topic(새로운 topic)

DESC topic;

SELECT * FROM topic;

 

Theme. 관계형 데이터베이스의 이해(Relational Database)와 JOIN

JOIN을 통해 독립적인 테이블들을 읽을 때, 마치 하나인 것처럼 읽을 수 있게 된다.

위 topic과 author 테이블을 JOIN 해보자.

두 테이블이 결합될 수 있는 지점은 topic 테이블에서는 author_id, author 테이블에서는 id이다.

그래서 두 개의 테이블을 그 지점을 이용해서 합성해보면,

-- JOIN
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
-- topic 테이블과 author 테이블을 author_id, id의 지점에서 JOIN한다.

 

JOIN 결과

이를 중간의 author_id와 id column은 제외하고 보기 좋게 바꾸면,

(이때, id라는 column명이 겹치는데, 맨 앞의 id를 topic.id라고 명시해줘야 한다.)

즉, SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

라고 적는다. 그 결과,

이렇게 JOIN을 해놓으면 각각의 테이블을 독립적으로 관리하면서 수정하더라도, 다른 테이블을 다시 각각 고쳐줄 필요가 없는 장점이 있다. 

즉, 테이블 A,B,C가 있을 때, A와 B, A와 C를 각각 JOIN해줬다면,

A의 데이터를 수정하게 되었을 때, B와 C도 별도의 수정없이 자동으로 동시에 수정되게 된다.