基础sql整理

摘要:
当t1.score=98,count=0时,检索记录;当t1.score=97,count=1时,检索记录;当t1.score=86,count=2时,此记录将不会被取出。因此,检索到的数据是前两位数字。

参考https://www.cnblogs.com/yoyoketang/

student表:

基础sql整理第1张

grade表:

基础sql整理第2张

1.查询所有学生的数学成绩,显示学生姓名name,分数由高到低

SELECT a.name,b.score from sentiment.student a left join sentiment.grade b on a.id = b.id where b.kemu='数学' order by b.score DESC;
SELECT a.name,b.score from sentiment.student a,sentiment.grade b where a.id = b.id AND b.kemu='数学' order by b.score DESC;

基础sql整理第3张

2.统计每个学生的总成绩,显示字段:姓名,总成绩

SELECT a.name,sum(b.score) as SUM from sentiment.student a left join sentiment.grade b on a.id = b.id group bya.name;
SELECT a.name,sum(b.score) as sum from sentiment.student a,sentiment.grade b where a.id=b.id group by a.name;

基础sql整理第4张

3.统计每个学生的总成绩(由于学生可能有重复名字),显示字段:学生id,姓名,总成绩

SELECT a.name,a.id,sum(b.score) as sum from sentiment.student a,sentiment.grade b where a.id=b.id group by a.name,a.id;

基础sql整理第5张

4.列出各门课程成绩最好的学生,要求显示字段: 学号,姓名,科目,成绩

SELECT a.id,a.name,b.kemu,MAX(b.score) from sentiment.student a,sentiment.grade b where a.id = b.id group by b.kemu ORDER by b.score DESC;

SELECT a.name,b.id,b.kemu,b.score FROMsentiment.student a,sentiment.grade b,
(SELECT kemu,MAX(score) as scorem from sentiment.grade group bykemu) t 
where b.kemu=t.kemu and b.score=t.scorem and a.id=b.id

基础sql整理第6张

5.计算每个人的平均成绩,要求显示字段: 学号,姓名,平均成绩

SELECT a.id,a.name,avg(b.score) as score_avg from sentiment.student a,sentiment.grade b where a.id=b.id group by a.id;

基础sql整理第7张

6.列出各门课程的平均成绩,要求显示字段:课程,平均成绩

SELECT kemu,AVG(score) from sentiment.grade group by kemu;

基础sql整理第8张

7.列出数学成绩的排名,要求显示字段:学号,姓名,成绩,排名

SELECT a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC;

SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= '数学') c 
where a.id = c.id order by c.score DESC;

基础sql整理第9张

8.列出数学成绩前3名的学生

SELECT a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 3;

SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= '数学') c 
where a.id = c.id order by c.score DESC limit 3;

基础sql整理第10张

9.查询数学成绩第2和第3名的学生

SELECT a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 1,2;

SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= '数学') c 
where a.id = c.id order by c.score DESC limit 1,2;

基础sql整理第11张

10.查找每科成绩前2名,显示id,姓名,科目,分数

SELECT id,kemu,score from sentiment.grade ORDER by kemu,id DESC;
SELECT COUNT(*) FROM sentiment.grade WHERE kemu ='数学' and score>98;

SELECT t1.id,t1.kemu,t1.score FROMsentiment.grade t1
WHERE (SELECT COUNT(*) FROM sentiment.grade t2 WHERE t1.kemu=t2.kemu AND t2.score>t1.score)<2
ORDER BY t1.kemu,t1.score DESC;

基础sql整理第12张

说明:t2.score>t1.score,t1.score放入具体的数值。

t1.score=98时,count=0,该条记录取出;

t1.score=97时,count=1,该条记录取出;

t1.score=86时,count=2,该条记录不被取出;

...

所以取出的数据为前两位。

SELECT t1.id,t1.kemu,t1.score,a.name fromsentiment.grade t1,sentiment.student a
where t1.id=a.id
and (SELECT COUNT(*) from sentiment.grade t2 where t1.kemu=t2.kemu and t2.score>t1.score)<2
ORDER by t1.kemu DESC;

11.查询第3名到后面所有的学生数学成绩

SELECT a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 2,999;


SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= '数学') c 
where a.id = c.id order by c.score DESC limit 2,999;

基础sql整理第13张

12.统计英语课程少于80分的,显示学号id,姓名,科目,分数

SELECT id,kemu,score from sentiment.grade where kemu= '英语' and score < 80;
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= '英语' and score < 80) c 
where a.id =c.id;

SELECT a.id, a.name, b.kemu, b.score FROM sentiment.student a, sentiment.grade b WHERE a.id =b.id
AND b.kemu = '英语' AND b.score < 80;

基础sql整理第14张

13.统计每门课程不及格[0,60),一般[60,80],优秀(80,100]

SELECT count(*) from sentiment.grade where score < 60 group bykemu;
SELECT count(*) from sentiment.grade where score <= 80 and score >= 60;
SELECT count(*) from sentiment.grade where score > 80 group BYkemu;

SELECTb.kemu,
(SELECT count(*) from sentiment.grade where score < 60 and kemu=b.kemu) as不及格,
(SELECT count(*) from sentiment.grade where score <= 80 and score >= 60 and kemu=b.kemu) as一般,
(SELECT count(*) from sentiment.grade where score > 80 and kemu=b.kemu) as优秀
fromsentiment.grade b
GROUP by b.kemu

基础sql整理第15张

gradea表:

基础sql整理第16张

1.查询表中每门课都大于80分的学生姓名

-不考虑课程
SELECT name from gradea GROUP by name HAVING min(score)>80;
SELECT DISTINCT name from gradea where name not in (SELECT DISTINCT name FROM gradea WHERE score <=80);

基础sql整理第17张

-考虑课程为3
SELECT name from gradea GROUP by name HAVING min(score)>80 and count(kemu)>=3;

基础sql整理第18张

2.用sql查询出“张”姓学生中平均成绩大于75分的学生信息

SELECT name from sentiment.gradea where name LIKE '张%' GROUP by name having avg(score)>75;
select * from sentiment.gradea where name in  (select name from sentiment.gradea where name like '张%' group by name having avg(score) > 75);

基础sql整理第19张

免责声明:文章转载自《基础sql整理》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇大数据笔记(十七)——Pig的安装及环境配置、数据模型学习:基于注册表验证的逆向实现下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

Mysql日期转换函数、时间转换函数

Mysql日期转换函数、时间转换函数 一、MySQL 获得当前日期时间 函数 1,获得当前日期+时间(date + time)函数:now(): select now(); 结果:2008-08-08 22:20:46 2,获得当前日期+时间(date + time)函数:sysdate()sysdate() 日期时间函数跟 now() 类似,不同...

深入学习之mysql(三)单表操作

1、创建表的结构和数据 CREATE TABLE `t_student`( `id` INT PRIMARY KEY, `stuName` VARCHAR(10) NOT NULL, `age` INT NOT NULL, `sex` VARCHAR (4), `gradeName` VARCHAR(10) NOT NULL ); 插入数据: INSERT...

c#排序sql语句查询

排序存储的效果图: 根据id排序的sql存储过程: DECLARE @type varchar(5000) select @type=sortable_text from Sortable with(nolock) where sortable_type='NoteType' and [key]='00000000' selec...

SQL判断某列中是否包含中文字符、英文字符、纯数字

一、包含中文字符 select * from 表名 where 列名 like '%[吖-座]%' 二、包含英文字符 select * from 表名 where 列名 like '%[a-z]%'  三、包含纯数字 select * from 表名 where 列名 like '%[0-9]%'...

Socket Connect问题

一、非阻塞Connect对于Select时应注意的问题二、linux客户端socket非阻塞connect编程 一、非阻塞Connect对于Select时应注意的问题 对于面向连接的socket(SOCK_STREAM、SOCK_SEQPACKET),在读写数据之前必须建立连接。 建立连接的过程: 首先,服务器端socket必须在一个客户端知晓的地...

oracle hint

Hint概述基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。 此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从 而使语句高效的运行。例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更...