SQLite lesson 1 sqlite3.exe usage tutorial

1). Open opens the database

Example:

Sqlite > .open test.db

Note: > A little bit in the back

2) .tables view the table names contained in the database

Example:

Sqlite > .tables

Note: > A little bit in the back

3) .schema to view the structure of all tables in the database

Example:

Sqlite > .schema students

Note: > A little bit in the back

4) execute sql statement

Example:

Sqlite > select * from students where StudentID = 3

Note: before calling sql, you can format the output.

-displays the column names of the SELECT result set.

-- displays each field as a column.

Set the display width of the first column of the subsequent output to 10. 0.

Sqlite > .header on

Sqlite > .mode column

Sqlite > .width 10

5). Exit exit

6) .explain is ready to show the statements that convert SQL statements to VDBC machine code

Examples are as follows:

Sqlite > .explain

Sqlite > EXPLAIN select * from students

7) on the basis of the above, if the. explain is called

You can check the following simple details. At present, you need to wait until the English version has been translated to understand.

Sqlite > .explain

Sqlite > EXPLAIN QUERY PLAN select * from students

Sele order from data

0 0 0 SCAN TABLE students

Of course, you can see the difference after creating an index on the students table on the following website:

Extracted 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 as follows:

Sele order from data

0 000 SEARCH TABLE student USING INDEX student_index (StudentID=?)

To sum up, this statement shows how the data is queried. Of course, it is only valid when the index is established. Here we can boldly assert: in fact, if the database does not build an index, it is actually scanning the table records of the entire database. If the index is established, the index will be searched. This topic will be analyzed and explained in detail in future articles. And how to build the index correctly from the point of view of the source code, and how to build a clustered index on multiple columns.

Explore

1) the student_index table is not saved in the database, as to where it is waiting to be explored!

Error:no such table student_index

2) whether the index records the number of the corresponding data blocks to speed up the search, what is the saved structure?

Update alias question:

During the current testing, 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

Note that considerable attention should be paid to the execution of sql statements

Matters needing attention

The content encoding format saved in the SQLite database may be UTF-8 or GBK encoding. Start the sqlite.exe program directly to read the contents of the database. At present, when reading the GBK code, it displays garbled codes in Chinese.

Solution: many users want to be able to enter Chinese under the console. They must use the instruction chcp 936 and switch to GBK encoding format before they can enter Chinese normally.

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

3) enter the directory of the sqlite.exe program, start sqlite.exe, and display the contents of the UTF-8 database normally

4) if the database content is GBK encoded, you can use chcp 936.