The beginning of SQLite

1 , what is SQLite

File-based lightweight database server-free 0 configuration support transactional open source

2 , SQLite How to use

2.1 installation

SQLite official web download the corresponding DLLs and tools

Configuring Environment variables

View version after installation is complete

2.2 Create a database

SQLite's creation and use of the database is a command, the database is loaded according to the name of the database, if not exist, create the database,

You will then see sqlite> indicating that the database is being used

2.3 Create, delete data tables

Created: CREATE TABLE user

(

ID int PRIMARY key NOT NULL,--id primary key not empty

Name varchar () is not NULL,

Age int

);

Delete: DROP TABLE table_name

2.4 SQLite Data Type

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

Storage class	Describe
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 Affinity and type name

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:

Data type	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	NUMERIC

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 with the time data type

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.

Inserting data into the table

Format 1:insert into table_name (column1, Column2,column3,... columnn)] VALUES (value1, value2, value3,... Valuen);

Format 2:insert into company VALUES (7, ' James ', ' Houston ', 10000.00);

Import data from other tables

INSERT into First_table_name [(Column1,column2, ... columnn)]

SELECT column1, Column2, ... columnn

From Second_table_name [WhereCondition];

Deleting data Delete

DELETE from table_name where[condition];

Find Data Select

SQLite's SELECT statement is used to fetch data from the SQLite database table and return the data as a result table. These result tables are also known as result sets. The basic syntax for SQLite's SELECT statement is as follows:

SELECT column1, Column2, ColumnN fromtable_name;

Updating Data Update

UPDATE table_name

SET column1 = value1, Column2 =value2 ...., columnn = Valuen

WHERE [condition];

Fuzzy query like

%: A percent semicolon represents one or more arbitrary characters or data

_: The underscore represents a single number or character and 2 can be used in combination

WHERE SALARY like ' 200% '	Find any value starting with 200
WHERE SALARY like '%200% '	Find any value that contains 200 in any location
WHERE SALARY like ' _00% '	Find any value for the second and third digits 00
WHERE SALARY like ' 2_%_% '	Find any value starting with 2 with a length of at least 3 characters
WHERE SALARY like '%2 '	Find any value ending with 2
WHERE SALARY like ' _2%3 '	Find any value with a second digit of 2 and ending with 3
WHERE SALARY like ' 2___3 '	Finds any value that has a length of 5 digits and ends with 3, starting with 2

The glob operator of Glob:sqlite is the literal value used to match the wildcard specified pattern, and unlike the Like operator, the glob is case-sensitive

*: Asterisks represent 0, one or more digits or characters

?: The question mark represents a single number or character. These symbols can be used in combination.

Statement	Describe
WHERE SALARY GLOB ' 200* '	Find any value starting with 200
WHERE SALARY GLOB ' *200* '	Find any value that contains 200 in any location
WHERE SALARY GLOB '? 00* '	Find any value for the second and third digits 00
WHERE SALARY GLOB ' 2?? '	Find any value starting with 2 with a length of at least 3 characters
WHERE SALARY GLOB ' * * *	Find any value ending with 2
WHERE SALARY GLOB '? 2*3 '	Find any value with a second digit of 2 and ending with 3
WHERE SALARY GLOB ' 2??? 3 '	Finds any value that has a length of 5 digits and ends with 3, starting with 2

SQLite Limit clause : Limit the amount of data returned

format 1: returns the specified number of result sets

SELECT column1, Column2, ColumnN

From table_name

LIMIT [No of rows]

Format 2: fetches the value of n rows from the M line as a result set returned

SELECT column1, Column2, ColumnN

From table_name

LIMIT N OFFSET M

SQLite Order by Sort

Syntax format:

Selectcolumn-list

From table_name

[WhereCondition]

[ORDER BYcolumn1, Column2,.. columnn] [ASC | DESC];

SQLite Group by Grouping

Syntax format:

Selectcolumn-list

From table_name

WHERE [conditions]

GROUP BYcolumn1, Column2....columnn

ORDER BYcolumn1, Column2....columnn

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

Grammar

Here is where the HAVING clause is in the SELECT query:

SELECT from WHERE GROUP by have

SELECT column1, Column2 from Table1,table2

WHERE [conditions] GROUP by Column1,column2

Having [conditions]

ORDER by Column1, Column2

SQLite Distinct keywords Go heavy

Grammar

The basic syntax for DISTINCT keywords to eliminate duplicate records is as follows:

SELECT DISTINCT column1,column2,..... columnn

From table_name

WHERE [Condition]

SQLite Common Functions

SQLite has many built-in functions for working with string or numeric data. Some useful SQLite built-in functions are listed below, and all functions are case insensitive, which means you can use lowercase or uppercase or mixed forms of these functions. For more information, please check out the official SQLite documentation:

Serial number	Functions & Descriptions
1	SQLite COUNT function The SQLite count aggregate function is used to calculate the number of rows in a database table.
2	SQLite MAX function The SQLite max aggregation function allows us to select the maximum value for a column.
3	SQLite MIN function The SQLite min aggregation function allows us to select the minimum value for a column.
4	SQLite AVG function The SQLite AVG aggregation function calculates the average of a column.
5	SQLite SUM function The SQLite sum aggregation function allows you to calculate a sum for a numeric column.
6	SQLite RANDOM function The SQLite random function returns a pseudo-random integer between 9223372036854775808 and +9223372036854775807.
7	SQLite ABS function The SQLite ABS function returns the absolute value of the numeric parameter.
8	SQLite UPPER function The SQLite UPPER function converts a string to uppercase.
9	SQLite LOWER function The SQLite LOWER function converts a string to lowercase letters.
10	SQLite LENGTH function The SQLite length function returns the lengths of the strings.

The beginning of SQLite