SQLite3查询表结构

发布时间：2020-12-12 23:49:14 所属栏目：百科来源：网络整理

导读：运行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,UserNametext,EncrypteBox_SrcDisk text,EncrypteBox_SrcName text,En

运行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,UserNametext,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,UserName 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> .dump
BEGIN TRANSACTION;
CREATE TABLE AuditDate (Time text,FileExplain text);
COMMIT;
将AuditDate表的表结构输出：

sqlite> .dump AuditDate
BEGIN TRANSACTION;
CREATE TABLE AuditDate (Time text,En
crypteBox_SrcDisk text,
EncrypteBox_SrcEx text,Encrypt
eBox_DesPath text,FileDesP
ath text,FileExplain text);
COMMIT;

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

sqlite> .output 1.txt

将查询结果输出到屏幕：

sqlite> .output stdout
table|AuditDate|AuditDate|2|CREATE TABLE AuditDate (Time text,Encrypte
Box_SrcPath text,F
ileSrcPath text,Arial; font-size:16px; line-height:26px"> 创建表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>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!