Mysql 常用关联查询（内连接)

Mysql 常用关联查询（内连接)

一对多

查找一个文章并显示每个文章的分类

内连接

select article.id as id,article.title as title,article_cate.title as cate 
from article 
INNER JOIN article_cate ON article_cate.id=article.cate_id

多对多

一个学生可以选修多门课程，一门课程也可以被多个学生选修。

1、查询张三选修了那些课程

普通查询（能用简单查询就用简单查询，简单查询不行，就用内连接）

1、id为1的学生选修了那些课程
2、查询出课程id对应的课程
SELECT * FROM lesson 
where id in (select lesson_id from lesson_student WHERE student_id=1)

笛卡尔积关联查询(比较耗费性能，不建议使用)

SELECT * FROM lesson,lesson_student 
where lesson.id=lesson_student.lesson_id AND lesson_student.student_id=1

INNER JOIN(内连接)

SELECT * FROM lesson 
INNER JOIN lesson_student ON lesson.id=lesson_student.lesson_id AND lesson_student.student_id=1

2、查询 Java 程序设计被那些学生选修了

Java 程序设计的 id 为 2

普通查询（能用简单查询就用简单查询，简单查询不行，就用内连接）

1、哪些学生id选修了课程id为2的课程（中间表)
2、通过学生id查询学生
SELECT * FROM student
where id in (select student_id from lesson_student WHERE lesson_id=2)

笛卡尔积关联查询(比较耗费性能，不建议使用)

SELECT * FROM student,lesson_student 
where student.id=lesson_student.student_id AND lesson_student.lesson_id=2

INNER JOIN(内连接)

SELECT * FROM student INNER JOIN lesson_student ON student.id=lesson_student.student_id AND lesson_student.lesson_id=2