MySQL联表

-- 自联表

-- 建立salf_join_students表并向其中输入数据
create table salf_join_students(
sid varchar(10) primary key,
sname varchar(20) not null,
monitor_sid varchar(10) not null
);

insert into salf_join_students(sid,sname,monitor_sid) values
('s1','小石','s1'),
('s2','小张','s1'),
('s3','小王','s2'),
('s4','小黄','s3'),
('s5','小咪','s4'),
('s6','小徐','s6');

-- 直接联表，不加联表条件
-- 直接这么写会报错，因为from后边的两张表，或者主表和副标名字是一样的
select * from salf_join_students,salf_join_students;
-- 这种情况下，必须对其中至少一张表起别名
select * from salf_join_students,salf_join_students as salf_join_students2;
-- 添加上联表条件
select * from salf_join_students 学生,salf_join_students 班长
   where 学生.monitor_sid = 班长.sid;
select * from salf_join_students s join salf_join_students m on s.monitor_sid=m.sid;

-- 展示内联和外联
insert into join_classes (cid,cname) values ('c8','小何');
insert into join_students (sid,cid,sname)values('s7','c15','小任');

-- 内联查询
select * from join_classes c inner join join_students s on c.cid = s.cid;
-- 左外联 
select * from join_classes c left outer join join_students s on c.cid = s.cid;
select * from join_classes c left join join_students s on c.cid = s.cid;

-- 右外联
select * from join_classes c right outer join join_students s on c.cid = s.cid;
select * from join_classes c right join join_students s on c.cid = s.cid;