1. Insert or replace

If we want to INSERT a new record (INSERT), but if the record already exists, delete the original record first and then INSERT a new record.

Scenario example: the latest transaction order information of each customer stored in this table is required to ensure that the data of a single user is not entered repeatedly, with the highest execution efficiency and the least interaction with the database, so as to support the high availability of the database.

At this time, you can use the "REPLACE INTO" statement, so you don't have to query first and then decide whether to delete and then insert.

• The "REPLACE INTO" statement determines the uniqueness (whether it exists) based on the unique index or primary key.

• The "REPLACE INTO" statement determines the uniqueness (existence) based on the unique index or primary key.

• The "REPLACE INTO" statement determines the uniqueness (existence) based on the unique index or primary key.

Note: as shown in the following SQL, you need to establish a Unique index on the username field, and set the autoincrement of transId.

-- 20 Point recharge
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'Member recharge');
 
-- 21 Buy skin
REPLACE INTO last_transaction (transId,username,amount,trans_time,remark)
   VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', 'Buy blind monk's supreme fist skin');

If the record of username ='chenaha 'does not exist, the REPLACE statement will insert a new record (recharge for the first time). Otherwise, the current record of username ='chenaha' will be deleted and then a new record will be inserted.

Do not give a specific value for id, otherwise it will affect SQL execution, except for special business needs.

2. Insert or update

If we want to INSERT a new record (INSERT), but if the record already exists, we can update the record. At this time, we can use "INSERT into... On duplicate key update..." sentence:

Scenario example: the user's historical recharge amount is stored in this table. If the user recharges for the first time, a new piece of data will be added. If the user recharges too much, the historical recharge amount will be accumulated. It is necessary to ensure that the data of a single user is not entered repeatedly. You can use "insert into... On duplicate key update..." sentence.

Note: as above, "insert into... On duplicate key update..." Statement is based on a Unique index or primary key to determine whether it is Unique or not. As shown in the following SQL, you need to establish a Unique index on the username field, and set the autoincrement of transId.

-- User Chen haha recharged 30 yuan to buy a member
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'Full membership')
   ON DUPLICATE KEY UPDATE  total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='Full membership';
 
-- User Chen haha recharged 100 yuan to buy the skin of the blind man's supreme fist
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark)
   VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', 'Buy blind monk's supreme fist skin')
   ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='Buy blind monk's supreme fist skin';

If the record of username ='chenaha 'does not exist, the INSERT statement will INSERT a new record. Otherwise, the current record of username ='chenaha' will be updated, and the updated field is specified by UPDATE.

3. Insert or ignore

If we want to INSERT a new record (INSERT), but if the record already exists, we don't do anything and ignore it directly. At this time, you can use INSERT IGNORE INTO Sentence: there are many scenarios, so I won't give examples.

Note: same as above, "INSERT IGNORE INTO..." The statement is based on the Unique index or primary key to determine whether it is Unique or not. You need to establish a Unique index on the username field, and set the autoincrement of transId.

-- User added for the first time
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
   VALUES (null, 'chenhaha', 'male', 12, 0, '2020-06-11 20:00:20');
 
-- Secondary addition, ignore directly
INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time)
   VALUES (null, 'chenhaha', 'male', 12, 0, '2020-06-11 21:00:20');

If record with the username ='chenaha 'does not exist, INSERT statement will INSERT new record; otherwise, no operation will be performed.

4. If else judgment statement in SQL

As we all know, if else judgment is very useful everywhere. In SQL statements, "case when... Then... Else... End" statements can be used to add, delete, modify and query various statements.

Here's a scenario: Women's day big feedback. For new users registered in 2020, all adult female accounts will send 10 yuan red envelopes, and other users will send 5 yuan red envelopes for automatic recharge. Share: Spring Boot learning notes.

The example statement is as follows:

-- Send red envelope statement
UPDATE users_info u
    SET u.balance = CASE WHEN u.sex ='female' and u.age > 18 THEN u.balance + 10 
                         ELSE u.balance + 5 end 
                         WHERE u.create_time >= '2020-01-01'

Scenario 2: there is a student's college entrance examination score table, which needs to list the grades. Key universities with a score of more than 650, 600-650 are one, 500-600 are two, 400-500 are three, and junior colleges below 400;

The original test data are as follows:

Query statement:

SELECT *,case when total_score >= 650  THEN 'Key universities' 
              when total_score >= 600 and total_score <650 THEN 'a copy'
              when total_score >= 500 and total_score <600 THEN 'Two copies'
              when total_score >= 400 and total_score <500 THEN 'Three books'        
              else 'junior college' end as status_student
              from student_score;

5. Specify data snapshot or backup

If you want to snapshot a table, that is, copy the data of the current table to a new table, you can combine CREATE TABLE and SELECT:

-- yes class_id=1(Take a snapshot of the records of the first shift) and store them as a new table students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM student WHERE class_id=1;

The newly created table structure is exactly the same as that used by SELECT.

Recommended reading: 36 military regulations for MySQL database development!

6. Write query result set

If the query result set needs to be written to the table, you can directly INSERT the result set of the SELECT statement into the specified table by combining INSERT and SELECT.

For example, create a statistics table to record the average score of each class:

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

Then, we can write the average grade of each class in one sentence:

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

Ensure that the columns of the INSERT statement and the columns of the SELECT statement correspond to each other one by one, and you can directly save the query results in the statistics table:

SELECT * FROM statistics;

+----+----------+--------------+
| id | class_id | average |
+----+----------+--------------+
| 1 |        1 | 475.5 |
| 2 |        2 | 473.33333333 |
| 3 |        3 | 488.66666666 |
+----+----------+--------------+
3 rows in set (0.00 sec)

7. Enforce the use of the specified index

When querying, the database system will automatically analyze the query statements and select the most appropriate index. However, most of the time, the query optimizer of database system may not always be able to use the optimal index. If we know how to select an index, we can use FORCE INDEX to force the query to use the specified index. For example:

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

The index is specified only if the index idx_class_id must exist. In addition, I will pay attention to the official account Java technology stack, and reply in the background: interview can get my MySQL series interview questions and answers, which are very complete.