[한화시스템 BEYOND SW캠프 7기] #05. MariaDB_기본(2)

아로롱·2024년 5월 17일

MariaDB sql

BEYOND SW CAMP 7

목록 보기

5/30

🦭 MariaDB

저번 게시글에 이어 mariaDB 문자 타입부터 살펴보자.
수업 시간에 진행한 실습 위주로 복습해보았다.

✏️ 타입(data type)

숫자 타입
- 정수
  - TINYINT
    - -128 ~ 127범위, 1바이트
    - java의 byte와 매핑
  - INT
    - 4바이트
    - java의 int타입과 매핑
  - BIGINT
    - 8바이트
    - java의 long타입과 매핑
    - id 값은 일반적으로 BIGINT 사용
  - UNSIGNED
    - 양수만 표현 가능
    - 표현값 2배로 증가
    - TINYINT UNSIGNED 이렇게 사용한다면 255까지 사용 가능.
- 실수
  - 고정 소수점 타입
    - DECIMAL(M,D)
      - java의 Bigdecimal
      - M은 총자릿수 (정수부+소수부)를 의미 하고, 65자리까지 표현가능
      - D는 소수부 의미
      - 정확한 숫자 표현을 위해 사용.
  - 부동 소수점 타입
    - FLOAT, DOUBLE
    - 오차가 발생할 여지가 있음.

문자 타입
- CHAR와 VARCHAR
  - CHAR(M)
    - M은 문자열의 최대 길이를 의미(숫자를 대입)
    - 고정 길이의 문자열(0~255까지 설정가능)
      - 설정한 길이 만큼의 데이터 공간 차지
      - 부족한 입력 데이터는 공백으로 차지
    - 길이가 정해진 데이터를 저장할때 사용이 적합
  - VARCHAR(M)
    - M은 문자열의 최대 길이를 의미(숫자를 대입)
    - java의 String과 호환
    - 0부터 65,535까지 설정 가능
    - 가변 길이의 문자열
      -> 실제 입력된 문자열의 길이만큼만 저장하고 사용
    - 일반적으로 가장 많이 사용
- TEXT
  - 가변 길이 문자
  - 최대 65,535글자의 글자를 적재할 수 있지만 크기를 미리 지정할 수 없다.
  - VARCHAR 보다 더 큰 범위의 표현이 가능 ex) LONGTEXT는 4GB
  - disk에 저장하기 때문에 조회 속도가 VARCHAR(메모리 저장)에 비해 느림
  - index 사용의 어려움
  - 다양한 크기의 바이너리 데이터를 저장할 수 있는 타입
  - 일반적으로 PNG와 같은 이미지 파일을 저장할때 지정하는 타입

    INSERT INTO author(id, email, profile_photo) VALUES(7, 'abc@test.com', LOAD_FILE('/Users/tte/tte-picture.png'));

💡 *macOS에서 경로 확인하는 법
파일 선택 후 Command + Option + C(⌘ + ⌥ + C)

ENUM
- 미리 들어갈수 있는 특정 데이터의 목록값을 지정

컬럼명 ENUM('데이터값1','데이터값2',...)
ALTER TABLE author MODIFY COLUMN role ENUM(’user’, ‘admin’);
NOT NULL DEFAULT 'user' 등의 옵션도 추가 가능

-- enum : 삽인될 수 있는 데이터 종류를 한정하는 데이터 타입
-- role 컬럼 / not null 일 때 default를 지정해주지 않으면 자동으로 enum(1,2)의 1번이 값으로 지정된다!
    ALTER TABLE author ADD COLUMN ROLE ENUM('admin', 'user') NOT NULL DEFAULT 'user';
-- enum 컬럼 실습
-- user1 을 insert => 에러
insert into author(id, email, role) values(8, 'test@test.com', 'user1');
-- user 또는 admin insert => 정상
insert into author(id, email, role) values(8, 'test@test.com', 'user');

날짜와 시간

DATE
- 날짜를 저장할 수 있는 타입
- YYYY-MM-DD
DATETIME(m)
- 날짜와 함께 시간까지 저장. (m) 지정시 소수점 microseconds
- YYYY-MM-DD HH:MM:SS’
- 가장 많이 사용됨.
- java의 localdatetime과 sync
- DATETIME DEFAULT CURRENT_TIMESTAMP 옵션 많이 사용
- 현재시간을 default로 삽입하는 형식

-- DATE / DATETIME 실습
-- author 테이블에 birth_day 컬럼을 date로 추가
-- 날짜 타입의 insert는 문자열 형식으로 insert
alter table author add column birth_day date; 
insert into author(id, email, birth_day) values(9, 'tteia@test.com', '1996-04-30');
-- date 타입이 아닌 varchar 였어도 들어갈 값. 왜 date ? => +40을 한다면 40일이 더해짐. varchar 였으면 계산이 X
-- author, post 둘 다 datetime으로 created_time 컬럼 추가
alter table author add column created_time datetime;
alter table post add column created_time datetime;
insert into author(id, email, created_time) values(10, 'abc@abc.com', '1999-01-01 23:11:32');
insert into post(id, title, created_time) values(7, 'hello', '1999-01-01 23:11:32');alter table author modify column created_time datetime default current_timestamp;
insert into author(id, email) values(11, 'abc@abc.com');

검색 패턴

LIKE
- 특정 패턴을 포함하는 데이터만을 검색하기 위한 와일드카드(wildcard) 문자
- 일반적으로 %와 함께 사용됨
- SELECT * FROM author WHERE name LIKE ‘홍%’;
- SELECT * FROM author WHERE name LIKE ‘%동';
- SELECT * FROM author WHERE name LIKE ‘%길%';
NOT LIKE

    select * from post where title like '%o'; --o로 끝나는 title 검색
    select * from post where title like 'h%'; --h로 시작하는 title 검색
    select * from contents where title like '%orl%'; --단어 중간에 orl 이라는 키워드가 있는 경우 검색
    select * from post where title not like '%o'; --o로 시작하지 않는 title 검색

REGEXP
- 정규표현식을 토대로 패턴 연산 수행
- SELECT * FROM author WHERE Name REGEXP ‘[a-z]’;
- SELECT * FROM author WHERE Name REGEXP ‘[가-힣]’;

NOT REGEXP

-- REGEXP : 정규표현식을 활용한 조회
select * from author where name REGEXP '[a-z]';
select * from author where name REGEXP '[가-힣]';

IFNULL(a, b)

만약 a의 값이 NULL이 아니면 a 그 자체를 반환하고, NULL이면 b를 반환

-- ifnull(a,b) : 만약에 a가 null이면 b 반환, null이 아니면 a반환.
select title, contents, author_id from post;
select title, contents, ifnull(author_id, '익명') as 저자 from post;

타입 변환

CAST
- CAST(a AS type)
- a값(문자열,숫자)을 특정 type으로 변환
- SELECT CAST(20200101 AS DATE); => 2020-01-01
CONVERT
- 문자열, 숫자등을 날짜/시간으로 변환하는데 사용
- CONVERT('2020-01-01', DATE); => 2020-01-01
- CONVERT가 CAST에 비해 더 많은 옵션들을 가지고 있으나, 사용가능한 환경의 제약이 존재
DATE_FORMAT
- DATE_FORMAT 함수는 날짜/시간 타입의 데이터를 지정된 형식의 문자열로 변환
- 가장 많이 사용됨
- DATE_FORMAT(date, format)
  - ex)SELECT DATE_FORMAT('2020-01-01', '%Y-%m-%d'); => 2020-01-01

-- 날짜 변환 : 숫자 -> 날짜, 문자 -> 날짜
-- CAST / CONVERT
select CAST(20200101 AS DATE);
SELECT CAST('20200101' AS DATE);
select CONVERT(20200101, DATE);
SELECT CONVERT('20200101', DATE);

-- datetime 조회 방법
select * from post where created_time like '2024-05%';
select * from post where created_time <= '2024' and created_time >= '1999';
select * from post where created_time between '1999-01-01' and '2024-12-31';

-- date_format
select date_format(created_time, '%Y-%m') from post;
-- 실습 : post를 조회할 때 date_format 활용하여 2024년 데이터 조회
select * from post where date_format(created_time, '%Y') = '2024';

✏️ 제약조건(constraint)

데이터를 입력받을 때 실행되는 검사 규칙

CREATE 문으로 테이블을 생성 또는 ALTER 문으로 필드를 추가할 때 설정

NOT NULL

FOREIGN KEY

UNIQUE

PRIMARY KEY

NOT NULL
- default값은 nullable
- not null제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다.
```
-- not null 조건 추가
alter table 테이블명 modify  column 컬럼명 타입 not null;
```
UUID (Universally Unique Identifier)
- 데이터베이스 관리에서 널리 사용되는 유니크 식별자로, 특정 항목을 전 세계적으로 유일하게 식별할 수 있도록 설계됨.
- ALTER TABLE 테이블명 ADD COLUMN 컬럼명 CHAR(36) NOT NULL DEFAULT (UUID());
- 장점
  - 분산환경에서 DB통합에 이점
  - id 값이 유출되었을 때, 다른 id 값의 추측이 어렵게 하여 보안상 이점.
- 단점
  - int 또는 long 타입에 비해 더 많은 용량 차지
💡 UNIQUE, PK, FK ⇒ 자동으로 index가 생성된다. → 목차페이지(검색의 성능을 높일 수 있다.)
UNIQUE
- UNIQUE 제약 조건을 설정하면, 해당 필드는 값이 unique해야함을 의미
  - 특정 컬럼에 대해 각 행의 값이 고유함을 보장.
    -> 즉, 해당 컬럼에 중복된 값이 있으면 안 됨.
    예) 이메일은 사용자 마다 고유해야 함 > UNIQUE 적용
- 방법1 : CREATE TABLE 테이블이름
  (필드명 필드타입 UNIQUE, ...);
- 방법2 : CREATE TABLE 테이블이름
  (필드이름 필드타입, …, [CONSTRAINT 제약조건이름] UNIQUE (필드이름)
  );
  => UNIQUE 제약 조건을 별도로 정의하며, 선택적으로 제약 조건에 이름을 부여하는 방법
PRIMARY KEY
- PRIMARY KEY 제약 조건을 가진 컬럼을 기본키(pk)라 함
  - 기본 키는 해당 테이블의 데이터를 유일하게 식별하는 역할을 하며, 다른 테이블과의 관계에서 외래 키(FK)로 사용될 수도 있다.
    - NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다
- PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정
  - UNIQUE는 한 테이블의 여러 필드에 설정 가능
  - NOT NULL도 물론 여러 필드에 설정 가능
FOREIGN KEY ⇒ 만들면 제약 조건 및 index(성능-목차) 기본 생성
- 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할
  - 기준이 되는 다른 테이블의 내용을 참조해서 레코드가 입력
  - 하나의 테이블을 다른 테이블에 의존하게 만드는 것
  - 다른 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이어야 함
- 문법
  - CREATE TABLE 테이블이름
    ( 필드명 필드타입 FOREIGN KEY (필드이름) REFERENCES 참조테이블명 (참조필드명) [ON DELETE/UPDATE CASCADE]
  - 참조되는 테이블에서 데이터의 수정이나 삭제가 발생시 영향
  - ON DELETE : ON DELETE CASCADE → 부모 삭제 ⇒ 자식 삭제
  - ON UPDATE : ON UPDATE CASCADE → 부모 변경 ⇒ 자식 변경
    => 위 설정시 동작옵션
  - CASCADE : 참조되는 테이블에서 데이터를 삭제/수정하면 같이 삭제/수정
  - SET NULL : 참조되는 테이블에서 데이터를 삭제/수정하면 데이터는 NULL로 변경
  - RESTRICT
    - 자식 테이블의 데이터가 남아 있으면, pk 대상 데이터 수정/삭제 불가
    - 동작옵션을 주지 않으면 기본은 RESTRICT

-- on delete cascade 테스트 -> 부모 테이블의 id를 수정하면 수정이 안됨 ! 부모의 id가 수정되면 자식도 함께 변경되도록 해줌 : cascade;
-- delete : 삭제 가능 / update : 수정 가능
alter table post add constraint post_author_fk foreign key(author_id) references author(id) on delete cascade on update cascade;
-- 실습 delete => set null, update cascade;
alter table post drop foreign key post_author_fk;
alter table post add constraint post_author_fk foreign key(author_id) references author(id) on delete set null on update cascade;

-- 시간 세팅시 가장 많이 사용
ALTER TABLE author ADD column created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE post ADD column created_at DATETIME DEFAULT CURRENT_TIMESTAMP;

📌 today

타입 파트에서는 어떤 언어로든 프로그래밍을 공부하거나, 알고리즘 개념을 마주하다보면 자주 만나는 개념들이어서 수업도 실습도 수월하게 따라갔다.
하지만 개념이 쌓이고, 타입변환과 제약 조건과 같은 여러가지 응용이 쌓여가면서 수업 시간 실습이나 프로그래머스 문제 풀이에 시간 소요가 생기면서 지체되는 문제가 발생했다.

수업 시간에는 강사님의 개념 설명에 최대한 집중하면서 정답 코드를 그대로 따라 치더라도 실습을 놓치지 않는 것에 집중하였고, 집에 돌아와 오늘 배운 개념을 꼼꼼하게 정리해보았다. (이 글 속에 있는 개념들을 이해하고 작성하는데에만 3시간이 걸렸다..)

시간은 오래 걸렸지만, 복습 시간을 가지면서 오늘 배운 개념을 다시 마주했을 때 처음 마주한 것과 같은 두려움은 느끼지 않을 수 있을 것 같다! 응용력이 100% 되는 그 순간까지 열심히 달려보겠다!

아로롱

Dilige, et fac quod vis

이전 포스트

[한화시스템 BEYOND SW캠프 7기] #04. MariaDB_기본

다음 포스트