MySQL 数据库学习记录

关键词：MySQL、C++

一、认识 MySQL

1.1 什么是 MySQL

MySQL 是 关系型 数据库。

关系型可以理解为表格的概念。
- 表格具有表头、行、列、键、值等。
  - 表头：每一列的名称；
  - 列：具有相同数据类型的集合；
  - 行：每一行用来描述某个具体信息；
  - 键：表中用来识别某个特定对象的方法，键的值在当前列中具有唯一性；
  - 值：行的具体信息，每个值必须与该列的数据类型相同。
一个关系型数据库由一个或多个表格组成。

如：

id	name	sex	age
1	小明	男	18
2	小红	女	17
3	小张	男	19
4	小李	女	16

表头为 id、name、sex、age；
id：作为一个键。

1.2 Windows 下配置 MySQL

此处为免安装版 MySQL-8.0.36，下载地址 - ZIP文件

解压到目录：E:\MySQL-8.0.36。

接着在文件夹中创建配置文件 my.ini，内容如下：

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
port=3306
 
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=E:\\MySQL-8.0.36
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

然后配置环境变量：

打开环境变量设置，新建变量名 MYSQL_HOME，变量值为 E:\MySQL-8.0.36。
系统变量 PATH 中添加 E:\MySQL-8.0.36\bin。

最后启动命令提示符（cmd），执行命令：

1	mysqld.exe --install MySQL --defaults-file="E:\MySQL-8.0.36\my.ini"

如果安装被拒绝，请使用管理员权限启动cmd。

提示 Service successfully installed. 表示成功。

安装成功

1.2.1 MySQL 的启动、停止和卸载

MySQL 服务的启动：net start MySQL

MySQL 服务的停止：net stop MySQL

MySQL 服务的卸载：sc delete MySQL

1.3 MySQL 脚本的基本组成

MySQL 通过执行 SQL 脚本来完成对数据库的操作。

脚本由一条或多条 MySQL 语句（SQL 语句 + 扩展语句）组成，保存为脚本文件一般为 .sql 文件。
控制台可以进行单句脚本执行。

标识符：

命名对象，如数据库、表、列、变量等。由字母、数字、下划线组成，第一个字符不能为数字。
- 大小写是否敏感取决于操作系统，Windows 下是不敏感的，但大多数 Linux 下是敏感的。

关键字：

关键字有特定的含义，避免作为标识符。（此处不列出，使用中学习）

语句：

是组成 MySQL 脚本的最小单位。每条语句可以完成特定操作，由 SQL 语句 + 扩展语句组成。

函数：

实现数据库操作的高级功能，大致分为：字符串函数、数学函数、日期时间函数、搜索函数、加密函数、信息函数。

1.4 MySQL 中的数据类型

三大数据类型：数字、日期/时间、字符串。

数字：

整型：
- tinyint：1个字节，带符号范围 $-128\sim127$ ；
- smallint：2个字节，带符号范围 $-32768\sim32767$ ；
- mediumint：3个字节，带符号范围 $-8388608\sim8388607$ ；
- int：4个字节，带符号范围 $-2^{31}\sim2^{31}-1,-2147483648\sim2147483647$ ；
- bigint：8个字节，带符号范围 $-2^{63}\sim2^{63}-1,\pm9.22\times10^{18}$ ；
浮点型：
- float(m,d)：单精度浮点型，8位精度（4字节），总精度 $m$ ，小数位 $d$ ，以实际为准。
- double：双精度浮点型，16位精度（8字节），总精度 $m$ ，小数位 $d$ ，以实际为准。
- real
- decimal(m,d)：定点数，存放精确的值。如果 $m>d$ 则大小为 $m+2$ 字节，否则为 $d+2$ 字节。

日期和时间：

date：格式为 YYYY-MM-DD，大小3字节，范围1000-01-01/9999-12-31。
time：格式为 HH:MM:SS，大小3字节，范围-838:59:59/838:59:59。
year：格式为 YYYY，大小1字节，范围1901/2155。
datetime：格式为 YYYY-MM-DD HH:MM:SS，大小8字节，范围1000-01-01 00:00:00/9999-12-31 23:59:59。
timestamp：格式为 YYYY-MM-DD HH:MM:SS，大小4字节，范围1970-01-01 00:00:00/2038-01-19 03:14:07（结束时间为距离1970-01-01 00:00:00 的第 $2^{31}-1$ $2^{31} - 1$ 秒，看时区）。
- 时间戳的数据会随其他字段修改时自动更新。

字符串类型：

字符串：
- char(n)：定长字符串，0~255字节。
- varchar(n)：变长字符串，0~65535字符。
- char(n) 与 varchar(n)：两者的 $n$ 都表示字符个数，而不是字节个数。前者若存入字符数小于 $n$ ，则用空格补在后面，查询时去掉空格；而后者没这个规定。
文本：
- tinytext：短文本字符串，0~255字节。
- text：长文本数据，0~65535字节。
- mediumtext：中等长度文本数据，0~16777215字节。
- longtext：极大文本数据，0~4294967295字节。
varchar 与 text：
- 前者可以指定 n，后者不能指定；
- 后者不能有默认值；
- 前者可直接创建索引，后者创建索引需要指定前多少个字符。前者查询速度快于后者。
二进制（存储图片、音乐等）：
- tinyblob：二进制字符串，0~255字节。
- blob：二进制长文本，0~65535字节。
- mediumblob：二进制中等长度文本，0~16777215字节。
- longblob：二进制极大文本，0~4294967295字节。
_blob 与 _text：
- 前者以二进制方式存储，英文不区分大小写，后者以文本方式存储，英文区分大小写；
- 前者存储的数据只能整体读出；
- 前者不用指定字符集，后者可以指定字符集。

数据类型的属性：

NULL：数据列可包含 NULL 值。
NOT NULL：数据列不允许包含 NULL 值。
DEFAULT：数据列的默认值。
PRIMARY KEY：主键。
AUTO_INCREMENT：自动递增。
UNSIGNED：无符号。
CHARACTER SET name：指定字符集。

二、使用 MySQL

2.1 登录到 MySQL

先启动 MySQL 服务（管理员权限cmd）：

1	net start mysql

然后使用自带的客户端工具登录，cmd输入：

1	mysql -h 主机名 -u 用户名 -p

-h：表示登录到的主机名，当前机器可省略。
-u：表示登录的用户名。
-p：表示登录时需要输入密码，密码为空可忽略。
这里安装的 MySQL 默认 root 账号是无密码的。

我的第一次安装出现了坑：“如何解决本地计算机上的mysql服务启动停止后,某些服务在未由其他服务或程序使用时将自动停止”

可能是因为之前不懂事弄着玩过，然后出了一堆错误。

解决办法：CSDN - 如何解决本地计算机上的mysql服务启动停止后,某些服务在未由其他服务或程序使用时将自动停止

2.2 创建一个数据库

创建命令如下：

1	create database 数据库名 [options];

创建数据库

MySQL 语句以分号作为结束。
show databases; 可查看创建了的数据库。

选择操作的数据库（必须先选择数据库，才能进行数据库的操作）。

登录时指定数据库：

1	mysql -D 数据库名 -h 主机名 -u 用户名 -p

登陆后使用 use 数据库名; 语句选择数据库。

2.2.1 创建数据库表

比如创建一张表，存放学号、姓名、性别、年龄、电话号码。

使用命令：

1	create table 表名称;

有时候表信息比较多，使用 .sql 脚本事先记录，然后执行 .sql 文件。

create tables students
(
    id int unsigned not null auto_increment primary key,
    name char(8) not null,
    sex char(1) not null,
    age tinyint unsigned not null,
    tel char(13) null default "-"
);

通过命令提示符下的文件重定向执行执行该脚本。打开命令提示符, 输入：

1	mysql -D samp_db -u root -p < createtable.sql

使用 show tables; 可以查看已创建的表。
使用 describe 表名; 可以查看已创建表的详细信息。

创建的表

2.3 操作 MySQL 数据库

2.3.1 向表中插入数据

使用 insert 语句：

1	insert [into] 表名 [(列名1,列名2,列名3,...)] values (值1,值2,值3,...);

如：

1	insert into students values (NULL,'ZhangSan','M',18,'13800000000');

也可以不按列的顺序：

1	insert into students (name,sex,age) values ('LiSi','F',19);

2.3.2 查询表中的数据

使用 select 语句：

1	select 列名称 from 表名称 [查询条件];

如查询表中所有学生的名字和年龄：

1	select name, age from students;

通配符 *：表示所有内容。

1	select * from students;

通配符查询

如果具有查询条件，使用 where 关键字：

1	select 列名称 from 表名称 where 条件;

如查找女性：

1	select * from students where sex = 'F';

带条件查询

查询前已添加了 LiSi 的数据。

where 支持 =、!=、<、<=、>、>=、in、not in、is [not] null、like 等运算符，支持与 or、and 的组合。

如：

查询名字中带有 “Wang” 字的所有人信息：

1	select * from students where name like "%Wang%";

查询 id 小于5且年龄大于20的所有人信息：

1	select * from students where id<5 and age>20;

2.3.3 更新表中的数据

使用 update 语句：

1	update 表名称 set 列名称=新值 where 更新条件;

如：

将 id 为5的手机号改为默认的"-"：

1	update students set tel=default where id=5;

将所有人的年龄增加1：

1	update students set age=age+1;

2.3.4 删除表中的数据

使用 delete 语句：

1	delete from 表名称 where 删除条件;

如：

删除所有年龄小于21岁的数据：

1	delete from students where age<20;

删除表中的所有数据：

1	delete from students;

2.3.5 创建后表的修改

使用 alter table 语句：

添加列

1	alter table 表名 add 列名列数据类型 [after 插入位置];

如：

在表后添加列 address：alter table students add address char(20);
在 age 列后插入 birthday：alter table students add birthday date after age;

修改列

1	alter table 表名 change 列名新列名新列数据类型;

如：

修改列 age 的数据类型：alter table students change age age tinyint unsigned null;

删除列

1	alter table 表名 drop 列名;

如：

删除列 birthday：alter table students drop birthday;

修改表名

1	alter table 表名 rename 新表名;

删除整张表

1	drop table 表名;

删除整个数据库

1	drop database 数据库名;

2.4 修改 root 用户密码

其中一种方法：

1	mysqladmin -u root -p password 新密码

三、在C++工程上使用MySQL

3.1 使用mysql.h

IDE：Visual Studio 2022

3.1.1 VS中配置MySQL

添加包含目录：VC++目录 中的 包含目录 添加上 MySQL 文件夹中的 include 目录。

添加包含目录

添加库目录：VC++目录 中的 库目录 添加上 MySQL 文件夹中的 lib 目录。

添加库目录

添加附加依赖项：链接器 中的 输入 → 附加依赖项 添加上 libmysql.lib。

添加附加依赖项

复制动态库：将 lib 文件夹下的 libmysql.dll 复制到工程文件夹下。

3.1.2 相关函数代码

头文件：mysql.h

// 连接数据库：
MYSQL *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语句:
int mysql_query(MYSQL *mysql, const char *q);

// 获取上一条MySQL语句执行结果：
MYSQL_RES *mysql_store_result(MYSQL *mysql);

// 获取上一条MySQL语句执行结果行数：
uint64_t mysql_affected_rows(MYSQL *mysql);

用类实现一个基础连接数据库，可以执行数据库语句、输出查询结果等功能。

// DataBase.h

#pragma once

#include <mysql.h>
#include <vector>
#include <string>
#include <windows.h>
#include <winsock.h>

class DataBase
{
private:
	bool _state;					// 连接状态
	MYSQL *_conn;					// 数据库连接
	std::vector<MYSQL_FIELD *> fd;	// 字段列数组
	std::vector<std::string> _field;// 字段名
	MYSQL_RES *_res;				// 返回行的查询结果集
	MYSQL_ROW _column;				// 一个行数据的类型安全的表示

public:

	/*
	* @brief 构造函数，初始化参数
	*/
	DataBase();

	/*
	* @brief 连接数据库
	* @param ip IP地址
	* @param name 用户名
	* @param pass 密码
	* @param dataBaseName 数据库名
	* @param port 端口
	*/
	bool connect(std::string const &ip, std::string const &name, std::string const &pass, std::string const &dataBaseName, int const port);

	/*
	* @brief 获取表字段数
	* @param tableName 表名
	*/
	int getTableField(std::string const &tableName);

	/*
	* @brief 查询表
	* @param tableName 表名
	* @return 查询结果
	*/
	std::string query(std::string const &tableName);

	/*
	* @brief 执行 sql 指令
	* @param sentence sql 命令
	*/
	bool implement(std::string const &sentence);
};

//DataBase.cpp

#include "DataBase.h"

DataBase::DataBase() : _state(false), _res(nullptr), _column(nullptr)
{
	_conn = new MYSQL;
}

bool DataBase::connect(std::string const &ip, std::string const &name, std::string const &pass, std::string const &dataBaseName, int const port)
{
	if (_state)
		return false;

	// 初始化数据库
	mysql_init(_conn);

	// 连接数据库
	if (mysql_real_connect(_conn, ip.c_str(), name.c_str(), pass.c_str(), dataBaseName.c_str(), port, NULL, 0))
	{
		_state = true;
		return true;
	}
}

int DataBase::getTableField(std::string const &tableName)
{
	if (!_state)
		return -1;

	// 将字符串格式化为查询数组
	char query[150];
	sprintf_s(query, "show columns from %s", tableName.c_str());

	// 执行查询
	if (mysql_query(_conn, query))
		return -1;

	// 获取查询结果
	_res = mysql_store_result(_conn);
	if (_res == nullptr)
		return -1;

	return mysql_affected_rows(_conn);
}

std::string DataBase::query(std::string const &tableName)
{
	if (!_state)
		return "";

	// 获取列数
	int field = getTableField(tableName);

	// 将字符串格式化为查询数组
	char query[150];
	sprintf_s(query, "select * from %s", tableName.c_str());

	// 执行查询
	if (mysql_query(_conn, query))
		return "";

	// 获取查询结果
	_res = mysql_store_result(_conn);
	if (_res == nullptr)
		return "";

	// 将查询结果转化为字符串输出
	fd.reserve(field);
	fd.resize(field);
	for (int i = 0; i < field; i ++)
		fd[i] = mysql_fetch_field(_res);

	std::string res = "";
	while (_column = mysql_fetch_row(_res))
	{
		for (int i = 0; i < field; i ++)
			res += _column[i], res += "\t";
		res += "\n";
	}
	return res;
}

bool DataBase::implement(std::string const &sentence)
{
	if (!_state)
		return false;

	// 字符串格式化
	char query[150];
	sprintf_s(query, "%s", sentence.c_str());

	// 执行命令
	if (mysql_query(_conn, query))
		return false;

	return true;
}

完整代码见仓库：Gitee - MySQL - C++

3.2 使用QtSQL

Qt6.6.1_msvc2019_64

3.2.1 Qt的SQL模块

VS 中勾选上 SQL 模块：

勾选SQL

3.2.2 数据库驱动

QtSQL 是 Qt 提供的一个访问数据库的接口。但需要数据库驱动才能使用不同类型的数据库。

驱动名称	对应数据库
`QDB2`	IBM DB2
`QIBASE`	Borland Interbase
`QMYSQL`	MySQL
`QOCI`	Oracle Call Interface Driver
`QODBC`	ODBC（包括MS SQL Server）
`QPSQL`	PostgreSQL
`QSQLITE`	SQLite v3
`QSQLITE2`	SQLite v2

很不幸，MySQL 需要额外下载驱动，可前往 Github - thecodemonkey86/qt_mysql_driver 下载对应版本的编译后的文件。

将下载到的压缩包解压后，把 sqldrives 文件夹中的文件复制到 Qt 目录下的 sqldrives 文件夹中。如此处我是： ..\Qt\6.6.1\msvc2019_64\plugins\sqldrivers

添加驱动

3.2.3 连接数据库

通过 QSqlDatabase 类，可以操作数据库。

连接数据库的代码：

// QtSql 连接数据库
QSqlDatabase db;
db = QSqlDatabase::addDatabase("QMYSQL", "connection");

// 配置主机名
db.setHostName("localhost");

// 配置数据库名
db.setDatabaseName("students");

// 配置用户名和密码
db.setUserName("root");
db.setPassword("");

// 配置端口
db.setPort(3306);

// 打开数据库
db.open();

其中函数：

1
2
3

QSqlDatabase addDatabase(
    const QString& type,
    const QString& connectionName = QLatin1StringView(defaultConnection));

创建一个数据库连接，第一个参数指定数据库驱动，第二个参数指定连接名称，多线程访问数据库中每个线程都需要使用不同的连接名称。

3.2.4 访问数据库

通过 QSqlQuery 类，可以访问数据库。

// 创建查询对象
QSqlQuery query(db);

// 执行查询语句
query.exec("...");

可以通过 QSqlQuery::prepare() 和 QSqlQuery::bindValue() 函数进行数据库语句的构造。

如：

void updateUser(QSqlDatabase db,const int &id,const QString &username) {
    QSqlQuery query(db);

    // 构造查询语句
    query.prepare("update newUser set username=:username WHERE id=:id");
    
    // 绑定参数
    query.bindValue(":id", id);
    query.bindValue(":username", username);

    // 执行语句
    query.exec();
}

3.3 学生管理系统demo

demo

完整代码见仓库：Gitee - MySQL - C++

分支：

master：mysql.h方式
QSQL：QtSql方式

Reference：

21分钟 MySQL 入门教程 - https://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html
C/C++ vs2017连接MySQL数据库 - 增删改查(详细步骤) - https://blog.csdn.net/qq_45698148/article/details/117136407
MySQL 教程 - RUNOOB：https://www.runoob.com/mysql/mysql-tutorial.html
Qt零基础系列10：使用Qt如何操作数据库：https://zhuanlan.zhihu.com/p/348174965