Example Analysis of entry statement in mysql

This article mainly shows you the "sample analysis of entry sentences in mysql", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of entry sentences in mysql".

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

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2).

(business requirements: data queried from one table should be associated with another table and implement fuzzy queries)

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

The usage of Update Set

(business requirements: update a field when it is passed in, and ignore it when it is not, so that 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=: roomonID`

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

The following SQL statement deletes multiple pieces of eligible data

(business requirements: during the delete operation, if the person who created the data is the one who manipulated 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 create username

The usage of Insert

(business requirements: 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 requirements: for each new piece of data, a field is + 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 Magnesia loginNameParticipationParticipationParticipationParticipationParticipationParticipationDate

: status,:createUser,now ()

From cm_ engineers`

(business requirements: add data from other tables 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

The above is all the content of the article "sample Analysis of getting started sentences in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!