[SQL]17장. 테이블 생성과 조작

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

---

📍 테이블 생성과 조작

• 데이터베이스 테이블을 생성하는 데는 두 가지 방법이 있다.
  • DBMS는 데이터베이스 테이블을 생성하고 관리할 수 있는 대화형 관리 툴을 제공한다.
  • SQL 문으로 테이블을 직접 생성하고 관리한다.
• 테이블을 생성하려면 CREATE TABLE 이라는 SQL 문을 사용한다.
• DBMS에서 제공하는 대화형 관리 툴을 사용할 때도 실제로는 SQL 문을 사용한다.

---

📌 기본 테이블 생성

• CREATE TABLE을 사용해서 테이블을 생성할 때 다음 규칙이 준수되어야 한다.
  • CREATE TABLE 문 뒤에 새로운 테이블 이름을 적는다.
  • 테이블 이름과 정의를 콤마(,)로 구분하여 적는다.
  • 일부 DBMS에서는 테이블의 위치도 명시해야 한다(테이블을 생성할 특정 데이터베이스).

🧷 Products 테이블 생성 예제

CREATE TABLE Products
(
    prod_id     CHAR(10)        NOT NULL,
    vend_id     CHAR(10)        NOT NULL,
    prod_name   CHAR(254)       NOT NULL,
    prod_price  DECIMAL(8,2)    NOT NULL,
    prod_desc   VARCHAR(1000)   NULL
);

• CREATE TABLE 테이블 뒤에 바로 테이블 이름이 나온다.
• 실제 테이블 정의(열 정의)는 괄호에 둘려싸여 있고, 열은 콤마(,)로 구분된다.
• 각 열의 정의는 열 이름으로 시작하고, 그 다음으로는 열의 데이터형이 나온다.
• 마지막으로 해당 열이 NULL 값이 가능한지에 대한 정보를 입력한다.

💡 존재하는 테이블 이름을 사용하면 안된다.

새로운 테이블을 생성할 때, 존재하는 테이블 이름을 사용하면 에러가 발생한다.
실수로 덮었쓰는 것을 막기 위해, SQL은 테이블을 직접 삭제하고 다시 만들도록 유도한다.
따라서 이미 존재하는 테이블 이름을 사용하여 테이블을 덮어쓸 수는 없다.

---

📌 NULL 값 사용하기

• NULL을 허용하는 열은 그 열에 아무런 값을 넣지 않아도 행 삽입이 허용된다.
• 반면 NULL을 허용하지 않는 열은 값이 없는 행을 허용하지 않는다.
  • 즉, 행이 삽입되거나 업데이트될 때 그 열이 꼭 필요하다는 의미이다.
• 모든 테이블 열은 NULL이나 NOT NULL 열이고, 그것은 테이블이 생성되는 시점에 테이블 정의에서 결정된다.

❗️ NULL 명시

대부분의 DBMS는 NOT NULL 이 없으면 NULL로 인식하지만, 모든 DBMS가 그런 것은 아니다.
일부 DBMS는 NULL 키워드를 쓰지 않으면 에러가 발생한다.

💡 기본 키와 NULL 값

기본 키는 테이블에서 각 행을 구별해주는 고유한 값을 가지는 열이다.
NULL 값을 허용하지 않는 열만 기본 키로 사용할 수 있다.
아무런 값을 가지지 않을 수 있는 열(NULL 값을 허용하는 열)은 고유한 구별자로 사용할 수 없다.

❗️ NULL 이해하기

NULL 값과 빈 문자열을 혼동해서는 안된다.
NULL은 아직 값이 없는 것이지 빈 문자열이 아니다.
빈 문자열('')은 NOT NULL 열에 허용되는 값이다.
즉, NULL은 현재 시점에 해당 열의 값을 모른다는 것을 의미하고 빈 문자열은 해당 열의 값이 없다는 것을 의미한다.

---

📌 기본값 지정하기

• SQL에서는 행을 삽입할 때 값이 없으면 자동으로 들어가는 기본값을 지정할 수 있다.
• 기본값은 CREATE TABLE 문에서 열을 정의할 때 DEFAULT 키워드를 사용해서 지정한다.

CREATE TABLE OrderItems
(
    order_num   INTEGER         NOT NULL,
    order_item  INTEGER         NOT NULL,
    prod_id     CHAR(10)        NOT NULL,
    quantity    INTEGER         NOT NULL DEFAULT 1,
    item_price  DECIMAL(8,2)    NOT NULL
);

• 기본값은 날짜나 시간 열에서도 자주 사용한다.
• 시스템 날짜를 가져오는 함수나 변수를 사용하여 행을 삽입하는 시점의 날짜와 시간을 기본값으로 지정할 수 있다.

DBMS	함수/변수
Db2	CURRENT_DATE
MySQL	CURRENT_DATE() or NOW()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	date('now')

💡 NULL 값 대신 기본값 사용하기

많은 데이터베이스 개발자들은 NULL 열 대신 기본값을 선호하는데, 특히 계산이나 데이터 그룹핑에 사용한다.

---

📍 테이블 변경하기

• 테이블 정의를 업데이트할 때 ALTER TABLE 문을 사용한다.
• 다음은 ALTER TABLE을 사용할 때 고려해야 할 점이다.
  • 기본적으로 데이터가 있는 테이블은 변경해서는 안된다. 테이블을 설계할 때 시간을 충분히 할애하여, 나중에 많은 변경이 필요하지 않도록 미래의 필요를 예측해야 한다.
  • 모든 DBMS는 존재하는 테이블에 열을 추가하는 것은 허용하지만, 추가하는 열의 데이터형에 몇 가지 제약을 둔다.
  • 다수의 DBMS는 테이블에 있는 열을 제거하거나 변경하는 것을 허용하지 않는다.
  • 대부분의 DBMS는 열 이름의 변경을 허용한다.
  • 다수의 DBMS는 데이터가 있는 열을 변경하는 것은 제한하고, 데이터가 없는 열을 변경하는 것에는 제한 사항을 많이 두지 않는다.

• ALTER TABLE 을 이용하여 테이블을 변경하기 위해서는 다음 정보를 반드시 적어야 한다.
  • ALTER TABLE 문 뒤에 변경할 테이블 이름을 적는다(테이블이 존재하지 않으면 에러가 발생한다).
  • 변경할 사항을 나열한다.

• 존재하는 테이블에 열을 추가하는 것은 모든 DBMS가 지원하는 유일한 작업이다.

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

• 열을 수정하거나 삭제하는 것 또는 제약 사항이나 키를 추가하는 등의 다른 변경 작업도 비슷한 문법을 사용한다.

ALTER TABLE Vendors
DROP COLUMN vend_phone;

• 복잡한 테이블 구조를 변경하려면, 보통 다음 순서대로 진행해야 한다.

1. 새로운 열 구조를 가진 새 테이블을 생성한다.
2. INSERT SELECT 문을 사용하여 이전의 테이블에 있는 데이터를 새로운 테이블에 복사한다. 필요하다면 변환함수나 계산 필드를 사용한다.
3. 새로운 테이블에 원하는 데이터가 있는지 확인한다.
4. 이전 테이블의 이름을 변경한다(또는 삭제한다).
5. 새로운 테이블을 이전에 사용한 테이블 이름으로 변경한다.
6. 필요하다면 트리거, 저장 프로시저, 인덱스, 외래 키 등을 다시 생성한다.

❗️ ALTER TABLE 문을 신중히 사용하라

ALTER TABLE을 사용할 때는 매우 신중해야 한다.
작업 전에 완벽히 백업(스키마와 데이터 모두)해야 한다.
데이터베이스 테이블 변경은 되돌릴 수 없는 작업이다.
필요 없는 열이 추가되었다면 그 열을 제거할 수는 있다.
하지만 실수로 필요한 열을 삭제한다면, 그 열에 있는 데이터는 모두 잃게 될 것이다.

---

📍 테이블 삭제하기

• DROP TABLE 구문을 사용하면 테이블을 삭제할 수 있다.

DROP TABLE CustCopy;

💡 실수로 테이블을 삭제하지 못하도록 관계 규칙을 사용하라.

많은 DBMS가 다른 테이블과 관련된 테이블은 삭제하지 못하게 규칙을 정할 수 있도록 한다.
규칙을 설정하면 그 관계를 삭제하기 전까지 DBMS가 관련된 테이블을 삭제하지 못하게 막는다.
필요한 테이블을 실수로 삭제하지 않도록 가능하면 규칙을 사용하는 것이 좋다.

---

📍 테이블 이름 바꾸기

• 테이블 이름을 바꾸는 것은 DBMS마다 아주 다르게 지원한다.
• Db2, MariaDB, MySQL, Oracle, PostgreSQl 사용자들은 RENAME 문장을 사용할 수 있다.
• SQL Server 사용자들은 sp_rename 이라는 저장 프로시저를 사용할 수 있다.
• 테이블 이름을 바꾸기 위한 기본 문법은 이전의 테이블 이름과 새로운 테이블 이름을 모두 적는 것이다.

---

📍 도전 과제

1. Vendors 테이블에 웹사이트 열(vend_web)을 추가하라. 필드는 URL이 충분히 들어갈 수 있을 정도로 커야 한다.

ALTER TABLE Vendors
ADD vend_web CHAR(254);

2. Vendors 테이블을 업데이트하여 웹사이트를 추가하라.

UPDATE Vendors
SET vend_web = 'www.google.com'
WHERE vend_id = 'DLL01'