0. intro

Relational DB

• MySQL, Oracle, ...

schema

• schema : 서로 연관된 표들을 grouping
  

---

1. MySQL intro

• user에게 차등적으로 권한 부여 가능

MySQL 서버 접속

mysql -uroot -p -h아이피주소

• localhost 접속

  mysql -uroot -p -h127.0.0.1
  mysql -uroot -p -hlocalhost

스키마 만들기

CREATE DATABASE DB_이름;

스키마 삭제

DROP DATABASE DB_이름;

스키마 보기

SHOW {DATABASES | SCHEMAS}
	[LIKE 'pattern' | WHERE expr];

테이블 보기

SHOW TABLES;

테이블 구조 보기

DESC 테이블이름;

작업할 스키마 선택

USE DB_이름;

비밀번호 재설정

SET PASSWORD = PASSWORD('비밀번호');

SQL(Structured Query Language)

• table, 표
• row, record, 행
• column, 열

---

2. MySQL 테이블 생성

CREATE TABLE 테이블_이름(
	변수명_1 변수타입_1 option_1,
    변수명_2 변수타입_2 option_2,
    ...
    변수명_N 변수타입_N option_N
);

CREATE TABLE topic(
	id INT(11) NOT NULL AUTO_INCREMENT,	
    title varchar(100) NOT NULL,
    description TEXT NULL,
    created DATETIME NOT NULL,
    author varchar(15) NULL,
    profile varchar(200) NULL,
    PRIMARY KEY(id)
);

• INT(11) : 검색할 때 11자리 노출
• varchar(100) : 100자리까지 저장

---

3. CRUD

Create
Read
Update
Delete

테이블 행 보기

SELECT * FROM topic;

1) Create

INSERT INTO 테이블이름 (열1, 열2, ...)
VALUES (값1, 값2, ...);

insert into topic(title, description, created, author, profile) values('MySQL', 'MySQL is ...', NOW(), 'egoing', 'developer');

2) READ

전체 행 읽기

SELECT * FROM topic;

특정 열 읽기

SELECT id, title, created, author FROM topic;

expression

SELECT 'egoing', 1+1;

where

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

order by

SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC;

LIMIT

SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;

3) UPDATE

UPDATE topic SET description='Oracle is...', title='Oracle' WHERE id=2;

4) DELETE

DELETE FROM topic WHERE id=5;

---

4. Advanced

테이블 분리

• 장점 : 중복 데이터 제거
• 단점 : 테이블 모든 정보 보려면 join 해야

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

JOIN

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

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

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

MySQL client

client <--> server

• mysql monitor : CUI, mysql과 함께 설치됨
• mysql workbench : GUI

MySQL workbench

---

출처 : 인프런 "DATABASE 1&2 - MySQL"
https://www.inflearn.com/course/database-2-mysql-%EA%B0%95%EC%A2%8C#curriculum