MySQL entry-table data additions and deletions

      This part is the simplest and the most troublesome. Simple is because actually only includes adding and removing the four parts of the plug. In general, it is not troublesome to add data, delete data, modify data or query data. Who wouldn't? Before the Training institute mysql , I knew that a bottleneck in the programmer's growth path was the database. How to write a high-maintenance sql statement, how to maintain high maintenance while maintaining the efficiency of execution, this is a problem. I have recently been doing a tricky project, often left join 5~6 sheet, sweep table 10 Tens of thousands of, the query speed is amazing. 10 tens of thousands of is just test data, and when real projects go online, the volume of data may reach millions. Therefore inefficient MYSQL  statement, the program may crash directly. Brother Lian it Education (www.lampbrother.net )

Therefore, the data on this part of the deletion and modification is the most difficult, is also crucial, must learn. But today we only write the simplest additions and deletions, the following blog will continue in-depth to organize the relevant aspects of knowledge.

Add Data:

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

This is relatively simple. This is a generic statement. You can value inset a single piece of data and insert multiple data. This is in the "Mysql Learning Note ( two ) table structure Additions and deletions" test SQL inside. can refer to, here does not repeat the code.

Precautions:

When inserting fields into char ,varchar ,text , and date types, the field values are enclosed in single quotation marks.

When inserting data into the self-increment auto_increment field, it is recommended that you insert a NULL value, at which point the field inserts the next number into the self-increment field. In fact, I usually do not write directly.

When you insert data into a default value constraint field, the field value can use the default keyword, which indicates that the field is inserted with the defaults.

When inserting a new record, you need to be aware of the foreign key constraint relationships between the tables, in principle inserting the data into the parent table and then inserting the data into the child table.

Delete data:

Delete Data is a dangerous operation, usually in the development testing phase, may be used, but to the real project on-line stage, there will be no delete permissions.

Syntax: delete from table name where condition ;

　　Delete from classes where class_no = 53; ( you can continue to use the "Mysql Learning Note ( two ) on the table structure additions and deletions" in the test SQL);

Emphasize a sentence, delete data and modify data, without conditions are smelly rogue.

To modify the data:

Modifying the data is also a risky operation, and only certain fields of some tables allow changes when the project is online.

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

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

Emphasize a sentence, delete data and modify data, without conditions are smelly rogue.

Query data:

Basically the project has more than 90% about the database operation is the query operation. Therefore, the query statement written well, will directly reflect a programmer's programming ability.

Against those who shake their heads at the sight of a long SQL statement is negative. They learned from some sources that the length of theSQL statement would reduce the efficiency of execution and would be far away from long MySQL statements. A few scattered fragments of MySQL abruptly that could have been one go.

For those of us who are skilled, it is not right to feel that way. Everything should be based on the facts, the speed ofSQL execution is not determined by the feeling of the head. The real decision of the SQL execution speed or MySQL itself, so encountered doubts, put into MySQL run a run. The fast one is slow, and it stands out.

If the execution time of the two is almost equal, of course, do not hesitate to choose one go sql. Good maintenance ah ! Can greatly reduce the amount of code.

Some friends may retort that a long SQLis annoying to watch and how is it good to maintain it ? This is actually related to personal habits, for the long SQL statement, I personally usually write, do not know right, please give guidance.

Copy Code

　　# long SQL;

　　Select field 1, field 2, field 3, field n

　　 from table one as T1, table two as T2, Table III as T3

　　Left join table four as T4 on T1. Field 1 = T4. Field 1

　　Left join table Five as T5 on T1. Field 2 = T5. Field 2

　　where T1. Field 1=1 and T2. Field 2=2 and T3. Fields 3 > 3

　　GROUP by T1. Field 1

　　ORDER BY T1

　　Limit 1, 5;

Copy Code

In short, all the principle is to be logical clear, typesetting beautiful, can be aligned must be aligned.

The code as Bai Juyi's poetry to write, and strive to concise, but to balance the readability, so that three-year-old children and 70-year-old old woman can understand.

Write code as a graphic design work, between equals, and between variables. A patchwork of code snippets and code snippets, uniform.

Do not skimp on spaces and line breaks. Visualize your code as a stream of data, so that your code has a flow of space.

MySQL entry-table data additions and deletions