牛客SQL34 (批量插入数据) SQL35 (批量插入数据，不使用replace操作) SQL42(删除emp_no重复的记录) SQL44(REPLACE替换)

一、题目

题目已经先执行了如下语句:

drop table if exists actor;
CREATE TABLE actor (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  DATETIME NOT NULL)

请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

二、解题思路

1. INSERT INTO 表名 VALUES

（1）批量插入

VALUES(value1, ...) , (value1, ...)

(2) 针对所有列插入，可以不用指定列

INSERT INTO actor(
    actor_id,first_name,last_name,last_update
)
VALUES(
    1,'PENELOPE','GUINESS','2006-02-15 12:34:33'
)
,
(
    2,'NICK','WAHLBERG','2006-02-15 12:34:33'
);

2.INSERT INTO 表名 SELECT

从一个表复制数据，然后把数据插入到一个已存在的表中。

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

插入多条数据的时候执行会执行多次 INSERT INTO VALUE效率较低，
使用 INSERT INTO SELECT 先查询要添加的数据再将所有数据进行拼接 在执行插入，提高效率

#字段必须完全相同
INSERT INTO table2
SELECT * FROM table1;

或者我们可以只复制希望的列插入到另一个已存在的表中：

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

–语句形式 3：

INSERT INTO table1 (字段1，字段2, ...) 
SELECT '值1','值2' ... 
UNION ALL SELECT '值1','值2' ... ;

具体代码如下

INSERT INTO actor
SELECT 1,'PENELOPE','GUINESS','2006-02-15 12:34:33'
UNION SELECT 2,'NICK','WAHLBERG','2006-02-15 12:34:33'

三、批量插入数据，不使用replace操作

1.题目

题目已经先执行了如下语句:

drop table if exists actor;
CREATE TABLE actor (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  DATETIME NOT NULL);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

2.解题

如果不存在则插入，如果存在则忽略

INSERT IGNORE INTO tablename VALUES(...);

如果不存在则插入，如果存在则替换
插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，

INSERT REPLACE INTO tablename VALUES(...);

这里指的存在表示的是unique属性的列值存在的情况下，unique表示键值唯一.
具体代码如下:

insert ignore into actor
VALUES('3','ED','CHASE','2006-02-15 12:34:33')

4. SQL42 删除emp_no重复的记录

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);

删除后titles_test表为

MySQL中不允许在子查询的同时删除表数据，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录。

DELETE FROM titles_test
WHERE id in 
    (
        SELECT id
        FROM 
        (
        SELECT *,
             RANK() OVER(PARTITION BY emp_no ORDER BY id ) as rank1
        FROM titles_test
     ) AS t  #临时表
    WHERE t.rank1!=1)

5.REPLACE替换

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。
（1）全字段更新替换。
如果主键存在，替换，如果不存在，添加

REPLACE INTO titles_test 
VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

（2）REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。

UPDATE titles_test
SET emp_no=REPLACE(emp_no,10001,10005)
WHERE id=5;