大家好，我是叶扬，一个专注于AI玩法的探索者。
热衷于分享AI学习心得，辅助画图写文章，无所不用。
关注我，一起 AI 破局。
Windows 安装 sqlite
找一个空文件夹，shift+右键，在此处打开 Powershell 窗口(S)，
在powershell中，输入如下命令
# 使用winget
winget install sqlite.sqlite
安装成功后，输入下面的命令，进行测试
sqlite3 --version
出现版本号则表示安装成功。
创建数据库
还是在powershell中，输入
sqlite3 db251128.sqlite
查看是否创建成功
.quit # 退出sqlite，回到文件夹路径下
ls
dbeaver连接sqlite数据库
选上路径，测试链接，就成功了。
建表
-- 学生表
CREATE TABLE Student(
sid TEXT,
sname TEXT,
sage TEXT, -- 日期直接存 TEXT，格式保持 yyyy-MM-dd
ssex TEXT
);
-- 课程表
CREATE TABLE Course(
cid TEXT,
cname TEXT,
tid TEXT
);
-- 教师表
CREATE TABLE Teacher(
tid TEXT,
tname TEXT
);
-- 成绩表
CREATE TABLE SC(
sid TEXT,
cid TEXT,
score REAL -- SQLite 用 REAL 代替 DECIMAL
);
插数
INSERT INTO Student(sid,sname,sage,ssex) VALUES
('01','赵雷','1990-01-01','男'),
('02','钱电','1990-12-21','男'),
('03','孙风','1990-05-20','男'),
('04','李云','1990-08-06','男'),
('05','周梅','1991-12-01','女'),
('06','吴兰','1992-03-01','女'),
('07','郑竹','1989-07-01','女'),
('08','王菊','1990-01-20','女');
INSERT INTO Course(cid,cname,tid) VALUES
('01','语文','02'),
('02','数学','01'),
('03','英语','03');
INSERT INTO Teacher(tid,tname) VALUES
('01','张三'),
('02','李四'),
('03','王五');
INSERT INTO SC(sid,cid,score) VALUES
('01','01',80),
('01','02',90),
('01','03',99),
('02','01',70),
('02','02',60),
('02','03',80),
('03','01',80),
('03','02',80),
('03','03',80),
('04','01',50),
('04','02',30),
('04','03',20),
('05','01',76),
('05','02',87),
('06','01',31),
('06','03',34),
('07','02',89),
('07','03',98);
表结构
开始做题
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
with t1 as
(select sid ,score from sc where sc.cid = '01'),
t2 as
(select sid,score from sc where sc.cid = '02')
select
s.*,
t1.score,
t2.score
from t1
full join t2 on t1.sid =t2.sid
left join student s on t1.sid = s.sid
where t1.score > t2.score
1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
with t1 as
(select sid,cid,score from sc where sc.cid = '01'),
t2 as
(select sid,cid,score from sc where sc.cid = '02')
select
t1.sid,
t1.cid,
t1.score,
t2.sid,
t2.cid,
t2.score
from t1
left join t2 on t1.sid =t2.sid
1.2 查询同时存在01和02课程的情况
with t1 as
(select sid,cid,score from sc where sc.cid = '01'),
t2 as
(select sid,cid,score from sc where sc.cid = '02')
select
t1.sid,
t1.cid,
t1.score,
t2.sid,
t2.cid,
t2.score
from t1
inner join t2 on t1.sid =t2.sid
1.3 查询选择了02课程但没有01课程的情况
select * from sc s
where s.cid = '02'
and s.sid not in (select c.sid from sc c where cid = '01')
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select
s.sid,
t.sname,
avg(s.score) avg_score
from sc s
left join student t on s.sid = t.sid
group by s.sid, t.sname
having avg(s.score) >= 60
3.查询在 sc 表存在成绩的学生信息
select
s.sid,
s.sname,
s.sage,
s.ssex
from student s
where s.sid in (
select c.sid from sc c group by c.sid
)
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select t.sid, t.sname, count(s.cid), sum(s.score)
from sc s
right join student t on s.sid = t.sid
group by s.sid,t.sname
5.查询「李」姓老师的数量
select count(*) from teacher t where t.tname like '李%'
6.查询学过「张三」老师授课的同学的信息
select * from student s
left join sc on sc.sid = s.sid
left join course c on sc.cid = c.cid
left join teacher t on c.tid = t.tid
where t.tname = '张三'
7.查询没有学全所有课程的同学的信息
select * from student s
where s.sid not in (
select sc.sid
from sc group by sc.sid
having count(sc.cid) = (select count(c.cid) from course c)
)
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select t.*
from sc
inner join student t on sc.sid = t.sid
where sc.cid in (
select s1.cid from sc s1 where s1.sid = '01'
)
and sc.sid <> '01'
group by sc.sid
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select s.*
from student s
join sc on s.sid = sc.sid
where s.sid <> '01'
group by s.sid
having count(sc.cid) = (select count(cid) from sc where sid = '01')
and sc.cid in (select cid from sc where sid = '01')
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select sname
from student s
where sid not in (
select sid from sc
left join course c on sc.cid = c.cid
left join teacher t on c.tid = t.tid
where t.tname = '张三'
) t
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sc.sid , t.sname , avg(sc.score) avg_score
from sc
left join student t on sc.sid = t.sid
where sc.score < 60
group by sc.sid
having count(sc.cid) >=2
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select t.sid,t.sname,t.sage,t.ssex
from sc
left join student t on sc.sid = t.sid
where sc.cid = '01' and sc.score <60
order by sc.score desc
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s.sid,s.cid,s.score,t.avg_score
from sc s
left join (
select sid,avg(score) avg_score
from sc s2
group by sid
) t on s.sid =t.sid
order by t.avg_score desc
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
以如下形式显示：课程 id，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
--- sqlite 浮点数转换
select
s.cid 课程id,
c.cname 课程name,
max(s.score) 最高分,
min(s.score) 最低分,
avg(s.score) 平均分,
-- count(s.cid),
-- sum(jg),
-- sum(zd),
-- sum(yl),
-- sum(yx),
-- count(s.sid) 人数,
-- 转为浮点数后计算
round( (sum(jg) * 1.000 / count(s.cid)) , 4 ) 及格率,
round( (sum(zd) * 1.000 / count(s.cid)) , 4 ) 中等率,
round( (sum(yl) * 1.000 / count(s.cid)) , 4 ) 优良率,
round( (sum(yx) * 1.000 / count(s.cid)) , 4 ) 优秀率
from (
select
sc.sid , sc.cid, sc.score,
case when sc.score>=60 then 1 else 0 end jg,
case when sc.score>=70 and sc.score<80 then 1 else 0 end zd,
case when sc.score>=80 and sc.score<90 then 1 else 0 end yl,
case when sc.score>=90 then 1 else 0 end yx
from sc
) s
left join course c on s.cid = c.cid
group by s.cid,c.cname
order by count(s.sid) desc, s.cid asc
15.按各科成绩进行排序，并显示排名， score 重复时保留名次空缺
/*
row_number()：为每行分配唯一序号，无视重复值
rank():按值排名，重复值获相同名次，后续名次跳过空缺（保留名次空缺）
dense_rank():按值排名，重复值获相同名次，后续名次不跳过（无空缺）
*/
select
s.cid,
s.sid,
s.score,
rank() over (partition by s.cid order by s.score desc) 名次
from sc s
order by s.cid, 名次;
15.1 按各科成绩进行行排序，并显示排名， score 重复时合并名次
select
s.cid,
s.sid,
s.score,
dense_rank() over (partition by s.cid order by s.score desc) 名次
from sc s
order by s.cid, 名次;
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sid, score,rank() over (order by score desc) 名次
from(
select sid, sum(score) score
from sc s
group by sid
) t
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sid, score,dense_rank() over (order by score desc) 名次
from(
select sid, sum(score) score
from sc s
group by sid
) t
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
--- sqlite 浮点数转换
select
s.cid 课程编号,
c.cname 课程名称,
sum(jg) '[60-0]',
sum(zd) '[70-60]',
sum(yl) '[85-70]',
sum(yx) '[100-85]',
-- count(sid) 人数,
-- 转为浮点数后计算
round( (sum(jg) * 1.000 / count(s.cid)) , 4 ) '[60-0]率',
round( (sum(zd) * 1.000 / count(s.cid)) , 4 ) '[70-60]率',
round( (sum(yl) * 1.000 / count(s.cid)) , 4 ) '[85-70]率',
round( (sum(yx) * 1.000 / count(s.cid)) , 4 ) '[100-85]率'
from (
select
sc.sid , sc.cid, sc.score,
case when sc.score<60 then 1 else 0 end jg,
case when sc.score>=60 and sc.score<70 then 1 else 0 end zd,
case when sc.score>=70 and sc.score<85 then 1 else 0 end yl,
case when sc.score>=85 then 1 else 0 end yx
from sc
) s
left join course c on s.cid = c.cid
group by s.cid,c.cname
18.查询各科成绩前三名的记录
select sid, cid, score,sort
from (
select sid,cid,score,
rank()over (partition by cid order by score desc) sort
from sc
) t where sort <=3
19.查询每门课程被选修的学生数
select cid,count(sid) cnt
from sc
group by cid
20.查询出只选修两门课程的学生学号和姓名
select sc.sid,t.sname
from sc
left join student t on sc.sid = t.sid
group by sc.sid
having count(sc.cid) = 2
21.查询男生、女生人数
select ssex ,count(sid)
from student s
group by s.ssex
select
sum(case when ssex='男' then 1 else 0 end)as 男生人数,
sum(case when ssex='女' then 1 else 0 end) as 女生人数
from student;
22.查询名字中含有「风」字的学生信息
select * from student s where sname like '%风%'
23.查询同名同性学生名单，并统计同名人数
select sname,count(sname) from student s group by sname having count(sname) >1
24.查询 1990 年出生的学生名单
select * from student s where sage like '1990%'
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(score)
from sc
group by cid
order by avg(score) desc,cid asc;
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select sc.sid, t.sname, avg(sc.score)
from sc
left join student t on sc.sid = t.sid
group by sc.sid, t.sname
having avg(score) >=85
27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select t.sname,sc.score
from sc
left join student t on sc.sid =t.sid
left join course c on sc.cid=c.cid
where c.cname = '数学' and sc.score < 60
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select t.sid ,t.sname ,t.sage ,t.ssex ,c.cname, sc.score
from student t
left join sc on t.sid = sc.sid
left join course c on c.cid =sc.cid
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.sname, c.cname, sc.score
from sc
left join student s on s.sid =sc.sid
left join course c on c.cid =sc.cid
where sc.score >70
30.查询不及格的课程
select distinct (sc.cid)
from sc
where sc.score <60
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select sc.sid, s.sname, sc.score
from sc
left join student s on sc.sid = s.sid
where sc.cid = '01' and sc.score > 80
32.求每门课程的学生人数
select sc.cid, count(sid) cnt
from sc
group by sc.cid
33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select /* sc.cid,c.cname,t.tname, score , */sc.sid, s.sname, s.sage ,s.ssex, score
from sc
left join student s on sc.sid = s.sid
left join course c on c.cid =sc.cid
left join teacher t on t.tid = c.tid
where t.tname = '张三'
order by sc.score desc
limit 1
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select /* sc.cid,c.cname,t.tname, score , */sc.sid, s.sname, s.sage ,s.ssex, score
from sc
left join student s on sc.sid = s.sid
left join course c on c.cid =sc.cid
left join teacher t on t.tid = c.tid
where t.tname = '张三'
and score = (
select
max(score)
from sc
left join student s on sc.sid = s.sid
left join course c on c.cid =sc.cid
left join teacher t on t.tid = c.tid
where t.tname = '张三'
)
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 #interesting
select distinct s.sid, s.cid ,s.score
from sc s
join sc
on s.sid = sc.sid
and s.score = sc.score
and s.cid <> sc.cid
36.查询每门成绩最好的前两名
select sid,cid, score,sort
from (
select sid,cid,score,
rank()over (partition by cid order by score desc) sort
from sc
) t where sort <=2
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select cid, count(sid)
from sc
group by cid
having count(sid) > 5
38.检索至少选修两门课程的学生学号
select sid, count(cid)
from sc
group by sid
having count(cid) >=2
39.查询选修了全部课程的学生信息
select sc.sid,t.sname ,t.sage ,t.ssex
from sc
left join student t on sc.sid =t.sid
group by sc.sid
having count(sc.cid) = (select count(cid) from course)
40.查询各学生的年龄，只按年份来算
-- strftime('%y', 'now')
select
sid,
sname,
ssex,
sage,
-- 提取当前年份 - 出生日期年份 = 按年份计算的年龄
strftime('%y', 'now') - strftime('%y', sage) age
from student;
41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- strftime('%m%d', 'now')
select
sid,
sname,
ssex,
sage,
-- 计算精确年龄：年份差 - 月日比较结果（true则1，false则0）
(strftime('%y', 'now') - strftime('%y', sage))
- (case when strftime('%m%d', 'now') < strftime('%m%d', sage) then 1 else 0 end) age
from student;
42.查询本周过生日的学生
select
sid,
sname,
ssex,
sage,
-- strftime('%w', 'now'),
strftime('%m-%d', sage) wek
from student
where
-- 将生日转为当年日期，获取其周数，与当前周数一致
strftime('%w', date(strftime('%y', 'now') || '-' || strftime('%m-%d', sage)))
= strftime('%w', 'now');
43.查询下周过生日的学生
select
sid,
sname,
ssex,
sage,
strftime('%m-%d', sage) wek
from student
where
-- 将生日转为当年日期，获取其周数
strftime('%w', date(strftime('%y', 'now') || '-' || strftime('%m-%d', sage)))
= strftime('%w', date('now', '+7 days'));
44.查询本月过生日的学生
select
sid,
sname,
ssex,
sage
from student
where strftime('%m',sage) = strftime('%m','now')
45.查询下月过生日的学生
select
sid,
sname,
ssex,
-- cast(strftime('%m', sage) as integer),
sage
from student
where
cast(strftime('%m', sage) as integer) = (cast(strftime('%m', 'now') as integer) % 12) +1;
我是叶扬，人生苦短，我用 AI，也用高效的方法。希望今天的分享能够帮到你！
---
扬哥最近在学习AI，打造主业之外的第二曲线。
这里有一份 AI 实操学习，了解一下，只有学完就能用
海量精华帖子➕三天免费课程，AI写作，AI视频，AI编程、智能体、RPA……
试一试，免费看高质量头部社群帖子
点击【阅读原文】查看叶扬用AI编程 制作网站吧，或许有你感兴趣的内容哦！
本文使用 文章同步助手 同步
