SQLite database usage

I. Install SQLite3 method 1. Character interface sudoapt-getinstallsqlite32, graphic interface sudoapt-getinstallsqliteman 2. SQLite data type SQLite has the following five basic data types: 1. integer: A signed integer (up to 64 bits ). 2. real: The floating point type represented by 8 bytes. 3. text: character

1. install SQLite3 method 1. Character interface sudo apt-get install sqlite3 2. graphic interface sudo apt-get install sqliteman 2. SQLite data type SQLite has the following five basic data types: 1. integer: A signed integer (up to 64 bits ). 2. real: The floating point type represented by 8 bytes. 3. text: character

1. Install SQLite3

1. Character Interface

Sudo apt-get install sqlite3

2. Graphic Interface

Sudo apt-get install sqliteman

Ii. SQLite Data Type

SQLite has the following five basic data types:

1. integer: A signed integer (up to 64 bits ).

2. real: The floating point type represented by 8 bytes.

3, text: character type, support a variety of encoding (such as UTF-8, UTF-16), unlimited size.

4. blob: any type of data with unlimited size.

5. null: null.

Iii. SQLite command

1. Create and open a database:

Sqlite3 *. db

Tip: When the *. db file does not exist, sqlite will create and open the database file. When the *. db file exists, sqlite will open the database file.

2. Database exit command:

. Quit or. exit

Note: SQL statement format: All SQL statements end with a semicolon. SQL statements are case insensitive. The two minus signs "--" represent comments.

3. create a table: create statement Syntax:

Create table Name (column name 1 data type, column name 2 data type, column name 3 data type ,...); create a table. The table contains three columns: "id", "name", and "addr ".

Enter create table persons (id integer, name text, addr text) in the terminal );

4. create a table: create Statement (set the primary key)

When designing a table with sqlite, you can use primary key to manually set the primary key (integer type) for each table. The column data set as the primary key cannot be repeated. Syntax:

Create table Name (column name 1 data type primary key, column name 2 data type, column name 3 data type ,...);

Enter create table persons (id integer primary key, name text, addr text) in the terminal );

5. view the table:. tables view the structure of the data table:. schema

6. modify a table: alter statement

Add or delete columns in an existing table. Syntax: (add or delete-sqlite3 not supported currently)

Alter table name add column name data type;

Enter alter table persons add sex text in the terminal;

7. delete a table: drop table statement

Used to delete a table (the table structure, attributes, and table indexes will also be deleted) Syntax: drop table name;

Enter drop table persons on the terminal;

8. Insert a new row: The inser into Statement (assign values to all columns) 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.

Enter create table persons (id integer, name text, addr text) in the terminal );

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

9. Insert a new row: The inser into Statement (partial assignment) assigns values to some columns in a row. Syntax: insert into Table Name (column name 1, column name 2,...) values (column value 1, column value 2 ,...);

Enter insert into persons (id, name) values (1, 'Peter ') in the terminal ');

10. Modify the data in the table: update statement

Use where to search for one or more rows based on matching conditions, and modify the column value of the corresponding row in the table based on the search results (the column name is specified for the modification ).

Syntax: update table name set column 1 = value 1 [, column 2 = value 2,...] [matching condition];

Note: When multiple columns and rows in the Table Meet the matching conditions, the corresponding multiple rows are modified. If the matching condition is null, all are matched.

11. Matching: where clause

The where clause specifies the matching conditions: equal to (=), not equal to (<>), greater than (>), less than (<), and greater than or equal to (> =), less than or equal to (<= ).

Matching condition Syntax: (basic) where column name operator column Value

Enter: update persons set id = 2, addr = 'tianjing 'where name = 'Peter ';

12. delete table data: delete statement

Use where to search for one or more rows based on matching conditions, and delete the rows in the table based on the search results. Syntax: delete from table name [matching condition];

Note: When multiple columns and rows in the Table Meet the matching conditions, the corresponding multiple rows are deleted.

On the terminal, enter delete from persons where name = 'Peter ';

13. Query: select statement (basic)

Select data from the table, and the results are stored in a result table (called a result set ). Syntax:

1. select * from table name [matching condition];

2. select column name 1 [, column name 2,...] from table name [matching condition];

Tip: The asterisk (*) is used to select wildcards for all columns.

Enter insert into persons values (1, 'Peter ', 'tianjing') on the terminal ');

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. Matching condition Syntax: where column name in (column value 1, column value 2,...) Example:

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 ,...)

Input in the terminal: 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 the where substatement (the relationship between multiple conditions is the relation between them ). Matching condition Syntax: where column 1 = value 1 [and column 2 = value 2 and...] example:

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...];

Input in the terminal: 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 the where substatement (the relationship between multiple conditions is or ). Matching condition Syntax: where column 1 = value 1 [or column 2 = value 2 or...] example:

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 name column 1 = value 1 [or column 2 = value 2 or...];

Input in the terminal: select * from persons where addr = 'beijing' or addr = 'hebei ';

17. The between A and B operator selects A data range between A and B. These values can be numerical values, text values, or dates. Matching condition Syntax: where column name between A and B example:

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: strings are matched in ascii order.

Input in the terminal: select * from persons where id between 1 and 3;

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

18. like is used for fuzzy search. Matching condition Syntax: where column name like column Value

1. If the column value is a number, it is equivalent to column name = column value.

2. If the column value is a string, the wildcard "%" can be used to represent one or more missing characters ).

Input in the terminal: select * from persons where id like 3;

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

19. not can retrieve the complement of the original result set. Matching 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 not like column Value

Input in the terminal: select * from persons where id not in (1 );

Select * from persons where addr not like '% jing % ';

20. The order by statement sorts the result set based on the specified column. The result set is sorted in ascending order by default. You can use the desc keyword to sort the result set in descending order. Example:

Ascending order: select * from table name order by column name;

Descending order: select * from table name order by column name desc;

Input in the terminal: select * from persons order by name;

Select * from persons order by id;

Select * from persons order by id desc;

Iv. SQLite C language programming

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

Function: Open the database.

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

Sqlite3: Database identifier. This struct is the database operation handle. This handle allows you to perform operations on database files.

Return Value: SQLITE_ OK is returned for success, and non-SQLITE_ OK is returned for failure.

2. int sqlite3_close (sqlite3 * db );

Function: disables the database and releases the resources applied for when the database is opened.

Parameter: db: Database ID.

Return Value: SQLITE_ OK is returned successfully. Non-SQLITE_ OK is returned for failure.

Note: sqlite3 uses two libraries: pthread and dl. Therefore,-lpthread and-ldl should be added to the link.

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

Function: Execute the SQL statement to which the SQL statement points. If the result set is not empty, the function calls the function pointed to by the function pointer callback.

Parameter: db: Database ID.

SQL: An SQL statement (one or more) ending.

Callback: the callback function pointer. After this statement is executed, sqlite3 will call the function you provided.

Arg: The parameter passed to the callback function when sqlite3_exec is executed.

Errmsg: the address that stores the error message. You can check this pointer after the execution fails.

Print error message: 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. When the sqlite3_exec function executes an SQL statement and the result set is not empty, the sqlite3_exec function automatically calls this function, each time you call this function, a row of information about the result set is sent to this function.

Parameter: para: The parameter that sqlite3_exec sends to this function. para is an address of any data type.

N_column: Number of columns in the result set.

Column_value: the address of the pointer array, which stores the first address of each column value in a row.

Column_name: the address of the pointer array, which stores the first address of the column name corresponding to each column value in a row.

Returned value: If the value is not 0, sqlite3_exec is notified to terminate the callback.

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

Function: Execute the SQL statement to which the SQL statement points. The function saves the data address of the result set in the function parameters.

Parameter: db: Database ID.

SQL: An SQL statement (one or more) ending.

Resultp: the address of the pointer array, which records the data of the result set. Memory layout: First stores the column names of each column, and then the values of each column in each row.

Nrow: number of rows in the result set (excluding column names ).

Ncolumn: Number of columns in the result set.

Errmsg: error message.

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

Function: releases the memory allocated by sqlite3_get_table.

Parameter: the first address of the result set data.