mysql学习—表内连接查询

水深无声 2024-04-17 05:36 54阅读 0赞

# 1. 内连接查询 #

查询两个表中符合条件的共有记录

内连接查询效果图:

![20190822220151375.png][]

内连接查询语法格式:

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

[20190822220151375.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/0c61ea490d264d8783a387aa79e213b3.png