mysql数据库视图列子参考_MySQL视图小例子

场景：

某查询接口 查询sql语句已确定，用该sql语句去查 表 t_strategy_stock 中的数据，但是 表t_strategy_stock 的字段名称和 sql 语句中写死的名称不同。

需求：

查询接口不做改动，sql语句不变，从表 t_strategy_stock 中查出数据。

解决方法：

新建视图，把 表t_strategy_stock 中的字段名称映射成 和查询接口 SQL 语句中相同的名称，然后从视图中查询数据。

1.带字段判断和字符串连接 concat 的例子：

select `t`.`formula_id` AS `gscode`,concat(convert(if((`t`.`market` = 0),'SZ','SH') using utf8),`t`.`code`) AS `gpcode`,`t`.`date` AS `ymd`,`t`.`time` AS `hms`,`t`.`price` AS `price`,`t`.`zdf` AS `zdf`

from `t_strategy_stock` `t`

表 t_strategy_stock ：

视图 v_strategy_stock ：

2.带字符串截取substr的例子：

select `t`.`formula_id` AS `formula_id`, `t`.`period_type` AS `period_type`, `t`.`ymd` AS `ymd`, if( (convert(substring(`t`.`gpcode`,1,2)using utf8) = 'SZ'), 0,1) AS `gpMarket`, convert(substring(`t`.`gpcode`,3,6)using utf8) AS `gpcode`, `t`.`dayNum` AS `dayNum`

from `signaldata` `t`

表 signaldata：

视图 v_signaldata：

select case convert(`t`.`gscode` using utf8)

when 'DYYH' then 37

when 'DYEH' then 38

when 'HJY' then 39

when 'PKS' then 40 end

AS `formula_id`, 7 AS `period_type`, `t`.`ymd` AS `ymd`, if( (convert(substring(`t`.`gpcode`,1,2)using utf8) = 'SZ'), 0,1) AS `gpMarket`, convert(substring(`t`.`gpcode`,3,6)using utf8) AS `gpcode`, `t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`, `t`.`f7` AS `f7`, `t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`

from `calcgsdataflash` `t`

表：calcgsdataflash

视图：v_calcgsdataflash

select case formula_id

when '4' then 'DXJJ' end

AS `gscode`,concat(convert(if((`t`.`gpMarket` = 0),'SZ','SH') using utf8),`t`.`gpcode`) AS `gpcode`,`t`.`ymd` AS `ymd`,

( floor(`t`.`hms` / 10000) * 60 + floor(`t`.`hms` % 10000 / 100) ) AS `hms`,

`t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`, ( floor(`t`.`f7` / 10000) * 60 + floor(`t`.`f7` % 10000 / 100) ) AS `f7`, `t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`

from `stockpooldata_flash` `t`

3.联表查询新建视图

场景：需要从表 t_fid_254获取 market、code、date、buy、sell，从表 stockqt 获取 name字段

select `t`.`market` AS `market`,`t`.`code` AS `code`,`t`.`date` AS `date`,substr(`t`.`data`,2,1) AS `buy`,substr(`t`.`data`,(length(`t`.`data`) - 1),1) AS `sell`,

`qt`.`gpName` AS `name`

from (`t_fid_254` `t` left join `stockqt` `qt` on(((`t`.`code` = `qt`.`gpcode`) and (`t`.`market` = `qt`.`gpMarket`))))

表 t_fid_254：

表 stockqt：

新建的视图 v_t_fid_254：

4.创建按指定字符分割字符串函数

场景：需要将data字符串( 格式：[1,-1895154,,1] )按逗号分割

解决方法：

1.创建一个自定义函数 SPLIT_STR 完成分割功能

CREATE FUNCTION SPLIT_STR(

x VARCHAR(255),

delim VARCHAR(12),

pos INT

)

RETURNS VARCHAR(255)

RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),

LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),

delim, '');

2.使用SPLIT_STR函数按逗号分割data

SELECT `t`.`market` as `market`,

`t`.`code` as `code`,

`t`.`date` as `date`,

`t`.`time` as `time`,

SPLIT_STR(`t`.`data`, ',', 2) as `ZJQD`,

SPLIT_STR(`t`.`data`, ',', 3) as `ZLZB`,

SPLIT_STR(`t`.`data`, ',', 4) as `SHZB`,

SPLIT_STR(SPLIT_STR(`t`.`data`, ',', 6), ']', 1) as `ZJQDZB`,

`qt`.`gpName` as `name`

from (`t_fid_8` as `t` left join `stockqt` as `qt` on((`t`.`code` = `qt`.`gpcode`) and (`t`.`market` = `qt`.`gpMarket`)) )