SQL DATABASE

SQL CREATE DATABASE Statement

CREATE DATABASE databasename;

DROP DATABASE

DROP DATABASE databasename;

SQL BACKUP DATABASE Statement

BACKUP DATABASE databasename
TO DISK = 'filepath';

CREATE TABLE

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

Create Table Using Another Table

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

ALTER TABLE Statement

SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table

add -
ALTER TABLE Customers
ADD Email varchar(255);

SQL Constraints

SQL constraints are used to specify rules for data in a table.

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Used to create and retrieve data from the database very quickl

NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

유니크 row는 여러개일 수 있지만 primary key는 table당 하나만 존재가능

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

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

ALTER 로 PRIMARY KEY 추가 -
  ALTER TABLE Persons
  ADD PRIMARY KEY (ID);

FOREIGN KEY Constraint

공부를 좀 더 해보자

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years:

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

DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column.

City varchar(255) DEFAULT 'Sandnes'

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.