MYSQL之简单语句查询三

• 连接查询:
• 内连接多表查询 inner join...on

• //内连接就是:多个表中都有的数据拿出来,合并成一条数据
  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);