YII框架：
Yii::$app->db->createCommand($sql)->query()//返回DataReader對(duì)象,并返回查詢結(jié)果集
Yii::$app->db->createCommand($sql)->queryOne()//返回結(jié)果集的第一行數(shù)據(jù)
Yii::$app->db->createCommand($sql)->queryAll();//查詢所有行數(shù)據(jù)
Yii::$app->db->createCommand($sql)->queryRow();//查詢第一行數(shù)據(jù)
Yii::$app->db->createCommand($sql)->query();//返回某列數(shù)據(jù)扶踊，array
Yii::$app->db->createCommand($sql)->queryScalar();//返回某個(gè)字段值赵哲，string
Yii::$app->db->createCommand($sql)->execute();//創(chuàng)建猪落、更新胶果、刪除，的執(zhí)行
$query->createCommand()->getRawSql();//打印sql
Yii::$app->db->getLastInsertID();//獲取最新插入的一條sql的id
Yii::$app->db->createCommand()->batchInsert('dg_brand_carousel', ['brand_id'], [['5'],['8']])->getRawSql(); //批量插入
YII2復(fù)制記錄：
$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 ?>">
控制器跳轉(zhuǎn)頁面：
控制器頁面提示并跳轉(zhuǎn)：echo "<script> alert('操作成功');parent.location.href='/index/index'; </script>";
控制器頁面提示并返回上一頁：echo "<script> alert('產(chǎn)品！');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);
}
}
四舍五入保留兩位小數(shù)：sprintf("%.2f", round($a,2))
去除頑固空格：
str_replace(chr(194) . chr(160), "", $str);
$str = preg_replace("/(\s|\?\;| |\xc2\xa0)/","",$str);
mysql5.7聚合group by報(bào)錯(cuò)豁状，修改：
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類型字符串直接轉(zhuǎn)換日期
SELECT id,product_name,FROM_UNIXTIME(`expires_time`) FROM `dg_order_goods` where 1 order by id desc
