一篇文章带你掌握SQLite3基本用法

.open filename --打开文件
-- 注解
.show --显示SQLite 命令提示符的默认设置
.q --退出
.databases --显示数据库（注：显示打开的数据库）
.help  --帮助
.dump  --导入导出数据库
.tables  --查看表

.open test.db --没有就创建
sqlite3 DatabaseName.db

sqlite3 test.db .dump　> filename --导出
sqlite3 test.db < filename --导入

--注意，在打开数据库时才能操作
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

.schema --注意：打开数据库时才能操作

DROP TABLE database_name.table_name;

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);
 

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, \'Paul\', 32, \'California\', 20000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, \'Allen\', 25, \'Texas\', 15000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, \'Teddy\', 23, \'Norway\', 20000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, \'Mark\', 25, \'Rich-Mond \', 65000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, \'David\', 27, \'Texas\', 85000.00 );
 
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, \'Kim\', 22, \'South-Hall\', 45000.00 );

INSERT INTO COMPANY VALUES (7, \'James\', 24, \'Houston\', 10000.00 );

.header on
.mode column
.timer on　--开启ＣＰＵ定时器
SELECT * FROM table_name;　--显示表table_name

.header on
.mode column
SELECT * FROM COMPANY;

.header on
.mode column
SELECT ID, NAME, SALARY FROM COMPANY;　--只输出ID, NAME和SALARY三列

.width num1,num1,num3....
SELECT * FROM COMPANY;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SELECT * FROM COMPANY WHERE SALARY > 50000;

SELECT * FROM COMPANY WHERE SALARY = 20000;

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

DELETE FROM table_name　WHERE [condition];

DELETE FROM COMPANY WHERE ID = 7;

UPDATE table_name　
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

UPDATE COMPANY SET ADDRESS = \'Texas\' WHERE ID = 6;

UPDATE COMPANY SET ADDRESS = \'Texas\', SALARY = 20000.00;

#include 
#include 
 
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
 
   rc = sqlite3_open(\"test.db\", &db);
 
   if( rc ){
      fprintf(stderr, \"Can\'t open database: %s\\n\", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, \"Opened database successfully\\n\");
   }
   sqlite3_close(db);
}

gcc lianjie.c -l sqlite3

sql = \"CREATE TABLE COMPANY(\"  \\
         \"ID INT PRIMARY KEY     NOT NULL,\" \\
         \"NAME           TEXT    NOT NULL,\" \\
         \"AGE            INT     NOT NULL,\" \\
         \"ADDRESS        CHAR(50),\" \\
         \"SALARY         REAL );\";

#include 
#include 
#include  
 
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i 
 

  sql = \"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \"  \\
         \"VALUES (1, \'Paul\', 32, \'California\', 20000.00 ); \" \\
         \"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \"  \\
         \"VALUES (2, \'Allen\', 25, \'Texas\', 15000.00 ); \"     \\
         \"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)\" \\
         \"VALUES (3, \'Teddy\', 23, \'Norway\', 20000.00 );\" \\
         \"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)\" \\
         \"VALUES (4, \'Mark\', 25, \'Rich-Mond \', 65000.00 );\";

#include 
#include 
#include 
 
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i 
 

typedef int (*sqlite3_callback)(
void*,    /* Data provided in the 4th argument of sqlite3_exec() */
int,      /* The number of columns in row */
char**,   /* An array of strings representing fields in the row */
char**    /* An array of strings representing column names */
);

#include 
#include 
#include 
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, \"%s: \", (const char*)data);
   for(i=0; i 
 

sql = \"DELETE from COMPANY where ID=2; \" \\\\删除ＩＤ等于２的行
         \"SELECT * from COMPANY\";        \\\\显示表

#include 
#include 
#include  
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, \"%s: \", (const char*)data);
   for(i=0; i 
 

#include 
#include 
#include  
 
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, \"%s: \", (const char*)data);
   for(i=0; i