The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL commands against an SQLite database.
// conmect db
sqlite3 myDatabase.db
// list tables
.tables
// layout of all tables
.schema
// layout of selected table
.schema table_name
// output to a text file
sqlite> .output c:/sqlite/chinook.sql
// dump database
sqlite> .dump
// dump only selected table
sqlite> .dump table_name
// exit
.quit
Select statement
The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. A SELECT statement does not make any changes to the database.
SELECT DISTINCT column_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;
-- Select all rows in a table
SELECT * FROM Customers;
-- Select only specified columns in a table
SELECT customer_name, custormer_surname
FROM Customers;
-- Sort by specified columns in ascending order
SELECT customer_name, custormer_surname
FROM Customers
ORDER BY custormer_surname ASC;
-- ASC or DSC. If nothing specified it is ASC by default
-- Select unique values like set operation
SELECT DISTINCT city
FROM customers;
-- Filter results
SELECT customer_name, custormer_surname
FROM Customers
WHERE age<35;
-- Return null values
SELECT customer_name, custormer_surname
FROM Customers
WHERE age IS NULL;
-- NOT NULL; -> return non-null values
Datatypes In SQLite
Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container – the particular column in which the value is stored.
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. Flexible typing is a feature of SQLite, not a bug.
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
- 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.
SELECT
typeof(10), -- INTEGER
typeof(10.0), -- REAL
typeof('10'), -- TEXT
typeof(x'10'),-- BLOB
typeof(NULL); -- NULLAlias
SELECT column_name AS alias_name
from my_table;
-- OR
SELECT e.employee_id, e.last_name, departments.department_name
FROM employees AS e;
Leave a Reply