SQL 기초(4) schema

SQLBolt 참고

1. What is a Schema?

In SQL, the database schema is what describes the structure of each table, and the datatypes that each column of the table can contain.

SQL에서 데이터베이스 스키마는 각 테이블의 구조와 테이블의 각 열이 포함 할 수있는 데이터 유형을 설명하는 것이다.

• 데이터의 고정된 유형은 데이터베이스가 수백만 또는 수십억 개의 행을 저장하더라도 효율적이고 일관성을 유지할 수 있도록한다.

• 스키마의 예)

CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

2. Inserting new data

• 기본

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

• 열을 지정해 데이터 삽입

(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

경우에 따라, 불완전한 데이터가 있고 테이블에 기본값을 지원하는 열이 포함 된 경우, 행 추가 시 원하는 열의 공간을 지정해 데이터를 추가 할 수 있다.

• 값에 수식, 문자열 표현식 가능

INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000);

3. Updating rows

• 업데이트

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

✔ 열과 행이 항상 짝을 이룸.
✔ 잘못된 행 집합을 업데이트하거나, WHERE 절을 생략해 모든 행에 업데이트 하는 실수 주의.
✔ 팁: 항상 제약 조건(WHERE)을 먼저 작성하고 SELECT 쿼리에서 테스트하여 올바른 행을 업데이트하고 있는지 확인한 다음 업데이트 할 열 / 값 쌍을 작성

ex) UPDATE Movies
SET Director = "John Lasseter"
WHERE Id = 2

4. Deleting rows

• 행 삭제

DELETE FROM mytable
WHERE condition;

WHERE을 사용하지 않으면 모든 행 삭제
Update와 같은 실수 주의! Always read your DELETE statements twice and execute once.

ex) DELETE FROM Movies
WHERE Year < 2005

5-1. Creating tables

• 테이블 추가

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

새로운 테이블의 구조가 스키마로 정의될 경우 테이블 생성이 가능하다.
이미 같은 이름의 열이 존재하면 error가 나타난다. 그렇기에 IF NOT EXISTS를 사용한다.

5-2. 테이블 유형 조건

1. ⭕ 허용 조건

• INTEGER, BOOLEAN
  : 정수 데이터 유형은 숫자 또는 나이의 개수와 같은 정수 값을 저장할 수 있습니다. 일부 구현에서 부울 값은 0 또는 1의 정수 값으로 표시됩니다.
• FLOAT, DOUBLE, REAL
  : 부동 소수점 데이터 유형은 측정 값 또는 분수 값과 같은 보다 정확한 숫자 데이터를 저장할 수 있습니다. 해당 값에 필요한 부동 소수점 정밀도에 따라 다른 유형을 사용할 수 있습니다.
• CHARACTER(num_chars), VARCHAR(num_chars), TEXT
  : 텍스트 기반 데이터 유형은 모든 부분에서 문자열과 텍스트를 저장할 수 있습니다. 다양한 유형 간의 차이는 일반적으로 텍스트로 작업 할 때 데이터베이스의 효율성을 뒷받침합니다.
  CHARACTER 및 VARCHAR (변수 문자) 유형은 모두 저장할 수있는 최대 문자 수로 지정되므로 (더 긴 값은 잘릴 수 있음) 큰 테이블로 저장하고 쿼리하는 것이 더 효율적일 수 있습니다.
• DATE, DATETIME
  : SQL은 또한 시계열 및 이벤트 데이터를 추적하기 위해 날짜 및 시간 스탬프를 저장할 수 있습니다. 시간대에 걸쳐 데이터를 조작 할 땐 작업하기가 까다로울 수 있습니다.
• BLOB
  : 마지막으로 SQL은 데이터베이스의 blob에 바이너리 데이터를 저장할 수 있습니다. 이러한 값은 종종 데이터베이스에 대해 불투명하므로 다시 쿼리하려면 일반적으로 올바른 메타 데이터와 함께 값을 저장해야합니다.

2. ❌제약 조건

• PRIMARY KEY
  : 이는 이 열의 값이 고유하며 각 값을 사용하여 이 테이블의 단일 행을 식별 할 수 있음을 의미합니다.
• AUTOINCREMENT
  : 정수 값의 경우, 값이 자동으로 채워지고 각 행이 삽입 될 때마다 증가 함을 의미합니다. 모든 데이터베이스에서 지원되지는 않습니다.
• UNIQUE
  : 이 열의 값은 고유해야하므로이 열에 같은 값을 가진 다른 행을 테이블의 다른 행과 삽입 할 수 없습니다. 테이블의 행에 대한 키가 아니어도된다는 점에서PRIMARY KEY와 다릅니다.
• NOT NULL
  : 이는 삽입 된 값이 'NULL'이 될 수 없음을 의미합니다.
• CHECK (expression)
  : 삽입 된 값이 유효한지 테스트하기 위해 더 복잡한 식을 실행할 수 있습니다. 예를 들어 값이 양수인지, 특정 크기보다 큰지, 특정 접두사로 시작하는지 등을 확인할 수 있습니다.
• FOREIGN KEY
  : 열의 각 값이 다른 테이블의 열에있는 다른 값과 일치하는지 확인하는 일관성 검사입니다.
  예를 들어, ID별로 모든 직원을 나열하는 테이블과 급여 정보를 나열하는 테이블이 두 개있는 경우 'FOREIGN KEY'는 급여 테이블의 모든 행이 마스터 직원 목록의 유효한 직원에 해당하는지 확인할 수 있습니다.

6. Altering tables

: to add, remove, or modify columns and table constraints.

• Adding columns

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

- MySQL과 같은 데이터베이스들에서 FIRST, AFTER 을 사용해 추가할 열을 지정할 수 있다.

ex)
ALTER TABLE Movies
ADD COLUMN Language TEXT
DEFAULT "English";
-> Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.

• Removing columns

ALTER TABLE mytable
DROP column_to_be_deleted;

-일부 데이터베이스(SQLite 포함)는 이 기능을 지원하지 않는다. 대신 새 테이블을 만들고 데이터를 옮길 수 있다.

• Renaming the table

ALTER TABLE mytable
RENAME TO new_table_name;

• 그 외
  각 데이터베이스 구현은 테이블을 변경하는 다양한 방법을 지원하므로 진행하기 전에 항상 데이터베이스 문서 (MySQL, Postgres, SQLite, Microsoft SQL Server)를 참조하는 것이 가장 좋습니다.

7. Dropping tables

• 전체 테이블 제거

DROP TABLE IF EXISTS mytable;

드문 경우이긴 하지만 모든 데이터 및 메타데이터를 포함하는 전체 테이블을 제거할 수 있습니다. 이렇게 하려면 테이블 스키마를 데이터베이스에서 완전히 제거한다는 점에서 DELETE 문과 다른 DROP TABLE 문을 사용할 수 있습니다.

CREATE TABLE 문과 마찬가지로 지정된 테이블이 없으면 데이터베이스에서 오류가 발생할 수 있어 IF EXISTS 절을 사용할 수 있습니다.

또한 제거할 테이블의 열에 종속된 다른 테이블이 있는 경우(예: FOREIGN KEY 종속성이 있는 경우) 종속된 행을 제거하거나 해당 테이블을 완전히 제거하려면 먼저 모든 종속 테이블을 업데이트해야 합니다.

ex) DROP TABLE IF EXISTS Movies;