mysql视图创建方法

1.下载Navicat

 

2.建立数据库链接

 

用户、密码：kedacom/[email protected]

 

3.双击打开数据库

 

4、ismp_cardmng下新建视图

 

 

 

5、插入以下视图语句：

select  concat(`a1`.`id`,'cl') AS `id`,`c1`.`badge_number` AS `badge_number`,

d1.user_name  as username,

`a1`.`recordtime` AS `checktime`

from ((`ismp_vehiclebayonet`.`vbs_bayonet_history` `a1`

join `ismp_vehiclebayonet`.`vbs_vehicleinfo` `b1`

on((`a1`.`plate_number` = `b1`.`plate_number`)))

join `ismp_usermng`.`ums_app_user_ext` `c1`

on((`b1`.`user_id` = `c1`.`user_id`)))

left join ismp_cardmng.cms_user_info d1

on d1.user_id=c1.user_id

 

UNION

select concat(`a`.`id`,'ry') AS `id`,

`b`.`badge_number`  AS `badge_number`,`c`.`user_name` AS `username`,`a`.`credit_time` AS `recordtime`

from `ismp_cardmng`.`cms_user_info` `c`

join `ismp_cardmng`.`cms_credit_record` `a`

on `c`.`user_id` = `a`.`owner_id`

left join `ismp_usermng`.`ums_app_user_ext` `b`

on convert(`c`.`unique_id` using utf8mb4) = `b`.`user_id`

保存为视图：

 

6、双击查看视图

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7、新建查询，分别执行以下2个语句：

 

create user "test"@"%" identified by "kedacom";

GRANT SELECT ON `ismp_cardmng`.record TO 'test'@'%';

 

 

 

8、退出kedacom账户，用test 账户登陆

 

 

 

 

查看到的结果如下：

（id，工号，姓名，刷卡时间）