1. 内连接查询
查询两个表中符合条件的共有记录
内连接查询效果图:
内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2;
说明:inner join 就是内连接查询关键字;on 就是连接查询条件
2. 例子
在这里我们先创建三个表分别是:student学生表,course课程表,stu_cour选课表
create table student(
id int unsigned primary key auto_increment not null,
name varchar(20) not null comment '学生姓名'
);
insert into student values(0,'诸葛亮'),(0,'虞姬'),(0,'孙尚香'),(0,'赵云'),(0,'宫本武藏');
create table course(
id int unsigned primary key auto_increment not null,
name varchar(20) not null comment '课程名称'
);
insert into course values
(0,'语文'),
(0,'数学'),
(0,'英语'),
(0,'物理'),
(0,'化学'),
(0,'历史'),
(0,'地理');
create table stu_cour(
stu_id int comment '学生id',
cour_id int comment '课程id',
primary key(stu_id,cour_id)
);
insert into stu_cour values
(1,1),(1,2),(1,3),(1,4),
(2,1),(2,2),(2,3),(2,5),
(3,1),(3,2),(3,3),(3,7),
(4,1),(4,2),(4,3),(4,4),
(5,1),(5,2),(5,3),(5,5);
例1,查询诸葛亮选了那些课程
select * from student as s inner join stu_cour as sc inner join course as c on s.id=sc.stu_id and sc.cour_id=c.id and s.name='诸葛亮';
例2,查询选物理课程的学生有哪些
select * from student as s inner join stu_cour as sc inner join course as c on s.id=sc.stu_id and sc.cour_id=c.id and c.name='物理';
例3,查询已经被选过的课程有哪些
select c.name from stu_cour as sc inner join course as c on sc.cour_id=c.id group by c.name;
例4,查询还没有被选过的课程有哪些
select name from course where name not in (select c.name from stu_cour as sc inner join course as c on sc.cour_id=c.id group by c.name);