Basic use of sqlite in Ubuntu and problems encountered by newbies

Hits: 0

[Download and use of sqlite] in Ubuntu

1. Download

2. Create a table

After entering this mode, if you want to exit, you can execute

​ .quit command

View the structure diagram of the table command:

​ .[schema]

command to view table

​ .table

3. Other commands Database system commands:

Use .help to see all system commands. All start with . The sql command is no different from other [QML .]

Basic sql commands:

//Create table
 create  table stu( id  Integer , name  varchar , score Integer );
//insert all fields
insert  into stu values ​​( 1001 , 'Zhang San' , 18 );
//insert some fields
insert  into stu ( name ,score) values ​​( "Li Si" , 100 )

//Basic query can use where   and  or etc.
 select  id  from stu where  name = 'Zhang San'  and score = 100 ;

//other commands
update, delete

//Modify the table to add a column
alter table stu add column address char; 

//sqlite3 does not support deleting a column, but it can copy a customized new table. Methods as below:
create table stu as select id,name,socore from stu;
// delete the original table
drop table stu
//change table name
alter  table stu1 rename  to stu;

SQLite programming interface (C-like language)

serial number API & Description
1 * int sqlite3_open(const char *filename, sqlite3 * ppDb) This routine opens a connection to a SQLite database file and returns a database connection object for other SQLite programs. If the filename parameter is NULL or ‘:memory:’, then sqlite3_open() will create an in-memory database in RAM, which will only last for the duration of the session. If the filename filename is not NULL, then sqlite3_open() will attempt to open the database file using this parameter value. If a file of that name does not exist, sqlite3_open() will create a new database file of that name and open it. ppDb is the sqlite handle pointer return value type int, if the return is successful, it will be 0, if it fails, it will return an error code (non-zero value)
2 * int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char * errmsg) This routine provides a shortcut for executing SQL commands, which are provided by the sql parameter and can be composed of multiple SQL commands. Here, the first parameter sqlite3 is the open database object, sqlite_callback is a callback with data as its first parameter, and errmsg will be returned to get any errors generated by the program. The sqlite3_exec() program parses and executes each command given by the sql parameter until the end of the string or until an error is encountered.
3 const sqlite3_close(sqlite3*) This routine closes the database connection previously opened by calling sqlite3_open(). All connection-related statements should complete before the connection is closed. If there are still queries that have not completed, sqlite3_close() will return a SQLITE_BUSY forbidding close error message.

Other language interfaces can refer to

​ runoob.com

SQLite API URL:

​ https://tool.oschina.net/apidocs/apidoc?api=sqlite

​ https://tool.oschina.net/apidocs/apidoc?api=sqlite

Problems encountered:

  • Open the database again and cannot see the previous sqlite3 database library: (test.db)

    ​ First. quit to exit the database and then execute:

sqlite3 test.db #Like the command to create a library, if there is this library for direct use, if not, create it. 
#Then execute .database to see the previously created library.

sqlite> .database
seq name file

0 main /root/test.db
sqlite> .schema
CREATE TABLE TABLE_PRO(PROJECT VARCHAR,TASK VARCHAR, address varchar);

You may also like...

Leave a Reply

Your email address will not be published.