Mysql - DML [INSERT、UPDATE、DELETE、REPLACE]

---------------------------------
---------------------------------

-- DML语句对表数据进行insert[增], delete[删], update[改], merge[合并, Mysql不可用], replce into[删+增/增]

---------------------------------
---------------------------------

-- CREATE TABLE IF NOT EXISTS users2 (
--     `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
--     `username` VARCHAR(20) NOT NULL DEFAULT '',
--     `create_time` INT(11) NOT NULL DEFAULT 0
--  ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci COMMENT "用户表2";


-- INSERT 插入数据到数据库表中
-- DELETE 删除数据库表中的数据
-- UPDATE 修改数据库表中的数据
-- MERGE 插入所有行到另一个已经存在的表。如果要插入的行的键已匹配已经存在行，测更新已经存在的行而不是插入一个新行。
-- REPLACE INTO[先删除再插入] 类似insert插入操作。区别是replace会根据主键或者唯一索引检查数据是否存在，如果存在就先删除再更新，不存在则直接新增记录。

-- 常见DML操作 INSERT DELETE UPDATE

-- 向数据库表中插入数据 INSERT
INSERT INTO users(`id`, `username`, `status`, `create_time`) VALUES(1, '章三', 1, 1677045914); -- 插入条数据
INSERT INTO users VALUES(2, '章三2', 1, 1677055914); -- 省略字段，值必须要包含所有字段的值
INSERT INTO users(`id`, `username`, `status`, `create_time`) 
    VALUES(3, '测试', 2, 1677045924), (4, 'Hello', 1, 1677046114); -- 插入多条数据
INSERT INTO users VALUES(5, '测试你好', 2, 1677045924), (6, 'Yesterday', 1, 1677046114); -- 省略字段，值必须要包含所有字段的值
INSERT INTO users(`username`, `status`, `create_time`) VALUES('王二', 1, 1677046134); -- 不指定主键进行插入
INSERT INTO users(`username`, `status`, `create_time`) 
    VALUES('HAHA', 1, 1677046214), ('WORLD', 2, 1677046234); -- 不指定主键，批量插入多条

INSERT INTO users2(`id`, `username`, `create_time`) SELECT `id`,`username`,`create_time` FROM users;

-- 结语
-- 插入数据库表数据使用INSERT INTO TABLENAME(fieldList) VALUES(fieldValues),(fieldValues);
-- 插入数据可批量插入
-- 插入数据的sql中TABLENAME后边的字段列表可省略，但是注意，如果省略的话后边VALUES中的值必须要有所有字段的值才行
-- 插入数据的sql中TABLENAME后边的字段列表可以不写自增ID的字段，这样数据库就会自己去做处理


-- 删除数据库表中的数据 DELETE
-- DELETE FROM TABLENAME WHERE exp...;

DELETE FROM users; -- 删除表中所有数据, 慎用
DELETE FROM users where id = 1; -- 指定where条件进行删除
DELETE FROM users where username='Hello' AND status = 1; -- 指定where条件进行删除

-- 结语
-- DELETE 删除数据最好加上要删除的条件信息，不然会删除整张表所有数据
-- DELETE删除后自增字段下个值不变，不会重置，TRUNCATE会重置自增字段为初始值


-- UPDATE修改数据信息
-- UPDATE TABLENAME SET Field=NewValue WHERE exp...;

UPDATE users SET username="张三" WHERE id  = 1; -- 主键约束修改数据
UPDATE users SET username="张三" WHERE id  = 1; -- 主键约束修改数据，数据未发生改变，显示修改成功，匹配1行，影响0行
UPDATE users SET username="hhhhhhh" WHERE id = 100000000000; -- 不存在的数据也可以执行修改，返回匹配 0 影响行数为0


-- MERGE Mysql不可用

-- REPLACE 
-- REPLACE INTO TableName(FieldName) Values(Values);
-- 表必须有唯一主键索引, 因为REPLACE是按照主键索引进行数据判断的。

-- 插入一条数据 相当于INSERT INTO
REPLACE INTO users(username, status, create_time) VALUES('hehe2', 1, 1677045914); -- 插入一条数据，影响1行
-- 如果ID已存在则更新，否则插入一条数据
-- 相当于
-- if not exists (select 1 from t where id = 1) 
-- insert into TableName(id, fields) values(1, values) 
-- else 
-- update TableName set field = value where id = 1;

REPLACE INTO users(id, username, status, create_time) VALUES(10, 'hehe2', 1, 1677045914); -- 删除并以新值插入数据，影响2行
-- 删除时影响1行，重新插入时影响1行

REPLACE INTO users(username, `status`, create_time) VALUES ('AAAAA', 1, 1677046134), ('BBBBB', 2, 1677046134); -- 插入2行
-- 没有指定判断索引，所以插入两行数据，影响2行

REPLACE INTO users(`id`, `username`, `status`, `create_time`) 
    VALUES (10, 'CCCCCCC', 2, 1677045914), (13, 'DDDDD', 1, 1677045914); -- 批量操作
-- 影响3行，有一存在1条，被删除影响1行，重新插入影响1行，不存在的直接插入，影响1行，总记录2行，Duplicates 1行

REPLACE INTO users SET username="GGGGGGGG"; -- 插入一条username=GGGGGGGG的数据
REPLACE INTO users SET username="HHHHHH", id=14; -- 删除14，再以新值插入表中

REPLACE INTO users2 SELECT id, username, create_time from users limit 3;
-- 从users表中查出3条数据插入到users2表中
-- 如果users2中以存在从users表中查出的主键，就更新
-- SELECT的字段按位置插入到users2中，不用管SELECT的字段名叫啥


-- INSERT INTO 与 REPLACE INTO
-- INSERT INTO只向表中插入数据
-- REPLACE INTO向表中插入数据，插入之前会先判断主键数据是否存在，存在就先删除旧数据，然后把新数据再插入表中。
-- REPLACE INTo TABLENAME SET 像 UPDATE TABLENAME SET
-- REPLACE INTo TABLENAME SET 不能带WHERE，按唯一主键值判断是更新[删除再插入]还是插入
-- REPLACE INTO TABLENAME SELECT Fields FROM TABLENAME2... 从表2 查出数据插入表1中
-- INSERT INTO TABLENAME(Fields1) SELECT Fields FROM TABLENAME2... 从表2 查出数据插入表1中，主键不能重复,必须指定插入字段