数据库基础知识

数据库

相对于文件操作,数据库的存储空间更加大,数据库就是存储数据的仓库.
下面我们来了解下数据库的一些常用基础知识.

---

数据库中的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操作

1. 项目配置
   找到安装好的mysql目录,以本人为例,我将mysql安装在D盘:

如图所示,将include和lib两个文件夹复制到创建的项目中去;

项目属性配置:

附加依赖项添加上libmysql.lib,在项目中也添加相应的操作:

#pragma comment (lib,"libmysql.lib")//项目代码中相应添加

至此,一个mysql的项目配置就初步完成了!

2. 使用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());

3. 项目完整代码

#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;
}