一文复习Mysql连接、自关联、自查询

# 内连接：把两张表相同的地方查询出来
select *
from a
join b
on a.id = b.id

select name,courseNo,courseName
from students s1
join scores s2
on s1.courseNo = s2.courseNo
where name = 'tony'

select *
from students
join scores
on students.studentId = scores.studentsId
join course
on scores.courseNo = course.courseNo
# 左连接：包括了内连接，同时还查询左表特有的内容
select *
from students
left join scores
on students.id = scores.id
# 右连接：包括了内连接，同时还查询右表特有的内容
select *
from scores
right join courses
on scores.couseNo = courses.courseNo
# 自关联
select * from areas a1
join areas a2
where a1.name = 'new york'
# 子查询
select * 
from students
where age > (select avg(age) from students)

select *
from scores
where studentNo in (select studentNo
                   from students
                   where age = 30)

select *
from (select * from students where sex = 'women') stu
join scores
on stu.stuId = scores.stuId