Sqlite3 provides multiple commands to view the schema of a Database
". Tables command to view all the tables in the current database, for example, example 14: SQLite>. tablestbl1tbl2sqlite> ". tables is similar to executing the following statement in list mode: Select name from sqlite_master where type in ('table', 'view ') and name not like 'sqlite _ % 'Union all select name from sqlite_temp_master where type in ('table', 'view') order
1. In fact, if you view the source code of the sqlite3 Program (found in the source tree in the file src/shell. c), you'll find exactly the above query. in addition ,". the tables command can also be followed by a parameter, which is a pattern. In this way, the command only lists the tables that match the table name and the parameter. For example, 14-1: SQLite>. tables. tablesandroid_metadata bookmarks system1_th_devices securesqlite>. tables S %. tables S % secure sqlite_sequence systemsqlite> ". the "indices" command lists all indices (indexes) of a specified table ). The first parameter is the table name. For example, example 15: SQLite>. schema system. schema systemcreate Table System (_ id integer primary key autoincrement, Name text unique on conflict replace, value text); Create index systemindex1 on system (name); SQLite>. tables. tablesandroid_metadata bookmarks system1_th_devices securesqlite>. indices System. indices systemsqlite_autoindex_system_1systemindex1sqlite> ". schema command. If no parameter exists, it displays the create
SQL statement of table and create index. For example, the example 16 ". schema" command can contain a parameter, which is a pattern used to filter tables. Only the SQL statements that meet the conditions and all indexes are displayed. For example, example 15 and example 17. example 16: SQLite>. schema. schemacreate table android_metadata (locale text); Create Table incluth_devices (_ id integer primary key, Name text, ADDR text, channel integer, type Integer ); create Table bookmarks (_ id integer primary key, title text, folder text, intent text, shortcut integer, ordering integer); Create Table secure (_ id integer primary key autoincrement, name text unique on conflict repla Ce, value text); Create Table System (_ id integer primary key autoincrement, Name text unique on conflict replace, value text); Create index bookmarksindex1 on bookmarks (folder ); create index bookmarksindex2 on bookmarks (shortcut); Create index secureindex1 on secure (name); Create index systemindex1 on system (name); SQLite> example 17: SQLite>. schema S %. schema S % create table secure (_ id integer primary key AU Toincrement, Name text unique on conflict replace, value text); Create Table sqlite_sequence (name, SEQ); Create Table System (_ id integer primary key autoincrement, Name text unique on conflict replace, value text); Create index secureindex1 on secure (name); Create index systemindex1 on system (name); SQLite> ". the schema command function is similar to the following statement: Select SQL from (select * From sqlite_master Union all select * From sqlite_t Emp_master) Where type! = 'Meta' order by tbl_name, type DESC, name if you pass a parameter ". schema "to indicate that you only want to get the schema of the table and include the schema of the index, the SQL statement should be as follows: Select SQL from (select * From sqlite_master Union all select * From sqlite_temp_master) Where type! = 'Meta' and SQL not null and name not like 'sqlite _ % 'order by substr (type, 2, 1 ),
Name if you want to ". schema supports parameters. the SQL statement should be as follows: Select SQL from (select * From sqlite_master Union all select * From sqlite_temp_master) Where tbl_name like '% s' and type! = 'Meta' and SQL not null and name not like 'sqlite _ %'
Order by substr (type,), name here "% s" in the SQL statement will be replaced by the parameter you passed in. you can only display a portion of the schema. in fact ". tables also uses this "like" Method for pattern query. The ". Databases" command lists all databases in the current connection. Generally, there must be at least two. One is "Main", the original database opened. the other is "Temp", the database used for temporary tables. there may be additional Databases listed for databases attached using the attach statement. the first output column is the name the database is attached with, and the second column is the filename of the external file. ". the dump command converts the database content into an ASCII text file. This file can be converted back into a database by piping it back into sqlite3. you can use the following command to back up an archival database:
$ Echo '. dump' | sqlite3 ex1 | gzip-C> ex1.dump.gz will produce a file named ex1.dump.gz, which contains all the information for rebuilding the database and rebuilding the database. You only need the following statement: $ zcat ex1.dump.gz | sqlite3 ex2 because the text format is pure SQL, you can use. the dump command imports your database to another common database engine. for example, $ createdb ex2 $ sqlite3 ex1. dump | Psql ex2the ". explain "dot command can be used to set the output mode to" column "and to set the column widths to values that are reasonable for looking at the output of an explain command. the explain command is an SQLite-specific SQL extension that is useful for debugging. if any regular SQL is prefaced by explain, then the SQL command is parsed and analyzed but is not executed. instead, the sequence of virtual machine instructions that wocould have been used to execute the SQL command are returned like a query result. for example:
SQLite>. explainsqlite> explain Delete from tbl1 where two <20; ADDR opcode P1 P2 P3 ---- ------------ ----- ------------------------------------- 0 listopen 0 0 1 open 0 1 tbl1 2 next 0 9 3 field 0 1 1 integer 20 0 5 GE 0 2 6 Key 0 0 7 listwrite 0 0 8 goto 0 2 9 Noop 0 0 10 listrewind 0 0 11 listread 0 14 12 Delete 0 0 13 goto 0 11 14 listclose 0 0the ". timeout "command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. the default value of the timeout is zero so that an error is returned
Immediately if any needed database table or index is locked. ". exit and ". the quite command is used to exit the sqlite3 program. they seem to have no difference in how to use the sqlite3 command using shell scripts. One way is to use the "Echo" or "cat" command to output some sqlite3 commands to a file, then execute the sqlite3 program and use the file as the input stream of sqlite3. This method is good and can be used by many programs. Another method is to use an SQL statement as the second parameter of sqlite3 and execute the SQL operation. For convenience, sqlite3 allows another parameter after the first parameter database name to specify the SQL statement to be executed. If sqlite3 is started with two parameters, the second parameter will be passed to SQLite as an SQL statement.
Library, the returned results will be displayed in the standard output in list mode, and then the sqlite3 program will exit. For example, example 17: # sqlite3/data/COM. android. providers. settings/databases/settings. DB "select * from system; select * from system" uses the SQL statement as the second parameter of sqlite3. This method is mainly used in combination with sqlite3 and other programs (such as "awk. for example, example 18: $ sqlite3 ex1 'select * From tbl1' |> awk '{printf "<tr> <TD> % S <TD> % s \ n ", $1, $2} '<tr> <TD> Hello <TD> 10 <tr> <TD> goodbye <TD> 20 $ the end of an SQL statement of sqlite3 is generally the end of an SQL statement of sqlite3. is a semicolon "; ". however, when you run sqlite3 in shell, you can also use "go"
(Uppercase) or "/" as a sign of the end of an SQL statement. they are used in SQL Server and Oracle respectively. but when sqlite3_exec () cannot use shell, it will first convert them into semicolons ";" and then pass them to the function. compile sqlite3the sqlite3 program is built automatically when you compile the SQLite program is automatically compiled when you compile the SQLite library. just get a copy of the source tree, Run "Configure" and then "make ".
