MySql多表关联语句操作

最新推荐文章于 2023-08-21 21:00:00 发布
最新推荐文章于 2023-08-21 21:00:00 发布
阅读量876 收藏 6
点赞数
分类专栏： 数据库 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35996924/article/details/109268150
版权

MySql多表关联语句操作

一、建表语句

# 学生表
CREATE TABLE `student` (
  `id` varchar(20) NOT NULL COMMENT '学生编号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
  `birth` varchar(20) NOT NULL DEFAULT '' COMMENT '出生年月',
  `sex` varchar(10) NOT NULL DEFAULT '' COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 课程表
CREATE TABLE `course` (
  `id` varchar(20) NOT NULL COMMENT '课程编号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '课程名称',
  `teacher_id` varchar(20) NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 教师表
CREATE TABLE `teacher` (
  `id` varchar(20) NOT NULL COMMENT '教师编号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 成绩表
CREATE TABLE `score` (
  `student_id` varchar(20) NOT NULL COMMENT '学生编号',
  `course_id` varchar(20) NOT NULL COMMENT '课程编号',
  `score` int(3) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`student_id`,`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、测试数据

--插入学生表测试数据
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');

--教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

--成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

三、测试

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

# 方式一
SELECT student.* , score.score AS 01_score,s.score AS 02_score
FROM student
JOIN score ON student.id = score.student_id AND score.course_id = '01'
LEFT JOIN score s ON student.id =s.student_id AND s.course_id = '02' OR s.score =NULL
WHERE score.score >s.score;

# 方式2
SELECT student.* , score.score AS 01_score , s.score AS 02_score 
FROM student , score , score s
WHERE student.id = score.student_id AND student.id = s.student_id 
AND score.course_id = '01' AND s.course_id = '02' AND score.score > s.score ;

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

SELECT student.id , student.`name` , ROUND(AVG(score.score),2) AS avg_score
FROM student 
JOIN score ON student.id = score.student_id 
GROUP BY student.id HAVING avg_score > 60;

3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

SELECT student.id , student.`name` , ROUND(AVG(score.score),2) AS avg_score
FROM student 
JOIN score ON student.id = score.student_id 
GROUP BY student.id HAVING avg_score < 60
UNION
SELECT student.id , student.`name` , 0 AS avg_score
FROM student
WHERE student.id NOT IN (SELECT DISTINCT student_id FROM score);

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT student.id , student.`name` , COUNT(score.course_id) AS sum_course , SUM(score.score) AS sum_score
FROM student
LEFT JOIN score ON student.id = score.student_id 
GROUP BY student.id ;

5、查询"李"姓老师的数量

SELECT COUNT(teacher.id)
FROM teacher 
WHERE teacher.`name` LIKE '李%';

6、查询学过"张三"老师授课的同学的信息

# 方式1
SELECT student.* 
FROM student , course , score , teacher
WHERE student.id = score.student_id AND course.id = score.course_id AND course.teacher_id = teacher.id AND teacher.`name` = '张三';

# 方式2
SELECT student.* 
FROM student 
JOIN score ON student.id = score.student_id
JOIN course ON score.course_id = course.id
JOIN teacher ON course.teacher_id = teacher.id
WHERE teacher.`name` = '张三';

# 方式3
SELECT student.* 
FROM student 
JOIN score ON student.id = score.student_id
WHERE score.course_id IN (SELECT course.id 
	FROM course 
	WHERE course.teacher_id = (SELECT teacher.id 
		FROM teacher 
		WHERE teacher.`name` = '张三'));

7、查询没学过"张三"老师授课的同学的信息

SELECT student.* 
FROM student
WHERE student.id NOT IN (
	SELECT student.id
	FROM student 
	JOIN score ON student.id = score.student_id
	JOIN course ON score.course_id = course.id
	JOIN teacher ON course.teacher_id = teacher.id
	WHERE teacher.`name` = '张三'
);

8、询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT student.*
FROM student , score s1, score s2
WHERE student.id = s1.student_id AND student.id = s2.student_id AND s1.course_id = '01' AND s2.course_id = '02';

9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

# 方式1
SELECT student.*
FROM student 
JOIN score ON student.id = score.student_id AND score.course_id = '01'
WHERE student.id NOT IN (
	SELECT student.id
	FROM student 
	JOIN score ON student.id = score.student_id AND score.course_id = '02'
)

# 方式2
SELECT student.* 
FROM student
WHERE student.id in (
	SELECT score.student_id
	FROM score
	WHERE score.course_id = '01'
) AND student.id NOT IN (
	SELECT score.student_id
	FROM score
	WHERE score.course_id = '02'
)

10、查询没有学全所有课程的同学的信息

# 方法1
SELECT student.*
FROM student
LEFT JOIN score ON student.id = score.student_id
GROUP BY student.id
HAVING count(student.id) < (
	SELECT count(course.id) 
	FROM course
)

# 方式2
SELECT student.* 
FROM student
WHERE student.id NOT IN (
	SELECT score.student_id 
	FROM score
	GROUP BY score.student_id
	HAVING count(score.student_id) = (
		SELECT count(course.id) 
		FROM course
	)
)

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

SELECT student.* 
FROM student
WHERE student.id IN(
	SELECT score.student_id
	FROM score
	WHERE score.course_id IN (
		SELECT score.course_id
		FROM score 
		WHERE score.student_id = '01'
	)
)

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

# 方式1
SELECT student.*
FROM student
WHERE student.id in (
	SELECT score.student_id
	FROM score
	GROUP BY score.student_id
	HAVING count(score.student_id) = (
		SELECT count(score.student_id) 
		FROM score
		WHERE score.student_id = '01'
	)
) AND student.id NOT IN (
	#找到‘01’同学没学过课程的同学
	SELECT score.student_id
	FROM score
	WHERE score.course_id in (
		#找到‘01’同学没学过的课程
		SELECT course.id
		FROM course
		WHERE course.id NOT IN (
			#找出‘01’同学学习的课程
			SELECT score.course_id
			FROM score
			WHERE score.student_id = '01'
		)
	)
) AND student.id NOT IN ('01');

# 方式2
SELECT student.* 
FROM (
	SELECT score.student_id , GROUP_CONCAT(score.course_id ORDER BY score.course_id) group1
	FROM score
	GROUP BY score.student_id
	HAVING score.student_id > '01'
) s1
JOIN (
	SELECT score.student_id , GROUP_CONCAT(score.course_id ORDER BY score.course_id) group2
	FROM score
	GROUP BY score.student_id
	HAVING score.student_id = '01'
) s2 ON s1.group1 = s2.group2
JOIN student ON student.id = s1.student_id;

13、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT student.`name`
FROM student
WHERE student.id NOT IN (
	SELECT score.student_id
	FROM score
	WHERE score.course_id IN (
		SELECT course.id 
		FROM course , teacher
		WHERE course.teacher_id = teacher.id AND teacher.`name` = '张三'
	)
)

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

# 方式1
SELECT student.id , student.`name` , s1.avg_score
FROM student
JOIN (
	SELECT score.student_id , ROUND(AVG(score.score),2) AS avg_score
	FROM score
	WHERE score.score < 60
	GROUP BY score.student_id
	HAVING COUNT(*) >=2
) AS s1 ON student.id = s1.student_id;

# 方式2
SELECT student.id , student.`name` , ROUND(AVG(score.score),2) AS avg_score
FROM student
LEFT JOIN score ON student.id = score.student_id
WHERE student.id in (
	SELECT score.student_id 
	FROM score
	WHERE score.score < 60 
	GROUP BY score.student_id
	HAVING COUNT(*) >= 2
)
GROUP BY student.id;

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

SELECT student.* , score.course_id , score.score
FROM student
LEFT JOIN score ON student.id = score.student_id
WHERE score.course_id = '01' AND score.score < 60 
ORDER BY score.score DESC;

16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

残*影
关注
专栏目录
MySQL如何多表关联更新
JustinMars的博客
06-06 603
在 MySQL 中，多表关联更新（也称为多表更新）通常使用 JOIN 语法。通过在 UPDATE 语句中使用 JOIN，可以一次性更新多个表中的数据。
mysql 多表关联次序_SQL语句分组排序，多表关联排序
weixin_34310322的博客
01-19 1606
SQL语句分组排序，多表关联排序总结几种常见的方法：案例一：在查询结果中按人数降序排列,若人数相同,则按课程号升序排列？分析：单个表内的多个字段排序，一般可以直接用逗号分割实现。select * from tableA order by col1 desc,col2 asc;--先按col1降序，相同则按col2 升序案例二：T-SQL查询班级信息：班级人数+班级信息，按人数多少排序？--创建测试...
sql多表关联语句
05-04
sql server 数据库 多表关联查询语句
【MySQL数据库的表连接语句】
DiKL_Y的博客
06-25 2770
连接语句
多表连接查询
weixin_30847865的博客
04-21 4833
本文主要列举两张和三张表来讲述多表连接查询。 新建两张表： 表1：student 截图如下： 表2：course 截图如下： （此时这样建表只是为了演示连接SQL语句，当然实际开发中我们不会这样建表，实际开发中这两个表会有自己不同的主键。） 一、外连接 外连接可分为：左连接、右连接、完全外连接。 1、左连接 left join 或 left outer join SQL语...
Mysql的关联查询语句
热门推荐
迟迟 CSDN
02-01 2万+
一 内连接( inner join） 1、多表中同时符合某种条件的数据记录的集合 (取两表公共部分) 2、inner join 可以缩写成 join 例如:   select * from A,B WHERE A.id=B.id 或者  select * from A inner join B on A.id=B.id 内连接分为三类:{ （1）等值连接: on A.id=B.id （...
关于Mysql多表关联语句查询
weixin_45725794的博客
01-29 127
有没有大佬解答一下啊
Mysql跨表更新 多表update sql语句总结
12-15
在MySQL数据库中，跨表更新是一项常见的操作，特别是在处理关联数据时。本篇文章将总结如何使用多表`UPDATE` SQL语句来实现这种更新。首先，我们有两个表，一个是`Product`，存储产品基本信息，包括产品价格（Price...
mysql实现多表关联统计(子查询统计)示例
09-09
在MySQL中，多表关联统计是一项常见的操作，特别是在数据分析和报表生成中。它涉及到从多个相关的数据表中提取并合并信息，以便进行更复杂的聚合计算。在这个示例中，我们将探讨如何利用子查询来处理多表关联统计的...
Mysql多表关联更新
qingxikeren的专栏
06-02 1万+
日常的开发中一般都是写的单表update语句，很少写多表关联的update。 在MySQL中我们有几种方法可以做到多表关联更新： 在两张表之间做关联，可以更新关联的多张表 update biao1，表2 set 表1.列名=表2.列名 --外键或关联字段 where 更新条件；--例如：表1.列名=表2.列名，或字段aa>1等等 通过 INNER JOIN，可以更新关联的多张表 update 表1 inner join 表2 on 表1.列名=表2.列名 --外表链接 ID se
MYSQL多表连接语句
m0_69917453的博客
09-09 1195
MYSQL多表连接
mysql两表关联查询语句
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 2494
mysql三张表关联查询 语句为：selecta.uid,a.uname,a.upsw,a.urealname,a.utel,a.remark,ifexists(select*fromsysdatabaseswherename='databaseName',dropdatabasedatabaseNamegotable ...
mysql关联查询语句
sunspun的博客
11-23 3113
MySQL关联查询前言内连接等值内连接where子句on子句using子句自然连接非等值内连接外连接左外连接右外连接全外连接交叉连接 前言 MySQL中当查询数据来自多张表时需要用到关联查询，其中关联查询又分为内连接、外连接、交叉连接。 创建两张表，分别是学生表、课程表。建表语句如下： create table employee( empNo INT PRIMARY KEY, eName varchar(10) not NULL, job varchar(10), salary VARCHAR(10
MySQL多表关联查询
weixin_65685029的博客
08-21 4617
一、关联查询方式 1、inner join--内连接 2、left join--左连接 3、right join--右连接 4、自连接 5、交叉连接 6、子查询 二、关联查询应用 1、inner join--内连接 2、left join--左连接 3、right join--右连接 4、自连接 5、交叉连接 6、子查询 7、group_concat（字段名）函数的使用
Mysql多表关联查询
rs勿忘初心的博客
02-21 1545
​​​​​三表联合查询语句，注意select时，如果某个字段在不同的表中有重复的话需要指定具体的表，比如应该使用：select replay_task.start_time from xxx，而不是select start_time from xxx，因为start_time在replay_task与general_flow两张表中同时存在。 select replay_case.case_name,replay_case.flow_id,replay_task.case_id,general_flow
mysql怎样用建表语_MySQL多表创建关联及操作
weixin_29057163的博客
02-18 211
外键现在有两张表“分类表”和“商品表”，为了表明商品属于哪个类别，通常情况下，我们将在商品上添加一列，用来存放分类的cid信息，此列成为外键。此时，分类表 category 称作主表，cid 成为主键。商品表 products 成为从表，category_id成为外键。通过主表和从表的外键描述主外键的关系，呈现的就是一对多的关系。外键特点1.从表外键的值是对主键的引用。2.从表外键类型必须...
mysql俩个表之间关联语法_MySQL多表关联SQL语句调优
weixin_35160923的博客
01-19 516
本文不涉及复杂的底层数据结构，是通过explain解释SQL，并根据当前可能出现的情况，来做具体的优化，使百万级、千万级数据表关联查询第一页结果能在几秒内完成(真实业务告警系统优化结果)。希望对您有一定的帮助。需要优化的查询：使用explain出现了Using temporary；有分页时出现了Using filesort则表示使用不了索引，需要根据下面的技巧来调整语句rows过多，或者几乎是全表...
多个表关联的查询语句
dianpi2993的博客
07-02 388
select a.*,(select table1.name from table1 where table1.id=a.table1ID) from table as a select a.*,b.* from table as a,table1 as b where ab.id=a.table1ID select a.* ,b.* from table as a left ...
mysql中多表关联查询语句
最新发布
05-10
在MySQL中，多表关联查询是指在查询中涉及到多个表，并且这些表之间存在关联关系，通过关联字段将这些表连接起来，实现数据的综合查询。常见的多表关联查询语句包括内连接、左连接、右连接和全连接。 1. 内连接 内...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值