sqlite第五天

简单的用户登录的程序：
1. 设计一个用户登录的流程图

2. 在ubuntu系统中建立一个数据库，代码如下：

-----创建USER表
CREATE TABLE "USER" (
"UserId"  INT PRIMARY KEY,
"UserName" text  NOT NULL,
"LoginName" text  NOT NULL,
"Passwd" text NOT NULL,
"Passwd2" text NOT NULL,
"Age"  int  NOT NULL,
"Sex"   text NOT NULL,
"Status" int NOT NULL,
"Remarks" text  );

-----向USER插入数据
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1001', '王小明', 'admin','123456','123123','19','男','1','');
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1002', '王小刚', 'aaa','123456','123123','20','男','1','');
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1003', 'mary', 'bbb','123456','123123','21','女','1','');
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1004', 'mark', 'ccc','123456','123123','22','男','1','');
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1005', 'jhon', 'ddd','123456','123123','23','男','1','');
INSERT INTO "USER" ("UserId", "UserName", "LoginName","Passwd","Passwd2","Age","Sex","Status","Remarks")  VALUES ('1006', 'angel', 'ddd','123456','123123','23','女','1','');

创建ROLE表
CREATE TABLE "ROLE" (
"RoleId"  INT PRIMARY KEY,
"RoleName" text  NOT NULL,
"CreateTime" text  NOT NULL,
"Remarks" text NOT NULL
);

向ROLE表插入数据
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('1', '读者',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('3', '作者',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('5', '图书管理员',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('7', '超级图书管理员',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('8', '用户管理员',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('9', '角色管理员',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('10', '用户角色分配员',Current_timestamp,'');
INSERT INTO "ROLE" ("RoleId", "RoleName", "CreateTime","Remarks")  VALUES ('11', 'root超级管理员',Current_timestamp,'');

------创建UR 用户角色关系表
CREATE TABLE "UR" (
"Id"  INT PRIMARY KEY NOT NULL,
"UserId" int NOT NULL,
"RoleId" int  NOT NULL
);

----向UR表插入数据
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('1', '1001','11');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('2', '1002','7');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('3', '1002','8');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('4', '1002','10');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('5', '1003','1');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('6', '1004','3');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('7', '1005','5');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('8', '1006','9');
INSERT INTO "UR" ("Id", "UserId", "RoleId")  VALUES ('9', '1006','1');

---创建book 图书表
CREATE TABLE "book" (
"BookId"  INT PRIMARY KEY NOT NULL,
"BookName" text NOT NULL,
"Writer" int  NOT NULL,
"UpdateTime" TEXT NOT NULL,
"Remarks" TEXT
);

向book表插入数据
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('1', 'sqlite菜鸟教程','1004',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('2', 'web前端','1004',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('3', 'C语言精义','1004',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('4', '海贼王','1004',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('5', '火影忍者','1010',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('6', '黑色四叶草','1011',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('7', '蜡笔小新','1012',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('8', '恶霸奶爸','1013',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('9', '学园奶爸','1014',Current_timestamp,'');
INSERT INTO "book" ("BookId", "BookName","Writer", "UpdateTime","Remarks")  VALUES ('10', '大圣归来','1015',Current_timestamp,'');

3. 用户登录

我们在创建数据库以后，了解一下用户登录的流程，并进行写出程序：
我们需要在先打开数据库，执行内容，最后关闭数据库；

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
#include <string.h> 
int flag=0;//查无此户的标志
int  rc;
char strlogin[200]; /* 用户名 */
char strSQL[200];//要执行的sql查询
char strpwd[200]; // 输入的密码
int successflag=0;//登陆成功的标识  0失败   1成功
 
 //回调函数
 static int callback_login(void *NotUsed, int argc, char **argv, char **azColName)
 {
	flag=1;

	char* str2 = argv[1];//查询出的密码
	char* str3 = argv[2];//查询出来的状态码
	int i;
	
	for(i=0;i<3;i++){
		printf("请输入密码：\n");
		scanf( "%s", strpwd );	char* str1 = strpwd;//输入的密码
		if(strcmp(str2, str1) != 0){//判断字符串是否相等
			printf("密码错误！\n");
		}else if(strcmp(str3, "1") != 0){
			printf("账户被锁定！\n");
			break;
		}else{
			printf("登陆成功！\n");
			successflag=1;//登陆成功的标识
			break;
		}
	
	}
	if(i==3){
		printf("密码已尝试3次！账户被锁定！\n");
	}

    return 0;
 }
 
 


//主函数
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   char *sql;//要执行的sql
   char *SuccessMsg = 0;//提示成功的消息
   const char* data = "查询结果";


   //打开数据库
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "无法打开数据库 : %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stdout, "打开数据库成功！\n");
   }
   

  
  while(1){
	memset(strSQL,0,200);
	memset(strlogin,0,200);
	memset(strpwd,0,200);
	printf("请输入登陆名：\n");
    scanf( "%s", strlogin ); 
    strcpy(strSQL,"select LoginName,Passwd,Status from User where LoginName ='");
    strcat(strSQL, strlogin);//拼接字符串
    strcat(strSQL,"';");
	sql=strSQL;//为sql赋值	
	rc = sqlite3_exec(db, sql, callback_login, 0, &zErrMsg);
	if( rc != SQLITE_OK ){
		fprintf(stderr, "执行失败: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	}
	if(successflag==1){
		break;
	}
  }
   
 



	if(flag==0){
		printf("查无此户！\n");
	}


	  
   //关闭数据库
   sqlite3_close(db);
   return 0;
}