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,工号,姓名,刷卡时间)