Shulou(Shulou.com)06/01 Report--

When writing SQL, we often use some skills of writing SQL statements flexibly, which can greatly simplify the program logic. Reduce the number of interactions between the program and the database, which is conducive to the high availability of the database. At the same time, it can also show that your SQL is very good, which will brighten the eyes of your colleagues.

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 before inserting the new record.

Scenario example: each customer's last transaction order information stored in this table is required to ensure that individual user data is not repeatedly entered, the execution efficiency is the highest, the interaction with the database is the least, and the high availability of the database is supported.

At this point, you can use the "REPLACE INTO" statement so that you don't have to query first and then 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 SQL below, a unique index (Unique) needs to be established on the username field, and the transId setting can be self-incremented.

-- 20:00 recharge REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chen', 30,' 2020-06-11 20 null, 'member recharge');-- 21:00 purchase of REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chen', 100,' 2020-06-11 21 VALUES VALUES, 'purchase of blind monk supreme fist skin')

If the record of username='chen' does not exist, the REPLACE statement will insert the new record (the first recharge), otherwise, the record of the current username='chen' will be deleted and then the new record will be inserted.

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

two。 Insert or update

If we want to insert a new record (INSERT), 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 a new data is added for the first recharge, and if the user has been recharged, the historical recharge amount will be accumulated. You need to ensure that individual user data are not re-entered.

At this point, you can use "INSERT INTO. ON DUPLICATE KEY UPDATE." Statement.

Note: ditto, "INSERT INTO... ON DUPLICATE KEY UPDATE..." The statement is based on a unique index or primary key to determine whether it exists or not. As shown in SQL below, a unique index (Unique) needs to be established on the username field, and the transId setting can be incremented by itself.

-- user Chen recharged 30 yuan to buy member INSERT INTO total_transaction (chen', 30, '2020-06-11 2000 null,' full member') ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 2000 chen', 20, last_remark = 'full member' -- user Chen recharged 100yuan for INSERT INTO total_transaction skin of blind man, last_transTime='2020-06-11, ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11, 211lace 0000' Last_remark = 'Buy Blind Monk Supreme Boxing skin'

If the record of username='chen' does not exist, the INSERT statement will insert the new record, otherwise, the record of the current username='chen' 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, do nothing and just ignore it, we can use INSERT IGNORE INTO. Sentence: there are many situations, so I won't repeat them with examples.

Note: ditto, "INSERT IGNORE INTO..." Statement is based on a unique index or primary key to determine whether it exists or not, you need to establish a unique index (Unique) on the username field, and the transId setting can be self-incremented.

-- users add INSERT IGNORE INTO users_info (id, username, sex, age, balance, create_time) VALUES for the first time (null, 'chen',' male', 12, 0, '2020-06-11 20-20-12-00-20)) -- second addition, directly ignoring INSERT IGNORE INTO users_info (id, username, sex, age, balance, create_time) VALUES (null, 'chen',' male', 12, 0, '2020-06-11 21-21-00-20')

If the record for username='chen' does not exist, the INSERT statement inserts the new record, otherwise no action is performed.

If-else judgment statement in 4.SQL

As we all know, if-else judgment is useful everywhere. In SQL statements, the "CASE WHEN... THEN... ELSE... END" statement can be used to add, delete, modify and check all kinds of statements.

Give a scenario: International Working Women's Day big feedback, new users registered in 2020, all adult female accounts send 10 yuan red packets, other users send 5 yuan red envelopes, automatically recharge.

The example statement is as follows:

-- 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 for students in the college entrance examination, which needs to list the grades. More than 650 are key universities, 600-650 is a single university, 500-600 is an ordinary university, 400-500 is an ordinary university, and below 400 is a junior college.

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 = 500 and total_score = 400 and total_score

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.