目 录
4.6 查询选修了"c04"课程的学生中,成绩比”李露“高的学生的学号和成绩
4.7 查询和“孙晓英”在同一个班级的学生的学号、姓名和所在班级
4.8 查询学生表中比“19电商1”班所有学生年龄都小的学生的学号和年龄
4.9 查询学生表中比“19电商1”班某个学生年龄小的学生的学号和年龄
现有教务管理系统的数据库。存放学生的信息。创建数据库jwgl。创建三张表:学生表s,课程表c,成绩表sc。安装数据库,使用图形化界面navicat。
一、创建数据库:
create database jwgl;
二、创建表结构:DDL
2.1 学生表s:
create table s(
sno varchar(8) primary key,
sname varchar(10),
ssex varchar(5),
sage int,
sclass varchar(10),
jg varchar(10)
);
选中,右击,运行已选择的。
2.2 成绩表sc:
create table sc (
sno varchar(10),
cno varchar(10),
score int
);
2.3 课程表c:
create table c (
cno varchar(5) primary key,
cname varchar(255),
precid varchar(255),
cpro varchar(255),
credit int
);
2.4 查看表结构:
desc s; desc sc; desc c;
三、录入数据:DML
3.1 对学生表s的数据录入:
varchar类型的要用引号引起来
insert into 插入数据,s是表,values是值。如果给某些字段添加值(主键必须有值),那么:
insert into s(字段1,字段2) values(字段1的值,字段2的值); 要一一对应起来,如果s表中不写字段,那么值values中必须添加所有的值。
查看select * from s;
3.2 对成绩表sc的数据录入:
insert into sc values("060101","c01",91);
查看表数据:select * from sc;
3.3 对课程表c的数据录入:
insert into c values("c01","高等数学","","公共必修",4);
查看表数据:select * from c;
3.4 通过插入数据,有以下三张表:
学生表:
成绩表:
课程表:
四、查询:
4.1 同时选修了c04和c05的同学的学号
分析:查询的结果是显示学号,课程号和学号,是要在成绩表中查询,并且是选修了c04和c05课程的学号。查出学号,所以是select sno。可以先查出选修了c04课程的学号,且(and)在这些学号中在查出选修c05课程的学号。在成绩表中也可以很清晰的看出选修了c04和c05课程的只有060101学号。语句:
select sno from sc where cno = "c05" and sno in(select sno from sc where cno = "c04");
也可以使用连接的方式:连接sno。
select a.sno from sc a join sc b on a.sno = b.sno where a.cno = "c04" and b.cno = "c05";
4.2 查询选修了“高等数学”的学生的学号和姓名
4.2.1 嵌套查询,多表查询(三张表)
select sno 学号,sname 姓名 from s where sno in (
select sno from sc where cno in (
select cno from c where cname = "高等数学"
)
);
从课程表中查出选修了高等数学的课程号。再根据课程号在成绩表查出学号。再利用学号在学生表中查出学号和姓名。
4.2.2 连接三张表
直接在三张表中查询,利用where语句连接三张表,并且课程表c中课程为高等数学。
select s.sno,sname
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno and cname='高等数学';
4.3 查询没有选课的学生的学号和姓名
distinct:去重
在成绩表中给课程号cno去重,查出cno。根据cno在学生表中查出不在这些cno中的学号和姓名。
select sno,sname
from s
where sno not in(select distinct sno from sc);
4.4 查找只有男同学选修的课程的课程号
查询男同学选修的课程的课程号。由于一个同学有多个课程,那么这个课程是有可能被女生选了的。不能保证选到的课程号只有男生选了。
需要在学生表中先查出女生的学号 并在成绩表中根据这个学号选出女生选的课程号。在反查出以外的课程号。
select cno from sc where cno not in(
select cno from sc where sno in(
select sno from s where ssex='女'));
4.5 查询年龄比“吴俊”大的男同学的学号和年龄
查询出年龄吴俊的年龄。在学生表中根据男生和比吴俊的年龄大的学生,的学号和年龄。
select sno,sage from s
where sage>(select sage from s where sname='吴俊') and ssex='男';
4.6 查询选修了"c04"课程的学生中,成绩比”李露“高的学生的学号和成绩
查询出李露的学号
并且学号是李露的,课程号是c04的成绩,(sc表)
根据这个成绩,比这个成绩高的学号和成绩。
select sno,score from sc where cno='c04' and score>(
select score from sc where sno=(
select sno from s where sname='李露') and cno='c04');
4.7 查询和“孙晓英”在同一个班级的学生的学号、姓名和所在班级
查出孙晓英的班级,根据班级查出学号和姓名和班级。
select sno,sname,sclass from s
where sclass=(select sclass from s where sname='孙晓英');
4.8 查询学生表中比“19电商1”班所有学生年龄都小的学生的学号和年龄
查出班级是19电商1的年龄,在学生表中年龄比查出的年龄都笑的学号和年龄。
select sno,sage from s
where sage<all(select sage from s where sclass='19电商1');
4.9 查询学生表中比“19电商1”班某个学生年龄小的学生的学号和年龄
这里是满足比查出的年龄小的即可,上一题是比查出的最小的还要小,也就是比查出所用的年龄都小。这里就是满足能够比查出的年龄小的就可以了,使用any。
select sno,sage from s
where sage<any(select sage from s where sclass='19电商1');
4.10 查询选修c04成绩最高分学生的学号和成绩
根据成绩降序排序,desc降序,asc升序,limit取第一个最高分。
select sno,score from sc
where cno='c04' order by score desc limit 1;
4.11 聚合函数
4.11.1 count计数
select count(sno) 学生人数 from s;
select count(ssex) as 男生人数 from s where ssex='男';
统计有多少人选修了,对学号去重,并计数
select count(distinct sno) as 选课人数 from sc;
4.11.2 去重计数
统计有多少门课被选修了
select count(distinct cno) 被选课程数 from sc;
4.11.3 求和
求学生060101的总分数
select sum(score) as 总分 from sc where sno='060101';
4.11.4 最高分
找出课程c01的最高分。select max(score) as 最高分 from sc where cno='c01';
4.11.5 平均值
select avg(score) as 平均分 from sc where sno='060101';
由于c03课程没有成绩,在取平均数时只用三个值,故266/3为平均数。
---end---