Tongze Wang

BlogProjectAbout

SQL Basics with SQLite

May 12, 2022

Data Types

Available Data Types in SQLite3
Type Description
NULL The value is a NULL value.
INTEGER The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB The value is a blob of data, stored exactly as it was input.

Boolean

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

Date and Time

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Create a table

CREATE TABLE IF NOT EXISTS student (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  gpa REAL CHECK (gpa > 0)
);

AUTOINCREMENT ?

Without the AUTOINCREMENT keyword, the new primary key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table when inserting a new row.

To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword. Then the key chosen will be one more than the largest key that has ever existed in that table.

Constraints

Available Constraints in SQLite3
Type Description
NOT NULL The column may not contain a NULL value.
UNIQUE The value must by unique across the current column.
PRIMARY KEY Similar to UNIQUE, but a table can only have one primary key.
FOREIGN KEY The values in the column points to a PRIMARY KEY in another table.
CHECK It attach a table constraint to column definition.
DEFAULT Provide a default value when no data is provided when inserting rows.

Show All Available Tables

sqlite> .tables
student

Show the Schema of a Table

sqlite> .schema student
CREATE TABLE student (id integer primary key autoincrement, name text);

More on Dot Commands

sqlite> .help
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
...

More information at https://www.sqlite.org/cli.html .

Insert a row

INSERT INTO student VALUES (
  NULL,
  "John Doe",
  3.7
);

Select a row

SELECT * FROM student WHERE gpa > 2.0;

Query Clauses

Query clauses
Clause name Purpose
select Determines which columns to include in the query’s result set
from Identifies the tables from which to retrieve data and how the tables should be joined
where Filters out unwanted data
group by Used to group rows together by common column values
having Filters out unwanted groups
order by Sorts the rows of the final result set by one or more columns

Update a row

UPDATE student SET gpa = 4.0 WHERE id = 1;

Delete a row

DELETE FROM student WHERE id = 1;

Alter a table

ALTER TABLE student ADD gender TEXT;

Drop a table

DROP TABLE student;

JOINS

Inner Join

SELECT <fields> FROM A INNER JOIN B ON A.id = B.id;

Left Join

SELECT <fields> FROM A LEFT JOIN B ON A.id = B.id;

References