新建、更改视图例子(可直接复制)

1.新建视图：
http://www.w3school.com.cn/sql/sql_view.asp
w3school的教程并不能让我直接快速新建视图，因此新建视图例如：
hotel_test表示数据库名
v_mum_sysmessage是视图名称

CREATE VIEW hotel_test.v_mum_sysmessage
AS
(SELECT 
m.id AS mumId, 
m.mumname AS mumname,
sm.type AS type,
sm.title AS title,
sm.subTitle AS subTitle,
sm.hasUrl AS hasUrl,
sm.url AS url,
sm.content AS content,
sm.validFlg AS validFlg,
sm.delFlag AS delFlag,
sm.releaseTime AS releaseTime,
sm.effectiveTime AS effectiveTime,
sm.createAt AS createAt,
sm.updateAt AS updateAt,
sm.deviceId AS deviceId,
sm.uid AS uid,
sm.readFlg AS readFlg,
sm.readedTime AS readedTime
FROM
 stab_system_message sm,stab_museum m
WHERE m.id = sm.mumId

);

2.修改视图：
hotel_Dev是数据库名
v_log_device_user_mum是要修改的视图
DELIMITER是分隔符，与sql语句无具体关系

DELIMITER $$

ALTER ALGORITHM=UNDEFINED DEFINER=`hotel_Dev`@`%` SQL SECURITY DEFINER VIEW `v_log_device_user_mum` AS 
SELECT
  `lg`.`id`           AS `id`,
  `lg`.`deviceId`     AS `deviceId`,
  `lg`.`type`         AS `type`,
  `lg`.`degree`       AS `degree`,
  `lg`.`code`         AS `code`,
  `lg`.`deviceStatus` AS `deviceStatus`,
  `lg`.`result`       AS `result`,
  `lg`.`uid`          AS `uid`,
  `lg`.`reportTime`   AS `reportTime`,
  `d`.`name`          AS `deviceName`,
  `dt`.`deviceType`   AS `deviceType`,
  `dt`.`name`         AS `deviceTypeName`,
  `d`.`parent_id`     AS `pavilionId`,
  `pv`.`name`         AS `pavilionName`,
  `pv`.`parent_id`    AS `mumId`,
  `m`.`mumname`       AS `mumname`,
  `u`.`name`          AS `username`,
  `u`.`expId`         AS `expId`,
  `u`.`expPic`        AS `expPic`,
  `p`.`id`            AS `positionId`,
  `p`.`position_name` AS `positionName`
FROM (((((((`stab_controll_log` `lg`
         LEFT JOIN `stab_device` `d`
           ON ((`lg`.`deviceId` = `d`.`id`)))
        LEFT JOIN `stab_devicetype` `dt`
          ON ((`d`.`device_type` = `dt`.`deviceType`)))
       LEFT JOIN `stab_pavilion` `pv`
         ON ((`d`.`pavilion_id` = `pv`.`id`)))
      LEFT JOIN `stab_museum` `m`
        ON ((`pv`.`parent_id` = `m`.`id`)))
     LEFT JOIN `stab_user` `u`
       ON ((`lg`.`uid` = `u`.`id`)))
    LEFT JOIN `stab_user_position` `up`
      ON ((`lg`.`uid` = `up`.`uid`)))
   LEFT JOIN `stab_position` `p`
     ON ((`up`.`pid` = `p`.`id`)))$$

DELIMITER ;

关于select中的left join on :
http://www.w3school.com.cn/sql/sql_join_left.asp
https://blog.csdn.net/weixin_36174051/article/details/80377061

alter  VIEW `v_worksheet_device` AS 
SELECT
`ws`.`id`                 AS `worksheet_id`,
`ws`.`nation_code`        AS `nation_code_ws`,
  `ws`.`worker_id`          AS `worker_id`,
  `ws`.`work_num`           AS `work_num`,
  `ws`.`goods_type`         AS `goods_type`,
  `ws`.`work_sheet_address` AS `work_sheet_address`,
  `ws`.`appointment_date`   AS `appointment_date`,
  `ws`.`work_sheet_type`    AS `work_sheet_type`,
  `ws`.`work_sheet_status`  AS `work_sheet_status`,
  `d`.`id`                  AS `device_id`,
  `w`.`nation_code`         AS `nation_code`,
  `w`.`worker_name`         AS `worker_name`,
  `w`.`worker_phone`        AS `worker_phone`,
  `w`.`evaluation`          AS `evaluation`,
  `u`.`id`                  AS `uid`,
  `u`.`realname`            AS `realname`,
  `u`.`username`            AS `username`  
FROM `tab_work_sheet` `ws`
    left JOIN `tab_device` `d` on ws.device_id = d.id
    LEFT JOIN `tab_user` `u` on ws.uid = u.id
   LEFT JOIN `tab_worker` `w` on ws.worker_id = w.id
where ws.del_flag !=1