MySql应用语句

MySql应用语句

--新增字段
alter table dx_order_huifang add addusername varchar(200) not Null default null COMMENT '添加人姓名';
--修改字段类型
alter table table modify column 字段 varchar(1000);
--修改时间为时间戳
update  dx_order set o2o_order_pickup_time  = UNIX_TIMESTAMP('2021-03-11 15:38:12')  where order_id =1105 
--连表修改
update  dx_goods as a  inner join(
   select dg.goods_id,dg.goods_salenum  from   dx_goods as dg inner join    dx_goodsclass as gc     on dg.gc_id  = gc.gc_id  or dg.gc_id_1  = gc.gc_id    where gc.gc_name = '房屋维修'
 ) as b on  a.goods_id=b.goods_id   set  vm_order_count =80 ,vm_order_count_sum =80+b.goods_salenum;
--连表统计
select  a.cnt/b.cnt,b.store_name
from
(
select   count(*) as cnt,do.store_id,s.store_name from ds_order as do inner join ds_store as s on s.store_id = do.store_id  where s.store_name  like '%搬家%' and do.order_state in (40,50) group by do.store_id desc 
) as a inner join
(
select   count(*) as cnt,do.store_id,s.store_name from ds_order  as do inner join ds_store as s on s.store_id = do.store_id where s.store_name like '%搬家%' group by do.store_id desc
) as b  on a.store_id =b.store_id;
--正则查询
SELECT * FROM article WHERE 
CONCAT_WS(" ", title, subtitle, tag) REGEXP "车|摩托|红色|美国|2006"