Comprehensive Sqlite Learning (2) and sqlite Learning

Comprehensive Sqlite Learning (2) and sqlite Learning

Finally, CSDN was chosen to sort out the knowledge points published over the past few years. This article was migrated to CSDN in parallel. Because CSDN also supports the MarkDown syntax, it's awesome!

[Craftsman's water: http://blog.csdn.net/yanbober]

This article continues with the previous article. Read the previous article Sqlite comprehensive learning (1).

Create a database using SQLite

Database creation Syntax:

sqlite3 DatabaseName.db

An instance is shown as follows:

SQLite additional database

Assume that multiple databases are available at the same time, and you want to use any of them. The attach dtabase Statement of SQLite is used to select a specific database. After this command is used, all SQLite statements will be executed under the attached database. The attach database statement adds another DATABASE file to the current connection. If the file name is ": memory:", we can regard it as a memory DATABASE, and the memory DATABASE cannot be persisted to the disk file. If you operate a table in the Attached database, you must add the database name before the table name, for example, dbname. table_name. It should be noted that if a transaction contains multiple Attached database operations, the transaction is still atomic.

Additional database syntax (the value of Alias-Name cannot be "main" or "temp ):

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

An instance is shown as follows:

SQLite database Separation

The detach dtabase Statement of SQLite is used to separate and DETACH a named database from a database connection. The connection was previously appended using the ATTACH statement. If the same database file has been attached with multiple aliases, the DETACH command will only disconnect the specified name, while the remaining are still valid. You cannot separate the main or temp database. If the database is in the memory or temporary database, the database will be destroyed and the content will be lost.

Database separation Syntax:

DETACH DATABASE 'Alias-Name';

An instance is shown as follows:

Create a table using SQLite

The create table statement of SQLite is used to CREATE a new TABLE in any given database. Creating a basic table involves naming tables, defining columns, and Data Types of each column.

Table creation Syntax:

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

An instance is shown as follows:

SQLite Delete table

The drop table statement of SQLite is used to delete TABLE definitions and all related data, indexes, triggers, constraints, and permission specifications of the TABLE. Note that once a table is deleted, all information in the table will be lost forever.

Delete table Syntax:

DROP TABLE database_name.table_name;

An instance is shown as follows:

SQLite Insert statement

The insert into Statement of SQLite is used to add new data rows to a table in the database.
Insert statement Syntax:

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

To add values to all columns in the table, you do not need to specify the column name in the SQLite query. Make sure that the order of values is the same as that of columns in the table.

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

You can also use the select statement on a table with a set of fields to fill data in another table (use one table to fill another table ). The following is the Syntax:

INSERT INTO first_table_name [(column1, column2, ...columnN)] SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition];

An instance is shown as follows:

SQLite Select statement

The SELECT statement of SQLite is used to obtain data from the SQLite database table and return data in the form of a result table. These result tables are also called result sets.

Select statement Syntax:

SELECT column1, column2, columnN FROM table_name;

If you want to obtain all available fields, you can use the following syntax:

SELECT * FROM table_name;

An instance is shown as follows:

SQLite Arithmetic Operators

Operator	Description
+	Add the values on both sides of the operator
-	Left operand minus right operand
*	Multiply the values on both sides of an operator
/	Left operand divided by right operand
%	Returns the remainder after dividing the left operand by the right operand.

This is not a demo for pediatrics.

SQLite comparison operator

Operator	Description
=	Check whether the values of the two operands are equal. If they are equal, the condition is true.
=	Check whether the values of the two operands are equal. If they are equal, the condition is true.
! =	Check whether the values of the two operands are equal. If they are not equal, the condition is true.
<>	Check whether the values of the two operands are equal. If they are not equal, the condition is true.
>	Check whether the value of the left operand is greater than the value of the right operand. If yes, the condition is true.
<	Check whether the value of the left operand is smaller than the value of the right operand. If yes, the condition is true.
>=	Check whether the value of the left operand is greater than or equal to the value of the right operand. If yes, the condition is true.
<=	Check whether the value of the left operand is less than or equal to the value of the right operand. If yes, the condition is true.
! <	Check whether the value of the left operand is not less than the value of the right operand. If yes, the condition is true.
!>	Check whether the value of the left operand is not greater than the value of the right operand. If yes, the condition is true.

This is not an example in Pediatrics. It can be used in combination with the where statement.

SQLite logical operators

Operator	Description
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 values in a range of values within the given minimum and maximum values.
EXISTS	The EXISTS operator is used to search for the existence of rows in a specified table that meets certain conditions.
IN	The IN operator is used to compare a value with a series of values IN a specified list.
NOT IN	The opposite of the IN operator, used to compare a value with a value that is not IN a series of specified lists.
LIKE	The LIKE operator is used to compare a value with a similar value using the wildcard operator.
GLOB	The GLOB operator is used to compare a value with a similar value using the wildcard operator. GLOB differs from LIKE in that it is case sensitive.
NOT	NOT operator is the opposite of the logical operators used. For example, not exists, not between, not in, and so on. It is a negative operator.
OR	The OR operator is used to combine multiple conditions in the WHERE clause of an SQL statement.
IS NULL	The NULL operator is used to compare a value with a NULL value.
IS	The IS operator IS similar to =.
IS NOT	Is not operator and! = Similar.
\\(Two vertical lines)	Connect two different strings to get a new string.
UNIQUE	The UNIQUE operator searches for each row in a specified table to ensure uniqueness (no duplicates ).

An instance is shown as follows:

SQLite Operator

The following table lists the bitwise operators supported by SQLite.

Operator	Description
&	Binary AND operator.
/(A vertical line)	Binary OR operator.
~	The binary complement operator is a unary operator and has the "flip" bitwise effect.
<	The value of the left operand moves to the number of digits specified by the right operand.
>>	The value of the right operand moves the number of digits specified by the right operand to the right.

The example will not be demonstrated.

SQLite Boolean expression

The Boolean expression of SQLite obtains data based on matching a single value.

The usage of the SQLite Boolean expression is as follows:

SQLite numeric expression

A numeric expression is used to perform any mathematical operations in a query.

Syntax:

SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;

Example:

SQLite date expression

The date expression returns the current system date and time value. These expressions are used for various data operations.

Syntax:

SELECT CURRENT_TIMESTAMP;

Returned results:
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite Where clause

The WHERE clause of SQLite is used to specify conditions for obtaining data from one or more tables. If the given condition is true, a specific value is returned from the table. You can use the WHERE clause to filter records and obtain only the required records. The WHERE clause is not only available in SELECT statements, but also in UPDATE and DELETE statements.

Syntax:

SELECT column1, column2, columnN FROM table_name WHERE [condition]

There are many examples of where clauses.

SQLite and or operators

The and or operators of SQLite are used to compile multiple conditions to narrow down the data selected in the SQLite statement. These two operators are called JOIN operators. These operators provide the possibility of multiple comparisons between different operators in the same SQLite statement.

Syntax:

SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN];

There are many examples of and or.

SQLite Update statement

SQLite UPDATE query is used to modify existing records in the table. You can use the UPDATE query with the WHERE clause to UPDATE the selected rows. Otherwise, all rows will be updated.

Syntax:

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

An instance is shown as follows:

SQLite Delete statement

SQLite's DELETE query is used to DELETE existing records in the table. You can use the DELETE query with the WHERE clause to DELETE selected rows. Otherwise, all records will be deleted.

Syntax:

DELETE FROM table_name WHERE [condition];

This is easy. To clear all data, you can directly: delete from table_name;

SQLite Like clause

The LIKE operator of SQLite is used to match the text value of the specified wildcard pattern. If the search expression matches the pattern expression, the LIKE operator returns true, that is, 1. Here there are two wildcards used with the like operator:

• Percent (%)
• Underline (_)

Percent sign (%) Represents zero, one or more numbers or characters. An underscore (_) represents a single number or character. These symbols can be combined.

Syntax:

SELECT FROM table_name WHERE column LIKE 'XXXX%';SELECT FROM table_name WHERE column LIKE 'XXXX_';

The example above also has the example of using like, which is easier. Both XXXX % and XXXX _ can be used in any combination.

SQLite Glob clause

The GLOB operator of SQLite is used to match the text value of the specified wildcard pattern. If the search expression matches the pattern expression, the GLOB operator returns true, that is, 1. Unlike the LIKE operator, GLOB is case-sensitive. For the following wildcards, it follows the UNIX syntax.

• Asterisk (*)
• Question mark (?)

The asterisk (*) Represents zero, one or more numbers or characters. Question mark (?) Represents a single number or character. These symbols can be combined.

Syntax:

SELECT FROM table_name WHERE column GLOB 'XXXX*';SELECT FROM table_name WHERE column GLOB 'XXXX?';

The example above also uses glob, which is easier. XXXX * And XXXX? Can be used in any combination.

SQLite Limit clause

The LIMIT clause of SQLite is used to LIMIT the number of data returned by the SELECT statement.

Basic syntax for SELECT statements with LIMIT clauses:

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]

Syntax used by the LIMIT clause and the OFFSET clause together:

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]

The following is an example:

SQLite Order By keyword

The order by clause of SQLite is used to sort data in ascending or descending ORDER based on one or more columns.

Syntax:

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

You can use multiple columns in the order by clause. Make sure that the sorting column you are using is in the column list. ASC is in ascending order, and DESC is in descending order.

The following is an example:

SQLite Group By keyword

The group by clause of SQLite is used together with the SELECT statement to GROUP the same data. In the SELECT statement, the group by clause is placed after the WHERE clause and before the order by clause.

Syntax:

SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN;

The group by clause must be placed after the conditions in the WHERE clause and before the order by clause.

The following is an example:

SQLite Having clause

The HAVING clause allows you to specify conditions to filter the grouping results that will appear in the final results. The WHERE clause sets conditions on the selected column, while the HAVING clause sets conditions on the GROUP created BY the group by clause.

The position of the HAVING clause in the SELECT query is as follows:

SELECT FROM WHERE GROUP BY HAVING ORDER BY

In a query, the HAVING clause must be placed after the group by clause and before the order by clause. The syntax of the SELECT statement containing the HAVING clause is as follows:

SELECT column1, column2FROM table1, table2WHERE [ conditions ]GROUP BY column1, column2HAVING [ conditions ]ORDER BY column1, column2;

The following is an example:

SQLite Distinct keyword

The DISTINCT keyword of SQLite is used with the SELECT statement to eliminate all repeated records and obtain only one record. There may be a situation where multiple duplicate records exist in a table. When such a record is extracted, the DISTINCT keyword is particularly meaningful. It only gets the unique record, rather than the duplicate record.

Syntax:

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

The following is an example:

Summary

So far, the basic Sqlite usage is acceptable. More information about Sqlite will be detailed in the next article. Read the next article 《..... Waiting

[Craftsman's water: http://blog.csdn.net/yanbober]