cmd下sqlite3使用指南

电玩女神 2022-05-17 12:46 625阅读 0赞

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数据库**中的信息存在于一个内置表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官方文档][SQLite]

[SQLite]: https://www.sqlite.org/lang.html