Detailed introduction to mysql statements

This article mainly shares with you the detailed introduction to mysql statements, and specifically explains the mysql statements in detail step by step. I hope it can help you.

Select, Left Join usage, and implementation of fuzzy query:

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`;

Usage of Update Set

(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`;

Usage of Delete,In: (IN operator allows us to specify multiple values ​​​​in the WHERE clause)

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;

Usage of Insert

(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!