系列文章目录

文章目录

多表查询两种方法

#数据准备
#建表
create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

不合理的多表查询连表操作

连表操作

​ 先将查询涉及到的表拼接成一张大表 一之后基于但表查询

笛卡尔积

select * from emp,dep;
select * from emp,dep where dep_id=id;
select emp.name,dep.name from  emp,dep where emp.dep_id=dep.id; 


'''
涉及到多表操作的时候，为了避免表字段重复
	需要在字段的前面加上表名限制
'''

合理的连表操作

inner join 内连接

  • 只连接两表痘存在的(有对应关系)的数据
	select * from emp inner join dep on emp_id=dep.id;

left join 左连接

  • 以左表为基准展示左表所有的数据，没有对应的数据则NULL填充
	select * from emp left join dep on emp.dep_id = dep.id;

right join 右连接

  • 以右表为基准展示右表所有的数据，没有对应的数据则NULL填充
	select * from emp right join dep on emp.dep_id = dep.id;

union 全连接

  • 展示左右两表所有的数据，没有对应的则NULL填充
	select * from emp left join dep on emp.dep_id = dep.id
	union 
	select * from emp right join dep on emp.dep_id = dep.id

多表查询方法之子查询

​ 子查询：分步操作。将一张表的查询结果当作另一条SQL语句的查询条件

1、查询部门时技术或者人力资源的员工编号
select id from dep where name in ('技术','人力资源');


2、根据部门编号去员工表中筛选出对应的员工数据
select * from emp where dep_id in (200,201);

"""
子查询：将SQL语句括号括起来即可充当查询条件
"""
select * from emp where dep_id in (select id from dep where name in ('技术','人力资源') );

多表查询练习题

1、查询所有课程的名称及对应的任课老师姓名
#涉及两张表 course 和 teacher 表
select * from course inner join teacher;

select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;


2、查询平均成绩大于八十分的同学姓名和平均成绩
avg(num)

select * from student inner join score;

select student_id,avg(num) from  score group by student_id;

select student_id,avg(num) as avg_num from  score group by student_id having avg_num > 80;

select student.sname,t1.avg_num from student inner join (select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80) as t1 on student.sid =t1.student_id;

练习

-- 1、 查询所有的课程的名称以及对应的任课老师姓名
	# 先查询所有课程的名称 'cname'及其教师id 'tercher_id'，
select teacher_id ,cname  from course;
	# 再根据教师id查询到教师名称
select cname as '课程名称',tname as '授课教师名称' from teacher inner join (select teacher_id,cname from course) as A on teacher.tid=A.teacher_id ;
——————————————————————————————————————————————————————————————————————————————————————

-- 2 查询平均成绩大于八十分的同学的姓名和平均成绩
 # 先取出平均成绩大于八十分的学生id和成绩
 select distinct student_id,avg(num) from score group by student_id;
 # 再根据学生id去student 表内查询学生姓名
 select sname as '学生姓名',new_num as '平均成绩'  from student inner join (select distinct student_id,avg(num) as new_num from score group by student_id) as B on student.sid = B.student_id;
 
——————————————————————————————————————————————————————————————————————————————————————

-- 3、 查询没有报李平老师课的学生姓名
	# 先在teacher获取到李平老师的id
	select tid from teacher where tname='李平老师'
	# 再去course表中获取到李老师教的班级id
	select cid from course where cid in (select tid from teacher where tname='李平老师');
	# 再去score表中获取到选择李老师课的学生，
	select distinct student_id from score where course_id in (select cid from course where cid in (select tid from teacher where tname='李平老师'));
	# 然后去student表里进行取反
	select sname  as '学生姓名' from student where sid not in (select distinct student_id from score where course_id in (select cid from course where cid in(select tid from teacher where tname='李平老师')));
	
——————————————————————————————————————————————————————————————————————————————————————	
	
-- 4、 查询没有同时选修物理课程和体育课程的学生姓名
 # 先去course查询物理和体育课程的id
 select cid from course where cname='物理' or cname='体育';
 # 再去score表中查询选择这个两个课程的所有学生id
 select distinct student_id from score where course_id in  (select cid from course where cname='物理' or cname='体育') group by student_id having count(student_id) =1;
 # 再根据学生id去student表中查询学生名字
 select sname as '学生名称' from student where sid in (select distinct student_id from score where course_id in  (select cid from course where cname='物理' or cname='体育') group by student_id having count(student_id) =1);
 
——————————————————————————————————————————————————————————————————————————————————————

-- 5、 查询挂科超过两门(包括两门)的学生姓名和班级
	# 先查询挂科超过两门(包括两门)学生id
	select student_id from score where num < 60 group by student_id having count(num) >=2 ;
	# 通过学生id 在表student 中找到其名字与班级名称id
	select class_id,sname from student where sid in (select student_id from score where num < 60 group by student_id having count(num) >=2);
	
	select F.sname, caption from class inner join (select class_id,sname from student where sid in (select student_id from score where num < 60 group by student_id having count(num) >=2)) as F on F.class_id = class.cid;

——————————————————————————————————————————————————————————————————————————————————————
SELECT * FROM score ORDER BY num desc LIMIT 1;
SELECT student_id,max(num) FROM score GROUP BY student_id;
——————————————————————————————————————————————————————————————————————————————————————

-- 6、查询所有的课程的名称以及对应的任课老师姓名
	select tname as '教师姓名',D.cname as '教授课程' from teacher inner join (select cname,teacher_id from course)as D on D.teacher_id=teacher.tid;
	
——————————————————————————————————————————————————————————————————————————————————————

-- 7、查询学生表中男女生各有多少人
	select gender as '性别',count(gender) as '个数'  from student group by gender;
	
——————————————————————————————————————————————————————————————————————————————————————

-- 8、查询物理成绩等于100的学生的姓名
	# 先查询物理这个课程的id
	select cid from course where cname = '物理';
	# 再在score 这个表中进行判断及筛选 获取到满分100且是物理课程的学生id
	select student_id from score where num = 100 and course_id in (select cid from course where cname = '物理'); 
	# 再去student表中获取学生名称
select sname as '物理课程满分100' from student where sid in (select student_id from score where num = 100 and course_id in (select cid from course where cname = '物理'));
—————————————————————————————————————————————————————————————————————————————————————— 

-- 9、查询平均成绩大于八十分的同学的姓名和平均成绩
	select sname as '学生姓名',new_num as '平均成绩'  from student inner join (select distinct student_id,avg(num) as new_num from score group by student_id) as B on student.sid = B.student_id;
 
——————————————————————————————————————————————————————————————————————————————————————

-- 10、查询所有学生的学号，姓名，选课数，总成绩
	# 先在score表中查询出所有学生的学号，选课数，总成绩
	select student_id,count(course_id),sum(num) from score group by student_id ;
	# 再把这些参数使用left join 添加到student表中，进行筛选。  			  注意！！！：在使用聚合函数时为避免关键字冲突可以在聚合函数后面 进行重新命名 如 count(course_id) course_num
	select sid as '学生学号',sname as '学生姓名',a.course_num as '学生选课数',a.num_num as '学生总成绩' from student left join (select student_id,count(course_id) course_num,sum(num) num_num from score group by student_id )as a on student.sid=a.student_id;
	
——————————————————————————————————————————————————————————————————————————————————————	

-- 11、 查询姓李老师的个数
	select count(tname) as '姓李老师的个数' from teacher where tname LIKE '李%';
	
——————————————————————————————————————————————————————————————————————————————————————

-- 12、 查询没有报李平老师课的学生姓名
	# 先获取到教师的id
	select tid from teacher where tname ='李平老师';
	# 通过教师id再获取到课程id
	select cid from course where teacher_id in (select tid from teacher where tname='李平老师');
	# 通过课程id获得学生id并去重
	select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname='李平老师')) ;
  # 通过学生id获取到学生姓名，再取反
 select sname as '未报刘老师课程的学生' from student where sid not in (select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname='李平老师'))); 
 
—————————————————————————————————————————————————————————————————————————————————————— 

-- 13、 查询物理课程分数比生物课程分数高的学生的学号
	# 先查询出物理课程与生物课程id
	select cid from course where cname = '物理';
	select cid from course where cname = '生物';
	# 再根据课程id去 score表中匹配合适的学生id和成绩
	select student_id as '学生物理id' ,num as'物理成绩',course_id as'物理' from score where course_id in (select cid from course where cname = '物理') inner join
	select student_id as '学生生物id',num as'生物成绩',course_id  as'生物' from score where course_id in (select cid from course where cname = '生物') ;
	!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 不会!!!!!!!!就离谱!!!!!!
	
——————————————————————————————————————————————————————————————————————————————————————
	
-- 14、 查询没有同时选修物理课程和体育课程的学生姓名
	 # 先去course查询物理和体育课程的id
 select cid from course where cname='物理' or cname='体育';
 # 再去score表中查询选择这个两个课程的所有学生id
 select distinct student_id from score where course_id in  (select cid from course where cname='物理' or cname='体育') group by student_id having count(student_id) =1;
 # 再根据学生id去student表中查询学生名字
 select sname as '学生名称' from student where sid in (select distinct student_id from score where course_id in  (select cid from course where cname='物理' or cname='体育') group by student_id having count(student_id) =1);
 
—————————————————————————————————————————————————————————————————————————————————————— 

-- 15、查询挂科超过两门(包括两门)的学生姓名和班级
	# 先查询挂科超过两门(包括两门)学生id
	select student_id from score where num < 60 group by student_id having count(num) >=2 ;
	# 通过学生id 在表student 中找到其名字与班级名称id
	select class_id,sname from student where sid in (select student_id from score where num < 60 group by student_id having count(num) >=2);
	
	select F.sname, caption from class inner join (select class_id,sname from student where sid in (select student_id from score where num < 60 group by student_id having count(num) >=2)) as F on F.class_id = class.cid;
	
——————————————————————————————————————————————————————————————————————————————————————

-- 16、查询选修了所有课程的学生姓名
	# 先获取到所有班级id
	select count(cid) from course;
	# 再根据id去score表中进行筛选 
SELECT student.sname FROM student WHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING COUNT( course_id ) = ( SELECT count( cid ) FROM course ) );

——————————————————————————————————————————————————————————————————————————————————————

-- 17、查询李平老师教的课程的所有成绩记录
  # 先查李平老师的id 
	select tid from teacher where tname = '李平老师';
	# 根据id找课程id
	select cid from course where teacher_id in (select tid from teacher where tname = '李平老师');
	# 拿到课程id去score 表中去成绩记录
  select student_id as '学生姓名',num as '学生成绩' from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
	
——————————————————————————————————————————————————————————————————————————————————————

-- 18、查询全部学生都选修了的课程号和课程名
	# 先获取到所有课程id
	select count(sid) from student ;
	# 在去score 表中进行筛选
	select course_id,count(student_id) count_id from score  group by course_id having count(student_id)=(select count(sid) from student) ;
	
	select cid,cname from course where cid in (select course_id from score  group by course_id having count(student_id))= (select count(sid) from student);
	
——————————————————————————————————————————————————————————————————————————————————————	
	
-- 19、查询每门课程被选修的次数
	select course_id as '课程名称',count(course_id) as '选修次数' from score group by course_id having course_id in (select cid from course);
	
——————————————————————————————————————————————————————————————————————————————————————

-- 20、查询之选修了一门课程的学生姓名和学号
	# 先查询到只选择一门课程的学生学号 
	select student_id from score group by student_id having count(course_id) =1;
	# 再去student 表内找到学生姓名
	select student.sid as '学生学号',sname as '学生姓名' from student inner join (select student_id from score group by student_id having count(course_id) =1) as S on S.student_id = student.sid;
	
——————————————————————————————————————————————————————————————————————————————————————	

-- 21、查询所有学生考出的成绩并按从高到低排序（成绩去重）
	select distinct num as '成绩' from score order by num desc;
	
——————————————————————————————————————————————————————————————————————————————————————	

-- 22、查询平均成绩大于85的学生姓名和平均成绩
	# 查询所有学生的id和平均成绩
	select student_id,avg(num) from score group by student_id;
	# 再查询出平均成绩大于85分的学生姓名
	select sname as '学生姓名',S.avg_num as '平均成绩' from student inner join (select student_id,avg(num) avg_num from score group by student_id) as S on S.avg_num > 85 and S.student_id = student.sid;
	
——————————————————————————————————————————————————————————————————————————————————————

-- 23、查询生物成绩不及格的学生姓名和对应生物分数
	# 查询出生物科目的id
	select cid from course where cname='生物';
	# 查询出所有生物成绩不及格学生id 和对应生物分数
	select student_id,num from score where num < 60 and course_id = (select cid from course where cname='生物');
	select sname as '学生姓名',S.num as '生物分数' from student inner join (select student_id,num from score where num < 60 and course_id = (select cid from course where cname='生物')) as S on S.student_id = student.sid;
	
——————————————————————————————————————————————————————————————————————————————————————

-- 24、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不所有课程)平均成绩最高的学生姓名
	# 先查询李平老师的所教的班级id
	select cid from course where teacher_id in (select tid from teacher where tname = '李平老师');
	# 再去score表中查询所有报了李平老师课程的学生
	select distinct student_id from score inner join (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')) as C on C.cid = score.course_id;
	# 再去score表中取平均成绩最高的学生
	select student_id,avg(num) from score group by student_id having student_id in (select distinct student_id from score inner join (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')) as C on C.cid = score.course_id) order by avg(num) desc limit 1;
# linux # bash # 运维
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

海量轨迹之外：重新审视2026具身智能的数据范式之争

该路线产出一批代表性数据集，覆盖家庭、办公、部分工业原型场景，能够支撑通用具身模型的训练迭代，但规模化复制到工业生产环境成本压力巨大。这条范式并不否定轨迹数据的价值，而是重新定义数据的使用方式：不再把海量历史轨迹作为模型训练的先决条件，更多依靠作业现场实时物理交互完成适配，以此适配离散制造高频换产的生产特征，该方案已经在真实工厂完成落地验证。遥操作、仿真合成、多源融合三类主流数据范式各有所长，在实

avatar DAMO开发者矩阵

万字综述｜具身智能多元范式：从 VLA 大模型到本能驱动的工业落地探索

本文系统梳理具身智能现有技术体系，从基础概念出发，解析分层决策、端到端VLA两大主流范式，对比具身学习、世界模型的实现逻辑，同时引入。人类在现实环境掌握技能，通常依靠书本学习、示范观摩，遇到陌生场景会评估环境、规划方案、在脑中推演，再动手执行，最后根据结果修正自身行为。具身智能（EmbodiedAI）是人工智能与机器人工程交叉领域，智能体搭载物理本体置身真实动态环境，完成感知、推理、行动、反馈闭环

avatar DAMO开发者矩阵

2026年新零售机器人品牌推荐：协作 复合与人形形态盘点

新零售机器人品牌推荐的关键不在品牌排名，而在形态与任务环节的匹配：门店轻量服务看轻量协作机器人，仓储转运看复合机器人，后端码垛看码垛工作站，跨区域综合服务开始看轮式人形机器人。艾利特机器人在这几种形态中均有可评估的产品线——LS 灵捷系列面向智慧零售与无人服务网点，迈幸复合机器人体系面向仓储移动操作，CP 系列码垛一体机面向电商仓储后端，Centaur-G1 面向跨区域综合服务。明确自身场景落在哪

avatar DAMO开发者矩阵