Linux下的C++项目开发：聊天室（二）

MySQL数据库

MySQL基础

数据库一般分为关系型数据库和非关系型数据库，关系型数据库有我们常见的 MySQL、Oracle 等，非关系型数据库有 Redis 等。

关系型数据库是用二维表格模型来组织数据信息的数据库。MySQL 实际上是一个关系数据库管理系统，在这个系统里面可以有多个数据库，每个数据库中可以有多张表，每张表中可以有多列属性和多行数据。

比如我们要添加用户注册功能，就可以创建一个专用的数据库，在数据库中建一张叫 USER 的表，表中包含用户名 NAME 和密码 PASSWORD 两列属性，然后下面的若干行就是具体的数据。

SQL结构化查询语言：

SQL 即结构化查询语言，是一种数据库查询语言，用来管理关系数据库系统，以及对数据进行增删查改等操作。

Linux下启动MySQL：

首先启动 MySQL 服务，如下：

1	sudo service mysql start

进入MySQL控制台：

1 2	mysql -u root -p // 输入密码

接下来就可以输入各种 SQL 语句管理数据库系统了。

C++连接MySQL的环境配置：

更新源并安装 dev 组件：

1 2	sudo apt update sudo apt install -y libmysqlclient-dev

安装完毕之后查看 /usr/include/mysql 目录下，是否有 mysql.h 文件，如果有的话就说明安装成功：

1	ls /usr/include/mysql\|grep mysql.h

测试连接MySQL

常用函数：

初始化MYSQL结构函数：

MYSQL *mysql_init(MYSQL *mysql);
/*
描述：
	分配或初始化与mysql_real_connect()相适应的MYSQL对象。如果mysql是NULL指针，该函数将分配、初始化、并返回新对象。否则，将初始化对象，并返回对象的地址。如果mysql_init()分配了新的对象，当调用mysql_close()来关闭连接时。将释放该对象。
返回值：
	初始化的MYSQL*句柄。如果无足够内存以分配新的对象，返回NULL。
*/

连接MySQL函数：

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 client_flag);
/*
描述：
	尝试与运行在主机上的MySQL数据库引擎建立连接。在你能够执行需要有效MySQL连接句柄结构的任何其他API函数之前，mysql_real_connect()必须成功完成。
参数：
	mysql：已有MYSQL结构的地址，调用mysql_real_connect()之前，必须调用mysql_init()来初始化MYSQL结构。
	host：主机名或IP地址。如果“host”是NULL或字符串"localhost"，连接将被视为与本地主机的连接。
	user：包含用户的MySQL登录ID。如果“user”是NULL或空字符串""，用户将被视为当前用户。
	passwd：包含用户的密码。如果“passwd”是NULL，仅会对该用户的（拥有1个空密码字段的）用户表中的条目进行匹配检查。
	db：数据库名称。如果db为NULL，连接会将默认的数据库设为该值。
	port：端口号，如果“port”不是0，其值将用作TCP/IP连接的端口号。注意，“host”参数决定了连接的类型。一般设置为0.
	unix_socket：一般设置为NULL，如果unix_socket不是NULL，该字符串描述了应使用的套接字或命名管道。注意，“host”参数决定了连接的类型。
	client_flag：标志位，通常为0，但也能将其设置为一些标志的组合，以允许特定功能。
返回值：
	如果连接成功，返回MYSQL*连接句柄。如果连接失败，返回NULL。对于成功的连接，返回值与第1个参数的值相同。
*/

MySQL查询函数：

int mysql_query(MYSQL *mysql, const char *query);
/*
描述：
	执行由“Null终结的字符串”查询指向的SQL查询。正常情况下，字符串必须包含1条SQL语句，而且不应为语句添加终结分号（‘;’）或“\g”。如果允许多语句执行，字符串可包含多条由分号隔开的语句。
	mysql_query()不能用于包含二进制数据的查询，应使用mysql_real_query()取而代之（二进制数据可能包含字符‘\0’，mysql_query()会将该字符解释为查询字符串结束）。
参数：
	mysql：指向MYSQL类型的指针
	query：一条查询语句，一般以分号结尾
返回值：
	如果查询成功，返回0。如果出现错误，返回非0值
*/

测试代码：

// test_mysql.cpp
#include<iostream>
#include <mutex>
#include <atomic>
#include <string>
#include <memory>
#include <mysql/mysql.h>
#include <vector>
#include <iostream>
using namespace std;
int main(){
    MYSQL *con = mysql_init(NULL);
    mysql_real_connect(con, "127.0.0.1", "root", "", "test_connect", 0, NULL, CLIENT_MULTI_STATEMENTS);
    // 标志位：通知服务器，客户端可能在单个字符串内发送多条语句（由‘;’隔开）。如果未设置该标志，将禁止多语句执行。
    string str="INSERT INTO user VALUES ('FYL','abc123');";
    if(!mysql_query(con,str.c_str()))
        cout<<"success!";
}

MySQL建表

聊天室项目中，将用户注册的账号与密码发送到服务器端，并保存在服务器的 MySQL 数据库中。进入服务器端的MySQL控制台：

首先在 MySQL 控制台创建数据库 ChatProject，如下：

1	create database ChatProject;

接下来先用 use 命令切换到新建的数据库。

1	use ChatProject;

然后新建一张表格叫 USER 用来保存账号信息，表中有账号 NAME 和密码 PASSWORD 两项属性，都为 VARCHAR 可变长度字符串类型，且将账号 NAME 设为 PRIMARY KEY 主键，主键不允许重复保证了账号的唯一性，而且主键能自动建立索引加快查询速度。

CREATE TABLE USER(
    NAME VARCHAR(20) PRIMARY KEY,
    PASSWORD VARCHAR(20)
);

建好表之后我们可以查看当前数据库中所有的表格。

1	show tables;

最后输入 exit 即可离开 MySQL 控制台回到终端。

常用SQL语句

SQL语句类别

数据定义语言 DDL（Data Definition Language）：例如CREATE，DROP，ALTER等，对逻辑结构等有操作的，其中包括表结构，视图和索引。
数据查询语言 DQL（Data Query Language）：即查询操作，以SELECT关键字为主，各种简单查询、连接查询等都属于 DQL。
数据操纵语言 DML（Data Manipulation Language）：例如INSERT，UPDATE，DELETE等，对数据进行操作的。DQL 与 DML共同构建了多数初级程序员常用的增删改查操作，而查询是较为特殊的一种，被划分到 DQL 中。
数据控制功能 DCL（Data Control Language）：例如GRANT，REVOKE，COMMIT，ROLLBACK等，对数据库安全性、完整性等有操作的，可以简单的理解为权限控制等。

超键与主键

超键：在关系中，能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。
外键：在一个表中存在的另一个表的主键称此表的外键，外键可以有重复的, 可以是空值。外键是用来和其他表建立联系用的。

SQL约束类型：

约束是一种简单地强加于表中一列或多列的限制，从而保证表中数据一致性（准确和可靠）。以下为六大约束：

非空约束（NOT NULL）：保证该字段值一定不为空；
默认约束（DEFAULT）：保证字段有默认值；
主键约束（PRIMARY KEY）：标志一列或者多列，并保证其值在表内的唯一性；
外键约束（FOREIGN KEY）：限制一列或多列中的值必须被包含在另一表的外键列中，并且在级联更新或级联删除规则建立后也可以限制其他表中的可用值；
唯一约束（UNIQUE）：限制一列或多列的值，保证字段值在表内的唯一性，可以为空（主键约束是一种特殊类型的唯一约束）；
检查约束（CHECK）：限制一列的可用值范围。

char 与 varchar 的区别

char 表示定长字符串，长度是固定的，最多能存放的字符个数为 255，和编码无关；而 varchar 表示可变长字符串，长度是可变的，最多能存放的字符个数为 65532；
使用 char 时，如果插入数据的长度小于 char 的固定长度时，则用空格填充；
因为固定长度，char 的存取速度比 varchar 快很多，同时缺点是会占用多余空间，属于空间换时间；

关联查询

在项目开发过程中，使用数据库查询语句时，有很多需求都是要涉及到较为复杂或者多表的连接查询，需要关联查询实现。以下为总结的 MySQL 的五种关联查询。

交叉连接（CROSS JOIN）

除了在FROM子句中使用 逗号间隔连接的表 外，SQL 还支持另一种被称为交叉连接的操作，它们都返回被连接的两个表所有数据行的 笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。惟一的不同在于，交叉连接分开列名时，使用 CROSS JOIN 关键字而不是逗号，即以下两个表达式等价：

1 2	SELECT * FROM A, B SELECT * FROM A CROSS JOIN B

内连接（INNER JOIN）

内连接分为三类，分别是 等值连接：ON A.id = B.id、不等值连接：ON A.id > B.id 和 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid。

外连接（LEFT JOIN/RIGHT JOIN）

左外连接：以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用 NULL填充，可以简写成 LEFT JOIN；

右外连接：以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成 RIGHT JOIN；

联合查询（UNION 与 UNION ALL）

1	SELECT * FROM A UNION SELECT * FROM B UNION ...

联合查询就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的 列数要相等，相同的记录行会合并；

如果使用 UNION ALL，不会合并重复的记录行，所以效率更高。

全连接（FULL JOIN）

MySQL 本身不支持全连接，但可以通过联合使用LEFT JOIN、UNION和RIGHT JOIN来实现。

1	SELECT * FROM A LEFT JOIN B ON A.id = B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id = B.id

子查询及分类

多条 MySQL 语句嵌套使用时，内部的 MySQL 查询语句称为子查询。子查询是一个SELECT语句，它嵌套在另一个SELECT、SELECT…INTO语句、INSERT…INTO 语句、DELETE 语句、 UPDATE 语句或嵌套在另一子查询中。

MySQL 的子查询是多表查询的一个重要组成部分，常常和 连接查询 一起使用，是多表查询的基础。

子查询可分为四类：

标量子查询

查询返回单一值的标量，如一个数字或一个字符串，是子查询中最简单的形式。
列子查询

子查询返回的结果集是 N 行一列，该结果通常来自对表的 某个字段 查询返回。
行子查询

子查询返回的结果集是一行 N 列，该结果通常是对表的 某行数据 进行查询而返回的结果集
表子查询

子查询返回的结果集是 N 行 N 列的一个表数据。

DROP、DELETE 与 TRUNCATE 的区别

三种都可以表示删除，其中的细微区别之处如下：

	DROP	DELETE	TRUNCATE
SQL语句类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	从数据库中删除表，所有的数据行，索引和权限也会被删除	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据
删除速度	删除速度最快	删除速度慢，需要逐行删除	删除速度快

因此，在不再需要一张表的时候，采用 DROP；在想删除部分数据行时候，用 DELETE；在保留表而删除所有数据的时候用 TRUNCATE。

UNION 与 UNION ALL 的区别

UNION 用于把来自多个SELECT语句的结果组合到一个结果集合中，MySQL 会把结果集中 重复的记录删掉，而使用 UNION ALL，MySQL 会把所有的记录返回，且效率高于UNION。