MySQL多表关联查询实例演示

最新推荐文章于 2022-08-27 10:49:58 发布
最新推荐文章于 2022-08-27 10:49:58 发布
阅读量297 收藏
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/WeiMY666/article/details/116128986
版权

MySQL多表关联查询演示

前期准备

一，创建student表，并插入数据

##创建student表
create table student(
sid varchar(10)not null unique primary key,
sname varchar(10)not null ,
sage datetime,
ssex varchar(10)not null
);
##插入数据 
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
##查看上两步的结果
select*from student;

运行结果：
在这里插入图片描述

二，同上创建科目表course，教师表teacher，成绩表sc,并插入数据

##科目表
create table Course(
CId varchar(10)not null,
Cname nvarchar(10)not null,
TId varchar(10)not null
);
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
##教师表
create table Teacher(
TId varchar(10),
Tname varchar(10)not null
);
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
##成绩表
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

运行结果：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

实例演示

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

#1.把01课程对应的学生编号和成绩放在表a
#2.把02课程对应的学生编号和成绩放在表b
#3.在a表和b表学生编码相同的情况下找出a表成绩较高的人
select s.*, a.score as score_01, b.score as score_02
from Student s,
     (select sid, score from SC where cid=01) a,
     (select sid, score from SC where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid;

效果如下：
在这里插入图片描述

2.查询同时存在" 01 “课程和” 02 "课程的情况

#前两步思路同上，最后判断同一个学生编码是否同时存在在两个表中
select * from
	(select sid,score from SC where cid=01) A,
	(select sid,score from SC where cid=02) B
where A.sid = B.sid;

效果如下：
在这里插入图片描述

3. 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

#使用左连接left join...on
#左连接以前表为主，后表若不满足条件则返回null
select * from 
	(select sid,score from SC where cid=01) A
left join
	(select sid,score from SC where cid=02) B
on A.sid = B.sid;

效果如下：
在这里插入图片描述

4.查询不存在" 01 “课程但存在” 02 "课程的情况

#思路相同，改成使用右连接
select * from 
	(select sid,score from SC where cid=01) A
right join
	(select sid,score from SC where cid=02) B
on A.sid = B.sid
where A.sid is NULL;

效果如下：
在这里插入图片描述

5…查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid) p
where s.sid=p.sid and p.avg>=60;

select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
where s.sid=p.sid ;

select p.sid,s.sname,p.avg from 
(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
left join
Student s
on s.sid=p.sid ;

在这里插入图片描述

6.查询在 SC 表存在成绩的学生信息

#只需要匹配一下student表中和sc表中的sid
select distinct Student.*
from
Student,SC
where Student.sid = SC.sid;

在这里插入图片描述

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select s.sid,s.sname,cs.count,cs.sum from 
(select sid,count(score) count,sum(score) sum from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid;

在这里插入图片描述

8.查询没有学全所有课程的同学的信息

select s.sid,s.sname,IFNULL(cs.count,0) num from 
(select sid,count(score) count from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid
where IFNULL(cs.count,0)<(select count(distinct cid) as cid_num from Course);

在这里插入图片描述

9.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select Student.* from
(select distinct(sid) from SC
where cid in 
(select cid from SC where sid = '01')) A
left join 
Student
on A.sid = Student.sid;

在这里插入图片描述

10.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

select sid, 
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having cids = 
(select 
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having sid = "01") and sid <> "01";

在这里插入图片描述

select s.*, 
concat(max(case when sc.cid='01' then sc.cid else "" end),max(case when sc.cid='02' then sc.cid else "" end),max(case when sc.cid='03' then sc.cid else "" end) ) as cids
from SC sc,Student s
where sc.SId=s.SId
group by sid
having cids = 
(select 
concat(max(case when sc.cid='01' then sc.cid else "" end),max(case when sc.cid='02' then sc.cid else "" end),max(case when sc.cid='03' then sc.cid else "" end) ) as cids
from SC sc
group by sid
having sid = "01") and sid <> "01"; # <>为不等于!=

在这里插入图片描述

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sid,s.sname,avg(SC.score) avg from Student s,SC
where s.sid=SC.sid AND SC.score<60
GROUP BY SC.sid
having count(*)>1;

在这里插入图片描述

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select Student.sname,A.* from Student
right join
(select sid,score from SC 
where score < 60 and cid = '01'
order by score desc) A
on Student.sid = A.sid;

在这里插入图片描述

13.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select a.sid, a.cid, a.score, count(a.score<b.score)+1 as rank
from SC as a 
left join SC as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

14.按各科成绩进行排序，并显示排名， Score 重复时合并名次

select  a.sid ,a.cid, a.score, count( distinct b.score) as rank
from SC a 
left join   SC b  
on a.cid=b.cid  and a.score<=b.score
group by a.cid, a.sid
order by a.cid, a.score desc;

15.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select a.* ,count(a.cj<b.cj)+1 as rank
from 
(select sid,sum(score)  as cj from SC group by sid) a
left join 
(select sid, sum(score) as cj from SC group by sid) b
on a.cj<b.cj
group by a.sid
order by a.cj desc;

+------+-------+------+
| sid  | cj    | rank |
+------+-------+------+
| 01   | 269.0 |    1 |
| 03   | 240.0 |    2 |
| 02   | 210.0 |    3 |
| 07   | 187.0 |    4 |
| 05   | 163.0 |    5 |
| 04   | 100.0 |    6 |
| 06   |  65.0 |    7 |
+------+-------+------+

16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from SC sc
group by sc.sid
order by total desc)q;

17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from SC sc left join Course course
on sc.cid = course.cid
group by sc.cid;

18.查询各科成绩前三名的记录

select * from SC sc
where (
select count(*) from SC as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc;

19.查询每门课程被选修的学生数

select cid,count(sid) from SC
group by cid;

20.查询出只选修两门课程的学生学号和姓名

select s.sid,s.sname from Student s
where sid in
(select sid from SC
group by SC.sid
having count(SC.cid)=2
);

21.查询男生、女生人数

select ssex, count(*) from Student
group by ssex;

22.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select Course.cname,s.cid,s.avg FROM
Course,(select cid,avg(score) avg from SC GROUP BY cid) s
where Course.cid=s.cid
order by s.avg desc,s.cid asc;

+--------+------+----------+
| cname  | cid  | avg      |
+--------+------+----------+
| 数学   | 02   | 72.66667 |
| 英语   | 03   | 68.50000 |
| 语文   | 01   | 64.50000 |
+--------+------+----------+

23.查询 1990 年出生的学生名单

select * from Student
where year(sage) =1990;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
+------+--------+---------------------+------+

24.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select s.sname,SC.score from Student s,SC,Course
where s.sid = SC.sid
and Course.cid = SC.cid
and Course.cname = "数学"
and SC.score < 60;

+--------+-------+
| sname  | score |
+--------+-------+
| 李云   |  30.0 |
+--------+-------+

25.查询不及格的课程

select cid from SC
where score<60
GROUP BY cid;
霜月拾伍
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【跟上黑马学sql】学习笔记-MySQL-多表查询实例
weixin_67842001的博客
06-14 432
目录 创建emp表，并插入数据 多表查询实例 内连接演示 外连接演示 自连接 联合查询 ： 联合查询的多张表的列数必须保持一致，字段类型也要保持一致。 子查询
mysql多表查询+数据库备份基础
m0_52091913的博客
07-28 301
多表查询+数据库备份基础，暂时没有删除
MySQL SQL多表关联查询最佳实例
12-07
Mysql学习中 ，单表查询每个人都很容易掌握；但是多表之间的关联查询对于初学者是很难掌握的，以下是我学习中，结合老师所讲例子作的总结，主要就是三张表的关联查询。还包括group by操作。Join on；left join on；group by。
mysql数据库中多表关联查询的实例
weixin_30251587的博客
04-19 415
1.对于商店表，货物表，存在一对多的关系，即一个商店可以售卖多种货物。 查找：对数据库中所有商店表按所售卖货物的种类多少进行排序输出。 数据库中表格式： sql语句： select a.shop_id,a.shop_name from shop a left join goods b on a.shop_i...
mysql多表查询示例_mysql多表的查询的简单示例
weixin_28481133的博客
02-08 250
mysql多表的查询的简单示例发布时间：2020-05-14 10:49:08来源：亿速云阅读：194作者：三月下面讲讲关于mysql多表的查询的简单示例，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完mysql多表的查询的简单示例这篇文章你一定会有所受益。导入hellodb.sql生成数据库，并进入到数据库之中mysql -uroot < hellodb.sql...
MySQL深入浅出之多表查询实操
406156440的博客
08-24 2105
1. 内连接: 只显示匹配的数据 <四大连接条件cross, natural, using, on> # 查询员工的编号,姓名, 薪水, 部门编号 select empno,ename, sal, deptno from emp; # 查询这些以及另外一张表的部门名称 select empno,ename, sal, deptno from emp; select dname from dept where deptno = 20; # 这个20是上个命令查到的 select dname
多表查询及其案例
LJL1999的博客
08-27 1111
多表查询及其案例
mysql多表连接查询实例讲解
12-16
实际的项目，存在多张表的关联关系。不可能在一张表里面就能检索出所有数据。如果没有表连接的话，那么我们就需要非常多的操作。比如需要从A表找出限制性的条件来从B表中检索数据。不但需要分多表来操作，而且效率也...
mysql多表查询实例代码
最新发布
09-05
这个示例代码演示了MySQL多表查询的基本语法和用法。根据实际需求，你可以根据表结构和关联关系来编写适合自己的多表查询代码。<span class="em">1 #### 引用[.reference_title] - *1* [个人网站源码包含主页面，...
Mybatis的多表查询实例
不思量
04-16 684
通过实例演示Mybatis的多表查询（一对一、一对多、多对多）
数据库多表联合查询附简单例子
sinat_26566137的博客
02-28 1702
多表联合检索： SQL语句： Select 用逗号隔开的一排列名 From 表名1，表名2, … Where 检索条件+连接条件 相当于语句 如果没有检索条件+连接条件，同理于笛卡尔积 θ连接之等值连接： 例如：按"001"号课成绩由高到低顺序显示所有学生的姓名（二表连接） Select Sname From Student, SC Where Student.S# = SC.S# and...
MySQL - 多表查询与案例详解
m0_67391683的博客
07-28 798
内连接相当于查询A、B交集部分数据外连接外连接分为两种，分别是左外连接和右外连接左外连接查询左表所有数据，以及两张表交集部分数据右外连接查询右表所有数据，以及两张表交集部分数据自连接当前表与自身的连接查询，自连接必须使用表别名。...
MySQL联表查询
m0_51067047的博客
06-07 618
1、JOIN关键词 联表查询JOIN 这是student表 这是result表 查询参加了考试的同学(学号，姓名，科目编号，分数) /*思路： 1.分析需求，分析查询的字段来自哪些表(连接查询) 2.确定使用哪种连接查询？七种 确定交叉点(这两个表中哪个数据是相同的) 判断的条件：学生表中studentNo = 成绩表中的studentNo */ -- INNER JOIN 就类似取两个表的交集部分 SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`S
mysql sql语句多表合并UNION ALL和UNION
weixin_30735391的博客
10-17 2568
select d1.ID,CAST(d1.ID AS CHAR) AS intId, d1.CODE_TYPE, d1.CODE, d1.CODE_IMG, d1.VALUE from m_dict_code d1 where d1.CODE_TYPE in('USER_TYPE','addSupers') UNION ...
Mysql两表数据合并(union)
weixin_43262264的博客
04-22 1万+
遇到的问题是，有两个单独的订单统计表，分别统计的是不同渠道的不同商品按日期的统计数据，在后台数据展示时，又要把两类渠道的数据，按时间顺序放在一起展示，如下是简化后的两个商品统计表ClassA和ClassB，大概思路就是个自group后合并到一起。 class ClassA(db.Model): __tablename__ = "classa" id = db.Column(db.I...
8. 数据库表的关联
cwtnice的博客
02-23 9708
操作系统： windows IDE： Pycharm 后端系统开发中， 数据库设计是重中之重。特别是前后端分离的系统， 后端的职责基本就是数据管理， 开发的代码几乎都是围绕数据操作的。 因此，常用的数据库表和表之间的关系的设计就很重要。 目前使用的数据库系统主要还是关系型数据库(建立在关系模型基础上的数据库) 。 常见的关系型数据库：mysql、oracle、 sqlserver、SQLite ； 常见的非关系型数据库：mongodb； 而关系型数据库，设计的一个难点就是各种表之间的关联关系 。 常见的
mysql查询同时选修了两门_查找同时选修了C01及C02两门课程的学生姓名及学号
热门推荐
weixin_35186171的博客
02-05 1万+
慕盖茨4494581join两个选课表，如果学生选了2个及以上的课程，得到的数据是翻n倍的，答案不对，至少不完整。正常思路：1.得到所有包含C01、C02课程的选课：selectA.学号,B.课程名from选课表asAjoin课程表asBonA.课程号=B.课程号whereB.课程名in('C01','C02')orderbyA.学号2.得到同时包含C01、C...
MySQL复习记录【三】：连接查询
weixin_42927719的博客
11-16 455
连接查询 含义：又称多表查询，当查询字段来自多个表时，需要用到多表连接 笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 按年代分类： sql92标准 sql99标准（推荐）：支持内连接+外联接（左外+右外）+交叉连接 按功能分类： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外联接 全外联接 交叉连接 1.等值连接 注意： （1）多表等值连接的结果为多表的交集部分 （2）n表连接，至少需.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值