cmd下sqlite3使用指南

1、进入数据库 .db文件所在目录，如‪D:\microblog\app.db

C:\Users\Administrator>d:

D:\>cd D:\microblog

D:\microblog>

2、建立、并进入数据库 sqlite3 app.db

D:\microblog>sqlite3 app.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite>

3、查看数据库中的表 .tables

sqlite> .tables
alembic_version  post             user

4、创建一个结构，语法如下：

create table testTabel( ID INT PRIMARY KEY NOT NULL, 
DEPT CHAR(50) NOT NULL, 
EMP_ID INT NOT NULL);

5、查看表格结构 .schema post

sqlite> .schema post
CREATE TABLE post (
        id INTEGER NOT NULL,
        body VARCHAR(140),
        timestamp DATETIME,
        user_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE INDEX ix_post_timestamp ON post (timestamp);

6、查看某个表的信息 select * from user;

sqlite> select * from user;
1|susan|susan@example.com|pbkdf2:sha256:50000$OONOkVyy$8d008c6647ab95a5793cf60bf57eaa3bb1123d6e5b3135c5cc5e42e02eddae32||2018-08-11 11:02:54.902074
2|belen|belen@example.com|pbkdf2:sha256:50000$PEDt5NxS$cf6c958c97b6ad28d9495d138cb5a310f6f2389534b0cafa3002dd3cec9af9d1|学习Flask超级教程，Python Web开发学习，坚持！|2018-08-11 11:21:03.778628

7、也可以.dump来查看某个表的信息 .dump user

sqlite> .dump user
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (
        id INTEGER NOT NULL,
        username VARCHAR(64),
        email VARCHAR(120),
        password_hash VARCHAR(128), about_me VARCHAR(140), last_seen DATETIME,
        PRIMARY KEY (id)
);
INSERT INTO "user" VALUES(1,'susan','susan@example.com','pbkdf2:sha256:50000$OONOkVyy$8d008c6647ab95a5793cf60bf57eaa3bb1123d6e5b3135c5cc5e42e02eddae32',NULL,'2018-08-11 11:02:54.902074');
INSERT INTO "user" VALUES(2,'belen','belen@example.com','pbkdf2:sha256:50000$PEDt5NxS$cf6c958c97b6ad28d9495d138cb5a310f6f2389534b0cafa3002dd3cec9af9d1','学习Flask超级教程，Python Web开发学习，坚持！','2018-08-11 11:21:03.778628');
CREATE UNIQUE INDEX ix_user_email ON user (email);
CREATE UNIQUE INDEX ix_user_username ON user (username);
COMMIT;

8、查询所有表名信息 select * from sqlite_master;

sqlite> select * from sqlite_master;
table|alembic_version|alembic_version|2|CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL,
        CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)
index|sqlite_autoindex_alembic_version_1|alembic_version|3|
table|user|user|4|CREATE TABLE user (
        id INTEGER NOT NULL,
        username VARCHAR(64),
        email VARCHAR(120),
        password_hash VARCHAR(128), about_me VARCHAR(140), last_seen DATETIME,
        PRIMARY KEY (id)
)
index|ix_user_email|user|5|CREATE UNIQUE INDEX ix_user_email ON user (email)
index|ix_user_username|user|6|CREATE UNIQUE INDEX ix_user_username ON user (username)
table|post|post|7|CREATE TABLE post (
        id INTEGER NOT NULL,
        body VARCHAR(140),
        timestamp DATETIME,
        user_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user (id)
)
index|ix_post_timestamp|post|8|CREATE INDEX ix_post_timestamp ON post (timestamp)

SQLite数据库中的信息存在于一个内置表sqlite_master中，在查询器中可以用:select * from sqlite_master来查看。如果只要列出所有表名的话，则只要一个语句:SELECT name FROM sqlite_master WHERE type='table' order by name，因为表的列type固定为table。
9、 退出sqlite语句 .quit

sqlite> .quit

D:\microblog>

参考：
SQLite官方文档