[SQL] SQL 데이타베이스 정리

# CREATE DB

CREATE DATABASE databasename;

 

# DROP DB

DROP DATABASE databasename;

 

 

 

# BACKUP DB

 

- 존재하는 DB에 full back up을 create

BACKUP DATABASE databasename
TO DISK = 'filepath';

- DIFFERENTIAL 

BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

 

# CREATE TABLE

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

 

# DROP TABLE

DROP TABLE table_name;

 

# ALTER TABLE

- 존재하는 table의 column을 add, delete, modify

ALTER TABLE table_name
ADD column_name datatype;

 

# Constraints

- table내 data 대한 Rule 지정

- CREATE TABLE 구문과 함께 지정 or ALTER TABLE과.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

 

# NOT NULL

- NULL 값을 accept 할 수 없는 column들을 enforce

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

 

# UNIQUE

- column 내 모든 값들이 다름.

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

 

# PRIMARY KEY

- 테이블 내 각 record를 uniquely identify

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

 

# FOREIGN KEY

- table내 link를 destroy하는 action을 막음

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

 

# CHECK

- column내 배치될 수 있는 값 범위를 제한

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

 

# DEFAULT

- column에 대한 default 값을 설정

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

 

 

# CREATE INDEX

- table내 index들을 만드는데 사용

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

 

# AUTO INCREMENT

- 새로운 record가 테이블에 삽입될때 unique number가 자동적으로 생성

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

 

# SQL Dates

- MySQL

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• YEAR - format YYYY or YY

- SQL Server

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: a unique number

 

# CREATE VIEW

- view : SQL 구문의 result-set 기반으로 한 virtual table

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;