MySQL：视图+用户管理+访问+连接池原理

一、视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样（相当于是把查询的内容当成一个临时表来使用），视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

1.1 为什么需要视图

案例：

当我们需要频繁从上面两表中获取用户名和部门名称的时候，我们会发现每次都需要频繁地进行内连接和条件设置，显然很麻烦

所以我们可以用视图来存储用户名+部门名称的查询结构

1.2 视图的基本使用

创建视图

create view 视图名 as select语句；

创建视图之后会多了一个表

我们将查询出来的结果放到视图里其实就相当于存到一个临时的表结构（高频使用）里

视图的改变会影响基表

基表的改变会影响视图

删除视图

drop view 视图名；

1.3视图的规则和限制

1、与表一样，必须唯一命名（不能出现同名视图或表名）

2、创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响

3、视图不能添加索引，也不能有关联的触发器或者默认值

4、视图可以提高安全性，必须具有足够的访问权限

5、order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by ,那么该视图中的order by 将被覆盖

6、视图可以和表一起使用

二、用户管理

如果我们只能使用root用户，这样存在安全隐患。这时，就需要使用MySQL的用户管理。

2.1 用户

2.1.1 用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中

mysql> use mysql;mysql> select host,user,authentication_string from user;

字段解释：

host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆

user：用户名

authentication_string：用户密码通过password函数加密后的

*_priv：用户拥有的权限

2.1.2 创建用户

create user '用户名'@'登陆主机/ip' identified by '密码';--密码会被做哈希加密保存到user表里

–备注：可能实际在设置密码的时候，因为mysql本身的认证等级比较高，一些简单的密码无法设置，会爆出如下报错：-- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

– 解决方案：Your password does not satisfy the current policy requirements-CSDN博客

–查看密码设置相关要求：

SHOW VARIABLES LIKE 'validate_password%';

–注意：关于新增用户这里，需要大家注意，不要轻易添加一个可以从任意地方登陆的user。

登录之前刷新一下

flush privileges;

– 此时便可以使用新账号新密码进行登陆啦

2.1.3 删除用户

drop user '用户名'@'主机名'

2.1.4 设置一个任意主机都可以登录的用户

如果MySQL服务器直接暴露在公网，就会存在很大的风险，为了保证数据安全，MySQL服务器的端口是不对外开放的。

2.1.5修改用户密码

mysql会将用户设置的明文密码通过password函数加密之后再抄到表上！

自己改自己的密码

set password=password('新的密码');

root用户修改指定用户的密码

set password for '用户名'@'主机名'=password('新的密码')；

2.2 数据库的权限

MySQL数据库提供的权限列表：

2.2.1 给用户授权

刚创建的用户没有任何权限。需要给用户授权。

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：权限列表，多个权限用逗号分开

grant select on ... grant select, delete, create on .... grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限

*.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）

库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)

identied by可选。如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

root建一个表然后给这个表权限

这个时候张三就能看到这个表了

2.2.2 查看现有权限

特定用户现有查看权限 
mysql> show grants for 'zhangsan'@'%';

注意：如果发现赋权限后，没有生效，执行如下指令：

flush privileges;

2.2.3回收权限

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；

三、C/C++访问数据库

要使用C语言连接mysql，需要使用mysql官网提供的库，大家可以去官网下载我们使用C接口库来进行连接要正确使用，我们需要做一些准备工作：

1、保证mysql服务有效

先创建一个测试的数据库

root：建表之后赋权

cyx：

2、在官网上载适己台mysql connect库，以备后用 https://www.mysql.com/

找到右下角的

找到

先查一下我们mysql的版本

他默认是9.2.0版本的，我们如果想用老版本可以点

//跳过一下基础IO那里动静态库的引入

但是其实最早用yum源下载mysql-community-server的时候就已经把相关的库都配置好了！！

centos安装mysql以及mysql-devel_mysql-devel安装包下载-CSDN博客

要用C++连接的必须再安装mysql-devel。

其中 include 包含所有的方法声明， lib 包含所有的方法实现（打包成库）

3.1 Connector/C库的引入

test.cc

#include<iostream>
#include<mysql.h>
using namespace std;
int main()
{cout<<"mysql client Version: "<< mysql_get_client_info()<<endl;return 0;
}

makefile

mytest:test.ccg++ -o $@ $^ -std=c++11 -I/usr/include/mysql -L/lib64/mysql -lmysqlclient
.PHONY:clean
clean:rm -rf mytest

3.2C/C++进行增删查改

先找到官方文档找库的使用说明：https://www.mysql.com/

找到C API找5.7

找到

可以开个翻译

3.2.1初始化mysql_init

要使用库，必须先进行初始化！

MYSQL *mysql_init(MYSQL *mysql);//创建一个MYSQL对象

如： MYSQL *mfp = mysql_init(NULL)

//初始化数据库MYSQL *mfp = mysql_init(NULL);if(mfp==nullptr) {cerr<<"mysql_init error"<<endl;return 1;

3.2.2链接数据库mysql_real_connect

初始化完毕之后，必须先链接数据库，在进行后续操作。（mysql网络部分是基于TCP/IP的）

MYSQL *mysql_real_connect(MYSQL *mysql, //MYSQL对象const char *host,//主机? ? ? ? ? ? ? ? ? ? ? const char *user,//用户? ? ? ? ? ? ? ? ? ? ? const char *passwd,//密码? ? ? ? ? ? ? ? ? ? ? const char *db,//链接的数据库? ? ? ? ? ? ? ? ? ? ? unsigned int port,//端口号? ? ? ? ? ? ? ? ? ? ? const char *unix_socket,//域间套接字，这里设null? ? ? ? ? ? ? ? ? ? ? unsigned long clientflag);//设置为0

第一个参数 MYSQL是 C api中一个非常重要的变量（mysql_init的返回值），里面内存非常丰富，有port,dbname,charset等连接基本参数。它也包含了一个叫 st_mysql_methods的结构体变量，该变量里面保存着很多函数指针，这些函数指针将会在数据库连接成功以后的各种数据操作中被调用。

 //链接数据库
if(mysql_real_connect(mfp,host.c_str(),user.c_str(),password.c_str(),db.c_str(),port,nullptr,0)==nullptr){cerr<<"mysql_real_connect error"<<endl;return 2;}cout<<"connect success"<<endl;

3.3.3命令行 show processlist

我们接下来可以进一步确定是否链接上了！

show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

以下是程序运行之前，查一下

以下我们让进程sleep 10秒

然后运行程序再查会发现多了一个运行线程

3.2.4 下发mysql命令mysql_query

int mysql_query(MYSQL *mysql, const char *q);

第一个参数上面已经介绍过，第二个参数为要执行的sql语句,如“select * from table”。

返回值0表示成功，非0表示失败

为了方便验证，我们给conn数据库建个 user表

3.2.4.1 模拟MySQL语句

//尝试接受命令string cmd;while(true){cout<<"MySQL>>";if(!getline(cin,cmd) || cmd=="quit") break;int n=mysql_query(mfp,cmd.c_str());if(n==0) cout<<cmd<<"success"<<n<<std::endl;else cout<<cmd<<"failed"<<n<<std::endl;}

先验证quit和ctrl+d

在验证插入表：

3.2.4.2快速下达指令

 //快速下达指令string cmd="update user set name='lisi' where id=1;";int n=mysql_query(mfp,cmd.c_str());if(n==0) cout<<cmd<<"success"<<endl;else cout<<cmd<<"failed"<<endl;

3.2.4.3 中文乱码mysql_set_character_set

为什么这里会出现乱码问题呢？？原因是客户端和服务器在编码统一的问题上没有达成一致

设置连接的默认字符集的utf8，原始默认是latin1 我们可以在链接成功后设置一下

mysql_set_character_set(mfp,"utf8");

3.2.5获取执行结果mysql_store_result

sql执行完以后，如果是查询语句，我们当然还要读取数据，如果update，insert，delete（增shan）等语句，那么就看下操作成功与否即可。

我们来看看如何获取查询结果：如果mysql_query返回成功，那么我们就通过mysql_store_result这个函数来读取结果。

MYSQL_RES *mysql_store_result(MYSQL *mysql);

该函数会调用MYSQL变量中的st_mysql_methods中的 read_rows 函数指针来获取查询的结果。同时该函数会返回

MYSQL_RES 这样一个变量，该变量主要用于保存查询的结果（多级指针数组）。同时该函数malloc了一片内存空间来存储查询过来的数据，所以我们一定要记的 free(result),不然是肯定会造成内存泄漏的。

执行完mysql_store_result以后，其实数据都已经在MYSQL_RES 变量中了，下面的api基本就是读取MYSQL_RES 中的数据。

1、获取结果行数mysql_num_rows

my_ulonglong mysql_num_rows(MYSQL_RES *res);

2、获取结果列数mysql_num_elds

unsigned int mysql_num_fields(MYSQL_RES *res);

3、获取列名mysql_fetch_elds

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);

MYSQL_FIELD 是属性

如：

 //获取属性int nrows=mysql_num_rows(res);cout<<"nrows="<<nrows<<endl;int ncols=mysql_num_fields(res);cout<<"ncols="<<ncols<<endl;MYSQL_FIELD *flds=mysql_fetch_fields(res);for(int i=0;i<ncols;++i){cout<<flds[i].name<<"	";}cout<<endl;

4.获取结果内容mysql_fetch_row（很像迭代器）

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

它会返回一个MYSQL_ROW变量，MYSQL_ROW其实就是char **.就当成一个二维数组来用吧。

 // 获取内容int i = 0; 
MYSQL_ROW line; 
for(; i < nrows; i++){ line = mysql_fetch_row(res); //迭代器遍历 可以拿到一行数据int j = 0; for(; j < ncols; j++){ cout<<line[j]<<"	"; } cout<<endl; 
}

5、清空结果集 mysql_free_result

void mysql_free_result(MYSQL_RES *result)

3.2.6关闭mysql链接mysql_close

void mysql_close(MYSQL *sock);

3.2.7 支持事务等常用操作

my_bool STDCALL mysql_autocommit(MYSQL * mysql, my_bool auto_mode); 
my_bool STDCALL mysql_commit(MYSQL * mysql); 
my_bool STDCALL mysql_rollback(MYSQL * mysql);

四、mysql图形化界面方案推荐与演示

11 款超赞的 MySQL 图形化工具，好用！-CSDN博客

phpMyAdmin可以安装到云服务器上，然后拿浏览器访问云服务器，登录后就能看到

但是安装起来有点麻烦

体验很好，但是要收费

体验很好，但要收费

是mysql官方提供的图形化界面方案，免费的，体验一般

下载方式：https://www.mysql.com/

点击

然后直接下载到本地

使用图形化界面，除了像phpMyAdmin这样的基于web的mysql图形化界面解决方案，注定了图形化界面一定是在你本地的机器上，而我们用的云服务器是在云上的，从官方上看，云服务器如果部署了mysql，这mysql的端口号一定不能暴露，而是应该都在内网当中，当一个公司员工想访问内网的mysql要经历防火墙、LVS、诸如堡垒机这样的机器，才能进入到内网中在外部访问公司级别的数据库，但是一般的刚创业的小公司出于成本考虑，可能会在开发时把端口号暴露公网，就有点不太好，一般来说我们线上部署完服务后，数据库服务应该单独在云上单独买机器，一定要和web服务器（可能是两台机器）和mysql部署在同一个内网中，隐藏端口号，否则很容易被攻击。