MySQL：《学习笔记与实战》之查询实例（2）房源视图

 1.创建视图中的函数说明

    ①-----------------------replace 替换字符串

        replace(object,search,replace)
         把object对象中出现的的search全部替换成replace
        select replace('www.jb51.net','w','Ww')--->WwWwWw.jb51.net 

    ②-------------------------substring  截取字符串
        substring（str, pos, length） 
        参数说明：substring（被截取字符串，从第几位开始截取，截取长度） 

        select substring（content,5） as abstract from my_content_t 
        select substring（content,5,200） as abstract from my_content_t 
        （注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度）
      ③-------------------------locate 返回字符串出现的位置

        locate (substr,str,pos) 
        如果不加pos参数，则返回子串 substr 在字符串 str 中第一次出现的位置。

         加了pos参数后，则返回在 pos 位置后第一次出现的位置。

        如果 substr 不在 str 中返回 0 ；如果在str 中，则返回子串 substr 在字符串 str 中第一次出现的位置。
        实例代码如下

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

④-------------------------用到的时间函数

DATE_ADD(date,INTERVAL expr type) 
DATE_SUB(date,INTERVAL expr type)

  INTERVAL为关键词，expr是具体的时间间隔，type是时间单位

   在date 的基础上加上或减去表达式expr的时间，type可以是复合的时间单位。

mysql> select date_add('2013-01-18', interval '1 2' YEAR_MONTH);
+-----------------------------------------------------+
| date_add('2013-01-18', interval '1 2' YEAR_MONTH) |
+-----------------------------------------------------+
| 2014-03-18                                          |
+-----------------------------------------------------+
mysql> select date_add('2013-01-18', interval '1-2' YEAR_MONTH);
+----------------------------------------------------+
| date_add('2013-01-18', interval '1-2' YEAR_MONTH) |
+----------------------------------------------------+
| 2014-03-18                                         |
+----------------------------------------------------+
mysql> select date_add('2013-01-18', interval '1,2' YEAR_MONTH);
+---------------------------------------------------+
| date_add('2013-01-18', interval '1,2' YEAR_MONTH) |
+---------------------------------------------------+
| 2014-03-18                                        |
+---------------------------------------------------+
mysql> select date_add('2013-01-18', interval 1 YEAR_MONTH);
+-----------------------------------------------+
| date_add('2013-01-18', interval 1 YEAR_MONTH) |
+-----------------------------------------------+
| 2013-02-18                                    |
+-----------------------------------------------+
mysql> select date_add('2013-01-18', interval -1 YEAR_MONTH);
+------------------------------------------------+
| date_sub('2013-01-18', interval 1 YEAR_MONTH) |
+------------------------------------------------+
| 2012-12-18                                     |
+------------------------------------------------+

 

MICROSECOND	间隔单位：毫秒
SECOND	间隔单位：秒
MINUTE	间隔单位：分钟
HOUR	间隔单位：小时
DAY	间隔单位：天
WEEK	间隔单位：星期
MONTH	间隔单位：月
QUARTER	间隔单位：季度
YEAR	间隔单位：年
SECOND_MICROSECOND	复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒
MINUTE_MICROSECOND	复合型，间隔单位：分、毫秒
MINUTE_SECOND	复合型，间隔单位：分、秒
HOUR_MICROSECOND	复合型，间隔单位：小时、毫秒
HOUR_SECOND	复合型，间隔单位：小时、秒
HOUR_MINUTE	复合型，间隔单位：小时分
DAY_MICROSECOND	复合型，间隔单位：天、毫秒
DAY_SECOND	复合型，间隔单位：天、秒
DAY_MINUTE	复合型，间隔单位：天、分
DAY_HOUR	复合型，间隔单位：天、小时
YEAR_MONTH	复合型，间隔单位：年、月

对应复合型的type，需要使用引号对两个参数进行引用起来，中间用任何非数字字符作为间隔即可，并且不能使用负数。

但是时间间隔只指定了一个值， 那么也能正常工作，但是对应XXX_YYY使用的单位为YYY，也就相当于单一单位的type，同时可以使用负数。

 

2.创建对应的视图

SELECT
REPLACE(	SUBSTRING(
		address,
		1,
		LOCATE(' ', address)
	) ,'区','')AS region,
SUBSTRING(address,
LOCATE(" ",address),
LOCATE(" ",address,LOCATE(" ",address)+1)-LOCATE(" ",address)) 
AS location,
address,

#02-from_web 分类
	CASE from_web
WHEN 1 THEN
	'【58】'
ELSE
	'安居客'
END from_web,

 CASE agent_company
WHEN '' THEN
	'散户'
ELSE
	agent_company
END agent_company,

date,

#03-总价区间房源
 CASE
WHEN total_price >= 0
AND total_price <= 50 THEN
	'总价1：<=50'
WHEN total_price > 50
AND total_price <= 100 THEN
	'总价2：50-100'
WHEN total_price > 100
AND total_price <= 200 THEN
	'总价3：100-200'
WHEN total_price > 200
AND total_price <= 300 THEN
	'总价4：200-300'
WHEN total_price > 300
AND total_price <= 400 THEN
	'总价5：300-400'
WHEN total_price > 400
AND total_price <= 500 THEN
	'总价6：400-500'
WHEN total_price > 500
AND total_price <= 1000 THEN
	'总价7：500-1000'
WHEN total_price > 1000 THEN
	'总价8：>1000'
END AS total,

#04-单价区间房源
 CASE
WHEN price <= '15000元/㎡' THEN
	'单价1：<1.5万'
WHEN price > '15000元/㎡'
AND price <= '20000元/㎡' THEN
	'单价2：1.5-2万'
WHEN price > '20000元/㎡'
AND price <= '25000元/㎡' THEN
	'单价3：2-2.5万'
WHEN price > '25000元/㎡'
AND price <= '30000元/㎡' THEN
	'单价4：2.5-3万'
WHEN price > '30000元/㎡'
AND price <= '35000元/㎡' THEN
	'单价5：3-3.5万'
WHEN price > '35000元/㎡'
AND price <= '40000元/㎡' THEN
	'单价6：3.5-4万'
WHEN price > '40000元/㎡'
AND price <= '50000元/㎡' THEN
	'单价7：4-5万'
WHEN price > '50000元/㎡' THEN
	'单价8：>5万'
END AS price,


#05-面积区间房源
 CASE
WHEN REPLACE (acreage, '㎡', '') <= 50 THEN
	'面积1：<50'
WHEN REPLACE (acreage, '㎡', '') > 50
AND REPLACE (acreage, '㎡', '') <= 100 THEN
	'面积2：50-100'
WHEN REPLACE (acreage, '㎡', '') > 100
AND REPLACE (acreage, '㎡', '') <= 150 THEN
	'面积3：100-150'
WHEN REPLACE (acreage, '㎡', '') > 150
AND REPLACE (acreage, '㎡', '') <= 200 THEN
	'面积4：150-200'
WHEN REPLACE (acreage, '㎡', '') > 200 THEN
	'面积5：>200'
END AS area
FROM
	yuan_fang
WHERE
	date BETWEEN DATE_SUB(curdate(),INTERVAL 7 DAY) and curdate()