一.连接MySQL数据库
一个最简单的程序示例:
- #include <stdio.h>
- #include "mysql.h"
- int main()
- {
- MYSQL mysql;
- int t, r;
- mysql_init(&mysql);
- if (!mysql_real_connect(&mysql, "localhost", "root", "supersmd", "student", 0, NULL, 0))
- printf("Error connecting to database: %s\n", mysql_error(&mysql));
- else
- printf("Connected MySQL successfully!\n");
- mysql_close(&mysql);
- return 0;
- }
它的编译命令为:gcc -o connect connect.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient
如果报出链接错误,没有找到mysqlclient 库,检查一下是否安装了,没有的话可以:
sudo apt-get install libMySQLclient15-dev
步骤:
1.初始化MySQL的连接句柄
在连接MySQL数据库之前,首先调用mysql_init()函数以初始化一个连接句柄。若建立成功,则返回一个新创建的链接句柄的指针;否则返回NULL
mysql_init()函数的原型如下:
MYSQL *mysql_init(MYSQL *);
2.实际连接数据库
在调用mysql_init()函数初始化MySQL句柄以后,调用mysql_real_connect()函数实际进行与MySQL服务器的链接。
mysql_real_connect()函数原型如下:
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, cosnt char *db, unsigned int port, const char *NAME_socket, unsigned long client_flag);
其中的参数含义:
mysql 是MYSQL连接句柄的指针。
host 既可以是主机名也可以是IP地址。如果连接的是本地机器,则其值为“localhost”
user 指定登录MySQL服务器的用户名
passwd 指定用户user的密码
db 是数据库名称
port 应该为0,不为0则将其值作为TCP/IP连接的端口号
NAME_socket 默认为NULL,如不为NULL,则该字符串描述了应使用的套接字或命名管道
client_flag 通常为0
二. 数据查询
- #include <stdio.h>
- #include <string.h>
- #include "mysql.h"
- int main()
- {
- MYSQL mysql;
- MYSQL_RES *res; //该结构代表返回行的查询结果(SELECT,SHOW,DESCRIBE,EXPLAIN)
- MYSQL_ROW row; //这是一行数据的“类型安全”表示,它指向含有一行数据的字符串数组。行号可以通过mysql_fetch_row()函数获得
- char *query;
- int flag, t;
- mysql_init(&mysql);
- if (!mysql_real_connect(&mysql, "localhost", "root", "supersmd", "student", 0, NULL, 0)) //若连接数据库失败
- {
- printf("Failed to connect to MySQL!\n");
- return 0;
- }
- else
- printf("Connected MySQL successfully!\n");
- query = "select * from student"; //要查询的SQL语句
- flag = mysql_real_query(&mysql, query, (unsigned int)strlen(query));
- if (flag) //非0查询失败
- {
- printf("query failed!\n");
- return 0;
- }
- else
- printf("[%s] made...\n", query);
- res = mysql_store_result(&mysql); //保存查询结果
- while (row = mysql_fetch_row(res))
- {
- for (t = 0; t < mysql_num_fields(res); t++)
- printf("%s ", row[t]);
- printf("\n");
- }
- mysql_close(&mysql);
- return 0;
- }
(1) int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length);
执行指定为带计数的字符串query的SQL查询。查询成功返回0;
(2) mysql_real_query()的辅助函数及其用法
函数原型 | 用法 |
mysql_store_result() | 将查询的全部结果读取到客户端。查询成功后必须调用mysql_store_result() |
mysql_num_field() | 返回结果集中的行数 |
mysql_fetch_row() | 检索结果集的下一行 |
三.添加数据
- #include <stdio.h>
- #include <string.h>
- #include "mysql.h"
- int main()
- {
- MYSQL mysql;
- MYSQL_RES *res;
- MYSQL_ROW row;
- char *query[2];
- int i, flag, r;
- mysql_init(&mysql);
- if (!mysql_real_connect(&mysql, "localhost", "root", "supersmd", "student", 0, NULL, 0))
- {
- printf("Failed to connect to MySQL!\n");
- return 0;
- }
- else
- printf("Connected MySQL successfully!\n");
- query[0] = "insert into student (sno, sname)values(1010,'kobe')";
- query[1] = "insert into student (sno, sname)values(1020,'TMC')";
- for (i = 0; i < 2; i++)
- {
- if (mysql_real_query(&mysql, query[i], (unsigned int)strlen(query[i])))
- {
- printf("insert data %d failed!\n", i+1);
- return 0;
- }
- }
- mysql_close(&mysql);
- return 0;
- }
四.删除数据
- #include <stdio.h>
- #include <string.h>
- #include "mysql.h"
- int main()
- {
- MYSQL mysql;
- MYSQL_RES *res;
- MYSQL_ROW row;
- char *query;
- int i, flag, r;
- mysql_init(&mysql);
- if (!mysql_real_connect(&mysql, "localhost", "root", "supersmd", "student", 0, NULL, 0))
- {
- printf("Failed to connect to MySQL!\n");
- return 0;
- }
- else
- printf("Connected MySQL successfully!\n");
- query = "delete from student where sno = '1001'";
- if (!mysql_real_query(&mysql, query, (unsigned int)strlen(query)))
- {
- printf("delete data successfully!\n");
- }
- mysql_close(&mysql);
- return 0;
- }