Fingsinz's space | 风信梓

MySQL 数据库学习记录

一、认识 MySQL
二、使用 MySQL
三、在C++工程上使用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 模块：

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

完整代码见仓库：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

2024-02-26 该篇文章被 Fingsinz 打上标签: MySQL 归为分类: 笔记