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

视图

概念

视图是一个虚拟表，其内容由查询定义。

同真实的表一样，视图包含一系列带有名称的列和行数据。

视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

基本使用

创建视图

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

案例

create view v_ename_dname(v_ename,v_dname) as select ename, dname     -- 如果需要定义视图的列名，那就 v_ename_dname(视图列名1，视图列名2....)，否则视图的列明会与		原表所查询的列名相同;

from EMP, DEPT where EMP.deptno=DEPT.deptno; 

select * from v_ename_dname; -- 从视图 v_ename_dname 读数据

+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

当我们创建好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';
1
2

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

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 | <== 视图中的数据也发生了变化
+-------+----------+
1
2
3
4
5
6
7
8
9

删除视图

drop view 视图名；
1

视图规则和限制

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

• 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响;(视图可以当做一个别名，如果设计复杂了，到时候简便的通过视图名操作–>（底层还是在原表中操作的时候），效率慢了还不知道哪出现的问题)

• 视图不能添加索引，也不能有关联的触发器或者默认值（只有.frm表属性 文件）

• 视图可以提高安全性，必须具有足够的访问权限;(为某个用户创建相关视图规范其权限)

• order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by ,那么该创建视图中的 order by 将被覆盖（检索select视图的order by 优先级高于试图本身的order by）

• 视图可以和表一起使用;

总结

• 回想我们连表使用笛卡尔积的场景，如果我们需要将这个联合的表进一步当成一个完成的表来使用，那么就需要嵌套冗长的select …inner…as 临时表的select语句;

• 我们可以用创建视图的方式，将inner连表的表信息 以视图的结构记录下来，方便之后直接使用;

用户管理

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

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

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

mysql> use mysql;
Database changed;

mysql> select host,user,authentication_string from user;-- 查看用户信息

+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
-- 可以通过desc user初步查看一下表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14

字段解释：

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

• user： 用户名

• authentication_string： 用户密码通过password函数加密后的字符串

创建用户

语法:

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

案例：

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。容易被攻击....
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

删除用户

语法：

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

示例：

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

修改用户密码

语法：

• 自己改自己密码

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

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

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

案例:

mysql> select host,user, authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| % | root | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | whb | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)


mysql> set password for 'whb'@'localhost'=password('87654321'); -- 用root账户 修改whb的密码
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> select host,user, authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| % | root | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | whb | *5D24C4D94238E65A6407DFAB95AA4EA97CA2B199 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

分配数据库的权限

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

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

语法:

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

说明：

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

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

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

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

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

案例:

• 终端A

-- 使用root账号
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 57test |
| bit_index |
| ccdata_pro |
| innodb_test |
| musicserver |
| myisam_test |
| mysql |
| order_sys |
| performance_schema |
| scott |
| sys |
| test |
| vod_system |
+--------------------+
14 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account |
| student |
| user |
+----------------+
3 rows in set (0.01 sec)

-- 给用户whb赋予test数据库下所有文件的select权限
mysql> grant select on test.* to 'whb'@'localhost';
Query OK, 0 rows affected (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

• 终端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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

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

回收数据库权限

语法：

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

示例：

• root身份，终端A

-- 回收whb对test数据库的所有权限

mysql> revoke all on test.* from 'whb'@'localhost';
Query OK, 0 rows affected (0.00 sec)
1
2
3
4

• -whb身份，终端B

mysql> show databases;-- revoke whb的权限之前
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)

mysql> show databases;-- revoke whb的权限之后，看不到之前允许查看的数据库了
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

远程连接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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

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

• 通过 mysql_get_client_info() 函数，来验证我们的引入是否成功

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

1
2
3
4
5
6
7
8
9

-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

连接MySQL接口介绍

• 初始化mysql_init()

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

• 链接数据库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);
1
2
3
4
5
6
7
8

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

• 下发mysql命令mysql_query()

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

获取sql执行结果

• 获取执行结果mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql);
1

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]即可;
1
2
3
4
5

• 获取记录的内容mysql_fetch_row

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

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result); 
1
2
3
4

模拟读取一次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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25