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