Practical SQL Statements ​

Using flexible techniques when writing SQL can significantly simplify program logic.

Insert or Replace ​

If you want to insert a new record but delete the existing one if it already exists, you can use the REPLACE statement, which avoids the need for prior checks:

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, 'Xiao Ming', 'F', 99);

If the record with id=1 does not exist, the REPLACE statement will insert a new record; otherwise, the current record with id=1 will be deleted before inserting the new one.

Insert or Update ​

To insert a new record but update it if it already exists, use the INSERT INTO ... ON DUPLICATE KEY UPDATE ... statement:

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, 'Xiao Ming', 'F', 99) ON DUPLICATE KEY UPDATE name='Xiao Ming', gender='F', score=99;

If the record with id=1 does not exist, the INSERT statement will add a new record; otherwise, the existing record will be updated based on the specified fields.

Insert or Ignore ​

To insert a new record but ignore it if it already exists, use the INSERT IGNORE INTO ... statement:

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, 'Xiao Ming', 'F', 99);

If the record with id=1 does not exist, it will be inserted; otherwise, no operation will occur.

Snapshot ​

To take a snapshot of a table's data into a new table, combine CREATE TABLE with SELECT:

-- Create a snapshot of records with class_id=1 and store it in a new table students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

The new table will have the same structure as the original table.

Write Query Results to Table ​

To write the results of a query to a table, combine INSERT with SELECT to directly insert the results into a specified table.

For example, to create a table statistics that records 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)
);

You can then use a single statement to insert the average scores:

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

Ensure that the columns in the INSERT statement correspond one-to-one with those in the SELECT statement:

SELECT * FROM statistics;

+----+----------+--------------+ | id | class_id | average | +----+----------+--------------+ | 1 | 1 | 86.5 | | 2 | 2 | 73.666666666 | | 3 | 3 | 88.333333333 | +----+----------+--------------+ 3 rows in set (0.00 sec)

Force Use of Specified Index ​

When querying, the database system automatically analyzes the query and chooses the most suitable index. However, if you know which index to use, you can enforce its use with FORCE INDEX. For example:

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

The specified index idx_class_id must exist for this to work.