DB와 테이블 작성

3 minute read

DB생성하기

CREATE DATABASE DB이름;

생성시 중복되는 이름이 있을경우 DBMS에서 자동으로 중복생성을 막아준다.
생성할떄 이름이 중복되는지 확인하고 싶으면

CREATE DATABASE IF NOT EXISTS DB이름;

해당 이름이 존재하지 않으면 생성하게 된다.

DB선택하기

USE DB이름;

DB서버에 처음 접속하고 난 후에는 어느 DB에서 작업을 할 것인지를 지정해줘야 한다.
그 후에는 지정된 DB안에 있는 것을 SQL문에서 가리킬때 DB이름을 생략해도 된다.

데이터 타입

숫자형 타입

정수형 타입

TINYINT
최소 -128에서 최대 127까지의 정수를 저장할 수 있는 타입이다.
TINYINT SIGNED : -128 ~ 127
TINYINT UNSIGNED : 0 ~ 255
SMALLINT
SMALLINT SIGNED : -32768 ~ 32767
SMALLINT UNSIGNED : 0 ~ 65535
MEDIUMINT
MEDIUMINT SIGNED : -8388608 ~ 8388607
MEDIUMINT UNSIGNED : 0 ~ 16777215
INT
INT SIGNED : -2147483648 ~ 2147483647
INT UNSIGNED : 0 ~ 4294967295
BIGINT
BIGINT SIGNED : -9223372036854775808 ~ 9223372036854775807
BIGINT UNSIGNED : 0 ~ 18446744073709551615

실수형 타입

DECIMAL
DECIMAL(M, D)형식으로 나타내고 M은 최대로 쓸 수 있는 전체 숫자의 자릿수이고, D는 최대로 쓸 수 있는 소수점 뒤에 있는 자리의 수를 의미한다.
M은 최대 65, D는 최대 30까지 가능하다.
예시) DECIMAL(5, 2)는 -999.99 ~ 999.99까지의 실수를 나타낼 수 있다.
DECIMAL대신에 DEC, NUMERIC, FIXED를 써도 된다.
FLOAT
-3.402823466E+38 ~ -1.175494351E-38,
0,
1.175494351E-38 ~ 3.402823466E+38
범위의 실수들을 나타낼 수 있다.
DOUBLE
-1.7976931348623157E+308 ~ -2.2250738585072014E-308,
0,
2.2250738585072014E-308 ~ 1.7976931348623157E+308
범위의 실수들을 나타낼 수 있다.

날짜 및 시간 타입

DATE
날짜를 저장하는 데이터 타입이다. ‘2020-10-10’이런식으로 나타낸다.
DATETIME
날짜와 시간을 저장하는 데이터 타입이다. ‘2020-10-10 09:30:27’이런식으로 나타낸다.
TIMESTAMP
날짜와 시간을 저장하는 데이터 타입이다. DATETIME과는 다르게 타임존 정보도 함께 저장한다.
그냥 저장했을땐 DATETIME과 차이가 없지만,
```
SET time_zone = '-10:00';
```
MySQL서버의 시간대 설정을 바꿔보면 시간이 차이가 나는것을 볼 수 있다.
TIME
시간을 나타내는 데이터 타입이다. ‘09:30:31’이런식으로 나타낸다.

문자열 타입

CHAR
문자열을 나타내는 기본 타입이다. CHAR(30)이런 형식으로 나타낸다.
괄호안의 숫자는 문자를 최대 몇 자까지 저장할 수 있는지 나타낸다.
0부터 255까지의 숫자를 적을 수 있다.
VARCHAR
문자열을 나타내는 타입이다. VARCHAR(30)이런 식으로 나타낸다.
괄호안의 숫자는 0부터 65,535(2^16 - 1)를 쓸 수 있다.
CHAR와의 차이점은 CHAR는 고정길이 타입이고, VARCHAR는 가변길이 타입이다.
CHAR(10)이라면 어떤 길이의 문자열이 저장되든 항상 10만큼의 용량을 차지한다.
반면에 VARCHAR(10)은 5자가 저장되면 저장용량도 5만큼만 차지한다.
따라서 길이가 크게 변하지 않을 컬럼은 CHAR를 쓰고 그렇지 않다면 VARCHAR를 쓴다.
TEXT
문자열을 나타내는 타입이다. 최대 65525자까지 저장할 수 있다.
이 외에도 16,777,215 (224 − 1) 자까지 저장할 수 있는 MEDIUMTEXT, 4,294,967,295(232 − 1) 자까지 저장할 수 있는 LONGTEXT 타입이 있다.
길이가 정말 긴 문자열을 저장하려면 TEXT계열의 타입을 쓰면 된다.

테이블 생성하기

CREATE TABLE `DB이름`.`테이블이름` (
    `컬럼의 이름` 컬럼의 데이터 타입, 컬럼의 속성
    PRIMARY KEY(`PK컬럼`));

CREATE TABLE `school`.`student` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NULL,
    `admission_date` DATE NULL,
    PRIMARY KEY(`id`));

NULL은 데이터에 빈 값을 허용할 지를 나타낸다.
pk로 사용할 컬럼을 PRIMARY KEY에 작성하면 된다.
백틱(`)은 생략해도 무관하다.

테이블에 row추가하기

INSERT INTO 테이블이름
    (컬럼, 컬럼...)
    VALUES (컬럼의 row값, 컬럼의 row값...);

INSERT INTO student
    (name, student_number, admission_date)
    VALUES ('영희', 167861, '2018-03-12');

생성후에 row를 더 추가할때는 컬럼이름을 생략 가능하다.

INSERT INTO student
    VALUES ('철수', 151161, '2019-03-12');

특정 컬럼들에만 값이 있는 row를 추가할때는 아래와 같이 하면 된다.

INSERT INTO student
    (name, admission_date)
    VALUES ('민수', '2018-03-12');

이러면 student_number에는 NULL이 들어간채로 row가 추가된다.

테이블의 row갱신

UPDATE 테이블이름 SET 컬럼이름 = '값' WHERE pk = ?;

UPDATE student SET name = '광수' WHERE id = 1;

id가 1인 row의 name컬럼을 ‘광수’로 변환하게 된다.
만약 WHERE절을 생략하게 된다면 name컬럼의 모든 값이 변경되버리니 반드시 주의해야한다.

UPDATE student SET name = '민수', student_number = 123456 WHERE id = 1;

여러 값을 동시에 바꾸려면 콤마뒤에 추가로 작성하면 된다.

컬럼의 기존 값을 기준으로 갱신

UPDATE student SET score = score + 3;

이런식으로 모든 row의 score값을 3씩 더할 수가 있다.

테이블 삭제

DELETE FROM 테이블이름 WHERE pk = ?;

DELETE FROM student WHERE id = 1;

id가 1인 row를 삭제한다. 마찬가지로 WHERE를 작성하지 않으면 모든 row가 삭제되어 버린다.

물리 삭제, 논리 삭제

위와 같이 row를 바로 삭제해버리는 것을 물리 삭제라고 한다.
반면 논리 삭제는 row를 삭제하지 않고 삭제 여부를 나타내는 별도의 컬럼을 둬서 거기에 삭제되었음을 나타내는 값을 넣는다.

id	customer_id	is_cancelled
1	11	null
2	312	Y

이렇게 컬럼을 추가해서 표시를 해주면 된다.
논리 삭제는 해당 row를 삭제된 것처럼 논리적으로 간주만 할 뿐 실제로 삭제하지는 않는다.
그래서 논리 삭제를 할때는 DELETE문을 쓰는게 아니라 UPDATE문을 써야한다.

UPDATE order SET is_cancelled = 'Y';

이러면 데이터는 그대로 남아있기 때문에 나중에 분석에 활용한다거나 할때 사용할 수 있다.
하지만 논리 삭제를 하게 되면 유효한 row만 조회해야 할때

SELECT * FROM WHERE is_cancelled != 'Y';

이런식으로 별도의 조건을 추가해야 하기 때문에 번거로워지는 단점이 있다.
또한 실제로 row를 삭제하는 것이 아니기 때문에 DB내의 저장용량은 줄어들지 않게 된다.

Share on

Twitter Facebook LinkedIn

우성