Database

create database

1
create database database_name;

delete database

1
drop database database_name;

Table

create table

1
2
3
4
5
6
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

1
2
3
4
5
6
7
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
PersonID LastName FirstName Address City

alter table

The alter table statement can allows you to add, alter column and drop column for a table.

  • add

    1
    2
    ALTER TABLE table_name
    ADD column_name datatype;

  • alter column

    1
    2
    ALTER TABLE table_name
    ALTER COLUMN column_name datatype;

  • drop column

    1
    2
    ALTER TABLE table_name
    DROP COLUMN column_name;

Constraints

1
2
3
4
5
6
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

As you see, the constraints are used to specify rules for the columns.

The following constraints are commonly used in SQL:

  • 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

    (However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.)

  • FOREIGN KEY - Prevents actions that would destroy links between tables

  • CHECK - Ensures that the values in a column satisfies a specific condition

  • DEFAULT - Sets a default value for a column if no value is specified

  • CREATE INDEX - Used to create and retrieve data from the database very quickly

UNIQUE

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

If want to add a unique constraint on the table that already exists, use the following syntax:

1
2
3
use Teach;
alter table t
add unique (TNO);

To drop a unique constraint, use the following syntax:

1
2
3
use Teach;
alter table t
drop index TNO;

PRIMARY KEY

The PRIMARY KEY contain UNIQUE values, and also can not contain NULL value.

A table can have only one PRIMARY KEY, but a PRIMARY KEY can consist of multiple columns(field). 

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

To create a PRIMARY KEY constraint on the “ID” column when the table is already created, use the following SQL:

1
2
3
use Teach;
alter table s
add primary key (SNo);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

1
2
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

To drop a PRIMARY KEY constraint, use the following SQL:

1
2
ALTER TABLE Persons
DROP PRIMARY KEY;

FOREIGN KEY

The relationship between the PRIMARY KEY and FOREIGN KEY is that the FOREIGN KEY refers to the PRIMARY KEY, so that the table with foreign key is a child table, and the table with primary key is parent table.

The function of the foreign key prevent invalid data from being inserted into the foreign column.

Just like this:

Persons Table

PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

Orders Table

OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1

MySQL

1
2
3
4
5
6
7
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

1
2
3
4
5
CREATE TABLE Orders (
  OrderID int NOT NULL PRIMARY KEY,
  OrderNumber int NOT NULL,
  PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
8
CREATE TABLE Orders (
  OrderID int NOT NULL,
  OrderNumber int NOT NULL,
  PersonID int,
  PRIMARY KEY (OrderID),
  CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
  REFERENCES Persons(PersonID)
);

To create a FOREIGN KEY constraint on the “PersonID” column when the “Orders” table is already created, use the following SQL:

1
2
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

1
2
3
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To drop a FOREIGN KEY constraint, use the following SQL:

1
2
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Check

The check constraint is used to limit the value range that can be placed in the column.

MySQL

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
CREATE TABLE Persons (
  ID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
8
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')
);

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

1
2
ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

1
2
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

To drop a CHECK constraint, use the following SQL:

1
2
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Views

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

1
2
3
4
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL creates a view that shows all customers from Brazil:

1
2
3
4
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
1
SELECT * FROM [Brazil Customers];

output

CustomerName ContactName
Comércio Mineiro Pedro Afonso
Familia Arquibaldo Aria Cruz
Gourmet Lanchonetes André Fonseca
Hanari Carnes Mario Pontes
Que Delícia Bernardo Batista
Queen Cozinha Lúcia Carvalho
Ricardo Adocicados Janete Limeira
Tradição Hipermercados Anabela Domingues
Wellington Importadora Paula Parente