SQLite Comprehensive Learning (ii)

PS One sentence: Eventually choose Csdn to organize the publication of the knowledge points of these years, the article parallel migration to CSDN. Because CSDN also support markdown grammar, Ah!

"Artisan Joshui Http://blog.csdn.net/yanbober"

This article continues to receive an article, read the previous "SQLite comprehensive study (i)"

SQLite CREATE Database

To create the database syntax:

sqlite3 DatabaseName.db

An example is shown below:

SQLite Additional Database

Suppose you want to use either of these when there are multiple databases available at the same time. SQLite's attach dtabase statement is used to select a particular database, and all SQLite statements will be executed under the attached database after the command is used. The ATTACH database statement adds another file to the current connection, and if the file name is ": Memory:", we can treat it as a memory database and the memory database cannot be persisted to the disk file. If you manipulate a table in the attached database, you need to add the database name, such as Dbname.table_name, before the table name. Finally, it is necessary to note that if a transaction contains multiple attached database operations, the transaction is still atomic.

Additional database syntax (the value of alias-name cannot be either main or temp, System):

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

An example is shown below:

sqlite Separate database

SQLite's detach dtabase statement is used to detach and dissociate a named database from a database connection that was previously appended using the Attach statement. If the same database file has been appended with more than one alias, the DETACH command will only break the connection for the given name, while the remaining remains valid. You cannot detach the main or temp database. If the database is in memory or a staging database, the database will be destroyed and the content will be lost.

Detach Database Syntax:

DETACH DATABASE ‘Alias-Name‘;

An example is shown below:

SQLite CREATE TABLE

The SQLite CREATE TABLE statement is used for creating a new table in any given database. Create a basic table that involves naming tables, defining columns, and data types for each column.

To create a table syntax:

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

An example is shown below:

SQLite Delete Table

The drop TABLE statement from SQLite is used to delete the table definition and all its related data, indexes, triggers, constraints, and permission specifications for the table. Use this command with special care, because once a table is deleted, all information in the table will be lost forever.

Delete Table Syntax:

DROP TABLE database_name.table_name;

An example is shown below:

SQLite INSERT statement

The INSERT INTO statement for SQLite is used to add new rows of data to a table in the database.
Insert statement Syntax:

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

If you want to add values for all the columns in the table, you can also not need to specify the column names in the SQLite query. However, make sure that the order of values is consistent with the order of the columns in the table.

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

You can also populate the data to another table by using a SELECT statement on a table that has a set of fields (one table is used to populate the other table). Here's the syntax:

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

An example is shown below:

SQLite SELECT statement

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.

SELECT statement syntax:

SELECT column1, column2, columnN FROM table_name;

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

SELECT * FROM table_name;

An example is shown below:

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 the operator
/	Left operand divided by right operand
%	Remainder after the left operand divided by the right number of operands

This is too small for pediatrics to show examples.

SQLite comparison Operators

operator	Description
==	Check that the values of the two operands are equal, and if they are equal, the condition is true.
=	Check that the values of the two operands are equal, and if they are equal, the condition is true.
!=	Check that the values of the two operands are equal, and the condition is true if they are not equal.
<>	Check that the values of the two operands are equal, and the condition is true if they are not equal.
>	Checks if the value of the left operand is greater than the value of the right operand, and if so, the condition is true.
<	Checks if the value of the left operand is less than the value of the right operand, or if the condition 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.
<=	Checks if the value of the left operand is less than or equal to the right operand, and if so the condition 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.
!>	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.

This too small pediatrics does not demonstrate an example, in conjunction with where statement use on the line.

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 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! =.
\\(Two vertical line)	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).

An example is shown below:

SQLite bitwise operator

The bitwise operators supported by the SQLite language are listed in the following table.

operator	Description
&	Binary and operator.
/(One vertical line)	Binary or operator.
~	The binary complement operator is a unary operator with a "flip" bit effect.
<<	The value of the left operand moves the number of digits specified by the right operand to the left.
>>	The right operand value shifts the right operand to the specified number of digits.

The example does not show.

SQLite Boolean expression

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

The use of the SQLite Boolean expression is shown below:

sqlite Numeric Expression

Numeric expressions are used to perform any mathematical operation in a query.

Grammar:

SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;

The following example:

sqlite Date Expression

Date expressions return the current system date and time values, which are used for various data operations.

Grammar:

SELECT CURRENT_TIMESTAMP;

return Result:
Current_timestamp = 2013-03-17 10:43:35

Summary

At this point, the basic use of SQLite has been considered possible. More knowledge about SQLite the next one will be explained in detail. Read the next article on ... Wait

"Artisan Joshui Http://blog.csdn.net/yanbober"

SQLite Comprehensive Learning (ii)