This is the first part of a series of notes on MySQL. I hope this series of notes will be based on the author’s own experience in learning MySQL technology and avoid summarization by content, that is, different from the structure of books that allocate chapters by content, there will be a smooth reading curve. Technical points with rich content will be gradually deepened in multiple study notes according to the topic.

This section is titled a little more than CRUD, with just a little more extension than the most basic W3C SQL tutorial, and meets the need to go from just reading introductory material to being able to get started and do a simple job.

The third part continues to focus on the basic SQL statements UPDATE and DELETE. These two statements are relatively simple and may be commonly used in handling and handling online data problems. In the development process, only the most basic operations will be used.

The DELETE statement

The most basic usage will not be repeated. When using DELETE to DELETE data from multiple tables instead of just a simple WHERE condition, it is written like this:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Copy the code

Note that DELETE is followed by two table names. If alias is used, the delete statement must use alias instead of the table name:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;   - right
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;   Error -
Copy the code

It is important to note that DELETE and UPDATE should be conditional. Otherwise, it can easily become a disaster. The database needs to ensure that the Multiversion Concurrency Control (MVCC) is concurrency control. I myself online database to do these operations including SELECT when the hand has already formed A subconscious, will first write where conditions, CtrL-A then start to write update, SELECT.

TRUNCATE TABLE

While the DELETE operation is slow, what do you do if you need to quickly clear a table for writing unit tests or your own development library? The answer is use TRUNCATE.

Note that when TRUNCATE is used, if the table has self-increment columns, such as self-increment primary keys, the table will be cleared to the initial value, that is, after TRUNCATE, the data of the whole table will be lost, and the self-increment sequence will return to the original value.

The DELETE FROM t; Thus, a DELETE statement that deletes the entire table will not empty the auto-increment. If there is a foreign key association, then use set foreign_key_checks to remove foreign key checks:

set foreign_key_checks = 0;
truncate Account;
set foreign_key_checks = 1;
Copy the code

This feature also causes an interesting exception encountered by the author during development: when the primary key of a certain table T is cleared in this way, the associated table A is not cleared, and then new data is inserted into T, and the increment ID starts from 1 again. The result is that some of the old data in A is associated with the new data in T. Ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha, ha.

The UPDATE statement

The most basic usage will not be repeated. Look at the following example:

UPDATE t1 SET yourname = realname + ".avi";
Copy the code

The value set is an expression, and can be used with other tables, or update both tables at the same time, using JOIN:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
Copy the code

Select a table from inner JOIN. Select a table from inner join. The update can also update its own column:

UPDATE t1 SET counting = counting + 1 where id = 2;
Copy the code

This column increment technique is a common practice in real development. It is very convenient to use in counting statistics and avoid having to face the value overwrite problem caused by concurrent read-write conflicts. Then you can add order by and limit to update only the first few conditions:

UPDATE t SET counting = counting + 1 ORDER BY id DESC LIMIT 10;
Copy the code

Also in the increment itself example above, there are special cases that are interesting, namely when the primary key to be updated increases its ID. So if you do not add order by directly update:

UPDATE t SET id = id + 1;   -- This is wrong
Copy the code

An ID conflict error is reported and cannot be executed. Add order by and you can execute it smoothly:

UPDATE t SET id = id + 1 ORDER BY id DESC;
Copy the code

Note that the DELETE and UPDATE concurrency should be constrained. This can easily lead to a disaster. The database needs to ensure that the Multiversion concurrency Control (MVCC) is concurrency control. Your other requests get stuck when the table is locked. When I do these operations on the online database, including SELECT, my hand has formed A subconscious, I will write where condition first, Ctrl-A jump to the command line before starting to write update, SELECT.