SQLite3查询表结构

运行SQLite3 shell程序：

C:\>SQLite3_3_5.exe

SQLite version 3.3.5

Enter ".help" for instructions

创建表AuditData：

sqlite> create table AuditDate (Time text,      OperateType integer,    UserName text,EncrypteBox_SrcDisk text,EncrypteBox_SrcName text,EncrypteBox_SrcPath text,       EncrypteBox_SrcEx text,EncrypteBox_DesDisk text,EncrypteBox_DesName text,EncrypteBox_DesPath text,EncrypteBox_De*** text,FileName text,FileSrcPath text,FileDesPath text,FileExplain text);

查询表AuditDate的内容：

sqlite> select * from AuditDate

   ...> ;

查询当前数据库下的所有表名称：

sqlite> .table

AuditDate

使用SQL语句查询表结构信息：

sqlite> select * from sqlite_master where type = "table";

table|AuditDate|AuditDate|2|CREATE TABLE AuditDate (Time text,  OperateType integer,    UserName text,EncrypteBox_SrcDisk text,EncrypteBox_SrcName text,EncrypteBox_SrcPath text,       EncrypteBox_SrcEx text,EncrypteBox_DesDisk text,EncrypteBox_DesName text,EncrypteBox_DesPath text,EncrypteBox_De*** text,FileName text,FileSrcPath text,FileDesPath text,FileExplain text)

查看SQLite3帮助命令：
sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds

.width NUM NUM ...     Set column widths for "column" mode

退出SQLite3：

sqlite> .quit

启动SQLite3，创建1.db，并连接到该数据库：

C:\>SQLite3_3_5.exe 1.db
SQLite version 3.3.5

Enter ".help" for instructions

将表结构输出：

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE AuditDate (Time text,      OperateType integer,    UserName text,EncrypteBox_SrcDisk text,EncrypteBox_SrcName text,EncrypteBox_SrcPath text,EncrypteBox_SrcEx text,EncrypteBox_DesDisk text,EncrypteBox_DesName text,EncrypteBox_DesPath text,EncrypteBox_De*** text,FileName text,FileSrcPath text,FileDesPath text,FileExplain text);
COMMIT;

将AuditDate表的表结构输出：

sqlite> .dump AuditDate
BEGIN TRANSACTION;
CREATE TABLE AuditDate (Time text,      OperateType integer,    UserName text,En
crypteBox_SrcDisk text,EncrypteBox_SrcName text,EncrypteBox_SrcPath text,
EncrypteBox_SrcEx text,EncrypteBox_DesDisk text,EncrypteBox_DesName text,Encrypt
eBox_DesPath text,EncrypteBox_De*** text,FileName text,FileSrcPath text,FileDesP
ath text,FileExplain text);
COMMIT;

将查询结果输出到文件1.txt：

sqlite> .output 1.txt

将查询结果输出到屏幕：

sqlite> .output stdout
sqlite> select * from sqlite_master where type = "table";
table|AuditDate|AuditDate|2|CREATE TABLE AuditDate (Time text,  OperateType integer,    UserName text,EncrypteBox_SrcDisk text,EncrypteBox_SrcName text,Encrypte
Box_SrcPath text,       EncrypteBox_SrcEx text,EncrypteBox_DesDisk text,EncrypteBox_DesName text,EncrypteBox_DesPath text,EncrypteBox_De*** text,FileName text,F

ileSrcPath text,FileDesPath text,FileExplain text)

创建表adb，并且向其中插入数据，然后查询该表中字段的数据类型：

sqlite>CREATE TABLE adb (name text)
sqlite> select typeof(name) from adb;
sqlite> insert into adb (name) values ('ddd');
sqlite> select typeof(name) from adb;
text
sqlite>

查看整个数据库的表结构

sqlite> .schema
CREATE TABLE a (time integer not null default current_timestamp,name text);
CREATE TABLE ab (time integer,name text);
CREATE TABLE abc (name text,time integer not null default current_timestamp);
CREATE TABLE abcd (name text,time integer not null default current_timestamp);
CREATE TABLE abcde (name text,time integer );

显示表头：

sqlite> .head on
sqlite> select * from abcde;
name|time
aaa|
sqlite>

查看当前SQlite3运行时的配置信息：

sqlite> .show
     echo: off
  explain: off
  headers: on
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>