mysql多表查询练习

最新推荐文章于 2024-01-16 18:13:29 发布
最新推荐文章于 2024-01-16 18:13:29 发布
阅读量341 收藏 1
点赞数
分类专栏： MySQL 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42846555/article/details/120198517
版权

mysql多表查询练习**

  1. 查询科目对应的代课老师姓名（内，左，右连接查询）
#隐式内连接
SELECT
	co.cname,
	te.tname
FROM
	course as co,
	teacher as te
WHERE
co.teacher_id = te.tid

#显式内连接
SELECT
	co.cname,
	te.tname 
FROM
	course as co
	INNER JOIN teacher as te ON co.teacher_id = te.tid;

#左外连接
SELECT
	co.cname,
	te.tname 
FROM
	course as co
	LEFT JOIN 	teacher as te ON co.teacher_id = te.tid;
	
	-- 右外连接
SELECT
	co.cname,
	te.tname
FROM
	teacher as te
	RIGHT JOIN 	course as co ON te.tid =co.teacher_id;

在这里插入图片描述

  1. 查询物理成绩100分的姓名，班级，科目 ，成绩
SELECT 
	student.sname,
	class.caption,
	course.cname,
	score.num

FROM
course
JOIN  student
JOIN class
JOIN score on course.cid=score.course_id and student.sid=student_id and student.class_id=class.cid


WHERE

num=100 and cname='物理'

在这里插入图片描述
3.查询男女生人数

SELECT 
gender,
count(*) gender
FROM
student
	
GROUP BY
gender

在这里插入图片描述
4.查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
	* 
FROM
	(
SELECT
	student.sname,
	student.gender,
	class.caption,
	avg( score.num ) AS avg_score_num 
FROM
	score
	JOIN class
	JOIN student ON student.sid = score.student_id 
	AND student.class_id = class.cid 
GROUP BY
	student_id 
	) AS avg_score_num_list 
WHERE
	avg_score_num_list.avg_score_num >= 80

在这里插入图片描述

SELECT
	sname,
	avg_num 
FROM
	student
	JOIN ( SELECT student_id, avg( num ) avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t2 ON sid = t2.student_id;

在这里插入图片描述
5. 查询所有学生的学号，姓名，选课数，总成绩

SELECT
	student.sid,
	student.sname,
	student.gender,
	class.caption,
	count( * ) AS course_num,
	SUM( score.num ) AS score_num 
FROM
	score
	JOIN class
	JOIN student ON student.sid = score.student_id 
	AND class.cid = student.class_id 
GROUP BY
	student_id

在这里插入图片描述

SELECT
	student.sid,
	sname,
	course_num,
	total_mark 
FROM
	student
	JOIN ( SELECT student_id, count( * ) course_num, sum( num ) total_mark FROM score GROUP BY student_id ) AS t ON sid = student_id;

在这里插入图片描述
6. 查询姓李老师

SELECT 
*
FROM
teacher
WHERE
tname like"李%"

在这里插入图片描述
7.显示聚合汇总所有表格

SELECT 
student.sid,
student.sname,
student.gender,
class.caption,
course.cname,
score.num,
teacher.tname
FROM
student
JOIN class
JOIN score
JOIN course 
JOIN teacher on course.cid=score.course_id and course.teacher_id=teacher.tid and student.sid=score.student_id and class.cid=student.class_id

在这里插入图片描述
8.查询没有报李平老师课的学生姓名

SELECT
	* 
FROM
	student 
WHERE
	student.sid NOT IN (
SELECT
	score.student_id 
FROM
	score 
WHERE
	score.course_id IN ( SELECT course.cid FROM course WHERE course.teacher_id IN ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' ) ) 
	)

在这里插入图片描述

  1. 查询物理课程比生物课程高的学生的学号
SELECT 
*
FROM 
(SELECT
sw.sid as id,
wl.num as wlnum,
sw.num as swnum

FROM 
(	SELECT
*
FROM
score
WHERE
	score.course_id = (SELECT cid FROM course WHERE cname = '生物') ) as sw
	JOIN (SELECT
*
FROM
score
WHERE
	course_id = ( SELECT cid FROM course WHERE cname = '物理' ) ) as wl on sw.student_id=wl.student_id
	)  as wl_sw_num
	WHERE
	wl_sw_num.wlnum>=wl_sw_num.swnum

在这里插入图片描述
10. 查询没有同时选修物理课程和体育课程的学生姓名

SELECT
* 
FROM 
student 
WHERE 
student.sid not in (SELECT 
wl_form.student_id
FROM 
(SELECT 
score.student_id
FROM
score
WHERE
score.course_id=(SELECT cid FROM course WHERE course.cname='体育')
) as ty_form
JOIN (SELECT 
score.student_id
FROM
score
WHERE
score.course_id=(SELECT cid FROM course WHERE course.cname='物理')) as wl_form  on wl_form.student_id=ty_form.student_id )

在这里插入图片描述

新建表

/*
 数据导入：
 Navicat Premium Data Transfer
 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam
 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8
 Date: 10/21/2016 06:46:46 AM
*/
 
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
 
-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
 
-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
 
-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
 
-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
 
-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
 
-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
 
SET FOREIGN_KEY_CHECKS = 1;
默金……
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL单表查询练习
12-07
MySQL单表查询练习题
多表联查（58道Mysql练习）
咖啡有点苦的博客
04-21 3072
【代码】多表联查（58道Mysql练习）
Mysql多表联查——经典50题
weixin —> Ryan-0713
10-16 6135
Mysql多表联查
ruoyi-cloud代码生成（跨数据库）
m0_55675518的博客
09-15 2378
使用ruoyi-cloud生成代码，发现只能生成当前的数据表，在微服务开发当中，每个模块都来自于不同的数据库，因此需要修改代码生成的配置文件，因此有了这篇笔记，跨数据库生成代码。
MySQL基础——多表查询练习题
m0_73703409的博客
01-15 2743
1.查询员工的姓名、年龄、部门信息（隐式内连接）2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接）3.查询拥有员工的部门id、部门名称4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有分配部门，也需要展示出来5.查询所有员工的工资等级6.查询‘研发部’所有员工的信息及工资等级(两种写法)7.查询‘研发部’员工的平均薪资8.查询工资比‘灭绝’高的员工信息9.查询比平均薪资高的员工信息10.查询低于本部门平均工资的员工信息。
MySQL单表多表查询练习
HealerCCX的博客
01-09 946
MySQL单表多表查询
MySQL：多表查询练习
最新发布
05-07
MySQL：多表查询练习
mysql 多表联接查询练习
10-31
mysql,多表联接查询练习,mysql,多表联接内联,外联练习。 mysql,多表联接查询练习,mysql,多表联接内联,外联练习。
MySQL多表查询详解
03-08
文档讲述了MySQL中的多表查询和一些其他的知识点，这是以后经常用到的
MySQL练习——多表联查（99语法和92语法实现）
weixin_47540149的博客
03-26 462
对于sql92语法来说，表连接用的是where，where筛选用的也是where，混淆在一起不清不楚。注：inner可以省略，写上inner可以增加代码的可读性。1）逗号(“,”)换成(inner)join；表连接和后面的where条件筛选，分离开来。select {*|字段列名}select {*|字段列名}2）where换成on；where 查询条件;
MySQL必学！！！！练习题（多表查询）
weixin_52263599的博客
01-16 645
3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息。15.从student表和score表中查询出学生的学号，然后合并查询结果。17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩。4.从student表中查询计算机系和英语系的学生的信息。16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩。5.从student表中查询年龄18~22岁的学生信息。13.查询同时参加计算机和英语考试的学生的信息。9.用连接的方式查询所有学生的信息和考试信息。
MySQL|多表查询-练习
weixin_48118167的博客
12-16 714
尚硅谷21.11，shk，MySQL基础课程，多表查询练习题 题目见课件 -- 多表查询-1 #1 SELECT e.last_name,e.department_id,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id; #2 SELECT e.job_id,d.department_id,d.location_id FROM departments d,employees
MySQL练习(二):多表查询
小郑要做干饭人的博客
01-09 526
创建相应的表,以及添加数据: CREATE TABLE `stu` ( `sid` int(11) DEFAULT NULL, `sname` varchar(25) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` char(6) DEFAULT NULL, `score` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `groupLeaderId` int(11) DEFAUL
001云E办项目之创建项目
热爱编写程序的药学在读书
10-29 2515
这文章后续会陆陆续续的退出更多，有如下的一些建议： 如果你在想跟着一个项目练习，请认认真真看完没一点 你可以收获很多知识 说不定以后就是你去公司创建项目的流程 一、创建项目 1、创建一个SpringBoot项目 创建yeb为一个父项目，里面的除了pom.xml文件不删，其他的都有可以干掉 创建yeb-server为子项目，这个里面什么都不能删，这个是创建Maven项目 2、看父项目依赖 <?xml version="1.0" encoding="UTF-8"?> <project
【MySQL】多表查询策略（多表联查&子查询）
热门推荐
尹煜的博客
05-01 1万+
MySQL语句学习的难点和重点就在于多表查询，同时MySQL也有诸多方法供大家选择，不论是多表联查（联结表、左连接、右连接……）还是子查询（SELECT子查询、WHERE子查询、FROM子查询），掌握一种方法达到目的即可，当然其他方法也需要理解，本文将阐述完整的多表查询方法。 首先分享下学习链接，大家可以在看完本文后选择以下链接巩固学习成果，我相信如果都给掌握的话，MySQL语句中90%的问题都将迎刃而解！！ 视频学习链接：SQL学习之道_哔哩哔哩_bilibili
MySQL多表查询练习
weixin_42744020的博客
03-29 275
* 多表查询练习 -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); -- 添加4个部门 INSERT INTO dept(id,dnam...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值