A detailed introduction to mysql tips for operating sql-Mysql Tutorial-php.cn

A detailed introduction to mysql tips for operating sql

黄舟

Release： 2017-03-04 14:55:33

Original

898 people have browsed it

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 one time
#

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

Copy after login

It is best to give the table an alias when querying to facilitate finding the fields to be queried in the table. 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=&#39;&#39;
-- and cus.customerId=&#39;&#39;
and cus.customerName=&#39;&#39;
or cus.authState=&#39;&#39;
-- or cus.createTime=&#39;&#39;;

Copy after login

This way You can write many query conditions, use the ones you need to open as needed, and comment out the ones you don't need. 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(&#39;https://www.baidu.com/handbook/&#39;,ex.fileName);
--（3）：查询执行的sql结果是否正确
SELECT * FROM `explain_download` ed;
--（4）：不正确的话，执行回滚
ROLLBACK;
--（5）：正确 提交数据
commit;

Copy after login

Note

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

The above is the detailed introduction of mysql tips for operating sql. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!