In writing SQL, often flexible use of some SQL statement writing skills, can greatly simplify the program logic. Reduce the number of applications interacting with the database, which is good for high availability of the database, but also to make your SQL look great and impress your colleagues.
1. Insert or replace
If we want to INSERT a new record, but if the record already exists, we delete the original record first and then INSERT the new record.
Scenario example: This table stores the latest transaction order information of each customer. It is required to ensure that the data of a single user is not entered repeatedly, and the execution efficiency is the highest, and the interaction with the database is the least, so as to support high availability of the database.
In this case, you can use the “REPLACE INTO” statement so that you don’t have to query and decide whether to delete and then insert.
- The “REPLACE INTO” statement determines uniqueness based on a unique index or primary key.
- The “REPLACE INTO” statement determines uniqueness based on a unique index or primary key.
- The “REPLACE INTO” statement determines uniqueness based on a unique index or primary key.
Note: As shown in the following SQL, you need to create Unique index (transId) in the USERNAME field.
- 20 points prepaid phone REPLACE INTO last_transaction (transId, username, amount, trans_time, remark) VALUES (null, 'chenhaha, 30, '2020-06-11 20:00:20', 'Member recharge '); - 21 points for the REPLACE skin 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 ');Copy the codeIf the record of username=’chenhaha’ does not exist, the REPLACE statement will insert a new record (first recharge); otherwise, the current record of username=’chenhaha’ will be deleted and a new record will be inserted.
Do not give a specific id, otherwise SQL execution will be affected, except for special business requirements.
2. Insert or update
If we want to INSERT a new record, but update the record if it already exists, we can use “INSERT INTO… ON DUPLICATE KEY UPDATE …” Statement:
Scenario example: This table stores the user’s historical recharge amount. If the user recharges for the first time, a new data item is added. If the user overcharges, the historical recharge amount is accumulated. You can use the “INSERT INTO… ON DUPLICATE KEY UPDATE …” Statements.
INSERT INTO… ON DUPLICATE KEY UPDATE …” Statements are unique based on unique indexes or primary keys. As shown in the following SQL, you need to create Unique index (transId) on the username field.
INSERT INTO total_TRANSACTION (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'DUPLICATE KEY UPDATE total_amount=total_amount + 30, Last_transTime ='2020-06-11 20:00:20', last_remark =' new member '; INSERT INTO total_TRANSACTION (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', Last_transTime ='2020-06-11 21:00:00') ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', Last_remark =' buy new skin ';Copy the codeIf the username=’chenhaha’ record does not exist, the INSERT statement will INSERT a new record; otherwise, the current username=’chenhaha’ record will be updated with the field specified by UPDATE.
3. Insert or ignore
If we want to INSERT a new record, but if the record already exists, we can do nothing and simply IGNORE it, we can use INSERT IGNORE INTO… Statement: There are many scenarios, no more examples.
INSERT IGNORE INTO… Username = ‘Unique’ and transId = ‘transId’; username = ‘Unique’;
INSERT IGNORE INTO users_info (ID, username, sex, age,balance, create_time) VALUES (null, 'chenhaha', 'male ', 12, 0, 'the 2020-06-11 20:00:20'); -- secondary add, INSERT IGNORE INTO users_info (id, username, sex, age,balance, create_time) VALUES (null, 'chenhaha', 'male ', 12, 0, 'the 21:00:20 2020-06-11');Copy the codeIf username=’chenhaha’ does not exist, the INSERT statement inserts a new record; otherwise, no operation is performed.
4. If-else statement in SQL
As we all know, if-else is useful everywhere, in SQL statements, “CASE WHEN… THEN … ELSE … The “END” statement can be used in all types of statements.
To a scene: Women’s Day big feedback, 2020 registered new users, all adult female accounts send 10 yuan red envelope, other users send 5 yuan red envelope, automatic recharge.
The following is an example statement:
UPDATE users_info U SET U.balance = CASE WHEN U.S ex =' female 'and U.age > 18 THEN U.balance + 10 ELSE U.Balance + 5 end WHERE u.create_time >= '2020-01-01'Copy the codeSituation 2: There is a table of students’ college entrance examination scores, and the grades need to be listed. The scores above 650 are key universities, the scores between 600 and 650 are one, the scores between 500 and 600 are two, the scores between 400 and 500 are three, and the scores below 400 are junior colleges.
The original test data is as follows:
Query statement:
SELECT *,case when total_score >= 650 THEN '本' WHEN total_score >= 600 and total_score <650 THEN' 本 Total_score >= 500 and total_score <600 THEN '三本' when total_score >= 400 and total_score <500 THEN '三本' else as status_student from student_score;Copy the code
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:
Students_of_class1 create a snapshot for class_id=1 (class1) and store the snapshot as a new table students_of_class1: CREATE TABLE students_of_class1 SELECT * FROM student WHERE class_id=1;Copy the codeThe newly created table structure is exactly the same as that used by SELECT.
6. Write the query result set
If the query result set needs to be written to a table, you can combine INSERT and SELECT to INSERT the result set of the SELECT statement directly into the specified table.
For example, create a table called statistics that records the average grade of each class:
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
Copy the codeWe can then write the average grade for each class with a single statement:
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
Copy the codeMake sure that the columns of the INSERT statement and the SELECT statement match one by one, and you can store the results of the query directly in the statistics table:
SELECT * FROM statistics;
Copy the code+ - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id class_id | business | + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 | 475.5 | | | 2 473.33333333 | | | 3 | 3 | | 488.66666666 + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code7. Force the specified index to use
In the query, the database system will automatically analyze the query statement, and select a most appropriate index. But many times, the query optimizer of a database system does not always 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. Such as:
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
Copy the codeTo specify an index, idx_class_id must exist.
Original link: blog.csdn.net/qq_39390545…
Copyright notice: This article is originally published BY CSDN blogger “_ Haha Chen”. It is subject to CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement.
Recent hot articles recommended:
1.1,000+ Java Interview Questions and Answers (2021)
2. I finally got the IntelliJ IDEA activation code thanks to the open source project. How sweet!
3. Ali Mock is officially open source, killing all Mock tools on the market!
4.Spring Cloud 2020.0.0 is officially released, a new and disruptive version!
5. “Java Development Manual (Songshan version)” the latest release, quick download!
Feel good, don’t forget to click on + forward oh!