MySQL小功能


1.创建一张与 A 表结构一样的 B 表

          
CREATE TABLE B LIKE A
          
        

2.1.将 A 表数据迁移到 B 表 (INSERT INTO ... SELECT)

          
SELECT * INTO B FROM A

INSERT INTO B SELECT * FROM A WHERE id < 100

INSERT INTO B(id, t_name, t_year, t_desc)
SELECT  id
       ,t_name
       ,t_year
       ,t_address
FROM A
WHERE id < 100


只迁移 B 表中不存在的数据
INSERT INTO B(t_name, t_year, t_desc)
SELECT  t.t_name
       ,t.t_year
       ,t.t_address
FROM A t
LEFT JOIN B j ON t.t_name = j.t_name
WHERE j.t_name IS NULL AND t.id < 100
          
        

2.2.创建一张表结构和数据来源自 A 表的 B 表, 即实现表备份 (CREATE TABLE ... SELECT)

          
CREATE TABLE B
SELECT id,t_name FROM A WHERE id = 2

CREATE TABLE B
SELECT * FROM A
          
        

2.3.数据不存在则插入, 存在则不插入且忽略异常 (INSERT IGNORE INTO)

          
INSERT IGNORE INTO B values(3, '张三', 2023, '上海')
          
        

2.4.数据不存在则插入, 存在则更新指定字段 (ON DUPLICATE key update)

          
INSERT INTO B values(3, '张三', 2023, '上海')
ON DUPLICATE key update t_year = 2023, t_desc = '四川成都'
          
        

3.1.将 A 表数据更新到 B 表

          
UPDATE B t SET t.address = ( SELECT j.address FROM A j WHERE j.user_id = t.user_id )

-- MySQL 不支持
UPDATE B t SET t.address = j.address FROM A j
WHERE t.user_id = j.user_id

-- B 表中与 A 表交集的数据被更新
UPDATE B t,A j
SET t.address = j.address
WHERE t.user_id = j.user_id
AND t.address = '' AND t.is_deleted = 0
AND j.address IS NOT NULL

-- B 表中与 A 表交集的数据被更新
UPDATE B t INNER JOIN A j ON t.user_id = j.user_id
SET t.address = j.address
WHERE t.address = '' AND t.is_deleted = 0

-- B 表中与 A 表交集的数据被更新
UPDATE B t LEFT JOIN A j ON t.user_id = j.user_id
SET t.address = j.address
WHERE t.address = '' AND t.is_deleted = 0
AND j.user_id IS NOT NULL

-- B 表全部数据被更新
UPDATE B t LEFT JOIN A j ON t.user_id = j.user_id
SET t.address = j.address
WHERE t.address = '' AND t.is_deleted = 0
          
        

4.数据备份与恢复
数据备份与恢复