大家好，又见面了，我是你们的朋友全栈君。
创建两个表：
CREATE TABLE a_student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
sno VARCHAR(20) NOT NULL COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘性名’,
sex TINYINT NOT NULL COMMENT ‘性别 1：男，2：女’,
sage int COMMENT ‘年龄’,
CREATE_time datetime DEFAULT NULL COMMENT ‘创建时间’,
test_time datetime COMMENT ‘测试时间’,
UPDATE_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
);
CREATE TABLE b_student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
sno VARCHAR(20) NOT NULL COMMENT ‘学号’,
sname VARCHAR(20) NOT NULL COMMENT ‘性名’,
sex TINYINT NOT NULL COMMENT ‘性别 1：男，2：女’
);
添加数据：
a_student：
b_student：
查询并集UNION ALL
例1：两表字段相同
SELECT sno,sname FROM a_student
UNION ALL
SELECT sno,sname FROM b_student
例2：两表字段不同
SELECT sno,sname FROM a_student
UNION ALL
SELECT sno,sex FROM b_student
例3：两表字段数量不一样
SELECT * FROM a_student UNION ALL
SELECT sno,sname FROM b_student
例4：两表字段数量不一样
SELECT sno,sname FROM a_stunt
UNION ALL
SELECT sno FROM b_student
查询并集UNION
例1：两表字段不同
SELECT sno,sname FROM a_student
UNION
SELECT sno,sex FROM b_student
结果不明显，新增数据：
a_student：
例2：两表字段相同，且有重复数据（UNION ALL）
SELECT sno,sname FROM a_student
UNION ALL
SELECT sno,sname FROM b_student
例3：两表字段相同，且有重复数据(UNION)
SELECT sno,sname FROM a_student
UNION
SELECT sno,sex FROM b_student
总结：去重去的是完全相同的数据，指查询的两个字段值都相同
交集INNER JOIN
— INNER JOIN (等值连接) 只返回两个表中联结字段相等的行
— inner join并不以谁为基础,它只显示符合条件的记录.
例1：以a表为主表
SELECT a.*
FROM a_student a
INNER JOIN b_student b
ON a.id=b.id AND a.sname=b.sname;
例2：以b表为主表
— USING(id,name) 等价于 on后面的条件
SELECT b.* FROM a_student a
INNER JOIN b_student b USING(id,sname)
差集:
例1：a-b
SELECT a.*
FROM
a_student a
LEFT JOIN b_student b
ON a.id=b.id AND a.sname=b.sname
WHERE b.id IS NULL
例2：b-a
SELECT b.*
FROM
a_student a
RIGHT JOIN b_student b
ON a.id=b.id AND a.sname=b.sname
WHERE a.id IS NULL
补充：
单纯的外连接：
例1：右外连接
SELECT *
FROM
a_student a
RIGHT JOIN b_student b
ON a.id=b.id AND a.sname=b.sname
例2：左外连接
SELECT *
FROM
a_student a
LEFT JOIN b_student b
ON a.id=b.id AND a.sname=b.sname
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/154947.html原文链接：https://javaforall.net
