SQL常用语句

最新推荐文章于 2024-07-08 11:25:23 发布
最新推荐文章于 2024-07-08 11:25:23 发布
阅读量792 收藏 10
点赞数
分类专栏： 数据库 文章标签： sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/leiyu6666/article/details/108938777
版权

SQL语句学习

文章目录

前言

之前只会写一些简单的增删查改sql语句，今天深入的学习下sql语句。

新建四个数据库。

student表：
在这里插入图片描述

score表：stuId和courseId为联合主键。
score表
course表：
course表
teacher表：
在这里插入图片描述

四张表对应的关系：
在这里插入图片描述

一、模糊查询

查找姓雨的学生

select * from student where name like '雨%';

查找名字最后一个字是雨的学生

select * from student where name like '%雨';

查找名字中带雨的学生

select * from student where name like '%雨%';

统计姓雨的学生个数

select COUNT(name)
from student 
where name like '雨%';

二、汇总分析

查寻课程编号为0001的总成绩

select SUM(score) 
from score 
where courseId ='0001';

查询选了0001的课程总人数

select COUNT(DISTINCT stuId) 
from score 
where courseId ='0001';

二、分组查询

查询各科成绩最高和最低的分。分组依据（课程）

SELECT courseId,max(score) as 最高分,min(score) as 最低分
from score 
GROUP BY courseId;

查询每门课程被选修的学生数

SELECT courseId,COUNT(stuId)
from score 
GROUP BY courseId

查询男生、女生人数。分组依据（性别）

SELECT sex,COUNT(stuId)
from student 
GROUP BY sex

查询平均成绩大于60分学生的学号和平均成绩。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足，HAVING 子句可以让我们筛选分组后的各组数据

/* 
题目翻译成大白话：
平均成绩：展开来说就是计算每个学生的平均成绩
这里涉及到“每个”就是要分组了
平均成绩大于60分，就是对分组结果指定条件

分析思路
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩在成绩表中，所以查找的是成绩表score]
where 查询条件 [没有]
group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
having 对分组结果指定条件 [平均成绩大于60分]
*/
SELECT stuId,AVG(score)
from score 
GROUP BY stuId
HAVING  AVG(score)>60

查询至少选修两门课程的学生学号

/* 
翻译成大白话：
第1步，需要先计算出每个学生选修的课程数据，需要按学号分组
第2步，至少选修两门课程：也就是每个学生选修课程数目>=2，对分组结果指定条件

分析思路
select 查询结果 [学号,每个学生选修课程数目：汇总函数count]
from 从哪张表中查找数据 [课程的学生学号：课程表score]
where 查询条件 [至少选修两门课程：需要先计算出每个学生选修了多少门课，需要用分组，所以这里没有where子句]
group by 分组 [每个学生选修课程数目：按课程号分组，然后用汇总函数count计算出选修了多少门课]
having 对分组结果指定条件 [至少选修两门课程：每个学生选修课程数目>=2]
*/
SELECT stuId, COUNT(courseId) as 选修课程数目
from score 
GROUP BY stuId
HAVING  COUNT(courseId)>=2

查询同名同姓学生名单并统计同名人数。COUNT(*) 函数返回在给定的选择中被选的行数。

SELECT name, COUNT(*) as 同名同姓人数
from student 
GROUP BY name
HAVING COUNT(*)>=2

查询不及格的课程并按课程号从大到小排列

SELECT courseId
from score 
where score<60
ORDER BY courseId DESC

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT courseId,avg(score) as 平均成绩
from score 
GROUP BY courseId
ORDER BY avg(score) ASC,courseId DESC;

检索课程编号为“0003”且分数大于60的学生学号，结果按按分数降序排列

SELECT stuId,score
from score 
where courseId='0003' and score>60
ORDER BY score DESC;

统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT courseId,COUNT(stuId)
from score 
GROUP BY courseId
HAVING COUNT(stuId)>2
ORDER BY COUNT(stuId) DESC,courseId ASC

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

SELECT stuId,AVG(score) as 平均成绩
from score 
WHERE score<60 
GROUP BY stuId
HAVING COUNT(courseId)>2

查询学生的总成绩并进行排名

SELECT stuId,sum(score)
from score 
GROUP BY stuId
order by sum(score);

查询平均成绩大于60分的学生的学号和平均成绩

SELECT stuId,avg(score)
from score 
GROUP BY stuId
HAVING avg(score)>60

三、复杂查询

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

SELECT name,stuId FROM student 
where stuId in(
SELECT stuId 
FROM score
where score<60)

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

SELECT name,stuId FROM student 
where stuId in(
SELECT stuId 
FROM score
GROUP BY stuId
HAVING COUNT(courseId)< (select count(courseId) from course)
)

【日期类型】查询本月过生日的学生

select * 
from student 
where month (birthday ) = month(now());​

【日期类型】查询所有学生年龄

select stuId ,timestampdiff(year ,birthday ,now())
from student ;

【分组取每组最小值】按课程号分组取成绩最小值所在行的数据

select * from score as a 
where score = (
select min(score) 
from score as b 
where b.courseId = a.courseId);

【每组最大的N条记录】查询各科成绩前两名的记录

(SELECT * from score WHERE courseId='0001' ORDER BY score DESC LIMIT 2)
UNION all
(SELECT * from score WHERE courseId='0002' ORDER BY score DESC LIMIT 2)
UNION all
(SELECT * from score WHERE courseId='0003' ORDER BY score DESC LIMIT 2)

四、多表查询

查询所有学生的学号、姓名、选课数、总成绩。(left join :0004无课程不显示)

select b.stuId,b.name,COUNT(courseId) as 选课数,SUM(score) as 总成绩 
from score a LEFT JOIN student b
on a.stuId=b.stuId
GROUP BY stuId

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select b.stuId,b.name,avg(score) as 平均成绩 
from score a LEFT JOIN student b
on a.stuId=b.stuId 
GROUP BY stuId
HAVING avg(score)>85

查询学生的选课情况：学号，姓名，课程号，课程名称

select b.stuId,b.name,a.courseId,c.course
from score a LEFT JOIN student b on a.stuId=b.stuId
JOIN course c on a.courseId=c.courseId

查询出每门课程的及格人数和不及格人数

select stuId,
sum(CASE 
		WHEN score>=60 THEN	1
		ELSE	0
	END
)as 及格人数,
sum(CASE 
		WHEN score<60 THEN 1
		ELSE 0
	END
)as 不及格人数
FROM score
GROUP BY stuId

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.courseId,b.course,
sum(CASE 
		WHEN score>=85 THEN	1
		ELSE	0
	END
)as '[100-85]',
sum(CASE 
		WHEN score BETWEEN 70 and 85 THEN 1
		ELSE 0
	END
)as '[85-70]',
sum(CASE 
		WHEN score<70 and score>60 THEN 1
		ELSE 0
	END
)as '[70-60]',
sum(CASE 
		WHEN score<60 THEN 1
		ELSE 0
	END
)as '[<60]'
FROM score a JOIN course b on a.courseId=b.courseId
GROUP BY a.courseId

查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

SELECT a.stuId ,b.name
FROM score a JOIN student b on a.stuId=b.stuId
WHERE a.courseId='0003' and a.score>80

【多表连接】检索"0002"课程分数小于60，按分数降序排列的学生信息

SELECT b.*,a.score
FROM student b JOIN score a  on a.stuId=b.stuId
WHERE a.courseId='0002' and a.score<60
ORDER BY a.score  DESC

【多表连接】查询不同老师所教不同课程平均分从高到低显示

SELECT c.name,c.teacherId,AVG(a.score) as 平均成绩 
FROM course b 
	 JOIN score a  on a.courseId=b.courseId
     left JOIN teacher c on b.teacherId=c.teacherId
GROUP BY teacherId
ORDER BY AVG(a.score) DESC

查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT c.name,a.score 
FROM course b 
		 JOIN score a  on a.courseId=b.courseId
     JOIN student c on a.stuId=c.stuId
WHERE b.course='数学' and a.score<60

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


SELECT c.name,c.stuId,avg(a.score) 
FROM student c 
	 JOIN score a on a.stuId=c.stuId
where a.score<60
GROUP BY a.stuId
having COUNT(a.stuId)>=2

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT c.stuId,a.courseId,a.score
FROM score c 
	 JOIN score a on a.stuId=c.stuId
where a.score=c.score and a.courseId!=c.courseId

查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

SELECT c.stuId
FROM score c 
	 JOIN score a on a.stuId=c.stuId
where a.score>c.score and a.courseId='0001' and c.courseId='0002'

查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

SELECT b.stuId,b.`name`
FROM 
(SELECT stuId,courseId from score where courseId='0001' )as c
JOIN 
(SELECT stuId,courseId from score where courseId='0002' )as a 		
on a.stuId=c.stuId
join student b on a.stuId = b.stuId

查询学过“马耘”老师所教的所有课的同学的学号、姓名

SELECT a.stuId,a.`name`
FROM student a 
	JOIN score b on a.stuId =b.stuId
	join course c on c.courseId =b.courseId
	join teacher d on d.teacherId = c.teacherId
WHERE d.`name`='马耘'

查询没学过"马化腾"老师讲授的任一门课程的学生姓名（与上题类似，"没学过"用not in来实现)

SELECT `name` FROM student 
where stuId not in 
	(SELECT a.stuId
	FROM student a 
		JOIN score b on a.stuId =b.stuId
		join course c on c.courseId =b.courseId
		join teacher d on d.teacherId = c.teacherId
	WHERE d.`name`='马化腾')

查询选修“马耘”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）

SELECT a.`name`,b.score
FROM student a 
JOIN score b on a.stuId =b.stuId
join course c on c.courseId =b.courseId
join teacher d on d.teacherId = c.teacherId
WHERE d.`name`='马耘'
ORDER BY score DESC LIMIT 1

查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

SELECT `name`,stuId FROM student 
where stuId in(
	SELECT DISTINCT(stuId) FROM score where courseId in
		(SELECT courseId FROM score where stuId ='0001'))
and stuId!='0001'

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT a.stuId,AVG(score) as 平均成绩,
max(CASE WHEN b.course='语文' THEN	a.score	ELSE	NULL END
)as '语文',
max(CASE WHEN b.course='数学' THEN  a.score	ELSE	NULL END
)as '数学',
max(CASE WHEN b.course='英语' THEN  a.score	ELSE	NULL END
)as '英语'
from score a
	JOIN course b on a.courseId=b.courseId
GROUP BY a.stuId 
ORDER BY AVG(score) DESC

五、窗口函数

查询学生平均成绩及其名次

SELECT stuId,AVG(score) as 平均成绩,ROW_NUMBER() over(ORDER BY AVG(score) desc)as 排名
from score
GROUP BY stuId

按各科成绩进行排序，并显示排名

SELECT courseId,stuId,score,row_number () over(partition by courseId order by score DESC)as 单科排名
from score

查询每门功成绩最好的前两名学生姓名

SELECT a.courseId,b.`name`,a.score,a.ranking FROM(SELECT courseId, stuId,score,row_number () over(partition by courseId order by score DESC)as ranking from score) as a 
JOIN student b on a.stuId=b.stuId
where a.ranking <3

查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（与上一题相似）

SELECT a.courseId,b.`name`,a.score 
FROM(SELECT courseId, stuId,score,row_number () over(partition by courseId order by score DESC)as ranking from score) as a 
JOIN student b on a.stuId=b.stuId
where a.ranking in(2,3)

更新

编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

SELECT a.Score,( SELECT count(DISTINCT b.Score) FROM Score b WHERE b.Score >= a.Score ) AS "Rank" 
FROM
	Score a 
ORDER BY
	a.Score DESC
JavaGOAT
关注
专栏目录
sql问题总结
qq_35300804的博客
10-11 2630
表结构 DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` varchar(32) NOT NULL, `name` varchar(255) DEFAULT NULL, `t_id` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Re
MySQL分组查询问题-Unknown column ‘score‘ in ‘having clause‘
qq_44371321的博客
11-06 6264
根据表查询学生所有课程的成绩大于80的， 使用分组时，需要注意score字段的时候需要使用聚合函数来配合分组，如果直接写score》=80会出现： Unknown column ‘score’ in ‘having clause’ #查询所以课程中成绩大于80的学生姓名 #select name from studentscore group by name having min(score)>=80 ...
SQL 中的LastIndexOf，截取最后一次出现字符后面的字符
天外有天，人外有人。 专注品质 不断创新 让精益求精成为习惯
06-27 2196
SQL如何取出一个字符串中最后一个特殊字符右边的字符，例如：10*20*300，怎样得到300？ 使用reverse配合charindex来实现。  reverse是把字符串倒置，然后通过charindex来获取倒置之后第一个*的位置，然后使用substring函数进行字符串截取，截取后再使用reverse倒置回来即可。  以下为例子  declare @str v
sqlserver中获取最后一个字符所在的位置
menghuannvxia的专栏
10-22 8007
CHARINDEX('字符',reverse(字段名称)) 这个意思就是将字段进行反转，就是从后往前取，这样就可以获取一个字符最后所在的位置
sql查询学生大于平均值的成绩
热门推荐
Baibair的博客
09-24 2万+
情景 有一张表，里面是学生及其对应课程的成绩，要查出大于学生的所有课程平均值的课程。 表的结构 解决方法 我想到两种sql，如下 第一种 查出每个学生对应的 平均值再与原表连接，然后查询条件就比较该课程成绩分数和平均值 select t1.student_id,course_id from t_mark t1, ( SELECT student_id,AVG(mark) avg from t_mark group by student_id ) t2 where t1.student_id
leftjoin多表联合查询_（五）数据分析-多表查询
weixin_39851918的博客
11-30 314
一、表的加法（union）UNION 操作符用于合并两个或多个 SELECT 语句的结果集。注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。1、SQL UNION 语法（默认UNION 操作符选取不同的值。如允许重复值，使用 UNION ALL。）SELECT column_name(s) FROM ...
mysql case when 去重
最新发布
shaolong_Li的博客
07-08 351
此处只需把唯一的id取出去重即可得到去重后的统计数。
sql常用语句
03-18
主要内容包括，增删改查（CURD），重点在查询（子查询，模糊查询，分组查询等）。 在开始之前，要注意：sql是不区分大小写的，但是...另外sql语句可以写在一行，但为了方便阅读与调试，一般会将sql语句分成多行来写
SQL常用语句共7页.pdf.zip
11-25
这个压缩包文件"SQL常用语句共7页.pdf.zip"显然包含了关于SQL常用语句的精华内容，尽管实际的文档内容并未直接提供，但我们可以根据标题和描述推测其可能涵盖的关键知识点。 1. **数据查询**：SQL的核心功能之一...
学生平均成绩大于80同表sql
09-19
sql语句 查询同表中学生评价成绩大于80 的人
SQL常用语句.zip
03-22
下面将介绍一些SQL的常用语句，包括数据查询、数据操作、数据定义和数据控制等方面 一、数据查询语句 SELECT语句：用于从数据库中选择数据。基本语法为“SELECT 列名 FROM 表名 WHERE 条件”。例如，查询名为...
inner join 重复数据_数据分析-SQL-多表查询
weixin_39750410的博客
11-28 1541
内容目录表的加法多表关联多表关联应用案例case when 表达式练习题一.表的加法 （union,union all）EXCEL的应用场景中经常会有表的追加，追加是指行追加，列名称都一样的 不同记录的表工作场景中经常会有 1月份 2月份 3月份......每个月份 或者 不同合作部门提供给你的数据表（前提是表的列数是一样的）的数据记录要追加到一张表里便于做数据透视表SQL中追加相同表结构的表的记...
MySQL语句面试实例
qq_29423387的博客
10-19 693
参考链接 需要数据库表：（使用phpmyadmin创建） 学生表-Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别； 课程表-Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号； 教师表-Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名； 成绩表-SC(SID,CID,score) --SID 学生编号,CID 课程编号,sc
sql case when 去重
mn_kw的博客
05-29 4782
count(distinct case trainnumber when 'null' then 0 when '0' then 0 else trainnumber end) as train_num
HQL函数大全
weixin_43351279的博客
09-17 6530
HQL函数大全 一、关系运算 等值比较: = 等值比较: <=> 不等值比较: <>和!= 小于比较: < 小于等于比较: <= 大于比较: > 大于等于比较: >= 区间比较: between and 空值判断: IS NULL 非空判断: IS NOT NULL LIKE比较: 使用通配符 like ‘%a%’| ‘_a_’|concat(’%’,’’,’%’) JAVA的LIKE操作: 使用正则表达式 A rlike 正则表达’
case when用法_CASE … WHEN和cast类型转换（Hive日志案例分析应用）
weixin_39623716的博客
11-24 363
先补充两个知识点：CASE … WHEN和CAST类型转换CASE … WHEN…的用法创建数据库创建员工表create table emp(empno int comment '员工编号',ename string comment '员工姓名',job string comment '员工职位',mgr int comment '领导编号',hiredate string comment '入职...
sql统计各科成绩大于平均分的人_白话SQL
weixin_30284931的博客
12-17 2773
自己已建立了一个数据库，数据库中有四个数据表，如下所示：学生表：xuesheng（学号，学生姓名，出生年月，性别）成绩表：chengji（学号，课程号，成绩）课程表：kecheng（课程号，课程名称，教师号）教师表：jiaoshi（教师号，教师姓名）将表中的数据填充完整后，下面开始SQL语句操作1.展示数据库show databases;2.使用数据库use myshu;3.展示数据库中的所有表s...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值