Basic SQLite Tutorial

Advanced SQLite Tutorial

SQLite Program Interface

SQLite Data Types

SQLite data types are a property used to specify the data type of any object. Each column, variable, and expression in SQLite has a corresponding data type.

You will use these data types when creating tables. SQLite uses a more general dynamic type system. In SQLite, the data type of a value is associated with the value itself, not with its container.

SQLite storage classes

Each value stored in the SQLite database has one of the following storage classes-

Serial number	Storage class and description
1	`NULL` This value is a NULL value.
2	`INTEGER` This value is a signed integer, stored in1,2,3,4,6or8bytes.
3	`REAL` This value is a floating-point value, stored as8byte IEEE floating-point number.
4	`TEXT` This value is a text string, using the database encoding (UTF-8, UTF-16BE or UTF-16LE) storage
5	`BLOB` This value is a drop of data, stored completely as input.

SQLite storage classes are more general than data types. For example, the INTEGER storage class includes6different integer data types of different lengths.

SQLite affinity types

SQLite supports the concept of type similarity on columns. Any column can still store any type of data, but the preferred storage class of the column is called affinity. SQLite3Each column in the database is assigned one of the following type affinities-

Serial number	Affinity and description
1	`TEXT` This column uses the storage class NULL, TEXT, or BLOB to store all data.
2	`NUMERIC` This column may contain values using all five storage classes.
3	`INTEGER` It behaves the same as a column with NUMERIC affinity, but with the exception of CAST expressions.
4	`REAL` It behaves like a column with NUMERIC affinity, except that it forces integer values to be converted to floating-point representation.
5	`NONE` Columns with affinity of NONE do not prefer one storage class over another and do not attempt to force data from one storage class to another.

SQLite affinity and type names

The following table lists the names of various data types, which can be used in SQLite3when defining a table.

data type	affinity
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB undefined data type	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

boolean data type

SQLite does not have a separate boolean storage class. Instead, boolean values are stored as integers 0(false) and1(true).

date and time data types

SQLite does not have a type for storing dates and/Or store the date and time separately as a storage class, but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Serial number	Storage classes and date formats
1	`TEXT` The date format is 'YYYY'-MM-DD HH:MM:SS.SSS
2	`REAL` B.C.4714Year11Month24Days since noon at Greenwich on the day
3	`INTEGER` Since1970-01-01 Seconds since 00:00:00 UTC

You can choose to store dates and times in these formats and freely convert between them using built-in date and time functions.

SQLite Create Database SQLite Syntax