大家好,又见面了,我是你们的朋友全栈君。
创建两个表:
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
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/154947.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...