-- 建表
CREATE TABLE `xin_stu_t_bak` (
  `id` bigint NOT NULL COMMENT '主鍵',
  `relation_id` bigint DEFAULT NULL COMMENT '外鍵, 記錄教師id',
  `student_name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `student_age` bigint DEFAULT NULL COMMENT '年齡',
  `school` varchar(300) DEFAULT NULL COMMENT '學(xué)校',
  KEY `relationid` (`relation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `xin_teach_t_bak` (
  `id` bigint NOT NULL COMMENT '主鍵',
  `teacher_name` varchar(30) DEFAULT NULL COMMENT '教師姓名',
  `teacher_age` bigint DEFAULT NULL COMMENT '教師年齡',
  `school` varchar(300) DEFAULT NULL COMMENT '學(xué)校',
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 加索引
create index id on xin_stu_t_bak(relation_id);
create index id on xin_teach_t_bak(id);
-- 添數(shù)據(jù)
INSERT INTO lelele.xin_stu_t_bak (id,relation_id,student_name,student_age,school) VALUES
	 (1,NULL,'尤仁義1',11,'徐州中學(xué)'),
	 (2,1,'尤仁義2',12,'徐州中學(xué)'),
	 (3,NULL,'朱有理1',11,'徐州中學(xué)'),
	 (4,2,'朱有理2',12,'徐州中學(xué)'),
	 (5,2,'朱有理3',13,'徐州中學(xué)'),
	 (6,3,'宋昆明1',11,'徐州中學(xué)'),
	 (7,3,'宋昆明2',12,'徐州中學(xué)'),
	 (8,14,'宋昆明3',13,'徐州中學(xué)');
INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES
	 (1,'王翠花1',31,'徐州中學(xué)'),
	 (2,'王翠花2',31,'徐州中學(xué)'),
	 (3,'王翠花3',33,'徐州中學(xué)'),
	 (4,'王翠花4',34,'徐州中學(xué)'),
	 (5,'王翠花5',35,'徐州中學(xué)'),
	 (1,'王翠花1',31,'徐州中學(xué)'),
	 (1,'王翠花1',31,'徐州中學(xué)'),
	 (2,'王翠花2',31,'徐州中學(xué)'),
	 (6,'王翠花6',31,'徐州中學(xué)'),
	 (7,'王翠花7',31,'徐州中學(xué)');
INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES
	 (8,'王翠花8',33,'徐州中學(xué)'),
	 (9,'王翠花9',34,'徐州中學(xué)'),
	 (10,'王翠花10',35,'徐州中學(xué)'),
	 (11,'王翠花11',31,'徐州中學(xué)'),
	 (12,'王翠花12',31,'徐州中學(xué)'),
	 (13,'王翠花13',31,'徐州中學(xué)');

select * from xin_stu_t_bak a where a.relation_id in ( select id from xin_teach_t_bak b)

select * from xin_stu_t_bak a where a.relation_id not in ( select id from xin_teach_t_bak b)

select * from xin_stu_t_bak a

select * from xin_stu_t_bak a where exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

select * from xin_stu_t_bak a where not exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

for (int i = 0; i<10000; i++){ 
	for(int j = 0; j<10; j++){
	}
}

for (int i = 0; i<10; i++){ 
	for(int j = 0; j<10000; j++){
	}
}

select * from xin_stu_t_bak a where a.relation_id in ( select id from xin_teach_t_bak b where a.relation_id = b.id)

select * from xin_stu_t_bak a where a.relation_id in (‘1', ‘2')

select * from xin_stu_t_bak a where a.relation_id not in (‘1', ‘2')

select * from xin_stu_t_bak a where exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

select * from xin_stu_t_bak a where not exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

select * from xin_stu_t_bak a where a.relation_id not in ( select id from xin_teach_t_bak b where a.relation_id = b.id)

select * from xin_stu_t_bak a where not exists ( select 1 from xin_teach_t_bak b where a.relation_id = b.id)

select a.* from xin_stu_t_bak a left join xin_teach_t_bak b on a.relation_id = b.id where b.id is null;