//内连接就是:多个表中都有的数据拿出来,合并成一条数据
select * from students as s inner join classes as c on s.cls_id=c.id;
//去掉重复的列
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
左连接
//students表为基准
select * from students as s left join classes as c on s.cls_id=c.id;
//从查询结果集中查询id是null的数据,使用having
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
右连接
//以classes表为基准,从students表中查询存在的数据
select * from students as s right join classes as c on s.cls_id=c.id;
自关联:
//省市区设计在同一个表中,在同一个表中做关联查询,称为自关联
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="广东省"
子查询
select * from students where height=(select max(height) from students);