MySQL多表查询

上一篇文章是对单个表进行操作，下面是对多个表进行操作，进行查询

一）主键外键

例：

CREATE  TABLE  banji_course(

     banji_id  INT ,

     course_id  INT ,

     PRIMARY  KEY (banji_id,course_id),  -- 联合主键

     FOREIGN  KEY (banji_id)  REFERENCES  banji(id),  -- banji_id既是联合主键又是外键

     FOREIGN  KEY (course_id)  REFERENCES  course(id)  -- course_id既是联合主键又是外键

);  //将班级表和课程表进行关联

二）嵌套查询

SELECT  *  FROM  student 

WHERE  banji_id=( SELECT  id  FROM  banji  WHERE  ` name `= 'java18' );

SELECT * FROM student

WHERE banji_id  IN(SELECT id FROM banji WHERE name = 'java18' OR name = 'java17'); 

//"="：要求子查询只有一个结果。 "in"：子查询可以有多个结果

三）多表关联INNER JOIN / LEFT JOIN /  RIGHT JOIN

SELECT s.id,s.name,s.age,s.gender,b.name

FROM student AS s INNER JOIN banji AS b ON s.banji_id = b.id;//两个表联合查询,起别名是为了防止列名一样进行区分，on确定一下范围

SELECT *

FROM student AS s INNER JOIN banji AS b ON s.banji_id = b.id

INNER JOIN banji_course AS bc ON b.id = bc.banji_id 

INNER JOIN course AS c ON bc.course_id = c.id;//将查询出的表当成虚拟表进行关联

SELECT *

FROM banji AS b LEFT JOIN

banji_course AS bc ON b.id = bc.banji_id

LEFT JOIN course AS c ON bc.course_id = c.id;//LEFT JOIN左边的全部显示，on条件没达到的左表中的情况也全部显示，RIGHT JOIN 右表的全部显示

四）like模糊查找

SELECT * FROM student WHERE name LIKE '张%'；//%0个或多个任意字符

SELECT * FROM student WHERE name LIKE '张_';  //_只有一个任意字符

//加\转成本来的意思，在%前加\，显示本来%