[인프런] <Chap 3> MySQL 기본

1. MySQL의 구조

- 데이터를 최종적으로 저장하는 곳 : 표(table)

 

- 표가 매우 많아짐(글 저장 표, 댓글 표, 회원정보 저장 표 .... )

   -> 나눠진 표들을 잘 정리해야 할 필요 생김(file에서의 디렉토리 처럼)

 

- 연관된 표를 그룹화한 폴더 : 데이터베이스(database) = 스키마(schema)

 

- 스키마들의 집합 : 데이터베이스 서버(database server)에 저장

---

2. MySQL 스키마의 사용

- 명령어 하나하나를 뭐 외울필요는 없다. 구글링 필수

 

- SHOW DATABASE : 데이터 베이스 항목 나열

- USE opentutorials : 해당 데이터베이스로 변경

 

 

---

3. SQL과 테이블 구조

- SQL : Structured Query Language

   -> Structured : 구조화 된(정리화 된)

   -> Query : 데이터베이스에게 요청(질의)한다는 의미

   -> Language : 데이터베이스도 이해하고, 나도 이해할 수 있는 공통의 약속 언어

   -> 매우 쉽고 중요하다.

 

- 관계형 데이터베이스들을 제어하기 위해 표준화된 언어이다.

- 우리도 모르게 SQL에 의해서 수많은 정보 서비스를 경험하고 있다.

 

 

- row, record : 행 -> 위에는 2개의 행(데이터 2건)

- column : 열  ->  위에는 4개의 열(데이터의 타입 / 구성)

 

 

---

4. MySQL 테이블의 생성

 

다음 Spread Sheet 기준으로 데이터베이스를 형성할 것이다.

 

- CREATE TABLE topic : 'topic'이라는 이름의 table 생성

1열 :  id INT(11) NOT NULL AUTO_INCREMENT -> 이름은 id, 11자리까지 가능하며 null이 들어오면 안되고, 자동으로 증가시킴(++)

2열 : title VARCHAR(100) NOT NULL -> 이름은 title, 100자까지 variable하게 가능하며, Null이 들어오면 안된다.

3열 : description TEXT NULL -> 이름은 description, TEXT(max size : 65,535 characters), Null 들어와도 된다.

4열 : created DATETIME NOT NULL -> 이름은 created, 날짜 시간을 제공하는 형식으로 표현하며, Null 들어오면 안된다.

5열 : author VARCHAR(15) NULL -> 이름은 author, 15자 까지 가능하며, Null 들어와도 된다(익명).

6열 : profile VARCHAR(200) NULL -> 이름은 profile, 200자 까지 가능하며, Null 가능.

마지막 : PRIMARY KEY(id) -> Primary key를 id로 지정한다. 일단 중복 방지로 알아두자. 

 

- SHOW TABLES 하면 만든 테이블 목록이 뜬다.

 

- 테이블 생성 중 위의 에러가 뜰 경우,

-> SET PASSWORD = PASSWORD("password 다시 작성") 을 통해, 비밀번호 설정 후 다시 테이블을 만들면 성공한다.

 

---

5. MySQL CRUD

- Create : 생성, 필수

- Read : 읽어오기, 필수

- Update : 수정, 없을수도 잇음

- Delete : 삭제, 없을수도 있음

 

 

5.1 SQL의 Insert 구문

 

DESC topic을 통해, topic table row(field)의 Description을 볼 수 있다. 이걸 기반으로 Insert를 하면 수월하다.

- Insert(=CREATE 역할)를 통해 테이블에 데이터를 넣을 수 있다.

- 첫번째 괄호에 row 목록들을 쭉 작성(DESC topic으로 참고)

- 두번째 괄호에 VALUES ( ~ ) 방식으로 순서대로 value를 입력한다.

   null 가능하다 했으니, 그 부분은 첫번째 괄호, 두번째 괄호에 둘 다 포함 안시키면 된다.

- SELECT * FROM topic 을 통해 방금 넣은 데이터를 확인할 수 있다. (READ)

 

 

5.2 SQL의 SELECT 구문

- Create, Delete, Insert는 쉬우나, Select는 좀 어려울 수 있다.

 

 

- 대괄호 : 생략 가능

- 대괄호의 순서 중요!

 

 

- FROM 없이 SELECT ~에서 ~부분은 select_expr을 의미한다. from 없으므로 그냥 입력된 항목 그대로 출력한다.

- SELECT id, title, created, author FROM topic WHERE author='egoing';

   -> topic에서 id ~ author 열을 가져와라, 근데 또 거기서 author가 egoing인 것만 가져와라라는 의미

 

 

- ORDER BY(필수 x) id(col_name) DESC(역순)

   -> 가져온 데이터를 id 역순으로 보여줘라

 

- 데이터가 엄청 많을 수 있다. 다 출력하면 과부하 가능성 있음.. 일부만 보여주기를 원할때

   -> LlMIT 1 -> 1개만 보여줘라

 

 

- 등등, 언급안된 수많은 SELECT 방법이 있다. 일단 기초먼저 알고, 이후에 필요할 때 구글링을 통해서 SQL을 만들 수 있다.

 

 

5.3 SQL의 UPDATE 구문

- 수정에 대한 것을 배워보자.

 

 

- UPDATE topic SET description='Oracle is ....!!', title='ORACLE';

   -> 전체 row에 대해서, description과 title을 모두 사용자가 지정한 value로 업데이트한다는 의미이다.

   -> 따라서 WHERE를 통해 id를 전달하여 변경하고자 하는 row를 지정해 줘야 한다.

   -> SET에서 value가 2개(description, title)이길래, 뒤에 title이 뭔 의미인가 했는데 걍 둘 다 바꾼다는 의미이다. 여기서 혼자 낚임;

 

 

 

-> 겨우 겨우 UPDATE를 통해 description, title을 id 지정하여 동시에 변경해준다. 물론 id 외에 다른 row로도 지정 가능하다.

-> 무조건 WHERE를 통해 범위 지정을 필수로 해주자!

-> CREATE / DELETE등으로 어쨌든 테이블이 수정되면, 이전으로 복귀 절대 불가이다.. 

 

 

5.4 SQL의 DELETE 구문

- 여기도 WHERE 중요, 빼먹으면 모든 행 삭제;;;

 

 

---

6. 더 배워보기

- 데이터베이스 상관 없이 적용되는 본질 : CRUD

- 추가로 더 깊게 들어가봅시다.

 

 

6.1 관계형 데이터베이스의 필요성

- Spread Sheet이용해서 관계형 데이터베이스의 필요성에 대해서 알아봅시다.

 

- author, profile 데이터가 중복된게 3개 보인다. 한 1000개 이상 중복되었다고 가정하자.

- 같은 데이터 중복은 굉장히 경제적으로 손해이다.

- 또한 데이터 처리하는 과정이 매우 반복적이고 복잡할것이다.

 

 

- 다음과 같이 중복된 부분을 author table로 빼서 분리하였다.

- topic에는 author_id라는 이름으로, author와 매핑하였다.

 

 

- author 변경사항이 있을 경우, author 테이블에서만 변경해주는 방식으로 한결 유지보수가 쉬워진다. 

- egoing -> 이고잉으로 변경할 경우, topic에는 따로 변경해줄 사항이 없다. 즉 이전처럼 하나하나 다 바꿔줄 필요가 없어진다.

 

 

- 또한, name과 profile이 동일할 경우, 이게 같은사람인지 다른사람인지 구분할 방법이 없었다.

- author에 id를 통해 topic에 반영함으로써 동일 인물인지에 대한 정보를 추가로 제공할 수 있다.

 

But,

- 기존에는, topic(하나의 테이블)에 모든 정보가 있으므로, 직관적으로 모든 것을 알 수 있다.

- 변경된 사항으로, 별도의 표를 비교해가면서 봐야하는 큰 불편함이 생긴다.

-> 테이블을 분리해서 관리하면서도, 비교하는 불편함을 없앨 수 없을까? -> MySQL!

-> 저장은 분산해서, 보여줄땐 합해서 보여준다.

 

- author 테이블과 topic 테이블을 분리해서 관리하였다.

- 새로운 SELECT문을 통해서, 두 테이블을 합쳐서 한꺼번에 보여줄 수 있다. 

 

6.2 테이블 분리하기

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');
 
--
-- 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);

- 그냥 복사해서 쭉 치면 알아서 생성되고 들어간다.

`author_id` int(11) DEFAULT NULL, -> DEFAULT NULL에 유의를 해야 할까??

 

주석 관련

 

 

6.3 JOIN - 관계형 데이터베이스의 꽃

- 이를 통해, 분리된 테이블을 하나의 테이블 처럼 조회할 수 있다.

 

그 와중에, 터미널에서 커서 이동이 조금 귀찮았다;

 

- 위의 Spread Sheet 2개는 합치기 전이고, 아래 3개는 comment까지 추가하여 분리한 테이블이다.

- 두 테이블 간의 관계 뿐 아니라 author id와 매핑할 수 있는 인자를 가진 테이블들 모두 JOIN이 가능하다.

 

- select * from topic left join author on topic.author_id = author.id;와 같이 작성하면, 매핑된 결과가 나온다.

- 매핑된 부분인 author_id, id 부분을 보기 싫으면, * 부분에 원하는 column만 넣으면 된다.

- 이 때 id가 두 개이므로, topic.id 처럼 지정을 해줘야 한다.

- select topic.id as topic_id를 통해, 해당 id를 원하는 이름으로 출력할 수 있다.

 

- left join : 왼쪽 테이블을 기준으로 조회한다. 즉 위의 코드에서 topic이 왼쪽에 있으므로, topic 기준으로 author를 조회한다.

- right join : 오른쪽 테이블 기준으로 조회한다. 아래 다이어그램이 반대로 될 것이다.

 

- 위에는 author 기준으로 topic을 조회하였다. -> 이렇게 하면, topic의 원래 순서 그대로 출력된다.

- 아래는 topic 기준으로 author을 조회하였다. -> 이렇게하면 author의 원래 순서 그대로 출력된다.(1. egoing - 2. duru - 3. taeho)

- 뭔가 반대여야 맞는거 같긴한데, 일단 이게 맞다.

- on 절의 조건을 만족하지 않는 경우, 기준과 반대인 테이블의 필드 값은 null로 표현된다.

 

 

---

6.4 인터넷과 데이터베이스

 

- database Server에서 'Server' 란?

 

 

- 데이터베이스 클라이언트 : 우리가 사용한 mysql, 명령어 기반 프로그램(mysql monitor)

- 'MySQL workbench'를 이용해서 ui로 다룰 수도 있다.(클라이언트 하나 더 사용)

- 전세계에서 수많은 컴퓨터가 접속할 수 있도록 도와준다.

 

 

---

6.5 MySQL 클라이언트

- GUI가 아닌, 명령어 기반의 프로그램 - CLI (mySQL Workbench는 GUI) = mySQL monitor라고 함.

- mysql 서버가 있는 곳에 모니터가 있음. 언제든 사용할 수 있다.

- 명령어를 기억해야한다는 단점이 있음. 

 

 

---

6.5 MySQL Workbench

- CLI에서, /mysql -uroot -p -hlocalhost

   -> -h 부분에 서버에 해당하는 컴퓨터의 주소를 적어줘야 한다. (ex -hgoogle / -hfacebook)

 

- 서버와 클라이언트가 현재 같은 컴퓨터에 위치하고 있을 경우? 

   -> -hlocalhost = -h127.0.0.1

   -> -h 안치면, mysql이 설치된 컴퓨터를 그냥 가리키게 됨. 걍 디폴트 값임.

 

- connection name : my server

- host name : 현재 mysql이 설치된 server의 name(내 컴퓨터)

- username : ./mysql -uroot -> 이 부분임

 

- gui로 누르고 apply 누르면, 위와 같이 sql을 만들어 줌.

- 결국 모든 클라이언트는, sql을 서버에 전송함으로써 데이터베이스 서버를 제어하게 됨

- MySQL 서버를 대상으로, 정말 많은 MySQL 클라이언트가 있다.

- MySQL을 사용하는 모든 앱, 웹, 데이터 분석 시스템들은 본질적으로 MySQL 클라이언트이다.

 

 

---

6.5 Outro

- 지금까지 공부한거 레벨에서 복습

- Select 를 효율적으로 짜서, 원하는 데이터를 가져올 수 있도록 연습해야한다.

 

 

+ 추가 공부 사항

 

- index : 색인 기능

   -> 데이터가 들어올때, 데이터베이스가 그 column의 데이터를 잘 저장(별도로 관리)

   -> 이후에 검색 시, 빠른 속도로 검색할 수 있음

 

- modeling

   -> 데이터가 많아짐에 따라, 테이블 관리에 대한 모델링 방법도 중요하다.

 

- backup

   -> 하드디스크에 대해서 예측 불가능 : 언제 고장날지?  // 예측 가능 : 언젠간 고장난다!

   -> 따라서 데이터 백업 센터가 필수 (mysqldump, binary log)

 

- cloud

   -> 큰 회사에서 제공하는 데이터센터를 제공받아 사용

   -> 원격 제어로 사용

   -> mySQL을 서비스화 하여, 매우 쉽게 사용할 수 있게 된다.

   -> 필요없을 때 끄면, 더이상 과금 x

   -> 최고의 엔지니어가 관리해 줌(백업 등)

   -> aws rds / google cloud sql for my sql / azure database for mysql

 

- programming

   -> 현재는 데이터베이스를 부품으로 사용해서 완제품이 만들어짐(수많은 앱, 웹, 분석 결과)

   -> java mysql api (이 언어로 데이터베이스 시스템을 쉽게 handling 할 수 있는 조작장치 찾기)