SQLite3查询表结构

前端之家收集整理的这篇文章主要介绍了SQLite3查询表结构前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

(转载)http://www.jb51.cc/article/p-nvdjkfaj-bdt.html


运行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_DesEx 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 version 3.3.5

Enter ".help" for instructions

将表结构输出

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
sqlite> select * from sqlite_master where type = "table";
table|AuditDate|AuditDate|2|CREATE TABLE AuditDate (Time text,Encrypte
Box_SrcPath text,F

ileSrcPath 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>


//----------------------------------------------------------------------------------------

总结:很多时候,想看看android里面数据库中标的结构,

使用.schema就可以了

猜你在找的Sqlite相关文章