本文通过一系列SQL查询实例，详细展示了如何操作数据库中的学生、教师、课程及成绩表，包括添加数据、查询特定字段、统计分析等操作。内容涵盖基本查询、区间查询、聚合函数、子查询以及多表联查，旨在帮助读者掌握数据库信息检索的基本技能。
一，先创建几个表
学生表Student: 学号;姓名;性别;出生年月日;所在班级
create table student (
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20));
教师表Teacher: 教师编号;教师姓名;教师性别;出生日期;职称;所在部门
create table teacher (
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
课程表Course: 课程号;课程名称;教室编号
create table course (
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
成绩表Score: 学号;课程号;成绩
create table score (
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
往数据表中添加数据
添加学生信息：
insert into student values(‘101’,’曾华’,’男’,’1977-09-01’,’95033’);
insert into student values(‘102’,’匡明’,’男’,’1975-10-02’,’95031’);
insert into student values(‘103’,’王丽’,’女’,’1976-01-23’,’95033’);
insert into student values(‘104’,’李军’,’男’,’1976-02-20’,’95033’);
insert into student values(‘105’,’王芳’,’女’,’1975-02-10’,’95031’);
insert into student values(‘106’,’陆君’,’男’,’1974-06-03’,’95031’);
insert into student values(‘107’,’宋江’,’男’,’1976-10-02’,’95033’);
insert into student values(‘108’,’李逵’,’男’,’1975-11-11’,’95031’);
insert into student values(‘109’,’赵力’,’男’,’1974-06-02’,’95031’);
添加教师表
insert into teacher values('804','李程','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
添加成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');
二，查询练习
1，查询student 表中的所有记录
select * from student; *表示所有的意思，select *表示所有字段，从student表，表示所有字段
*表示sno\sname\ssex\sbirthday\class这些字段
2，查询student表中的所有记录中指定的记录比如：sname、ssex和class这三个字段
select sname,ssex,class from student;
3，查询教师所有单位即即不重复的depart列 distinct排除重复
select depart from teacher; select distinct depart from teacher;
本来查询depart这个字段有重复的，加上distinct（有区别的）就排除重复的
4，查询score表中成绩在60到80之间的所有记录——查询区间
select * from score where degree between 60 and 80;
查询区间——where 字段 between ... and ...;
select * from score where degree >60 and degree <80;
查询区间——直接使用运算符进行比较
5，查询score 表中成绩为85、86或88的记录
表示或者关系的查询 in 同等字段
select * from score where degree in(85,86,88);
6，查询student表中的”95031”班或者性别为”女”的同学记录——不同字段的或者or
select * from student where class=’95031’ or ssex=’女’
7，以class 降序查询student表中的所有记录
降序 select * from student order by class desc;
升序 select * from student order by class asc;
8，以cno升序、degree降序查询score表的所有记录
select * from student order by cno asc,degree desc;
9，查询”95031”班的学生人数
统计——count
select count(*) from student where class=’95031’;
10，查询score表中的最高分的学生学号和课程号(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);——只查询
正常的开发步骤：
1)找到最高分 select max(degree) from score;
2)找到最高分的sno和cno
select sno,cno from score where degree=(select max(degree) from score);
还有一种方法是排序，但是容易出现错误
select sno,cno,degree from score order by degree desc limit 0,1; 先排个降序再选择第一个
想找除前两名就是0,2 前三名0,3 第一个数字是开始的数字，第二个数字第多少条
11，查询每门课的平均成绩avg
首先要知道有几门课 select * from course;
再求平均分
select avg(degree) from score where cno=’3-105’;
select avg(degree) from score where cno=’3-245’;
select avg(degree) from score where cno=’6-166’;
还有一种方式直接在一个sql语句中写
select cno,avg(degree) from score group by cno; —用到了分组，先分组，再把成绩平均
12，查询score表中至少有2名学生选择的并以3开头的课程的平均分数
select cno,avg(degree) from score #选score表中的cno,和 avg
grop by cno #将cno（课程号）进行分组
having count(cno>=2) #选择至少有两名学生选择的课程号
and con like ‘3%’; #课程号以3开头 like就是以什么开头
select cno,avg(degree),count(*) from score grop by cno having count(cno>=2) and con like ‘3%’;
count(*)查看多少人
有3个人，并且以3开头的课程的平均分数
13，查询分数大于70，小于90的sno列
查询范围——两种方式，一种数学取值，一种between
(1)select sno,degree from score where degree>70 and degree <90;
(2)select sno,degree from score where degree between 70 and 90;
14，查询所有学生的sname、cno和degree列——查询不同的表中的字段
select sname from student;选出student表中的sname字段
select cno,degree from score;选出score表中的cno,degree字段
上面只选出来了单个表单个字段，现在提出新办法，将他们进行联合
select sno,sname from student;选出student表中的sname字段
select sno,cno,degree from score;选出score表中的cno,degree字段
看出两个表中都有sno,先给两个表中都选中sno,让student中的sno 等于score的sno对应过去
然后将两个表格进行融合
select sname,cno.degree from student,score where student.sno=score.sno;
15，查询所有学生的sno、cname和degree列
sno在student和score cname在course degree在score
相当于sno可以使用score来查询，只用查询两个表，course和score这两个表有共同的cno
和上面一样
select cno,cname from course;
select cno,sno,degree from score;
将两个进行融合，多表查询就是先写出他们查询的和共同拥有的在进行融合
select sno,cname,degree from course,score where course.cno=score.cno;
16，查询所有学生的sname、cname和degree
sname 在student cname在course degree在score
共同点：student和score都有sno，course和score都有cno两个条件用and连接起来
select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
17，查询”95031”班学生每门课的平均成绩
select * from student where class=’95031’;先查询95031班级里面有谁
select sno from student where class=’95031’;
select * from score where sno in(select sno from student where class=’95031’);
下来算平均成绩
select cno,avg(degree) from score
where sno in(select sno from student where class=’95031’)
group by cno;
18，查询选修’’3-105’’课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录
（1）选出成绩高于“109”号同学“3-105”成绩的所有同学
select degree from score where sno=’109’ and cno=’3-105’;
（2）在查出大于高于“109”号同学“3-105”成绩的所有同学
select * from score where
cno=’3-105’ and
degree>(select degree from score where sno=’109’ and cno=’3-105’);
19，查询成绩高于学号为”109”、课程号为“3-105”的成绩的所有记录
select * from score where
degree>(select degree from score where sno=’109’ and cno=’3-105’);
20，查询学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列
(1)先查出学号为108、101学生的信息——select * from student where in(108,101);
(2)只需要年份——select year(sbirthday) from student where sno in(108,101);
(3)筛选其他同学的年份——
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
21，查询“张旭”教师任课的学生成绩
(1)先查询张旭这个教师select * from teacher where tname=’张旭’；
(2)再找出他上课的编号select tno from teacher where tname=’张旭’；
(3)找出他上的什么课select * from course where tno=(select tno from teacher where tname=’张旭’)；
(4)找出他上课的编号select cno from course where tno=(select tno from teacher where tname=’张旭’)；
(5)融合select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=’张旭’));
22，查询某课程的同学人数多于5人的教师姓名
(1)查询课程人数多于5人的select cno from score group by cno having count(*)>5;
having 是在 group by后面用于统计的，相当于分组后的条件
(2)在教师表中找与成绩表相关的字段，没有，引入课程表tno->teacher,course cno->score,course
select tno from course where cno=(select cno from score group by cno having count(*)>5);
(3)融合
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
23，查询95033班和95031班全体学生的记录
select * from student where class in ('95031','95033');
24，查询存在有85分以上成绩的课程cno
select * from score where degree>85; select cno,degree from score where degree>85;
25，查询出“计算机系”教师所教课程的成绩表
select * from teacher where depart=’计算机系’;
select * from course where tno in (select * from teacher where depart=’计算机系’);
找到了他们所带课程的编号
select * from score where cno in (select tno from course where tno in (select tno from teacher where depart=’计算机系’))
26，查询“计算机系”与“电子工程系”不同职称的教师的tname和prof
select * from teacher where depart ='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
select * from teacher where depart ='电子工程系' and prof not in (select prof from teacher where depart='计算机系');
连接起来
select * from teacher where depart ='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
union
select * from teacher where depart ='电子工程系' and prof not in (select prof from teacher where depart='计算机系');
union是将不同的表格连接起来，求并集
27，查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree,并按degree从高到底次序排序
select * from score where cno=’3-245’; select * from score where cno='3-105';
至少高于，就是高于编号为3-245中的任意一个，任意用any
select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245');
排序后：
排序：select * from score where cno='3-105' and degree>any(select degree from score
where cno='3-245') order by degree desc;
28，查询选修编号为“3-105”且成绩高于编号为“3-245”课程的同学的cno、sno和degree
且，所有的意思all
select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
29，查询所有教师和同学的name、sex和 birthday
老师的select tname,tsex,tbirthday from teacher;学生的select sname,ssex,sbirthday from student;
联合起来
select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from student;
因为上面的name、sex和birther很多,表述意义不清晰所以 使用 别名 as
select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;
30，查询所有女教师和女同学之间的name、sex和 birthday
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=’女’
union
select sname,ssex,sbirthday from student ssex=’女’;
31，查询成绩比该课程平均成绩低的同学
每门课的平均成绩：select cno,avg(degree) from score group by cno;
将上面生成的所有成绩表（select * from score;）复制一份，设为表a和表b，遍历表a中的数，分别和表b中的平均数进行比较
select * from score a where degree<(select avg(degree) from score b where a.cn o=b.cno);
32，查询所有任课教师的tname和depart
查询教室的所有信息select * from teacher;
查询课程表中安排了课程select * from course
select tname,depart from teacher where tno in (select tno from course);
33，查询至少有2名男生的班号
先按照班号进行分组select class from student where ssex='男' group by class having count(*)>1;
34，查询student表中不姓王的同学的记录
select * from student where sname not like ‘王%’； like ‘_%’是模糊查询，像什么
35，查询student表中的每个学生的姓名和年龄
年龄是当前年份减去出生年份select sname,year(now())-year(sbirthday) as ‘年龄’ from student;
36，查询student表中最大和最小的sbirthday日期值
select sbirthday from student order by sbirthday;
order by的作用是结果按照顺序进行排列
最大值和最小值
37，以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,year(sbirthday) ; 顺序就是order by....desc/asc
38，查询男教师及其所上的课程
先找出男教师的信息select * from teacher where tsex=’男’;
course表和teacher表中共有的是tno这列，所以名主要提取，男教师的tno这一字段
作为条件，从course中找出满足tno条件的结果 括号里面的条件，不能为*
select * from course where tno in (select tno from teacher where tsex=’男’);
39，查询最高分同学的sno、cno和degree列
先查出来最高分：select max(degree) from score;——92’
select sno,cno,degree from score where degree=(select max(degree) from score);
select * from score where degree=(select max(degree) from score);
两个都可以，因为，score就包括了sno,cno,degree这三个字段，也可以用*表示
40，查询和李军同性别的所有同学的sname
先查出李军的性别select ssex from student where sname='李军';
select sname from student where ssex=(select ssex from student where sname='李军');
41，查询和李军同性别并同班级的所有同学的sname
select sname from student
where ssex=(select ssex from student where sname='李军')
and class=(select class from student where sname='李军');
42，查询所有选修“计算机导论”课程的“男”同学的成绩
select * from course where cname=’计算机导论’;——选出计算机导论
select * from student where ssex='男'; ——选出男同学
select * from score
where cno=(select cno from course where cname='计算机导论')
and sno in (select sno from student where ssex='男');
为什么有的条件用=有的用in
43，假设使用如下命令建立一个grade表：
create table grade(
low int(3),
upp int(3),
grade char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
查询所有同学的sno,cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
