YII框架：
Yii::$app->db->createCommand($sql)->query()//返回DataReader对象,并返回查询结果集
Yii::$app->db->createCommand($sql)->queryOne()//返回结果集的第一行数据
Yii::$app->db->createCommand($sql)->queryAll();//查询所有行数据
Yii::$app->db->createCommand($sql)->queryRow();//查询第一行数据
Yii::$app->db->createCommand($sql)->query();//返回某列数据，array
Yii::$app->db->createCommand($sql)->queryScalar();//返回某个字段值，string
Yii::$app->db->createCommand($sql)->execute();//创建、更新、删除，的执行
$query->createCommand()->getRawSql();//打印sql
Yii::$app->db->getLastInsertID();//获取最新插入的一条sql的id
Yii::$app->db->createCommand()->batchInsert('dg_brand_carousel', ['brand_id'], [['5'],['8']])->getRawSql(); //批量插入
YII2复制记录：
$orderModel = BjOrder::findOne(10);
$orderModel->id = null;
$orderModel->isNewRecord = true;
$orderModel->trackCode='';
$orderModel->salesNum = $models->salesNumNew;
$orderModel->insert();
YII提交表单：
<input name="_csrf" type="hidden" id="_csrf" value="<?= Yii::$app->request->csrfToken ?>">
控制器跳转页面：
控制器页面提示并跳转：echo "<script> alert('操作成功');parent.location.href='/index/index'; </script>";
控制器页面提示并返回上一页：echo "<script> alert('产品！');history.go(-1);</script>";
记录日志：
if ($res['Code'] != 'OK') {
$noticelog = './noticelog';
$con = "\n" . '阿里云';
if (!file_exists($noticelog)) {
mkdir($noticelog, 0777, true);
file_put_contents('./noticelog/log.txt', $con);
} else {
file_put_contents('./noticelog/log.txt', $con, FILE_APPEND);
}
}
四舍五入保留两位小数：sprintf("%.2f", round($a,2))
去除顽固空格：
str_replace(chr(194) . chr(160), "", $str);
$str = preg_replace("/(\s|\ \;| |\xc2\xa0)/","",$str);
mysql5.7聚合group by报错，修改：
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set SESSION sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ';
sql：
1、GROUP_CONCAT
SELECT type,GROUP_CONCAT(`id` SEPARATOR ',') id FROM `raw_order` where type in ('3','4') GROUP BY type
2、字符串类型日期查询
SELECT id,str_to_date(start_time,'%Y-%m-%d'),str_to_date(end_time,'%Y-%m-%d') FROM `news` WHERE end_time > STR_TO_DATE("2021-02-18","%Y-%m-%d") and start_time < STR_TO_DATE("2021-02-18","%Y-%m-%d")
select id,pay_time from raw_order where date_format(pay_time,'%Y-%m-%d') BETWEEN str_to_date('2020-10-01', '%Y-%m-%d') AND str_to_date('2021-11-08', '%Y-%m-%d')
3、先排序后去重
SELECT `id`, `barcode`, `price` FROM (SELECT * FROM `product` WHERE `id` < 30 ORDER BY `price` DESC) `product` GROUP BY `barcode` ORDER BY `id` DESC；
select id,goods_name,barcode,sale_price from `product` where id in(select SUBSTRING_INDEX(group_concat(id order by `sale_price` asc),',',1) from `product` group by barcode ) and id>305 order by `id` desc
4、find_in_set
select B.shop_id,t.sid from product as B,(select A.id as sid from shop as A where country='HK') t where FIND_IN_SET(t.sid,B.shop_id)
select k.id,k.goods_name,k.shop_id,sr.id as sid from product k left join shop sr on FIND_IN_SET(sr.id,k.shop_id) where sr.id>0 GROUP BY k.id desc
5、where exist
SELECT * FROM `product` where EXISTS(SELECT 1 FROM dg_wxlive_room_goods_query WHERE product.id=dg_wxlive_room_goods_query.platform_goods_id)
6、查询int类型字符串直接转换日期
SELECT id,product_name,FROM_UNIXTIME(`expires_time`) FROM `dg_order_goods` where 1 order by id desc
网友评论