最近实在没事做,就想捣腾一下vc数据库操作。很多东西都是看过就忘,大学的时候学过SQL语句,但是没做数据库开发,忘得差不多了。
写博客是个不错的习惯,以后遇到相同的问题直接查看博客就OK了,省的在浩瀚的网络上查资料,网上资料是比较多,好多千篇一律,还是自己遇到并实践过的比较实在。
安装完mysql,在安装目录下的include文件夹下包含有mysql.h,在lib目录下有libmysql.lib,这两个要用到。安装目录下还有些简单的examples,可以参考一下。
c语言出生的,写代码很难摒弃c的风格完全转换为c++风格,很多时候写出的代码,完全看不到c++的影子,(/ □ \)
下面是整个案例的代码,看注释大概能够清楚调用的大概流程:
// MysqlTest.cpp : 定义控制台应用程序的入口点。
//
#include "stdafx.h"
#include <winsock.h>
#include <Windows.h>
#include <iostream>
#include <mysql.h>
#include <stdlib.h>
#include <string>
using namespace std;
#pragma comment(lib,"libmysql.lib")
//数据库记录数据结构
typedef struct Record
{
string id ;
string name;
string sex;
string age;
string tel;
string country;
string birth;
Record()
{
id = "null";
name = "Siri";
sex = "女";
age = "18";
tel = "18945217725";
birth = "1996-11-13";
country = "American";
}
};
const unsigned short Port = 3306;
const char *IPAddress<span style="white-space:pre"> </span>= "localhost";
const char *UserName = "root";
const char *PassWord = "2010";
const char *DBName = "book_db";
MYSQL *mysql;
MYSQL_RES *res;
MYSQL_ROW row;
Record record;
int InsertRecord(Record re)
{
//组合SQL语句
string strSQL = "INSERT INTO students values(" + re.id + ",\"" + re.name + "\",\"" + re.sex + "\",\""
+ re.age + "\",\"" + re.tel + "\",\""+ re.birth + "\",\""+ re.country + "\");";
cout << strSQL << endl;
//执行插入语句
if(mysql_real_query(mysql,strSQL.c_str(),strSQL.length()))
{
cout << mysql_error(mysql) << endl;
return -1;
}
return 0;
}
int DeleteRecord(string str,int flag)
{
string strSQL = "DELETE FROM students";
switch(flag)
{
case 0:
strSQL += (" where id=\"" + str + "\";");
break;
case 1:
strSQL += (" where name=\"" + str + "\";");
break;
case 2:
strSQL += (" where sex=\"" + str + "\";");
break;
case 3:
strSQL += (" where age=\"" + str + "\";");
break;
case 4:
strSQL += (" where tel=\"" + str + "\";");
break;
case 5:
strSQL += (" where birth=\"" + str + "\";");
break;
case 6:
strSQL += (" where country=\"" + str + "\";");
break;
}
//执行删除操作
cout << strSQL << endl;
if(mysql_real_query(mysql,strSQL.c_str(),strSQL.length()))
{
cout << mysql_error(mysql) << endl;
return -1;
}
return 0;
}
int QueryRecord()
{
string strSQL("SELECT name,sex,age,country FROM students");//select语句
if(mysql_real_query(mysql,strSQL.c_str(),strSQL.length()))
{
cout << mysql_error(mysql) << endl;
return -1;
}
//存储数据集
res = mysql_store_result(mysql);//此处还可以使用mysql_use_result
while(row = mysql_fetch_row(res))
{
for(int i = 0;i < mysql_num_fields(res);i++)
{
if(row[i] != NULL)
cout << row[i] << "\t";
else
cout << "<null>" << "\t";
}
cout << "\n";
}
mysql_free_result(res);//释放数据集
return 0;
}
int UpdateRecord(string name,string sex)
{
string strSQL = "UPDATE students set country=\'England\' where name=\'"+ name
+ "\' and sex=\'" + sex + "\';";
cout << strSQL << endl;
if(mysql_real_query(mysql,strSQL.c_str(),strSQL.length()))
{
cout << mysql_error(mysql) << endl;
return -1;
}
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
mysql = (MYSQL*)malloc(sizeof(MYSQL));
//初始化
mysql_init(mysql);
if(mysql == NULL)
{
cout<<"ERROR:mysql init error!\n";
return 0;
}
//设置数据库编码方式
mysql_options(mysql,MYSQL_SET_CHARSET_NAME,"gb2312");
//连接到指定数据库
mysql = mysql_real_connect(mysql,IPAddress,UserName,PassWord,DBName,Port,NULL,0);
if(!mysql)
{
cout << "Connect failed!\n";
return 0;
}
//查询数据
QueryRecord();
//插入数据
InsertRecord(record);
//删除 name="Siri" 的记录
DeleteRecord("Siri",1);
//更新数据
UpdateRecord("Siri","女");
mysql_close(mysql);//关闭连接
free(mysql);
system("pause");
return 0;
}
要特别注意:如果数据库支持中文编码,需要在初始化和连接数据库之间调用mysql_options()函数来设置数据库的编码方式为“gb2312”或者gbk,否则从数据库获取的中文数据显示为乱码。调用方法如下:
mysql_options(mysql,MYSQL_SET_CHARSET_NAME,"gb2312");
顺便把创建数据库的代码贴出来:
create table students
(
id int unsigned not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
birth char(20) null
country char(20) null
);