The SQL CREATE
DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL
database.
Syntax
CREATE DATABASE databasename;
CREATE DATABASE
Example
The following SQL statement creates a database
called "testDB":
CREATE DATABASE testDB;
Tip: Make
sure you have admin privilege before creating any database. Once a database is
created, you can check it in the list of databases with the following SQL
command: SHOW DATABASES;
The SQL DROP DATABASE
Statement
The DROP DATABASE statement is used to drop an existing SQL
database.
Syntax
DROP DATABASE databasename;
Note: Be
careful before dropping a database. Deleting a database will result in loss of
complete information stored in the database!
DROP DATABASE Example
The following SQL statement drops the existing
database "testDB":
DROP DATABASE testDB;
Tip: Make
sure you have admin privilege before dropping any database. Once a database is
dropped, you can check it in the list of databases with the following SQL
command: SHOW DATABASES;
The SQL BACKUP DATABASE
Statement
The BACKUP DATABASE statement is used in SQL Server to create a
full back up of an existing SQL database.
Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath';
The SQL BACKUP WITH
DIFFERENTIAL Statement
A differential back up only backs up the parts of
the database that have changed since the last full database backup.
Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;
BACKUP DATABASE
Example
The following SQL statement creates a full back up
of the existing database "testDB" to the D disk:
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak';
Tip: Always
back up the database to a different drive than the actual database. Then, if
you get a disk crash, you will not lose your backup file along with the database.
BACKUP WITH
DIFFERENTIAL Example
The following SQL statement creates a differential
back up of the database "testDB":
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;
Tip: A
differential back up reduces the back up time (since only the changes are
backed up).
The SQL CREATE TABLE
Statement
The CREATE TABLE statement is used to create a new table in a
database.
Syntax
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.).
SQL CREATE TABLE
Example
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)
);
The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of type
varchar and will hold characters, and the maximum length for these fields is
255 characters.
The SQL DROP TABLE
Statement
The DROP TABLE statement is used to drop an existing table
in a database.
Syntax
DROP TABLE table_name;
Note: Be
careful before dropping a table. Deleting a table will result in loss of
complete information stored in the table!
SQL DROP TABLE
Example
The following SQL statement drops the existing
table "Shippers":
DROP TABLE Shippers;
SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a
table, but not the table itself.
Syntax
TRUNCATE TABLE table_name;
SQL 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.
ALTER TABLE - ADD
Column
To add a column in a table, use the following
syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column
to the "Customers" table:
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP
COLUMN
To delete a column in a table, use the following
syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the "Email"
column from the "Customers" table:
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - RENAME
COLUMN
To rename a column in a table, use the following
syntax:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE -
ALTER/MODIFY DATATYPE
To change the data type of a column in a table, use
the following syntax:
SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Now we want to add a column named
"DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
ADD DateOfBirth date;
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:
ALTER TABLE Persons
ALTER 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:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
SQL constraints are used to specify rules for data in a table.
SQL Create Constraints
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.
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
SQL Constraints
SQL constraints are used to specify rules for the data in a table.
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 SQL:
NOT NULL- Ensures that a column cannot have a NULL valueUNIQUE- Ensures that all values in a column are differentPRIMARY KEY- A combination of aNOT NULLandUNIQUE. Uniquely identifies each row in a tableFOREIGN KEY- Prevents actions that would destroy links between tablesCHECK- Ensures that the values in a column satisfies a specific conditionDEFAULT- Sets a default value for a column if no value is specifiedCREATE INDEX- Used to create and retrieve data from the database very quickly
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.
SQL NOT NULL on
CREATE TABLE
The following SQL 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
);
SQL NOT NULL on ALTER
TABLE
To create a NOT
NULL constraint on the "Age"
column when the "Persons" table is already created, use the following
SQL:
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
SQL UNIQUE Constraint
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.
SQL UNIQUE Constraint
on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when
the "Persons" table is created:
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
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)
);
SQL UNIQUE Constraint
on ALTER TABLE
To create a UNIQUE constraint on the "ID" column when
the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (ID);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
DROP a UNIQUE
Constraint
To drop a UNIQUE constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
SQL PRIMARY KEY
Constraint
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; and in the
table, this primary key can consist of single or multiple columns (fields).
SQL PRIMARY KEY on
CREATE TABLE
The following SQL 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)
);
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
To allow naming of a PRIMARY
KEY constraint, and for defining
a PRIMARY KEY 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 PK_Person PRIMARY KEY (ID,LastName)
);
Note: In
the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the
primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on
ALTER TABLE
To create a PRIMARY
KEY constraint on the "ID"
column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD 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:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If
you use ALTER TABLE to add a primary key, the primary key
column(s) must have been declared to not contain NULL values (when the table
was first created).
DROP a PRIMARY KEY
Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to
prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection
of fields) in one table that refers to the PRIMARY KEY in
another table.
The table with the foreign key is called the child table, and the
table with the primary key is called the referenced or parent table.
Notice 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
a FOREIGN KEY in the "Orders"
table.
The FOREIGN KEY constraint prevents
invalid data from being inserted into the foreign key column, because it has to
be one of the values contained in the parent table.
SQL FOREIGN KEY on
CREATE TABLE
The following SQL creates a FOREIGN
KEY on the "PersonID"
column when the "Orders" table is created:
MySQL:
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:
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:
MySQL / SQL Server / Oracle / MS Access:
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)
);
SQL FOREIGN KEY on
ALTER TABLE
To create a FOREIGN
KEY constraint on the
"PersonID" column when the "Orders" table is already
created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
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:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY
Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
SQL CHECK Constraint
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 column it will allow 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.
SQL CHECK on CREATE
TABLE
The following SQL creates a CHECK constraint on the "Age" column when
the "Persons" table is created. The CHECK constraint ensures that the age of a person
must be 18, or older:
MySQL:
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:
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:
MySQL / SQL Server / Oracle / MS Access:
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')
);
SQL CHECK on ALTER
TABLE
To create a CHECK constraint on the "Age" column when
the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
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:
MySQL / SQL Server / Oracle / MS Access:
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:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL DEFAULT
Constraint
The DEFAULT constraint is used to set a default value for
a column.
The default value will be added to all new records,
if no other value is specified.
SQL DEFAULT on CREATE
TABLE
The following SQL sets a DEFAULT value for the "City" column when
the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
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 GETDATE():
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER
TABLE
To create a DEFAULT constraint on the "City" column
when the table is already created, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server:
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR
City;
MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT
Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL Server:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL CREATE INDEX
Statement
The CREATE INDEX statement is used to create indexes in
tables.
Indexes are used to retrieve data from the database
more quickly than otherwise. The users cannot see the indexes, they are just
used to speed up searches/queries.
Note: Updating
a table with indexes takes more time than updating a table without (because the
indexes also need an update). So, only create indexes on columns that will be
frequently searched against.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are
allowed:
CREATE INDEX index_name
ON table_name (column1, column2,
...);
CREATE UNIQUE INDEX
Syntax
Creates a unique index on a table. Duplicate values
are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,
...);
Note: The
syntax for creating indexes varies among different databases. Therefore: Check
the syntax for creating indexes in your database.
CREATE INDEX Example
The SQL 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);
DROP INDEX Statement
The DROP INDEX statement is used to delete an index in a
table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name
DROP INDEX index_name;
AUTO INCREMENT Field
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.
Syntax for SQL Server
The following SQL statement defines the
"Personid" column to be an auto-increment primary key field in the
"Persons" table:
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new
record.
Tip: To
specify that the "Personid" column should start at value 10 and
increment by 5, change it to IDENTITY(10,5).
To insert a new record into the "Persons"
table, we will NOT have to specify a value for the "Personid" column
(a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');
The SQL statement above would insert a new record
into the "Persons" table. The "Personid" column would be
assigned a unique value. The "FirstName" column would be set to
"Lars" and the "LastName" column would be set to
"Monsen".
SQL Dates
The most difficult part when
working with dates is to be sure that the format of the date you are trying to
insert, matches the format of the date column in the database.
As long as your data contains only the date portion, your queries
will work as expected. However, if a time portion is involved, it gets more
complicated.
SQL Date Data Types
MySQL comes
with the following data types for storing a date or a date/time value in the
database:
DATE- format YYYY-MM-DDDATETIME- format: YYYY-MM-DD HH:MI:SSTIMESTAMP- format: YYYY-MM-DD HH:MI:SSYEAR- format YYYY or YY
SQL Server comes
with the following data types for storing a date or a date/time value in the
database:
DATE- format YYYY-MM-DDDATETIME- format: YYYY-MM-DD HH:MI:SSSMALLDATETIME- format: YYYY-MM-DD HH:MI:SSTIMESTAMP- format: a unique number
Note: The
date types are chosen for a column when you create a new table in your
database!
Now we want to select the records with an OrderDate
of "2008-11-11" from the table above.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
Note: Two dates can easily be compared if
there is no time component involved!
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is
looking only for dates with no time portion.
Tip: To
keep your queries simple and easy to maintain, do not use time-components in
your dates, unless you have to!
SQL CREATE VIEW
Statement
In SQL, a view is a virtual table based on the
result-set of an SQL 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 SQL statements and functions to a view
and present the data as if the data were coming from one single table.
A view is created with the CREATE
VIEW statement.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2,
...
FROM table_name
WHERE condition;
Note: A
view always shows up-to-date data! The database engine recreates the view,
every time a user queries it.
SQL CREATE VIEW
Examples
The following SQL creates a view that shows all
customers from Brazil:
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName,
ContactName
FROM Customers
WHERE Country = 'Brazil';
We can query the view above as follows:
SELECT * FROM [Brazil Customers];
The following SQL creates a view that selects every
product in the "Products" table with a price higher than the average
price:
CREATE VIEW [Products Above Average
Price] AS
SELECT ProductName,
Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
We can query the view above as follows:
SELECT * FROM [Products Above Average Price];
SQL Updating a View
A view can be updated with the CREATE
OR REPLACE VIEW statement.
SQL CREATE OR REPLACE
VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2,
...
FROM table_name
WHERE condition;
The following SQL adds the "City" column
to the "Brazil Customers" view:
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName,
ContactName, City
FROM Customers
WHERE Country = 'Brazil';
SQL Dropping a View
A view is deleted with the DROP
VIEW statement.
SQL DROP VIEW Syntax
DROP VIEW view_name;
The following SQL drops the "Brazil
Customers" view:
DROP VIEW [Brazil Customers];
SQL 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.
Each column in a database table is required to have a name and a
data type.
An SQL developer must decide what type of data that will be stored
inside each column when creating a table. The data type is a guideline for SQL
to understand what type of data is expected inside of each column, and it also
identifies how SQL will interact with the stored data.
Note: Data
types might have different names in different database. And even if the name is
the same, the size and other details may be different! Always
check the documentation!
MySQL Data Types (Version 8.0)
In MySQL there are three main data types: string, numeric, and
date and time.
String Data Types
|
Data type |
Description |
|
CHAR(size) |
A FIXED length string (can contain
letters, numbers, and special characters). The size parameter specifies the
column length in characters - can be from 0 to 255. Default is 1 |
|
VARCHAR(size) |
A VARIABLE length string (can contain
letters, numbers, and special characters). The size parameter specifies the
maximum string length in characters - can be from 0 to 65535 |
|
BINARY(size) |
Equal to CHAR(), but stores binary
byte strings. The size parameter specifies the column length in bytes.
Default is 1 |
|
VARBINARY(size) |
Equal to VARCHAR(), but stores binary
byte strings. The size parameter specifies the maximum column length in
bytes. |
|
TINYBLOB |
For BLOBs (Binary Large Objects). Max
length: 255 bytes |
|
TINYTEXT |
Holds a string with a maximum length
of 255 characters |
|
TEXT(size) |
Holds a string with a maximum length
of 65,535 bytes |
|
BLOB(size) |
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(val1, val2, val3, ...) |
A string object that can have only
one value, chosen from 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. The values are sorted in the order you enter
them |
|
SET(val1, val2, val3, ...) |
A string object that can have 0 or
more values, chosen from a list of possible values. You can list up to 64
values in a SET list |
Numeric Data Types
|
Data type |
Description |
|
BIT(size) |
A bit-value type. The number of bits
per value is specified in size. The size parameter can hold a
value from 1 to 64. The default value for size is 1. |
|
TINYINT(size) |
A very small integer. Signed range is
from -128 to 127. Unsigned range is from 0 to 255.
The size parameter specifies the maximum display width (which is
255) |
|
BOOL |
Zero is considered as false, nonzero
values are considered as true. |
|
BOOLEAN |
Equal to BOOL |
|
SMALLINT(size) |
A small integer. Signed range is from
-32768 to 32767. Unsigned range is from 0 to 65535.
The size parameter specifies the maximum display width (which is
255) |
|
MEDIUMINT(size) |
A medium integer. Signed range is
from -8388608 to 8388607. Unsigned range is from 0 to 16777215.
The size parameter specifies the maximum display width (which is
255) |
|
INT(size) |
A medium integer. Signed range is
from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295.
The size parameter specifies the maximum display width (which is
255) |
|
INTEGER(size) |
Equal to INT(size) |
|
BIGINT(size) |
A large integer. Signed range is from
-9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to
18446744073709551615. The size parameter specifies the maximum
display width (which is 255) |
|
FLOAT(size, d) |
A floating point number. The total
number of digits is specified in size. The number of digits after the
decimal point is specified in the d parameter. This syntax is
deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions |
|
FLOAT(p) |
A floating point number. MySQL uses
the p value to determine whether to use FLOAT or DOUBLE for the
resulting data type. If p is from 0 to 24, the data type becomes
FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() |
|
DOUBLE(size, d) |
A normal-size floating point number.
The total number of digits is specified in size. The number of digits
after the decimal point is specified in the d parameter |
|
DOUBLE PRECISION(size, d) |
|
|
DECIMAL(size, d) |
An exact fixed-point number. The
total number of digits is specified in size. The number of digits after
the decimal point is specified in the d parameter. The maximum
number for size is 65. The maximum number for d is 30.
The default value for size is 10. The default value
for d is 0. |
|
DEC(size, d) |
Equal to DECIMAL(size,d) |
Date and Time Data Types
|
Data type |
Description |
|
DATE |
A date. Format: YYYY-MM-DD. The
supported range is from '1000-01-01' to '9999-12-31' |
|
DATETIME(fsp) |
A date and time combination. Format:
YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition
to get automatic initialization and updating to the current date and time |
|
TIMESTAMP(fsp) |
A timestamp. TIMESTAMP values are
stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00'
UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01
00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and
updating to the current date and time can be specified using DEFAULT
CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition |
|
TIME(fsp) |
A time. Format: hh:mm:ss. The
supported range is from '-838:59:59' to '838:59:59' |
|
YEAR |
A year in four-digit format. Values
allowed in four-digit format: 1901 to 2155, and 0000. |
|
MySQL 8.0 does not support year in
two-digit format. |
SQL Server Data Types
String Data Types
|
Data type |
Description |
Max size |
Storage |
|
char(n) |
Fixed width character string |
8,000 characters |
Defined width |
|
varchar(n) |
Variable width character string |
8,000 characters |
2 bytes + number of chars |
|
varchar(max) |
Variable width character string |
1,073,741,824 characters |
2 bytes + number of chars |
|
text |
Variable width character string |
2GB of text data |
4 bytes + number of chars |
|
nchar |
Fixed width Unicode string |
4,000 characters |
Defined width x 2 |
|
nvarchar |
Variable width Unicode string |
4,000 characters |
|
|
nvarchar(max) |
Variable width Unicode string |
536,870,912 characters |
|
|
ntext |
Variable width Unicode string |
2GB of text data |
|
|
binary(n) |
Fixed width binary string |
8,000 bytes |
|
|
varbinary |
Variable width binary string |
8,000 bytes |
|
|
varbinary(max) |
Variable width binary string |
2GB |
|
|
image |
Variable width binary string |
2GB |
Numeric Data Types
|
Data type |
Description |
Storage |
|
bit |
Integer that can be 0, 1, or NULL |
|
|
tinyint |
Allows whole numbers from 0 to 255 |
1 byte |
|
smallint |
Allows whole numbers between -32,768
and 32,767 |
2 bytes |
|
int |
Allows whole numbers between
-2,147,483,648 and 2,147,483,647 |
4 bytes |
|
bigint |
Allows whole numbers between
-9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
8 bytes |
|
decimal(p,s) |
Fixed precision and scale numbers. |
5-17 bytes |
|
Allows numbers from -10^38 +1 to
10^38 –1. |
||
|
The p parameter indicates the maximum
total number of digits that can be stored (both to the left and to the right
of the decimal point). p must be a value from 1 to 38. Default is 18. |
||
|
The s parameter indicates the maximum
number of digits stored to the right of the decimal point. s must be a value
from 0 to p. Default value is 0 |
||
|
numeric(p,s) |
Fixed precision and scale numbers. |
5-17 bytes |
|
Allows numbers from -10^38 +1 to
10^38 –1. |
||
|
The p parameter indicates the maximum
total number of digits that can be stored (both to the left and to the right
of the decimal point). p must be a value from 1 to 38. Default is 18. |
||
|
The s parameter indicates the maximum
number of digits stored to the right of the decimal point. s must be a value
from 0 to p. Default value is 0 |
||
|
smallmoney |
Monetary data from -214,748.3648 to
214,748.3647 |
4 bytes |
|
money |
Monetary data from
-922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
8 bytes |
|
float(n) |
Floating precision number data from
-1.79E + 308 to 1.79E + 308. |
4 or 8 bytes |
|
The n parameter indicates whether the
field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53)
holds an 8-byte field. Default value of n is 53. |
||
|
real |
Floating precision number data from
-3.40E + 38 to 3.40E + 38 |
4 bytes |
Date and Time Data Types
|
Data type |
Description |
Storage |
|
datetime |
From January 1, 1753 to December 31,
9999 with an accuracy of 3.33 milliseconds |
8 bytes |
|
datetime2 |
From January 1, 0001 to December 31,
9999 with an accuracy of 100 nanoseconds |
6-8 bytes |
|
smalldatetime |
From January 1, 1900 to June 6, 2079
with an accuracy of 1 minute |
4 bytes |
|
date |
Store a date only. From January 1,
0001 to December 31, 9999 |
3 bytes |
|
time |
Store a time only to an accuracy of
100 nanoseconds |
3-5 bytes |
|
datetimeoffset |
The same as datetime2 with the
addition of a time zone offset |
8-10 bytes |
|
timestamp |
Stores a unique number that gets
updated every time a row gets created or modified. The timestamp value is
based upon an internal clock and does not correspond to real time. Each table
may have only one timestamp variable |
0 Comments