实用SQL技巧

实用SQL语句

编写sql时，灵活运用一些技巧，可以大大简化程序逻辑。

• 插入或替换：
  我们希望在插入一条记录时，如果记录存在就先删除，然后再插入新记录。此时可以使用REPLACE语句，这样就不需要先查询再决定是否要先删除。

replace into students (name, gender, class_id)
values ('秋香', 0, '20211101');

• 插入或更新：
  我们希望在插入INSERT一条记录时，如果存在，就更新该记录。此时可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ... 语句。

insert into student (name, gender, class_id)
values ('石榴姐', 1, '20211101')
on duplicate key update name = '秋香'

• 插入或忽略
  插入INSERT一条记录时，如果该记录存在，则什么也不做，此时可以使用INSERT IGNORE INTO...语句。

# 插入数据时，如果存在则忽略，不做任何操作
insert ignore into student (name, gender)
values ('秋香1', 1);

• 快照
  如果想复制某个表的数据到一个新表，可以结合CREATE TABLE和SELECT：

create table copy_test 
select * from test t
where id = 1; # 条件非必须

• 写入查询结果集
  如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。
  创建一个统计成绩的表statistics，记录各班的平均成绩：

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

然后，我们就可以用一条语句写入各班的平均成绩：

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

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果

• 强制使用指定的索引：
  在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。

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