Some useful SQL statements in MySQL

Practical SQL

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 the 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 repeatedly entered, and the execution efficiency is the highest, the interaction with the database is the least, and the database is highly available.
At this point, you can use the "REPLACE INTO" statement, so that you do not have to query first, and then decide whether to delete and insert first.

• The "REPLACE INTO" statement is based on a unique index or primary key to determine whether it is unique or not.
• The "REPLACE INTO" statement is based on a unique index or primary key to determine whether it is unique or not.
• The "REPLACE INTO" statement is based on a unique index or primary key to determine whether it is unique or not.

Note: as shown in the following SQL, you need to create a Unique index in the username field, and the transId setting can be increased automatically.

-- 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 the skin of the supreme fist of blind monk');

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

Do not give specific value to id, otherwise SQL execution will be affected, except for special business requirements.

2. Insert or update

If we want to INSERT a new record (INSERT), but if the record already exists, update the record. At this time, we can use the "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 piece of data will be added. If the user recharges for the first time, the historical recharge amount will be accumulated. It is necessary to ensure that the data of a single user is not repeatedly entered.
At this time, you can use the "insert into... On duplicate key update..." statement.

Note: as above, the "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 create a Unique index on the username field, and the transId setting can be increased automatically.

-- User Chen haha recharged 30 yuan to buy members 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 ha ha recharged 100 yuan to buy the highest fist skin of the blind INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', 'Buy the skin of the supreme fist of blind monk') ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='Buy the skin of the supreme fist of blind monk';

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

3. Insert or ignore

If we want to INSERT a new record (INSERT), but if the record already exists, we can ignore nothing directly. At this time, we can use the INSERT IGNORE INTO... Statement: there are many scenarios, and we will not repeat them with examples.

Note: as above, the "INSERT IGNORE INTO..." statement is based on the Unique index or primary key to determine whether it is Unique or not. You need to create a Unique index on the username field, and the transId setting can be increased automatically.

-- First added by user INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) VALUES (null, 'chenhaha', 'male', 12, 0, '2020-06-11 20:00:20'); -- Add twice, 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 the record of username ='chenaha 'does not exist, the INSERT statement will INSERT the 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.

Give a scene: Women's day big feedback. For new users registered in 2020, all adult female accounts will be given 10 yuan red packets, and other users will be given 5 yuan red packets, which will be automatically recharged.
The sample statements are as follows:

-- Send red packet 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 score table of college entrance examination for students, which needs to list the grades. Key universities have more than 650 scores, 600-650 for one, 500-600 for two, 400-500 for 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 copies' else 'junior college' end as status_student from student_score;

5. Specify data snapshot or backup

If you want to take a snapshot of 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(The records of the first shift) are snapshot and stored as new tables 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.

6. Write query result set

If the query result set needs to be written to the 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 statistics table to record the average scores 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 scores of each class in one sentence:

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

To ensure that the columns of the INSERT statement and the SELECT statement can correspond one by one, 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. Force the specified index

When querying, the database system will automatically analyze the query statement and select the most appropriate index. But in many cases, the query optimizer of database system does not always use the optimal index. If we know how to select an index, we can use FORCE INDEX to force queries to use the specified index. For example:

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

The premise of specifying an index is the index idx_class_id must exist.

experience:

MySQL has a long way to go. Never look down and cheer up. I hope this article can help you.

Add new comment

0 comments