Sqlite uses dynamic types. Any value in any field
Can use a single db connection to access multiple files
Databases can be in memory
.schema - show database / table schema
.databases - show attached databases
.dump - output database (or objects) contents as sql
.exit - exits sqlite (or .quit or .q)
.import - import data into a table
.mode - changed the output mode
.output - send output to a file. If not specified, goes to stdout
.tables - list tables
.schema <tablename> show table schema
.q or .quit - same as exit
PRAGMA foreign_keys = ON; // defaults to off for backwards compatibility
PRAGMA journal_mode = WALL; // speeds up writes and makes things atomic
PRAGMA synchronous = NORMAL; // balance of durability and performance
PRAGMA synchronous = FULL; // commit all changes to disk before continuing. Safer but slower.
.dumpCopy the schema and data to a text file that you can use to recreate the database
.output mydb.sql -- if you don't do this, .dump will just go to stdout
.dump
.dump tablename -- dump a specific table
.importImport data into a sqlite table
If the table doesn't exist, it is created, with the column names coming from the first line in the CSV
.import --csv city.csv citie
If want to import the csv into an existing table, and the first row of the csv is the column names, you want to skip it.
.import --csv --skip 1 city.csv othercities
There is no built in export, but you can export to csv with the following:
.headers on
.mode csv
.output data.csv
SELECT id, name, company FROM customers;
or
sqlite3 -header -csv database.db "select * from customers;" > cust.csv
or, if you have a file that contains the sql query
sqlite3 -header -csv database.db < query.sql > cust.csv
### `.schema`
Similar to `.dump`, but only the database schema, not data.
```sql
.output mydbschema.sql
.schema
.scema tablename
.schema [tablename] - show schema of table, or all tables if no table name used.
.dump does data and schema
.schema just does schema
To get just the data:
.mode insert -- any output is now the sql command to insert that data, not just the data
.output data.sql -- set output to a file rather than stdout
select * from artists;
.modeSets the output mode of sqlite commands
There are a bunch. .help mode to get the list
To name a few, csv, html, json, insert, list (default)
The default of .mode list
sqlite> select name from artists where artistid between 5 and 8;
Alice In Chains
Antônio Carlos Jobim
Apocalyptica
Audioslave
sqlite> .mode insert
sqlite> select name from artists where artistid between 5 and 8;
INSERT INTO "table" VALUES('Alice In Chains');
INSERT INTO "table" VALUES('Antônio Carlos Jobim');
INSERT INTO "table" VALUES('Apocalyptica');
INSERT INTO "table" VALUES('Audioslave');
sqlite> .mode json
sqlite> select name from artists where artistid between 5 and 8;
[{"Name":"Alice In Chains"},
{"Name":"Antônio Carlos Jobim"},
{"Name":"Apocalyptica"},
{"Name":"Audioslave"}]
.tablesLists tables.
Can use wildcards similar to LIKE
.tables 'a%'
Get table names via an SQL statement
SELECT name
FROM sqlite_schema
WHERE type = 'table' AND
name NOT LIKE 'sqlite_%';
Alternative way of listing schema
.header on
.mode column
pragma table_info('contacts');
-- or
SELECT SQL FROM sqlite_schema
WHERE name = 'contacts';
If you want sqlite to return the number of changed records in a command:
(for some reason, this is deprecated)
PRAGMA count_changes=true;
Unlike most databases, sqlite's data type is determined by the data in the field, not the type declared on the field.
Data types (called storage classed in sqlite) are more of an affinity to a data type that an restriction.
If you define a field as integer, and try to store some text that can be converted to an integer, it will do that conversion automatically.
If it can't be converted, it is just stored as text.
A single field is not restricted to a data type. The type is determined on each field and each record based on that records content.
| Storage Class | Meaning |
|---|---|
| NULL | missing or unknown |
| INTEGER | Integer value stored in variable sizes, from 1 - 8 bytes |
| REAL | Real values that use 8 byte floats |
| TEXT | text data. No practical limit |
| BLOB | binary data again with no practical limit |
Dates can be stored in either text, integer or real
Sqlite uses the following rules for sorting with mixed data types per field
TEXT field to store a date time, it needs to be in the format
YYYY-MM-DD HH:MM:SS.SSS
SELECT datetime('now'); -- utc
SELECT datetime('now','localtime'). -- local time
REAL storage class to store date time as Julian day numbers. The number of days since November 24, 4714 BC
INSERT INTO datetime_real (d1) VALUES (julianday('now'));
SELECT d1 FROM datetime_real; -- value isn't human readable. ie. 24573904.3125151
SELECT date(d1) time(d1) FROM datetime_real; -- get human readable info
INTEGER to store date time.
INSERT INTO datetime_int (d1) VALUES(strftime('%s','now'));
SELECT datetime(d1,'unixepoch') FROM datetime_int
JSON is just stored as a TEXT storage class and there are json functions to use that data.
json_extract(json, path)
json_insert(json, path, value)
json_replace(json, path, value)
json_remove(json, path)
json_group_array( ) - aggregate values into a json array
json_group_object( ) - aggregate values into a json object
When you connect to a database, it's name is main regardless of the file name.
You can access the database that contains temporary tables via the temp database
To attach to additional databases use:
ATTACH DATABASE file_name AS database_name;
ATTACH DATABASE :memory: AS fastdatabase_name;
ATTACH DATABASE '' AS temporary_database_name;
If the file_name doesn't exist, it is created.
Once attached, you can refer to all objects in it under the database_name using dot notation
Syntax
SELECT [DISTINCT] columns list
FROM table_list
JOIN table ON join_condition
WHERE row_filter
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
HAVING group_filter;
DISTINCT - only get unique values from a column
FROM - table name to get data from
JOIN - join to another table with a relationship
WHERE - filter values retrieved
ORDER BY - sort output
LIMIT count OFFSET offset - limit number of records retrieved
GROUP BY - group row and apply aggregate functions
HAVING - used to filter when using GROUP BY
Some SELECT statements with growing complexity
Select all data from table1
SELECT * from table1;
Select columns 1 & 2 from table1
SELECT column1, column2 FROM table1
WHERE albumid = 1
WHERE
name = 'testes'
AND size > 50
WHERE name LIKE '%cocko%'
WHERE id IN(2,3,4)
WHERE name IN ('craigus','cuntos')
-- sort in ascending by column1
SELECT column1, column2 FROM table1
ORDER BY column1 Asc;
-- sort order can be done by number. This represents the 2nd column defined in the SELECT clause
SELECT column1, column2 FROM table1
ORDER BY 2;
-- NULL is considered smaller than any other value.
-- you can change where NULLs appear in a sorted order with NULLS FIRST/LAST
SELECT column1, column2 FROM table1
ORDER BY email NULLS LAST;
Filter data
SELECT column1, column2 FROM table1
WHERE column1 = 4
WHERE column2 IS NULL or to search for empty fields.
WHERE column2 NOT NULL or only fields with data
Can be applied at Table or Column level
Usually done with CREATE TABLE, but can be done with ALTER TABLE
NOT NULL
UNIQUE
PRIMARY KEY - combination of NOT NULL and UNIQUE
FOREIGN KEY
CHECK all values in a column satisfies a specific condition
DEFAULT
CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
phone TEXT NOT NULL
CHECK (length(phone) >= 10)
);
Are used to give a table or column a temporary name that only lasts for the query. Used to make object names more readable for that query.
In column syntax, define it as part of the SELECT statement
In table syntax, define it as part of the FROM statement
the AS keyword is optional. Just separating names by a space works
SELECT long_column_stupid_name AS cuntos FROM table1;
SELECT column1 FROM long_stupid_table_name AS cuntos;
Table Alias example
SELECT c.FirstName, c.LastName, c.City
FROM customers c
WHERE c.City LIKE 'a%';
Table name cannot start with the name sqlite
Although sqlite is generally typeless, with the primary key, it matters.
Create the primary key with INTEGER, not INT. They are not the same.
Basic create table
CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email text NOT NULL UNIQUE,
phone text
);
Create a table with a relationship
CREATE TABLE contact_groups (
contact_id INTEGER,
group_id INTEGER,
PRIMARY KEY (contact_id, group_id),
FOREIGN KEY (contact_id) REFERENCES contacts (contact_id)
ON DELETE CASCADE ON UPDATE NO ACTION,
FOREIGN KEY (group_id) REFERENCES groups (group_id)
ON DELETE CASCADE ON UPDATE NO ACTION
);
Create a temporary table
CREATE TEMPORARY TABLE table_name ( name TEXT );
Insert new records into a table
INSERT INTO table1 (
column1,
column2 )
VALUES
(
value1,
value2
);
-- multiple records
INSERT INTO table1 ( column1, column2 )
VALUES ( value1, value2 ),
VALUES ( value1, value2 ),
VALUES ( value1, value2 );
-- if the table was created with default values for certain fields
INSERT INTO artists DEFAULT VALUES;
-- copy one table to another
CREATE TABLE artists_backup(
ArtistId INTEGER PRIMARY KEY AUTOINCREMENT,
Name NVARCHAR
);
INSERT INTO artists_backup
SELECT ArtistId, Name
FROM artists;
update an existing record
If you don't set a WHERE condition, all records are updated.
UPDATE table1
SET column1 = value1,
column2 = value2
WHERE condition
ORDER column_or_expression
LIMIT row_count OFFSET offset;
Always use ORDER BY with the LIMIT clause, as without order, the order of rows is unspecified.
Allows you to update data in one table based on data in another.
often called an UPDATE JOIN because it involves 2 tables.
UPDATE target_table
SET
col1 = val1,
col2 = val2
FROM source_table
[ WHERE condition];
Example, the employee wage info is in the sales_employees table, but their performance is under sales_performances
This updates their pay based on their performance
UPDATE sales_employees AS e
SET
salary = CASE s.score
WHEN 1 THEN salary * 1.02 -- 2% increase for score 1
WHEN 2 THEN salary * 1.04 -- 4% increase for score 2
WHEN 3 THEN salary * 1.06 -- 6% increase for score 3
WHEN 4 THEN salary * 1.08 -- 8% increase for score 4
WHEN 5 THEN salary * 1.10 -- 10% increase for score 5
END
FROM
sales_performances AS s
WHERE
e.id = s.sales_employee_id;
An update example that updates inventory with sales
CREATE TABLE inventory (
item_id INTEGER PRIMARY KEY,
item_name TEXT NOT NULL,
quantity INTEGER NOT NULL
);
CREATE TABLE sales (
sales_id INTEGER PRIMARY KEY,
item_id INTEGER,
quantity_sold INTEGER,
sales_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (item_id) REFERENCES inventory (item_id)
);
INSERT INTO
inventory (item_id, item_name, quantity)
VALUES
(1, 'Item A', 100),
(2, 'Item B', 150),
(3, 'Item C', 200);
INSERT INTO
sales (item_id, quantity_sold)
VALUES
(1, 20),
(1, 30),
(2, 25),
(3, 50);
UPDATE inventory
SET
quantity = quantity - daily.qty
FROM
(
SELECT
SUM(quantity_sold) AS qty,
item_id
FROM
sales
GROUP BY
item_id
) AS daily
WHERE
inventory.item_id = daily.item_id;
Insert or replace the existing row in a table
If a unique or primary key constraint violation occurs, replace:
REPLACE means INSERT or REPLACE. Not INSERT or UPDATE
INSERT OR REPLACE INTO table(column_list)
VALUES (value_list);
-- short form
REPLACE INTO table(column_list).....
Upsert is a combination of update and insert
If you attempt to insert or update a row that causes a unique constraint violation specified in ON CONFLICT, the DO clause takes over.
This inserts a new row if a specified unique identifier doesn't exist, or updates an existing row if it does.
INSERT INTO table1(column_list)
VALUES(value_list)
ON CONFLICT (conflict_column)
DO
UPDATE SET column_name = expression
WHERE conflight_condition;
If you don't want to take action on a conflict
INSERT INTO table1(column_list)
VALUES(value_list)
ON CONFLICT(conflict_column)
DO NOTHING;
Examples
Using upsert to store the number of times a term was searched.
If it doesn't exist, it gets created. If it does (a conflict) it gets updated
create table search_stats (
id integer primary key,
keyword text unique not null,
search_count int not null default 1
);
-- run the following for each term you want to track
insert into search_stats(keyword)
values ('knobjockey')
on conflict (keyword)
do
update set search_count = search_count + 1;
Example #2
This updates a table with contact info. The email address has to be unique.
If you try to update a record that creates an email conflict, update the record, but only if the effective date you are trying to enter is later than the existing date.
The excluded keyword gives access to the values you were trying to insert or update.
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT NOT NULL,
effective_date DATE NOT NULL
);
-- craete record
INSERT INTO contacts(name, email, phone, effective_date)
VALUES('Jane Doe', 'jane@test.com', '(408)-111-2222', '2024-04-05');
-- update the table
INSERT INTO
contacts (name, email, phone, effective_date)
VALUES
(
'Jane Smith',
'jane@test.com',
'(408)-111-3333',
'2024-05-05'
)
ON CONFLICT (email) DO
UPDATE
SET
name = excluded.name,
phone = excluded.phone,
effective_date = excluded.effective_date
WHERE
excluded.effective_date > contacts.effective_date;
delete record(s)
If you don't set a WHERE condition, all records are deleted.
DELETE FROM table1
WHERE condition;
[ ORDER BY criteria ]
[ LIMIT row_count OFFSET offset ]
Delete an entire table from a database
DROP TABLE [ IF EXISTS ] [schema_name.]table1;
insert, update, delete have an optional returning clause that returns the row that is inserted, updated or deleted
DELETE from contacts
WHERE id = 1
RETURNING name as Dead_Prick;
Restrictions
returning clause can't be used as a subqueryupdate fromALTER TABLE existint_table
RENAME TO new_table;
ALTER TABLE table_name
ADD COLUMN column_definition
current_timestamp, current_date, current_time or an expressionALTER TABLE table_name
RENAME COLUMN current_name TO new_name;
This isn't natively supported. To do this, you have to create a new table without the column, then copy data from the old to the new
-- disable foreign key constraint check
PRAGMA foreign_keys=off;
-- start a transaction
BEGIN TRANSACTION;
-- Here you can drop column
CREATE TABLE IF NOT EXISTS new_table(
column_definition,
...
);
-- copy data from the table to the new_table
INSERT INTO new_table(column_list)
SELECT column_list
FROM table;
-- drop the table
DROP TABLE table;
-- rename the new_table to the table
ALTER TABLE new_table RENAME TO table;
-- commit the transaction
COMMIT;
-- enable foreign key constraint check
PRAGMA foreign_keys=on;
Uses the standard comparison operator
= <> != < <= > =>
Returns 1 (true), 0 (false) or NULL.
ALL 1 if all expressions are 1
AND 1 if both expressions are 1, otherwise a 0, except, NULL AND 1 = NULL, NULL AND NULL = NULL
ANY 1 if any set of comparisons is 1
BETWEEN 1 if a value is within a range
EXISTS 1 if a subquery contains any rows
IN 1 if a value is in a list of values
LIKE 1 if a value matches a pattern
NOT reverses the other operators
OR 1 if either expression is 1, otherwise a 0, except False and NULL = NULL, NULL OR NULL = NULL
WHERE
Total > 5 AND
(city = 'Melbourne' OR city = 'Sydney')
Works with numbers, text and dates and finds data inclusive of the criteria
SELECT column(s)
FROM table1
WHERE column1 BETWEEN value1 AND value2;
-- or
WHERE column1 NOT BETWEEN value1 AND value2;
-- the two following statements produce the same results
WHERE column1 BETWEEN 2 AND 5
WHERE column1 >= 2 AND column1 <= 5
-- to make exclusive
WHERE column1 > 2 AND column1 < 5
Determine if a value matches a list of values.
Used to have multiple criteria in a WHERE clause
expression [NOT] IN (valuelist|subquery)
SELECT name, age FROM people
WHERE
age IN (54,69);
By default, not case sensitive for unicode characters in the ASCII range.
There is PRAGMA case_sensitive_like = true, but the sqlite documentation advised not to use it.
[https://www.sqlite.org/pragma.html#pragma_case_sensitive_like]
Used for pattern matching
There are two wild cards
SELECT column1
FROM table1
WHERE column1 LIKE pattern;
WHERE name LIKE 'a%' -- any value that starts with a
WHERE name LIKE '%a' -- any value that ends with a
WHERE name LIKE '%or%' -- any value that has "or" in any position
WHERE name LIKE '_r%' -- any value that has "r" in second position
WHERE name LIKE 'a_%_%' -- any value that has "a" with 3 characters
WHERE name LIKE 'a%o%' -- values that start with "a" and ends with "o"
ESCAPE clause
If you want the search the characters % and _, you need to escape them.
-- you want to search for 10% anywhere in the field
-- the first and last % are wildcards, the middle one is part of the search string
WHERE name LIKE '%10\%%' ESCAPE '\';
IS case sensitive
Used to determine if a string matches a specific pattern
Wilds cards are * and ? or a list such as : [ABC]
^ is like a not operator [^4-6]. (not 4 to 6)
A name that starts with any first character, followed by ere then anything after.
WHERE name GLOB '?ere*'; -- A name that starts with any first character, followed by `ere` then anything after.
WHERE name GLOB '*^1-9]*'; -- does not contain any numbers
WHERE name GLOB '*[1-9]'; -- ends with a number
optional clause to limit the number of records returned
An OFFSET 12 would start returning from the 12th record.
A subquery is a SELECT nested in another statement or subquery
Typically a subquery returns a single row, although it may return multiple rows for comparing values with the IN operator
Can use subqueries in SELECT, FROM, WHERE and JOIN clauses
SELECT column1 FROM table1
WHERE column1 = (SELECT column1 FROM table2);
Get albumid from a title, then use that to look up tracks
SELECT trackid, name, albumid FROM tracks
WHERE albumid = (
SELECT albumid FROM albums WHERE
title = "Let There Be Rock"
);
Using a subquery that returns a list of items
SELECT customerid, firstname, lastname FROM customers
WHERE supportrepid IN (
SELECT employeeid
FROM employees
WHERE country = "Canada"
);
A correlated subquery is one that uses values from the outer query, so unlike a normal subquery, can't be executed independently
These are not efficient as the subquery is evaluated for each row processed by the outer query.
This returns albums who's track sizes add up to over 10MB
SELECT albumid,
title
FROM albums
WHERE 10000000 > (
SELECT sum(bytes)
FROM tracks
WHERE tracks.AlbumId = albums.AlbumId
)
ORDER BY title;
A logical operator that checks whether a subquery returns any row.
In the below example, for each customer in the customer table, the invoice table is search to see if an invoices exists under that customer id
SELECT
CustomerId,
FirstName,
LastName,
Company
FROM
Customers c
WHERE
EXISTS (
SELECT
1
FROM
Invoices
WHERE
CustomerId = c.CustomerId
)
ORDER BY
FirstName,
LastName;
Returns 1 row per group, when can then be displayed or fed into an aggregate function
Must come after the FROM clause, and the WHERE clause if it exists.
The HAVING clause specified a search condition for a group
If you use HAVING outside of GROUP BY, it acts just like a WHERE clause
A difference between HAVING and WHERE is that HAVING is applied after GROUP BY, but WHERE is applied before.
SELECT column1
aggregate_function(column2)
FROM table1
GROUP BY column1,
HAVING search_condition;
AVG
COUNT
MAX
MIN
SUM
GROUP_CONCAT(expression, separator)
LEFT OUTER JOIN is the same as a LEFT JOIN
Returns all records from the left table and matched records from the right
All records in Table1 are returned even if there is no match in table2. In that case, any columns from table2 will be null
If there is a WHERE clause, it is applied after the records from the JOIN are returned, so you could search for the columns in table2 that are NULL to find out records that don't match.
SELECT table1col1, table1col2, table2col1
FROM table1
LEFT JOIN table2 ON table1.pk = table2.fk
WHERE table2col1 IS NULL
Similar to LEFT, but ALL rows from the right table (table 2 in this example) are returned and matching rows from left table. If no match, data from table1 will be null
USING
If the key fields in both tables having the same name, use can use USING rather than ON and you only specify the field name once.
SELECT table1col1, table1col2, table2col1
FROM table1
RIGHT JOIN table2 USING (id)
Return records from both tables that match
table1 has pk, col1, col2
table2 has fk, cola, colb
SELECT col1, col2, cola, colb
FROM table1
INNER JOIN table2 ON table2.fk = table1.pk
3 Table join
country is related to timezone via the key country_code
timezone is related to dst_changes via zone.id
SELECT country.country_code, country_name, timezone.zone_id, zone_name, utc_offset
FROM country
INNER JOIN timezone ON country.country_code = timezone.country_code
INNER JOIN dst_changes on timezone.zone_id = dst_changes.zone_id
where country.country_code = 'AU'
Basically a combination of a LEFT and RIGHT Join
Returns all records from both tables. If there row that aren't related, NULL fills the unrelated fields.
Also known as a cartesian join
Return all records from both tables
SELECT * FROM table1;
CROSS JOIN table2;
Can use either LEFT or INNET Join, but use multiple aliases on the same table to refer to itself
SELECTX m.firstname || ' ' || m.lastname as 'Manager',
e.firstname || ' ' || e.lastname as 'Pleb'
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;
Basic syntax
query1
UNION [ALL]
query2
UNION [ALL]
queryN...;
Union combines queries, eliminating duplications (unless ALL is specified)
The difference between UNION and JOINS is that joins combine columns from related tables, whereas Union combines rows from the results of multiple queries.
Union Rules
Compares the results of 2 queries and returns distinct rows from the first query that are not output by the second query
The number of types of columns in the two queries must be the same / compatible
Simple but stupid example
This excludes the results from the 2nd select statement from the first. ie song with the in the name is excluded
select * from tracks;
except
select * from tracks where name like '%the%'
Combines the results of multiple sets of queries and returns distinct rows that appear in all queries.
Another simple but stupid example where if a track id is either between 1 & 6 or 4 & 10 and it's name starts with P
select * from tracks where trackid between 1 and 6
intersect
select * from tracks where trackid between 4 and 10
intersect
select * from tracks where name like 'p%';
CTE's define temporary result sets within the scope of a query. The exist to make your query more readable and make more modular queries.
WITH cte_name (
-- cte query
)
SELECT * FROM cte_name
WITH aussie AS (
SELECT concat(firstname,' ',lastname) name, state from customers
WHERE country = "Australia"
)
SELECT * from aussie;
You can use CASE in any clause or statement that accepts a valid expression such as:
WHERE, ORDER BY, HAVING, SELECT, UPDATE, DELETE etc
There are two forms of CASE. simple CASE and Searched CASE
Simple CASE. If there is no ELSE, and nothing matches, NULL is returned.
When one expression matches, it doesn't fall through to the subsequent ones.
Simple CASE
CASE case_expression
WHEN when_expression_1 THEN result_1
WHEN when_expression_2 THEN result_2
...
[ ELSE result_x ]
END
customerid, firstname and lastname are all fields in the table. CASE displays a new field CustomerGroup that only has Domestic or Foreign
SELECT customerid,
firstname,
lastname,
CASE country
WHEN 'USA'
THEN 'Domestic'
ELSE 'Foreign'
END CustomerGroup
FROM
customers
ORDER BY
LastName,
FirstName;
Searched CASE
CASE
WHEN bool_expression1 THEN result_1
WHEN bool_expression2 THEN result_2
[ ELSE resultX ]
END
select trackid, name,
case
when milliseconds < 60000 then 'short'
when milliseconds > 60000 and milliseconds < 300000 then 'medium'
else 'long'
end category
from tracks;
Also called computed columns.
Can define a column whose values are derived from other columns in the same table
In SQLITE at least, they can be calculated as needed (virtual), or stored on disk (stored) like any other column
column_name data_type
[ GENERATED ALWAYS ] AS expression
[VIRTUAL | STORED]
GENERATED ALWAYS keywords are optional and not needed
VIRTUAL is the default as the storage type and is better optimised if lots of writes are done
STORED is better for performance if lots of reads are done.
CREATE TABLE products (
name TEXT NOT NULL,
price REAL NOT NULL,
discount REAL NOT NULL,
tax REAL NOT NULL,
net_price REAL
AS (price * (1-discount) * (1+tax))
);
not bull, check, unique, foreign key constraintsprimary keyalter table add column to add a stored column, but a virtual one can be addedINTEGER PRIMARY KEY column, but not directly reference a ROWID columnCREATE TABLE strict_table_name (
name TEXT NOT NULL,
price INTEGER NOT NULL
) STRICT;
INT, INTEGER, REAL, TEXT, BLOB, ANYANY column will accept any kind of data, but there is no attempt at any conversions.PRIMARY KEY columns are implicitly NOT NULLPRAGMA integrity_check and PRAGMA quick_check verify the contents of all columnsDeleted objects don't free up the space on the disk. They just mark it as free.
An SQLite file never shrinks in size. Only grows.
Over time with lots of inserts, updates and deletes, fragments the file and can slow things down.
It copies the database to another file, defrags it in the process, then copies the new database over the original
VACUUM;
You can vacuum to another file, which can act as a backup. This leave the original file unchanged.
VACUUM schema0name INFO filename;
VACUUM main INTO './backup.db;
A view is a result of a stored query. A way of store a query into a named object stored in the database
It can provide an abstraction layers on top of base tables. You can add or remove columns in a view without changing the base table.
It can encapsulate complex queries to simplify the data access
In SQlite, views are read only.
Examples:
CREATE VIEW v_tracks
AS
SELECT
trackid,
tracks.name,
albums.Title AS album,
media_types.Name AS media,
genres.Name AS genres
FROM
tracks
INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId
INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId
INNER JOIN genres ON genres.GenreId = tracks.GenreId;
-- once defined, can use this view to simplify queries
SELECT * FROM v_tracks;
-- delete the view
DROP VIEW [IF EXISTS] v_tracks;
Create a view with a custom names
columns AlbumTitle and Minutes aren't part of the base table.
CREATE VIEW v_albums (
AlbumTitle,
Minutes
)
AS
SELECT albums.title,
SUM(milliseconds) / 60000
FROM tracks
INNER JOIN
albums USING (
AlbumId
)
GROUP BY AlbumTitle;
-- to use.
SELECT * from v_albums;
CREATE [UNIQUE] INDEX index_name
ON table_name(column_list);
List indexes on a table
pragma index_list('table_name');
To get info an an index
pragma index_info('index_name');
To check if an index is being used for a query
EXPLAIN QUERY PLAN
SELECT
first_name,
last_name,
email
FROM
contacts
WHERE
email = 'lisa.smith@sqlitetutorial.net';
View indexes on all tables
SELECT
name,
tbl_name,
sql
FROM
sqlite_master
WHERE
type= 'index';
Delete an index
DROP INDEX [IF EXISTS] index_name;
You can create specific indexes so speed up common searches
If you can this as a common query
SELECT customerid,
company
FROM customers
WHERE length(company) > 10
ORDER BY length(company) DESC;
-- create the following index to speed up this query
CREATE INDEX customers_length_company
ON customers(LENGTH(company));
-- prefix the above SELECT with the follwing to see that the search uses indexes
EXPLAIN QUERY PLAN
A named object that is executed automatically when an insert, update or delete is issued
CREATE TRIGGER [IF NOT EXSITS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[WHEN condition]
BEGIN
statements;
END;
BEFORE or AFTER trigger can be on a table
INSTEAD OF can only be on a view
[https://www.sqlitetutorial.net/sqlite-full-text-search/]
typeof() - returns a string indicating the data type of an expression
returns null, integer, real, text or blob
date - calculate date based on multiple date modifiers
time - calculate a time based on multiple date modifier
datetime - calculate a date and time based on one or more date modifiers
julianday - converts dates and times to Julian day numbers
unixepoch - returns a timestamp of a date or datetime value
strftime - format a date value based on a format string
current_timestamp returns the current UTC date and time. YYYY-MM-DD HH:MM:SS
current_date - returns the current date in UTC YYYY-MM-DD
current_time - returns the current time in UTC HH:MM:SS
avg() - returns the average of a group
count() - returns the number of rows that match a condition
max() - returns the maximum value in a group
min() - returns the minimum value in a group
sum() - returns the sum of values
group_concat(expression,separator) - returns a string
substr - return substring
trim - returns string with specified characters removed from beginning and end
ltrim
rtrim
length - returns number of characters or bytes in a blob
replace - returns a copy of a string with each instance of a substring replaced by another string
upper - return string converted to upper case
lower
instr - find a substring in a string and return an integer indicating position of first occurrence
coalesce - return the first non-null argument
if null - provide the null if/else construct
iif - add if-else logic to queires
nullif - return null if first argument is equal to second argument
abs - return the absolute value of a number
acos, acosh, asin, asinh, atan, atan2, atanh, cos, cosh
ceil or ceiling - returns smallest integer value greater than or equal to a given number
degrees - convert value in radians to degrees
floor - returns the largest integer value less than or equal to a given number
ln, lob(b), log, log10, log2
mod - returns the modulus of a division
pi - returns pi
pow or power - returns a number raised to the power of another number
radians - convert degrees to radians
random - return a random integer value
round - round off a floating value to a specified precision
sin sinh
sqrt - returns the square root of a number
tan tanh
trunc - returns the integer part of a number
SELECT current_timestamp;
SELECT datetime(current_timestamp, 'localtime')
-- having it as a default
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
note TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT current_timestamp,
updated_at TEXT NOT NULL DEFAULT current_timestamp
);
CREATE TRIGGER update_notes_updated_at
AFTER UPDATE ON notes
WHEN old.updated_at <> current_timestamp
BEGIN
UPDATE notes
SET updated_at = CURRENT_TIMESTAMP
WHERE id = OLD.id;
END;
SELECT datetime(current_timestamp,'localtime')
SELECT datetime('now')
Supported
Only RENAME TABLE, ADD COLUMN, RENAME COLUMN & DROP COLUMN
Unsupported
Others such as ALTER COLUMN, ADD CONSTRAINT and so on are not supported