微擎学习随记_数据库操作命令

微擎学习随记_数据库操作命令

二表连查并去掉NULL和0

$sql = 'select sum(b.level) as sumCore, count(b.level) as dataCount from  ' . tablename('ccnb_shop_order') . 'a left join '. tablename('ccnb_shop_order_comment')  . " b on a.id = b.orderid where a.uniacid = 1 and a.is_league = 1 and a.storeid = {$order['storeid']} and b.level is not NULL and b.level!=0";

等价的SQL语句：

select sum(b.level) as sumcore,count(b.level) as sumCore from ims_ccnb_shop_order as a left join ims_ccnb_shop_order_comment as b on a.id = b.orderid where a.uniacid = 1 and a.is_league = 1 and a.storeid = 134 and b.level is not NULL and b.level!=0;

 

三表连查

$sql = 'select o.*, a.realname as addressname,m.realname from ' . tablename('ccnb_shop_order') . ' o ' . ' left join ' . tablename('ccnb_shop_member') . ' m on o.openid = m.openid ' . ' left join ' . tablename('ccnb_shop_member_address') . ' a on a.id = o.addressid ' . ' where 1 ' . $condition . ' order by o.id desc ';

等价的sql语句：

select o.*, a.realname as addressname,m.realname from ims_ccnb_shop_order as o  left join ims_ccnb_shop_member as m on o.openid = m.openid  left join ims_ccnb_shop_member_address as a on a.id = o.addressid   where o.deleted = 0  order by o.id desc ;

查询和数据排序

$limit = ' LIMIT ' . (($pindex - 1) * $psize) . ',' . $psize;
​
$sql = 'select * from ' . tablename('mc_credits_record') . " where uid = {$userData[0]['uid']}  and credittype='credit1' "  . "  ORDER BY createtime  DESC  {$limit} ";

等价的sql语句：

select * from ims_mc_credits_record where uid = 214 and credittype='credit1'ORDER BY createtime DESC LIMIT 0,20 

创建关联数据库

Create TABLE IF NOT EXISTS `ims_shop_trends` (    
  `id` int(6) NOT NULL COMMENT '动态id',    
  `content` varchar(60) NOT NULL COMMENT '动态内容',    
  `create_time` datatime NOT NULL COMMENT '创建时间',
  `storeid` INT(10) NOT NULL COMMENT '店铺id',        
    PRIMARY KEY  (`id`),   

等价的sql语句

FOREIGN KEY(storeid) REFERENCES ims_ccnb_shop_store(id) on delete cascade on update cascade)    
ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; 

 

为现有表添加外键

Alter table ims_ccnb_shop_store_trends add constraint FK_ID foreign key(storeid) REFERENCES ims_ccnb_shop_store(id);

数据模糊查询

select a.*,b.storename from `ims_ccnb_shop_store_trends` a left join `ims_ccnb_shop_store` b on a.storeid = b.id Where b.uniacid = 1 and b.status = 1 and b.audit_status = 1 AND (b.storename LIKE '%1%' OR a.describe LIKE '%1%') order by create_time desc

等价sql语句

select a.*,b.storename from ims_ccnb_shop_store_trends as a left join ims_ccnb_shop_store as b on a.storeid = b.id Where b.uniacid = 1 and b.status = 1 and b.audit_status = 1 AND (b.storename LIKE '%1%' OR a.describe LIKE '%1%') order by create_time desc

关联查询删除数据

delete b from ims_ccnb_shop_order as a left join ims_core_paylog as b on a.ordersn = b.tid left join ims_ccnb_shop_member as c on a.openid = c.openid where a.uniacid = 1 and a.is_league = 1 and a.storeid = 184 and b.status = 1 and b.fee<5;