MySQL的视图,用户管理,C语言连接

小咪咪 2024-03-31 12:58 56阅读 0赞

## 视图 ##

### 概念 ###

> 视图是一个**虚拟表**，**其内容由查询定义**。
> 
> 同真实的表一样，视图包含一系列带有名称的列和行数据。
> 
> **视图的数据变化会影响到基表**，**基表的数据变化也会影响到视图**。

### 基本使用 ###

#### 创建视图 ####

create view 视图名（视图列名） as select语句； -- 体现了 视图的内容由查询定义

**案例**

![在这里插入图片描述][7d3deb9f6e52406c929331e570eff54d.png]

**当我们创建好v\_ename\_dname之后**，我们在储存mysql文件对应的linux目录下(inoodb引擎下)**查看相关文件**，发现这个视图区别于别的正常表;

*  正常表:有.frm 表属性，和.ibd index索引+data数据 两部分!
 *  视图:只有.frm 表属性这一部分;

> 所以我们可以理解为**创建一个视图,就是创建了一种临时表的属性并存储了起来**，并没有copy各种真实数据;
> 
> **操作视图**的时候，**本质还是通过视图中的.frm表属性，去原始的创建该视图的表中进行操作**(比如笛卡尔积联表等)

这么说来，视图最大的意义就是方便我们进行使用tmp临时表的各种操作; eg:笛卡尔积联表的临时表进一步当成表操作，就可以搞个视图，不用select冗长嵌套了;

*  修改了视图，对基表数据有影响

update v_ename_dname set dname='sales' where ename='CLARK';
    select * from EMP where ename='CLARK';

*  修改了基表，对视图有影响

mysql> update EMP set deptno=20 where ename='JAMES'; -- 修改基表
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    mysql> select * from v_ename_dname where ename='JAMES';
    +-------+----------+
    | ename | dname |
    +-------+----------+
    | JAMES | RESEARCH | <== 视图中的数据也发生了变化
    +-------+----------+

#### 删除视图 ####

drop view 视图名；

### 视图规则和限制 ###

*  与表一样，必须唯一命名（不能出现同名视图或表名）
 *  创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响;(视图可以当做一个别名，如果设计复杂了，到时候简便的通过视图名操作–>（底层还是在原表中操作的时候），效率慢了还不知道哪出现的问题)
 *  **视图不能添加索引**，也不能有关联的触发器或者默认值（只有.frm表属性 文件）
 *  视图可以提高安全性，必须具有足够的访问权限;(为某个用户创建相关视图规范其权限)
 *  **order by 可以用在视图中**，但是如果从该视图检索数据 **select 中也含有 order by ,那么该创建视图中的 order by 将被覆盖**（检索select视图的order by 优先级高于试图本身的order by）
 *  视图可以和表一起使用;

### 总结 ###

*  回想我们连表使用**笛卡尔积**的场景，如果我们需要将这个**联合**的表进一步当成一个完成的表来使用，那么就需要**嵌套冗长的select …inner…as 临时表**的select语句;
 *  我们可以用**创建视图的方式，将inner连表的表信息 以视图的结构记录下来**，**方便之后直接使用**;

## 用户管理 ##

*  如果我们只能使用root用户，这样存在安全隐患(root权限太大了)。 这时，就需要使用MySQL的用户管理,如下

![在这里插入图片描述][edbac06054c040eebf8e8616b9f80601.png]

> 其实我们在企业中，当然也是用DBA数据库管理员为我们创造的用户来访问公司内部数据库，不然的话人人都用root是非常危险的，删库跑路或者误删别人的数据库文件咋办？

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

字段解释：

*  host： 表示这个用户可**以从哪个主机登陆**，如果是**localhost**，表示只能从本机登陆;
 *  user： 用户名
 *  authentication\_string： **用户密码通过password函数加密后**的字符串

### 创建用户 ###

语法:

create user '用户名'@'登陆主机/ip' identified by '密码';

案例：

mysql> create user 'whb'@'localhost' identified by '12345678';
    Query OK, 0 rows affected (0.06 sec)
    
    
    mysql> select user,host,authentication_string from user;
    +---------------+-----------+-------------------------------------------+
    | user | host | authentication_string |
    +---------------+-----------+-------------------------------------------+
    | root | % | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
    | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | whb | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 		-- 新增的用户
    +---------------+-----------+-------------------------------------------+
    4 rows in set (0.00 sec)
    
    -- 此时便可以使用新账号新密码进行登陆啦
    
    -- 备注：可能实际在设置密码的时候，因为mysql本身的认证等级比较高，一些简单的密码无法设置，会爆出如下报错：
    -- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
    
    -- 解决方案：https://blog.csdn.net/zhanaolu4821/article/details/93622812
    
    -- 查看密码设置相关要求：SHOW VARIABLES LIKE 'validate_password%';
    
    -- 关于新增用户这里，需要大家注意，不要轻易添加一个可以从任意地方登陆的user。容易被攻击....

### 删除用户 ###

语法：

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

示例：

mysql> select user,host,authentication_string from user;
    
    +---------------+-----------+-------------------------------------------+
    | user | host | authentication_string |
    +---------------+-----------+-------------------------------------------+
    | root | % | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
    | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | whb | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
    +---------------+-----------+-------------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql> drop user whb; -- 尝试删除
    ERROR 1396 (HY000): Operation DROP USER failed for 'whb'@'%' -- <= 直接给个用户名，不能删除，它默认的登录位置是%，表示所有地方可以登陆的用户
    
    
    mysql> drop user 'whb'@'localhost'; -- 正常成功删除用户
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user,host,authentication_string from user;
    +---------------+-----------+-------------------------------------------+
    | user | host | authentication_string |
    +---------------+-----------+-------------------------------------------+
    | root | % | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
    | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    +---------------+-----------+-------------------------------------------+
    3 rows in set (0.00 sec)

### 修改用户密码 ###

语法：

*  自己改自己密码

set password=password('新的密码'); -- 调用了password函数

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

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

案例:

### 分配数据库的权限 ###

MySQL数据库提供的权限列表：  
![在这里插入图片描述][73ad12d1bfb74ac592e5126c204d0e9d.png]

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

语法:

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

说明：

*  权限列表，**多个权限用逗号分开**

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

*  on后跟 \*\*.\*\* : 代表**本系统中的所有数据库的所有对象**（表，视图，存储过程等）
 *  on后跟 库.\* : 表示**某个数据库中的所有数据对象**(表，视图，存储过程等)
 *  identified by为可选项。 如果**用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户**

案例:

*  终端A

*  终端B

-- 使用whb账号
    
    mysql> show databases; -- 授权之前
    
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)
    
    -- 暂停等root用户给whb赋完权之后，再查看
    mysql> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    | test | 			-- 赋完权之后，就能看到新的数据库了
    +--------------------+
    2 rows in set (0.01 sec)
    
    mysql> use test;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed；
    
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | account |
    | student |
    | user |
    +----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from account;
    +----+--------+---------+
    | id | name | blance |
    +----+--------+---------+
    | 2 | 李四 | 321.00 |
    | 3 | 王五 | 5432.00 |
    | 4 | 赵六 | 543.90 |
    | 5 | 赵六 | 543.90 |
    +----+--------+---------+
    4 rows in set (0.00 sec)
    
    mysql> delete from account;
    ERROR 1142 (42000): DELETE command denied to user 'whb'@'localhost' for table 'account' -- ERROR没有删除权限
    
    mysql> show grants for 'whb'@'%';   -- 查看whb的权限
    +-----------------------------------------------+
    | Grants for whb@% |
    +-----------------------------------------------+
    | GRANT USAGE ON *.* TO 'whb'@'%' |
    | GRANT ALL PRIVILEGES ON `test`.* TO 'whb'@'%' |
    +-----------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> show grants for 'root'@'%';  -- 查看root的权限
    +-------------------------------------------------------------+
    | Grants for root@% |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------+
    1 row in set (0.00 sec)

> 注意：如果发现赋权限后，没有生效，执行如下指令：flush privileges; 刷新一下;

### 回收数据库权限 ###

语法：

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

示例：

*  root身份，终端A

-- 回收whb对test数据库的所有权限
    
    mysql> revoke all on test.* from 'whb'@'localhost';
    Query OK, 0 rows affected (0.00 sec)

*  \-whb身份，终端B

## 远程连接MySQL ##

*  前面的部分内容讲了mysql的基础，作为一个后端开发人员，后面我们只关心使用;

> 我们使用C接口库来进行连接，要正确使用，我们需要做一些准备工作：
> 
>  *  保证mysql服务有效
>  *  在官网上下载合适自己平台的mysql connect库，以备后用

### 引入Connector/C ###

-- 我们下载下来的库格式如下:
    [whb@MiWiFi-R1CL-srv lib]$ tree .
    .
    ├── include  -- include
    │ ├── big_endian.h
    │ ├── byte_order_generic.h
    │ ├── byte_order_generic_x86.h
    │ ├── decimal.h
    │ ├── errmsg.h
    │ ├── keycache.h
    │ ├── little_endian.h
    │ ├── m_ctype.h
    │ ├── m_string.h
    │ ├── my_alloc.h
    │ ├── my_byteorder.h
    │ ├── my_compiler.h
    │ ├── my_config.h
    │ ├── my_dbug.h
    │ ├── my_dir.h
    │ ├── my_getopt.h
    │ ├── my_global.h
    │ ├── my_list.h
    │ ├── my_pthread.h
    │ ├── mysql
    │ │ ├── client_authentication.h
    │ │ ├── client_plugin.h
    │ │ ├── client_plugin.h.pp
    │ │ ├── get_password.h
    │ │ ├── plugin_auth_common.h
    │ │ ├── plugin_trace.h
    │ │ ├── psi
    │ │ │ ├── mysql_file.h
    │ │ │ ├── mysql_idle.h
    │ │ │ ├── mysql_mdl.h
    │ │ │ ├── mysql_memory.h
    │ │ │ ├── mysql_ps.h
    │ │ │ ├── mysql_socket.h
    │ │ │ ├── mysql_sp.h
    │ │ │ ├── mysql_stage.h
    │ │ │ ├── mysql_statement.h
    │ │ │ ├── mysql_table.h
    │ │ │ ├── mysql_thread.h
    │ │ │ ├── mysql_transaction.h
    │ │ │ ├── psi_base.h
    │ │ │ ├── psi.h
    │ │ │ └── psi_memory.h
    │ │ ├── service_my_snprintf.h
    │ │ └── service_mysql_alloc.h
    │ ├── mysql_com.h
    │ ├── mysql_com_server.h
    │ ├── mysqld_ername.h
    │ ├── mysqld_error.h
    │ ├── mysql_embed.h
    │ ├── mysql.h
    │ ├── mysql_time.h
    │ ├── mysql_version.h
    │ ├── my_sys.h
    │ ├── my_xml.h
    │ ├── sql_common.h
    │ ├── sql_state.h
    │ ├── sslopt-case.h
    │ ├── sslopt-longopts.h
    │ ├── sslopt-vars.h
    │ └── typelib.h
    └── lib  -- lib
    ├── libmysqlclient.a
    ├── libmysqlclient_r.a -> libmysqlclient.a
    ├── libmysqlclient_r.so -> libmysqlclient.so
    ├── libmysqlclient_r.so.18 -> libmysqlclient.so.18
    ├── libmysqlclient_r.so.18.3.0 -> libmysqlclient.so.18.3.0
    ├── libmysqlclient.so -> libmysqlclient.so.18
    ├── libmysqlclient.so.18 -> libmysqlclient.so.18.3.0
    └── libmysqlclient.so.18.3.0

*  其中 include 包含所有的方法声明， lib 包含所有的方法实现（打包成库）
 *  通过 mysql\_get\_client\_info() 函数，来验证我们的引入是否成功

//cpp文件 test.cpp
    #include <stdio.h>
    #include <mysql.h>
    int main()
    {
        
        printf("mysql client Version: %s\n", mysql_get_client_info());
        return 0;
    }

-- linux shell终端
    [hb@MiWiFi-R1CL-srv lib]$ gcc -o test test.c -I./include -L./lib -lmysqlclient -- test.cpp
    
    [hb@MiWiFi-R1CL-srv lib]$ ls
    include lib test test.c
    
    [hb@MiWiFi-R1CL-srv lib]$ ./test -- 第一次执行失败，少个ldd环境路径
    ./test: error while loading shared libraries: libmysqlclient.so.18: cannot open shared
    object file: No such file or directory
    
    [hb@MiWiFi-R1CL-srv lib]$ export LD_LIBRARY_PATH=./lib -- 动态库查找路径,讲解ldd命令
    
    [hb@MiWiFi-R1CL-srv lib]$ ./test -- 第二次执行成功，显示了mysql_get_client_info()信息
    mysql client Version: 6.1.6

*  至此**引入库的工作已经做完**，接下来就是熟悉接口;

### 连接MySQL接口介绍 ###

*  **初始化**mysql\_init()

//返回的MYSQL *类型相当于创建了一个MYSQL句柄
    MYSQL *mysql_init(MYSQL *mysql);

*  **链接数据库**mysql\_real\_connect()

// 第一个参数就是 上面mysql_init初始化的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 clientflag);

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

*  **下发mysql命令**mysql\_query()

//第一个参数上面已经介绍过，第二个参数为要执行的sql语句,如 “select * from table”。
    int mysql_query(MYSQL *mysql, const char *q);

#### 获取sql执行结果 ####

*  **获取执行结果**mysql\_store\_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql);

sql执行完以后，如果update，insert等语句，那么就看下库中操作成功与否即可(返回非0成功)。**但如果是查询语句，我们当然还要读取数据(查询的结果，读取到cpp业务逻辑中)**，

如果**mysql\_query返回成功，那么我们就通过mysql\_store\_result这个函数来读取结果**。

注意:

> **该函数会返回 MYSQL\_RES 这样一个变量**，该变量主要用于**保存查询的结果**
> 
> 该函数**malloc了一片内存空间来存储查询过来的数据**，所以**我们一定要记的 free(result)**,不然是肯定会造成内存泄漏的。

*  获取结果 行数, 列数，列名

my_ulonglong mysql_num_rows(MYSQL_RES *res); //行数
    
    unsigned int mysql_num_fields(MYSQL_RES *res);  //列数
    
    MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);  //列名 MYSQL_FIELD*相当于一个保存列名的数组，需要用的时候,配合row数和col数进行for循环处理MYSQL_FIELD[i]即可;

*  获取记录的**内容**mysql\_fetch\_row

//它会返回一个MYSQL_ROW变量，MYSQL_ROW其实就是char ** (MySQL进行select打印的时候，对每个字段，其实都是当成一个string字符串处理的); 
    //所以MYSQL_ROW可以当成一个string类型的数组使用;
    
    MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

模拟读取一次seect的全部记录:

//  1:	将列名读入cpp;
    int fields = mysql_num_fields(res); //列数（每条记录有多少个 固定 字段）
    int nums = mysql_num_rows(MYSQL_RES *res); //行数（多少条记录）
    MYSQL_FIELD *field = mysql_fetch_fields(res); //列名（每个字段的名字）
    
    //打印(或其他处理)所有的列名;
    int i = 0;
    for(; i < fields; i++){
        
    	cout<<field[i].name<<" ";
    }
    cout<<endl;
    
    
    //打印(或其他处理)所有的正文内容;
    i = 0;
    MYSQL_ROW line; //二维string数组
    
    for(; i < nums; i++){
        //nums行记录
    	line = mysql_fetch_row(res);
    	int j = 0;
    	for(; j < fields; j++){
        //每行记录(存入line中了),fields列字段(fields个string);
    		cout<<line[j]<<"    ";
    	}
    	cout<<endl;
    }

[7d3deb9f6e52406c929331e570eff54d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/1c4865c7813142acb279e976c69bc7ef.png
[edbac06054c040eebf8e8616b9f80601.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5b6de8c257dc4f6da3a4e5f19c9d281b.png
[73ad12d1bfb74ac592e5126c204d0e9d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/8da11f54a5b8471a92b6118eb4e96818.png