数据库
相对于文件操作,数据库的存储空间更加大,数据库就是存储数据的仓库.
下面我们来了解下数据库的一些常用基础知识.
数据库中的DDL和c)DML语句
-
DDL(Data Definition Language)数据定义语言
查看数据库和表、创建和删除表和数据库等;
例如:show databases; show tables; use xxx数据库名; create database xxx; //(mysql的方言:CREATE SCHEMA xxx) create table xxx; drop database xxx; drop table yyy;
-
DML (数据操纵语言,Data Manipulation Language )
对表的增删改查等直接操作数据;
例如://1、插入记录(record): //a)指定字段插入: INSERT INTO t_admin (f_name, f_pass, f_prior) VALUES ('admin', 'admin', '0'); //b)不指定字段(全部字段): INSERT INTO t_admin VALUES ('admin', 'admin', '0'); INSERT INTO t_worker VALUES(1008,'张珊',8888.88,'2019-9-9'); //2、删除记录:DELETE FROM语句一定要使用WHERE条件,否则就使用truncate语句; DELETE FROM t_worker; //删除表内所有记录; truncate t_worker; //清空一张表,速度比上面的DELETE速度快相当于格式化: //3、筛选:SELECT : //a)可以无条件筛选,代表所有记录; //b)可以选择字段显示,或者用*代表所有字段; SELECT * FROM t_worker; SELECT * FROM t_worker WHERE f_sala>5000 AND f_sala<10000;
创建数据库以及数据库里头的表和字段的时候,可以当作是写一个结构体的方式去理解.(比较奇葩的思路…)
c++使用mysql API操作
- 项目配置
找到安装好的mysql
目录,以本人为例,我将mysql
安装在D
盘:
如图所示,将include
和lib
两个文件夹复制到创建的项目中去;
项目属性配置:
附加依赖项添加上libmysql.lib
,在项目中也添加相应的操作:
#pragma comment (lib,"libmysql.lib")//项目代码中相应添加
至此,一个mysql
的项目配置就初步完成了!
- 使用
mysql-API
操作\- 连接数据库
if (!mysql_real_connect(mysql, "localhost", "root", "123456", "worker", 3306, nullptr, 0))
{
cout <<"连接数据库失败("<<mysql_errno(mysql)<<"):" << mysql_error(mysql) << endl;
return -1;
}
* 操作创建表以及列
string sql = "CREATE TABLE IF NOT EXISTS `t_image` ( \
`id` int AUTO_INCREMENT,\
`name` varchar(1024),\
`path` varchar(2046),\
`size` int,\
PRIMARY KEY(`id`)\
) ";
int re = mysql_query(mysql, sql.c_str());//从字符串换成const char*
if (re != 0)
{
cout << "mysql_query failed!" << mysql_error(mysql) << endl;
}
* 插入数据操作
for (int i = 0; i < 100; i++)
{
stringstream ss;
ss << "insert `t_image` (`name`,`path`,`size`)values('image";
ss << i << ".jpg','d:/img/', 10240)";
sql = ss.str();
re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "mysql_affected_rows " << count << " id =" <<
mysql_insert_id(mysql) << endl;
}
else
{
cout << "insert failed!" << mysql_error(mysql) << endl;
}
}
* 更新数据操作
string sql = "update t_image set `name`='test3.png',size=2000 where id=53";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "update mysql_affected_rows " << count << endl;
}
else
{
cout << "update failed!" << mysql_error(mysql) << endl;
}
* 单独删除数据操作
string sql = "delete from t_image where id=53";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "delete mysql_affected_rows " << count << endl;
}
else
{
cout << "delete failed!" << mysql_error(mysql) << endl;
}
string sql = "delete from t_image";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "delete mysql_affected_rows " << count << endl;
}
else
{
cout << "delete failed!" << mysql_error(mysql) << endl;
}
//实际清理了空间
sql = "OPTIMIZE TABLE t_image";
re = mysql_query(mysql, sql.c_str());
- 项目完整代码
#include <iostream>
#include <sstream>
#include <mysql.h>
#include <map>
using namespace std;
#pragma comment (lib,"libmysql.lib")
//time(NULL); time(&tt);
int main()
{
MYSQL* mysql = mysql_init(NULL);
if (!mysql)
{
cout << "mysql init error:" << endl;
return -1;
}
//连接数据库
if (!mysql_real_connect(mysql, "localhost", "root", "123456", "worker", 3306, nullptr, 0))
{
cout <<"连接数据库失败("<<mysql_errno(mysql)<<"):" << mysql_error(mysql) << endl;
return -1;
}
#if 0 //操作创建表以及列
//string sql = "CREATE TABLE IF NOT EXISTS `t_image` ( \
// `id` int AUTO_INCREMENT,\
// `name` varchar(1024),\
// `path` varchar(2046),\
// `size` int,\
// PRIMARY KEY(`id`)\
// ) ";
//int re = mysql_query(mysql, sql.c_str());//从字符串换成const char*
//if (re != 0)
//{
// cout << "mysql_query failed!" << mysql_error(mysql) << endl;
//}
#endif //操作创建表以及列
#if 0插入数据操作
for (int i = 0; i < 100; i++)
{
stringstream ss;
ss << "insert `t_image` (`name`,`path`,`size`)values('image";
ss << i << ".jpg','d:/img/', 10240)";
string sql = ss.str();
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "mysql_affected_rows " << count << " id =" <<
mysql_insert_id(mysql) << endl;
}
else
{
cout << "insert failed!" << mysql_error(mysql) << endl;
}
}
#endif 插入数据操作
#if 0 //更新数据,必须在插入数据之后再操作
string sql = "update t_image set `name`='test3.png',size=2000 where id=55";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "update mysql_affected_rows " << count << endl;
}
else
{
cout << "update failed!" << mysql_error(mysql) << endl;
}
#endif //更新数据,必须在插入数据之后再操作
#if 0 //单独删除操作
string sql = "delete from t_image where id=53";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "delete mysql_affected_rows " << count << endl;
}
else
{
cout << "delete failed!" << mysql_error(mysql) << endl;
}
#endif //单独删除操作
#if 1 //直接删除整个表
string sql = "delete from t_image";
int re = mysql_query(mysql, sql.c_str());
if (re == 0)
{
int count = mysql_affected_rows(mysql);
cout << "delete mysql_affected_rows " << count << endl;
}
else
{
cout << "delete failed!" << mysql_error(mysql) << endl;
}
//实际清理了空间
sql = "OPTIMIZE TABLE t_image";
re = mysql_query(mysql, sql.c_str());
#endif //直接删除整个表
return 0;
}