1、首先准备两个数据表，如下图所示，结构需要一样。2、接着往第一个数据表先插入一些数据。3、将第一个中的插入进来。4、接着编写插入语句，注意这次直接在insert后面用select获取数据。5、然后我们就可以看到第二个数据表中有数据了。6、最后再进行select查询数据的时候还可以用where进行筛选。
2);insertinto #t1values(1,3);insertinto #t2values(1,2);insertinto #t2values(1,null);select*from #t1where c2notin(select c2from #t2);-->执行结果:无select*from #t1wherenotexists(select1from #t2where #t2.c2=#t1.c2)-->执行结果:1 3...
insert into A1 values(1,2); insert into A1 values(1,3); insert into A2 values(1,2); insert into A2 values(1,null); select * from A1 where c2 not in(select c2 from A2); -->执行结果:无(null) select * from A1 where not exists(select c2 from A2 where A2.c2=A1.c2); -->...
CREATETABLE`testa`(`id`int(11)NULLDEFAULTNULL);INSERTINTO`testa`VALUES(1);INSERTINTO`testa`VALUES(2);INSERTINTO`testa`VALUES(NULL);INSERTINTO`testa`VALUES(NULL);INSERTINTO`testa`VALUES(3);CREATETABLE`testb`(`id`int(11)NULLDEFAULTNULL);INSERTINTO`testb`VALUES(1);INSERTINTO`testb`VALUES(2)...
insert into #t1 values(1,2); insert into #t1 values(1,3); insert into #t2 values(1,2); insert into #t2 values(1,null); select * from #t1 where c2 not in(select c2 from #t2); -->执行结果:无 select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)...
INSERT INTO TABLENAME (id,likes) VALUES (1,18) ON DUPLICATE KEY UPDATE likes=likes+1; UPDATE TABLENAME SET likes=likes+1 WHERE id=1; 如果是插入值,返回受影响行数为1,如果执行了更新操作那么返回的受影响行数为2. 如果条件是针对于主键操作的,那么此操作是无效操作,等同于ignore 如果一次性插入多...
insert into #t1 values(1,2); insert into #t1 values(1,3); insert into #t2 values(1,2); insert into #t2 values(1,null); select * from #t1 where c2 not in(select c2 from #t2); -->执行结果:无 select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)...
create table#t1(c1 int,c2 int);create table#t2(c1 int,c2 int);insert into #t1values(1,2);insert into #t1values(1,3);insert into #t2values(1,2);insert into #t2values(1,null);select*from #t1 where c2 notin(select c2 from #t2);-->执行结果:无 ...
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1; 1. 加入a是唯一索引时,如果表中已存在a=1,则插入失败,等价于下面的update语句 UPDATE `table` SET `c`=`c`+1 WHERE `a`=1; 1. ...
1、方法一 IF NOT EXISTS(SELECT * FROM TABLE_NAME WHERE FILED1 = 1 ) THEN INSERT INTO TABLE_NAME VALUES(1 2、将要插入的数据先写入临时表，然后用 INSERT INTO TABLE_NAME SELECT * FROM #TEMP_TABLE A LEFT JOIN TABLE_NAME ON A.FILED1 = B.FIELD1 WHERE B.FILED1 IS NULL ...