mysql-视图入门（一）

视图简述

MySQL视图是一个虚拟表，其内容由查询定义.同真实的表一样，可以显示查询列和数据行，但视图并不以数据行的形式存储于数据库中.一般用于保存复杂sql逻辑的实现，方便使用，且每次使用的时候都会进行sql的查询.

视图实践

ft_v_user

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_user` AS
 
select `u`.`user_code` AS `user_code`,`u`.`user_name` AS `user_name`,
`u`.`user_type` AS `user_type`,`u`.`create_user` AS `create_user`,`u`.`register_time` AS `register_time`,
`u`.`pwd_change_time` AS `pwd_change_time`,`u`.`sys_privilege` AS `sys_privilege`,`u`.`sys_time` AS `sys_time`,
`ur`.`role_code` AS `role_code`,`ur`.`role_name` AS `role_name`,`ur`.`platform_code` AS `platform_code`,`ur`.`platform_name` AS `platform_name`,
`d`.`code` AS `dept_code`,`d`.`name` AS `dept_name` 
from ((
	(`sys_user` `u` left join `ft_v_user_role` `ur` on((`u`.`user_code` = `ur`.`user_code`))) 
	left join `sys_user_dept` `ud` on((`u`.`user_code` = `ud`.`user_code`))
	) 
	left join `sys_dept` `d` on((`ud`.`dept_code` = `d`.`code`))
)

#分析：平台用户表关联ft_v_user_role视图(用户信息关联角色信息关联平台信息逻辑)关联用户部门关系表(中间表，通过部门编号关联)再关联部门信息
#简述：用户信息关联角色信息关联平台信息关联部门信息

#去括号精简版，显示结果一致
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_user` AS

select `u`.`user_code` AS `user_code`,`u`.`user_name` AS `user_name`,
`u`.`user_type` AS `user_type`,`u`.`create_user` AS `create_user`,`u`.`register_time` AS `register_time`,
`u`.`pwd_change_time` AS `pwd_change_time`,`u`.`sys_privilege` AS `sys_privilege`,`u`.`sys_time` AS `sys_time`,
`ur`.`role_code` AS `role_code`,`ur`.`role_name` AS `role_name`,`ur`.`platform_code` AS `platform_code`,`ur`.`platform_name` AS `platform_name`,
`d`.`code` AS `dept_code`,`d`.`name` AS `dept_name` 
from 
	`sys_user` `u` left join `ft_v_user_role` `ur` on(`u`.`user_code` = `ur`.`user_code`)
	left join `sys_user_dept` `ud` on(`u`.`user_code` = `ud`.`user_code`)
	
	left join `sys_dept` `d` on(`ud`.`dept_code` = `d`.`code`)

ft_v_user_role

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_user_role` AS 

#用户表(用户登录名字段)关联用户角色关系表(关联到角色表的中间表，关联出角色编号进而关联角色详情)再关联系统角色表（关联）再关联管理平台表(管理平台和系统角色表通过平台编号关联)
#角色表和平台表通过共有字段platform_code关联，平台用户表和用户角色关系表通过共有字段user_code关联，借助关联字段实现显示列的组装
#简述：用户信息关联角色信息关联管理平台信息
select `u`.`user_code` AS `user_code`,
`r`.`role_code` AS `role_code`,`r`.`platform_code` AS `platform_code`,`r`.`role_name` AS `role_name`,
`r`.`memo` AS `memo`,`r`.`role_status` AS `role_status`,`r`.`sys_privilege` AS `sys_privilege`,`r`.`sys_time` AS `sys_time`, 
`p`.`platform_name` AS `platform_name`
from ((
	(`sys_user` `u` join `sys_user_role` `ur` on((`u`.`user_code` = `ur`.`user_code`))) 
	join `sys_role` `r` on((`ur`.`role_code` = `r`.`role_code`))
	) 
	left join `sys_platform` `p` on((`r`.`platform_code` = `p`.`platform_code`))
)

ft_v_user_platform

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_user_platform` AS 

select `u`.`user_code` AS `user_code`,`u`.`pwd` AS `pwd`,`u`.`user_name` AS `user_name`,`u`.`company_code` AS `company_code`,
`u`.`dept_code` AS `dept_code`,`u`.`user_type` AS `user_type`,`u`.`create_user` AS `create_user`,`u`.`register_time` AS `register_time`,
`u`.`pwd_change_time` AS `pwd_change_time`,`u`.`op_user` AS `op_user`,`u`.`sys_time` AS `sys_time`,
`up`.`platform_code` AS `platform_code`,
`p`.`platform_name` AS `platform_name` 
from ((
	`sys_user` `u` left join `sys_user_platform` `up` on((`up`.`user_code` = `u`.`user_code`))) 
	left join `sys_platform` `p` on((`up`.`platform_code` = `p`.`platform_code`))
)


#分析：平台用户表关联用户管理平台关系表(中间表，关联到具体平台信息)再关联管理平台表
#用户表和用户平台关系表通过用户编号维护关系，管理平台表和用户管理平台关系表通过管理平台编号维护关系
#简述：用户信息关联管理平台信息

ft_v_role_menu

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_role_menu` AS 

select `sys_role_menu`.`role_code` AS `role_code`,`sys_role_menu`.`menu_code` AS `menu_code`,
`sys_menu`.`menu_name` AS `menu_name`,`sys_menu`.`menu_url` AS `menu_url`,`sys_menu`.`menu_icon` AS `menu_icon`,
`sys_menu`.`parent_code` AS `parent_code`,`sys_menu`.`menu_sort` AS `menu_sort` 
from (
	(`sys_role_menu` join `sys_menu` on((`sys_role_menu`.`menu_code` = `sys_menu`.`menu_code`))) 
	join `sys_role` on((`sys_role_menu`.`role_code` = `sys_role`.`role_code`))
)

#分析：角色菜单关系表(menu_code)关联系统菜单表再关联(role_code)系统角色表
#系统角色表(中间表-角色菜单关系表)关联系统菜单表.每个角色有相关菜单的操作权限.
#简述：角色信息关联系统菜单信息

ft_v_role 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_role` AS 

select `r`.`role_code` AS `role_code`,`r`.`role_name` AS `role_name`,`r`.`role_status` AS `role_status`,`r`.`memo` AS `memo`,`r`.`platform_code` AS `platform_code`,`r`.`op_user` AS `op_user`,`r`.`sys_time` AS `sys_time`,`r`.`sys_privilege` AS `sys_privilege`,
`p`.`platform_name` AS `platform_name` 
from (
	`sys_role` `r` left join `sys_platform` `p` on((`r`.`platform_code` = `p`.`platform_code`))
)

#分析：系统角色信息表(platform_code)关联管理平台表
#简述：系统角色信息关联平台信息（角色是基于平台的）

ft_v_menu 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_menu` AS 

select `m`.`menu_code` AS `menu_code`,`m`.`menu_name` AS `menu_name`,`m`.`menu_url` AS `menu_url`,
`m`.`menu_icon` AS `menu_icon`,`m`.`menu_sort` AS `menu_sort`,`m`.`memo` AS `memo`,`m`.`parent_code` AS `parent_code`,
`m`.`platform_code` AS `platform_code`,`m`.`op_user` AS `op_user`,`m`.`sys_time` AS `sys_time`,
`p`.`platform_name` AS `platform_name` 
from (`sys_menu` `m` left join `sys_platform` `p` on((`m`.`platform_code` = `p`.`platform_code`))) 
where (`p`.`platform_name` is not null)

#分析：系统菜单表(platform_code)关联管理平台表
#简述：系统菜单信息关联平台信息(菜单项基于管理平台)

#去除括号-简略版（结果一致） 
select `m`.`menu_code` AS `menu_code`,`m`.`menu_name` AS `menu_name`,`m`.`menu_url` AS `menu_url`,
`m`.`menu_icon` AS `menu_icon`,`m`.`menu_sort` AS `menu_sort`,`m`.`memo` AS `memo`,`m`.`parent_code` AS `parent_code`,
`m`.`platform_code` AS `platform_code`,`m`.`op_user` AS `op_user`,`m`.`sys_time` AS `sys_time`,
`p`.`platform_name` AS `platform_name` 
from `sys_menu` `m` left join `sys_platform` `p` on(`m`.`platform_code` = `p`.`platform_code`) 
where `p`.`platform_name` is not null

ft_v_user_tofu

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ft_v_user_tofu` AS 

select `ut`.`user_code` AS `user_code`,`ut`.`tofu_code` AS `tofu_code`,
`t`.`platform_code` AS `platform_code` 
from (
	`sys_user_tofu` `ut` left join `sys_tofu` `t` on((`ut`.`tofu_code` = `t`.`tofu_code`))
)

#分析：用户豆腐块关系表(tofu_code)关联豆腐块信息表
#简述：用户信息关联豆腐块信息（豆腐块信息表关联了平台编号）

#简洁版（结果一致）
select `ut`.`user_code` AS `user_code`,`ut`.`tofu_code` AS `tofu_code`,
`t`.`platform_code` AS `platform_code` 
from (
	`sys_user_tofu` `ut` left join `sys_tofu` `t` on `ut`.`tofu_code` = `t`.`tofu_code`
)