Linux运维：mysql高级查询语句(2)

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---

心在征途

关注

25
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
Linux运维：mysql高级查询语句(2)

现有系统的数据库。存放学生的信息。创建数据库创建三张表：学生表s，课程表c，成绩表sc。安装数据库，使用图形化界面navicat。
复制链接

扫一扫