连接方式:
1、内连接-inner join
显示左表以及右表符合连接条件的记录
2、左外连接-left join
显示左表的全部以及右表符合条件的记录,右表不符合条件的 显示NULL
3、右外连接-right join
显示右表的全部以及左表符合条件的记录,左表不符合条件的 显示NULL
一、内连接
eg:
select * from girls,boys; 等效于 select *from girls inner join boys;
笛卡尔积
查询出牵手成功的男女嘉宾
-- 方法1
select * from girls,boys where girls.matchNum=boys.matchNum;
-- 方法2
SELECT * from girls inner join boys where girls.matchNum=boys.matchNum;
-- 方法3 使用on语法
SELECT * from girls inner join boys on girls.matchNum=boys.matchNum;
-- 方法4 使用using,使用该语法的前提条件是查询的表中有相同的字段,并且表达意思一样
select * from girls inner join boys using(matchNum)
二、左外连接
查询出牵手成功的男女嘉宾信息(以下几种方法)
select * from boys LEFT JOIN girls on boys.matchNum=girls.matchNum
where girlNum is not NULL;
select * from boys,girls where boys.matchNum=girls.matchNum;
select * from boys inner join girls on boys.matchNum=girls.matchNum;
查询出没牵手成功的男嘉宾信息 (以下几种方法)
select boys.boyNum,boys.matchNum from boys LEFT JOIN girls on boys.matchNum=girls.matchNum where girlNum is NULL;
select boys.boyNum,boys.matchNum from girls RIGHT JOIN boys on boys.matchNum=girls.matchNum where girlNum is null;
select * from boys where boys.boyNum not in
(select boys.boyNum from boys,girls where boys.matchNum=girls.matchNum)
三、右外连接
四、全连接
union all 和 union的区别:
union all 不去重
union去重
-- 查询所有的男女嘉宾信息
select girls.girlNum 嘉宾编号, girls.matchNum 牵手标号 from girls
union
select boys.boyNum 嘉宾编号, boys.matchNum 牵手标号 from boys;
查询牵手成功的嘉宾信息
实战:
-- 1)查询出投资用户的信息,显示用户昵称、手机号码、可用余额
-- -- insert into invest VALUES
-- -- (1,1,12,5000.00,SYSDATE(),1),
-- -- (2,3,14,60000.00,SYSDATE(),1),
-- -- (3,4,1,100000.00,SYSDATE(),1);
-- 1)查询出投资用户的信息,显示用户昵称、手机号码、可用余额
select member.RegName,member.MobilePhone,member.LeaveAmount from member,invest
where member.Id=invest.MemberID;
select member.RegName,member.MobilePhone,member.LeaveAmount from member inner join
invest on member.Id=invest.MemberID; -- 内连接
select member.RegName,member.MobilePhone,member.LeaveAmount from member left JOIN
invest on member.Id=invest.MemberID
where invest.id is not null ;-- 左连接 左表查询全部的,右表没匹配上的为null
-- 2)查询出未投资用户的信息,显示用户昵称、手机号码、可用余额
select member.RegName,member.MobilePhone,member.LeaveAmount from member left JOIN
invest on member.Id=invest.MemberID
where invest.id is null ;-- 左连接 左表查询全部的,右表没匹配上的为null
select * from member where id not in (SELECT memberId from invest);