数据库内容复习04-MySql的其它操作

复习数据库方面的内容, 以下为笔记内容, 参考了:
廖雪峰的MySQL教程
SQL truncate 、delete与drop区别
…

插入(Insert)

-- 单插入
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

-- 多插入
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

备注: INSERT语句会返回实际插入的行数以 例如: Query OK, 4 rows affected

更新(Update)

-- 更新符合指定条件的行
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

-- 更新表的所有行(慎用!)
UPDATE <表名> SET 字段1=值1, 字段2=值2, ...;

备注: UPDATE语句会返回更新的行数以及WHERE条件匹配的行数 例如: Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

删除(Delete)

-- 删除符合指定条件的行
DELETE FROM <表名> WHERE ...;

-- 删除表的所有行(慎用!)
DELETE FROM <表名>;

-- truancate 清空表的所有行, 不同于Delete 这个操作不能回滚, 但速度更快
TRUANCATE <表名>;

备注: DELETE语句也会返回删除的行数以及WHERE条件匹配的行数 例如: Query OK, 1 row affected (0.01 sec)

其它实用插入/更新操作

-- 插入或替换: 如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

-- 插入或更新: 如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

-- 插入或忽略: 如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT

-- 全表快照(students_snapshoot 与 students 的结构内容一致)
CREATE TABLE students_snapshoot SELECT * FROM students;

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_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;