SQL操作

sql操作

创建
CREATE TABLE COMPANY(
ID INI PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE TEXT 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 );

更新
PDATE COMPANY set SALARY = 25000.00 where ID=1;

查询
SELECT * from COMPANY;

删除
DELETE from COMPANY where ID=2;

代码实例

http://www.runoob.com/sqlite/sqlite-c-cpp.html

#include <iostream>
using namespace std;
#include "sqlite3.h"
#pragma comment(lib,"sqlite3.lib")//回调函数
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
    int i;
    for(i=0; i<argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

void main()  
{      /* Open database */
    sqlite3 *db;
    char *zErrMsg=0;
    int rc;
    char *sql; //SQL语句

    rc=sqlite3_open("test.db",&db);
    if (rc)
    {
        fprintf(stderr,"Can't open database: %s\n",sqlite3_errmsg(db));  //fprintf文件操作 printf标准输出
        exit(0);
    }
    else
    {
        fprintf(stderr,"Opened database successfully\n");
    }

    /*创建表*/
//     sql="CREATE TABLE COMPANY("\
//         "ID INI PRIMARY     KEY   NOT NULL,"\
//         "NAME               TEXT  NOT NULL,"\
//         "AGE                TEXT  NOT NULL,"\
//         "ADDRESS            CHAR(50),"\
//         "SALARY                REAL);";

    /*插入*/
//     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 );";

    /*查询数据*/
    sql="SELECT * from COMPANY";
    /*删除数据*/

    /*    sql = "DELETE from COMPANY where ID=2; " \
        "SELECT * from COMPANY";*/
    /*更新记录*/
    /*sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
        "SELECT * from COMPANY";*/

    /* Execute SQL statement 执行SQL语句 */
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);  //sqlite3_exec执行一条sql语句的函数  程序解析并执行由 sql 参数所给的每个命令，直到字符串结束或者遇到错误为止。

    //db打开数据库对象

    if( rc != SQLITE_OK )
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else
    {
        fprintf(stdout, "Operation created successfully\n");
    }
    sqlite3_close(db);
    system("pause");
}