-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite3_snippets.txt
42 lines (38 loc) · 3.26 KB
/
sqlite3_snippets.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
sqlite3 test.db - create new DB from cmd-line
.headers on - turn on headers on the first line of each output for visual convenience.
.backup backup.db - backups our DB (opened before)
.restore backup.db - restores from backup
.tables - lists all tables in DB
.schema - print structure of your tables
.dump - dumps all DB as SQL
.exit or .quit - :)
---
CREATE TABLE contacts (name TEXT, phone INTEGER, email TEXT); - create table with three columns. (Data types: NULL, INTEGER, REAL(float point number), TEXT, BLOB)
CREATE TABLE IF NOT EXISTS contacts (name TEXT, phone INTEGER, email TEXT); - create the same table if it not exists
CREATE TABLE contacts (_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT); PRIMARY KEY is the key for table indexing(unic! will autoincrement by default), NON NULL - we can't create a record without filling this field
INSERT INTO contacts (name, phone, email) VALUES('Tim', 5353535, 'some@mail.com'); - insert data to specified columns (when not all fields might be given)
INSERT INTO contacts VALUES('Vasia', 42523525, 'sss@mail.com'); - when all fields are given and in exact order
SELECT * FROM contacts; - get all colums
SELECT name, email FROM contacts; - get from specified columns
UPDATE contacts SET email='steve@mail.com'; - will update email field in ALL columns!!! RISKY!!
UPDATE contacts SET email='steve@mail.com' WHERE name = 'Steve'; - updates email field of the records, where name is set to Steve
SELECT * FROM contacts WHERE name = 'Brian'; - select all columns where name is Brian
SELECT phone, email FROM contacts WHERE name = 'Brian'; - select only choosen columns
DELETE FROM contacts WHERE phone = 1234; - delete record where phone is set to 1234
SELECT * FROM artists ORDER BY name; - choosing in which order to return rows
SELECT * FROM artists ORDER BY name COLLATE NOCASE DESC; - ignoring letters case(COLLATE NOCASE) and making descending order(DESC or ASC for ascending)
SELECT * FROM albums ORDER BY artist, name COLLATE NOCASE; - order by two columns!
SELECT songs.track, songs.title, albums.name FROM songs JOIN albums ON songs.album = albums._id; - we specifying which columns and from which tables to select(we can ommit table names if they are unique), than which tables to JOIN and by which statement/column.
SELECT songs.track, songs.title, albums.name FROM songs JOIN albums ON songs.album = albums._id ORDER BY songs.title DESC;
--same as--
SELECT songs.track, songs.title, albums.name FROM songs INNER JOIN albums ON songs.album = albums._id;
:::::::::
There are different types of joins available in SQL:
INNER JOIN(same as JOIN): returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: It combines the results of both left and right outer joins. (NOT the same as UNION operation!)
The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
:::::::::::