DB-1 MySQL (22/12/22)

nazzzo·2022년 12월 22일
mysql
0

DB 사용 입문

1. DB(DataBase)?

우리가 흔히 DB나 데이터베이스라고 부르는 개념의 정식 명칭은
RDBMS(Relational DataBase Management System/ 관계형 데이터베이스)입니다
(DBMS는 데이터베이스에 매니지먼트(관리자) 기능을 부여한 것을 의미합니다)

관계형 데이터베이스에는 몇가지 대표적인 플랫폼들이 있습니다

  • Oracle
  • MySQL
  • MariaDB (=MariaDB)
  • PostgreQSL
  • Mssql

그리고 비관계형 데이터베이스(SQL 구문을 사용하지 않는)의
대표적인 플랫폼으로는 MongoDB가 있고요

관계형 데이터베이스에서는 모든 자료가 텍스트 형태로, 테이블 안에 저장됩니다
평소에 엑셀을 많이 다뤄본 사람일수록 RBDMS 사용법에 대한 이해가 빠를 것 같아요
그만큼 비슷한 면이 많습니다


2. SQL 개요


SQL이란 Structured Query Language의 약자로,
DBMS의 매니지먼트(데이터 보관, 저장, 삭제 수정 등)를 위해 사용하는 언어를 의미합니다
문법에 대해서는 어느정도 보편적인 표준이 잡힌 상태입니다


mysql 접속하기

mysql -uroot -p;
-- mysql > : 입력창 앞에 이 표시가 뜨면 mysql에 접속된 상태, 이때부터 SQL 문법을 사용할 수 있습니다

만약 이 코드가 실행되지 않았다면 접속 자체가 이루어지지 않았을 가능성이 큽니다

*SQL 명령어는 테이블, 컬럼명 등과의 구분을 위해 대문자로 작성하는 것을 추천합니다

SQL 분류

  • 데이터 정의어(DDL)
  • 데이터 조작어(DML)
  • 데이터 제어어(DCL)

데이터 정의어 (DDL)

  • CREATE
  • SHOW
  • DROP
  • ALTER
-- 모든 저장소 확인
SHOW DATABASES;

-- 저장소 생성
CREATE DATABASE [저장소 이름];

CREATE DATABASE workspace DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- utf8mb4 : utf8 + 이모지(mb4)까지 저장 가능
-- COLLATE utf8mb4_general_ci : 아래에서 설명


-- 저장소 삭제
DROP DATABASE;


-- 저장소 진입
USE [저장소 이름];
-- > Database changed


-- 현재 사용중인 저장소의 상태 확인
SHOW tables;
-- > Empty set (0.00 sec)

-- 테이블 생성
CREATE TABLE [테이블 이름];
-- 먼저 field(항목)을 만들고 시작해야 합니다
-- 주로 스네이크 표기법을 사용합니다

CREATE TABLE store (
    category VARCHAR(50),
    store_name VARCHAR(50),
    menu_name VARCHAR(50),
    price VARCHAR(50),
    tel VARCHAR(50),
    address VARCHAR(50)
);


-- 데이터 변경 명령어
ALTER [변경할 항목];

ALTER TABLE store RENAME TO store2;


-- 필드 확인
DESC [테이블명];

DESC store;

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| category   | varchar(50) | YES  |     | NULL    |       |
| store_name | varchar(50) | YES  |     | NULL    |       |
| menu_name  | varchar(50) | YES  |     | NULL    |       |
| price      | varchar(50) | YES  |     | NULL    |       |
| tel        | varchar(50) | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Field Type

숫자형, 문자형, 날짜형, 이진데이터 타입(Buffer...)

숫자형 ~

  • INT : 4byte ~ 음수도 표현 가능합니다

문자형

  • CHAR : 255byte ~ 고정 데이터 (남자/여자)
  • VARCHAR : 255byte ~ 가변 데이터 (전화번호 등)
  • TEXT : 65535byte까지 저장할 수 있습니다 (게시판 등의 많은 텍스트를 보관하기에 용이합니다)

날짜형

  • DATE : 연월일
  • TIME : 시간
  • DATETIME : 연월일시간 (yyyy-mm-dd hh:mm:ss)
  • TIMESTAMP : 연월일시간 (itegrger)
  • YEAR

이진데이터타입

  • BLOB : 이미지, 파일...

키(key)
키는 중복값이 없어야 합니다

  • PRIMARY KEY : 중복값 입력 불가, Null 불가능, 테이블당 하나만 넣을 수 있습니다 (user_id, user_name, user_pw)
  • UNIQUE : 중복값 입력 불가, Null 가능 (NOT NULL UNIQUE = PRIMARY KEY)
  • AUTO_INCREMENT : 자동생성되는 데이터(1씩 증가)입니다
    (PRIMARY KEY & AUTO_INCREMENT : 중복값이 없으면서 1씩 자동생성 (~index))
CREATE TABLE user (
    user_id varchar(50) PRIMARY KEY,
    user_pw varchar(50) NOT NULL,
    user_name varchar(50) NOT NULL,
    gender char(4) DEFAULT '남자',
    register_date DATETIME DEFAULT now()
);

+---------------+-------------+------+-----+-------------------+-------------------+
| Field         | Type        | Null | Key | Default           | Extra             |
+---------------+-------------+------+-----+-------------------+-------------------+
| user_id       | varchar(50) | NO   | PRI | NULL              |                   |
| user_pw       | varchar(50) | NO   |     | NULL              |                   |
| user_name     | varchar(50) | NO   |     | NULL              |                   |
| gender        | char(4)     | YES  |     | 남자               |                   |
| register_date | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------------+-------------+------+-----+-------------------+-------------------+


-- field : 첫번째 가로줄
-- row : 나머지 가로줄
-- columm : 세로줄 (키값)

VARCHAR(50), CHAR(4)
VARCHAR(50)은 문자열을 저장하기 위한 자료형입니다. VARCHAR는 "Variable CHARacter"의 준말로, 가변 길이의 문자열을 저장할 수 있습니다. 50은 저장할 수 있는 문자열의 최대 길이를 의미합니다. 즉, VARCHAR(50)은 최대 50글자의 문자열을 저장할 수 있는 자료형입니다.

VARCHAR 자료형은 고정 길이의 CHAR 자료형과 달리, 실제로 저장되는 문자열의 길이만큼 공간을 차지합니다. 예를 들어, VARCHAR(50)에 "Hello"라는 문자열을 저장하면, 실제로는 "Hello"가 저장되고 그 외의 공간은 사용되지 않습니다. 이는 CHAR 자료형과 달리, CHAR 자료형은 지정된 길이만큼 공간을 차지하기 때문입니다.

PRIMARY KEY & UNIQUE KEY
PRIMARY KEY는 테이블에서 각 행을 고유하게 식별하기 위해 사용됩니다. 각 테이블에는 하나의 PRIMARY KEY만 지정할 수 있으며, 이 값은 중복될 수 없습니다. PRIMARY KEY는 NULL 값을 허용하지 않습니다.

UNIQUE key는 테이블에서 각 행을 유일하게 식별하기 위해 사용됩니다. 각 테이블에는 여러 개의 UNIQUE key를 지정할 수 있으며, 이 값들은 중복될 수 없습니다. UNIQUE key는 NULL 값을 허용할 수 있습니다.

간단히 요약하면, PRIMARY KEY는 테이블에서 각 행을 고유하게 식별하는데 사용되고, NULL 값을 허용하지 않습니다. UNIQUE key는 테이블에서 각 행을 유일하게 식별하는데 사용되고, NULL 값을 허용할 수 있습니다.

COLLATE (콜레이트)
COLLATE는 데이터베이스에서 어떻게 데이터가 정렬되고 비교되는지를 결정하는 규칙을 의미합니다.
콜레이션은 데이터베이스, 테이블, 또는 컬럼에 지정될 수 있습니다. 콜레이션은 언어, 지역, 문자 집합과 같은 요소를
고려하여 데이터를 정렬하고 비교할 수 있는 규칙을 정의합니다.

예를 들어, 콜레이션은 언어에 따라 알파벳 순서가 달라질 수 있기 때문에
언어가 영어인 경우 "A"가 "B"보다 앞에 오고, 일본어인 경우 "あ"가 "い"보다 앞에 오게 됩니다.
콜레이션은 이러한 요소를 고려하여 적절한 정렬 규칙을 정의하기 때문에
데이터베이스에서 데이터를 정렬하고 비교할 때 사용됩니다.

데이터 조작어 (DML)

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

INSERT

INSERT INTO [테이블명](필드1, 필드2...) values(타입)

INSERT INTO user(user_id, user_pw, user_name, gender) 
values('web7722', '1234', '김XX', '남자');

INSERT INTO user(user_id, user_pw, user_name, gender) 
values('admin', '1234', '관리자', '남자');

SELECT

추가한 데이터 내용을 조회하려면 SELECT 문법을 사용해야 합니다

SELECT (필드) FROM [테이블명]

SELECT user_id,user_pw,user_name,gender FROM user;

-- 모든 필드를 확인하려면
SELECT * FROM user;

+---------+---------+-----------+--------+---------------------+
| user_id | user_pw | user_name | gender | register_date       |
+---------+---------+-----------+--------+---------------------+
| admin   | 1234    | 관리자      | 남자    | 2022-12-22 11:30:16 |
| web7722 | 1234    | 김XX       | 남자    | 2022-12-22 11:20:40 |
+---------+---------+-----------+--------+---------------------+

*테이블 설계만 잘 해놓는다면 중복값 체크는 DB측에서 해주기 때문에
사용자가 만들어야 할 코드의 양이 줄어듭니다

WHERE 절
하나의 row만 체크하고 싶을 때 사용합니다 (해당 게시물만 확인, 수정, 삭제하고 싶을 때...)

SELECT * FROM user WHERE user_id='admin';

+---------+---------+-----------+--------+---------------------+
| user_id | user_pw | user_name | gender | register_date       |
+---------+---------+-----------+--------+---------------------+
| admin   | 1234    | 관리자      | 남자    | 2022-12-22 11:30:16 |
+---------+---------+-----------+--------+---------------------+

UPDATE

UPDATE user SET gender='여자' WHERE user_id='admin';

SELECT * FROM user WHERE user_id='admin';
+---------+---------+-----------+--------+---------------------+
| user_id | user_pw | user_name | gender | register_date       |
+---------+---------+-----------+--------+---------------------+
| admin   | 1234    | 관리자      | 여자    | 2022-12-22 11:30:16 |
| web7722 | 1234    | 김XX       | 남자    | 2022-12-22 11:20:40 |
+---------+---------+-----------+--------+---------------------+


UPDATE user SET user_pw='0000', user_name='master', gender='남자' WHERE user_id='admin';

+---------+---------+-----------+--------+---------------------+
| user_id | user_pw | user_name | gender | register_date       |
+---------+---------+-----------+--------+---------------------+
| admin   | 0000    | master    | 남자    | 2022-12-22 11:30:16 |
+---------+---------+-----------+--------+---------------------+

DELETE
삭제는 모든 row를 한번에 삭제합니다

DELETE FROM user WHERE user_id='web7722';

SELECT * FROM user;
+---------+---------+-----------+--------+---------------------+
| user_id | user_pw | user_name | gender | register_date       |
+---------+---------+-----------+--------+---------------------+
| admin   | 0000    | master    | 남자    | 2022-12-22 11:30:16 |
+---------+---------+-----------+--------+---------------------+

데이터 제어어 (DL)

  • GRANT
  • REVOKE

*데이터 제어어는 SQL 문법에 더 숙달된 후에 다루기로 하겠습니다

3. 게시판 만들기

CREATE TABLE board (
    idx int(11) AUTO_INCREMENT PRIMARY KEY,
    subject varchar(150) NOT NULL, 
    content TEXT NULL,
    writer varchar(50) NOT NULL,
    registerDate DATETIME NOT NULL DEFAULT now(),
    hit int(11) DEFAULT 0
);

*필드를 추가하고 싶은 경우에는 ALTER 명령어를 사용해야 합니다

INSERT INTO board(subject, content, writer) 
values('hello world', '1234', 'web7722');
INSERT INTO board(subject, content, writer) 
values('hello world2', '1234', 'web7722');
INSERT INTO board(subject, content, writer) 
values('hello world5', '1234', 'web7722');

+-----+--------------+---------+---------+---------------------+------+
| idx | subject      | content | writer  | registerDate        | hit  |
+-----+--------------+---------+---------+---------------------+------+
|   1 | hello world  | 1234    | web7722 | 2022-12-22 11:55:01 |    0 |
|   2 | hello world2 | 1234    | web7722 | 2022-12-22 11:55:05 |    0 |
|   3 | hello world3 | 1234    | web7722 | 2022-12-22 11:55:09 |    0 |
|   4 | hello world4 | 1234    | web7722 | 2022-12-22 11:55:32 |    0 |
|   5 | hello world5 | 1234    | web7722 | 2022-12-22 11:55:42 |    0 |
+-----+--------------+---------+---------+---------------------+------+

그밖의 명령어

-- mysql 종료
mysql> exit 
-- 혹은 ctrl+D


-- 현재 터미널이 바라보고 있는 디렉토리에 백업파일(backup.sql)을 생성합니다
mysqldump -uroot -p workspace > backup.sql;


-- 해당 파일을 실행합니다
SOURCE [파일 경로]
-- 절대경로를 알고 있다면 명령어를 바로 사용하는 것도 가능합니다

-- 백업파일이 위치한 디렉토리에서 mysql에 접속 > use [저장소명]
SOURCE ./backup.sql;
profile
nazzzo
이전 포스트

JS-21 비동기 처리 (22/12/21)

다음 포스트

DB-2 MVC 패턴 (23/01/01)

0개의 댓글