多表关联查询之内关联，左关联

-- 同学持有的手机记录表

create table student_phone (
id int primary key,
stu_name varchar2(20),
phone varchar2(200)
)

insert into student_phone values (1,'小红','华为 荣耀v9');
insert into student_phone values (2,'小军','荣耀v9(华为)');
insert into student_phone values (3,'小红','小米note2');
insert into student_phone values (4,'小平','小米 note2');
insert into student_phone values (5,'小平','小米 note2');
insert into student_phone values (6,'小云','小米 Note2');
insert into student_phone values (7,'小云','小米 NOTE2');
insert into student_phone values (8,'小云','小米 NOTE 2');

 

commit

select phone,count(1) t from student_phone
group by phone order by t desc
---------------------------
drop table student_phone

create table student_phone (
id int primary key,
stu_name varchar2(20),
phone int
)

create table phone(
id int primary key,
phone_name varchar2(200)
)
insert into phone values(1,'荣耀v9(华为)');
insert into phone values(2,'小米 note2');
commit

select * from phone
select * from student_phone

insert into student_phone values (1,'小红',1);
insert into student_phone values (2,'小军',1);
insert into student_phone values (3,'小红',2);
insert into student_phone values (4,'小平',2);
insert into student_phone values (5,'小平',2);
insert into student_phone values (6,'小云',2);
insert into student_phone values (7,'小云',2);
insert into student_phone values (8,'小云',2);
commit

select phone,count(1) t from student_phone
group by phone order by t desc

可以通过phone=id
select * from student_phone,phone
where student_phone.phone=phone.id

------------------------------
select a.stu_name,b.phone_name from student_phone a,phone b
where a.phone=b.id

-------------------------------------
select phone_name,count(1) 总人数 from
(
select a.stu_name,b.phone_name from student_phone a,phone b
where a.phone=b.id
) ttttt
group by phone_name order by 总人数 desc

----------------------------

-- 多表关联
-- 内关联跟左关联只是改一下关键字inner—>left
-- 内关联
select * from student_phone,phone
where student_phone.phone=phone.id
-- 1.内关联 (丢失不满足的记录条数)
select * from student_phone a
inner join
phone b on a.phone=b.id

 

--
insert into student_phone values (9,'小成',3);
commit
select * from phone
select * from student_phone

select a.stu_name,b.phone_name from student_phone a
inner join
phone b on a.phone=b.id
--测试
insert into phone values(3,'iphone8（美国总统专用）');
commit
delete from phone where id=3

丢失了小成

-- 2.左关联

select a.stu_name,b.phone_name from student_phone a
left join
phone b on a.phone=b.id

-- 改进一下
select a.stu_name,nvl(b.phone_name,'未知') from student_phone a
left join
phone b on a.phone=b.id

 

--回归原始 (oracle专用+：即把丢失的+回来)
select a.stu_name,b.phone_name from student_phone a,phone b
where a.phone=b.id(+)

--求什么手机最多人使用（排序）
--分组[后]最大值
select * from(
select phone,count(1) counts from student_phone group by phone
order by counts desc
)
where rownum=1

select * from(
select phone,count(1) counts from student_phone group by phone
order by counts asc
)
where rownum=1