MySQL database

SQL Database

• SQL CREATE DATABASE

CREATE DATABASE databasename;

• SQL DROP DATABASE

DROP DATABASE databasename;

• SQL BACKUP DATABASE for SQL Server

BACKUP DATABASE databasename TO DISK = ‘filepath’;

• SQL CREATE TABLE

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

-SQL DROP TABLE

DROP TABLE tablename;

TRUNCATE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_name;

• SQL ALTER TABLE

Alter table - add column

ALTER TABLE Customers
ADD Email varchar(255);

Alter table - drop column

ALTER TABLE Customers
DROP COLUMN Email;

• SQL Constraints

• NOT NULL

ALTER TABLE Persons
MODIFY Age int NOT NULL;

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

• UNIQUE

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

• PRIMARY KEY

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

• FOREIGN KEY

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

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

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

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

• CHECK

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

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')
);

• DEFAULT

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

The default value will be added to all new records IF no other value is specified.

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

• INDEX

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

ALTER TABLE table_name
DROP INDEX index_name;

• AUTO INCREMENT

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

let the sequence start with 100

ALTER TABLE Persons AUTO_INCREMENT=100;

• SQL Dates

MySQL comes with the following data types for storing a date or a date/time value in the database:
> 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

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

• SQL VIEW

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";