What is SQLite? How does it work?

SQLite

SQLite is an ACID-compliant relational database management system that is contained in a relatively small C library. Unlike many other database management systems, SQLite is not a database engine with a client/server structure, but is integrated into user programs.

SQLite complies with ACID and implements most SQL standards. It uses dynamic, weakly typed SQL syntax. As an embedded database, it is a common choice for applications, such as web browsers, to store data locally/on clients. It is probably the most widely deployed database engine, as it is being used by some popular browsers, operating systems, and embedded systems. At the same time, it has language bindings for many programming languages.

SQLite statement

Adjust the print display effect

sqlite> .mode column
sqlite> .header on
Copy the code

Retrieving a single column

select prod_name from products;
Copy the code

Retrieve multiple columns

select prod_id, prod_name, prod_price from products;
Copy the code

Retrieve all columns

select * from products;
Copy the code

Retrieve different values

select vend_id from products;
select DISTINCT vend_id from products;
Copy the code

Limit the results

SELECT prod_name FROM Products LIMIT 5;
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
Copy the code

Sorting data

SELECT prod_name FROM Products ORDER BY prod_name;
Copy the code

When specifying an ORDER BY clause, ensure that it is the last clause in the SELECT statement. If it is not the last clause, an error message will appear.

In general, the columns used in the ORDER BY clause will be the columns selected for display. However, this does not have to be the case; it is perfectly legal to sort data with non-retrieved columns.

Sort by multiple columns

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
Copy the code

Sort by column position

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2.3;
Copy the code

Specify sort direction

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;
Copy the code

The WHERE clause filters data

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
SELECT prod_name, prod_price FROM Products WHERE prod_price < = 10;
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01'; - do not match
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10; Range -
SELECT prod_name FROM Products WHERE prod_price IS NULL; - a null value
Copy the code

Combine the WHERE clause

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price < = 4;
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
Copy the code

SQL processes the AND operator before the OR operator, AND uses parentheses to change the order of calculations if necessary.

SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price > = 10;
Copy the code

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01'.'BRS01' ) ORDER BY prod_name;
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
Copy the code

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
Copy the code

The LIKE operator

Percent sign (%) wildcard

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%'; Retrieve any word that begins with Fish
Copy the code

The wildcard % looks like it can match anything, with one exception: NULL. The clause WHERE prod_name LIKE ‘%’ does not match rows with product names called NULL.

Underscore wildcard

The underscore serves the same purpose as the % wildcard, but it matches a single character, not multiple characters.

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';
Copy the code

Square brackets [] wildcard

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
SELECT cust_contact FROM Customers WHERE NOT cust_contact LIKE '[JM]%' ORDER BY cust_contact;
Copy the code

Concatenate field

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ') ' FROM Vendors ORDER BY vend_name;
Copy the code

The alias

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ') ' AS vend_title FROM Vendors ORDER BY vend_name;
Copy the code

Perform arithmetic calculations

SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_num = 20008;
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
Copy the code

Text processing function

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;
Copy the code

• LEFT() (or using a substring function) returns the character to the LEFT of the string
• RIGHT() (or using a substring function) returns the character to the RIGHT of the string
• LENGTH() (also using DATALENGTH() or LEN())) returns the LENGTH of the string
• UPPER() (Access uses UCASE()) to convert the string to uppercase
• LOWER() (Access uses LCASE()) to convert a string to lowercase
• LTRIM() removes whitespace from the left of the string
• RTRIM() removes the whitespace to the right of the string
• SOUNDEX() Returns the SOUNDEX value of the string

Date and time handlers

SELECT order_num FROM Orders WHERE strftime('%Y', order_date) = '2012';
Copy the code

Numerical processing function

• ABS() returns the absolute value of a number
• Cosine of theta returns the cosine of an Angle
• EXP() returns the exponent of a number
• PI() returns PI
• Sine () returns the sine of an Angle
• SQRT() returns the square root of a number
• Tangent of theta returns the tangent of an Angle

Aggregation function example

SELECT AVG(prod_price) AS avg_price FROM Products;
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
Copy the code

SELECT count(vend_id) AS avg_price FROM Products WHERE vend_id = 'DLL01';
SELECT MAX(prod_price) AS max_price FROM Products;
SELECT MIN(prod_price) AS min_price FROM Products;
Copy the code

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
Copy the code

• AVG() returns the average value of a column
• COUNT() returns the number of rows in a column
• MAX() returns the maximum value of a column
• MIN() returns the minimum value for a column
• SUM() returns the SUM of the values of a column

Aggregate different values

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
Copy the code

Combinatorial aggregation function

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products;
Copy the code