SQLite | Tables | Creating a Table
A table is where actual values are stored in a database. A database can contain multiple tables. This article explains how to create them.
Creating a Table with CREATE TABLE
The basic SQLite syntax for creating a table is as follows.
CREATE TABLE table_name (column_name1, column_name2, ...);
You can choose any table name except one beginning with sqlite_, which is reserved for SQLite itself.
A table must have at least one column. Separate multiple columns with commas.
The following statement creates a table named customer with the columns id and name.
create table customer (id, name);
sqlite>
sqlite> create table customer (id, name);
sqlite>
The table has been created when the command completes as shown.
Creating a Table with Column Data Types
SQLite allows columns to be created either with or without declared data types. See SQLite Data Types for details. Use the following syntax to declare data types.
CREATE TABLE table_name (column_name1 data_type, column_name2 data_type, ...)
Write the data type after each column name, separated by a space.
The following statement creates customer2 with an INTEGER column named id and a TEXT column named name.
create table customer2 (id integer, name text);
sqlite>
sqlite> create table customer2 (id integer, name text);
sqlite>
The table has been created when the command completes as shown.
Listing Tables in a Database
Use the SQLite .tables command to list tables in the database.
.tables
.tables? TABLE?
Without an argument, the command displays all tables. With a table-name pattern, it displays matching tables.
List all tables.
.tables
sqlite> .tables
customer customer2 test test2
sqlite>
The output includes the customer and customer2 tables created on this page.
You can also supply a name or pattern.
.tables customer
.tables customer%
sqlite> .tables customer
customer
sqlite>
sqlite> .tables customer%
customer customer2
sqlite>
The first command uses an exact table name. The second uses a percent sign (%) to match table names containing the specified pattern.