Mysql学习之sql语句练习

最新推荐文章于 2022-11-13 16:34:38 发布
最新推荐文章于 2022-11-13 16:34:38 发布
阅读量129 收藏
点赞数
原文链接：http://www.cnblogs.com/quanloveshui/p/11065522.html
版权

    本文主要练习mysql中group by、having、order by、limit等的基本使用。练习题、表结构以及测试数据均借用老男孩教程中的数据。

一、表结构和数据

    表结构和表数据

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

表结构和数据
View Code

    表的数据示例

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 二、练习题

    1、查询“生物”课程比“物理”课程成绩高的所有学生的学号

思路：
    获取所有有生物课程的人（学号，成绩） - 临时表
    获取所有有物理课程的人（学号，成绩） - 临时表
    根据【学号】连接两个临时表：
        学号  物理成绩   生物成绩



SELECT
    A.student_id,sw,tyFROM
    (SELECT student_id,num AS sw FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '生物') AS A
LEFT JOIN 
   (SELECT student_id,num AS ty FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '体育') AS B 
ON A.student_id = B.student_id
WHERE sw > IF (isnull(ty), 0, ty);
View Code

     2、查询平均成绩大于60分的同学的学号和平均成绩

SELECT student_id,avg(num) FROM score GROUP BY student_id HAVING avg(num) > 60
View Code

    3、查询所有同学的学号、姓名、选课数、总成绩

SELECT
    score.student_id,
    student.sname,
    sum(score.course_id),
    sum(score.num)
FROM
    score
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
    score.student_id
View Code

    4、查询姓“李”的老师的个数

SELECT count(*) FROM teacher WHERE tname LIKE '李%'
View Code

    5、查询没学过“李平”老师课的同学的学号、姓名

思路：
    先查到“李平老师”老师教的所有课ID
    获取选过课的所有学生ID
    学生表中筛选学生id不在“获取选过课的所有学生ID”数据



SELECT
    student.sid,
    student.sname
FROM
    student
WHERE
    student.sid NOT IN (
        SELECT DISTINCT student_id FROM score WHERE score.course_id IN (
                SELECT cid FROM course 
            LEFT JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
    )
View Code

    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

思路：
    先查到既选择001又选择002课程的所有同学
    根据学生进行分组，如果学生数量等于2表示，两门均已选择

SELECT
    student_id,
    sname
FROM
    (SELECT * FROM score WHERE course_id = 1 OR course_id = 2) AS A
      LEFT JOIN student ON A.student_id = student.sid
         GROUP BY student_id
             HAVING count(student_id) = 2
View Code

    7、查询学过“李平”老师所教的所有课的同学的学号、姓名

思路：
    先查李平老师教的所有课
    再查选了这些课的学生id
    根据学生id在学生表中筛选数据
    


select student_id,sname from
    (select student_id,course_id from score where course_id in 
        (SELECT cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname='李平老师')) as A
LEFT JOIN student on A.student_id = student.sid
View Code

    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

思路：
    获取所有001课程的人（学号，成绩） - 临时表
    获取所有002课程的人（学号，成绩） - 临时表
    根据【学号】连接两个临时表：
        学号  001课程成绩   002课程成绩

SELECT * FROM
(select student_id,num as a  from score where course_id=1) as A
LEFT JOIN 
       (select student_id,num as b from score where course_id=2) as B
 on A.student_id = B.student_id where a < if(isnull(b),0,b);
View Code

    9、查询有课程成绩小于60分的同学的学号、姓名

SELECT
    sid,
    sname
FROM student WHERE sid IN 
  (
        SELECT DISTINCT student_id FROM score WHERE num < 60
    )
View Code

    10、查询没有学全所有课的同学的学号、姓名

思路：
        在分数表中根据学生进行分组，获取每一个学生选课数量
        如果数量 == 总课程数量，表示已经选择了所有课程


SELECT
    student_id,
    sname
FROM
    score
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
    student_id
HAVING
    count(course_id) = (SELECT count(1) FROM course)
View Code

    11、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

思路：
        获取 001 同学选择的所有课程
        获取课程在其中的所有人以及所有课程
        根据学生筛选，获取所有学生信息
        再与学生表连接，获取姓名



select student_id,sname, count(course_id)
     from score left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id
View Code

    12、查询至少学过学号为“001”同学所有课的其他同学学号和姓名

思路：
    和11类似
    先找到和001的学过的所有人
    然后个数 ＝ 001所有学科     ＝＝》 其他人可能选择的更多


select student_id,sname, count(course_id)
   from score left join student on score.student_id = student.sid
      where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id 
          having count(course_id)=(select count(course_id) from score where student_id = 1)
View Code

    13、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

思路：
    类似11、12
    个数相同
    002学过的也学过


select student_id,sname from score left join student on score.student_id = student.sid where student_id in 
     (select student_id from score  where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)) 
        and course_id in (select course_id from score where student_id = 1) group by student_id
            HAVING count(course_id) = (select count(1) from score where student_id = 1)
View Code

    14、删除学习“李平”老师课的score表记录

思路：
    查找李平老师教的课
    根据课程id删除记录

DELETE FROM score WHERE course_id in (
    SELECT cid from course LEFT JOIN teacher on  course.teacher_id=teacher.tid where teacher.tname='李平老师')
View Code

    15、向分数表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩

思路：
   insert 支持 
         inset into tb1(xx,xx) select x1,x2 from tb2;
     获取所有没上过002课的所有人，获取002的平均成绩数据然后进行插入


insert into score(student_id, course_id, num)
   select sid,2,(select avg(num) from score where course_id = 2)
    from student where sid not in 
       (select student_id from score where course_id = 2)
View Code

 

转载于:https://www.cnblogs.com/quanloveshui/p/11065522.html

alkcf22626
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
navicat及MySQL练习题参考答案
04-14 1434
MySQL练习题参考答案 navicat： 　　 注释：--空格 快捷键：Ctrl ？ 　　　　 在navicat写sql语句：选中一个数据库---点‘’查询‘’---‘’新建查询‘’----就可以写sql语句然后执行了 　　　　　　转储或运行sql文件：选中一个数据库右键选择转储（可选择储存结构和数据或仅储存结构）或运行sql文件 　...
Mysql基本语句练习
hypindulge的博客
07-23 357
Mysql基本语句练习
mysql部署练习_mysql 及练习题
weixin_39748773的博客
02-02 100
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；思路：获取所有有生物课程的人(学号，成绩) - 临时表获取所有有物理课程的人(学号，成绩) - 临时表根据【学号】连接两个临时表：学号 物理成绩 生物成绩然后再进行筛选select A.student_id,sw,ty from(select student_id,num as sw from score left join cour...
MySQL 作业题及答案
weixin_30258901的博客
02-15 315
MySQL 测试题 一、 表关系： 请创建如下表，并创建相关约束 创建表sql如下： /* Navicat MySQL Data Transfer Source Server : 192.168.118.14 Source Server Version : 50544 Source Host : 192.168.118.14...
SQL经典50查询语句案例_5(查询没学过“叶平”老师课的同学的学号、姓名)
souldoms的博客
04-26 8502
SQL经典50查询语句案例_5： 5、查询没学过“叶平”老师课的同学的学号、姓名： SELECT SId, StuName FROM student WHERE SId NOT in( SELECT s.SId FROM teacher t,sc s, course c WHERE t.Tname = '叶平' AND c.TId = t.TId AND c.CId = s.CI...
mysql练习（待补充）
weixin_30301449的博客
10-11 84
2、查询‘生物’课程比‘物理’课程成绩高的所有学生的学号 思路： 获取所有生物课程的人（学号，成绩）-临时表 获取所有物理课程的人（学号，成绩）-临时表 根据学号连接两个临时表： 学号 生物成绩 物理成绩 SELECT A.student_id,sw,wl from (SELECT student_id,num as sw from score LEFT JOIN cour...
mysql操作表语句练习
weixin_45163798的博客
03-10 330
1.表结构: 2.建表 # 1、创建表 # 创建班级表 create table class( cid int primary key auto_increment, caption varchar(32) not null ); # 创建学生表 create table student( sid int primary key auto_increment, gender char(1) n...
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
基于mysql的sql语句学习与练习
03-22
在IT领域，数据库管理和查询是核心技能之一，MySQL作为广泛使用的开源关系型数据库管理系统，其SQL（Structured Query Language）语句的学习与实践至关重要。本文将深入探讨基于MySQL的SQL语句，帮助你掌握数据操作...
MySQL练习代码， sql语句练习3
06-17
总的来说，“MySQL练习代码，sql语句练习3”将引导你深入理解SQL语言的各个方面，通过实践来提升你的数据库管理和查询技能。无论你是初学者还是经验丰富的开发者，这样的练习都将帮助你巩固理论知识，提高实际操作...
MySQL练习代码， sql语句练习1
06-17
本压缩包“Sql1”提供了SQL语句的练习，帮助用户深入理解和熟练掌握MySQL中的SQL用法。 首先，基础的SQL语句包括SELECT、INSERT、UPDATE和DELETE。SELECT用于从数据库中查询数据，是SQL中最常用的部分。你可以学习...
MySQL练习代码， sql语句练习2
06-17
本资料包含的是"SQL语句练习2"，旨在帮助用户深入理解和熟练掌握MySQL中的SQL查询语言。 SQL（Structured Query Language），结构化查询语言，是用于管理和处理关系数据库的标准语言。在“SQL语句练习2”中，我们...
sql习题及答案
weixin_30757793的博客
10-20 285
sql习题：http://www.cnblogs.com/wupeiqi/articles/5729934.html 习题答案参考：https://www.cnblogs.com/wupeiqi/articles/5748496.html （有些答案有错） -- SELECT count(*) from score WHERE num>60; -- ...
sql查询练习题
rainturbons的博客
11-13 251
mysql多表查询练习题
SQL入门学习（4）
qq_43242266的博客
07-30 923
目录 合并查询 存储查询结果到表中 数据表中数据的操纵 添加数据 修改数据 删除数据 合并查询 合并查询是使用UNION 操作符将来自不同查询的数据合并起来，形成一个具有综合信息的查询结果，UNION操作会自动将重复的数据行剔除。 参与合并查询的各个子查询使用的表结构应该相同，即各个子查询中的字段数目和对应的数据类型都必须相同。 [例]从SC数据表中查询出学号为“S1”同...
标准SQL语句（6） --- 数据的增删改
xjh1991xjh的专栏
05-15 1500
1. 添加一行新记录 添加数据使用的SQL命令 insert into
ArcGIS Engine编程初学至精通指南
最新发布
07-23
《ArcGIS Engine开发从入门到精通》是一套全面讲解GIS技术与ArcGIS Engine应用的教程。ArcGIS Engine是由Esri公司推出的强大地理信息系统开发平台，它允许开发者利用.NET、Java等编程语言创建桌面、Web及移动GIS应用程序。本教程旨在帮助初学者和有一定经验的GIS从业者快速掌握ArcGIS Engine的核心技术和实际应用。 1. **GIS基础概念**：我们需要理解什么是GIS（Geographic Information System，地理信息系统），它是如何整合地理位置数据和属性数据，以及如何通过地图进行数据的可视化和分析。 2. **ArcGIS Engine介绍**：ArcGIS Engine是Esri GIS技术栈的一部分，提供了丰富的GIS组件，包括地图显示、图层管理、地理处理工具、查询功能等，用于构建自定义的GIS应用。 3. **开发环境配置**：讲解如何设置开发环境，包括安装ArcGIS Engine SDK，集成开发环境（如Visual Studio）的配置，以及必要的开发工具和库的添加。 4. **基础控件与组件
springboot整合MinIO中间件，实现文件便捷管理
07-23
springboot整合MinIO中间件，实现文件便捷管理
C#语言实现的百度关键词点击代码，采用外部浏览器控制
07-23
标题中的“C#百度关键词点击源码(外部浏览器操作)”是指使用C#编程语言编写的一段代码，其目的是模拟用户行为，对百度搜索引擎中的特定关键词进行自动点击。这种技术在SEO（搜索引擎优化）领域可能会被用到，以提高网站在搜索结果中的排名。 在SEO中，关键词点击率是影响网站排名的一个重要因素。正常情况下，用户对某个搜索结果的点击次数多，搜索引擎会认为该结果更符合用户的搜索需求，从而提升其在搜索结果中的位置。然而，手动模拟大量点击既耗时又不实际，因此开发这样的源码可以自动化这一过程，但需要注意的是，如果被搜索引擎检测到异常，可能会被视为违反其使用政策，导致网站被降权甚至被屏蔽。 描述中提到的“浏览器外部操作”意味着这个C#程序不会直接嵌入到浏览器内部，而是作为一个独立的应用程序运行，通过API或者模拟键盘鼠标操作与浏览器进行交互。这种方式可能包括使用自动化测试框架如Selenium，或者利用Windows API来模拟用户输入和页面交互。这样的设计可以避免因修改浏览器行为而引起的安全问题，同时也能更好地模拟真实用户的行为。 标签“SEO源码”表明这段代码是针对搜索引擎优化目的编写的，可能
MySQL数据库SQL语句练习：创建与操纵关系表
"实验1 MYSQL数据库SQL语句练习实验" 实验1是针对初学者设计的，旨在帮助他们理解和掌握数据库管理系统（DBMS）的核心功能，特别是聚焦于MySQL数据库和SQL语言的使用。实验的目标包括： 1. 了解DBMS的基本功能和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值