sqlite基础使用

基础使用

sqlite> .help
sqlite> .exit
[root@datapipline as4k]# 

[root@datapipline as4k]# sqlite3  dpmonitor.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /as4k/dpmonitor.db                                        
sqlite> 

创建数据库
sqlite3 DatabaseName.db .databases

[root@datapipline as4k]# sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -mmap N              default mmap size set to N
   -nullvalue TEXT      set text string for NULL values. Default ''
   -separator SEP       set output field separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS

sqlite3 dpmonitor.db

CREATE TABLE [dptask] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[taskid] INTEGER  NULL
)

创建表

sqlite3 dpmonitor.db "CREATE TABLE [dptask] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, [taskid] INTEGER  NULL)"

sqlite3 dpmonitor.db    "CREATE TABLE DEPARTMENT2(ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL)"

查看该库有几个表
sqlite3 dpmonitor.db .tables

查个某个表的详细信息
[root@datapipline as4k]# sqlite3 dpmonitor.db ".schema dptask"
CREATE TABLE [dptask] ([id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, [taskid] INTEGER  NULL);

删除表

DROP TABLE database_name.table_name;
sqlite3 dpmonitor.db .tables
sqlite3 dpmonitor.db "DROP TABLE DEPARTMENT2;"
sqlite3 dpmonitor.db .tables

插入数据

INSERT INTO dptask(taskid, webStatus, sourceStatus, sinkStatus, webDpsysCmp) VALUES (9, 'run', 'run', 'run', 'ok')
sqlite3 dpmonitor.db "INSERT INTO dptask(taskid, webStatus, sourceStatus, sinkStatus, webDpsysCmp) VALUES (9, 'run', 'run', 'run', 'ok')"

查看数据

SELECT column1, column2, columnN FROM table_name;
sqlite3 dpmonitor.db "SELECT * FROM dptask"

sqlite3  dpmonitor.db "SELECT webStatus FROM dptask"

输出对齐

.header on
.mode column 

sqlite> .header on
sqlite> .mode column
sqlite> select * from dptask;
id          taskid      webStatus   sourceStatus  sinkStatus  webDpsysCmp  sourceCntName
----------  ----------  ----------  ------------  ----------  -----------  -------------
1           9           ACTIVE      RUNNING       run         ok                        
2           8           ACTIVE      RUNNING       run         ok                        
3           7           ACTIVE      RUNNING       run         ok                        
4           6           ACTIVE      RUNNING       run         ok                        
5           5           PAUSED      PAUSED        run         ok                        
6           4           PAUSED      PAUSED        run         ok                        
7           3           PAUSED      PAUSED        run         ok                        
8           2           PAUSED      PAUSED        run         ok                        
sqlite> 


[root@datapipline as4k]# sqlite3  -header dpmonitor.db "SELECT * FROM dptask" | column -t -s "|"
id  taskid  webStatus  sourceStatus  sinkStatus  webDpsysCmp  sourceCntName                        sinkCntName
1   9       ACTIVE     RUNNING       RUNNING     ok           dbz-mysql-connector-dptask_9_1       dp-tidb-connector-dptask_9_1
2   8       ACTIVE     RUNNING       RUNNING     ok           dp-mysql-batch-connector-dptask_8_1  dp-tidb-connector-dptask_8_1
3   7       ACTIVE     RUNNING       RUNNING     ok           dp-mysql-batch-connector-dptask_7_1  dp-tidb-connector-dptask_7_1
4   6       ACTIVE     RUNNING       RUNNING     ok           dbz-mysql-connector-dptask_6_1       dp-tidb-connector-dptask_6_1
5   5       PAUSED     PAUSED        PAUSED      ok           dp-mysql-batch-connector-dptask_5_1  dp-tidb-connector-dptask_5_1
6   4       PAUSED     PAUSED        PAUSED      ok           dp-mysql-batch-connector-dptask_4_1  dp-tidb-connector-dptask_4_1
7   3       PAUSED     PAUSED        PAUSED      ok           dp-mysql-batch-connector-dptask_3_1  dp-tidb-connector-dptask_3_1
8   2       PAUSED     PAUSED        PAUSED      ok           dbz-mysql-connector-dptask_2_1       dp-tidb-connector-dptask_2_1


sqlite3  -header dpmonitor.db "SELECT * FROM pipelineStartEvent where taskid=11" | column -t -s "|"

sqlite3  -header dpmonitor.db "SELECT * FROM heartbeat" | column -t -s "|"


sqlite3  -header dpmonitor.db "SELECT * FROM dptask where taskid=${cTaskId} order by id desc" | column -t -s "|" | head >> $FILE8

test

sqlite3 dpmonitor.db "DROP TABLE dptask"
sqlite3 dpmonitor.db "CREATE TABLE [dptask] (
[id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[taskid] INTEGER  NULL,
[webStatus] TEXT  NULL,
[sourceStatus] TEXT  NULL,
[sinkStatus] TEXT  NULL,
[webDpsysCmp] TEXT  NULL
)"