SQLite database Use

First, install the SQLite3 method

1. Character interface

sudo apt-get install Sqlite3

2. Graphical interface

sudo apt-get install Sqliteman

Second, SQLite data type

SQLite has the following five basic data types:

1, Integer: Signed integer (up to 64 bits).

2, the Real:8 byte represents the floating-point type.

3, Text: Character type, support many encodings (such as UTF-8, UTF-16), the size is unlimited.

4, Blob: Any type of data, unlimited size.

5, NULL: Indicates a null value.

Third, SQLite command use

1. Create and open the database:

Sqlite3 *.db

Tip: When the *.db file does not exist, SQLite creates and opens the database file. When the *.db file is present, SQLite opens the database file.

2. Exit the database command:

. Quit or. exit

NOTE: SQL statement format: All SQL statements are terminated with a semicolon, and SQL statements are not case-sensitive. Two minus "--" represents a comment.

3. CREATE TABLE: Creation statement syntax:

CREATE TABLE table name (column name 1 data type, column name 2 data type, column name 3 data type, ...); Create a table The table contains 3 columns, the column names are: "id", "name", "addr".

Under Terminal input: CREATE TABLE persons (ID integer, name text, addr text);

4. Creating a Table: Create statement (set Primary key)

When designing a table with SQLite, each table can be manually set by primary key (integer), and the column data set as the primary key cannot be duplicated. Grammar:

CREATE TABLE table name (column name 1 data type primary key, column name 2 data type, column name 3 data type, ...) ；

Under Terminal input: CREATE TABLE persons (ID integer primary key, name text, addr text);

5. View table:. Tables view the structure of the data table:. Schema

6. Modify the table: Alter statement

Add or remove columns from an existing table. Syntax: (add, remove-sqlite3 temporarily not supported)

ALTER TABLE name add column name data type;

Under terminal input: ALTER TABLE persons add sex text;

7. Delete tables: DROP TABLE statement

Syntax for dropping tables (the structure of the table, the properties, and the index of the table will also be deleted): drop table name;

In Terminal input: drop table persons;

8. Insert New line: Inser into statement (all assignments) assigns values to all columns in a row. Syntax: INSERT into table name values (column value 1, column value 2, column value 3, column value 4, ...);

Note: When the column value is a string, add the ' number.

Under Terminal input: CREATE TABLE persons (ID integer, name text, addr text);

Insert into persons values (1, ' Lucy ', ' Beijing ');

9. Insert New line: Inser into statement (partial assignment) assigns a value to some column in a row. Syntax: INSERT into table name (column name 1, column Name 2, ...) VALUES (column value 1, column value 2, ...);

Under terminal input: INSERT into persons (ID, name) VALUES (1, ' Peter ');

10. Modify the data in the table: UPDATE statement

Use where to find one or more rows based on the matching criteria, modify the column values of the corresponding rows in the table based on the results of the lookup (modify which column is specified by the column name).

Syntax: Update table name set column 1 = value 1 [, Column 2 = value 2, ...] [Matching conditions];

Note: When there are multiple columns in the table, and multiple rows match the matching criteria, the corresponding multiline is modified. Matches all if the match condition is empty.

11. Match: WHERE clause

The WHERE clause is used to specify the conditions for matching: equals (=), not equal to (<>), greater than (>), less than (<), greater than or equal (>=), less than or equal (<=).

Match condition Syntax: (base) where Column name operator column value

In terminal input: Update persons set id=2, addr= ' tianjing ' where name= ' Peter ';

12. Delete data from table: DELETE statement

Use where to find one or more rows based on the matching criteria, and delete the rows found in the table based on the results found. Syntax: delete from table name [match condition];

Note: When there are multiple columns in the table, and multiple rows match the matching criteria, the corresponding multiline is deleted.

In Terminal input: Delete from persons where name= ' Peter ';

13. Query: SELECT statement (Base)

When you select data from a table, the results are stored in a result table (called a result set). Grammar:

1. Select * from table name [match condition];

2. Select column name 1[, column Name 2, ...] from table name [match condition];

Tip: An asterisk (*) is a wildcard character that selects all columns.

In terminal input: INSERT into persons values (1, ' Peter ', ' tianjing ');

Insert into persons values (3, ' Bob ', ' Hebei ');

SELECT * from persons;

SELECT * from persons where id=1;

Select name from persons;

Select name from persons where id=1;

14, in allows us to specify multiple values in the WHERE clause. Match condition Syntax: where column name in (column value 1, column value 2, ...) Cases:

1. Select * FROM table name where column name in (value 1, value 2, ...);

2. Select column name 1[, column name 2,...] FROM table name where column name in (column value 1, column value 2, ...)

In Terminal input: SELECT * from persons where ID in (1, 2);

Select name from persons where ID in (1, 2);

15. And can combine two or more conditions in a where sub-statement (the relationship between multiple conditions). Match condition Syntax: where column 1 = value 1 [and column 2 = value 2 and ...] Cases:

1. Select * FROM table name where column 1 = value 1 [and column 2 = value 2 and ...];

2. Select column name 1[, column Name 2, ...] from table name where column 1 = value 1 [and column 2 = value 2 and ...];

In Terminal input: SELECT * from persons where id=1 and Addr= ' Beijing ';

Select name from persons where id=1 and Addr= ' Beijing ';

16, or can combine two or more conditions in a where sub-statement (a relationship between multiple conditions). Match condition Syntax: where column 1 = value 1 [or column 2 = value 2 or ...] Cases:

1. Select * FROM table name where column 1 = value 1 [or column 2 = value 2 or ...];

2. Select column name 1[, column name 2,...] FROM table list 1 = value 1 [or column 2 = value 2 or ...];

In Terminal input: SELECT * from persons where addr= ' Beijing ' or addr= ' Hebei ';

17, operator between A and B will select a range of data between A and B. These values can be numeric, text, or date. Match condition Syntax: where column name between A and B:

1. Select * FROM table name where column name between A and B;

2. Select column name 1[, column name 2,...] FROM table name where column name between A and B;

Note: matching strings is matched in ASCII order.

In Terminal input: SELECT * from persons where ID between 1 and 3;

SELECT * from persons where addr between ' a ' and ' C ';

18, like for Fuzzy Lookup. Match condition Syntax: Where column name like column value

1, if the column value is the number equivalent to column name = column value.

2. If the column value is a string you can use the wildcard "%" to represent the missing characters (one or more).

In Terminal input: SELECT * from persons where ID like 3;

SELECT * from persons where addr like '%jing% ';

19. Not the complement of the original result set is desirable. Match condition Syntax: example:

1, where column name not in (column value 1, column value 2, ...)

2, where not (column 1 = value 1 [and column 2 = value 2 and ...])

3, where not (column 1 = value 1 [or column 2 = value 2 or ...])

4. Where column name not between A and B

5. Where column name is not a like column value

In Terminal input: SELECT * from persons where ID not in (1);

SELECT * from persons where addr isn't like '%jing% ';

20. The ORDER BY statement sorts the result set according to the specified column. By default, the result set is sorted in ascending order, and you can use the DESC keyword to sort the result set in descending sequence. Cases:

Ascending: SELECT * FROM table name order by column name;

Descending: SELECT * from table name order by column name Desc;

In Terminal input: SELECT * from Persons order by name;

SELECT * from persons the Order by ID;

SELECT * from persons order by id DESC;

Four, SQLite C language programming

1, int sqlite3_open (char *db_name,sqlite3 **db);

Function: Open the database.

Parameters: db_name: The database file name, which must be (UTF-8) encoded if the file name contains characters outside the range of the ASCII code table.

Sqlite3: Database identity, which is a database manipulation handle. This handle allows you to manipulate the database file accordingly.

Return value: Successfully returned SQLITE_OK, failed to return non-SQLITE_OK.

2, int sqlite3_close (sqlite3 *db);

Function: Closes the database and frees the resources requested when the database is opened.

Parameter: DB: The identity of the database.

Return value: Sqlite_ok returned successfully. Failed to return non-SQLITE_OK.

Note: Sqlite3 uses two libraries: pthread, DL, so the link should be added-lpthread and-LDL.

3. sqlite3_exec function: int sqlite3_exec (sqlite3 *db, const char *sql,exechandler_t callback,void, char *arg);

Function: Executes the SQL statement that the SQL points to, and if the result set is not empty, the function calls the function pointer callback.

Parameter: DB: The identity of the database.

Sql:sql statement (one or more), with '; ' End.

Callback: is a callback function pointer, and when this statement executes, SQLITE3 will call the function you provided.

ARG: The arguments passed to the callback function when the sqlite3_exec is executed.

ErrMsg: The address where the error message is stored, which can be consulted when execution fails.

Print error message Method: printf ("%s\n", errmsg);

4. Callback function pointer: typedef int (*exechandler_t) (void *para, int n_column, char **column_value,char **column_name);

Function: This function is defined by the user, and when the SQLITE3_EXEC function executes the SQL statement, the result set is not empty and the SQLITE3_EXEC function calls this function automatically, and each time this function is called, a line of information from the result set is passed to this function.

Parameters: Para:sqlite3_exec The arguments passed to this function, para the address of any data type.

N_column: The number of columns in the result set.

Column_value: The address of an array of pointers that holds the first address of each column value in a row of information.

COLUMN_NAME: The address of the pointer array that holds the first address of the column name for each column in a row of information.

Return value: Notifies sqlite3_exec to terminate the callback if it is a value other than 0.

5. sqlite3_get_table function: int sqlite3_get_table (sqlite3 *db, const char *sql,char ***resultp, int *nrow,int *ncolumn,char * * ERRMSG);

Function: Executes the SQL statement that SQL points to, and the function saves the address of the data associated with the result set in the parameters of the function.

Parameter: DB: The identity of the database.

Sql:sql statement (one or more), with '; ' End.

RESULTP: The address of an array of pointers, which records the data of the result set. Memory layout: The column names for each column, followed by the values for each column in each row.

Nrow: The number of rows in the result set (not including the column name).

Ncolumn: The number of columns in the result set.

ErrMsg: Error message.

6, sqlite3_free_table function: void sqlite3_free_table (char **resultp);

Function: Frees the memory allocated by the sqlite3_get_table.

Parameters: The first address of the result set data.

SQLite database Use