SQLite First lesson Sqlite3.exe use tutorial

1). Open the Database

Example:

sqlite>. Open Test.db

Note that:> is a little behind

2). Tables View the table names contained in the database

Example:

Sqlite>. Tables

Note that:> is a little behind

3). Schema View the structure of all tables in the database

Example:

Sqlite>. Schema students

Note that:> is a little behind

4) Execute SQL statement

Example:

Sqlite> SELECT * from students where StudentID = 3;

Note: You can set the result format of the output before calling SQL

-Displays the column name of the select result set.

--Displays the individual fields as columns.

--Set the display width of the first column after output to 10.

Sqlite>. Header on

sqlite>. Mode column

sqlite>. Width 10

5). Exit exits

6). Explain prepare the statement to display the SQL statement converted to VDBC machine code

Examples are as follows:

Sqlite>. Explain

Sqlite> EXPLAIN SELECT * from students;

7) on the basis above, if the. Explain is called.

You can see the simple details below, and now we need to wait until the English version is translated to understand

Sqlite>. Explain

sqlite> EXPLAIN QUERY PLAN SELECT * from students;

Sele Order from data

0 0 0 SCAN TABLE Students

Of course, I saw on the following site the creation of indexes on the students table, and then the difference:

Excerpt from: http://blog.itpub.net/16900201/viewspace-1291550/

Sqlite> CREATE INDEX Student_index on students (StudentID);

sqlite> EXPLAIN QUERY PLAN select * FROM student where Studnetid = 1;

The results are shown below:

Sele Order from data

0 0 0 SEARCH TABLE student USING INDEX student_index (studentid=?)

In summary, the statement shows how the data is queried, of course, only when the index is valid, here we can boldly assert: In fact, if the database is not indexed, in fact, is to scan the entire database table records, if the index will be indexed search, this topic will be in future articles, For detailed analysis, and how to correctly index from the source point of view, and to set up clustered indexes on multiple columns.

Here are some simple explorations:

1) The database does not save student_index this table, as to save where to wait for exploration!!

Error:no such table student_index;

2) does the index record the number of the corresponding data block, to speed up the search, what is the structure of the save??

Update alias Problem:

In the current testing process, it is found that SQLite does not support the use of aliases in update, for example:

Update task as T set t.state = 4 where T.taskid = 65

Description requires considerable attention when executing SQL statements

Precautions

SQLite database saved content encoding format may be UTF-8 or GBK encoding, directly start Sqlite.exe program read the contents of the database, the current reading GBK encoding, display Chinese garbled

Solution: Many users want to be able to input Chinese in the console, must use the instruction Chcp 936, switch to the GBK encoding format, to be able to enter the normal

2) currently view UTF-8 encoded database file, use instruction chcp 65001, switch to UTF-8 coded character set

3) Enter the Sqlite.exe program directory, start Sqlite.exe, this time normal display UTF-8 database content

4) If the database content is GBK encoded, you can use CHCP 936.

SQLite First lesson Sqlite3.exe use tutorial