Tips on operating sql with mysql

This article focuses on the tips for executing sql. This method not only brings convenience in operation, but also ensures the security of data.

1: Query data (guaranteed query performance)

# First I want to explain

SELECT *  和 SELECT t.id , t.name 的性能，结论：后者性能其实总体优于前者，不信可以自己去试验。

It is best to give the table an alias when querying to facilitate finding the table The field to be queried. When executing sql for multi-condition query, use tools such as Navicat to write query sql in the following format:

SELECT cus.uuid , cus.customerId FROM `customer` cus where 1=1
and cus.uuid=''
-- and cus.customerId=''
and cus.customerName=''
or cus.authState=''
-- or cus.createTime='';

This method can write many query conditions. You need to use which one according to your needs. Open that and annotate what is not needed. Multi-condition query is faster.

2: Insert/update/delete data (ensure data security)

When operating the database, data security is very important. It is okay to test the database, but it is even more important if you operate the official database. Be careful. The following is a little trick that can help you avoid problems with database SQL execution. Post the code directly: ##

--（1）：打开事务
START TRANSACTION
--（2）：执行要操作数据库的sql ，insert ，update，delete等
update explain_download ex set ex.url = concat('https://www.baidu.com/handbook/',ex.fileName);
--（3）：查询执行的sql结果是否正确
SELECT * FROM `explain_download` ed;
--（4）：不正确的话，执行回滚
ROLLBACK;
--（5）：正确 提交数据
commit;

Note: Each line above is executed one line at a time.

The above is the detailed content of Tips on operating sql with mysql. For more information, please follow other related articles on the PHP Chinese website!