WIC项目_SAIO SQLCE DB manmger转换出现的问题

本文介绍了六个SQL表的创建过程，包括用户表、应用表、价格表、参数表、交易表及更新表。每张表详细定义了字段名、类型及主键，确保数据结构合理并符合业务需求。

1 首先是表的字段名称不能是SQL的关键字符例如update、select等

//1 创建表 UserTb
CREATE TABLE UserTb(uid         nvarchar(6)     PRIMARY KEY,
                    pwd         nvarchar(6)     ,
                    privilege   nchar(1)
);
------------------------------------------------------------
CREATE TABLE UserTb(uid nvarchar(6) PRIMARY KEY, pwd nvarchar(6) NOT NULL, privilege nchar(1) NOT NULL);
////////////////////////////////////////////////////////////

//2 创建表 APLTb
CREATE TABLE APLTb(upc          nvarchar(17)     PRIMARY KEY,
                   description    nvarchar(50)    ,
                   category    nchar(2)    ,
                   subcategory    nchar(3)    ,
                   punit    nvarchar(10)    ,
                   maxprice    nchar(6)    ,
                   ptype     nchar(2)
);
------------------------------------------------------------
CREATE TABLE APLTb(upc nvarchar(17) PRIMARY KEY, description nvarchar(50), category nchar(2), subcategory nchar(3), punit nvarchar(10), maxprice nchar(6), ptype nchar(2));
////////////////////////////////////////////////////////////

//3 创建表 PriceTb
CREATE TABLE PriceTb (upc    nchar(17)    PRIMARY KEY,
                      price    nchar(6)
);
------------------------------------------------------------
CREATE TABLE PriceTb (upc nchar(17) PRIMARY KEY, price nchar(6));
////////////////////////////////////////////////////////////

//4 创建表 ParamTb
CREATE TABLE ParamTb(pid    smallint    PRIMARY KEY,
                     pname    nvarchar(30)    ,
                     pvalue    nvarchar(40)
);
------------------------------------------------------------
CREATE TABLE ParamTb(pid smallint PRIMARY KEY, pname nvarchar(30), pvalue nvarchar(40));
////////////////////////////////////////////////////////////

//5 创建表 TransTb
CREATE TABLE TransTb (traceid    nchar(6)    PRIMARY KEY,
                      msgtype    nchar(4)    ,
                      amount    nchar(12)    ,
                      uid    nchar(4)    ,
                      transdate    nchar(6)    ,
                      transtime    nchar(6)    ,
                      aiicode    nvarchar(12)
);
------------------------------------------------------------
CREATE TABLE TransTb (traceid nchar(6) PRIMARY KEY, msgtype nchar(4), amount nchar(12), uid nchar(4), transdate    nchar(6), transtime nchar(6), aiicode nvarchar(12));
////////////////////////////////////////////////////////////

//6 创建表 UpdateTb
CREATE TABLE UpdateTb(tid    nchar(2)    PRIMARY KEY,
                      tname    nvarchar(20)    ,
                      lupdate    nchar    (6)    ,
                      luptime    nchar    (6)
);
-----------------------------------------------------------
CREATE TABLE UpdateTb (tid nchar(2) PRIMARY KEY, tname nvarchar(20), lupdate nchar(6), luptime nchar(6));
////////////////////////////////////////////////////////////

    CString strCreate =_T("CREATE TABLE UserTb(uid nvarchar(6) PRIMARY KEY, pwd nvarchar(6) NOT NULL, privilege nchar(1) NOT NULL);");
    test.ExecuteSQL(strCreate);

    CString strCreate1 =_T("CREATE TABLE APLTb(upc nvarchar(17) PRIMARY KEY, description nvarchar(50), category nchar(2), subcategory nchar(3), punit nvarchar(10), maxprice nchar(6), ptype nchar(2));");
    test.ExecuteSQL(strCreate1);

    CString strCreate2 =_T("CREATE TABLE PriceTb (upc nchar(17) PRIMARY KEY, price nchar(6));");
    test.ExecuteSQL(strCreate2);

    CString strCreate3 =_T("CREATE TABLE ParamTb(pid smallint(2) PRIMARY KEY, pname nvarchar(30), pvalue nvarchar(40));");
    if(    test.ExecuteSQL(strCreate3))
    {
        MessageBox(NULL, _T("Insert OK"), _T("3"), MB_OK);
    }
    else
    {
        MessageBox(NULL, _T("Insert Fail"), _T("3"), MB_OK);
    }

    CString strCreate4 =_T("CREATE TABLE TransTb (traceid nchar(6) PRIMARY KEY, msgtype nchar(4), amount nchar(12), uid nchar(4), transdate    nchar(6), transtime nchar(6), aiicode nvarchar(12));");
    test.ExecuteSQL(strCreate4);

    CString strCreate5 =_T("CREATE TABLE UpdateTb (tid nchar(2) PRIMARY KEY, tname nvarchar(20), lupdate nchar(6), luptime nchar(6));");
2 插入数据的时候要注意字段的长度
    csPwd = CMd5::Encrypt(_T("123456"));
    csSql.Format(_T("INSERT INTO UserTb(uid, pwd, privilege) VALUES(123456, '%s', 2);"), csPwd);
    //pwd字段经过加密后变成32为MD5码，无法存储在只有6个字节的表中

    if(    test.ExecuteSQL(strCreate5))
    {
        MessageBox(NULL, _T("Insert OK"), _T("5"), MB_OK);
    }
    else
    {
        MessageBox(NULL, _T("Insert Fail"), _T("5"), MB_OK);
    }