Mysql study notes (3) Add, delete, modify and check table data.

This part is the simplest and the most troublesome. It’s simple because it actually only includes four parts: additions, deletions, and insertions. Generally speaking, adding data, deleting data, modifying data, and querying data are not troublesome. They are commonly used in our daily life. Who doesn't know this? When I was learning MySQL at a training institution, I knew that a bottleneck in a programmer's growth journey was the database. How to write highly maintainable SQL statements and how to maintain high maintainability while maintaining high execution efficiency is a difficult problem. I'm currently working on a rather tricky project. I often left join 5 to 6 tables and scan tens of thousands of tables. The query speed is surprisingly slow. Tens of thousands is just test data. When the real project comes online, the amount of data may reach millions. Therefore, inefficient mysql statements may cause the program to crash directly.


Therefore, the addition, deletion, modification and insertion of data is the most difficult and crucial, so you must learn it well. But today we only write the simplest additions, deletions and insertions. The following blogs will continue to organize related knowledge in depth.


Add data:


　 insert into table name (field 1, field 2, field 3, field n) values ​​(value 1, value 2, value 3, value 4);


This is relatively simple. This is a general statement. You can insert one piece of data or insert multiple pieces of data. This is included in the test sql of "Mysql Study Notes (2) Addition, Deletion, Modification and Inquiry of Table Structure". You can refer to it, I won’t repeat the code here.


                                                                                                                                                                                                                                                                                           


When inserting data into the auto-increment auto_increment field, it is recommended to insert a NULL value. At this time, the field will insert the next number into the auto-increment field. In fact, I usually just don’t write.


When inserting data into a default value constrained field, the field value can use the default keyword, which means that the default value of the field is inserted.


                                                                                                                                             When inserting new records, you need to pay attention to the foreign key constraint relationships between tables. In principle, insert data into the parent table first, and then insert data into the child table.





Delete data:


Deleting data is a relatively dangerous operation. It may be used during the development and testing phase, but when the actual project is launched, there will be no delete permission.


Syntax: delete from table name where condition;


delete from classes where class_no = 53; (You can continue to use the test sql in "Mysql Study Notes (2) Addition, Deletion, Modification and Check of Table Structure");


I would like to emphasize that deleting data and modifying data without any conditions are both scoundrels.





Modify data:


Modifying data is also a very dangerous operation. When the project goes online, only certain fields of some tables are allowed to be changed.


Syntax: update table name set field name = field value where condition.


Update classes set class_name='roverliang' where class_no=52;


I would like to emphasize that deleting data and modifying data without any conditions are both scoundrels.





Query data:


Basically, more than 90% of the database operations in the project are query operations. Therefore, whether the query statement is well written will directly reflect a programmer's programming ability.


I am against those people who shake their heads in denial when they see a long SQL statement. They learned from some information that writing long SQL statements will reduce execution efficiency, and they simply avoid long MySQL statements. MySQL, which could have been completed in one go, was abruptly divided into several scattered fragments.


For those of us who are engaged in technology, it is wrong to rely on feelings. Everything must be based on facts. The speed of SQL execution is not determined by feelings. What really determines the execution speed of sql is mysql itself, so if you encounter any doubts, just put it into mysql and run it. Which one is faster and which one is slower is a matter of distinction.


If the execution time of the two is almost the same, of course you should not hesitate to choose SQL in one go. It’s so easy to maintain! It can greatly reduce the amount of code.


Some friends may retort that such a long sql is annoying to look at, so how can it be easy to maintain? This is actually related to personal habits. For long SQL statements, I usually write this way. I don’t know if it is correct. Please give me some advice.





Copy code
#longsql;

select field 1, field 2, field 3, field n


from Table 1 as t1, Table 2 as t2, Table 3 as t3
                                                                                                                                             on t1.Field 1 = t4.Field 1
left join Table 5 as t5 on t1.Field2 = t5.Field2


where t1.Field1=1 and t2.Field2=2 and t3.Field3 > 3


group by t1.Field1


order by t1 1,5


Write the code as Bai Juyi's poetry, strive to be concise, but take into account the readability, so that it can be understood by a three-year-old child and a seventy-year-old woman.


Write the code as a graphic design work, with neat layout between equal signs and variables. The code snippets are well-proportioned and uniform.


Don’t be stingy with spaces and line breaks. Think of code as a flow of data and give it room to flow.


The above is the addition, deletion, modification and query of table data in Mysql study notes (3). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!