Relational Data Base Management Systems: Comparative Syntax

DROP SCHEMA IF EXISTS new_database;
CREATE SCHEMA new_database;
USE new_database;

This is a typical scheme for creating a table in SQLite:

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
	column_1 data_type PRIMARY KEY,
   	column_2 data_type NOT NULL,
	column_3 data_type DEFAULT 0,
	table_constraints
) [WITHOUT ROWID];

Use IF NOT EXISTS option to create a new table if it does not exist. Attempting to create a table that already exists without using the IF NOT EXISTS option will result in an error.

You may specify the schema_name to which the new table belongs. The schema can be the main database, temp database or any attached database.

Each column statement may end in a constraint. SQLite supports PRIMARY KEY, UNIQUE, NOT NULL, and CHECK column constraints.

Last inside the set of round brackets, you specify the table constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints.

The foreign key is specified like this:

FOREIGN KEY (column)
  REFERENCES table (column)

where table refers to a different table, and the expression table (column) references a different table's column.

Finally and after the closing round bracket, you may add the WITHOUT ROWID option.

By default, a row in a table has an implicit column, which is referred to as the rowid, oid or _rowid_ column. The rowid column stores a 64-bit signed integer key that uniquely identifies the row inside the table. If you don't want SQLite to create the rowid column, you specify the WITHOUT ROWID option. A table that contains the rowid column is known as a rowid table. Note that the WITHOUT ROWID option is only available in SQLite 3.8.2 or later.

You may append which engine to use, a character set directive etc.

CREATE TABLE table_name (
  ...
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

Unlike other database systems, SQLite uses a dynamic type system. In other words, the data type of a column is determined by the value stored in it, rather than by the column's declared data type. You don't even have to declare a specific data type for a column when creating a table.

SQLite data types (or, rather, storage classes) are NULL, INTEGER, REAL, TEXT, and BLOB.

Database systems such as MySQL and PostgreSQL use static typing. This means that when you declare a column with a specific data type, that column can store only data of the declared type. For example, if you declare a quantity column with the type integer, you can only store whole numbers in that column.

CREATE TABLE table_name (
  id INTEGER PRIMARY KEY [AUTOINCREMENT], -- an alias for the ROWID
  ...);

(AUTOINCREMENT prevents reuse of ROWID's from previously deleted rows.)

CREATE TABLE table_name (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ...);

INSERT INTO new_table SELECT * FROM old_table;

DROP TABLE old_table;

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
...
COMMIT;
PRAGMA foreign_keys=on;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @old_autocommit=@@autocommit;
SET AUTOCOMMIT=0;
...
UNLOCK TABLES;
COMMIT;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET AUTOCOMMIT=@old_autocommit;

Dump the entire database or tables into a text file:

sqlite> .output ~/Documents/sqlite/db/new_world.db
sqlite> .dump
sqlite> .exit

Dump only a specific table:

sqlite> .output ~/Documents/sqlite/db/Euphoria.sql
sqlite> .dump Euphoria
sqlite> .quit

CREATE [UNIQUE] INDEX index_name
  ON table_name(column_list);

The UNIQUE qualifier enforces uniqueness on the indexed column values (column_list).

Within a table definition:

KEY index_name (column_list);