常用的MySQL语句

1.从两张表查询四个字段，插入第三张表中

insert into s_scenic(`showName`,`areaCode`,`level`,`address`)
SELECT
`景点名称` AS showName, `areaCode`,
'0' AS level,`地址` AS address
FROM
sheet1
union all
SELECT
`景区名称`,
case `所在地市` when '柯城区' then 330802
when '衢江区' then 330803
when '常山县' then 330822 end ,
case `等级` when '4A' then '4'
when '3A' then '3'
when '2A' then '2'
end,
`景区地址`
FROM
`tmp_scenic`

2.替换掉address字段中的指定值

UPDATE s_hotel set address = REPLACE(address,'浙江省','')

3.替换掉某字段默认值是null

UPDATE s_hotel set `level`='0' WHERE `level` is null

4.用update语句一次更新多个字段

update table set field_1='A',field_2='b',field_3='C' where field_4='F'

5.模糊查询

UPDATE s_hotel set areaCode='330803' WHERE address LIKE '%衢江区%'

6.字符串连接

SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston';