Basic knowledge of SQLite

SQLite is a software library that implements a self-contained, server-free, 0-configured, transactional SQL database engine. SQLite is one of the fastest growing database engines, which is growing in popularity, regardless of its size. SQLite source code is not subject to copyright restrictions.

Why use SQLite?

There is no need for a separate server process or operation of the system (no server).

SQLite does not require configuration, which means that installation or management is not required.

A complete SQLite database is stored in a single cross-platform disk file.

SQLite is very small, is lightweight, fully configured when less than 400KiB, omit optional feature configuration when less than 250KiB.

SQLite is self-sufficient, which means that no external dependencies are required.

SQLite transactions are fully ACID-compatible and allow secure access from multiple processes or threads.

SQLite supports the functionality of most query languages for the SQL92 (SQL2) standard.

SQLite is written using ansi-c and provides a simple and easy-to-use API.

SQLite can be run in UNIX (Linux, Mac os-x, Android, IOS) and Windows (Win32, WinCE, WinRT).

DDL-Data Definition language

CREATE
Create a new table, a view of a table, or other objects in the database.

Alter
Modifies an existing database object in the database, such as a table.

DROP
Deletes the entire table, or the view of the table, or other objects in the database.

DML-Data Manipulation language

INSERT
Create a record.

UPDATE
Modify the record.

DELETE
Deletes a record.

DQL-Data Query Language

SELECT
Retrieve some records from one or more tables.

SQLite is case-insensitive, but some commands are case-sensitive, such as GLOB and GLOB have different meanings in SQLite's statements.

SQLite annotations are additional annotations that you can add to your SQLite code to make them more readable, and they can appear in any space, including in the middle of an expression and other SQL statements, but they cannot be nested.

The SQL comment starts with two consecutive "-" characters (ASCII 0x2d) and expands to the next newline character (ASCII 0x0a) or until the input ends, whichever is first.

You can also use C-style annotations to start with "/*" and extend to the next "*/" character pair or until the input ends, whichever is the first. SQLite annotations can span multiple lines.

SQLite Storage Class

Each value stored in the SQLite database has one of the following storage classes:

Null
Value is a NULL value.

INTEGER
The value is a signed integer that is stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value.

REAL
The value is a floating-point value that is stored as a 8-byte IEEE floating-point number.

TEXT
The value is a text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).

Blob
The value is a blob of data that is stored entirely according to its input.

SQLite's storage classes are slightly more common than data types. An integer storage class, for example, contains 6 different lengths of integral data types.

SQLite Affinity Type

SQLite supports the type affinity concept on columns. Any column can still store any type of data, but the preferred storage class for the column is its affinity. In the SQLite3 database, the columns for each table are assigned to one of the following types of affinity:

TEXT
The column stores all data using the storage class NULL, TEXT, or BLOB.

NUMERIC
The column can contain values that use all five storage classes.

INTEGER
Same as the column with NUMERIC affinity, with an exception in the CAST expression.

REAL
Similar to a column with NUMERIC affinity, the difference is that it forces an integer value to be converted to a floating point representation.

NONE
Columns with affinity NONE, which storage class is not preferred, nor does it attempt to cast data from one storage class to another.

SQLite Affinity and type names

The following table lists the various data type names that can be used when creating the SQLite3 table, and also shows the appropriate application Affinity:

INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB no datatype specified	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME

Boolean data type

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

Date and Time data types

SQLite does not have a separate storage class for storing dates and/or times, but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Storage class
Date format

TEXT
The format is "Yyyy-mm-dd HH:MM:SS." SSS "date.

REAL
The number of days starting from midday in November 24, 4714 GMT.

INTEGER
The number of seconds from 1970-01-01 00:00:00 UTC.

You can store dates and times in any of the above formats, and you can use the built-in date and time functions to freely convert different formats.

What is the SQLite operator?

An operator is a reserved word or character that is primarily used in the WHERE clause of an SQLite statement, such as comparison and arithmetic operations.

Operator is used to specify conditions in the SQLite statement and to concatenate multiple conditions in the statement.

• Arithmetic operators

• Comparison operators

• logical operators

• Bitwise operators

SQLite arithmetic operators

+
Add-Adds the values on both sides of the operator
A + B will get 30

-
Subtraction-left operand minus right operand
A-B will get-10

*
Multiplication-multiplies the values on both sides of the operator
A * B will get 200

/
Division-left operand divided by right operand
B/A will get 2

%
Modulo-the remainder of the left operand divided by the right operand
B% A would give 0

SQLite comparison Operators

==
Check that the values of the two operands are equal, and if they are equal, the condition is true.
(A = = B) is not true.

=
Check that the values of the two operands are equal, and if they are equal, the condition is true.
(A = b) is not true.

!=
Check that the values of the two operands are equal, and the condition is true if they are not equal.
(A! = B) is true.

<>
Check that the values of the two operands are equal, and the condition is true if they are not equal.
(a <> B) is true.

>
Checks if the value of the left operand is greater than the value of the right operand, and if so, the condition is true.
(A > B) is not true.

<
Checks if the value of the left operand is less than the value of the right operand, or if the condition is true.
(A < b) is true.

>=
Checks if the value of the left operand is greater than or equal to the right operand, and if so the condition is true.
(a >= B) is not true.

<=
Checks if the value of the left operand is less than or equal to the right operand, and if so the condition is true.
(a <= B) is true.

!<
Checks whether the value of the left operand is not less than the value of the right operand, or if the condition is true.
(a!< B) is false.

!>
Checks if the value of the left operand is not greater than the value of the right operand, and if so, the condition is true.
(a!> B) is true.

SQLite logical operators

and
The AND operator allows the existence of multiple conditions in the WHERE clause of an SQL statement.

Between
The between operator is used to search for a value within a range of values for a given minimum and maximum value.

EXISTS
The EXISTS operator is used to search for the existence of a row in a specified table that satisfies certain criteria.

Inch
The in operator is used to compare a value to a series of values in a specified list.

Not in
The opposite of the in operator, which is used to compare a value to a value that is not in a series of specified lists.

Like
The LIKE operator is used to compare a value to a similar value using a wildcard operator.

GLOB
The GLOB operator is used to compare a value to a similar value using a wildcard operator. The difference between GLOB and like is that it is case-sensitive.

Not
The NOT operator is the opposite of the logical operator used. such as not EXISTS, not between, not in, and so on. It is a negation operator.

OR
The OR operator is used to combine multiple conditions in a WHERE clause of an SQL statement.

Is NULL
The null operator is used to compare a value to a null value.

Is
The IS operator is similar to =.

is not
The is not operator is similar to! =.

||
Connect two different strings and get a new string.

UNIQUE
The unique operator searches for each row in the specified table, ensuring uniqueness (No duplicates).

SQLite bitwise operator

&
If both are present in two operands, the binary and operator copies one to the result.
(A & B) will be 12, i.e. 0000 1100

|
If it exists in either operand, the binary OR operator copies one into the result.
(A | B) will get 61, i.e. 0011 1101

~
The binary complement operator is a unary operator with a "flip" bit effect.
(~a) will get-61, which is 1100 0011,2 of the complement form, signed binary number.

<<
Binary left shift operator. The value of the left operand moves the number of digits specified by the right operand to the left.
A << 2 will get 240, which is 1111 0000

>>
Binary right-shift operator. The value of the left operand moves the right operand to the specified number of digits.
A >> 2 will get 15, which is 0000 1111

The LIKE operator is a literal value that is used to match a wildcard specified pattern. If the search expression matches the pattern expression, the LIKE operator returns True (TRUE), which is 1. Here are two wildcard characters to use with the LIKE operator:

• Percent percent (%)

• Underline (_)

A percent symbol (%) represents 0, one or more digits or characters. An underscore (_) represents a single number or character. These symbols can be used in combination.

The GLOB operator is a literal value that is used to match a wildcard specified pattern. If the search expression matches the pattern expression, the GLOB operator returns True (TRUE), which is 1. Unlike the LIKE operator, GLOB is case-sensitive and follows the UNIX syntax for the following wildcard character.

• asterisk (*)

• Question mark (?)

An asterisk (*) represents 0, one, or more digits or characters. The question mark (?) represents a single number or character. These symbols can be used in combination.

The order by clause is used to arrange data in ascending or descending order based on one or more columns.

The basic syntax for the ORDER by clause is as follows:

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER by clause. Make sure that the sort you use is listed in the column list.

The group by clause is used with the SELECT statement to group the same data.

In a SELECT statement, the GROUP by clause is placed after the WHERE clause and placed before the ORDER by clause.

The HAVING clause allows you to specify criteria to filter the grouped results that will appear in the final result.

The WHERE clause sets the condition on the selected column, while the HAVING clause sets the condition on the group created by the GROUP BY clause.

The DISTINCT keyword is used with the SELECT statement to eliminate all duplicate records and to get only one record at a time.

There may be a situation in which there are multiple duplicate records in a table. When such a record is extracted, the DISTINCT keyword becomes particularly meaningful, and it only gets the unique record, not the duplicate record

Basic knowledge of SQLite