갱동구리

Be brave.

[SQL] SQL DDL 기초(1)

업데이트:     Updated:

카테고리:

태그:

🚀 1. 개요

(DDL: Data Definition Language)중 CREATE문에 대해 알아본다.

  • CREATE - 데이터베이스, 테이블 생성
  • ALTER - 테이블 변경
  • DROP - 테이터베이스, 테이블 삭제

2. CREATE

2.1 CREATE DATABASE

  • 새 데이터베이스를 만듭니다. (데이터베이스를 생성하기 전에 관리자 권한이 있는지 확인해야합니다.)
  • SHOW DATABASES; 명령을 사용하여 데이터베이스 목록을 확인할 수 있습니다.
CREATE DATABASE databasename;

2.2 CREATE TABLE

  • 새 테이블을 만듭니다. (column 매개변수는 테이블의 속성 이름을 지정하며, datatype 매개변수는 데이터 유형을 지정합니다. )
  • 데이터베이스 시스템 별 데이터 유형을 참고하려면 다음 링크를 확인하세요. 데이터 유형
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);
  • 다른 테이블을 사용하여 테이블을 만들 수도 있습니다. (새 테이블은 기존 테이블의 모든 열 또는 특정 열을 선택하여 정의할 수 있으며, 새 테이블은 기존 테이블의 값으로 채워집니다.)
CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;
2.2.1 자동 증분
  • 자동 증분을 사용하면 새 레코드가 테이블에 삽입될 때 고유 번호가 자동으로 생성됩니다.
  • 새 레코드가 삽입될 때마다 자동으로 생성하려는 필드는 기본 키인 경우가 많습니다.
  • 다음 SQL 문은 “Personid” 열을 “Persons” 테이블의 자동 증가 필드로 정의합니다. (MySQL 구문)
CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);
  • MySQL은 AUTO_INCREMENT 키워드를 사용하여 자동 증가 기능을 수행합니다.
  • 기본적으로 시작 값은 1이며 AUTO_INCREMENT은 각 새 레코드에 대해 1씩 증가합니다.
  • AUTO_INCREMENT 시퀀스가 다른 값으로 시작 되도록 하려면 다음 SQL 문을 사용합니다.
ALTER TABLE Persons AUTO_INCREMENT=100;

NOTE: 데이터베이스 시스템에 따라 자동 증분을 사용하는 방법은 다릅니다. 특히 오라클에서는 조금 더 까다로우니 사용하는 시스템에 따라 사용 법을 참고하시기 바랍니다.

2.2.2 SQL 제약
  • SQL 제약 조건은 테이블의 데이터에 대한 규칙을 지정하는 데 사용됩니다.
  • 제약 조건은 테이블에 들어갈 수 있는 데이터 유형을 제한할 수 있습니다.
  • 제약 조건은 열 수준 또는 테이블 수준에서 정의할 수 있습니다.
  • 다음 제약 조건은 SQL에서 일반적으로 사용됩니다.
    • NOT NULL - 열이 NULL 값을 가질 수 없도록 합니다.
    • UNIQUE - 열이 중복 값을 가질 수 없도록 합니다.
    • PRIMARY KEY - NOT NULL과 UNIQUE의 조합으로 테이블의 각 행을 고유하게 식별할 수 있는 기본키를 지정합니다.
    • FORIEGN KEY - 외래키를 지정합니다.
    • CHECK - 열의 값이 특정 조건을 충족하는지 확인
    • DEFAULT - 값이 지정되지 않은 경우 열의 기본값을 설정합니다.
    • CREATE INDEX - 색인을 지정합니다.
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);
2.2.2.1 NOT NULL 제약
  • 기본적으로 열은 NULL 값을 사용할 수 있습니다.
  • NOT NULL 제약 조건은 열에서 NULL 값을 허용하지 않도록 강제합니다. 이는 필드가 항상 값을 가져야한다는 것을 의미합니다.
  • 다음 SQL은 “Persons” 테이블이 생성될 때 “ID”, “LastName”, “FirstName” 열이 NULL 값을 허용하지 않도록 합니다.
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
2.2.2.2 UNIQUE 제약
  • UNIQUE 제약 조건은 열의 모든 값이 서로 다른지 확인합니다.
  • PRIMARY KEY 제약 조건은 자동으로 UNIQUE 제약 조건을 갖습니다.
  • 다음 SQL은 “Persons” 테이블이 생성될 떄 “ID” 열에 UNIQUE 제약 조건을 생성합니다. (Oracle 기준)
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
2.2.2.3 PRIMARY KEY 제약
  • PRIMARY KEY 제약 조건은 테이블의 각 레코드를 유일하게 식별합니다.
  • 테이블은 하나의 기본 키만 가질 수 있으며, 테이블에서 이 기본키는 단일 열 또는 다중 열로 구성될 수 있습니다.
  • 다음 SQL은 “Persons” 테이블이 생성될 떄 “ID”열에 PRIMARY KEY 제약 조건을 생성합니다.
CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
  • 제약 조건의 이름(PK_Person)을 지정하고 여러 열(“ID”, “LastName”)에 PRIMARY KEY 제약 조건을 정의하려면 다음 SQL 문을 사용합니다.
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
2.2.2.4 FOREIGN KEY 제약
  • FOREIGN KEY 제약 조건은 부모 테이블의 기본키 속성에 포함된 값 중 하나가 외래키 값에 사용되는 것을 보장합니다.
  • 다음 SQL은 “Orders” 테이블이 생성될 떄 “PersonID” 열에 FOREIGN KEY를 생성합니다. (Persons-부모 테이블)
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
2.2.2.4.1 참조되는 테이블의 데이터 삭제 및 수정 시 참조 무결성 제약조건
삭제
  • 사원 테이블의 소속부서 속성은 부서 테이블의 부서번호 속성을 참조하는 외래키다.
  • 부서 테이블에서 홍보부 투플을 삭제하려고 할 때 다음 네 가지 중 한가지 방법으로 처리하도록 선택할 수 있다.
    • ON DELETE NO ACTION : 투플을 삭제하지 못하게 한다.
    • ON DELETE CASCADE : 관련 투플을 함께 삭제한다.
    • ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
    • ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
  • 사원 테이블을 정의하는 CREATE TABLE 문을 작성할 때 이 중 하나를 지정하면 되는데, 별도로 지정하지 않으면 ON DELETE NO ACTION이 기본으로 선택된다.
수정
  • 부서 테이블에서 투플을 수정하려고 할 때도 다음 네 가지 중 한 가지 방법으로 처리하도록 선택할 수 있다.
    • ON UPDATE NO ACTION : 투플을 변경하지 못하도록 한다.
    • ON UPDATE CASCADE : 관련 투플에서 외래키 값을 함께 변경한다.
    • ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
    • ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경한다.
  • 사원 테이블을 정의하는 CREATE TABLE 문을 작성할 때 네 가지 방법 중 하나를 지정하면 되는데, 지정하지 않으면 ON UPDATE NO ACTION이 기본으로 선택된다.

2.2.2.5 CHECK 제약
  • CHECK 제약 조건은 열에 배치할 수 잇는 값 범위를 제한하는 데 사용됩니다.
  • 열에 CHECK 제약 조건을 정의하면 이 열에 대해 특정 값만 허용됩니다.
  • 이떄 다른 열의 값을 기반으로 특정 열의 값을 제한할 수 있습니다.
  • 다음 SQL은 “Persons” 테이블이 생성될 떄 “Age”열에 CHECK 제약 조건을 생성합니다. 제약 조건은 Age가 18세 이상이어야 함을 보장합니다.
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);
  • 제약 조건의 이름(CHK_Person)을 지정하고 여러 열(“Age”, “City”)에 대한 CHECK 제약 조건을 정의하려면 다음 SQL 문을 사용합니다.
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
2.1.2.6 DEFAULT 제약
  • DEFAULT 제약 조건은 열의 기본값을 설정하는 데 사용됩니다.
  • 다른 값을 지정하지 않으면 기본값이 모든 새 레코드에 추가됩니다.
  • 다음 SQL은 “Persons” 테이블이 생성될 때 “City” 열의 DEFAULT 값을 설정합니다.
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);
  • GETDATA() 같은 함수를 사용하여 DEFAULT 제약 조건을 정의할 수 있습니다.
CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

2.3 CREATE INDEX

  • CREATE INDEX문은 테이블에 인덱스를 만드는 데 사용됩니다.
  • 인덱스는 데이터베이스에서 데이터를 빠르게 검색하는 데 사용됩니다. 사용자는 인덱스를 볼 수 없고 검색/쿼리 속도를 높이는 데만 사용됩니다.
  • 테이블에 인덱스를 생성할 때 중복 값을 허용하지 않으려면 UNIQUE와 함께 사용합니다.
  • 다음 SQL문은 “Persons” 테이블의 “LastName” 열에 “idx_lastname”이라는 인덱스를 생성합니다.
CREATE [UNIQUE] INDEX idx_lastname
ON Persons (LastName);
  • 여러 열 조합에 대한 인덱스를 생성하려는 경우 쉼표로 구분하여 괄호 안에 열 이름을 나열합니다.
CREATE [UNIQUE] INDEX idx_pname
ON Persons (LastName, FirstName);

NOTE: 인덱스가 있는 테이블을 업데이트하는 것은 인덱스가 없는 테이블을 업데이트하는 것보다 시간이 더 걸립니다.(인덱스도 업데이트해야 하기 떄문). 따라서 자주 검색되는 열에 대해서만 인덱스를 생성해야합니다.

2.4 CREATE VIEW

  • SQL에서 뷰는 SQL 문의 결과 집합을 기반으로 하는 가상 테이블입니다.
  • 뷰는 실제 테이블처럼 행과 열을 포함합니다. 뷰의 필드는 데이터베이스에 있는 하나 이상의 실제 테이블의 필드입니다.
  • 다음 SQL은 브라질의 모든 고객을 보여주는 “Brazill customers” view를 생성합니다.
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil'
[WITH CHECK OPTION;]
2.4.1 view 란?
  • 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 뷰를 가상 테이블이라고 하는 이유는 일반 테이블과 달리 데이터를 실제로 저장하고 있지 않기 때문이다.
  • 물리적으로 존재하면서 실제로 데이터를 저장하는 일반 테이블과 달리, 뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있어 사용자는 그 차이를 느끼기 어렵다.
  • 일반적으로 뷰는 기본 테이블을 기반으로 만들어지지만 다른 뷰를 기반으로 새로운 뷰를 만들 수도 있다.
  • 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어진다.
  • 뷰에 대한 SELECT 문은 내부적으로 기본 테이블에 대한 SELECT 문으로 변환되어 수행된다.
  • 기본 테이블에서 어떤 투플을 어떻게 변경해야 할지 명확히 제시하지 못하는 뷰는 삽입,수정,삭제 등 변경이 허용되지 않는다.
2.4.2 view 에서 변경이 불가능한 중요한 특징
  • 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
  • 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다.
  • DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.
  • GROUP BY 절을 포함하여 정의한 뷰는 변경할 수 없다.
  • 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.
2.4.3 view의 장점
  • 질의문을 좀 더 쉽게 작성할 수 있다.
  • 데이터의 보안 유지에 도움이 된다.
2.4.4 VIEW 업데이트
  • CREATE OR REPLACE VIEW문으로 view를 업데이트할 수 있습니다.
  • 다음 SQL은 “Brazil Customers” view에 “City” 열을 추가합니다.
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';

📌 출처

W3schools-SQL 튜토리얼-SQL DATABASE

👍 개인 공부 기록용 블로그입니다. 오류나 조언이 있으시면 언제든지 댓글 혹은 메일로 남겨주시면 감사하겠습니다! 😄

맨 위로 이동하기

SQL 카테고리 내 다른 글 보러가기

댓글남기기

최근 글 10 개 :)