SQLite .mode 显示模式


.mode MODE 设置输出模式,MODE 可以是下列之一:
  • csv 逗号分隔的值

  • column 左对齐的列

  • html HTML 的

    代码

  • insert TABLE 表的 SQL 插入(insert)语句

  • line 每行一个值

  • list 由 .separator 字符串分隔的值

  • tabs 由 Tab 分隔的值

  • tcl TCL 列表元素

  • 创建表:

    CREATE TABLE person(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER NOT NULL, PHONE_NUMBER TEXT NOT NULL);
    insert into person(NAME,AGE,PHONE_NUMBER) values('ZHANG SAN', 20, '13012345678');
    insert into person(NAME,AGE,PHONE_NUMBER) values('LI SI', 22, '15055550000');
    insert into person(NAME,AGE,PHONE_NUMBER) values('WANG WU', 24, '17077770000');

    1、默认模式:list,(实际运行结果和说明写的不一样)这里是以 | 分割

    sqlite> .mode
    current output mode: list
    sqlite> select * from person;
    ID|NAME|AGE|PHONE_NUMBER
    1|ZHANG SAN|20|13012345678
    2|LI SI|22|15055550000
    3|WANG WU|24|17077770000

    2、.csv模式,该模式列间以逗号分隔

    sqlite> .mode csv
    sqlite> select * from person;
    ID,NAME,AGE,PHONE_NUMBER
    1,"ZHANG SAN",20,13012345678
    2,"LI SI",22,15055550000
    3,"WANG WU",24,17077770000

    3、column模式,该模式最为常用,每列数据左对齐,关键字和数据用横线分隔,显示起来比较直观清晰

    sqlite> .mode column
    sqlite> select * from person;
    ID  NAME       AGE  PHONE_NUMBER
    --  ---------  ---  ------------
    1   ZHANG SAN  20   13012345678
    2   LI SI      22   15055550000
    3   WANG WU    24   17077770000

    4、html模式 该模式将表格以html格式输出

    sqlite> .mode html
    sqlite> select * from person;
    <TR>
        <TH>IDTH>
        <TH>NAMETH>
        <TH>AGETH>
        <TH>PHONE_NUMBERTH>
    TR>
    <TR>
        <TD>1TD>
        <TD>ZHANG SANTD>
        <TD>20TD>
        <TD>13012345678TD>
    TR>
    <TR>
        <TD>2TD>
        <TD>LI SITD>
        <TD>22TD>
        <TD>15055550000TD>
    TR>
    <TR>
        <TD>3TD>
        <TD>WANG WUTD>
        <TD>24TD>
        <TD>17077770000TD>
    TR>

    5、insert模式 该模式将表中每条数据以SQL语句输出

    sqlite> .mode insert
    sqlite> select * from person;
    INSERT INTO "table"(ID,NAME,AGE,PHONE_NUMBER) VALUES(1,'ZHANG SAN',20,'13012345678');
    INSERT INTO "table"(ID,NAME,AGE,PHONE_NUMBER) VALUES(2,'LI SI',22,'15055550000');
    INSERT INTO "table"(ID,NAME,AGE,PHONE_NUMBER) VALUES(3,'WANG WU',24,'17077770000');

    6、line模式,该模式将每行的每个值用赋值的形式输出(实例较长,只列出部分)

    sqlite> .mode line
    sqlite> select * from person;
              ID = 1
            NAME = ZHANG SAN
             AGE = 20
    PHONE_NUMBER = 13012345678
    
              ID = 2
            NAME = LI SI
             AGE = 22
    PHONE_NUMBER = 15055550000
    
              ID = 3
            NAME = WANG WU
             AGE = 24
    PHONE_NUMBER = 17077770000

    7、tabs模式,该模式列间以tab键分隔,注意:tab键默认占8个字符,故第一行数据的“California”占了两个tab,所以“20000.0”只能在下一个tab中输出,而第二行则是每列数据各占一个tab

    sqlite> .mode tabs
    sqlite> select * from person;
    ID      NAME    AGE     PHONE_NUMBER
    1       ZHANG SAN       20      13012345678
    2       LI SI   22      15055550000
    3       WANG WU 24      17077770000

    8、tcl模式,该模式的关键字和各数据均用双引号引起,列间以空格分隔

    sqlite> .mode tcl
    sqlite> select * from person;
    "ID" "NAME" "AGE" "PHONE_NUMBER"
    "1" "ZHANG SAN" "20" "13012345678"
    "2" "LI SI" "22" "15055550000"
    "3" "WANG WU" "24" "17077770000"