在linux下可以用mysql提供的api访问本地或是远程的数据库。
开发环境:
操作系统:ubuntu 6.06
web环境:linux+apache2+php5+mysql5
安装LAMP的环境有很多的资料可以查找,但是在安装的过程,一定要安装mysqlclient这个,因为我们开发的是客户端,mysql提供了一个api──mysql.h,都在这个包中!呵呵。。。我找了好久!好了开始编程!
明确的细节:
数据库的用户名:test
数据库的密码:wywywy
数据库用户的权限:test的权限,create ,insert,delete,drop
默认的数据库:test
api函数头文件位置:/usr/include/mysql/mysql.h
编译的方法:
dubuntu@euwy-laptop:/tools/study/c/mysql$ gcc create.c -lmysqlclient
现在开始编程和学习
第一个函数──连接数据库
MYSQL * STDCALL mysql_real_connect(MYSQL *mysql, const char *host,
const char *user,
const char *passwd,
const char *db,
unsigned int port,
const char *unix_socket,
unsigned long clientflag);
返回值:成功返回指向MYSQL的结构体,连接事败返回NULL
行参解释 :
MYSQL *mysql 通过mysql_init()得到的一个mysql的指针
const char *host 本地或是远程的主机名,如果是NULL或是localhost,就指向本地 的回环地址
const char *user 数据库的登录id,如果为空用字符串""表示
const char *password 登录数表示据库的用户密码,如果为NULL,用字符串""
const char *db 数据的名称,如果为NULL,则登录到默认的数据库
unsigned int port 数据库的端口号,如果为0,是为合适的通讯协议的端口,如果不为 零,为tcp/ip的连接端口号
const char *unix_socket 如果不为NULL,则表示使用unix_socket的管道
unsigned long clientflag 经常为之0 在执行mysql_real_connet()函数前,要执行msyql_init(MYSQL *mysql)函数,进行初始化!
MYSQL * STDCALL mysql_init(MYSQL *mysql);
返回值:NULL表示没有足够的内存空间,成功返回MYSQL *
调用这个函数目的是,在调用msyql_real_connet()函数的时候提供一个退出的函数出口。
数据库的关闭函数:
void STDCALL mysql_close(MYSQL *sock);
返回值:无返回值
例题:
程序名称:connect.c
#include <stdio.h>
#include <mysql/mysql.h>
MYSQL my_connection;
int main(int argc,char *argv[])
{
if(argc!=4)
...{
printf("parameter is error ");
return -1;
}
mysql_init(&my_connection);
if(mysql_real_connect(&my_connection,argv[1],argv[2],argv[3],"test",0,NULL,0))
...{
printf("******** ");
mysql_close(&my_connection);
return 0;
}
else
...{
printf("######## ");
mysql_close(&my_connection);
return -1;
}
}
编译方式:
gcc connect.c -Wall -lmysqlclient 执行前的数据库:
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> 执行终端结果:
dubuntu@euwy-laptop:/tools/study/c/mysql$ ./a.out 127.0.0.1 test wywywy
******** 第二个函数──执行SQL语句
在对数据库操作的sql语句中,常用的有,insert,dorp,delete,create,和desc,selec,show,等语句,前面的是没有返回结果的sql语句,只是对数据库中的数据产生影响,而后面的语句是会从数据库返回数据的结果集合,也就是语言一样的结果集!
相关的数据结构:
MYSQL_RES ── 结果集
当我们执行sql语句,如desc,select,show等要求返回数据库信息的用这个结构体。
typedef struct st_mysql_res {
my_ulonglong row_count;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
unsigned long *lengths; /**//* column lengths of current row */
MYSQL *handle; /**//* for unbuffered reads */
MEM_ROOT field_alloc;
unsigned int field_count, current_field;
MYSQL_ROW row; /**//* If unbuffered read */
MYSQL_ROW current_row; /**//* buffer to current row */
my_bool eof; /**//* Used by mysql_fetch_row */
/**//* mysql_stmt_close() had to cancel this result */
my_bool unbuffered_fetch_cancelled;
const struct st_mysql_methods *methods;
} MYSQL_RES; MYSQL_FIELD──字段名。。。等数据库的数据
typedef struct st_mysql_field {
char *name; /**//* Name of column */
char *org_name; /**//* Original column name, if an alias */
char *table; /**//* Table of column if column was a field */
char *org_table; /**//* Org table name, if table was an alias */
char *db; /**//* Database for table */
char *catalog; /**//* Catalog for table */
char *def; /**//* Default value (set by mysql_list_fields) */
unsigned long length; /**//* Width of column (create length) */
unsigned long max_length; /**//* Max width for selected set */
unsigned int name_length;
unsigned int org_name_length;
unsigned int table_length;
unsigned int org_table_length;
unsigned int db_length;
unsigned int catalog_length;
unsigned int def_length;
unsigned int flags; /**//* Div flags */
unsigned int decimals; /**//* Number of decimals in field */
unsigned int charsetnr; /**//* Character set */
enum enum_field_types type; /**//* Type of field. See mysql_com.h for types */
} MYSQL_FIELD; 相关函数原型:
MYSQL_REAL_QUERY──数据库查询
int STDCALL mysql_real_query(MYSQL *mysql, const char *q,unsigned long length);
MYSQL *mysql 通过mysql_real_connect()返回的MYSQL指针
const char *q 复合sql的单调语句,要用";"结尾
unsigned long length strlen(q)的长度
MYSQL_STROE_RESULT与MYSQL_USE_RESULT──获得结果集
MYSQL_RES * STDCALL mysql_store_result(MYSQL *mysql);
MYSQL_RES * STDCALL mysql_use_result(MYSQL *mysql);
返回值:成功取得结果返回MYSQL_RES指针,失败为NULL
对客户端而言,有两种方法处理结果集合。一种方法是通过调用mysql_store_result ()立刻检索全部结果。该函数从服务器获得查询返回的所有行,并将他们存储在客户端。第二种方法是对客户通过调用mysql_use_result()初 始化一个一行一行地结果集合的检索。该函数初始化检索,但是实际上不从服务器获得任何行 在两种情况中,你通过mysql_fetch_row ()存取行。用mysql_store_result()、mysql_fetch_row()储存取已经从服务器被取出的行。用 mysql_use_result()、mysql_fetch_row()实际上从服务器检索行。调用mysql_fetch_lengths()可获 得关于每行中数据值尺寸的信息。
MYSQL_FETCH_ROW──获取行的信息
typedef char **MYSQL_ROW;
MYSQL_ROW STDCALL mysql_fetch_row(MYSQL_RES *result);
返回值:成功返回结果集中下一行指针,否则返回null或是错误码
MYSQL_FREE_RESULT──释放结果集空间
void STDCALL mysql_free_result(MYSQL_RES *result); 例题:
程序名称:create.c
#include <stdio.h>
#include <mysql/mysql.h>
#include <string.h>
MYSQL mysql_connect;
MYSQL_RES *res;
MYSQL_ROW row;
int main(int argc,char *argv[])
{
char *query="create table test( num int ,name char(50))";
int result;
if(argc!=4)
...{
printf("parameter is error ");
return -1;
}
if(mysql_init(&mysql_connect)==NULL)
...{
printf("memery is error ");
return -1;
}
if(mysql_real_connect(&mysql_connect,argv[1],argv[2],argv[3],"test",0,NULL,0))
...{
printf("%s connect is succee ",argv[1]);
result=mysql_real_query(&mysql_connect,query,strlen(query));
if(result==0)
...{
printf("sql=%s is succse ",query);
}
else
...{
printf("query is %s ",mysql_error(&mysql_connect));
return -1;
}
}
else
...{
printf("conncet %s mysql is %d:%s ",argv[1],mysql_errno(&mysql_connect),mysql_error(&mysql_connect));
return -1;
}
} 编译方法
dubuntu@euwy-laptop:/tools/study/c/mysql$ gcc -lmysqlclient create.c -Wall 在进行执行之前。。。数据库的状态,在数据库test中没有table
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> 执行结果:
dubuntu@euwy-laptop:/tools/study/c/mysql$ ./a.out 127.0.0.1 test wywywy
127.0.0.1 connect is succee
sql=create table test( num int ,name char(50)) is succse
dubuntu@euwy-laptop:/tools/study/c/mysql$ 数据库结果:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> desc test
-> ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | int(11) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> 结果分析:可以看到在数据库建立了数据表test
例题
程序名称:insert.c
#include <stdio.h>
#include <mysql/mysql.h>
#include <string.h>
MYSQL mysql;
MYSQL_ROW row;
MYSQL_RES *res;
int main(int argc,char *argv[])
{
char *query="insert into test values(5,'wang')";
int result;
if(argc!=4)
...{
printf("main parameter is error ");
goto error;
}
if(mysql_init(&mysql)==NULL)
...{
printf("memery is error ");
goto error;
}
if(mysql_real_connect(&mysql,argv[1],argv[2],argv[3],"test",0,NULL,0))
...{
printf("%s is connect.... ",argv[1]);
result=mysql_real_query(&mysql,query,strlen(query));
if(result==0)
...{
printf("%s sql is succse ",query);
return 0;
}
else
...{
printf("sql is error :%s ",mysql_error(&mysql));
goto error;
}
}
else
...{
printf("%s ",mysql_error(&mysql));
goto error;
}
error:
printf("program is error ");
return -1;
}
编译方法:
dubuntu@euwy-laptop:/tools/study/c/mysql$ gcc -lmysqlclient insert.c -Wall 执行前数据库状态:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> 执行结果
dubuntu@euwy-laptop:/tools/study/c/mysql$ ./a.out 127.0.0.1 test wywywy
127.0.0.1 is connect....
insert into test values(5,'wang') sql is succse 数据库变化:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+------+------+
| num | name |
+------+------+
| 5 | wang |
+------+------+
1 row in set (0.00 sec)
mysql> 其他的sql语句,像drop,delet等不产生结果的语句,适用方法同
而像有返回的数据的sql语句,像select,show,desc....等怎么操作呢
例题:
程序名称:select.c
#include <stdio.h>
#include <mysql/mysql.h>
#include <string.h>
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
int main(int argc,char *argv[])
{
char *query="select * from test";
int result,col,i;
if(argc!=4)
...{
printf("main's parameter is error ");
goto error;
}
if(mysql_init(&mysql)==NULL)
...{
printf("memey is error ... ");
goto error;
}
if(mysql_real_connect(&mysql,argv[1],argv[2],argv[3],"test",0,NULL,0))
...{
printf("mysql server %s is connect ... ",argv[1]);
result=mysql_real_query(&mysql,query,strlen(query));
if(result==0)
...{
printf("mysql sql=%s is succes ",query);
res=mysql_store_result(&mysql);
if(res)
...{
printf("retived %lu Rows ",(unsigned long)mysql_num_rows(res));
col=mysql_num_fields(res);
while((row=mysql_fetch_row(res)))
...{
for(i=0;i<col;i++)
...{
printf("%s ",row[i]);
printf(" ");
}
printf(" ");
}
mysql_free_result(res);
mysql_close(&mysql);
return 0;
}
else
...{
printf("mysql result %s ",mysql_error(&mysql));
goto error;
}
}
else
...{
printf("mysql sql %s is %s:%d ",query,mysql_error(&mysql),mysql_errno(&mysql));
goto error;
}
}
else
...{
printf("mysql connect is %s:%d ",mysql_error(&mysql),mysql_errno(&mysql));
mysql_close(&mysql);
goto error;
}
error:
printf("error.... ");
return -1;
} 编译方法:
dubuntu@euwy-laptop:/tools/study/c/mysql$ gcc -lmysqlclient -Wall select.c 执行前数据库的状态
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
mysql> select * from test;
+------+----------+
| num | name |
+------+----------+
| 5 | wang |
| 5 | NULL |
| 5 | NULL |
| 5 | name |
| 5 | wangyong |
| 5 | wangyong |
+------+----------+
6 rows in set (0.06 sec)
mysql> 执行方法:
dubuntu@euwy-laptop:/tools/study/c/mysql$ ./a.out 127.0.0.1 test wywywy
mysql server 127.0.0.1 is connect ...
mysql sql=select * from test is succes
retived 6 Rows
5 wang
5 (null)
5 (null)
5 name
5 wangyong
5 wangyong
dubuntu@euwy-laptop:/tools/study/c/mysql$