数据库之 MySQL—— 50个查询系列

最新推荐文章于 2024-11-29 23:39:24 发布

原创最新推荐文章于 2024-11-29 23:39:24 发布 · 4.4k 阅读

55 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql #50个经典sql查询 #数据库操作 #50个查询系列

数据库专栏收录该内容

62 篇文章

订阅专栏

博客围绕项目中的50个Sql语句展开，给出了学生表、课程表、教师表和成绩表的结构，还提供了数据库、表结构与数据的链接。详细列举了如查询特定课程成绩高低的学生学号、平均成绩大于特定分数的同学信息等50个不同的Sql查询问题及部分分析思路。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

个人博客网：https://wushaopei.github.io/ (你想要这里多有)

一个项目涉及到的50个Sql语句
问题及描述：
--1.学生表

Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表

Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

--3.教师表

Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名

--4.成绩表

SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

select * from Student
select * from Course
select * from Teacher
select * from SC

--创建测试数据

create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))

create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10))

create table Teacher(Tid varchar(10),Tname nvarchar(10))

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1))

--------------------------------插入数据-------------------------------------------------

insert into student
select '1000','张无忌',18,'男' union
select '1001','周芷若',19,'女' union
select '1002','杨过',19,'男' union
select '1003','赵敏',18,'女' union
select '1004','小龙女',17,'女' union
select '1005','张三丰',18,'男' union
select '1006','令狐冲',19,'男' union
select '1007','任盈盈',20,'女' union
select '1008','岳灵珊',19,'女' union
select '1009','韦小宝',18,'男' union
select '1010','康敏',17,'女' union
select '1011','萧峰',19,'男' union
select '1012','黄蓉',18,'女' union
select '1013','郭靖',19,'男' union
select '1014','周伯通',19,'男' union
select '1015','瑛姑',20,'女' union
select '1016','李秋水',21,'女' union
select '1017','黄药师',18,'男' union
select '1018','李莫愁',18,'女' union
select '1019','冯默风',17,'男' union
select '1020','王重阳',17,'男' union
select '1021','郭襄',18,'女' 
go

insert  into teacher
select '001','姚明' union
select '002','叶平' union
select '003','叶开' union
select '004','孟星魂' union
select '005','独孤求败' union
select '006','裘千仞' union
select '007','裘千尺' union
select '008','赵志敬' union
select '009','阿紫' union
select '010','郭芙蓉' union
select '011','佟湘玉' union
select '012','白展堂' union
select '013','吕轻侯' union
select '014','李大嘴' union
select '015','花无缺' union
select '016','金不换' union
select '017','乔丹'
go

insert into course
select '001','企业管理','002' union
select '002','马克思','008' union
select '003','UML','006' union
select '004','数据库','007' union
select '005','逻辑电路','006' union
select '006','英语','003' union
select '007','电子电路','005' union
select '008','思想概论','004' union
select '009','西方哲学史','012' union
select '010','线性代数','017' union
select '011','计算机基础','013' union
select '012','AUTO CAD制图','015' union
select '013','平面设计','011' union
select '014','Flash动漫','001' union
select '015','Java开发','009' union
select '016','C#基础','002' union
select '017','Oracl数据库原理','010'
go

insert into SC
select '1001','003',90 union
select '1001','002',87 union
select '1001','001',96 union
select '1001','010',85 union
select '1002','003',70 union
select '1002','002',87 union
select '1002','001',42 union
select '1002','010',65 union
select '1003','006',78 union
select '1003','003',70 union
select '1003','005',70 union
select '1003','001',32 union
select '1003','010',85 union
select '1003','011',21 union
select '1004','007',90 union
select '1004','002',87 union
select '1005','001',23 union
select '1006','015',85 union
select '1006','006',46 union
select '1006','003',59 union
select '1006','004',70 union
select '1006','001',99 union
select '1007','011',85 union
select '1007','006',84 union
select '1007','003',72 union
select '1007','002',87 union
select '1008','001',94 union
select '1008','012',85 union
select '1008','006',32 union
select '1009','003',90 union
select '1009','002',82 union
select '1009','001',96 union
select '1009','010',82 union
select '1009','008',92 union
select '1010','003',90 union
select '1010','002',87 union
select '1010','001',96 union

select '1011','009',24 union
select '1011','009',25 union

select '1012','003',30 union
select '1013','002',37 union
select '1013','001',16 union
select '1013','007',55 union
select '1013','006',42 union
select '1013','012',34 union
select '1000','004',16 union
select '1002','004',55 union
select '1004','004',42 union
select '1008','004',34 union
select '1013','016',86 union
select '1013','016',44 union
select '1000','014',75 union
select '1002','016',100 union
select '1004','001',83 union
select '1008','013',97
go

数据库、表结构与数据，链接地址：

链接：https://pan.baidu.com/s/1K8ikM_HmJYpUy70ClJyb3Q
提取码：se3b

--1、查询“001”课程比“002”课程成绩高的所有学生的学号；

第一步明确主表和次表。

我们要查询的是学生的学号，那么主表就是学生表（student）

SELECT s.Sid 
from student s
 where
-- SC是次表，我们用来筛选数据
 (SELECT sc.score FROM SC sc WHERE sc.Cid='001'and s.Sid=sc.Sid) 
  >
(SELECT sc.score FROM SC sc WHERE sc.Cid='002' and s.Sid=sc.Sid)

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

select s.Sname,t1.*
from student s,
   (select sc.Sid,avg(sc.score) avgscore
		from sc sc 
		group by sc.Sid
		HAVING avgscore > 60 ) t1
where t1.Sid = s.Sid

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

select s.Sname,s.Sid,
   (select count(sc.Cid)  from sc where sc.Sid=s.Sid) countsc,
   (select sum(sc.score)  from sc where sc.Sid=s.Sid) sumsc
from student s

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

SELECT
	count(*),
	t.Tname
FROM
	teacher t
WHERE
	t.Tname LIKE '李%'

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

 SELECT Sid,Sname FROM student
  WHERE Sid NOT IN
   (
    SELECT Sid FROM SC sc
     INNER JOIN course cu ON sc.Cid=cu.Cid
     INNER JOIN teacher tc ON cu.Tid=tc.Tid    
      WHERE tc.Tname='叶平'
   )

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


select  s.Sid,s.Sname
from student s ,
	(select sc1.Sid 
	from (select sc.Sid from sc where sc.Cid = '001' ) sc1 ,
	     (select sc.Sid from sc where sc.Cid = '002' ) sc2
	where sc1.Sid = sc2.Sid ) t1
where s.Sid = t1.Sid

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

SELECT  s.Sid,s.Sname
from student s
where not EXISTS
(
select c.Cid from course c where c.Tid =    
			(select t.Tid from teacher t where t.Tname ="叶平")
       and c.Cid not in 
			(select sc.Cid from sc where sc.Sid = s.Sid)
)

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

SELECT s.Sid,s.Sname
from student s
 where
-- SC是次表，我们用来筛选数据
 (SELECT sc.score FROM SC sc WHERE sc.Cid='002'and s.Sid=sc.Sid) 
  <
(SELECT sc.score FROM SC sc WHERE sc.Cid='001' and s.Sid=sc.Sid)

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

select s.Sid,s.Sname
from student s
where s.Sid not in (
select sc.Sid from sc where  sc.score > 60 and s.Sid = sc.Sid)

分析：

1、先查询有课程大于60分的同学的学号

2、使用 not 进行反向选择

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

select s.Sid,s.Sname
from student s
where (select count(*) from sc where s.Sid=sc.Sid)
    <
     (select count(*) from course)

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

select s.Sid,s.Sname from student s 
where s.Sid in 
(
select DISTINCT  sc.Sid  from sc where sc.Cid 
    in (select sc.Cid from sc where sc.Sid="1001")
)

分析：

   1、先根据 1001 号同学的Sid 查找到他所学的课程的编号，

   2、根据课程编号查找到所有学过该课程的学生编号 Sid

   3、只要有学过其中至少一门以上课程的学生为符合条件筛选

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

select s.Sid,s.Sname
from student s
where s.Sid in (

select DISTINCT sc.Sid from sc where sc.Cid not in (
    select sc.Cid from sc where sc.Sid = '1001')
)

--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； (从子查询中获取父查询中的表名，这样也行？？？？)

UPDATE sc
SET score = (SELECT
               a.avgScore
             FROM (SELECT
                     AVG(sc_1.score)    avgScore
                   FROM sc sc_1,(SELECT * FROM sc )c
                   WHERE c.Cid = sc_1.Cid) a)
WHERE Cid IN(SELECT
                course.Cid
              FROM course,
                teacher
              WHERE teacher.Tname = '叶平'
                  AND teacher.Tid = course.Tid);

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

select Sid from sc where Sid not in (
select Sid from sc where Cid not in (
SELECT Cid from sc where Sid = "1002"))
GROUP BY Sid 
HAVING count(*) = (select count(*) from sc where Sid = "1002")

分析：

1、使用双重否定你的方式，快速且方便的获得结果；

2、第一重not in 否定获取不包括 1002 同学所学课程的Sid ；

3、第二重 not in 否定获取所学课程最少一个且为 1002 同学所学的课程的 Sid；

4、使用 group by 分组函数去重

5、获取 1002 同学所学课程的 count （*）进行函数运算，满足 count (*) 数量大小的说明 1002 号同学所学的课程都有学

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

DELETE sc 
from course c,teacher t,sc
where c.Cid = sc.Cid and t.Tid = c.Tid and t.Tname="叶平"

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

INSERT sc 
select Sid,"002",
(select avg(score)
from sc 
where Cid="002") 
from student 
where Sid 
not in (
select Sid
from sc
where Cid = "003")

分析：

    1、子表 : 编号‘003“ 课程的同学学号，从 sc 表中查 where Cid= ”003“

    2、子表：编号”002“课程的平均成绩，从 sc表中查，where Cid ="002"

    3、没有上过”003“课程使用 not in   （子表1）

    4、插入操作： insert sc(表名），插入记录，由2可知，插入课程为 “002”，使用select Sid ,"002"，score 使用 “002” 平均值，即子表 2 作为临时表。

--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT
	t.Sid AS 学生ID,
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sc.Sid = t.Sid
		AND Cid = "004"
	) AS 数据库,
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sc.Sid = t.Sid
		AND Cid = "001"
	) AS 企业管理,
	(
		SELECT
			score
		FROM
			sc
		WHERE
			sc.Sid = t.Sid
		AND Cid = "006"
	) AS 英语,
	count(*) AS 有效课程数,
	avg(t.score) AS 平均成绩
FROM
	sc AS t
GROUP BY
	t.Sid
ORDER BY
	avg(t.score) DESC

分析：

1、使用select 查询 sc 表，并where 查询对应的 Cid ，分别查询到 004、001、006三个课程

2、有效课程数，就是学生的选修课程，使用count(*)可以获得

3、平均成绩为当前查询到的学生对象的 score 的avg（score）值

4、使用 as 为每一个返回的参数赋予别名： t.Sid as 学生ID

5、主表查询， from sc as t ，t 为主表别名，使用 t.Sid 即学生号作为分组函数的关键；使用 avg(t.score) 获取学生总课程平均分，并使用 order by 进行排序，降序为 desc

6、显示学生的数据库、企业管理、英语成绩需将 t.Sid 和临时表 sc 中的 sc.Sid 进行关联，即 sc.Sid = t.Sid

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

SELECT
	l.Cid AS 课程ID,
	l.score AS 最高分,
	r.score AS 最低分
FROM
	sc l,
	sc AS r
WHERE
	l.Cid = r.Cid
AND l.score = (
	SELECT
		max(il.score)
	FROM
		sc AS il,
		student AS im
	WHERE
		l.Cid = il.Cid
	AND im.Sid = il.Sid
	GROUP BY
		il.Cid
)
AND r.score = (
	SELECT
		min(ir.score)
	FROM
		sc AS ir
	WHERE
		r.Cid = ir.Cid
	GROUP BY
		ir.Cid
)
GROUP BY
	l.Cid

--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)

SELECT
	sc.Cid AS 课程id,
	FORMAT(avg(sc.score), 2) AS 平均成绩,
	CONCAT(
		FORMAT(
			100 * SUM(
				CASE
				WHEN sc.score >= 60 THEN
					1
				ELSE
					0
				END
			) / COUNT(*),
			2
		),
		'%'
	) AS 及格率
FROM
	sc
GROUP BY
	sc.Cid
ORDER BY
	AVG(sc.score),
	100 * SUM(
		CASE
		WHEN sc.score >= 60 THEN
			1
		ELSE
			0
		END
	) / COUNT(*) DESC

--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

select sum(sc2.企业管理)/count(sc2.企业管理) as 企业管理平均分, CONCAT(FORMAT(100*SUM(case when sc2.企业管理>=60 then 1 else 0 end)/COUNT(*),2),'%') as 企业管理及格率,
	sum(sc2.马克思)/count(sc2.马克思) as 马克思平均分, CONCAT(FORMAT(100*SUM(case when sc2.马克思>=60 then 1 else 0 end)/COUNT(*),2),'%') as 马克思及格率,
	sum(sc2.UML)/count(sc2.UML) as UML平均分, CONCAT(FORMAT(100*SUM(case when sc2.UML>=60 then 1 else 0 end)/COUNT(*),2),'%') as UML及格率,
	sum(sc2.数据库)/count(sc2.数据库) as 数据库平均分, CONCAT(FORMAT(100*SUM(case when sc2.数据库>=60 then 1 else 0 end)/COUNT(*),2),'%') as 数据库及格率
from (
    select 
	(case when sc.Cid=001 then sc.score else null end) as 企业管理,
	(case when sc.Cid=002 then sc.score else null end) as 马克思,
	(case when sc.Cid=003 then sc.score else null end) as UML,
	(case when sc.Cid=004 then sc.score else null end) as 数据库
    from sc
) sc2;

--21、查询不同老师所教不同课程平均分从高到低显示

SELECT
	max(t.Tid) AS 教师id,
	max(t.Tname) AS 教师名字,
	c.Cid AS 课程id,
	MAX(c.Cname) AS 课程名称,
	AVG(s.score) AS 平均成绩
FROM
	sc AS s,
	course AS c,
	teacher AS t
WHERE
	t.Tid = c.Tid
OR c.Cid = s.Cid -- 根据老师id找到课程id
GROUP BY
	c.Cid -- 根据课程id分组
ORDER BY
	AVG(score) DESC -- 根据平均分排序

--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）格式：[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

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

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

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

--27、查询出只选修了一门课程的全部学生的学号和姓名

--28、查询男生、女生人数

--29、查询姓“张”的学生名单

--30、查询同名同性学生名单，并统计同名人数

--31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

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

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