一,union 和union all
union 并集,表中的所有数据,并且去除重复数据,同时进行默认规则排序;
union all 对两个结果进行并集操作,包括重复行,不进行排序;
union 操作符用于合并两个或多个select语句的结果集,需要满足以下条件:
1,相同数量的列;
2,列也必须拥有相似的数据类型;
3,同时,每条select语句中的列的顺序必须相同
二,建表:
DROP TABLE GestOptionCourse;
CREATE TABLE GestOptionCourse(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
course VARCHAR(20),
NAME VARCHAR(40),
tel VARCHAR(15),
teacher VARCHAR(20)
);
ROP TABLE MusicOptionCourse
CREATE TABLE MusicOptionCourse(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
course VARCHAR(20),
tel VARCHAR(20),
teacher VARCHAR(20),
tem VARCHAR(20)
);
插入数据:
INSERT INTO GestOptionCourse(NAME, course, tel, teacher)
VALUES('令狐冲', '紫霞秘籍', '10000001', '岳不群'),
('张无忌', '九阴真经', '10000002', '白猿'),
('周芷若', '九阴白骨爪', '10000003', '倚天屠龙刀');
SELECT * FROM MusicOptionCourse;
TRUNCATE TABLE MusicOptionCourse #清空表操作
INSERT INTO MusicOptionCourse(NAME, course, tel, teacher, tem)
VALUES('令狐冲', '笑傲江湖曲', '10000001', '任盈盈', '标记1')
,('乐之杨', '杏花天盈', '20000005', '乐韶凤', '标记2'),
('黄蓉', '碧海潮生曲', '20000006', '黄药师', '标记3');
三,操作:
两个表预览:
SELECT * FROM GestOptionCourse;
SELECT * FROM MusicOptionCourse;
注意:tem字段为故意留作验证
1,验证union和union all:
SELECT NAME,tel, course, teacher FROM MusicOptionCourse UNION SELECT NAME, tel, course, teacher FROM GestOptionCourse;
SELECT NAME,tel, course, teacher FROM MusicOptionCourse UNION ALL SELECT NAME, tel, course, teacher FROM GestOptionCourse;
结果竟然是一样的,这和union会去重复数据不一致;其实这是由于过滤出来的字段必须完全一致才能去重,
看以下取相同字段的语句:
SELECT NAME, tel FROM MusicOptionCourse UNION SELECT NAME, tel FROM GestOptionCourse
SELECT NAME, tel FROM MusicOptionCourse UNION ALL SELECT NAME, tel FROM GestOptionCourse
2,union两边字段不一致时会报错:
SELECT NAME, tel, teacher FROM MusicOptionCourse UNION ALL SELECT NAME, tel FROM GestOptionCourse
错误代码: 1222
The used SELECT statements have a different number of columns
3,字段顺序不一致时:
SELECT NAME,course, tel, teacher FROM MusicOptionCourse UNION SELECT NAME, tel, course, teacher FROM GestOptionCourse;
4,left join
SELECT a.name, a.tel FROM MusicOptionCourse a
LEFT JOIN GestOptionCourse b ON a.teacher = b.teacher AND a.tel=b.tel
明明是想结果查询出来一个符合条件的,为什么会出来三个呢,看下面语句就明白了:
SELECT * FROM MusicOptionCourse a
LEFT JOIN GestOptionCourse b ON a.name = b.name AND a.tel=b.tel
原来是做连接(left join)会把左侧的数据都列出来的原因;
变为下面语句即可:
SELECT * FROM MusicOptionCourse a
LEFT JOIN GestOptionCourse b ON a.name = b.name WHERE a.tel=b.tel