[DB] MySQL 기본 CRUD

[DB] MySQL 기본 CRUD

2022. 3. 5. 16:27ㆍMajor`/DB

SHOW DATABASES

- 현재 계정에서 접근 가능한 DB의 목록들을 보여준다

CREATE DATABASE 'sample'

USE sample;

CREATE DATABASE ~ : '~'라는 DB를 현재 계정에 생성

USE ~ : 현재 계정에서 '~'라는 DB를 사용하겠다

SHOW TABLES;

SHOW TABLE STATUS;

SHOW TABLES : USE를 통해서 사용하는 DB의 테이블에는 어떤 테이블이 존재하는가

SHOW TABLE STATUS : USE를 통해서 사용하는 DB의 테이블의 정보들 (버전, ROW수, ...)

DESCRIBE [테이블 이름];
DESC [테이블 이름];

DESC [테이블 이름] : 해당 테이블의 COLUMN의 정보 (필드, 타입, NULL?, ....)

- DESCRIBE, DESC는 동일한 의미이다

LIMIT [N]

LIMIT [N] : 최대 N개까지 출력 제한

- SELECT절 맨 마지막에 작성해야 한다

DDL (Data Definition Language)

CREATE

- DB 생성 & 해당 DB에 테이블을 생성

CREATE DATABASE test;
-- test라는 DB를 현재 계정에 생성

CREATE TABLE user(
    컬럼명1 컬럼타입1,
    컬럼명2 컬럼타입2,
    ...
}
-- user라는 테이블을 현재 DB상에 생성

테이블 필드 타입

1. 숫자 타입

2. 문자열 타입

3. 날짜 & 시간 타입

1. 숫자 타입

정수 타입 (Integer)
고정 소수점 타입 (Fixed-Point)
부동 소수점 타입 (Floating-Point)
비트값 타입 (Bit-Value)

▶ (1) 정수 타입

타입	저장 공간	Signed Range	Unsigned Range
TINYINT	1byte	-128 ~ 127	0 ~ 255
SMALLINT	2byte	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3byte	-8388608 ~ 8388607	0 ~ 16777215
INT	4byte	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8byte	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

▶ (2) 고정 소수점 타입

DECIMAL(M,D)

M : 실수의 총 자릿수 & 최대 65

D : 소수 부분의 자릿수 & 0이면 소수 부분 X

▶ (3) 부동 소수점 타입

FLOAT(P)

P : 0 ~ 24 >> FLOAT 값

P : 25 ~ 53 >> DOUBLE 값

FLOAT(M,D)
DOUBLE(M,D)

고정 소수점 타입처럼 사용할 수 있는 비표준 문법도 지원한다

▶ (4) 비트값 타입

BIT(M)

0, 1로 구성된 바이너리 값을 저장할 수 있다

M : 1 ~ 64까지 설정가능

- 입력한 값 앞에 0을 자동으로 추가해서 길이를 알아서 맞춰준다

-- Example)

Code BIT(7)
..
..
>> VALUE로 100을 넣으면 자동으로 0000100으로 변환되어서 저장이 된다

2. 문자열 타입

CHAR & VARCHAR
BINARY & VARBINARY
BLOB & TEXT
ENUM
SET

▶ (1) CHAR & VARCHAR

CHAR(M)
VARCHAR(M)

CHAR : 고정 길이 문자열 & 0 ~ 255

VARCHAR : 가변 길이 문자열 & 0 ~ 65535

-- Example)

name1 CHAR(5)
name2 VARCHAR(5)

1) name1
INSERT INTO ~ VALUES('');
INSERT INTO ~ VALUES('ABC');
INSERT INTO ~ VALUES('ABCD');
INSERT INTO ~ VALUES('ABCDE');

2) name2
INSERT INTO ~ VALUES('');
INSERT INTO ~ VALUES('ABC');
INSERT INTO ~ VALUES('ABCD');
INSERT INTO ~ VALUES('ABCDE');

입력값	CHAR(5) 저장 형태	CHAR 저장 공간	VARCHAR(5) 저장 형태	VARCHAR 저장 공간
''	' '	4 bytes	''	1 bytes
'ABC'	'ABC '	4 bytes	'ABC'	4 bytes
'ABCD'	'ABCD'	4 bytes	'ABCD'	5 bytes
'ABCDE'	'ABCD'	4 bytes	'ABCD'	5 bytes

※ VARCHAR의 저장 공간

VARCHAR는 데이터 + 문자열의 길이를 저장하기 위한 메모리를 저장 공간에 저장해놓는다

- 문자열의 길이의 경우 255글자 이하는 1 byte, 255글자 이상은 2 byte의 추가 공간이 필요하다

- 그래서 'ABC'를 저장하는 경우, (데이터 길이 3 + 문자열의 길이(255글자 이하) 1) >> 4 bytes가 필요한 것이다

▶ (2) BINARY & VARBINARY

BINARY(M)
VARBINARY(M)

- CHAR & VARCHAR와 동일한 형식으로 저장하지만 문자 집합이 아닌 바이너리 데이터를 저장하는 점이 다르다

▶ (3) BLOB & TEXT

BLOB

- Binary Large Object >> 다양한 크기의 바이너리 데이터를 저장할 수 있는 타입

- TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB

TEXT

- VARCHAR와 비슷하지만, TEXT는 기본값을 가질 수 없다

- 문자열의 대소문자를 구분한다

- TINYTEXT / TEXT / MEDIUMTEXT / LONGTEXT

▶ (4) ENUM

- 미리 정의한 집합 안의 요소 중 하나를 저장할 수 있는 타입

- 가독성을 높일 수 있고, 특정 숫자에 문자열로 의미를 부여할 수 있다

ENUM('데이터값1', '데이터값2', '데이터값3', ...)

- 최대 65535개의 ENUM 데이터를 포함할 수 있다

- ENUM데이터는 내부적으로 정수로 변환되어서 인식한다

- 각 데이터는 내부적으로 0부터 시작해서 1씩 증가

0 : 빈 문자열과 대응
1부터 집합의 첫번째 요소(데이터값1)와 대응된다

▶ (5) SET

- 미리 정의한 집합 안의 요소 중 여러개를 동시에 저장할 수 있는 타입

SET('데이터값1', '데이터값2', '데이터값3', ...)

- 최대 64개의 SET 데이터를 포함할 수 있다

3. 날짜 & 시간 타입

DATE & DATETIME & TIMESTAMP
TIME
YEAR

▶ (1) DATE & DATETIME & TIMESTAMP

DATE

- 날짜를 저장할 수 있는 타입

- 기본형식 : 'YYYY-MM-DD' & '1000-01-01' ~ '9999-12-31'

DATETIME

- 날짜 + 시간을 저장할 수 있는 타입

- 기본 형식 : 'YYYY-MM-DD HH:MM:SS' & '1000-01-01 00:00:00 ~ '9999-12-31 23:59:59'

TIMESTAMP

- 날짜 + 시간을 나타내는 타임스탬프를 저장

- 데이터의 최종 변경 시각을 저장하고 확인할 때 유용

- 범위 : '1970-01-01 00:00:01 ~ '2038-01-19 03:14:07'

▶ (2) TIME

- 시간을 저장할 수 있는 타입

- 기본형식 : 'HH:MM:SS' OR 'HHH:MM:SS' & '-838:59:59' ~ '838:59:59'

▶ (3) YEAR

- 연도를 저장할 수 있는 타입

- YEAR(2) : 2자리 연도

- YEAR(4) : 4자리 연도

※ AUTO_INCREMENT 키워드

- 해당 필드의 값을 1부터 시작해서 ROW가 추가될때마다 해당 ROW는 해당 필드의 값을 1씩 증가된 값으로 저장한다

- TRUNCATE를 통해서 AUTO_INCREMENT를 초기화할 수 있다

- AUTO_INCREMENT로 설정된 필드는 굳이 INSERT과정에서 값을 넣을 필요가 없다

ALTER TABLE [테이블명]
AUTO_INCREMENT=[X];

- AUTO_INCREMENT를 'X'로 설정해줄 수 있다

CREATE TABLE user(
    idx INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    pwd VARCHAR(20) NOT NULL,
    gender CHAR(2) NOT NULL,
    point INT,
    PRIMARY KEY(idx)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER

- DB & 테이블의 내용을 수정

ALTER DATABASE [DB이름] CHARACTER SET=[문자집합이름]
ALTER DATABASE [DB이름] COLLATE=[콜레이션이름]

테이블 수정

1. ADD

2. DROP

3. MODIFY COLUMN

▶ (1) ADD

ALTER TABLE [테이블명] ADD [필드이름] [필드타입];
----------------------------------------------------
ALTER TABLE user ADD Major VARCHAR(20);

▶ (2) DROP

ALTER TABLE [테이블명] DROP [필드이름]
------------------------------------
ALTER TABLE user DROP Major;

▶ (3) MODIFY COLUMN

- 테이블의 특정 필드 타입 변경

ALTER TABLE [테이블명] MODIFY COLUMN [필드이름] [필드타입];
--------------------------------------------------------
ALTER TABLE user Birthday VARCHAR(20);

>> DATE 타입이였던 Birthday를 VARCHAR(20) 타입으로 변경

DROP

- DB & DB의 테이블 삭제

DROP DATABASE sample;
DROP TABLE user;
TRUNCATE TABLE user;

DROP : 해당 테이블 전체를 DB에서 아예 삭제

TRUNCATE : 해당 테이블의 데이터만 전체적으로 삭제 >> 테이블 자체는 아직 존재한다

DML (Data Manipulation Language)

INSERT

INSERT INTO [테이블명]
VALUES(데이터값1, 데이터값2, 데이터값3, ....)
>> 테이블의 모든 필드에 값을 설정

INSERT INTO [테이블명](필드이름1, 필드이름2, 필드이름3, ...)
VALUES(데이터값1, 데이터값2, 데이터값3, ....)
>> 테이블의 특정 필드에 값을 설정

생략 가능 필드

- NULL 저장가능 필드

- DEFAULT 제약조건 설정된 필드

- AUTO_INCREMENT키워드 로 설정된 필드

UPDATE

UPDATE [테이블명]
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ....
WHERE (조건)

DELETE

DELETE FROM [테이블명] 
WHERE (조건)

SELECT

SELECT [필드명]
FROM [테이블명]
(
WHERE 조건
GROUP BY 필드명
HAVING 집계함수
ORDER BY ASC|DESC
)

INDEX

- 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용

※ Full Scan & Range Scan

Full Scan

- 원래 MySQL에서는 데이터를 검색할 때, 테이블 전체를 차례대로 검색한다 :: Full Scan

- 따라서, 테이블 크기가 매우 크면 데이터 탐색 시간도 그만큼 늘어난다

Range Scan

- 인덱스를 사용하면 테이블 전체를 검색하지 않아도, 해당 데이터에 대한 검색 & 질의에 대한 처리가 굉장히 빠르게 이루어진다 :: Range Scan

- 인덱스는 user가 직접 접근 X

- 인덱스는 검색 & 질의에 대한 처리에서만 사용된다

>> 인덱스가 설정된 필드에 대해서 수정 작접이 일어나면 인덱스도 함꼐 수정해야 한다

INDEX 생성

CREATE INDEX [인덱스 이름]
ON [테이블이름](필드이름1, 필드이름2, ...)

INDEX 정보 보기

SHOW INDEX
FROM [테이블명]

UNIIQUE INDEX 생성

CREATE UNIQUE INDEX [인덱스명]
ON [테이블명](필드이름1, 필드이름2, ...)

INDEX 정렬

CREATE INDEX [인덱스명]
ON [테이블명](필드이름 ASC)

CREATE INDEX [인덱스명]
ON [태이블명](필드이름 DESC)

FULLTEXT INDEX 추가

- 일반적인 인덱스와 달리, 매우 빠르게 테이블의 모든 텍스트 필드를 검색

- 자연어를 이용해서 데이터를 검색할 수 있도록 모든 데이터의 문자열 단어를 저장한ㄷ

ALTER TABLE [테이블명]
ADD FULLTEXT [인덱스명](필드이름)

INDEX 삭제

ALTER TABLE [테이블명]
DROP INDEX [인덱스명]

DROP INDEX [인덱스명]
ON [테이블명]

VIEW

- DB에 존재하는 가상 테이블

- 실제 테이블처럼 ROW, COLUMN을 보유하지만, 실제로 데이터를 저장하지는 않는다

- 다른 테이블 OR 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만 수행한다

단점

1. 한번 정의된 뷰는 변경할 수 없다

2. 삽입/삭제/갱신 작업에 많은 제한 사항이 존재

3. 자신만의 인덱스를 가질 수 없다

뷰 생성

CREATE VIEW [뷰이름] AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
(
WHERE 조건
)

- 원본 테이블과 동일한 이름을 가질 수 없다

저작자표시 변경금지

Coding -> 블로그 이전

Coding

DDL (Data Definition Language)

CREATE

테이블 필드 타입

1. 숫자 타입

2. 문자열 타입

3. 날짜 & 시간 타입

※ AUTO_INCREMENT 키워드

ALTER

테이블 수정

DROP

DML (Data Manipulation Language)

INSERT

UPDATE

DELETE

SELECT

INDEX

※ Full Scan & Range Scan

Full Scan

Range Scan

>> 인덱스가 설정된 필드에 대해서 수정 작접이 일어나면 인덱스도 함꼐 수정해야 한다

INDEX 생성

INDEX 정보 보기

UNIIQUE INDEX 생성

INDEX 정렬

FULLTEXT INDEX 추가

INDEX 삭제

VIEW

단점

뷰 생성

관련글

티스토리툴바