mysql表查询练习题

最新推荐文章于 2022-03-16 15:49:58 发布
最新推荐文章于 2022-03-16 15:49:58 发布
阅读量198 收藏 1
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/zzhhtt/p/9011211.html
版权

之前做练习的时候有遇到求每门课程的前几名的题目，困扰了很久，后面找到了查询的方法在这做个记录。

#建表

  
/*
 数据导入：
 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;

　　

 #查询出每门课程的前两名的分数

select 
	sname,cname,num,rank 
from 
	course
inner join
	(select 
		sname,cid,num,rank 
	from 
		student
	inner join 
		(select 
			score.student_id sid,t4.course_id cid,t4.num num,t4.rank rank 
		from 
			score
		inner join
			(select 
				course_id,num,rank 
			from 
				(select 
					t1.course_id,t1.num,count(*) as rank 
				from 
					(select course_id,num from score
					group by course_id,num) as t1
				inner join
					(select course_id,num from score
					group by course_id,num) as t2
				on t1.course_id=t2.course_id and t1.num<=t2.num
				group by course_id,num) as t3
			where rank<3
			order by course_id,num desc) as t4
		on score.course_id=t4.course_id and score.num=t4.num
		order by score.course_id,t4.rank) as a
	on student.sid=a.sid) b
on course.cid=b.cid

　　

 

 

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

转载于:https://www.cnblogs.com/zzhhtt/p/9011211.html

aian1859
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL_MySQL俩表索引优化实例
u013185175的博客
02-13 789
1.引入 我们呢，在上一节课的时候优化了一张单一的数据库表。那么接下来呢。我们一起优化一下俩张数据库表的索引。 2.创建操作的数据库 创建用户表：admin,并添加数据 -- ---------------------------- -- Table structure for admin -- ---------------------------- DROP TABL...
mysql专项练习----菜鸟水平提升第一道题
无名小辈
08-03 589
按照上回老哥的代码进行一次练习 创建数据库 插入字段 SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `Teacher` -- ---------------------------- DROP TABLE IF EXISTS `Teacher`; CREATE TABLE...
oracle apex触发器,导入sql文件时，Oracle APEX不会创建触发器
weixin_35733495的博客
04-08 344
我有一个从 Oracle Express 导出的SQL文件(很可能是Navicat Lite)，并且有创建触发器的查询 .但是在使用APEX( SQL Workshop - > SQL Script )导入文件后，触发器未成功创建，没有错误 .查询是这样的，它们肯定是有效的，当我用 SQL Command 手动运行这些查询时，我可以成功创建触发器 .创建或替换TRIGGER“RESEN...
附件——数据库高级对应sql建表语句
不放手，不错过的博客
08-05 507
数据库高级——附件：建表语句
MySQL学习笔记（四）
qq_45740503的博客
03-16 617
MySQL学习笔记（四）4.1 基本的select查询语句4.2 列的别名4.3 去除重复行4.4 空值参与运算4.5 着重号 `order表`4.6 查询常数4.7 显示表结构4.8 过滤数据 `where关键字使用`4.9 基本的SELECT语句练习 /* SQLyog Ultimate v12.08 (64 bit) MySQL - 5.7.28-log : Database - testdb ****************************************************
MySQL单表查询练习
12-07
### MySQL单表查询练习知识点详解 #### 1. 查看EMP表中部门号为5的员工的姓名，职位，参加工作...以上就是针对MySQL单表查询练习题中的知识点详细解释。通过这些练习题，你可以更好地掌握MySQL查询的基础语法和技巧。
mysql查询练习题
12-11
一份关于mysql的题目，查询相关的练习题，一表，两表，多表等的连接查询。
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL查询练习题旨在帮助用户熟悉和提升SQL查询技巧，涵盖了多方面的数据库操作，如联接查询、...通过解决这些练习题，用户可以深入了解和掌握SQL查询语言的核心功能，为实际数据库管理与数据分析工作打下坚实基础。
MYSQL8.0练习题表清单和关联关系
04-02
用户MySQL8.0练习的表清单和关联关系图
SQL笔记-通过构建索引表方便数据库管理
IT1995的博客
04-17 4812
首先来帖下SQL代码吧： /* SQLyog Ultimate v11.24 (32 bit) MySQL - 5.5.56 : Database - demostrationtest ********************************************************************* */ /*!40101 SET NAMES utf8 */; /...
ORACLE数据库之ORA-02290检查校验问题解析
辉哥的博客
09-02 2443
问题描述 首先是在开发需求迭代时，发现数据库某个非空字段需要修改成非必填类型。然后就一顿操作猛如虎(就是数据库直接把必输选项的对号取消了)，测试环境一顿测试没问题，好了到了预生产环境，一开始就一片片报错：现实惨不忍睹，一片片啊。 Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02290: check constraint (TEST.SYS_C0016848) violated; ]; ORA-02290: ...
Spring-Security笔记3 相关数据库建立
杨毅的专栏
03-01 287
使用ORALCE数据库spring-security提供了一套建表语句表之间的关系：spring-security提供的建表语句-- ---------------------------- -- Table structure for PERSISTENT_LOGINS -- ---------------------------- DROP TABLE "PERSISTENT_LOGINS";...
java建站系统开发教程系列之设计表结构
weixin_30415801的博客
07-02 288
java建站系统开发教程系列之设计表结构 根据需求设计表结构如下： SET FOREIGN_KEY_CHECKS=0; -- Table structure for tbl_articles -- ---------------------------- DROP TABLE IF EXISTS tbl_articles; CREATE TABLE tbl_articles ( id int(1...
Oracle触发器使用
JavaCoder的专栏
11-19 464
触发器(trigger)         应用场景：1.复杂的安全性检查；2.数据确认；3.实现审计功能；4.完成数据的备份和同步。         类型：语句级触发器（针对是表）和行级触发器（针对是行， :old和:new表示一行的记录，其中：old表示操作之前的值，:new表示操作之后的值）。 基本结构 create or replace trigger
oracle数据库 练习一
苏凯祺的博客
07-26 2348
一、网络服务销售系统E-R图 案例分析：         现有一个关于网络服装销售系统的项目，要求我们开发数据库部分。 系统所能达到的功能包括以下几个方面：   1.客户注册功能。客户在购物之前必须先注册，然后才能浏览到所以库 存服装信息，如款式，价格等。   2.顾客可以浏览到库存服装信息，如款式，价格，所剩数量等。   3.顾客可以订购自己喜欢的服装，并可以在未付款之前修
mybatis框架学习（完整）
热门推荐
wei_li_2015的博客
06-27 3万+
  目录   总结jdbc出现的问题 mybatis介绍 mybatis入门程序 占位符#{}与字符串拼接符${}区别 mybatis框架的原理 别名配置与映射文件加载方式 mybatis与hibernate的比较   一、总结jdbc出现的问题     1、准备数据库，执行mysql语句。 -- ---------------------------- -- Tab...
SQL基础
bsbhenry的专栏
02-16 222
环境：mysql-5.5.15，mysql-front 5.3   新建table websites  SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `websites` -- ---------------------------- ...
【MySQL8入门到精通】运维篇-SQL实战（100道题库）
IT邦德
12-03 1万+
Mysql命令大全，助你顺利毕业考试
mysql单表查询练习题
最新发布
08-01
根据提供的引用内容，这是一个关于MySQL单表查询的练习题。根据引用[1]，查询语句是SELECT * FROM emp WHERE deptno=30，它的作用是从名为emp的表中选择所有列，其中部门编号为30的记录。而引用[2]是一个较为复杂的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值