驾校理论考试系统之数据库编程二

数据库操作类：

来自周公的： 文件取自周公博客

这个图标是从文件中提取出来的，后面会介绍这个方法

根据理论考试题目分布：

道路交通安全法规和规章 25 (违法行为综合判断与案例分析)

地方性法规 5%

道路交通信号 20% （常见交通标志标线和交警手势信号辨识）

安全行车，文明驾驶知识 20% （驾驶职业道德和文明驾驶常识）（安全行车常识）

复杂条件道路下的安全驾驶知识 10% （恶劣气候和复杂道路条件下驾驶常识）

紧急情况处理10% （紧急情况下避险常识）

常见故障判断，车辆维护5%

自救和危险品 5% （交通事故救护及常见危化品处置常识）

 

根据我的题库改为

违法行为综合判断与案例分析 15% 单选7

安全行车常识 25% 判断10 单选 15

常见交通标志标线和交警手势信号辨识 20% 判断10 单选8

驾驶职业道德和文明驾驶常识 10% 判断5 单选5

恶劣气候和复杂道路条件下驾驶常识 10% 判断 5 单选5

紧急情况下避险常识 10% 判断 5 单选5

交通事故救护及常见危化品处置常识 10% 判断5 单选5

多选题10个从总题库中抽取10个

说明：

    在先前数据库设计中我在题库中设计了两个属性，Rate和Random， 其中Rate是用来提高错题的出现次数，Random则用来随机抽取题目，从下面的select语句中可看出：

ORDER BY Rate DESC,Random ，按照先Rate降序排列，再按随机数Random降序排列，Limit使用来限定一次去多少个的！IN则是集合操作，表示在这里面取数据

 

选择

1. 取50个选择题

SELECT TestID,BelongTo,Type,Question,Answer,Image,Video FROM COMPREHENSIVE_EXAM

WHERE TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1001')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 7)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1002')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 15)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1003')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 8)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1004')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1005')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1006')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1007')

AND Type = '单选'

ORDER BY Rate DESC,Random

LIMIT 5)

1.     取40个判断题

SELECT TestID,BelongTo,Type,Question,Answer,Image,Video FROM COMPREHENSIVE_EXAM

WHERE TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1002')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 10)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1003')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 10)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1004')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1005')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1006')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 5)

OR

TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM

WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1007')

AND Type = '判断'

ORDER BY Rate DESC,Random

LIMIT 5)

1.     取10个多选题

SELECT TestID,BelongTo,Type,Question,Answer,Image,Video

FROM COMPREHENSIVE_EXAM

WHERE Type = '多选'

ORDER BY Rate DESC,Random

LIMIT 10

插入

//向表ERRORLIST插入数据

private void InsertERRORLIST(string TableName, string TestID)

{

string sql = "INSERT INTO ERRORLIST(UID, TableName, TestID, DateTime)" + "values(@UID, @TableName, @TestID, @DateTime)";

SQLiteParameter[] parameters = new SQLiteParameter[]{

new SQLiteParameter("@UID","UI1001"),

new SQLiteParameter("@TableName", TableName),

new SQLiteParameter("@TestID", TestID),

new SQLiteParameter("@DateTime", DateTime.Now.ToString("yyyy-MM-dd")),

};

db.ExecuteNonQuery(sql, parameters);

}

更新

//错题比率清零

private void SetZero()

{

string sql = "UPDATE COMPREHENSIVE_EXAM SET Rate = 500 ";

db.ExecuteNonQuery(sql, null);

MessageBox.Show("清零成功");

}

 

//更新数据库中题目排名rate，答对减一，答错加一,并且将错误的答案写到表ErrorList中

private void UpadateSqlRate()

{

using (SQLiteConnection connection = new SQLiteConnection("Data Source= CarTraining.db"))

{

connection.Open();

using (DbTransaction transaction = connection.BeginTransaction())

{

using (SQLiteCommand command = new SQLiteCommand(connection))

{

int toAdd = 0;

string sqlsel;

string sqlupd;

 

for (int i = 0; i < 100; i++)

{

int rightAnswer = Convert.ToInt32(dt.Rows[i][4]);

 

if (allAnswer[i] == rightAnswer)

{

toAdd = -1;

}

else

{

toAdd = 1;

}

//根据testID获取表COMPREHENSIVE_EXAM中的Rate排名值

sqlsel = "SELECT Rate FROM COMPREHENSIVE_EXAM WHERE TestID = '"

+ dt.Rows[i][0].ToString() +"'";

command.CommandText = sqlsel;

SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);

DataTable data = new DataTable();

adapter.Fill(data);

//更新表COMPREHENSIVE_EXAM中的排名值

toAdd = Convert.ToInt32(data.Rows[0][0]) + toAdd;

sqlupd = "UPDATE COMPREHENSIVE_EXAM SET Rate = " + toAdd

+ " WHERE TestID = '" + dt.Rows[i][0].ToString() + "'";

command.CommandText = sqlupd;

command.ExecuteNonQuery();

}

}

transaction.Commit();

}

}

}

删除

//错题集清空

private void EmptyErrorList()

{

string sql = "DELETE FROM ERRORLIST";

db.ExecuteNonQuery(sql, null);

MessageBox.Show("错题集清空成功");

}

转载于:https://www.cnblogs.com/zuizuihao/archive/2012/07/18/2598395.html