SQL - NOT NULL Constraint

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 SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values:

Example

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

Try it Yourself »

Tip: If the table has already been created, you can add a NOT NULL constraint to a column with the ALTER TABLE statement.

SQL - Date Functions

The following table lists the most important built-in date functions in MySQL:

Function	Description
NOW()	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Extracts the date part of a date or date/time expression
EXTRACT()	Returns a single part of a date/time
DATE_ADD()	Adds a specified time interval to a date
DATE_SUB()	Subtracts a specified time interval from a date
DATEDIFF()	Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

SQL - Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Function	Description
AVG()	Returns the average value
COUNT()	Returns the number of rows
FIRST()	Returns the first value
LAST()	Returns the last value
MAX()	Returns the largest value
MIN()	Returns the smallest value
ROUND()	Rounds a numeric field to the number of decimals specified
SUM()	Returns the sum

SQL - INSERT INTO SELECT Examples

Copy only a few columns from "Suppliers" into "Customers":

Example

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

Try it Yourself »

Copy only the German suppliers into "Customers":

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='India';

Try it Yourself »

SQL - INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

SQL - SELECT INTO Examples

Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2017
FROM Customers;

Use the IN clause to copy the table into another database:

SELECT *
INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

Copy only a few columns into the new table:

SELECT CustomerName, ContactName
INTO CustomersBackup2017
FROM Customers;

Copy only the German customers into the new table:

SELECT *
INTO CustomersBackup2017
FROM Customers
WHERE Country='India';

Copy data from more than one table into the new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT *
INTO newtable
FROM table1
WHERE 1=0;