SQLite Database Introduction

Use SQLite installed on Linux (CentOS7) to introduce.

(You can also use python to install SQLite3 for learning.)

• SQLite Preface

• Introduction to SQLite Features

• SQLite Data Type

• SQLite Constraint

• SQLite Syntax

• SQLite Join

• SQLite Database & Table

• SQLite Backup & Restore

• SQLite Functions

• Reference

  SQLite, a lightweight self-contained database, is an ACID-compliant relational database management system contained in a relatively small C library.

  ACID( Atomicity, Consistency, Isolation, Durability ).

  RDBMS( Relational Database Management System ).

1. ACID transactions.

2. Zero configuration - no need to install and manage configuration.

3. Supports most SQL92 standards, such as: ATTACH DATABASE, BEGIN TRANSACTION, COMMENT, etc., and it also supports transaction processing functions.

4. A complete database stored in a single disk file.

• Suitable storage format for application files.

5. Database files can be freely shared between machines with different byte orders.

6. Support database size up to 2TB.

7. The source code is small, about 130,000 lines of C code, 4.44M.

• Simple and easy to use API.

• Available as a separate header package, easy to compile and easy to add to large projects.

8. Written in ANSI-C with TCL/TK bindings .

9. Faster operations on data than most popular databases.

10. Supports multiple platforms and multiple development languages: UNIX (Linux, Mac OS-X, Android, IOS), Windows (Win32, WinCE, WinRT), C, PHP, Perl, Java, C#, Python, Ruby, C++.

11. Command line interface for managing databases from the command line.

12. Standalone: ​​no additional dependencies.

SQLite Architecture:

 

   SQLite Execution Process:

• SQLite first creates or opens a database, then executes the corresponding SQL statement, and finally closes the database.

• In the Prepare phase, the input SQL statement is compiled into VDBE bytecode through the parser, tokenizer and code generator.

• In the execution phase, the virtual machine executes the operator, the execution process is a step-by-step process, each step is started by the sqlite3_step function, and the sqlite3VdbeExec function executes the operator compiled by the Prepare phase.

• In the Final stage, first execute the sqlite3VdbeFinalize function to close the VDBE, and then execute the sqlite_finalize () function to end the execution of SQLite.

 

The operation of the database is mainly to operate the table.

Each column of the table has a certain data type, such as integer value, string, Boolean, etc.

Data Type	Define		Data Type	Define
NULL	Indicates that the value is a NULL value.		double	64-bit real number
INTEGER	Unsigned integer value.		char(n)	A string of length n, n cannot exceed 254.
REAL	Floating point value.		varchar(n)	A string of variable length and its maximum length is n, where n cannot exceed 4000.
TEXT	Text string, the encoding method used for storage is UTF-8, UTF-16BE, UTF-16LE.		graphic(n)	Same as char(n), but its unit is two bytes and n cannot exceed 127. Such as Chinese.
BLOB	Store Blob data, this type of data is exactly the same as the input data, 1 means true, 0 means false.		vargraphic(n)	A variable-length double-character string with a maximum length of n, where n cannot exceed 2000.
smallint	16-bit integer		date	Contains year, month, date.
integer	32-bit integer		time	Contains hours, minutes, seconds.
decimal(p,s)	The exact value p refers to the number of decimal digits in all, and s refers to the number of decimal places after the decimal point. Default: p = 5, s = 0.		timestamp	Contains year, month, day, hour, minute, second, thousandth of a second.
float	32-bit real number		…	…

Each column of the table has some restrictive attributes, such as the data of some columns cannot be repeated, and some restrict the data range, etc.

Constraints are used to further describe the data attributes of each column.

Name	Define
NOT NULL	NOT NULL is a non-null field, which needs to be declared in advance when defining the table.
UNIQUE	In addition to the main column, there are also some columns that cannot have duplicate values.
PRIMARY KEY	Generally, it is an integer or a string, as long as it is guaranteed to be unique. In SQLite, if the primary key is an integer type, the value of the column can grow automatically.
FOREIGN KEY	A foreign key can be used to establish a relationship between two tables or more tables.
CHECK	Certain values ​​must meet certain conditions before they are allowed to be stored, which is why this CHECK constraint is required.
DEFAULT	There are some special field columns, in each record, the value is basically the same.

Operator	Illustrate
AND	This operator allows multiple conditions to be present (or used) in the WHERE clause of an SQL statement.
BETWEEN	This operator is used to search for values ​​that lie within a range of a given minimum and maximum value.
EXISTS	This operator is used to search for the existence of rows in the specified table that meet certain criteria.
IN	This operator is used to compare a value to a value in a specified list of literal values.
NOT IN	This operator is used to compare the negation of a value with a value in a specified list of literal values.
LIKE	This operator is used to compare a value with similar values ​​using the wildcard operator.
GLOB	This operator is used to compare values ​​with similar values ​​using wildcard operators.  ( GLOB != glob )
NOT	This operator reverses the meaning of the logical operator using it.
OR	This operator is used to combine multiple conditions in where clause of SQLite statement.
IS NULL	This operator is used to compare a value with an empty (null) value.
IS	This operator works like “=” operator.
IS NOT	This operator works like “!=” operator.
||	This operator is used to concatenate two different strings on both sides of the operator to create a new string.
UNIQUE	This operator searches for the uniqueness of each row of records in the specified table (values ​​are not repeated).

SQLite is not case sensitive. -- However, there are some case-sensitive commands.

Comment:

• Comments are used to increase code readability in SQLite code.

• Comments cannot be nested.

• Comments start with two consecutive "-" characters.

• Sqlite > .table – This is a simple comment.

• You can also start with a "/*" character and extend to the next "*/" character to treat the included content as a comment.

SQLite statement:

• All SQLite statements start with keywords (such as: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc.). All statements end with a semicolon (;).

Common SQLite Statements:

Case1: Create table:

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY( one or more columns )); 

Case2: Select:

SELECT column1, column2....column
FROM table_name; 

Case3: Delete:

DELETE FROM table_name WHERE { CONDITION }; 

Case4: Update:

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ]; 

Case5: Insert:

INSERT INTO 
table_name( column1, column2....column )
VALUES ( value1, value2....valueN ); 

Case6: Like:

SELECT column1, column2....column
FROM table_name
WHERE column_name LIKE { PATTERN }; 

Case7: Group:

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name; 

Such as analyze, and/or, alter, between…

Operations overview:

Insert:

 Update:

Delete:

 Alert:

 Select:

In SQLite, the JOIN clause is used to combine records from two or more tables in a database.

• It combines fields from both tables by using common values ​​from both tables.

There are mainly three types of joins in SQLite:

1. SQLite Inner Join

2. SQLite Outer Join

3. Sqlite Cross Join

Example:

• If there are two tables

MEMBER:

 SALARY:

Inner Join:

It is used to combine all row records from multiple tables that satisfy the join condition.

• SQLite inner join is the default join type.

Syntax
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
Or
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ... 
Or
SELECT ... FROM table1 NATURAL JOIN table2... 

The shaded part is the intersection of inner joins:

Left Join:

  Similar to the INNER JOIN clause, the LEFT JOIN clause is an optional clause of the SELECT statement. Data from multiple related tables can be queried using the LEFT JOIN clause.

Syntax
SELECT ... FROM table1 LEFT JOIN table2 ON conditional_expression WHERE search_condition 

Outer Join:

In the SQL standard, there are three types of outer joins:

1. Left outer join

2. Right outer join

3. Full outer join

However, SQLite only supports left outer joins.

SQLite left outer join is used to fetch all rows from the left table specified in the ON condition and only those rows from the right table that satisfy the join condition are recorded.

Syntax
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression
Or
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ... 

The shaded part is the intersection of left outer joins:

Cross Join:

SQLite cross join is used to match every row of the first table with every row of the second table. If the first table contains x columns and the second table contains y columns, the resulting cross join table results will contain x * y columns.

Syntax
SELECT ... FROM table1 CROSS JOIN table2

 Schematic:

Create Database:

Create a test database file in current directory.

 To Create it in a different directory, simply use the full path.

Attach a Database:

• We can use the ATTACH DATABASE cmd to create a database from within the SQLite3 cmd utility.

Manipulate an existing database:

Create Table:

CREATE a TABLE

INSERT some items

Check the data

Rename table

 

Step:

• .dump ->  Displays on the screen all the SQL statements for creating the table and inserting records into the table.

• .output GARMINSE.sql ->  Specifies the filename of the dump command output to the file.

• .dump à Output the SQL statement that creates and inserts data into the base table to the file specified by output.

• .output stdout ->  Restore output to standard output device (screen).

• .dump ->  At this point, the output SQL statement returns to the screen.

• Drop table SE; ->  Delete SE table statement description.

• .read GARMINSE.sql ->  Execute all the SQL statements contained in GARMINSE.sql to rebuild the table and related data that was just deleted.

  SQLite aggregate functions are functions that combine the values ​​of multiple rows as input for some condition and form a single value as the output result.

Number	Functions	Description
1	MIN()	The MIN() function is used to query the lowest (minimum) value of a column.
2	MAX()	The MAX() function is used to query the highest (maximum) value of a column.
3	AVG()	The AVG() function is used to query the average value of a column.
4	COUNT()	The COUNT() function is used to count the number of rows in a database table.
5	SUM()	The SQLite SUM() function is used to query the total number (sum of additions) of a specified numeric column.
6	RANDOM()	The RANDOM() function returns a pseudorandom integer between -9223372036854775808 and +9223372036854775807.
7	ABS()	The ABS() function is used to get the absolute value of a given parameter.
8	UPPER()	The UPPER() function is used to convert the given string argument to uppercase letters.
9	LOWER()	The LOWER() function is used to convert the given string argument to lowercase letters.
10	LENGTH()	The LENGTH() function is used to get the length of the given string.

Case1: SUM

Case2: COUNT

  Note: The content of SQLite is far more than that. Interested friends can also refer to the website link I posted, which has more detailed content.

Reference:

• SQLite Home Page

• https://zh.m.wikipedia.org/zh-hans/SQLite

• bind manual page - Tk Built-In Commands

• SQLite 注入 | 菜鸟教程

• https://docs.python.org/zh-tw/3/library/sqlite3.html#

• SQLite 教程 | 菜鸟教程

• https://www.sqlitetutorial.net/

Reference

 Reference