스키마: 테이블(표)들를 그룹핑하는 일종의 폴더의 형태
데이터 베이스 서버: 스키마들의 집합
MySQL(Structured Query Language)
-서버 실행
-mysql -uroot -p
-스키마 생성
-CREATE DATABASE opentutorials;
-스키마 삭제
-DROP DATABASE opentutorials;
-스키마 보기
-SHOW DATABASES;
-어떤 스키마를 사용할지 결정
-USE opentutorials;
-현재 스키마에서 테이블 보기
-SHOW TABLES;
CRUD
CREATE
-테이블 만들기
-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));
-테이블 구조 보기
-DESC topic;
-데이터 Key, Value로 삽입
-INSERT INTO topic (title,description,created,author,profile) VALUEs('MySQL','MYSQL is ...',NOW(),'egoing','developer');
READ
-모든 데이터 조회
-SELECT * FROM topic;
-원하는 컬럼만 보기
-SELECT id,title,created,author FROM topic;
-조건을 만족하는 데이타만 보기
-SELECT id,title,created,author FROM topic WHERE author='egoing'
-내림차순으로 정렬해서 보기
-SELECT id,title,created,author FROM topic WHERE author='egoing' ORDER BY id DESC;
-최대 2개까지만 보기
-SELECT id,title,created,author FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;
UPDATE
-데이터 내용 고치기
-UPDATE topic SET description='PostgreSQL is ...', title='POSTGRESQL' WHERE id=4;
(주의: WHERE 빠지면 다 적용되는 낭패)
DELETE
-조건에 해당하는 데이터 삭제
-DELETE FROM topic WHERE id=5;
관계형데이터베이스의 필요성
-중복되는 데이터 -> 용량 낭비 -> 개선여지가 있음 -> 테이블을 분리하고 매핑 키(참조)로 해결 ->유지 보수 편리
-테이블 매핑으로 합치기(JOIN)
-SELECT * FROM Rtopic LEFT JOIN author ON Rtopic.author_id = author.id;
Rtopic
+----+------------+-------------------+---------------------+-----------+
| id | title | description | created | author_id |
+----+------------+-------------------+---------------------+-----------+
| 1 | MySQL | MySQL is ... | 2023-03-01 18:33:15 | 1 |
| 2 | Oracle | Oracle is ... | 2023-03-01 18:33:49 | 1 |
| 3 | SQL Server | SQL Server is ... | 2023-03-01 18:34:09 | 2 |
| 4 | PostgreSQl | PostgreSQL is ... | 2023-03-01 18:34:33 | 3 |
| 5 | MongoDB | MongoDB is ... | 2023-03-01 18:34:56 | 1 |
+----+------------+-------------------+---------------------+-----------+
author
+----+--------+---------------------------+
| id | name | profile |
+----+--------+---------------------------+
| 1 | egoing | developer |
| 2 | duru | data administrator |
| 3 | taeho | data scientist, developer |
+----+--------+---------------------------+
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| id | title | description | created | author_id | id | name | profile |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
| 1 | MySQL | MySQL is ... | 2023-03-01 18:33:15 | 1 | 1 | egoing | developer |
| 2 | Oracle | Oracle is ... | 2023-03-01 18:33:49 | 1 | 1 | egoing | developer |
| 5 | MongoDB | MongoDB is ... | 2023-03-01 18:34:56 | 1 | 1 | egoing | developer |
| 3 | SQL Server | SQL Server is ... | 2023-03-01 18:34:09 | 2 | 2 | duru | data administrator |
| 4 | PostgreSQl | PostgreSQL is ... | 2023-03-01 18:34:33 | 3 | 3 | taeho | data scientist, developer |
+----+------------+-------------------+---------------------+-----------+------+--------+---------------------------+
SELECT Rtopic.id AS topic_id, title, name, profile FROM Rtopic LEFT JOIN author ON Rtopic.author_id = author.id;
+----------+------------+--------+---------------------------+
| topic_id | title | name | profile |
+----------+------------+--------+---------------------------+
| 1 | MySQL | egoing | developer |
| 2 | Oracle | egoing | developer |
| 5 | MongoDB | egoing | developer |
| 3 | SQL Server | duru | data administrator |
| 4 | PostgreSQl | taeho | data scientist, developer |
+----------+------------+--------+---------------------------+