《SQL必知必会》学习笔记——第十三课 创建高级连结

第十三课 创建高级连结

一、使用表别名

二、使用不同类型的连结

1.自连结

（1）自连结用来替代从相同表中检索数据所使用的子查询语句

SELECT 
i1.id,
i1.age,
i1.city
FROM inf as i1,inf as i2
WHERE 
i1.city = i2.city
AND i2.name = 'allen'
;

2.自然连结

SELECT 
score.*,
inf.id,
inf.age,
inf.city

FROM inf LEFT OUTER JOIN score 
on inf.id = score.id
;

自然连结使得每列只返回一次，使用通配符来实现，如score.* 

3.外连结

（1）左外连结和右外连结可以互换位置来实现

SELECT 
score.*,
inf.id,
inf.age,
inf.city
FROM inf LEFT OUTER JOIN score 
on inf.id = score.id
;

 

4.使用带聚集函数的连结

SELECT 
score.*,
inf.id,
inf.age,
inf.city,
COUNT(score.rank) AS count_rank
FROM inf LEFT OUTER JOIN score 
on inf.id = score.id
GROUP BY inf.id
;