웹 풀사이클 데브코스 TIL [Day 10] - MariaDB Data Types & Keys

JaeKyung Hwang·2023년 11월 27일
FKPKTILforeign keymariadb data typesprimary keyunique key교육생국비지원교육수강후기코딩부트캠프프로그래머스 데브코스
0

프로그래머스 웹 풀사이클 데브코스 TIL

목록 보기
10/85
post-thumbnail

스프린트1

2023.11.27(월)

📜포트폴리오 웹 페이지 제작

  • HTML/CSS만을 이용해 디자인
  • Github Pages로 웹 페이지 배포
    • main html 파일명을 index.html로 해야 동작한다!

      GitHub Pages will look for an index.html, index.md, or README.md file as the entry file for your site.

  • 생각보다 css쪽이 힘들었다. 특히 가로 세로로 내가 원하는 위치에 요소들을 배치하는 게 까다로운 것 같다..
  • 간단하게 만들어 봤다.

💾데이터베이스 모델링

word에 코드 넣을 때 Alt + N & J(개체) > OpenDocument Text로 Pygments에서 꾸민 SQL 코드를 붙여넣어서 작성하면 편하다.

이번 데이터베이스 모델링 과제가 하나의 table이 다른 table을 참조하는 형태라 이와 관련된 MariaDB Key와 Data Type에 대해 가볍게 읽어보았다.

🤔MariaDB Data Types

  • 나는 이번에 과제에 INT와 VARCHAR 말고도 table의 date field에 대해 DATETIME Type('YYYY-MM-DD hh:mm:ss' 형태)을 사용해보았다.
  • 예매 수량의 경우 최대 100장이니까 TINYINT를 써도 될 것 같다.
  • 공식 문서 : MariaDB Data Types

숫자 타입

  1. 정수 타입(integer types)

    • 표현할 수 있는 최댓값, 최솟값이
      • 각 정수 타입에 따라 저장 공간이 달라서 달라짐
      • INT[(M)] [SIGNED | UNSIGNED | ZEROFILL]
        • type 뒤에 다음 조합만 가능 (MariaDB 10.2.8 ~)
          • SIGNED (default)
          • UNSIGNED
          • ZEROFILL
          • UNSIGNED ZEROFILL
          • ZEROFILL UNSIGNED
        타입저장 공간최댓값최솟값
        SignedUnsignedSignedUnsigned
        TINYINT/INT11바이트-1280127255
        SMALLINT/INT22바이트-3276803276765535
        MEDIUMINT/INT33바이트-83886080838860716777215
        INT/INT44바이트-2147483648021474836474294967295
        BIGINT/INT88바이트-92233720368547758080922337203685477580718446744073709551615

  2. 고정 소수점 타입(fixed-point types)

    • 실수의 값을 정확하게 표현하기 위해 사용
    • DECIMAL[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
      • M : 소수부를 포함한 실수의 총 자릿수 ≤ 65
      • D : 소수부 자릿수 (default 0 → 반올림) ≤ 38 (MariadB 10.2.1 이후), ≤ 30 (MariadB 10.2.1 이전)

  3. 부동 소수점 타입(floating-point types)

    • 부동소수점은 고정소수점보다 큰 수를 다룰 수 있다
    • M과 D가 생략되면 하드웨어가 허용하는 한계까지 값이 저장
      타입저장 공간설명
      FLOAT[(M,D)] [SIGNED \| UNSIGNED \| ZEROFILL]4바이트single-precision floating-point number는 대략 소수점 7자리까지 정확함
      DOUBLE[(M,D)] [SIGNED \| UNSIGNED \| ZEROFILL]8바이트double-precision floating-point number 대략 소수점 15자리까지 정확함

  4. 비트값 타입(bit-value type)

    • 0과 1로 구성되는 binary 값 저장 (M 비트)
    • M 비트보다 짧은 비트 값은 자동으로 zero padding
    • 보통 이진 플래그나 상태를 나타내는 데 사용
    • BIT[(M)]
      • M : 비트 길이 (1 ≤ M ≤ 64) (default 1)
      • b'value'로 값 입력

  5. 논리 자료형(boolean type)

    • BOOL/BOOLEAN : 0은 false, 0이 아닌 값은 true로 간주
    • True = 1, False = 0이므로 boolean은 이 값으로 비교를 하면 안됨. 🔗

문자열 타입

1. CHAR와 VARCHAR

  • 둘 다 문자열 데이터 저장 가능
  • 저장 방식과 추출 방식, 최대 길이를 다루는 방식에서 차이가 있음

    • M : 저장할 수 있는 최대 문자열 길이

      타입범위짧은 문자열
      CHAR[(M)]0 ≤ M ≤ 255 (default 1)오른쪽을 공백으로 채워서 길이를 맞춤
      PAD_CHAR_TO_FULL_LENGTH SQL 모드가 활성화되지 않은 경우 CHAR 값이 검색되면 후행 공백 제거
      VARCHAR(M)0 ≤ M ≤ 65532실제 입력된 문자열의 길이만큼만 저장하고 사용, 후행 공백 제거 X

2. BINARY와 VARBINARY

  • 각각 CHAR과 VARCHAR과 동일하지만 이진 데이터 블록을 저장 (M 바이트)
  • BINARY(M), VARBINARY(M)

3. BLOB과 TEXT

  • BLOB은 Binary Large Object로 다양한 크기의 바이너리 데이터를 저장
    • 저장할 수 있는 데이터 최대 크기에 따라 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB으로 구분
  • TEXT는 VARCHAR과 비슷
    • 저장할 수 있는 데이터 최대 크기에 따라 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT으로 구분

4. ENUM

  • 미리 정의한 집합 안의 요소 중 하나만을 저장
  • 가독성 GOOD, 특정 숫자에 문자열로 의미 부여 가능
  • ENUM 목록 집합은 최대 65535개의 ENUM 데이터를 가질 수 있음
  • ENUM 데이터는 내부적으로 정수로 변환되어 인식
    • 내부적으로 0부터 시작해서 1씩 증가
    • 단, 0은 빈 문자열과 대응, 1부터 집합의 첫 요소와 대응
      • ex) ENUM('apple','orange','pear')
        IndexValue
        NULLNULL
        0''
        1'apple'
        2'orange'
        3'pear'
  • ENUM('val1','val2',...)
    • 실제 문자열 data를 입력하거나 mapping되는 정수 값을 입력
    • table 입력 시 ENUM 데이터에서 후행 공백 자동 제거
  1. SET
  • 미리 정의한 집합 안의 요소 중 여러 개를 동시에 저장
  • SET 목록 집합은 최대 64개의 SET 데이터를 가질 수 있음
  • SET 데이터에는 쉼표(,)를 포함할 수 없음
  • SET 데이터는 내부적으로 정수로 변환되어 인식
  • SET('val1','val2',...)
    • 0개 이상의 문자열 data를 가질 수 있음

날짜와 시간 타입

  • DATE, DATETIME, TIMESTAMP, TIME, YEAR

    • 입력받은 데이터가 유효한 날짜와 시간이 아니면 모두 0을 저장

    • 날짜 형식 변경

    • 0 ≤ microseconed precision ≤ 6 (default 0)

      타입설명
      DATE날짜 저장 : 'YYYY-MM-DD’ 형식
      ‘1000-01-01' ~ '9999-12-31
      DATETIME [(microsecond precision)]날짜 & 시간 저장 : 'YYYY-MM-DD HH:MM:SS.ffffff’ 형식 (default CURRENT_TIMESTAMP)
      ’1000-01-01 00:00:00.000000’ ~ ‘9999-12-31 23:59:59.999999’
      ※ SQL_MODE에 NO_ZERO_DATE가 지정되어 있지 않은 한 '0000-00-00'을 특별한 zero 날짜 값으로 지원
      ※ 날짜의 개별 구성 요소도 SQL_MODE에 NO_ZERO_IN_DATE가 지정되어 있지 않은 한 0(ex: '2015-00-12')으로 설정
      ※ ALLOW_INVALID_DATES SQL_MODE가 enable된 경우, 일 부분이 1 ~ 31 사이에 있으면 31일 미만인 달에도 오류 발생 X
      TIMESTAMP [(<microsecond precision)]날짜 & 시간 저장 : 'YYYY-MM-DD HH:MM:SS.ffffff’ 형식

      ▸explicit_defaults_for_timestamp가 OFF일 때(= MariaDB 10.10까지 Default 값)
      레코드가 삽입되거나 업데이트될 때 열에 값이 명시적으로 할당되지 않은 경우 또는 NULL 값이 할당된 경우 자동으로 현재 날짜 및시간으로 초기화
      (단 레코드의 첫번째 TIMESTAMP에만 적용, 이후 TIMESTAMP열에는 반영 X : TIMESTAMP열이 여러 개면 첫번째에만 적용된다는 뜻)
      → 자동으로 DEFAULT CURRENT_TIMESTAMP와 ON UPDATE CURRENT_TIMESTAMP 속성이 할당된 것과 동일
      DEFAULT에 상수 값을 줘서 자동 초기화를 비활성화 가능

      ▸explicit_defaults_for_timestamp가 ON일 때(= MariaDB 10.10+부터 Default 값)
      다른 일반 type처럼 속성 할당해서 사용 가능

      일반적으로 행이 추가되거나 업데이트된 시점을 정의하는 데 사용
      '1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC
      TIME [(<microsecond precision>)]시간 저장 : 'HH:MM:SS.ssssss’
      '-838:59:59.999999' ~ '838:59:59.999999’
      YEAR4자리 연도 저장 (2자리 연도는 MariaDB 5.5.27 이후로 deprecated)
      숫자와 문자열 모두 가능
      1901 ~ 2155, 0000
      ※ 숫자 0은 0년, 문자열 ‘0’은 2000년 반영 → 문자열을 사용하는 것이 안전할 것 같다

🔑MariaDB KEY

기본키(Primary Key)

  • unique(중복 X) & NULL 불가능
  • 기본키는 table당 하나만 가능
  • 기본키로 만들 index는 반드시 NOT NULL로 정의되어야 함
  • MariaDB의 default storage engine인 InnoDB에서는 PK가 존재하지 않고 UNIQUE index가 없는 경우, 사용자에게 보이지 않는 6-bytes clustered index를 생성함
  • 보통 AUTO_INCREMENT와 함께 많이 쓰임
  • 기본키는 보통 table을 생성할 때 추가됨

    • PRIMARY KEY ('col')로 지정하거나 속성으로 PRIMARY KEY를 넣어도 됨

      CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  **PRIMARY KEY (`ID`)**
) ENGINE=Aria;
  • table 생성 후에 추가 (CREATE INDEX 명령어는 불가능)
    • ALTER TABLE Employees ADD PRIMARY KEY (ID);

고유키(Unique Key)

  • unique(중복 X) & NULL 가능
  • table 생성 시 추가

    • UNIQUE KEY ('col')로 지정하거나 속성으로 UNIQUE를 넣어도 됨

      CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  `Employee_Code` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`ID`),
  **UNIQUE KEY (`Employee_Code`)**
) ENGINE=Aria;
  • table 생성 후에 추가 (CREATE INDEX와 ALTER TABLE 명령어 둘 다로 가능)
    • ALTER TABLE Employees ADD UNIQUE EmpCode(Employee_Code);
    • CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);
  • UNIQUE index는 여러 개 가능 → 이 경우 index의 조합이 unique하면 됨
    CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b));

INSERT INTO t1 values (1,1), (2,2);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

INSERT INTO t1 values (2,1);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
+---+------+
  • SQL에서 NULL끼리는 서로 동일하게 취급되지 않음! ⇒ NULL Key를 가지는 record는 중복 저장됨
    INSERT INTO t1 values (3,NULL), (3, NULL);

SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 2 |    2 |
| 3 | NULL |
| 3 | NULL |
+---+------+

외래키(Foreign Key)

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT or NO ACTION | CASCADE | SET NULL | SET DEFAULT

  • 다른 테이블의 기본키 참조

  • 테이블과의 관계를 설정하고 데이터 무결성(data integrity)을 강화하기 위해 사용!

  • 외래키 열과 참조되는 열은 반드시 같은 data type이어야 함

  • 부모와 자식 테이블은 반드시 같은 storage engine을 사용해야 함

    • 부모 테이블(parent table)과 자식 테이블(child table)? 아래 그림을 보면 바로 이해된다.

  • 부모 table : author
    자식 table : book

    CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

  • 참조하는 부모 키가 존재하지 않으면 오류 발생

    INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

  • 부모 키의 행을 삭제하거나 수정할 수 없음!

    UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

  • table 생성 후에 추가 (ALTER TABLE 명령어로 가능)

    • ALTER TABLE book ADD [CONSTRAINT fk_book_author] FOREIGN KEY (author_id) REFERENCES author (id);

이 외에도 다른 KEY 종류가 있다.

profile
JaeKyung Hwang
이것저것 관심 많은 개발자👩‍💻
이전 포스트

웹 풀사이클 데브코스 TIL [Day 9] - 데이터베이스

다음 포스트

웹 풀사이클 데브코스 TIL [Day 11] - 짧은 회고

0개의 댓글