03-005 sql50题-mariadb

最新推荐文章于 2024-07-10 15:47:03 发布
最新推荐文章于 2024-07-10 15:47:03 发布
阅读量450 收藏 1
点赞数
分类专栏： database 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/csdn_immortal/article/details/102546088
版权

sql50题续

01.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

s_idc_id_01c_id_02
学生编号课程01成绩课程02成绩

select s_id from 表 where c_id_01 > c_id_02

 1. 
SELECT
	* 
FROM
	( SELECT * FROM Score WHERE c_id = '01' ) AS t1
	INNER JOIN ( SELECT * FROM Score WHERE c_id = '02' ) AS t2 ON t1.s_id = t2.s_id 
WHERE
	t1.s_score > t2.s_score;
----------------------------------------------------------------------
 2. 
SELECT
	* 
FROM
	Score t1,
	Score t2 
WHERE
	t1.c_id = '01' 
	AND t2.c_id = '02' 
	AND t1.s_id = t2.s_id 
	AND t1.s_score > t2.s_score;
-----------------------------------------------------------------------
 3. 
SELECT
	* 
FROM
	Score t1
	LEFT JOIN Score t2 ON t1.s_id = t2.s_id 
WHERE
	t1.c_id = '01' 
	AND t2.c_id = '02' 
	AND t1.s_score > t2.s_score;

在这里插入图片描述

02查询平均成绩大于60分的学生的学号和平均成绩 类似的题目（重点）

select s_id from Score GROUP BY s_id having avg(s_score) > 60;

在这里插入图片描述

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

  1. 方法一

在这里插入图片描述

  1. 方法二
    在这里插入图片描述
    在这里插入图片描述

4、查询姓“张”的老师的个数（不重要）

在这里插入图片描述
%匹配任意字符，_匹配单个字符

5、查询没学过“张三”老师课的学生的学号、姓名（重点）

注意：条件中不能用<>‘张三’
在这里插入图片描述

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

在这里插入图片描述

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

在这里插入图片描述

8、查询课程编号为“02”的总成绩（不重点）

select sum(s_score) from Score where c_id = '02';

在这里插入图片描述

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

select s1.s_id,s1.s_name from Student s1 inner join Score s2 
on s1.s_id = s2.s_id 
group by s1.s_id having max(s2.s_score < 60);

最大成绩小于60分的学生
学生小于60分的课程数 =学生所学课程数

10.查询没有学全所有课的学生的学号、姓名(重点)

select s1.s_id,s1.s_name from Student s1 inner join Score s2 
on s1.s_id = s2.s_id group by s1.s_id,s1.s_name having count(s2.c_id)
< (select count(distinct c_id) from Course);

在这里插入图片描述在这里插入图片描述在这里插入图片描述
在这里插入图片描述

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

select distinct s1.s_id,s1.s_name from Student s1 inner join Score s2 
on s1.s_id = s2.s_id where c_id in 
(
select distinct c_id from Score where s_id = '01'
) and s1.s_id <> '01';

在这里插入图片描述

12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

select distinct s1.s_id,s1.s_name from Student s1 inner join Score s2 
on s1.s_id = s2.s_id where c_id in 
(
select distinct c_id from Score where s_id = '01'
) and s1.s_id <> '01' group by s1.s_id,s1.s_name
having count(distinct s2.c_id) = (select count(distinct c_id) from Score where s_id = '01');

上述sql考虑不完整，如01选了01,02,03课程，而有人选了02，03,04也满足查询条件。如下图是考虑较为完整的结果
在这里插入图片描述

13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

select * from Student where s_id not in (
select s_id from Score s1 
inner join Course  c1 on s1.c_id = c1.c_id
inner join Teacher t1 on c1.t_id = t1.t_id 
where t1.t_name = '张三';
)

在这里插入图片描述

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

select s_id,avg(s_score) from Score where s_score < 60
group by s_id having count(c_id)>= 2;
-----------------------------------------------------------
select s1.s_id,s1.s_name,avg(s2.s_score) from Student s1 
inner join Score s2 on s1.s_id = s2.s_id 
where s1.s_id in (
select s_id from Score where s_score < 60 group by s_id having count(c_id) >= 2
) group by s1.s_id,s1.s_name;

在这里插入图片描述

16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）在这里插入图片描述

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

group by 分组后，筛选结果必须加修饰符，否则报错
对于分组后的结果，还需要按照题目要求列出所有课程成绩，因此需要用到case when else end
在这里插入图片描述

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
在这里插入图片描述

19、按各科成绩进行排序，并显示排名(重点row_number ,22,25类似)

窗口函数
在这里插入图片描述

20、查询学生的总成绩并进行排名（不重点）

在这里插入图片描述

21 、查询不同老师所教不同课程平均分从高到低显示(不重点)在这里插入图片描述

22、查询所有课程的成绩第2名到第3名(考虑成绩并列)的学生信息及该课程成绩（dense_rank重要 19,25类似）

19.22.25三题，问题不是台严谨，此处有一门课的3个同学成绩相同
具体参见：
窗口函数

select s1.*,s2.s_score,c_id from (
select *,dense_rank() over(partition by c_id order by s_score desc) rank from Score
) s2 inner join Student s1  on s1.s_id = s2.s_id 
where rank >=2 and rank <= 3 order by c_id,rank;

在这里插入图片描述

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

select s1.c_id,
c1.c_name,
sum(case when s1.s_score>=85 and s1.s_score <=100 then 1 else 0 end) '优秀人数',
sum(case when s1.s_score>=70 and s1.s_score <85 then 1 else 0 end) '优良人数',
sum(case when s1.s_score>=60 and s1.s_score <70 then 1 else 0 end) '人数',
sum(case when s1.s_score <60 then 1 else 0 end) '不及格人数'
from Score s1 inner join Course c1 on s1.c_id = c1.c_id group by s1.c_id,c1.c_name;

在这里插入图片描述

24、查询学生平均成绩及其名次（同19题，重点）在这里插入图片描述在这里插入图片描述

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与19,22题类似）

窗口函数
在这里插入图片描述在这里插入图片描述使用group by 合并同类型课程
在这里插入图片描述

select 课程号,课程名称,
max(case rank when 1 then 课程分数 else null end) 'FIRST',
max(case rank when 2 then 课程分数 else null end) 'secord',
max(case rank when 3 then 课程分数 else null end) 'third'
from (
SELECT
	s1.s_id '学号',
	s2.s_name '姓名',
	s1.c_id '课程号',
	c1.c_name '课程名称',
	s1.s_score '课程分数',
	row_number() over( PARTITION BY s1.c_id ORDER BY s_score DESC ) 'rank' 
FROM
	Score s1
	INNER JOIN Student s2 ON s1.s_id = s2.s_id
	INNER JOIN Course c1 ON s1.c_id = c1.c_id
	) a where a.rank in (1,2,3) group by 课程号,课程名称;

26、查询每门课程被选修的学生数(不重点)

在这里插入图片描述

27、 查询出只有两门课程的全部学生的学号和姓名(不重点)

在这里插入图片描述

28、查询男生、女生人数(不重点)

在这里插入图片描述

29 查询名字中含有"风"字的学生信息（不重点）

在这里插入图片描述
通配符，%号匹配任意个字符，_匹配单个字符

30题忽略掉

31、查询1990年出生的学生名单（重点year）

select * from Student where s_birth like '1990%'; -- 字符串使用通配符匹配
select * from Student where year(s_birth)=1990;

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

在这里插入图片描述

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

在这里插入图片描述

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）在这里插入图片描述

35、查询所有学生的课程及分数情况（重点）

备注:
1.要选出需要的字段 用case when 当c1.c_name=‘数学’ then 可以得到对应的 s_core
2.要展现出每个同学的各科成绩为一行，所以需要用到group by分组函数
3.select 查询结果列要与GROUP UP 分组的列一致，所以case when 加修饰max或者min等

select s1.s_id,s1.s_name,
min(case c1.c_name when '数学' then s_score else null end) '数学',
min(case c1.c_name when '语文' then s_score else null end) '语文',
min(case c1.c_name when '英语' then s_score else null end) '英语' 
from Student s1 
inner join  Score s2 on s1.s_id = s2.s_id 
inner join Course c1 on s2.c_id = c1.c_id
group by s1.s_id,s_name;

在这里插入图片描述

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

注：不用group by

select s2.s_name,c_name,s_score from Score s1 
inner join Student s2 on s1.s_id = s2.s_id
inner join Course c1 on s1.c_id = c1.c_id 
where s1.s_score > 70 order by s_name,s_score;

在这里插入图片描述

37、查询不及格的课程并按课程号从大到小排列(不重点)

在这里插入图片描述

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）在这里插入图片描述

39、求每门课程的学生人数（不重要）

在这里插入图片描述

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

SQL SERVER 中用top

MYSQL 用 limit

select 筛选的是order by 后的数

select s2.s_id,s_name,s_score from Teacher t1 
inner join Course c1 on t1.t_id = c1.t_id
inner join Score s1 on c1.c_id = s1.c_id
inner join Student s2 on s1.s_id = s2.s_id
order by s_score desc limit 1;

在这里插入图片描述

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

select * from Score where s_id in (

select s_id from (
			select s1.s_id,s1.s_score from Score s1 
			inner join 
					(select s_id from Score group by s_id having count(distinct c_id ) > 1) s2 
			on s1.s_id = s2.s_id
			group by s1.s_id,s1.s_score
) s3 
group by s_id having count(s_id) = 1

)

在这里插入图片描述在这里插入图片描述

42、查询每门成绩最好的前两名（同22和25题）

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）在这里插入图片描述

44、检索至少选修两门课程的学生学号（不重要）

在这里插入图片描述

45、 查询选修了全部课程的学生信息

select s_id,count(c_id) from Score group by s_id 
having count(c_id) = (select count(c_id) from Course)

在这里插入图片描述

46、查询两门以上不及格课程的同学的学号及其平均成绩

select s_id,avg(s_score) from Score where s_id in (
select s_id from Score where s_score < 60 
group by s_id having count(distinct c_id) > 2
);

插入图片描述](https://img-blog.csdnimg.cn/20191020005818591.png)

47、查询各学生的年龄（精确到月份）

在这里插入图片描述

48、查询本月过生日的学生（无法使用week、date(now()）

1 查询下月过生日的学生（无法使用week、date(now()）

2查询本周过生日的学生（无法使用week、date(now()）

3查询下周过生日的学生（无法使用week、date(now()）

csdn_immortal
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
BUUCTF：[SWPU2019]Web1 sql注入 MariaDB注入--无列名注入-group_concat ---- /**/---group by ----- 3.9修改
Zero_Adam的博客
02-24 3682
目录：一、自己做：二、不足&&学到的：三、学习WP1.测试看看过滤了那些关键字，（这里要多看看，学学）2.使用无列名注入：这里稍停一下：3.下面学习无列名注入： 无列名注入详解：无列名注入详解 参考自：本题目的详解 写博客只是为了输出学习而已， 无他 一、自己做： 发现了 那个广告的地方可能有漏洞 二、不足&&学到的： 尝试了ssti。却忘记了尝试sql注入。。。 过滤了order的时候，可以用group by 来测试有多少行 单引号闭合，正常闭合会出错的话，可以,
AWS-SAA考试认证-学习资料中文
08-02
2. Amazon Relational Database Service (RDS)：RDS 提供了托管的关系数据库服务，支持MySQL、Oracle、Microsoft SQL Server、PostgreSQL和MariaDB等多种引擎。用户可以控制是否及何时升级到新的支持版本，并利用...
03-005 sql50题工具准备-mariadb
csdn_immortal的博客
09-27 139
赞一个位置
CTFSHOW -SQL 注入
m0_64180167的博客
11-13 685
重新来做一遍 争取不看wp。
MariaDB+SpringBoot 报错 Caused by: java.sql.SQLException: GSS-API authentication exception
菜鸟江涛的博客
08-11 677
最近在搭建springboot + mybatis + mariadb 项目的时候遇到了如题的问题，搜索了一圈没找到合适的解决方法，因此记录下我的解决方案，希望对后来者有帮助。
SQL必知必会 - 课后题练习记录
Venassa佳的博客
04-03 1735
第2课 检索数据 第3课 排序检索数据 第4课 过滤数据 第5课 高级数据过滤 第6课用通配符进行过滤 第7课创建计算字段 第8课使用函数处理数据 第 9 课 汇总数据 第 10 课 分组数据 第11 课 使用子查询 12 联结多个表
TiDB-PCTA课程课后习题
coding0820的博客
06-24 3306
TiDB-PCTA认证，学习
Jarvis-OJ WEB 多题writeup
烟敛寒林的博客
04-19 4079
PORT51（curl） LOCALHOST（伪造ip） Login（SQL注入） 神盾局的秘密（base64编码+反序列化） IN A Mess（代码审计+过滤空格SQL注入） admin（robots.txt+cookie欺骗） [61dctf]babyphp（Git泄露+代码注入） Easy Gallery（文件上传、%00截断） Simple Injection（过滤空格S...
【Mariadb数据库SQL操作】
小小Python的博客
06-19 608
使用SQL命令管理数据库： 1.数据库管理命令 查看数据库：MariaDB [(none)]> show databases; 查看当前库：MariaDB [(none)]> select database(); 查看当前用户：MariaDB [(none)]> select user(); 创建数据库：create database if not exists 数据库名; 切换数据库：use 数据库名； 删除数据库：慎用！！！ drop database if exists 数据库名;
面试题009-Java-MyBatis
最新发布
zouhuu的博客
07-10 981
1. 什么是MyBatis？它与Hibernate有什么区别？ 2. 说一下MyBatis的执行流程？ 3. MyBatis是否支持延迟加载？ 4. MyBatis中一级缓存和二级缓存的区别？ 5. MyBatis中的动态SQL是什么？ 6. 如何在MyBatis中实现分页？ 7. MyBatis如何实现大规模数据插入MySQL数据库中？ 8. MyBatis-Plus了解吗？
sql语句的练习题
03-16
### SQL语句练习题知识点解析 #### 1. 查询年龄小于20岁的学生姓名与年龄 ```sql SELECT sname, sage FROM s WHERE sage ; ``` **知识点：** - **基本查询语法:** 使用`SELECT`来指定要查询的列，`FROM`指定表名。 ...
MySQL的经典面试题
03-25
1、MySQL的复制原理以及流程 (1)、复制基本原理流程 1. 主：binlog线程——记录下所有...4. 如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制， SQL 线程只做 coordinator，
MySQL数据库高级工程师-面试题-MySQL DBA面试题01-风哥整理（面试必过）
01-02
4. 如果是多线程复制，无论是5.6 库级别的假多线程还是MariaDB 或者5.7 的真正的多线程复制， SQL 线 程只做coordinator，只负责把relay log 中的binlog 读出来然后交给worker 线程， woker 线程负 责具体binlog ...
Python-Flask
04-25
Interview- 偶遇面试类小习题Mariadb - MYSQL 练习 - all.sql 为第一次数据备份 - oracleData.sql 本地安装熟练使用操作的表数据,在 MYSQL 新建用于练习 - school.sql 新建用户特权操作数据 - videoInclude.sql 视频...
03-012 oracle中xmlagg和listagg
csdn_immortal的博客
12-02 4267
oracle列转换行有如下方法 一、wm_concat 1、语法 wm_concat() 实例如下 select wm_concat(distinct vendor) from ams_asset_info 二、XMLAGG函数 效果和wm_concat一样，但是性能不一样，XMLAGG排序一下，性能快一下 1、语法 *xmlagg(xmlparse(content 合并字段||’,’ well...
03-014 expdp导出库时排除表
csdn_immortal的博客
12-04 2811
说明，从12c上导出数据库，排除指定表 排出一个表 EXCLUDE=TABLE:“IN(‘表名’)” 即exclude=table:“in(‘表名’)”,其中"和’和()需要转义 expdp 用户名/密码@实例名 directory=导出路径 dumpfile=导出数据名.dmp logfile=日志文件.log EXCLUDE=TABLE:\"IN\(\'表名\'\)\" 排除多个指定表...
01-01 菜鸟教程数据库初识
csdn_immortal的博客
12-21 1819
菜鸟教程完整版 linux上安装mariadb的版本如下 /usr/share/doc/MariaDB-server-10.2.19 导入表mysql < websites.sql MariaDB [my_db]> show databases; +--------------------+ | Database | +---------------...
01-02菜鸟教程第二部分
csdn_immortal的博客
12-21 1683
MariaDB [my_db]> select * from websites; +----+---------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+------------...
$ yum install mariadb-embedded mariadb-libs mariadb-bench mariadb mariadb-sever
06-09
其中，mariadb-embedded 是嵌入式数据库引擎，mariadb-libs 是数据库的共享库文件，mariadb-bench 是性能测试工具，mariadb 是 MariaDB 的核心软件包，mariadb-server 是 MariaDB 数据库服务器。请注意，为了使用这...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值