首页 > mysql的小练习

mysql的小练习

建立如下表：

建表语句：

class表创建语句
create table class(cid int not null auto_increment primary key, caption varchar(32) not null)engine=innodb default charset=utf8;student表创建语句
create table student(-> sid int not null auto_increment primary key,-> name varchar(32) not null,-> gender varchar(8) not null,-> class_id int not null)engine=innodb default charset=utf8;teacher表创建语句
create table teacher(-> tid int not null auto_increment primary key,-> tname varchar(32) not null)engine=innodb default charset=utf8;course表创建语句
create table course(-> cid int not null auto_increment primary key,-> cname varchar(16) not null,-> teacher_id int not null)engine=innodb default charset=utf8;score表创建语句
create table score(-> sid int not null auto_increment primary key,-> student_id int not null,-> corse_id int not null,-> number int not null)engine=innodb default charset=utf8;

练习题目：

1、查询所有的课程的名称以及对应的任课老师姓名2、查询学生表中男女生各有多少人3、查询物理成绩等于100的学生的姓名4、查询平均成绩大于八十分的同学的姓名和平均成绩5、查询所有学生的学号，姓名，选课数，总成绩6、 查询姓李老师的个数7、 查询没有报李平老师课的学生姓名8、 查询物理课程比生物课程高的学生的学号9、 查询没有同时选修物理课程和体育课程的学生姓名10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名12、查询李平老师教的课程的所有成绩记录13、查询全部学生都选修了的课程号和课程名14、查询每门课程被选修的次数15、查询之选修了一门课程的学生姓名和学号16、查询所有学生考出的成绩并按从高到低排序（成绩去重）17、查询平均成绩大于85的学生姓名和平均成绩18、查询生物成绩不及格的学生姓名和对应生物分数19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名20、查询每门课程成绩最好的前两名学生姓名21、查询不同课程但成绩相同的学号，课程号，成绩22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；24、任课最多的老师中学生单科成绩最高的学生姓名

答案：

#1、查询所有的课程的名称以及对应的任课老师姓名
SELECTcourse.cname,teacher.tname
FROMcourse
INNER JOIN teacher ON course.teacher_id = teacher.tid;#2、查询学生表中男女生各有多少人
SELECTgender 性别,count(1) 人数
FROMstudent
GROUP BYgender;#3、查询物理成绩等于100的学生的姓名
SELECTstudent.sname
FROMstudent
WHEREsid IN (SELECTstudent_idFROMscoreINNER JOIN course ON score.course_id = course.cidWHEREcourse.cname = '物理'AND score.num = 100);#4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECTstudent.sname,t1.avg_num
FROMstudent
INNER JOIN (SELECTstudent_id,avg(num) AS avg_numFROMscoreGROUP BYstudent_idHAVINGavg(num) > 80
) AS t1 ON student.sid = t1.student_id;#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECTstudent.sid,student.sname,t1.course_num,t1.total_num
FROMstudent
LEFT JOIN (SELECTstudent_id,COUNT(course_id) course_num,sum(num) total_numFROMscoreGROUP BYstudent_id
) AS t1 ON student.sid = t1.student_id;#6、 查询姓李老师的个数
SELECTcount(tid)
FROMteacher
WHEREtname LIKE '李%';#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
SELECTstudent.sname
FROMstudent
WHEREsid NOT IN (SELECT DISTINCTstudent_idFROMscoreWHEREcourse_id IN (SELECTcourse.cidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老师'));#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
SELECTt1.student_id
FROM(SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '物理')) AS t1
INNER JOIN (SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '生物')
) AS t2 ON t1.student_id = t2.student_id
WHEREt1.num > t2.num;#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
SELECTstudent.sname
FROMstudent
WHEREsid IN (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcidFROMcourseWHEREcname = '物理'OR cname = '体育')GROUP BYstudent_idHAVINGCOUNT(course_id) = 1);#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)
SELECTstudent.sname,class.caption
FROMstudent
INNER JOIN (SELECTstudent_idFROMscoreWHEREnum < 60GROUP BYstudent_idHAVINGcount(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
SELECTstudent.sname
FROMstudent
WHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = (SELECT count(cid) FROM course));#12、查询李平老师教的课程的所有成绩记录
SELECT*
FROMscore
WHEREcourse_id IN (SELECTcidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老师');#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)
SELECTcid,cname
FROMcourse
WHEREcid IN (SELECTcourse_idFROMscoreGROUP BYcourse_idHAVINGCOUNT(student_id) = (SELECTCOUNT(sid)FROMstudent));#14、查询每门课程被选修的次数
SELECTcourse_id,COUNT(student_id)
FROMscore
GROUP BYcourse_id;#15、查询之选修了一门课程的学生姓名和学号
SELECTsid,sname
FROMstudent
WHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = 1);#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCTnum
FROMscore
ORDER BYnum DESC;#17、查询平均成绩大于85的学生姓名和平均成绩
SELECTsname,t1.avg_num
FROMstudent
INNER JOIN (SELECTstudent_id,avg(num) avg_numFROMscoreGROUP BYstudent_idHAVINGAVG(num) > 85
) t1 ON student.sid = t1.student_id;#18、查询生物成绩不及格的学生姓名和对应生物分数
SELECTsname 姓名,num 生物成绩
FROMscore
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHEREcourse.cname = '生物'
AND score.num < 60;#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECTsname
FROMstudent
WHEREsid = (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcourse.cidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老师')GROUP BYstudent_idORDER BYAVG(num) DESCLIMIT 1);#20、查询每门课程成绩最好的前两名学生姓名
#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据
SELECT*
FROMscore
ORDER BYcourse_id,num DESC;#表1：求出每门课程的课程course_id,与最高分数first_num
SELECTcourse_id,max(num) first_num
FROMscore
GROUP BYcourse_id;#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_num
SELECTscore.course_id,max(num) second_num
FROMscore
INNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id
) AS t ON score.course_id = t.course_id
WHEREscore.num < t.first_num
GROUP BYcourse_id;#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_num
SELECTt1.course_id,t1.first_num,t2.second_num
FROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1
INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id
) AS t2 ON t1.course_id = t2.course_id;#查询前两名的学生（有可能出现并列第一或者并列第二的情况）
SELECTscore.student_id,t3.course_id,t3.first_num,t3.second_num
FROMscore
INNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHEREscore.num >= t3.second_num
AND score.num <= t3.first_num;#排序后可以看的明显点
SELECTscore.student_id,t3.course_id,t3.first_num,t3.second_num
FROMscore
INNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHEREscore.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BYcourse_id;#可以用以下命令验证上述查询的正确性
SELECT*
FROMscore
ORDER BYcourse_id,num DESC;-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
View Code

小知识：外键约束条件

　　外键约束有三种约束模式（都是针对父表的约束）：模式一： district 严格约束（默认的 ），父表不能删除或者更新已经被子表数据引用的记录模式二：cascade 级联模式：父表的操作，对应的子表关联的数据也跟着操作 。模式三：set null：置空模式，父表操作之后，子表对应的数据（外键字段）也跟着被置空。通常的一个合理的约束模式是：删除的时候子表置空；更新的时候子表级联。指定模式的语法：foreign key(外键字段)references 父表(主键字段)on delete 模式 on update 模式;注意：删除置空的前提条件是 外键字段允许为空，不然外键会创建失败。外键虽然很强大，能够进行各种约束，但是外键的约束降低了数据的可控性和可拓展性。通常在实际开发时，很少使用外键来约束。

 

转载于:https://www.cnblogs.com/one-tom/p/11177105.html

更多相关：

  • 【全网唯一】全网唯一能够跑通的，跑不通你来找我~用node.js完成微信支付下单功能，且只需要一个文件wxpay.js就解决业务

        先吐为敬！   最近心血来潮研究nodejs如何完成微信支付功能，结果网上一搜索，一大堆“代码拷贝党”、“留一手”、“缺斤少两”、“不说人话”、“自己都没跑通还出来发blog”、“各种缺少依赖包”、“各种注释都没有”、“自己都不知道在写什么”的程序大神纷纷为了增加自己博客一个帖子的名额而发布了各种千奇百�...

  • ceph bluestore源码分析：C++ 获取线程id

    阅读ceph源码过程中需要明确当前操作是由哪个线程发出，此时需要根据线程id来确认线程名称 C++获取线程id是通过系统调用来直接获取 函数描述 头文件: 函数名称:syscall(SYS_gettid) 该函数直接返回了一个pid_t int类型的数字，即为当前线程id 此外函数pthread_s...

  • 分库分表之后，主键的处理方法

    面试题 分库分表之后，id 主键如何处理？ 面试官心理分析 其实这是分库分表之后你必然要面对的一个问题，就是 id 咋生成？因为要是分成多个表之后，每个表都是从 1 开始累加，那肯定不对啊，需要一个全局唯一的 id 来支持。所以这都是你实际生产环境中必须考虑的问题。 面试题剖析 基于数据库的实现方案 数据库自增 id 这个就是说你的...

  • ORM操作models一对多、多对多关系

    ORM操作    单表、一对多表操作 1 from django.db import models 2 3 4 class UserGroup(models.Model): 5 title = models.CharField(max_length=32) 6 7 8 class UserInfo(m...

  • Class101–如何高效地构建强大的数字绘画

    你有没有对一幅画有过很好的想法，但不知道如何开始或努力完成这个过程？这门课的目的是给你工具和理解，帮助你把想法变成现实。 所有你需要知道的基本知识。 本课程将涵盖颜色和光线的基本原理、透视构图以及使你的过程高效可靠的高级技巧。 这门课是为初学者和中级艺术家开设的。 对于任何渴望扩展技能和开发高效流程的人来说，这是一堂完美的...

  • 2016-2022年AutoCAD起重机吊装计划和索具图纸

    AutoCAD Crane Lifting Plan and Rigging Drawings 2016-2022 完成AutoCAD 2D高级起重机提升计划和索具图纸-基于项目的培训   你会学到什么 学习所有基本和高级的AutoCAD 2D工具栏 学习高级块和动态块 准备AutoCAD面试和考试 创建图纸、物料清单和布局...

  • Blender中的主程序纹理学习课程 Master Procedural Texturing in Blender

    挖掘Blender不可思议的强大节点编辑器的无限潜力。 你会学到: 逐步构建高级和高度可定制的程序纹理。 将许多不同层次的细节结合成一个复杂而现实的结果。 从头开始构建高级程序纹理背后的思维过程。 使用组节点为您的材料创建超级方便的定制。 课程获取：Blender中的主程序纹理学习课程-云桥网 MP4 |视频:h264，12...

  • c4d中的3D插图制作视频教程 Skillshare – 3D Illustration in Cinema 4D

    技能分享——C4D的3D插图 教程大小解压后：1.33G 1920X1080 mp4 语言：英语+中英文字幕（根据原英文字幕机译更准确） C4D是众所周知的平易近人的软件，也是进入3D艺术梦幻世界的理想门户。 加入马特的这个课程，了解他作为专业3d艺术家多年来学到的伟大的工作流程技巧。 在这门初学者课程中，你将使用C4D来重...

  • Blender创作你自己的动画短片学习教程

    Filmmaking with Blender – Create your own animated Short Film 时长:1h 20m |视频:. MP4 1280×720，30 fps(r) |音频:AAC，44100 Hz，2ch |大小:1G 语言：英语+中英文字幕（根据原英文字幕机译更准确） 在本课程中，我将...

  • mysql 5.7 驱动_这些 MySQL 调优配置，你都知道吗？

    关注我的头条号，获取更多运维相关知识。你想了解哪方面的运维知识，请评论区告诉我，我会尽快更新~MySQL 数据库，基本上运维都会接触到，而对于数据库的性能优化，想必是大家比较关注的，下面分享一些调优技巧。先决条件安装 MySQL，版本大于 5.7有部分数据操作系统、数据库管理员权限MySQL 系统性能优化在系统层面，调整硬件和软件选项...

  • jpa分页查询_spring data jpa 居然提供了这么多查询方式！

    spring data jpa提供了多种查询方式，如下：方法名称查询继承Repository接口测试代码方法名称中支持的关键字(官方文档提供)使用JPA命名查询在User实体中定义jpql(类似于hql)jpql(百度百科)继承JpaRepository接口测试使用@Query查询和命名查询不同的是，将jpql写到了 dao 方法上，...

  • 看懂SqlServer查询计划(转)

    转自：http://www.cnblogs.com/fish-li/archive/2011/06/06/2073626.html   对于SqlServer的优化来说，可能优化查询是很常见的事情。关于数据库的优化，本身也是一个涉及面比较的广的话题，本文只谈优化查询时如何看懂SqlServer查询计划。由于我对SqlServer的认识...

  • Java课程寒假之开发记账本软件（网页版）之二

    一.实现基础功能之一（记账） 　　一个记账本最基础之一的功能就是记账，所以也是首先要解决的问题，我选择了上学期使用的MySQL数据库来对账本进行存储。 　　我选择记账的方法是分开记账，就是支出放在一个表，收入放在一个表，这样在数据库方面会比较的直观的看到，但是在显示的时候就会产生部美观的问题，就是支出和收入都是分开来的，没有办法按照时...

  • Navicat新建查询快捷键

    在Navicat中，我们选中一个表，双击打开，这是如果要新建查询这个表的sql语句，可以直接用快捷键  ctrl+q  会自动打开查询窗口，并直接写好 sql：select * from （当前打开的表名） 我们直接在后面补全查询条件即可，非常方便。 转载于:https://www.cnblogs.com/libin6505/...

©2024 11GX.COM