mysql学习—表内连接查询

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);