MySQL tutorial

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

The following MySQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

SELECT CustomerName, City FROM Customers;

---

SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

The following MySQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

SELECT DISTINCT Country FROM Customers;

The following MySQL statement lists the number of different (distinct) customer countries:

SELECT COUNT(DISTINCT Country) FROM Customers;

---

WHERE

The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following MySQL statement selects all the customers from the country "Mexico", in the "Customers" table:

SELECT * FROM Customers WHERE Country = 'Mexico';

MySQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes:

SELECT * FROM Customers WHERE CustomerID = 1;

- Operators in the WHERE clause

• = - Equal
• <> - Not equal. Note: In some versions of MySQL this operator may be written as !=
• > - Greater than
• < - Less than
• >= - Greater than or equal
• <= - Less than or equal
• BETWEEN - Between a certain range
• LIKE - Search for a pattern
• IN - To specify multiple possible values for a column

---

AND, OR and NOT

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE;
• The OR operator displays a record if any of the conditions separated by OR is TRUE;

The NOT operator displays a record if the condition(s) is NOT TRUE.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

The following MySQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin';

The following MySQL statement selects all fields from "Customers" where city is "Berlin" OR "München":

SELECT * FROM Customers WHERE City = 'Berlin' OR City = 'München';

The following MySQL statement selects all fields from "Customers" where country is NOT "Germany":

SELECT * FROM Customers WHERE NOT Country = 'Germany';

You can also combine the AND, OR and NOT operators.

The following MySQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München":

SELECT * FROM Customers WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'München');

The following MySQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":

SELECT * FROM Customers WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

---

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The following MySQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

SELECT * FROM Customers ORDER BY Country DESC;

The following MySQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

SELECT * FROM Customers ORDER BY Country, CustomerName;

The following MySQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

---

INSERT INTO

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. The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in the MySQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

The following MySQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

It is also possible to only insert data in specific columns.

The following MySQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

---

NULL Values

A field with a NULL value is a field with no value.

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

The IS NULL operator is used to test for empty values (NULL values). The following MySQL lists all customers with a NULL value in the "Address" field:

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values). The following MySQL lists all customers with a value in the "Address" field:

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;

---

UPDATE

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The following MySQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1;

---

DELETE

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

The following MySQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

---

LIMIT

SELECT * FROM Customers WHERE Country = 'Germany' LIMIT 3;

---

MIN() and MAX()

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

The following MySQL statement finds the price of the cheapest product:

SELECT MIN(Price) AS SmallestPrice FROM Products;

The following MySQL statement finds the price of the most expensive product:

SELECT MAX(Price) AS LargestPrice FROM Products;

---

COUNT(), AVG() and SUM()

The COUNT() function returns the number of rows that matches a specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT SUM(column_name)
FROM table_name
WHERE condition;

The following MySQL statement finds the number of products:

SELECT COUNT(ProductID) FROM Products;

The following MySQL statement finds the average price of all products:

SELECT AVG(Price) FROM Products;

The following MySQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:

SELECT SUM(Quantity) FROM OrderDetails;

---

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters;
• _ - The underscore represents a single character;

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

The following MySQL statement selects all customers with a CustomerName starting with "a":

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

The following MySQL statement selects all customers with a CustomerName that have "or" in any position:

SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

The following MySQL statement selects all customers with a CustomerName that have "r" in the second position:

SELECT * FROM Customers WHERE CustomerName LIKE '_r%';

The following MySQL statement selects all customers with a CustomerName that does NOT start with "a":

SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';

---

IN

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

The following MySQL statement selects all customers that are located in "Germany", "France" and "UK":

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

The following MySQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');

The following MySQL statement selects all customers that are from the same countries as the suppliers:

SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

---

BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The following MySQL statement selects all products with a price BETWEEN 10 and 20:

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

To display the products outside the range of the previous example, use NOT BETWEEN:

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

The following MySQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1, 2, 3);

The following MySQL statement selects all products with a ProductName BETWEEN 'Carnarvon Tigers' and 'Mozzarella di Giovanni':

SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;

The following MySQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':

SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

---

Aliases

MySQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable. An alias only exists for the duration of the query.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

The following MySQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

---

Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

We can create the following MySQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Here are the different types of the JOINs in MySQL:

• (INNER) JOIN: Returns records that have matching values in both tables;
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table;
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table;

---

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

The following MySQL statement selects all orders with customer information:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

The following MySQL statement selects all orders with customer and shipper information:

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

---

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

The following MySQL statement will select all customers, and any orders they might have:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

---

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

The following MySQL statement will return all employees, and any orders they might have placed:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

---

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns;
• The columns must also have similar data types;
• The columns in each SELECT statement must also be in the same order;

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

The following MySQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

The following MySQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

---

GROUP BY

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

The following MySQL statement lists the number of customers in each country:

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

---

HAVING

The HAVING clause was added to MySQL because the WHERE keyword could not be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The following MySQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

---

EXISTS

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

The following MySQL statement returns TRUE and lists the suppliers with a product price less than 20:

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);

---

ANY and ALL

The ANY and ALL operators are used with a WHERE or HAVING clause.

• The ANY operator returns true if any of the subquery values meet the condition;
• The ALL operator returns true if all of the subquery values meet the condition;

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

The following MySQL statement returns TRUE and lists the productnames if it finds ANY records in the OrderDetails table that quantity = 10:

SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

The following MySQL statement returns TRUE and lists the productnames if ALL the records in the OrderDetails table has quantity = 10:

SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

---

INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

• INSERT INTO SELECT requires that data types in source and target tables match;
• The existing records in the target table are unaffected;

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

The following MySQL statement copies "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

---

CASE

The CASE statement goes through conditions and return a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

The following MySQL goes through conditions and returns a value when the first condition is met:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

The following MySQL will order the customers by City. However, if City is NULL, then order by Country:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

---

IFNULL(), COALESCE()

Look at the following SELECT statement:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder) FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.

IFNULL() function lets you return an alternative value if an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

or we can use the COALESCE() function, like this:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;

---

Stored procedures

A stored procedure is a prepared MySQL code that you can save, so the code can be reused over and over again. So if you have an MySQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
	sql_statement
END //

Create:

DELIMITER //
CREATE PROCEDURE SelectAllCountries()
BEGIN
	SELECT * FROM countries;
END //

Call:

CALL SelectAllCountries();

Drop:

DROP PROCEDURE IF EXISTS SelectAllCountries;

---

Comments

Single line comments:

#Select all:
SELECT * FROM countries; -- this is a comment

Multi-line comments:

/*
Select
all
*/
SELECT * FROM countries;

---

CREATE DATABASE

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

CREATE DATABASE databasename;

The following MySQL statement creates a database called "testDB":

CREATE DATABASE testDB;

---

DROP DATABASE

The DROP DATABASE statement is used to drop an existing MySQL database.

DROP DATABASE databasename;

The following MySQL statement drops the existing database "testDB":

DROP DATABASE testDB;

---

CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database.

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

The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:

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

Create table using another table:

A copy of an existing table can also be created using CREATE TABLE. The new table gets the same column definitions. All columns or specific columns can be selected. If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

The following MySQL creates a new table called "TestTables" (which is a copy of the "Customers" table):

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

---

DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE table_name;

The following MySQL statement drops the existing table "Shippers":

DROP TABLE Shippers;

---

TRUNCATE TABLE

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

TRUNCATE TABLE table_name;

---

ALTER TABLE

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.

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following MySQL adds an "Email" column to the "Customers" table:

ALTER TABLE Customers
ADD Email varchar(255);

To delete a column in a table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

The following MySQL deletes the "Email" column from the "Customers" table:

ALTER TABLE Customers
DROP COLUMN Email;

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

---

Constraints

MySQL constraints are used to specify rules for data in a table. Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

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

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in MySQL:

• 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;

- NOT NULL

By default, a column can hold NULL values. 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.

The following MySQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:

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

To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following MySQL:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

- UNIQUE

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

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

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

To create a UNIQUE constraint on the "ID" column when the table is already created, use the following MySQL:

ALTER TABLE Persons
ADD UNIQUE (ID);

- PRIMARY KEY

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, which may consist of single or multiple fields.

The following MySQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:

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

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following MySQL:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

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

ALTER TABLE Persons
DROP PRIMARY KEY;

- 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.

The following MySQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

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

- 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.

The following MySQL 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

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.

The following MySQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

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

To create a DEFAULT constraint on the "City" column when the table is already created, use the following MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

To drop a DEFAULT constraint, use the following MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

---

INDEX

The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX creates an index on a table. Duplicate values are allowed:

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

CREATE UNIQUE INDEX creates a unique index on a table. Duplicate values are not allowed:

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

The MySQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:

CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

The DROP INDEX statement is used to delete an index in a table.

ALTER TABLE table_name
DROP INDEX index_name;

---

AUTO INCREMENT

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.

The following MySQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:

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

MyMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following MySQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');

The MySQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

---

Views

In MySQL, a view is a virtual table based on the result-set of an MySQL statement. 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. You can add MySQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

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

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

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

We can query the view above as follows:

SELECT * FROM BrazilCustomers;

A view can be updated with the CREATE OR REPLACE VIEW command.

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

The following MySQL adds the "City" column to the "Brazil Customers" view:

CREATE OR REPLACE VIEW BrazilCustomers AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";

A view is deleted with the DROP VIEW command.

DROP VIEW view_name;

The following MySQL drops the "Brazil Customers" view:

DROP VIEW BrazilCustomers;

---

Data types

The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

In MySQL there are three main data types: text, number, and date.

Text data types:

• CHAR(size) - Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters;
• VARCHAR(size) - Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type;
• TINYTEXT - Holds a string with a maximum length of 255 characters;
• TEXT - Holds a string with a maximum length of 65,535 characters;
• BLOB - For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data;
• MEDIUMTEXT - Holds a string with a maximum length of 16,777,215 characters;
• MEDIUMBLOB - For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data;
• LONGTEXT - Holds a string with a maximum length of 4,294,967,295 characters;
• LONGBLOB - For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data;
• ENUM(x, y, z, etc.) - Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them;
• SET - Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice;

Number data types:

• TINYINT(size) - 128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis;
• SMALLINT(size) - 32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis;
• MEDIUMINT(size) - 8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis;
• INT(size) - 2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis;
• BIGINT(size) - 9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis;
• FLOAT(size, d) - A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter;
• DOUBLE(size, d) - A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter;
• DECIMAL(size, d) - A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter;

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date data types:

• DATE();
• DATETIME();
• TIMESTAMP();
• TIME();
• YEAR();