MySQL-09-连接查询
[toc]
1、连接方式概述
- 内连接-INNER JOIN
- 显示左表以及右表符合连接条件的记录
- 左外连接(左连接)-LEFT JOIN
- 显示左表的全部记录以及右表符合连接条件的记录
- 右表不符合条件的显示NULL
- 右外连接(右连接)-LEFT JOIN
- 显示右表的全部记录以及左表符合连接条件的记录
- 左表不符合条件的显示NULL
- 全连接-UNION ALL
- 完全连接返回左表和右表中所有的行
2、创建表
创建boys表与girls表,建表SQL见附录
3、内连接
-- 隐式内连接
SELECT * FROM girls,boys;
-- INNER JOIN
SELECT * FROM boys inner join girls;
SELECT * FROM boys join girls;
SELECT * FROM boys cross join girls;
-- 查询两表中matchNum相同的所有信息
SELECT * from girls,boys where girls.matchNum=boys.matchNum;
SELECT * FROM girls inner join boys where girls.matchNum=boys.matchNum;
-- 使用on
SELECT * FROM girls inner join boys on girls.matchNum=boys.matchNum;
-- 使用using
SELECT * FROM girls inner join boys using(matchNum);
- 使用on
- 使用using
4、左外连接
-- 显示出所有男生信息,并显示matchNum相同的matchNum信息
select * from boys LEFT JOIN girls on girls.matchNum=boys.matchNum;
-- 查询所有matchNum相同的男女信息
select * from boys LEFT JOIN girls on girls.matchNum=boys.matchNum where girls.matchNum is not null;
5、右外连接
-- 显示出所有女生信息,并显示matchNum相同的matchNum信息
select * from boys RIGHT JOIN girls on girls.matchNum=boys.matchNum;
-- 查询所有matchNum相同的男女信息
select * from boys RIGHT JOIN girls on girls.matchNum=boys.matchNum where boys.matchNum is not null;
!
6、全连接
union all 完全拼接
union 去重拼接
-- 返回两表中的所有信息
select * from boys
union all
select * from girls;
附录
CREATE TABLE `girls` (
`girlNum` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`matchNum` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-001', '1');
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-002', '2');
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-003', '3');
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-004', '4');
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-005', '5');
INSERT INTO `girls`(`girlNum`, `matchNum`) VALUES ('girl-006', '6');
CREATE TABLE `boys` (
`boyNum` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`matchNum` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-001', '1');
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-002', '2');
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-003', '3');
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-005', '8');
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-004', '7');
INSERT INTO `boys`(`boyNum`, `matchNum`) VALUES ('boy-006', '9');
-- 隐式内连接
SELECT * FROM girls,boys ;
-- INNER JOIN
SELECT * FROM boys inner join girls;
SELECT * FROM boys join girls;
SELECT * FROM boys cross join girls;
-- 查询两表中matchNum相同的所有信息
SELECT * from girls,boys where girls.matchNum=boys.matchNum;
SELECT * FROM girls inner join boys where girls.matchNum=boys.matchNum;
-- 使用on或using
SELECT * FROM girls inner join boys on girls.matchNum=boys.matchNum;
SELECT * FROM girls inner join boys using(matchNum);
-- 显示出所有男生信息,并显示matchNum相同的matchNum信息
select * from boys LEFT JOIN girls on girls.matchNum=boys.matchNum;
-- 查询所有matchNum相同的男女信息
select * from boys LEFT JOIN girls on girls.matchNum=boys.matchNum where girls.matchNum is not null;
-- 显示出所有女生信息,并显示matchNum相同的matchNum信息
select * from boys RIGHT JOIN girls on girls.matchNum=boys.matchNum;
-- 查询所有matchNum相同的男女信息
select * from boys RIGHT JOIN girls on girls.matchNum=boys.matchNum where boys.matchNum is not null;
-- 返回两表中的所有信息
select * from boys
union
select * from girls;