新建和删除视图

一:视图是在使用时动态生成的
二:MySQL视图的定义有一些限制，例如，在FROM关键字后面不能包含子查询，这和其他数据库是不同的
，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以将子查询的内容先定义
成一个视图，然后对该视图再创建视图就可以实现类似的功能了。

1:删除视图

drop view if exists `v_t_birthday_gift_detail`;

2.创建新视图

CREATE or REPLACE VIEW `v_t_birthday_gift_detail` AS SELECT
    jcd.id,
    jcd.userName,
    jcd.contractTime as contractTime,
    jcd.userIDCard, 
    jcd.sex,
    CASE
    WHEN jcd.sex IS 0 THEN
    男
    ELSE
    女
    END AS sexName,
     CASE
WHEN CAST(CONCAT(month(NOW()), DATE_FORMAT(jcd.userBirthDate, '%m')) AS DATE) < CAST(DATE_FORMAT(jcd.interestPeriod, '%y-%m-%d') AS DATE) THEN
    YEAR (NOW()) + 1
ELSE
    YEAR (NOW())
END AS changeYear
    f_formatting_idNo (jcd.userIDCard) AS userIDCardHide,
    date_format(jcd.userBirthDate, '%Y-%m-%d') AS userBirthDate,
    jcd.contractNum,
    jcd.interestPeriod,
    jcd.investExpireDate,
    jcd.branchId,
    jcd.branchName,
    jcd.departmentId,
    jcd.branchDepName,
    jcd.staffId,
    jcd.staffName,
    jcu.userId,
    m.giftName,
    m.giftAmount,
    YEAR (NOW()) AS currentYear,
    CASE
WHEN i.userId IS NULL THEN
    0
ELSE
    1
END AS isChange,
WHEN i.userId IS NULL THEN
    否
ELSE
    是
END AS isChangeName,
 m.giftType,
 DATE_FORMAT(i.`createTime`, '%Y-%m-%d %H:%i:%s') createTime,
 a.`realName` auserName,
 CASE
WHEN CAST(CONCAT(YEAR(NOW()), DATE_FORMAT(jcd.userBirthDate, '-%m-%d')) AS DATE) < CAST(DATE_FORMAT(jcd.interestPeriod, '%y-%m-%d') AS DATE) THEN
    YEAR (NOW()) + 1
ELSE
    YEAR (NOW())
END AS changeYear
FROM
    t_jt_contract_user jcu
LEFT JOIN t_jt_contract_data jcd ON jcd.userIDCard = jcu.userIDCard
LEFT JOIN v_t_birthday_gift_temp i ON i.userId = jcu.userId
LEFT JOIN t_jt_contract_exchange_map m ON i.id = m.cgiId
LEFT JOIN t_jt_staff s ON i.`staffId` = s.`id`
LEFT JOIN `t_admin` a ON s.`adminId` = a.`id`
WHERE
    jcd.contractState = 1
AND jcd.contractFlag IN (2, 3)
AND jcd.redemption IN (2, 6)