Shulou(Shulou.com)05/31 Report--

This article mainly introduces what SQLite is used for. The introduction in the article is very detailed and has certain reference value. Interested friends must read it!

What is SQLite?

SQLite is a lightweight database that is an ACID compliant relational database management system contained in a relatively small C library. It implements a self-sufficient, serverless, zero-configuration, transactional SQL database engine. SQLite's code is in the public domain and therefore free to use for any purpose, commercial or private. SQLite is the most widely deployed database in the world.

SQLite supports Windows/Linux/Unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interfaces. Similarly, compared with Mysql and PostgreSQL, two world-famous open source database management systems, its processing speed is faster than them.

Why SQLite?

SQLite is compact and lightweight, less than 400KiB when fully configured and less than 250KiB when optional features are omitted.

SQLite is self-sufficient and does not require any external dependencies.

SQLite does not require a separate server process or operating system.

SQLite requires no configuration, which means no installation or administration is required.

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

SQLite transactions are fully ACID compliant, allowing safe access from multiple processes or threads.

SQLite is cross-platform and supports many operating systems, such as UNIX (Linux, Mac OS-X, Android, iOS), Windows (Win32, WinCE, WinRT).

Comments on SQlite

SQLite comments are additional comments that can be added to SQLite code to increase readability. They can appear in any white space, including within expressions and in the middle of other SQL statements, but they cannot be nested.

SQL comments start with two consecutive-characters and extend to the next newline or to the end of the input, whichever comes first.

- This is a comment.

Or you can use C-style comments that start with/* and end with */, whichever comes first.

/* This is a comment */Create database

Note: Typically, database names should be unique within an RDBMS.

The syntax is as follows:

sqlite3 DatabaseName.db Attach database

When multiple databases are available at the same time and you want to use only one of them, you can use ATTACH DATABASE to select a specific database. After using this command, all SQLite statements will be executed under the attached database.

The syntax is as follows:

ATTACH DATABASE file_name AS database_name; Detach database

The DETACH DTABASE statement is used to detach and dissociate named databases from a database connection that was previously attached using the ATTACH statement. If multiple aliases have been attached to the same database file, DETACH will disconnect only the one with the given name, while the rest remain valid.

The syntax is as follows:

DETACH DATABASE 'Alias-Name'; Create table

CREATE TABLE creates a new table in any given database. Creating basic tables involves naming tables, defining columns, and the data type for each column.

The syntax is as follows:

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

DROP TABLE is used to delete a table definition and all its associated data, indexes, triggers, constraints, and permission specifications for the table.

The syntax is as follows:

DROP TABLE database_name.table_name; insert data

INSERT INTO is used to add new rows of data to a table in the database.

The syntax is as follows:

Inserts data into the specified column:

INSERT INTO TABLE_NAME [(column1, column2, column3,... columnN)] VALUES (value1, value2, value3,... valueN);

Insert data into all columns

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,... valueN); query statement

SELECT is used to retrieve data from SQLite database tables and return the data as a result table. These result tables are also called result sets.

The syntax is as follows:

--Query all records in a table SELECT * FROM table_name;--Query records in a specified column SELECT column1, column2, columnN FROM table_name; Modify data

UPDATE is used to modify existing records in the table. You can use UPDATE queries with WHERE clauses to modify specified rows, otherwise all rows will be modified.

The syntax is as follows:

UPDATE table_name SET column1 = value1, column2 = value2..., columnN = valueWHERE [condition]; delete data

Delete is used to delete existing records in a table. You can delete selected rows using a Delete query with a WHERE clause, otherwise all records are deleted.

The syntax is as follows:

Delete FROM table_name WHERE [condition];Where clause

The WHERE clause is used to specify conditions for obtaining data from a table or tables. In addition to SELECT statements, of course, can also be used in UPDATE, Delete statements.

The syntax is as follows:

SELECT column1, column2, columnN FROM table_nameWHERE [condition]AND/OR operator

The AND/OR operator is used to compile multiple conditions to narrow down the data selected in a SQLite statement. Also known as join operators.

The syntax is as follows:

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] AND/OR [condition2]... AND/OR [conditionN];Order By clause

The Order By clause is used for sorting, which is divided into ascending and descending sorting. The default is ascending (ASC) and descending is DESC.

The syntax is as follows:

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

The Group By clause is used with SELECT statements to group the same data.

The syntax is as follows:

SELECT column-list FROM table_nameWHERE [ conditions ]GROUP BY column1, column2... columnNDistinct Keyword

The Distinct keyword is used in conjunction with SELECT statements to eliminate all duplicate records and obtain only unique records.

The syntax is as follows:

SELECT DISTINCT column1, column2,... columnN FROM table_nameWHERE [condition]Having clause

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

Unlike the WHERE clause, the HAVING clause sets conditions on the grouping created by the GROUP BY clause.

The syntax is as follows:

SELECT column1, column2, columnN FROM table_nameWHERE [condition] GROUP BY column1, column2... columnNHAVING [condition]ORDER BY column1, column2... columnNLike clause

The Like clause is used to match the literal value of the wildcard specified pattern. The Like statement is case-insensitive.

The syntax is as follows:

SELECT column_list FROM table_nameWHERE column LIKE 'A%'

'A %': Find any value that starts with A.

'% A': Find any value ending in A.

'_A %': Find any value whose second digit is A.

'%A %': Find any value containing A.

'A__B': Find any value of length 4 that starts with A and ends with B.

LIMIT clause

The LIMIT clause is used to limit the amount of data returned by a SELECT statement.

The syntax is as follows:

SELECT column1, column2, columnN FROM table_nameLIMIT [no of rows]

Syntax when the LIMIT clause is used with the OFFSET clause:

SELECT column1, column2, columnN FROM table_nameLIMIT [no of rows] OFFSET [row num]Glob subsentence

Glob operators are literal values used to match wildcard specified patterns. If the search expression matches the pattern expression, the GLOB operator returns true (that is, 1). Unlike the LIKE operator, GLOB is case-sensitive, and for wildcard stars * and question marks? It follows UNIX syntax.

The syntax is as follows:

SELECT FROM table_nameWHERE column GLOB 'A*'

'A*': Find any value that starts with A.

'* A': Find any value ending in A.

'*A*': Find any value that contains A.

'A??? ': Finds any value that starts with A and is at least 4 characters long.

'? A': Find any value whose second digit is A.

'A?? B': Find any value of length 4 that starts with A and ends with B.

The above is "SQLite has what to use" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.