7 min to read

SQL과 MySQL

Featured image

데이터 유형 및 데이터 조작

데이터 유형 소개

MySQl의 데이터 유형은 테이블의 열에 저장할 수 있는 값의 종류를 정의함. 가능한 값의 범위와 해당 값에 대해 수행할 수 있는 작업을 지정함. 테이블의 각 열에는 어떤 종류의 데이터를 저장할 수 있는지 결정하는 특정 데이터 유형이 있음.

일반적인 데이터 유형

-- CREATION of table with a column using TYNYINT
CREATE TABLE user_profile(
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    active_user TINYINT(1)
);

-- TINYINT can be used to show TRUE or FALSE
-- User can be updated to show they are non active
UPDATE user_profile SET active_user = 0
WHERE user_id = 3;

위의 예에서 “user_profile” 테이블에는 INT 유형의 “user_id”, VARCHAR 유형의 “username”, TINYINT(1) 유형의 “active_user” 라는 3개의 열이 있음. “active_user” 열은 사용자가 활성 상태인지 여부에 대한 부울 형식의 정보를 저장하기 위함. 1은 활성, 0은 비활성.

TINYINT(1)은 부울 값을 나타내는 데 자주 사용되지만 모든 데이터베이스 시스템에서 부울 유형으로 엄격하게 적용되는 것은 아님. 일부 시스템에서는 TINYINT 범위(-128 ~ 127 또는 0 ~ 255 ) 내 모든 값을 허용할 수 있음. TINYINT가 부울 유사값에 대해 어떻게 처리되고 사용되는지 이해하려면 DB 시스템의 문서를 확인하는 것이 중요함.

데이터 유형의 중요성:

ex)

  1. 올바른 데이터 유형 선택:
    • 저장하는 데이터의 성격을 고려, 공간 낭비에 주의. 필요 이상의 큰 데이터 유형을 사용하지 말것, 반대로 데이터가 잘리거나 손실될 수 있는 경우 더 작은 데이터 유형을 사용.
  2. 데이터 유형 번경:
    • SQL 명령을 사용해 열의 데이터 유형을 변경할 수 있지만 올바르게 수행하지 않으면 데이터가 손실될 수 있으므로 주의.
  3. Null 값: 
    CREATE TABLE students(
 id INT NOT NULL PRIMARY KEY,
 name VARCHAR(100),
 class_number INT
);
    • ‘NULL’ 값을 허용하거나 허용하지 않는 속성이 있을 수도 있음. Null은 데이터가 없음을 나타냄.
  4. 추가 데이터 유형: ENUM (미리 정의된 값 목록), SET( 값 세트) 같은 특수 유형도 제공됨.

데이터 구조 생성:

테이블은 관계형 데이터베이스에서 데이터를 구성하고 저장하기 위한 기본 구조임. 행과 열로 구성되며 각 열에는 특정 데이터 유형이 있음.

-- DB 생성
CREATE DATABASE techstore;
-- DB 진입 및 사용
USE techstore;

CREATE TABLE product(
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_cost DECIMAL(5,2)
);

위와 같은 테이블을 생성하려면

CREATE TABLE table_name ();

다음으로 괄호 안에 해당 열에 저장될 데이터와 가장 일치하는 각 항목에 대해 신중하게 선택한 데이터 유형과 함께 테이블에 추가하려는 열을 쉼표로 구분해 나열.

CREATE TABLE table_name (
    column1_name INT,
    column2_name VARCHAR(255),
    column3_name BLOB,
    column4_name TIMESTAMP,
    column5_name FLOAT
);

이러한 열의 데이터를 처리하는 방법을 MySQL에 추가로 지정하는 다양한 선택적 “제약조건”을 추가할 것.

CREATE TABLE table_name (
    column1_name INT PRIMARY KEY AUTO_INCREMENT,
    column2_name VARCHAR(255) UNIQUE,
    column3_name BLOB NOT NULL,
    column4_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    column5_name FLOAT
);

테이블이 생성 되면 “DESCRIBE” 명령으로 언제든지 테이블 구조의 세부 사항을 볼 수 있음. “DESCRIBE product;”

테이블의 Primary Key 생성:

잘 설계된 DB의 모든 테이블에는 일반적으로 Primary key가 있음. 이는 테이블의 특정 데이터 행에 대한 고유 식별자 역할을 하도록 지정된 테이블의 특정 열임. 기본 키로 적합한 열을 선택하려면 해당 열의 데이터가 두 가지 요구 사항을 충족해야함.

  1. 고유성 - 기본 키 열의 두 행은 동일값을 가질 수 없음.
  2. 항상 값이 있음(Null 아님) - 기본 키 열의 데이터 행은 비어있으면 안됨. SQL에서 데이터 조각이 비어있는 경우는 NULL이며 이로 인해 이전 고유성 요구 사항도 무효화됨.

기본 키는 특별히 생성된 “[table_name]_id”라는 간단한 정수 열인 경우가 많음. 기본 키를 추가하는 것은 “PRIMARY KEY” 라는 단어를 추가하는 것만큼 간단함.

테이블의 기본 키는 때로 2개 이상의 열로 구성될 수 있음. 이를 composite primary key라고 함. 복합 기본 키는 두 개 이상의 열로 구성될 순 있으나 여전히 단일 기본 키로 간주됨.

Foreign Key를 사용해 테이블 관계설정:

techstore DB에 “order”, “customer” 테이블을 추가하기. 첫 번째로 주문을 처리하고, 두 번째로 주문 고객을 추적함. 여기에는 2개의 별도 CREATE TABLE 문이 필요함. 그 다음 3 테이블 사이에 2가지 관계를 설정함. 하나는 order 와 product 간의 관계이고, 다른 관계는 order와 customer 간의 관계임.

먼저 order table을 생성함. “order_id”, “customer_id”, “product_id” 라는 4개의 열을 저장함. Foreign Key로 시작하는 2개의 열을 추가함. 이는 열이 아니라 두 테이블 간의 관계를 생성하는 특수한 유형의 제약 조건임.

CREATE TABLE order(
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

이 코드를 실행하면 오류가 발생함. 오류는 “참조 테이블을 열지 못함” 같은 내용이 표시됨. MySQL은 테이블 간 관계를 매우 엄격하게 처리하며 두 테이블 중 하나라도 존재하지 안으면 두 테이블의 연결을 허용하지 않음. 아직 customer 테이블을 생성하지 않았으므로, 위 FOREIGN KEY 제약 조건이 실패함. 먼저 customer 테이블을 생성한 다음 order 테이블을 생성해보도록 할것.

-- 4개의 customer 데이터
CREATE TABLE customer(
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

order 테이블을 다시 생성해보면 문제가 없음. product 테이블과 customer 테이블이 모두 존재하기 때문.

“FOREIGN KEY” 제약 조건은 한 테이블의 PRIMARY KEY 다른 테이블의 FOREIGN KEY 간의 관계를 설정하는데 사용함. FOREIGN KEY는 항상 PRIMARY KEY에 연결됨.