表连接查询
一、交叉连接 - 笛卡尔积
查询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、查询考过‘王老师’老师所教课程的学校的信息
2 -- 方法1
3 select * from student where id in(
4 select stu_id from score where course_id=(
5 select course_id from teacher where name='王老师'
6 )
7 );
8 -- 方法2
9 select s.name,s.school
10 from student as s
11 inner join score as sc
12 on s.id =sc.stu_id
13 inner join teacher as t
14 on t.course_id=sc.course_id
15 where t.name='王老师';
16
17 -- 练习:2、查询在score表中有成绩的学员的信息
18 select * from student where id in(select stu_id from score);
19
20 -- 练习:3、查询'python基础'课程并且分数在80分以上的学员的姓名和学校
21 -- 方法1
22 select student.name,student.school from
23 student left join score
24 on student.id = score.stu_id
25 left join course
26 on score.course_id = course.id
27 where score>80 and cname='python基础';
28 -- 方法2
29 select name,school from student where id in(
30 select stu_id from score where score>80 and course_id = (
31 select id from course where cname='python基础'
32 )
33 );
34
35 -- 练习:4、查询和‘张三’相同班级以及相同专业的同学的信息
36 -- 方法1
37 select student.name,classinfo.classname,major.m_name from
38 student left join classinfo
39 on student.class_id=classinfo.id
40 left join major
41 on student.major_id=major.id
42 where class_id=(select class_id from student where name='张三')
43 and major_id=(select major_id from student where name='张三')
44 and student.name!='张三';
45 -- 方法2
46 select student.name,classinfo.classname,major.m_name
47 from student
48 inner join classinfo
49 on student.class_id=classinfo.id
50 inner join major
51 on student.major_id=major.id
52 where class_id=(select class_id from student where name='张三')
53 and major_id=(select major_id from student where name='张三')
54 and student.name!='张三';
练习题