MySQL CREATE DATABASE Statement

 The CREATE DATABASE statement is used to create a new SQL database.

Syntax : CREATE DATABASE database_name;
Example : CREATE DATABASE testDB;
 The above SQL statement creates a database called "testDB":
Show Databases
 Once a database is created, you can check it in the list of databases with the following SQL command:
Syntax : SHOW DATABASES;
The MySQL CREATE TABLE Statement
 The CREATE TABLE statement is used to create a new table in a database.
Syntax :
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
.........
);
MySQL CREATE TABLE Example
 The following example creates a table named "Persons" with five columns:
CREATE TABLE Persons (
PersonID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
The MySQL INSERT INTO Statement
 The INSERT INTO statement is used to insert new records in a table.
 It is possible to write the INSERT INTO statement in two ways:
Syntax 1
 Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 Syntax 2
 If you insert values for ALL the columns of the table, you can omit the column names.
 However, the order of the values must be in the same order as the columns in the table:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Demo Database
 Below is a selection from the "Customers" table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country

White Clover 305 - 14th Ave.
89 Karl Jablonski Seattle 98128 USA
Markets S. Suite 3B
Matti
90 Wilman Kala Keskuskatu 45 Helsinki 21240 Finland
Karttunen

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

INSERT INTO Example

 Here we insert values for ALL the columns of the table, so we omit the column names.
 The following SQL inserts a new record in the "Customers" table:
Example :
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
 Insert Data Only in Specific Columns
 Here we insert values only in some specific columns of the table.
 The following SQL inserts a new record - but only inserts data in the "CustomerName", "City", and
"Country" columns (CustomerID will be updated automatically):
Example :
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
Insert Multiple Rows
 To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:
 The following SQL inserts three new records in the "Customers" table:
Example :
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
 Note: Make sure you separate each set of values with a comma ,.
The MySQL SELECT Statement
 The SELECT statement is used to select data from a database.
 The data returned is stored in a result table, called the result-set.
 The following SQL selects the "CustomerName", "City", and "Country" columns from the "Customers"
table:
Example : SELECT CustomerName, City, Country FROM Customers;
SELECT Syntax
SELECT column1, column2, ………. FROM table_name;
 Here, column1, column2, ... are the column names in the table you want to select data from.
 The table_name represents the name of the table you want to select data from.
 To select all columns, without specifying every column name, use the following syntax:
SELECT * FROM table_name;
SELECT ALL Columns
 To select ALL columns, without specifying every column name, use the SELECT * syntax:
 The following SQL selects ALL the columns from the "Customers" table:
Example : SELECT * FROM Customers;

Demo Database
 Below is a selection from the "Customers" table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country

1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Avda. de la
2 Ana Trujillo México D.F. 05021 Mexico
Emparedados y helados Constitución 2222
Antonio Moreno
3 Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

The MySQL UPDATE Statement

 The UPDATE statement is used to update or modify one or more records in a table.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
 Example :
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Multiple Records
 The WHERE clause determines which records that will be updated.
 The following SQL will update the PostalCode to 00000 for ALL records where country is "Mexico":
Example :
UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';
 Update Warning! Be careful when updating records. If you omit the WHERE clause, ALL records will be
updated!

The MySQL DELETE Statement

 The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
 Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The
WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the
table will be deleted!
SQL DELETE Example
 The following SQL deletes the customer "Alfreds Futterkiste" from the "Customers" table:
Example : DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
Delete All Records
 It is possible to delete all records in a table, without deleting the table. This means that the table structure,
attributes, and indexes will be intact.
DELETE FROM table_name;
 The following SQL deletes ALL records in the "Customers" table, without deleting the table:
Example : DELETE FROM Customers;
Delete a Table
 To delete the table completely, use the DROP TABLE statement:
Syntax : DROP TABLE table_name;
 The following SQL drops the entire "Customers" table:
 Example : DROP TABLE Customers;
CONSTRAINTS
MySQL NOT NULL Constraint
 The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always
contain a value, which means that you cannot insert a new record, or update a record without adding a
value to this field.
 By default, a column can hold NULL values.

NOT NULL on CREATE TABLE

 To define a NOT NULL constraint when creating a table, add NOT NULL after the data type of the column
name.
 The following SQL creates a "Persons" table, and ensures that the "ID", "LastName", and "FirstName"
columns cannot accept NULL values:
Example :
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

NOT NULL on ALTER TABLE

 To define a NOT NULL constraint on an existing table, use ALTER TABLE and add NOT NULL after the data
type of the column name.
 The following SQL adds a NOT NULL constraint on the "Age" column, after the "Persons" table is already
created:
ALTER TABLE Persons
MODIFY Age int NOT NULL;

Remove a NOT NULL Constraint

 To remove a NOT NULL constraint from a column (to let the column accept NULL values again), use the
following syntax:
ALTER TABLE Persons
MODIFY Age int NULL;
MySQL UNIQUE Constraint
 The UNIQUE constraint ensures that all values in a column are unique.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of
columns. However, you can have many UNIQUE constraints per table, but only one PRIMARY
KEY constraint per table.

UNIQUE Constraint on CREATE TABLE

 The following SQL defines a UNIQUE constraint for the "ID" column upon creation of the "Persons" table:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
Naming a Unique Constraint
 To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following
SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

UNIQUE Constraint on ALTER TABLE

 To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL
syntax:
ALTER TABLE Persons
ADD UNIQUE (ID);
Naming a Unique Constraint
 To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following
SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
Drop a UNIQUE Constraint
 To drop a UNIQUE constraint, use the following SQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
MySQL PRIMARY KEY Constraint
 The PRIMARY KEY constraint uniquely identifies each record in a database table.
 A PRIMARY KEY constraint ensures unique values, and cannot contain NULL values (it is a combination of
both a UNIQUE constraint and a NOT NULL constraint).
 A table can have only ONE PRIMARY KEY constraint. The primary key can either be a single column, or a
combination of columns.
 Tip: The primary key is the target for FOREIGN KEY constraints in other tables (which enforces referential
integrity between data in two tables).

PRIMARY KEY on CREATE TABLE

 The following SQL creates a PRIMARY KEY on the "ID" column upon creation of the "Persons" table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
PRIMARY KEY on Multiple Columns
 To define an un-named PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int,
LastName varchar(255),
FirstName varchar(255),
Age int,
PRIMARY KEY (ID, LastName)
);
 Note: In the example above, the PRIMARY KEY value is made up of two columns (ID + LastName).
 To define a named PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int,
LastName varchar(255),
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
);
 Note: In the example above, the PRIMARY KEY is named "PK_Person", and the value is made up of two
columns (ID + LastName).
 PRIMARY KEY on ALTER TABLE
 To create a PRIMARY KEY constraint on the "ID" column when the table already has been created, use the
following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
PRIMARY KEY on Multiple Columns
 To define a named PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);
 Note: When using ALTER TABLE to add a primary key, the primary key column(s) must have been declared
with NOT NULL upon creation of the table.

Drop a PRIMARY KEY Constraint

 To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
MySQL CHECK Constraint
 The CHECK constraint is used to ensure that the values in a column satisfy a specific condition.
 The CHECK constraint evaluates the data to TRUE or FALSE. If the data evaluates to TRUE, the operation is
ok. If the data evaluates to FALSE, the entire INSERT or UPDATE operation is aborted, and an error is
raised.

CHECK Constraint on CREATE TABLE

 The following SQL creates a CHECK constraint on the "Age" column upon creation of the "Persons" table.
 Here, the CHECK constraint ensures that the "Age" column must have a value of 18, or above:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age >= 18)
);
Naming a CHECK Constraint
 To name a CHECK constraint, or to define a CHECK constraint on multiple columns, use the following SQL
syntax:
 CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT chk_PersonAge CHECK (Age >= 18 AND City = 'Sandnes')
);

CHECK Constraint on ALTER TABLE

 To create a CHECK constraint on the "Age" column when the table is already created, use the following
SQL:
ALTER TABLE Persons
ADD CHECK (Age >= 18);
Naming a CHECK Constraint
 To name a CHECK constraint, and to define a CHECK constraint on multiple columns, use the following SQL
syntax:
ALTER TABLE Persons
ADD CONSTRAINT chk_PersonAge CHECK (Age >= 18 AND City = 'Sandnes');

Drop a CHECK Constraint

 To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CHECK chk_PersonAge;
MySQL DEFAULT Constraint
 The DEFAULT constraint is used to automatically insert a default value for a column, if no value is specified.
 The default value will be added to all new records (if no other value is specified).
 DEFAULT Constraint on CREATE TABLE
 The following SQL sets a DEFAULT value for the "City" column upon creation of the "Persons" table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
 City varchar(255) DEFAULT 'Sandnes'
);
 The DEFAULT constraint can also be used to insert system values, by using functions
like CURRENT_DATE() to insert the current date:
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);

DEFAULT Constraint on ALTER TABLE

 To define a DEFAULT constraint on the "City" column when the table is already created, use the following
SQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

DROP a DEFAULT Constraint

 To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
MySQL FOREIGN KEY Constraint
 The FOREIGN KEY constraint establishes a link between two tables, and prevents action that will destroy
the link between them.
 A FOREIGN KEY is a column in a table that refers to the PRIMARY KEY in another table.
 The table with the foreign key column is called the child table, and the table with the primary key column
is called the referenced or parent table.
 The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column (in the
child table), because the value has to exist in the parent table.
 The FOREIGN KEY constraint also prevents you from deleting a record in the parent table, if related rows
still exist in the child table.
 Assume we have two tables:
Persons Table

 PersonID  LastName  FirstName  Age

 1  Hansen  Ola  30
 2  Svendson  Tove  23
 Orders Table

 OrderID  OrderNumber  PersonID

 3  22456  2
 4  24562  1
 Here we see that the "PersonID" column in the "Orders" table points to the "PersonID" column in the
"Persons" table.
 The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
 The "PersonID" column in the "Orders" table is the FOREIGN KEY in the "Orders" table.

FOREIGN KEY on CREATE TABLE

 The following SQL creates a FOREIGN KEY constraint on the "PersonID" column upon creation of the
"Orders" table:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int,
CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

FOREIGN KEY on ALTER TABLE

 To create a FOREIGN KEY constraint on the "PersonID" column after the "Orders" table is created, use the
following SQL:
ALTER TABLE Orders
ADD CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);

Drop a FOREIGN KEY Constraint

 To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_Person;
The MySQL DROP TABLE Statement

 The DROP TABLE statement is used to permanently delete an existing table in a database.
 Note: Be careful before dropping a table! Dropping a table deletes the entire table and all its content!
Syntax : DROP TABLE table_name;
 To prevent an error from occur (if the table does not exists), it is a good practice to add the IF
EXISTS clause:
DROP TABLE IF EXISTS table_name;
 Note: In most databases you cannot drop a table that is referenced by a foreign key constraint in another
table. To solve this, you must remove the foreign key constraint or drop the dependent table.

MySQL DROP TABLE Example

 The following SQL statement drops the "Shippers" table:
Example : DROP TABLE IF EXISTS Shippers;

MySQL TRUNCATE TABLE

 The TRUNCATE TABLE statement is used to delete all the records in a table, but it keeps the table
structure, columns and constraints.
Syntax : TRUNCATE TABLE table_name;
MySQL 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.
 Common ALTER TABLE operations are:
 Add column - Adds a new column to a table
 Drop column - Deletes a column in a table
 Rename column - Renames a column
 Modify column - Changes the data type, size, or constraints of a column
 Add constraint - Adds a new constraint
 Rename table - Renames a table

ALTER TABLE - ADD Column

 To add a column in a table, use the following syntax:
Syntax
ALTER TABLE table_name
ADD column_name datatype;
 The following SQL adds an "Email" column to the "Customers" table:
Example :
ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE - DROP COLUMN

 To delete a column in a table, use the following syntax:
Syntax :
ALTER TABLE table_name
DROP COLUMN column_name;
 The following SQL deletes the "Email" column from the "Customers" table:
Example :
ALTER TABLE Customers
DROP COLUMN Email;

ALTER TABLE - RENAME COLUMN

 To rename a column in a table, use the following syntax:
Syntax
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

ALTER TABLE - MODIFY Datatype

 To modify the data type, size or constraints of a column in a table, use the following syntax:
Syntax :
ALTER TABLE table_name
MODIFY column_name new_datatype constraint;
 The following SQL modifies the size of the "Email" column to varchar(100), and we also add a NOT
NULL constraint:
Example :
ALTER TABLE Customers
MODIFY Email varchar(100) NOT NULL;
 ALTER TABLE - ADD CONSTRAINT
 To add a constraint to an existing table, use the following syntax:
Syntax :
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
 The following SQL adds a constraint named "CHK_Age" that is a CHECK constraint that ensures that the
"Age" column has a value of 18 and above:
Example :
ALTER TABLE Members
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

ALTER TABLE - Rename table

 To rename a table, use the following syntax:
Syntax :
ALTER TABLE table_name
RENAME TO new_table_name;
 The following SQL renames the "Customers" table to "Clients":
Example :
ALTER TABLE Customers
RENAME TO Clients;

MySQL ALTER TABLE Example

 Assume we have a "Persons" table, that looks like this:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
 Now we want to add a column named "DateOfBirth" in the "Persons" table.
 We use the following SQL statement:
Example :
ALTER TABLE Persons
ADD DateOfBirth date;
 Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type
specifies what type of data the column can hold. For a complete reference of all the data types available in
MySQL, go to our Data Types reference.
 The "Persons" table will now look like this:

ID LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example

 Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
 We use the following SQL statement:
Example :
ALTER TABLE Persons
MODIFY COLUMN DateOfBirth year;
 Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit
format.

DROP COLUMN Example

 Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
 We use the following SQL statement:
Example :
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
 The "Persons" table will now look like this:
ID LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger