SQL实战

最新推荐文章于 2022-11-10 19:16:10 发布
最新推荐文章于 2022-11-10 19:16:10 发布
阅读量114 收藏
点赞数
文章标签： python 人工智能
原文链接：https://juejin.im/post/5b2855bf6fb9a00e6678a42e
版权

一、表关系

二、操作表

1、自行创建测试数据

表结构和数据

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;
复制代码

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

思路：
   获取所有有生物课程的人（学号，成绩） - 临时表
   获取所有有物理课程的人（学号，成绩） - 临时表
   根据【学号】连接两个临时表：
       学号  物理成绩   生物成绩
   然后再进行筛选
select A.student_id,sw,ty from
(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);
复制代码

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

思路：
根据学生分组，使用avg获取平均值，通过having对avg进行筛选
select student_id,avg(num) from score group by student_id having avg(num) > 60
复制代码

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

select score.student_id,sum(score.num),count(score.student_id),student.sname
from
score left join student on score.student_id = student.sid  
group by score.student_id
复制代码

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

select count(tid) from teacher where tname like '李%'
select count(1) from (select tid from teacher where tname like '李%') as B
复制代码

6、查询没学过“叶平”老师课的同学的学号、姓名；

思路：
       先查到“李平老师”老师教的所有课ID
       获取选过课的所有学生ID
       学生表中筛选
select * from student where 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 tname = '李平老师'
)
)
复制代码

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

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

select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B  
left join student on B.student_id = student.sid group by 
student_id HAVING count(student_id) > 1
复制代码

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

同第7题，只不过将001和002变成 in (叶平老师的所有课)

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

同第2题

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

select sid,sname from student where sid in (
select distinct student_id from score where num < 60
)
复制代码

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

思路：
       在分数表中根据学生进行分组，获取每一个学生选课数量
       如果数量 == 总课程数量，表示已经选择了所有课程
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)
复制代码

12、查询至少有一门课与学号为“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
复制代码

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

先找到和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)
复制代码

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

个数相同
       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)
复制代码

15、删除学习“叶平”老师课的score表记录；

delete from score where course_id in (
select cid from course left join teacher on 
course.teacher_id = teacher.tid where teacher.name = '叶平'
)
复制代码

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“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
)
复制代码

17、按平均成绩从低到高

显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

select sc.student_id,
(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
(select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc
复制代码

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id, max(num) as max_num, min(num) as min_num 
from score group by course_id;
复制代码

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

思路：case when .. then
select course_id, avg(num) as avgnum,sum(case when score.num > 60 
then 1 else 0 END)/count(1)*100 as percent from score group by 
course_id order by avgnum asc,percent desc;
复制代码

20、课程平均分从高到低显示（现实任课老师）；

select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id
复制代码

识别图中二维码,领取python全套视频资料
weixin_34311757
关注
SQL排序函数详解+案例实战
前景
08-02 1282
SQL排序函数详解+案例实战
Oracle PL/SQL实战（待续）
07-27
在这个"Oracle PL/SQL实战（待续）"的主题中，我们将深入探讨这个语言的核心概念和实战技巧。 在Oracle数据库中，PL/SQL提供了丰富的控制结构，如循环、条件判断，以及异常处理机制，使得开发者能够编写复杂的业务...
【sql：练习题】按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩...
weixin_30443731的博客
08-12 8427
题目：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 分析：第一步应该是拿到平均分 拿到平均分后 再怎么拿学生 课程 和成绩呢？ left join 一下吧 sql: 如下： SELECT student_score.*, a FROM student_score LEFT JOIN ( SELECT studentid, AVG(score)AS a FROM...
Sql-测试5，6题解
qq_62649563的博客
11-10 832
目录1.查询没有选修java学生的姓名2.查询不同老师所教不同课程的平均分从高到低显示：3.查询出只选修一门课程的全部学生的学号和姓名：4.按各科平均成绩从低到高和及格率的百分数从高到低顺序： 相关的代码如下： 思路：首先，我们知道学生姓名是在students表中的，那么全部的学号都在students表中，那么我们只需要选出选修了java课程的同学的学号，在这里用的是一个子查询语句。相关代码如上。二、 思路： 三、查询出只选修一门课程的全部学生的学号和姓名： 思路： 四、按各科平均成绩从低到高和及格率的
DQL之多表查询—进阶练习
玩转Java
08-24 532
DQL之多表查询—进阶练习： 需要创建5张表，如图所示： 1，创建表，导入数据： 创建class表，导入数据 #创建表及插入记录 CREATE TABLE class ( cid int(11) NOT NULL AUTO_INCREMENT, caption varchar(32) NOT NULL, PRIMARY KEY (cid) ); #插入班级数据 INSERT INTO class VALUES (1, '三年二班'), (2, '三年三班'), (3, '一年二班'), (4, '二年九
sql练习
frank的博客
11-03 1438
一、创建数据 1、分析表关系 2、创建表 1.创建年级表 create table class_grade( gid int not null auto_increment primary key, gname char(20) not null )engine=INNODB DEFAULT CHARSET=utf8; 2.创建老师表 create table teacher( tid in...
Mysql数据库SQL实战
02-05
数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战...
数据库SQL实战牛客网数据库SQL实战题目汇总源代码
03-07
数据库SQL实战牛客网数据库SQL实战题目汇总源代码。编号 分类 标题 提交 1 过滤 查找最晚入职员工的所有信息 :monkey_face: 2 过滤 查找入职员工时间排名倒数第三的员工所有信息 :monkey_face: 3 连接 查找当前薪水...
数据库SQL实战案例.zip
最新发布
05-21
"数据库SQL实战案例.zip"这个压缩包文件，从其标题和描述来看，显然包含了关于数据库操作和SQL实际应用的练习题目，可能是为了帮助用户提升数据库查询和管理的能力。在数据库系统中，SQL是用于与关系型数据库进行...
数据库SQL实战.rar
05-09
数据库SQL实战是一个重要的主题，它是IT领域中不可或缺的一部分，尤其对于数据存储、处理和分析的专业人士来说。SQL（Structured Query Language）是用于管理和操作关系型数据库的语言，它为数据的检索、更新、创建...
多表查询 数据库（案例）
warmsun
03-21 552
首先我们创建测试的表 班级表 ：四个班级 教师表：五个老师 课程表：四个课程 学生表：学生的信息 分数表： 学生的成绩 CREATE DATABASE db1 CHAR SET utf8; USE db1; #创建表及插入记录 CREATE TABLE class ( cid INT(11) NOT NULL AUTO_INCREMENT, caption VARCHAR(32) NOT NULL, PRIMARY KEY (cid) ) ENGINE=INNODB CHARSET=utf8;
SQL语句-3
qq_840675460的博客
05-31 447
三，DQL(数据查询)语句 3.1 查询语法 SELECT <列名|表达式|函数|常量> FROM <表名> [where <查询条件表达式>] [order by <排序的列名>[ASC或DESC]]; [LIMIT [位置偏移量,]行数]; **注意：语法中 <>中的内容是必须的，[]中的内...
SQL入门简单语句
代码改变人生
11-11 1072
已知建立的数据库名称为EDUC，包含三个表SC(SID,CID,Grade)，Student(SID,Sname,Sex,Birthday,Speciality)，Course（CID,Cname,Period,Credit） 1.查询表Student的所有元素 use EDUC select * from Student 2.查询表Student中所有男同学的信息 use E
分数排名 leecode 学习笔记
keny-大成的博客
03-15 571
分数排序
SQL干货丨关于分组和聚合函数，如何实现查询排名？！
威哥爱编程，CSDN 博客专家、全栈领域新星创作者、华为 HDE，愿交天下 IT 技术爱好者
08-03 856
我们在学习数据库查询时，经常会遇到关于分组和聚合函数的查询，比如查询每门课程的最高分，每位同学的平均分，其实这些都是比较一般的问题。但如果遇到查询每门课程成绩的前几名问题，就会变的很棘手，今天老谢给你唠唠这方面的问题。其实遇到这种问题，并不单纯是在考察我们的SQL水平，同时也是在考察我们的思考能力，我们得学会思考如何进行查询，如何进行分组，如何进行条件过滤。然后我们可以根据学生和科目再进行分组，查询出比这个学生该门科目高的学生成绩条数是多少，如果条数小于3，这样就得到了前三名。...
【MySQL】178. 分数排名
weixin_41724761的博客
07-08 589
178. 分数排名 编写一个 SQL 查询来实现分数排名。 如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。 ±—±------+ | Id | Score | ±—±------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | ±—±------+ 例如，根据上述给定的 Scores
Python之路day11-数据库多表查询作业
weixin_30883311的博客
09-27 413
Eva_J链接: 上课链接:https://www.cnblogs.com/Eva-J/articles/10544358.html 作业链接: 1. 数据库小考:https://www.cnblogs.com/Eva-J/articles/11074845.html 2. 多表查询:https://www.cnblogs.com/Eva-J/articles/968...
按课程总成绩从高到低排序输出各个学生的信息
qq_43801143的博客
02-22 9851
按课程总成绩从高到低排序输出各个学生的信息（20分） 题目内容： 设计一个结构体类型，包含姓名、学号、语文成绩、数学成绩、英语成绩。输入n个学生的信息，输出按三门课程总成绩从高到低排序的各个学生的姓名、学号和各科成绩。 输入格式: 首先第1行输入一个整数n(1<=n<=20)，表示学生人数，然后输入n行，每行包含一个学生的信息：姓名（不超过20个字符）、学号（不超过12个字符）...
SQL50之按各科成绩进行排序，并显示排名
qq_40745994的博客
08-19 4558
让我第一次感觉到sql的强大sql的美丽。 https://blog.csdn.net/liyang_nash/article/details/99641571
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值