Sqlite3 installation and basic commands

From: http://hi.baidu.com/vv1133/blog/item/8fae0df29f0cfc18b17ec573.html

 

Install

$ Tar zxvf sqlite-3.3.5.tar.gz

$ Sqlite-3.3.5 CD

$./Configure -- disable-TCL -- prefix =/usr/local/sqlite3/(the subsequent path can also be omitted, that is, it is installed in the default path)

$ Make

$ Make install

Create a database
Use sqlite3.exe in the region. The data creation command is as follows:
Sqlite3 db_name.xx
The database name is arbitrary. After creation, the database is directly entered. If the file exists, the database is opened directly;

 

SQL Instruction format
All SQL commands end with semicolons (;). To improve readability, multiple lines of one command can be edited until a semicolon ends;
Two minus signs (--) in sqlite3 represent annotations, which are ignored by SQLite.

 

Create a table

Enter the following command to create a tab_name table:
Create Table tab_name (field1, field2, field3 ...);
Sqlite3 has no strict requirements on fields. A field can store any type of data, and it will be automatically converted in a timely manner. Of course, you can also define the data type when creating a table.

 

Data Type
Null
Integer
Real
Text
Blob

But in fact, sqlite3 also accepts the following data types:
Smallint is an integer of 16 bits.
An integer of 32-bit interger.
Decimal (P, S) P exact value and the decimal integer of s size. Exact value P refers to the number of digits after the decimal point. If not specified

The system is set to P = 5; S = 0.
Float 32-bit real number.
The real number of the double 64-bit element.
Char (n) n length string, N cannot exceed 254.
A string with an unfixed varchar (n) length and a maximum length of N. N cannot exceed 4000.
Graphic (n) is the same as char (N), but it is measured in double-bytes. N cannot exceed 127. This form supports two character-length fonts, such as Chinese characters.
A dual-character string with a variable vargraphic (n) length and a maximum length of N. N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp includes year, month, day, hour, minute, second, And 1‰ seconds.

View
. Database: displays database information;
. Tables: displays the table name. (It may also be. Table)
The. schema command can be used to view the SQL commands used to create a data table;
. Schema table_name: SQL command used to view the table table_name created;

 

Insert record
Insert into table_name values (field1, field2, field3 ...);

 

Query
Select * From table_name; view all records in the table_name table;
Select * From table_name where field1 = 'xxxxx'; query records that meet the specified conditions;

 

Delete
Drop table_name; delete a table;
Drop index_name; delete an index;

 

Change output format

. Mode list | column | insert | Line | tabs | TCL | CSV
. Separator "X" change the boundary symbol to X

 

Change output
. Output file_name | stdout

Output to file or standard output (default Terminal)

. Databases: List database file names
. Tables? Pattern? List? Pattern? Matched table name
. Import file table:
. Dump? Table? Generate an SQL script to form a database table
. Output Filename: import the output to the specified file.
. Output stdout print the output to the screen
. Mode mode? Table? Set the data output mode (CSV, HTML, TCL...
. Nullvalue string replaces the output null string with the specified string
. Read filename: Execute the SQL statement in the specified file
. Schema? Table? Print the SQL statement used to create a database table
. Separator string replaces the field separator with the specified string
. Show print the settings of all SQLite Environment Variables
. Quit exit command line interface

 

For example, to create an SQLite database that contains a table "tb11" named "ex1", you can do this:

$ Sqlite3 ex1

SQLite version 3.3.17

Enter ". Help" for instructions

SQLite> Create Table tbl1 (one varchar (10), two smallint );

SQLite> insert into tbl1 values ('Hello! ', 10 );

SQLite> insert into tbl1 values ('Goodbye ', 20 );

SQLite> select * From tbl1;

Hello! | 10

Goodbye | 20

SQLite>

You can knock on the file terminator (usually Ctrl + D) or interrupt character (usually Ctrl + C) of your system ). To terminate the sqlite3 program. Are you sure you want to input a semicolon at the end of each SQL statement! The sqlite3 program queries a semicolon to determine the end of an SQL statement. If you omit the semicolon, sqlite3 will give you a continuous command prompt and wait for you to add more text to the current SQL command. This feature allows you to enter multiple SQL statements with multiple rows, for example:

SQLite> Create Table tbl2 (

...> F1 varchar (30) primary key,

...> F2 text,

...> F3 real

...> );

SQLite>

 

Question: query the sqlite_master table

The SQLite database framework is stored in a special table named "sqlite_master. You can query this special table by executing "select" like querying other tables. For example:

$ Sqlite3 ex1

SQLite vresion 3.3.10

Enter ". Help" for instructions

SQLite> select * From sqlite_master;

Type = table

Name = tbl1

Tbl_name = tbl1

Rootpage = 3

SQL = CREATE TABLE tbl1 (one varchar (10), two smallint)

SQLite>

 

However, you cannot execute commands such as drop table, update, insert, or delete in the sqlite_master table. The sqlite_master table is automatically updated when you create, delete, and index a database. You cannot manually change the sqlite_master table.

The structure of the temporary table is not stored in the "sqlite_master" table, because the temporary table is invisible to the application, rather than the table created by the application. The temporary table structure is stored in another table named "sqlite_temp_master. The "sqlite_temp_master" table is the temporary table itself.