CREATE TABLE union_tab_1 (
id INT,
val VARCHAR(10)
);
CREATE TABLE union_tab_2 (
id INT,
val VARCHAR(10)
); INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(1, 'A');
INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(2, 'B');
INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(3, 'C');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(1, 'A','11');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(1, 'C','22');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(2, 'B','33');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(4, 'D','44');
假设我两张表的数据如上面所示,我想通过对两张表的id和val字段都相等来查询出三个结果
1、两张表都有的记录如(1, 'A'); (2, 'B');
1、表一有表二没有的记录如(3, 'C');
3、表二有表一没有的记录如(1, 'C','22'); (4, 'D','44'); 本人刚接触数据库,对SQL只了解一些基本的语句,希望能有详细的说明。多谢了!MySQL数据库
id INT,
val VARCHAR(10)
);
CREATE TABLE union_tab_2 (
id INT,
val VARCHAR(10)
); INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(1, 'A');
INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(2, 'B');
INSERT INTO DEFECTDBTEST.union_tab_1 VALUES(3, 'C');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(1, 'A','11');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(1, 'C','22');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(2, 'B','33');
INSERT INTO DEFECTDBTEST.union_tab_2 VALUES(4, 'D','44');
假设我两张表的数据如上面所示,我想通过对两张表的id和val字段都相等来查询出三个结果
1、两张表都有的记录如(1, 'A'); (2, 'B');
1、表一有表二没有的记录如(3, 'C');
3、表二有表一没有的记录如(1, 'C','22'); (4, 'D','44'); 本人刚接触数据库,对SQL只了解一些基本的语句,希望能有详细的说明。多谢了!MySQL数据库
select a.* from t1 a inner join t2 b on a.id=b.id and a.val=b.val
1、表一有表二没有的记录如(3, 'C');
select a.* from t1 a left join t2 b on a.id=b.id and a.val=b.val
where b.id is null
3、表二有表一没有的记录如(1, 'C','22'); (4, 'D','44');
select b.* from t1 a right join t2 b on a.id=b.id and a.val=b.val
where a.id is null
select * from union_tab_1 where exists (select 1 from union_tab_2 where id=union_tab_1.id and val=union_tab_1.val)1、表一有表二没有的记录如(3, 'C');
select * from union_tab_1 where not exists (select 1 from union_tab_2 where id=union_tab_1.id and val=union_tab_1.val)3、表二有表一没有的记录如(1, 'C','22'); (4, 'D','44');
select * from union_tab_2 where not exists (select 1 from union_tab_1 where id=union_tab_2.id and val=union_tab_2.val)
我执行上面的语句后,表一增加了一个字段,我如何把表二查询出来的结果的flag字段修改为1?
UPDATE union_tab_2 SET flag=-1;
UPDATE union_tab_1 SET flag=0 WHERE EXISTS (SELECT 1 FROM union_tab_2 WHERE id=union_tab_1.id AND val=union_tab_1.val);
UPDATE union_tab_2 SET flag=0 WHERE EXISTS (SELECT 1 FROM union_tab_2 WHERE id=union_tab_1.id AND val=union_tab_1.val);
UPDATE union_tab_1 SET flag=1 WHERE NOT EXISTS (SELECT 1 FROM union_tab_2 WHERE id=union_tab_1.id AND val=union_tab_1.val);
UPDATE union_tab_2 SET flag=1 WHERE NOT EXISTS (SELECT 1 FROM union_tab_1 WHERE id=union_tab_2.id AND val=union_tab_2.val)
这样我实现了;可是对1楼和2楼的语句还是不怎么懂,不知道用一楼的方法如何实现上面更新的操作