SQL(데이터베이스 생성, 테이블 생성, 수정, 삭제, 제약조건)

Posted on | 0 comments

w3schools.com 의 ‘SQL’과정을 참고하였습니다.

SQL CREATE DATABASE

  • 관리자가 관리하는 부분
  • 데이터베이스를 만들 때 사용
CREATE DATABASE databasename ;

SQL DROP DATABASE

  • 데이터베이스 삭제는 복구가 어렵기 때문에 신중해야 한다.
  • 삭제 하기 전에, 관리자 권한이 있어야 한다.
DROP DATABASE databasename;

SQL CREATE TABLE

  • 데이터베이스에 새 테이블을 만들 때 사용

-- 예제
CREATE TABLE Persons(
  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),  
);

-- 예제2
-- SELECT문을 활용하여, 다른 테이블의 칼럼들을 새 테이블에 복붙
CREATE TABLE new_table_name AS
  SELECT column1,  column2
  FROM existing_table_name
  WHERE condition;

SQL DROP TABLE

  • 기존 테이블을 삭제 할 때 사용

-- DROP TABLE
 DROP TABLE table_name ;

-- TRUNCATE TABLE
-- 테이블 내부의 데이터는 삭제하고,
-- 테이블 자체는 삭제 하지 않음
TRUNCATE TABLE table_name;

SQL ALTER TABLE

  • 기존 테이블에 열 추가, 삭제, 수정할 때 사용
  • 기존 테이블에 다양한 제한 조건을 추가하는데에도 사용
-- 열추가
ALTER TABLE table_name
ADD column_name datatype;

-- 열삭제
-- 일부 데이터베이스 시스템에서는 역삭제가 혀용이 안될 수도 있음
ALTER TABLE table_name
DROP COLUMN column_name;

-- 열수정
-- SQL SERVER/MS
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

-- ORACLE(10버전 이하)
MODIFY COLUMN column_name datatype;

-- ORACLE(10버전 이상)
MODIFY column_name datatype ;

SQL 제약 조건 만들기

  • CREATE TABLE 혹은 ALTER TABLE로 작성될 때 제한 조건을 지정할 수 있다.
  • 제한 조건과 데이터 사이에 위반이 있으면, 실행이 중단된다.
  • 제한 조건을 지정함으로써, 데이터의 정확성과 신뢰성을 보장 할 수 있다.
  • 기본적인 제약 조건
    • NOT NULL : 열이 NULL 값을 가질 수 없음을 보장
    • UNIQUE : 열의 모든 값이 서로 다른지 확인
    • PRIMARY KEY : NOT NULL과 UNIQUE의 조합. 테이블의 각 행을 고유하게 식별
    • FOREIGN KEY : 다른 테이블의 행 / 레코드를 고유하게 식별
    • CHECK : 열의 모든 값이 특정 조건을 충족하는지 확인
    • DEFAULT : 값이 지정되지 않은 경우 열의 기본값을 설정
    • INDEX : 데이터베이스에서 데이터를 매우 신속하게 생성 및 검색하는 데 사용

SQL NOT NULL 제약 조건

  • 기본적으로 NULL 값을 포함 할 수 있다.
  • NOT NULL제약 조건은 열이 NULL값을 허용하지 않도록 한다.
  • 필드에 값을 빈 값이 들어갈 경우, 삽입 수정이 실행이 되지 않는다.

SQL UNIQUE 제약 조건

  • 모든 열의 값들이 서도 다른지 확인
  • PRIMARY KEY 제약 조건에는 자동으로 UNIQUE제약 조건이 적용
  • 여러개의 UNIQUE 제약 조건을 가질 수 있찌만, PRIMARY KEY는 오직 한개여야 한다.

-- CREATE TABLE 한개의 속성에만 UNIQUE 지정할 때
-- SQL SERVER/ORACLE/MS
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
  );
-- MY SQL
CREATE TABLE Persons (
    ID int NOT NULL,
    UNIQUE (ID)
);

-- 여러개 속성에 UNIQUE 제약 조건 지정할 때
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    CONSTRAINT UC_Person UNIQUE(ID,LastName)
  );

-- ALTER TABLE
-- 한개의 경우
ALTER TABLE Persons
ADD UNIQUE (ID);
-- 여러개
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

-- DROP
-- MY SQL
ALTER TABLE Persons
DROP INDEX UC_Person;
-- SQL SERVER/ORACLE/MS
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

SQL PRIMARY KEY 제약조건

  • 데이터베이스 테이블의 각 레코드를 고유하게 식별
  • 기본키는 UNIQUE값을 포함해야하며, NULL 값은 포함 할 수 없다.
  • 테이블에는 기본키는 하나만 있어야 한다. 기본 키는 하나 또는 여러 개 필드로 구성이 가능 하다.

-- MY SQL
CREATE TABLE Persons (
    ID int NOT NULL,
    PRIMARY KEY (ID)
);
-- SQL SERVER/ORACLE/MS
CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
);

-- 한개이상의 필드를 기본키로 만들 때
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);


-- 이미 만들어진 테이블의 기본키 바꿀 때
-- 이 경우, NULL 값을 포함하지 않도록 이미 선언 되어 있어야 한다 .
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
-- 두개 이상의 필드를 기본키로 할 때
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

-- 기본키 삭제
-- MYSQL
ALTER TABLE Persons
DROP PRIMARY KEY;
-- SQL SERVER/ORACLE/MS
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

SQL FOREGIN KEY 제약

  • 외래키는 두 테이블을 서로 연결할 때 사용하는 키이다.
  • 외래키는 다른 테이블의 PRIMARY KEY를 참조하는 테이블의 필드이다.
  • 외래키가 들어있는 테이블을 하위 테이블이라고하고, 후보키가 들어있는 테이블을 참조 된 테이블 혹은 상위 테이블이라고 한다.
-- MYSQL
CREATE TABLE Orders(
  OrderID int NOT NULL,
  PersonID int,
  PRIMARY KEY(OrderID),
  FOREIGN KEY(PersonID) REFERENCES Persons(PersonId)
);
-- SQL SERVER/ORACLE/MS
CREATE TABLE Orders(
  OrderID int NOT NULL PRIMARY KEY,
  PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
-- 여러열의 FOREIGN KEY  제약 조건을 정의 및 이름 지정
CREATE TABLE Orders(
  OrderID int NOTNULL
  PersonID int,
  PRIMARY KEY (OrderID).
  CONSTRAINT FK_PersonOrder FOREIGN KEY(PersonID)
  REFERENCES Persons(PersonID)
);

--  테이블이 생성 된후, 외래키 제약 조건을 만들 때
ALTER TABLE Orders
ADD FOREGIN KEY (PersonID) REFERENCES Persons(PersonID);
-- 이름 생성 및 여러열의 외래키 만들때
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

SQL CHECK 제약

  • 열에 배치 할 수 있는 값 범위를 제한 할 때 사용
-- MYSQL
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);
-- SQL SERVER/ORACLE/MS
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

-- 이름 지정 및 두개 이상 지정할때
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')
);

-- 기존 테이블 제약 조건 만들때
ALTER TABLE Persons
ADD CHECK (Age>=18);
-- 두개이상
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

SQL DEFAULT

  • 열의 기본값을 제공하는데 사용
  • 다른 값을 지정하지 않을 경우, 기본값이 모든 새 레코드에 추가된다.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

-- 함수를 사용하여, 시스템 값을 삽입하는데도 가능
CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

-- ALTER ,DROP은 SQL 언어마다 조금씩 달라서 홈페이지 참고

SQL CREATE INDEX

  • 데이터베이스에서 데이터를 빠르게 검색하는 데 사용한다.
  • 업데이트가 많은 경우에는 매번 INDEX를 갱신해야해서, 시간이 더 많이 걸릴 수 있다. 이때는 자주 검색할 열에만 INDEX를 사용하자.
-- index  기본 구조
CREATE INDEX index_name
ON table_name ( column1 , column2 , ...);

CREATE UNIQUE INDEX index_name
ON table_name ( column1 , column2 , ...);

-- drop SQL 언어마다 조금씩 다르므로 홈페이지 참고