MySQL入门基础4-多表查询（内连接，左右外连接，全连接）

多表查询

多表还是单表

问题：软件中不同模块，不同类型的数据怎么保存？

一个表：字段过多、表数据庞大，数据冗余、维护，无法体现数据间丰富的关系

多个表：怎么保持表与表之间的联系？

 示例1：

SELECT member.regName,member.mobilephone,loan.amount

FROM member,loan

WHERE member.id = loan.memberID

AND member.id = 1

内连接：

 

INNER JOIN

内连接，也称为等值连接

显示左表及右表符合连接条件（字段匹配关系）的记录

将两张表的列组合在一起，产出新的结果集，结果集是两个表的公共部分

不使用ON语法时，join、inner join、逗号、cross join结果相同，都是取两个表的笛卡尔积

-- INNER JOIN

SELECT * FROM boys INNER JOIN girls;

SELECT * FROM boys,girls ORDER BY boyNum ASC;

SELECT * FROM boys INNER JOIN girls WHERE boys.matchNum = girls.matchNum;

SELECT * FROM boys,girls WHERE boys.matchNum = girls.matchNum

-- ON/USING的方式

SELECT * FROM boys INNER JOIN girls ON boys.matchNum = girls.matchNum;

SELECT * FROM boys INNER JOIN girls USING(matchNum);

左/右外连接 LEFT/RIGHT JOIN：

 

 读取左表全部数据，即便右表没有对应数据

如果读取右边没有匹配的记录，右侧结果集字段讲为null

A LEFT JOIN B：以A表为基础连接B查询结果

查询出所有A记录

SELECT * FROM A LEFT JOIN B ON A.key=B.key;

查询出不在B表中的A记录

SELECT * FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;

SELECT boys.boyNum,boys.matchNum from girls RIGHT JOIN boys ON boys.matchNum = girls.matchNum WHERE girls.matchNum is NULL;

SELECT boys.boyNum,boys.matchNum from boys LEFT JOIN girls ON boys.matchNum = girls.matchNum WHERE girls.matchNum is NULL;

全连接：

完全连接返回左表和右表中的所有行

用UNION ALL的方式达到FULL JOIN的效果

查询A表B表所有数据

SELECT * FROM A LEFT JOIN B ON A.key=B.key

UNION ALL

SELECT * FROM A RIGHT JOIN B ON A.key=B.key;

查询A表B表公共数据

SELECT * FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL

UNION ALL

SELECT * FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;

UNION ALL/UNION（去重重复）

SELECT * FROM girls FULL JOIN boys;

SELECT girlNum 嘉宾编号,matchNum 牵手标号 FROM girls

UNION

SELECT boyNum 嘉宾编号,matchNum 牵手标号 FROM boys;

SELECT girls.girlNum 嘉宾编号,girls.matchNum 牵手标号 FROM girls LEFT JOIN boys

ON girls.matchNum = boys.matchNum WHERE boys.boyNum IS NOT NULL

UNION

SELECT boys.boyNum 嘉宾编号,boys.matchNum 牵手标号FROM boys LEFT JOIN girls ON boys.matchNum = girls.matchNum WHERE girls.girlNum IS NOT NULL;