LEFT JOIN keyword will be returned from the left table (table_name1) All rows, even if there are no matching rows in the right table (table_name2).
(Business requirement: The data queried from one table should be associated with another table and fuzzy query implemented)
const sql = `select cei.intentionID,cei. engineerName,cei.nickname,cei.headImgUrl,
ifnull(cmw.headImgUrl,'') inviteImgUrl,ifnull(cmw.nickname,'') inviteNickName
from cm_engineer_intentions cei
left join cm_member_wechats cmw on cmw. referralCode=cei.inviteCode
where (:isGrant=-1 or isGrant=:isGrant)
and (:intentionStatus=-1 or intentionStatus=:intentionStatus)
and (:searchKey='' or concat( engineerMobile,engineerName) like concat('%',:searchKey,'%'))
order by intentionStatus asc,createTime desc`;
(Business requirements: Update when a field is passed in, ignore it when it is not passed in, so you can choose whether to insert a field according to your needs)
const contactRemarkSql = isUpdateRemark ? ',contactRemark=:contactRemark' : '';
const sql = `update cm_engineer_intentions
set intentionStatus=:intentionStatus ${contactRemarkSql}
where intentionID=: intentionID`;
Below The SQL statement can delete multiple pieces of data that meet the conditions
(Business requirement: When performing a delete operation, if the person who created the data is the person who operates the data, delete it, otherwise update the status)
const deleteSql = delete from cm_fast_order_goods where orderGoodsID in (${deleteData.deleteIDs}) and createUser=:userName;
const updateSql = update cm_fast_order_goods set status=0 where orderGoodsID in (${deleteData.deleteIDs}) and createUser!=:userName;
(Business requirement: add a new piece of data)
const sqlOrderEngineer = ` insert into cm_order_engineers
(orderEngineerID,orderGoodsID,orderID,engineerID,engineerName,createUser,createTime)
values
(:orderEngineerID,:orderGoodsID,:orderID,:engineerID,:engineerName,:createUser,:createTime)
(Business requirement: Every time a new piece of data is added, a certain field will be increased by 1 based on the maximum value of the original data)
const sql = `insert into cm_engineers
(engineerCode,loginName,password,engineerName,sex,birthDate,status,createUser,createTime)
select
ifnull(max(engineerCode),0)+1,:loginName, :password,:engineerName,:sex,:birthDate,
:status,:createUser,now()
from cm_engineers`;
(Business requirements: from other tables Add data to the current table)
insert into cm_fast_order_goods (orderGoodsID,orderID,goodsID,goodsCode,goodsPicUrl,createUser,createTime)
select :orderGoodsID,:orderID,:goodsID ,goodsCode,iconUrl,:createUser,now()
from cm_service_goods
where goodsID=:goodsID and status=1
Related recommendations:
How to implement MySQL statement locking
Detailed tutorial on using mysql statement to create a data table
Performance analysis and optimization of mysql statements
The above is the detailed content of Detailed introduction to mysql statements. For more information, please follow other related articles on the PHP Chinese website!