MYSQL表连接查询

表连接查询

一、交叉连接 - 笛卡尔积

​ 　　查询teacher和course表中所有的数据

​ 　　select * from teacher,course;

​ 　　select name,courseid,course.id,cname from teacher,course where teacher.courseid=course.id;

二、内连接

　　在关联的两张表中，把满足条件的数据筛选出来

　　select 字段,... ...

　　from 表1

　　inner join 表2

　　on 条件

　　inner join 表3

　　on 条件

-- 使用内连接查询teacher和course 表中的数据（姓名，年龄，课程名称，课时）
    select t.name,t.age,c.cname,c.cduration
    from teacher as t
    inner join course as c
    on t.course_id=c.id;
 -- 查询学员的姓名，年龄，所在班级名称，专业名称 并筛选出1902的学员
    select s.name,s.age,c.classname,m.m_name
    from student as s
    inner join classinfo as c
    on s.class_id=c.id
    inner join major as m
    on s.major_id=m.id
    where c.classname='1902';
-- 查询学员的姓名，毕业院校，所在班级，考试科目，考试成绩
    select s.name,s.school,c.classname,course.cname,sc.score
    from student as s
    inner join classinfo as c
    on s.class_id=c.id
    inner join score as sc
    on s.id=sc.stu_id
    inner join course
    on sc.course_id = course.id;

三、外链接

　　1.左外链接

　　作用：(1)左表中所有的数据都会查询出来（即便不满足条件）

　　　　　(2)将右表中满足关联条件的数据查询出来

　　　　　(3)关键不上的数据关联字段将以null作为填充（查询哪些课程没有老师去讲 就用左外链）

　　　　　语法：select 字

　　　　　　　　from A left join B

　　　　　　　　on 关联条件

-- 左外链接，左表：course 右表：teacher
-- 关联条件：teacher.course_id=course.id
-- 以左表为主 左表的id都显示出来 查询右表关联信息，未关联到的信息显示为null,一般用来查询null的值
select * from
course left join teacher
on teacher.course_id = course.id
where teacher.id is null;

​ 　　2.右外链接

​ 　　作用：1.右表中所有的数据都会查询出来（即便不满足条件）

　　　　　 2.将左表中满足关联条件的数据查询出来

　　　　　 3.关键不上的数据关联字段将以null作为填充

​ 　　语法：select 字段

　　　　　 from A right join B

　　　　    on 关键条件

-- 右外链接，左表:teacher,右表：course,
-- 关联条件：teacher.course_id=course.id
select * from
teacher right join course
on teacher.course_id = course.id
where teacher.id is null;

-- 练习：查询没有参加考试的学生
select student.name,score.score from
student left join score
on student.id=score.stu_id
where score.score is null;

四、子查询

　　1.什么是子查询：将一个查询的结果作为外侧操作的一个条件出现

　　2.语法：select ... from 表名 where 条件=(select ...);

-- 查询student表中比‘李四’年龄大的学员的信息
select * from student
where age>(select age from student where name='李四');

-- 练习：1、查询考过‘王老师’老师所教课程的学校的信息
-- 方法1
select * from student where id in(
    select stu_id from score where course_id=(
        select course_id from teacher  where name='王老师'
    )
);
-- 方法2
select s.name,s.school
from student as s
inner join score as sc
on s.id =sc.stu_id
inner join teacher as t
on t.course_id=sc.course_id
where t.name='王老师';

-- 练习：2、查询在score表中有成绩的学员的信息
select * from student where id in(select stu_id from score);

-- 练习：3、查询'python基础'课程并且分数在80分以上的学员的姓名和学校
-- 方法1
select student.name,student.school from
student left join score
on student.id = score.stu_id
left join course
on score.course_id = course.id
where score>80 and  cname='python基础';
-- 方法2
select name,school from student where id in(
    select stu_id from score where score>80 and course_id = (
        select id from course where cname='python基础'
    )
);

-- 练习：4、查询和‘张三’相同班级以及相同专业的同学的信息
-- 方法1
select student.name,classinfo.classname,major.m_name from
student left join classinfo
on student.class_id=classinfo.id
left join major
on student.major_id=major.id
where class_id=(select class_id from student where name='张三')
and major_id=(select major_id from student where name='张三')
and student.name!='张三';
-- 方法2
select student.name,classinfo.classname,major.m_name
from student
inner join classinfo
on student.class_id=classinfo.id
inner join major
on student.major_id=major.id
where class_id=(select class_id from student where name='张三')
and major_id=(select major_id from student where name='张三')
and student.name!='张三';